0

This is not the same question as double quoted elements in csv cant read with pandas.

The difference is that in that question: "ABC,DEF" was breaking the code.

Here, "ABC "DE" ,F" is breaking the code.

The whole string should be parsed in as 'ABC "DE", F'. Instead the inside double quotes are leading to the below-mentioned issue.

I am working with a csv file that contains the following type of entries:

header1, header2, header3,header4

2001-01-01,123456,"abc def",V4

2001-01-02,789012,"ghi "jklm" n,op",V4

The second row of data is breaking the code, with the following error:

ParserError: Error tokenizing data. C error: Expected 4 fields in line 1234, saw 5

I have tried playing with various sep, delimiter & quoting etc. arguments but nothing seems to work.

Can someone please help with this? Thank you!

Community
  • 1
  • 1
user10771507
  • 1
  • 1
  • 1
    Check this - https://stackoverflow.com/questions/26595819/double-quoted-elements-in-csv-cant-read-with-pandas – Tom Ron Dec 10 '18 at 15:44
  • It's not the same issue. Please see update to the question above. Thank you, Tom. – user10771507 Dec 10 '18 at 15:57
  • read the file as a single text string with fp.read. ` from io import StringIO; clean_text = text.replace(',"', '$$commadquote').replace('",', '$$dquotecomma').replace('"', "'").replace('$$commadquote', ',"').replace( '$$dquotecomma', '",'); pd.read_csv(StringIO(clean_text)) – Andrew Lavers Dec 10 '18 at 17:48

1 Answers1

0

Based on the two rows you have provided here is an option where the text file is read into a Series object and then regex extract is used via Series.str.extract() get the information you want in a DataFrame:

with open('so.txt') as f:
    contents = f.readlines()

s = pd.Series(contents)

s now looks like the following:

0 header1, header2, header3,header4\n 1 \n 2 2001-01-01,123456,"abc def",V4\n 3 \n 4 2001-01-02,789012,"ghi "jklm" n,op",V4

Now you can use regex extract to get what you want into a DataFrame:

df = s.str.extract('^([0-9]{4}-[0-9]{2}-[0-9]{2}),([0-9]+),(.+),(\w{2})$')

# remove empty rows
df = df.dropna(how='all')

df looks like the following:

0 1 2 3 2 2001-01-01 123456 "abc def" V4 4 2001-01-02 789012 "ghi "jklm" n,op" V4

and you can set your columns names with df.columns = ['header1', 'header2', 'header3', 'header4']

jeschwar
  • 1,286
  • 7
  • 10