Strip white spaces from CSV file

Question

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)

Killen

Answer 1

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)

Dorladorlisa

To stripe white spaces: reader = csv.reader(f, skipinitialspace=True,delimiter=',', quoting=csv.QUOTE_NONE), right? – Wealthy

Not removing trailling spaces? Why is there not option for this??? – Disposure

Answer 2

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

Stormi

If your whitespace is only to the left of the field name like ' date' and not 'date ' or ' date ', csv.DictReader(csvfile, skipinitialspace=True) works just as well. – Atonsah

Answer 3

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]

Sleepless

This is the best possible solution using the csv module. The Reader class returned by the csv.reader() function isn't exposed in the csv or _csv module to allow overriding its next() method. – Stormi

@Stormi It's even better to yield a generator, especially with big files: for row in reader: yield (c.strip() for c in row) – Indian

Answer 4

You can do:

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

Edmondson

Answer 5

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)))

Campanile

Answer 6

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.

Comestible

Expanding this solution to the general case would end up in a re-write of the csv module. – Stormi

Answer 7

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))

Cantonment

Answer 8

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 ]

Eonian

I like this solution, but if your cells are empty, you will need to protect the strip() calls to run only on strings. E.g., [ { k.strip() if isinstance(k, str) else k: v.strip() if isinstance(v, str) else v for k,v in row.items() } for row in reader ] – Ambriz

Answer 9

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

Mlawsky

Answer 10

The following code may help you:

import pandas as pd

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

Contend