How to ask a generated pivot table to include all column combination possibilities?

Question

When use the Python pivot tables, I would like to include all column combination possibilities. For example:

import pandas as pd 
from pandas import DataFrame 

Result ={
 'SenderUserId': ['a', 'a', 'b', 'c', 'c'], 
 'Date': ['1', '2', '2', '3', '4'], 
 'RecipientUserId': ['m', 'm', 'n', 'n', 'z'],
 'nmail':[1, 2, 3, 3,7] 
}


result = DataFrame (Result, columns = ['SenderUserId', 'Date', 'RecipientUserId', 'nmail'])

result = result.pivot_table(index=['SenderUserId'], columns =['Date', 'RecipientUserId'], values = 'nmail').stack()

print (result.head ())

will be producing the following results:

Date                            1    2    3    4
SenderUserId RecipientUserId
a            m                1.0  2.0  NaN  NaN
b            n                NaN  3.0  NaN  NaN
c            n                NaN  NaN  3.0  NaN
             z                NaN  NaN  NaN  7.0

However, what I really wanted to get was something like:

Date                            1    2    3    4
SenderUserId RecipientUserId
a            m                1.0  2.0  NaN  NaN
             n                NaN  NaN  NaN  NaN
             z                NaN  NaN  NaN  NaN
b            m                NaN  NaN  NaN  NaN
             n                NaN  3.0  NaN  NaN
             z                NaN  NaN  NaN  NaN
c            m                NaN  NaN  NaN  NaN
             n                NaN  NaN  3.0  NaN
             z                NaN  NaN  NaN  7.0

As you can see, we just add a few lines where there is no initial column match and assign them NaN. That's okay. As long as this question can be solved, I don't necessarily need to use pivot_table. Any help would be really appreciated!

score 3 · Accepted Answer · answered Aug 21 '19 at 03:28

All the extra rows you're asking for will necessarily be all NaN, so you can just add them in at the end. After doing the pivot, you can use the technique described here to "expand" the resulting index to all possible combinations.

pivot = result.pivot_table(index=['SenderUserId'], columns=['Date', 'RecipientUserId'], values='nmail').stack()
pivot.reindex(pd.MultiIndex.from_product(pivot.index.levels, names=pivot.index.names))

The result:

Date                            1    2    3    4
SenderUserId RecipientUserId                    
a            m                1.0  2.0  NaN  NaN
             n                NaN  NaN  NaN  NaN
             z                NaN  NaN  NaN  NaN
b            m                NaN  NaN  NaN  NaN
             n                NaN  3.0  NaN  NaN
             z                NaN  NaN  NaN  NaN
c            m                NaN  NaN  NaN  NaN
             n                NaN  NaN  3.0  NaN
             z                NaN  NaN  NaN  7.0

How to ask a generated pivot table to include all column combination possibilities?

1 Answers1