Pandas : How to split list inside a dictionary into new columns

September 22, 2022

I have a dataframe

A      B
1     [{'X': 'Room1', 'Y': ['A1', 'A2']}
2     [{'X': 'Room2', 'Y': ['B1', 'B2','B3']}

I am trying to split the list inside B col , into new columns

Expected Dataframe

A    opt_X     opt_Y_1   opt_Y_2    opt_Y_2
1    Room1     A1        A2         0
1    Room2     B1        B2         B3

I got it till here

L = [json_normalize(x) for x in df.pop('B')]
df = df.join(pd.concat(L, ignore_index=True, sort=False).add_prefix('opt_'))

To get this dataframe

A   opt_X    opt_Y
1   Room1    ['A1','A2']
2   Room2    ['B1', 'B2','B3']

>Solution :

Use concat with all column without Y and then convert Y column to DataFrame with rename for starting columns names by 1:

L = [pd.json_normalize(x) for x in df.pop('B')]
df1 = pd.concat(L, ignore_index=True, sort=False)
df = pd.concat([df, 
                df1.drop('Y', axis=1).add_prefix('opt_'), 
                pd.DataFrame(df1['Y'].tolist())
                  .rename(columns=lambda x: x+1)
                  .fillna(0).add_prefix('opt_Y_')], axis=1)
print (df)
   A  opt_X opt_Y_1 opt_Y_2 opt_Y_3
0  1  Room1      A1      A2       0
1  2  Room2      B1      B2      B3