CODEWITHSUNDEEP
pythonpandas
Cole Ragone
Cole Ragone

Reputation: 47

Adding a counter column in pd.DataFrame that resets based on a condition?

In short, I need to add a column that continues to count up until a new game is listed in the dataframe. Here is a snippet of the pd.DataFrame, df.

Month Year Game Earnings

  7  2017       ARMS   1195.00

  8  2017       ARMS     91.00

9 2017 ARMS 50.00

10 2017 ARMS 261.00

11 2017 ARMS 695.00

... ... ... ... ...

8 2021 rFactor 2 6647.79

9 2021 rFactor 2 27021.02

10 2021 rFactor 2 21020.26

11 2021 rFactor 2 11865.00

12 2021 rFactor 2 359.95

I can't figure out how to use df.loc[] to conditionally add a counter, more specifically, how to check if the next row != the current row.

This is what I have so far... I know it probably has to do with the if statement.

counter = 0

def update_month_counter(row):
    global counter
    for game in range(0, len(df)):
        if df.loc[game, df['Game']] != df.loc[game+1, df['Game']]:
            counter = 1
        else:
            counter += 1
    return counter 

df["MonthCounter"] = df.apply(update_month_counter, axis=1)
print(df)

Any help?

(ps. the dataframe has already been grouped by the game and sorted by the month and year).

Upvotes: 0

Views: 281

Answers (1)

Jason Baker
Jason Baker

Reputation: 3716

I think this is what you want using .cumcount

df["Count"] = df.groupby("Game").cumcount() + 1
print(df)

   Month  Year     Game  Earnings  Count
0      7  2017     ARMS   1195.00      1
1      8  2017     ARMS     91.00      2
2      9  2017     ARMS    261.00      3
3     10  2021  rFactor   6647.79      1
4     11  2021  rFactor  27021.02      2
5     12  2021  rFactor  21020.26      3

Upvotes: 1

Related Questions