split a list of dictionaries into multiple columns

Question

I have a dataframe with 30000 rows and 5 columns. one of this column is a list of dictionaries and a few Nan's. I wanted to split this column into 3 fields (legroom to In-FLight Enternatinment) and wanted to extract ratings

Below is a sample for reference

d = {'col1': [[{'rating': 5, 'ratingLabel': 'Legroom'}, {'rating': 5, 'ratingLabel': 'Seat comfort'}, {'rating': 5, 'ratingLabel': 'In-flight Entertainment'}],'Nan']}
df = pd.DataFrame(data=d)
df

What about `df['col1'].explode().apply(pd.Series)['rating']`? — Willem Van Onsem, Jan 05 '20 at 16:39

S. M. · Answer 1 · 2020-01-05T18:18:25.263

Here is a possible solution using the DataFrame.apply() and pd.Series and a strategy from Splitting dictionary/list inside a Pandas Column into Separate Columns

import pandas as pd

d = {'col1': [[{'rating': 5, 'ratingLabel': 'Legroom'},
               {'rating': 5, 'ratingLabel': 'Seat comfort'},
               {'rating': 5, 'ratingLabel': 'In-flight Entertainment'}],
              [{'rating': 5, 'ratingLabel': 'Legroom'},
               {'rating': 5, 'ratingLabel': 'Seat comfort'},
               {'rating': 5, 'ratingLabel': 'In-flight Entertainment'}],
              'Nan']}
df = pd.DataFrame(data=d)
df

df_split = df['col1'].apply(pd.Series)
pd.concat([df,
           df_split[0].apply(pd.Series).rename(columns = {'rating':'legroom_rating',
                                                          'ratingLabel':'1'}),
           df_split[1].apply(pd.Series).rename(columns = {'rating':'seat_comfort_rating',
                                                         'ratingLabel':'2'}),
           df_split[2].apply(pd.Series).rename(columns = {'rating':'in_flight_entertainment_rating',
                                                         'ratingLabel':'3'})],
           axis = 1).drop(['col1','1','2','3',0],
                         axis = 1)

Producing the following DataFrame

score 0 · Answer 2 · answered Jan 05 '20 at 18:16

IIUC This should do the trick:

df=df["col1"].apply(lambda x: pd.Series({el["ratingLabel"]: el["rating"] for el in x if isinstance(x, list)}))

Output:

   Legroom  Seat comfort  In-flight Entertainment
0      5.0           5.0                      5.0
1      NaN           NaN                      NaN

split a list of dictionaries into multiple columns

2 Answers2