R add columns by loop in data table

Question

I have a data table like this:

DT <- data.table(ID=rep(c(1:2),each=6), year=rep(c(2003:2006),each=3), month=rep(c(5:8),3), day=rep(c(11:14),3),value=c(101:112))

And I would like to add columns with the conditions:

1, add 5 columns with names: V100, V102, V105, V108, V112

2, in each column, grouped by ID and year, sum up the values less than the value in the column name, eg: for column V112, sum up grouped values less than 112

So the outcome will look like:

DT1 <- data.table(ID=rep(c(1:2),each=2), year=c(2003:2006), "100"=rep(0,4), "102"=c(2,0,0,0),"105"=c(3,2,0,0),"108"=c(3,3,2,0),"112"=rep(3,4))

I tried write codes but couldn't figure out:

degree <- c(100,102,105,108,112)    
 for (d in degree)
{  
   f_year <- function(d) {sum(DT$value <= d)}
   DT <- DT[,d:=f_year(),by=list(ID,year)]
}

Any help would be appreciated!

Rentrop · Accepted Answer · 2015-02-03 23:32:33Z

3

Thats what lapply can be used for.

degree <- c(100, 102, 105, 108, 112)  
myfun <- function(x,y) sum(y <= x)
DT1 <- DT[, lapply(degree, myfun, value), by = .(ID, year)]
setnames(DT1, c("ID", "year", as.character(degree)))

Result:

> DT1
   ID year 100 102 105 108 112
1:  1 2003   0   2   3   3   3
2:  1 2004   0   0   2   3   3
3:  2 2005   0   0   0   2   3
4:  2 2006   0   0   0   0   3

edited Feb 3, 2015 at 23:32

answered Feb 3, 2015 at 22:11

Rentrop

21.6k12 gold badges75 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rentrop Over a year ago

You meen inbetween the input parameters? better now?

Arun Over a year ago

@Floo0, I tend to ignore such useless comments.

Arun · Accepted Answer · 2015-02-03 23:35:03Z

3

Just another way:

cols = c(100,102,105,108,112)
DT[, lapply(cols, function(x) sum(value <= x)), by=.(ID, year)]
#    ID year V1 V2 V3 V4 V5
# 1:  1 2003  0  2  3  3  3
# 2:  1 2004  0  0  2  3  3
# 3:  2 2005  0  0  0  2  3
# 4:  2 2006  0  0  0  0  3

Then you can set the names.

Instead if you'd like to set names directly, then you can create a named list first:

named_cols = setattr(as.list(cols), 'names', cols) 
DT[, lapply(named_cols, function(x) sum(value<=x)), by=.(ID, year)]
#    ID year 100 102 105 108 112
# 1:  1 2003   0   2   3   3   3
# 2:  1 2004   0   0   2   3   3
# 3:  2 2005   0   0   0   2   3
# 4:  2 2006   0   0   0   0   3

answered Feb 3, 2015 at 23:35

Arun

119k28 gold badges290 silver badges396 bronze badges

Collectives™ on Stack Overflow

R add columns by loop in data table

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related