How To Pre-process Data Before Pandas.read_csv()

I have a slightly broken CSV file that I want to pre-process before reading it with pandas.read_csv(), i.e. do some search/replace on it. I tried to open the file and and do the pr

Solution 1:

After further investigation of Pandas' source, it became apparent, that it doesn't simply require an iterable, but also wants it to be a file, expressed by having a read method (is_file_like() in inference.py).

So, I built a generator the old way

class InFile(object):
def __init__(self, infile):
    self.infile = open(infile)

def __next__(self):
    return self.next()

def __iter__(self):
    return self

def read(self, *args, **kwargs):
    return self.__next__()

def next(self):
    try:
        line: str = self.infile.readline()
        line = re.sub(r'","',r',',line) # do some fixing
        return line
    except:
        self.infile.close()
        raise StopIteration

This works in pandas.read_csv():

df = pd.read_csv(InFile("some.csv"))

To me this looks super complicated and I wonder if there is any better (→ more elegant) solution.

Solution 2:

Here's a solution that will work for smaller CSV files. All lines are first read into memory, processed, and concatenated. This will probably perform badly for larger files.

import re
from io import StringIO
import pandas as pd

with open('file.csv') as file:
    lines = [re.sub(r'","', r',', line) for line in file]

df = pd.read_csv(StringIO('\n'.join(lines)))