---
title: Arrow
author: David Kane and Zayan Farooq
tutorial:
  id: arrow
output:
  learnr::tutorial:
    progressive: yes
    allow_skip: yes
runtime: shiny_prerendered
description: 'Tutorial for Chapter 22: Arrow'
---

```{r setup, include = FALSE}
library(learnr)
library(tutorial.helpers)
library(tidyverse)
library(arrow)
library(dbplyr, warn.conflicts = FALSE)
library(duckdb)
knitr::opts_chunk$set(echo = FALSE)
options(tutorial.exercise.timelimit = 1200, 
        tutorial.storage = "local") 

pq_path <- "data/seattle-library-checkouts"

```

```{r copy-code-chunk, child = system.file("child_documents/copy_button.Rmd", package = "tutorial.helpers")}
```

```{r info-section, child = system.file("child_documents/info_section.Rmd", package = "tutorial.helpers")}
```


<!-- DK: file.remove() all the files which you had the student create. -->

<!-- DK: Stick a bunch of comments here about the complexity of this tutorial, given the data that has to be downloaded.  -->


## Introduction
### 

*This is a ROUGH, UNFINISHED, draft.*

This tutorial covers [Chapter 22: Arrow](https://r4ds.hadley.nz/arrow.html) from [*R for Data Science (2e)*](https://r4ds.hadley.nz/) by Hadley Wickham, Mine Çetinkaya-Rundel, and Garrett Grolemund. Note that the data used in this chapter is more than 8 gigabytes. This might be too much for your computer. We are also not sure if our approach is sensible. Consider this an very experimental tutorial. You might be better off just reading the chapter. 

## Getting the data
### 

CSV files are designed to be easily read by humans. They’re a good interchange format because they’re very simple and they can be read by every tool under the sun. But CSV files aren’t very efficient: you have to do quite a lot of work to read the data into R. In this chapter, you’ll learn about a powerful alternative: the [parquet format](https://parquet.apache.org/), an open standards-based format widely used by big data systems.

### Exercise 1

Load the **tidyverse** library.

```{r getting-the-data-1, exercise = TRUE}

```

```{r getting-the-data-1-hint-1, eval = FALSE}
library(...)
```

```{r, include=FALSE}
library(tidyverse)
```

### 

We’ll pair parquet files with Apache Arrow, a multi-language toolbox designed for efficient analysis and transport of large datasets. We’ll use Apache Arrow via the the arrow package, which provides a dplyr backend allowing you to analyze larger-than-memory datasets using familiar **dplyr** syntax

### Exercise 2

Load the **arrow** library.

```{r getting-the-data-2, exercise = TRUE}

```

```{r getting-the-data-2-hint, eval = FALSE}
library(...)
```

```{r, include=FALSE}
library(arrow)
```

###

As an additional benefit, arrow is extremely fast: you’ll see some examples later in the chapter.

### Exercise 3

Load the **duckdb** library

```{r getting-the-data-3, exercise = TRUE}

```

```{r getting-the-data-3-hint, eval = FALSE}
library(...)
```

```{r, include=FALSE}
library(duckdb)
```
###

Both **arrow** and **dbplyr** provide **dplyr** backends, so you might wonder when to use each. In many cases, the choice is made for you, as in the data is already in a database or in parquet files, and you’ll want to work with it as is. But if you’re starting with your own data (perhaps CSV files), you can either load it into a database or convert it to parquet.

### Exercise 4

Load the **dbplyr** library with `warn.conflicts = FALSE`

```{r getting-the-data-4, exercise = TRUE}

```

```{r getting-the-data-4-hint, eval = FALSE}
library(..., warn.conflicts = FALSE)
```

```{r, include=FALSE}
library(dbplyr, warn.conflicts = FALSE)
```

###

In general, it’s hard to know what will work best, so in the early stages of your analysis we’d encourage you to try both and pick the one that works the best for you.

### Exercise 5

Press `"Run Code"`. This will download a 9 GB CSV file which will take about 5 minutes to download so go check you email or read some more of the chapter and come back in 5 minutes.

```{r getting-the-data-5, exercise = TRUE}
dir.create("data", showWarnings = FALSE)

curl::multi_download(
  "https://r4ds.s3.us-west-2.amazonaws.com/seattle-library-checkouts.csv",
  "data/seattle-library-checkouts.csv",
  resume = TRUE
)
```

###

This dataset contains 41,389,465 rows that tell you how many times each book was checked out each month from April 2005 to October 2022.
 
## Opening a dataset
###

Let’s start by taking a look at the data. At 9 GB, this file is large enough that we probably don’t want to load the whole thing into memory. A good rule of thumb is that you usually want at least twice as much memory as the size of the data, and many laptops top out at 16 GB. This means we want to avoid `read_csv()` and instead use the `arrow::open_dataset()`.

### Exercise 1

Set `seattle_csv` to `open_dataset()`. Within `open_dataset()`, add the argument `sources = "data/seattle-library-checkouts.csv"`

```{r opening-a-dataset-1, exercise = TRUE}

```

```{r opening-a-dataset-1-hint, eval = FALSE}
... <- open_dataset(
  sources = "...")
)
```

```{r, include=FALSE}
# seattle_csv <- open_dataset(
#  sources = "data/seattle-library-checkouts.csv"
# )
```

###

In this chapter, we’ll continue to use the tidyverse, particularly dplyr, but we’ll pair it with the arrow package which is designed specifically for working with large data. Later in the chapter, we’ll also see some connections between arrow and duckdb, so we’ll also need dbplyr and duckdb.

### Exercise 2

Copy previous code and add the argument `format = "csv"` to `open_dataset()`

```{r opening-a-dataset-2, exercise = TRUE}

```

<button onclick = "transfer_code(this)">Copy previous code</button>

```{r opening-a-dataset-2-hint, eval = FALSE}
seattle_csv <- open_dataset(
  sources = "data/seattle-library-checkouts.csv",
  format = "..."
)
```

```{r, include=FALSE}
# seattle_csv <- open_dataset(
#   sources = "data/seattle-library-checkouts.csv",
#   format = "csv"
# )
```

###

What happens when this code is run? `open_dataset()` will scan a few thousand rows to figure out the structure of the dataset. Then it records what it’s found and stops; it will only read further rows as you specifically request them. 

### Exercise 3

Type `seattle_csv` and run code.

```{r opening-a-dataset-3, exercise = TRUE}

```

```{r opening-a-dataset-3-hint, eval = FALSE}
seattle_csv
```

```{r, include=FALSE}
# seattle_csv
```
###

The first line in the output tells you that `seattle_csv` is stored locally on-disk as a single CSV file; it will only be loaded into memory as needed. The remainder of the output tells you the column type that arrow has imputed for each column.

### Exercise 4

Copy previous code and pipe it into `glimpse()`

```{r opening-a-dataset-4, exercise = TRUE}

```

<button onclick = "transfer_code(this)">Copy previous code</button>

```{r opening-a-dataset-4-hint, eval = FALSE}
seattle_csv |>
  ...()
```

```{r, include=FALSE}
# seattle_csv |>
#   glimpse()
```

###

We can see what’s actually in with glimpse(). This reveals that there are ~41 million rows and 12 columns, and shows us a few values.

### Exercise 5

Start a new pipe with `seattle_csv`.

```{r opening-a-dataset-5, exercise = TRUE}

```

```{r opening-a-dataset-5-hint, eval = FALSE}
seattle_csv
```

```{r, include=FALSE}
# seattle_csv
```

###

Let's start off with a code to tell us the total number of checkouts per year.

### Exercise 6

Continue the pipe with `count()`. Within `count()`, add the arguments `CheckoutYear` and `wt = Checkouts`. Make sure to separate the arguments with commas.

```{r opening-a-dataset-6, exercise = TRUE}

```

<button onclick = "transfer_code(this)">Copy previous code</button>

```{r opening-a-dataset-6-hint, eval = FALSE}
seattle_csv |> 
  count(..., wt = ...)
```

```{r, include=FALSE}
# seattle_csv |> 
#   count(CheckoutYear, wt = Checkouts)
```

###


### Exercise 7

Continue the pipe with `arrange()`, Within `arrange()`, add the argument `CheckoutYear`.

```{r opening-a-dataset-7, exercise = TRUE}

```

<button onclick = "transfer_code(this)">Copy previous code</button>

```{r opening-a-dataset-7-hint, eval = FALSE}
seattle_csv |> 
  count(CheckoutYear, wt = Checkouts) |> 
  arrange(...)
```

```{r, include=FALSE}
# seattle_csv |> 
#   count(CheckoutYear, wt = Checkouts) |> 
#   arrange(CheckoutYear)
```

###

We can start to use this dataset with dplyr verbs, using collect() to force arrow to perform the computation and return some data. For example, this next code will tell us the total number of checkouts per year.

### Exercise 8

Finish off the pipe with `collect()`.

```{r opening-a-dataset-8, exercise = TRUE}

```

<button onclick = "transfer_code(this)">Copy previous code</button>

```{r opening-a-dataset-8-hint, eval = FALSE}
... |> 
  collect()
```

```{r, include=FALSE}
# seattle_csv |> 
#   count(CheckoutYear, wt = Checkouts) |> 
#   arrange(CheckoutYear) |> 
#   collect()
```

###

Thanks to arrow, this code will work regardless of how large the underlying dataset is. But it’s currently rather slow: on Hadley’s computer, it took ~10s to run. That’s not terrible given how much data we have, but we can make it much faster by switching to a better format.

## The parquet format
##

To make this data easier to work with, lets switch to the parquet file format and split it up into multiple files. The following sections will first introduce you to parquet and partitioning, and then apply what we learned to the Seattle library data.

### Exercise 1

Set `pq_path` to `"data/seattle-library-checkouts"`

```{r the-parquet-format-1, exercise = TRUE}

```

```{r the-parquet-format-1-hint, eval = FALSE}
pq_path <- "..."
```

```{r, include=FALSE}
pq_path <- "data/seattle-library-checkouts"
```

###

Like CSV, parquet is used for rectangular data, but instead of being a text format that you can read with any file editor, it’s a custom binary format designed specifically for the needs of big data.

### Exercise 2

Start a pipe with `seattle_csv`.

```{r the-parquet-format-2, exercise = TRUE}

```

```{r the-parquet-format-2-hint, eval = FALSE}
seattle_csv
```

```{r,include=FALSE}
# seattle_csv
```

###

Parquet files are usually smaller the equivalent CSV file. Parquet relies on efficient encodings to keep file size down, and supports file compression. This helps make parquet files fast because there’s less data to move from disk to memory.

### Exercise 3

Continue the pipe with `group_by()`. Within `group_by()`, add the argument `CheckoutYear`.

```{r the-parquet-format-3, exercise = TRUE}

```

<button onclick = "transfer_code(this)">Copy previous code</button>

```{r the-parquet-format-3-hint, eval = FALSE}
seattle_csv |>
  group_by(...)
```

```{r, include=FALSE}
# seattle_csv |>
#   group_by(CheckoutYear)
```

###

Parquet files have a rich type system. As we talked about in Section 8.3, a CSV file does not provide any information about column types. For example, a CSV reader has to guess whether `"08-10-2022"` should be parsed as a string or a date. In contrast, parquet files store data in a way that records the type along with the data.

### Exercise 4

Continue the pipe with `write_dataset()`. Within `write_dataset()` add the argument `path = pq_path`.

```{r the-parquet-format-4, exercise = TRUE}

```

<button onclick = "transfer_code(this)">Copy previous code</button>

```{r the-parquet-format-4-hint, eval = FALSE}
seattle_csv |>
  group_by(CheckoutYear) |>
  write_dataset(...)
```

```{r, include=FALSE}
# seattle_csv |>
#   group_by(CheckoutYear) |>
#   write_dataset(path = pq_path)
```

###

Parquet files are “column-oriented”. This means that they’re organized column-by-column, much like R’s data frame. This typically leads to better performance for data analysis tasks compared to CSV files, which are organized row-by-row.

### Exercise 5

Copy previous code and add the argument `format = "parquet"` in `write_dataset()`. Make sure to separate the arguments with commas. This will take a minute but it pays off as it makes future operations much faster.

```{r the-parquet-format-5, exercise = TRUE}

```

<button onclick = "transfer_code(this)">Copy previous code</button>

```{r the-parquet-format-5-hint, eval = FALSE}
seattle_csv |>
  group_by(CheckoutYear) |>
  write_dataset(path = pq_path, format = "...")
```

###

Parquet files are “chunked”, which makes it possible to work on different parts of the file at the same time, and, if you’re lucky, to skip some chunks all together.

Let’s take a look at what we just produced:

### Exercise 6

Let’s take a look at what we just produced. Press "Run Code"

```{r the-parquet-format-6, exercise = TRUE}
tibble(
  files = list.files(pq_path, recursive = TRUE),
  size_MB = file.size(file.path(pq_path, files)) / 1024^2
)
```

###

Our single 9 GB CSV file has been rewritten into 18 parquet files. The file names use a “self-describing” convention used by the Apache Hive project. Hive-style partitions name folders with a “key=value” convention, so as you might guess, the `CheckoutYear=2005` directory contains all the data where `CheckoutYear` is `2005`. Each file is between 100 and 300 MB and the total size is now around 4 GB, a little over half the size of the original CSV file. This is as we expect since parquet is a much more efficient format.

## Using dplyr with arrow
###




### Exercise 1

Set `seattle_pq` to `open_dataset()` with the argument `pq_path` inside.

```{r using-dplyr-with-arr-1, exercise = TRUE}

```

```{r using-dplyr-with-arr-1-hint, eval = FALSE}
... <- open_dataset(...)
```

```{r, include=FALSE}
# seattle_pq <- open_dataset(pq_path)
```

###

As datasets get larger and larger, storing all the data in a single file gets increasingly painful and it’s often useful to split large datasets across many files. When this structuring is done intelligently, this strategy can lead to significant improvements in performance because many analyses will only require a subset of the files.

### Exercise 2

Start a pipe with `seattle_pq`

```{r using-dplyr-with-arr-2, exercise = TRUE}

```

```{r using-dplyr-with-arr-2-hint, eval = FALSE}
seattle_pq
```

###

There are no hard and fast rules about how to partition your dataset: the results will depend on your data, access patterns, and the systems that read the data. You’re likely to need to do some experimentation before you find the ideal partitioning for your situation.

### Exercise 3

Continue the pipe with `filter()`. Within `filter()`, add the argument `CheckoutYear >= 2018`.

```{r using-dplyr-with-arr-3, exercise = TRUE}

```

<button onclick = "transfer_code(this)">Copy previous code</button>

```{r using-dplyr-with-arr-3-hint, eval = FALSE}
seattle_pq |> 
  filter(CheckoutYear >= ...) 
```

###

As a rough guide, arrow suggests that you avoid files smaller than 20MB and larger than 2GB and avoid partitions that produce more than 10,000 files. You should also try to partition by variables that you filter by; as you’ll see shortly, that allows arrow to skip a lot of work by reading only the relevant files.

### Exercise 4

Copy previous code and add the argument `MaterialType == "BOOK"` within `filter()`. Make sure to separate the arguments with commas.


```{r using-dplyr-with-arr-4, exercise = TRUE}

```

<button onclick = "transfer_code(this)">Copy previous code</button>

```{r using-dplyr-with-arr-4-hint, eval = FALSE}
seattle_pq |> 
  filter(CheckoutYear >= 2018, MaterialType == "...")
```

###

Make sure to separate arguments within a function with a `,` or `&`. Otherwise, it will not work.

### Exercise 5

Continue the pipe with `group_by()`. Within `group_by()`, add the argument `CheckoutYear`.

```{r using-dplyr-with-arr-5, exercise = TRUE}

```

<button onclick = "transfer_code(this)">Copy previous code</button>

```{r using-dplyr-with-arr-5-hint, eval = FALSE}
seattle_pq |> 
  filter(CheckoutYear >= 2018, MaterialType == "BOOK") |>
  group_by(...)
```

###

This will group the data by checkout year so there is one row for every year.

### Exercise 6

Copy previous code and add the argument `CheckoutMonth` to `group_by()`.

```{r using-dplyr-with-arr-6, exercise = TRUE}

```

<button onclick = "transfer_code(this)">Copy previous code</button>

```{r using-dplyr-with-arr-6-hint, eval = FALSE}
seattle_pq |> 
  filter(CheckoutYear >= 2018, MaterialType == "BOOK") |>
  group_by(CheckoutYear, ...)
```

###

This will group the data so their is one row for every month in every year.

### Exercise 7

Continue the pipe with `summarize()`. Within `summarize()`, add `TotalCheckouts = sum(Checkouts)`

```{r using-dplyr-with-arr-7, exercise = TRUE}

```

<button onclick = "transfer_code(this)">Copy previous code</button>

```{r using-dplyr-with-arr-7-hint, eval = FALSE}
seattle_pq |> 
  filter(CheckoutYear >= 2018, MaterialType == "BOOK") |>
  group_by(CheckoutYear, CheckoutMonth) |>
  summarize(... = sum(...))
```

###

This will create a new column that is called `TotalCheckouts` that is the sum of checkouts for that month.

### Exercise 8

Continue the pipe with `arrange()`. Within `arrange()`, add the argument `CheckoutYear`.

```{r using-dplyr-with-arr-8, exercise = TRUE}

```

<button onclick = "transfer_code(this)">Copy previous code</button>

```{r using-dplyr-with-arr-8-hint, eval = FALSE}
seattle_pq |> 
  filter(CheckoutYear >= 2018, MaterialType == "BOOK") |>
  group_by(CheckoutYear, CheckoutMonth) |>
  summarize(TotalCheckouts = sum(Checkouts)) |>
  arrange(...)
```

###

This will arrange the data so that the highest checkout year is at the top of the tibble.

### Exercise 9

Copy previous code and add the argument `CheckoutMonth` to `arrange()`. Make sure to separate the arguments with commas.

```{r using-dplyr-with-arr-9, exercise = TRUE}

```

<button onclick = "transfer_code(this)">Copy previous code</button>

```{r using-dplyr-with-arr-9-hint, eval = FALSE}
seattle_pq |> 
  filter(CheckoutYear >= 2018, MaterialType == "BOOK") |>
  group_by(CheckoutYear, CheckoutMonth) |>
  summarize(TotalCheckouts = sum(Checkouts)) |>
  arrange(CheckoutYear, ...)
```

###

This will arrange the data so that the highest month in every year goes first in order throughout the tibble. This will make the tibble more organized and easier to read.

### Exercise 10

Set this whole code chunk to `query`

```{r using-dplyr-with-arr-10, exercise = TRUE}

```

<button onclick = "transfer_code(this)">Copy previous code</button>

```{r using-dplyr-with-arr-10-hint, eval = FALSE}
query <- seattle_pq |> 
  filter(CheckoutYear >= 2018, MaterialType == "BOOK") |>
  group_by(CheckoutYear, CheckoutMonth) |>
  summarize(TotalCheckouts = sum(Checkouts)) |>
  arrange(CheckoutYear, CheckoutMonth)
```

###

 for arrow data is conceptually similar to **dbplyr**, Chapter 22: you write **dbplyr** code, which is automatically transformed into a query that the Apache Arrow C++ library understands, which is then executed when you call `collect()`. If we print out the query object we can see a little information about what we expect Arrow to return when the execution takes place.

### Exercise 11

Type `query` and hit "Run Code".

```{r using-dplyr-with-arr-11, exercise = TRUE}

```

```{r using-dplyr-with-arr-11-hint, eval = FALSE}
query
```

###

Typing the name of the dataset name and running the code will simply output the dataset.

### Exercise 12

Copy previous code and pipe it into `collect()`.

```{r using-dplyr-with-arr-12, exercise = TRUE}

```

<button onclick = "transfer_code(this)">Copy previous code</button>

```{r using-dplyr-with-arr-12-hint, eval = FALSE}
query|>
  ...()
```

###

Like **dbplyr**, arrow only understands some R expressions, so you may not be able to write exactly the same code you usually would. However, the list of operations and functions supported is fairly extensive and continues to grow; find a complete list of currently supported functions in `?acero`.

### Exercise 13

Start a pipe with `seattle_csv`

```{r using-dplyr-with-arr-13, exercise = TRUE}

```

```{r using-dplyr-with-arr-13-hint, eval = FALSE}
seattle_csv
```

###

We are going to do the same thing as above but we will change a couple things about the tibble.

### Exercise 14

Continue the pipe with `filter()`. Within `filter()`, add the argument `CheckoutYear == 2021`

```{r using-dplyr-with-arr-14, exercise = TRUE}

```

<button onclick = "transfer_code(this)">Copy previous code</button>

```{r using-dplyr-with-arr-14-hint, eval = FALSE}
seattle_csv |> 
  filter(CheckoutYear == ...)
```

###

The last code had the `CheckoutYear` greater than or equal to `2018`. This one has it exactly at `2021` so all data will be within this year.

### Exercise 15

Copy previous code and add the argument `MaterialType = "BOOK"` to `filter()`

```{r using-dplyr-with-arr-15, exercise = TRUE}

```

<button onclick = "transfer_code(this)">Copy previous code</button>

```{r using-dplyr-with-arr-15-hint, eval = FALSE}
seattle_csv |> 
  filter(CheckoutYear == 2021, MaterialType == "...")
```

###

Setting the `MaterialType` to `BOOK` will give us rows where the material type is only book.

### Exercise 16

Continue the pipe with `group_by()`. Within `group_by()`, add the argument `CheckoutMonth`

```{r using-dplyr-with-arr-16, exercise = TRUE}

```

<button onclick = "transfer_code(this)">Copy previous code</button>

```{r using-dplyr-with-arr-16-hint, eval = FALSE}
seattle_csv |> 
  filter(CheckoutYear == 2021, MaterialType == "BOOK") |>
  group_by(...)
```

###

We can only `group_by()` the `CheckoutMonth` because we filtered this dataset to only have data from one year.

### Exercise 17

Continue the pipe with `summarize()`. Within `summarize()`, add `TotalCheckouts = sum(Checkouts)`

```{r using-dplyr-with-arr-17, exercise = TRUE}

```

<button onclick = "transfer_code(this)">Copy previous code</button>

```{r using-dplyr-with-arr-17-hint, eval = FALSE}
seattle_csv |> 
  filter(CheckoutYear == 2021, MaterialType == "BOOK") |>
  group_by(CheckoutMonth) |>
  summarize(... = sum(...))
```

###

The `sum()` function finds the sum of the argument within it.

### Exercise 18

Continue the pipe with `arrange()`. Within `arrange()`, add the argument `CheckoutMonth`.

```{r using-dplyr-with-arr-18, exercise = TRUE}

```

<button onclick = "transfer_code(this)">Copy previous code</button>

```{r using-dplyr-with-arr-18-hint, eval = FALSE}
seattle_csv |> 
  filter(CheckoutYear == 2021, MaterialType == "BOOK") |>
  group_by(CheckoutMonth) |>
  summarize(TotalCheckouts = sum(Checkouts)) |>
  arrange(...)
```

###

Again, we can only `arrange()` the `CheckoutMonth` because we filtered this dataset to only have data from one year.


### Exercise 19

Copy previous code and put `CheckoutMonth` within `desc()` within `arrange()`

```{r using-dplyr-with-arr-19, exercise = TRUE}

```

<button onclick = "transfer_code(this)">Copy previous code</button>

```{r using-dplyr-with-arr-19-hint, eval = FALSE}
seattle_csv |> 
  filter(CheckoutYear == 2021, MaterialType == "BOOK") |>
  group_by(CheckoutMonth) |>
  summarize(TotalCheckouts = sum(Checkouts)) |>
  arrange(...(CheckoutMonth))
```

###

When adding `desc()` within the `arrange()` function, the data is given to us in a descending order.

### Exercise 20

Continue the pipe with `collect()`

```{r using-dplyr-with-arr-20, exercise = TRUE}

```

<button onclick = "transfer_code(this)">Copy previous code</button>

```{r using-dplyr-with-arr-20-hint, eval = FALSE}
seattle_csv |> 
  filter(CheckoutYear == 2021, MaterialType == "BOOK") |>
  group_by(CheckoutMonth) |>
  summarize(TotalCheckouts = sum(Checkouts)) |>
  arrange(desc(CheckoutMonth)) |>
  ...()
```

###

The `collect()` function is used to convert the dataset into a standard data frame or tibble.

### Exercise 21

Finish off the pipe with `system.time()`

```{r using-dplyr-with-arr-21, exercise = TRUE}

```

<button onclick = "transfer_code(this)">Copy previous code</button>

```{r using-dplyr-with-arr-21-hint, eval = FALSE}
seattle_csv |> 
  filter(CheckoutYear == 2021, MaterialType == "BOOK") |>
  group_by(CheckoutMonth) |>
  summarize(TotalCheckouts = sum(Checkouts)) |>
  arrange(desc(CheckoutMonth)) |>
  collect() |> 
  ...()
```

###

This code calculates the number of books checked out in each month of 2021.

Now let’s use our new version of the dataset in which the Seattle library checkout data has been partitioned into 18 smaller parquet files.

### Exercise 22

Copy previous code and switch `seattle_csv` for `seattle_pq`

```{r using-dplyr-with-arr-22, exercise = TRUE}

```

<button onclick = "transfer_code(this)">Copy previous code</button>

```{r using-dplyr-with-arr-22-hint, eval = FALSE}

```

###

Partitioning improves performance because this query uses `CheckoutYear == 2021` to filter the data, and arrow is smart enough to recognize that it only needs to read 1 of the 18 parquet files.

### Exercise 23

Start a new pipe with `seattle_pq`

```{r using-dplyr-with-arr-23, exercise = TRUE}

```

```{r using-dplyr-with-arr-23-hint, eval = FALSE}
seattle_pq
```

###

The parquet format improves performance by storing data in a binary format that can be read more directly into memory. The column-wise format and rich metadata means that arrow only needs to read the four columns actually used in the query (`CheckoutYear`, `MaterialType`, `CheckoutMonth`, and `Checkouts`).

### Exercise 24

Continue the pipe with `to_duckdb()`

```{r using-dplyr-with-arr-24, exercise = TRUE}

```

<button onclick = "transfer_code(this)">Copy previous code</button>

```{r using-dplyr-with-arr-24-hint, eval = FALSE}
seattle_pq |> 
  ...()
```

###

The ~100x speedup in performance is attributable to two factors: the multi-file partitioning, and the format of individual files. This massive difference in performance is why it pays off to convert large CSVs to parquet!

### Exercise 25

Continue the pipe with `filter()`. Within `filter()`, add the arguments `CheckoutYear >= 2018` and `MaterialType == "BOOK"`. Make sure to separate the arguments with commas.

```{r using-dplyr-with-arr-25, exercise = TRUE}

```

<button onclick = "transfer_code(this)">Copy previous code</button>

```{r using-dplyr-with-arr-25-hint, eval = FALSE}
seattle_pq |> 
  to_duckdb() |>
  filter(... >= 2018, MaterialType == "...")
```

###

Another advantage of parquet and arrow is it’s very easy to turn an arrow dataset into a DuckDB database by calling `arrow::to_duckdb()`.

### Exercise 26

Continue the pipe with `group_by()`. Within `group_by()`, add the argument `CheckoutYear`.

```{r using-dplyr-with-arr-26, exercise = TRUE}

```

<button onclick = "transfer_code(this)">Copy previous code</button>

```{r using-dplyr-with-arr-26-hint, eval = FALSE}
seattle_pq |> 
  to_duckdb() |>
  filter(CheckoutYear >= 2018, MaterialType == "BOOK") |>
  group_by(...)
```

###

You’ve been given a taste of the arrow package, which provides a **dplyr** backend for working with large on-disk datasets. It can work with CSV files, its much much faster if you convert your data to parquet.

### Exercise 27

Continue the pipe with `summarize()`. Within `summarize()`, add `TotalCheckouts = sum(Checkouts)`

```{r using-dplyr-with-arr-27, exercise = TRUE}

```

<button onclick = "transfer_code(this)">Copy previous code</button>

```{r using-dplyr-with-arr-27-hint, eval = FALSE}
seattle_pq |> 
  to_duckdb() |>
  filter(CheckoutYear >= 2018, MaterialType == "BOOK") |>
  group_by(CheckoutYear) |>
  summarize(... = sum(...))
```

###

Parquet is a binary data format that’s designed specifically for data analysis on modern computers. Far fewer tools can work with parquet files compared to CSV, but it’s partitioned, compressed, and columnar structure makes it much more efficient to analyze.

### Exercise 28

Continue the pipe with `arrange()`. Within `arrange()`, add the argument `desc(CheckoutYear)`.

```{r using-dplyr-with-arr-28, exercise = TRUE}

```

<button onclick = "transfer_code(this)">Copy previous code</button>

```{r using-dplyr-with-arr-28-hint, eval = FALSE}
seattle_pq |> 
  to_duckdb() |>
  filter(CheckoutYear >= 2018, MaterialType == "BOOK") |>
  group_by(CheckoutYear) |>
  summarize(TotalCheckouts = sum(Checkouts)) |>
  ...(desc(...))
```

###

Using `desc()` within `arrange()` will give you the argument in the tibble in descending order instead of ascending. This way, you ccan see the values from greatest to least.

### Exercise 29

Continue the pipe with `collect()`.

```{r using-dplyr-with-arr-29, exercise = TRUE}

```

<button onclick = "transfer_code(this)">Copy previous code</button>

```{r using-dplyr-with-arr-29-hint, eval = FALSE}
seattle_pq |> 
  to_duckdb() |>
  filter(CheckoutYear >= 2018, MaterialType == "BOOK") |>
  group_by(CheckoutYear) |>
  summarize(TotalCheckouts = sum(Checkouts)) |>
  arrange(desc(CheckoutYear)) |>
  collect()
```

###

The neat thing about `to_duckdb()` is that the transfer doesn’t involve any memory copying, and speaks to the goals of the arrow ecosystem: enabling seamless transitions from one computing environment to another.

## Cleaning up

### Exercise 1

Now, we need to remove that 9 GB dataset. Press "Run Code".

```{r cleaning-up-1, exercise = TRUE}
file.remove("data/seattle-library-checkouts.csv")
```


###



## Summary
### 

This tutorial covered [Chapter 22: Arrow](https://r4ds.hadley.nz/arrow.html) from [*R for Data Science (2e)*](https://r4ds.hadley.nz/) by Hadley Wickham, Mine Çetinkaya-Rundel, and Garrett Grolemund. 

 

```{r download-answers, child = system.file("child_documents/download_answers.Rmd", package = "tutorial.helpers")}
```
