Generate missing multi-indexes

Question

having a dataframe such as:

myFrame = pd.DataFrame(np.random.randint(1000, size=[7,4]), 
index=[['GER','GER','GER','GER','FRA','FRA','FRA'],
['Phone','Email','Chat','Other','Phone','Chat','Email']])

                0   1   2   3
GER     Phone   765 876 588 933
        Email   819 364 42  73
        Chat    954 665 317 918
        Other   692 531 312 206
FRA     Phone   272 261 426 270
        Chat    158 172 347 902
        Email   453 721 67  6

How could I easily add the missing index(es) of the inner level? E.g. you can see that GER has an "Other" index label. I'd like to add that "Other" to all countries and fill it's values with e.g. 0. There might be a third outer index (e.g. ITA), for which yet another inner-index could be found (e.g. SMS).

At the end, all countries should have exactly the same amount of indexes.

Thanks!

jezrael · Accepted Answer · 2018-09-11 09:04:07Z

Use reindex with MultiIndex.from_product created by unique values of each levels generated by MultiIndex.get_level_values:

mux = pd.MultiIndex.from_product([myFrame.index.get_level_values(0).unique(), 
                                  myFrame.index.get_level_values(1).unique()])  

print (myFrame.reindex(mux, fill_value=0))
             0    1    2    3
GER Phone  250  614  226  777
    Email  917  156  148  902
    Chat   537  665   87   75
    Other  431  203  921  572
FRA Phone  159  790  646  810
    Email  294  205  949  726
    Chat   209  895  128  282
    Other    0    0    0    0

Another solution with unstack and stack - MultiIndex is sorted:

print (myFrame.unstack(fill_value=0).stack(dropna=False))

             0    1    2    3
FRA Chat   209  895  128  282
    Email  294  205  949  726
    Other    0    0    0    0
    Phone  159  790  646  810
GER Chat   537  665   87   75
    Email  917  156  148  902
    Other  431  203  921  572
    Phone  250  614  226  777

Collectives™ on Stack Overflow

Generate missing multi-indexes

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related