Extract value from JSON data of a pandas column with condition apply

Question

Need to extract value from a json string stored in pandas column and assign it to a column with a conditional apply to rows with null values only.

df = pd.DataFrame({'col1': [06010, np.nan, 06020, np.nan],
                   'json_col': [{'Id': '060',
                                 'Date': '20210908',
                                 'value': {'Id': '060',
                                           'Code': '06037'}
                                 },
                                 {'Id': '061',
                                 'Date': '20210908',
                                 'value': {'Id': '060',
                                           'Code': '06038'}
                                 },
                                 {'Id': '062',
                                 'Date': '20210908',
                                 'value': {'Id': '060',
                                           'Code': '06039'}
                                 },
                                 {'Id': '063',
                                 'Date': '20210908',
                                 'value': {'Id': '060',
                                           'Code': '06040'}
                                 }],
                })
                         

# Check for null condition and extract Code from json string

df['Col1'] = df[df['Col1'].isnull()].apply(lambda x : [x['json_col'][i]['value']['Code'] for i in x])

Expected result:

for performance reasons, it is better if you preprocess the json data into flat form before creating the dataframe. — sammywemmy
– sammywemmy, Commented Sep 12, 2021 at 4:48

akuiper · Accepted Answer · 2021-09-12 04:42:58Z

1

To extract field from a dictionary column, you can use .str accessor. For instance, to extract json_col -> value -> code you can use df.json_col.str['value'].str['Code']. And then use fillna to replace nan in col1:

df.col1.fillna(df.json_col.str['value'].str['Code'])

0    06010
1    06038
2    06020
3    06040
Name: col1, dtype: object

answered Sep 12, 2021 at 4:42

akuiper

216k33 gold badges362 silver badges379 bronze badges

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

2 Comments

kms Over a year ago

For some reason, this isn't working for me. Doesn't throw any errors, but it isn't assign values either.

akuiper Over a year ago

You can just assign it to a column with: df['col1'] = df.col1.fillna(df.json_col.str['value'].str['Code'])

U13-Forward · Accepted Answer · 2021-09-12 04:44:26Z

0

Try with this:

>>> df['col1'].fillna(df['json_col'].map(lambda x: x['value']['Code']))
0    06010
1    06038
2    06020
3    06040
Name: col1, dtype: object
>>>

answered Sep 12, 2021 at 4:44

U13-Forward

71.8k15 gold badges100 silver badges125 bronze badges

Collectives™ on Stack Overflow

Extract value from JSON data of a pandas column with condition apply

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related