Pandas count occurrences

Question

Hi I have datamodel like this:

Number    Type
123       A
123       B
455       B
455       A
455       C

and I need to make some kind of a pivot table like this:

Number    A    B    C    SUM
123       1    1    0    2
455       1    1    1    3

Tried with pandas pivot table but with no positive results

Thank you

Then you need to see your data for the correct column name, is it `Type` or `Type ` or `Type `. — Quang Hoang, May 05 '23 at 16:17
Number collumn is int64 (dtype), Type collumn is object (dtype) — NC11, May 05 '23 at 16:24
Then there's no reason for `KeyError: "Type"`, you can try: `pd.crosstab(df.iloc[:, 0], df.iloc[:, 1])`... — Quang Hoang, May 05 '23 at 16:34
Thank you for your answer, now it works. Maybe a some kind of typo on first try... — NC11, May 05 '23 at 16:35

score 1 · Answer 1 · answered May 05 '23 at 16:07

1

You can do a crosstab then assign the count of unique Number to a new SUM column

out = (pd.crosstab(df['Number'], df['Type'])
       .assign(SUM=df['Number'].value_counts())
       .reset_index())

print(out)

Type  Number  A  B  C  SUM
0        123  1  1  0    2
1        455  1  1  1    3

answered May 05 '23 at 16:07

Ynjxsjmh

1 Answers1