Combine separate Year and Month columns into single Date Column

Question

I have a data frame (df) like this:

    code     year  month
1 YYOOGG    2011    8
2 YYOOGG    2011    1
3 YYOOGG    2011    4
4 YYOOGG    2011    3
5 YYOOGG    2011    12
6 YYOOGG    2011    9

and I need to create a 4th column with the Date like this:

    code     year  month  Date
1 YYOOGG    2011    8     2011-08
2 YYOOGG    2014    1     2014-01
3 YYOOGG    2016    4     2016-04
4 YYOOGG    2009    3     2009-03
5 YYOOGG    2000    12    2000-12
6 YYOOGG    2010    9     2010-09

I tried this:

  df$Date <- as.Date(paste(df$year, df$month, sep="-"), "%Y-%M")

but I get the following as the date:

2011-09-09

Jacob H · Accepted Answer · 2016-09-09 22:05:55Z

32

I'd use zoo::as.yearmon as follows

df$Date <- as.yearmon(paste(df$year, df$month), "%Y %m")

It will not look like the desired output (i.e. 2011-01).

However, IMO this approach is better than m0h3n's because df$Date will be saved as a yearmon object rather than a string. Therefore you can be handled it like a date. For example, if you save df$Date as a string you're going to have a hard time plotting your data over time, etc...

edited Sep 9, 2016 at 22:05

answered Sep 9, 2016 at 21:58

Jacob H

4,5333 gold badges34 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rich Scriven · Accepted Answer · 2016-09-10 07:23:49Z

12

A date typically contains a day, otherwise it's not actually a date. For this reason, I'll create a character vector for the new column. Using only base R, you could use sprintf() to put the two columns together, adding a zero wherever necessary on the month column ...

within(df, Date <- sprintf("%d-%02d", year, month))
#     code year month    Date
# 1 YYOOGG 2011     8 2011-08
# 2 YYOOGG 2011     1 2011-01
# 3 YYOOGG 2011     4 2011-04
# 4 YYOOGG 2011     3 2011-03
# 5 YYOOGG 2011    12 2011-12
# 6 YYOOGG 2011     9 2011-09

Or

df$Date <- with(df, sprintf("%d-%02d", year, month))

edited Sep 10, 2016 at 7:23

answered Sep 10, 2016 at 7:09

Rich Scriven

99.8k11 gold badges190 silver badges252 bronze badges

1 Comment

kev Over a year ago

I would like to add the day that is always 01, how can I implement that?

Arun kumar mahesh · Accepted Answer · 2016-09-10 07:06:11Z

1

You can try in this way but previous OP has a better code

data <-  data.frame(code=c("ABCF","DEFG"), year = c(2011,2012), month = c(08,12))



for(i in 1:nrow(data)){
if(nchar(data$month[i])==1){
  data$Date[i] <- paste(data$year[i],data$month[i],sep="-0")
}else{
  data$Date[i] <- paste(data$year[i],data$month[i],sep="-")
  }
}

data
  code year month    Date
1 ABCF 2011     8 2011-08
2 DEFG 2012    12 2012-12

answered Sep 10, 2016 at 7:06

Arun kumar mahesh

2,3552 gold badges17 silver badges23 bronze badges

Comments

iouraich · Accepted Answer · 2021-10-19 09:12:41Z

1

Since you have only the month and year, and you are only interested in those, I would suggest the following.

df %>% 
mutate(
day = "01", #Create a day column just to get a full date format. The day will be dropped in the following step
date_yr_m = as.yearmon(paste0(year,month,day), "%Y %b") #use the zoo as.yearmon() to get the year and month only
)

This will make it possible to create a 'yearmon' column that can be arranged chronologically in case you want to plot a figure.

edited Oct 19, 2021 at 9:12

answered Oct 19, 2021 at 8:45

iouraich

3,0565 gold badges31 silver badges42 bronze badges

Comments

tassones · Accepted Answer · 2022-08-23 17:50:37Z

1

Here is a dplyr solution similar to @RichScriven

Example data frame

df <- data.frame(matrix(ncol = 3, nrow = 6))
colnames(df)[1:3] <- c("code","year","month")
df$code <- "YYOOGG"
df$year <- 2011
df$month <- c(8,1,4,3,12,9)

Solution

library(dplyr)

df <- df %>%
  mutate(Date = with(., sprintf("%d-%02d", year, month)))

answered Aug 23, 2022 at 17:50

tassones

1,90814 silver badges36 bronze badges

Comments

user63230 · Accepted Answer · 2023-11-20 18:27:49Z

0

Adding lubridate::make_date:

library(tidyverse)
library(lubridate)

df %>%
  mutate(date = make_date(year, month))

answered Nov 20, 2023 at 18:27

user63230

4,9012 gold badges26 silver badges59 bronze badges

Collectives™ on Stack Overflow

Combine separate Year and Month columns into single Date Column

6 Answers 6

Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related