R: get function in a data.table with ifelse criteria

Question

I have this sample data

rawdata <- data.table(  name = c("a", "b", "c", "d", "e"),  btax = c(2000, 200, 300, 600, 700),  rate = c(0.3, 0.2, 0.1, 0.05, 0.5))

I am taking input from excel where the cells are named region. these named ranges tells which columns are available and which ones to use from the rawdata . Let say I am trying to calculate net figure after tax. I have the tax rate always given but the amount is some times after tax and some times in gross. so using an ifelse I need to create a column after tax which is use after tax if it is given else calculate it. and vice verse.

both gross and net columns are required with either of them given.

This is the code which I have tried.

beforetax<-read.xlsx(xlsxFile="//xxxxxxxx/Book1.xlsx",namedRegion="beforetax",   colNames=FALSE)[,1];
aftertax<-read.xlsx(xlsxFile="//xxxxxxxxx/Book1.xlsx",namedRegion="aftertax",   colNames=FALSE)[,1];
taxrate<-read.xlsx(xlsxFile="//xxxxxxxxx/Book1.xlsx",namedRegion="taxrate",   colNames=FALSE)[,1];

rawdata[, net := get(beforetax)*get(rate)];
rawdata[, net := get(aftertax)];

column D shows the name of the cell of column E

Output should look like this:

data.table(  name = c("a", "b", "c", "d", "e"),  btax = c(2000, 200, 300, 600, 700),  rate = c(0.3, 0.2, 0.1, 0.05, 0.5), BeforeTax1 = c(2000, 200, 300, 600, 700), AfterTax1 = c(1400, 160, 270, 570, 350))

Answer 1

[Rewritten now that I think I understand the problem]

Reproducible example:

library(data.table)

rawdata <- data.table(name = c("a", "b", "c", "d", "e"),
                      btax = c(2000, 200, 300, 600, 700),
                      rate = c(0.3, 0.2, 0.1, 0.05, 0.5))   

# metadata (read from Excel using openxlsx::read.xlsx)
beforetax <- "btax"
aftertax  <- NULL
taxrate   <- "rate"

Solution (data.table < V1.15.0):

# advisable to check one or other of before/after col names is NULL 
stopifnot(sum(sapply(list(beforetax, aftertax), is.null)) == 1L)

if (is.null(aftertax)) {
  rawdata[, ':=' (BeforeTax1 = get(beforetax),
                  AfterTax1  = get(beforetax) * (1 - get(taxrate)))]
} else {
  rawdata[, ':=' (BeforeTax1 = get(aftertax) / (1 - get(taxrate)),
                  AfterTax1  = get(aftertax))]
}    

Result:

> rawdata
     name  btax  rate BeforeTax1 AfterTax1
   <char> <num> <num>      <num>     <num>
1:      a  2000  0.30       2000      1400
2:      b   200  0.20        200       160
3:      c   300  0.10        300       270
4:      d   600  0.05        600       570
5:      e   700  0.50        700       350

Alternative solution (data.table >= V1.15.0) avoiding get which is now discouraged in favour of env (and also using the let alias for ':='):

if (is.null(aftertax)) {
  rawdata[, let(BeforeTax1 = b, AfterTax1 = b * (1 - r)),
          env = list(r = taxrate, b = beforetax)]
} else {
  rawdata[, let(BeforeTax1 = a / (1 - r), AfterTax1 = a),
          env = list(r = taxrate, a = aftertax)]
}    

See this vignette on Programming with data.table as mentioned by @SamR