CODEWITHSUNDEEP
pythoncsvdata-munging
BAI
BAI

Reputation: 621

Strip white spaces from CSV file

I need to stripe the white spaces from a CSV file that I read 

import csv

aList=[]
with open(self.filename, 'r') as f:
    reader = csv.reader(f, delimiter=',', quoting=csv.QUOTE_NONE)
    for row in reader:
        aList.append(row)
    # I need to strip the extra white space from each string in the row
    return(aList)

Upvotes: 44

Views: 108569

Answers (10)

Alexander Martins
Alexander Martins

Reputation: 383

The following code may help you:

import pandas as pd

aList = pd.read_csv(r'filename.csv', sep='\s*,\s*', engine='python')

Upvotes: 0

timothyzhang
timothyzhang

Reputation: 800

I figured out a very simple solution:

import csv

with open('filename.csv') as f:
  reader = csv.DictReader(f)
  rows = [ { k.strip(): v.strip() for k,v in row.items() } for row in reader ]

Upvotes: 2

Luke404
Luke404

Reputation: 10620

and here is Daniel Kullmann excellent solution adapted to Python3:

import re

class CSVSpaceStripper:
    """strip whitespaces around delimiters in the file
    NB has hardcoded delimiter ";"
    """

    def __init__(self, filename):
        self.fh = open(filename, "r")
        self.surroundingWhiteSpace = re.compile(r"\s*;\s*")
        self.leadingOrTrailingWhiteSpace = re.compile(r"^\s*|\s*$")

    def close(self):
        self.fh.close()
        self.fh = None

    def __iter__(self):
        return self

    def __next__(self):
        line = self.fh.readline()
        line = self.surroundingWhiteSpace.sub(";", line)
        line = self.leadingOrTrailingWhiteSpace.sub("", line)
        return line

Upvotes: 0

CivFan
CivFan

Reputation: 15112

In my case, I only cared about stripping the whitespace from the field names (aka column headers, aka dictionary keys), when using csv.DictReader.

Create a class based on csv.DictReader, and override the fieldnames property to strip out the whitespace from each field name (aka column header, aka dictionary key).

Do this by getting the regular list of fieldnames, and then iterating over it while creating a new list with the whitespace stripped from each field name, and setting the underlying _fieldnames attribute to this new list.

import csv

class DictReaderStrip(csv.DictReader):
    @property                                    
    def fieldnames(self):
        if self._fieldnames is None:
            # Initialize self._fieldnames
            # Note: DictReader is an old-style class, so can't use super()
            csv.DictReader.fieldnames.fget(self)
            if self._fieldnames is not None:
                self._fieldnames = [name.strip() for name in self._fieldnames]
        return self._fieldnames

Upvotes: 17

Nuno André
Nuno André

Reputation: 5349

The most memory-efficient method to format the cells after parsing is through generators. Something like:

with open(self.filename, 'r') as f:
    reader = csv.reader(f, delimiter=',', quoting=csv.QUOTE_NONE)
    for row in reader:
        yield (cell.strip() for cell in row)

But it may be worth moving it to a function that you can use to keep munging and to avoid forthcoming iterations. For instance:

nulls = {'NULL', 'null', 'None', ''}

def clean(reader):
    def clean(row):
        for cell in row:
            cell = cell.strip()
            yield None if cell in nulls else cell

    for row in reader:
        yield clean(row)

Or it can be used to factorize a class:

def factory(reader):
    fields = next(reader)

    def clean(row):
        for cell in row:
            cell = cell.strip()
            yield None if cell in nulls else cell

    for row in reader:
        yield dict(zip(fields, clean(row)))

Upvotes: 4

Finger Picking Good
Finger Picking Good

Reputation: 400

Read a CSV (or Excel file) using Pandas and trim it using this custom function.

#Definition for strippping whitespace
def trim(dataset):
    trim = lambda x: x.strip() if type(x) is str else x
    return dataset.applymap(trim)

You can now apply trim(CSV/Excel) to your code like so (as part of a loop, etc.)

dataset = trim(pd.read_csv(dataset))
dataset = trim(pd.read_excel(dataset))

Upvotes: 2

daniel kullmann
daniel kullmann

Reputation: 14023

You can create a wrapper object around your file that strips away the spaces before the CSV reader sees them. This way, you can even use the csv file with cvs.DictReader.

import re

class CSVSpaceStripper:
  def __init__(self, filename):
    self.fh = open(filename, "r")
    self.surroundingWhiteSpace = re.compile("\s*;\s*")
    self.leadingOrTrailingWhiteSpace = re.compile("^\s*|\s*$")

  def close(self):
    self.fh.close()
    self.fh = None

  def __iter__(self):
    return self

  def next(self):
    line = self.fh.next()
    line = self.surroundingWhiteSpace.sub(";", line)
    line = self.leadingOrTrailingWhiteSpace.sub("", line)
    return line

Then use it like this:

o = csv.reader(CSVSpaceStripper(filename), delimiter=";")
o = csv.DictReader(CSVSpaceStripper(filename), delimiter=";")

I hardcoded ";" to be the delimiter. Generalising the code to any delimiter is left as an exercise to the reader.

Upvotes: 3

CaraW
CaraW

Reputation: 499

There's also the embedded formatting parameter: skipinitialspace (the default is false) http://docs.python.org/2/library/csv.html#csv-fmt-params

aList=[]
with open(self.filename, 'r') as f:
    reader = csv.reader(f, skipinitialspace=False,delimiter=',', quoting=csv.QUOTE_NONE)
    for row in reader:
        aList.append(row)
    return(aList)

Upvotes: 49

mgilson
mgilson

Reputation: 309949

with open(self.filename, 'r') as f:
    reader = csv.reader(f, delimiter=',', quoting=csv.QUOTE_NONE)
    return [[x.strip() for x in row] for row in reader]

Upvotes: 14

sapi
sapi

Reputation: 10224

You can do:

aList.append([element.strip() for element in row])

Upvotes: 4

Related Questions