I’m working on the following code :
df1 = pd.DataFrame()
df1['Date'] = ["29/07/2021", "29/07/2021", "29/07/2021", "29/07/2021", "30/07/2021", "30/07/2021", "30/07/2021", "30/07/2021", "31/07/2021", "31/07/2021", "01/08/2021", "01/08/2021", "02/08/2021"]
df1['Time'] = ["06:48:00", "06:52:00", "06:56:00", "06:59:00", "07:14:00", "07:24:00", "07:40:00", "07:45:00", "08:42:00", "08:45:00", "08:52:00", "08:55:00", "09:07:00"]
df1['Column1'] = [0.0001, 0.002, 0.004, 0.5, 0.005, 0.0006, 0.08, 0.07, 0.003, 0.02, 0.0002, 0.0045, 0.0034]
df2 = pd.DataFrame()
df2['Date'] = ["29/07/2021", "30/07/2021", "31/07/2021", "01/08/2021", "02/08/2021"]
df2['Column1'] = [0.0056, 0.0594, 0.959, 0.0034, 0.00065]
I want to subtract the df2[‘Column1’] values from the df1[‘Column1] values, for each day in a new dataframe.
For example, for the first date (which is 29/07/2021) we will have a dataframe like this :
Date New_Column
29/07/2021 0.0001-0.0056
29/07/2021 0.002-0.0056
29/07/2021 0.004-0.0056
29/07/2021 0.5-0.0056
And so on for each date we will subtract one value that is related to one date from df2 from different values that are related to one date in df1.
Thank you.
>Solution :
If there are no duplicate dates in df2, then we can create a mapping from dates to values and map it to Date column in df1. Then we can simply subtract the corresponding values:
df1['New Column'] = df1['Column1'] - df1['Date'].map(df2.set_index('Date')['Column1'])
Output:
Date Time Column1 New Column
0 29/07/2021 06:48:00 0.0001 -0.00550
1 29/07/2021 06:52:00 0.0020 -0.00360
2 29/07/2021 06:56:00 0.0040 -0.00160
3 29/07/2021 06:59:00 0.5000 0.49440
4 30/07/2021 07:14:00 0.0050 -0.05440
5 30/07/2021 07:24:00 0.0006 -0.05880
6 30/07/2021 07:40:00 0.0800 0.02060
7 30/07/2021 07:45:00 0.0700 0.01060
8 31/07/2021 08:42:00 0.0030 -0.95600
9 31/07/2021 08:45:00 0.0200 -0.93900
10 01/08/2021 08:52:00 0.0002 -0.00320
11 01/08/2021 08:55:00 0.0045 0.00110
12 02/08/2021 09:07:00 0.0034 0.00275