r - Adding the values of second column based on date and time of first column -

i have data frame 2 variables. first column "x" represents date , time format dd/mm/yyyy hh:mm, values in second column "y" electricity meter reading taken each after 5 minutes. want add values of each half hour. instance

x                y   13/12/2014 12:00 1    13/12/2014 12:05 2   13/12/2014 12:10 1   13/12/2014 12:15 2   13/12/2014 12:20 2   13/12/2014 12:25 1 

at end want present result as:

13/12/2014 12:00 9   13/12/2014 12:30 12   

and on...

here's alternative approach takes x in count (as per op comment).

first, make sure x of proper posixct format manipulate correctly (i'm using data.table package here convenience)

library(data.table) setdt(df)[, x := as.posixct(x, format = "%d/%m/%y %r")] 

then, aggregate per cumulative minutes instances of 00 or 30 within x while summing y , extracting first value of x per each group. i've made more complicated data set in order illustrate more complicated scenarios (see below)

df[order(x), .(x = x[1l], y = sum(y)), = cumsum(format(x, "%m") %in% c("00", "30"))] #    cumsum                   x y # 1:      0 2014-12-13 12:10:00 6 # 2:      1 2014-12-13 12:30:00 6 # 3:      2 2014-12-13 13:00:00 3 

---

data

df <- read.table(text = "x y   '13/12/2014 12:10' 1   '13/12/2014 12:15' 2   '13/12/2014 12:20' 2   '13/12/2014 12:25' 1 '13/12/2014 12:30' 1 '13/12/2014 12:35' 1 '13/12/2014 12:40' 1 '13/12/2014 12:45' 1 '13/12/2014 12:50' 1 '13/12/2014 12:55' 1 '13/12/2014 13:00' 1 '13/12/2014 13:05' 1 '13/12/2014 13:10' 1", header = true) 

---

some explanations

• the by expression:
  • format(x, "%m") gets minutes out of x (see ?strptime)
  • next step check if match 00 or 30 (using %in%)
  • cumsum separates these matched values separate groups aggregate by putting expression by statement (see ?data.table)
• the jth epression
  • (x = x[1l], y = sum(y)) getting first value of x per each group , sum of y per each group.
• the ith expression
  • i've added order(x) in order make sure data set ordered date (one of main reasons i've converted x proper posixct format)

for better understanding on how data.table works, see tutorials here