Shifting Values in Python

Question

I have a problem that sounds easy, however, I could not find a solution. I would like to shift values according to the first year of the release. I mean the first column represents the years of the release and the columns are years when the device is broken (values are numbers of broken devices).

See example:

# TESTING of the shifting
df = pd.DataFrame({'Delivery Year' : [1976,1977,1978,1979], "Freq" : [120,100,80,60],
                                              "1976" : [10,float('nan'),float('nan'),float('nan')], 
                                              "1977" : [5,3,float('nan'),float('nan')], 
                                              "1978" : [10,float('nan'),8,float('nan')], 
                                              "1979" : [13,10,5,14]
                   })
df

Desired Output:

# DESIRED
df = pd.DataFrame({'Delivery Year' : [1976,1977,1978,1979], "Freq" : [120,100,80,60],
                                              "1. Year" : [10,3,8,14], 
                                              "2. Year" : [5,float('nan'),5,float('nan')], 
                                              "3. Year" : [10,10,float('nan'),float('nan')], 
                                              "4. Year" : [13,float('nan'),float('nan'),float('nan')]
                   })
df

jezrael · Accepted Answer · 2021-09-20 10:06:34Z

1

Use custom function with compare columnsnames without Delivery Year, Freq with Delivery Year and shifting by this value:

def f(x):
    shifted = np.argmin((x.index.astype(int)< x.name[0]))
    return x.shift(-shifted)
   

df = df.set_index(['Delivery Year', 'Freq']).apply(f, axis=1)
df.columns = [f'{i + 1}.Year' for i in range(len(df.columns))]
df = df.reset_index()
print (df)
   Delivery Year  Freq  1.Year  2.Year  3.Year  4.Year
0           1976   120    10.0     5.0    10.0    13.0
1           1977   100     3.0     NaN    10.0     NaN
2           1978    80     8.0     5.0     NaN     NaN
3           1979    60    14.0     NaN     NaN     NaN

edited Sep 20, 2021 at 10:06

answered Sep 20, 2021 at 9:53

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jaroslav Kotrba Over a year ago

WOW, thank you very much it is working correctly :)

Collectives™ on Stack Overflow

Shifting Values in Python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related