Update rows depending on the value in following rows in multiple columns

  • A+
Category:Languages

I have a data frame with few thousands rows and selected 2 columns such as:

col1  col2 2     11 3     11 4     12 4     1 5     1 6     2 1     3 1     3 2     4 

In each column values at some point reset to 1, and then go on acummulating up to some value before resetting again. Reset point in each column is independent from the other. What I need is a function detecting reset and updating values BEFORE this reset with negative values from -1 to -3 - for each column depending on its own reset. So needed result would be:

col1  col2 2     -3 3     -2 4     -1 -3     1 -2     1 -1     2 1     3 1     3 2     4 

Any suggestions how this could be done? (Dplyr solution would be most welcome).


How about something like this?

f <- function(x) {     idx <- which(x == 1 & dplyr::lag(x) != 1);     for (i in 1:length(idx)) x[seq(idx[i] - 3, idx[i] - 1)] <- -3:-1;     return(x); }  df[] <- lapply(df, f); #   col1 col2 #1    2   -3 #2    3   -2 #3    4   -1 #4   -3    1 #5   -2    1 #6   -1    2 #7    1    3 #8    1    3 #9    2    4 

Explanation: We define a function f that finds the reset point within a (column) vector; for every reset point, the previous 3 entries are then replaced with -3:-1. We then apply this function to every column of the data.frame.


Sample data

df <- read.table(text = "col1  col2 2     11 3     11 4     12 4     1 5     1 6     2 1     3 1     3 2     4", header = T) 

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: