I have two dataframes as below.
df1: AreaCode Amount 1 non 2 non 3 non 4 non 5 non 6 non 7 non 8 non df2: AreaCode Amount 3 8 4 11 5 17
I want to get the answer like below dataframe.
df2: AreaCode Amount 1 non 2 non 3 8 4 11 5 17 6 non 7 non 8 non
How can I realize my aim?
something like this ?
df1.set_index('AreaCode').replace({'non':np.nan}).combine_first(df2.set_index('AreaCode')).reset_index()
Out[58]:
AreaCode Amount
0 1 NaN
1 2 NaN
2 3 8.0
3 4 11.0
4 5 17.0
5 6 NaN
6 7 NaN
7 8 NaN
Or we using merge
df1.merge(df2,on='AreaCode',how='left').ffill(1).drop('Amount_x',1).rename(columns={'Amount_y':'Amount'})
Out[69]:
AreaCode Amount
0 1 non
1 2 non
2 3 8
3 4 11
4 5 17
5 6 non
6 7 non
7 8 non
You can do this:
df2 = df2.set_index('AreaCode')
df1['Amount'] = df1.AreaCode.map(df2['Amount']).fillna(df1.Amount)
df1
Output:
AreaCode Amount
0 1 non
1 2 non
2 3 8
3 4 11
4 5 17
5 6 non
6 7 non
7 8 non
