How do i parse a json string in a csv column and break it down into multiple columns?

Question

My objective was to read a json string located in the 4th column "REQUEST_RE" of a csv file and breakdown that 4th column into its own individual columns.

my data is in the following format per csv row on the 4th column:

Row 2: {"Fruit":"Apple","Cost":"1.5","Attributes":{"ID":"001","Country":"America"}}

Row 3: {"Fruit":"Orange","Cost":"2.0","Attributes":{"ID":"002","Country":"China"}}

to be changed into:

i was trying this: Parsing a JSON string which was loaded from a CSV using Pandas

and i ended up using this:

InterimReport = pd.read_csv(filename, index_col=False, usecols=lambda col: col not in ["SYSTEMID"]) InterimReport.join(InterimReport['REQUEST_RE'].apply(json.loads).apply(pd.Series))

but i was unable to split my json string into columns.

my json string still remained a json string and was unchanged.

json is a core module in Python, maybe you should use that? Show the first few lines of the full file, not just one column, and also your code that reads it. — Tomalak
– Tomalak, Commented May 28, 2020 at 8:15

Serge Ballesta · Accepted Answer · 2020-05-28 09:05:13Z

1

You should load the CSV file ignoring the JSON string at this time.

Then you convert the column to a json list and normalize it:

tmp = pd.json_normalize(InterimReport['REQUEST_RE'].apply(json.loads).tolist()).rename(
          columns=lambda x: x.replace('Attributes.', ''))

You should get something like:

    Fruit Cost   ID  Country
0   Apple  1.5  001  America
1  Orange  2.0  002    China

That you can easily concat to the original dataframe:

InterimReport = pd.concat([InterimReport.drop(columns=['REQUEST_RE']), tmp], axis=1)

answered May 28, 2020 at 9:05

Serge Ballesta

150k13 gold badges137 silver badges267 bronze badges

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

Collectives™ on Stack Overflow

How do i parse a json string in a csv column and break it down into multiple columns?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related