In Python, is there a way to create a new column in Pandas, but select which row the data would start on?

Question

Basically I want to add create a new column for each years worth of data but have it start one row down from where the last column started.

This is what it would look like in Excel

Answer 1

Use pandas.Series.shift to create each new column shifted down by 1.

For example, given

df = pd.DataFrame({'prod2018': [100, 90, 80, 70, 60, 50, 40, 30, 20, 10, 0, 0, 0]}, 
                  index=pd.RangeIndex(2018, 2031))

which looks like

      prod2018
2018       100
2019        90
2020        80
2021        70
2022        60
2023        50
2024        40
2025        30
2026        20
2027        10
2028         0
2029         0
2030         0

do

for i in range(2019, 2023):
    df[f'prod{i}'] = df[f'prod{i - 1}'].shift(1, fill_value=0)

giving

      prod2018  prod2019  prod2020  prod2021  prod2022
2018       100         0         0         0         0
2019        90       100         0         0         0
2020        80        90       100         0         0
2021        70        80        90       100         0
2022        60        70        80        90       100
2023        50        60        70        80        90
2024        40        50        60        70        80
2025        30        40        50        60        70
2026        20        30        40        50        60
2027        10        20        30        40        50
2028         0        10        20        30        40
2029         0         0        10        20        30
2030         0         0         0        10        20