Pandas df append new column based on dict

user_13809871 0
Viewed 57

I was trying to add a new column in my dataframe, by using a dictionary:

my dataframe looks like this:

df = pd.DataFrame({
        'Time': [1,2,3,4,5,6,7,8,9,10],
        'Code': ['A', 'C', 'X', 'Y', 'A', 'B', 'X', 'A', 'Z', 'L'],
        'Value': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
        })

and my dic looks like this:

dic = {(1,'A',1):"P",(2,'C',2):"O",(3,'X',3):"P",(4,'Y',4):"O",(5,'A',5):"P",(6,'B',6):"O",(7,'X',7):"P",(8,'A',8):"P",(9,'Z',9):"O",(10,'L',10):"O"}

I did some research on how to do this and come up with using map function:

df['final'] = dic[df['Time',"Code","Value"].map(dic)

But it does work - unhashable type: "Series" ..

Is there any way to solve this? Thanks guys

pandas dataframe numpy dictionary python
2 Answers

Use DataFrame.join with convert dic to Series:

df = df.join(pd.Series(dic).rename('final'), on=['Time','Code','Value'])
print (df)
   Time Code  Value final
0     1    A      1     P
1     2    C      2     O
2     3    X      3     P
3     4    Y      4     O
4     5    A      5     P
5     6    B      6     O
6     7    X      7     P
7     8    A      8     P
8     9    Z      9     O
9    10    L     10     O
user_29010020

Code snippet should work for your use case.

import pandas as pd

df = pd.DataFrame({
        'Time': [1,2,3,4,5,6,7,8,9,10],
        'Code': ['A', 'C', 'X', 'Y', 'A', 'B', 'X', 'A', 'Z', 'L'],
        'Value': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
        })

dic = {(1,'A',1):"P",(2,'C',2):"O",(3,'X',3):"P",(4,'Y',4):"O",(5,'A',5):"P",(6,'B',6):"O",(7,'X',7):"P",(8,'A',8):"P",(9,'Z',9):"O",(10,'L',10):"O"}

df['final'] = df.apply(lambda x: dic.get((x['Time'],x['Code'],x['Value']),''),axis=1)

df

Output:

   Time Code  Value final
0     1    A      1     P
1     2    C      2     O
2     3    X      3     P
3     4    Y      4     O
4     5    A      5     P
5     6    B      6     O
6     7    X      7     P
7     8    A      8     P
8     9    Z      9     O
9    10    L     10     O
user_136595760
Related