How to separate a time series basis into periods

Question

I have a time series basis. On the basis has information of consumption of pig feed (follows below with information of only one day and one animal). There is a column with identification of the animal, day and time of day that the animal consumed the feed. In the time column I needed it to be 0 to 23, and in the hours that the animal did not consume it to be zero consumption. After that I need to calculate the total consumption every six hours within each day. That is, in the final table it would have only the total consumption from 0 to 5, 6 to 11, 12 to 17 and 18 to 23 hours.

    X Animal Dia Hora Consumo
1   1      5   1    1   89.96
2   2      5   1    1   44.98
3   3      5   1    1   74.97
4   4      5   1    2  134.95
5   5      5   1    2   14.99
6   6      5   1    2   59.98
7   7      5   1    9  419.83
8   8      5   1    9   59.98
9   9      5   1    9   74.97
10 10      5   1    9   29.99
11 11      5   1   11   29.99
12 12      5   1   11  194.92
13 13      5   1   13  119.95
14 14      5   1   13   29.99
15 15      5   1   14   59.98
16 16      5   1   16  254.90
17 17      5   1   18  179.93

Answer 1

This problem can be solved with the packages tidyr and dplyr.

library(tidyr)
library(dplyr)

dados.completos <- dados %>%
  select(-X) %>%
  complete(Hora=0:23, Animal, Dia, fill = list(Consumo = 0))

The above code removes the column X, because the original problem does not quote it. Next, the function complete determines all possible values for the hours, saying to complete the cases without observations with 0, through the argument fill. All is saved within the object dados.completos:

dados.completos %>% print(n=Inf)
# A tibble: 33 x 4
    Hora Animal   Dia Consumo
   <int>  <int> <int>   <dbl>
 1     0      5     1    0.00
 2     1      5     1   89.96
 3     1      5     1   44.98
 4     1      5     1   74.97
 5     2      5     1  134.95
 6     2      5     1   14.99
 7     2      5     1   59.98
 8     3      5     1    0.00
 9     4      5     1    0.00
10     5      5     1    0.00
11     6      5     1    0.00
12     7      5     1    0.00
13     8      5     1    0.00
14     9      5     1  419.83
15     9      5     1   59.98
16     9      5     1   74.97
17     9      5     1   29.99
18    10      5     1    0.00
19    11      5     1   29.99
20    11      5     1  194.92
21    12      5     1    0.00
22    13      5     1  119.95
23    13      5     1   29.99
24    14      5     1   59.98
25    15      5     1    0.00
26    16      5     1  254.90
27    17      5     1    0.00
28    18      5     1  179.93
29    19      5     1    0.00
30    20      5     1    0.00
31    21      5     1    0.00
32    22      5     1    0.00
33    23      5     1    0.00

This being said, just separate the observations present within complete data into groups of 6 hours. For this, I used the function %/%, that when applied in a %/% b, calculates the entire division of a for b. Thus,

dados.completos %>%
  mutate(Periodo = Hora %/% 6) %>%
  group_by(Periodo) %>%
  summarise(Total = sum(Consumo))
# A tibble: 4 x 2
  Periodo  Total
    <dbl>  <dbl>
1       0 419.83
2       1 809.68
3       2 464.82
4       3 179.93

in which Periodo is the period of the day when consumption occurred. Period 0 is between 0 and 5 hours; period 1 between 6 and 11, and so on.

---

Edition held after this comment from the OP

To obtain results grouped by Animal, Dia and Periodo, do

dados.totais %>% mutate(Periodo = Hora %/% 6) %>%
  group_by(Hora, Animal, Dia, Periodo) %>%
  summarise(Total = sum(Consumo)) %>%
  print(n=Inf)