我如何删除条件列值上的整日行.. pandas [英] how can i delete whole day rows on condition column values.. pandas

问题描述

我具有以下时间序列数据帧

i have below times series data frames

我想根据条件删除行(每天检查一次):选中aaa> 100，然后删除整天的行(在下面，删除所有2015-12-01行，因为aaa列的最后3个值是1000)

i wanna delete rows on condtion (check everyday) : check aaa>100 then delete all day rows (in belows, delete all 2015-12-01 rows because aaa column last 3 have 1000 value)

     ....
   date       time    aaa
2015-12-01,00:00:00,0
2015-12-01,00:15:00,0
2015-12-01,00:30:00,0
2015-12-01,00:45:00,0
2015-12-01,01:00:00,0
2015-12-01,01:15:00,0
2015-12-01,01:30:00,0
2015-12-01,01:45:00,0
2015-12-01,02:00:00,0
2015-12-01,02:15:00,0
2015-12-01,02:30:00,0
2015-12-01,02:45:00,0
2015-12-01,03:00:00,0
2015-12-01,03:15:00,0
2015-12-01,03:30:00,0
2015-12-01,03:45:00,0
2015-12-01,04:00:00,0
2015-12-01,04:15:00,0
2015-12-01,04:30:00,0
2015-12-01,04:45:00,0
2015-12-01,05:00:00,0
2015-12-01,05:15:00,0
2015-12-01,05:30:00,0
2015-12-01,05:45:00,0
2015-12-01,06:00:00,0
2015-12-01,06:15:00,0
2015-12-01,06:30:00,1000
2015-12-01,06:45:00,1000
2015-12-01,07:00:00,1000
         ....

我该怎么办?

推荐答案

我认为，如果MultiIndex首先根据条件比较aaa的值，然后通过

I think you need if MultiIndex first compare values of aaa by condition and then filter all values in first level by boolean indexing, last filter again by isin with inverted condition by ~:

print (df)
                      aaa
date       time          
2015-12-01 00:00:00     0
           00:15:00     0
           00:30:00     0
           00:45:00     0
2015-12-02 05:00:00     0
           05:15:00   200
           05:30:00     0
           05:45:00     0
2015-12-03 06:00:00     0
           06:15:00     0
           06:30:00  1000
           06:45:00  1000
           07:00:00  1000

lvl0 = df.index.get_level_values(0)
idx = lvl0[df['aaa'].gt(100)].unique()
print (idx)
Index(['2015-12-02', '2015-12-03'], dtype='object', name='date')

df = df[~lvl0.isin(idx)]
print (df)
                     aaa
date       time         
2015-12-01 00:00:00    0
           00:15:00    0
           00:30:00    0
           00:45:00    0

如果第一列不是索引，则仅比较列date:

And if first column is not index only compare column date:

print (df)
          date      time   aaa
0   2015-12-01  00:00:00     0
1   2015-12-01  00:15:00     0
2   2015-12-01  00:30:00     0
3   2015-12-01  00:45:00     0
4   2015-12-02  05:00:00     0
5   2015-12-02  05:15:00   200
6   2015-12-02  05:30:00     0
7   2015-12-02  05:45:00     0
8   2015-12-03  06:00:00     0
9   2015-12-03  06:15:00     0
10  2015-12-03  06:30:00  1000
11  2015-12-03  06:45:00  1000
12  2015-12-03  07:00:00  1000

idx = df.loc[df['aaa'].gt(100), 'date'].unique()
print (idx)
['2015-12-02' '2015-12-03']

df = df[~df['date'].isin(idx)]
print (df)
         date      time  aaa
0  2015-12-01  00:00:00    0
1  2015-12-01  00:15:00    0
2  2015-12-01  00:30:00    0
3  2015-12-01  00:45:00    0

这篇关于我如何删除条件列值上的整日行.. pandas 的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！