0

This is a simple question but most of the solution I found here were based on one column match (mainly only ID).

Df1
'Name'    'Dept'   'Amount'    'Leave'
ABC         1         10          0
BCD         1          5          0



Df2
'Alias_Name', 'Dept', 'Amount', 'Leave', 'Address', 'Join_Date'
ABC             1        100       5      qwerty       date1
PQR             2         0        2      asdfg        date2

I want to replaces row values in df1 when both the Name and Dept are matched. I tried merge(left_on=['Name', 'Dept'], right_on=['Alias_Name', 'Dept'], how='left') but it gives me double number of columns with _x and _y suffix. I just need to replaces the Dept, Amount, Leave in df1 if the Name and Dept are matched with any row in df2.

Desired Output:

Name     Dept     Amount     Leave
ABC        1       100         5
BCD        1        5          0

3 Answers3

0

You can create a temp column in both data frames which will be sum of both "Name" and "Dept". That column can be used as primary key to match

sam
  • 2,263
  • 22
  • 34
0
new_df = df1[['Name', 'Dept']].merge(df2[['Alias_Name', 'Dept', 'Amount', 'Leave']].rename(columns={'Alias_Name': 'Name'}), how='left').fillna(df1[['Amount', 'Leave']])

Result:

  Name  Dept  Amount  Leave
0  ABC     1   100.0    5.0
1  BCD     1     5.0    0.0

You can use new_df[['Amount', 'Leave']] = new_df[['Amount', 'Leave']].astype(int) to re-cast the dtype if that's important.

r.ook
  • 13,466
  • 2
  • 22
  • 39
0

Try:

# select rows that should be replace
replace_df = df1[['Name', 'Dept']].merge(df2, left_on=['Name', 'Dept'], right_on=['Alias_Name', 'Dept'], how='inner')
# replace rows in df1
df1.iloc[replace_df.index] = replace_df

Result:

        Name    Dept    Amount  Leave
    0   ABC      1       100    5
    1   BCD      1         5    0
Rostyslav Shevchenko
  • 201
  • 2
  • 6
  • What is the difference in ```replace_df = df1[['Name', 'Dept']].merge(df2, left_on=['Name', 'Dept'], right_on=['Alias_Name', 'Dept'], how='inner')``` and ```replace_df = pd.merge(df1, df2, left_on=['Name', 'Dept'], right_on=['Alias_Name', 'Dept'], how='inner')``` what is the purpose of ```df1[['Name', 'Dept']].merge``` ?? –  May 12 '20 at 23:36