How to separate a time series base into periods

Question

How to separate a time series base into periods

Navigation

#1 by (4 votes)

3

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

    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

r

asked by anonymous 11.09.2017 / 21:49

1 answer

Error opening form - Delphi Dynamize language for placeholder

score 4 · Accepted Answer

This problem can be solved with 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 X column because the original problem does not quote it. Next, the complete function determines all possible values for the hours, saying to complete the cases without observations with 0, through the fill argument. Everything is saved inside 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

That said, simply separate the observations present within complete data into groups of 6 hours. For this, I used the %/% function, which when applied to a %/% b , calculates the entire division of a by 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

where 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.

Editing done after this OP comment

To get 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)