CODEWITHSUNDEEP
pythonpandas
edgar lenin lizarraga gastelum
edgar lenin lizarraga gastelum

Reputation: 69

How to count the values corresponding to each unique value in another column in a dataframe?

i have a table like this:

Car Type |  Color  |  ID
 VW      |  Blue   | 123
 VW      |  Red    | 567
 VW      |  Black  | 779
 -----------------------
 AUDI    | Silver  | 112
 AUDI    | Black   | 356
 AUDI    | White   | 224

how can i get something like this? where each row contains the count of colors for each car type?

Car Type |  Color  |  ID | Total
 VW      |  Blue   | 123 |  3
 VW      |  Red    | 567 |  3
 VW      |  Black  | 779 |  3
 -----------------------
 AUDI    | Silver  | 112 |  3
 AUDI    | Black   | 356 |  3
 AUDI    | White   | 224 |  3

Cheers...

Upvotes: 1

Views: 1400

Answers (2)

jezrael
jezrael

Reputation: 862611

Use for number of unique values per groups use GroupBy.transform with DataFrameGroupBy.nunique:

df['Total'] = df.groupby('Car Type')['Color'].transform('nunique')

Use for count values per groups use DataFrameGroupBy.size:

df['Total'] = df.groupby('Car Type')['Color'].transform('size')

Difference with changed one value:

df['Total_uniq'] = df.groupby('Car Type')['Color'].transform('nunique')
df['Total_size'] = df.groupby('Car Type')['Color'].transform('size')
print (df)
  Car Type   Color   ID  Total_uniq  Total_size
0       VW    Blue  123           2           3
1       VW    Blue  567           2           3 <- set value to Blue
2       VW   Black  779           2           3
3     AUDI  Silver  112           3           3
4     AUDI   Black  356           3           3
5     AUDI   White  224           3           3

Upvotes: 3

Celius Stingher
Celius Stingher

Reputation: 18367

Here is another option similar to Jezrael, who beat me to it!

import pandas as pd 
a = {'Car type':['VW','VW','VW','AUDI','AUDI','AUDI'],'Color':['Blue','Red','Black','Silver','Black','White'],'ID':[123,567,779,112,356,224]}
df = pd.DataFrame(a)
print(df)
df_a = df.merge(df.groupby(['Car type'],as_index=False).agg({'Color':'nunique'}),how='left',on='Car type').rename(columns={'Color_x':'Color','Color_y':'Unique_colors'})

Output:

  Car type   Color   ID  Unique_colors
0       VW    Blue  123              3
1       VW     Red  567              3
2       VW   Black  779              3
3     AUDI  Silver  112              3
4     AUDI   Black  356              3
5     AUDI   White  224              3

Upvotes: 0

Related Questions