pythonpandasdataframeresampling

Python Pandas DataFrame resample daily data to week by Mon-Sun weekly definition?


import pandas as pd
import numpy as np

dates = pd.date_range('20141229',periods=14, name='Day')
df = pd.DataFrame({'Sum1': [1667, 1229, 1360, 9232, 8866, 4083, 3671, 10085, 10005, 8730, 10056, 10176, 3792, 3518],
                   'Sum2': [91, 75, 75, 254, 239, 108, 99, 259, 395, 355, 332, 386, 96, 111],
                   'Sum3': [365.95, 398.97, 285.12, 992.17, 1116.57, 512.11, 504.47, 1190.96, 1753.6, 1646.25, 1344.05, 1582.67, 560.95, 736.44],
                   'Sum4': [5, 5, 1, 5, 8, 8, 2, 10, 12, 16, 16, 6, 6, 3]},index=dates); print(df)

The df produced looks like this:

             Sum1  Sum2     Sum3  Sum4
Day                                   
2014-12-29   1667    91   365.95     5
2014-12-30   1229    75   398.97     5
2014-12-31   1360    75   285.12     1
2015-01-01   9232   254   992.17     5
2015-01-02   8866   239  1116.57     8
2015-01-03   4083   108   512.11     8
2015-01-04   3671    99   504.47     2
2015-01-05  10085   259  1190.96    10
2015-01-06  10005   395  1753.60    12
2015-01-07   8730   355  1646.25    16
2015-01-08  10056   332  1344.05    16
2015-01-09  10176   386  1582.67     6
2015-01-10   3792    96   560.95     6
2015-01-11   3518   111   736.44     3

Let's say I resample the Dataframe to try and sum the daily data into weekly rows:

df_resampled = df.resample('W', how='sum', label='left'); print(df_resampled)

This produces the following:

             Sum1  Sum2     Sum3  Sum4
Day                                   
2014-12-28  30108   941  4175.36    34
2015-01-04  56362  1934  8814.92    69

Question 1: my definition of a week is Mon - Sun. Since my data starts on 2014-12-29 (a Monday), I want my Day label to also start on that day. How would I make the Day index label be the date of every Monday instead of every Sunday?

Desired Output:

             Sum1  Sum2     Sum3  Sum4
Day                                   
2014-12-29  30108   941  4175.36    34
2015-01-05  56362  1934  8814.92    69

What have I tried regarding Question 1?

I changed 'W' to 'W-MON' but it produced 3 rows by counting 2014-12-29 in 2014-12-22 row which is not what I want:

             Sum1  Sum2     Sum3  Sum4
Day                                   
2014-12-22   1667    91   365.95     5
2014-12-29  38526  1109  5000.37    39
2015-01-05  46277  1675  7623.96    59

Question 2: how would I format the Day index label to look like a range? Ex:

                         Sum1  Sum2     Sum3  Sum4
Day                                   
2014-12-29 - 2015-01-04  30108   941  4175.36    34
2015-01-05 - 2015-01-11  56362  1934  8814.92    69

Solution

  • In case anyone else was not aware, it turns out that the weekly Anchored Offsets are based on the end date. So, just resampling 'W' (which is the same as 'W-SUN') is by default a Monday to Sunday sample. The date listed is the end date. See this old bug report wherein neither the documentation nor the API got updated.

    Given that you specified label='left' in the resample parameters, you must have realized that fact. It's also why using 'W-MON' does not have the desired effect. What is confusing is that the left bound is not actually in the interval. As such the

    The approach for getting the right label and data in the interval has changed somewhat over different revisions of pandas. The most recent (and hopefully current) approach is listed first for quick access with earlier version for historical reference. All should yield the results listed in the Original Approach at the end of this answer.

    Update 1.1.0

    The most succinct approach is now using the handy closed parameter that controls which side of the interval is closed/included (see documentation):

    df_resampled = df.resample('W-MON', label='left', closed='left').sum()
    

    Thanks to xiaotong xu for noting this simpler form.

    An alternate approach since the handy loffset argument is deprecated as of version 1.1.0 is shifting after the resample. In this particular case that would mean:

    from pandas.tseries.frequencies import to_offset
    df_resampled = df.resample('W', label='left').sum()
    df_resampled.index = df_resampled.index + to_offset(pd.DateOffset(days=1))
    

    Update

    There is now was a loffset argument to resample() that allows you to shift the label offset. So, instead of modifying the index, you simple add the loffset argument like so:

    df.resample('W', how='sum', label='left', loffset=pd.DateOffset(days=1))
    

    Also of note how=sum is now deprecated in favor of using .sum() on the Resampler object that .resample() returns. So, the fully updated call would be:

    df_resampled = df.resample('W', label='left', loffset=pd.DateOffset(days=1)).sum()
    

    Original approach

    So, to display the start date for the period instead of the end date, you may add a day to the index. That would mean you would do:

    df_resampled.index = df_resampled.index + pd.DateOffset(days=1)
    

    For completeness, here is your original data with another day (a Sunday) added on the beginning to show the grouping really is Monday to Sunday:

    import pandas as pd
    import numpy as np
    
    dates = pd.date_range('20141228',periods=15, name='Day')
    df = pd.DataFrame({'Sum1': [10000, 1667, 1229, 1360, 9232, 8866, 4083, 3671, 10085, 10005, 8730, 10056, 10176, 3792, 3518],
                   'Sum2': [10000, 91, 75, 75, 254, 239, 108, 99, 259, 395, 355, 332, 386, 96, 111],
                   'Sum3': [10000, 365.95, 398.97, 285.12, 992.17, 1116.57, 512.11, 504.47, 1190.96, 1753.6, 1646.25, 1344.05, 1582.67, 560.95, 736.44],
                   'Sum4': [10000, 5, 5, 1, 5, 8, 8, 2, 10, 12, 16, 16, 6, 6, 3]},index=dates);
    print(df)
    df_resampled = df.resample('W', how='sum', label='left')
    df_resampled.index = df_resampled.index - pd.DateOffset(days=1)
    print(df_resampled)
    

    This outputs:

                 Sum1   Sum2      Sum3   Sum4
    Day
    2014-12-28  10000  10000  10000.00  10000
    2014-12-29   1667     91    365.95      5
    2014-12-30   1229     75    398.97      5
    2014-12-31   1360     75    285.12      1
    2015-01-01   9232    254    992.17      5
    2015-01-02   8866    239   1116.57      8
    2015-01-03   4083    108    512.11      8
    2015-01-04   3671     99    504.47      2
    2015-01-05  10085    259   1190.96     10
    2015-01-06  10005    395   1753.60     12
    2015-01-07   8730    355   1646.25     16
    2015-01-08  10056    332   1344.05     16
    2015-01-09  10176    386   1582.67      6
    2015-01-10   3792     96    560.95      6
    2015-01-11   3518    111    736.44      3
    
                 Sum1   Sum2      Sum3   Sum4
    Day                                      
    2014-12-22  10000  10000  10000.00  10000
    2014-12-29  30108    941   4175.36     34
    2015-01-05  56362   1934   8814.92     69
    

    I believe that is what you wanted for Question 1.