CODEWITHSUNDEEP
pandasdataframecountpandas-groupbycumsum
JonathanMcM
JonathanMcM

Reputation: 41

Counting groups in columns in dataframe

I have a dataframe df:

     prds
0    E01
1    E02
2    E03
3    E04
4    E01
5    E02
6    E03
7    E04
8    F01
9    F02
10   F03
11   F04
12   F05

I would like to have an count on each group in the column 'prds' on another column 'match', hence:

    prds  match
0    E01    1
1    E02    2
2    E03    3
3    E04    4
4    E01    1
5    E02    2
6    E03    3
7    E04    4
8    F01    1
9    F02    2
10   F03    3
11   F04    4
12   F05    5

Any help would be greatly appreciated please. Thanking you in advance.

Upvotes: 1

Views: 26

Answers (2)

Mykola Zotko
Mykola Zotko

Reputation: 17824

You can simply extract digits:

df['match'] = df['prds'].str.extract('(\d+)').astype('int')

Output:

   prds  match
0   E01      1
1   E02      2
2   E03      3
3   E04      4
4   E01      1
5   E02      2
6   E03      3
7   E04      4
8   F01      1
9   F02      2
10  F03      3
11  F04      4
12  F05      5

Upvotes: 0

jezrael
jezrael

Reputation: 862671

If each group is possible defined by ending by 1 value is possible use Series.str.endswith with Series.cumsum and pass to GroupBy.cumcount:

df['match'] = df.groupby(df['prds'].str.endswith('1').cumsum()).cumcount() + 1
print (df)
   prds  match
0   E01      1
1   E02      2
2   E03      3
3   E04      4
4   E01      1
5   E02      2
6   E03      3
7   E04      4
8   F01      1
9   F02      2
10  F03      3
11  F04      4
12  F05      5

Upvotes: 2

Related Questions