Add New Row Based On An If Condition Via Python

I need to add a new row if two consecutive cells of the column door are the same and the difference between two consecutive cells in the column time is more than 5 minutes. df: Tim

Solution 1:

So first, I highly recommend you to always deliver a working example, for copy-paste!

import pandas as pd
import numpy as np
import datetime as dt

df= pd.DataFrame({'Time':['17:01:10', '13:23:00', '11:23:00', '10:01:10','09:01:10','09:01:10'],
 'door':['RDC_OUT-1', 'RDC_IN-1','RDC_IN-1','RDC_OUT-1','RDC_IN-1','RDC_IN-1'],
 'name':['alex','alex','alex','alex','alex','alex']})

then, convert your time stamp and features, so you can do math on it:

# replace door with bin valuedf['door']= df['door'].map({'RDC_IN-1': 0, 'RDC_OUT-1': 1})
# convert time stampdf['Time'] = pd.to_datetime(df['Time'], format="%H:%M:%S")

Now you are able to unleash the power of pandas data frame ;)

# sort by time stamp
df= df.sort_values(by='Time')

# calculate difference to next row per column
df_diff = df[['Time', 'door']].diff(periods=-1)

# select and copy relevant rows 
df_add = df[(df_diff.Time < dt.timedelta(minutes=-5))& (df_diff.door ==0)].copy()

# change the time stamp of copied rows
df_add.loc[df_add.door == 0, 'Time'] =  pd.to_datetime('12:00:00', format="%H:%M:%S")
df_add.loc[df_add.door == 1, 'Time'] =  pd.to_datetime('14:00:00', format="%H:%M:%S")


# switch the label of copied rows
df_add['door']= -(df['door']-1)

# change name to mark the new
df_add['name']= 'new_alex'# append existing data frame with new rows and sort by time stamp
df = df.append(df_add ).sort_values(by='Time')

# remap the door featuere
df['door']= df['door'].map({0:'RDC_IN-1', 1:'RDC_OUT-1'})

This should give you the output:

Timedoorname41900-01-01 09:01:10   RDC_IN-1alex51900-01-01 09:01:10   RDC_IN-1alex31900-01-01 10:01:10  RDC_OUT-1alex21900-01-01 11:23:00   RDC_IN-1alex21900-01-01 12:00:00  RDC_OUT-1new_alex11900-01-01 13:23:00   RDC_IN-1alex01900-01-01 17:01:10  RDC_OUT-1alex