CODEWITHSUNDEEP
pythonpandas
Tatik
Tatik

Reputation: 1227

How to find the time difference between first row and the row when the condition is met?

I need to find the time difference (in minutes) between the beginning and the moment when col1 exceeds the value 20.

For the data below, the answer should be 72 minutes (from 20:00:19 till 21:12:00).

df:

date_time            col1
2018-03-04 20:00:19  9
2018-03-04 21:10:00  13
2018-03-04 21:12:00  21
2018-03-04 21:15:00  25

How can I do it? This is my current snippet:

df.index = pd.to_datetime(df['date_time'])
start = df.index[0]
row_id = df.index[df['col1'] > 20]
time_val = start - df.index[row_id]

Upvotes: 4

Views: 163

Answers (5)

BENY
BENY

Reputation: 323376

IIUC I am using ptp

df.loc[df.col1.le(20).shift().cumprod().ne(0),'date_time'].ptp()
Out[1232]: Timedelta('0 days 01:11:41')

Upvotes: 2

piRSquared
piRSquared

Reputation: 294526

Assuming 'date_time' is dtype datetime. We can use diff to get Timedelta and cumsum to get cumulative Timedelta. Then we can use idxmax on df.col1.gt(20)

df.date_time.diff().fillna(0).cumsum()[df.col1.gt(20).idxmax()]

Timedelta('0 days 01:11:41')

Timedelta has a total_seconds method that you can divide by 60

df.date_time.diff().fillna(0).cumsum()[df.col1.gt(20).idxmax()].total_seconds() / 60

71.68333333333334

Or you can divide by another Timedelta

df.date_time.diff().fillna(0).cumsum()[df.col1.gt(20).idxmax()] / pd.Timedelta(1, unit='m')

71.68333333333334

Upvotes: 3

Yuca
Yuca

Reputation: 6101

One liner:

ans = pd.to_datetime(df.groupby(df.col1>20).first().date_time).diff().dt.total_seconds()/60

output:

ans[True]:

71.68333333333334

Upvotes: 4

Juan C
Juan C

Reputation: 6132

After transforming your columns to the desired output:

df.date_time=pd.to_datetime(df.date_time)
df.col1=pd.to_numeric(df.col1)
id=df[df.col1>20].col1.idxmin()
diff=(df.iloc[id].date_time-df.iloc[0].date_time).seconds/60

Upvotes: 1

RenauV
RenauV

Reputation: 383

You could try this:

for index, row in df1.iterrows():
    if row['col'] > 20:
        total_seconds = int((df1['date_time'][0] - row['date_time']).total_seconds())
        minutes, remainder = divmod(total_seconds,60)
        print('{} mins'.format(minutes))
        break

Upvotes: 0

Related Questions