Hi, I’m posting this as part of the intro course so it’s not urgent, although I would like an answer, it’s been mildly annoying me for a while!
I have a dataset with year and month in two separate columns. Year is integer and month is character. I want to combine them into one field that I can use as a date to filter dates after a certain month etc.
So far I have concatenated the two columns into one field, and then tried to convert the combined field to date using the Zoo package and yearmon. But I end up with a column with all NAs.
If anyone can tell me what I’m doing wrong, or another way around this, that would be great! Thank you in advance. Rachel
# install and load packages
pacman::p_load(zoo, lubridate, readxl, reprex, datapasta, tidyverse)
# import data
data <- data.frame(
stringsAsFactors = FALSE,
Year = c(2022, 2022, 2022, 2022, 2022),
Month = c("December","January","February",
"March","April")
)
# concatenate the month and year into one field
data$Date <- with(data, sprintf("%d-%02s", Year,Month))
# convert to date
data <- data %>%
mutate(as.yearmon(Date))
Created on 2024-03-23 by the reprex package (v2.0.1)
1 Like
Hello,
The issue is due to Month being a character, the as.yearmon() function appears to require month as an integer. The code below joins Month to its integer and achieves what you are looking to do. However, another option would be to use regular dates of the form YYYY-mm-dd and just fixing the day component as 01 as this may allow you to do date arithmetic more easily.
# loading packages
library(tidyverse)
library(zoo)
#>
#> Attaching package: 'zoo'
#> The following objects are masked from 'package:base':
#>
#> as.Date, as.Date.numeric
# creating fake data
data <- data.frame(
stringsAsFactors = FALSE,
Year = c(2022, 2022, 2022, 2022, 2022),
Month = c("December", "January", "February",
"March", "April")
)
# concatenate the year and month
data <- data |>
left_join(enframe(month.name, name = "month_int"), by = c(Month = "value")) |>
select(Year, Month = month_int) |>
unite(col = year_mon, Year, Month, remove = FALSE, sep = "-")
# convert to date
data <- data |>
mutate(year_mon = as.yearmon(year_mon))
Created on 2024-03-23 with reprex v2.1.0
Session info
sessioninfo::session_info()
#> ─ Session info ───────────────────────────────────────────────────────────────
#> setting value
#> version R version 4.3.1 (2023-06-16)
#> os macOS Ventura 13.6.3
#> system x86_64, darwin20
#> ui X11
#> language (EN)
#> collate en_US.UTF-8
#> ctype en_US.UTF-8
#> tz America/Toronto
#> date 2024-03-23
#> pandoc 3.1.1 @ /Applications/RStudio.app/Contents/Resources/app/quarto/bin/tools/ (via rmarkdown)
#>
#> ─ Packages ───────────────────────────────────────────────────────────────────
#> package * version date (UTC) lib source
#> cli 3.6.2 2023-12-11 [1] CRAN (R 4.3.0)
#> colorspace 2.1-0 2023-01-23 [1] CRAN (R 4.3.0)
#> digest 0.6.34 2024-01-11 [1] RSPM (R 4.3.0)
#> dplyr * 1.1.4 2023-11-17 [1] CRAN (R 4.3.0)
#> evaluate 0.23 2023-11-01 [1] CRAN (R 4.3.0)
#> fansi 1.0.6 2023-12-08 [1] CRAN (R 4.3.0)
#> fastmap 1.1.1 2023-02-24 [1] CRAN (R 4.3.0)
#> forcats * 1.0.0 2023-01-29 [1] CRAN (R 4.3.0)
#> fs 1.6.3 2023-07-20 [1] CRAN (R 4.3.0)
#> generics 0.1.3 2022-07-05 [1] CRAN (R 4.3.0)
#> ggplot2 * 3.5.0 2024-02-23 [1] RSPM (R 4.3.0)
#> glue 1.7.0 2024-01-09 [1] RSPM (R 4.3.0)
#> gtable 0.3.4 2023-08-21 [1] CRAN (R 4.3.0)
#> hms 1.1.3 2023-03-21 [1] CRAN (R 4.3.0)
#> htmltools 0.5.7 2023-11-03 [1] CRAN (R 4.3.0)
#> knitr 1.45 2023-10-30 [1] CRAN (R 4.3.0)
#> lattice 0.22-5 2023-10-24 [2] CRAN (R 4.3.0)
#> lifecycle 1.0.4 2023-11-07 [1] CRAN (R 4.3.0)
#> lubridate * 1.9.3 2023-09-27 [1] CRAN (R 4.3.0)
#> magrittr 2.0.3 2022-03-30 [1] CRAN (R 4.3.0)
#> munsell 0.5.0 2018-06-12 [1] CRAN (R 4.3.0)
#> pillar 1.9.0 2023-03-22 [1] CRAN (R 4.3.0)
#> pkgconfig 2.0.3 2019-09-22 [1] CRAN (R 4.3.0)
#> purrr * 1.0.2 2023-08-10 [1] CRAN (R 4.3.0)
#> R.cache 0.16.0 2022-07-21 [1] CRAN (R 4.3.0)
#> R.methodsS3 1.8.2 2022-06-13 [1] CRAN (R 4.3.0)
#> R.oo 1.26.0 2024-01-24 [1] RSPM (R 4.3.0)
#> R.utils 2.12.3 2023-11-18 [1] CRAN (R 4.3.0)
#> R6 2.5.1 2021-08-19 [1] CRAN (R 4.3.0)
#> readr * 2.1.5 2024-01-10 [1] RSPM (R 4.3.0)
#> reprex 2.1.0 2024-01-11 [1] RSPM (R 4.3.0)
#> rlang 1.1.3 2024-01-10 [1] RSPM (R 4.3.0)
#> rmarkdown 2.25 2023-09-18 [1] CRAN (R 4.3.0)
#> rstudioapi 0.15.0 2023-07-07 [1] CRAN (R 4.3.0)
#> scales 1.3.0 2023-11-28 [1] CRAN (R 4.3.0)
#> sessioninfo 1.2.2 2021-12-06 [1] CRAN (R 4.3.0)
#> stringi 1.8.3 2023-12-11 [1] CRAN (R 4.3.0)
#> stringr * 1.5.1 2023-11-14 [1] CRAN (R 4.3.0)
#> styler 1.10.2 2023-08-29 [1] CRAN (R 4.3.0)
#> tibble * 3.2.1 2023-03-20 [1] CRAN (R 4.3.0)
#> tidyr * 1.3.1 2024-01-24 [1] RSPM (R 4.3.0)
#> tidyselect 1.2.0 2022-10-10 [1] CRAN (R 4.3.0)
#> tidyverse * 2.0.0 2023-02-22 [1] CRAN (R 4.3.0)
#> timechange 0.3.0 2024-01-18 [1] RSPM (R 4.3.0)
#> tzdb 0.4.0 2023-05-12 [1] CRAN (R 4.3.0)
#> utf8 1.2.4 2023-10-22 [1] CRAN (R 4.3.0)
#> vctrs 0.6.5 2023-12-01 [1] CRAN (R 4.3.0)
#> withr 3.0.0 2024-01-16 [1] RSPM (R 4.3.0)
#> xfun 0.42 2024-02-08 [1] RSPM (R 4.3.0)
#> yaml 2.3.8 2023-12-11 [1] CRAN (R 4.3.0)
#> zoo * 1.8-12 2023-04-13 [1] CRAN (R 4.3.0)
#>
#> [1] /Users/timothychisamore/Library/R/x86_64/4.3/library
#> [2] /Library/Frameworks/R.framework/Versions/4.3-x86_64/Resources/library
#>
#> ──────────────────────────────────────────────────────────────────────────────
All the best,
Tim
Hi Tim,
Thank you so much for your helpful and speedy reply! I’m glad I asked 
Best wishes,
Rachel
1 Like