Window functions and grouped mutate/filter
2015-06-15
Перевод
https://cran.r-project.org/web/packages/dplyr/vignettes/window-functions.html
Оконная функция является вариантом агрегирующей функции. В то время как агрегирующая функция, такая как
sum() и mean(), принимает n исходных значений и возвращает единственное значение, оконная функция возвращает n значений. Результат работы оконной функции зависит от всех её исходных значений, поэтому оконные функции не включают поэлементно работающие функции, такие как + или round(). Оконные функции включают варианты агрегирующих функций, такие как cumsum() и cummean(), функции для ранжирования и упорядочивания, такие как rank(), и функции для получения смещений, такие как lead() и lag().Оконные функции используются совместно с
mutate и filter для решения широкого спектра задач, некоторые из которых показаны ниже:library(Lahman)
batting <- select(tbl_df(Batting), playerID, yearID, teamID, G, AB:H)
batting <- arrange(batting, playerID, yearID, teamID)
players <- group_by(batting, playerID)
# Для каждого игрока найти два года с наибольшим числом попаданий
filter(players, min_rank(desc(H)) <= 2 & H > 0)
# В пределах каждого игрока ранжировать каждый год по числу сыгранных игр
mutate(players, G_rank = min_rank(G))
# Для каждого игрока найти каждый год, который был лучше предыдущего
filter(players, G > lag(G))
# Для каждого игрока рассчитать среднее изменение количества сыгранных игр в год
mutate(players, G_change = (G - lag(G)) / (yearID - lag(yearID)))
# Для каждого игрока найти, когда они сыграли больше игр, чем в среднем
filter(players, G > mean(G))
# Для каждого игрока рассчитать z-статистику на основе числа сыгранных игр
mutate(players, G_z = (G - mean(G)) / sd(G))Перед чтением этой виньетки вы должны быть знакомы с
mutate() и filter(). Если вы хотите использовать оконные функции с базами данных SQL, вы также должны быть знакомы с основы трансляции в SQL с помощью dplyr.Типы оконных функций
Есть пять основных семейств оконных функций. Два семейства не связаны с агрегирующими функциями:- Ранжирующие и упорядочивающие функции:
row_number(),min_rank(RANKв SQL),dense_rank(),cume_dist(),percent_rank()иntile(). Эти функции принимают вектор для упорядочивания и возвращают разные типы рангов. - Смещения
lead()иlag()позволяют вам получить доступ к предыдущему и следующему значению вектора, упрощая расчёты разностей и трендов.
- Кумулятивные агрегирования:
cumsum(),cummin(),cummax()(базовые функции R) иcumall(),cumany(),cummean()(из dplyr). - Скользящие агрегирования действую в окне фиксированной ширины. Вы не найдёте их в составе базовых функций R или в dplyr, но есть много реализаций в других пакетах, таких как RcppRoll.
- Рециклирующие агрегирования, когда агрегирование повторяется в соответствии с длиной исходных данных. В R они не нужны, поскольку рециклирование вектора автоматически приводит к рециклированию агрегирование, когда это требуется. Это важно в SQL, поскольку присутствие агрегирующей функции обычно говорит базе данных, что нужно возвращать только одну строку на группу.
Ранжирующие функции
Ранжирующие функции являются “вариациями на тему” и различаются тем, как они обрабатывают [ранговые] связки:x <- c(1, 1, 2, 2, 2)
row_number(x)## [1] 1 2 3 4 5min_rank(x)## [1] 1 1 3 3 3dense_rank(x)## [1] 1 1 2 2 2row_number() и min_rank() могут быть рассчитаны с помощью базовой функции rank() с разными аргументами ties.method. Эти функции предоставляются для того, чтобы меньше печатать, а также для простоты конвертации из R в SQL.Две другие ранжирующие функции возвращают числа от 0 до 1.
percent_rank() возвращает процент ранга; cume_dist() возвращает долю значений, меньших или равных данному значению.cume_dist(x)## [1] 0.4 0.4 1.0 1.0 1.0percent_rank(x)## [1] 0.0 0.0 0.5 0.5 0.5# Выбрать два лучших года
filter(players, min_rank(desc(G)) < 2)
# Выбрать 10% лучших лет
filter(players, cume_dist(desc(G)) < 0.1)ntile() делит данные на n частей одинакового размера. Это грубое ранжирование, которое может использоваться вместе с mutate() для деления данных на части с последующим вычислением сводных статистик. Например, мы можем использовать ntile() для деления игроков в команде на четыре ранжированные группы и рассчитать среднее число игр в каждой группе.by_team_player <- group_by(batting, teamID, playerID)
by_team <- summarise(by_team_player, G = sum(G))
by_team_quartile <- group_by(by_team, quartile = ntile(G, 4))
summarise(by_team_quartile, mean(G))## Source: local data frame [4 x 2]
##
## quartile mean(G)
## 1 1 5.355776
## 2 2 24.912267
## 3 3 77.288933
## 4 4 373.693195desc(), чтобы ранжировать от большего к меньшему.Lead и lag
lead() и lag() создают смещенные версии исходного вектора (опережающую или отстающую относительно исходного вектора).x <- 1:5
lead(x)## [1] 2 3 4 5 NAlag(x)## [1] NA 1 2 3 4- Рассчитать разности или процентные изменения.
# Рассчитать относительное изменение сыгранных игр
mutate(players, G_delta = G - lag(G))## Source: local data frame [97,889 x 8]
## Groups: playerID
##
## playerID yearID teamID G AB R H G_delta
## 1 aardsda01 2004 SFN 11 0 0 0 NA
## 2 aardsda01 2006 CHN 45 2 0 0 34
## 3 aardsda01 2007 CHA 25 0 0 0 -20
## 4 aardsda01 2008 BOS 47 1 0 0 22
## 5 aardsda01 2009 SEA 73 0 0 0 26
## 6 aardsda01 2010 SEA 53 0 0 0 -20
## 7 aardsda01 2012 NYA 1 NA NA NA -52
## 8 aardsda01 2013 NYN 43 0 0 0 42
## 9 aaronha01 1954 ML1 122 468 58 131 NA
## 10 aaronha01 1955 ML1 153 602 105 189 31
## .. ... ... ... ... ... ... ... ...lag() более удобно, чем diff(), потому что для n исходных данных diff() возвращает n-1 результатов.- Узнать, когда значение изменилось.
# Найти, когда игрок менял команды
filter(players, teamID != lag(teamID))## Source: local data frame [79,782 x 7]
## Groups: playerID
##
## playerID yearID teamID G AB R H
## 1 aardsda01 2006 CHN 45 2 0 0
## 2 aardsda01 2007 CHA 25 0 0 0
## 3 aardsda01 2008 BOS 47 1 0 0
## 4 aardsda01 2009 SEA 73 0 0 0
## 5 aardsda01 2010 SEA 53 0 0 0
## 6 aardsda01 2012 NYA 1 NA NA NA
## 7 aardsda01 2013 NYN 43 0 0 0
## 8 aaronha01 1955 ML1 153 602 105 189
## 9 aaronha01 1956 ML1 153 609 106 200
## 10 aaronha01 1957 ML1 151 615 118 198
## .. ... ... ... ... ... ... ...lead() и lag() имеют необязательный аргумент order_by. Если он задан, то вместо использования порядка строк для определения порядка значений будет использована другая переменная. Это важно, если ваши данные еще не отсортированы, или вы хотите отсортировать их одним способом, а рассчитать лаг другим.Вот пример того, что случится, если вы не указали аргумент
order_by, когда это нужно было сделать:df <- data.frame(year = 2000:2005, value = (0:5) ^ 2)
scrambled <- df[sample(nrow(df)), ]
wrong <- mutate(scrambled, running = cumsum(value))
arrange(wrong, year)## year value running
## 1 2000 0 54
## 2 2001 1 55
## 3 2002 4 54
## 4 2003 9 50
## 5 2004 16 41
## 6 2005 25 25right <- mutate(scrambled, running = order_by(year, cumsum(value)))
arrange(right, year)## year value running
## 1 2000 0 0
## 2 2001 1 1
## 3 2002 4 5
## 4 2003 9 14
## 5 2004 16 30
## 6 2005 25 55Кумулятивные (накопительные, аккумулирующие) агрегирования
R предоставляет кумулятивную сумму (cumsum()), кумулятивное минимальное значение (cummin()) и кумулятивное максимальное значение (cummax()). (А также cumprod(), но это редко бывает полезным). Другими распространёнными аккумулирующими функциями являются cumany() и cumall() - кумулятивные версии || и &&, и cummean() - кумулятивное среднее. Они не являются встроенными функциями R, но их эффективные версии предоставляются в составе dplyr.cumany() и cumall() полезны для выбора всех строк до или всех строк после условия, верного для первого (или последнего) случая. Например, мы можем использовать cumany(), чтобы найти все записи для игроков после того, как они сиграли 150 игр за год:filter(players, cumany(G > 150))order_by, поэтому dplyr предоставляет вспомогательную функцию order_by(). Вы передаёте ей переменную, по которой хотите упорядочить, а затем вызываете оконную функцию:x <- 1:10
y <- 10:1
order_by(y, cumsum(x))## [1] 55 54 52 49 45 40 34 27 19 10with_order().Рециклирующие агрегирования
Рециклирование векторов в R упрощает выбор значений, больших или меньших, чем сводная статистика. Я называю это рециклирующим агрегированием, потому что значение для агрегирования повторяется до тех пор, пока его длина не станет равной исходному вектору. Рециклирующие агрегирования полезны, если вы хотите найти все значения, большие среднего или меньшие медианы:filter(players, G > mean(G))
filter(players, G < median(G))median() или quantile(), в процессе отбора наблюдений вы можете достичь того же эффекта при помощи ntile(). Например, x > median(x) эквивалентно ntile(x, 2) == 2; x > quantile(x, 75) эквивалентно ntile(x, 100) > 75 или ntile(x, 4) > 3.filter(players, ntile(G, 2) == 2)x == max(x)) или наименьшим (x == min(x)) значением переменной, но ранжирующие функции дают вам больший контроль над ранговыми связками и позволяют выбрать количество наблюдений.Рециклирующие агрегирования также полезны в сочетании с
mutate(). Например, для набора данных batting, мы можем рассчитать “карьерные года”, количество лет, в течение которых игрок играл после прихода в лигу:mutate(players, career_year = yearID - min(yearID) + 1)## Source: local data frame [97,889 x 8]
## Groups: playerID
##
## playerID yearID teamID G AB R H career_year
## 1 aardsda01 2004 SFN 11 0 0 0 1
## 2 aardsda01 2006 CHN 45 2 0 0 3
## 3 aardsda01 2007 CHA 25 0 0 0 4
## 4 aardsda01 2008 BOS 47 1 0 0 5
## 5 aardsda01 2009 SEA 73 0 0 0 6
## 6 aardsda01 2010 SEA 53 0 0 0 7
## 7 aardsda01 2012 NYA 1 NA NA NA 9
## 8 aardsda01 2013 NYN 43 0 0 0 10
## 9 aaronha01 1954 ML1 122 468 58 131 1
## 10 aaronha01 1955 ML1 153 602 105 189 2
## .. ... ... ... ... ... ... ... ...mutate(players, G_z = (G - mean(G)) / sd(G))## Source: local data frame [97,889 x 8]
## Groups: playerID
##
## playerID yearID teamID G AB R H G_z
## 1 aardsda01 2004 SFN 11 0 0 0 -1.1167685
## 2 aardsda01 2006 CHN 45 2 0 0 0.3297126
## 3 aardsda01 2007 CHA 25 0 0 0 -0.5211586
## 4 aardsda01 2008 BOS 47 1 0 0 0.4147997
## 5 aardsda01 2009 SEA 73 0 0 0 1.5209324
## 6 aardsda01 2010 SEA 53 0 0 0 0.6700611
## 7 aardsda01 2012 NYA 1 NA NA NA -1.5422042
## 8 aardsda01 2013 NYN 43 0 0 0 0.2446255
## 9 aaronha01 1954 ML1 122 468 58 131 -1.1556746
## 10 aaronha01 1955 ML1 153 602 105 189 0.5191140
## .. ... ... ... ... ... ... ... ...Оконные функции в SQL
Оконные функции в SQL слегка иные. Синтаксис немного отличается, и все кумулятивные, скользящие и рециклирующие агрегирующие функции основаны на простой агрегирующей функции. Цель этого раздела - не рассказать вам всё, что нужно знать об оконных функциях в SQL, а лишь напомнить вам основы и показать, как dplyr транслирует ваши выражения из R в SQL.Структура оконной функции в SQL
В SQL оконные функции имеют вид[expression] OVER ([partition clause] [order clause] [frame_clause]):- expression (выражение) является комбинацией имён переменных и оконных функций. Поддержка оконных функций в разных базах данных различна, но большинство из них поддерживает ранжирующие функции,
lead,lag,nth,first,last,count,min,max,sum,avgиstddev. dplyr генерирует их из выражений на языке R при вызове вами функцийmutateилиfilter. - partition clause (условие разделения) определяет, как оконная функция разбивается по группам. Это играет ту же роль, что и
GROUP BYдля агрегирующих функций иgroup_by()в dplyr. Для различных оконных функций есть возможность быть разделёнными на разные группы, но не все базы данных это поддерживают, как и dplyr. - order clause (условие порядка) контролирует упорядочивание (когда это имеет значение). Это важно для ранжирующих функций, поскольку определяет, по каким переменным ранжировать, но также требуется для кумулятивных функций и
lead. Всякий раз, когда вы думаете о понятиях “до” и “после” в SQL, вы должны указывать, какая переменная определяет порядок. В dplyr вы делаете это при помощиarrange(). Если условие порядка отсутствует, когда оно необходимо, некоторые базы данных выводят сообщение об ошибке, в то время как другие возвращают непредсказуемые результаты. - frame clause (условие фрейма) определяет, какие строки, передаваемые оконной функции, описывают, какие строки (относительно текущей) должны включаться. Условие фрейма обеспечивает два смещения, которые определяют начало и конец фрейма. Имеются три специальных значения: -Inf означает включение всех предшествующих строк (в SQL - “unbounded preceding”), 0 означает текущую строку (“current row”), Inf означает все последующие строки (“unbounded following”). Полный набор опций является всеобъемлющим, но довольно запутанным, и в краткой форме визуально представлен ниже.
- Рециклирующим:
BETWEEN UNBOUND PRECEEDING AND UNBOUND FOLLOWING - Кумулятивным:
BETWEEN UNBOUND PRECEEDING AND CURRENT ROW - Скользящим:
BETWEEN 2 PRECEEDING AND 2 FOLLOWING
Проще всего понять эти спецификации, глядя на несколько примеров. Простые примеры требуют только условия разделения и порядка:
- Ранжировать каждый год для игрока по числу хоумранов:
RANK() OVER (PARTITION BY playerID ORDER BY desc(H)) - Рассчитать изменение числа игр от одного года до следующего:
G - LAG(G) OVER (PARTITION G playerID ORDER BY yearID)
- Скользящая сумма G по каждому игроку:
SUM(G) OVER (PARTITION BY playerID ORDER BY yearID BETWEEN UNBOUND PRECEEDING AND CURRENT ROW) - Вычисление “карьерных лет”:
YearID - min(YearID) OVER (PARTITION BY playerID BETWEEN UNBOUND PRECEEDING AND UNBOUND FOLLOWING) + 1 - Вычисление скользящего среднего сыгранных игр:
MEAN(G) OVER (PARTITION BY playerID ORDER BY yearID BETWEEN 2 PRECEEDING AND 2 FOLLOWING)
Трансляция dplyr в SQL
Чтобы увидеть, как отдельные оконные функции транслируются в SQL, мы можем использоватьtranslate_sql() с аргументом window = TRUE.if (has_lahman("postgres")) {
players_db <- group_by(tbl(lahman_postgres(), "Batting"), playerID)
print(translate_sql(mean(G), tbl = players_db, window = TRUE))
print(translate_sql(cummean(G), tbl = players_db, window = TRUE))
print(translate_sql(rank(G), tbl = players_db, window = TRUE))
print(translate_sql(ntile(G, 2), tbl = players_db, window = TRUE))
print(translate_sql(lag(G), tbl = players_db, window = TRUE))
}if (has_lahman("postgres")) {
players_by_year <- arrange(players_db, yearID)
print(translate_sql(cummean(G), tbl = players_by_year, window = TRUE))
print(translate_sql(rank(), tbl = players_by_year, window = TRUE))
print(translate_sql(lag(G), tbl = players_by_year, window = TRUE))
}- Для ранжирующих функций упорядочивающая переменная является первым аргументом:
rank(x),ntile(y, 2). Если опущен или равенNULL, будет использоваться порядок по умолчанию, связанный с таблицей (как заданоarrange())). - Аккумулирующие агрегирования принимают единственный аргумент (вектор для агрегирования). Для управления порядком используйте
order_by(). - Агрегирования, реализованные в dplyr (
lead,lag,nth_value,first_value,last_value), имеют аргументorder_by. Его установка переопределяет порядок по умолчанию.
mutate(players,
min_rank(yearID),
order_by(yearID, cumsum(G)),
lead(order_by = yearID, G)
)arrange(). Он будет добавлен в будущем релизе.Трансляция фильтров на основе оконных функций
Есть некоторые ограничения оконных функций в SQL, которые делают их использование сWHERE сложным. Возьмём этот простой пример, где мы хотим найти год, когда каждый игрок сыграл больше всего игр:filter(players, rank(G) == 1)SELECT и ORDER_BY.SELECT *
FROM Batting
WHERE rank() OVER (PARTITION BY "playerID" ORDER BY "G") = 1;SELECT и обращение к ней в WHERE или HAVING не работает, потому что WHERE и HAVING вычисляются перед оконной функцией.SELECT *, rank() OVER (PARTITION BY "playerID" ORDER BY "G") as rank
FROM Batting
WHERE rank = 1;
SELECT *, rank() OVER (PARTITION BY "playerID" ORDER BY "G") as rank
FROM Batting
HAVING rank = 1;SELECT *
FROM (
SELECT *, rank() OVER (PARTITION BY "playerID" ORDER BY "G") as rank
FROM Batting
) tmp
WHERE rank = 1;
Комментариев нет:
Отправить комментарий