Tomas Ericsson
Reputation: 347
first lag in multiple columns
I have factor variable that occurs in two columns and and now I want first lag, no matter what column factor last appeared in.
Consider following data.table.
require(data.table)
set.seed(21)
dt <- data.table(item1 = c(rep(sample(letters[1:5]), 2), sample(letters[6:10])),
item2 = c(rep(sample(letters[6:10]), 2), sample(letters[1:5])),
value1 = rnorm(15, 5, 2),
value2 = rnorm(15, 5, 2),
iteration = rep(1:3, each = 5))
> dt
item1 item2 value1 value2 iteration
1: d i 0.4464375 6.491179 1
2: b j 6.5148245 5.665638 1
3: c f 3.9031889 2.751919 1
4: a g 5.3450990 3.587738 1
5: e h 6.1257061 3.544912 1
6: d i 8.0236359 1.331371 2
7: b j 6.3180503 4.184624 2
8: c f 7.2440561 5.053722 2
9: a g 3.4307173 6.823257 2
10: e h 4.1486154 8.268693 2
11: j a 5.7859952 5.121371 3
12: f c 5.0735143 8.695145 3
13: i e 2.9358327 5.160250 3
14: g d 2.4702771 7.837112 3
15: h b 4.5460694 7.917232 3
I have tried to solve this with data.table package.
dt[, lag1 := c(NA, value1), by = item1]
dt[, lag2 := c(NA, value2), by = item2]
dt
item1 item2 value1 value2 iteration lag1 lag2
1: d i 0.4464375 6.491179 1 NA NA
2: b j 6.5148245 5.665638 1 NA NA
3: c f 3.9031889 2.751919 1 NA NA
4: a g 5.3450990 3.587738 1 NA NA
5: e h 6.1257061 3.544912 1 NA NA
6: d i 8.0236359 1.331371 2 0.4464375 6.491179
7: b j 6.3180503 4.184624 2 6.5148245 5.665638
8: c f 7.2440561 5.053722 2 3.9031889 2.751919
9: a g 3.4307173 6.823257 2 5.3450990 3.587738
10: e h 4.1486154 8.268693 2 6.1257061 3.544912
11: j a 5.7859952 5.121371 3 NA NA
12: f c 5.0735143 8.695145 3 NA NA
13: i e 2.9358327 5.160250 3 NA NA
14: g d 2.4702771 7.837112 3 NA NA
15: h b 4.5460694 7.917232 3 NA NA
I could probably solve this by creating one column for item and one for value, but is there a better solution?
And just to be clear, my expected value on lag1 on row 11 is 4.184624.
I will also need lag for item2 and did this dplyr.
dt %>%
mutate(nr = 1:nrow(dt)) %>%
gather(key, value, -nr, -iteration) %>%
mutate(key = ifelse(key == "item1" | key == "item2", "item", "value"),
variabel = rep(c(1, 2), 2, each = nrow(dt))) %>%
spread(key, value) %>%
group_by(item) %>%
arrange(nr) %>%
mutate(lag = lag(value)) %>%
gather(key, value, -iteration, -nr, -variabel) %>%
unite(key, c("key", "variabel"), sep = "") %>%
spread(key, value)
iteration nr item1 item2 lag1 lag2 value1 value2
* <int> <int> <chr> <chr> <chr> <chr> <chr> <chr>
1 1 1 e f <NA> <NA> 4.48327811883486 5.98823833422944
2 1 2 b i <NA> <NA> 6.21252978898878 3.6803830789734
3 1 3 d g <NA> <NA> 5.62689643314086 7.00228385274896
4 1 4 c h <NA> <NA> 5.10720616395708 7.14416894881173
5 1 5 a j <NA> <NA> 7.25650757535391 6.51153141154262
6 2 6 e f 4.48327811883486 5.98823833422944 3.88373308164829 2.08907058913021
7 2 7 b i 6.21252978898878 3.6803830789734 8.07191789162847 6.88574195362948
8 2 8 d g 5.62689643314086 7.00228385274896 4.87510729533042 1.25944984673148
9 2 9 c h 5.10720616395708 7.14416894881173 5.0431504307243 4.4934555124612
10 2 10 a j 7.25650757535391 6.51153141154262 0.820345123625779 4.41487625686153
11 3 11 g d 1.25944984673148 4.87510729533042 3.37822264689098 5.43753611910662
12 3 12 j a 4.41487625686153 0.820345123625779 -0.88757977661203 2.28986114731552
13 3 13 i e 6.88574195362948 3.88373308164829 4.96240860503556 4.75454561215201
14 3 14 h b 4.4934555124612 8.07191789162847 4.29063975464589 4.09626986248512
15 3 15 f c 2.08907058913021 5.0431504307243 5.07114037497055 5.19449624162733
Upvotes: 1
Views: 306
Answers (2)
MKR
Reputation: 20095
A solution can be found by using melt after adding a column for row number.
library(data.table)
#Add a column to represent row number in current table. This will be used
#later to sort data.table to find correct `lag`
dt[,rn:=.I]
#Use melt to transfer values in long format. value1 contains "items" and
#value2 contains "values"
dt<-melt(dt, id=c("iteration","rn"), measure=list(1:2,c("value1","value2")),
value.name = c("item","value"))
#The order in original table is row number, item1 and item2. The same can be
#achieved by sorting on "rn" and "variable"
dt[order(rn,variable), lag := shift(value), by = item]
dt[variable==1,][dt[variable==2,],.(item1 = item, item2 = i.item, value1 = value,
value2=i.value, iteration, lag1 = lag, lag2 = i.lag), on=("rn")]
# item1 item2 value1 value2 iteration lag1 lag2
# 1: d i 0.4464375 6.491179 1 NA NA
# 2: b j 6.5148245 5.665638 1 NA NA
# 3: c f 3.9031889 2.751919 1 NA NA
# 4: a g 5.3450990 3.587738 1 NA NA
# 5: e h 6.1257061 3.544912 1 NA NA
# 6: d i 8.0236359 1.331371 2 0.4464375 6.491179
# 7: b j 6.3180503 4.184624 2 6.5148245 5.665638
# 8: c f 7.2440561 5.053722 2 3.9031889 2.751919
# 9: a g 3.4307173 6.823257 2 5.3450990 3.587738
# 10: e h 4.1486154 8.268693 2 6.1257061 3.544912
# 11: j a 5.7859952 5.121371 3 4.1846241 3.430717
# 12: f c 5.0735143 8.695145 3 5.0537224 7.244056
# 13: i e 2.9358327 5.160250 3 1.3313712 4.148615
# 14: g d 2.4702771 7.837112 3 6.8232573 8.023636
# 15: h b 4.5460694 7.917232 3 8.2686930 6.318050
Upvotes: 3
chinsoon12
Reputation: 25223
Posting another similar approach. Similar in using an elongated version of item1 + item2 into a long data.table. Difference is in using joins.
There are 2 possible situations:
1) the lag is always in the immediate previous iteration, then the following code using normal join should work:
library(data.table)
set.seed(21)
dt <- data.table(item1 = c(rep(sample(letters[1:5]), 2), sample(letters[6:10])),
item2 = c(rep(sample(letters[6:10]), 2), sample(letters[1:5])),
value1 = rnorm(15, 5, 2),
value2 = rnorm(15, 5, 2),
iteration = rep(1:3, each = 5))
#if that first lag can always be found in previous iteration
dt[.(iitem=c(item1, item2), ivalue=c(value1, value2), iiteration=c(iteration + 1, iteration + 1)),
lag1 := ivalue,
on=c(item1="iitem", iteration="iiteration")]
dt[.(iitem=c(item1, item2), ivalue=c(value1, value2), iiteration=c(iteration + 1, iteration + 1)),
lag2 := ivalue,
on=c(item2="iitem", iteration="iiteration")]
dt
# item1 item2 value1 value2 iteration lag1 lag2
# 1: d i 0.4464375195067456 6.491178609416053 1 NA NA
# 2: b j 6.5148244502509627 5.665638360665036 1 NA NA
# 3: c f 3.9031888919439428 2.751919085284464 1 NA NA
# 4: a g 5.3450989557007524 3.587738435542055 1 NA NA
# 5: e h 6.1257061355108435 3.544912270783058 1 NA NA
# 6: d i 8.0236359188753603 1.331371229451156 2 0.4464375195067456 6.491178609416053
# 7: b j 6.3180503383288116 4.184624119479032 2 6.5148244502509627 5.665638360665036
# 8: c f 7.2440561493491140 5.053722389597528 2 3.9031888919439428 2.751919085284464
# 9: a g 3.4307172617070858 6.823257275121762 2 5.3450989557007524 3.587738435542055
# 10: e h 4.1486154223793710 8.268692951017332 2 6.1257061355108435 3.544912270783058
# 11: j a 5.7859951827443368 5.121371228719468 3 4.1846241194790323 3.430717261707086
# 12: f c 5.0735142596491132 8.695145055731583 3 5.0537223895975281 7.244056149349114
# 13: i e 2.9358326775434151 5.160249909302514 3 1.3313712294511557 4.148615422379371
# 14: g d 2.4702770572371642 7.837111765957783 3 6.8232572751217617 8.023635918875360
# 15: h b 4.5460694295527579 7.917231870893728 3 8.2686929510173321 6.318050338328812
2) if the lag might be in earlier iterations, then the following code using non-equi joins should help
library(data.table)
set.seed(21)
dt <- data.table(item1 = c(rep(sample(letters[1:5]), 2), sample(letters[6:10])),
item2 = c(rep(sample(letters[6:10]), 2), sample(letters[1:5])),
value1 = rnorm(15, 5, 2),
value2 = rnorm(15, 5, 2),
iteration = rep(1:3, each = 5))
#remove iteration=2, item1=c, item2=f to show finding lag from earlier iterations
dt <- dt[-8,]
#if that first lag can be found in even earlier iteration, using non-equi joins as follows:
elongated <- dt[,.(item=c(item1, item2), ivalue=c(value1, value2), iteration=c(iteration, iteration), cpyalliter=c(iteration, iteration))]
dt[, lag1 := elongated[.SD, on=.(item=item1, iteration < iteration)][,
last(ivalue), by=.(item1=item, item2, value1, value2, iteration)]$V1 ]
dt[, lag2 := elongated[.SD, on=.(item=item2, iteration < iteration)][,
last(ivalue), by=.(item1, item2=item, value1, value2, iteration)]$V1 ]
dt
# item1 item2 value1 value2 iteration lag1 lag2
# 1: d i 0.4464375195067456 6.491178609416053 1 NA NA
# 2: b j 6.5148244502509627 5.665638360665036 1 NA NA
# 3: c f 3.9031888919439428 2.751919085284464 1 NA NA
# 4: a g 5.3450989557007524 3.587738435542055 1 NA NA
# 5: e h 6.1257061355108435 3.544912270783058 1 NA NA
# 6: d i 8.0236359188753603 1.331371229451156 2 0.4464375195067456 6.491178609416053
# 7: b j 6.3180503383288116 4.184624119479032 2 6.5148244502509627 5.665638360665036
# 8: a g 3.4307172617070858 6.823257275121762 2 5.3450989557007524 3.587738435542055
# 9: e h 4.1486154223793710 8.268692951017332 2 6.1257061355108435 3.544912270783058
# 10: j a 5.7859951827443368 5.121371228719468 3 4.1846241194790323 3.430717261707086
# 11: f c 5.0735142596491132 8.695145055731583 3 2.7519190852844644 3.903188891943943
# 12: i e 2.9358326775434151 5.160249909302514 3 1.3313712294511557 4.148615422379371
# 13: g d 2.4702770572371642 7.837111765957783 3 6.8232572751217617 8.023635918875360
# 14: h b 4.5460694295527579 7.917231870893728 3 8.2686929510173321 6.318050338328812
I wonder if there is a way to write the 2nd case more succinctly (i.e. with a little less chaining)
Upvotes: 0
Related Questions
- Use lagged columns in data.table in another column
- How to lag multiple specific columns of a data frame in R
- Create lagged columns using data.table
- How to take lag on mutliple columns as given below in R or Python
- data.table lag of one column
- R - Computations with lag variable by group
- Using lead/lag with multiple variables in data.table
- lag of multiple column using r
- R: How to calculate lag for multiple columns by group for data table
- How to lag data table value by more than one