Subtract 2 dataframes with different number of rows and taking in consideration the Date column-CodePudding

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.

CodePudding user response：

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