Good afternoon. I have the following data structure:
structure(list(CIDADE = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L,
1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L,
2L), .Label = c("A", "B"), class = "factor"), MES = c(1L, 2L,
3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 11L, 12L, 1L, 2L, 3L, 4L, 5L,
6L, 7L, 8L, 9L, 10L, 11L, 12L), CLIENTES = c(29L, 26L, 25L, 29L,
30L, 27L, 25L, 25L, 25L, 25L, 27L, 27L, 27L, 26L, 27L, 28L, 28L,
25L, 26L, 26L, 25L, 30L, 30L, 29L), REMANEJADOS = c(3L, 2L, 1L,
1L, 4L, 4L, 2L, 3L, 1L, 1L, 3L, 4L, 5L, 3L, 4L, 2L, 5L, 5L, 5L,
4L, 5L, 2L, 4L, 3L), REMANEJAMENTOS = c(4L, 3L, 4L, 3L, 5L, 1L,
4L, 4L, 2L, 5L, 5L, 1L, 5L, 4L, 2L, 5L, 5L, 5L, 2L, 1L, 5L, 2L,
3L, 3L), TRANSFERENCIAS = c(2L, 4L, 2L, 1L, 3L, 3L, 5L, 4L, 2L,
3L, 4L, 5L, 1L, 4L, 1L, 4L, 1L, 4L, 4L, 1L, 1L, 1L, 2L, 4L),
EVASOES = c(1L, 2L, 2L, 5L, 1L, 2L, 1L, 1L, 2L, 2L, 5L, 2L,
5L, 4L, 3L, 2L, 5L, 2L, 4L, 1L, 2L, 4L, 3L, 5L), HORAS = c(80,
80, 80, 80, 80, 80, 80, 80, 80, 80, 80, 80, 80, 80, 80, 80,
80, 80, 80, 80, 80, 80, 80, 80), TURMAS = c(2L, 1L, 1L, 1L,
2L, 2L, 2L, 3L, 3L, 1L, 2L, 1L, 2L, 3L, 1L, 3L, 1L, 1L, 1L,
1L, 1L, 1L, 2L, 3L)), .Names = c("CIDADE", "MES", "CLIENTES",
"REMANEJADOS", "REMANEJAMENTOS", "TRANSFERENCIAS", "EVASOES",
"HORAS", "TURMAS"), row.names = c(NA, -24L), class = "data.frame")
I calculated the accumulation of some variables that are necessary
dados <- dados %>%
group_by(CIDADE) %>%
mutate(CliAcu = cumsum(CLIENTES),
RmdAcu = cumsum(REMANEJADOS),
RmtAcu = cumsum(REMANEJAMENTOS),
TrsAcu = cumsum(TRANSFERENCIAS),
EvsAcu = cumsum(EVASOES),
TurAcu = cumsum(TURMAS))
Now I need to calculate a formula that takes into account these accumulated variables ( CliAcu
, RmdAcu
, RmtAcu
, TrsAcu
, EvsAcu
, TurAcu
) and the HORAS
variable according to following relationship:
Formula = (CliAcu + RmdAcu - RmtAcu[mês_anterior] - TrsAcu[mês_anterior] - EvsAcu[mês_anterior])*HORAS/TurAcu
I have the following code that does what I need:
if(dim(dados)[1] != 0){
dados$Valor[1] <- (dados$CliAcu[1]+dados$RmdAcu[1]-0-0-0)*
dados$HORAS[1]/dados$TurAcu[1]
if(nrow(dados) != 1){
for(i in 2:nrow(dados)){
if(dados$MES[i] == 1){
dados$Valor[i] <- (dados$CliAcu[i]+dados$RmdAcu[i]-0-0-0)*
dados$HORAS[i]/dados$TurAcu[i]
} else{
if(dados$MES[i] <= dados$MES[i-1]){
dados$Valor[i] <- (dados$CliAcu[i]+dados$RmdAcu[i]-0-0-0)*
dados$HORAS[i]/dados$TurAcu[i]
} else{
dados$Valor[i] <- (dados$CliAcu[i]+dados$RmdAcu[i]-
dados$EvsAcu[i-1]-dados$RmtAcu[i-1]-
dados$TrsAcu[i-1])*
dados$HORAS[i]/dados$TurAcu[i]
}
}
}
}
}
As an observation, I am decreasing 0 three times in 3 counts because in the 1st month of the observations the subtractions do not interest me (this 1st month can be any of the months of the year).
Someone has some suggestions for improvement of this last code, using the package dplyr
for example.