Converting daily data in to weekly in Pandas

Question

I have a dataframe as given below:

Index    Date               Country    Occurence
0    2013-12-30                  US             1
1    2013-12-30                  India          3
2    2014-01-10                  US             1
3    2014-01-15                  India          1
4    2014-02-05                  UK             5

I want to convert daily data into weekly,grouped by anatomy,method being sum. Itried resampling,but the output gave Multi Index data frame from which i was not able to access "Country" and "Date" columns(pls refer above)

The desired output is given below:

Date   Country    Occurence
Week1  India      4
Week2  
Week1   US        2
Week2
Week5   Germany   5

Its always good to post some code :) – WoodChopper Oct 29 '15 at 07:01 — WoodChopper, Oct 29 '15 at 07:01
Yeah sure, will do that ... :) – Nithin Das Oct 29 '15 at 09:56 — Nithin Das, Oct 29 '15 at 09:56

Zero · Accepted Answer · 2015-10-29T10:24:13.923

You can groupby on country and resample on week

In [63]: df
Out[63]:
        Date Country  Occurence
0 2013-12-30      US          1
1 2013-12-30   India          3
2 2014-01-10      US          1
3 2014-01-15   India          1
4 2014-02-05      UK          5

In [64]: df.set_index('Date').groupby('Country').resample('W', how='sum')
Out[64]:
                    Occurence
Country Date
India   2014-01-05          3
        2014-01-12        NaN
        2014-01-19          1
UK      2014-02-09          5
US      2014-01-05          1
        2014-01-12          1

And, you could use reset_index()

In [65]: df.set_index('Date').groupby('Country').resample('W', how='sum').reset_index()
Out[65]:
  Country       Date  Occurence
0   India 2014-01-05          3
1   India 2014-01-12        NaN
2   India 2014-01-19          1
3      UK 2014-02-09          5
4      US 2014-01-05          1
5      US 2014-01-12          1

As mentioned,i tried Resampling to weekly data and the output gave the similar one like above.But how can i access the 'Country' and 'Date' column,as this is a multiindex dataframe.I tried **droplevel(0)** to remove the first level,but it didn't work. So,is there any other way that i get "Date", "Country", "Occurence" in same level — Nithin Das, Oct 29 '15 at 09:53
Check the updated solution with `reset_index()`, that works? — Zero, Oct 29 '15 at 10:24

Converting daily data in to weekly in Pandas

1 Answers1