1

I have dataframe with 2 date columns (year and month) and I would like to merge them into 1 (I will use that column in plotting x-axis).

How can I merge them so values in it will look like "1990-03"

structure(list(Year = c(1983, 1983, 1983, 1983, 1983, 1983), 
    Month = c(5, 6, 7, 8, 9, 10), MEI = c(2.556, 2.167, 1.741, 
    1.13, 0.428, 0.002), CO2 = c(345.96, 345.52, 344.15, 342.25, 
    340.17, 340.3), CH4 = c(1638.59, 1633.71, 1633.22, 1631.35, 
    1648.4, 1663.79), N2O = c(303.677, 303.746, 303.795, 303.839, 
    303.901, 303.97), CFC.11 = c(191.324, 192.057, 192.818, 193.602, 
    194.392, 195.171), CFC.12 = c(350.113, 351.848, 353.725, 
    355.633, 357.465, 359.174), TSI = c(1366.1024, 1366.1208, 
    1366.285, 1366.4202, 1366.2335, 1366.0589), Aerosols = c(0.0863, 
    0.0794, 0.0731, 0.0673, 0.0619, 0.0569), Temp = c(0.109, 
    0.118, 0.137, 0.176, 0.149, 0.093)), row.names = c(NA, 6L
), class = "data.frame")

I was trying some methods but keep getting NA

1 Answers1

4

You could use sprintf to format the month into two digits and paste with the year:

sprintf("%04d-%02d",df$Year,df$Month)
[1] "1983-05" "1983-06" "1983-07" "1983-08" "1983-09" "1983-10"
df$Date <- sprintf("%04d-%02d",df$Year,df$Month)
## dplyr alternative
df %>% mutate(Date=sprintf("%04d-%02d",df$Year,df$Month))

  Year Month   MEI    CO2     CH4     N2O  CFC.11  CFC.12      TSI Aerosols  Temp    Date
1 1983     5 2.556 345.96 1638.59 303.677 191.324 350.113 1366.102   0.0863 0.109 1983-05
2 1983     6 2.167 345.52 1633.71 303.746 192.057 351.848 1366.121   0.0794 0.118 1983-06
3 1983     7 1.741 344.15 1633.22 303.795 192.818 353.725 1366.285   0.0731 0.137 1983-07
4 1983     8 1.130 342.25 1631.35 303.839 193.602 355.633 1366.420   0.0673 0.176 1983-08
5 1983     9 0.428 340.17 1648.40 303.901 194.392 357.465 1366.234   0.0619 0.149 1983-09
6 1983    10 0.002 340.30 1663.79 303.970 195.171 359.174 1366.059   0.0569 0.093 1983-10

For completeness (improving on @KarthikS's comment) U could use stringr::str_c and stringr::str_pad the latter to pad the month with 0s and the former to concatenate the Year and Month

str_c(df$Year, str_pad(df$Month, 2, pad=0), sep='-')
[1] "1983-05" "1983-06" "1983-07" "1983-08" "1983-09" "1983-10"
Abdessabour Mtk
  • 3,895
  • 2
  • 14
  • 21
  • 2
    great, to make it work with ggplot i also used as.yearmon from zoo library `as.Date(as.yearmon(sprintf("%04d-%02d",df$Year,df$Month)))` –  Nov 21 '20 at 18:03
  • 1
    You can also use ```df$YM <- format(as.Date(paste(df$Year, df$Month, "1", sep = "-"), format = "%Y-%m-%d"), "%Y-%m")``` in case you want to stay with base R. Also, this question is possibly connected to [Converting year and month (“yyyy-mm” format) to a date?](https://stackoverflow.com/questions/6242955/converting-year-and-month-yyyy-mm-format-to-a-date) and [Extract Month and Year From Date in R](https://stackoverflow.com/questions/37704212/extract-month-and-year-from-date-in-r/37704385). – MacOS Nov 21 '20 at 18:33