Provides supercharged version of dplyr::mutate
with .by (group by), .order_by and .frame aggregation over arbitrary
window frame
Arguments
- x
(
data.frame_- ...
expressions to be passed to dplyr::mutate
- .by
(expression, optional: Yes) Columns to group by
- .order_by
(expression, optional: Yes) Columns to order by
- .frame
(vector, optional: Yes) Object of class
framecreated by one of these functions:rows_between,range_between- .complete
(flag, default: FALSE) passed to slider::slide
Details
A window function returns a value for every input row of a dataframe
based on a group of rows (frame) in the neighborhood of the input row. This
function implements computation over groups (partition_by in SQL) in a
predefined order (order_by in SQL) across a neighborhood of rows (frame)
defined by
rows_between: Number of rows before and after the corresponding row. Example:c(2, 1)range_between: Range is numeric or interval objects Example:c(days(2), days(1))
This implementation is inspired by spark's window API.
The output has the same row order as the input independent of the
order_by.
See also
rows_between(), range_between(), mutate()
Examples
library("magrittr") # for pipe
# example 1: rows between
# Using iris dataset,
# compute cumulative mean of column `Sepal.Length`
# ordered by `Petal.Width` and `Sepal.Width` columns
# grouped by `Petal.Length` column
iris %>%
mutate(sl_mean = mean(Sepal.Length),
.order_by = c(Petal.Width, Sepal.Width),
.by = Petal.Length,
.frame = rows_between(Inf, 0),
) %>%
dplyr::slice_min(n = 3, Petal.Width, by = Species)
#> # A tibble: 15 × 6
#> Sepal.Length Sepal.Width Petal.Length Petal.Width Species sl_mean
#> <dbl> <dbl> <dbl> <dbl> <fct> <dbl>
#> 1 4.9 3.1 1.5 0.1 setosa 4.9
#> 2 4.8 3 1.4 0.1 setosa 4.8
#> 3 4.3 3 1.1 0.1 setosa 4.3
#> 4 5.2 4.1 1.5 0.1 setosa 5.05
#> 5 4.9 3.6 1.4 0.1 setosa 4.85
#> 6 4.9 2.4 3.3 1 versicolor 4.95
#> 7 5 2 3.5 1 versicolor 5
#> 8 6 2.2 4 1 versicolor 6
#> 9 5.8 2.7 4.1 1 versicolor 5.8
#> 10 5.7 2.6 3.5 1 versicolor 5.35
#> 11 5.5 2.4 3.7 1 versicolor 5.5
#> 12 5 2.3 3.3 1 versicolor 5
#> 13 6.1 2.6 5.6 1.4 virginica 6.1
#> 14 6 2.2 5 1.5 virginica 6
#> 15 6.3 2.8 5.1 1.5 virginica 6.3
# example 2: range between
# Using a sample airquality dataset,
# compute mean temp over last seven days in the same month for every row
set.seed(101)
airquality %>%
# create date column
dplyr::mutate(date_col = lubridate::make_date(1973, Month, Day)) %>%
# create gaps by removing some days
dplyr::slice_sample(prop = 0.8) %>%
# compute mean temperature over last seven days in the same month
tidier::mutate(avg_temp_over_last_week = mean(Temp, na.rm = TRUE),
.order_by = date_col,
.by = Month,
.frame = range_between(
lubridate::days(7), # 7 days before current row
lubridate::days(-1) # do not include current row
)
)
#> # A tibble: 122 × 8
#> Ozone Solar.R Wind Temp Month Day date_col avg_temp_over_last_week
#> <int> <int> <dbl> <int> <int> <int> <date> <dbl>
#> 1 10 264 14.3 73 7 12 1973-07-12 85.5
#> 2 NA 127 8 78 6 26 1973-06-26 75.4
#> 3 16 77 7.4 82 8 3 1973-08-03 81
#> 4 14 191 14.3 75 9 28 1973-09-28 71.8
#> 5 NA 138 8 83 6 30 1973-06-30 76.6
#> 6 NA 98 11.5 80 6 28 1973-06-28 75.8
#> 7 122 255 4 89 8 7 1973-08-07 83.7
#> 8 47 95 7.4 87 9 5 1973-09-05 92.5
#> 9 23 220 10.3 78 9 8 1973-09-08 90.7
#> 10 NA 286 8.6 78 6 1 1973-06-01 NaN
#> # ℹ 112 more rows
# example 3: custom function / modeling over window frame
fit_lm_safe = function(df, formula, min_rows = 2) {
if (is.null(df) || nrow(df) < min_rows) {
return(NULL)
}
tryCatch(
lm(formula, data = df),
error = function(e) NULL
)
}
mtcars %>%
mutate(s = list(fit_lm_safe(pick(everything()), mpg ~ .)),
.by = c(cyl, vs),
.order_by = qsec,
.frame = range_between(-1, 3),
.complete = FALSE
) %>%
head(10)
#> # A tibble: 10 × 12
#> mpg cyl disp hp drat wt qsec vs am gear carb s
#> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <list>
#> 1 21 6 160 110 3.9 2.62 16.5 0 1 4 4 <list [1]>
#> 2 21 6 160 110 3.9 2.88 17.0 0 1 4 4 <list [1]>
#> 3 22.8 4 108 93 3.85 2.32 18.6 1 1 4 1 <list [1]>
#> 4 21.4 6 258 110 3.08 3.22 19.4 1 0 3 1 <list [1]>
#> 5 18.7 8 360 175 3.15 3.44 17.0 0 0 3 2 <list [1]>
#> 6 18.1 6 225 105 2.76 3.46 20.2 1 0 3 1 <list [1]>
#> 7 14.3 8 360 245 3.21 3.57 15.8 0 0 3 4 <list [1]>
#> 8 24.4 4 147. 62 3.69 3.19 20 1 0 4 2 <list [1]>
#> 9 22.8 4 141. 95 3.92 3.15 22.9 1 0 4 2 <list [1]>
#> 10 19.2 6 168. 123 3.92 3.44 18.3 1 0 4 4 <list [1]>
if (FALSE) { # \dontrun{
# example 4: parallel execution across many groups using tidyr::nest and furrr
future::plan(future::multisession(workers = 2))
iris %>%
tidyr::nest(.by = Species) %>%
dplyr::mutate(
data = furrr::future_map(data, ~ .x %>%
mutate(
sl_mean = mean(Sepal.Length),
.order_by = Petal.Width,
.frame = rows_between(2, 2)
)
)
) %>%
tidyr::unnest(data)
} # }