mydata <- data.frame(co = c(2.3,3.1,5.7), b = c(3,5,6), mydate = c(as.Date ("2015-10-01"), as.Date ("2015-11-01"), as.Date ("2015-12-01")))
I would like to add and fill columns based on a date.
I want the final output to look something like this:
co b mydate m1 m2 m3 m4 m5 m6 m7 m8 m9 m10 m11 m12
1 2.3 3 2015-10-01 0 0 0 0 0 0 0 0 0 1 0 0
2 3.1 5 2015-11-01 0 0 0 0 0 0 0 0 0 0 1 0
3 5.7 6 2015-12-01 0 0 0 0 0 0 0 0 0 0 0 1
>Solution :
For both, I add a for loop that adds the missing months; if your real data is full-enough (all months are included), then you may not need it (or may not care even if a month is missing).
base R (and reshape2)
out <- mydata |>
transform(m = format(mydate, format = "m%m"), v = 1L) |>
reshape2::dcast(formula = co + b + mydate ~ m, value.var = "v", fill = 0L)
for (col in paste0("m", 1:12)) if (!col %in% names(out)) out[[col]] <- 0L
out
# co b mydate m10 m11 m12 m1 m2 m3 m4 m5 m6 m7 m8 m9
# 1 2.3 3 2015-10-01 1 0 0 0 0 0 0 0 0 0 0 0
# 2 3.1 5 2015-11-01 0 1 0 0 0 0 0 0 0 0 0 0
# 3 5.7 6 2015-12-01 0 0 1 0 0 0 0 0 0 0 0 0
dplyr
library(dplyr)
out <- mydata %>%
mutate(m = format(mydate, format = "m%m"), v = 1L) %>%
tidyr::pivot_wider(c(co, b, mydate), names_from = "m", values_from = "v", values_fill = 0L)
for (col in paste0("m", 1:12)) if (!col %in% names(out)) out[[col]] <- 0L
out
# # A tibble: 3 x 15
# co b mydate m10 m11 m12 m1 m2 m3 m4 m5 m6 m7 m8 m9
# <dbl> <dbl> <date> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int>
# 1 2.3 3 2015-10-01 1 0 0 0 0 0 0 0 0 0 0 0
# 2 3.1 5 2015-11-01 0 1 0 0 0 0 0 0 0 0 0 0
# 3 5.7 6 2015-12-01 0 0 1 0 0 0 0 0 0 0 0 0