Pandas: groupby and get tail based on some column value

user_6065920 0
Viewed 312

I have a dataframe looks like

id    week    value
1      1        15
1      2        29
1      3        49
1      3        19
2      6        10
2      7        99
2      8        53

How extract dataframe based on the last 2 weeks for each id? It's like a tail but not for the records.

Desirable output

id    week    value
1      2        29
1      3        49
1      3        19
2      7        99
2      8        53
pandas python
2 Answers

This more like factorized then pick the last two of each group

m = df.iloc[::-1].groupby('id')['week'].transform(lambda x :x.factorize()[0]).isin([0,1])
out = df[m]

   id  week  value
1   1     2     29
2   1     3     49
3   1     3     19
5   2     7     99
6   2     8     53

Or we fix the tail with drop_duplicates

df.merge(df.drop_duplicates(['id','week']).groupby('id').tail(2).drop('value',1))
   id  week  value
0   1     2     29
1   1     3     49
2   1     3     19
3   2     7     99
4   2     8     53
user_79645270

Assume data have been sorted by id and week, then groupby tail will do the job

df.groupby('id').tail(2)

Revision:

(df[['id', 'week']]
   .drop_duplicates()
   .groupby('id')
   .tail(2) 
   .merge(df)
)
user_117478610
Related