Pandas summary table with zero values

Question

I'm trying to create a summary table with .describe() from python's pandas.

I have the follow dataframe:

df = pd.DataFrame({'Group':['Group1', 'Group1', 'Group1', 'Group2', 'Group2', 'Group2', 'Group3', 'Group3', 'Group4'],
'Cat':['Cat1', 'Cat2', 'Cat3', 'Cat4', 'Cat5', 'Cat', 'Cat7', 'Cat8', 'Cat9'],
'Value':[1230,4019,9491,9588,6402,1923,492,8589,8582]})
df

    Group   Cat Value
0   Group1  Cat1    1230
1   Group1  Cat2    4019
2   Group1  Cat3    9491
3   Group2  Cat4    9588
4   Group2  Cat5    6402
5   Group2  Cat     1923
6   Group3  Cat7    492
7   Group3  Cat8    8589
8   Group4  Cat9    8582

I want to generate a summary table grouped by Group and Cat, which all Cats that aren't in Group appear in the same way, with all values = 0.

I was trying with:

        df.groupby(['Group', 'Cat']).describe()

# That has the following output:
            Value
    count   mean    std min 25% 50% 75% max
    Group   Cat                             
    Group1  Cat1    1.0 1230.0  NaN 1230.0  1230.0  1230.0  1230.0  1230.0
            Cat2    1.0 4019.0  NaN 4019.0  4019.0  4019.0  4019.0  4019.0
            Cat3    1.0 9491.0  NaN 9491.0  9491.0  9491.0  9491.0  9491.0
    Group2  Cat     1.0 1923.0  NaN 1923.0  1923.0  1923.0  1923.0  1923.0
            Cat4    1.0 9588.0  NaN 9588.0  9588.0  9588.0  9588.0  9588.0
            Cat5    1.0 6402.0  NaN 6402.0  6402.0  6402.0  6402.0  6402.0
    Group3  Cat7    1.0 492.0   NaN 492.0   492.0   492.0   492.0   492.0
            Cat8    1.0 8589.0  NaN 8589.0  8589.0  8589.0  8589.0  8589.0
    Group4  Cat9    1.0 8582.0  NaN 8582.0  8582.0  8582.0  8582.0  8582.0

But the output I want is:

                Value
    count   mean    std min 25% 50% 75% max
    Group   Cat                             
    Group1  Cat1    1.0 1230.0  NaN 1230.0  1230.0  1230.0  1230.0  1230.0
            Cat2    1.0 4019.0  NaN 4019.0  4019.0  4019.0  4019.0  4019.0
            Cat3    1.0 9491.0  NaN 9491.0  9491.0  9491.0  9491.0  9491.0
            Cat4    0.0 0.0     NaN 0.0     0.0     0.0     0.0     0.0
            Cat5    0.0 0.0     NaN 0.0     0.0     0.0     0.0     0.0
            Cat6    0.0 0.0     NaN 0.0     0.0     0.0     0.0     0.0
            Cat7    0.0 0.0     NaN 0.0     0.0     0.0     0.0     0.0
            Cat8    0.0 0.0     NaN 0.0     0.0     0.0     0.0     0.0
            Cat9    0.0 0.0     NaN 0.0     0.0     0.0     0.0     0.0
    Group2  Cat     1.0 1923.0  NaN 1923.0  1923.0  1923.0  1923.0  1923.0
            Cat1    0.0 0.0     NaN 0.0     0.0     0.0     0.0     0.0
            Cat2    0.0 0.0     NaN 0.0     0.0     0.0     0.0     0.0
            Cat3    0.0 0.0     NaN 0.0     0.0     0.0     0.0     0.0
            Cat4    1.0 9588.0  NaN 9588.0  9588.0  9588.0  9588.0  9588.0
            Cat5    1.0 6402.0  NaN 6402.0  6402.0  6402.0  6402.0  6402.0
            Cat6    0.0 0.0     NaN 0.0     0.0     0.0     0.0     0.0
            Cat7    0.0 0.0     NaN 0.0     0.0     0.0     0.0     0.0
            Cat8    0.0 0.0     NaN 0.0     0.0     0.0     0.0     0.0
            Cat9    0.0 0.0     NaN 0.0     0.0     0.0     0.0     0.0
    Group3  Cat1    0.0 0.0     NaN 0.0     0.0     0.0     0.0     0.0
            Cat2    0.0 0.0     NaN 0.0     0.0     0.0     0.0     0.0
            Cat3    0.0 0.0     NaN 0.0     0.0     0.0     0.0     0.0
            Cat4    0.0 0.0     NaN 0.0     0.0     0.0     0.0     0.0
            Cat5    0.0 0.0     NaN 0.0     0.0     0.0     0.0     0.0
            Cat6    0.0 0.0     NaN 0.0     0.0     0.0     0.0     0.0
            Cat7    1.0 492.0   NaN 492.0   492.0   492.0   492.0   492.0
            Cat8    1.0 8589.0  NaN 8589.0  8589.0  8589.0  8589.0  8589.0
            Cat9    0.0 0.0     NaN 0.0     0.0     0.0     0.0     0.0
    Group4  Cat1    0.0 0.0     NaN 0.0     0.0     0.0     0.0     0.0
            Cat2    0.0 0.0     NaN 0.0     0.0     0.0     0.0     0.0
            Cat3    0.0 0.0     NaN 0.0     0.0     0.0     0.0     0.0
            Cat4    0.0 0.0     NaN 0.0     0.0     0.0     0.0     0.0
            Cat5    0.0 0.0     NaN 0.0     0.0     0.0     0.0     0.0
            Cat6    0.0 0.0     NaN 0.0     0.0     0.0     0.0     0.0
            Cat7    0.0 0.0     NaN 0.0     0.0     0.0     0.0     0.0
            Cat8    0.0 0.0     NaN 0.0     0.0     0.0     0.0     0.0
            Cat9    1.0 8582.0  NaN 8582.0  8582.0  8582.0  8582.0  8582.0

I want to know how to get this output.

user_9840637 · Answer

You can also create a Cartesian product index list from the index you get and reindex:

out = df.groupby(['Group', 'Cat']).describe()
idx = pd.MultiIndex.from_product((out.index.levels[0],out.index.levels[1]))
out = out.reindex(idx,fill_value=0)

            Value                                                     
            count    mean  std     min     25%     50%     75%     max
Group1 Cat    0.0     0.0  0.0     0.0     0.0     0.0     0.0     0.0
       Cat1   1.0  1230.0  NaN  1230.0  1230.0  1230.0  1230.0  1230.0
       Cat2   1.0  4019.0  NaN  4019.0  4019.0  4019.0  4019.0  4019.0
       Cat3   1.0  9491.0  NaN  9491.0  9491.0  9491.0  9491.0  9491.0
       Cat4   0.0     0.0  0.0     0.0     0.0     0.0     0.0     0.0
       Cat5   0.0     0.0  0.0     0.0     0.0     0.0     0.0     0.0
       Cat7   0.0     0.0  0.0     0.0     0.0     0.0     0.0     0.0
       Cat8   0.0     0.0  0.0     0.0     0.0     0.0     0.0     0.0
       Cat9   0.0     0.0  0.0     0.0     0.0     0.0     0.0     0.0
Group2 Cat    1.0  1923.0  NaN  1923.0  1923.0  1923.0  1923.0  1923.0
       Cat1   0.0     0.0  0.0     0.0     0.0     0.0     0.0     0.0
       Cat2   0.0     0.0  0.0     0.0     0.0     0.0     0.0     0.0
       Cat3   0.0     0.0  0.0     0.0     0.0     0.0     0.0     0.0
       Cat4   1.0  9588.0  NaN  9588.0  9588.0  9588.0  9588.0  9588.0
       Cat5   1.0  6402.0  NaN  6402.0  6402.0  6402.0  6402.0  6402.0
       Cat7   0.0     0.0  0.0     0.0     0.0     0.0     0.0     0.0
       Cat8   0.0     0.0  0.0     0.0     0.0     0.0     0.0     0.0
       Cat9   0.0     0.0  0.0     0.0     0.0     0.0     0.0     0.0
Group3 Cat    0.0     0.0  0.0     0.0     0.0     0.0     0.0     0.0
       Cat1   0.0     0.0  0.0     0.0     0.0     0.0     0.0     0.0
       ....................................
       ...............................

user_7964527 · Answer

Check with unstack + stack, notice I also recommend keep the row value as NaN not fill by 0

out = df.groupby(['Group', 'Cat']).describe().unstack().stack(dropna=False)

Pandas summary table with zero values

2 Answers