-1

With the pandas dataframe below (which is a set of results, per team, ordered chronologically)

Team Result
TeamA Win
TeamA Loss
TeamA Win
TeamB Loss
TeamB Loss
TeamC Loss
TeamC Win
TeamC Win
TeamC Loss
TeamC Loss

I want to iterate through the dataset and add a new column ("GameNumber") which denotes whether a game is the 1st, 2nd etc. game of each team's season.

i.e. the GameNumber value continues to increment until it find a row where the "Team" value differs from the row above. When it finds such a row, it resets the counter and begins counting from '1' again.

  • Sample desired result
Team Result GameNumber
TeamA Win 1
TeamA Loss 2
TeamA Win 3
TeamB Loss 1
TeamB Loss 2
TeamC Loss 1
TeamC Win 2
TeamC Win 3
TeamC Loss 4
TeamC Loss 5

Many thanks!

I've tried various pandas functions (incl. explode & groupby) but these don't seem to meet the need.

Philby_Walsh
  • 21
  • 3

1 Answers1

0

Use groupby.cumcount and a mask with where:

df['GameNumber'] = (df.groupby('Team').cumcount().add(1)
                      .where(df['Result'].eq('Loss'), '')
                    )

Output:

    Team Result GameNumber
0  TeamA    Win           
1  TeamA   Loss          2
2  TeamA    Win           
3  TeamB   Loss          1
4  TeamB   Loss          2
5  TeamC   Loss          1
6  TeamC    Win           
7  TeamC    Win           
8  TeamC   Loss          4
9  TeamC   Loss          5
mozway
  • 194,879
  • 13
  • 39
  • 75
  • Thanks for sharing mozway. However, as you can see, the output produced by your code suggestion does not match the "Sample desired result" table above. However this code: df2["GameNumber"] = df2.groupby(['Team']).cumcount()+1 from the item below meets the need exactly https://stackoverflow.com/questions/23435270/add-a-sequential-counter-column-on-groups-to-a-pandas-dataframe – Philby_Walsh Feb 28 '23 at 10:14
  • @Philby_Walsh this is because your [original question's formatting](https://stackoverflow.com/revisions/84e143e2-671f-4d96-a0d7-1f393bc852c2/view-source) displayed the data in a way that suggested that you only wanted values when the Result was Loss, which you then fixed after I answered ;) – mozway Feb 28 '23 at 10:20
  • That explains it, many thanks Mozway! – Philby_Walsh Mar 01 '23 at 09:54