How can I get the positions of a group?

Question

I have pandas dataframe like this

data = [[1, 'a'], [2, 'a'], [3, 'b'], [4, 'b'], [5, 'a'], [6, 'c']]
df1 = pd.DataFrame(data, columns=['Id', 'Group'])

Id    Group
 1      a
 2      a
 3      b
 4      b
 5      a
 6      c

Without changing order I need to get the position of every Id based on the `Group.

Basically, I want below output

Id    Group   position
 1      a         1
 2      a         2
 3      b         1
 4      b         2
 5      a         3
 6      c         1

Does https://stackoverflow.com/questions/33899369/ranking-order-per-group-in-pandas help? — Karl Knechtel, Oct 09 '21 at 07:07
@KarlKnechtel Actually, it helps me at a range. However, both questions have few differences but helpful. Thanks for mentioning that. — Kalana, Oct 09 '21 at 07:15

score 3 · Accepted Answer · answered Oct 09 '21 at 07:01

3

try, transform + cumcount

df1['position'] = df1.groupby('Group').transform('cumcount') + 1

   Id Group  position
0   1     a         1
1   2     a         2
2   3     b         1
3   4     b         2
4   5     a         3
5   6     c         1

answered Oct 09 '21 at 07:01

sushanth

8,275
3
17
28

SeaBean · Answer 2 · 2021-10-09T12:12:13.750

You can simply do it by .cumcount

df1['position'] = df1.groupby('Group').cumcount() + 1

GroupBy.cumcount numbers each item in each group from 0 to the length of that group - 1. It is NOT an aggregated function producing condensed result. So no need to use .transform() to propagate aggregated result back to each item of the whole group.

Result:

print(df1)

   Id Group  position
0   1     a         1
1   2     a         2
2   3     b         1
3   4     b         2
4   5     a         3
5   6     c         1

How can I get the positions of a group?

2 Answers2