Numbering lines within groups in a data frame

Question

Working with a data frame like this:

set.seed(100) df <- data.frame(cat = c(rep("aaa", 5), rep("bbb", 5), rep("ccc", 5)), val = runif(15)) df <- df[order(df$cat, df$val), ] df cat val 1 aaa 0.05638315 2 aaa 0.25767250 3 aaa 0.30776611 4 aaa 0.46854928 5 aaa 0.55232243 6 bbb 0.17026205 7 bbb 0.37032054 8 bbb 0.48377074 9 bbb 0.54655860 10 bbb 0.81240262 11 ccc 0.28035384 12 ccc 0.39848790 13 ccc 0.62499648 14 ccc 0.76255108 15 ccc 0.88216552 

I am trying to add a column with numbering inside each group. Running this method obviously does not use R privileges:

  df$num <- 1 for (i in 2:(length(df[,1]))) { if (df[i,"cat"]==df[(i-1),"cat"]) { df[i,"num"]<-df[i-1,"num"]+1 } } df cat val num 1 aaa 0.05638315 1 2 aaa 0.25767250 2 3 aaa 0.30776611 3 4 aaa 0.46854928 4 5 aaa 0.55232243 5 6 bbb 0.17026205 1 7 bbb 0.37032054 2 8 bbb 0.48377074 3 9 bbb 0.54655860 4 10 bbb 0.81240262 5 11 ccc 0.28035384 1 12 ccc 0.39848790 2 13 ccc 0.62499648 3 14 ccc 0.76255108 4 15 ccc 0.88216552 5 

What would be a good way to do this?

Answer 1

Use ave , ddply , dplyr or data.table :

 df$num <- ave(df$val, df$cat, FUN = seq_along) 

or

 library(plyr) ddply(df, .(cat), mutate, id = seq_along(val)) 

or

 library(dplyr) df %>% group_by(cat) %>% mutate(id = row_number()) 

or (the most efficient memory as it is assigned by reference in DT ):

 library(data.table) DT <- data.table(df) DT[, id := seq_len(.N), by = cat] DT[, id := rowid(cat)] 

Answer 2

To do this, r-faq question more is a complete, alternative version of R with sequence and rle :

 df$num <- sequence(rle(df$cat)$lengths) 

which gives the intended result:

 > df cat val num 4 aaa 0.05638315 1 2 aaa 0.25767250 2 1 aaa 0.30776611 3 5 aaa 0.46854928 4 3 aaa 0.55232243 5 10 bbb 0.17026205 1 8 bbb 0.37032054 2 6 bbb 0.48377074 3 9 bbb 0.54655860 4 7 bbb 0.81240262 5 13 ccc 0.28035384 1 14 ccc 0.39848790 2 11 ccc 0.62499648 3 15 ccc 0.76255108 4 12 ccc 0.88216552 5 

If df$cat is a factor variable, you must first wrap it in as.character :

 df$num <- sequence(rle(as.character(df$cat))$lengths) 

Answer 3

Here is an option that uses a for loop across groups rather than line by line (e.g. OP)

 for (i in unique(df$cat)) df$num[df$cat == i] <- seq_len(sum(df$cat == i)) 

Answer 4

Here is a small improvement trick that allows you to sort "val" within groups:

 # 1. Data set set.seed(100) df <- data.frame( cat = c(rep("aaa", 5), rep("ccc", 5), rep("bbb", 5)), val = runif(15)) # 2. 'dplyr' approach df %>% arrange(cat, val) %>% group_by(cat) %>% mutate(id = row_number()) 

Answer 5

I would like to add a variant of data.table using the rank() function which provides an additional opportunity to change the order and, therefore, makes it a little more flexible than the seq_len() solution and is quite similar to the row_number function in an RDBMS.

 # Variant with ascending ordering library(data.table) dt <- data.table(df) dt[, .( val , num = rank(val)) , by = list(cat)][order(cat, num),] cat val num 1: aaa 0.05638315 1 2: aaa 0.25767250 2 3: aaa 0.30776611 3 4: aaa 0.46854928 4 5: aaa 0.55232243 5 6: bbb 0.17026205 1 7: bbb 0.37032054 2 8: bbb 0.48377074 3 9: bbb 0.54655860 4 10: bbb 0.81240262 5 11: ccc 0.28035384 1 12: ccc 0.39848790 2 13: ccc 0.62499648 3 14: ccc 0.76255108 4 # Variant with descending ordering dt[, .( val , num = rank(-val)) , by = list(cat)][order(cat, num),] 

Answer 6

Another dplyr might be:

 df %>% group_by(cat) %>% mutate(num = 1:n()) cat val num <fct> <dbl> <int> 1 aaa 0.0564 1 2 aaa 0.258 2 3 aaa 0.308 3 4 aaa 0.469 4 5 aaa 0.552 5 6 bbb 0.170 1 7 bbb 0.370 2 8 bbb 0.484 3 9 bbb 0.547 4 10 bbb 0.812 5 11 ccc 0.280 1 12 ccc 0.398 2 13 ccc 0.625 3 14 ccc 0.763 4 15 ccc 0.882 5