CODEWITHSUNDEEP
pythonpandasdataframe
Rod0n
Rod0n

Reputation: 1079

Upsampling daily multi indexed data to hourly samples in Pandas

I've a dataframe that looks similar to this:

                     time       currency          rate
2021-02-22 00:00:00+00:00            USD  54410.856295
2021-02-23 00:00:00+00:00            USD  48691.894832
2021-02-24 00:00:00+00:00            USD  49849.378714
2021-02-25 00:00:00+00:00            USD  46992.665450
2021-02-26 00:00:00+00:00            USD  46551.487477
2021-02-22 00:00:00+00:00            EUR  50410.856295
2021-02-23 00:00:00+00:00            EUR  41691.894832
2021-02-24 00:00:00+00:00            EUR  40849.378714
2021-02-25 00:00:00+00:00            EUR  42992.665450
2021-02-26 00:00:00+00:00            EUR  42551.487477

What I'd like to do is to upsample the daily data to hourly, so I have the hourly rate by currency. I've tried the following:

df.index = pd.DatetimeIndex(pd.to_datetime(df['time']))
df['time'] = df.resample('H').ffill()

But it fails because the column 'time' is not unique so I think I would need a multi index to do this. I've tried several things but no luck.

Upvotes: 0

Views: 52

Answers (1)

Quang Hoang
Quang Hoang

Reputation: 150735

Try setting time as index, then groupby:

(df.set_index('time').groupby('currency')
   .apply(lambda x: x.resample('H').ffill())
   .reset_index('currency', drop=True)
   .reset_index()
)

Output:

                         time currency          rate
0   2021-02-22 00:00:00+00:00      EUR  50410.856295
1   2021-02-22 01:00:00+00:00      EUR  50410.856295
2   2021-02-22 02:00:00+00:00      EUR  50410.856295
3   2021-02-22 03:00:00+00:00      EUR  50410.856295
4   2021-02-22 04:00:00+00:00      EUR  50410.856295
..                        ...      ...           ...
189 2021-02-25 20:00:00+00:00      USD  46992.665450
190 2021-02-25 21:00:00+00:00      USD  46992.665450
191 2021-02-25 22:00:00+00:00      USD  46992.665450
192 2021-02-25 23:00:00+00:00      USD  46992.665450
193 2021-02-26 00:00:00+00:00      USD  46551.487477

[194 rows x 3 columns]

Upvotes: 2

Related Questions