Hello first time posting and i was wondering if anyone could help.
Timestamp Hour Value (AVG)
2021-10-02 14:00:00 13.800
2021-10-02 13:00:00 14.725
2021-10-02 12:00:00 16.300
2021-10-02 11:00:00 15.850
2021-10-02 10:00:00 15.200
2021-10-02 09:00:00 18.680
2021-10-02 08:00:00 14.350
2021-10-02 07:00:00 14.200
2021-10-02 06:00:00 15.980
2021-10-02 05:00:00 12.550
2021-10-02 04:00:00 17.320
2021-10-02 03:00:00 14.610
2021-10-02 02:00:00 15.200
2021-10-02 01:00:00 19.290
2021-10-02 00:00:00 12.240
i have the dataframe above, it takes the road temp every hour for every day of the year. I am trying to get the max Road surface temperature for each day.(there are multiple more days however i could not post them all )
This is all the code i have done so far.
my_data = pd.read_sql(" SELECT * FROM weather_data ",connection_To_SQL)# this statement selects everything from the weather_data table in mysql
my_data['Hour'] = my_data['Hour'].astype(str).map(lambda x: x[7:]) # changed (Hour: 0 days 14:00:00 to Hour: 14:00:00 )
my_data['Timestamp'] = pd.to_datetime(my_data.Timestamp, format='%Y-%m-%d ')
print(my_data)
Trial = my_data.loc[(my_data['Description_input']=='Road Surface Temperature (°C)') ,['Timestamp','Hour','Value (AVG)']]
print(Trial)
is there a way to change this so i only get the max value for each specific day and its corresponding hour? for example: timestamp Hour value 2021-10-02 12:00:00 16.300 2021-10-06 15:00:00 35.000
thank you
