extract an element from a dictionary inside a list in a dataframe

user_2145138 0
Viewed 303

Let say we have a dataframe with the following format:

col1
[{'overall_prop': '0.812'}, {'overall_prop': '0.125'}, {'overall_prop': '0.062'}]
{}

The original data is in json format. I want to extract the value of 'overall_prop' from the first element off the list in each row and here is what I tried to extract the first element:

 df['col1'].str[0]

everything is fine and then the following to extract 'overall_prop':

df['col1'].str[0].map(lambda x: x.get('overall_prop'))

but complained:

{AttributeError}'float' object has no attribute 'get'

because of {} (python dict object) that became nan

Then I tried this:

df['col1'].where(df['col1'].notna(), lambda x: [{}]).str[0].map(lambda x: x.get('overall_prop'))

but this time:

{TypeError}argument of type 'NoneType' is not iterable

In summary, I'm looking for a solution to extract an element from a dictionary inside a list that can handle null values.

pandas dataframe dictionary python json
1 Answers

EDIT Ver 1: col1 is list of dicts and x[0] has overall_prop

You can do this. Use df.col1.apply(lambda x: x[0]['overall_prop']) to get the first element from the list and the overall_prop value from the dictionary in the first element.

The assumption here is that each row in col1 is a dictionary and has the key overall_prop

import pandas as pd
df = pd.DataFrame({'col1':[[{'overall_prop': '0.001'},
                            {'overall_prop': '0.002'},
                            {'overall_prop': '0.003'}],
                           [{'overall_prop': '0.004'},
                            {'overall_prop': '0.005'},
                            {'overall_prop': '0.006'}],
                           [{'overall_prop': '0.007'},
                            {'overall_prop': '0.008'},
                            {'overall_prop': '0.009'}],
                           [{'overall_prop': '0.010'},
                            {'overall_prop': '0.011'},
                            {'overall_prop': '0.012'}],
                           [{'overall_prop': '0.013'},
                            {'overall_prop': '0.014'},
                            {'overall_prop': '0.015'}]]})

print (df)

df['overall_prop'] = df['col1'].apply(lambda x: x[0]['overall_prop'])
print (df)

The output of this will be:

                                                col1 overall_prop
0  [{'overall_prop': '0.001'}, {'overall_prop': '...        0.001
1  [{'overall_prop': '0.004'}, {'overall_prop': '...        0.004
2  [{'overall_prop': '0.007'}, {'overall_prop': '...        0.007
3  [{'overall_prop': '0.010'}, {'overall_prop': '...        0.010
4  [{'overall_prop': '0.013'}, {'overall_prop': '...        0.013

EDIT Ver 2: col1 is list of dicts and empty dict in list

If you have rows that do not have overall_prop as a key, you can use this.

df = pd.DataFrame({'col1':[[{'overall_prop': '0.001'},
                            {'overall_prop': '0.002'},
                            {'overall_prop': '0.003'}],
                           [{}],
                           [{'incorrect_key': '0.004'},
                            {'overall_prop': '0.005'},
                            {'overall_prop': '0.006'}],
                           [{'overall_prop': '0.007'},
                            {'overall_prop': '0.008'},
                            {'overall_prop': '0.009'}],
                           [{'overall_prop': '0.010'},
                            {'overall_prop': '0.011'},
                            {'overall_prop': '0.012'}],
                           [{'overall_prop': '0.013'},
                            {'overall_prop': '0.014'},
                            {'overall_prop': '0.015'}]]})

import numpy as np

df['overall_prop'] = df['col1'].apply(lambda x: x[0]['overall_prop'] if 'overall_prop' in x[0] else np.NaN)

The output of this will be:

                                                col1 overall_prop
0  [{'overall_prop': '0.001'}, {'overall_prop': '...        0.001
1                                               [{}]          NaN
2  [{'incorrect_key': '0.004'}, {'overall_prop': ...          NaN
3  [{'overall_prop': '0.007'}, {'overall_prop': '...        0.007
4  [{'overall_prop': '0.010'}, {'overall_prop': '...        0.010
5  [{'overall_prop': '0.013'}, {'overall_prop': '...        0.013

EDIT Ver 3: col1 has varying types of data

df = pd.DataFrame({'col1':[[{'overall_prop': '0.001'},
                            {'overall_prop': '0.002'},
                            {'overall_prop': '0.003'}],
                           [{}],
                           {'bad':'0.999'},
                           {},
                           'just a bad string',
                           250,
                           35.25,
                           True,
                           False,
                           (10,20),
                           [{'incorrect_key': '0.004'},
                            {'overall_prop': '0.005'},
                            {'overall_prop': '0.006'}],
                           [{'overall_prop': '0.007'},
                            {'overall_prop': '0.008'},
                            {'overall_prop': '0.009'}],
                           [{'overall_prop': '0.010'},
                            {'overall_prop': '0.011'},
                            {'overall_prop': '0.012'}],
                           [{'overall_prop': '0.013'},
                            {'overall_prop': '0.014'},
                            {'overall_prop': '0.015'}]]})

def prop_check(x):
    if isinstance(x,list) and isinstance(x[0],dict) and 'overall_prop' in x[0]:
        return x[0]['overall_prop']
    else: return np.NaN

df['overall_prop'] = df['col1'].apply(lambda x: prop_check(x))
print (df)

The output of this will be:

                                                 col1 overall_prop
0   [{'overall_prop': '0.001'}, {'overall_prop': '...        0.001
1                                                [{}]          NaN
2                                    {'bad': '0.999'}          NaN
3                                                  {}          NaN
4                                   just a bad string          NaN
5                                                 250          NaN
6                                               35.25          NaN
7                                                True          NaN
8                                               False          NaN
9                                            (10, 20)          NaN
10  [{'incorrect_key': '0.004'}, {'overall_prop': ...          NaN
11  [{'overall_prop': '0.007'}, {'overall_prop': '...        0.007
12  [{'overall_prop': '0.010'}, {'overall_prop': '...        0.010
13  [{'overall_prop': '0.013'}, {'overall_prop': '...        0.013
user_138739800
Related