Check if dataframe column contains an integer from another dataframe column and add column based on-CodePudding

I have two dataframes, a date lookup and a dataframe with the data I'm interested in. I want to look up the row value from dataframe 2 and match with the row from dataframe 1, then add the "date" value for the from dataframe 2 to a new column in dataframe 1.

Dataframe 1

name  sp  val
ccs   2   4.5
ccs   4   5
ccs   5   3
ccs   7   8

lookup dataframe 2

date        sp
2013-10-01  1
2013-11-01  2
2013-12-01  3
2014-01-01  4
2014-02-01  5
2014-03-01  6
2014-04-01  7

final dataframe 1

name  sp  val   date
ccs   2   4.5   2013-11-01
ccs   4   5     2014-01-01
ccs   5   3     2014-02-01
ccs   7   8     2014-05-01

CodePudding user response：

I didn't see anything from df2 for sp = 7 so I'm moving forward under the assumption it was left out on accident. However, this should get you the results you requested

df_merge = pd.merge(df1, df2, how = 'inner', on = 'sp')

CodePudding user response：

You can use a mapping dict:

df1['date'] = df1['sp'].map(df2.set_index('sp')['date'])
print(df1)

# Output
  name  sp  val        date
0  ccs   2  4.5  2013-11-01
1  ccs   4  5.0  2014-01-01
2  ccs   5  3.0  2014-02-01
3  ccs   7  8.0  2014-04-01