I have two dataframes (simplified examples below). One contains a series of dates and values (df1), the second contains a date range (df2). I would like to identify/select/mask the date range from df2 in df1, sum the associated df1 values and add them to a new column in df2.
I'm a novice and all the techniques I have tried have been unsuccessful--a combination of wrong method, combining incompatible methods, syntax errors and so on. I have searched the Q&As here, but none have quite addressed this issue.
import pandas as pd
#********** df1: dates and values ***********
rng = pd.date_range('2012-02-24', periods=12, freq='D')
df1 = pd.DataFrame({ 'STATCON': ['C00028', 'C00489', 'C00038', 'C00589', 'C10028', 'C00499', 'C00238', 'C00729',
'C10044', 'C00299', 'C00288', 'C00771'],
'Date': rng,
'Val': [0.96, 0.57, 0.39, 0.17, 0.93, 0.86, 0.54, 0.58, 0.43, 0.19, 0.40, 0.32]
})
#********** df2: date range ***********
df2 = pd.DataFrame({
'BCON': ['B002', 'B004', 'B005'],
'Start': ['2012-02-25', '2012-02-28', '2012-03-01'],
'End': ['2012-02-29', '2012-03-04', '2012-03-06']
})
df2[['Start','End']] = df2[['Start','End']].apply(pd.to_datetime)
#********** Desired Output: df2 -- date range with summed values ***********
df3 = pd.DataFrame({
'BCON': ['B002', 'B004', 'B005'],
'Start': ['2012-02-25', '2012-02-28', '2012-03-01'],
'End': ['2012-02-29', '2012-03-04', '2012-03-06'],
'Sum_Val': [2.92, 3.53, 2.46]
})
CodePudding user response:
You can solve this with the Dataframe.apply
function as follow:
def to_value(row):
return df1[(row['Start'] <= df1['Date']) & (df1['Date'] <= row['End'])]['Val'].sum()
df3 = df2.copy()
df3['Sum_Val'] = df3.apply(to_value, axis=1)
The to_value
function is called on every row of the df3 dataframe.
See here for a live implementation of the solution: https://1000words-hq.com/n/TcYN1Fz6Izp
CodePudding user response:
One option is with conditional_join from pyjanitor - it tries to avoid searching every row (which can be memory consuming, depending on the data size):
# pip install pyjanitor
import pandas as pd
import numpy as np
df2 = df2.astype({'Start':np.datetime64, 'End':np.datetime64})
(df1
.conditional_join(
df2,
('Date', 'Start', '>='),
('Date', 'End', '<='))
.loc[:, ['BCON', 'Start', 'End', 'Val']]
.groupby(['BCON', 'Start', 'End'], as_index = False)
.agg(sum_val = ('Val', 'sum'))
)
BCON Start End sum_val
0 B002 2012-02-25 2012-02-29 2.92
1 B004 2012-02-28 2012-03-04 3.53
2 B005 2012-03-01 2012-03-06 2.46