How to combine data.table with a loop

Question

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 1

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"

Answer 2

You can also use dplyr::mutate

library(dplyr)
data %>% 
        group_by(A) %>% 
        mutate(LABEL=paste(seq_along(A),seq_along(A)+1,sep="-"))

Here you group by A, find the sequence along the group and concatenate sequence+1

Source: local data frame [10 x 3]
Groups: A [3]

       A      B LABEL
   <dbl> <fctr> <chr>
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