In pandas, how to reindex(fill 0) in level 2 in multiindex-CodePudding

I have a dataframe with 2 level of index: month and rating. The rating should be 1,2,3 (not to be confused with the columns 1,2,3). I found that for some months, the rating could be missing. E.g, (Population and 2021-10) only has rating 1,2. I need every month to have ratings 1,2,3. So I need to fill 0 for the missing rating index. How can I do it?

I've tried reindex([1,2,3],level='rating'), but does not work. I've spend hours trying. Please help. Thanks!

CodePudding user response：

You can use pd.MultiIndex.from_product to create a full index:

>>> df
                             1         2         3
(Population)       1  0.436954  0.897747  0.387058
                   2  0.464940  0.611953  0.133941
2021-08(Refreshed) 1  0.496111  0.282798  0.048384
                   2  0.163582  0.213310  0.504647
                   3  0.008980  0.651175  0.400103

>>> df.reindex(pd.MultiIndex.from_product(df.index.levels), fill_value=0)
                             1         2         3
(Population)       1  0.436954  0.897747  0.387058
                   2  0.464940  0.611953  0.133941
                   3  0.000000  0.000000  0.000000  # New record
2021-08(Refreshed) 1  0.496111  0.282798  0.048384
                   2  0.163582  0.213310  0.504647
                   3  0.008980  0.651175  0.400103