How to transform raw data to fixed-frequency time series?

5

1

How to transform raw data to fixed-frequency time series?

For example I have the following raw data in DataFrame

                       A    B
2017-01-01 00:01:01    0    100
2017-01-01 00:01:10    1    200
2017-01-01 00:01:16    2    300
2017-01-01 00:02:35    3    100
2017-01-01 00:02:40    4    100

I'd like to transform it into a time series:

  1. 1 minute frequency
  2. column A should have sum of values in time interval
  3. column B should have mean of values in time interval
  4. possibly other functions over other columns

Note: Raw data is not periodic.

Transformed data should be:

                       A    B
2017-01-01 00:01:00    3    200
2017-01-01 00:02:00    7    100

mahnunchik

Posted 2017-06-21T16:48:25.713

Reputation: 153

Answers

5

This sort of effect can be achieved with pandas.DataFrame.resample() combined with Resampler.aggregate() like:

Code:

df.resample("1Min").agg({'A': sum, 'B': np.mean})

Test code:

df = pd.read_fwf(StringIO(u"""
                           A    B
    2017-01-01T00:01:01    0    100
    2017-01-01T00:01:10    1    200
    2017-01-01T00:01:16    2    300
    2017-01-01T00:02:35    3    100
    2017-01-01T00:02:40    4    100"""),
                  header=1, parse_dates=[0], index_col=0)

print(df)    
print(df.resample("1Min").agg({'A': sum, 'B': np.mean}))

Results:

                     A    B
2017-01-01 00:01:01  0  100
2017-01-01 00:01:10  1  200
2017-01-01 00:01:16  2  300
2017-01-01 00:02:35  3  100
2017-01-01 00:02:40  4  100

                     A    B
2017-01-01 00:01:00  3  200
2017-01-01 00:02:00  7  100

Stephen Rauch

Posted 2017-06-21T16:48:25.713

Reputation: 1 725