mercredi 23 septembre 2020

ifelse replace value if it is lower than previous

I am working with a dataset which has some errors in the data. Numbers are sometimes registered wrong. Here is some toy data example:

Fake data

The issue is that the Reversal column should only be counting up (per unique ID). So in a vector of 0,0,0,1,1,1,0,1,2,2,0,0,2,3, the 0's following the 1 and 2 should not be 0's. Instead, they should be equal to whatever value came before. I tried to remedy this by using the lag function from the dplyr package:

Data$Reversal <- ifelse(Data$Reversal < lag(Data$Reversal), lag(Data$Reversal), Data$Reversal) . But this results in numerous issues:

  1. The first value becomes NA. I've tried using the default=Data$Reversal call in the lag function but to no avail.
  2. The Reversal value should reset to 0 for each Unique ID. Now it continues across ID's. I tried a messy code using group_by(ID) but could not get this to work, as it broke my earlier ifelse function.
  3. This only works when there is 1 error. But if there are two errors in a row it only fixes 1 value.

Alternatively, I found this thread in which the answer provided by Andrie also seems promising. This fixes problem 1 and 3, but I can't get this code to work per ID (using the group_by function).

Andrie's answer:


local({
  r <- rle(data)
  x <- r$values
  x0 <- which(x==0) # index positions of zeroes
  xt <- x[x0-1]==x[x0+1] # zeroes surrounded by same value
  r$values[x0[xt]] <- x[x0[xt]-1] # substitute with surrounding value
  inverse.rle(r)
})

Any help would be much appreciated.

Aucun commentaire:

Enregistrer un commentaire