How can I shift all date indices by 1 hour in a multi-level array?-CodePudding

                            price   
symbol date     
AAPL   2022-10-07 23:57:00  137.17
       2022-10-07 23:58:00  137.16
       2022-10-08 00:00:00  137.17
MSFT   2022-10-07 23:57:00  200.54
       2022-10-07 23:58:00  200.75
       2022-10-08 00:00:00  200.62

The dates are on level 1 index, in a multi-index dataframe. The type of the date index is "Index"

How can I shift all dates by one hour such that those timestamps are as follow:

2022-10-07 22:57:00        
2022-10-07 22:58:00           
2022-10-07 23:00:00

If I do this (suggested by Andrej before I pointed out I have duplicated time indices due to multiple symbols):

df.index = df.index.set_levels(
df.index.get_level_values("date") - pd.Timedelta("1 hour"), level="date")

I get:

ValueError: Level values must be unique:

CodePudding user response：

Try:

df.index = df.index.set_levels(
    df.index.get_level_values("date") - pd.Timedelta("1 hour"), level="date"
)
print(df)

Prints:

                             price
symbol date                       
AAPL   2022-10-07 22:57:00  137.17
       2022-10-07 22:58:00  137.16
       2022-10-07 23:00:00  137.17

EDIT:

def fn(x):
    x.index = x.index.set_levels(
        x.index.get_level_values("date") - pd.Timedelta("1 hour"),
        level="date",
    )
    return x


df = df.groupby(level=0, as_index=False).apply(fn).droplevel(0)
print(df)

Prints:

                             price
symbol date                       
AAPL   2022-10-07 22:57:00  137.17
       2022-10-07 22:58:00  137.16
       2022-10-07 23:00:00  137.17
MSFT   2022-10-07 22:57:00  200.54
       2022-10-07 22:58:00  200.75
       2022-10-07 23:00:00  200.62

CodePudding user response：

Another possible solution:

df.index = df.index.set_levels(
        df.reset_index()['date'] - pd.Timedelta('1 hour'),
        level='date', verify_integrity=False)

Output:

                             price
symbol date                       
AAPL   2022-10-07 22:57:00  137.17
       2022-10-07 22:58:00  137.16
       2022-10-07 23:00:00  137.17
MSFT   2022-10-07 22:57:00  200.54
       2022-10-07 22:58:00  200.75
       2022-10-07 23:00:00  200.62