Home > OS >  Pandas: Calculate for how much time a value is consecutive
Pandas: Calculate for how much time a value is consecutive

Time:01-16

I'm starting from a panads df that looks like this:

value  time  button
 10    1000   up
 8     1100   up
 4     1150   down
 1     1200   down
 6     1225   up
 5     1300   no
 5     1350   no

What I need is calculated for how long the button up or the button down were pressed

Basically don't do anything until there are consicutive "up" or "down" or "no", but when they change I need to substract from the last occurence the first one and store the results in a new df to end with something like this:

button  time
up       100
down     100
up       25
no       125

I've tried to do so with this:


df['same_as_prev'] = (df['button'] == df['button'].shift()).astype(int)


df['consec_rows'] = df['same_as_prev'].cumsum()


df_result = df.groupby(['button','consec_rows']).agg({'time': lambda x: x.iloc[-1]-x.iloc[0]})

But I'm always getting 0 as results

CodePudding user response:

You can use:

# set up grouper (consecutive values)
group = df['button'].ne(df['button'].shift()).cumsum()

# aggregate
out = (df.assign(time=df['time'].diff())
         .groupby(group, group_keys=False)
         .agg({'button': 'first', 'time': 'sum'})
         .reset_index(drop=True)
      )

Output:

  button   time
0     up  100.0
1   down  100.0
2     up   25.0
3     no  125.0

CodePudding user response:

I would do the following:

import pandas as pd
df = pd.DataFrame({
    'value': [10, 8, 4, 1, 6, 5, 5],
    'time': [1000, 1100, 1150, 1200, 1225, 1300, 1350],
    'button': ['up', 'up', 'down', 'down', 'up', 'no', 'no'],
})
initial_time = df.time.values[0]
changes = df.mask(df.button == df.button.shift(-1)).dropna()
result = (
    changes[['button']]
    .assign(time=(
        changes.time - 
        changes.time.shift(fill_value=initial_time)
    ))
    .reset_index(drop=True)
    
)

output:

  button   time
0     up  100.0
1   down  100.0
2     up   25.0
3     no  125.0
  • Related