Subset of data by day according to most non-null data

Okay, the good news is I have an answer for you, and the bad news is that you have more questions to ask yourself. The bad news first: you need to consider how you want to handle multiple days that have the same number of non-zero values ​​for "c". I'm not going to address this in this answer.

Now the good news: it's really simple.

Step 1 . First, format your data frame. Since we are changing data types for a couple of variables (from b to datetime and c to numeric), we need to create a new data frame or recalibrate the old one. I prefer to keep the original and create a new one, for example:

a <- df1$a
b <- strptime(df1$b, "%d/%m/%Y %H:%M")
c <- as.numeric(df1$c)
hour <- as.numeric(format(b, "%H"))
date <- format(b, "%x")

df2 <- data.frame(a, b, c, hour, date)

#   a                   b c hour      date
# 1 1 2012-12-05 05:00:00 0    5 12/5/2012
# 2 2 2012-12-05 06:00:00 0    6 12/5/2012
# 3 3 2012-12-06 05:00:00 0    5 12/6/2012
# 4 4 2012-12-06 06:00:00 1    6 12/6/2012
# 5 5 2012-12-07 09:00:00 1    9 12/7/2012
# 6 6 2012-12-07 07:00:00 1    7 12/7/2012

      

        
        
        
      

    

Note that I've also added the "hour" and "date" variables. This makes it easy to sort our data by these fields for our later aggregation function.

Step 2 . Now calculate how many non-zero values ​​there are for each day between the hours from 06:00 to 08:00. Since we are using the "hour" values, this means the values ​​"6" and "7" (represents 06:00 - 07:59).

library(plyr)
df2 <- ddply(df2[df2$hour %in% 6:7,], .(date), mutate, non_zero=sum(c))

#   a                   b c hour      date non_zero
# 1 2 2012-12-05 06:00:00 0    6 12/5/2012        0
# 2 4 2012-12-06 06:00:00 1    6 12/6/2012        1
# 3 6 2012-12-07 07:00:00 1    7 12/7/2012        1

      

        
        
        
      

    

The "plyr" package is great for this kind of thing. The 'ddply' package specifically uses dataframes for both input and output data (hence "dd"), and the "mutate" function allows us to preserve all data when additional columns are added. In this case, we want to get the amount "c" for each day in .(date)

. The substitution of our data for the clock is accounted for in the data argument df2[df2$hour %in% 6:7,]

, which says to show us the rows where the hour value is in the set {6,7}.

Step 3 . The last step is just a subset of the data for the maximum number of non-zero values. We can remove the extra columns we used and revert to our original three.

subset_df <- df2[df2$non_zero==max(df2$non_zero),1:3]

#   a                   b c
# 2 4 2012-12-06 06:00:00 1
# 3 6 2012-12-07 07:00:00 1

      

        
        
        
      

    

Good luck!

Update . In the OP's request, I am writing a new function "ddply" which will also contain time columns to plot.

df2 <- ddply(df2[df2$hour %in% 6:7,], .(date), mutate, non_zero=sum(c), plot_time=as.numeric(format(b, "%H")) + as.numeric(format(b, "%M")) / 60)
subset_df <- df2[df2$non_zero==max(df2$non_zero),c("a","b","c","plot_time")]

      

        
        
        
      

    

We need to roll the time down one continuous variable, so I chose the clock. Leaving any data in temporary format will take us longer to fiddle with, and using a string format (like "hh: mm") will limit the types of functions you can use on it. Continuous numbers are the most flexible, so we get the number of hours as.numeric(format(b, "%H"))

and add it to the number of minutes divided by 60 as.numeric(format(b, "%M")) / 60

to convert minutes to hours. Also, since we are dealing with a large number of columns, I have included a final subset operator to name the columns I want rather than refer to numbers. Once I come across columns that are not in continuous order, I find the use of names easier to debug.