How to get list of previous n values of a column conditionally in DataFrame?

user_8147508 0
Viewed 70

My dataframe looks like below:

Subject     Score
    1       15
    2       0
    3       18
    2       30
    3       17
    1       5
    4       9
    2       7
    1       20
    1       8
    2       9
    1       12

I want to get the previous 3 scores for each record grouped by Subject as a list in new column like below:

Subject   Score Previous
1       15      []
2       0       []
3       18      []
2       30      [0]
3       17      [18]
1       5       [15]
4       9       []
2       7       [30,0]
1       20      [5,15]
1       8       [20,5,15]
2       9       [7,30,0]
1       12      [8,20,5]

Below code rolls all record not grouped by Subject

df['Previous']= [x.values.tolist()[:-1] for x in points['Score'].rolling(4)]

How do I get the above expected result?

pandas dataframe python
1 Answers

Since rolling only supports production of numeric values, this has to be a work around.

Try sort_values first then groupby rolling on window + 1 and strip off the last element:

window = 3
df = df.sort_values('Subject')
df['Previous'] = [
    x.agg(list)[:-1] for x in df.groupby('Subject')['Score'].rolling(window + 1)
]

    Subject  Score     Previous
0         1     15           []
5         1      5         [15]
8         1     20      [15, 5]
9         1      8  [15, 5, 20]
11        1     12   [5, 20, 8]
1         2      0           []
3         2     30          [0]
7         2      7      [0, 30]
10        2      9   [0, 30, 7]
2         3     18           []
4         3     17         [18]
6         4      9           []

Then sort_index to restore the initial order:

df = df.sort_index()

    Subject  Score     Previous
0         1     15           []
1         2      0           []
2         3     18           []
3         2     30          [0]
4         3     17         [18]
5         1      5         [15]
6         4      9           []
7         2      7      [0, 30]
8         1     20      [15, 5]
9         1      8  [15, 5, 20]
10        2      9   [0, 30, 7]
11        1     12   [5, 20, 8]

(Optional use extended slicing to reverse the lists and get elements in same order as expected output above):

window = 3
df = df.sort_values('Subject')
df['Previous'] = [x.agg(list)[-2::-1]
                  for x in df.groupby('Subject')['Score'].rolling(window + 1)]
df = df.sort_index()

df:

    Subject  Score     Previous
0         1     15           []
1         2      0           []
2         3     18           []
3         2     30          [0]
4         3     17         [18]
5         1      5         [15]
6         4      9           []
7         2      7      [30, 0]
8         1     20      [5, 15]
9         1      8  [20, 5, 15]
10        2      9   [7, 30, 0]
11        1     12   [8, 20, 5]

Complete Working Example:

import pandas as pd

df = pd.DataFrame({
    'Subject': [1, 2, 3, 2, 3, 1, 4, 2, 1, 1, 2, 1],
    'Score': [15, 0, 18, 30, 17, 5, 9, 7, 20, 8, 9, 12]
})

window = 3
df = df.sort_values('Subject')
df['Previous'] = [
    x.agg(list)[:-1] for x in df.groupby('Subject')['Score'].rolling(window + 1)
]
df = df.sort_index()

print(df)
user_154978880
Related