groupby cumsum (or cumcount) with cyclical data

I have a dataframe that looks like

Column 'SWITCH' is cyclical data and I'd like to count the number of ON and OFF for each cycle like this:

I'd tried cumsum or cumcount but it didn't reset the count when the next 'ON' cycle has come (it keeps counting on the number from the previous cycle).

What can I do?

Try put in the cumsum of the difference as well:

switch_blocsk = df['SWITCH'].ne(df['SWITCH'].shift()).cumsum()

df['cum.count'] = df.groupby(['ID', switch_blocks]).cumcount().add(1)

You need to create a new column which indicates the change in the 'SWITCH' column, then you can use 'groupby' to perform the cumulative count.

import pandas as pd

# Create sample data
df = pd.DataFrame({'ID': ['A'] * 9 + ['B'] * 9,
                   'SWITCH': ['ON'] * 3 + ['OFF'] * 3 + ['ON'] * 3 + ['ON'] * 3 + ['OFF'] * 3 + ['OFF'] * 3})

df['SWITCH_CHANGE'] = (df['SWITCH'] != df['SWITCH'].shift()).astype(int)

df['Cum. Count'] = df.groupby(['ID', df.SWITCH_CHANGE.cumsum()])['SWITCH'].cumcount() + 1

print(df)

Result: