CODEWITHSUNDEEP
pythonpandasnumpy
yuchen huang
yuchen huang

Reputation: 257

How to get values from one dataframe based on row?

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?

Upvotes: 1

Views: 45

Answers (2)

Scott Boston
Scott Boston

Reputation: 153460

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

Upvotes: 1

BENY
BENY

Reputation: 323236

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

Upvotes: 4

Related Questions