Урок 5 Добавление, изменение и удаление строк дата фрейма через rows_*()

5.1 Описание

В SQL мы часто используем операции изменения данных, такие как INSERT, UPDATE и DELETE, так вот начиная с версии dplyr 1.0.0 в пакете появилось целое семейство функций которые реализуют эти операции с фреймами на языке R.

Функции которые будут рассмотрены в этом видео: - rows_insert() - rows_update() - rows_upsert() - rows_patch() - rows_delete()

Также мы разберёмся с новым аргументом функции summarise(), .groups, который позволяет изменять группировку данных после их агрегации.

В основе урока лежит статья “dplyr 1.0.0: last minute additions”.

5.2 Видео

5.3 Код

#devtools::install_github("tidyverse/dplyr")
library(dplyr)

# summarise + .groups
starwars %>% 
  group_by(homeworld, species) %>% 
  summarise(n = n())

## аргумент .groups
### .groups = "drop_last" удалит последнюю группу
### .groups = "drop" удалит всю группировку
### .groups = "keep" созранит всю группировку
### .groups = "rowwise" разобъёт фрейм на группы как rowwise()

# rows_*()
## rows_update(x, y) обновляет строки в таблице x найденные в таблице y
## rows_patch(x, y) работает аналогично rows_update() но заменяет только NA
## rows_insert(x, y) добавляет строки в таблицу x из таблицы y
## rows_upsert(x, y) обновляет найденные строки в x и добавляет не найденные из таблицы y
## rows_delete(x, y) удаляет строки из x найденные в таблице y.

# Создаём тестовые данные
df <- tibble(a = 1:3, b = letters[c(1:2, NA)], c = 0.5 + 0:2)
df

new <- tibble(a = c(4, 5), b = c("d", "e"), c = c(3.5, 4.5))
new

# БАзовые примеры
## добавляем новые строки
df %>% rows_insert(new)

## row_insert вернёт ошибку если мы попытаемся добавить уже существующую строку
df %>% rows_insert(tibble(a = 3, b = "c"))

## если вы хотите обновить существующее значение необходимо использовать row_update
df %>% rows_update(tibble(a = 3, b = "c"))

## но rows_update вернёт ошибку если вы попытаетесь обновить несуществующее значание
df %>% rows_update(tibble(a = 4, b = "d"))

## rows_patch заполнит только пропущенные значения по ключу
df %>% 
  rows_patch(tibble(a = 2:3, b = "B"))

## rows_upsert также вы можете добавлять новые и заменять существуюие значения 
## функцией rows_upsert
df %>% 
  rows_upsert(tibble(a = 3, b = "c")) %>% 
  rows_upsert(tibble(a = 4, b = "d"))

# ################################
# РАЗБЕРЁМ Аргументы немного более подробно
set.seed(555)

# менеджеры
managers <- c("Paul", "Alex", "Tim", "Bill", "John")
# товары
products <- tibble(name  = paste0("product_", LETTERS), 
                   price = round(runif(n = length(LETTERS), 100, 400), 0))

# функция генерации купленных товаров
prod_list <- function(prod_list, size_min, size_max) {
  
  prod <- tibble(product = sample(prod_list, 
                                  size = round(runif(1, size_min, size_max), 0) ,
                                  replace = F))
    return(prod)
}


# генерируем продажи
sales <- tibble(id         = 1:200,
                manager_id = sample(managers, size = 200, replace = T),
                refund     = FALSE,
                refund_sum = 0)

# генерируем списки купленных товаров
sale_proucts <-
    sales %>%
      rowwise(id) %>%
      summarise(prod_list(products$name, 1, 6)) %>%
      left_join(products, by = c("product" = "name"))
  
# объединяем продажи с товарами
sales <- left_join(sales, sale_proucts, by = "id")

# возвраты
refund <- sample_n(sales, 25) %>%
          mutate( refund = TRUE,
                  refund_sum = price * 0.9) %>%
          select(-price, -manager_id) 

# отмечаем возвраты в таблице продаж
sales %>%
  rows_update(refund)

# аргумент by
result <-
  sales %>%
    rows_update(refund, by = c("id", "product"))

5.4 Упражнение

В этот раз вам необходимо будет рассчитать зарплату 6ти сотрудников.

Для получения тестовых данных выполните приведённый ниже пример кода:

library(dplyr)

# зарплатная ведомость со ставками от бухгалтерии
salary <- tibble(
  employee_id = 1:5,
  rate        = c(1000, 1200, 700, 1500, 2000),
  bonus       = rep(0, 5),
  penalty     = rep(0, 5)
)

# бонусы от руководителей отделов
bonus <- tibble(
  employee_id = c(3, 5),
  bonus = c(100, 500)
)

# штрафы от руководителей отделов
penalty <- tibble(
  employee_id = c(1, 4, 5),
  penalty = c(150, 320, 80)
)

# внесение нового сотрудника в ведомость
new <- tibble(
  employee_id = 6,
  rate = 500,
  bonus = 0,
  penalty = 0
)

# корректировки ставки по фактически выработанному времени
time_rate <- tibble(
  employee_id = 1:6, 
  time_rate = c(1, 1, 1, 0.8, 1, 0.5)
)

В результате вы сформировали 5 таблиц:

salary - зарплатная ведомость от бухгалтерии, бухгалтерия знает только данные о ставках сотрудников;
bonus - бонусы, которые выписали руководители отделов сотрудникам;
penalty - штрафы, которые выписали руководители отделов;
new - таблица с 6 сотрудником, он новичёк и бухгалтерия забыла внести его сразу в основную ведомость;
time_rate - данные о фактически отработанном времени сотрудника з амесяц.

Ваша задача расчитать фактическую запрлату каждого сотрудника по формуле total = rate * time_rate + bonus - penalty.

Итоговая таблица при правильном расчёте будет иметь следующий вид:

# A tibble: 6 x 6
  employee_id  rate bonus penalty time_rate total
        <dbl> <dbl> <dbl>   <dbl>     <dbl> <dbl>
1           1  1000     0     150       1     850
2           2  1200     0       0       1    1200
3           3   700   100       0       1     800
4           4  1500     0     320       0.8   880
5           5  2000   500      80       1    2420
6           6   500     0       0       0.5   250

4 Обновлённая функция summarise()

6 dplyr 1.1.0: аргумент .by, join_by(), reframe()