Shift Pandas date index to next month

Question

I have a dataframe with a date index. I want to create a new column with a lagged value. That is, laggedfoo(1aug2016) = foo(1july2016)

I used dataframe.shift, but it didn't behave as expected; I can hack it to work, but I think I'm missing the big picture of how to treat and shift date indices.

When I shift by 1 'm', instead of shifting into the next month, it shifts to the end-of-month of the current month. (1July2016 becomes 30July2016, not 1Aug2016).

I can shift by 2 and get what I want, but I fear I'm missing some fundamental idea to using shift and the freq parameter

Answer 1

I think you need change frequency to month start frequency - MS - see offset-aliases:

import pandas as pd
    
fwd_df = pd.DataFrame(
    {
        "Dubai m1": {
            pd.Timestamp("2016-08-01 00:00:00"): 3, 
            pd.Timestamp("2016-07-01 00:00:00"): 2,
            pd.Timestamp("2016-09-01 00:00:00"): 4,
            pd.Timestamp("2016-06-01 00:00:00"): 1,
        }
    }
)
    
print(fwd_df["Dubai m1"])
# 2016-06-01    1
# 2016-07-01    2
# 2016-08-01    3
# 2016-09-01    4
# Name: Dubai m1, dtype: int64
    
print(fwd_df["Dubai m1"].shift(1, freq="MS"))
# 2016-07-01    1
# 2016-08-01    2
# 2016-09-01    3
# 2016-10-01    4
# Freq: MS, Name: Dubai m1, dtype: int64