Numeric sequence with condition

Question

I have a big data.frame that I want to generate a new column (called Seq) to, which has a sequential values that restarts every time there is a change in a different column. Here is an example of the data.frame (with omitted columns) and the new column called Seq. As you can see there is a sequentiel count, but everytime there is a new IDPath, the sequentiel count restarts. The sequentiel length can have different lengths, some are 1 long, while others are 300.

IDPath    LogTime               Seq
AADS      19-06-2015 01:57      1
AADS      19-06-2015 01:55      2
AADS      19-06-2015 01:54      3
AADS      19-06-2015 01:53      4
DHSD      19-06-2015 12:57      1
DHSD      19-06-2015 10:58      2
DHSD      19-06-2015 09:08      3
DHSD      19-06-2015 08:41      4

These are all great answers! Thanks for the help! All of them solves my problem, thanks! — KhalidN, Oct 06 '15 at 09:25
Cool. You should pick one and accept it as the answer (prbly Japp's or CathG's if you are using data.table). — hrbrmstr, Oct 06 '15 at 09:29
Or you guys need to start closing such dupes in the first place. — David Arenburg, Oct 06 '15 at 09:34

Cath · Answer 1 · 2015-10-06T09:35:14.843

6

Using data.table package, here is a way to obtain what you want:

require(data.table)
setDT(dt)[, Seq:=1:.N, by=IDPath]
# or, as mentioned by @DavidArenburg
setDT(dt)[, Seq:=seq_len(.N), by=IDPath]

dt
#   IDPath          LogTime Seq
#1:   AADS 19-06-2015 01:57   1
#2:   AADS 19-06-2015 01:55   2
#3:   AADS 19-06-2015 01:54   3
#4:   AADS 19-06-2015 01:53   4
#5:   DHSD 19-06-2015 12:57   1
#6:   DHSD 19-06-2015 10:58   2
#7:   DHSD 19-06-2015 09:08   3
#8:   DHSD 19-06-2015 08:41   4

edited Oct 06 '15 at 09:35

answered Oct 06 '15 at 09:06

Cath

23,906
5
52
86

2

`seq_len(.N)` will be probably a bit faster than `:` – David Arenburg Oct 06 '15 at 09:33

hrbrmstr · Accepted Answer · 2015-10-06T09:49:29.280

Obligatory Hadleyverse answer (base R answer also included after Hadleyvese answer):

library(dplyr)

dat <- read.table(text="IDPath    LogTime 
AADS      '19-06-2015 01:57'      
AADS      '19-06-2015 01:55'    
AADS      '19-06-2015 01:54'      
AADS      '19-06-2015 01:53'      
DHSD      '19-06-2015 12:57'      
DHSD      '19-06-2015 10:58'      
DHSD      '19-06-2015 09:08'      
DHSD      '19-06-2015 08:41'      ", header=TRUE, stringsAsFactors=FALSE, quote="'")

mutate(group_by(dat, IDPath), Seq=1:n())

OR (via David Arenburg)

mutate(group_by(dat, IDPath), Seq=row_number())

Or if you're into piping:

dat %>%
  group_by(IDPath) %>%
  mutate(Seq=1:n())

OR (via David Arenburg)

dat %>%
  group_by(IDPath) %>%
  mutate(Seq=row_number())

Obligatory base R answer:

unsplit(lapply(split(dat, dat$IDPath), transform, Seq=1:length(IDPath)), dat$IDPath)

OR more idiomatically (via David again)

with(dat, ave(IDPath, IDPath, FUN = seq_along))

If it really is a HUGE data frame then you may want to start with tbl_dt(dat) for the dplyr solutions, but CathG's or Jaap's versions will be faster if you're already using data.table.

I think the idimatic base would be something like `with(dat, ave(IDPath, IDPath, FUN = seq_along))` — David Arenburg, Oct 06 '15 at 09:38
updated! (and you are totally right abt the closing thing…argh!) — hrbrmstr, Oct 06 '15 at 09:39

Jaap · Answer 3 · 2018-10-11T08:01:49.560

You can also use the rleid function from the data.table package which is specifically designed for generating a run-length type id column in grouping operations:

library(data.table)
setDT(df)[, Seq := rleid(LogTime), by=IDPath]

which gives:

> df
   IDPath          LogTime Seq
1:   AADS 19-06-2015:01:57   1
2:   AADS 19-06-2015:01:55   2
3:   AADS 19-06-2015:01:54   3
4:   AADS 19-06-2015:01:53   4
5:   DHSD 19-06-2015:12:57   1
6:   DHSD 19-06-2015:10:58   2
7:   DHSD 19-06-2015:09:08   3
8:   DHSD 19-06-2015:08:41   4

Another option would be to use the rowid function:

setDT(df)[, Seq := rowid(IDPath)]

score 1 · Answer 4 · answered Oct 06 '15 at 09:18

This might be a bit lengthy approach but it's simple,

alphabets <- c("a", "a", "b", "c", "c")
df <- data.frame(alphabets)
a <- table(df$alphabets)
k <- 1


for (i in 1:length(a))
{
 l <- 1
 for(j in 1:a[i])
{
   df$seq[k] <- l
   k <- k+ 1
   l <- l+ 1
}
}

df
#  alphabets seq
#1         a   1
#2         a   2
#3         b   1
#4         c   1
#5         c   2

Numeric sequence with condition

4 Answers4

Linked