I have a data.frame that looks like this:
> df1
Date Name Surname Amount
2015-07-24 John Smith 200
I want to extrapolate all the infos out of the Date into new columns, so I can get to this:
> df2
Date Year Month Day Day_w Name Surname Amount
2015-07-24 2015 7 24 Friday John Smith 200
So now I'd like to have Year, Month, Day and Day of the Week. How can I do that? When I try to first make the variable a date using as.Date the data.frame gets messed up and the Date all become NA (and no new columns). Thanks for your help!
Maybe this helps:
df2 <- df1
dates <- strptime(as.character(df1$Date),format="%Y-%m-%d")
df2$Year <- format(dates, "%Y")
df2$Month <- format(dates, "%m")
df2$Day <- format(dates, "%d")
df2$Day_w <- format(dates, "%a")
Afterwards you can rearrange the order of columns in df2as you desire.
Here's a simple and efficient solution using the devel version of data.table and its new tstrsplit function which will perform the splitting operation only once and also update your data set in place.
library(data.table)
setDT(df1)[, c("Year", "Month", "Day", "Day_w") :=
c(tstrsplit(Date, "-", type.convert = TRUE), wday(Date))]
df1
# Date Name Surname Amount Year Month Day Day_w
# 1: 2015-07-24 John Smith 200 2015 7 24 6
Note that I've used a numeric representation of the week days because there is an efficient built in wday function for that in the data.table package, but you can easily tweak it if you really need to using format(as.Date(Date), format = "%A") instead.
In order to install the devel version use the following
library(devtools)
install_github("Rdatatable/data.table", build_vignettes = FALSE)
