Python Pandas - 根据多个日期在多个列中用NAN替换值？

Question

我有一个数据表，里面记录了多个实体在不同时间的观察数据。这个数据表的索引是时间序列，虽然是独特的，但时间间隔不规则。

数据表的一部分看起来像这样：

DATE    ('ACTION', 111, 1/7/2010)   ('ACTION', 222, 1/5/2010)
1/1/2010    10                          5
1/2/2010    10                          5
1/3/2010    10                          5
1/4/2010    15                          5
1/5/2010    10                          5
1/6/2010    10                          5
1/7/2010    10                          5
1/8/2010    10                          5

这个元组是一个层级索引。在这个元组中，值1是一个类别，值2是一个ID，值3是事件日期。我想用这个事件日期减去1天作为最大日期，然后把这个日期之后的值替换成NaN。

处理后的数据表应该是这样的：

DATE    ('ACTION', 111, 1/7/2010)   ('ACTION', 222, 1/5/2010)
1/1/2010    10                          5
1/2/2010    10                          5
1/3/2010    10                          5
1/4/2010    15                          5
1/5/2010    10                          NaN
1/6/2010    10                          NaN
1/7/2010    NaN                         NaN
1/8/2010    NaN                         NaN

这个数据表可能会有多达100000列。我知道如何在一列中替换值，可能是用布尔掩码来实现。但我不太明白如何在多列上高效地做到这一点。

这样做的原因是为了确保观察数据是在事件日期之前的。任何帮助都会非常感激。

Answer 1

我相信可能有更好的方法来做这件事，但用三行代码就能完成这个任务。

In [194]:

A=(np.array(pd.to_datetime(df['DATE']))[...,np.newaxis]+12*60*12*10**10)>\
   np.array([np.datetime64(pd.to_datetime(item[-1])) for item in df.columns.tolist()[1:]])
B=np.hstack((np.ones(len(df)).reshape((-1,1))!=1, A))
print df.where(~B)

#       DATE  (ACTION, 111, 1/7/2010)  (ACTION, 222, 1/5/2010)
#0  1/1/2010                       10                        5
#1  1/2/2010                       10                        5
#2  1/3/2010                       10                        5
#3  1/4/2010                       15                        5
#4  1/5/2010                       10                      NaN
#5  1/6/2010                       10                      NaN
#6  1/7/2010                      NaN                      NaN
#7  1/8/2010                      NaN                      NaN

#[8 rows x 3 columns]

我假设你的 DATE 列是以 string（字符串）的形式存储的，而且你列名中的每个元组的最后一个项目也是以 string 存储的。如果这两者都是这样的话，你需要在第一行进行转换，否则你可以省略一些。

补充说明：这个运行得比较慢，100 loops, best of 3: 4.55 ms per loop。

Answer 2

也许速度不是特别快，但这已经是基于pandas的一种更简洁的方法：

df.where(df.apply(lambda x: x.index < pd.Timestamp(x.name[2])))

apply这个函数会返回一个包含真/假值的数据表（<这个表达式会对每一列进行评估，而x.name[2]则选择该列名称的第三层），而where会把假值替换成NaN。

完整示例：

In [1]: import pandas as pd

In [2]: from StringIO import StringIO

In [3]: s = """,ACTION,ACTION
   ...: ,111,222
   ...: ,1/7/2010,1/5/2010
   ...: DATE,,
   ...: 1/1/2010,    10,                          5
   ...: 1/2/2010,    10,                          5
   ...: 1/3/2010,    10,                          5
   ...: 1/4/2010,    15,                          5
   ...: 1/5/2010,    10,                          5
   ...: 1/6/2010,    10,                          5
   ...: 1/7/2010,    10,                          5
   ...: 1/8/2010,    10,                          5"""

In [4]: df = pd.read_csv(StringIO(s), header=[0,1,2], index_col=0, parse_dates=True)

In [5]: df.where(df.apply(lambda x: x.index < pd.Timestamp(x.name[2])))
Out[5]:
              ACTION
                 111       222
            1/7/2010  1/5/2010
DATE
2010-01-01        10         5
2010-01-02        10         5
2010-01-03        10         5
2010-01-04        15         5
2010-01-05        10       NaN
2010-01-06        10       NaN
2010-01-07       NaN       NaN
2010-01-08       NaN       NaN