Creating a dataframe from values extracted from a json column in Pandas

Question

I loaded a .csv file into a df, and one of the row of a columns contains a list of dictionary like below.

data = [{"character": "Jake Sully", "gender": 2,}, {"character": "Neytiri", "gender": 1},                                                         
        {"character": "Dr. Grace Augustine","gender": 1},         
        {"character": "Col. Quaritch", "gender": 2]

But of course after loading it, it's read as a string. So, I converted each row in the column to a json, which makes it easy to extract values based on the key name. I then need to create a seperate df like so.

df = {'character': ['Jake Sully','Neytiri', 'Dr. Grace Augustine', 'Col.Quaritch'], 
    'gender': [2, 1, 1, 2]}

This is my code but I can't quite get the desired df ouput right.

df = pd.DataFrame() #create new df
keys = ['character','gender'] #keys to extract values from json
lst=[]
for val in data: #to iterate over data series
    for object in json.loads(val):
        for key in keys:
            lst.append(object[key])
    df = pd.concat([df,pd.DataFrame(lst,columns=[key])], axis=1)

Can someone tell me what i am doing wrong?

Related?

akilat90
– akilat90

2018-06-06 13:31:50 +00:00
Commented Jun 6, 2018 at 13:31 — akilat90
– akilat90, Commented Jun 6, 2018 at 13:31

jpp · Accepted Answer · 2018-06-06 10:37:00Z

2

pd.DataFrame accepts a list of dictionaries directly:

data = [{"character": "Jake Sully", "gender": 2,},
        {"character": "Neytiri", "gender": 1},
        {"character": "Dr. Grace Augustine","gender": 1},
        {"character": "Col. Quaritch", "gender": 2}]

df = pd.DataFrame(data)  # or pd.DataFrame.from_dict(data)

print(df)

             character  gender
0           Jake Sully       2
1              Neytiri       1
2  Dr. Grace Augustine       1
3        Col. Quaritch       2

Therefore, you only need to extract a list of dictionaries from your json file. One way you can do this is via json.loads.

A better idea is to read your data directly into a dataframe via pd.read_json.

answered Jun 6, 2018 at 10:37

jpp

166k37 gold badges301 silver badges362 bronze badges

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

4 Comments

Zoozoo Over a year ago

Thanks, but as I have mentioned, the file is a .csv file, which contains one column that is a list of dictionary. And after pd.read_csv('filename.csv'), I did use json.loads in the code above.

Zoozoo Over a year ago

I did, but i get a ValueError: Expected object or value. That's why i loaded as pd.read_csv('filename.csv'), after which i iterate over each row in the column and then use json.loads(row_in_col_on_interst).

Zoozoo Over a year ago

Sorry! typo. V is actually the iterator val in for val in data: have updated it.

jpp Over a year ago

@Zoozoo, You can try including pd.read_csv('filename.csv').to_dict() in your question.

U13-Forward · Accepted Answer · 2018-06-06 10:43:25Z

0

I may be don't understand your question completely, but I am able to get df just fine.

data = [{"character": "Jake Sully", "gender": 2,}, 
         {"character": "Neytiri", "gender": 1},
         {"character": "Dr. Grace Augustine","gender": 1},
         {"character": "Col. Quaritch", "gender": 2}]

pd.DataFrame(data)

Out:

             character       gender
0           Jake Sully       2
1              Neytiri       1
2  Dr. Grace Augustine       1`

edited Jun 6, 2018 at 10:43

U13-Forward

71.8k15 gold badges100 silver badges125 bronze badges

answered Jun 6, 2018 at 10:41

dgomzi

961 silver badge14 bronze badges

1 Comment

Zoozoo Over a year ago

data represents only ONE row of the column of interest. It's to show that after i loaded the .csv file using pd.read_csv('filename.csv'), this column is loaded as string.

Zoozoo · Accepted Answer · 2018-06-06 12:24:34Z

0

figured it out.

df = pd.DataFrame() #create new df
keys = ['character','gender'] #keys to extract values from json
for i,key in enumerate(keys):
     lst_i = []
     for row in data: #iterating over the rows in the cols of interest 
          for object in json.loads(row):
              lst_i.append(object[key])
     df = pd.concat([df,pd.DataFrame(lst_i,columns=[key])], axis=1)

answered Jun 6, 2018 at 12:24

Zoozoo

2405 silver badges14 bronze badges

Collectives™ on Stack Overflow

Creating a dataframe from values extracted from a json column in Pandas

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related