Mario M. Mario M. - 13 days ago 8
R Question

How to combine data.table with a loop

My dataframe looks like this:

> data <- data.frame(A=c(1,1,1,2,2,3,3,3,3,3), B=c("1A","1B","1C","2A","2B","3A","3B","3C","3D","3E"))


I want to add a new variable labelled in function of variable A and B. The result must be:

> data
A B LABEL
1 1 1A 1-2
2 1 1B 2-3
3 1 1C 3-4
4 2 2A 1-2
5 2 2B 2-3
6 3 3A 1-2
7 3 3B 2-3
8 3 3C 3-4
9 3 3D 4-5
10 3 3E 5-6


I try this with data.table function. The code I try:

> setDT(data)
> data <- data[,list(LABEL = for(i in 1:length(A)){paste(i, "-", i+1, sep="")}),by=c("A","B")]


Message Error: "Error in
[.data.table
(data, , list(LABEL = for (i in 1:length(A)) { :
Column 1 of j's result for the first group is NULL. We rely on the column types of the first result to decide the type expected for the remaining groups (and require consistency). NULL columns are acceptable for later groups (and those are replaced with NA of appropriate type and recycled) but not for the first. Please use a typed empty vector instead, such as integer() or numeric().
"

Answer

We can use shift to create the 'lead' values of the sequence after grouping by 'A', and paste it with the sequence of rows to create the 'LABEL'

library(data.table)
setDT(data)[, LABEL := paste(seq_len(.N), shift(seq_len(.N),
                          type='lead', fill= .N+1), sep="-"), by = A]

Or

setDT(data)[, LABEL := paste(seq_len(.N), seq_len(.N)+1, sep = "-"), by = A]
data
#    A  B LABEL
# 1: 1 1A   1-2
# 2: 1 1B   2-3
# 3: 1 1C   3-4
# 4: 2 2A   1-2
# 5: 2 2B   2-3
# 6: 3 3A   1-2
# 7: 3 3B   2-3
# 8: 3 3C   3-4
# 9: 3 3D   4-5
#10: 3 3E   5-6

Or we can use base R methods

i1 <- sequence(tabulate(data$A))
data$LABEL <- paste(i1, i1+1, sep="-")
data$LABEL
#[1] "1-2" "2-3" "3-4" "1-2" "2-3" "1-2" "2-3" "3-4" "4-5" "5-6"