R Help: Count Unique Values by Group

Question

Here is a sample dataset to illustrate my problem:

example=data.frame(Group1=c(1,1,1,2,2,10,15,23),
               Group2=c(100,100,150,200,234,456,465,710),
               UniqueID=c('ABC67DF','ADC45BN','ADC45BN','ADC44BB','BBG40ML','CXD99QA','BBG40ML','VDF72PX'))

This is what the dataset looks like:

Group1  Group2  UniqueID
1       100     ABC67DF 
1       100     ADC45BN
1       150     ADC45BN
2       200     ADC44BB
2       234     BBG40ML
10      456     CXD99QA
15      465     BBG40ML
23      710     VDF72PX

I want to count the number of occurrences of each UniqueID and have a dataset that looks like this:

Group1  Group2  UniqueID  Count
1       100     ABC67DF   1
1       100     ADC45BN   1
1       150     ADC45BN   2
2       200     ADC44BB   1 
2       234     BBG40ML   1
10      456     CXD99QA   1
15      465     BBG40ML   2
23      710     VDF72PX   1

I have tried the following code:

library(plryr)
Count=count(data$UniqueID)

But this just squishes my dataset down to show only unique UniqueIDs. Can anyone help me acquire the desired dataset?

Do you mean `dplyr` or `plyr`? I am not familiar with `plryr`. — r2evans, Apr 01 '19 at 20:29
Try `data.table::rowid(example$UniqueID)`. This doesn't count however, but gives desired output. — markus, Apr 01 '19 at 20:31

Jilber Urbina · Accepted Answer · 2019-04-01T20:43:05.023

An R base solution

example$ones <- 1   # create a vector of 1's
example <- transform(example, Count = ave(ones, UniqueID, FUN=cumsum)) # get counts
example$ones <- NULL # delete vector of 1's previously created
example  # check results
  Group1 Group2 UniqueID Count
1      1    100  ABC67DF     1
2      1    100  ADC45BN     1
3      1    150  ADC45BN     2
4      2    200  ADC44BB     1
5      2    234  BBG40ML     1
6     10    456  CXD99QA     1
7     15    465  BBG40ML     2
8     23    710  VDF72PX     1

R Help: Count Unique Values by Group

1 Answers1