CODEWITHSUNDEEP
pythonpython-3.xpandasdataframe
jayko03
jayko03

Reputation: 2481

Inserting list element into pandas dataframe

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?

Upvotes: 0

Views: 52

Answers (4)

Ch3steR
Ch3steR

Reputation: 20669

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

Upvotes: 1

sammywemmy
sammywemmy

Reputation: 28644

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

Upvotes: 1

Joran Beasley
Joran Beasley

Reputation: 113978

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")

Upvotes: 0

BENY
BENY

Reputation: 323226

What I will do 

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

Upvotes: 4

Related Questions