Pandas resample method does not work properly

Question

I have been analyzing the Seoul Bike Sharing Demand dataset, which is available at Seoul Bike Sharing Demand . During my analysis, I found the need to use a resampling method. To accomplish this, I loaded the dataset into a Pandas DataFrame, which I named df. Then, I applied the desired resampling method using the following code snippet:

label_encoders = {}
categorical_columns = list()
for column in df.columns:
    if df.dtypes[column] in [np.int64, np.float64]:
        pass
    else:
        if column != 'Date':
            categorical_columns.append(column)
            Label_encoder = LabelEncoder()
            label_encoders[column] = Label_encoder
            
            numerical_column = Label_encoder.fit_transform(df[column])
            df[column] = numerical_column

ndf = df.copy()
ndf.set_index('Date', inplace = True)

I want to draw a correlation between Holidays and Rented Bike count.

Holiday = ndf[ndf['Holiday'] == 0].resample('D')['Rented Bike Count'].sum()

But I expected that it does not include days that are not holiday which in this case , their Holiday column should not be 1(0 -> Holiday, 1 -> Not Holiday) . But when I run this code, the result is something like this:

Date
2017-12-22    7184
2017-12-23       0
2017-12-24    2014
2017-12-25    3966
2017-12-26       0
              ... 
2018-10-05       0
2018-10-06       0
2018-10-07       0
2018-10-08       0
2018-10-09       0
Freq: D, Name: Rented Bike Count, Length: 292, dtype: int64

For example 2017-12-23 is not Holiday but is included in the result and other days like
2018-10-05. But if I run the code with a minor change for non-holiday days it seems to work fine

Holiday = ndf[ndf['Holiday'] == 1].resample('D')['Rented Bike Count'].sum()

And it as expected does not included the holiday days in the processing . I mean why this problem pops up? What am I doing wrong in this case? Thanks in advance

Refrain from showing your data as an image or link. Your question needs a minimal reproducible example consisting of sample input, expected output, actual output, and only the relevant code necessary to reproduce the problem. See [How to make good reproducible pandas examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) for best practices related to Pandas questions. — itprorh66, Jun 27 '23 at 20:19

score 1 · Accepted Answer · answered Jun 27 '23 at 21:19

I mean why this problem pops up? What am I doing wrong in this case?

It's not a problem and you're not doing anything wrong :-)

When you resample a time series, you aggregate values for a same time slice. In your case, you convert an hourly TS to a daily TS and compute the sum of Rented Bike Count. As you filter your dataframe to keep only holidays, you expected to have only 18 days (432 records where holidays=0 / 24 hours)

You're right but remember, you use resample so Pandas will return a DatetimeIndex with a freq=D from the first date (first holiday) to the last date (last holiday). So in your case all days between 2017-12-22 and 2018-09-25 (278 days):

m = ndf['Holiday'] == 0
start = ndf[m].index.min()
end = ndf[m].index.max()

# Output
>>> m.sum() / 24
18.0

>>> start
Timestamp('2017-12-22 00:00:00')  # the first holiday

>>> end
Timestamp('2018-09-25 00:00:00')  # the last holiday

>>> end - start + pd.Timedelta(days=1)  # not inclusive, add 1 day
Timedelta('278 days 00:00:00')

The output of your code is:

>>> ndf[ndf['Holiday'] == 0].resample('D')['Rented Bike Count'].sum()
Date
2017-12-22     7184  # first holiday
2017-12-23        0
2017-12-24     2014
2017-12-25     3966
2017-12-26        0
              ...  
2018-09-21        0
2018-09-22        0
2018-09-23    20060
2018-09-24    17259
2018-09-25    23350  # last holiday
Freq: D, Name: Rented Bike Count, Length: 278, dtype: int64
#     ^-- daily freq                       ^-- number of days

To get only holidays, you have to use the boolean mask m:

>>> ndf[ndf['Holiday'] == 0].resample('D')['Rented Bike Count'].sum()[m[m].index.unique()]

Date
2017-12-22     7184
2017-12-24     2014
2017-12-25     3966
2017-12-31     3423
2018-01-01     4290
2018-02-15     3484
2018-02-16     2593
2018-02-17     3118
2018-01-03     5132
2018-01-05    26820
2018-05-22    13790
2018-06-06    30498
2018-08-15    18565
2018-09-23    20060
2018-09-24    17259
2018-09-25    23350
2018-03-10    30349
2018-09-10        0
Name: Rented Bike Count, dtype: int64

Note: you should sort your dataframe by index (Date) to better understanding.

Pandas resample method does not work properly

1 Answers1