Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Adding a counter column for a set of similar rows in R [duplicate]

Tags:

r

I have a data-frame in R with two columns. The first column contains the subjectID and the second column contains the trial ID that subject has done.

The a specific subjectID might have done the trial for more than 1 time. I want to add a column with a counter that starts counting for each subject-trial unique value and increment by 1 till it reaches the last row with that occurance.

More precisely, I have this table:

ID T
A  1
A  1
A  2
A  2
B  1
B  1
B  1
B  1

and I want the following output

ID  T  Index
A   1   1
A   1   2
A   2   1
A   2   2
B   1   1
B   1   2
B   1   3
B   1   4
like image 929
BICube Avatar asked Nov 07 '13 22:11

BICube


1 Answers

I really like the simple syntax of data.table for this (not to mention speed)...

#  Load package
require( data.table )
#  Turn data.frame into a data.table
dt <- data.table( df )

#  Get running count by ID and T
dt[ , Index := 1:.N , by = c("ID" , "T") ]
#   ID T Index
#1:  A 1     1
#2:  A 1     2
#3:  A 2     1
#4:  A 2     2
#5:  B 1     1
#6:  B 1     2
#7:  B 1     3
#8:  B 1     4

.N is an integer equal to the number of rows in each group. The groups are defined by the column names in the by argument, so 1:.N gives a vector as long as the group.

As data.table inherits from data.frame any function that takes a data.frame as input will also take a data.table as input and you can easily convert back if you wished ( df <- data.frame( dt ) )

like image 158
Simon O'Hanlon Avatar answered Oct 28 '22 04:10

Simon O'Hanlon