How to group exact timestamp information into time period bins and average?

user_11149645 0
Viewed 184

I have a timestamped dataframe with two columns, containing sales of different products. What I want to do is create a plot of a certain products sale numbers in 10 minutes with 5 minute shifts, going back to 10 days back. I want the intervals shift like shown in the image. I have tried to use between function in a datetime shifting loop but it takes forever to fill up a list , shown in the code section, any faster solution?

enter image description here

example_apple = []
while interval_time <=  end_time:
example_apple.append(df_thisyear.filter(df_thisyear.TIMESTAMP.between(start_time, interval_time)) \
                               .filter(df_thisyear.product == 'Apple').count())
start_time = start_time + timedelta(minutes=5)
interval_time = interval_time + timedelta(minutes=5)
pandas-groupby pandas dataframe
1 Answers

Key for you is to sample the "exact time stamps" down to a column with desired "time bins" (e.g., 5-minute intervals). Once you do that, all yours rows may have different exact timestamps, but those that belong to the same time interval (bin) will have the same bin column value. Then, you you can easily select them.

If your timestamps are distributed over a span from 1-20, say like this:

data = pd.DataFrame({'timestamp': range(1, 21)})

and your have a list of start times for your desired time interval bins, such as this:

bins = [1, 6, 11, 16]

then you can sample the "exact" timestamps down into "time period bins" like this:

data['time_bin'] = pd.cut(
    data['timestamp'], 
    bins,
    labels=list(range(1, len(bins))),
    right=False
)

This will give you 1 single additional column time_bin, which is the down-sampled "exact timestamps" from the timestamp column into the time_bins that you have defined in your list.

A great, long explanation of different time-manipulation techniques see here

With that binned df, you can use df.groupby() to group over each of the time intervals that you have in your new column. For example, let's count how many timestamps we now have in each of our time_bins:

data.groupby('time_bin').count()[['timestamp']]

          timestamp
time_bin           
1                 5
2                 5
3                 5

With that group, do whatever: Count the number of apples and oranges, analyze each time bin separately, average over 2 time bins, take a cumulative sum or rolling average over n groups, etc.

user_147754780
Related