Group rows by attributes

Question

I have a data frame containing student latency data for different classes. Each row contains information about the last student and his class: the date and time of the class, class name, class size, number of minutes of delay, and gender of the student. To get the total percentage of late students for all classes, I need to count the number of rows (late students) and compare them with the total number of students who attended the class.

I can't just summarize class sizes for all rows; who will count students in this class several times, once for each late student in the class. Instead, I need to count each class size only once for each class meeting.

Example

Key: minutes were late, class name, students attending the floor, delayed sex, a few minutes late.

11/12/10 Stats 30 M 1
11/12/10 Stats 30 M 1
11/12/10 Stats 30 M 1
11/15/10 Stats 40 F 3
11/15/10 Stats 40 F 3
11/15/10 Stats 40 F 3
11/16/10 Radar 22 M 2
11/16/10 Radar 22 M 2
11/16/10 Radar 22 M 2
11/16/10 Radar 22 M 2
11/16/10 Radar 22 M 2

In this case, there are three different classes of meetings and 11 late students. How can I make sure that each class of a class matches only once?

Answer 1

If I understand what you want correctly, it is easier to do with the plyr package, and not with or because it understands what a multidimensional grouping is. For instance:

ddply(df, .(DATE,CLASS), transform, PERCENT_LATE=length(MINUTES.LATE)/CLASS.SIZE))

. ddply DATE CLASS. - , ( ). ( ). .

Answer 2

. "" :

>  sum_late <- tapply( tst$V5, paste(tst$V1, tst$V2, sep="_"), length)
>  csize <- tapply( tst$V3, paste(tst$V1, tst$V2, sep="_"), head,1)
> pct_late <- 100*sum_late/csize
> pct_late
11/12/10_Stats 11/15/10_Stats 11/16/10_Radar 
      10.00000        7.50000       22.72727 

:

>  dfcount <- aggregate( tst$V5, list(tst$V1, tst$V2), length)
> dfcount$pct <- 100*aggregate( tst$V5, list(tst$V1, tst$V2), length)$x/aggregate( tst$V3, list(tst$V1, tst$V2), head,1)$x
> dfcount
   Group.1 Group.2 x      pct
1 11/16/10   Radar 5 22.72727
2 11/12/10   Stats 3 10.00000
3 11/15/10   Stats 3  7.50000

Answer 3

: , % , aggregate(), :

> df2 <- within(df, pcLate <- 100 * (1 / Size)) 
> df2
         Date Class Size Sex MinsLate   pcLate
1  2010-11-12 Stats   30   M        1 3.333333
2  2010-11-12 Stats   30   M        1 3.333333
3  2010-11-12 Stats   30   M        1 3.333333
4  2010-11-15 Stats   40   F        3 2.500000
5  2010-11-15 Stats   40   F        3 2.500000
6  2010-11-15 Stats   40   F        3 2.500000
7  2010-11-16 Radar   22   M        2 4.545455
8  2010-11-16 Radar   22   M        2 4.545455
9  2010-11-16 Radar   22   M        2 4.545455
10 2010-11-16 Radar   22   M        2 4.545455
11 2010-11-16 Radar   22   M        2 4.545455
> with(df2, aggregate(pcLate, by = list(Date = Date, Class = Class), sum))
        Date Class        x
1 2010-11-16 Radar 22.72727
2 2010-11-12 Stats 10.00000
3 2010-11-15 Stats  7.50000

---

:

Assuming it dfcontains the sample data that you provide, we can do this in a couple of steps usingaggregate()

First, take the number of students with a delay in class:

summ <- with(df, aggregate(MinsLate, by = list(Date = Date, Class = Class),
                           FUN = length))
names(summ)[3] <- "nLate"

What gives us this starting point

> head(summ)
        Date Class nLate
1 2010-11-16 Radar     5
2 2010-11-12 Stats     3
3 2010-11-15 Stats     3

Then form the class dimensions:

summ$Size <- with(df, aggregate(Size, by = list(Date = Date, Class = Class),
                                FUN = unique)$x)

What awaits us:

> head(summ)
        Date Class nLate Size
1 2010-11-16 Radar     5   22
2 2010-11-12 Stats     3   30
3 2010-11-15 Stats     3   40

Then calculate the percentage later:

summ <- within(summ, pcLate <- 100 * (nLate / Size))

Result:

> head(summ)
        Date Class nLate Size   pcLate
1 2010-11-16 Radar     5   22 22.72727
2 2010-11-12 Stats     3   30 10.00000
3 2010-11-15 Stats     3   40  7.50000

If you need to do this a lot, wrap it in a function

tardiness <- function(df) {
    out <- with(df, aggregate(MinsLate, by = list(Date = Date, Class = Class),
                              FUN = length))
    names(out)[3] <- "nLate"
    out$Size <- with(df, aggregate(Size, by = list(Date = Date, Class = Class),
                                   FUN = unique)$x)
    out <- within(out, pcLate <- 100 * (nLate / Size))
    out
}

It takes all the steps for us:

> tardiness(df)
        Date Class nLate Size   pcLate
1 2010-11-16 Radar     5   22 22.72727
2 2010-11-12 Stats     3   30 10.00000
3 2010-11-15 Stats     3   40  7.50000

Answer 4

Follow @Gavin re comment: redundant output using generalization:

df.out <- ddply(x, .(DATE, CLASS), summarise    
    , NLATE = length(c(DATE, CLASS)) / 2
    , SIZE = unique(CLASS.SIZE)
    , PCLATE = 100 * (length(c(DATE, CLASS)) / 2 )/ unique(CLASS.SIZE)
    )
> df.out
      DATE CLASS NLATE SIZE PCLATE
1 11/12/10 Stats     3   30  10.00
2 11/15/10 Stats     3   40   7.50
3 11/16/10 Radar     5   22  22.73