使用重复索引重塑 Pandas 数据框并填充缺失的行

Question

我想添加与给定索引对应的缺失行。

例如，如果我有：

df = pd.DataFrame({"date": ["1", "2", "1", "3"],

                   "name": ["bob", "bob", "anne", "anne"],

                   "x": [1, 2, 2, 3],

                   "y": [2, 4, 5, 5]})

我想获得以下内容：

    name    date    x   y

    anne    1       2   5

    anne    2       NA  NA   <- because date 2 is missing for Anne

    anne    3       3   5

    bob     1       1   2

    bob     2       2   4

    bob     3       NA  NA   <- because date 3 is missing for Bob

我用 pivot_table、pivot 尝试了很多东西，但到目前为止还想不通。

df.pivot_table(index = ["name", "date"], values = ['x','y'], fill_value=0).reset_index()

没有填充缺失的行。

Answer 1

DataFrame.set_index&nbsp;与DataFrame.unstack,DataFrame.stack和一起使用DataFrame.reset_index：df = df.set_index(["name", "date"]).unstack().stack(dropna=False).reset_index()print (df)&nbsp; &nbsp;name date&nbsp; &nbsp; x&nbsp; &nbsp; y0&nbsp; anne&nbsp; &nbsp; 1&nbsp; 2.0&nbsp; 5.01&nbsp; anne&nbsp; &nbsp; 2&nbsp; NaN&nbsp; NaN2&nbsp; anne&nbsp; &nbsp; 3&nbsp; 3.0&nbsp; 5.03&nbsp; &nbsp;bob&nbsp; &nbsp; 1&nbsp; 1.0&nbsp; 2.04&nbsp; &nbsp;bob&nbsp; &nbsp; 2&nbsp; 2.0&nbsp; 4.05&nbsp; &nbsp;bob&nbsp; &nbsp; 3&nbsp; NaN&nbsp; NaN您的解决方案是可能的相同方式：df = df.pivot_table(index = ["name", "date"], values = ['x','y'], fill_value=0).unstack().stack(dropna=False).reset_index()print (df)&nbsp; &nbsp;name date&nbsp; &nbsp; x&nbsp; &nbsp; y0&nbsp; anne&nbsp; &nbsp; 1&nbsp; 2.0&nbsp; 5.01&nbsp; anne&nbsp; &nbsp; 2&nbsp; NaN&nbsp; NaN2&nbsp; anne&nbsp; &nbsp; 3&nbsp; 3.0&nbsp; 5.03&nbsp; &nbsp;bob&nbsp; &nbsp; 1&nbsp; 1.0&nbsp; 2.04&nbsp; &nbsp;bob&nbsp; &nbsp; 2&nbsp; 2.0&nbsp; 4.05&nbsp; &nbsp;bob&nbsp; &nbsp; 3&nbsp; NaN&nbsp; NaN

Answer 2

另一种方法Multiindex.from_product是reindex：idx = pd.MultiIndex.from_product((df['date'].unique(),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;df['name'].unique()),names=['date','name'])df.set_index(['date','name']).reindex(idx).sort_index(level=1).reset_index()&nbsp; date&nbsp; name&nbsp; &nbsp; x&nbsp; &nbsp; y0&nbsp; &nbsp; 1&nbsp; anne&nbsp; 2.0&nbsp; 5.01&nbsp; &nbsp; 2&nbsp; anne&nbsp; NaN&nbsp; NaN2&nbsp; &nbsp; 3&nbsp; anne&nbsp; 3.0&nbsp; 5.03&nbsp; &nbsp; 1&nbsp; &nbsp;bob&nbsp; 1.0&nbsp; 2.04&nbsp; &nbsp; 2&nbsp; &nbsp;bob&nbsp; 2.0&nbsp; 4.05&nbsp; &nbsp; 3&nbsp; &nbsp;bob&nbsp; NaN&nbsp; NaN