pd.pivot_table(df, index = col1, columns = col2, value = ?)

I want the values to be the count of values in col 1.The values in col 1 are all strings. I basically want to imitate what is happening in the excel file pictured below

Id be open to using other functions than pd.pivot_table() if that would make things easier

pd.pandas(index = col1, columns = col2, values = col1)? Im not sure how to engage this.

Col1    Col2
A   Red
A   Red
A   Red
A   Blue
A   Blue
A   Blue
A   Blue
A   Blue
B   Blue
B   Blue
C   Blue
C   Blue
C   Blue
C   Blue
C   Orange
C   Orange
A   Orange
A   Orange
A   Orange
A   Orange
A   Red
A   Red
A   Red
A   Red
A   Red
B   Red
B   Green
B   Green
C   Green
C   Green
C   Green

Would it be possible to do something like this

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

CodePudding user response：

Try to use pd.crosstab. To make the total columns use .sum() (with proper axis=)

x = pd.crosstab(df.Col1, df.Col2)
x["Grand Total"] = x.sum(axis=1)
x = pd.concat([x, x.sum().to_frame().rename(columns={0: "Grand Total"}).T])
x.columns.name, x.index.name = None, None

print(x.to_markdown())

Prints: