Resample Time Series Data and Find Maximum Month-End Frequency in Python

Assume, you have time series and the result for maximum month-end frequency,

DataFrame is:
 Id time_series
0 1 2020-01-05
1 2 2020-01-12
2 3 2020-01-19
3 4 2020-01-26
4 5 2020-02-02
5 6 2020-02-09
6 7 2020-02-16
7 8 2020-02-23
8 9 2020-03-01
9 10 2020-03-08
Maximum month end frequency:
              Id time_series
time_series
2020-01-31    4 2020-01-26
2020-02-29    8 2020-02-23
2020-03-31    10 2020-03-08

Solution

To solve this, we will follow the steps given below −

• Define a dataframe with one column,

d = {'Id': [1,2,3,4,5,6,7,8,9,10]}
df = pd.DataFrame(d)

• Create date_range function inside start=’01/01/2020’, periods = 10 and assign freq = ‘W’. It will generate ten dates from given start date to next weekly start dates and store it as df[‘time_series’].

df['time_series'] = pd.date_range('01/01/2020', periods=10, freq='W')

• Apply resample method to find the maximum month end frequency,

df.resample('M', on='time_series').max())

Example

Let’s see the below implementation to get a better understanding −

import pandas as pd
d = {'Id': [1,2,3,4,5,6,7,8,9,10]}
df = pd.DataFrame(d)
df['time_series'] = pd.date_range('01/01/2020',
                                    periods=10,
                                    freq='W')
print("DataFrame is:\n",df)
print("Maximum month end frequency: ")
print(df.resample('M', on='time_series').max())

Output

DataFrame is:
 Id time_series
0 1 2020-01-05
1 2 2020-01-12
2 3 2020-01-19
3 4 2020-01-26
4 5 2020-02-02
5 6 2020-02-09
6 7 2020-02-16
7 8 2020-02-23
8 9 2020-03-01
9 10 2020-03-08
Maximum month end frequency:
              Id time_series
time_series
2020-01-31    4 2020-01-26
2020-02-29    8 2020-02-23
2020-03-31    10 2020-03-08