How to aggregate irregularly sampled data for Time Series Analysis

Question

I am trying to forecast daily profit using time series analysis, but daily profit is not only recorded unevenly, but some of the data is missing.

Raw Data:

Date	Revenue
2020/1/19	10$
2020/1/20	7$
2020/1/25	14$
2020/1/29	18$
2020/2/1	12$
2020/2/2	17$
2020/2/9	28$

The above table is an example of what kind of data I have. Profit is not recorded daily, so date between 2020/1/20 and 2020/1/24 does not exist. Not only that, say the profit recorded during the period between 2020/2/3 and 2020/3/8 went missing in the database. I would like to recover this missing data and use time series analysis to predict the profit after 2020/2/9 ~.

My approach was to first aggregate the profit every 6 days since I have to recover the profit between 2020/2/3 and 2020/3/8. So my cleaned data will look something like this

Date	Revenue
2020/1/16 ~ 2020/1/21	17$
2020/1/22 ~ 2020/1/27	14$
2020/1/28 ~ 2020/2/2	47$
2020/2/3 ~ 2020/2/8	? (to predict)

After applying this to a time series model, I would like to further predict the profit after 2020/2/9 ~. This is my general idea, but as a beginner at Python, using pandas library, I have trouble executing my ideas. Could you please help me how to aggregate the profit every 6 days and have the data look like the above table?

Answer 1

Easiest way is using pandas resample function.

Provided you have an index of type Datetime resampling to aggregate profits at every 6 days would be as simple as your_dataframe.resample('6D').sum()

You can do all sorts of resampling (end of month, end of quarter, begining of week, every hour, minute, second, ...). Check the full documentation if you're interested: https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.resample.html?highlight=resample#pandas.DataFrame.resample

Answer 2

I suggest using a combination of .rolling, pd.date_range, and .reindex

Say your DataFrame is df, with proper datetime indexing:

df = pd.DataFrame([['2020/1/19',10],
                   ['2020/1/20',7],
                   ['2020/1/25',14],
                   ['2020/1/29',18],
                   ['2020/2/1',12],
                   ['2020/2/2',17],
                   ['2020/2/9',28]],columns=['Date','Revenue'])

df['Date'] = pd.to_datetime(df['Date'])
df.set_index('Date',inplace=True)

The first step is to 'fill in' the missing days with dummy, zero revenue. We can use pd.date_range to get an index with evenly spaced dates from 2020/1/16 to 2020/2/8, and then .reindex to bring this into the main df DataFrame:

evenly_spaced_idx = pd.date_range(start='2020/1/16',end='2020/2/8',freq='1d')
df = df.reindex(evenly_spaced_idx, fill_value=0)

Now we can take a rolling sum for each 6 day period. We're not interested in every day's six day revenue total, only every 6th days, though:

summary_df = df.rolling('6d').sum().iloc[5::6, :]

The last thing with summary_df is just to format it the way you'd like so that it clearly states the date range which each row refers to.

summary_df['Start Date'] = summary_df.index-pd.Timedelta('6d')
summary_df['End Date'] = summary_df.index
summary_df.reset_index(drop=True,inplace=True)

Answer 3

You can use resample for this.

Make sure to have the "Date" column as datetime type.

>>> df = pd.DataFrame([["2020/1/19"  ,10],
    ... ["2020/1/20"  ,7],
    ... ["2020/1/25"  ,14],
    ... ["2020/1/29"  ,18],
    ... ["2020/2/1"  ,12],
    ... ["2020/2/2"  ,17],
    ... ["2020/2/9"  ,28]], columns=['Date', 'Revenue'])
>>> df['Date'] = pd.to_datetime(df.Date)

For pandas < 1.1.0

>>> df.set_index('Date').resample('6D', base=3).sum()
            Revenue
Date
2020-01-16       17
2020-01-22       14
2020-01-28       47
2020-02-03        0
2020-02-09       28

For pandas >= 1.1.0

>>> df.set_index('Date').resample('6D', origin='2020-01-16').sum()
            Revenue
Date
2020-01-16       17
2020-01-22       14
2020-01-28       47
2020-02-03        0
2020-02-09       28