CODEWITHSUNDEEP
pythonpandas
raam
raam

Reputation: 241

Group dataframe with until column value meets the condition

I am having the following data frame need group the frame based on the condition Data Frame

date,day,Name,value,orinal_Time_amin,diff
9/23/2019,Monday,ABCD,21,2019-09-23 00:41:00,0
9/23/2019,Monday,ABCD,5460,2019-09-23 00:45:00,5
9/23/2019,Monday,ABCD,2742,2019-09-23 00:50:00,5
9/23/2019,Monday,ABCD,13552,2019-09-23 00:55:00,5
9/23/2019,Monday,ABCD,6178,2019-09-23 01:24:00,25
9/23/2019,Monday,ABCD,171,2019-09-23 01:25:00,5
9/23/2019,Monday,ABCD,763,2019-09-23 01:30:00,5
9/23/2019,Monday,ABCD,1694,2019-09-23 01:35:00,5
9/23/2019,Monday,ABCD,164,2019-09-23 02:05:00,35
9/23/2019,Monday,ABCD,162,2019-09-23 02:10:00,5
9/23/2019,Monday,ABCD,162,2019-09-23 02:41:00,31

output

Day Name min.value min.time max.time
Monday ABCD 21 2019-09-23 00:41:00 2019-09-23 01:35:00
Monday ABCD 162 2019-09-23 02:05:00 2019-09-23 02:10:00
Monday ABCD 162 2019-09-23 02:41:00 2019-09-23 02:41:00

Explanation

I want to group the dataframe with Day,name,date until the diff <30 and get min value.

Upvotes: 0

Views: 51

Answers (1)

Michael Gardner
Michael Gardner

Reputation: 1813

Perhaps this?

Data:

date,day,Name,value,orinal_Time_amin,diff
9/23/2019,Monday,ABCD,21,2019-09-23 00:41:00,0
9/23/2019,Monday,ABCD,5460,2019-09-23 00:45:00,5
9/23/2019,Monday,ABCD,2742,2019-09-23 00:50:00,5
9/23/2019,Monday,ABCD,13552,2019-09-23 00:55:00,5
9/23/2019,Monday,ABCD,6178,2019-09-23 01:24:00,25
9/23/2019,Monday,ABCD,171,2019-09-23 01:25:00,5
9/23/2019,Monday,ABCD,763,2019-09-23 01:30:00,5
9/23/2019,Monday,ABCD,1694,2019-09-23 01:35:00,5
9/23/2019,Monday,ABCD,164,2019-09-23 02:05:00,35
9/23/2019,Monday,ABCD,162,2019-09-23 02:10:00,5
9/23/2019,Monday,ABCD,162,2019-09-23 02:41:00,31

Code:

df = pd.read_clipboard(sep=',')

df['max.time'] = df.groupby(df['diff'].gt(30).cumsum())['orinal_Time_amin'].transform('max')
df['min.time'] = df.groupby(df['diff'].gt(30).cumsum())['orinal_Time_amin'].transform('min')
df['min.value'] = df.groupby(df['diff'].gt(30).cumsum())['value'].transform('min')

df[['day', 'Name', 'min.value', 'min.time', 'max.time']].drop_duplicates()

Out[1]: 
       day  Name  min.value             min.time             max.time
0   Monday  ABCD         21  2019-09-23 00:41:00  2019-09-23 01:35:00
8   Monday  ABCD        162  2019-09-23 02:05:00  2019-09-23 02:10:00
10  Monday  ABCD        162  2019-09-23 02:41:00  2019-09-23 02:41:00

Upvotes: 1

Related Questions