Question: I have two tables: one ('sales') with sales data (types of goods, dates of sale, and quantity) and another ('ref') with types of goods and a reference date. I want to add a column to the second table that would show the total quantity of the corresponding item sold within seven days (give or take) from the reference date. Here's the sample data: sales = pd.DataFrame({'Fruit': {0: 'apples', 1: 'oranges', 2: 'pears', 3: 'apples', 4: 'apples', 5: 'bananas', 6: 'oranges', 7: 'pears', 8: 'pears', 9: 'oranges', 10: 'bananas', 11: 'apples', 12: 'pears', 13: 'pears', 14: 'apples', 15: 'pears', 16: 'oranges', 17: 'oranges', 18: 'pears'}, 'Date': {0: '2023-07-07', 1: '2023-02-05', 2: '2023-08-16', 3: '2023-07-26', 4: '2023-07-14', 5: '2024-02-01', 6: '2023-09-19', 7: '2023-04-08', 8: '2023-06-08', 9: '2023-05-15', 10: '2023-10-20', 11: '2023-07-25', 12: '2023-07-31', 13: '2023-10-08', 14: '2023-06-28', 15: '2023-08-15', 16: '2023-05-14', 17: '2023-07-28', 18: '2023-07-29'}, 'Quantity': {0: 18, 1: 10, 2: 10, 3: 20, 4: 16, 5: 14, 6: 18, 7: 18, 8: 14, 9: 19, 10: 16, 11: 16, 12: 17, 13: 10, 14: 16, 15: 15, 16: 18, 17: 20, 18: 19}}) sales['Date'] = pd.to_datetime(sales['Date']) ref = pd.DataFrame({'Fruit': {0: 'apples', 1: 'bananas', 2: 'oranges', 3: 'apples', 4: 'pears', 5: 'oranges', 6: 'bananas', 7: 'oranges', 8: 'oranges'}, 'Date': {0: '2023-07-25', 1: '2023-12-27', 2: '2023-07-13', 3: '2023-06-27', 4: '2023-07-08', 5: '2023-09-17', 6: '2023-10-25', 7: '2023-10-05', 8: '2023-04-14'}}) ref['Date'] = pd.to_datetime(ref['Date'])  For example, the first row of ref should show 36 (20 apples from 2023-07-36 and 16 apples from 2023-07-25). If I were using Excel, I'd use this formula: =SUMIFS(sales.Quantity, sales.Fruit, ref.Fruit, sales.Date, ">="&ref.Date-7, sales.Date, "<="&ref.Date+7). In Python, I can get the results I needed on a single-item basis, like this: sales[(sales['Fruit']=='apples')& (sales['Date']>=pd.to_datetime('2023-07-25')-pd.to_timedelta(7, unit='d'))& (sales['Date']<=pd.to_datetime('2023-07-25')+pd.to_timedelta(7, unit='d'))]['Quantity'].sum()  and using iloc: sales[(sales['Fruit']==ref.iloc[0,0])& (sales['Date']>=ref.iloc[0,1]-pd.to_timedelta(7, unit='d'))& (sales['Date']<=ref.iloc[0,1]+pd.to_timedelta(7, unit='d'))]['Quantity'].sum()  but when I try to add a new column to ref with this calculation, I get 'ValueError: Can only compare identically-labeled Series objects'. ref['Total'] = sales[(sales['Fruit']==ref.iloc[ref.index,0])& (sales['Date']>=ref.iloc[ref.index,1]-pd.to_timedelta(7, unit='d'))& (sales['Date']<=ref.iloc[ref.index,1]+pd.to_timedelta(7, unit='d'))]['Quantity'].sum()  I'm guessing I'm wrong to use ref.index in place of the 0 in iloc to get the number I need - what should I use instead?

Answer: You can use conditional_join from pyjanitor: ref['start'] = ref['Date'] - pd.to_timedelta(7, unit='d') ref['end'] = ref['Date'] + pd.to_timedelta(7, unit='d') out = (sales .conditional_join( ref, ('Date', 'end', '<='), ('Date', 'start', '>='), ('Fruit', 'Fruit', '=='), how='right', df_columns='Quantity') .groupby(['Fruit', 'Date'], as_index=False)['Quantity'] .sum()) print (out) Fruit Date Quantity 0 apples 2023-06-27 16.0 1 apples 2023-07-25 36.0 2 bananas 2023-10-25 16.0 3 bananas 2023-12-27 0.0 4 oranges 2023-04-14 0.0 5 oranges 2023-07-13 0.0 6 oranges 2023-09-17 18.0 7 oranges 2023-10-05 0.0 8 pears 2023-07-08 0.0