I'm analyzing a time series, and based on certain criteria, I can pick out rows that are either the start or the end of the events. At this point, my series looks something like this (I've left out some repetitive values for brevity):
The Setup
import numpy as np
import pandas
from pandas import Timestamp
datadict = {'event': {
Timestamp('2010-01-01 00:20:00', tz=None): 'event start',
Timestamp('2010-01-01 00:30:00', tz=None): '--',
Timestamp('2010-01-01 00:40:00', tz=None): '--',
Timestamp('2010-01-01 00:50:00', tz=None): '--',
Timestamp('2010-01-01 01:00:00', tz=None): '--',
Timestamp('2010-01-01 01:10:00', tz=None): 'event end',
Timestamp('2010-01-01 01:20:00', tz=None): '--',
Timestamp('2010-01-01 02:20:00', tz=None): '--',
Timestamp('2010-01-01 02:30:00', tz=None): 'event start',
Timestamp('2010-01-01 02:40:00', tz=None): '--',
Timestamp('2010-01-01 02:50:00', tz=None): '--',
Timestamp('2010-01-01 03:00:00', tz=None): '--',
Timestamp('2010-01-01 03:10:00', tz=None): '--',
Timestamp('2010-01-01 03:20:00', tz=None): '--',
Timestamp('2010-01-01 03:30:00', tz=None): 'event end',
}}
data = pandas.DataFrame.from_dict(datadict)
event
2010-01-01 00:20:00 event start
2010-01-01 00:30:00 --
2010-01-01 00:40:00 --
2010-01-01 00:50:00 --
2010-01-01 01:00:00 --
2010-01-01 01:10:00 event end
2010-01-01 01:20:00 --
2010-01-01 02:20:00 --
2010-01-01 02:30:00 event start
2010-01-01 02:40:00 --
2010-01-01 02:50:00 --
2010-01-01 03:00:00 --
2010-01-01 03:10:00 --
2010-01-01 03:20:00 --
2010-01-01 03:30:00 event end
Here's what I would like to achieve (ideally without for
loops)
event event number
2010-01-01 00:20:00 event start 1
2010-01-01 00:30:00 -- 1
2010-01-01 00:40:00 -- 1
2010-01-01 00:50:00 -- 1
2010-01-01 01:00:00 -- 1
2010-01-01 01:10:00 event end 1
2010-01-01 01:20:00 -- NA
2010-01-01 02:20:00 -- NA
2010-01-01 02:30:00 event start 2
2010-01-01 02:40:00 -- 2
2010-01-01 02:50:00 -- 2
2010-01-01 03:00:00 -- 2
2010-01-01 03:10:00 -- 2
2010-01-01 03:20:00 -- 2
2010-01-01 03:30:00 event end 2
2010-01-01 03:40:00 -- NA
2010-01-01 03:50:00 -- NA
Here's what I've tried
With some optimistic assumptions about the quality of my data, I can get Event Numbers like this:
table = data[data.event != '--'].reset_index()
table['event number'] = 1 + np.floor(table.index / 2)
table = table.set_index('index')
event event number
index
2010-01-01 00:20:00 event start 1
2010-01-01 01:10:00 event end 1
2010-01-01 02:30:00 event start 2
2010-01-01 03:30:00 event end 2
I can then join
that to my original dataframe, and fillna
with the method='ffill'
data2 = data.join(table[['event number']])
data2['filled'] = data2['event number'].fillna(method='ffill')
event event number filled
2010-01-01 00:20:00 event start 1 1
2010-01-01 00:30:00 -- NaN 1
2010-01-01 00:40:00 -- NaN 1
2010-01-01 00:50:00 -- NaN 1
2010-01-01 01:00:00 -- NaN 1
2010-01-01 01:10:00 event end 1 1
2010-01-01 01:20:00 -- NaN 1 # <- d'oh
2010-01-01 02:20:00 -- NaN 1 # <- d'oh
2010-01-01 02:30:00 event start 2 2
2010-01-01 02:40:00 -- NaN 2
2010-01-01 02:50:00 -- NaN 2
2010-01-01 03:00:00 -- NaN 2
2010-01-01 03:10:00 -- NaN 2
2010-01-01 03:20:00 -- NaN 2
2010-01-01 03:30:00 event end 2 2
The Problem
As you can see, the time between events (01:20 through 02:20) is being associated with event #1.
The Question
Is there anyway to skip over these sections without looping?
See Question&Answers more detail:
os