pandas match date in one df with timeframe in another, then groupby-sum

Question

I have two dataframes, test1 and test2. For each ID value in test2, I want to check the date in test2 and compare it to the date ranges for that same ID value in test1. If any of the date's in test2 are within a date range in test1, sum the amount column and assign that sum as an additional column in test1.

Output:

So the new test1 df will have a column amount_sum which is the sum of all amounts in test2 where the date is within the date range of test1 - for that ID

import random
import string

test1 = pd.DataFrame({
    'ID':[''.join(random.choice(string.ascii_letters[0:4]) for _ in range(3)) for n in range(100)],
    'date1':[pd.to_datetime(random.choice(['01-01-2018','05-01-2018','06-01-2018','08-01-2018','09-01-2018'])) + pd.DateOffset(int(np.random.randint(0, 100, 1))) for n in range(100)],
    'date2':[pd.to_datetime(random.choice(['01-01-2018','05-01-2018','06-01-2018','08-01-2018','09-01-2018'])) + pd.DateOffset(int(np.random.randint(101, 200, 1))) for n in range(100)]
})

test2 = pd.DataFrame({
    'ID':[''.join(random.choice(string.ascii_letters[0:4]) for _ in range(3)) for n in range(100)],
    'amount':[random.choice([1,2,3,5,10]) for n in range(100)],
    'date':[pd.to_datetime(random.choice(['01-01-2018','05-01-2018','06-01-2018','08-01-2018','09-01-2018'])) + pd.DateOffset(int(np.random.randint(0, 100, 1))) for n in range(100)]
})

jezrael · Accepted Answer · 2018-11-13T08:46:23.873

Use:

#outer join both df by ID columns
df = test1.merge(test2, on='ID', how='outer')
#filter by range
df = df[(df.date > df.date1) & (df.date < df.date2)]
#thank you @Abhi for alternative
#df = df[df.date.between(df.date1, df.date2, inclusive=False)]
#aggregate sum
s = df.groupby(['ID','date1','date2'])['amount'].sum()
#add new column to test1
test = test1.join(s, on=['ID','date1','date2'])

Sample:

#https://stackoverflow.com/q/21494489
np.random.seed(123)

#https://stackoverflow.com/a/50559321/2901002
def gen(start, end, n):
    start_u = start.value//10**9
    end_u = end.value//10**9

    return pd.to_datetime(np.random.randint(start_u, end_u, n), unit='s')

n = 10
test1 = pd.DataFrame({
    'ID':np.random.choice(list('abc'), n),
    'date1': gen(pd.to_datetime('2010-01-01'),pd.to_datetime('2010-03-01'), n).floor('d'),
    'date2':gen(pd.to_datetime('2010-03-01'),pd.to_datetime('2010-06-01'), n).floor('d')
})

m = 5
test2 = pd.DataFrame({
    'ID': np.random.choice(list('abc'), m),
    'amount':np.random.randint(10, size=m),
    'date':gen(pd.to_datetime('2010-01-01'), pd.to_datetime('2010-06-01'), m).floor('d')
})

print (test1)
  ID      date1      date2
0  c 2010-01-15 2010-05-22
1  b 2010-02-08 2010-04-16
2  c 2010-01-24 2010-04-12
3  c 2010-02-01 2010-04-09
4  a 2010-01-19 2010-05-20
5  c 2010-01-27 2010-05-24
6  c 2010-02-23 2010-03-15
7  b 2010-01-31 2010-05-09
8  c 2010-02-23 2010-03-29
9  b 2010-01-08 2010-03-07

print (test2)
  ID  amount       date
0  a       4 2010-05-15
1  b       6 2010-03-26
2  a       1 2010-01-07
3  b       5 2010-02-07
4  a       6 2010-04-13

#outer join both df by ID columns
df = test1.merge(test2, on='ID', how='outer')
#filter by range
df = df[(df.date > df.date1) & (df.date < df.date2)]
print (df)
   ID      date1      date2  amount       date
6   b 2010-02-08 2010-04-16     6.0 2010-03-26
8   b 2010-01-31 2010-05-09     6.0 2010-03-26
9   b 2010-01-31 2010-05-09     5.0 2010-02-07
11  b 2010-01-08 2010-03-07     5.0 2010-02-07
12  a 2010-01-19 2010-05-20     4.0 2010-05-15
14  a 2010-01-19 2010-05-20     6.0 2010-04-13

#thank you @Abhi for alternative
#df = df[df.date.between(df.date1, df.date2, inclusive=False)]
#aggregate sum
s = df.groupby(['ID','date1','date2'])['amount'].sum()
#add new column to test1
test = test1.join(s, on=['ID','date1','date2'])
print (test)
  ID      date1      date2  amount
0  c 2010-01-15 2010-05-22     NaN
1  b 2010-02-08 2010-04-16     6.0
2  c 2010-01-24 2010-04-12     NaN
3  c 2010-02-01 2010-04-09     NaN
4  a 2010-01-19 2010-05-20    10.0
5  c 2010-01-27 2010-05-24     NaN
6  c 2010-02-23 2010-03-15     NaN
7  b 2010-01-31 2010-05-09    11.0
8  c 2010-02-23 2010-03-29     NaN
9  b 2010-01-08 2010-03-07     5.0

What if an `ID` shows up more than once in `test2`? Will the amount be summed when you outer join? — conv3d, Nov 13 '18 at 07:17

pandas match date in one df with timeframe in another, then groupby-sum

1 Answers1