I am currently processing some large time-series data with pandas, and I have a function that is intolerably slow, and I'm sure it could be done faster.
The problem is: I'm studying a factory that produces things. It runs continuously throughout the week, but on weekends it shuts down. Before the end of the week, and at the start of a new one, the factory behaves differently, which interferes with the analysis that I'm doing, and so I want to filter out a time window around these weekends.
I have a large dataframe, call it df
, whose rows are the articles produced and the columns are their various attributes, one of which is the time at which it was produced, df['timeProduced'
]. These articles are produced at irregularly-spaced points in time.I want to discard the rows in the table whose timeProduced
entry was near one of these shutdown periods. The actual data is confidential, but it looks similar to this:
index partId colour timeProduced \ ...
1 '026531|352' Red 2017-02-01 00:00:02
2 '026531|353' Blue 2017-02-01 00:00:03
3 '026531|354' Blue 2017-02-01 00:00:05
4 '026531|355' Green 2017-02-01 00:00:09
This takes tens of minutes to crunch through a million entries. I know it's slow because it isn't at all vectorized, but I'm not sure how to do a pure vectorized numpy/pandas implementation. Any ideas?
def dropIrregularGaps(series, gapLength, runIn):
'''
Designed for time-series data where there is points sampled at irregular time intervals.
Detects adjacent points that are sampled too far apart, and then removes points on either
side of the gap which are within a defined runIn period.
Assumes timeseries data is already sorted. If not, will deliver garbage.
series is a pandas series object, with values as pandas DateTime objects.
gapLength is the amount of time that is considered to be a shutdown
runIn is the length of time to remove on either side of the gap.
returns a list of indices that are valid
'''
samples = list(series)
indices = list(series.index)
prev = samples[0]
ind = indices[0]
allGoodIndices = []
currentGoodIndices = [ind]
currentGoodTimes = [prev]
skipPoint = None
for new, ind in zip(samples[1:], indices[1:]):
if skipPoint:
if new - skipPoint >= runIn:
# if a gap has been detected, skip over all points until the current
# point is past the run-in period.
skipPoint = None
currentGoodIndices = [ind]
currentGoodTimes = [new]
elif new - prev > gapLength:
# if a gap is detected. cut out the cooldown period from the list,
# and add what remains to the list of goodIndices.
endPoint = currentGoodTimes[-1]
while currentGoodTimes and (endPoint - currentGoodTimes[-1] < runIn):
del (currentGoodTimes[-1])
del (currentGoodIndices[-1])
allGoodIndices += currentGoodIndices
currentGoodIndices = []
currentGoodTimes = []
skipPoint = new
else:
currentGoodIndices += [ind]
currentGoodTimes += [new]
prev = new
allGoodIndices += currentGoodIndices
return allGoodIndices
I operate this function by taking my dataframe, and running:
result = dropIrregularGaps(df['timeProduced'],pd.Timedelta('4 hours'), pd.Timedelta('8 hours 0 minutes'))
I then use the result to index into the dataframe, giving me the dataframe without the startup/cooldown periods.
df = df.loc[result]