时间戳

首页 > 解决方案 > 填写数据框中缺少的日期

python - 填写数据框中缺少的日期

问题描述

我有一个数据框，看起来像这样。

数据框

它有 8 列和 n 行。第一列是缺少天数的日期。（如 1946-01-04 等...）但也有重复项（如 1946-01-02）我想要一个代码来保持这些重复项，但还要填写缺失的日期并添加NaN到行中的其他单元格中。

我试过这个

dfx = pd.DataFrame(None, index=pd.DatetimeIndex(start=df.地震の発生日時.min(), end=df.地震の発生日時.max(), freq='D'))
df = df.apply(pd.concat([df, dfx], join='outer', axis=1))

但它只是从文件末尾添加.min()到.max()...我想将它应用到数据中，例如

Date        Time        Places  w     x      y    z
1946-01-02  14:45:00    6.8   36.3  140.1   31  3.2 1
1946-01-02  22:18:00    7.6   40.5  141.4   0   4.6 3
1946-01-02  23:29:00    6.7   36.1  139.4   39  4.3 2
1946-01-03  04:28:00    5.6   34.4  136.5   1   4.2 2
1946-01-03  04:36:00    6.5   35.5  139.5   50  3   1
1946-01-04  00:00:00    NaN   NaN   NaN     NaN NaN NaN
1946-01-06  10:56:00    8.1   41.5  143.4   51  5.2 3

顺便提一句。我无法使用inner join. 它抛出： AttributeError: 'Places' is not a valid function for 'Series' object

标签： pythonpandasdataframe

解决方案

如果第一列DatetimeIndex没有时间填充，解决方案：

print (df)
                Time  Places     w      x   y    z  col
Date                                                   
1946-01-02  14:45:00     6.8  36.3  140.1  31  3.2    1
1946-01-02  22:18:00     7.6  40.5  141.4   0  4.6    3
1946-01-02  23:29:00     6.7  36.1  139.4  39  4.3    2
1946-01-03  04:28:00     5.6  34.4  136.5   1  4.2    2
1946-01-05  04:36:00     6.5  35.5  139.5  50  3.0    1

print (df.index)
DatetimeIndex(['1946-01-02', '1946-01-02', '1946-01-02', '1946-01-03',
               '1946-01-05'],
              dtype='datetime64[ns]', name='Date', freq=None)

使用以下命令创建新的 DataFrame date_range：

dfx = pd.DataFrame(index=pd.date_range(start=df.index.min(), 
                                             end=df.index.max(), freq='D'))

print (dfx)
Empty DataFrame
Columns: []
Index: [1946-01-02 00:00:00, 1946-01-03 00:00:00, 1946-01-04 00:00:00, 1946-01-05 00:00:00]

然后使用DataFrame.join：

df = dfx.join(df)
print (df)
                Time  Places     w      x     y    z  col
1946-01-02  14:45:00     6.8  36.3  140.1  31.0  3.2  1.0
1946-01-02  22:18:00     7.6  40.5  141.4   0.0  4.6  3.0
1946-01-02  23:29:00     6.7  36.1  139.4  39.0  4.3  2.0
1946-01-03  04:28:00     5.6  34.4  136.5   1.0  4.2  2.0
1946-01-04       NaN     NaN   NaN    NaN   NaN  NaN  NaN
1946-01-05  04:36:00     6.5  35.5  139.5  50.0  3.0  1.0

如果有DatetimeIndex时间创建列DataFrame.reset_index：

print (df)
                    Places     w      x   y    z  col
DateTime                                              
1946-01-02 14:45:00     6.8  36.3  140.1  31  3.2    1
1946-01-02 22:18:00     7.6  40.5  141.4   0  4.6    3
1946-01-02 23:29:00     6.7  36.1  139.4  39  4.3    2
1946-01-03 04:28:00     5.6  34.4  136.5   1  4.2    2
1946-01-05 04:36:00     6.5  35.5  139.5  50  3.0    1

print (df.index)
DatetimeIndex(['1946-01-02 14:45:00', '1946-01-02 22:18:00',
               '1946-01-02 23:29:00', '1946-01-03 04:28:00',
               '1946-01-05 04:36:00'],
              dtype='datetime64[ns]', name='DateTime', freq=None)

df = df.reset_index()
print (df)
             DateTime  Places     w      x   y    z  col
0 1946-01-02 14:45:00     6.8  36.3  140.1  31  3.2    1
1 1946-01-02 22:18:00     7.6  40.5  141.4   0  4.6    3
2 1946-01-02 23:29:00     6.7  36.1  139.4  39  4.3    2
3 1946-01-03 04:28:00     5.6  34.4  136.5   1  4.2    2
4 1946-01-05 04:36:00     6.5  35.5  139.5  50  3.0    1

然后通过替换列中的错误值删除时间Series.str.normalize和最后：mergeDateTime

d = df['DateTime'].dt.normalize()
dfx = pd.DataFrame({'Dates':pd.date_range(start=d.min(), 
                                             end=d.max(), freq='D')})

print (dfx)
       Dates
0 1946-01-02
1 1946-01-03
2 1946-01-04
3 1946-01-05

df = dfx.merge(df.assign(Dates=d), on='Dates', how='left')
df['DateTime'] = df['DateTime'].fillna(df['Dates'])
print (df)
       Dates            DateTime  Places     w      x     y    z  col
0 1946-01-02 1946-01-02 14:45:00     6.8  36.3  140.1  31.0  3.2  1.0
1 1946-01-02 1946-01-02 22:18:00     7.6  40.5  141.4   0.0  4.6  3.0
2 1946-01-02 1946-01-02 23:29:00     6.7  36.1  139.4  39.0  4.3  2.0
3 1946-01-03 1946-01-03 04:28:00     5.6  34.4  136.5   1.0  4.2  2.0
4 1946-01-04 1946-01-04 00:00:00     NaN   NaN    NaN   NaN  NaN  NaN
5 1946-01-05 1946-01-05 04:36:00     6.5  35.5  139.5  50.0  3.0  1.0

推荐阅读