Parsing json in csv in pandas not working

Question

I have this data:

#my.csv
"accountid","configuration"
"797847293","{'dBInstanceIdentifier': 'grafanadb', 'dBInstanceClass': 'db.t3.micro', 'engine': 'postgres', 'dBInstanceStatus': 'available'}"

How can I bring into format like:

# result.csv
accountid,dBInstanceIdentifier,dbInstanceClass,engine
797847293,grafanadb,db.t3.micro,engine

I tried it with python pandas, but not result so far respectively errors.

Any ideas?

You will have to load the data and write an algorithm to transform the data as you wish, then write to the result.csv. — Januka samaranyake
– Januka samaranyake, Commented Apr 19, 2022 at 13:16

Ynjxsjmh · Accepted Answer · 2022-04-19 14:10:01Z

1

You can read the csv to dataframe and convert configuration to dict with ast.literal_eval. Then use pd.json_normalize to convert a column of dictionary into columns.

import ast

df = pd.read_csv('data.csv', quotechar='"')

out = pd.concat([df['accountid'], pd.json_normalize(df['configuration'].apply(ast.literal_eval))], axis=1)

print(out)

   accountid dBInstanceIdentifier dBInstanceClass    engine dBInstanceStatus
0  797847293            grafanadb     db.t3.micro  postgres        available

answered Apr 19, 2022 at 14:10

Ynjxsjmh

30.3k7 gold badges43 silver badges64 bronze badges

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

1 Comment

WorkoutBuddy Over a year ago

Perfect, that is what I was looking for. I could reduce my code a lot. Could be so easy to make the manager happy xD

rcshon · Accepted Answer · 2022-04-19 13:25:25Z

1

Assuming you've gotten this csv file from some data source, it shouldn't be this inconsistent in its formatting and should have a double quote " at the end of the second line which I believe you've missed out when copying over. Like this:

#my.csv
"accountid","configuration"
"797847293","{'dBInstanceIdentifier': 'grafanadb', 'dBInstanceClass': 'db.t3.micro', 'engine': 'postgres', 'dBInstanceStatus': 'available'}"

If that is the case, you can simply specify the quotechar argument in pd.read_csv to tell it to ignore delimiters inside the double quotes "

df = pd.read_csv('a.csv', quotechar='"')

You can then do your processing in pandas before writing back into csv from here.

answered Apr 19, 2022 at 13:25

rcshon

9271 gold badge7 silver badges12 bronze badges

Collectives™ on Stack Overflow

Parsing json in csv in pandas not working

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related