CODEWITHSUNDEEP
pythonpandasdatetimeindexingpandas-groupby
reservoirinvest
reservoirinvest

Reputation: 1777

Split end-of-month data into weeks

Would like to split monthly data into weeks. Here is the code tried:

import pandas as pd
import datetime

d = {'By' : pd.Series(pd.date_range('20171030', periods=2, freq='M')),
     'Qty': pd.Series([100,200], index=range(2))}
df = pd.DataFrame(d)
df.resample('W').sum()

...gives me

Only valid with DatetimeIndex, TimedeltaIndex or PeriodIndex, but got an instance of 'RangeIndex' error.

Looking to split the _Qty_ value by _Sundays_

Know how to get a date_range by using:

<code>dates = pd.DataFrame(pd.date_range(min(df.By), max(df.By), freq="W-Sun"), columns=['Week'])</code>

...but don't know how to generate the split.

Upvotes: 0

Views: 208

Answers (2)

reservoirinvest
reservoirinvest

Reputation: 1777

Question was answered in the following #46579271

Upvotes: 0

cmaher
cmaher

Reputation: 5215

As indicated in the exception, pd.DataFrame.resample requires that you have some type of a datetime index; currently, your datetime values are in your column 'By'. Move said column to your DataFrame's index, and the resampling works as expected:

df.set_index('By').resample('W-Sun').sum()
#               Qty
# By               
# 2017-11-05  100.0
# 2017-11-12    NaN
# 2017-11-19    NaN
# 2017-11-26    NaN
# 2017-12-03  200.0

Upvotes: 1

Related Questions