I have some sensor data that contains timestamps from when a machine is turned on and an indicator variable showing whether or not the machine is actively running. The data is mostly recorded every two minutes, but they there could be more or less time between two sensor readings due to issues with the sensor.
I want to create a new feature that captures at a specific datetime, how long has the machine been in that Run status for.
I currently have the data loaded in a
DataFrame, and I have been able to create a new variable that shows whether or not the
Run status has changed from the previous reading. Next, I need to figure out how capture the amount of time since the last time the
Run status changed. If the readings where consistently every two minutes, I could maybe do some type of counter that resets when
Run changes. Instead, I took an approach where if the time changed, I hold onto that value and keep subtracting the next Datetime stamp from it, as long as Run didn't change. When Run changes, I start the processes over.
This approach seems to work fine on this small example, but I feel like it's not the most efficient use of resources, especially when trying to scale up to my 400,000 record dataset.
# load example data df = pd.DataFrame(data = [['2015-01-01 00:00', 1], ['2015-01-01 00:02', 1], ['2015-01-01 00:04', 1], ['2015-01-01 00:06', 0], ['2015-01-01 00:08', 0], ['2015-01-01 00:10', 1], ['2015-01-01 00:12', 0], ['2015-01-01 00:15', 1], ['2015-01-01 00:17', 1], ['2015-01-01 00:19', 1], ['2015-01-01 00:23', 0], ['2015-01-01 00:25', 0], ['2015-01-01 00:30', 0], ['2015-01-01 00:32', 0], ['2015-01-01 00:34', 0]], columns = ['Datetime', 'Run']) # convert to datetime object df.Datetime = pd.to_datetime(df['Datetime']) # create an empty column to capture change point in Run df['Run_Change'] = "" # set the first Run_Change equal to 'Change', since we don't know what happened before it df = df.set_value(df.index, 'Run_Change', 'Change') # create a column to capture the amount of time between each Run_Change df['Time_Since_Change'] = df['Datetime'] - df['Datetime'] # set the first time_since_change to 0 since we don't know what happened before it first_change = df.ix[df.index]['Datetime'] - df.ix[df.index]['Datetime'] df = df.set_value(df.index, 'Time_Since_Change', first_change) # set iniital datetime to based changes on change_time = df.ix[df.index]['Datetime'] # starting at the second event... for i in df.index[1:]: # ...compare Run at this time to Run at the previous time...and if it changeed... if df.ix[i]['Run'] != df.ix[i-1]['Run']: # ...set Status equal to 'Change' df.set_value(i, 'Run_Change', 'Change') # ...and grab the 'Datetime' that the change happeneed change_time = df.ix[i]['Datetime'] # otherwise... else: #...set equal to 'No Change' df.set_value(i, 'Run_Change', 'No Change') # calculate the time since the last change time_since_change = df.ix[i]['Datetime'] - change_time # update DF df.set_value(i, 'Time_Since_Change', time_since_change) # convert Time_Since_Change to minutes df['Time_Since_Change_2'] = df['Time_Since_Change'] / np.timedelta64(1, 'm')