read_csv() with messy quotation

Question

I have a CSV-file I want to read into a pandas df, but encounter some troubles.

The CSV-file (file.csv) has a structure as following:

header1, header2, header3 "abc, \" hello \" ", 0, "aaa" "abc, \", 0, "aaa"

I want the following output:

header1, header2, header3 "abc, hello", 0, aaa abc, 0, aaa

If I apply pd.read_csv("file.csv", header=None, escapechar = "\\") I get the following output:

 header1 header2 header3 0 abc, " hello " 0.0 "aaa" 1 abc ", 0, aaa" NaN NaN

I actually don't care about special characters, comma's etc. I only want the correct values to be in the correct columns as illustrated.

I can't reproduce the issue with pd.read_csv("file.csv", header=None, escapechar = "\\"). Can you add to your question, a more complete example, the code used and the full traceback error ? — Timeless
– Timeless, Commented Jan 29, 2023 at 10:46
@Timeless Thanks for your feedback. The question is updated. — ozturkdk
– ozturkdk, Commented Jan 29, 2023 at 12:03

Timeless · Accepted Answer · 2023-01-29 12:23:52Z

I actually don't care about special characters, comma's etc. I only want the correct values to be in the correct columns as illustrated.

Here is a proposition with pandas.read_csv :

from io import StringIO s = """header1, header2, header3 "abc, \" hello \" ", 0, "aaa" "abc, \", 0, "aaa"""  df = (pd.read_csv(StringIO(s)) # <- put here your .csv-pathfile .astype(str) # <- to cast all the cols as str for cleaning .replace({'[",]*': "", "\s+": " ", "\s*$": ""}, regex=True) .rename(columns=str.strip) .infer_objects() )

Output :

print(df) header1 header2 header3 0 abc hello 0 aaa 1 abc 0 aaa

df.replace accepts a dictionnary to replace multiple substrings. Here, we first get rid of special characters, then replace extra whitespaces with a single whitespace and finally remove any trailing whitespaces. You can comment (#) the line when we call df.replace to see the difference.

Collectives™ on Stack Overflow

read_csv() with messy quotation

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related