CODEWITHSUNDEEP
pythonpandasdateplottime-series
Fernando Quintino
Fernando Quintino

Reputation: 193

Aggregate and plot time series in pandas

I have a dataframe with date as index. I want to count events aggregated bi-weekly and plot. Example:

date        id
2018-01-01  a1
2018-01-01  a2
2018-01-05  a3
2018-01-12  a4
2018-01-15  a5
2018-01-17  a6
2018-01-19  a7
...

Should appear as (the format is ilustrative, if I am able to discriminate then it is fine ):

2018-01-1   4
2018-01-2   3
...

Then plot.

If possible, I want a parameter so I am able to switch to weekly or monthly.

Upvotes: 1

Views: 815

Answers (1)

mozway
mozway

Reputation: 260630

If date is the index, you can use resample with the SemiMonthStart ('SMS') frequency:

df.index = pd.to_datetime(df.index)
df.resample('SMS').count()

Output:

            id
date          
2018-01-01   4
2018-01-15   3

You can also use date offsets (here SemiMonthBegin) to round your dates and use this result to groupby+count:

group = (pd.to_datetime(df['date'])
           .apply(pd.offsets.SemiMonthBegin().rollback)
           )

out = df.groupby(group)['id'].count()

Output:

date
2018-01-01    4
2018-01-15    3
Name: id, dtype: int64

Upvotes: 2

Related Questions