R4marketing | канал Алексея Селезнёва | Язык R. Страница 2

library(tidyr)

# тестовые данные
df <- tibble(
  id = 1:3,
  x = c("a", "a-b", "a-b-c")
)

# запускаем процесс отладки
df |> separate_wider_delim(
  x, 
  delim = "-", 
  names = c("x", "y"),
  too_few = "debug",
  too_many = "debug"
)

# A tibble: 3 × 6
     id x     y     x_ok  x_pieces x_remainder
  <int> <chr> <chr> <lgl>    <int> <chr>      
1     1 a     NA    FALSE        1 ""         
2     2 a-b   b     TRUE         2 ""         
3     3 a-b-c b     FALSE        3 "-c"    

x_pieces

x_remainder

# A tibble: 3 × 3
     id a     b    
  <int> <chr> <chr>
1     1 a     NA   
2     2 a     b    
3     3 a     b  

# тестовые данные
vt_census <- tidycensus::get_decennial(
  geography = "block",
  state = "VT",
  county = "Washington",
  variables = "P1_001N",
  year = 2020
)

# разбиваем столбец GEOID на 4 компонента по позиции
vt_census |>
  select(GEOID) |> 
  separate_wider_position(
    GEOID,
    widths = c(state = 2, county = 3, tract = 6, block = 4)
  )

# разбиваем столбец names на компоненты по разделителю
vt_census |>
  select(NAME) |> 
  separate_wider_delim(
    NAME,
    delim = ", ",
    names = c("block", "block_group", "tract", "county", "state")
  )

# избавляемся от мусора с помощью разбивки через регулярные выражения
vt_census |>
  select(NAME) |> 
  separate_wider_regex(
    NAME,
    patterns = c(
      "Block ", block = "\\d+", ", ",
      "Block Group ", block_group = "\\d+", ", ",
      "Census Tract ", tract = "\\d+.\\d+", ", ",
      county = "[^,]+", ", ",
      state = ".*"
    )
  )

# тестовые данные
df_delim <- tibble(
  id = 1:3,
  x = c("a", "a-b", "a-b-c")
)

df <- tibble(
  id = 1:3, 
  x = c("ab", "def", "")
)

# разбивка на строки по разделителю
df |> separate_longer_delim(x, delim = "-")
# разбивка на строки по позиции
df %>% separate_longer_position(x, 1)

library(tidyquery)
library(nycflights13)
library(dplyr)

query(
" SELECT origin, dest,
    COUNT(flight) AS num_flts,
    round(SUM(seats)) AS num_seats,
    round(AVG(arr_delay)) AS avg_delay
  FROM flights f LEFT OUTER JOIN planes p
    ON f.tailnum = p.tailnum
  WHERE distance BETWEEN 200 AND 300
    AND air_time IS NOT NULL
  GROUP BY origin, dest
  HAVING num_flts > 3000
  ORDER BY num_seats DESC, avg_delay ASC
  LIMIT 2;"
)

#> # A tibble: 2 × 5
#>   origin dest  num_flts num_seats avg_delay
#>   <chr>  <chr>    <int>     <dbl>     <dbl>
#> 1 LGA    DCA       4468    712643         6
#> 2 EWR    BOS       5247    611192         5

airports %>%
  query("SELECT name, lat, lon ORDER BY lat DESC LIMIT 5")

show_dplyr(
" SELECT manufacturer, 
    COUNT(*) AS num_planes
  FROM planes
  WHERE engine = 'Turbo-fan'
  GROUP BY manufacturer
  ORDER BY num_planes DESC;"
)

#> planes %>%
#>   filter(engine == "Turbo-fan") %>%
#>   group_by(manufacturer) %>%
#>   summarise(num_planes = dplyr::n()) %>%
#>   ungroup() %>%
#>   arrange(dplyr::desc(num_planes))