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

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?

CodePudding user response：

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.)