I got a huge dataset consisting of meteorological data collected every hour from 01.01.2003 until 31.12.2023 (Picture from part of the dataset). This dataset has 24 rows for each day and 1 row for each hour of these 20 years.
I want to have another data frame in my script that contains columns deriving from the original date frame. These columns would be "Date" - one row per day, "AvgAirTemp" - average air temperature that day, "MaxAirTemp" - maximum air temperature that day, "MinAirTemp" - "minimum air temperature that day.
Creating the new data frame isn't the problem, I have it already. But I couldn't find how to calculate the average of the values in 24 rows and transpose it to the right row/column in the new data frame. The same for the max and min values. I was trying to find a way to use mutate for this, but no success.
I appreciate your assistance.
Since you mention mutate, I am assuming you are using dplyr. If not, you can follow the same logic with aggregate.
data |> group_by(Date) |> mutate(Mean_Temp = mean(DryAirTemp), Min_Temp = min(DryAirTemp)) -> dailydata