填补Pandas中缺失的行[重复]

of1yzvn4  于 12个月前  发布在  其他
关注(0)|答案(3)|浏览(88)

此问题在此处已有答案

pandas or python equivalent of tidyr complete(4个回答)
28天前关闭
我有一个Pandas框架d1像:

date         key   value
0   2023-12-01   K0    9
1   2023-12-03   K1    3
2   2023-12-04   K0    10
3   2023-12-01   K1    8

字符串
我如何有效地估算与缺失日期相关的行,以获得一个像这样的嵌套框架:

date         key   value
0   2023-12-01   K0    9
1   2023-12-02   K0    0
2   2023-12-03   K0    0
3   2023-12-04   K0    10
4   2023-12-01   K1    8
5   2023-12-02   K1    0
6   2023-12-03   K1    3
7   2023-12-04   K1    0

uxh89sit

uxh89sit1#

尝试:

def fn(g, mx, mn):
    r = pd.date_range(mn, mx)
    g["date"] = pd.to_datetime(g["date"])
    g = g.set_index("date").reindex(r).reset_index()
    g["key"] = g["key"].ffill().bfill()
    g["value"] = g["value"].fillna(0).astype(int)
    return g

mx, mn = df["date"].max(), df["date"].min()
out = df.groupby("key", group_keys=False).apply(fn, mx=mx, mn=mn)
print(out)

字符串
印刷品:

index key  value
0 2023-12-01  K0      9
1 2023-12-02  K0      0
2 2023-12-03  K0      0
3 2023-12-04  K0     10
0 2023-12-01  K1      8
1 2023-12-02  K1      0
2 2023-12-03  K1      3
3 2023-12-04  K1      0

o8x7eapl

o8x7eapl2#

您可以使用预先创建的Mapdf执行merge

target = pd.merge(pd.date_range(df.date.min(),df.date.max()).to_frame(name = 'date'),
                  df[['key']].drop_duplicates(),
                  how='cross')

target = target.merge(df, how = 'left').fillna(0).sort_values(['key'])
        date key  value
0 2023-12-01  K0    9.0
2 2023-12-02  K0    0.0
4 2023-12-03  K0    0.0
6 2023-12-04  K0   10.0
1 2023-12-01  K1    8.0
3 2023-12-02  K1    0.0
5 2023-12-03  K1    3.0
7 2023-12-04  K1    0.0

字符串

34gzjxbg

34gzjxbg3#

另一种可能的解决方案,基于pandas.DataFrame.stackpandas.DataFrame.unstack

(df.set_index(['date', 'key']).unstack(fill_value=0)
 .asfreq('D', fill_value=0).stack().sort_index(level=1).reset_index())

字符串
输出量:

date key  value
0 2023-12-01  K0      9
1 2023-12-02  K0      0
2 2023-12-03  K0      0
3 2023-12-04  K0     10
4 2023-12-01  K1      8
5 2023-12-02  K1      0
6 2023-12-03  K1      3
7 2023-12-04  K1      0

相关问题