I have a data table with a different number of entries per ID
.
The third column contains flags
. For each ID, I want to remove all entries from value
column whenever a flag equals 1
until the next 1
occurs.
Example Code:
<code>library(data.table)
set.seed(666)
toyDataset = data.table(ID = c(rep("A",6),
rep("B",8),
rep("C",7),
rep("D",10)),
value = runif(31),
flag = c(c(0,0,1,1,0,0),
c(1,0,0,0,1,0,0,0),
c(0,0,0,0,1,0,0),
c(0,0,1,0,1,0,0,1,0,1)))
desiredResult = toyDataset
desiredResult$value[c(3:4,
7:11,
19:21,
24:26,29:31)] = NA
</code>
<code>library(data.table)
set.seed(666)
toyDataset = data.table(ID = c(rep("A",6),
rep("B",8),
rep("C",7),
rep("D",10)),
value = runif(31),
flag = c(c(0,0,1,1,0,0),
c(1,0,0,0,1,0,0,0),
c(0,0,0,0,1,0,0),
c(0,0,1,0,1,0,0,1,0,1)))
desiredResult = toyDataset
desiredResult$value[c(3:4,
7:11,
19:21,
24:26,29:31)] = NA
</code>
library(data.table)
set.seed(666)
toyDataset = data.table(ID = c(rep("A",6),
rep("B",8),
rep("C",7),
rep("D",10)),
value = runif(31),
flag = c(c(0,0,1,1,0,0),
c(1,0,0,0,1,0,0,0),
c(0,0,0,0,1,0,0),
c(0,0,1,0,1,0,0,1,0,1)))
desiredResult = toyDataset
desiredResult$value[c(3:4,
7:11,
19:21,
24:26,29:31)] = NA
I have one apparently working solution based on various conditions, which looks kinda messy though:
<code>switch = 0
for(i in 1:nrow(toyDataset)){
#reset switch with new ID
if(i > 1 && toyDataset$ID[i] != toyDataset$ID[(i-1)]) switch = 0
#conditions to toggle switch
if(toyDataset$flag[i] == 1 && switch == 0){
switch = 1
toyDataset$value[i] = NA
} else if (toyDataset$flag[i] == 1 && switch == 1){
switch = 0
toyDataset$value[i] = NA
}
if(switch == 1) toyDataset$value[i] = NA
}
</code>
<code>switch = 0
for(i in 1:nrow(toyDataset)){
#reset switch with new ID
if(i > 1 && toyDataset$ID[i] != toyDataset$ID[(i-1)]) switch = 0
#conditions to toggle switch
if(toyDataset$flag[i] == 1 && switch == 0){
switch = 1
toyDataset$value[i] = NA
} else if (toyDataset$flag[i] == 1 && switch == 1){
switch = 0
toyDataset$value[i] = NA
}
if(switch == 1) toyDataset$value[i] = NA
}
</code>
switch = 0
for(i in 1:nrow(toyDataset)){
#reset switch with new ID
if(i > 1 && toyDataset$ID[i] != toyDataset$ID[(i-1)]) switch = 0
#conditions to toggle switch
if(toyDataset$flag[i] == 1 && switch == 0){
switch = 1
toyDataset$value[i] = NA
} else if (toyDataset$flag[i] == 1 && switch == 1){
switch = 0
toyDataset$value[i] = NA
}
if(switch == 1) toyDataset$value[i] = NA
}
I was hoping there was a more efficient solution, perhaps making use of data.table
.
Help is much appreciated!