datetime - How create a matrix from the data.frame where time variable is separated to year/month in R?

Question

Closed. This question needs to be more focused. It is not currently accepting answers. question from:https://stackoverflow.com/questions/65952449/how-create-a-matrix-from-the-data-frame-where-time-variable-is-separated-to-year

Answer 1

Extract year and month data from monthtrunc and get the data in wide format.

library(tidyverse)

df %>%
  mutate(monthtrunc = ymd_hms(monthtrunc), 
         year = year(monthtrunc), 
         month = month(monthtrunc)) %>%
  select(-monthtrunc) %>%
  pivot_wider(names_from = month, values_from = pricevel) %>%
  column_to_rownames('year')

#         1     2    3    4    5     6     7     8    9   10   11    12
#2011 0.974 1.591 1.27 1.81 1.08 0.972 1.498 1.375 2.04 1.43 1.20 1.143
#2012 1.427 2.228 1.53 1.23 1.58 1.328 1.785 1.964 1.74 1.56 1.67 1.551
#2013 1.304 0.832 1.13 1.38 1.20 1.985 1.343 1.451 1.78 1.98 1.87 1.497
#2014 2.011 1.821 1.85 1.92 1.96 1.881 1.947 1.453 1.73 2.22 2.38 2.261
#2015 1.367 2.167 2.00 1.91 2.52 4.652 4.063 3.525 3.36 3.28 2.72 3.132
#2016 3.432 2.510 2.43 1.97 2.04 1.981 1.253 1.396 1.92 1.39 1.25 1.387
#2017 1.108 1.442 1.13 1.30 1.45 2.185 1.493 1.956 1.54 1.90 1.50 1.332
#2018 1.867 1.945 1.34 1.07 3.26 1.249 0.666 0.808 1.61 1.37 1.29 0.994
#2019 1.130 0.940 1.09 1.44 3.09 4.374 1.690 1.572 2.16 1.74 1.66 1.585
#2020 2.616 4.829 5.03 4.17 4.74 4.898 4.739 2.455 3.86 3.82 5.48 4.132
#2021 1.916    NA   NA   NA   NA    NA    NA    NA   NA   NA   NA    NA

If you have hourly data you can aggregate the values by including values_fn = sum or values_fn = mean in pivot_wider.