Ifelse with data.table
Here are my details:
BuyDate SellDate Number
2015-01-01 NA 1
2015-01-01 2015-01-03 1
2015-01-01 2015-01-03 -1
2016-12-09 NA -1
I want to create a new column Start
, so I can get the following result.
BuyDate SellDate Number Start
2015-01-01 NA 1 2015-01-01
2015-01-01 2015-01-03 1 2015-01-01
2015-01-01 2015-01-03 -1 2015-01-03
2016-12-09 NA -1 2016-12-09
Code:
data[,Start:=ifelse(Number=="1",BuyDate,ifelse(is.na(SellDate),BuyDate,SellDate))]
However, I get:
BuyDate SellDate Number Start
2015-01-01 NA 1 1420070400
2015-01-01 2015-01-03 1 1420070400
2015-01-01 2015-01-03 -1 1420243200
2016-12-09 NA -1 1481241600
How can I solve this?
str(data)
Classes ‘data.table’ and 'data.frame':
$BuyDate : POSIXct, format: "2015-01-01" "2015-01-01" "2015-01-01" "2016-12-09"
$SellDate: POSIXct, format: NA "2015-01-03" "2015-01-03" NA
$Number : chr "1" "1" "-1" "-1"
source to share
Better not to use ifelse
, since "Date" can be bound to storage values integer
, instead we assign ( :=
) "Start" as "SellDate", then we specify a boolean condition in 'i' to identify the elements 'NA' in 'Start' or 1 to 'Number' and assign ( :=
) the items in 'BuyDate' that match 'i' 'Start'
data[, Start := SellDate][Number==1, Start := BuyDate
][is.na(Start), Start := BuyDate][]
As @Cath mentioned in the comments, this can be done in two steps
data[, Start := SellDate][(Number==1) | is.na(Start), Start := BuyDate][]
source to share
The Start variable must be converted to POSIXct:
require(dplyr)
data[, Start:= (ifelse(Number=="1",BuyDate,ifelse(is.na(SellDate),BuyDate,SellDate)) %>%
as.POSIXct(origin = "1970-01-01"))]
ADDED:
The following codes work with dplyr
. I'm not sure why dplyr
the above example won't work.
library(dplyr)
library(data.table)
dates <- as.POSIXct(Sys.Date() + 1:20)
dates2 <- as.POSIXct(Sys.Date() + 21:40)
tmp <- data.table(date = dates, date2 = dates2)
tmp[runif(20)>.8, date2 := NA]
tmp[, date3 := (ifelse(is.na(date2), date, date2) %>% as.POSIXct(origin = "1970-01-01"))]
source to share