Is there a fast way to do a rolling sum of Pandas Dataframe at even time intervals when your data is not in even time interval?

user_13956296 0
Viewed 152

Let's say I have a dataframe, where the index are time stamps. However, the time stamps are at uneven intervals, so I want make it even. For example, I want to make my intervals, 1 minute time intervals. I want sum all values from t0 to t1 and make that sum the value at t1.

The way I have been doing it is via a loop. First I create a list of time stamps with starting and ending times. Then I subset my dataframe into a small one, and then do my calculations (in this case the sum) on that small dataframe. Then I save my value to a list. And repeat.

This unfortunately takes a long time.

Is there a faster way to do this? I am dealing with data on a really small time scale so I don't think it makes sense to create extra rows with 0 data and use the built in rolling sum function...

Example of data is below:

2020-04-01 00:03:48.197028     1
2020-04-01 00:24:07.186631    11
2020-04-01 00:24:07.200361     5
2020-04-01 00:24:07.204382     1
2020-04-01 00:24:07.208525    13

I want to convert it instead to something like:

2020-04-01 00:24:00.000000     sum(23:59 to 24:00)
2020-04-01 00:24:01.000000     sum(24:00 to 24:01)
2020-04-01 00:24:02.000000     sum(24:01 to 24:02)
2020-04-01 00:24:03.000000     sum(24:02 to 24:03)
2020-04-01 00:24:04.000000     sum(24:03 to 24:04)
pandas for-loop python-3.x python loops
1 Answers

Create an evenly-spaced datetime-index, apply it to your data and do a rolling-sum on the data frame with the evenly-spaced index. Since this will happen within numpy/pandas it will be much much faster than doing Python loops over the data.

Using the data from your example and assuming millisecond intervals:

df = """2020-04-01 00:03:48.197028\t1
2020-04-01 00:24:07.186631\t11
2020-04-01 00:24:07.200361\t5
2020-04-01 00:24:07.204382\t1
2020-04-01 00:24:07.208525\t13"""

# Reading the sample dataframe
from io import StringIO
mfile = StringIO(df)
adf = pd.read_csv(mfile, sep="\t")
adf.columns =  ['mtimestamp', 'mnumber']
adf.mtimestamp = pd.to_datetime(adf.mtimestamp)

# Creating a proper datetime index
adf = adf.set_index(pd.DatetimeIndex(adf['mtimestamp']))
adf = adf.drop(columns='mtimestamp')

# Resampling and summing
adf.resample('1ms').sum()

yields

                        mnumber
mtimestamp  
2020-04-01 00:24:07.186 11
2020-04-01 00:24:07.187 0
2020-04-01 00:24:07.188 0
user_33053010
Related