Python - How to extract values from different list of dictionaries in the rows of the dataframe

Question

I have data frame as like df:

    id        features                             
    100     [{'city': 'Rio'},  {'destination': '2'}]        
    110     [{'city': 'Sao Paulo'}]     
    135     [{'city': 'Recife'}, {'destination': '45'}]
    145     [{'city': 'Munich'}, {'destination': '67'}]
    167     [{'city': 'Berlin'}, {'latitude':'56'}, {'longitude':'30'}]

I have to extract column name and values from features column to separate columns as like:

    id      city          destination   latitude       longitude                            
    100     'Rio'          '2'            NaN            NaN  
    110     'Sao Paulo'    NaN            NaN            NaN  
    135     'Recife'       '45'           NaN            NaN  
    145     'Munich'       '67'           NaN            NaN  
    167     'Berlin'       NaN            '56'           '30'

I tried to do it with usage idea as like:

1st method to extract:

df = df.explode('features').reset_index(drop = True)
result = pd.concat([df.drop(columns='features'), 
           pd.json_normalize(df['features'])], axis=1)

result is only id column.

2nd method:

df = df.explode('features').reset_index(drop = True)
df2 = df.set_index('id')
df2 = df2['features'].astype('str')
df2 = df2.apply(lambda x: ast.literal_eval(x))
df2 = df2.apply(pd.Series)
result = df2.reset_index()

result is very closed what I need:

    id      city          destination        latitude      longitude                             
    100     'Rio'           NaN                NaN            NaN  
    100      NaN            '2'                NaN            NaN  
    110     'Sao Paulo'     NaN                NaN            NaN  
    135     'Recife'        NaN                NaN            NaN  
    135      NaN            '45'               NaN            NaN  
    145     'Munich'        NaN                NaN            NaN  
    145     'Munich'        '67'               NaN            NaN  
    167     'Berlin'        NaN                NaN            NaN 
    167      NaN            NaN                '56'           NaN  
    167      NaN            NaN                NaN          '30'

How is possible to achieve an expected result in view of:

    id      city          destination   latitude       longitude                            
    100     'Rio'          '2'            NaN            NaN  
    110     'Sao Paulo'    NaN            NaN            NaN  
    135     'Recife'       '45'           NaN            NaN  
    145     'Munich'       '67'           NaN            NaN  
    167     'Berlin'       NaN            '56'           '30'

Thanks

score 2 · Accepted Answer · answered Mar 02 '23 at 23:25

Try:

df = pd.concat(
    [
        df,
        df.pop("features").apply(
            lambda x: pd.Series({k: v for d in x for k, v in d.items()})
        ),
    ],
    axis=1,
)
print(df)

Prints:

    id       city destination latitude longitude
0  100        Rio           2      NaN       NaN
1  110  Sao Paulo         NaN      NaN       NaN
2  135     Recife          45      NaN       NaN
3  145     Munich          67      NaN       NaN
4  167     Berlin         NaN       56        30

Laurent B. · Answer 2 · 2023-03-03T01:14:02.740

import pandas as pd

df = pd.DataFrame({'id':[100, 110, 135, 145, 167],
                   'features':[[{'city': 'Rio'},  {'destination': '2'}],
                               [{'city': 'Sao Paulo'}] ,
                               [{'city': 'Recife'}, {'destination': '45'}],
                               [{'city': 'Munich'}, {'destination': '67'}],
                               [{'city': 'Berlin'}, {'latitude':'56'}, {'longitude':'30'}],
                               ],
                   })

r = df['features'].explode().to_json(orient='records')
r = pd.read_json(r, orient='records')

r['city'] = r['city'].fillna(method="ffill")
r = r.groupby('city').sum().reset_index(level=0)

print(r)

        city  destination  latitude  longitude
0     Berlin          0.0      56.0       30.0
1     Munich         67.0       0.0        0.0
2     Recife         45.0       0.0        0.0
3        Rio          2.0       0.0        0.0
4  Sao Paulo          0.0       0.0        0.0

score 1 · Answer 3 · answered Mar 03 '23 at 01:42

try this:

from collections import ChainMap


out = pd.DataFrame(
    data=[*df['features'].apply(lambda x: ChainMap(*x))],
    index=df.index
)
print(out)
>>>

	city	destination	latitude	longitude
100	Rio	2	nan	nan
110	Sao Paulo	nan	nan	nan
135	Recife	45	nan	nan
145	Munich	67	nan	nan
167	Berlin	nan	56	30

Python - How to extract values ​from different list of dictionaries in the rows of the dataframe

3 Answers3

Python - How to extract values from different list of dictionaries in the rows of the dataframe