CODEWITHSUNDEEP
pythonpandas
ShortHair
ShortHair

Reputation: 109

Create regular time series from irregular interval with python

I wonder if is it possible to convert irregular time series interval to regular one without interpolating value from other column like this :

Index                  count
2018-01-05 00:00:00      1
2018-01-07 00:00:00      4
2018-01-08 00:00:00      15
2018-01-11 00:00:00      2
2018-01-14 00:00:00      5
2018-01-19 00:00:00      5
....

2018-12-26 00:00:00      6
2018-12-29 00:00:00      7
2018-12-30 00:00:00      8

And I expect the result to be something like this:

Index                  count
2018-01-01 00:00:00      0
2018-01-02 00:00:00      0
2018-01-03 00:00:00      0
2018-01-04 00:00:00      0
2018-01-05 00:00:00      1
2018-01-06 00:00:00      0
2018-01-07 00:00:00      4
2018-01-08 00:00:00      15
2018-01-09 00:00:00      0
2018-01-10 00:00:00      0
2018-01-11 00:00:00      2
2018-01-12 00:00:00      0
2018-01-13 00:00:00      0
2018-01-14 00:00:00      5
2018-01-15 00:00:00      0
2018-01-16 00:00:00      0
2018-01-17 00:00:00      0
2018-01-18 00:00:00      0
2018-01-19 00:00:00      5
....

2018-12-26 00:00:00      6
2018-12-27 00:00:00      0
2018-12-28 00:00:00      0
2018-12-29 00:00:00      7
2018-12-30 00:00:00      8
2018-12-31 00:00:00      0

So, far I just try resample from pandas but it only partially solved my problem.

Thanks in advance

Upvotes: 2

Views: 1099

Answers (1)

jezrael
jezrael

Reputation: 863031

Use DataFrame.reindex with date_range:

#if necessary
df.index = pd.to_datetime(df.index)

df = df.reindex(pd.date_range('2018-01-01','2018-12-31'), fill_value=0)
print (df)
            count
2018-01-01      0
2018-01-02      0
2018-01-03      0
2018-01-04      0
2018-01-05      1
          ...
2018-12-27      0
2018-12-28      0
2018-12-29      7
2018-12-30      8
2018-12-31      0

[365 rows x 1 columns]

Upvotes: 2

Related Questions