CODEWITHSUNDEEP
pythonpandas
BALAJI R
BALAJI R

Reputation: 129

How to get total based on date range

I have two data frame

Logic : Add Quantity from 02.01.2020 to next 3 days

Part No    Date       Quantity         
   X     01.01.2020      52      
   Y     02.01.2020      ?
   Z     03.01.2020      ?

Date            Quantity
01.01.2020         10
02.01.2020         20
03.01.2020         15
04.01.2020         17
05.01.2020         19

I am new to python pandas. Pls guide

Upvotes: 0

Views: 54

Answers (1)

Zaraki Kenpachi
Zaraki Kenpachi

Reputation: 5730

You need to slice data by 3 days for each date, then collect results.

import pandas as pd
from io import StringIO
from dateutil.relativedelta import relativedelta

data = StringIO("""
date Quantity
01.01.2020 10
02.01.2020 20
03.01.2020 15
04.01.2020 17
05.01.2020 19
""")

# load raw data & convert to datetime
df = pd.read_csv(data, sep=' ', engine='python')
df['date'] = pd.to_datetime(df['date'], format='%d.%m.%Y')

# slice data by 3 days forward
data = {}
for index, row in df.iterrows():
    start_date = row['date']+ relativedelta(days=1)
    end_date = row['date'] + relativedelta(days=4)
    sum = df.loc[(df['date'] >= start_date) & (df['date'] < end_date)]['Quantity'].agg('sum')
    data.update({row['date'] : [sum]})

# collect data into new df
df_final = pd.DataFrame.from_dict(data, orient='index', columns=['Quantity'])

Output:

            Quantity
2020-01-01        52
2020-01-02        51
2020-01-03        36
2020-01-04        19
2020-01-05         0

Upvotes: 1

Related Questions