pandas shifting missing months

user_8039968 0
Viewed 34

let's assume the following dataframe and shift operation:

d = {'col1': ['2022-01-01','2022-02-01','2022-03-01','2022-05-01'], 'col2': [1,2,3,4]}
df = pd.DataFrame(d)
df['shifted'] = df['col2'].shift(1, fill_value=0)

I want to create a column containing the values of the month before and filling it up for months which do not exist with 0, so the desired result would look like:

col1 col2 shifted
2022-01-01 1 0
2022-02-01 2 1
2022-03-01 3 2
2022-05-01 4 0

So in the last line the value is 0 because there is no data for April.


But at the moment it looks like this:

col1 col2 shifted
2022-01-01 1 0
2022-02-01 2 1
2022-03-01 3 2
2022-05-01 4 3

Does anyone know how to achieve this?

pandas shift python
1 Answers

One idea is create month PeriodIndex, so possible shift by months, last replace missing values:

df = df.set_index(pd.to_datetime(df['col1']).dt.to_period('m'))
df['shifted'] = df['col2'].shift(1, freq='m').reindex(df.index, fill_value=0)

print (df)
               col1  col2  shifted
col1                              
2022-01  2022-01-01     1        0
2022-02  2022-02-01     2        1
2022-03  2022-03-01     3        2
2022-05  2022-05-01     4        0

Last is possible remove PeriodIndex:

df = df.reset_index(drop=True)
print (df)
         col1  col2  shifted
0  2022-01-01     1        0
1  2022-02-01     2        1
2  2022-03-01     3        2
3  2022-05-01     4        0
user_29010020
Related