Преобразовать синтаксис соединения dplyr в чистый синтаксис data.table

Я изучаю data.table. Мне трудно преобразовать синтаксис соединения dplyr. Можете ли вы рекомендовать эквивалент данных .table для следующих тестовых случаев?

library(data.table)
library(dplyr)

dtProduct <- data.table(
    ProductID  = c(6, 33, 17, 88, 44, 51),
    ProductName= c("Shirt", "Helmet", "Gloves", "Towel", "Chair", "Detergent"),
    Price= c(25, 60, 10, 7.5, 135, 16),
    key = 'ProductID'
)

set.seed(20141216)
dtOrder <- data.table(
    OrderID    = sample(1001:9999, 12),
    CustomerID = sample(271:279, 12, replace=TRUE),
    # NOTE: some non-existent ProductID intentionally introduced
    ProductID  = sample(c(dtProduct[, ProductID], 155, 439), 12, replace=TRUE),
    Qty = sample(1:3, 12, replace=TRUE),
    key = 'OrderID'
)

> tables()
     NAME      NROW NCOL MB COLS                             KEY      
[1,] dtOrder     12    4  1 OrderID,CustomerID,ProductID,Qty OrderID  
[2,] dtProduct    6    3  1 ProductID,ProductName,Price      ProductID

> dtProduct
   ProductID ProductName Price
1:         6       Shirt  25.0
2:        17      Gloves  10.0
3:        33      Helmet  60.0
4:        44       Chair 135.0
5:        51   Detergent  16.0
6:        88       Towel   7.5
> dtOrder
    OrderID CustomerID ProductID Qty
 1:    1651        275         6   3
 2:    2726        272        88   2
 3:    3079        275        88   2
 4:    3168        274        17   1
 5:    4816        277        88   1
 6:    4931        278        51   1
 7:    5134        274       439   2
 8:    5265        272        33   3
 9:    7702        275        33   2
10:    7727        279       155   2
11:    8412        273        88   2
12:    9130        271        17   3

Case1: Показать детали заказа, нет соответствия идентификатора продукта

dtOrder %>%
    inner_join(dtProduct, by="ProductID") %>%
    transmute(OrderID, ProductID, ProductName, Qty, Price, ExtPrice=Qty*Price)

   OrderID ProductID ProductName Qty Price ExtPrice
1     1651         6       Shirt   3  25.0     75.0
2     3168        17      Gloves   1  10.0     10.0
3     9130        17      Gloves   3  10.0     30.0
4     5265        33      Helmet   3  60.0    180.0
5     7702        33      Helmet   2  60.0    120.0
6     4931        51   Detergent   1  16.0     16.0
7     2726        88       Towel   2   7.5     15.0
8     3079        88       Towel   2   7.5     15.0
9     4816        88       Towel   1   7.5      7.5
10    8412        88       Towel   2   7.5     15.0

Case2: Показать детали заказа, ВКЛЮЧАЯ НЕИСПРАВНОСТЬ ProductID

dtOrder %>%
    left_join(dtProduct, by="ProductID") %>%
    transmute(OrderID, ProductID, ProductName, Qty, Price, ExtPrice=Qty*Price)

   OrderID ProductID ProductName Qty Price ExtPrice
1     1651         6       Shirt   3  25.0     75.0
2     3168        17      Gloves   1  10.0     10.0
3     9130        17      Gloves   3  10.0     30.0
4     5265        33      Helmet   3  60.0    180.0
5     7702        33      Helmet   2  60.0    120.0
6     4931        51   Detergent   1  16.0     16.0
7     2726        88       Towel   2   7.5     15.0
8     3079        88       Towel   2   7.5     15.0
9     4816        88       Towel   1   7.5      7.5
10    8412        88       Towel   2   7.5     15.0
11    7727       155          NA   2    NA       NA
12    5134       439          NA   2    NA       NA

Case3: Показать ошибки заказа (только без соответствия ProductID)

dtOrder %>%
    left_join(dtProduct, by="ProductID") %>%
    filter(is.na(ProductName)) %>%
    select(OrderID, ProductID, ProductName, Qty)

  OrderID ProductID ProductName Qty
1    7727       155          NA   2
2    5134       439          NA   2

Case4: различные агрегаты по идентификатору продукта, результат сортировки по TotalSales по убыванию

dtOrder %>%
    inner_join(dtProduct, by="ProductID") %>%
    group_by(ProductID) %>%
    summarize(OrderCount=n(), TotalQty=sum(Qty), TotalSales=sum(Qty*Price)) %>%
    arrange(desc(TotalSales))

  ProductID OrderCount TotalQty TotalSales
1        33          2        5      300.0
2         6          1        3       75.0
3        88          4        7       52.5
4        17          2        4       40.0
5        51          1        1       16.0


Case5: различные агрегаты по идентификатору продукта, результат сортировки по TotalSales по убыванию

  • ПРИМЕЧАНИЕ 1. На этот раз ProductName отображается вместе с ProductID

  • ПРИМЕЧАНИЕ2: сортировка по убыванию TotalSales больше не работает (BUG?)

    dtOrder %>%
   inner_join(dtProduct, by="ProductID") %>%
   group_by(ProductID, ProductName) %>%
   summarize(OrderCount=n(), TotalQty=sum(Qty), TotalSales=sum(Qty*Price)) %>%
   arrange(desc(TotalSales))

  ProductID ProductName OrderCount TotalQty TotalSales
1         6       Shirt          1        3       75.0
2        17      Gloves          2        4       40.0
3        33      Helmet          2        5      300.0
4        51   Detergent          1        1       16.0
5        88       Towel          4        7       52.5

Благодарим вас за любую помощь.

Ответы

Ответ 1

setkey(dtOrder, ProductID)

(1-2)

# this will be literally what you wrote

dtProduct[dtOrder,
          list(OrderID, ProductID, ProductName, Qty, Price, ExtPrice=Qty*Price),
          nomatch = 0 # or omit this to get (2)
         ]

# but I think you'd be better off with this
dtProduct[dtOrder][, ExtPrice := Qty*Price][]

(3)

# you can again take the literal direction:
dtProduct[dtOrder][!is.na(ProductName)][,
          list(OrderID, ProductID, ProductName, Qty)]

# but again I think you'd be better off with
dtOrder[!dtProduct]

(4-5)

dtProduct[dtOrder, nomatch = 0][,
          list(OrderCount=.N, TotalQty=sum(Qty), TotalSales=sum(Qty*Price)),
          by = list(ProductID, ProductName)][
          order(-TotalSales)]

Ответ 2

Вы должны посмотреть ?data.table и просмотреть примеры. Это очень хороший способ обучения. Мы в процессе написания более подробные виньетки FR # 944, запланированные на 1.9.8. Но до тех пор:

- форму данных.

data.table синтаксис имеет вид:

x[i, j, by, ...] # i = where, j = select|modify|update, by = group by

- операции подмножества

Когда i является integer или logical expression, мы называем его подмножеством. Например:

x[a > 1]

Что это делает? Столбец a из data.table x проверяется на условие > 1, что приводит к логическому вектору = length(a). И те строки, где условие оценивается как TRUE, идентифицируются и возвращаются все столбцы, соответствующие этим строкам.

- соединяется как расширение подмножеств

Концепция

В data.table объединения можно рассматривать как естественное расширение подмножеств. То есть мы можем думать о соединении как операции подмножества, но используя другую таблицу данных. Это то, что мы подразумеваем под последовательным синтаксисом - форма x[i, j, by] не установлена.

Первым шагом к объединению в data.tables является установка ключей. Это может быть выполнено с помощью функции setkey(), цель которой двукратна:

  • упорядочивать строки таблицы данных в порядке возрастания (по возрастанию) по предоставленным столбцам. Это делается путем ссылки на эффективную память.

  • отметьте те столбцы, которые указаны в качестве ключевых столбцов, на которых могут выполняться соединения (если и когда вы выполняете соединение).

Обратите внимание, что в настоящее время для соединения формы x[i], x необходимо, чтобы ключевые столбцы (столбцы) были установлены абсолютно. i может быть установлен или не установлен.

  • Если i также имеет его набор ключевых столбцов, то объединения выполняются путем сопоставления первого столбца ключа i с первым ключевым столбцом x, вторым со вторым и т.д.

  • Если i не заданы ключевые столбцы, то первый столбец из i сопоставляется с первым столбцом ключа x, вторым столбцом i со вторым столбцом ключа x и т.д.

Да, мы знаем, что было бы удобно сопоставлять имена столбцов, когда i не имеет ключевых столбцов, но у нас просто не было времени, чтобы добраться до него.

Второй и последний шаг - выполнить соединение: -).

Но как операция объединения расширяет подмножество? Когда i является таблицей данных. Для каждой строки в i он находит соответствующие индексы строк в x путем сопоставления по столбцам x, которые мы установили. Это возвращает набор индексов строк x для каждой строки в i (или NA, если совпадение не найдено).

Теперь у нас есть соответствующие индексы строк. Все, что нам нужно вернуть, это столбцы. Но так как i также является data.table, он может иметь дополнительные столбцы. Таким образом, мы возвращаем столбцы как x, так и i для тех, которые соответствуют индексам строк.

Пример

Вот небольшой пример, который поможет вам усвоить концепцию, прежде чем двигаться дальше. Рассмотрим два data.tables x и Y, как показано ниже:

X = data.table(a=c(1,1,1,2,2,5,6), b=1:7, key="a")
#    a b
# 1: 1 1
# 2: 1 2
# 3: 1 3
# 4: 2 4
# 5: 2 5
# 6: 5 6
# 7: 6 7

key(X)
# [1] "a"

Y = data.table(a=c(6,2), c=letters[1:2])
#    a c
# 1: 6 a
# 2: 2 b

key(Y)
# NULL

# join
X[Y]
#    a b c
# 1: 6 7 a
# 2: 2 4 b
# 3: 2 5 b

Обратите внимание, что мы использовали аргумент key= в функции data.table(), чтобы напрямую установить столбцы ключей. В качестве альтернативы мы могли бы просто создать x без ключей, а затем setkey(X, a).

Функция key() возвращает столбцы ключей, если они есть. Если ключ не установлен, он возвращает NULL.

Y не имеет ключевых столбцов, а x имеет только один ключевой столбец. Таким образом, объединение выполняется с использованием первого столбца a из Y и первого ключевого столбца a of x. a=6 в Y соответствует строке 7 из x и a=2 в строках 4 и 5.

Вы можете проверить это, используя аргумент which = TRUE:

X[as.data.table(6), which=TRUE] # [1] 7
X[as.data.table(2), which=TRUE] # [1] 4 5

Это также удобный (и быстрый) способ подмножества data.table, но с использованием подмножества, основанного на бинарном поиске. Поскольку эта операция весьма полезна, data.table предоставляет простой способ сделать это, а не писать as.data.table() каждый раз.

# faster way of doing X[a == 6] on data.table with 'a' as key column
X[J(6)] # J for Join
X[J(2)]

# (or)

X[.(6)] # . is an alias for J
X[.(2)]

Я думаю, что это должно еще больше помочь понять, что мы подразумеваем подмножествами, являются расширениями объединений.

вернуться к вашему вопросу

Теперь давайте забудем о всех этих "левых", "правильных", "внутренних", "внешних" и т.д. и посмотрим на фактическую операцию, которую вы хотите выполнить. У вас есть два data.tables - dtP и dtO (сокращенный для удобства).

случай 1:

Для каждой строки столбца ProductID в dtO вы хотите найти соответствующие строки в dtP, но вы не хотите возвращать NA. И вы также хотите выбрать столбцы, которые вы хотите вывести, а также некоторые вычисления.

То есть i = dtO и x = dtP. Строка ключа для dtP установлена ​​правильно. Но ключевым столбцом для dtO является orderID. Если мы соединим как таковой, он будет соединяться orderID с dtO с ProductID от dtP, что неверно.

Либо мы должны установить ключ от dtO до ProductID, либо установить ключ из dtO в NULL и переместить столбец ProductID в качестве первого столбца (пока не будет выполнено сопоставление по именам). Установите ключ ProductID здесь:

# set key
setkey(dtO, ProductID)
# join
dtP[dtO, .(OrderID, ProductID, ProductName, Qty, Price, ExtPrice=Qty*Price), nomatch=0L]

Должно быть совершенно очевидно, что это происходит сейчас. При использовании только соответствующих индексов строк извлеките все эти столбцы (включая выражение).

Зачем нам сначала присоединяться и выбирать/агрегировать?

случай 2:

То же, что и в случае 1, но вам нужны даже несоответствующие строки. Ключ уже установлен правильно из случая 1.

# join
dtP[dtO, .(OrderID, ProductID, ProductName, Qty, Price, ExtPrice=Qty*Price)]

Возвращает все строки orderID, даже если нет совпадения, и все указанные столбцы (включая выражение).

случай 3:

Вы хотите, чтобы все строки в dtO не совпадали с dtP.

not-join or anti-join
dtO[!dtP]

Найти все строки, в которых столбец ключей dtP соответствует dtO. Верните все остальные строки из dtO. При необходимости вы можете указать все необходимые столбцы в j.

случай 4:

Прочитайте by=.EACHI из этого сообщения.

Вы присоединяетесь к ProductID, а затем агрегируете по тому же столбцу. Но зачем нам этот промежуточный результат? Это совершенно ненужно и тратит память и вычислительное время! Вместо этого мы можем использовать by=.EACHI, который будет оценивать j-выражение для совпадающих строк для каждой строки в i.

dtO[dtP, .(.N, sQty = sum(Qty), sSales = sum(Qty*Price)), by=.EACHI, nomatch=0L][order(-sSales)]

Чтобы проверить свое понимание, попробуйте выяснить, почему мы не сделали dtP[dtO, ...] здесь.

случай 5:

Идентичен для @eddi.

Я лично считаю более естественным думать о реальной задаче, которую я хочу выполнить, вместо того, чтобы выяснить тип функции соединения, связанной с задачей, которую я хочу выполнить (я никогда не могу вспомнить, какая data.table "оставлена" "и какой из них" прав "... и, кстати, что такое" внутреннее "," внешнее "и" полное внешнее "?).

НТН