Get continuous dataframe in pandas by filling the missing month with 0

Question

I have a pandas dataframe as shown below where I have Month-Year, need to get the continuous dataframe which should include count as 0 if no rows are found for that month. Excepcted output is shown below.

Input dataframe

Month   | Count
--------------
Jan-15  | 10
Feb-15  | 100
Mar-15  | 20
Jul-15  | 10
Sep-15  | 11 
Oct-15  | 1 
Dec-15  | 15

Expected Output

Month   | Count
--------------
Jan-15  | 10
Feb-15  | 100
Mar-15  | 20
Apr-15  | 0
May-15  | 0
Jun-15  | 0
Jul-15  | 10
Aug-15  | 0
Sep-15  | 11 
Oct-15  | 1
Nov-15  | 0 
Dec-15  | 15

score 4 · Accepted Answer · answered Sep 04 '16 at 11:37

4

You can set the Month column as the index. It looks like Excel input, if so, it will be parsed at 01.01.2015 so you can resample it as follows:

df.set_index('Month').resample('MS').asfreq().fillna(0)
Out: 
            Count
Month            
2015-01-01   10.0
2015-02-01  100.0
2015-03-01   20.0
2015-04-01    0.0
2015-05-01    0.0
2015-06-01    0.0
2015-07-01   10.0
2015-08-01    0.0
2015-09-01   11.0
2015-10-01    1.0
2015-11-01    0.0
2015-12-01   15.0

If the month column is not recognized as date, you need to convert it first:

df['Month'] = pd.to_datetime(df['Month'], format='%b-%y')

answered Sep 04 '16 at 11:37

ayhan

70,170
20
182
203

asfreq() getting error in asfreq, seems like some argument needs to be passed – Vinay Ranjan Sep 04 '16 at 11:44
here is the error. asfreq() takes at least 2 arguments (1 given) – Vinay Ranjan Sep 04 '16 at 11:48
What version of pandas are you using? – ayhan Sep 04 '16 at 11:48
Pandas = 0.16.0 – Vinay Ranjan Sep 04 '16 at 11:51
1

Resample API changed in 0.18.0. Unfortunately I don't have access to earlier versions right now so I can't try it out. Is it possible for you to upgrade? – ayhan Sep 04 '16 at 12:22

Get continuous dataframe in pandas by filling the missing month with 0

Input dataframe

Expected Output

1 Answers1