extra commas when using read_csv causing too many "s in data frame

Question

I'm trying to read in a large file (~8Gb) using pandas read_csv. In one of the columns in the data, there is sometimes a list which includes commas but it enclosed by curly brackets e.g.

"label1","label2","label3","label4","label5"

"{A1}","2","","False","{ "apple" : false, "pear" : false, "banana" : null}

Therefore, when these particular lines were read in I was getting the error "Error tokenizing data. C error: Expected 37 fields in line 35, saw 42". I found this solution which said to add sep=",(?![^{]*})" into the read_csv arguments which worked with splitting the data correctly. However, the data now includes the quotation marks around every entry (this didn't happen before I added the sep argument in).

The data looks something like this now:

"label1" "label2" "label3" "label4" "label5"

"{A1}" "2" "" "False" "{ "apple" : false, "pear" : false, "banana" : null}"

meaning I can't use, for example, .describe(), etc on the numerical data because they're still strings.

Does anyone know of a way of reading it in without the quotation marks but still splitting the data where it is?

Very new to Python so apologies if there is an obvious solution.

serialdev found a solution to removing the "s but the data columns are objects and not what I would expect/want, e.g. the integer values aren't seen as integers.

The data needs to be split at "," explicitly (including the "s), is there a way of stating that in the read_csv arguments?

Thanks!

Answer 1

To read in the data structure you specified, where the last element is an unknown length.

"{A1}","2","","False","{ "apple" : false, "pear" : false, "banana" : null}"

"{A1}","2","","False","{ "apple" : false, "pear" : false, "banana" : null, "orange": "true"}"

Change the separate to a regular expression using a negative forward lookahead assertion. This will enable you to separate on a ',' only when not immediately followed by a space.

df = pd.read_csv('my_file.csv', sep='[,](?!\s)', engine='python', thousands='"')

print df

        0  1   2        3                                                  4
0  "{A1}"  2 NaN  "False"  "{ "apple" : false, "pear" : false, "banana" :...
1  "{A1}"  2 NaN  "False"  "{ "apple" : false, "pear" : false, "banana" :...

Specifying the thousands separator as the quote is a bit of a hackie way to parse fields contains a quoted integer into the correct datatype. You can achieve the same result using converters which can also remove the quotes from the strings should you need it to and cast "True" or "False" to a boolean.