使用pandas dataframe填充日期列中缺失的行

Question

    uuid  site          ts  visit
0  +CW99  1124  2013-06-24     2
1  +CW99  1124  2013-06-26     1
2  +CW99  1124  2013-06-27     1
3  +CW99  1124  2013-06-20     1
4  +CW99  1124  2013-06-21     1
5  +CW99  1124  2013-06-24     2
6  +CW9W   956  2013-06-21     4
7  +CW9W   956  2013-06-22     2
8  +CW9W   956  2013-06-23     3
9  +CW9W   956  2013-06-24     4

我有一个传递进来的数据框长这样。我不知道有多少个独立的时间段(ts period)。因此，我想找到ts的最小值和最大值，然后扩展那些没有所有ts(每天)的行，并将visit列填充为0，同时复制其余字段。

        uuid  site          ts  visit
    0  +CW99  1124  2013-06-20     1
    1  +CW99  1124  2013-06-21     1
    2  +CW99  1124  2013-06-22     1
    3  +CW99  1124  2013-06-23     0
    4  +CW99  1124  2013-06-24     2
    5  +CW99  1124  2013-06-25     0
    6  +CW99  1124  2013-06-26     1
    7  +CW99  1124  2013-06-27     1
    8  +CW9W   956  2013-06-20     0
    9  +CW9W   956  2013-06-21     4
   10  +CW9W   956  2013-06-22     2
   11  +CW9W   956  2013-06-23     3
   12  +CW9W   956  2013-06-24     4
   13  +CW9W   956  2013-06-25     0
   14  +CW9W   956  2013-06-26     0
   15  +CW9W   956  2013-06-27     0

感谢您的选择。

- Felix Gao

Answer 1

这不是一件简单的事情。下面我将解释为什么。

首先，读入原始数据框并确保ts列的数据类型为datetime64[ns]。

# you may need to do this to get the correct dtype   
df['ts'] = df['ts'].to_datetime(df['ts'])

In [107]: df
Out[107]: 
    uuid  site                  ts  visit
0  +CW99  1124 2013-06-24 00:00:00      2
1  +CW99  1124 2013-06-26 00:00:00      1
2  +CW99  1124 2013-06-27 00:00:00      1
3  +CW99  1124 2013-06-20 00:00:00      1
4  +CW99  1124 2013-06-21 00:00:00      1
5  +CW99  1124 2013-06-24 00:00:00      2
6  +CW9W   956 2013-06-21 00:00:00      4
7  +CW9W   956 2013-06-22 00:00:00      2
8  +CW9W   956 2013-06-23 00:00:00      3
9  +CW9W   956 2013-06-24 00:00:00      4

In [106]: df.dtypes
Out[106]: 
uuid             object
site              int64
ts       datetime64[ns]
visit             int64
dtype: object

在最小值和最大值之间创建主时间。

In [110]: all_ts = pd.date_range(df['ts'].min(),df['ts'].max())

In [111]: all_ts
Out[111]: 
<class 'pandas.tseries.index.DatetimeIndex'>
[2013-06-20 00:00:00, ..., 2013-06-27 00:00:00]
Length: 8, Freq: D, Timezone: None

定义一个这样的函数

In [103]: def f(x):

              # we want all of the ``ts`` column that are not in the master time series
   .....:     adf = DataFrame(dict(ts = all_ts-Index(x['ts'])),columns=df.columns)

              # they should have visit of 0
   .....:     adf['visit'] = 0

              # first add them to the frame (x), ignoring the index
              # sort by the ts column
              # then fillforward missing values
   .....:     return x.append(adf,ignore_index=True).sort_index(by='ts').ffill()
   .....: 

应用该函数（如果需要，您也可以按uuid和site进行分组）

In [116]: df.groupby('uuid').apply(f)
Out[116]: 
          uuid  site                  ts  visit
uuid                                           
+CW99 3  +CW99  1124 2013-06-20 00:00:00      1
      4  +CW99  1124 2013-06-21 00:00:00      1
      0  +CW99  1124 2013-06-24 00:00:00      2
      5  +CW99  1124 2013-06-24 00:00:00      2
      6  +CW99  1124 2013-06-25 00:00:00      0
      1  +CW99  1124 2013-06-26 00:00:00      1
      2  +CW99  1124 2013-06-27 00:00:00      1
+CW9W 0  +CW9W   956 2013-06-21 00:00:00      4
      1  +CW9W   956 2013-06-22 00:00:00      2
      2  +CW9W   956 2013-06-23 00:00:00      3
      3  +CW9W   956 2013-06-24 00:00:00      4
      4  +CW9W   956 2013-06-25 00:00:00      0

注意：发布的框架中有重复项。不确定是否是有意为之。 我保留了这个。如果你没有重复项（在ts列中），这个问题会比较容易解决。 以下是无重复项的方法。

In [207]: def f(x):
   .....:     x = x.set_index('ts').reindex(all_ts).reset_index()
   .....:     x['visit'] = x['visit'].fillna(0)
   .....:     return x.ffill()
   .....: 

In [208]: df_no_dups.groupby('uuid').apply(f)
Out[208]: 
                      index   uuid  site  visit
uuid                                           
+CW99 0 2013-06-20 00:00:00  +CW99  1124      1
      1 2013-06-21 00:00:00  +CW99  1124      1
      2 2013-06-22 00:00:00  +CW99  1124      0
      3 2013-06-23 00:00:00  +CW99  1124      0
      4 2013-06-24 00:00:00  +CW99  1124      2
      5 2013-06-25 00:00:00  +CW99  1124      0
      6 2013-06-26 00:00:00  +CW99  1124      1
      7 2013-06-27 00:00:00  +CW99  1124      1
+CW9W 0 2013-06-20 00:00:00    NaN   NaN      0
      1 2013-06-21 00:00:00  +CW9W   956      4
      2 2013-06-22 00:00:00  +CW9W   956      2
      3 2013-06-23 00:00:00  +CW9W   956      3
      4 2013-06-24 00:00:00  +CW9W   956      4
      5 2013-06-25 00:00:00  +CW9W   956      0
      6 2013-06-26 00:00:00  +CW9W   956      0
      7 2013-06-27 00:00:00  +CW9W   956      0

这会强制所有元素都存在（注意第一个元素的NaN，因为无法在第一个元素上执行ffill）。如果你想要的话，可以删除这些。

- Jeff

---

谢谢Jeff，这个重复是无意的。如果不允许有重复的ts，如何解决这个问题？ - Felix Gao

添加了非重复项......它会得到一个包含所有日期的解决方案（但是如果不等于最小值，则某些第一项将具有NaN值）。顺便说一下，如果检测到重复项，它将引发异常。 - Jeff

除了那些NaN，它几乎是我所需要的。也许我应该按uuid和site分组，然后重置索引以摆脱NaN。 - Felix Gao

在函数“f”中，“fillna”可以这样使用：“x['uuid'] = x['uuid'].fillna(x['uuid'].unique()[0])”，以填充缺失值。 - Jeff

你也可以按照建议使用 ['uuid','site'] 进行分组。这里有很多可以玩的东西。 - Jeff