vendredi 20 mars 2020

using ifelse in data.table in R to assign values

This is a fictional example with data available in R. I create some extra columns such as gear, gender and age. These is made up data and makes no sense at all, but serves illustrational purposes.

data(mtcars)
setDT(mtcars) 
data(iris) 
setDT(iris)
iris$carb <- sample(1:4, length(iris$Sepal.Length), replace = T)
iris$gear <- sample(1:7, length(iris$Sepal.Length), replace = T)
iris$gender <- sample(1:3, length(iris$Sepal.Length), replace = T)
iris$age <- sample(1:14, length(iris$Sepal.Length), replace = T)
names(iris)

Then I want to create a merged filed, that is matched on the variable gear which is available in both DT. I only keep selected columns and change the table from long to wide format. So far so good:

combi <- dcast(merge(mtcarsTOTAL[,.(cost, gear)], iris[, .N, .(carb, gear, gender, age)], by = "gear"), 
               gender + age ~ carb, value.var = "cost", fun.aggregate = sum)

Now, i want to build in an budgeted variable. If the cost is higher than the budget, then use the budget value in the sum function. if its lower, take the actual cost value for the sum function. I am stuck on this one. Is it possible to somehow build it in as a condition in the data.table? I have tried something below, but well, its not working.

budget <- c(3000)

combi <- dcast(merge(mtcarsTOTAL[,.(cost, gear)], iris[, .N, .(carb, gear, gender, age)], by = "gear"), 
               gender + age ~ carb, value.var = ifelse( cost > budget, budget), fun.aggregate = sum)

Aucun commentaire:

Enregistrer un commentaire