I have a PANDAS DataFrame that contains sensor data that is recorded every hour (sample included below). It is important to note that every hour is not necessarily in the dataframe, as sometimes the sensor is down and nothing is recorded. This data is going to be used as input into a predictive model, but before that happens, I need to first figure out a way to following:
- If failure switches from 0 to 1, keep only the first row where failure = 1, and remove rows until failure switches back to 0.
- Create a new variable that shifts the time when the failure happens up by 2 hours and remove records from when new failure indicator starts until the period until the original failure ends.
In order to solve this problem, below are the steps I have taken. However, I realize that it is probably not ideal and could use a lot of improvement.
import pandas as pd
# load into Pandas
df = pd.DataFrame(data)
# convert index to datetime value
df.index = pd.to_datetime(df.index)
# find changes in failure
df['failure_change'] = df.Failure.diff()
# find datetimes where multiple failures happen in a row and drop
df = df.drop(df[(df['Failure'] == 1) & (df['failure_change'] != 1.0)].index)
# find index values for when the failure happened
fail_dt = df[df['Failure'] == 1].index
# subtract 2 hours from the datetime of each failure
new_fail_dts = set()
for dt in fail_dt:
new_fail_dts.add((dt - pd.to_timedelta(2, unit='h'), dt))
# go through df and create new failure indicator if datetime is in range leading up to failures as identified above
df['new_fail'] = 0
for i in df.index:
for dt in new_fail_dts:
if dt[0] <= i <= dt[1]:
df.set_value(i, 'new_fail', 1)
# look for when new_fail changes and only keep first one
df['new_fail_change'] = df.new_fail.diff()
# find datetimes where multiple new failures happen in a row and keep first
df = df.drop(df[(df['new_fail'] == 1) & (df['new_fail_change'] == 0)].index)
Example Data in Current Format
+----------------------+----------+------------+
| | Failure | Speed |
+----------------------+----------+------------+
| 2015-01-01 00:00:00 | 0 | 0.000000 |
| 2015-01-01 01:00:00 | 0 | 63.094019 |
| 2015-01-01 02:00:00 | 1 | 90.006264 |
| 2015-01-01 03:00:00 | 0 | 42.412872 |
| 2015-01-01 04:00:00 | 0 | 0.000000 |
| 2015-01-01 05:00:00 | 0 | 0.000000 |
| 2015-01-01 06:00:00 | 0 | 81.793235 |
| 2015-01-01 07:00:00 | 0 | 56.533471 |
| 2015-01-01 08:00:00 | 0 | 152.326947 |
| 2015-01-01 09:00:00 | 0 | 238.293261 |
| 2015-01-01 10:00:00 | 1 | 1.220514 |
| 2015-01-01 11:00:00 | 1 | 17.038855 |
| 2015-01-01 12:00:00 | 1 | 13.485625 |
| 2015-01-01 13:00:00 | 0 | 69.488021 |
| 2015-01-01 14:00:00 | 0 | 0.000000 |
| 2015-01-01 15:00:00 | 0 | 84.858909 |
| 2015-01-01 16:00:00 | 0 | 20.160277 |
| 2015-01-01 17:00:00 | 0 | 0.000000 |
| 2015-01-01 18:00:00 | 0 | 0.000000 |
| 2015-01-01 19:00:00 | 0 | 90.718714 |
| 2015-01-01 20:00:00 | 0 | 164.629853 |
| 2015-01-01 21:00:00 | 0 | 0.000000 |
| 2015-01-01 22:00:00 | 1 | 82.629209 |
| 2015-01-01 23:00:00 | 1 | 24.913644 |
+----------------------+----------+------------+
Data Stored as Dict
data = {'Failure': {('2015-01-01 00:00:00'): 0,
('2015-01-01 01:00:00'): 0,
('2015-01-01 02:00:00'): 1,
('2015-01-01 03:00:00'): 0,
('2015-01-01 04:00:00'): 0,
('2015-01-01 05:00:00'): 0,
('2015-01-01 06:00:00'): 0,
('2015-01-01 07:00:00'): 0,
('2015-01-01 08:00:00'): 0,
('2015-01-01 09:00:00'): 0,
('2015-01-01 10:00:00'): 1,
('2015-01-01 11:00:00'): 1,
('2015-01-01 12:00:00'): 1,
('2015-01-01 13:00:00'): 0,
('2015-01-01 14:00:00'): 0,
('2015-01-01 15:00:00'): 0,
('2015-01-01 16:00:00'): 0,
('2015-01-01 17:00:00'): 0,
('2015-01-01 18:00:00'): 0,
('2015-01-01 19:00:00'): 0,
('2015-01-01 20:00:00'): 0,
('2015-01-01 21:00:00'): 0,
('2015-01-01 22:00:00'): 1,
('2015-01-01 23:00:00'): 1},
'Speed': {('2015-01-01 00:00:00'): 0.0,
('2015-01-01 01:00:00'): 63.094018515337844,
('2015-01-01 02:00:00'): 90.006264149818463,
('2015-01-01 03:00:00'): 42.412872151481686,
('2015-01-01 04:00:00'): 0.0,
('2015-01-01 05:00:00'): 0.0,
('2015-01-01 06:00:00'): 81.7932352541048,
('2015-01-01 07:00:00'): 56.533470911782281,
('2015-01-01 08:00:00'): 152.32694722397184,
('2015-01-01 09:00:00'): 238.29326083823594,
('2015-01-01 10:00:00'): 1.220514306517468,
('2015-01-01 11:00:00'): 17.038855027411945,
('2015-01-01 12:00:00'): 13.485624530051169,
('2015-01-01 13:00:00'): 69.488020963841421,
('2015-01-01 14:00:00'): 0.0,
('2015-01-01 15:00:00'): 84.858909271558645,
('2015-01-01 16:00:00'): 20.160277319749248,
('2015-01-01 17:00:00'): 0.0,
('2015-01-01 18:00:00'): 0.0,
('2015-01-01 19:00:00'): 90.718713931973625,
('2015-01-01 20:00:00'): 164.62985302109433,
('2015-01-01 21:00:00'): 0.0,
('2015-01-01 22:00:00'): 82.629209162962155,
('2015-01-01 23:00:00'): 24.913643956122016}}
# load into pandas
df = pd.DataFrame(data)