Python: upsampling dataframe from daily to hourly data using ffill()

user_10857489 0
Viewed 970

I'm trying to upsample my data from daily to hourly frequency and forward fill missing data.

I start with the following code:

df1 = pd.read_csv("DATA.csv")   
df1.head(5)

Header

I then used the following to convert to a datetime string and set the date/time as an index:

df1['DT'] = pd.to_datetime(df1['DT']).dt.strftime('%Y-%m-%d %H:%M:%S')
df1.set_index('DT')

enter image description here

I try to resample hourly as follows:

df1['DT'] = df1.resample('H').ffill()

But I get the following error:

TypeError: Only valid with DatetimeIndex, TimedeltaIndex or PeriodIndex, but got an instance of 'RangeIndex'

I thought my dtype was already date time as instructed by the pd.to_datetime code above. Nothing I try seems to be working. Can anyone please help me?

My expected output is as follows:

DT                  VALUE
2016-08-01 00:00:00 0.000000
2016-08-01 01:00:00 0.000000
2016-08-01 02:00:00 0.000000

etc.

The file itself has approximately 1000 rows. The first 50 rows or so are zero so to clarify where there's actual data:

DT                  VALUE
2018-12-13 00:00:00 24000.000000
2018-12-13 01:00:00 24000.000000
2018-12-13 02:00:00 24000.000000
...
2018-12-13 23:00:00 24000.000000
2018-12-14 00:00:00 26000.000000
2018-12-14 01:00:00 26000.000000

etc.

pandas python
3 Answers

Try assign it back 

df1=df1.set_index('DT')

Or 

df1.set_index('DT',inplace=True)
user_79645270

I am assuming some initial rows of your dataset as you mentioned,

          DT    VALUE
0   2016-08-01  0
1   2016-08-02  0
2   2016-08-03  0
3   2016-08-04  0
4   2016-08-05  0
5   2016-08-06  0
6   2016-08-07  0
7   2016-08-08  0
8   2016-08-09  0

Then, make index on DT like this,

df = df.set_index('DT')
df

Output: 

           VALUE
   DT   
2016-08-01  0
2016-08-02  0
2016-08-03  0
2016-08-04  0
2016-08-05  0
2016-08-06  0
2016-08-07  0
2016-08-08  0
2016-08-09  0

Now, resample your dataframe,

df = df.resample('H').ffill()
df

Output: showing some initial values of output,

                VALUE
    DT  
2016-08-01 00:00:00 0
2016-08-01 01:00:00 0
2016-08-01 02:00:00 0
2016-08-01 03:00:00 0
2016-08-01 04:00:00 0
2016-08-01 05:00:00 0
2016-08-01 06:00:00 0
2016-08-01 07:00:00 0
2016-08-01 08:00:00 0
2016-08-01 09:00:00 0
2016-08-01 10:00:00 0
user_75154110

You could convert the index to a pd.DatetimeIndex and then resample that. I also don't think you need (or want) the strftime() call:

df1 = pd.read_csv("DATA.csv")
df1['DT'] = pd.to_datetime(df1['DT'])
df1.set_index('DT')
df1.index = pd.DatetimeIndex(df1.index)
df1['DT'] = df1.resample('H').ffill()

NOTE: You could probably combine a bunch of this and it would still be quite clear, like:

df1 = pd.read_csv("DATA.csv")
df1.index = pd.DatetimeIndex(pd.to_datetime(df1['DT']))
df1['DT'] = df1.resample('H').ffill()
user_55563590
Related