How to count string in columns using pandas?

Question

I have following dataset.

game_id h_abbr a_abbr 
0001    WSH    TOR 
0002    ANA    TOR 
0003    TOR    MIN

I want to count how many games each team has played so far. Output should be like this

game_id h_abbr a_abbr ht_game_no at_game_no
0001    WSH    TOR    1          1
0002    ANA    TOR    1          2
0003    TOR    MIN    3          1

jezrael · Accepted Answer · 2021-03-30 07:05:38Z

3

Use DataFrame.set_index with DataFrame.stack for MultiIndex Series, then use GroupBy.cumcount for counter, reshape by Series.unstack, add rename and add to original DataFrame by DataFrame.join:

s = df.set_index('game_id')[['h_abbr','a_abbr']].stack()

d = {'h_abbr':'ht_game_no','a_abbr':'at_game_no'}
df = df.join(s.groupby(s).cumcount().add(1).unstack().rename(columns=d), on='game_id')
print (df)
   game_id h_abbr a_abbr  ht_game_no  at_game_no
0        1    WSH    TOR           1           1
1        2    ANA    TOR           1           2
2        3    TOR    MIN           3           1

edited Mar 30, 2021 at 7:05

answered Mar 30, 2021 at 6:41

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

5 Comments

user8060120 Over a year ago

sorry without description it is not so clear

jezrael Over a year ago

@BrownBear - I looking for better solution, now add desc.

tdy Over a year ago

What's s.groupby(s)? I never saw groupby self before.

jezrael Over a year ago

@tdy - It is trick for groupby by Series if need grouping by their values.

jezrael Over a year ago

@tdy - But it is rare use.

Collectives™ on Stack Overflow

How to count string in columns using pandas?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related