Convert Key and Value in Dictionary Column as Different Columns Pandas

userpQFL 0
Viewed 28

I have a table like this

Name    Type Food   Variant and Price
A       Cake        {‘Choco’:100, ‘Cheese’:100, ‘Mix’: 125}
B       Drinks      {‘Cola’:25, ‘Milk’:35}
C       Side dish   {‘French Fries’:20}
D       Bread       {None:10}

I want to use the keys and values of dictionaries in the Variant and Price column as 2 different columns but I am still confused, here is the output that I want:

Name    Type Food   Variant          Price
A       Cake        Choco            100
A       Cake        Cheese           100
A       Cake        Mix              125
B       Drinks      Cola             25
B       Drinks      Milk             35
C       Side dish   French Fries     20
D       Bread       NaN              10

Can anyone help me to figure it out?

tabular dictionary dataframe pandas python
1 Answers

Create list of tuples and then use DataFrame.explode, last create 2 columns:

df['Variant and Price'] = df['Variant and Price'].apply(lambda x: list(x.items()))
df = df.explode('Variant and Price').reset_index(drop=True)
df[['Variant','Price']] = df.pop('Variant and Price').to_numpy().tolist()
print (df)
  Name  Type Food       Variant  Price
0    A       Cake         Choco    100
1    A       Cake        Cheese    100
2    A       Cake           Mix    125
3    B     Drinks          Cola     25
4    B     Drinks          Milk     35
5    C  Side dish  French Fries     20
6    D      Bread          None     10

Or create 2 columns and then use DataFrame.explode:

df['Variant'] = df['Variant and Price'].apply(lambda x: list(x.keys()))
df['Price'] = df.pop('Variant and Price').apply(lambda x: list(x.values()))
df = df.explode(['Variant', 'Price']).reset_index(drop=True)
jezrael0
Related