Python – Based on the sum of date ranges in two separate columns

Based on the sum of date ranges in two separate columns… here is a solution to the problem.

Based on the sum of date ranges in two separate columns

I want to sum all values in one column based on a date range in two columns:

Start_Date  Value_to_sum  End_date
2017-12-13    2          2017-12-13
2017-12-13    3          2017-12-16 
2017-12-14    4          2017-12-15
2017-12-15    2          2017-12-15

Simple groupby doesn’t do this because it only adds values for a specific date.

We could do an embedded for loop, but it will run forever:

unique_date = carry. Start_Date.unique()
carry = pd. DataFrame({'Date':unique_date})
carry['total'] = 0
for n in tqdm(range(len(carry))):
    tr = data.loc[data['Start_Date'] >= carry['Date'][n]]
    for i in tr.index:
        if carry['Date'][n] <= tr['End_date'][i]:
                carry['total'][n] += tr['Value_to_sum'][i]

Something like that will work, but like I said it will take a long time.

The expected output is a unique date and total number per day.

It should be

2017-12-13 = 5, 2017-12-14 = 7, 2017-12-15 = 9.

How do I calculate sum based on date range?


First, group by [“Start_Date”, “End_date”] to save some operations.

from collections import Counter
c = Counter()
df_g = df.groupby(["Start_Date", "End_date"]).sum().reset_index()

def my_counter(row):
    s, v, e = row. Start_Date, row. Value_to_sum, row. End_date
    if s == e:
        c[pd. Timestamp(s, freq="D")] += row. Value_to_sum
         c.update({date: v for date in pd.date_range(s, e)})

df_g.apply(my_counter, axis=1) 
Counter({Timestamp('2017-12-15 00:00:00', freq='D'): 9,
     Timestamp('2017-12-14 00:00:00', freq='D'): 7,
     Timestamp('2017-12-13 00:00:00', freq='D'): 5,
     Timestamp('2017-12-16 00:00:00', freq='D'): 3})

Tools used:

Elements are counted from an iterable or added-in from another mapping (or counter). Like dict.update() but adds counts instead of replacing them. Also, the iterable is expected to be a sequence of elements, not a sequence of (key, value) pairs. — Cited from Python 3 Documentation


Related Problems and Solutions