Shifting a value and creating a new index using pandas

I have a df

            2019            2020            2021            2022

A           10              20              30              40

I am trying to create 2 new indexes A-1 and A-2 so that the output would look like this:

            2019            2020            2021            2022

A           10              20              30              40
A-1         nan             10              20              40
A-2         nan             nan             10              20

I tried:

s = df.loc['A',:].shift(1, axis=0)
s = s.rename({'A': 'A-1'}, axis = 0)
df = df.combine_first(s)

But I get an error at
----> 3 df= df.combine_first(s)

ValueError: Must specify axis=0 or 1

When I add axis = 0 I get:

TypeError: combine_first() got an unexpected keyword argument ‘axis’

So I am not sure where is my mistake.

>Solution :

You need to change a bit your strategy. combine_first is not appropriate here.

Rather use concat, and keep the data as DataFrame:

pd.concat([df, df.loc[['A']].shift(1, axis=1).rename(index={'A': 'A-1'})])

output:

     2019  2020  2021  2022
A    10.0    20    30    40
A-1   NaN    10    20    30

shift programmatically

If you need to have many shifts, do it programmatically:

n = 3
df2 = pd.concat([df]+[(df.loc[['A']].shift(i+1, axis=1)
                         .rename(index={'A': f'A-{i+1}'})
                      ) for i in range(n)])

output:

     2019  2020  2021  2022
A    10.0  20.0  30.0    40
A-1   NaN  10.0  20.0    30
A-2   NaN   NaN  10.0    20
A-3   NaN   NaN   NaN    10

using Series

If really you only need to shift a single row, it is easier to work with Series:

n = 3
row = 'A'
s = df.loc[row]
df2 = pd.concat([s]+[s.shift(i+1).rename(f'{row}-{i+1}') for i in range(n)],
                axis=1).T

output:

     2019  2020  2021  2022
A    10.0  20.0  30.0    40
A-1   NaN  10.0  20.0    30
A-2   NaN   NaN  10.0    20
A-3   NaN   NaN   NaN    10