Ifelse behavior within data.table (R)

Question

I have a data.table full of some consumer products. I've created some distinction for the products as 'low', 'high', or 'unknown' quality. The data are time series, and I'm interested in smoothing out some seasonality in the data. If a product's raw classification (the classification churned out by the algorithm I used to determine quality) is 'low' quality in period X, but its raw classification was 'high' quality in period X-1, I'm reclassifying that product as 'high' quality for period X. This process is done within some sort of product group distinction.

To accomplish this, I've got something like the following:

require(data.table)

# lag takes a column and lags it by one period,
# padding with NA

lag <- function(var) {
    lagged <- c(NA, 
                var[1:(length(var)-1)])
    return(lagged)
}

set.seed(120)

foo <- data.table(group = c('A', rep(c('B', 'C', 'D'), 5)),
                  period = c(1:16),
                  quality = c('unknown', sample(c('high', 'low', 'unknown'), 15, replace = TRUE)))

foo[, quality_lag := lag(quality), by = group]

foo[, quality_1 := ifelse(quality == 'low' & quality_lag == 'high',
                          'high',
                          quality)]

Taking a look at foo:

    group period quality quality_lag quality_1
 1:     A      1 unknown          NA   unknown
 2:     B      2     low          NA        NA
 3:     C      3    high          NA      high
 4:     D      4     low          NA        NA
 5:     B      5 unknown         low   unknown
 6:     C      6    high        high      high
 7:     D      7     low         low       low
 8:     B      8 unknown     unknown   unknown
 9:     C      9    high        high      high
10:     D     10 unknown         low   unknown
11:     B     11 unknown     unknown   unknown
12:     C     12     low        high      high
13:     D     13 unknown     unknown   unknown
14:     B     14    high     unknown      high
15:     C     15    high         low      high
16:     D     16 unknown     unknown   unknown

So, quality_1 is mostly what I want. If period X is 'low' and period X-1 is 'high', we see the reclassification to 'high' occurs and everything is left mostly intact from quality. However, when quality_lag is NA, 'low' gets reclassified to NA in quality_1. This is not an issue with 'high' or 'unknown'.

That is, the first four rows of foo should look like this:

   group period quality quality_lag quality_1
 1:     A      1 unknown          NA   unknown
 2:     B      2     low          NA       low
 3:     C      3    high          NA      high
 4:     D      4     low          NA       low

Any thoughts on what is causing this?

For starters, you don't need to reinvent the wheel. `data.table` v>= 1.9.5 already has `lag` function called `shift`, so you could simply do `foo[, quality_lag := shift(quality), by = group]` — David Arenburg, Jan 29 '15 at 20:07
Second of all, `ifelse` is truly an awefull function and I always trying to avoid it. My advice would be just doing something like `foo[, quality_1 := quality][quality == 'low' & quality_lag == 'high', quality_1 := "high"]` — David Arenburg, Jan 29 '15 at 20:17
@DavidArenburg 's point is dead on. Also, see http://stackoverflow.com/q/16275149/1492421 for more info on ifelse — Ricardo Saporta, Jan 29 '15 at 20:20
Thanks for the tip about `shift` in data.table v >= 1.9.5, @DavidArenburg. I'll give your suggestion a shot, since getting rid of an `ifelse` statement where I can does sound appealing. — thagzone, Jan 29 '15 at 20:29
Ah, @DavidArenburg, your solution is so simple. Not sure why I didn't think of subsetting like that. If you want to write your comments up as an answer, I'll gladly accept it. — thagzone, Jan 29 '15 at 20:37

score 6 · Accepted Answer · edited May 23 '17 at 12:31

For starters, the Development version on GitHub already has an efficient lag function called shift which can be used both as lag or lead (and has some additional functionality too, see ?shift).

Take also a look here as there is a bunch of other new functions that are now present in v >= 1.9.5

So under v >= 1.9.5 we could simply do

foo[, quality_lag := shift(quality), by = group]

Though even under v < 1.9.5 you could make a use of .N instead of creating this function in the following manner

foo[, quality_lag2 := c(NA, quality[-.N]), by = group]

Regarding your second question, I would recommend avoiding ifelse all together for many reasons specified here

One possible alternative would be, just to use a simple indexing as in

foo[, quality_1 := quality][quality == 'low' & quality_lag == 'high', quality_1 := "high"]

This solution has a bit overhead, of calling [.data.table twice but it will still be much more efficient/safe than the ifelse solution.

score -3 · Answer 2 · answered Jan 29 '15 at 20:18

Your problem is that ifelse(NA, 1, 2) == NA, and when you do NA == 'low' the result is NA. An easy fix is to represent NA as strings in your lag function. Here is working version of your code:

require(data.table)

# lag takes a column and lags it by one period,
# padding with NA

lag <- function(var) {
    lagged <- c("NA", 
                var[1:(length(var)-1)])
    return(lagged)
}

set.seed(120)

foo <- data.table(group = c('A', rep(c('B', 'C', 'D'), 5)),
                  period = c(1:16),
                  quality = c('unknown', sample(c('high', 'low', 'unknown'), 15, replace = TRUE)))

foo[, quality_lag := lag(quality), by = group]

foo[, quality_1 := ifelse(quality == 'low' & quality_lag == 'high',
                          'high',
                          quality)]

You should **never** represent NA as a character mainly because `is.na("NA") # [1] FALSE` — Rich Scriven, Jan 29 '15 at 20:44

Ifelse behavior within data.table (R)

2 Answers2