This question already has an answer here:
-
Create counter with multiple variables [duplicate]
6 answers
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
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.
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
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)]
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
