Printable Bao Printable Bao - 2 months ago 15
Python Question

Input values to dataframe through lookup in pandas

I have a simple dataframe:

s =pd.DataFrame({'Dates':['date1','date1','date1','date2','date2','date2','date3','date3','date3'],'Contracts':['A','B','C','A','B','C','A','B','C']})


and a dictionary:

dict = {'date1':['A','B'],'date2':['B']}


What's the best way of creating the DataFrame as follows?

['date1','date1','date1','date2','date2','date2','date3','date3','date3'],'Contracts':['A','B','C','A','B','C','A','B','C'], 'Check':['Y','Y','N','N','Y','N','N','N','N']})


What I want to accomplish is to lookup values in dict and label the elements in dataframe s.

Thanks!

Answer Source

You can use df.apply with a lambda:

lookup = {'date1':['A','B'],'date2':['B']}

df.apply(lambda x: 'Y' if x['Dates'] in lookup and x['Contracts'] in lookup[x['Dates']] else 'N', axis=1)

You can make the assignment to a new column:

In [377]: df['Check'] = df.apply(lambda x: 'Y' if x['Dates'] in lookup and x['Contracts'] in lookup[x['Dates']] else 'N', 
     ...: axis=1)
In [378]: df
Out[378]: 
  Contracts  Dates Check
0         A  date1     Y
1         B  date1     Y
2         C  date1     N
3         A  date2     N
4         B  date2     Y
5         C  date2     N
6         A  date3     N
7         B  date3     N
8         C  date3     N