CODEWITHSUNDEEP
pythonpandas
Geo_J
Geo_J

Reputation: 25

How to iterate over unique values in pandas and count frequency of associated values

I have a dataframe that includes two columns of data as shown in the image below and I have added three additional columns corresponding to each of the 3 'types'.

enter image description here

My objective is the following: For each unique 'ID', I would like to count/tally the number of 'types' that occur through my df and add this value to the respective column.

Using the following line I can generate a count of each 'type' value but I am not sure how to iterate through each unique 'ID' and then add the 'type' counts to the new columns.

df.groupby('ID')['Type'].value_counts()

Thanks

Upvotes: 1

Views: 230

Answers (1)

jezrael
jezrael

Reputation: 862701

Reshape output by Series.unstack and DataFrame.join to original by ID column:

df1 = df.join(df.groupby('ID')['Type'].value_counts().unstack(fill_value=0), on='ID')

Or use crosstab with join:

df1 = df.join(pd.crosstab(df['ID'], df['Type']), on='ID')

Upvotes: 1

Related Questions