Import CSV with nested JSON into Pandas DataFrame

Question

I'm trying to import a csv file with a two nested JSON Obejcts inside using Jupiter Notebook.

I'm getting this error.

ParserError: Error tokenizing data. C error: Expected 29 fields in line 3, saw 35

The problem is that Pandas doesn't recognise the JSON Object and just uses the CSV delimiters which is a comma.

Here's a sample row of the CSV file:

309,DVD10_Welt.mxf,16947519284,00:37:32:24,0_yd3ugljx,"{"Type":"Source","Content-Type":"Beitrag"}",97,"Welt",NULL,NULL,NULL,"{"ContentType":"Beitrag","Description":"Sie beobachten jeden.","Keywords":["wissensthek","zukunft","\u00dcberwachung","roboter","technik","internet","dvd","wissen"],"ProductionDate":"2013-07-10T00:30:06.000Z","TitleIntern":null}"

This is my Line in Jupyter:

df = pd.read_csv(csv_file)
df

Can someone pls give me a hint?

Thanks Manuel

MaxU - stand with Ukraine · Accepted Answer · 2018-03-06 21:00:02Z

1

I don't think you can read it without preprocessing, because it's not a valid CSV file.

If you can save your CSV file properly quoted - it'll work.

Demo:

In [87]: df = pd.DataFrame({'ID':[1,2]})

In [88]: df['JSON'] = '{"Type":"Source","Content-Type":"Beitrag"}'

In [89]: df
Out[89]:
   ID                                        JSON
0   1  {"Type":"Source","Content-Type":"Beitrag"}
1   2  {"Type":"Source","Content-Type":"Beitrag"}

In [90]: df.to_csv('d:/temp/a.csv', index=False)

Resulting CSV:

ID,JSON
1,"{""Type"":""Source"",""Content-Type"":""Beitrag""}"
2,"{""Type"":""Source"",""Content-Type"":""Beitrag""}"

Check:

In [91]: pd.read_csv('d:/temp/a.csv')
Out[91]:
   ID                                        JSON
0   1  {"Type":"Source","Content-Type":"Beitrag"}
1   2  {"Type":"Source","Content-Type":"Beitrag"}

answered Mar 6, 2018 at 21:00

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Manuel Over a year ago

Thanks for your quick reply. I appreciate. Do you see any chance to get this preprocessing done in Python are does it have to be done by hand?

MaxU - stand with Ukraine Over a year ago

@Manuel, you can try to do it in Python, but it will not work for generic cases and this code might break any time... I would try to save CSV file properly instead...

Collectives™ on Stack Overflow

Import CSV with nested JSON into Pandas DataFrame

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related