I have a dataframe 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?

Thanks in advanced!

CodePudding user response：

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)

CodePudding user response：

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: