CODEWITHSUNDEEP
rsumaggregateunique
Ron
Ron

Reputation: 75

Counting the number of unique values by date in R

Please help me to count the number of unique IDs per Date. so, initially, there is this data frame of IDs and dates 

 ID         Date 
 1        2009/11/1
 1        2009/11/2
 1        2009/11/2
 2        2009/11/1
 2        2009/11/1
 2        2009/11/2 
 3        2009/11/1
 3        2009/11/3

It is possible to rearrange it by date. If we do so then we will see that on the 1st there are 3 unique IDs. On the 2ed 2 unique ID and on the 3rd there is one unique ID. So the final table should look like this: 

  Date      uniqueIDs
2009/11/1      3
2009/11/2      2
2009/11/3      1

I know that it is possible to aggregate with aggregate by using sum if the value is '1' or '0 'like that:

aggregate(DataFrame$RoomAv ~ DataFrame$Date, DataFrame, sum)

But how to count the unique number of IDs per day? The ID column is an integer column.

Thanks a lot!

Upvotes: 1

Views: 3857

Answers (2)

Len Greski
Len Greski

Reputation: 10865

Here's a solution with sqldf.

library(sqldf)

rawData <-"ID,Date 
 1,2009/11/1
 1,2009/11/2
 1,2009/11/2
 2,2009/11/1
 2,2009/11/1
 2,2009/11/2 
 3,2009/11/1
 3,2009/11/3 "

data <- read.csv(text = rawData,as.is=TRUE)

sqlStmt <- "select Date, count(distinct ID) from data group by Date"
sqldf(sqlStmt)

...and the output:

> sqldf(sqlStmt)
       Date count(distinct ID)
1 2009/11/1                  3
2 2009/11/2                  2
3 2009/11/3                  1
>

Upvotes: 2

YOLO
YOLO

Reputation: 21749

Here's a way you can do:

# using data.table

library(data.table)
setDT(df)[,uniqueN(ID),.(Date)]

        Date V1
1: 2009/11/1  3
2: 2009/11/2  2
3: 2009/11/3  1


# using dplyr

library(dplyr)
df %>% 
  group_by(Date) %>% 
  summarise(uniqueid = n_distinct(ID))

  Date      uniqueid
  <chr>        <int>
1 2009/11/1        3
2 2009/11/2        2
3 2009/11/3        1

Upvotes: 4

Related Questions