pythonpandaspivot-table

How to get multiple metrics per category value in pandas crosstab?


My current pandas crosstab is repeating the crosstab laterally (column wise) per metric. I would like to have the metrics repeated row-wise per row column category instead. The below illustration (current output vs desired output) shows the structural change I'm after. Appreciate your advice.

Here's my code: pd.crosstab([df.var1,df.var2], [df.var3], values=df.values_var, aggfunc=[len, np.mean])

Here's my illustration:

enter image description here


Solution

  • You only need df.stack(0):

    Create Input dataframe:

    df = pd.DataFrame([[79, 83,58,22,42,74],[72,48,29,68,77,48],[59,34,89,54,56,61],[48,41,70,33,40,56]])
    df.index = pd.MultiIndex.from_product([['A','B'],['Car1','Car2']], names=['Row Variable 1', 'Row Variable 2'])
    cols = pd.MultiIndex.from_product([['Count', 'Mean'],[1,2,3]], names=['Metrics','Column Varible 1'])
    df = df.set_axis(cols, axis=1)
    

    Input Dataframe:

    Metrics                       Count         Mean        
    Column Varible 1                  1   2   3    1   2   3
    Row Variable 1 Row Variable 2                           
    A              Car1              79  83  58   22  42  74
                   Car2              72  48  29   68  77  48
    B              Car1              59  34  89   54  56  61
                   Car2              48  41  70   33  40  56
    

    Now, let's stack level 0 of column muliindex header:

    df.stack(0)
    

    Output:

    Column Varible 1                        1   2   3
    Row Variable 1 Row Variable 2 Metrics            
    A              Car1           Count    79  83  58
                                  Mean     22  42  74
                   Car2           Count    72  48  29
                                  Mean     68  77  48
    B              Car1           Count    59  34  89
                                  Mean     54  56  61
                   Car2           Count    48  41  70
                                  Mean     33  40  56