python - 删除满足针对多列评估的条件的 DataFrame 的行

Question

我想过滤我的 DataFrame通过针对 DataFrame 的几列评估一些条件.我用下面的例子说明了我想做什么:

df = {'user': [1,1,1,2,2,2],
      'speed':[10,20,90,15,39, 10],
      'acceleration': [9.8,29,5,4,7, 3],
      'jerk':[50,60,60,40,20,-50],
      'mode':['car','car','car','metro','metro', 'metro']}


df = pd.DataFrame.from_dict(df)
df
    user  speed   acceleration  jerk  mode
0     1     10           9.8    50    car
1     1     20          29.0    60    car
2     1     90           5.0    60    car
3     2     15           4.0    40  metro
4     2     39           7.0    20  metro
5     2     10           3.0   -50  metro

在给定的示例中，我想根据针对 speed, acceleration 设置的阈值过滤数据框和 jerk列如下表:

+-------+-------+--------------+------+-----+
|       | speed | acceleration |    jerk    |
+-------+-------+--------------+------+-----+
|       | max   |    max       | min  | max |
| ---   | ---   |    ---       | ---  | --- |
| car   | 50    |    10        | -100 | 100 |
| metro | 35    |    5         | 60   | -40 |
+-------+-------+--------------+------+-----+

所以只有用户的 speed & acceleration下方 max以及用户的 jerk内 min-max被选中(或删除不满足规定条件的行)。

Answer 1

您可以使用 reindex ，然后执行 msk:

threshold=threshold.reindex(df['mode'])

threshold=threshold.reset_index(drop=True)

msk=(df.acceleration.lt(threshold['acceleration','max']))&\
    (df.speed.lt(threshold['speed','max']))&\
    (df.jerk.ge(threshold['jerk','min'])&\
     df.jerk.le(threshold['jerk','max']))
df[msk]

详情
以这个阈值数据框为例:

threshold=pd.DataFrame({'s':['car','car','metro','metro'],
                        'acceleration':[10,5,5,2],
                       'speed':[50,5,35,2],
                       'jerk':[-100,100,60,-40]})
threshold=threshold.groupby('s').agg({'acceleration':'max',
                                 'speed':'max',
                                 'jerk':['min','max']})

threshold
#      acceleration speed jerk     
#               max   max  min  max
#s                                 
#car             10    50 -100  100
#metro            5    35  -40   60

您可以使用 'mode'列制作 reindex :

threshold=threshold.reindex(df['mode'])
#      acceleration speed jerk     
#               max   max  min  max
#mode                              
#car             10    50 -100  100
#car             10    50 -100  100
#car             10    50 -100  100
#metro            5    35  -40   60
#metro            5    35  -40   60
#metro            5    35  -40   60

threshold=threshold.reset_index(drop=True)

msk=(df.acceleration.lt(threshold['acceleration','max']))&\
    (df.speed.lt(threshold['speed','max']))&\
    (df.jerk.ge(threshold['jerk','min'])&\
     df.jerk.le(threshold['jerk','max']))

df[msk]
#   user  speed  acceleration  jerk   mode
#0     1     10           9.8    50    car
#3     2     15           4.0    40  metro