Inserting list element into pandas dataframe

Question

I have one dataframe and a json format variable.

json_obj = { 1: [ {"father": "john", "father_age": 50},
                  {"son": "johnson", "son_age": 20} ],
             2: [ {"father": "smith", "father_age": 60},
                  {"son": "smithson", "son_age": 30} ] }
# df
index  area 
  1    CA
  2    NY
  3    TX

I want to insert new column with inner dictionary's key and its value into df

# df_final
index  area father  father_age    son      son_age
  1    CA   john        50     joshnson       20
  2    NY   smith       60     smithson       30
  3    TX

If its index and dictionary's key match, I want to insert data in that row. How can I make this?

BENY · Accepted Answer · 2020-05-14 04:44:57Z

4

What I will do

from collections import ChainMap
df=df.join(pd.Series(json_obj).map(lambda x : dict(ChainMap(*x))).apply(pd.Series))

answered May 14, 2020 at 4:44

BENY

324k22 gold badges176 silver badges250 bronze badges

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

Comments

sammywemmy · Accepted Answer · 2020-05-14 05:28:17Z

defaultdict could help here :

from collections import defaultdict
d = defaultdict(list)
for k,v in json_obj.items():
    for entry in v:
        for key, val in entry.items() :
            d[key].append(val)
    d['index'].append(k))

print(d)

defaultdict(list,
            {'father': ['john', 'smith'],
             'father_age': [50, 60],
             'son': ['johnson', 'smithson'],
             'son_age': [20, 30],
             'index': [1, 2]})

res = pd.DataFrame(d).set_index("index")

#read in dataframe : 
df = pd.read_clipboard(index_col=0)
df

         area
index   
 1        CA
 2        NY
 3        TX

#join both dfs on the index:
df.join(res)



        area    father  father_age  son        son_age
index                   
   1    CA      john    50.0       johnson      20.0
   2    NY      smith   60.0       smithson     30.0
   3    TX      NaN     NaN        NaN          NaN

Ch3steR · Accepted Answer · 2020-05-14 12:32:49Z

1

pd.DataFrame can convert a list of dictionaries to DataFrame. Extract values from your JSON object. And use pd.concat along axis =1.

t = map(lambda x: dict(p for d in x for p in d.items()),json_obj.values())

pd.concat([df,pd.DataFrame(t)],axis=1)

   index area father  father_age       son  son_age
0      1   CA   john        50.0   johnson     20.0
1      2   NY  smith        60.0  smithson     30.0
2      3   TX    NaN         NaN       NaN      NaN

edited May 14, 2020 at 12:32

answered May 14, 2020 at 4:53

Ch3steR

20.8k4 gold badges34 silver badges66 bronze badges

Comments

Joran Beasley · Accepted Answer · 2020-05-14 04:46:15Z

0

first you need to transform your json object

def my_reducer(left_dict,right_dict):
    d = {}
    d.update(left_dict)
    d.update(right_dict)
    return d

then use that to transform your json_obj

data = {key:reduce(my_reducer,val) for key,val in json_obj.items()}

now you can use that to make a dataframe

df2 = pandas.DataFrame(data).transpose()

now you can simply join the two dataframes

pandas.concat([df1,df2],join="left")

answered May 14, 2020 at 4:46

Joran Beasley

114k13 gold badges167 silver badges187 bronze badges

Collectives™ on Stack Overflow

Inserting list element into pandas dataframe

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related