How to set a multiindex with multiple dates in pandas?

Question

I have the following dataframe df:

Datetime1              Datetime2             Value
2018-01-01 00:00       2018-01-01 01:00      5
2018-01-01 01:00       2018-01-01 02:00      1
2018-01-01 02:00       2018-01-01 03:00      2
2018-01-01 03:00       2018-01-01 04:00      3
2018-01-01 04:00       2018-01-01 05:00      6

I want to set a multi index composed of Datetime1 and Datetime2 to further proceed with the data resampling and interpolation (from 1 hour to 30 minutes frequency).

If I do df.set_index(["Datetime1","Datetime2"]).resample("30T").ffill(), then it fails.

Desired output:

Datetime1              Datetime2             Value
2018-01-01 00:00       2018-01-01 01:00      5
2018-01-01 00:30       2018-01-01 01:30      5
2018-01-01 01:00       2018-01-01 02:00      1
2018-01-01 01:30       2018-01-01 02:30      1
...

It looks like MultiIndex is not the right tool for you. It's for a hierarchical index, i.e. with groups. See here pandas.pydata.org/pandas-docs/stable/user_guide/advanced.html What are you trying to achieve? Is Datetime2 always 1 hour offset from Datetime1 — Joooeey
– Joooeey, Commented Oct 8, 2021 at 10:06
What is your desired output? Consider having single datetime index but set its frequency (judging by your data it would be "1h"). Then resample the dataframe. — michcio1234
– michcio1234, Commented Oct 8, 2021 at 10:07
@Joooeey: Yes, Datetime2 is always 1 hour offset from Datetime1. I just need to resample the data from 1 hour frequency to 30 minutes frequency, maintaining the same offset. For Value I wanted to apply ffill(). — Fluxy
– Fluxy, Commented Oct 8, 2021 at 10:16
@michcio1234: The desired output is the same dataframe but resampled from 1 hour to 30 minutes frequency. How can I set a single datetime index with frequency? — Fluxy
– Fluxy, Commented Oct 8, 2021 at 10:18

jezrael · Accepted Answer · 2021-10-08 10:32:53Z

1

If there is one hour difference is possible create MultiIndex after resample with add 1H to new DatetimeIndex:

df = df.set_index(["Datetime1"])[['Value']].resample("30T").ffill()
df = df.set_index([df.index.rename('Datetime2') + pd.Timedelta('1H')], append=True)

print (df)
                                         Value
Datetime1           Datetime2                 
2018-01-01 00:00:00 2018-01-01 01:00:00      5
2018-01-01 00:30:00 2018-01-01 01:30:00      5
2018-01-01 01:00:00 2018-01-01 02:00:00      1
2018-01-01 01:30:00 2018-01-01 02:30:00      1
2018-01-01 02:00:00 2018-01-01 03:00:00      2
2018-01-01 02:30:00 2018-01-01 03:30:00      2
2018-01-01 03:00:00 2018-01-01 04:00:00      3
2018-01-01 03:30:00 2018-01-01 04:30:00      3
2018-01-01 04:00:00 2018-01-01 05:00:00      6

Or:

s = df.set_index(["Datetime1"])['Value'].resample("30T").ffill()
s.index = [s.index,s.index.rename('Datetime2') + pd.Timedelta('1H')]

print (s)
Datetime1            Datetime2          
2018-01-01 00:00:00  2018-01-01 01:00:00    5
2018-01-01 00:30:00  2018-01-01 01:30:00    5
2018-01-01 01:00:00  2018-01-01 02:00:00    1
2018-01-01 01:30:00  2018-01-01 02:30:00    1
2018-01-01 02:00:00  2018-01-01 03:00:00    2
2018-01-01 02:30:00  2018-01-01 03:30:00    2
2018-01-01 03:00:00  2018-01-01 04:00:00    3
2018-01-01 03:30:00  2018-01-01 04:30:00    3
2018-01-01 04:00:00  2018-01-01 05:00:00    6
Name: Value, dtype: int64

answered Oct 8, 2021 at 10:32

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Fluxy Over a year ago

jezrael, if Datetime1 and Datetime2 would need to be as 00:00 and 00:30 instead of 00:00 and 01:30, then the approach would be similar, right? Just Datetime2 would be shifted by 30 minutes instead of 1 hour.

jezrael Over a year ago

@Fluxy - exactly you are right. change + pd.Timedelta('1H') to + pd.Timedelta('30Min')

Joooeey · Accepted Answer · 2021-10-08 10:33:28Z

1

The multi-index is not meant for a double-index but for a hierarchical (grouped) index. See the docs. You said in the comments, that Datetime2 is always offset by 1 hour. That means it's probably fastest to recalculate it:

df.set_index("Datetime1","Datetime2").resample("30T").ffill()
df["Datetime2" = df.index + pd.Timedelta(1, "hour")

answered Oct 8, 2021 at 10:33

Joooeey

4,1653 gold badges44 silver badges59 bronze badges

Collectives™ on Stack Overflow

How to set a multiindex with multiple dates in pandas?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related