CODEWITHSUNDEEP
rdata.table
dal233
dal233

Reputation: 80

Refer to column numbers by using a number in another column when using data.table

I have the following data table:

library(data.table)

dt <- data.table("one" =c(100,200,300,400,500,600))
dt <- dt[,two:=round(one*1.05,0)][,three:=round(two*1.03,0)][,four:=round(three*1.07,0)][,five:=round(four*1.05,0)][,six:=round(five*1.1,0)][,curr:=c(3,4,5,6)]

> dt
   one two three four five six curr
1:  100 105   108  116  122 134    3
2:  200 210   216  231  243 267    4
3:  300 315   324  347  364 400    5
4:  400 420   433  463  486 535    6
5:  500 525   541  579  608 669    3
6:  600 630   649  694  729 802    4

I'd like to use data.table to grab, row-by-row, the number in column "curr" and calculate the mean of that column number in the table and the column immediately preceding it. Eg., for the first row it would grab the value of 108 in column "three" and the value of 105 in column "two" and give 106.5. For the second row, it would take the mean of 231 and 216 etc etc.

Upvotes: 0

Views: 97

Answers (2)

dal233
dal233

Reputation: 80

Here's another solution (in answer to my own question):

dt[, rowpos := .I]  #Using Rafa's idea above
dt[, c("new","new_prev") := .SD[, c(curr,curr-1), with=FALSE], by = rowpos]
dt$mean <- rowMeans(subset(dt, select = c(new, new_prev)),na.rm=TRUE)

> dt 
  one two three four five six curr rowpos new new_prev  mean
1: 100 105   108  116  122 134    3      1 108      105 106.5
2: 200 210   216  231  243 267    4      2 231      216 223.5
3: 300 315   324  347  364 400    5      3 364      347 355.5
4: 400 420   433  463  486 535    6      4 535      486 510.5
5: 500 525   541  579  608 669    3      5 541      525 533.0
6: 600 630   649  694  729 802    4      6 694      649 671.5

Upvotes: 0

rafa.pereira
rafa.pereira

Reputation: 13827

This will do the work:

# create a column with row positions
dt[, rowpos := .I]

# create a function that will be applied to every pair of column and row position
myfunc <- function(colpos,rowpos) { mean(c(as.matrix(dt)[rowpos,colpos], 
                                           as.matrix(dt)[rowpos,(colpos-1)])) }

# apply function
dt[ , var := myfunc(curr, rowpos) , by = rowpos]

#>    one two three four five six curr rowpos   var
#> 1: 100 105   108  116  122 134    3      1 106.5
#> 2: 200 210   216  231  243 267    4      2 223.5
#> 3: 300 315   324  347  364 400    5      3 355.5
#> 4: 400 420   433  463  486 535    6      4 510.5
#> 5: 500 525   541  579  608 669    3      5 533.0
#> 6: 600 630   649  694  729 802    4      6 671.5

Upvotes: 1

Related Questions