Generating a Date column in a dataframe

Question

I have the following table of quarterly data and want to generate a new column of date type for each row.

Year,Quarter,Sales
2008,1,1.703
2008,2,0.717
2008,3,6.892
2008,4,4.363
2009,1,3.793
2009,2,5.208
2009,3,7.367
2009,4,8.737
2010,1,8.752
2010,2,8.398

This is what I tried

quarters <- c('-03-31', '-06-30', '-09-30', '-12-31')
gen_date <- function(row) {
    year <- row[1]
    quarter <- row[2]
    date <- paste(toString(year), quarters[quarter], sep='')
    date <- as.Date((date), format="%Y-%m-%d")
    return(date)
}

df$Date <- apply(df, 1, gen_date)

However, the resulting column df$Date is not a date, but an int.

   Year Quarter       Sales  Date
1  2008       1        1.703 13969
2  2008       2        0.717 14060
3  2008       3        6.892 14152
4  2008       4        4.363 14244
5  2009       1        3.793 14334
6  2009       2        5.208 14425
7  2009       3        7.367 14517
8  2009       4        8.737 14609

`date <- as.Date(date), format="%Y-%m-%d")` throws an error. It should read `date <- as.Date(date, format="%Y-%m-%d")` — PhillipD, Feb 17 '18 at 20:21
Yeah, I copied the code wrong sorry. I was running it as as.Date((date), format="%Y-%m-%d") — FranGoitia, Feb 17 '18 at 20:23
`as.Date(zoo::as.yearqtr(paste(d$Year, d$Quarter, sep = "-")), frac = 1)`. Related: [Convert quarter/year format to a date](https://stackoverflow.com/questions/31071733/convert-quarter-year-format-to-a-date) — Henrik, Feb 17 '18 at 20:34

score 1 · Accepted Answer · answered Feb 17 '18 at 20:28

Try with lubridate:

library(lubridate)
Year=c(rep(2008,4),rep(2009,4),2010,2010)
Quarter=c(1,2,3,4,1,2,3,4,1,2)
Sales=c(1.7,0.7,6.9,4.3,3.79,5.2,7.3,8.7,8.7,8.4)
df=tibble(Year,Quarter,Sales)
df$Date=yq(paste(as.character(df$Year),as.character(df$Quarter),sep="-"))
df



Year Quarter Sales Date      
   <dbl>   <dbl> <dbl> <date>    
 1  2008    1.00 1.70  2008-01-01
 2  2008    2.00 0.700 2008-04-01
 3  2008    3.00 6.90  2008-07-01
 4  2008    4.00 4.30  2008-10-01
 5  2009    1.00 3.79  2009-01-01
 6  2009    2.00 5.20  2009-04-01
 7  2009    3.00 7.30  2009-07-01
 8  2009    4.00 8.70  2009-10-01
 9  2010    1.00 8.70  2010-01-01
10  2010    2.00 8.40  2010-04-01

score 0 · Answer 2 · answered Feb 17 '18 at 20:33

Try this:

library(lubridate)

dfx <- read.table(text = "Year,Quarter,Sales
2008,1,1.703
2008,2,0.717
2008,3,6.892
2008,4,4.363
2009,1,3.793
2009,2,5.208
2009,3,7.367
2009,4,8.737
2010,1,8.752
2010,2,8.398", header=T, sep=",")

dfx$month <- factor(dfx$Quarter)
levels(dfx$month) <- c('-03-31', '-06-30', '-09-30', '-12-31')
dfx$month <- as.character(dfx$month)

dfx$date <- ymd(paste(dfx$Year, dfx$month, sep="-"))

HTH

Generating a Date column in a dataframe

2 Answers2