Change two separate columns of month and year into one for date in R

Question

I am really new at using R and am having difficulty changing two columns into one for time. I have Months in 1-12 and years in 1999 etc. format. I am looking to change into one Date which R will recognise as a date. I tried using strptime but as I do not have a day this caused it to automatically use the current date instead - as date of the month isn't specified does this matter?

Thanks in advance

> head(tthm.data)
WSZ_Code Treatment_Code Year Month TTHM CL2_FREE    BrO3 Colour  PH  TURB
1         2              3 1996     1 30.7     0.35 0.00030   0.75 7.4 0.055
2         6              1 1996     2 24.8     0.25 0.00055   0.75 6.9 0.200
3         7              4 1996     2 60.4     0.05 0.00055   0.75 7.1 0.055
6         5              2 1996     3 40.3     0.15 0.00140   2.00 7.7 0.055
11        4              1 1996     3 46.5     0.25 0.00055   1.90 7.4 0.150
14        2              3 1996     3 28.4     0.25 0.00055   1.80 7.4 0.055

Welcome to Stack Overflow! Please add reproducible sample for good people here to help you. See http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example — CHP, Apr 09 '13 at 15:21
sorry what is exchanged? They are entirely separate... I think @e4e5f4 — Rbeginner, Apr 09 '13 at 15:54

score 0 · Answer 1 · answered Apr 09 '13 at 15:34

0

Not beautiful, but it works. Maybe somebody else can improve it:

x<-c(1:12)
y<-c(1999:2010)
df <- data.frame(x,y)
df$z <- ifelse(x>=10,paste0("01-",x,"-",y),paste0("01-0",x,"-",y))

df$q <- strptime(df$z,"%d-%m-%Y")
class(df$q)

answered Apr 09 '13 at 15:34

Jonas Tundo

6,137
2
35
45

And how to I incorporate that into my current data set as a new column? – Rbeginner Apr 09 '13 at 15:46
with the `$` sign. That's what I do with `z`. – Jonas Tundo Apr 09 '13 at 15:49
It hasn't linked it to each sample... It just gives me this: `(df$q) [1] "1999-01-01" "2000-02-01" "2001-03-01" "2002-04-01" "2003-05-01" "2004-06-01" "2005-07-01" "2006-08-01" "2007-09-01" "2008-10-01" "2009-11-01" "2010-12-01"` – Rbeginner Apr 09 '13 at 15:52
Yes they are, just enter `df`. If you do `df$q` only `q` is shown of course. – Jonas Tundo Apr 09 '13 at 16:33

score 0 · Accepted Answer · answered Apr 09 '13 at 15:52

0

Try this:

transform(tthm.data, Date = as.Date(paste(Year, Month, 1, sep = "-")))

or use "yearmon" class in the zoo package which uses year and month with no day needed:

library(zoo)
transform(tthm.data, Yearmon = as.yearmon(paste(Year, Month, sep = "-")))

answered Apr 09 '13 at 15:52

G. Grothendieck

254,981
17
203
341

Great - both worked, will use the zoo version and see how I get on. Thank you! – Rbeginner Apr 09 '13 at 15:57

CHP · Answer 3 · 2013-04-09T16:03:26.147

Building on G. Grothendieck's solution you can also use. [Also note comment below : ISOdate gives a "POSIXct" result but a "Date" or "yearmon" result would be better here.]

tthm.data
##    WSZ_Code Treatment_Code Year Month TTHM CL2_FREE    BrO3 Colour  PH  TURB
## 1         2              3 1996     1 30.7     0.35 0.00030   0.75 7.4 0.055
## 2         6              1 1996     2 24.8     0.25 0.00055   0.75 6.9 0.200
## 3         7              4 1996     2 60.4     0.05 0.00055   0.75 7.1 0.055
## 6         5              2 1996     3 40.3     0.15 0.00140   2.00 7.7 0.055
## 11        4              1 1996     3 46.5     0.25 0.00055   1.90 7.4 0.150
## 14        2              3 1996     3 28.4     0.25 0.00055   1.80 7.4 0.055


transform(tthm.data, Date = ISOdate(Year, Month, 1))
##    WSZ_Code Treatment_Code Year Month TTHM CL2_FREE    BrO3 Colour  PH  TURB                Date
## 1         2              3 1996     1 30.7     0.35 0.00030   0.75 7.4 0.055 1996-01-01 12:00:00
## 2         6              1 1996     2 24.8     0.25 0.00055   0.75 6.9 0.200 1996-02-01 12:00:00
## 3         7              4 1996     2 60.4     0.05 0.00055   0.75 7.1 0.055 1996-02-01 12:00:00
## 6         5              2 1996     3 40.3     0.15 0.00140   2.00 7.7 0.055 1996-03-01 12:00:00
## 11        4              1 1996     3 46.5     0.25 0.00055   1.90 7.4 0.150 1996-03-01 12:00:00
## 14        2              3 1996     3 28.4     0.25 0.00055   1.80 7.4 0.055 1996-03-01 12:00:00

Alternatively,

library(zoo)
transform(tthm.data, Date = yearmon(Year + (Month - 1)/12))
##    WSZ_Code Treatment_Code Year Month TTHM CL2_FREE    BrO3 Colour  PH  TURB     Date
## 1         2              3 1996     1 30.7     0.35 0.00030   0.75 7.4 0.055 Jan 1996
## 2         6              1 1996     2 24.8     0.25 0.00055   0.75 6.9 0.200 Feb 1996
## 3         7              4 1996     2 60.4     0.05 0.00055   0.75 7.1 0.055 Feb 1996
## 6         5              2 1996     3 40.3     0.15 0.00140   2.00 7.7 0.055 Mar 1996
## 11        4              1 1996     3 46.5     0.25 0.00055   1.90 7.4 0.150 Mar 1996
## 14        2              3 1996     3 28.4     0.25 0.00055   1.80 7.4 0.055 Mar 1996

Note that `ISOdate` gives a `"POSIXct"` result but a `"Date"` or `"yearmon"` result would be better here. — G. Grothendieck, Apr 09 '13 at 16:01

Change two separate columns of month and year into one for date in R

3 Answers3