How to add a group id without looping?

r

Solution

How about a short `c++` written `for` loop using `Rcpp`. This little function takes a `numeric` vector, i.e. your `ordernum` column and a `threshold` argument (the cumulative sum you want to start a new ID from) and returns a vector of IDs of length equal to the input vector. Should run relatively quickly as it's a `for` loop in `c++`. The code snippet below will install `Rcpp` for you if you haven't already got it installed and will compile the function ready for use. Just copy and paste into R...

if( !require(Rcpp) ) install.packages("Rcpp"); require(Rcpp)
Rcpp::cppFunction( ' NumericVector grpid( NumericVector x , int threshold ){
  int n = x.size();
  NumericVector out(n);
  int tot = 0;
  int id = 1;
  for( int i = 0; i < n; ++i){
    tot += x[i];
    out[i] = id;
    if( tot >= threshold ){
      id += 1;
      tot = 0;
    }
  }
  return out;
}')

Then to use the function just use it like any other R function, supplying the relevant arguments:

df$groupid <- grpid( df$ordernum , 30 )
#  productid ordernum groupid
#1        p1       10       1
#2        p2       20       1
#3        p3       30       2
#4        p4        5       3
#5        p5       20       3
#6        p6        8       3

BENCHMARKING COMPARISON

OP asked me to benchmark the Rcpp loop against a base R for loop. Here is the code and results. About a 400-fold increase in speed on a vector of 100,000 product ids:

set.seed(1)
x <- sample(30,1e5,repl=T)
for.loop <- quote({
    tot <- 0 
    id <- 1
    out <- numeric(length(x))
    for( i in 1:length(x) ){
        tot <- tot + x[i]
        out[i] <- id
        if( tot >= 30 ){
            tot <- 0
            id <- id + 1
        }
    }
})

rcpp.loop <- quote( out <- grpid(x,30))

require( microbenchmark )
print( bm , unit = "relative" , digits = 2 , "median" )
Unit: relative
            expr min  lq median  uq max neval
 eval(rcpp.loop)   1   1      1   1   1    50
  eval(for.loop) 533 462    442 428 325    50

Problem

I have dataframe such as: ``` productid ordernum p1 10 p2 20 p3 30 p4 5 p5 20 p6 8 ``` I would like to add another column, which called groupid,it groups the products together in sequence and once the sum(ordernum) reach 30 , assign a new group id, e.g. the result should be ``` productid ordernum groupid p1 10 1 p2 20 1 p3 30 2 p4 5 3 p5 20 3 p6 8 3 ``` It is very easy to do by looping, by how can I achieve this without looping?

Original source