Processing multiple csv's with Pandas which start at different rows

user_15969816 0
Viewed 53

I am currently working on processing multiple CSV files in a PD dataframe with a for loop like this:

for csvfile in all_filenames:
    with open(csvfile, 'r') as csvopen:
        content = csvopen.read() 
        csvopen.seek(0)
        dialect = csv.Sniffer().sniff(csvopen.readline(), [',',';','\t'])
    combined_csv = pd.read_csv(csvfile, sep=dialect.delimiter)
    appended_data.append(combined_csv)
    
appended_data = pd.concat(appended_data)

The issue is that the CSV files sometimes contain blank lines and can additionally contain 1 line of redundant information, resulting in 3 variants. The column names are also different, which I remap using a dict (see code below).

E.g.:

CSV 1
Line 1: No, Company, Contact Person name
Line 2: 1, EG, Tim

CSV 2
Line 1:
Line 2:
Line 3: No, Company, Contact Person
Line 4: 2, Given, Sarah

CSV 3
Line 1:
Line 2:
Line 3: This file contains information about..
Line 4: Number, Company name, Contact Person
Line 5: 3, Example, Johnny

Is there any way I can dynamically process these files without having to delete the lines manually beforehand? I would like to ignore the blank lines and description line in the file (CSV 3, Line 3 in example), if applicable, so they aren't used by the sniffer in determining the delimter and aren't included in the dataframe.

An additional issue is that the column names are spelled differently. I currently use a dict to map these columns to one value, but I can only process this once the file is processed into a dataframe.

general_dict = {'Number': ['No'],
                'Company': ['Company name']}

    col_list = combined_csv.columns.to_list()
      
    for col in col_list:
        for key, val in general_dict.items():
            if col in val:
                rename_dict[col] = key
            
                break
pandas csv python
1 Answers

After examining the problematic CSV files a bit further and thanks to the suggestion by @tripleee, I identified a \n\n character when opening the csv with latin1 encoding.

By adding the following lines of code I was able to process all files in one go.

content = re.sub('\ufeffTextbox7[\n]','',content)
content = re.sub('.*[\n]{2,}','',content)

Not the most clean solution, but works for my case!

for csvfile in all_filenames:
    try:
        with open(csvfile, 'r') as csvopen:
            content = csvopen.read() 
            csvopen.seek(0)
            dialect = csv.Sniffer().sniff(csvopen.readline(), [',',';','\t'])
    except:
        with open(csvfile, 'r', encoding='utf-8') as csvopen:
            content = csvopen.read() 
            content = re.sub('\ufeffTextbox7[\n]','',content)
            content = re.sub('.*[\n]{2,}','',content)
            csvopen.seek(0)
            dialect = csv.Sniffer().sniff(csvopen.read(10000), [',',';','\t'])
    combined_csv = pd.read_csv(StringIO(content), sep=dialect.delimiter, engine='python', quotechar='"', encoding='utf8')

One re.sub for the double new line breaks could work, but I'd rather seperate them should \uffef not occur in the file.

user_159698160
Related