How to replace dataframe column values with dictionary list keys?

Question

Supose I have a dictionary:

dic = {'1' : ['A', 'B', 'C'], '2' : ['D', 'E'] , '3' : ['F']}

and a data frame

df = pd.DataFrame()

df["ID"] = pd.Series(["A","B","C","D","E","F"])

df["Colour"] = pd.Series(["Blue","Purple","Green","Red","Pink","Black"])

How would I replace values in column df["ID"] with dictionary keys so that I have 1,2,3 in df["ID"] instead of A,B,C, D, E, F?

Thanks

score 1 · Accepted Answer · answered Sep 15 '22 at 18:30

Invert the dictionary and map:

d = {v:k for k,l in dic.items() for v in l}
# {'A': '1', 'B': '1', 'C': '1', 'D': '2', 'E': '2', 'F': '3'}

df['ID'] = df['ID'].map(d)

NB. If you have duplicated values in the lists, the last one seen will take precedence.

Output:

  ID  Colour
0  1    Blue
1  1  Purple
2  1   Green
3  2     Red
4  2    Pink
5  3   Black

score 0 · Answer 2 · answered Sep 15 '22 at 18:34

You can map you df with a dictionary, but you need to revert the key / values of the dictionary you have there :

new_dic = {}
for k,v in dict.items():
    for x in v:
        new_dic.setdefault(x,[]).append(k)

Then you can simply map you column :

df["ID"].map(new_dic)

You can refer to this question :

And this one :

2 Answers2