Is there a python function that would allow me to add an extra layer to cross tab?-CodePudding

I am trying to create a table that would imitate the following table from excel.

My Data is: Col1 Col2 Col3 A Red Cheetah A Red Cheetah A Red Cheetah A Blue Cheetah A Blue Cheetah A Blue Cheetah A Blue Cheetah A Blue Cheetah B Blue Cheetah B Blue Cheetah C Blue Cheetah C Blue Cheetah C Blue Lion C Blue Lion C Orange Lion C Orange Lion A Orange Lion A Orange Lion A Orange Lion A Orange Lion A Red Lion A Red Lion A Red Bear A Red Bear A Red Bear B Red Bear B Green Bear B Green Bear C Green Bear C Green Bear C Green Bear

I tried separating the data frame into smaller data frames based on the col3 but I would like it all to still be one table as pictured above

CodePudding user response：

Using crosstab

df = pd.crosstab(
    index=[df.Col1, df.Col3],
    columns=df.Col2,
    rownames=["Row Labels", "Column Labels"],
    colnames=["Count of Col1"],
    margins=True,
    margins_name="Grand Total"
)

print(df)

OutPut:

Count of Col1              Blue  Green  Orange  Red  Grand Total
Row Labels  Column Labels                                       
A           Bear              0      0       0    3            3
            Cheetah           5      0       0    3            8
            Lion              0      0       4    2            6
B           Bear              0      2       0    1            3
            Cheetah           2      0       0    0            2
C           Bear              0      3       0    0            3
            Cheetah           2      0       0    0            2
            Lion              2      0       2    0            4
Grand Total                  11      5       6    9           31

CodePudding user response：

Read your data from excel to python using pandas.read_excel. Then create pivot table using pandas.pivot_table. There are decent examples in the docs that look just like your problem. Good luck.