Python: How can I get the previous 5 values in a Pandas dataframe after skipping the very last one?

Question

I have a Pandas dataframe, df as follows:

      0       1    2
0  k86e  201409  180
1  k86e  201410  154
2  k86e  201411  157
3  k86e  201412  153
4  k86e  201501  223
5  k86e  201502  166
6  k86e  201503  163
7  k86e  201504  169
8  k86e  201505  157

I know that in order to get the last 5 values of say column 2, I have to do:

df[2].tail()

This will return the values 157, 169, 163, 166, 233.

However, I would like to skip the very last value which is = 157 and get the last five values before 157 e.g. 169, 163, 166, 233, 153.

How can I do this?

Thanks in advance!

Answer 1

Use negative indices and pass these to iloc to slice the rows of interest:

In [5]:

df.iloc[-6:-1]
Out[5]:
      0       1    2
3  k86e  201412  153
4  k86e  201501  223
5  k86e  201502  166
6  k86e  201503  163
7  k86e  201504  169

You can then index the col of interest using the above:

In [6]:

df.iloc[-6:-1]['2']
Out[6]:
3    153
4    223
5    166
6    163
7    169
Name: 2, dtype: int64

The following will also work as this uses the ordinal position of the column

df.iloc[-6:-1,2]

The syntax for iloc means iloc[start:end] in this case we can pass a negative index to indicate we want to start from the 6th row from the end and end at the last row but not include it, this is also known as open, closed interval.

There is a related SO question about slicing notation.

Also the python docs

Answer 2

df.iloc[-6:-1,2]
Out[54]: 
3    153
4    223
5    166
6    163
7    169

If you want just the values:

df.iloc[-6:-1,2].values
Out[64]: array([153, 223, 166, 163, 169], dtype=int64)