如何将每组中的行从第一行切片到包含特定值的行？

Question

我有这样一个数据帧：

df_1 = pd.DataFrame({
    'ID' : ['A', 'A', 'A', 'A', 'A', 'B', 'B', 'B', 'B', 'C', 'C', 'C', 'C', 'C'],
    'VAL' : ['shoes', 'flowers', 'chairs', 'apples', 'dice', 'shoes', 'apples',
             'curtain', 'sand', 'socks', 'necklacs', 'tables', 'dishes', 'apples'],
    'SEQ' : [0, 1, 2, 3, 4, 0, 1, 2, 3, 0, 1, 2, 3, 4]
})

   ID       VAL  SEQ
0   A     shoes    0
1   A   flowers    1
2   A    chairs    2
3   A    apples    3
4   A      dice    4
5   B     shoes    0
6   B    apples    1
7   B   curtain    2
8   B      sand    3
9   C     socks    0
10  C  necklacs    1
11  C    tables    2
12  C    dishes    3
13  C    apples    4

我想切片到某个值的行，例如，切片到ID的每个apple组的所有行：

Out[110]: 
   ID       VAL  SEQ
0   A     shoes    0
1   A   flowers    1
2   A    chairs    2
3   A    apples    3
4   B     shoes    0
5   B    apples    1
6   C     socks    0
7   C  necklacs    1
8   C    tables    2
9   C    dishes    3
10  C    apples    4

Answer 1

idxmax，groupby，concat

pd.concat([
    d.loc[:d.VAL.eq('apples').idxmax()]
    for _, d in df_1.groupby('ID')
])

   ID       VAL  SEQ
0   A     shoes    0
1   A   flowers    1
2   A    chairs    2
3   A    apples    3
5   B     shoes    0
6   B    apples    1
9   C     socks    0
10  C  necklacs    1
11  C    tables    2
12  C    dishes    3
13  C    apples    4

Answer 2

我正在使用transform

df_1[df_1.index<=df_1.VAL.eq('apples').groupby(df_1['ID']).transform('idxmax')]
Out[856]: 
   ID       VAL  SEQ
0   A     shoes    0
1   A   flowers    1
2   A    chairs    2
3   A    apples    3
5   B     shoes    0
6   B    apples    1
9   C     socks    0
10  C  necklacs    1
11  C    tables    2
12  C    dishes    3
13  C    apples    4

Answer 3

GroupBy.cumsum是你的朋友：

mask = (df_1['VAL'].eq('apples')
                   .shift()
                   .astype(float)
                   .groupby(df_1['ID'])
                   .cumsum()
                   .lt(1))
df_1[mask]

   ID       VAL  SEQ
1   A   flowers    1
2   A    chairs    2
3   A    apples    3
5   B     shoes    0
6   B    apples    1
9   C     socks    0
10  C  necklacs    1
11  C    tables    2
12  C    dishes    3
13  C    apples    4

---

如果ID可能以您要查找的术语结尾，那么上面的shift解决方案（虽然方便）将是不合适的。将GroupBy.apply与cumsum一起使用：

mask = (df_1['VAL'].eq('apples')
                   .groupby(df_1['ID'])
                   .apply(lambda x: x.shift().fillna(0).cumsum())
                   .lt(1))
df_1[mask]

   ID       VAL  SEQ
1   A   flowers    1
2   A    chairs    2
3   A    apples    3
5   B     shoes    0
6   B    apples    1
9   C     socks    0
10  C  necklacs    1
11  C    tables    2
12  C    dishes    3
13  C    apples    4