How to get a value from of the previous row in a (sliced) pandas data frame?

Question

I have this dataframe (drr2) which is filtered from my main dataframe (df).

First I want to get all the rows that are right before or previous to the the rows which has the value on Diff_indexes >100. (i.e, for example the first row with Diff_indexes >100 is the row with the index 234, meaning I am interested in row 112 and so on).

Here is My dataframe:

drr2 =
           Var1                                Var2  Diff_indexes
105    26.312972  DrawRatingStimulationPleasantness1          10.0
109    30.046355  DrawRatingStimulationPleasantness2           4.0
112    31.029982  DrawRatingStimulationPleasantness3           3.0
234    76.220259  DrawRatingStimulationPleasantness2         122.0
237    77.719969  DrawRatingStimulationPleasantness3           3.0
240    78.286882  DrawRatingStimulationPleasantness2           3.0
243    79.270320  DrawRatingStimulationPleasantness3           3.0
385   139.242069  DrawRatingStimulationPleasantness2         142.0
388   141.291997  DrawRatingStimulationPleasantness3           3.0
511   186.265111  DrawRatingStimulationPleasantness5         123.0
644   232.106194  DrawRatingStimulationPleasantness2         133.0
647   233.306226  DrawRatingStimulationPleasantness3           3.0
776   266.145625  DrawRatingStimulationPleasantness3         129.0
909   328.336363  DrawRatingStimulationPleasantness4         133.0
1040  377.658295  DrawRatingStimulationPleasantness2         131.0
1043  378.774965  DrawRatingStimulationPleasantness3           3.0
1046  379.108252  DrawRatingStimulationPleasantness4           3.0
1174  423.064324  DrawRatingStimulationPleasantness2         128.0
1304  465.054045  DrawRatingStimulationPleasantness4         130.0
1307  466.571473  DrawRatingStimulationPleasantness3           3.0
1466  506.243939  DrawRatingStimulationPleasantness3         159.0
1469  507.643720  DrawRatingStimulationPleasantness2           3.0
1618  564.352222  DrawRatingStimulationPleasantness2         149.0
1748  606.106813  DrawRatingStimulationPleasantness5         130.0
1757  609.423391  DrawRatingStimulationPleasantness4           9.0
1762  609.706667  DrawRatingStimulationPleasantness2           5.0
1893  654.060668  DrawRatingStimulationPleasantness1         131.0
1896  655.294472  DrawRatingStimulationPleasantness2           3.0
2022  700.601474  DrawRatingStimulationPleasantness4         126.0
2161  761.392519  DrawRatingStimulationPleasantness5         139.0
2164  764.076032  DrawRatingStimulationPleasantness4           3.0
2167  764.293120  DrawRatingStimulationPleasantness3           3.0
2170  764.676851  DrawRatingStimulationPleasantness2           3.0
2173  765.043686  DrawRatingStimulationPleasantness1           3.0

I already got all the indexes of the row that has Diff_indexes > 100 and put them in a list. And here is the code I used to do so:

rows_max_idx= drr2[drr2['Diff_indexes']>100]
list_indexes= list(rows_max_idx.index.values)
# To add the last value to the list: 
x = drr2['Diff_indexes'].index[-1]
list_indexes.append(x)
print(list_indexes)

And here is the outcome:

[234, 385, 511, 644, 776, 909, 1040, 1174, 1304, 1466, 1618, 1748, 1893, 2022, 2161, 2173]

This is my desired outcome: desired_df=

            Var1                                Var2  Diff_indexes
112    31.029982  DrawRatingStimulationPleasantness3           3.0
243    79.270320  DrawRatingStimulationPleasantness3           3.0
388   141.291997  DrawRatingStimulationPleasantness3           3.0
511   186.265111  DrawRatingStimulationPleasantness5         123.0
647   233.306226  DrawRatingStimulationPleasantness3           3.0
776   266.145625  DrawRatingStimulationPleasantness3         129.0
909   328.336363  DrawRatingStimulationPleasantness4         133.0
1046  379.108252  DrawRatingStimulationPleasantness4           3.0
1174  423.064324  DrawRatingStimulationPleasantness2         128.0
1307  466.571473  DrawRatingStimulationPleasantness3           3.0
1469  507.643720  DrawRatingStimulationPleasantness2           3.0
1618  564.352222  DrawRatingStimulationPleasantness2         149.0
1762  609.706667  DrawRatingStimulationPleasantness2           5.0
1896  655.294472  DrawRatingStimulationPleasantness2           3.0
2022  700.601474  DrawRatingStimulationPleasantness4         126.0
2170  764.676851  DrawRatingStimulationPleasantness2           3.0

the fesired_df should have the following 16 indexes respectively (112, 243, 388, 511, 647, 776, 909, 1046, 1174, 1307, 1469, 1618, 1762,1896, 2022, 2170)

I hope you can help me, Thank you in advance,

user_3474397 · Answer

Assuming that you ONLY want the rows immediately preceding the rows with Diff_indexes > 100, you can do this:

drr2.iloc[[x - 1 for x in drr2[drr2.Diff_indexes > 100].index.values]]

Basically, this takes the index location (not the value) of the rows with Diff_indexes > 100, uses a list comprehension to get the previous location (by subtracting one to each value in the list), and then uses the resulting list to slice the dataframe by index location (thus the .iloc)

user_10035985 · Answer

Try:

print(
    df.loc[
        df["Diff_indexes"].gt(100).shift(-1, fill_value=False)
    ].index.tolist()
)

Prints:

[112, 243, 388, 511, 647, 776, 909, 1046, 1174, 1307, 1469, 1618, 1762, 1896, 2022]

EDIT: To add last element from index:

print(
    df.loc[
        df["Diff_indexes"].gt(100).shift(-1, fill_value=False)
    ].index.tolist()
    + [df.index[-2]]
)

Prints:

[112, 243, 388, 511, 647, 776, 909, 1046, 1174, 1307, 1469, 1618, 1762, 1896, 2022, 2170]

How to get a value from of the previous row in a (sliced) pandas data frame?

2 Answers