Finding null values in a json record in a dataframe

Question

Let say we have a dataframe with the following format:

col1
{'overall_mean': '93.07', 'overall_median': '91.05'}
{}
{'overall_mean': '91.02', 'overall_median': '95.03'}

The original data is in json format. I want to extract some elements from each row and here is what I tried:

 df['col1'].apply(lambda col: col['overall_mean'])

If we have that element in the dictionary, everything is fine but if we don't have, it complains and it make sense. Then I changed to this one:

 df['col1'].apply(lambda col: None if len(col['overall_mean']) == 1 else col['overall_mean'])

and the reason to choose this conditions len(col['overall_mean']) == 1 was because that is the only condition that returns True. However it still cannot find those null value.

Maybe this list comprehension will do: df['col1'].apply(lambda col: col['overall_mean'] if 'overall_mean' in col else None) — Ralubrusto
– Ralubrusto, Commented Feb 5, 2021 at 20:45
Would you please add your solution as an answer? get method works faster but your approach is also working. — MTT
– MTT, Commented Feb 5, 2021 at 21:04

akuiper · Accepted Answer · 2021-02-05 20:58:58Z

2

Why not just use dictionary's get method ? it will return None if the key is not in the dictionary:

df.col1.map(lambda x: x.get('overall_mean'))

#0    93.07
#1     None
#2    91.02
#Name: col1, dtype: object

Or: df.col1.apply(lambda x: x.get('overall_mean'))

answered Feb 5, 2021 at 20:58

akuiper

216k33 gold badges362 silver badges379 bronze badges

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

Comments

Ralubrusto · Accepted Answer · 2021-02-05 21:07:31Z

1

Nice solution from @Psidom. Just copying from the comments an alternative one using list comprehensions

df['col1'].apply(lambda col: col['overall_mean'] if 'overall_mean' in col else None)

answered Feb 5, 2021 at 21:07

Ralubrusto

1,5532 gold badges16 silver badges28 bronze badges

Collectives™ on Stack Overflow

Finding null values in a json record in a dataframe

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related