Home > Blockchain >  Can we make the values in pandas.pivot_table() a count of column?
Can we make the values in pandas.pivot_table() a count of column?

Time:11-17

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

enter image description here

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 enter image description here

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:

Blue Green Orange Red Grand Total
A 5 0 4 8 17
B 2 2 0 1 5
C 4 3 2 0 9
Grand Total 11 5 6 9 31
  • Related