Reindex MultiIndex with unique values in level

user_9537439 0
Viewed 237

I have this dataframe:

df = pd.DataFrame({'NUMBER_1': {('2019-07', 'A'): 4, ('2019-07', 'D'): 2, ('2019-08', 'A'): 32, ('2019-08', 'B'): 14, ('2019-09', 'A'): 32, ('2019-09', 'B'): 53, ('2019-09', 'C'): 54, ('2019-09', 'D'): 24},
 'NUMBER_2': {('2019-07', 'A'): 75, ('2019-07', 'D'): 12, ('2019-08', 'A'): 42, ('2019-08', 'B'): 32, ('2019-09', 'A'): 54, ('2019-09', 'B'): 21,  ('2019-09', 'C'): 97, ('2019-09', 'D'): 65}})

df:

enter image description here

Where I'm looking for this output:

enter image description here

I have seen similar questions for categorical types columns, but not for indexes and I'm looking for a way to avoid using the method reset_index() as actually I'm using four indexes and not just two as in the minimal example. Any suggestions?

pandas python
1 Answers

You can define a MultiIndex using the current MultiIndex.levels, and reindex setting fill_value to 0:

df.reindex(pd.MultiIndex.from_product(df.index.levels), fill_value=0)

              NUMBER_1  NUMBER_2
2019-07 A         4        75
        B         0         0
        C         0         0
        D         2        12
2019-08 A        32        42
        B        14        32
        C         0         0
        D         0         0
2019-09 A        32        54
        B        53        21
        C        54        97
        D        24        65
user_96986840
Related