data frame corrections with moving window

Question

Problem: With a data frame like:

df <- data.frame('ID'  = c(1,   1,   1,   1,   2,   2,   2,   2),
                 'UD'  = c(0,   5,   10,  15,  0,   0,   10,  15),
                 'LD'  = c(5,   10,  15,  20,  5,   10,  15,  20),
                 'VAL' = c(1.2, 3.6, 5.7, 8.0, 5.2, 5.6, 8.7, 3.1))

for each ID group, the value of LD must match the value of UD on the next row down. So df[6, 2] should be 5, not 0.

I've been trying to write a function that can move through a data frame like this one and make that kind of correction. I think I've gotten close with the following, but the edited value is being overwritten as rollapply reassembles its outputs.

fix <- function(df) {
  df2 <- by(df, as.factor(df$ID), FUN = function(x) {

    rollapply(x, width = 2, FUN = function(y) {

      y[2, 2] <- ifelse(y[2, 2] != y[1, 3], y[1, 3], y[2, 2])
      print(y) #  test
      return(y)

      }, by.column = FALSE)
    print('x:') # test
    print(x)    # test
    return(x)
  })

  out <- do.call('rbind', df2)
  return(out)

  }

Is there a way to fix this, or a better alternative approach to the issue?

edit - expected output:

df2 <- data.frame('ID'  = c(1,   1,   1,   1,   2,   2,   2,   2),
                  'UD'  = c(0,   5,   10,  15,  0,   5,   10,  15),
                  'LD'  = c(5,   10,  15,  20,  5,   10,  15,  20),
                  'VAL' = c(1.2, 3.6, 5.7, 8.0, 5.2, 5.6, 8.7, 3.1))

yeah, that's the problem. see edit. – obrl_soil Jan 31 '17 at 05:49 — obrl_soil, Jan 31 '17 at 05:49

BLT · Accepted Answer · 2017-01-31T06:03:41.083

You can use dplyr. Group the data by 'ID', then set 'UD' to be the same as 'LD', but shifted by one (using mutate and lag). When you get to a new 'ID', set that first one to 0 (NA is the default).

library(dplyr)

fixed_df <- df %>% 
  group_by(ID) %>% 
  mutate(UD = lag(LD, default = 0))
fixed_df

#Source: local data frame [8 x 4]
#Groups: ID [2]
# 
#     ID    UD    LD   VAL
#  <dbl> <dbl> <dbl> <dbl>
#1     1     0     5   1.2
#2     1     5    10   3.6
#3     1    10    15   5.7
#4     1    15    20   8.0
#5     2     0     5   5.2
#6     2     5    10   5.6
#7     2    10    15   8.7
#8     2    15    20   3.1

That's a lot simpler! Thanks. – obrl_soil Jan 31 '17 at 06:09 — obrl_soil, Jan 31 '17 at 06:09

score 2 · Answer 2 · answered Jan 31 '17 at 05:58

2

We can use data.table. Convert the 'data.frame' to 'data.table' (setDT(df)), grouped by 'ID', we assign (:=) the lag of 'LD' (shift) as the 'UD' column

library(data.table)
setDT(df)[, UD := shift(LD, fill=0), by = ID]
df
#    ID UD LD VAL
#1:  1  0  5 1.2
#2:  1  5 10 3.6
#3:  1 10 15 5.7
#4:  1 15 20 8.0
#5:  2  0  5 5.2
#6:  2  5 10 5.6
#7:  2 10 15 8.7
#8:  2 15 20 3.1

answered Jan 31 '17 at 05:58

akrun

874,273
37
540
662

1

Neat, thanks. I'll stick with the dplyr approach just because I'm fairly unfamiliar with data.table. – obrl_soil Jan 31 '17 at 06:09

data frame corrections with moving window

2 Answers2