How to pivot df to get counts of column values into new columns

Question

I have a df containing data on cars, I've changed the df to only include 2 of its fields for this purpose:

df =

Company    Type
Audi       SUV
BMW        Coupe
Audi       Saloon
Mercedes   SUV
Mercedes   Saloon
...

I want to pivot the df and get counts of each "type" for each company. So the output would look like this:

Company   SUV    Coupe    Saloon
Audi      1      0        1
BMW       0      1        0
Mercedes  1      0        1

So far I've used this code to try get the output I need but it's not quite working:

df = df.pivot_table(values='Company', index=df.index, columns='Type', aggfunc='first')

What would be the most efficient way of doing this?

Mustafa Aydın · Accepted Answer · 2022-09-05 16:04:50Z

3

you're looking for pd.crosstab

In [127]: pd.crosstab(df.Company, df.Type)
Out[127]:
Type      Coupe  SUV  Saloon
Company
Audi          0    1       1
BMW           1    0       0
Mercedes      0    1       1

(Now, "Type" is the name of the columns, "Company" is the name of the index.)

edited Sep 5, 2022 at 16:04

answered Sep 5, 2022 at 15:41

Mustafa Aydın

18.4k4 gold badges21 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to pivot df to get counts of column values into new columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related