1

I have a data frame that looks like this:

      date         time              id            datetime    
1 2015-01-02 14:27:22.130 999000000007628 2015-01-02 14:27:22 
2 2015-01-02 14:41:27.720 989001002807730 2015-01-02 14:41:27 
3 2015-01-02 14:41:27.940 989001002807730 2015-01-02 14:41:27 
4 2015-01-02 14:41:28.140 989001002807730 2015-01-02 14:41:28 
5 2015-01-02 14:41:28.170 989001002807730 2015-01-02 14:41:28 
6 2015-01-02 14:41:28.350 989001002807730 2015-01-02 14:41:28

I need to find the number of unique "id"s for each "date" in that data frame.

I tried this: 

sums<-data.frame(date=unique(data$date), numIDs=0)

for(i in unique(data$date)){
  sums[sums$date==i,]$numIDs<-length(unique(data[data$date==i,]$id))
}

and I got the following error:

 Error in `$<-.data.frame`(`*tmp*`, "numIDs", value = 0L) : 
   replacement has 1 row, data has 0
 In addition: Warning message:
 In `==.default`(data$date, i) :
   longer object length is not a multiple of shorter object length

Any ideas?? Thank you!

Hopefully this helps!

data <- structure(list(date = structure(list(sec = c(0, 0, 0, 0, 0, 0, 
    0, 0, 0, 0), min = c(0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), 
    hour = c(0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), mday = c(2L, 
    2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L), mon = c(0L, 0L, 0L, 
    0L, 0L, 0L, 0L, 0L, 0L, 0L), year = c(115L, 115L, 115L, 115L, 
    115L, 115L, 115L, 115L, 115L, 115L), wday = c(5L, 5L, 5L, 
    5L, 5L, 5L, 5L, 5L, 5L, 5L), yday = c(1L, 1L, 1L, 1L, 1L, 
    1L, 1L, 1L, 1L, 1L), isdst = c(0L, 0L, 0L, 0L, 0L, 0L, 0L, 
    0L, 0L, 0L), zone = c("PST", "PST", "PST", "PST", "PST", 
    "PST", "PST", "PST", "PST", "PST"), gmtoff = c(NA_integer_, 
    NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, 
    NA_integer_, NA_integer_, NA_integer_, NA_integer_)), .Names = c("sec", 
"min", "hour", "mday", "mon", "year", "wday", "yday", "isdst", 
"zone", "gmtoff"), class = c("POSIXlt", "POSIXt")), time = c("14:27:22.130", 
"14:41:27.720", "14:41:27.940", "14:41:28.140", "14:41:28.170", 
"14:41:28.350", "14:41:28.390", "14:41:28.520", "14:41:28.630", 
"14:41:28.740"), id = c("999000000007628", "989001002807730", 
"989001002807730", "989001002807730", "989001002807730", "989001002807730", 
"989001002807730", "989001002807730", "989001002807730", "989001002807730"
), datetime = structure(list(sec = c(22.13, 27.72, 27.94, 28.14, 
28.17, 28.35, 28.39, 28.52, 28.63, 28.74), min = c(27L, 41L, 
41L, 41L, 41L, 41L, 41L, 41L, 41L, 41L), hour = c(14L, 14L, 14L, 
14L, 14L, 14L, 14L, 14L, 14L, 14L), mday = c(2L, 2L, 2L, 2L, 
2L, 2L, 2L, 2L, 2L, 2L), mon = c(0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L), year = c(115L, 115L, 115L, 115L, 115L, 115L, 115L, 
115L, 115L, 115L), wday = c(5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 
     5L), yday = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), isdst = c(0L, 
    0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), zone = c("PST", "PST", "PST", 
    "PST", "PST", "PST", "PST", "PST", "PST", "PST"), gmtoff =     c(NA_integer_, 
    NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, 
    NA_integer_, NA_integer_, NA_integer_, NA_integer_)), .Names = c("sec", 
    "min", "hour", "mday", "mon", "year", "wday", "yday", "isdst", 
    "zone", "gmtoff"), class = c("POSIXlt", "POSIXt")), site = c("Chivato", 
    "Chivato", "Chivato", "Chivato", "Chivato", "Chivato", "Chivato", 
    "Chivato", "Chivato", "Chivato")), .Names = c("date", "time", 
    "id", "datetime", "site"), row.names = c(NA, 10L), class = "data.frame")

3 Answers3

3

You can use the uniqueN function from data.table:

library(data.table)
setDT(df)[, uniqueN(id), by = date]

or (as per the comment of @Richard Scriven):

aggregate(id ~ date, df, function(x) length(unique(x)))
Jaap
  • 81,064
  • 34
  • 182
  • 193
  • I tried: setDT(data)[, uniqueN(id), by = date] and got the error: Error in byval[[jj]] : subscript out of bounds –  Oct 21 '15 at 05:05
  • @Alexis It should work (and works on the example data you provided). Could you include data & code in the question with which that error can be reproduced? – Jaap Oct 21 '15 at 05:08
  • here is my error: setDT(Chivato)[, uniqueN(id), by = date] Error in byval[[jj]] : subscript out of bounds –  Oct 21 '15 at 05:16
  • 1
    @Alexis Please post `dput(head(data))` **inside** your question. –  Oct 21 '15 at 05:17
  • 1
    @Alexis You just posted `head(data)` in comments. Please post `dput(head(data, n = 10))`. –  Oct 21 '15 at 05:24
  • @Pascal I posted it into the original question with n=2 because even at n=2 it was too long for a comment –  Oct 21 '15 at 05:29
  • @Alexis `n=2` is almost useless. Please uses at least `n=10`. And it is posted in the **question**, so it is fine. –  Oct 21 '15 at 05:30
  • 1
    @Alexis The formatting of the `date` column is causing the trouble. Could you try: `setDT(data)[, uniqueN(id), by = as.Date(date)]`? – Jaap Oct 21 '15 at 05:40
  • @Alexis I did nothing. For future question, please provide a `dput()` or `dput(head())`, to speed up the process. –  Oct 21 '15 at 05:50
  • @Pascal I will. Thank you! Good to know! –  Oct 21 '15 at 05:55
2

Or we could use n_distinct from library(dplyr)

library(dplyr) 
df %>%
   group_by(date) %>%
   summarise(id=n_distinct(id))
akrun
  • 874,273
  • 37
  • 540
  • 662
  • @Pascal If the OP's 'data' is `POSIXlt` class, which the `dplyr` wont' allow, it is better to convert it outside the `%>%` to 'Date' class, i.e. `data$date <- as.Date(date)` and it should work. – akrun Oct 21 '15 at 06:02
  • Good to know. Thank you. –  Oct 21 '15 at 06:03
0

This answer is in response to this post: group by and then count unique observations which was marked as duplicate as I was writing this draft. This is not in response to the question for the duplicate basis here: How to find number of unique ids corresponding to each date in a data drame which asks about finding unique ID's. I'm not sure the second post actually answers the OP's question which is,

"I want to create a table with the number of unique id for each combination of group1 and group2."

The keyword here is 'combination'. The interpretation is each id has a particular value for group1 and a particular value for group2 so that the set of data of interest is the particular set of values c(id, group1, group2).

Here is the data.frame the OP provided:

df1 <- data.frame(id=sample(letters, 10000, replace = T),
group1=sample(1:2, 10000, replace = T),
group2=sample(100:101, 10000, replace = T))

Using data.table inspired by this post -- https://stackoverflow.com/a/13017723/5220858:

>library(data.table)
>DT <- data.table(df1)
>DT[, .N, by = .(group1, group2)]

   group1 group2    N
1:      1    100 2493
2:      1    101 2455
3:      2    100 2559
4:      2    101 2493

N is the count for the id that has a particular group1 value and a particular group2 value. Expanding to include the id also returns a table of 104 unique id, group1, group2 combinations.

>DT[, .N, by = .(id, group1, group2)]

     id group1 group2   N
  1:  t      1    100 107
  2:  g      1    101  85
  3:  l      1    101  98
  4:  a      1    100  83
  5:  j      1    101  98
 ---                     
100:  p      1    101  96
101:  r      2    101  91
102:  y      1    101 104
103:  g      1    100  83
104:  r      2    100  77
Community
  • 1
  • 1
James Theobald
  • 121
  • 11