2

I have two dataframes shown as below. How can I replace Bank1 data by subtracting 10 by 3, and 55 by 2?

import pandas as pd

data = [['Bank1', 10, 55], ['Bank2', 15,65], ['Bank3', 14,54]]
df1 = pd.DataFrame(data, columns = ['BankName', 'Value1','Value2'])

enter image description here

df2 = pd.DataFrame([[3, 2]], columns = ['Value1','Value2'])

enter image description here

Desired Output(Only replace values in Bank1):

BankName Value1 Value2
Bank1 7 53
Bank2 15 65
Bank3 14 54
sushanth
  • 8,275
  • 3
  • 17
  • 28
Jiamei
  • 405
  • 3
  • 14

2 Answers2

3

try, using sub + combine_first

df1.sub(df2).combine_first(df1)

  BankName  Value1  Value2
0    Bank1     7.0    53.0
1    Bank2    15.0    65.0
2    Bank3    14.0    54.0
sushanth
  • 8,275
  • 3
  • 17
  • 28
  • 1
    Thanks, just out of curiosity. What if I want to subtract and replace the second row? This combine_first seems to be only applicable to the first row – Jiamei Oct 14 '21 at 03:33
  • @Jamie I thought about the same problem but your df2 does not have any indicator of the bank. So I wonder how you could tell which row to delete from. – EBDS Oct 14 '21 at 03:53
  • 2
    @sushanth I've learnt sub and combinefirst... had not used these before. Thanks! – EBDS Oct 14 '21 at 03:54
  • @Sushanth. Yes, this is actually a real issue I encountered today. df2 doesn't have any indicator of the bank name, but I know which row in df1 I want to subtract by df2, so no indicator is needed – Jiamei Oct 14 '21 at 04:04
0

First solution is create index in df22 by Bankname for align by df1 for correct row subracting:

df.set_index('BankName').sub(df2.set_index([['Bank1']]), fill_value=0)

df.set_index('BankName').sub(df2.set_index([['Bank2']]), fill_value=0)

You need create new column to df2 with BankName, convert BankName to index in both DataFrames, so possible subtract by this row:

df22 = df2.assign(BankName = 'Bank1').set_index('BankName')
df = df1.set_index('BankName').sub(df22, fill_value=0).reset_index()
print (df)
  BankName  Value1  Value2
0    Bank1     7.0    53.0
1    Bank2    15.0    65.0
2    Bank3    14.0    54.0

Subtract by Bank2:

df22 = df2.assign(BankName = 'Bank2').set_index('BankName')
df = df1.set_index('BankName').sub(df22, fill_value=0).reset_index()
print (df)

  BankName  Value1  Value2
0    Bank1    10.0    55.0
1    Bank2    12.0    63.0
2    Bank3    14.0    54.0

Another solution with filter by BankName:

m = df1['BankName']=='Bank1'
df1.loc[m, df2.columns] = df1.loc[m, df2.columns].sub(df2.iloc[0])
print (df1)
  BankName  Value1  Value2
0    Bank1       7      53
1    Bank2      15      65
2    Bank3      14      54

m = df1['BankName']=='Bank2'
df1.loc[m, df2.columns] = df1.loc[m, df2.columns].sub(df2.iloc[0])
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • I think your previous method works for me, I don't need to create another column. df.set_index('BankName').sub(df2.set_index([['Bank1']]), fill_value=0) – Jiamei Oct 14 '21 at 04:00