5
$\begingroup$

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
$\endgroup$
0
5
$\begingroup$

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
$\endgroup$
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.