How to fix extra comma column offset CSV using Pandas?

Question

The CSV file looks like this:

Actual data format

id,account,company,total_emp,country,state
1,12345,ABC,100,US,CA
2,32345,CCD,inc,100,US,USA,NA   
3,42345,ABC,LLC,100,US,WA
4,52345,DDM,100,CA,US,OR  
5,62345,TSL,100,US,UT
....

Easy to view

id, account, company, total_emp, country,  state
1,   12345,   ABC,     100,        US,       CA
2,   32345,   CCD,inc  100,        US,USA,   
3,   42345,   ABC,LLC  100,        US,       WA
4,   52345,   DDM,     100,        CA, US,   OR  
5,   62345,   TSL,     100,        US,       UT
....

Things to note:

• The column values for company do not have any quotation.
• There are other columns after 'company'.
• There are 2 columns with a comma issue.

Here, on id 2 and 3, we can see that the values are not in the quote but it has multiple commas. I am getting the following error: ValueError: 5 columns passed, passed data had 6 columns.

What did I do?

• unique issue: I tried to offset the value using readlines() but since I have a column after the company column, it did not work.

Could someone please help me solve this problem?

Answer 1

What you can do is to read the file line-by-line. For any correctly formatted lines, the logic is easy but for those with extra commas, the logic is more complex. The id, account and state columns can be found directly. To get the company value, we can try to find which column is total_emp as that one is numeric. In the below code, I assume that company contains at most 1 extra comma, however, the code can be extended to work for more.

After company is found, total_emp is easy and country will then be the rest of the values.

with open('a.csv') as f:
    for line in f.readlines()[1:]:  # ignore the header
        if line.count(',') == 5:
            id, account, company, total_emp, country, state = line.strip().split(',')
        else:
            fields = line.strip().split(',')
            id, account = fields[:2]
            state = fields[-1]

            if fields[3].isnumeric():
                company = fields[2]
                marker = 3
            else:
                company = ','.join(fields[2:4])
                marker = 4
            total_emp = fields[marker]
            country = ','.join(fields[-len(fields)+marker+1:-1])

Printing the result as follows:

print('id:', id, 'account:', account, 'company:', company, 'total_emp:', total_emp, 'country:', country, 'state:', state)

yields:

id: 1 account: 12345 company: ABC total_emp: 100 country: US state: CA
id: 2 account: 32345 company: CCD,inc total_emp: 100 country: US,USA state: NA
id: 3 account: 42345 company: ABC,LLC total_emp: 100 country: US state: WA
id: 4 account: 52345 company: DDM total_emp: 100 country: CA,US state: OR
id: 5 account: 62345 company: TSL total_emp: 100 country: US state: UT

Answer 2

How about use regex first to format the data.

import re

f = open("file.csv", "r")
f.readline()
data = []
for line in f.readlines():
    data.append(re.match(r"(?P<id>\d+),(?P<account>\d+),(?P<company>.*),(?P<total_emp>\d+),(?P<country>.+),(?P<state>.+)$", line).groupdict())
print(data)

Then, you will get the data you need.

[   {   'account': '12345',
        'company': 'ABC',
        'country': 'US',
        'id': '1',
        'state': 'CA',
        'total_emp': '100'},
    {   'account': '32345',
        'company': 'CCD,inc',
        'country': 'US,USA',
        'id': '2',
        'state': 'NA',
        'total_emp': '100'},
    {   'account': '42345',
        'company': 'ABC,LLC',
        'country': 'US',
        'id': '3',
        'state': 'WA',
        'total_emp': '100'},
    {   'account': '52345',
        'company': 'DDM',
        'country': 'CA,US',
        'id': '4',
        'state': 'OR',
        'total_emp': '100'},
    {   'account': '62345',
        'company': 'TSL',
        'country': 'US',
        'id': '5',
        'state': 'UT',
        'total_emp': '100'}]

After that, you can use pandas:

In [4]: pandas.DataFrame(data).set_index("id")
Out[4]:
   account  company country state total_emp
id
1    12345      ABC      US    CA       100
2    32345  CCD,inc  US,USA    NA       100
3    42345  ABC,LLC      US    WA       100
4    52345      DDM   CA,US    OR       100
5    62345      TSL      US    UT       100

Answer 3

Split the line on commas, then use slices to get the fields relative to the beginning and end of the line. The company name is everything between them.

for line in file:
    fields = line.strip().split(',')
    id, account = fields[:2]
    total_emp, country, state = fields[-3:]
    company = ','.join(fields[3:-3])
    # rest of code

This would be much harder if you had multiple fields that could have embedded commas.

EDIT After I wrote this answer you changed the file so it has multiple countries separated by commas. Now I don't have any good suggestions.