CODEWITHSUNDEEP
rgroup-bydplyrplyrreshape2
Heather Keturah
Heather Keturah

Reputation: 139

r Group by and count

I am dealing with a dataset which is as follows

      Id     Date           Color
      10     2008-11-17     Red
      10     2008-11-17     Red
      10     2008-11-17     Blue
      10     2010-01-26     Red
      10     2010-01-26     Green
      10     2010-01-26     Green
      10     2010-01-26     Red
      29     2007-07-31     Red
      29     2007-07-31     Red
      29     2007-07-31     Blue
      29     2007-07-31     Green
      29     2007-07-31     Red

My goal is to create a dataset like this

     Color      Representation      Count            Min   Max
     Red        1 + 1 + 1  = 3      2 + 2 + 3 = 7    2     3
     Blue       1 + 1      = 2      1 + 1            1     1
     Green      1 +  1     = 2      2 + 1            1     2

Representation

The value in 1st Row , 2nd column (Representation), is 3 because Red is represented three times based on the unique combination of ID and Date. For example, 1st and 2nd rows are the same, Id(10) and Date(2008-11-17) so this combination is represented once (1(10, 2008-11-17)). The 4th and 7th rows are the same Id(10) and Date(2010-01-26)combination, so this unique combination, is represented once (1(10, 2010-01-26)) . The 8th, 9th, 12th are the same combinations of Id(29) and Date(2007-07-31) and similarly this is represented once (1(29, 2007-07-31)). Thus the value is 3 in row 1, column 2.

1(10, 2008-11-17) + 1(10, 2010-10-26) + 1(29, 2007-07-31) =3

Count

The value in 1st Row , 3rd column (Count), is 7 because Red is mentioned twice by ID 10 on 2008-11-17 (2 10, 2008-11-17), again Red is mentioned twice by ID 10 on 2010-01-26 (2 10, 2010-01-26) and three times by ID 29 on 2007-07-31 2 29,2007-07-31

2(10, 2008-11-17) + 2(10, 2010-10-26) + 3(29, 2007-07-31)

Any help on accomplishing this unique frequency/count table is much appreciated.

Dataset

Id   = c(10,10,10,10,10,10,10,29,29,29,29,29) 
Date = c("2008-11-17", "2008-11-17", "2008-11-17","2010-01-26","2010-01-26","2010-01-26","2010-01-26",
         "2007-07-31","2007-07-31","2007-07-31","2007-07-31","2007-07-31") 
Color = c("Red", "Red", "Blue", "Red", "Green", "Green", "Red", "Red", "Red", "Blue", "Green", "Red") 
df = data.frame(Id, Date, Color)

Upvotes: 3

Views: 10072

Answers (3)

akrun
akrun

Reputation: 887173

Another option is data.table

library(data.table)
setDT(df)[, .(Representation = uniqueN(paste(Id, Date)), Count = .N) , by = Color]
#     Color Representation Count
#1:   Red              3     7
#2:  Blue              2     2
#3: Green              2     3

Update

For the second question, we can try

library(matrixStats)
m1 <- sapply(split(df[["Color"]], list(df$Id, df$Date), drop = TRUE),  function(x) table(x))
v1 <- (NA^!m1) * m1
df1 <- data.frame(Color = row.names(m1), Representation = rowSums(m1!=0), 
   Count = rowSums(m1), Min = rowMins(v1, na.rm=TRUE),
    Max = rowMaxs(v1, na.rm=TRUE))
row.names(df1) <- NULL
df1
#   Color Representation Count Min Max
#1  Blue              2     2   1   1
#2 Green              2     3   1   2
#3   Red              3     7   2   3

Upvotes: 3

ThatGuy
ThatGuy

Reputation: 1233

You can use the aggregate() function:

# Make a new column for the Date-Id joined (what you want to base the counts on
df$DateId <- paste(df$Date, df$Id)

# Get the representation values
Representation <- aggregate(DateId ~ Color, data=df,FUN=function(x){length(unique(x))})
Representation
#>   Color DateId
#> 1  Blue      2
#> 2 Green      2
#> 3   Red      3

# Get the Count values
Count <- aggregate(DateId ~ Color, data=df,FUN=length)
Count
#>   Color DateId
#> 1  Blue      2
#> 2 Green      3
#> 3   Red      7

Upvotes: 2

Gregor Thomas
Gregor Thomas

Reputation: 145785

With dplyr:

library(dplyr)
dat %>% group_by(Color) %>%
    summarize(Representation = n_distinct(Id, Date), Count = n())
# # A tibble: 3 × 3
#    Color Representation Count
#   <fctr>          <int> <int>
# 1   Blue              2     2
# 2  Green              2     3
# 3    Red              3     7

Upvotes: 4

Related Questions