Shifting a value and creating a new index using pandas

user_8913983 0
Viewed 90

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.

pandas python
2 Answers

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
user_163434640

Juste use loc to create your new indexes

>>> df = pd.DataFrame({2019:[10], 2020:[20], 2021:[30], 2022:[40]}, index=["A"])
>>> df.loc["A-1"] = df.loc["A"].shift()
>>> df.loc["A-2"] = df.loc["A-1"].shift()
>>> df
     2019  2020  2021  2022
A    10.0  20.0  30.0  40.0
A-1   NaN  10.0  20.0  30.0
A-2   NaN   NaN  10.0  20.0
user_28467110
Related