Pull value n before last value in df

user_15061143 0
Viewed 24

I have a df with annual data in a number of cols. I have code to pull the last value in each column, excluding 0s. I'd like to pull the nth row previous to the row I pulled, the issue being that the index isn't the same for all of these values. I'd like to use the n from df_periods on each col to pull the nth value previous. I'd thought of trying to index the values I get from endval but because they're not from the same row it doesn't seem to work. Any thoughts on this?

df=
col1 col2 col3 col4 col5
8    9    6    7    1
8    9    6    7    1
8    9    6    7    1
8    9    6    7    1
8    9    6    7    NaN  
8    NaN  6    7    NaN  
8    NaN  6    7    NaN  
8    NaN  6    7    NaN  

df_periods=
col1 col2 col3 col4 col5
 4    3    5    4    4

endval = df.stack().groupby(level=1).last()
pandas numpy python loops
1 Answers

Try:

# remove the `nan` values
s = df.stack().reset_index(name='value')

# enumerate date from bottom up
count_from_bottom = s[::-1].groupby('level_1').cumcount()[::-1]

# compare the enumeration with the respective threshold and extract data
endval = s.loc[count_from_bottom < s['level_1'].map(df_periods.iloc[0])]

Output:

    level_0 level_1  value
4         0    col5    1.0
9         1    col5    1.0
11        2    col2    9.0
14        2    col5    1.0
16        3    col2    9.0
17        3    col3    6.0
19        3    col5    1.0
20        4    col1    8.0
21        4    col2    9.0
22        4    col3    6.0
23        4    col4    7.0
24        5    col1    8.0
25        5    col3    6.0
26        5    col4    7.0
27        6    col1    8.0
28        6    col3    6.0
29        6    col4    7.0
30        7    col1    8.0
31        7    col3    6.0
32        7    col4    7.0
user_42384080
Related