Pandas flatten a time-series dataframe on same activity but different timestamps

user_15718345 0
Viewed 95

I'm looking to flatten certain processes. Basically looking at duplicates that are right after each other. Let's say I have a dataframe:

d = {'time': [12-08-2020, 13-08-2020, 14-08-2020, 15-08-2020, 16-08-2020], 'state': [off, on, on, on, off]}
df = pd.DataFrame(data=d)

Then I would use time.shift() to create the "time_end" column. Basically the next rows time. result:

         time state    time_end
0  12-08-2020   off  13-08-2020
1  13-08-2020    on  14-08-2020
2  14-08-2020    on  15-08-2020
3  15-08-2020    on  16-08-2020
4  16-08-2020   off         NaN

My question is now, how do I flatten it so that it becomes in actuality 3 lines like this:

         time state    time_end
0  12-08-2020   off  13-08-2020
1  13-08-2020    on  16-08-2020
4  16-08-2020   off         NaN

For my code I dont need repeat on's if they are followed by another on. Any help would be appreciated.

pandas time-series python
2 Answers

We can get the grouping of consecutive same state by .shift() + .ne() + .cumsum().

Then, for each group (of consecutive same state), we get the first entry of time and last entry of time_end using .groupby() + .agg(), as follows:

df['state_group'] = df['state'].ne(df['state'].shift()).cumsum()

df_out = df.groupby('state_group').agg({'time': 'first', 'state': 'first', 'time_end': 'last'}).reset_index(drop=True)

Result:

print(df_out)

         time state    time_end
0  12-08-2020   off  13-08-2020
1  13-08-2020    on  16-08-2020
2  16-08-2020   off        None

Just for information, the following interim dataframe is created with the grouping of consecutive same state after the first line of codes above. We based on this grouping to aggregate the desired flattened result.

         time state    time_end  state_group
0  12-08-2020   off  13-08-2020            1
1  13-08-2020    on  14-08-2020            2
2  14-08-2020    on  15-08-2020            2
3  15-08-2020    on  16-08-2020            2
4  16-08-2020   off         NaN            3
user_150706970

We can filter the DataFrame based on where the current row's state value does not equal the next row's state value, then create the time_end column by shifting back the filtered time column:

import pandas as pd

df = pd.DataFrame(data={
    'time': ['12-08-2020', '13-08-2020', '14-08-2020', '15-08-2020',
             '16-08-2020'],
    'state': ['off', 'on', 'on', 'on', 'off']
})

new_df = df[df['state'].ne(df['state'].shift())].reset_index(drop=True)
new_df['time_end'] = new_df['time'].shift(-1)

new_df:

         time state    time_end
0  12-08-2020   off  13-08-2020
1  13-08-2020    on  16-08-2020
2  16-08-2020   off         NaN
user_154978880
Related