Add column to pandas dataframe from a reversed dictionary

user_2884859 0
Viewed 120

I have a dataframe (pandas) and a dictionary with keys and values as list. The values in lists are unique across all the keys. I want to add a new column to my dataframe based on values of the dictionary having keys in it. E.g. suppose I have a dataframe like this

import pandas as pd
df = {'a':1, 'b':2, 'c':2, 'd':4, 'e':7}
df = pd.DataFrame.from_dict(df, orient='index', columns = ['col2'])
df = df.reset_index().rename(columns={'index':'col1'})
df

    col1    col2
0   a   1
1   b   2
2   c   2
3   d   4
4   e   7

Now I also have dictionary like this

my_dict = {'x':['a', 'c'], 'y':['b'], 'z':['d', 'e']}

I want the output like this

    col1    col2    col3
0   a   1   x
1   b   2   y
2   c   2   x
3   d   4   z
4   e   7   z

Presently I am doing this by reversing the dictionary first, i.e. like this

my_dict_rev = {value:key for key in my_dict for value in my_dict[key]}
df['col3']= df['col1'].map(my_dict_rev)
df

But I am sure that there must be some direct method.

pandas dataframe dictionary python
1 Answers

I know this is an old question but here are two other ways to do the same job. First convert my_dict to a Series object, then explode it. Then reverse the mapping and use map:

tmp = pd.Series(my_dict).explode()
df['col3'] = df['col1'].map(pd.Series(tmp.index, tmp))

Another option (starts similar to above) but instead of map, merge:

df = df.merge(pd.Series(my_dict, name='col1').explode().rename_axis('col3').reset_index())

Output:

  col1  col2 col3
0    a     1    x
1    b     2    y
2    c     2    x
3    d     4    z
4    e     7    z
Admin1
Related