CODEWITHSUNDEEP
rdata.table
jdepypere
jdepypere

Reputation: 3553

R get value at minimum and maximum time of day

I have some data that I need to analyse easily. I want to create a graph of the average usage per day of a week. The data is in a data.table with the following structure:

               time       value
2014-10-22 23:59:54   7433033.0
2014-10-23 00:00:12   7433034.0
2014-10-23 00:00:31   7433035.0
2014-10-23 00:00:49   7433036.0
...
2014-10-23 23:59:21   7443032.0
2014-10-23 23:59:40   7443033.0
2014-10-23 23:59:59   7443034.0
2014-10-24 00:00:19   7443035.0

Since the value is cumulative, I would need the maximum value of a day, minus the minimum value of that day, and then average all the values with the same days.

I already know how to get the day of the week (using as.POSIXlt and $wday). So how can I get the daily difference? Once I have the data in a structure like:

dayOfWeek  value
        0     10
        1     20
        2     50

I should be able to find the mean myself using some functions.

Here is a sample:

library(data.table)
data <- fread("http://pastebin.com/raw.php?i=GXGiCAiu", header=T)
#get the difference per day

#create average per day of week

Upvotes: 2

Views: 3076

Answers (3)

cburd
cburd

Reputation: 46

Came across this looking for something else. I think you were looking for the difference and mean per Monday, Tuesday, etc. Sticking with data.table allows a quick all in one call to get the mean per day of week and the difference per day of the week. This gives an output of 7 rows and three columns.

library(data.table)
data <- fread("http://pastebin.com/raw.php?i=GXGiCAiu", header=T)

data_summary <- data[,list(mean = mean(value),
                           diff = max(value)-min(value)),
                     by = list(date = format(as.POSIXct(time), format = "%A"))]

This gives an output of 7 rows and three columns.

        date    mean   diff
1:  Thursday 7470107 166966
2:    Friday 7445945   6119
3:  Saturday 7550000 100000
4:    Sunday 7550000 100000
5:    Monday 7550000 100000
6:   Tuesday 7550000 100000
7: Wednesday 7550000 100000

Upvotes: 1

bgoldst
bgoldst

Reputation: 35324

This is actually a trickier problem than it seemed at first glance. I think you need two separate aggregations, one to aggregate the cumulative usage values within each calendar day by taking the difference of the range, and then a second to aggregate the per-calendar-day usage values by weekday. You can extract the weekday with weekdays(), calculate the daily difference with diff() on the range(), calculate the mean with mean(), and aggregate with aggregate():

set.seed(1);
N <- as.integer(60*60*24/19*14);
df <- data.frame(time=seq(as.POSIXct('2014-10-23 00:00:12',tz='UTC'),by=19,length.out=N)+rnorm(N,0,0.5), value=seq(7433034,by=1,length.out=N)+rnorm(N,0,0.5) );
head(df);
##                  time   value
## 1 2014-10-23 00:00:11 7433034
## 2 2014-10-23 00:00:31 7433035
## 3 2014-10-23 00:00:49 7433036
## 4 2014-10-23 00:01:09 7433037
## 5 2014-10-23 00:01:28 7433039
## 6 2014-10-23 00:01:46 7433039
tail(df);
##                      time   value
## 63658 2014-11-05 23:58:14 7496691
## 63659 2014-11-05 23:58:33 7496692
## 63660 2014-11-05 23:58:51 7496693
## 63661 2014-11-05 23:59:11 7496694
## 63662 2014-11-05 23:59:31 7496695
## 63663 2014-11-05 23:59:49 7496697
df2 <- aggregate(value~date,cbind(df,date=as.Date(df$time)),function(x) diff(range(x)));
df2;
##          date    value
## 1  2014-10-23 4547.581
## 2  2014-10-24 4546.679
## 3  2014-10-25 4546.410
## 4  2014-10-26 4545.726
## 5  2014-10-27 4546.602
## 6  2014-10-28 4545.194
## 7  2014-10-29 4546.136
## 8  2014-10-30 4546.454
## 9  2014-10-31 4545.712
## 10 2014-11-01 4546.901
## 11 2014-11-02 4544.684
## 12 2014-11-03 4546.378
## 13 2014-11-04 4547.061
## 14 2014-11-05 4547.082
df3 <- aggregate(value~dayOfWeek,cbind(df2,dayOfWeek=weekdays(df2$date)),mean);
df3;
##   dayOfWeek    value
## 1    Friday 4546.196
## 2    Monday 4546.490
## 3  Saturday 4546.656
## 4    Sunday 4545.205
## 5  Thursday 4547.018
## 6   Tuesday 4546.128
## 7 Wednesday 4546.609

Upvotes: 1

shadow
shadow

Reputation: 22353

There are many ways to do this with R. You can use ave from base R or data.table or dplyr packages. These solutions all add the summaries as columns of your data.

data

df <- data.frame(dayOfWeek = c(0L, 0L, 1L, 1L, 2L), 
                 value = c(10L, 5L, 20L, 60L, 50L))

base r

df$min <- ave(df$value, df$dayOfWeek, FUN = min)
df$max <- ave(df$value, df$dayOfWeek, FUN = max)

data.table

require(data.table)
setDT(df)[, ":="(min = min(value), max = max(value)), by = dayOfWeek][]

dplyr

require(dplyr)
df %>% group_by(dayOfWeek) %>% mutate(min = min(value), max = max(value))

If you just want the summaries, you can also use the following: 

# base
aggregate(value~dayOfWeek, df, FUN = min)
aggregate(value~dayOfWeek, df, FUN = max)
# data.table
setDT(df)[, list(min = min(value), max = max(value)), by = dayOfWeek]
# dplyr
df %>% group_by(dayOfWeek) %>% summarise(min(value), max(value))

Upvotes: 4

Related Questions