Pandas: Create missing combination rows with zero values

Question

Let's say I have a dataframe df:

df = pd.DataFrame({'col1': [1,1,2,2,2], 'col2': ['A','B','A','B','C'], 'value': [2,4,6,8,10]})

    col1 col2 value
0   1    A    2
1   1    B    4
2   2    A    6
3   2    B    8
4   2    C    10

I'm looking for a way to create any missing rows among the possible combination of col1 and col2 with exiting values, and fill in the missing rows with zeros

The desired result would be:

    col1 col2 value
0   1    A    2
1   1    B    4
2   2    A    6
3   2    B    8
4   2    C    10
5   1    C    0    <- Missing the "1-C" combination, so create it w/ value = 0

I've looked into using stack and unstack to make this work, but I'm not sure that's exactly what I need.

Thanks in advance

Answer 1

Use pivot , then stack

df.pivot(*df.columns).fillna(0).stack().to_frame('values').reset_index()
Out[564]: 
   col1 col2  values
0     1    A     2.0
1     1    B     4.0
2     1    C     0.0
3     2    A     6.0
4     2    B     8.0
5     2    C    10.0