Replace columns with the same value between two dataframes-CodePudding

I have two dataframes

df1

    Date    RPM
0   0   0
1   1   0
2   2   0
3   3   0
4   4   0
5   5   0
6   6   0
7   7   0

and df2

    Date    RPM
0   0   0
1   2   2
2   4   4
3   6   6

I want to replace the RPM in df1 with the RPM in df2 where they have the same Date

I tried with replace but it didn't work out

CodePudding user response：

Use Series.map by Series created from df2 and then replace misisng valeus by original column by Series.fillna:

df1['RPM'] = df1['Date'].map(df2.set_index('Date')['RPM']).fillna(df1['RPM'])

CodePudding user response：

You could merge() the two frames on the Date column to get the new RPM against the corresponding date row:

df = df1.merge(df2, on='Date', how='left', suffixes=[None, ' new'])

   Date  RPM  RPM new
0     1    0      NaN
1     2    0      2.0
2     3    0      NaN
3     4    0      4.0
4     5    0      NaN
5     6    0      6.0
6     7    0      NaN

You can then fill in the nans in RPM new using .fillna() to get the RPM column:

df['RPM'] = df['RPM new'].fillna(df['RPM'])

   Date  RPM  RPM new
0     1  0.0      NaN
1     2  2.0      2.0
2     3  0.0      NaN
3     4  4.0      4.0
4     5  0.0      NaN
5     6  6.0      6.0
6     7  0.0      NaN

Then drop the RPM new column:

df = df.drop('RPM new', axis=1)

   Date  RPM
0     1  0.0
1     2  2.0
2     3  0.0
3     4  4.0
4     5  0.0
5     6  6.0
6     7  0.0

Full code:

df = df1.merge(df2, on='Date', how='left', suffixes=[None, ' new'])
df['RPM'] = df['RPM new'].fillna(df['RPM'])
df = df.drop('RPM new', axis=1)