按ID选择所有有2个最近日期的行

Question

我想根据每个ID选择具有最近2个日期的所有行。每个ID的Max和Max-1日期和行数可能因ID而异。

示例数据:

data = {'id':  np.repeat((['a','b','c']), 6),
'date': ['2020-12-07', '2020-12-07','2020-12-05','2020-12-05','2020-12-04','2020-12-04',
'2021-12-07', '2021-12-07','2021-09-05','2021-09-05','2021-05-04','2021-05-04',
'2021-09-05', '2021-09-05','2021-02-05','2021-02-05','2020-12-04','2020-12-04'],
'value1': np.repeat(([10,20,30]), 6),
'value2': np.repeat(([1000,2000,3000]), 6)
}
df = pd.DataFrame(data)

所需输出:

id   date    value1  value2
0   a   2020-12-07  10  1000
1   a   2020-12-07  10  1000
2   a   2020-12-05  10  1000
3   a   2020-12-05  10  1000
4   b   2021-12-07  20  2000
5   b   2021-12-07  20  2000
6   b   2021-09-05  20  2000
7   b   2021-09-05  20  2000
8   c   2021-09-05  30  3000
9   c   2021-09-05  30  3000
10  c   2021-02-05  30  3000
11  c   2021-02-05  30  3000

我已经读到.nlargest()可以拉出最后两个日期，但我很难找到一种方法将其应用于我的用例并维护df中的其他值。

Answer 1

您可以试试groupby().nth:

df[df['date']>=df.groupby("id")["date"].transform('nth', n=2)]

输出:

id        date  value1  value2
0   a  2020-12-07      10    1000
1   a  2020-12-07      10    1000
2   a  2020-12-05      10    1000
3   a  2020-12-05      10    1000
6   b  2021-12-07      20    2000
7   b  2021-12-07      20    2000
8   b  2021-09-05      20    2000
9   b  2021-09-05      20    2000
12  c  2021-09-05      30    3000
13  c  2021-09-05      30    3000
14  c  2021-02-05      30    3000
15  c  2021-02-05      30    3000

Answer 2

您可以尝试使用" density "排名:

>>> df[df.groupby("id")["date"].transform(pd.Series.rank, ascending=False, method="dense")<=2]
id       date  value1  value2
0   a 2020-12-07      10    1000
1   a 2020-12-07      10    1000
2   a 2020-12-05      10    1000
3   a 2020-12-05      10    1000
6   b 2021-12-07      20    2000
7   b 2021-12-07      20    2000
8   b 2021-09-05      20    2000
9   b 2021-09-05      20    2000
12  c 2021-09-05      30    3000
13  c 2021-09-05      30    3000
14  c 2021-02-05      30    3000
15  c 2021-02-05      30    3000