CODEWITHSUNDEEP
pythonpandastime-series
Cesar
Cesar

Reputation: 585

Filling data in timeseries based on date interval

I have this data frame:

           ds  Value
0  2017-03-31   3395
1  2017-04-30   4510
3  2017-05-31   2335
5  2017-06-30   6061
6  2017-07-31   4711
7  2017-08-31   3039

And I would like to define a date interval to fill the other rows with zeros.

For examples:

Expected result:

           ds  Value
0  2017-01-31   0
0  2017-02-28   0
0  2017-03-31   3395
1  2017-04-30   4510
3  2017-05-31   2335
5  2017-06-30   6061
6  2017-07-31   4711
7  2017-08-31   3039
8  2017-08-31   0
9  2017-08-31   0

I tried to use the following:

 df.reindex(pd.period_range(2017-01, 2017-10, freq='M'), fill_value=0)

But I got the following output:

ds  Value
2017-01 1970-01-01      0
2017-02 1970-01-01      0
2017-03 1970-01-01      0
2017-04 1970-01-01      0
2017-05 1970-01-01      0
2017-06 1970-01-01      0
2017-07 1970-01-01      0
2017-08 1970-01-01      0
2017-09 1970-01-01      0
2017-10 1970-01-01      0
2017-11 1970-01-01      0
2017-12 1970-01-01      0
2018-01 1970-01-01      0
2018-02 1970-01-01      0
2018-03 1970-01-01      0
2018-04 1970-01-01      0
2018-05 1970-01-01      0
2018-06 1970-01-01      0
2018-07 1970-01-01      0

Upvotes: 1

Views: 36

Answers (1)

jezrael
jezrael

Reputation: 862441

Create DatetimeIndex and reindex with date_range:

df = (df.set_index('ds')
        .reindex(pd.date_range('2017-01-31', '2017-10-31', freq='M'), fill_value=0))
print (df)
            Value
2017-01-31      0
2017-02-28      0
2017-03-31   3395
2017-04-30   4510
2017-05-31   2335
2017-06-30   6061
2017-07-31   4711
2017-08-31   3039
2017-09-30      0
2017-10-31      0

If want working with periods - convert DatetimeIndex to_period and reindex by period_range:

df = (df.set_index('ds')
        .to_period('M')
        .reindex(pd.period_range('2017-01', '2017-10', freq='M'), fill_value=0))
print (df)
         Value
2017-01      0
2017-02      0
2017-03   3395
2017-04   4510
2017-05   2335
2017-06   6061
2017-07   4711
2017-08   3039
2017-09      0
2017-10      0

Upvotes: 2

Related Questions