Pandas read_csv not ignoring commas inside quotted string

Question

I have a exported csv dataset which allows html text from users and I need to turn it into a DataFrame.

The columns with possible extra commas are quotted with ", but the parser is using the commas inside them as separators.

This is the code I'm using, and I've already tried solutions from a github issue and another post here.

pd.read_csv(filePath,sep=',', quotechar='"', error_bad_lines=False)

results in

Here is the csv file itself, with the columns and first entry.

I don't know what the issue is, quotechar was supposed to work, maybe the extra " inside the quotted string?

score 1 · Accepted Answer · answered May 16 '21 at 22:19

1

Here's the issue you're running into:

You set quote (") as your quotechar. Unfortunately, you also have quotes in your text:

<a href ="....">

And so... after that anchor tag, the next few commas are NOT considered inside quotes. Your best bet is probably to remake the original csv file with something else as quotechar (that doesn't appear at all in your text).

answered May 16 '21 at 22:19

DataSci Live

28
4

Was hoping this was not the problem, as regex are a pain to work with. I'll try replacing the quotechar, will post updates if it works. – Diogo Neiss May 16 '21 at 22:36
1

Replaced ", and ," with \`, and ,\` as they are not used in the text and changed the quotechar to "`", worked like a charm. – Diogo Neiss May 17 '21 at 14:13
Glad it worked for you! Regex can be a pain! – DataSci Live May 17 '21 at 23:05

Pandas read_csv not ignoring commas inside quotted string

1 Answers1