0

I am trying to create a new data frame from an existing data frame with the count of existing columns in the data frame. Let's assume I have following DataFrame

cus_Type ID ID_type
R 100 X
S 100 Y
R 101 X
S 102 Y
P 103 X
B 104 Y
P 105 X
B 106 Y
K 108 D

I would like to create a new data Frame from the above with following 'count' of cus_Type ,ID_type like below:

cus_Type ID_type count
R X 2
S Y 2
P X 2
B Y 2
K D 1

I am trying to use pd.groupby(['cus_Type','ID_type']) but not sure how to bring count as a new column in the existing data frame.

An73798
  • 1
  • 1

1 Answers1

1

Some options:

df.value_counts(['cus_Type', 'ID_type'], sort=False).reset_index(name='count')

Or:

df.groupby(['cus_Type', 'ID_type'], sort=False, as_index=False).agg(count=('ID', 'count'))

Or:

df.groupby(['cus_Type', 'ID_type'], sort=False)['ID'].count().reset_index(name='count')

output:

  cus_Type ID_type  count
0        R       X      2
1        S       Y      2
2        P       X      2
3        B       Y      2
4        K       D      1
mozway
  • 194,879
  • 13
  • 39
  • 75