Pandas:将每个小组的前2个小时设置为NAN

Question

我试图通过在每个"状态"组的前2个小时将'值设置为NAN来清洁我的数据。

我的数据帧看起来像这样：

>>> import pandas as pd
>>> import numpy as np
>>> 
>>> rng = pd.date_range('1/1/2016', periods=6, freq='H')
>>> 
>>> data = {'value': np.random.rand(len(rng)), 
...         'state': ['State 1']*3 + ['State 2']*3}
>>> df = pd.DataFrame(data, index=rng)
>>> 
>>> df
                       state     value
2016-01-01 00:00:00  State 1  0.800798
2016-01-01 01:00:00  State 1  0.130290
2016-01-01 02:00:00  State 1  0.464372
2016-01-01 03:00:00  State 2  0.925445
2016-01-01 04:00:00  State 2  0.732331
2016-01-01 05:00:00  State 2  0.811541

我想出了三种方法，两者都不起作用：

1）首次尝试使用.loc和/或.ix导致没有更改：

>>> df.loc[df.state=='State 2'].first('2H').value = np.nan
>>> df.ix[df.state=='State 2'].first('2H').value = np.nan
>>> df
                       state     value
2016-01-01 00:00:00  State 1  0.800798
2016-01-01 01:00:00  State 1  0.130290
2016-01-01 02:00:00  State 1  0.464372
2016-01-01 03:00:00  State 2  0.925445
2016-01-01 04:00:00  State 2  0.732331
2016-01-01 05:00:00  State 2  0.811541

2）第二次尝试导致错误：

>>> df.loc[df.state=='State 2', 'value'].first('2H') = np.nan
  File "<stdin>", line 1
SyntaxError: can't assign to function call

3）这是一种有效的黑客尝试，但显然不鼓励：

>>> temp = df.loc[df.state=='State 2']
>>> temp.first('2H').value = np.nan
/home/user/anaconda3/lib/python3.5/site-packages/pandas/core/generic.py:2698: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead
See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
  self[name] = value
>>> df.loc[df.state=='State 2'] = temp
>>> df
                       state     value
2016-01-01 00:00:00  State 1  0.800798
2016-01-01 01:00:00  State 1  0.130290
2016-01-01 02:00:00  State 1  0.464372
2016-01-01 03:00:00  State 2       NaN
2016-01-01 04:00:00  State 2       NaN
2016-01-01 05:00:00  State 2  0.811541

理想情况下，我想确定一种简单的方法，使每个组循环并清洁各自数据组的开始和结束。我的印象是。首先和。

使用.loc没有考虑到这些时间字符串格式，但我可能缺少某些内容。

在大熊猫中这样做的真实方法是什么？

Answer 1

第一个2H查找所有indexes，然后将index更改为Multiindex，swaplevel，用于匹配ix和最后一个reset_index：

idx = df.groupby('state')['value'].apply(lambda x: x.first('2H')).index
df.set_index('state', append=True, inplace=True)
df = df.swaplevel(0,1)
df.ix[idx,'value'] = np.nan
print (df.reset_index(level=0))
                       state     value
2016-01-01 00:00:00  State 1       NaN
2016-01-01 01:00:00  State 1       NaN
2016-01-01 02:00:00  State 1  0.406512
2016-01-01 03:00:00  State 2       NaN
2016-01-01 04:00:00  State 2       NaN
2016-01-01 05:00:00  State 2  0.226350