Find max values for each 5 rows in pd.DateFrame

user_8518294 0
Viewed 96

I have some marketing data with 1-minute interval. As a sample of csv-table, each row represents max values for each minute: 

time    ch1     ch2 ch3 ch4      
20:03   1754    539 149 1337     
20:04   2073    576 160 1448     
20:05   2246    599 176 1515     
20:06   2246    637 176 1531     
20:07   2457    651 183 1549     
20:08   2564    677 184 1655     
20:09   2624    712 191 1699     
20:10   2742    717 194 1672     
20:11   2788    714 199 1675     
20:12   2792    693 186 1680     
20:13   2914    708 188 1672     
20:14   3067    715 194 1685     
20:15   3067    725 196 1682

additionally, I need to find max values for each 5 minute. So I need to find max for every 5 rows (or less - if there are no more rows remained) of each columns and insert it to new 5-minute row.

What I looking to recieve (as example):

each new row has to represent max value for 5 

time    ch1     ch2 ch3 ch4     
20:03   2564    677 184 1655     
20:08   2914    717 199 1699     
20:13   3067    725 196 1685

I honestly have searched but no result.

Is there in Python some elegant solution for my task? Thank for your help!

pandas python
3 Answers
g = df.groupby(np.arange(len(df)) // 5)
g.max().assign(time=g.time.first())

    time   ch1  ch2  ch3   ch4   ch5
0  20:03  2457  651  183  1549  4840
1  20:08  2792  717  199  1699  5376
2  20:13  3067  725  196  1685  5670
user_23366540

By using your input : 

df['group']=df.index//5
target=df.groupby('group').agg(max)
target['time']=df.groupby('group').time.agg(min)

Out[511]: 
        time   ch1  ch2  ch3   ch4   ch5
group                                   
0      20:03  2457  651  183  1549  4840
1      20:08  2792  717  199  1699  5376
2      20:13  3067  725  196  1685  5670
user_79645270

Im going to assume that you did not convert your values to datetime since you specified this is a csv table of data, so I will convert the index to datetime.

df.index = pd.to_datetime(df.time,format='%H:%M')

Now that the index is of datetime format we can use resample to group by 5 minute intervals. Note: I will set the base to 3 here since that is how you wanted it formatted, however I think in the long run you may be better suited leaving it at 0. So to group the data just run

df.resample('5T',base=3).max().drop('time',1)

To dynamically set the base to the first minute value use

df.resample('5T',base=int(df.time.values[0][-1:])).max().drop('time',1)

Yields 

                      ch1  ch2  ch3   ch4
time
2017-09-20 20:03:00  2457  651  183  1549
2017-09-20 20:08:00  2792  717  199  1699
2017-09-20 20:13:00  3067  725  196  1685

If you dont want the date in the index just run

df.index = df.index.time

However, you need the date included to resample

           ch1  ch2  ch3   ch4
20:03:00  2457  651  183  1549
20:08:00  2792  717  199  1699
20:13:00  3067  725  196  1685
user_55144760
Related