Python pandas: Add a column to my dataframe that counts a variable

Question

I have a dataframe 'gt' like this:

org     group
org1      1
org2      1
org3      2
org4      3
org5      3
org6      3

and I would like to add column 'count' to gt dataframe to counts number member of the groups, expected results like this:

org     group   count
org1      1       2
org2      1       2
org3      2       1
org4      3       3
org5      3       3
org6      3       3

I know how to do it per one item of the group, but do not know how to make the count repeated for all of the group items, here is the code I have used:

gtcounts = gt.groupby('group').count()

This question includes this one. The other link has more responses and therefore might be also helpful for those who landed here. — J Agustin Barrachina
– J Agustin Barrachina, Commented Feb 20, 2023 at 16:04

Linda · Accepted Answer · 2017-06-08 12:29:27Z

122

Call transform this will return a Series aligned with the original df:

In [223]:

df['count'] = df.groupby('group')['group'].transform('count')
df
Out[223]:
    org  group  count
0  org1      1      2
1  org2      1      2
2  org3      2      1
3  org4      3      3
4  org5      3      3
5  org6      3      3

edited Jun 8, 2017 at 12:29

Linda

6574 silver badges14 bronze badges

answered Apr 22, 2015 at 8:35

EdChum

397k204 gold badges836 silver badges583 bronze badges

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

2 Comments

skan Over a year ago

Would it be possible to get the same result with apply() or map()?

J Agustin Barrachina Over a year ago

Take a look here

cottontail · Accepted Answer · 2023-10-04 03:35:37Z

0

It can also be done with a combination of value_counts() and map as well. Basically, the idea is to find the counts of each group; then map these counts back to the groups.

df['count'] = df['group'].map(df['group'].value_counts())

# or
df['count'] = df['group'].map(df.groupby('group')['group'].count())

Also groupby.transform can be used with size() as well.

df['count'] = df.groupby('group').transform('size')

Both of these methods produce the following transformation.

If you arrived here looking for a way to assign the per-group counter as a column, then use groupby.cumcount().

df['count'] = df.groupby('group').cumcount()+1

edited Oct 4, 2023 at 3:35

answered Mar 17, 2023 at 22:29

cottontail

25.6k25 gold badges184 silver badges176 bronze badges

Collectives™ on Stack Overflow

Python pandas: Add a column to my dataframe that counts a variable

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related