new pandas column from a parsed json dictionary column

user_11231224 0
Viewed 40

I have the following structure

col1 col2 col3
1     2   {"a":{"b":2, "c":3}}

col3 contains parsed json as a dictionary, im trying to make a new column for c but not for the rest. applying pandas i managed to do

df["col3"].transform(lambda x: x.get("a").get("c") )

and it worked for c but now when writing the same code for b i get back 'NoneType' object has no attribute 'get'.

(also this is my first question here so very sorry if anything is unclear)

pandas dataframe python json
2 Answers

Use Series.str.get:

In [390]: df['c'] = df['col3'].str.get('a').str.get('c')

In [391]: df
Out[391]: 
   col1  col2                     col3  c
0     1     2  {'a': {'b': 2, 'c': 3}}  3
user_58208140

A better option might be to use json_normalize:

df['c'] = pd.json_normalize(df['col3'])['a.c']

output:

   col1  col2                     col3  c
0     1     2  {'a': {'b': 2, 'c': 3}}  3
user_163434640
Related