pandas bucket timestamp into TimeGrouper frequency group

user_2587904 0
Viewed 482

I have a data frame in pandas with a DateTime index. When grouping it with a time grouper: pd.Grouper(freq='360Min'), how can I join this result back onto the original timestamp? I.e. an equijoin timestamp=bucket will not work? Is there a convenience function? Should the asof join be used? Or would I manually have to extract the hours and then try to match it up?

example:

for a source of

import pandas as pd
df = pd.DataFrame(
   {
       "Publish date": [
            pd.Timestamp("2000-01-02"),
            pd.Timestamp("2000-01-02"),
            pd.Timestamp("2000-01-09"),
            pd.Timestamp("2000-01-16")
        ],
        "ID": [0, 1, 2, 3],
        "Price": [10, 20, 30, 40]
    }
)

Which gives:

  Publish date  ID  Price
0   2000-01-02   0     10
1   2000-01-02   1     20
2   2000-01-09   2     30
3   2000-01-16   3     40

I want to perform an aggregation with an arbitrary frequency (not only month, day, hour), let's say 1

month.

agg_result = df.groupby(pd.Grouper(key="Publish date", freq="1M")).agg([pd.Series.mean, pd.Series.median]).reset_index()
agg_result.columns = ['_'.join(col).strip() for col in agg_result.columns.values]
agg_result.columns = ['Publish date month', 'ID_mean', 'ID_median', 'Price_mean', 'Price_median']
print(agg_result)
Publish date month  ID_mean  ID_median  Price_mean  Price_median
0         2000-01-31      1.5        1.5          25            25

How can I ensure that the equijoin would work again? I.e. transform the original timestamp into the fitting bucket using the same arbitrary frequency?

I.e. described in the code of the example, how can I get:

agg_result['Publish date month'] = agg_result['Publish date'].apply(magic transform to same frequency bucket)
df.merge(agg_result, on['Publish date month'])

To work, i.e. define the transformation to the right bucket?

datetimeindex pandas time-series group-by python
2 Answers

EDIT:

The easiest way to identify the corresponding original values for each group should be:

gb = df.groupby(pd.Grouper(key="Publish date", freq="1M"))
dict(list(gb['Publish date']))

You can then use this to join any information back to the original table.

Can you just join on two intermediate columns?

df['Publish date'].dt.month

and

df.groupby(pd.Grouper(key="Publish date", freq="1M")).agg([pd.Series.mean, pd.Series.median]).index.month

like this

results =  df.groupby(pd.Grouper(key="Publish date", freq="1M")).agg([pd.Series.mean, pd.Series.median])

results.columns = ['-'.join(col[::-1]).strip() for col in results.columns]

df['month'] = df['Publish date'].dt.month

results['month'] = results.index.month
results.merge(df)
user_87213890

I would use the Groupby.transform method:

import pandas as pd
df = pd.DataFrame(
   {
       "Publish date": [
            pd.Timestamp("2000-01-02"),
            pd.Timestamp("2000-01-02"),
            pd.Timestamp("2000-01-09"),
            pd.Timestamp("2000-01-16")
        ],
        "ID": [0, 1, 2, 3],
        "Price": [10, 20, 30, 40]
    }
)

g = df.groupby(pd.Grouper(key="Publish date", freq="1M"))

(
  df.join(g.transform('mean'), rsuffix='_mean')
    .join(g.transform('median'), rsuffix='_median')
)

And that returns:

  Publish date  ID  Price  ID_mean  Price_mean  ID_median  Price_median
0   2000-01-02   0     10      1.5          25        1.5            25
1   2000-01-02   1     20      1.5          25        1.5            25
2   2000-01-09   2     30      1.5          25        1.5            25
3   2000-01-16   3     40      1.5          25        1.5            25

You can also use pandas.concat in place of DataFrame.join:

methods = ['mean', 'median', 'std', 'min', 'max']

pd.concat([
    df, *[g.transform(m).add_suffix(f'_{m}') for m in methods]
], axis='columns')

And that gives you:

  Publish date  ID  Price  ID_mean  Price_mean  ID_median  Price_median    ID_std  Price_std  ID_min  Price_min  ID_max  Price_max
0   2000-01-02   0     10      1.5          25        1.5            25  1.290994  12.909944       0         10       3         40
1   2000-01-02   1     20      1.5          25        1.5            25  1.290994  12.909944       0         10       3         40
2   2000-01-09   2     30      1.5          25        1.5            25  1.290994  12.909944       0         10       3         40
3   2000-01-16   3     40      1.5          25        1.5            25  1.290994  12.909944       0         10       3         40
user_15527480
Related