3

I am trying to aggregate my time series data, and I want to get the average value of the aggregated value without including NAs or 0s.

Here is what my data looks like.

                     V1      423 470  473 626
 1: 2018-01-01 00:00:00  0.00000   0  0.0   0
 2: 2018-01-01 00:01:00  8.00000   0 95.0   0
 3: 2018-01-01 00:02:00  0.00000   0  0.0   0
 4: 2018-01-01 00:03:00 31.00000   0 24.5   0
 5: 2018-01-01 00:04:00 37.00000  28 33.0  31

I am trying to aggregate in 5 minute interval, and my expected output is

                   V1      423 470  473 626
  2018-01-01 00:05:00 34.00000  28 50.8  31
                         ~
: 2018-01-01 00:10:00        A   B    C   D

How do I aggregate them in 5 minute interval while getting the average value excluding 0 s or NA s ?

EDIT

structure(list(V1 = c("2018-01-01 00:00:00", "2018-01-01 00:01:00", 
"2018-01-01 00:02:00", "2018-01-01 00:03:00", "2018-01-01 00:04:00", 
"2018-01-01 00:05:00", "2018-01-01 00:06:00", "2018-01-01 00:07:00", 
"2018-01-01 00:08:00", "2018-01-01 00:09:00", "2018-01-01 00:10:00", 
"2018-01-01 00:11:00", "2018-01-01 00:12:00", "2018-01-01 00:13:00", 
"2018-01-01 00:14:00", "2018-01-01 00:15:00", "2018-01-01 00:16:00", 
"2018-01-01 00:17:00", "2018-01-01 00:18:00", "2018-01-01 00:19:00"
), `423` = c(0, 8, 0, 31, 37, 31, 26.1111111111111, 39.375, 35.5, 
19.3, 21.5454545454545, 41.2, 31, 27.375, 31, 24.3076923076923, 
26.1666666666667, 24, 26.8, 30.8181818181818), `470` = c(0, 0, 
0, 0, 28, 0, 0, 0, 27, 21, 0, 21.5, 0, 0, 0, 0, 10, 46, 19.5, 
0), `473` = c(0, 95, 0, 24.5, 33, 55, 50, 0, 47, 45, 0, 0, 35.4, 
0, 0, 23, 32.5, 0, 0, 55), `626` = c(0, 0, 0, 0, 31, 26, 0, 0, 
0, 16, 0, 0, 0, 0, 75, 0, 0, 48, 0, 0)), row.names = c(NA, -20L
), .internal.selfref = <pointer: 0x0000029131ff1ef0>, class = c("data.table", 
"data.frame"))
Yun Hyunsoo
  • 71
  • 1
  • 8
  • Can you post sample data? Please edit **the question** with the output of `dput(df)`. Or, if it is too big with the output of `dput(head(df, 20))`. – Rui Barradas Oct 11 '20 at 17:02

2 Answers2

1

Does this work:

> df %>% mutate(ID = rep(letters[1:ceiling(nrow(df)/5)], each = 5)) %>% 
+               group_by(as.numeric(as.factor(ID))) %>% 
+                       select(-c(v1,ID)) %>% summarise(across(`423`:`625`,~ mean(.x[which(.x>0)]))) %>% 
+                                 select(-1) %>% mutate(v1 =  seq.POSIXt(ymd_hms('2018-01-01 00:05:00'), by = '5 mins', length.out = n()))
`summarise()` ungrouping output (override with `.groups` argument)
# A tibble: 2 x 5
  `423` `470` `473` `625` v1                 
  <dbl> <dbl> <dbl> <dbl> <dttm>             
1  25.3    28  50.8    31 2018-01-01 00:05:00
2  20.7    26  49.7    32 2018-01-01 00:10:00
>

Data used:

> df
# A tibble: 10 x 5
   v1                  `423` `470` `473` `625`
   <dttm>              <dbl> <dbl> <dbl> <dbl>
 1 2018-01-01 00:00:00     0     0   0       0
 2 2018-01-01 00:01:00     8     0  95       0
 3 2018-01-01 00:02:00     0     0   0       0
 4 2018-01-01 00:03:00    31     0  24.5     0
 5 2018-01-01 00:04:00    37    28  33      31
 6 2018-01-01 00:05:00     0     0   0       0
 7 2018-01-01 00:06:00     8     0  95       0
 8 2018-01-01 00:07:00     0     0   0       0
 9 2018-01-01 00:08:00    30     0  20       0
10 2018-01-01 00:09:00    24    26  34      32
>

Running on new Data:

> dput(BB)
structure(list(V1 = structure(c(1514764800, 1514764860, 1514764920, 
1514764980, 1514765040, 1514765100, 1514765160, 1514765220, 1514765280, 
1514765340, 1514764800, 1514764860, 1514764920, 1514764980, 1514765040, 
1514765100, 1514765160, 1514765220, 1514765280, 1514765340), class = c("POSIXct", 
"POSIXt"), tzone = "UTC"), `423` = c(0, 8, 0, 31, 37, 31, 26.1111111111111, 
39.375, 35.5, 19.3, 21.5454545454545, 41.2, 31, 27.375, 31, 24.3076923076923, 
26.1666666666667, 24, 26.8, 30.8181818181818), `470` = c(0, 0, 
0, 0, 28, 0, 0, 0, 27, 21, 0, 21.5, 0, 0, 0, 0, 10, 46, 19.5, 
0), `473` = c(0, 95, 0, 24.5, 33, 55, 50, 0, 47, 45, 0, 0, 35.4, 
0, 0, 23, 32.5, 0, 0, 55), `626` = c(0, 0, 0, 0, 31, 26, 0, 0, 
0, 16, 0, 0, 0, 0, 75, 0, 0, 48, 0, 0)), row.names = c(NA, -20L
), class = c("data.table", "data.frame"))
> BB$V1 <- ymd_hms(BB$V1)
> BB %>% mutate(ID = rep(letters[1:ceiling(nrow(BB)/5)], each = 5)) %>% 
+   group_by(as.numeric(as.factor(ID))) %>% 
+   select(-c(V1,ID)) %>% summarise(across(`423`:`626`,~ mean(.x[which(.x>0)]))) %>% 
+   select(-1) %>% mutate(V1 =  seq.POSIXt(ymd_hms('2018-01-01 00:05:00'), by = '5 mins', length.out = n()))
`summarise()` ungrouping output (override with `.groups` argument)
# A tibble: 4 x 5
  `423` `470` `473` `626` V1                 
  <dbl> <dbl> <dbl> <dbl> <dttm>             
1  25.3  28    50.8    31 2018-01-01 00:05:00
2  30.3  24    49.2    21 2018-01-01 00:10:00
3  30.4  21.5  35.4    75 2018-01-01 00:15:00
4  26.4  25.2  36.8    48 2018-01-01 00:20:00
>
Karthik S
  • 11,348
  • 2
  • 11
  • 25
  • Sorry Karthik, the error states that "ID" is not identified. could you explain what ID indicates? Thank you for the effort – Yun Hyunsoo Oct 12 '20 at 04:34
  • So I am adding an ID column, one ID value for 5 rows i.e. for every 5 minute interval, so that I can group the values based on ID and perform aggregation. BTW, It ran for me and produced the output as shown above. On which step are you getting that error? – Karthik S Oct 12 '20 at 04:37
  • I am not sure.. I used BB instead of df which is the name of my data frame, and used this code BB %>% mutate(ID = rep(letters[1:ceiling(nrow(BB)/5)], each = 5)) %>% + group_by(as.numeric(as.factor(ID))) %>% + select(-c(v1,ID)) %>% summarise(across(`423`:`625`,~ mean(.x[which(.x>0)]))) %>% + select(-1) %>% mutate(v1 = seq.POSIXt(ymd_hms('2018-01-01 00:05:00'), by = '5 mins', length.out = n())) – Yun Hyunsoo Oct 12 '20 at 04:54
  • Could you test it on the edited question. I used dput to add my data. maybe the column V1 should be numeric? – Yun Hyunsoo Oct 12 '20 at 05:01
  • Can you please check now, I think I missed to mention BB$V1 <- ymd_hms(df$v1) step in my first code. – Karthik S Oct 12 '20 at 05:29
  • I getting an error running the code BB$V1 <- ymd_hms(df$v1) Error in set(x, j = name, value = value) : RHS of assignment to existing column 'V1' is zero length but not NULL. If you intend to delete the column use NULL. Otherwise, the RHS must have length > 0; e.g., NA_integer_. If you are trying to change the column type to be an empty list column then, as with all column type changes, provide a full length RHS vector such as vector('list',nrow(DT)); i.e., 'plonk' in the new column. i apologize for my poor explanation. Sorry Karthik – Yun Hyunsoo Oct 12 '20 at 06:11
  • Can you please check now, edited my code, It is ymd_hms(BB$V1), works fine for me now. – Karthik S Oct 12 '20 at 06:19
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/222888/discussion-between-yun-hyunsoo-and-karthik-s). – Yun Hyunsoo Oct 12 '20 at 06:46
1

The following uses cut to make of column V1 a grouping variable by 5 minutes intervals and then summarizes using a custom function to compute means without NA's or zero values. I have left this function in two code lines to make it more readable but it could simply be

f <- function(x) mean(x[x != 0], na.rm = TRUE)

The date time column V1 is first coerced to class "POSIXct".

library(data.table)

f <- function(x){
  y <- x[x != 0]
  mean(y, na.rm = TRUE)
}

df[, V1 := as.POSIXct(V1)]
df[, V1 := cut(V1, "5 mins")]
df[, lapply(.SD, f), by = V1]
#                    V1      423      470      473 626
#1: 2018-01-01 00:00:00 25.33333 28.00000 50.83333  31
#2: 2018-01-01 00:05:00 30.25722 24.00000 49.25000  21
#3: 2018-01-01 00:10:00 30.42409 21.50000 35.40000  75
#4: 2018-01-01 00:15:00 26.41851 25.16667 36.83333  48

A one-liner could be

df[, lapply(.SD, f), by = cut(as.POSIXct(V1), "5 mins")]
Rui Barradas
  • 70,273
  • 8
  • 34
  • 66
  • I keep getting the following error: : character string is not in a standard unambiguous format error in selecting method in function 'cut'? what format should V1 be? – Yun Hyunsoo Oct 12 '20 at 06:05
  • Classes ‘data.table’ and 'data.frame': 44040 obs. of 5 variables: $ V1 : chr "2018-01-01 00:00:00" "2018-01-01 00:01:00" "2018-01-01 00:02:00" "2018-01-01 00:03:00" ... $ 423: num 0 8 0 31 37 ... $ 470: num 0 0 0 0 28 0 0 0 27 21 ... $ 473: num 0 95 0 24.5 33 55 50 0 47 45 ... $ 626: num 0 0 0 0 31 26 0 0 0 16 ... - attr(*, ".internal.selfref")= – Yun Hyunsoo Oct 12 '20 at 06:06
  • @YunHyunsoo `V1` sould be of class `"POSIXct"`. In the comment where you post the output of `str` it's a `chr`. That's why I first coerce to datetime with `as.POSIXct`. – Rui Barradas Oct 12 '20 at 06:14
  • @YunHyunsoo Try running `df[, V1 := as.POSIXct(V1)]` or `df$V1 <- as.POSIXct(df$V1)` and see what `str(df)` gives. If it's now a POSXct column, the rest should work as expected. – Rui Barradas Oct 12 '20 at 06:16
  • I have successfully changed the V1 class to "POSIXct" format. but I keep getting the following error Error in `[.data.frame`(BB, , lapply(.SD, f), by = cut(as.POSIXct(V1), : unused argument (by = cut(as.POSIXct(V1), "5 mins")) I cannot understand my error code though.. – Yun Hyunsoo Oct 12 '20 at 06:42
  • @YunHyunsoo Have you loaded `library(data.table)`? – Rui Barradas Oct 12 '20 at 08:02
  • yes i have solved the error after library(data.table). I apologize for the bothering. Thank you so much for the explanation. Thank you!!! – Yun Hyunsoo Oct 12 '20 at 08:19