根据pandas中的前一小时数据添加缺失日期的数据

Question

我有一个如下所示的数据框：-

^{tb1}$

缺少日期的日期如下所示：

2021-01-23 2021-01-25

我想用之前的日期填充2021-01-23和2021-01-25的行。例如，应考虑22日的人力资源数据。 我有一个巨大的数据集，其中日期的全部数据可能会丢失两个小时。 日期也可以从未来的日期范围生成。2021-02-01 18:00:00至2021-02-02 18:00:00的示例

更新的数据框应如下所示：-

^{tb2}$

日期范围可以是最后7天

请帮我满足这个要求

谢谢

Answer 1

这是fill values的延续

• 生成由采样小时数和实例组合而成的DF（df2）
• 这将生成15行，因为instanceA有3次，instanceB有2次，跨越3个日期（2+3）*3
• 然后使用相同的技术填充CPULoad和合成memload
• 针对熊猫1.0.1和1.2.0进行测试

import pandas as pd
import io
import datetime as dt
import numpy as np
df = pd.read_csv(io.StringIO("""id  creTimestamp    CPULoad instnceId
0   2021-01-22 18:00:00 22.0    instanceA
1   2021-01-22 19:00:00 22.0    instanceA
2   2021-01-22 20:00:00 23.0    instanceB
3   2021-01-23 18:00:00 24.0    instanceA
4   2021-01-23 20:00:00 22.0    instanceA
5   2021-01-24 18:00:00 23.0    instanceB
6   2021-01-24 20:00:00 23.5    instanceA
"""), sep="\t", index_col=0)

df.creTimestamp = pd.to_datetime(df.creTimestamp)
df["memload"] = np.random.random(len(df))

# generate a DF for each time in instance in each date
df2 = (pd.merge(
    # for each time in instance
    df.assign(timestamp=df.creTimestamp.dt.time)
        .loc[:,["instnceId","timestamp"]]
        .drop_duplicates()
        .assign(foo=1),
    # for each date
    df.creTimestamp.dt.date.drop_duplicates().to_frame().assign(foo=1),
    on="foo"
).assign(creTimestamp=lambda dfa: dfa.apply(lambda r: dt.datetime.combine(r["creTimestamp"], r["timestamp"]), axis=1))
 .drop(columns="foo")
       # merge values back..
 .merge(df, on=["creTimestamp", "instnceId"], how="left")
)

# now get values to fill NaN
df2 = (df2.merge(df2.dropna().drop_duplicates(subset=["instnceId","timestamp"], keep="last"),
         on=["timestamp","instnceId"], suffixes=("","_pre"))
 .assign(CPULoad=lambda dfa: dfa.CPULoad.fillna(dfa.CPULoad_pre))
 .assign(memload=lambda dfa: dfa.memload.fillna(dfa.memload_pre))

)

输出

    instnceId timestamp        creTimestamp  CPULoad    creTimestamp_pre  CPULoad_pre
0   instanceA  18:00:00 2021-01-22 18:00:00     22.0 2021-01-23 18:00:00         24.0
1   instanceA  18:00:00 2021-01-23 18:00:00     24.0 2021-01-23 18:00:00         24.0
2   instanceA  18:00:00 2021-01-24 18:00:00     24.0 2021-01-23 18:00:00         24.0
3   instanceA  19:00:00 2021-01-22 19:00:00     22.0 2021-01-22 19:00:00         22.0
4   instanceA  19:00:00 2021-01-23 19:00:00     22.0 2021-01-22 19:00:00         22.0
5   instanceA  19:00:00 2021-01-24 19:00:00     22.0 2021-01-22 19:00:00         22.0
6   instanceB  20:00:00 2021-01-22 20:00:00     23.0 2021-01-22 20:00:00         23.0
7   instanceB  20:00:00 2021-01-23 20:00:00     23.0 2021-01-22 20:00:00         23.0
8   instanceB  20:00:00 2021-01-24 20:00:00     23.0 2021-01-22 20:00:00         23.0
9   instanceA  20:00:00 2021-01-22 20:00:00     23.5 2021-01-24 20:00:00         23.5
10  instanceA  20:00:00 2021-01-23 20:00:00     22.0 2021-01-24 20:00:00         23.5
11  instanceA  20:00:00 2021-01-24 20:00:00     23.5 2021-01-24 20:00:00         23.5
12  instanceB  18:00:00 2021-01-22 18:00:00     23.0 2021-01-24 18:00:00         23.0
13  instanceB  18:00:00 2021-01-23 18:00:00     23.0 2021-01-24 18:00:00         23.0
14  instanceB  18:00:00 2021-01-24 18:00:00     23.0 2021-01-24 18:00:00         23.0