How do I separate the dictionary list into separate columns?

user_14978092 0
Viewed 105

I have a list of dictionary in my dataframe column of vary length:

categories

1) [  { "S" : "Vibes" },  { "S" : "Themed" },  { "S" : "Experiences" },  { "S" : "Girls Night" }]
2) [  { "S" : "Vibes" }]
3) [  { "S" : "Vibes" },  { "S" : "Drinks" }]
.
.
.

I want to make it into separate columns , if there is no dictionary on particular list it should make it as a null for that category as the output should look like :

categories 1          categories 2         categories 3  ......

{ "S" : "Vibes" }     { "S" : "Themed" }    { "S" : "Themed" }
{ "S" : "Vibes" }]      null                   null
.                         .                     .
.                         .                     .
pandas dataframe list dictionary python
1 Answers

You can use .explode() to expand the list of dict in column categories into separate rows, then create the categories names ('categories 1', 'categories 2', etc) by grouping on the original row index (row index before explode) using .groupby() and get the serial number by .cumcount() within the group. Finally, we use .pivot() to pivot the rows into columns.

df1 = df.explode('categories')
df1['Cat_Num'] = 'categories ' + df1.groupby(level=0).cumcount().add(1).astype(str)
df2 = df1.pivot(columns='Cat_Num', values='categories').rename_axis(columns=None)

Demo

data = {'categories': [
[{ "S" : "Vibes" },  { "S" : "Themed" },  { "S" : "Experiences" },  { "S" : "Girls Night" }],
[  { "S" : "Vibes" }],
[  { "S" : "Vibes" },  { "S" : "Drinks" }]
]}
df = pd.DataFrame(data)

df1 = df.explode('categories')
df1['Cat_Num'] = 'categories ' + df1.groupby(level=0).cumcount().add(1).astype(str)
df2 = df1.pivot(columns='Cat_Num', values='categories').rename_axis(columns=None)

print(df2)



     categories 1     categories 2          categories 3          categories 4
0  {'S': 'Vibes'}  {'S': 'Themed'}  {'S': 'Experiences'}  {'S': 'Girls Night'}
1  {'S': 'Vibes'}              NaN                   NaN                   NaN
2  {'S': 'Vibes'}  {'S': 'Drinks'}                   NaN                   NaN
user_150706970
Related