Looking to create panel data that shows values for each month. Need help filling in months which no values are present

Question

Here is how my data is structured:

Date       Month Year Value

2018/10/3  10    2018 30

2018/9/1   9     2018 3

2018/7/3   7     2018 3

This is how I would like it to be:

Month Year Value

10    2018 30

9     2018 3

8     2018 0

7     2018 3

I'm really stumped on how to proceed. Any help would be much appreciated.

Answer 1

You can use the resample method on the dataframe. Like this

s=df.resample('M').mean()

s.Month=s.index.month

s.Year=s.index.year

s.Value.fillna(0,inplace=True)

            Month  Year  Value

Date                          

2018-07-31      7  2018    3.0

2018-08-31      8  2018    0.0

2018-09-30      9  2018    3.0

2018-10-31     10  2018   30.0