Python 根据条件按以上 5 行过滤数据框

发布于08月08日

我有一大堆东西，按人和约会时间分类.我想要将DF缩短到购买水果的行数和每人购买水果之前的最多5行数.如果5行中有一行是水果购买，那么我只需要提取上面的行，直到最后一次购买水果.

fruits = ["apple", "banana", "pear"]
  date    pesron  item
10:00      amy    apple
10:01      amy    pear
10:02      amy    ipad       (6 rows above 10:08 banana purchase, so delete)
10:03      amy    headphones
10:04      amy    missing
10:05      amy    laptop
10:06      amy    unknown
10:07      amy    table
10:08      amy    banana   (one of the 5 rows above 10:10 banana purchase is also a fruit so stop here)
10:09      amy    unknown
10:10      amy    banana
...        ben

预期yields

10:00      amy    apple
10:01      amy    pear
10:03      amy    headphones
10:04      amy    missing
10:05      amy    laptop
10:06      amy    unknown
10:07      amy    table
10:08      amy    banana
10:09      amy    unknown
10:10      amy    banana

我试过了

m1= df.item.isin(fruits)
df.loc[m1, df.iloc[idx-5,:]]

推荐答案

您可以按交换顺序创建帮助组，方法是使用累积总和对行进行索引，然后对原始行以上的5行使用GroupBy.tail:

print (df)
     date pesron        item
0   10:00    amy       apple
1   10:01    amy        pear
2   10:02    amy        ipad
3   10:03    amy  headphones
4   10:04    amy     missing
5   10:05    amy      laptop
6   10:06    amy     unknown
7   10:07    amy       table
8   10:08    amy      banana
9   10:09    amy     unknown
10  10:10    amy         ban <- change row
11  10:09    ben     unknown <- added row

fruits = ["apple", "banana", "pear"]

#swap rows and test for mmbership of list
m = df.iloc[::-1].item.isin(fruits)
#group by column and helper Series by cumulative sum of mask
g = m.groupby([df['pesron'], m.cumsum()])

#remove all rows after last match
mask1 = g.cummax()

#counter of rows per groups
mask2 = g.cumcount().lt(7).iloc[::-1]

#chain masks
df = df[mask1 & mask2]
print (df)
    date pesron        item
0  10:00    amy       apple
1  10:01    amy        pear
2  10:02    amy        ipad
3  10:03    amy  headphones
4  10:04    amy     missing
5  10:05    amy      laptop
6  10:06    amy     unknown
7  10:07    amy       table
8  10:08    amy      banana