I have a dataset of monthly averages from multiple (250) stations over several years (1980-2023). These data are currently stacked with a monthly average for each month of each year for each station. How can i transform them so that there are 250 rows with 516 columns (43 years x 12 months) that include the months with no data as NA?
2 110453 2023 11 370.9 2023-11
93 110453 2023 12 415.1788 2023-12
94 110453 2024 1 676.5803 2024-1
95 110453 2024 2 673.1603 2024-2
240 123548 2019 1 683.2573 2019-1
241 123548 2019 2 683.1791 2019-2
242 123548 2019 3 684.3534 2019-3
243 123548 2019 4 689.1183 2019-4
244 123548 2019 5 688.6553 2019-5
245 123548 2019 6 685.6333 2019-6
I have tried:
monthly_averages_1980$year_month <- paste(monthly_averages_1980$Year, monthly_averages_1980$Month, sep = "-")
monthly_averages_1980 <- spread(monthly_averages_1980, key = year_month, value = Average_Value)
but it doesn't remove the excessive rows from the input dataframe.
it also sorts the coulmns as alpha (1980-1, 1980-10, 1980-11...).
i'd like something like this:
