How to dinamically change the shift periods if value is NaN in Pandas?

user_6497419 0
Viewed 43

I have the following DF (simplified):

value
1
4
2
NaN
9
8
7

For example, when I apply the function shift(3)+current_row I get:

value  result
1      NaN
4      NaN
2      NaN  
NaN    NaN
9      13
8      10
7      NaN

But what I need is if a value is NaN, try with periods=N-1:

value  result
1      1       shift(3)=NAN -> shift(2)=NAN -> shift(1)=NAN -> current_value
4      5       shift(3)=NAN -> shift(2)=NAN -> shift(1)=1 + current_value
2      3       shift(3)=NAN -> shift(2)=1 + current_value
NaN    1       shift(3)=1 + if current_value == NaN then 0 else current_value
9      13      shift(3)=4 + current_value
8      10      shift(3)=2 + current_value
7      16      shift(3)=NaN -> shift(2)=9 + current_value

If it's possible, in the spirit of the pythonic way.

Thanks and regards.

pandas dataframe python-3.x
1 Answers

Shift the value column for the periods in the range 3...1, then create a dataframe from these shifted columns and backfill along the index axis to fill the NaN values, then using iloc select the first row and add this row with the value column

s = pd.DataFrame(df['value'].shift(3 - i) for i in range(3)).bfill().iloc[0]
df['result'] = df['value'].add(s, fill_value=0)

   value  result
0    1.0     1.0
1    4.0     5.0
2    2.0     3.0
3    NaN     1.0
4    9.0    13.0
5    8.0    10.0
6    7.0    16.0
user_128331660
Related