George
Reputation: 5681
average in previous group at the same place with another column
I have some data and I am dividing the mdo value by the count number of mdo instances in the previous group.
I am calculating the sog avg also.
But I want to calculate the sog avg that takes place to the same instances as the result (mdo/count) value.
library(dplyr)
library(lubridate)
library(purrr)
df <- tibble(mydate = as.Date(c("2019-05-11 23:01:00", "2019-05-11 23:02:00", "2019-05-11 23:03:00", "2019-05-11 23:04:00",
"2019-05-12 23:05:00", "2019-05-12 23:06:00", "2019-05-12 23:07:00", "2019-05-12 23:08:00",
"2019-05-13 23:09:00", "2019-05-13 23:10:00", "2019-05-13 23:11:00", "2019-05-13 23:12:00",
"2019-05-14 23:13:00", "2019-05-14 23:14:00", "2019-05-14 23:15:00", "2019-05-14 23:16:00",
"2019-05-15 23:17:00", "2019-05-15 23:18:00", "2019-05-15 23:19:00", "2019-05-15 23:20:00",
"2019-05-15 23:21:00", "2019-05-15 23:22:00", "2019-05-15 23:23:00", "2019-05-15 23:24:00",
"2019-05-15 23:25:00")),
mdo = c(1500, 1500, 1500, 1500,
1500, 1500, NA, 0,
0, 0, 900, 900, NA, NA, 1100, 1100,
1100, 200, 200, 200,200,
1100, 1100, 1100, 0
),
sog = c(12, 12, 12, 11, 10,9,
2,8.8, 8.7, 7.8, 11, 11, 12, 11,
9.54, 9.8, 10.4,4, 4, 4.5, 3.6,
7, 8, 9, 0))
df1 <- df %>%
mutate(grp = data.table::rleid(mdo))
df1 <- df1 %>%
#Keep only non-NA value
filter(!is.na(mdo)) %>%
#count occurence of each grp
count(grp, name = 'count') %>%
#Shift the count to the previous group
mutate(count = lag(count)) %>%
#Join with the original data
right_join(df1, by = 'grp') %>%
arrange(grp)
group_mdo <- df1 %>%
select(grp, mdo) %>%
unique() %>%
mutate(prev_mdo = lag(mdo, na.rm=TRUE)) %>%
select(-mdo) %>%
tidyr::fill(prev_mdo, .direction = "down")
df1 <- df1 %>%
left_join(group_mdo, by = "grp") %>%
mutate(result = ifelse(prev_mdo != 0, mdo / count, 0)) %>%
mutate(sog_avg = ifelse(prev_mdo != 0, map_dbl(.x = grp - 1, ~ mean(sog[grp == .x], na.rm=TRUE), na.rm=TRUE), NA))
The result right now is:
grp count mydate mdo sog prev_mdo result sog_avg
1 NA 2019-05-11 1500 12 NA NA NA
1 NA 2019-05-11 1500 12 NA NA NA
1 NA 2019-05-11 1500 12 NA NA NA
1 NA 2019-05-11 1500 11 NA NA NA
1 NA 2019-05-12 1500 10 NA NA NA
1 NA 2019-05-12 1500 9 NA NA NA
2 NA 2019-05-12 NA 2 1500 NA 11
3 6 2019-05-12 0 8.8 1500 0 2
3 6 2019-05-13 0 8.7 1500 0 2
3 6 2019-05-13 0 7.8 1500 0 2
4 3 2019-05-13 900 11 0 0 NA
4 3 2019-05-13 900 11 0 0 NA
5 NA 2019-05-14 NA 12 900 NA 11
5 NA 2019-05-14 NA 11 900 NA 11
6 2 2019-05-14 1100 9.54 900 550 11.5
6 2 2019-05-14 1100 9.8 900 550 11.5
6 2 2019-05-15 1100 10.4 900 550 11.5
7 3 2019-05-15 200 4 1100 66.7 9.91
7 3 2019-05-15 200 4 1100 66.7 9.91
7 3 2019-05-15 200 4.5 1100 66.7 9.91
7 3 2019-05-15 200 3.6 1100 66.7 9.91
8 4 2019-05-15 1100 7 200 275 4.03
8 4 2019-05-15 1100 8 200 275 4.03
8 4 2019-05-15 1100 9 200 275 4.03
9 3 2019-05-15 0 0 1100 0 8
My desired result:
grp count mydate mdo sog prev_mdo result sog_avg
1 NA 2019-05-11 1500 12 NA NA NA
1 NA 2019-05-11 1500 12 NA NA NA
1 NA 2019-05-11 1500 12 NA NA NA
1 NA 2019-05-11 1500 11 NA NA NA
1 NA 2019-05-12 1500 10 NA NA NA
1 NA 2019-05-12 1500 9 NA NA NA
2 NA 2019-05-12 NA 2 1500 NA NA
3 6 2019-05-12 0 8.8 1500 0 0
3 6 2019-05-13 0 8.7 1500 0 0
3 6 2019-05-13 0 7.8 1500 0 0
4 3 2019-05-13 900 11 0 0 0
4 3 2019-05-13 900 11 0 0 0
5 NA 2019-05-14 NA 12 900 NA NA
5 NA 2019-05-14 NA 11 900 NA NA
6 2 2019-05-14 1100 9.54 900 550 11
6 2 2019-05-14 1100 9.8 900 550 11
6 2 2019-05-15 1100 10.4 900 550 11
7 3 2019-05-15 200 4 1100 66.7 9.91
7 3 2019-05-15 200 4 1100 66.7 9.91
7 3 2019-05-15 200 4.5 1100 66.7 9.91
7 3 2019-05-15 200 3.6 1100 66.7 9.91
8 4 2019-05-15 1100 7 200 275 4.03
8 4 2019-05-15 1100 8 200 275 4.03
8 4 2019-05-15 1100 9 200 275 4.03
9 3 2019-05-15 0 0 1100 0 0
Where result is zero, sog_avg should be zero, where result is na, sog avg should be na.
And where result is being computed by using the previous group counts, sog avg should be computed with it's previous values.
So, for example:
mdo = 1100 , result is 550 because counts in previous non null group are 2 (mdo value 900).
1100 / 2 = 550 . At this point sog avg should be (11 + 11) / 2 = 11 because counts were 2 in the previous non null group.
Upvotes: 1
Views: 136
Answers (1)
Cole
Reputation: 11255
Here is a data.table approach. It extensively uses the idea of making groups by using base table or tapply and then lags those results. Note, this answer would fail if mdo is not constant throughout a group.
library(data.table)
dt = as.data.table(df)
dt[, grp := rleid(mdo)]
dt[!is.na(mdo),
count := {
cnt = table(grp)
rep(shift(cnt), cnt)
}
]
setcolorder(dt, c("grp", "count", "mydate", "mdo", "sog"))
dt[,
prev_mdo := {
ord = table(grp)
nafill(rep(shift(mdo[cumsum(ord)]), ord), "locf")
}
]
dt[, result := fifelse(prev_mdo != 0L, mdo / count, 0)]
dt[!is.na(result),
sog_avg := {
mn = tapply(sog, grp, mean)
rep(shift(mn), table(grp))
}]
dt[result == 0 | is.na(result), sog_avg := result]
dt
#> grp count mydate mdo sog prev_mdo result sog_avg
#> 1: 1 NA 2019-05-11 1500 12.00 NA NA NA
#> 2: 1 NA 2019-05-11 1500 12.00 NA NA NA
#> 3: 1 NA 2019-05-11 1500 12.00 NA NA NA
#> 4: 1 NA 2019-05-11 1500 11.00 NA NA NA
#> 5: 1 NA 2019-05-12 1500 10.00 NA NA NA
#> 6: 1 NA 2019-05-12 1500 9.00 NA NA NA
#> 7: 2 NA 2019-05-12 NA 2.00 1500 NA NA
#> 8: 3 6 2019-05-12 0 8.80 1500 0.00000 0.000000
#> 9: 3 6 2019-05-13 0 8.70 1500 0.00000 0.000000
#> 10: 3 6 2019-05-13 0 7.80 1500 0.00000 0.000000
#> 11: 4 3 2019-05-13 900 11.00 0 0.00000 0.000000
#> 12: 4 3 2019-05-13 900 11.00 0 0.00000 0.000000
#> 13: 5 NA 2019-05-14 NA 12.00 900 NA NA
#> 14: 5 NA 2019-05-14 NA 11.00 900 NA NA
#> 15: 6 2 2019-05-14 1100 9.54 900 550.00000 11.000000
#> 16: 6 2 2019-05-14 1100 9.80 900 550.00000 11.000000
#> 17: 6 2 2019-05-15 1100 10.40 900 550.00000 11.000000
#> 18: 7 3 2019-05-15 200 4.00 1100 66.66667 9.913333
#> 19: 7 3 2019-05-15 200 4.00 1100 66.66667 9.913333
#> 20: 7 3 2019-05-15 200 4.50 1100 66.66667 9.913333
#> 21: 7 3 2019-05-15 200 3.60 1100 66.66667 9.913333
#> 22: 8 4 2019-05-15 1100 7.00 200 275.00000 4.025000
#> 23: 8 4 2019-05-15 1100 8.00 200 275.00000 4.025000
#> 24: 8 4 2019-05-15 1100 9.00 200 275.00000 4.025000
#> 25: 9 3 2019-05-15 0 0.00 1100 0.00000 0.000000
#> grp count mydate mdo sog prev_mdo result sog_avg
Upvotes: 1
Related Questions
- dplyr (R) - Average of a column per group
- Average by group for 'n' number of rows per group - R
- compute avg in every previous group number
- average column rows according to value in other columns
- dplyr: average over a range based on first occurrence in a different column
- Average over groups and include previous groups
- How to return an average of last n rows from different group (indicated by a variable)
- Averaging dataframe based on current row-value and preceeding rows
- Finding mean of previous set of grouped data with dplyr
- R: Calculating mean value for preceding rows and within groups