Counting duplicate and unique values within groups in pandas

Question

Suppose I have a dataframe:

DF1:
Class | Age | City        | Color
  A   | 20  | Los Angeles | Blue
  A   | 20  | Los Angeles | Blue
  A   | 20  | Los Angeles | Red
  B   | 25  | Phoenix     | Yellow

I'd like to get a unique count of every duplicate and unique value so the output looks like this:

DF2:
Class | Age | City        | Color   | Count
  A   | 20  | Los Angeles | Blue    |  2
  A   | 20  | Los Angeles | Red     |  1
  B   | 25  | Phoenix     | Yellow  |  1

In this case, Class A, Age 20, City Los Angeles, and Color Blue shows up twice. I've tried using nunique but my output did not collapse duplicate vales together.

df = df.groupby(['Class', 'Age', 'City', 'Color']).nunique()

What did df.groupby(['Class', 'Age', 'City', 'Color']).nunique() return? — Jordan Singer
– Jordan Singer, Commented Jan 16, 2019 at 18:19

Dani Mesejo · Accepted Answer · 2019-01-16 18:25:32Z

1

You could use size:

result = df.groupby(['Class', 'Age', 'City', 'Color']).size().reset_index(name='Count')
print(result)

Output

  Class  Age         City   Color  Count
0     A   20  Los Angeles    Blue      2
1     A   20  Los Angeles     Red      1
2     B   25      Phoenix  Yellow      1

answered Jan 16, 2019 at 18:25

Dani Mesejo

62.2k6 gold badges56 silver badges86 bronze badges

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

Collectives™ on Stack Overflow

Counting duplicate and unique values within groups in pandas

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