0

I have a dataframe as such


       timestamp                    Format  MachineMode  \
0      2022-08-04 07:49:00+00:00    2000          3.0   
1      2022-08-04 07:50:00+00:00    2000          3.0   
2      2022-08-04 07:51:00+00:00    2000          3.0   
3      2022-08-04 07:52:00+00:00    2000          3.0   
4      2022-08-04 07:53:00+00:00    2000          3.0   
...                          ...     ...          ...   
59825  2022-12-07 06:43:00+00:00    2000          1.0   
59826  2022-12-07 06:44:00+00:00    2000          1.0   
59827  2022-12-07 06:45:00+00:00    2000          1.0   
59828  2022-12-07 06:49:00+00:00    2000          1.0   
59829  2022-12-07 06:50:00+00:00    2000          1.0   

                        RecipeName  ProductionSpeed  TankPressure  \
0                       2 LT Fanta       500.001283          4.72   
1                       2 LT Fanta       500.001650          4.71   
2                       2 LT Fanta       500.001333          4.72   
3                       2 LT Fanta       500.001350          4.71   
4                       2 LT Fanta       308.336100          5.37   
...                            ...              ...           ...   
59825  2 LT Coca Cola_23K Duo Pack       383.335217          4.80   
59826  2 LT Coca Cola_23K Duo Pack       383.335000          4.81   
59827  2 LT Coca Cola_23K Duo Pack       306.667767          4.82   
59828  2 LT Coca Cola_23K Duo Pack       364.168333          4.53   
59829  2 LT Coca Cola_23K Duo Pack       325.833783          4.76   

       CarouselPressure  
0                  0.98  
1                  0.98  
2                  0.98  
3                  0.98  
4                  0.96  
...                 ...  
59825              1.01  
59826              0.98  
59827              0.99  
59828              0.93  
59829              0.99

I want to resample the data to an hourly level, groupby the Format, Machinemode, recipename and the MAX productionspeed for that hour and display the TankPressure, CarouselPressure when the productionspeed is highest for that hour.

I have tried

new_bg1=new_bg.groupby(['RecipeName','Format','MachineMode', pd.Grouper(freq='H')]).ProductionSpeed.agg(['max','idxmax']).reset_index()

but I can't get it to show the values for the tankpressure and carouselpressure.

Any help would be appreciated

Jahrakal
  • 29
  • 4
  • `new_bg.loc[new_bg.groupby(['RecipeName','Format','MachineMode', pd.Grouper(freq='H')])['ProductionSpeed'].idxmax()]` – mozway Feb 22 '23 at 12:42
  • but I want it to show me the values for the carousel pressure and tankpressure as well. – Jahrakal Feb 22 '23 at 12:45
  • This will do that. Please provide a smaller example (well formatted as DataFrame constructor, or at least without the table broken in different parts) and the matching expected output – mozway Feb 22 '23 at 12:52
  • yes this works thank you, but this doesnt produce the "resampled" timestamp, basically a column on timestamps at an hourly level. I would like to have that, anyway I can get that? – Jahrakal Feb 22 '23 at 13:15
  • Maybe first `assign` as a new column then use that to `groupby` – mozway Feb 22 '23 at 15:39

0 Answers0