I have two pandas dataframes:
Smaller:
Larger:
I want to match on the Ticker and Year and then replace the numbers in the First and Last columns with those from the smaller dataframe.
I've tried using pd.merge but I succeeded only in adding rows or columns not replacing the specific cells. Can someone please post some code that would achieve this?
You can use merge with left join and suffixes and then combine_first with rename for remove _:
df1 = pd.DataFrame({'Ticker':list('abcdef'),
'Year':[2013,2014,2013,2014,2013,2014],
'C':[7,8,9,4,2,3],
'Last':[1,3,5,7,1,0],
'First':[5,3,6,9,2,4],
'F':list('aaabbb')})
print (df1)
C F First Last Ticker Year
0 7 a 5 1 a 2013
1 8 a 3 3 b 2014
2 9 a 6 5 c 2013
3 4 b 9 7 d 2014
4 2 b 2 1 e 2013
5 3 b 4 0 f 2014
df2 = pd.DataFrame({'First':[4,5,4,5],
'Last':[7,8,9,4],
'Year':[2013,2014,2014,2015],
'Ticker':list('aabc')})
print (df2)
First Last Ticker Year
0 4 7 a 2013
1 5 8 a 2014
2 4 9 b 2014
3 5 4 c 2015
df = df1.merge(df2, suffixes=('_',''), on=['Ticker','Year'], how='left')
df1[['First','Last']] = (df[['First','Last']].combine_first(df[['First_','Last_']]
.rename(columns=lambda x: x.strip('_'))))
print (df1)
C F First Last Ticker Year
0 7 a 4.0 7.0 a 2013
1 8 a 4.0 9.0 b 2014
2 9 a 6.0 5.0 c 2013
3 4 b 9.0 7.0 d 2014
4 2 b 2.0 1.0 e 2013
5 3 b 4.0 0.0 f 2014
