Advanced Data Wrangling and Analysis

# Advanced Data Wrangling and Analysis
## Going Deeper with R

---

layout: true
  
<div class="dk-footer">
<span>
<a href="https://rfortherestofus.com/" target="_blank">R for the Rest of Us
</a>
</span>
</div>

---

background-image:url("images/data.jpg")

# Importing Data

---

## Downloading Data Directly

The `download.file()` function lets us download files directly from websites.

```r
download.file(url, destfile = "data-raw/name-of-file.xlsx")
```

???

Benefit = reproducibility

---

## Importing Excel Files

---

## Importing Excel Files

```r
data_frame <- read_excel(path = "directory_name/file_name.xlsx",
                         sheet = "name of sheet")
```

---

## Importing Excel Files

```r
german_speakers <- read_excel(path = "data-raw/german-and-french-speakers.xlsx",
                              sheet = "German speakers") %>% 
  clean_names()
```

<div data-pagedtable="false">
  <script data-pagedtable-source type="application/json">
{"columns":[{"label":["state"],"name":[1],"type":["chr"],"align":["left"]},{"label":["number_of_german_speakers_2017"],"name":[2],"type":["chr"],"align":["left"]},{"label":["number_of_german_speakers_2018"],"name":[3],"type":["chr"],"align":["left"]},{"label":["number_of_german_speakers_2019"],"name":[4],"type":["chr"],"align":["left"]}],"data":[{"1":"Alabama","2":"426","3":"395","4":"711"},{"1":"Alaska","2":"331","3":"201","4":"131"},{"1":"Arizona","2":"636","3":"858","4":"136"},{"1":"Arkansas","2":"-","3":"635","4":"557"},{"1":"California","2":"440","3":"318","4":"854"},{"1":"Colorado","2":"251","3":"498","4":"768"},{"1":"Connecticut","2":"187","3":"334","4":"586"},{"1":"Delaware","2":"-","3":"998","4":"453"},{"1":"District of Columbia","2":"799","3":"745","4":"180"},{"1":"Florida","2":"399","3":"924","4":"958"},{"1":"Georgia","2":"910","3":"293","4":"821"},{"1":"Hawaii","2":"-","3":"303","4":"931"},{"1":"Idaho","2":"301","3":"349","4":"-"},{"1":"Illinois","2":"156","3":"573","4":"827"},{"1":"Indiana","2":"469","3":"188","4":"728"},{"1":"Iowa","2":"816","3":"330","4":"657"},{"1":"Kansas","2":"328","3":"546","4":"501"},{"1":"Kentucky","2":"299","3":"976","4":"332"},{"1":"Louisiana","2":"266","3":"-","4":"490"},{"1":"Maine","2":"376","3":"127","4":"206"},{"1":"Montana","2":"598","3":"296","4":"527"},{"1":"Nebraska","2":"252","3":"726","4":"232"},{"1":"Nevada","2":"910","3":"526","4":"647"},{"1":"New Hampshire","2":"896","3":"109","4":"818"},{"1":"New Jersey","2":"380","3":"961","4":"168"},{"1":"New Mexico","2":"574","3":"771","4":"336"},{"1":"New York","2":"135","3":"637","4":"207"},{"1":"North Carolina","2":"609","3":"970","4":"508"},{"1":"North Dakota","2":"891","3":"307","4":"652"},{"1":"Ohio","2":"960","3":"900","4":"186"},{"1":"Oklahoma","2":"769","3":"699","4":"621"},{"1":"Oregon","2":"864","3":"762","4":"471"},{"1":"Maryland","2":"247","3":"106","4":"507"},{"1":"Massachusetts","2":"775","3":"507","4":"859"},{"1":"Michigan","2":"776","3":"503","4":"467"},{"1":"Minnesota","2":"752","3":"344","4":"251"},{"1":"Mississippi","2":"945","3":"246","4":"372"},{"1":"Missouri","2":"699","3":"250","4":"972"},{"1":"Pennsylvania","2":"930","3":"475","4":"956"},{"1":"Rhode Island","2":"529","3":"852","4":"900"},{"1":"South Carolina","2":"617","3":"815","4":"182"},{"1":"South Dakota","2":"262","3":"601","4":"584"},{"1":"Tennessee","2":"153","3":"568","4":"476"},{"1":"Texas","2":"732","3":"571","4":"292"},{"1":"Utah","2":"669","3":"486","4":"758"},{"1":"Vermont","2":"698","3":"470","4":"158"},{"1":"Virginia","2":"125","3":"105","4":"432"},{"1":"Washington","2":"495","3":"474","4":"615"},{"1":"West Virginia","2":"603","3":"600","4":"974"},{"1":"Wisconsin","2":"582","3":"997","4":"842"},{"1":"Wyoming","2":"451","3":"593","4":"431"}],"options":{"columns":{"min":{},"max":[10]},"rows":{"min":[5],"max":[5]},"pages":{}}}
  </script>
</div>

---

## Other Packages for Importing Data

---

## Other Packages for Importing Data

.pull-left[
The [rio package](https://github.com/leeper/rio) makes it as simple as possible to import and export data.

You give it the location of the data file in the `import()` function and it auto-detects the file type. 
]

---

## My Turn

Throughout, I'll be working with [data on third grade math proficiency in Oregon schools](https://www.oregon.gov/ode/educator-resources/assessment/Pages/Assessment-Group-Reports.aspx).

I'll do the following:

1. Create a new project

1. Create a new R script file where I'll do all of my data cleaning work

1. Download math proficiency scores from 2017-2018 and 2018-2019 and put them in a data-raw folder

1. Import the two spreadsheets into two data frames (`math_scores_17_18` and `math_scores_18_19`)

---

## Your Turn

You'll be working with data on [Oregon school enrollment by race/ethnicity](https://www.oregon.gov/ode/reports-and-data/students/Pages/Student-Enrollment-Reports.aspx).

1. Create a new project. Make sure you put it somehwere you'll be able to find it again later!

1. Download the two files (links below) using the download.file() function into a data-raw folder (which you'll need to create)

1. Create a new R script file where you'll do all of your data cleaning work

1. Import the two spreadsheets into two data frames (`enrollment_17_18` and `enrollment_18_19`)

---

background-image:url("images/berries.jpg")

# Tidy Data

---

![](images/tidy-workflow.png)

---

## Untidy Data

<div data-pagedtable="false">
  <script data-pagedtable-source type="application/json">
{"columns":[{"label":["state"],"name":[1],"type":["chr"],"align":["left"]},{"label":["number_of_german_speakers_2017"],"name":[2],"type":["dbl"],"align":["right"]},{"label":["number_of_german_speakers_2018"],"name":[3],"type":["dbl"],"align":["right"]},{"label":["number_of_german_speakers_2019"],"name":[4],"type":["dbl"],"align":["right"]}],"data":[{"1":"Alabama","2":"426","3":"395","4":"711"},{"1":"Alaska","2":"331","3":"201","4":"131"},{"1":"Arizona","2":"636","3":"858","4":"136"},{"1":"Arkansas","2":"NA","3":"635","4":"557"},{"1":"California","2":"440","3":"318","4":"854"},{"1":"Colorado","2":"251","3":"498","4":"768"},{"1":"Connecticut","2":"187","3":"334","4":"586"},{"1":"Delaware","2":"NA","3":"998","4":"453"},{"1":"District of Columbia","2":"799","3":"745","4":"180"},{"1":"Florida","2":"399","3":"924","4":"958"},{"1":"Georgia","2":"910","3":"293","4":"821"},{"1":"Hawaii","2":"NA","3":"303","4":"931"},{"1":"Idaho","2":"301","3":"349","4":"NA"},{"1":"Illinois","2":"156","3":"573","4":"827"},{"1":"Indiana","2":"469","3":"188","4":"728"},{"1":"Iowa","2":"816","3":"330","4":"657"},{"1":"Kansas","2":"328","3":"546","4":"501"},{"1":"Kentucky","2":"299","3":"976","4":"332"},{"1":"Louisiana","2":"266","3":"NA","4":"490"},{"1":"Maine","2":"376","3":"127","4":"206"},{"1":"Montana","2":"598","3":"296","4":"527"},{"1":"Nebraska","2":"252","3":"726","4":"232"},{"1":"Nevada","2":"910","3":"526","4":"647"},{"1":"New Hampshire","2":"896","3":"109","4":"818"},{"1":"New Jersey","2":"380","3":"961","4":"168"},{"1":"New Mexico","2":"574","3":"771","4":"336"},{"1":"New York","2":"135","3":"637","4":"207"},{"1":"North Carolina","2":"609","3":"970","4":"508"},{"1":"North Dakota","2":"891","3":"307","4":"652"},{"1":"Ohio","2":"960","3":"900","4":"186"},{"1":"Oklahoma","2":"769","3":"699","4":"621"},{"1":"Oregon","2":"864","3":"762","4":"471"},{"1":"Maryland","2":"247","3":"106","4":"507"},{"1":"Massachusetts","2":"775","3":"507","4":"859"},{"1":"Michigan","2":"776","3":"503","4":"467"},{"1":"Minnesota","2":"752","3":"344","4":"251"},{"1":"Mississippi","2":"945","3":"246","4":"372"},{"1":"Missouri","2":"699","3":"250","4":"972"},{"1":"Pennsylvania","2":"930","3":"475","4":"956"},{"1":"Rhode Island","2":"529","3":"852","4":"900"},{"1":"South Carolina","2":"617","3":"815","4":"182"},{"1":"South Dakota","2":"262","3":"601","4":"584"},{"1":"Tennessee","2":"153","3":"568","4":"476"},{"1":"Texas","2":"732","3":"571","4":"292"},{"1":"Utah","2":"669","3":"486","4":"758"},{"1":"Vermont","2":"698","3":"470","4":"158"},{"1":"Virginia","2":"125","3":"105","4":"432"},{"1":"Washington","2":"495","3":"474","4":"615"},{"1":"West Virginia","2":"603","3":"600","4":"974"},{"1":"Wisconsin","2":"582","3":"997","4":"842"},{"1":"Wyoming","2":"451","3":"593","4":"431"}],"options":{"columns":{"min":{},"max":[10]},"rows":{"min":[5],"max":[5]},"pages":{}}}
  </script>
</div>

---

## Untidy Data

```r
german_speakers_numeric %>% 
  mutate(total = number_of_german_speakers_2017 + number_of_german_speakers_2018 + number_of_german_speakers_2019) %>% 
  select(state, total)
```

<div data-pagedtable="false">
  <script data-pagedtable-source type="application/json">
{"columns":[{"label":["state"],"name":[1],"type":["chr"],"align":["left"]},{"label":["total"],"name":[2],"type":["dbl"],"align":["right"]}],"data":[{"1":"Alabama","2":"1532"},{"1":"Alaska","2":"663"},{"1":"Arizona","2":"1630"},{"1":"Arkansas","2":"NA"},{"1":"California","2":"1612"},{"1":"Colorado","2":"1517"},{"1":"Connecticut","2":"1107"},{"1":"Delaware","2":"NA"},{"1":"District of Columbia","2":"1724"},{"1":"Florida","2":"2281"},{"1":"Georgia","2":"2024"},{"1":"Hawaii","2":"NA"},{"1":"Idaho","2":"NA"},{"1":"Illinois","2":"1556"},{"1":"Indiana","2":"1385"},{"1":"Iowa","2":"1803"},{"1":"Kansas","2":"1375"},{"1":"Kentucky","2":"1607"},{"1":"Louisiana","2":"NA"},{"1":"Maine","2":"709"},{"1":"Montana","2":"1421"},{"1":"Nebraska","2":"1210"},{"1":"Nevada","2":"2083"},{"1":"New Hampshire","2":"1823"},{"1":"New Jersey","2":"1509"},{"1":"New Mexico","2":"1681"},{"1":"New York","2":"979"},{"1":"North Carolina","2":"2087"},{"1":"North Dakota","2":"1850"},{"1":"Ohio","2":"2046"},{"1":"Oklahoma","2":"2089"},{"1":"Oregon","2":"2097"},{"1":"Maryland","2":"860"},{"1":"Massachusetts","2":"2141"},{"1":"Michigan","2":"1746"},{"1":"Minnesota","2":"1347"},{"1":"Mississippi","2":"1563"},{"1":"Missouri","2":"1921"},{"1":"Pennsylvania","2":"2361"},{"1":"Rhode Island","2":"2281"},{"1":"South Carolina","2":"1614"},{"1":"South Dakota","2":"1447"},{"1":"Tennessee","2":"1197"},{"1":"Texas","2":"1595"},{"1":"Utah","2":"1913"},{"1":"Vermont","2":"1326"},{"1":"Virginia","2":"662"},{"1":"Washington","2":"1584"},{"1":"West Virginia","2":"2177"},{"1":"Wisconsin","2":"2421"},{"1":"Wyoming","2":"1475"}],"options":{"columns":{"min":{},"max":[10]},"rows":{"min":[5],"max":[5]},"pages":{}}}
  </script>
</div>

???

But what if we have 10 or 20 years of data? Are we going to write out a all of those years individually?

---

## Tidy Data

<div data-pagedtable="false">
  <script data-pagedtable-source type="application/json">
{"columns":[{"label":["state"],"name":[1],"type":["chr"],"align":["left"]},{"label":["year"],"name":[2],"type":["chr"],"align":["left"]},{"label":["number"],"name":[3],"type":["dbl"],"align":["right"]}],"data":[{"1":"Alabama","2":"number_of_german_speakers_2017","3":"426"},{"1":"Alabama","2":"number_of_german_speakers_2018","3":"395"},{"1":"Alabama","2":"number_of_german_speakers_2019","3":"711"},{"1":"Alaska","2":"number_of_german_speakers_2017","3":"331"},{"1":"Alaska","2":"number_of_german_speakers_2018","3":"201"},{"1":"Alaska","2":"number_of_german_speakers_2019","3":"131"},{"1":"Arizona","2":"number_of_german_speakers_2017","3":"636"},{"1":"Arizona","2":"number_of_german_speakers_2018","3":"858"},{"1":"Arizona","2":"number_of_german_speakers_2019","3":"136"},{"1":"Arkansas","2":"number_of_german_speakers_2017","3":"NA"},{"1":"Arkansas","2":"number_of_german_speakers_2018","3":"635"},{"1":"Arkansas","2":"number_of_german_speakers_2019","3":"557"},{"1":"California","2":"number_of_german_speakers_2017","3":"440"},{"1":"California","2":"number_of_german_speakers_2018","3":"318"},{"1":"California","2":"number_of_german_speakers_2019","3":"854"},{"1":"Colorado","2":"number_of_german_speakers_2017","3":"251"},{"1":"Colorado","2":"number_of_german_speakers_2018","3":"498"},{"1":"Colorado","2":"number_of_german_speakers_2019","3":"768"},{"1":"Connecticut","2":"number_of_german_speakers_2017","3":"187"},{"1":"Connecticut","2":"number_of_german_speakers_2018","3":"334"},{"1":"Connecticut","2":"number_of_german_speakers_2019","3":"586"},{"1":"Delaware","2":"number_of_german_speakers_2017","3":"NA"},{"1":"Delaware","2":"number_of_german_speakers_2018","3":"998"},{"1":"Delaware","2":"number_of_german_speakers_2019","3":"453"},{"1":"District of Columbia","2":"number_of_german_speakers_2017","3":"799"},{"1":"District of Columbia","2":"number_of_german_speakers_2018","3":"745"},{"1":"District of Columbia","2":"number_of_german_speakers_2019","3":"180"},{"1":"Florida","2":"number_of_german_speakers_2017","3":"399"},{"1":"Florida","2":"number_of_german_speakers_2018","3":"924"},{"1":"Florida","2":"number_of_german_speakers_2019","3":"958"},{"1":"Georgia","2":"number_of_german_speakers_2017","3":"910"},{"1":"Georgia","2":"number_of_german_speakers_2018","3":"293"},{"1":"Georgia","2":"number_of_german_speakers_2019","3":"821"},{"1":"Hawaii","2":"number_of_german_speakers_2017","3":"NA"},{"1":"Hawaii","2":"number_of_german_speakers_2018","3":"303"},{"1":"Hawaii","2":"number_of_german_speakers_2019","3":"931"},{"1":"Idaho","2":"number_of_german_speakers_2017","3":"301"},{"1":"Idaho","2":"number_of_german_speakers_2018","3":"349"},{"1":"Idaho","2":"number_of_german_speakers_2019","3":"NA"},{"1":"Illinois","2":"number_of_german_speakers_2017","3":"156"},{"1":"Illinois","2":"number_of_german_speakers_2018","3":"573"},{"1":"Illinois","2":"number_of_german_speakers_2019","3":"827"},{"1":"Indiana","2":"number_of_german_speakers_2017","3":"469"},{"1":"Indiana","2":"number_of_german_speakers_2018","3":"188"},{"1":"Indiana","2":"number_of_german_speakers_2019","3":"728"},{"1":"Iowa","2":"number_of_german_speakers_2017","3":"816"},{"1":"Iowa","2":"number_of_german_speakers_2018","3":"330"},{"1":"Iowa","2":"number_of_german_speakers_2019","3":"657"},{"1":"Kansas","2":"number_of_german_speakers_2017","3":"328"},{"1":"Kansas","2":"number_of_german_speakers_2018","3":"546"},{"1":"Kansas","2":"number_of_german_speakers_2019","3":"501"},{"1":"Kentucky","2":"number_of_german_speakers_2017","3":"299"},{"1":"Kentucky","2":"number_of_german_speakers_2018","3":"976"},{"1":"Kentucky","2":"number_of_german_speakers_2019","3":"332"},{"1":"Louisiana","2":"number_of_german_speakers_2017","3":"266"},{"1":"Louisiana","2":"number_of_german_speakers_2018","3":"NA"},{"1":"Louisiana","2":"number_of_german_speakers_2019","3":"490"},{"1":"Maine","2":"number_of_german_speakers_2017","3":"376"},{"1":"Maine","2":"number_of_german_speakers_2018","3":"127"},{"1":"Maine","2":"number_of_german_speakers_2019","3":"206"},{"1":"Montana","2":"number_of_german_speakers_2017","3":"598"},{"1":"Montana","2":"number_of_german_speakers_2018","3":"296"},{"1":"Montana","2":"number_of_german_speakers_2019","3":"527"},{"1":"Nebraska","2":"number_of_german_speakers_2017","3":"252"},{"1":"Nebraska","2":"number_of_german_speakers_2018","3":"726"},{"1":"Nebraska","2":"number_of_german_speakers_2019","3":"232"},{"1":"Nevada","2":"number_of_german_speakers_2017","3":"910"},{"1":"Nevada","2":"number_of_german_speakers_2018","3":"526"},{"1":"Nevada","2":"number_of_german_speakers_2019","3":"647"},{"1":"New Hampshire","2":"number_of_german_speakers_2017","3":"896"},{"1":"New Hampshire","2":"number_of_german_speakers_2018","3":"109"},{"1":"New Hampshire","2":"number_of_german_speakers_2019","3":"818"},{"1":"New Jersey","2":"number_of_german_speakers_2017","3":"380"},{"1":"New Jersey","2":"number_of_german_speakers_2018","3":"961"},{"1":"New Jersey","2":"number_of_german_speakers_2019","3":"168"},{"1":"New Mexico","2":"number_of_german_speakers_2017","3":"574"},{"1":"New Mexico","2":"number_of_german_speakers_2018","3":"771"},{"1":"New Mexico","2":"number_of_german_speakers_2019","3":"336"},{"1":"New York","2":"number_of_german_speakers_2017","3":"135"},{"1":"New York","2":"number_of_german_speakers_2018","3":"637"},{"1":"New York","2":"number_of_german_speakers_2019","3":"207"},{"1":"North Carolina","2":"number_of_german_speakers_2017","3":"609"},{"1":"North Carolina","2":"number_of_german_speakers_2018","3":"970"},{"1":"North Carolina","2":"number_of_german_speakers_2019","3":"508"},{"1":"North Dakota","2":"number_of_german_speakers_2017","3":"891"},{"1":"North Dakota","2":"number_of_german_speakers_2018","3":"307"},{"1":"North Dakota","2":"number_of_german_speakers_2019","3":"652"},{"1":"Ohio","2":"number_of_german_speakers_2017","3":"960"},{"1":"Ohio","2":"number_of_german_speakers_2018","3":"900"},{"1":"Ohio","2":"number_of_german_speakers_2019","3":"186"},{"1":"Oklahoma","2":"number_of_german_speakers_2017","3":"769"},{"1":"Oklahoma","2":"number_of_german_speakers_2018","3":"699"},{"1":"Oklahoma","2":"number_of_german_speakers_2019","3":"621"},{"1":"Oregon","2":"number_of_german_speakers_2017","3":"864"},{"1":"Oregon","2":"number_of_german_speakers_2018","3":"762"},{"1":"Oregon","2":"number_of_german_speakers_2019","3":"471"},{"1":"Maryland","2":"number_of_german_speakers_2017","3":"247"},{"1":"Maryland","2":"number_of_german_speakers_2018","3":"106"},{"1":"Maryland","2":"number_of_german_speakers_2019","3":"507"},{"1":"Massachusetts","2":"number_of_german_speakers_2017","3":"775"},{"1":"Massachusetts","2":"number_of_german_speakers_2018","3":"507"},{"1":"Massachusetts","2":"number_of_german_speakers_2019","3":"859"},{"1":"Michigan","2":"number_of_german_speakers_2017","3":"776"},{"1":"Michigan","2":"number_of_german_speakers_2018","3":"503"},{"1":"Michigan","2":"number_of_german_speakers_2019","3":"467"},{"1":"Minnesota","2":"number_of_german_speakers_2017","3":"752"},{"1":"Minnesota","2":"number_of_german_speakers_2018","3":"344"},{"1":"Minnesota","2":"number_of_german_speakers_2019","3":"251"},{"1":"Mississippi","2":"number_of_german_speakers_2017","3":"945"},{"1":"Mississippi","2":"number_of_german_speakers_2018","3":"246"},{"1":"Mississippi","2":"number_of_german_speakers_2019","3":"372"},{"1":"Missouri","2":"number_of_german_speakers_2017","3":"699"},{"1":"Missouri","2":"number_of_german_speakers_2018","3":"250"},{"1":"Missouri","2":"number_of_german_speakers_2019","3":"972"},{"1":"Pennsylvania","2":"number_of_german_speakers_2017","3":"930"},{"1":"Pennsylvania","2":"number_of_german_speakers_2018","3":"475"},{"1":"Pennsylvania","2":"number_of_german_speakers_2019","3":"956"},{"1":"Rhode Island","2":"number_of_german_speakers_2017","3":"529"},{"1":"Rhode Island","2":"number_of_german_speakers_2018","3":"852"},{"1":"Rhode Island","2":"number_of_german_speakers_2019","3":"900"},{"1":"South Carolina","2":"number_of_german_speakers_2017","3":"617"},{"1":"South Carolina","2":"number_of_german_speakers_2018","3":"815"},{"1":"South Carolina","2":"number_of_german_speakers_2019","3":"182"},{"1":"South Dakota","2":"number_of_german_speakers_2017","3":"262"},{"1":"South Dakota","2":"number_of_german_speakers_2018","3":"601"},{"1":"South Dakota","2":"number_of_german_speakers_2019","3":"584"},{"1":"Tennessee","2":"number_of_german_speakers_2017","3":"153"},{"1":"Tennessee","2":"number_of_german_speakers_2018","3":"568"},{"1":"Tennessee","2":"number_of_german_speakers_2019","3":"476"},{"1":"Texas","2":"number_of_german_speakers_2017","3":"732"},{"1":"Texas","2":"number_of_german_speakers_2018","3":"571"},{"1":"Texas","2":"number_of_german_speakers_2019","3":"292"},{"1":"Utah","2":"number_of_german_speakers_2017","3":"669"},{"1":"Utah","2":"number_of_german_speakers_2018","3":"486"},{"1":"Utah","2":"number_of_german_speakers_2019","3":"758"},{"1":"Vermont","2":"number_of_german_speakers_2017","3":"698"},{"1":"Vermont","2":"number_of_german_speakers_2018","3":"470"},{"1":"Vermont","2":"number_of_german_speakers_2019","3":"158"},{"1":"Virginia","2":"number_of_german_speakers_2017","3":"125"},{"1":"Virginia","2":"number_of_german_speakers_2018","3":"105"},{"1":"Virginia","2":"number_of_german_speakers_2019","3":"432"},{"1":"Washington","2":"number_of_german_speakers_2017","3":"495"},{"1":"Washington","2":"number_of_german_speakers_2018","3":"474"},{"1":"Washington","2":"number_of_german_speakers_2019","3":"615"},{"1":"West Virginia","2":"number_of_german_speakers_2017","3":"603"},{"1":"West Virginia","2":"number_of_german_speakers_2018","3":"600"},{"1":"West Virginia","2":"number_of_german_speakers_2019","3":"974"},{"1":"Wisconsin","2":"number_of_german_speakers_2017","3":"582"},{"1":"Wisconsin","2":"number_of_german_speakers_2018","3":"997"},{"1":"Wisconsin","2":"number_of_german_speakers_2019","3":"842"},{"1":"Wyoming","2":"number_of_german_speakers_2017","3":"451"},{"1":"Wyoming","2":"number_of_german_speakers_2018","3":"593"},{"1":"Wyoming","2":"number_of_german_speakers_2019","3":"431"}],"options":{"columns":{"min":{},"max":[10]},"rows":{"min":[5],"max":[5]},"pages":{}}}
  </script>
</div>

---

## Tidy Data

```r
german_speakers_tidy %>% 
  group_by(state) %>% 
  summarize(total = sum(number, na.rm = TRUE))
```

???

The tidyverse wants data in a certain format: tidy. With data in this format, it becomes much easier to work with.

---

## The Three Rules of Tidy Data

1. Each variable forms a column.

2. Each observation forms a row.

3. Each type of observational unit forms a table.

*We'll focus on #1 and #3. To read about examples of untidy data, check out the [Tidy Data vignette](https://tidyr.tidyverse.org/articles/tidy-data.html).*

---

## My Turn

Let's take a look at my data and see which principles of tidy data it violates

---

## Your Turn

1. Read the [Tidy Data vignette](https://tidyr.tidyverse.org/articles/tidy-data.html)

1. Take a look at your data and see which principles of tidy data it violates

---

background-image:url("images/shape.jpg")

# Reshaping Data

---

## Reshaping Data: `pivot_longer()`

The `pivot_longer()` function helps us in situations where **column headers are values, not variable names**.

```r
data_frame %>% 
* pivot_longer(cols = columns_to_use)
```

```r
data_frame %>% 
  pivot_longer(cols = columns_to_use,
*              names_to = "name_of_identifer_variable")
```

```r
data_frame %>% 
  pivot_longer(cols = columns_to_use,
               names_to = "name_of_identifer_variable",
*              values_to = "name_of_value_variable")
```

---

## Reshaping Data: `pivot_longer()`

<div data-pagedtable="false">
  <script data-pagedtable-source type="application/json">
{"columns":[{"label":["state"],"name":[1],"type":["chr"],"align":["left"]},{"label":["number_of_german_speakers_2017"],"name":[2],"type":["chr"],"align":["left"]},{"label":["number_of_german_speakers_2018"],"name":[3],"type":["chr"],"align":["left"]},{"label":["number_of_german_speakers_2019"],"name":[4],"type":["chr"],"align":["left"]}],"data":[{"1":"Alabama","2":"426","3":"395","4":"711"},{"1":"Alaska","2":"331","3":"201","4":"131"},{"1":"Arizona","2":"636","3":"858","4":"136"},{"1":"Arkansas","2":"-","3":"635","4":"557"},{"1":"California","2":"440","3":"318","4":"854"},{"1":"Colorado","2":"251","3":"498","4":"768"},{"1":"Connecticut","2":"187","3":"334","4":"586"},{"1":"Delaware","2":"-","3":"998","4":"453"},{"1":"District of Columbia","2":"799","3":"745","4":"180"},{"1":"Florida","2":"399","3":"924","4":"958"},{"1":"Georgia","2":"910","3":"293","4":"821"},{"1":"Hawaii","2":"-","3":"303","4":"931"},{"1":"Idaho","2":"301","3":"349","4":"-"},{"1":"Illinois","2":"156","3":"573","4":"827"},{"1":"Indiana","2":"469","3":"188","4":"728"},{"1":"Iowa","2":"816","3":"330","4":"657"},{"1":"Kansas","2":"328","3":"546","4":"501"},{"1":"Kentucky","2":"299","3":"976","4":"332"},{"1":"Louisiana","2":"266","3":"-","4":"490"},{"1":"Maine","2":"376","3":"127","4":"206"},{"1":"Montana","2":"598","3":"296","4":"527"},{"1":"Nebraska","2":"252","3":"726","4":"232"},{"1":"Nevada","2":"910","3":"526","4":"647"},{"1":"New Hampshire","2":"896","3":"109","4":"818"},{"1":"New Jersey","2":"380","3":"961","4":"168"},{"1":"New Mexico","2":"574","3":"771","4":"336"},{"1":"New York","2":"135","3":"637","4":"207"},{"1":"North Carolina","2":"609","3":"970","4":"508"},{"1":"North Dakota","2":"891","3":"307","4":"652"},{"1":"Ohio","2":"960","3":"900","4":"186"},{"1":"Oklahoma","2":"769","3":"699","4":"621"},{"1":"Oregon","2":"864","3":"762","4":"471"},{"1":"Maryland","2":"247","3":"106","4":"507"},{"1":"Massachusetts","2":"775","3":"507","4":"859"},{"1":"Michigan","2":"776","3":"503","4":"467"},{"1":"Minnesota","2":"752","3":"344","4":"251"},{"1":"Mississippi","2":"945","3":"246","4":"372"},{"1":"Missouri","2":"699","3":"250","4":"972"},{"1":"Pennsylvania","2":"930","3":"475","4":"956"},{"1":"Rhode Island","2":"529","3":"852","4":"900"},{"1":"South Carolina","2":"617","3":"815","4":"182"},{"1":"South Dakota","2":"262","3":"601","4":"584"},{"1":"Tennessee","2":"153","3":"568","4":"476"},{"1":"Texas","2":"732","3":"571","4":"292"},{"1":"Utah","2":"669","3":"486","4":"758"},{"1":"Vermont","2":"698","3":"470","4":"158"},{"1":"Virginia","2":"125","3":"105","4":"432"},{"1":"Washington","2":"495","3":"474","4":"615"},{"1":"West Virginia","2":"603","3":"600","4":"974"},{"1":"Wisconsin","2":"582","3":"997","4":"842"},{"1":"Wyoming","2":"451","3":"593","4":"431"}],"options":{"columns":{"min":{},"max":[10]},"rows":{"min":[5],"max":[5]},"pages":{}}}
  </script>
</div>

---

## Reshaping Data: `pivot_longer()`

```r
german_speakers %>% 
  pivot_longer(cols = -state)
```

<div data-pagedtable="false">
  <script data-pagedtable-source type="application/json">
{"columns":[{"label":["state"],"name":[1],"type":["chr"],"align":["left"]},{"label":["name"],"name":[2],"type":["chr"],"align":["left"]},{"label":["value"],"name":[3],"type":["chr"],"align":["left"]}],"data":[{"1":"Alabama","2":"number_of_german_speakers_2017","3":"426"},{"1":"Alabama","2":"number_of_german_speakers_2018","3":"395"},{"1":"Alabama","2":"number_of_german_speakers_2019","3":"711"},{"1":"Alaska","2":"number_of_german_speakers_2017","3":"331"},{"1":"Alaska","2":"number_of_german_speakers_2018","3":"201"},{"1":"Alaska","2":"number_of_german_speakers_2019","3":"131"},{"1":"Arizona","2":"number_of_german_speakers_2017","3":"636"},{"1":"Arizona","2":"number_of_german_speakers_2018","3":"858"},{"1":"Arizona","2":"number_of_german_speakers_2019","3":"136"},{"1":"Arkansas","2":"number_of_german_speakers_2017","3":"-"},{"1":"Arkansas","2":"number_of_german_speakers_2018","3":"635"},{"1":"Arkansas","2":"number_of_german_speakers_2019","3":"557"},{"1":"California","2":"number_of_german_speakers_2017","3":"440"},{"1":"California","2":"number_of_german_speakers_2018","3":"318"},{"1":"California","2":"number_of_german_speakers_2019","3":"854"},{"1":"Colorado","2":"number_of_german_speakers_2017","3":"251"},{"1":"Colorado","2":"number_of_german_speakers_2018","3":"498"},{"1":"Colorado","2":"number_of_german_speakers_2019","3":"768"},{"1":"Connecticut","2":"number_of_german_speakers_2017","3":"187"},{"1":"Connecticut","2":"number_of_german_speakers_2018","3":"334"},{"1":"Connecticut","2":"number_of_german_speakers_2019","3":"586"},{"1":"Delaware","2":"number_of_german_speakers_2017","3":"-"},{"1":"Delaware","2":"number_of_german_speakers_2018","3":"998"},{"1":"Delaware","2":"number_of_german_speakers_2019","3":"453"},{"1":"District of Columbia","2":"number_of_german_speakers_2017","3":"799"},{"1":"District of Columbia","2":"number_of_german_speakers_2018","3":"745"},{"1":"District of Columbia","2":"number_of_german_speakers_2019","3":"180"},{"1":"Florida","2":"number_of_german_speakers_2017","3":"399"},{"1":"Florida","2":"number_of_german_speakers_2018","3":"924"},{"1":"Florida","2":"number_of_german_speakers_2019","3":"958"},{"1":"Georgia","2":"number_of_german_speakers_2017","3":"910"},{"1":"Georgia","2":"number_of_german_speakers_2018","3":"293"},{"1":"Georgia","2":"number_of_german_speakers_2019","3":"821"},{"1":"Hawaii","2":"number_of_german_speakers_2017","3":"-"},{"1":"Hawaii","2":"number_of_german_speakers_2018","3":"303"},{"1":"Hawaii","2":"number_of_german_speakers_2019","3":"931"},{"1":"Idaho","2":"number_of_german_speakers_2017","3":"301"},{"1":"Idaho","2":"number_of_german_speakers_2018","3":"349"},{"1":"Idaho","2":"number_of_german_speakers_2019","3":"-"},{"1":"Illinois","2":"number_of_german_speakers_2017","3":"156"},{"1":"Illinois","2":"number_of_german_speakers_2018","3":"573"},{"1":"Illinois","2":"number_of_german_speakers_2019","3":"827"},{"1":"Indiana","2":"number_of_german_speakers_2017","3":"469"},{"1":"Indiana","2":"number_of_german_speakers_2018","3":"188"},{"1":"Indiana","2":"number_of_german_speakers_2019","3":"728"},{"1":"Iowa","2":"number_of_german_speakers_2017","3":"816"},{"1":"Iowa","2":"number_of_german_speakers_2018","3":"330"},{"1":"Iowa","2":"number_of_german_speakers_2019","3":"657"},{"1":"Kansas","2":"number_of_german_speakers_2017","3":"328"},{"1":"Kansas","2":"number_of_german_speakers_2018","3":"546"},{"1":"Kansas","2":"number_of_german_speakers_2019","3":"501"},{"1":"Kentucky","2":"number_of_german_speakers_2017","3":"299"},{"1":"Kentucky","2":"number_of_german_speakers_2018","3":"976"},{"1":"Kentucky","2":"number_of_german_speakers_2019","3":"332"},{"1":"Louisiana","2":"number_of_german_speakers_2017","3":"266"},{"1":"Louisiana","2":"number_of_german_speakers_2018","3":"-"},{"1":"Louisiana","2":"number_of_german_speakers_2019","3":"490"},{"1":"Maine","2":"number_of_german_speakers_2017","3":"376"},{"1":"Maine","2":"number_of_german_speakers_2018","3":"127"},{"1":"Maine","2":"number_of_german_speakers_2019","3":"206"},{"1":"Montana","2":"number_of_german_speakers_2017","3":"598"},{"1":"Montana","2":"number_of_german_speakers_2018","3":"296"},{"1":"Montana","2":"number_of_german_speakers_2019","3":"527"},{"1":"Nebraska","2":"number_of_german_speakers_2017","3":"252"},{"1":"Nebraska","2":"number_of_german_speakers_2018","3":"726"},{"1":"Nebraska","2":"number_of_german_speakers_2019","3":"232"},{"1":"Nevada","2":"number_of_german_speakers_2017","3":"910"},{"1":"Nevada","2":"number_of_german_speakers_2018","3":"526"},{"1":"Nevada","2":"number_of_german_speakers_2019","3":"647"},{"1":"New Hampshire","2":"number_of_german_speakers_2017","3":"896"},{"1":"New Hampshire","2":"number_of_german_speakers_2018","3":"109"},{"1":"New Hampshire","2":"number_of_german_speakers_2019","3":"818"},{"1":"New Jersey","2":"number_of_german_speakers_2017","3":"380"},{"1":"New Jersey","2":"number_of_german_speakers_2018","3":"961"},{"1":"New Jersey","2":"number_of_german_speakers_2019","3":"168"},{"1":"New Mexico","2":"number_of_german_speakers_2017","3":"574"},{"1":"New Mexico","2":"number_of_german_speakers_2018","3":"771"},{"1":"New Mexico","2":"number_of_german_speakers_2019","3":"336"},{"1":"New York","2":"number_of_german_speakers_2017","3":"135"},{"1":"New York","2":"number_of_german_speakers_2018","3":"637"},{"1":"New York","2":"number_of_german_speakers_2019","3":"207"},{"1":"North Carolina","2":"number_of_german_speakers_2017","3":"609"},{"1":"North Carolina","2":"number_of_german_speakers_2018","3":"970"},{"1":"North Carolina","2":"number_of_german_speakers_2019","3":"508"},{"1":"North Dakota","2":"number_of_german_speakers_2017","3":"891"},{"1":"North Dakota","2":"number_of_german_speakers_2018","3":"307"},{"1":"North Dakota","2":"number_of_german_speakers_2019","3":"652"},{"1":"Ohio","2":"number_of_german_speakers_2017","3":"960"},{"1":"Ohio","2":"number_of_german_speakers_2018","3":"900"},{"1":"Ohio","2":"number_of_german_speakers_2019","3":"186"},{"1":"Oklahoma","2":"number_of_german_speakers_2017","3":"769"},{"1":"Oklahoma","2":"number_of_german_speakers_2018","3":"699"},{"1":"Oklahoma","2":"number_of_german_speakers_2019","3":"621"},{"1":"Oregon","2":"number_of_german_speakers_2017","3":"864"},{"1":"Oregon","2":"number_of_german_speakers_2018","3":"762"},{"1":"Oregon","2":"number_of_german_speakers_2019","3":"471"},{"1":"Maryland","2":"number_of_german_speakers_2017","3":"247"},{"1":"Maryland","2":"number_of_german_speakers_2018","3":"106"},{"1":"Maryland","2":"number_of_german_speakers_2019","3":"507"},{"1":"Massachusetts","2":"number_of_german_speakers_2017","3":"775"},{"1":"Massachusetts","2":"number_of_german_speakers_2018","3":"507"},{"1":"Massachusetts","2":"number_of_german_speakers_2019","3":"859"},{"1":"Michigan","2":"number_of_german_speakers_2017","3":"776"},{"1":"Michigan","2":"number_of_german_speakers_2018","3":"503"},{"1":"Michigan","2":"number_of_german_speakers_2019","3":"467"},{"1":"Minnesota","2":"number_of_german_speakers_2017","3":"752"},{"1":"Minnesota","2":"number_of_german_speakers_2018","3":"344"},{"1":"Minnesota","2":"number_of_german_speakers_2019","3":"251"},{"1":"Mississippi","2":"number_of_german_speakers_2017","3":"945"},{"1":"Mississippi","2":"number_of_german_speakers_2018","3":"246"},{"1":"Mississippi","2":"number_of_german_speakers_2019","3":"372"},{"1":"Missouri","2":"number_of_german_speakers_2017","3":"699"},{"1":"Missouri","2":"number_of_german_speakers_2018","3":"250"},{"1":"Missouri","2":"number_of_german_speakers_2019","3":"972"},{"1":"Pennsylvania","2":"number_of_german_speakers_2017","3":"930"},{"1":"Pennsylvania","2":"number_of_german_speakers_2018","3":"475"},{"1":"Pennsylvania","2":"number_of_german_speakers_2019","3":"956"},{"1":"Rhode Island","2":"number_of_german_speakers_2017","3":"529"},{"1":"Rhode Island","2":"number_of_german_speakers_2018","3":"852"},{"1":"Rhode Island","2":"number_of_german_speakers_2019","3":"900"},{"1":"South Carolina","2":"number_of_german_speakers_2017","3":"617"},{"1":"South Carolina","2":"number_of_german_speakers_2018","3":"815"},{"1":"South Carolina","2":"number_of_german_speakers_2019","3":"182"},{"1":"South Dakota","2":"number_of_german_speakers_2017","3":"262"},{"1":"South Dakota","2":"number_of_german_speakers_2018","3":"601"},{"1":"South Dakota","2":"number_of_german_speakers_2019","3":"584"},{"1":"Tennessee","2":"number_of_german_speakers_2017","3":"153"},{"1":"Tennessee","2":"number_of_german_speakers_2018","3":"568"},{"1":"Tennessee","2":"number_of_german_speakers_2019","3":"476"},{"1":"Texas","2":"number_of_german_speakers_2017","3":"732"},{"1":"Texas","2":"number_of_german_speakers_2018","3":"571"},{"1":"Texas","2":"number_of_german_speakers_2019","3":"292"},{"1":"Utah","2":"number_of_german_speakers_2017","3":"669"},{"1":"Utah","2":"number_of_german_speakers_2018","3":"486"},{"1":"Utah","2":"number_of_german_speakers_2019","3":"758"},{"1":"Vermont","2":"number_of_german_speakers_2017","3":"698"},{"1":"Vermont","2":"number_of_german_speakers_2018","3":"470"},{"1":"Vermont","2":"number_of_german_speakers_2019","3":"158"},{"1":"Virginia","2":"number_of_german_speakers_2017","3":"125"},{"1":"Virginia","2":"number_of_german_speakers_2018","3":"105"},{"1":"Virginia","2":"number_of_german_speakers_2019","3":"432"},{"1":"Washington","2":"number_of_german_speakers_2017","3":"495"},{"1":"Washington","2":"number_of_german_speakers_2018","3":"474"},{"1":"Washington","2":"number_of_german_speakers_2019","3":"615"},{"1":"West Virginia","2":"number_of_german_speakers_2017","3":"603"},{"1":"West Virginia","2":"number_of_german_speakers_2018","3":"600"},{"1":"West Virginia","2":"number_of_german_speakers_2019","3":"974"},{"1":"Wisconsin","2":"number_of_german_speakers_2017","3":"582"},{"1":"Wisconsin","2":"number_of_german_speakers_2018","3":"997"},{"1":"Wisconsin","2":"number_of_german_speakers_2019","3":"842"},{"1":"Wyoming","2":"number_of_german_speakers_2017","3":"451"},{"1":"Wyoming","2":"number_of_german_speakers_2018","3":"593"},{"1":"Wyoming","2":"number_of_german_speakers_2019","3":"431"}],"options":{"columns":{"min":{},"max":[10]},"rows":{"min":[5],"max":[5]},"pages":{}}}
  </script>
</div>

---

## Reshaping Data: `pivot_longer()`

```r
german_speakers %>% 
  pivot_longer(cols = -state,
*              names_to = "year",
*              values_to = "number")
```

<div data-pagedtable="false">
  <script data-pagedtable-source type="application/json">
{"columns":[{"label":["state"],"name":[1],"type":["chr"],"align":["left"]},{"label":["year"],"name":[2],"type":["chr"],"align":["left"]},{"label":["number"],"name":[3],"type":["chr"],"align":["left"]}],"data":[{"1":"Alabama","2":"number_of_german_speakers_2017","3":"426"},{"1":"Alabama","2":"number_of_german_speakers_2018","3":"395"},{"1":"Alabama","2":"number_of_german_speakers_2019","3":"711"},{"1":"Alaska","2":"number_of_german_speakers_2017","3":"331"},{"1":"Alaska","2":"number_of_german_speakers_2018","3":"201"},{"1":"Alaska","2":"number_of_german_speakers_2019","3":"131"},{"1":"Arizona","2":"number_of_german_speakers_2017","3":"636"},{"1":"Arizona","2":"number_of_german_speakers_2018","3":"858"},{"1":"Arizona","2":"number_of_german_speakers_2019","3":"136"},{"1":"Arkansas","2":"number_of_german_speakers_2017","3":"-"},{"1":"Arkansas","2":"number_of_german_speakers_2018","3":"635"},{"1":"Arkansas","2":"number_of_german_speakers_2019","3":"557"},{"1":"California","2":"number_of_german_speakers_2017","3":"440"},{"1":"California","2":"number_of_german_speakers_2018","3":"318"},{"1":"California","2":"number_of_german_speakers_2019","3":"854"},{"1":"Colorado","2":"number_of_german_speakers_2017","3":"251"},{"1":"Colorado","2":"number_of_german_speakers_2018","3":"498"},{"1":"Colorado","2":"number_of_german_speakers_2019","3":"768"},{"1":"Connecticut","2":"number_of_german_speakers_2017","3":"187"},{"1":"Connecticut","2":"number_of_german_speakers_2018","3":"334"},{"1":"Connecticut","2":"number_of_german_speakers_2019","3":"586"},{"1":"Delaware","2":"number_of_german_speakers_2017","3":"-"},{"1":"Delaware","2":"number_of_german_speakers_2018","3":"998"},{"1":"Delaware","2":"number_of_german_speakers_2019","3":"453"},{"1":"District of Columbia","2":"number_of_german_speakers_2017","3":"799"},{"1":"District of Columbia","2":"number_of_german_speakers_2018","3":"745"},{"1":"District of Columbia","2":"number_of_german_speakers_2019","3":"180"},{"1":"Florida","2":"number_of_german_speakers_2017","3":"399"},{"1":"Florida","2":"number_of_german_speakers_2018","3":"924"},{"1":"Florida","2":"number_of_german_speakers_2019","3":"958"},{"1":"Georgia","2":"number_of_german_speakers_2017","3":"910"},{"1":"Georgia","2":"number_of_german_speakers_2018","3":"293"},{"1":"Georgia","2":"number_of_german_speakers_2019","3":"821"},{"1":"Hawaii","2":"number_of_german_speakers_2017","3":"-"},{"1":"Hawaii","2":"number_of_german_speakers_2018","3":"303"},{"1":"Hawaii","2":"number_of_german_speakers_2019","3":"931"},{"1":"Idaho","2":"number_of_german_speakers_2017","3":"301"},{"1":"Idaho","2":"number_of_german_speakers_2018","3":"349"},{"1":"Idaho","2":"number_of_german_speakers_2019","3":"-"},{"1":"Illinois","2":"number_of_german_speakers_2017","3":"156"},{"1":"Illinois","2":"number_of_german_speakers_2018","3":"573"},{"1":"Illinois","2":"number_of_german_speakers_2019","3":"827"},{"1":"Indiana","2":"number_of_german_speakers_2017","3":"469"},{"1":"Indiana","2":"number_of_german_speakers_2018","3":"188"},{"1":"Indiana","2":"number_of_german_speakers_2019","3":"728"},{"1":"Iowa","2":"number_of_german_speakers_2017","3":"816"},{"1":"Iowa","2":"number_of_german_speakers_2018","3":"330"},{"1":"Iowa","2":"number_of_german_speakers_2019","3":"657"},{"1":"Kansas","2":"number_of_german_speakers_2017","3":"328"},{"1":"Kansas","2":"number_of_german_speakers_2018","3":"546"},{"1":"Kansas","2":"number_of_german_speakers_2019","3":"501"},{"1":"Kentucky","2":"number_of_german_speakers_2017","3":"299"},{"1":"Kentucky","2":"number_of_german_speakers_2018","3":"976"},{"1":"Kentucky","2":"number_of_german_speakers_2019","3":"332"},{"1":"Louisiana","2":"number_of_german_speakers_2017","3":"266"},{"1":"Louisiana","2":"number_of_german_speakers_2018","3":"-"},{"1":"Louisiana","2":"number_of_german_speakers_2019","3":"490"},{"1":"Maine","2":"number_of_german_speakers_2017","3":"376"},{"1":"Maine","2":"number_of_german_speakers_2018","3":"127"},{"1":"Maine","2":"number_of_german_speakers_2019","3":"206"},{"1":"Montana","2":"number_of_german_speakers_2017","3":"598"},{"1":"Montana","2":"number_of_german_speakers_2018","3":"296"},{"1":"Montana","2":"number_of_german_speakers_2019","3":"527"},{"1":"Nebraska","2":"number_of_german_speakers_2017","3":"252"},{"1":"Nebraska","2":"number_of_german_speakers_2018","3":"726"},{"1":"Nebraska","2":"number_of_german_speakers_2019","3":"232"},{"1":"Nevada","2":"number_of_german_speakers_2017","3":"910"},{"1":"Nevada","2":"number_of_german_speakers_2018","3":"526"},{"1":"Nevada","2":"number_of_german_speakers_2019","3":"647"},{"1":"New Hampshire","2":"number_of_german_speakers_2017","3":"896"},{"1":"New Hampshire","2":"number_of_german_speakers_2018","3":"109"},{"1":"New Hampshire","2":"number_of_german_speakers_2019","3":"818"},{"1":"New Jersey","2":"number_of_german_speakers_2017","3":"380"},{"1":"New Jersey","2":"number_of_german_speakers_2018","3":"961"},{"1":"New Jersey","2":"number_of_german_speakers_2019","3":"168"},{"1":"New Mexico","2":"number_of_german_speakers_2017","3":"574"},{"1":"New Mexico","2":"number_of_german_speakers_2018","3":"771"},{"1":"New Mexico","2":"number_of_german_speakers_2019","3":"336"},{"1":"New York","2":"number_of_german_speakers_2017","3":"135"},{"1":"New York","2":"number_of_german_speakers_2018","3":"637"},{"1":"New York","2":"number_of_german_speakers_2019","3":"207"},{"1":"North Carolina","2":"number_of_german_speakers_2017","3":"609"},{"1":"North Carolina","2":"number_of_german_speakers_2018","3":"970"},{"1":"North Carolina","2":"number_of_german_speakers_2019","3":"508"},{"1":"North Dakota","2":"number_of_german_speakers_2017","3":"891"},{"1":"North Dakota","2":"number_of_german_speakers_2018","3":"307"},{"1":"North Dakota","2":"number_of_german_speakers_2019","3":"652"},{"1":"Ohio","2":"number_of_german_speakers_2017","3":"960"},{"1":"Ohio","2":"number_of_german_speakers_2018","3":"900"},{"1":"Ohio","2":"number_of_german_speakers_2019","3":"186"},{"1":"Oklahoma","2":"number_of_german_speakers_2017","3":"769"},{"1":"Oklahoma","2":"number_of_german_speakers_2018","3":"699"},{"1":"Oklahoma","2":"number_of_german_speakers_2019","3":"621"},{"1":"Oregon","2":"number_of_german_speakers_2017","3":"864"},{"1":"Oregon","2":"number_of_german_speakers_2018","3":"762"},{"1":"Oregon","2":"number_of_german_speakers_2019","3":"471"},{"1":"Maryland","2":"number_of_german_speakers_2017","3":"247"},{"1":"Maryland","2":"number_of_german_speakers_2018","3":"106"},{"1":"Maryland","2":"number_of_german_speakers_2019","3":"507"},{"1":"Massachusetts","2":"number_of_german_speakers_2017","3":"775"},{"1":"Massachusetts","2":"number_of_german_speakers_2018","3":"507"},{"1":"Massachusetts","2":"number_of_german_speakers_2019","3":"859"},{"1":"Michigan","2":"number_of_german_speakers_2017","3":"776"},{"1":"Michigan","2":"number_of_german_speakers_2018","3":"503"},{"1":"Michigan","2":"number_of_german_speakers_2019","3":"467"},{"1":"Minnesota","2":"number_of_german_speakers_2017","3":"752"},{"1":"Minnesota","2":"number_of_german_speakers_2018","3":"344"},{"1":"Minnesota","2":"number_of_german_speakers_2019","3":"251"},{"1":"Mississippi","2":"number_of_german_speakers_2017","3":"945"},{"1":"Mississippi","2":"number_of_german_speakers_2018","3":"246"},{"1":"Mississippi","2":"number_of_german_speakers_2019","3":"372"},{"1":"Missouri","2":"number_of_german_speakers_2017","3":"699"},{"1":"Missouri","2":"number_of_german_speakers_2018","3":"250"},{"1":"Missouri","2":"number_of_german_speakers_2019","3":"972"},{"1":"Pennsylvania","2":"number_of_german_speakers_2017","3":"930"},{"1":"Pennsylvania","2":"number_of_german_speakers_2018","3":"475"},{"1":"Pennsylvania","2":"number_of_german_speakers_2019","3":"956"},{"1":"Rhode Island","2":"number_of_german_speakers_2017","3":"529"},{"1":"Rhode Island","2":"number_of_german_speakers_2018","3":"852"},{"1":"Rhode Island","2":"number_of_german_speakers_2019","3":"900"},{"1":"South Carolina","2":"number_of_german_speakers_2017","3":"617"},{"1":"South Carolina","2":"number_of_german_speakers_2018","3":"815"},{"1":"South Carolina","2":"number_of_german_speakers_2019","3":"182"},{"1":"South Dakota","2":"number_of_german_speakers_2017","3":"262"},{"1":"South Dakota","2":"number_of_german_speakers_2018","3":"601"},{"1":"South Dakota","2":"number_of_german_speakers_2019","3":"584"},{"1":"Tennessee","2":"number_of_german_speakers_2017","3":"153"},{"1":"Tennessee","2":"number_of_german_speakers_2018","3":"568"},{"1":"Tennessee","2":"number_of_german_speakers_2019","3":"476"},{"1":"Texas","2":"number_of_german_speakers_2017","3":"732"},{"1":"Texas","2":"number_of_german_speakers_2018","3":"571"},{"1":"Texas","2":"number_of_german_speakers_2019","3":"292"},{"1":"Utah","2":"number_of_german_speakers_2017","3":"669"},{"1":"Utah","2":"number_of_german_speakers_2018","3":"486"},{"1":"Utah","2":"number_of_german_speakers_2019","3":"758"},{"1":"Vermont","2":"number_of_german_speakers_2017","3":"698"},{"1":"Vermont","2":"number_of_german_speakers_2018","3":"470"},{"1":"Vermont","2":"number_of_german_speakers_2019","3":"158"},{"1":"Virginia","2":"number_of_german_speakers_2017","3":"125"},{"1":"Virginia","2":"number_of_german_speakers_2018","3":"105"},{"1":"Virginia","2":"number_of_german_speakers_2019","3":"432"},{"1":"Washington","2":"number_of_german_speakers_2017","3":"495"},{"1":"Washington","2":"number_of_german_speakers_2018","3":"474"},{"1":"Washington","2":"number_of_german_speakers_2019","3":"615"},{"1":"West Virginia","2":"number_of_german_speakers_2017","3":"603"},{"1":"West Virginia","2":"number_of_german_speakers_2018","3":"600"},{"1":"West Virginia","2":"number_of_german_speakers_2019","3":"974"},{"1":"Wisconsin","2":"number_of_german_speakers_2017","3":"582"},{"1":"Wisconsin","2":"number_of_german_speakers_2018","3":"997"},{"1":"Wisconsin","2":"number_of_german_speakers_2019","3":"842"},{"1":"Wyoming","2":"number_of_german_speakers_2017","3":"451"},{"1":"Wyoming","2":"number_of_german_speakers_2018","3":"593"},{"1":"Wyoming","2":"number_of_german_speakers_2019","3":"431"}],"options":{"columns":{"min":{},"max":[10]},"rows":{"min":[5],"max":[5]},"pages":{}}}
  </script>
</div>

---

## Reshaping Data: `pivot_wider()`

```r
german_speakers %>% 
  pivot_longer(cols = -state,
               names_to = "year",
               values_to = "number") %>% 
* pivot_wider(id_cols = year,
*             names_from = "state",
*             values_from = "number")
```

<div data-pagedtable="false">
  <script data-pagedtable-source type="application/json">
{"columns":[{"label":["year"],"name":[1],"type":["chr"],"align":["left"]},{"label":["Alabama"],"name":[2],"type":["chr"],"align":["left"]},{"label":["Alaska"],"name":[3],"type":["chr"],"align":["left"]},{"label":["Arizona"],"name":[4],"type":["chr"],"align":["left"]},{"label":["Arkansas"],"name":[5],"type":["chr"],"align":["left"]},{"label":["California"],"name":[6],"type":["chr"],"align":["left"]},{"label":["Colorado"],"name":[7],"type":["chr"],"align":["left"]},{"label":["Connecticut"],"name":[8],"type":["chr"],"align":["left"]},{"label":["Delaware"],"name":[9],"type":["chr"],"align":["left"]},{"label":["District of Columbia"],"name":[10],"type":["chr"],"align":["left"]},{"label":["Florida"],"name":[11],"type":["chr"],"align":["left"]},{"label":["Georgia"],"name":[12],"type":["chr"],"align":["left"]},{"label":["Hawaii"],"name":[13],"type":["chr"],"align":["left"]},{"label":["Idaho"],"name":[14],"type":["chr"],"align":["left"]},{"label":["Illinois"],"name":[15],"type":["chr"],"align":["left"]},{"label":["Indiana"],"name":[16],"type":["chr"],"align":["left"]},{"label":["Iowa"],"name":[17],"type":["chr"],"align":["left"]},{"label":["Kansas"],"name":[18],"type":["chr"],"align":["left"]},{"label":["Kentucky"],"name":[19],"type":["chr"],"align":["left"]},{"label":["Louisiana"],"name":[20],"type":["chr"],"align":["left"]},{"label":["Maine"],"name":[21],"type":["chr"],"align":["left"]},{"label":["Montana"],"name":[22],"type":["chr"],"align":["left"]},{"label":["Nebraska"],"name":[23],"type":["chr"],"align":["left"]},{"label":["Nevada"],"name":[24],"type":["chr"],"align":["left"]},{"label":["New Hampshire"],"name":[25],"type":["chr"],"align":["left"]},{"label":["New Jersey"],"name":[26],"type":["chr"],"align":["left"]},{"label":["New Mexico"],"name":[27],"type":["chr"],"align":["left"]},{"label":["New York"],"name":[28],"type":["chr"],"align":["left"]},{"label":["North Carolina"],"name":[29],"type":["chr"],"align":["left"]},{"label":["North Dakota"],"name":[30],"type":["chr"],"align":["left"]},{"label":["Ohio"],"name":[31],"type":["chr"],"align":["left"]},{"label":["Oklahoma"],"name":[32],"type":["chr"],"align":["left"]},{"label":["Oregon"],"name":[33],"type":["chr"],"align":["left"]},{"label":["Maryland"],"name":[34],"type":["chr"],"align":["left"]},{"label":["Massachusetts"],"name":[35],"type":["chr"],"align":["left"]},{"label":["Michigan"],"name":[36],"type":["chr"],"align":["left"]},{"label":["Minnesota"],"name":[37],"type":["chr"],"align":["left"]},{"label":["Mississippi"],"name":[38],"type":["chr"],"align":["left"]},{"label":["Missouri"],"name":[39],"type":["chr"],"align":["left"]},{"label":["Pennsylvania"],"name":[40],"type":["chr"],"align":["left"]},{"label":["Rhode Island"],"name":[41],"type":["chr"],"align":["left"]},{"label":["South Carolina"],"name":[42],"type":["chr"],"align":["left"]},{"label":["South Dakota"],"name":[43],"type":["chr"],"align":["left"]},{"label":["Tennessee"],"name":[44],"type":["chr"],"align":["left"]},{"label":["Texas"],"name":[45],"type":["chr"],"align":["left"]},{"label":["Utah"],"name":[46],"type":["chr"],"align":["left"]},{"label":["Vermont"],"name":[47],"type":["chr"],"align":["left"]},{"label":["Virginia"],"name":[48],"type":["chr"],"align":["left"]},{"label":["Washington"],"name":[49],"type":["chr"],"align":["left"]},{"label":["West Virginia"],"name":[50],"type":["chr"],"align":["left"]},{"label":["Wisconsin"],"name":[51],"type":["chr"],"align":["left"]},{"label":["Wyoming"],"name":[52],"type":["chr"],"align":["left"]}],"data":[{"1":"number_of_german_speakers_2017","2":"426","3":"331","4":"636","5":"-","6":"440","7":"251","8":"187","9":"-","10":"799","11":"399","12":"910","13":"-","14":"301","15":"156","16":"469","17":"816","18":"328","19":"299","20":"266","21":"376","22":"598","23":"252","24":"910","25":"896","26":"380","27":"574","28":"135","29":"609","30":"891","31":"960","32":"769","33":"864","34":"247","35":"775","36":"776","37":"752","38":"945","39":"699","40":"930","41":"529","42":"617","43":"262","44":"153","45":"732","46":"669","47":"698","48":"125","49":"495","50":"603","51":"582","52":"451"},{"1":"number_of_german_speakers_2018","2":"395","3":"201","4":"858","5":"635","6":"318","7":"498","8":"334","9":"998","10":"745","11":"924","12":"293","13":"303","14":"349","15":"573","16":"188","17":"330","18":"546","19":"976","20":"-","21":"127","22":"296","23":"726","24":"526","25":"109","26":"961","27":"771","28":"637","29":"970","30":"307","31":"900","32":"699","33":"762","34":"106","35":"507","36":"503","37":"344","38":"246","39":"250","40":"475","41":"852","42":"815","43":"601","44":"568","45":"571","46":"486","47":"470","48":"105","49":"474","50":"600","51":"997","52":"593"},{"1":"number_of_german_speakers_2019","2":"711","3":"131","4":"136","5":"557","6":"854","7":"768","8":"586","9":"453","10":"180","11":"958","12":"821","13":"931","14":"-","15":"827","16":"728","17":"657","18":"501","19":"332","20":"490","21":"206","22":"527","23":"232","24":"647","25":"818","26":"168","27":"336","28":"207","29":"508","30":"652","31":"186","32":"621","33":"471","34":"507","35":"859","36":"467","37":"251","38":"372","39":"972","40":"956","41":"900","42":"182","43":"584","44":"476","45":"292","46":"758","47":"158","48":"432","49":"615","50":"974","51":"842","52":"431"}],"options":{"columns":{"min":{},"max":[10]},"rows":{"min":[5],"max":[5]},"pages":{}}}
  </script>
</div>

???

I mostly use it to present data in tables

---

## My Turn

I'll do the following to create a new data frame called `third_grade_math_proficiency_18_19`:

1. Use `filter()` to only keep rows where the `student_group` variable is "Total Population (All Students)"

1. Use `filter()` to only keep third grade students

1. Use `select()` to only keep variables related to the **number** (not percentage) of students who are proficient in math

1. Use `pivot_longer()` to make my data frame tidy

---

## Your Turn

Do the following to create a new data frame called `enrollment_by_race_ethnicity_18_19`:

1. Start with the `enrollment_18_19` data frame

1. select() the `district_id` variable as well as those about number of students by race/ethnicity and get rid of all others (hint: use the [`contains()` helper function](https://dplyr.tidyverse.org/reference/select.html#useful-functions) within `select()`)

1. Use `pivot_longer()` to convert all of the race/ethnicity variables into one variable

1. Within `pivot_longer()`, use the names_to argument to call that variable `race_ethnicity`

1. Within `pivot_longer()`, use the values_to argument to call that variable `number_of_students`

---

background-image:url("images/missing.jpg")

# Dealing with Missing Data

---

## `na_if()`

```r
german_speakers %>% 
  pivot_longer(cols = -state,
               names_to = "year", 
               values_to = "number")
```

<div data-pagedtable="false">
  <script data-pagedtable-source type="application/json">
{"columns":[{"label":["state"],"name":[1],"type":["chr"],"align":["left"]},{"label":["year"],"name":[2],"type":["chr"],"align":["left"]},{"label":["number"],"name":[3],"type":["chr"],"align":["left"]}],"data":[{"1":"Alabama","2":"number_of_german_speakers_2017","3":"426"},{"1":"Alabama","2":"number_of_german_speakers_2018","3":"395"},{"1":"Alabama","2":"number_of_german_speakers_2019","3":"711"},{"1":"Alaska","2":"number_of_german_speakers_2017","3":"331"},{"1":"Alaska","2":"number_of_german_speakers_2018","3":"201"},{"1":"Alaska","2":"number_of_german_speakers_2019","3":"131"},{"1":"Arizona","2":"number_of_german_speakers_2017","3":"636"},{"1":"Arizona","2":"number_of_german_speakers_2018","3":"858"},{"1":"Arizona","2":"number_of_german_speakers_2019","3":"136"},{"1":"Arkansas","2":"number_of_german_speakers_2017","3":"-"},{"1":"Arkansas","2":"number_of_german_speakers_2018","3":"635"},{"1":"Arkansas","2":"number_of_german_speakers_2019","3":"557"},{"1":"California","2":"number_of_german_speakers_2017","3":"440"},{"1":"California","2":"number_of_german_speakers_2018","3":"318"},{"1":"California","2":"number_of_german_speakers_2019","3":"854"},{"1":"Colorado","2":"number_of_german_speakers_2017","3":"251"},{"1":"Colorado","2":"number_of_german_speakers_2018","3":"498"},{"1":"Colorado","2":"number_of_german_speakers_2019","3":"768"},{"1":"Connecticut","2":"number_of_german_speakers_2017","3":"187"},{"1":"Connecticut","2":"number_of_german_speakers_2018","3":"334"},{"1":"Connecticut","2":"number_of_german_speakers_2019","3":"586"},{"1":"Delaware","2":"number_of_german_speakers_2017","3":"-"},{"1":"Delaware","2":"number_of_german_speakers_2018","3":"998"},{"1":"Delaware","2":"number_of_german_speakers_2019","3":"453"},{"1":"District of Columbia","2":"number_of_german_speakers_2017","3":"799"},{"1":"District of Columbia","2":"number_of_german_speakers_2018","3":"745"},{"1":"District of Columbia","2":"number_of_german_speakers_2019","3":"180"},{"1":"Florida","2":"number_of_german_speakers_2017","3":"399"},{"1":"Florida","2":"number_of_german_speakers_2018","3":"924"},{"1":"Florida","2":"number_of_german_speakers_2019","3":"958"},{"1":"Georgia","2":"number_of_german_speakers_2017","3":"910"},{"1":"Georgia","2":"number_of_german_speakers_2018","3":"293"},{"1":"Georgia","2":"number_of_german_speakers_2019","3":"821"},{"1":"Hawaii","2":"number_of_german_speakers_2017","3":"-"},{"1":"Hawaii","2":"number_of_german_speakers_2018","3":"303"},{"1":"Hawaii","2":"number_of_german_speakers_2019","3":"931"},{"1":"Idaho","2":"number_of_german_speakers_2017","3":"301"},{"1":"Idaho","2":"number_of_german_speakers_2018","3":"349"},{"1":"Idaho","2":"number_of_german_speakers_2019","3":"-"},{"1":"Illinois","2":"number_of_german_speakers_2017","3":"156"},{"1":"Illinois","2":"number_of_german_speakers_2018","3":"573"},{"1":"Illinois","2":"number_of_german_speakers_2019","3":"827"},{"1":"Indiana","2":"number_of_german_speakers_2017","3":"469"},{"1":"Indiana","2":"number_of_german_speakers_2018","3":"188"},{"1":"Indiana","2":"number_of_german_speakers_2019","3":"728"},{"1":"Iowa","2":"number_of_german_speakers_2017","3":"816"},{"1":"Iowa","2":"number_of_german_speakers_2018","3":"330"},{"1":"Iowa","2":"number_of_german_speakers_2019","3":"657"},{"1":"Kansas","2":"number_of_german_speakers_2017","3":"328"},{"1":"Kansas","2":"number_of_german_speakers_2018","3":"546"},{"1":"Kansas","2":"number_of_german_speakers_2019","3":"501"},{"1":"Kentucky","2":"number_of_german_speakers_2017","3":"299"},{"1":"Kentucky","2":"number_of_german_speakers_2018","3":"976"},{"1":"Kentucky","2":"number_of_german_speakers_2019","3":"332"},{"1":"Louisiana","2":"number_of_german_speakers_2017","3":"266"},{"1":"Louisiana","2":"number_of_german_speakers_2018","3":"-"},{"1":"Louisiana","2":"number_of_german_speakers_2019","3":"490"},{"1":"Maine","2":"number_of_german_speakers_2017","3":"376"},{"1":"Maine","2":"number_of_german_speakers_2018","3":"127"},{"1":"Maine","2":"number_of_german_speakers_2019","3":"206"},{"1":"Montana","2":"number_of_german_speakers_2017","3":"598"},{"1":"Montana","2":"number_of_german_speakers_2018","3":"296"},{"1":"Montana","2":"number_of_german_speakers_2019","3":"527"},{"1":"Nebraska","2":"number_of_german_speakers_2017","3":"252"},{"1":"Nebraska","2":"number_of_german_speakers_2018","3":"726"},{"1":"Nebraska","2":"number_of_german_speakers_2019","3":"232"},{"1":"Nevada","2":"number_of_german_speakers_2017","3":"910"},{"1":"Nevada","2":"number_of_german_speakers_2018","3":"526"},{"1":"Nevada","2":"number_of_german_speakers_2019","3":"647"},{"1":"New Hampshire","2":"number_of_german_speakers_2017","3":"896"},{"1":"New Hampshire","2":"number_of_german_speakers_2018","3":"109"},{"1":"New Hampshire","2":"number_of_german_speakers_2019","3":"818"},{"1":"New Jersey","2":"number_of_german_speakers_2017","3":"380"},{"1":"New Jersey","2":"number_of_german_speakers_2018","3":"961"},{"1":"New Jersey","2":"number_of_german_speakers_2019","3":"168"},{"1":"New Mexico","2":"number_of_german_speakers_2017","3":"574"},{"1":"New Mexico","2":"number_of_german_speakers_2018","3":"771"},{"1":"New Mexico","2":"number_of_german_speakers_2019","3":"336"},{"1":"New York","2":"number_of_german_speakers_2017","3":"135"},{"1":"New York","2":"number_of_german_speakers_2018","3":"637"},{"1":"New York","2":"number_of_german_speakers_2019","3":"207"},{"1":"North Carolina","2":"number_of_german_speakers_2017","3":"609"},{"1":"North Carolina","2":"number_of_german_speakers_2018","3":"970"},{"1":"North Carolina","2":"number_of_german_speakers_2019","3":"508"},{"1":"North Dakota","2":"number_of_german_speakers_2017","3":"891"},{"1":"North Dakota","2":"number_of_german_speakers_2018","3":"307"},{"1":"North Dakota","2":"number_of_german_speakers_2019","3":"652"},{"1":"Ohio","2":"number_of_german_speakers_2017","3":"960"},{"1":"Ohio","2":"number_of_german_speakers_2018","3":"900"},{"1":"Ohio","2":"number_of_german_speakers_2019","3":"186"},{"1":"Oklahoma","2":"number_of_german_speakers_2017","3":"769"},{"1":"Oklahoma","2":"number_of_german_speakers_2018","3":"699"},{"1":"Oklahoma","2":"number_of_german_speakers_2019","3":"621"},{"1":"Oregon","2":"number_of_german_speakers_2017","3":"864"},{"1":"Oregon","2":"number_of_german_speakers_2018","3":"762"},{"1":"Oregon","2":"number_of_german_speakers_2019","3":"471"},{"1":"Maryland","2":"number_of_german_speakers_2017","3":"247"},{"1":"Maryland","2":"number_of_german_speakers_2018","3":"106"},{"1":"Maryland","2":"number_of_german_speakers_2019","3":"507"},{"1":"Massachusetts","2":"number_of_german_speakers_2017","3":"775"},{"1":"Massachusetts","2":"number_of_german_speakers_2018","3":"507"},{"1":"Massachusetts","2":"number_of_german_speakers_2019","3":"859"},{"1":"Michigan","2":"number_of_german_speakers_2017","3":"776"},{"1":"Michigan","2":"number_of_german_speakers_2018","3":"503"},{"1":"Michigan","2":"number_of_german_speakers_2019","3":"467"},{"1":"Minnesota","2":"number_of_german_speakers_2017","3":"752"},{"1":"Minnesota","2":"number_of_german_speakers_2018","3":"344"},{"1":"Minnesota","2":"number_of_german_speakers_2019","3":"251"},{"1":"Mississippi","2":"number_of_german_speakers_2017","3":"945"},{"1":"Mississippi","2":"number_of_german_speakers_2018","3":"246"},{"1":"Mississippi","2":"number_of_german_speakers_2019","3":"372"},{"1":"Missouri","2":"number_of_german_speakers_2017","3":"699"},{"1":"Missouri","2":"number_of_german_speakers_2018","3":"250"},{"1":"Missouri","2":"number_of_german_speakers_2019","3":"972"},{"1":"Pennsylvania","2":"number_of_german_speakers_2017","3":"930"},{"1":"Pennsylvania","2":"number_of_german_speakers_2018","3":"475"},{"1":"Pennsylvania","2":"number_of_german_speakers_2019","3":"956"},{"1":"Rhode Island","2":"number_of_german_speakers_2017","3":"529"},{"1":"Rhode Island","2":"number_of_german_speakers_2018","3":"852"},{"1":"Rhode Island","2":"number_of_german_speakers_2019","3":"900"},{"1":"South Carolina","2":"number_of_german_speakers_2017","3":"617"},{"1":"South Carolina","2":"number_of_german_speakers_2018","3":"815"},{"1":"South Carolina","2":"number_of_german_speakers_2019","3":"182"},{"1":"South Dakota","2":"number_of_german_speakers_2017","3":"262"},{"1":"South Dakota","2":"number_of_german_speakers_2018","3":"601"},{"1":"South Dakota","2":"number_of_german_speakers_2019","3":"584"},{"1":"Tennessee","2":"number_of_german_speakers_2017","3":"153"},{"1":"Tennessee","2":"number_of_german_speakers_2018","3":"568"},{"1":"Tennessee","2":"number_of_german_speakers_2019","3":"476"},{"1":"Texas","2":"number_of_german_speakers_2017","3":"732"},{"1":"Texas","2":"number_of_german_speakers_2018","3":"571"},{"1":"Texas","2":"number_of_german_speakers_2019","3":"292"},{"1":"Utah","2":"number_of_german_speakers_2017","3":"669"},{"1":"Utah","2":"number_of_german_speakers_2018","3":"486"},{"1":"Utah","2":"number_of_german_speakers_2019","3":"758"},{"1":"Vermont","2":"number_of_german_speakers_2017","3":"698"},{"1":"Vermont","2":"number_of_german_speakers_2018","3":"470"},{"1":"Vermont","2":"number_of_german_speakers_2019","3":"158"},{"1":"Virginia","2":"number_of_german_speakers_2017","3":"125"},{"1":"Virginia","2":"number_of_german_speakers_2018","3":"105"},{"1":"Virginia","2":"number_of_german_speakers_2019","3":"432"},{"1":"Washington","2":"number_of_german_speakers_2017","3":"495"},{"1":"Washington","2":"number_of_german_speakers_2018","3":"474"},{"1":"Washington","2":"number_of_german_speakers_2019","3":"615"},{"1":"West Virginia","2":"number_of_german_speakers_2017","3":"603"},{"1":"West Virginia","2":"number_of_german_speakers_2018","3":"600"},{"1":"West Virginia","2":"number_of_german_speakers_2019","3":"974"},{"1":"Wisconsin","2":"number_of_german_speakers_2017","3":"582"},{"1":"Wisconsin","2":"number_of_german_speakers_2018","3":"997"},{"1":"Wisconsin","2":"number_of_german_speakers_2019","3":"842"},{"1":"Wyoming","2":"number_of_german_speakers_2017","3":"451"},{"1":"Wyoming","2":"number_of_german_speakers_2018","3":"593"},{"1":"Wyoming","2":"number_of_german_speakers_2019","3":"431"}],"options":{"columns":{"min":{},"max":[10]},"rows":{"min":[5],"max":[5]},"pages":{}}}
  </script>
</div>

---

## `na_if()`

```r
german_speakers %>% 
  pivot_longer(cols = -state,
               names_to = "year", 
               values_to = "number") %>% 
* mutate(number = na_if(number, "-"))
```

---

## `na_if()`

<div data-pagedtable="false">
  <script data-pagedtable-source type="application/json">
{"columns":[{"label":["state"],"name":[1],"type":["chr"],"align":["left"]},{"label":["year"],"name":[2],"type":["chr"],"align":["left"]},{"label":["number"],"name":[3],"type":["chr"],"align":["left"]}],"data":[{"1":"Alabama","2":"number_of_german_speakers_2017","3":"426"},{"1":"Alabama","2":"number_of_german_speakers_2018","3":"395"},{"1":"Alabama","2":"number_of_german_speakers_2019","3":"711"},{"1":"Alaska","2":"number_of_german_speakers_2017","3":"331"},{"1":"Alaska","2":"number_of_german_speakers_2018","3":"201"},{"1":"Alaska","2":"number_of_german_speakers_2019","3":"131"},{"1":"Arizona","2":"number_of_german_speakers_2017","3":"636"},{"1":"Arizona","2":"number_of_german_speakers_2018","3":"858"},{"1":"Arizona","2":"number_of_german_speakers_2019","3":"136"},{"1":"Arkansas","2":"number_of_german_speakers_2017","3":"NA"},{"1":"Arkansas","2":"number_of_german_speakers_2018","3":"635"},{"1":"Arkansas","2":"number_of_german_speakers_2019","3":"557"},{"1":"California","2":"number_of_german_speakers_2017","3":"440"},{"1":"California","2":"number_of_german_speakers_2018","3":"318"},{"1":"California","2":"number_of_german_speakers_2019","3":"854"},{"1":"Colorado","2":"number_of_german_speakers_2017","3":"251"},{"1":"Colorado","2":"number_of_german_speakers_2018","3":"498"},{"1":"Colorado","2":"number_of_german_speakers_2019","3":"768"},{"1":"Connecticut","2":"number_of_german_speakers_2017","3":"187"},{"1":"Connecticut","2":"number_of_german_speakers_2018","3":"334"},{"1":"Connecticut","2":"number_of_german_speakers_2019","3":"586"},{"1":"Delaware","2":"number_of_german_speakers_2017","3":"NA"},{"1":"Delaware","2":"number_of_german_speakers_2018","3":"998"},{"1":"Delaware","2":"number_of_german_speakers_2019","3":"453"},{"1":"District of Columbia","2":"number_of_german_speakers_2017","3":"799"},{"1":"District of Columbia","2":"number_of_german_speakers_2018","3":"745"},{"1":"District of Columbia","2":"number_of_german_speakers_2019","3":"180"},{"1":"Florida","2":"number_of_german_speakers_2017","3":"399"},{"1":"Florida","2":"number_of_german_speakers_2018","3":"924"},{"1":"Florida","2":"number_of_german_speakers_2019","3":"958"},{"1":"Georgia","2":"number_of_german_speakers_2017","3":"910"},{"1":"Georgia","2":"number_of_german_speakers_2018","3":"293"},{"1":"Georgia","2":"number_of_german_speakers_2019","3":"821"},{"1":"Hawaii","2":"number_of_german_speakers_2017","3":"NA"},{"1":"Hawaii","2":"number_of_german_speakers_2018","3":"303"},{"1":"Hawaii","2":"number_of_german_speakers_2019","3":"931"},{"1":"Idaho","2":"number_of_german_speakers_2017","3":"301"},{"1":"Idaho","2":"number_of_german_speakers_2018","3":"349"},{"1":"Idaho","2":"number_of_german_speakers_2019","3":"NA"},{"1":"Illinois","2":"number_of_german_speakers_2017","3":"156"},{"1":"Illinois","2":"number_of_german_speakers_2018","3":"573"},{"1":"Illinois","2":"number_of_german_speakers_2019","3":"827"},{"1":"Indiana","2":"number_of_german_speakers_2017","3":"469"},{"1":"Indiana","2":"number_of_german_speakers_2018","3":"188"},{"1":"Indiana","2":"number_of_german_speakers_2019","3":"728"},{"1":"Iowa","2":"number_of_german_speakers_2017","3":"816"},{"1":"Iowa","2":"number_of_german_speakers_2018","3":"330"},{"1":"Iowa","2":"number_of_german_speakers_2019","3":"657"},{"1":"Kansas","2":"number_of_german_speakers_2017","3":"328"},{"1":"Kansas","2":"number_of_german_speakers_2018","3":"546"},{"1":"Kansas","2":"number_of_german_speakers_2019","3":"501"},{"1":"Kentucky","2":"number_of_german_speakers_2017","3":"299"},{"1":"Kentucky","2":"number_of_german_speakers_2018","3":"976"},{"1":"Kentucky","2":"number_of_german_speakers_2019","3":"332"},{"1":"Louisiana","2":"number_of_german_speakers_2017","3":"266"},{"1":"Louisiana","2":"number_of_german_speakers_2018","3":"NA"},{"1":"Louisiana","2":"number_of_german_speakers_2019","3":"490"},{"1":"Maine","2":"number_of_german_speakers_2017","3":"376"},{"1":"Maine","2":"number_of_german_speakers_2018","3":"127"},{"1":"Maine","2":"number_of_german_speakers_2019","3":"206"},{"1":"Montana","2":"number_of_german_speakers_2017","3":"598"},{"1":"Montana","2":"number_of_german_speakers_2018","3":"296"},{"1":"Montana","2":"number_of_german_speakers_2019","3":"527"},{"1":"Nebraska","2":"number_of_german_speakers_2017","3":"252"},{"1":"Nebraska","2":"number_of_german_speakers_2018","3":"726"},{"1":"Nebraska","2":"number_of_german_speakers_2019","3":"232"},{"1":"Nevada","2":"number_of_german_speakers_2017","3":"910"},{"1":"Nevada","2":"number_of_german_speakers_2018","3":"526"},{"1":"Nevada","2":"number_of_german_speakers_2019","3":"647"},{"1":"New Hampshire","2":"number_of_german_speakers_2017","3":"896"},{"1":"New Hampshire","2":"number_of_german_speakers_2018","3":"109"},{"1":"New Hampshire","2":"number_of_german_speakers_2019","3":"818"},{"1":"New Jersey","2":"number_of_german_speakers_2017","3":"380"},{"1":"New Jersey","2":"number_of_german_speakers_2018","3":"961"},{"1":"New Jersey","2":"number_of_german_speakers_2019","3":"168"},{"1":"New Mexico","2":"number_of_german_speakers_2017","3":"574"},{"1":"New Mexico","2":"number_of_german_speakers_2018","3":"771"},{"1":"New Mexico","2":"number_of_german_speakers_2019","3":"336"},{"1":"New York","2":"number_of_german_speakers_2017","3":"135"},{"1":"New York","2":"number_of_german_speakers_2018","3":"637"},{"1":"New York","2":"number_of_german_speakers_2019","3":"207"},{"1":"North Carolina","2":"number_of_german_speakers_2017","3":"609"},{"1":"North Carolina","2":"number_of_german_speakers_2018","3":"970"},{"1":"North Carolina","2":"number_of_german_speakers_2019","3":"508"},{"1":"North Dakota","2":"number_of_german_speakers_2017","3":"891"},{"1":"North Dakota","2":"number_of_german_speakers_2018","3":"307"},{"1":"North Dakota","2":"number_of_german_speakers_2019","3":"652"},{"1":"Ohio","2":"number_of_german_speakers_2017","3":"960"},{"1":"Ohio","2":"number_of_german_speakers_2018","3":"900"},{"1":"Ohio","2":"number_of_german_speakers_2019","3":"186"},{"1":"Oklahoma","2":"number_of_german_speakers_2017","3":"769"},{"1":"Oklahoma","2":"number_of_german_speakers_2018","3":"699"},{"1":"Oklahoma","2":"number_of_german_speakers_2019","3":"621"},{"1":"Oregon","2":"number_of_german_speakers_2017","3":"864"},{"1":"Oregon","2":"number_of_german_speakers_2018","3":"762"},{"1":"Oregon","2":"number_of_german_speakers_2019","3":"471"},{"1":"Maryland","2":"number_of_german_speakers_2017","3":"247"},{"1":"Maryland","2":"number_of_german_speakers_2018","3":"106"},{"1":"Maryland","2":"number_of_german_speakers_2019","3":"507"},{"1":"Massachusetts","2":"number_of_german_speakers_2017","3":"775"},{"1":"Massachusetts","2":"number_of_german_speakers_2018","3":"507"},{"1":"Massachusetts","2":"number_of_german_speakers_2019","3":"859"},{"1":"Michigan","2":"number_of_german_speakers_2017","3":"776"},{"1":"Michigan","2":"number_of_german_speakers_2018","3":"503"},{"1":"Michigan","2":"number_of_german_speakers_2019","3":"467"},{"1":"Minnesota","2":"number_of_german_speakers_2017","3":"752"},{"1":"Minnesota","2":"number_of_german_speakers_2018","3":"344"},{"1":"Minnesota","2":"number_of_german_speakers_2019","3":"251"},{"1":"Mississippi","2":"number_of_german_speakers_2017","3":"945"},{"1":"Mississippi","2":"number_of_german_speakers_2018","3":"246"},{"1":"Mississippi","2":"number_of_german_speakers_2019","3":"372"},{"1":"Missouri","2":"number_of_german_speakers_2017","3":"699"},{"1":"Missouri","2":"number_of_german_speakers_2018","3":"250"},{"1":"Missouri","2":"number_of_german_speakers_2019","3":"972"},{"1":"Pennsylvania","2":"number_of_german_speakers_2017","3":"930"},{"1":"Pennsylvania","2":"number_of_german_speakers_2018","3":"475"},{"1":"Pennsylvania","2":"number_of_german_speakers_2019","3":"956"},{"1":"Rhode Island","2":"number_of_german_speakers_2017","3":"529"},{"1":"Rhode Island","2":"number_of_german_speakers_2018","3":"852"},{"1":"Rhode Island","2":"number_of_german_speakers_2019","3":"900"},{"1":"South Carolina","2":"number_of_german_speakers_2017","3":"617"},{"1":"South Carolina","2":"number_of_german_speakers_2018","3":"815"},{"1":"South Carolina","2":"number_of_german_speakers_2019","3":"182"},{"1":"South Dakota","2":"number_of_german_speakers_2017","3":"262"},{"1":"South Dakota","2":"number_of_german_speakers_2018","3":"601"},{"1":"South Dakota","2":"number_of_german_speakers_2019","3":"584"},{"1":"Tennessee","2":"number_of_german_speakers_2017","3":"153"},{"1":"Tennessee","2":"number_of_german_speakers_2018","3":"568"},{"1":"Tennessee","2":"number_of_german_speakers_2019","3":"476"},{"1":"Texas","2":"number_of_german_speakers_2017","3":"732"},{"1":"Texas","2":"number_of_german_speakers_2018","3":"571"},{"1":"Texas","2":"number_of_german_speakers_2019","3":"292"},{"1":"Utah","2":"number_of_german_speakers_2017","3":"669"},{"1":"Utah","2":"number_of_german_speakers_2018","3":"486"},{"1":"Utah","2":"number_of_german_speakers_2019","3":"758"},{"1":"Vermont","2":"number_of_german_speakers_2017","3":"698"},{"1":"Vermont","2":"number_of_german_speakers_2018","3":"470"},{"1":"Vermont","2":"number_of_german_speakers_2019","3":"158"},{"1":"Virginia","2":"number_of_german_speakers_2017","3":"125"},{"1":"Virginia","2":"number_of_german_speakers_2018","3":"105"},{"1":"Virginia","2":"number_of_german_speakers_2019","3":"432"},{"1":"Washington","2":"number_of_german_speakers_2017","3":"495"},{"1":"Washington","2":"number_of_german_speakers_2018","3":"474"},{"1":"Washington","2":"number_of_german_speakers_2019","3":"615"},{"1":"West Virginia","2":"number_of_german_speakers_2017","3":"603"},{"1":"West Virginia","2":"number_of_german_speakers_2018","3":"600"},{"1":"West Virginia","2":"number_of_german_speakers_2019","3":"974"},{"1":"Wisconsin","2":"number_of_german_speakers_2017","3":"582"},{"1":"Wisconsin","2":"number_of_german_speakers_2018","3":"997"},{"1":"Wisconsin","2":"number_of_german_speakers_2019","3":"842"},{"1":"Wyoming","2":"number_of_german_speakers_2017","3":"451"},{"1":"Wyoming","2":"number_of_german_speakers_2018","3":"593"},{"1":"Wyoming","2":"number_of_german_speakers_2019","3":"431"}],"options":{"columns":{"min":{},"max":[10]},"rows":{"min":[5],"max":[5]},"pages":{}}}
  </script>
</div>

???

The `na_if()` function turns values that represent missing data into actual NAs

You can also do this with na arguments in read_excel etc

---

## `replace_na()`

```r
german_speakers %>% 
  pivot_longer(cols = -state,
               names_to = "year", 
               values_to = "number") %>% 
  mutate(number = na_if(number, "-")) %>% 
* mutate(number = replace_na(number, 0))
```

???

The `replace_na()` function does the opposite: turns NAs into values.

---

## `replace_na()`

<div data-pagedtable="false">
  <script data-pagedtable-source type="application/json">
{"columns":[{"label":["state"],"name":[1],"type":["chr"],"align":["left"]},{"label":["year"],"name":[2],"type":["chr"],"align":["left"]},{"label":["number"],"name":[3],"type":["chr"],"align":["left"]}],"data":[{"1":"Alabama","2":"number_of_german_speakers_2017","3":"426"},{"1":"Alabama","2":"number_of_german_speakers_2018","3":"395"},{"1":"Alabama","2":"number_of_german_speakers_2019","3":"711"},{"1":"Alaska","2":"number_of_german_speakers_2017","3":"331"},{"1":"Alaska","2":"number_of_german_speakers_2018","3":"201"},{"1":"Alaska","2":"number_of_german_speakers_2019","3":"131"},{"1":"Arizona","2":"number_of_german_speakers_2017","3":"636"},{"1":"Arizona","2":"number_of_german_speakers_2018","3":"858"},{"1":"Arizona","2":"number_of_german_speakers_2019","3":"136"},{"1":"Arkansas","2":"number_of_german_speakers_2017","3":"0"},{"1":"Arkansas","2":"number_of_german_speakers_2018","3":"635"},{"1":"Arkansas","2":"number_of_german_speakers_2019","3":"557"},{"1":"California","2":"number_of_german_speakers_2017","3":"440"},{"1":"California","2":"number_of_german_speakers_2018","3":"318"},{"1":"California","2":"number_of_german_speakers_2019","3":"854"},{"1":"Colorado","2":"number_of_german_speakers_2017","3":"251"},{"1":"Colorado","2":"number_of_german_speakers_2018","3":"498"},{"1":"Colorado","2":"number_of_german_speakers_2019","3":"768"},{"1":"Connecticut","2":"number_of_german_speakers_2017","3":"187"},{"1":"Connecticut","2":"number_of_german_speakers_2018","3":"334"},{"1":"Connecticut","2":"number_of_german_speakers_2019","3":"586"},{"1":"Delaware","2":"number_of_german_speakers_2017","3":"0"},{"1":"Delaware","2":"number_of_german_speakers_2018","3":"998"},{"1":"Delaware","2":"number_of_german_speakers_2019","3":"453"},{"1":"District of Columbia","2":"number_of_german_speakers_2017","3":"799"},{"1":"District of Columbia","2":"number_of_german_speakers_2018","3":"745"},{"1":"District of Columbia","2":"number_of_german_speakers_2019","3":"180"},{"1":"Florida","2":"number_of_german_speakers_2017","3":"399"},{"1":"Florida","2":"number_of_german_speakers_2018","3":"924"},{"1":"Florida","2":"number_of_german_speakers_2019","3":"958"},{"1":"Georgia","2":"number_of_german_speakers_2017","3":"910"},{"1":"Georgia","2":"number_of_german_speakers_2018","3":"293"},{"1":"Georgia","2":"number_of_german_speakers_2019","3":"821"},{"1":"Hawaii","2":"number_of_german_speakers_2017","3":"0"},{"1":"Hawaii","2":"number_of_german_speakers_2018","3":"303"},{"1":"Hawaii","2":"number_of_german_speakers_2019","3":"931"},{"1":"Idaho","2":"number_of_german_speakers_2017","3":"301"},{"1":"Idaho","2":"number_of_german_speakers_2018","3":"349"},{"1":"Idaho","2":"number_of_german_speakers_2019","3":"0"},{"1":"Illinois","2":"number_of_german_speakers_2017","3":"156"},{"1":"Illinois","2":"number_of_german_speakers_2018","3":"573"},{"1":"Illinois","2":"number_of_german_speakers_2019","3":"827"},{"1":"Indiana","2":"number_of_german_speakers_2017","3":"469"},{"1":"Indiana","2":"number_of_german_speakers_2018","3":"188"},{"1":"Indiana","2":"number_of_german_speakers_2019","3":"728"},{"1":"Iowa","2":"number_of_german_speakers_2017","3":"816"},{"1":"Iowa","2":"number_of_german_speakers_2018","3":"330"},{"1":"Iowa","2":"number_of_german_speakers_2019","3":"657"},{"1":"Kansas","2":"number_of_german_speakers_2017","3":"328"},{"1":"Kansas","2":"number_of_german_speakers_2018","3":"546"},{"1":"Kansas","2":"number_of_german_speakers_2019","3":"501"},{"1":"Kentucky","2":"number_of_german_speakers_2017","3":"299"},{"1":"Kentucky","2":"number_of_german_speakers_2018","3":"976"},{"1":"Kentucky","2":"number_of_german_speakers_2019","3":"332"},{"1":"Louisiana","2":"number_of_german_speakers_2017","3":"266"},{"1":"Louisiana","2":"number_of_german_speakers_2018","3":"0"},{"1":"Louisiana","2":"number_of_german_speakers_2019","3":"490"},{"1":"Maine","2":"number_of_german_speakers_2017","3":"376"},{"1":"Maine","2":"number_of_german_speakers_2018","3":"127"},{"1":"Maine","2":"number_of_german_speakers_2019","3":"206"},{"1":"Montana","2":"number_of_german_speakers_2017","3":"598"},{"1":"Montana","2":"number_of_german_speakers_2018","3":"296"},{"1":"Montana","2":"number_of_german_speakers_2019","3":"527"},{"1":"Nebraska","2":"number_of_german_speakers_2017","3":"252"},{"1":"Nebraska","2":"number_of_german_speakers_2018","3":"726"},{"1":"Nebraska","2":"number_of_german_speakers_2019","3":"232"},{"1":"Nevada","2":"number_of_german_speakers_2017","3":"910"},{"1":"Nevada","2":"number_of_german_speakers_2018","3":"526"},{"1":"Nevada","2":"number_of_german_speakers_2019","3":"647"},{"1":"New Hampshire","2":"number_of_german_speakers_2017","3":"896"},{"1":"New Hampshire","2":"number_of_german_speakers_2018","3":"109"},{"1":"New Hampshire","2":"number_of_german_speakers_2019","3":"818"},{"1":"New Jersey","2":"number_of_german_speakers_2017","3":"380"},{"1":"New Jersey","2":"number_of_german_speakers_2018","3":"961"},{"1":"New Jersey","2":"number_of_german_speakers_2019","3":"168"},{"1":"New Mexico","2":"number_of_german_speakers_2017","3":"574"},{"1":"New Mexico","2":"number_of_german_speakers_2018","3":"771"},{"1":"New Mexico","2":"number_of_german_speakers_2019","3":"336"},{"1":"New York","2":"number_of_german_speakers_2017","3":"135"},{"1":"New York","2":"number_of_german_speakers_2018","3":"637"},{"1":"New York","2":"number_of_german_speakers_2019","3":"207"},{"1":"North Carolina","2":"number_of_german_speakers_2017","3":"609"},{"1":"North Carolina","2":"number_of_german_speakers_2018","3":"970"},{"1":"North Carolina","2":"number_of_german_speakers_2019","3":"508"},{"1":"North Dakota","2":"number_of_german_speakers_2017","3":"891"},{"1":"North Dakota","2":"number_of_german_speakers_2018","3":"307"},{"1":"North Dakota","2":"number_of_german_speakers_2019","3":"652"},{"1":"Ohio","2":"number_of_german_speakers_2017","3":"960"},{"1":"Ohio","2":"number_of_german_speakers_2018","3":"900"},{"1":"Ohio","2":"number_of_german_speakers_2019","3":"186"},{"1":"Oklahoma","2":"number_of_german_speakers_2017","3":"769"},{"1":"Oklahoma","2":"number_of_german_speakers_2018","3":"699"},{"1":"Oklahoma","2":"number_of_german_speakers_2019","3":"621"},{"1":"Oregon","2":"number_of_german_speakers_2017","3":"864"},{"1":"Oregon","2":"number_of_german_speakers_2018","3":"762"},{"1":"Oregon","2":"number_of_german_speakers_2019","3":"471"},{"1":"Maryland","2":"number_of_german_speakers_2017","3":"247"},{"1":"Maryland","2":"number_of_german_speakers_2018","3":"106"},{"1":"Maryland","2":"number_of_german_speakers_2019","3":"507"},{"1":"Massachusetts","2":"number_of_german_speakers_2017","3":"775"},{"1":"Massachusetts","2":"number_of_german_speakers_2018","3":"507"},{"1":"Massachusetts","2":"number_of_german_speakers_2019","3":"859"},{"1":"Michigan","2":"number_of_german_speakers_2017","3":"776"},{"1":"Michigan","2":"number_of_german_speakers_2018","3":"503"},{"1":"Michigan","2":"number_of_german_speakers_2019","3":"467"},{"1":"Minnesota","2":"number_of_german_speakers_2017","3":"752"},{"1":"Minnesota","2":"number_of_german_speakers_2018","3":"344"},{"1":"Minnesota","2":"number_of_german_speakers_2019","3":"251"},{"1":"Mississippi","2":"number_of_german_speakers_2017","3":"945"},{"1":"Mississippi","2":"number_of_german_speakers_2018","3":"246"},{"1":"Mississippi","2":"number_of_german_speakers_2019","3":"372"},{"1":"Missouri","2":"number_of_german_speakers_2017","3":"699"},{"1":"Missouri","2":"number_of_german_speakers_2018","3":"250"},{"1":"Missouri","2":"number_of_german_speakers_2019","3":"972"},{"1":"Pennsylvania","2":"number_of_german_speakers_2017","3":"930"},{"1":"Pennsylvania","2":"number_of_german_speakers_2018","3":"475"},{"1":"Pennsylvania","2":"number_of_german_speakers_2019","3":"956"},{"1":"Rhode Island","2":"number_of_german_speakers_2017","3":"529"},{"1":"Rhode Island","2":"number_of_german_speakers_2018","3":"852"},{"1":"Rhode Island","2":"number_of_german_speakers_2019","3":"900"},{"1":"South Carolina","2":"number_of_german_speakers_2017","3":"617"},{"1":"South Carolina","2":"number_of_german_speakers_2018","3":"815"},{"1":"South Carolina","2":"number_of_german_speakers_2019","3":"182"},{"1":"South Dakota","2":"number_of_german_speakers_2017","3":"262"},{"1":"South Dakota","2":"number_of_german_speakers_2018","3":"601"},{"1":"South Dakota","2":"number_of_german_speakers_2019","3":"584"},{"1":"Tennessee","2":"number_of_german_speakers_2017","3":"153"},{"1":"Tennessee","2":"number_of_german_speakers_2018","3":"568"},{"1":"Tennessee","2":"number_of_german_speakers_2019","3":"476"},{"1":"Texas","2":"number_of_german_speakers_2017","3":"732"},{"1":"Texas","2":"number_of_german_speakers_2018","3":"571"},{"1":"Texas","2":"number_of_german_speakers_2019","3":"292"},{"1":"Utah","2":"number_of_german_speakers_2017","3":"669"},{"1":"Utah","2":"number_of_german_speakers_2018","3":"486"},{"1":"Utah","2":"number_of_german_speakers_2019","3":"758"},{"1":"Vermont","2":"number_of_german_speakers_2017","3":"698"},{"1":"Vermont","2":"number_of_german_speakers_2018","3":"470"},{"1":"Vermont","2":"number_of_german_speakers_2019","3":"158"},{"1":"Virginia","2":"number_of_german_speakers_2017","3":"125"},{"1":"Virginia","2":"number_of_german_speakers_2018","3":"105"},{"1":"Virginia","2":"number_of_german_speakers_2019","3":"432"},{"1":"Washington","2":"number_of_german_speakers_2017","3":"495"},{"1":"Washington","2":"number_of_german_speakers_2018","3":"474"},{"1":"Washington","2":"number_of_german_speakers_2019","3":"615"},{"1":"West Virginia","2":"number_of_german_speakers_2017","3":"603"},{"1":"West Virginia","2":"number_of_german_speakers_2018","3":"600"},{"1":"West Virginia","2":"number_of_german_speakers_2019","3":"974"},{"1":"Wisconsin","2":"number_of_german_speakers_2017","3":"582"},{"1":"Wisconsin","2":"number_of_german_speakers_2018","3":"997"},{"1":"Wisconsin","2":"number_of_german_speakers_2019","3":"842"},{"1":"Wyoming","2":"number_of_german_speakers_2017","3":"451"},{"1":"Wyoming","2":"number_of_german_speakers_2018","3":"593"},{"1":"Wyoming","2":"number_of_german_speakers_2019","3":"431"}],"options":{"columns":{"min":{},"max":[10]},"rows":{"min":[5],"max":[5]},"pages":{}}}
  </script>
</div>

---

## My Turn

I'll convert all of the missing values in the `number_proficient` variable to NA using `na_if()`

*I don't have any values where using `replace_na()` makes sense, but you'll use it shortly!*

---

## Your Turn

1. Convert all of the missing values in the `number_of_students` variable to NA using `na_if()`

1. Convert all of the NA values you just made to 0 using `replace_na()`.

---

background-image:url("images/types.jpg")

# Changing Variable Types

---

## Changing Variable Types

<div data-pagedtable="false">
  <script data-pagedtable-source type="application/json">
{"columns":[{"label":["state"],"name":[1],"type":["chr"],"align":["left"]},{"label":["year"],"name":[2],"type":["chr"],"align":["left"]},{"label":["number"],"name":[3],"type":["chr"],"align":["left"]}],"data":[{"1":"Alabama","2":"number_of_german_speakers_2017","3":"426"},{"1":"Alabama","2":"number_of_german_speakers_2018","3":"395"},{"1":"Alabama","2":"number_of_german_speakers_2019","3":"711"},{"1":"Alaska","2":"number_of_german_speakers_2017","3":"331"},{"1":"Alaska","2":"number_of_german_speakers_2018","3":"201"},{"1":"Alaska","2":"number_of_german_speakers_2019","3":"131"},{"1":"Arizona","2":"number_of_german_speakers_2017","3":"636"},{"1":"Arizona","2":"number_of_german_speakers_2018","3":"858"},{"1":"Arizona","2":"number_of_german_speakers_2019","3":"136"},{"1":"Arkansas","2":"number_of_german_speakers_2017","3":"0"},{"1":"Arkansas","2":"number_of_german_speakers_2018","3":"635"},{"1":"Arkansas","2":"number_of_german_speakers_2019","3":"557"},{"1":"California","2":"number_of_german_speakers_2017","3":"440"},{"1":"California","2":"number_of_german_speakers_2018","3":"318"},{"1":"California","2":"number_of_german_speakers_2019","3":"854"},{"1":"Colorado","2":"number_of_german_speakers_2017","3":"251"},{"1":"Colorado","2":"number_of_german_speakers_2018","3":"498"},{"1":"Colorado","2":"number_of_german_speakers_2019","3":"768"},{"1":"Connecticut","2":"number_of_german_speakers_2017","3":"187"},{"1":"Connecticut","2":"number_of_german_speakers_2018","3":"334"},{"1":"Connecticut","2":"number_of_german_speakers_2019","3":"586"},{"1":"Delaware","2":"number_of_german_speakers_2017","3":"0"},{"1":"Delaware","2":"number_of_german_speakers_2018","3":"998"},{"1":"Delaware","2":"number_of_german_speakers_2019","3":"453"},{"1":"District of Columbia","2":"number_of_german_speakers_2017","3":"799"},{"1":"District of Columbia","2":"number_of_german_speakers_2018","3":"745"},{"1":"District of Columbia","2":"number_of_german_speakers_2019","3":"180"},{"1":"Florida","2":"number_of_german_speakers_2017","3":"399"},{"1":"Florida","2":"number_of_german_speakers_2018","3":"924"},{"1":"Florida","2":"number_of_german_speakers_2019","3":"958"},{"1":"Georgia","2":"number_of_german_speakers_2017","3":"910"},{"1":"Georgia","2":"number_of_german_speakers_2018","3":"293"},{"1":"Georgia","2":"number_of_german_speakers_2019","3":"821"},{"1":"Hawaii","2":"number_of_german_speakers_2017","3":"0"},{"1":"Hawaii","2":"number_of_german_speakers_2018","3":"303"},{"1":"Hawaii","2":"number_of_german_speakers_2019","3":"931"},{"1":"Idaho","2":"number_of_german_speakers_2017","3":"301"},{"1":"Idaho","2":"number_of_german_speakers_2018","3":"349"},{"1":"Idaho","2":"number_of_german_speakers_2019","3":"0"},{"1":"Illinois","2":"number_of_german_speakers_2017","3":"156"},{"1":"Illinois","2":"number_of_german_speakers_2018","3":"573"},{"1":"Illinois","2":"number_of_german_speakers_2019","3":"827"},{"1":"Indiana","2":"number_of_german_speakers_2017","3":"469"},{"1":"Indiana","2":"number_of_german_speakers_2018","3":"188"},{"1":"Indiana","2":"number_of_german_speakers_2019","3":"728"},{"1":"Iowa","2":"number_of_german_speakers_2017","3":"816"},{"1":"Iowa","2":"number_of_german_speakers_2018","3":"330"},{"1":"Iowa","2":"number_of_german_speakers_2019","3":"657"},{"1":"Kansas","2":"number_of_german_speakers_2017","3":"328"},{"1":"Kansas","2":"number_of_german_speakers_2018","3":"546"},{"1":"Kansas","2":"number_of_german_speakers_2019","3":"501"},{"1":"Kentucky","2":"number_of_german_speakers_2017","3":"299"},{"1":"Kentucky","2":"number_of_german_speakers_2018","3":"976"},{"1":"Kentucky","2":"number_of_german_speakers_2019","3":"332"},{"1":"Louisiana","2":"number_of_german_speakers_2017","3":"266"},{"1":"Louisiana","2":"number_of_german_speakers_2018","3":"0"},{"1":"Louisiana","2":"number_of_german_speakers_2019","3":"490"},{"1":"Maine","2":"number_of_german_speakers_2017","3":"376"},{"1":"Maine","2":"number_of_german_speakers_2018","3":"127"},{"1":"Maine","2":"number_of_german_speakers_2019","3":"206"},{"1":"Montana","2":"number_of_german_speakers_2017","3":"598"},{"1":"Montana","2":"number_of_german_speakers_2018","3":"296"},{"1":"Montana","2":"number_of_german_speakers_2019","3":"527"},{"1":"Nebraska","2":"number_of_german_speakers_2017","3":"252"},{"1":"Nebraska","2":"number_of_german_speakers_2018","3":"726"},{"1":"Nebraska","2":"number_of_german_speakers_2019","3":"232"},{"1":"Nevada","2":"number_of_german_speakers_2017","3":"910"},{"1":"Nevada","2":"number_of_german_speakers_2018","3":"526"},{"1":"Nevada","2":"number_of_german_speakers_2019","3":"647"},{"1":"New Hampshire","2":"number_of_german_speakers_2017","3":"896"},{"1":"New Hampshire","2":"number_of_german_speakers_2018","3":"109"},{"1":"New Hampshire","2":"number_of_german_speakers_2019","3":"818"},{"1":"New Jersey","2":"number_of_german_speakers_2017","3":"380"},{"1":"New Jersey","2":"number_of_german_speakers_2018","3":"961"},{"1":"New Jersey","2":"number_of_german_speakers_2019","3":"168"},{"1":"New Mexico","2":"number_of_german_speakers_2017","3":"574"},{"1":"New Mexico","2":"number_of_german_speakers_2018","3":"771"},{"1":"New Mexico","2":"number_of_german_speakers_2019","3":"336"},{"1":"New York","2":"number_of_german_speakers_2017","3":"135"},{"1":"New York","2":"number_of_german_speakers_2018","3":"637"},{"1":"New York","2":"number_of_german_speakers_2019","3":"207"},{"1":"North Carolina","2":"number_of_german_speakers_2017","3":"609"},{"1":"North Carolina","2":"number_of_german_speakers_2018","3":"970"},{"1":"North Carolina","2":"number_of_german_speakers_2019","3":"508"},{"1":"North Dakota","2":"number_of_german_speakers_2017","3":"891"},{"1":"North Dakota","2":"number_of_german_speakers_2018","3":"307"},{"1":"North Dakota","2":"number_of_german_speakers_2019","3":"652"},{"1":"Ohio","2":"number_of_german_speakers_2017","3":"960"},{"1":"Ohio","2":"number_of_german_speakers_2018","3":"900"},{"1":"Ohio","2":"number_of_german_speakers_2019","3":"186"},{"1":"Oklahoma","2":"number_of_german_speakers_2017","3":"769"},{"1":"Oklahoma","2":"number_of_german_speakers_2018","3":"699"},{"1":"Oklahoma","2":"number_of_german_speakers_2019","3":"621"},{"1":"Oregon","2":"number_of_german_speakers_2017","3":"864"},{"1":"Oregon","2":"number_of_german_speakers_2018","3":"762"},{"1":"Oregon","2":"number_of_german_speakers_2019","3":"471"},{"1":"Maryland","2":"number_of_german_speakers_2017","3":"247"},{"1":"Maryland","2":"number_of_german_speakers_2018","3":"106"},{"1":"Maryland","2":"number_of_german_speakers_2019","3":"507"},{"1":"Massachusetts","2":"number_of_german_speakers_2017","3":"775"},{"1":"Massachusetts","2":"number_of_german_speakers_2018","3":"507"},{"1":"Massachusetts","2":"number_of_german_speakers_2019","3":"859"},{"1":"Michigan","2":"number_of_german_speakers_2017","3":"776"},{"1":"Michigan","2":"number_of_german_speakers_2018","3":"503"},{"1":"Michigan","2":"number_of_german_speakers_2019","3":"467"},{"1":"Minnesota","2":"number_of_german_speakers_2017","3":"752"},{"1":"Minnesota","2":"number_of_german_speakers_2018","3":"344"},{"1":"Minnesota","2":"number_of_german_speakers_2019","3":"251"},{"1":"Mississippi","2":"number_of_german_speakers_2017","3":"945"},{"1":"Mississippi","2":"number_of_german_speakers_2018","3":"246"},{"1":"Mississippi","2":"number_of_german_speakers_2019","3":"372"},{"1":"Missouri","2":"number_of_german_speakers_2017","3":"699"},{"1":"Missouri","2":"number_of_german_speakers_2018","3":"250"},{"1":"Missouri","2":"number_of_german_speakers_2019","3":"972"},{"1":"Pennsylvania","2":"number_of_german_speakers_2017","3":"930"},{"1":"Pennsylvania","2":"number_of_german_speakers_2018","3":"475"},{"1":"Pennsylvania","2":"number_of_german_speakers_2019","3":"956"},{"1":"Rhode Island","2":"number_of_german_speakers_2017","3":"529"},{"1":"Rhode Island","2":"number_of_german_speakers_2018","3":"852"},{"1":"Rhode Island","2":"number_of_german_speakers_2019","3":"900"},{"1":"South Carolina","2":"number_of_german_speakers_2017","3":"617"},{"1":"South Carolina","2":"number_of_german_speakers_2018","3":"815"},{"1":"South Carolina","2":"number_of_german_speakers_2019","3":"182"},{"1":"South Dakota","2":"number_of_german_speakers_2017","3":"262"},{"1":"South Dakota","2":"number_of_german_speakers_2018","3":"601"},{"1":"South Dakota","2":"number_of_german_speakers_2019","3":"584"},{"1":"Tennessee","2":"number_of_german_speakers_2017","3":"153"},{"1":"Tennessee","2":"number_of_german_speakers_2018","3":"568"},{"1":"Tennessee","2":"number_of_german_speakers_2019","3":"476"},{"1":"Texas","2":"number_of_german_speakers_2017","3":"732"},{"1":"Texas","2":"number_of_german_speakers_2018","3":"571"},{"1":"Texas","2":"number_of_german_speakers_2019","3":"292"},{"1":"Utah","2":"number_of_german_speakers_2017","3":"669"},{"1":"Utah","2":"number_of_german_speakers_2018","3":"486"},{"1":"Utah","2":"number_of_german_speakers_2019","3":"758"},{"1":"Vermont","2":"number_of_german_speakers_2017","3":"698"},{"1":"Vermont","2":"number_of_german_speakers_2018","3":"470"},{"1":"Vermont","2":"number_of_german_speakers_2019","3":"158"},{"1":"Virginia","2":"number_of_german_speakers_2017","3":"125"},{"1":"Virginia","2":"number_of_german_speakers_2018","3":"105"},{"1":"Virginia","2":"number_of_german_speakers_2019","3":"432"},{"1":"Washington","2":"number_of_german_speakers_2017","3":"495"},{"1":"Washington","2":"number_of_german_speakers_2018","3":"474"},{"1":"Washington","2":"number_of_german_speakers_2019","3":"615"},{"1":"West Virginia","2":"number_of_german_speakers_2017","3":"603"},{"1":"West Virginia","2":"number_of_german_speakers_2018","3":"600"},{"1":"West Virginia","2":"number_of_german_speakers_2019","3":"974"},{"1":"Wisconsin","2":"number_of_german_speakers_2017","3":"582"},{"1":"Wisconsin","2":"number_of_german_speakers_2018","3":"997"},{"1":"Wisconsin","2":"number_of_german_speakers_2019","3":"842"},{"1":"Wyoming","2":"number_of_german_speakers_2017","3":"451"},{"1":"Wyoming","2":"number_of_german_speakers_2018","3":"593"},{"1":"Wyoming","2":"number_of_german_speakers_2019","3":"431"}],"options":{"columns":{"min":{},"max":[10]},"rows":{"min":[5],"max":[5]},"pages":{}}}
  </script>
</div>

---

## Changing Variable Types

```
## Error: `summarise()` argument `total` errored.
## ℹ `total` is `sum(number)`.
## x invalid 'type' (character) of argument
```

---

## Changing Variable Types

To change variable types, you use the `mutate()` function combined with `as.numeric()`, `as.character()`, etc.

<div data-pagedtable="false">
  <script data-pagedtable-source type="application/json">
{"columns":[{"label":["total"],"name":[1],"type":["dbl"],"align":["right"]}],"data":[{"1":"80314"}],"options":{"columns":{"min":{},"max":[10]},"rows":{"min":[5],"max":[5]},"pages":{}}}
  </script>
</div>

???

Point to https://github.com/davidsjoberg/hablar

---

## My Turn

1. Convert the `number_proficient` variable to numeric by using `as.numeric()`

1. Count the number of students at each proficiency level

---

## Your Turn

1. Convert the `number_of_students` variable to numeric by using `as.numeric()`

1. Make sure you can use your `number_of_students` variable to count the total number of students in Oregon

---

background-image:url("images/throwing-pot.jpg")

# Advanced Variable Creation

---

## `recode()`

```r
data_frame %>% 
  mutate(variable = recode(variable, "old_value" = "new_value"))
```

---

## `recode()`

```r
german_speakers %>% 
  pivot_longer(cols = -state,
               names_to = "year", 
               values_to = "number") %>% 
  mutate(number = na_if(number, "-")) %>% 
  mutate(number = replace_na(number, 0)) %>% 
  mutate(number = as.numeric(number)) %>% 
  mutate(year = recode(year, 
*                      "number_of_german_speakers_2017" = "2017",
*                      "number_of_german_speakers_2018" = "2018",
*                      "number_of_german_speakers_2019" = "2019"))
```

---

## `recode()`

---

## `if_else()`

```r
data_frame %>% 
  mutate(variable = if_else(variable == "some_value", 
                            "if_true_value", 
                            "else_value"))
```

---

## `if_else()`

```r
german_speakers %>% 
  pivot_longer(cols = -state,
               names_to = "year", 
               values_to = "number") %>% 
  mutate(number = na_if(number, "-")) %>% 
  mutate(number = replace_na(number, 0)) %>% 
  mutate(number = as.numeric(number)) %>% 
* mutate(year = if_else(year == "number_of_german_speakers_2017", "2017", year)) %>%
* mutate(year = if_else(year == "number_of_german_speakers_2018", "2018", year)) %>%
* mutate(year = if_else(year == "number_of_german_speakers_2019", "2019", year))
```

---

## `if_else()`

---

## `str_remove()`

```r
data_frame %>% 
  mutate(variable = str_remove(variable, "text to remove"))
```

---

## `str_remove()`

???

Mention there are a whole bunch of other stringr functions

---

## `str_remove()`

---

![](images/parse_number.png)

Image courtesy of [Allison Horst](https://github.com/allisonhorst/stats-illustrations)

---

## `parse_number()`

```r
data_frame %>% 
  mutate(variable = parse_number(variable))
```

---

## `parse_number()`

---

## `parse_number()`

---

## `case_when()`

```r
data_frame %>% 
  mutate(variable = case_when(
    variable == "some_value" ~ "new_value",
    variable == "some_other_value" ~ "new_value_2",
    variable == "some_third_value" ~ "new_value_3",
    TRUE ~ "value_for_all_observations_that_dont_match_any_above_criteria"
  ))
```

---

## `case_when()`

```r
german_speakers %>% 
  pivot_longer(cols = -state,
               names_to = "year", 
               values_to = "number") %>% 
  mutate(number = na_if(number, "-")) %>% 
  mutate(number = replace_na(number, 0)) %>% 
  mutate(number = as.numeric(number)) %>% 
* mutate(year = case_when(
*   year == "number_of_german_speakers_2017" ~ "2017",
*   year == "number_of_german_speakers_2018" ~ "2018",
*   year == "number_of_german_speakers_2019" ~ "2019"
* ))
```

---

## `case_when()`

---

## More complicated `case_when()`

```r
german_speakers %>% 
  pivot_longer(cols = -state,
               names_to = "year", 
               values_to = "number") %>% 
  mutate(number = na_if(number, "-")) %>% 
  mutate(number = replace_na(number, 0)) %>% 
  mutate(number = as.numeric(number)) %>% 
  mutate(year = parse_number(year)) %>% 
* mutate(number_categorical = case_when(
*   number < 500 ~ "Less than 500",
*   between(number, 500, 1000) ~ "Between 500 and 1000",
*   number > 1000 ~ "Greater than 1000"
* ))
```

---

## More complicated `case_when()`

<div data-pagedtable="false">
  <script data-pagedtable-source type="application/json">
{"columns":[{"label":["state"],"name":[1],"type":["chr"],"align":["left"]},{"label":["year"],"name":[2],"type":["dbl"],"align":["right"]},{"label":["number"],"name":[3],"type":["dbl"],"align":["right"]},{"label":["number_categorical"],"name":[4],"type":["chr"],"align":["left"]}],"data":[{"1":"Alabama","2":"2017","3":"426","4":"Less than 500"},{"1":"Alabama","2":"2018","3":"395","4":"Less than 500"},{"1":"Alabama","2":"2019","3":"711","4":"Between 500 and 1000"},{"1":"Alaska","2":"2017","3":"331","4":"Less than 500"},{"1":"Alaska","2":"2018","3":"201","4":"Less than 500"},{"1":"Alaska","2":"2019","3":"131","4":"Less than 500"},{"1":"Arizona","2":"2017","3":"636","4":"Between 500 and 1000"},{"1":"Arizona","2":"2018","3":"858","4":"Between 500 and 1000"},{"1":"Arizona","2":"2019","3":"136","4":"Less than 500"},{"1":"Arkansas","2":"2017","3":"0","4":"Less than 500"},{"1":"Arkansas","2":"2018","3":"635","4":"Between 500 and 1000"},{"1":"Arkansas","2":"2019","3":"557","4":"Between 500 and 1000"},{"1":"California","2":"2017","3":"440","4":"Less than 500"},{"1":"California","2":"2018","3":"318","4":"Less than 500"},{"1":"California","2":"2019","3":"854","4":"Between 500 and 1000"},{"1":"Colorado","2":"2017","3":"251","4":"Less than 500"},{"1":"Colorado","2":"2018","3":"498","4":"Less than 500"},{"1":"Colorado","2":"2019","3":"768","4":"Between 500 and 1000"},{"1":"Connecticut","2":"2017","3":"187","4":"Less than 500"},{"1":"Connecticut","2":"2018","3":"334","4":"Less than 500"},{"1":"Connecticut","2":"2019","3":"586","4":"Between 500 and 1000"},{"1":"Delaware","2":"2017","3":"0","4":"Less than 500"},{"1":"Delaware","2":"2018","3":"998","4":"Between 500 and 1000"},{"1":"Delaware","2":"2019","3":"453","4":"Less than 500"},{"1":"District of Columbia","2":"2017","3":"799","4":"Between 500 and 1000"},{"1":"District of Columbia","2":"2018","3":"745","4":"Between 500 and 1000"},{"1":"District of Columbia","2":"2019","3":"180","4":"Less than 500"},{"1":"Florida","2":"2017","3":"399","4":"Less than 500"},{"1":"Florida","2":"2018","3":"924","4":"Between 500 and 1000"},{"1":"Florida","2":"2019","3":"958","4":"Between 500 and 1000"},{"1":"Georgia","2":"2017","3":"910","4":"Between 500 and 1000"},{"1":"Georgia","2":"2018","3":"293","4":"Less than 500"},{"1":"Georgia","2":"2019","3":"821","4":"Between 500 and 1000"},{"1":"Hawaii","2":"2017","3":"0","4":"Less than 500"},{"1":"Hawaii","2":"2018","3":"303","4":"Less than 500"},{"1":"Hawaii","2":"2019","3":"931","4":"Between 500 and 1000"},{"1":"Idaho","2":"2017","3":"301","4":"Less than 500"},{"1":"Idaho","2":"2018","3":"349","4":"Less than 500"},{"1":"Idaho","2":"2019","3":"0","4":"Less than 500"},{"1":"Illinois","2":"2017","3":"156","4":"Less than 500"},{"1":"Illinois","2":"2018","3":"573","4":"Between 500 and 1000"},{"1":"Illinois","2":"2019","3":"827","4":"Between 500 and 1000"},{"1":"Indiana","2":"2017","3":"469","4":"Less than 500"},{"1":"Indiana","2":"2018","3":"188","4":"Less than 500"},{"1":"Indiana","2":"2019","3":"728","4":"Between 500 and 1000"},{"1":"Iowa","2":"2017","3":"816","4":"Between 500 and 1000"},{"1":"Iowa","2":"2018","3":"330","4":"Less than 500"},{"1":"Iowa","2":"2019","3":"657","4":"Between 500 and 1000"},{"1":"Kansas","2":"2017","3":"328","4":"Less than 500"},{"1":"Kansas","2":"2018","3":"546","4":"Between 500 and 1000"},{"1":"Kansas","2":"2019","3":"501","4":"Between 500 and 1000"},{"1":"Kentucky","2":"2017","3":"299","4":"Less than 500"},{"1":"Kentucky","2":"2018","3":"976","4":"Between 500 and 1000"},{"1":"Kentucky","2":"2019","3":"332","4":"Less than 500"},{"1":"Louisiana","2":"2017","3":"266","4":"Less than 500"},{"1":"Louisiana","2":"2018","3":"0","4":"Less than 500"},{"1":"Louisiana","2":"2019","3":"490","4":"Less than 500"},{"1":"Maine","2":"2017","3":"376","4":"Less than 500"},{"1":"Maine","2":"2018","3":"127","4":"Less than 500"},{"1":"Maine","2":"2019","3":"206","4":"Less than 500"},{"1":"Montana","2":"2017","3":"598","4":"Between 500 and 1000"},{"1":"Montana","2":"2018","3":"296","4":"Less than 500"},{"1":"Montana","2":"2019","3":"527","4":"Between 500 and 1000"},{"1":"Nebraska","2":"2017","3":"252","4":"Less than 500"},{"1":"Nebraska","2":"2018","3":"726","4":"Between 500 and 1000"},{"1":"Nebraska","2":"2019","3":"232","4":"Less than 500"},{"1":"Nevada","2":"2017","3":"910","4":"Between 500 and 1000"},{"1":"Nevada","2":"2018","3":"526","4":"Between 500 and 1000"},{"1":"Nevada","2":"2019","3":"647","4":"Between 500 and 1000"},{"1":"New Hampshire","2":"2017","3":"896","4":"Between 500 and 1000"},{"1":"New Hampshire","2":"2018","3":"109","4":"Less than 500"},{"1":"New Hampshire","2":"2019","3":"818","4":"Between 500 and 1000"},{"1":"New Jersey","2":"2017","3":"380","4":"Less than 500"},{"1":"New Jersey","2":"2018","3":"961","4":"Between 500 and 1000"},{"1":"New Jersey","2":"2019","3":"168","4":"Less than 500"},{"1":"New Mexico","2":"2017","3":"574","4":"Between 500 and 1000"},{"1":"New Mexico","2":"2018","3":"771","4":"Between 500 and 1000"},{"1":"New Mexico","2":"2019","3":"336","4":"Less than 500"},{"1":"New York","2":"2017","3":"135","4":"Less than 500"},{"1":"New York","2":"2018","3":"637","4":"Between 500 and 1000"},{"1":"New York","2":"2019","3":"207","4":"Less than 500"},{"1":"North Carolina","2":"2017","3":"609","4":"Between 500 and 1000"},{"1":"North Carolina","2":"2018","3":"970","4":"Between 500 and 1000"},{"1":"North Carolina","2":"2019","3":"508","4":"Between 500 and 1000"},{"1":"North Dakota","2":"2017","3":"891","4":"Between 500 and 1000"},{"1":"North Dakota","2":"2018","3":"307","4":"Less than 500"},{"1":"North Dakota","2":"2019","3":"652","4":"Between 500 and 1000"},{"1":"Ohio","2":"2017","3":"960","4":"Between 500 and 1000"},{"1":"Ohio","2":"2018","3":"900","4":"Between 500 and 1000"},{"1":"Ohio","2":"2019","3":"186","4":"Less than 500"},{"1":"Oklahoma","2":"2017","3":"769","4":"Between 500 and 1000"},{"1":"Oklahoma","2":"2018","3":"699","4":"Between 500 and 1000"},{"1":"Oklahoma","2":"2019","3":"621","4":"Between 500 and 1000"},{"1":"Oregon","2":"2017","3":"864","4":"Between 500 and 1000"},{"1":"Oregon","2":"2018","3":"762","4":"Between 500 and 1000"},{"1":"Oregon","2":"2019","3":"471","4":"Less than 500"},{"1":"Maryland","2":"2017","3":"247","4":"Less than 500"},{"1":"Maryland","2":"2018","3":"106","4":"Less than 500"},{"1":"Maryland","2":"2019","3":"507","4":"Between 500 and 1000"},{"1":"Massachusetts","2":"2017","3":"775","4":"Between 500 and 1000"},{"1":"Massachusetts","2":"2018","3":"507","4":"Between 500 and 1000"},{"1":"Massachusetts","2":"2019","3":"859","4":"Between 500 and 1000"},{"1":"Michigan","2":"2017","3":"776","4":"Between 500 and 1000"},{"1":"Michigan","2":"2018","3":"503","4":"Between 500 and 1000"},{"1":"Michigan","2":"2019","3":"467","4":"Less than 500"},{"1":"Minnesota","2":"2017","3":"752","4":"Between 500 and 1000"},{"1":"Minnesota","2":"2018","3":"344","4":"Less than 500"},{"1":"Minnesota","2":"2019","3":"251","4":"Less than 500"},{"1":"Mississippi","2":"2017","3":"945","4":"Between 500 and 1000"},{"1":"Mississippi","2":"2018","3":"246","4":"Less than 500"},{"1":"Mississippi","2":"2019","3":"372","4":"Less than 500"},{"1":"Missouri","2":"2017","3":"699","4":"Between 500 and 1000"},{"1":"Missouri","2":"2018","3":"250","4":"Less than 500"},{"1":"Missouri","2":"2019","3":"972","4":"Between 500 and 1000"},{"1":"Pennsylvania","2":"2017","3":"930","4":"Between 500 and 1000"},{"1":"Pennsylvania","2":"2018","3":"475","4":"Less than 500"},{"1":"Pennsylvania","2":"2019","3":"956","4":"Between 500 and 1000"},{"1":"Rhode Island","2":"2017","3":"529","4":"Between 500 and 1000"},{"1":"Rhode Island","2":"2018","3":"852","4":"Between 500 and 1000"},{"1":"Rhode Island","2":"2019","3":"900","4":"Between 500 and 1000"},{"1":"South Carolina","2":"2017","3":"617","4":"Between 500 and 1000"},{"1":"South Carolina","2":"2018","3":"815","4":"Between 500 and 1000"},{"1":"South Carolina","2":"2019","3":"182","4":"Less than 500"},{"1":"South Dakota","2":"2017","3":"262","4":"Less than 500"},{"1":"South Dakota","2":"2018","3":"601","4":"Between 500 and 1000"},{"1":"South Dakota","2":"2019","3":"584","4":"Between 500 and 1000"},{"1":"Tennessee","2":"2017","3":"153","4":"Less than 500"},{"1":"Tennessee","2":"2018","3":"568","4":"Between 500 and 1000"},{"1":"Tennessee","2":"2019","3":"476","4":"Less than 500"},{"1":"Texas","2":"2017","3":"732","4":"Between 500 and 1000"},{"1":"Texas","2":"2018","3":"571","4":"Between 500 and 1000"},{"1":"Texas","2":"2019","3":"292","4":"Less than 500"},{"1":"Utah","2":"2017","3":"669","4":"Between 500 and 1000"},{"1":"Utah","2":"2018","3":"486","4":"Less than 500"},{"1":"Utah","2":"2019","3":"758","4":"Between 500 and 1000"},{"1":"Vermont","2":"2017","3":"698","4":"Between 500 and 1000"},{"1":"Vermont","2":"2018","3":"470","4":"Less than 500"},{"1":"Vermont","2":"2019","3":"158","4":"Less than 500"},{"1":"Virginia","2":"2017","3":"125","4":"Less than 500"},{"1":"Virginia","2":"2018","3":"105","4":"Less than 500"},{"1":"Virginia","2":"2019","3":"432","4":"Less than 500"},{"1":"Washington","2":"2017","3":"495","4":"Less than 500"},{"1":"Washington","2":"2018","3":"474","4":"Less than 500"},{"1":"Washington","2":"2019","3":"615","4":"Between 500 and 1000"},{"1":"West Virginia","2":"2017","3":"603","4":"Between 500 and 1000"},{"1":"West Virginia","2":"2018","3":"600","4":"Between 500 and 1000"},{"1":"West Virginia","2":"2019","3":"974","4":"Between 500 and 1000"},{"1":"Wisconsin","2":"2017","3":"582","4":"Between 500 and 1000"},{"1":"Wisconsin","2":"2018","3":"997","4":"Between 500 and 1000"},{"1":"Wisconsin","2":"2019","3":"842","4":"Between 500 and 1000"},{"1":"Wyoming","2":"2017","3":"451","4":"Less than 500"},{"1":"Wyoming","2":"2018","3":"593","4":"Between 500 and 1000"},{"1":"Wyoming","2":"2019","3":"431","4":"Less than 500"}],"options":{"columns":{"min":{},"max":[10]},"rows":{"min":[5],"max":[5]},"pages":{}}}
  </script>
</div>

---

## My Turn

I'll convert all instances of the `proficiency_level` variable to more meaningful observations using:

1. `recode()`

1. `if_else()`

1. `str_remove()`

1. `parse_number()`

1. `case_when()`

I'll then use `case_when()` to convert the proficiency level into a dichotomous (i.e. Proficient/Not Proficient).

---

## Your Turn

1. Remove the "x_2018_2019" portion of the `race_ethnicity` variable using `str_remove()`

1. Convert all instances of the `race_ethnicity` variable to more meaningful observations (e.g. turn "american_indian_alaska_native" into "American Indian/Alaskan Native") using any of the following:

- `recode()`
  
--

- `if_else()`

- `case_when()`

---

background-image:url("images/summarize.jpg")

# Advanced Summarizing

---

## `group_by()` + `mutate()`

```r
german_speakers %>% 
  pivot_longer(cols = -state,
               names_to = "year", 
               values_to = "number") %>% 
  mutate(number = na_if(number, "-")) %>% 
  mutate(number = replace_na(number, 0)) %>% 
  mutate(number = as.numeric(number)) %>% 
  mutate(year = parse_number(year)) %>% 
* group_by(year) %>%
* mutate(pct = number / sum(number)) %>%
  arrange(year, state)
```

---

## `group_by()` + `mutate()`

<div data-pagedtable="false">
  <script data-pagedtable-source type="application/json">
{"columns":[{"label":["state"],"name":[1],"type":["chr"],"align":["left"]},{"label":["year"],"name":[2],"type":["dbl"],"align":["right"]},{"label":["number"],"name":[3],"type":["dbl"],"align":["right"]},{"label":["pct"],"name":[4],"type":["dbl"],"align":["right"]}],"data":[{"1":"Alabama","2":"2017","3":"426","4":"0.016217451"},{"1":"Alaska","2":"2017","3":"331","4":"0.012600883"},{"1":"Arizona","2":"2017","3":"636","4":"0.024211969"},{"1":"Arkansas","2":"2017","3":"0","4":"0.000000000"},{"1":"California","2":"2017","3":"440","4":"0.016750419"},{"1":"Colorado","2":"2017","3":"251","4":"0.009555353"},{"1":"Connecticut","2":"2017","3":"187","4":"0.007118928"},{"1":"Delaware","2":"2017","3":"0","4":"0.000000000"},{"1":"District of Columbia","2":"2017","3":"799","4":"0.030417238"},{"1":"Florida","2":"2017","3":"399","4":"0.015189584"},{"1":"Georgia","2":"2017","3":"910","4":"0.034642912"},{"1":"Hawaii","2":"2017","3":"0","4":"0.000000000"},{"1":"Idaho","2":"2017","3":"301","4":"0.011458809"},{"1":"Illinois","2":"2017","3":"156","4":"0.005938785"},{"1":"Indiana","2":"2017","3":"469","4":"0.017854424"},{"1":"Iowa","2":"2017","3":"816","4":"0.031064413"},{"1":"Kansas","2":"2017","3":"328","4":"0.012486676"},{"1":"Kentucky","2":"2017","3":"299","4":"0.011382671"},{"1":"Louisiana","2":"2017","3":"266","4":"0.010126390"},{"1":"Maine","2":"2017","3":"376","4":"0.014313994"},{"1":"Maryland","2":"2017","3":"247","4":"0.009403076"},{"1":"Massachusetts","2":"2017","3":"775","4":"0.029503578"},{"1":"Michigan","2":"2017","3":"776","4":"0.029541648"},{"1":"Minnesota","2":"2017","3":"752","4":"0.028627988"},{"1":"Mississippi","2":"2017","3":"945","4":"0.035975331"},{"1":"Missouri","2":"2017","3":"699","4":"0.026610324"},{"1":"Montana","2":"2017","3":"598","4":"0.022765342"},{"1":"Nebraska","2":"2017","3":"252","4":"0.009593422"},{"1":"Nevada","2":"2017","3":"910","4":"0.034642912"},{"1":"New Hampshire","2":"2017","3":"896","4":"0.034109944"},{"1":"New Jersey","2":"2017","3":"380","4":"0.014466271"},{"1":"New Mexico","2":"2017","3":"574","4":"0.021851683"},{"1":"New York","2":"2017","3":"135","4":"0.005139333"},{"1":"North Carolina","2":"2017","3":"609","4":"0.023184102"},{"1":"North Dakota","2":"2017","3":"891","4":"0.033919598"},{"1":"Ohio","2":"2017","3":"960","4":"0.036546368"},{"1":"Oklahoma","2":"2017","3":"769","4":"0.029275164"},{"1":"Oregon","2":"2017","3":"864","4":"0.032891731"},{"1":"Pennsylvania","2":"2017","3":"930","4":"0.035404294"},{"1":"Rhode Island","2":"2017","3":"529","4":"0.020138572"},{"1":"South Carolina","2":"2017","3":"617","4":"0.023488655"},{"1":"South Dakota","2":"2017","3":"262","4":"0.009974113"},{"1":"Tennessee","2":"2017","3":"153","4":"0.005824577"},{"1":"Texas","2":"2017","3":"732","4":"0.027866606"},{"1":"Utah","2":"2017","3":"669","4":"0.025468250"},{"1":"Vermont","2":"2017","3":"698","4":"0.026572255"},{"1":"Virginia","2":"2017","3":"125","4":"0.004758642"},{"1":"Washington","2":"2017","3":"495","4":"0.018844221"},{"1":"West Virginia","2":"2017","3":"603","4":"0.022955688"},{"1":"Wisconsin","2":"2017","3":"582","4":"0.022156236"},{"1":"Wyoming","2":"2017","3":"451","4":"0.017169179"},{"1":"Alabama","2":"2018","3":"395","4":"0.014728913"},{"1":"Alaska","2":"2018","3":"201","4":"0.007494966"},{"1":"Arizona","2":"2018","3":"858","4":"0.031993437"},{"1":"Arkansas","2":"2018","3":"635","4":"0.023678127"},{"1":"California","2":"2018","3":"318","4":"0.011857708"},{"1":"Colorado","2":"2018","3":"498","4":"0.018569617"},{"1":"Connecticut","2":"2018","3":"334","4":"0.012454322"},{"1":"Delaware","2":"2018","3":"998","4":"0.037213812"},{"1":"District of Columbia","2":"2018","3":"745","4":"0.027779849"},{"1":"Florida","2":"2018","3":"924","4":"0.034454471"},{"1":"Georgia","2":"2018","3":"293","4":"0.010925498"},{"1":"Hawaii","2":"2018","3":"303","4":"0.011298382"},{"1":"Idaho","2":"2018","3":"349","4":"0.013013648"},{"1":"Illinois","2":"2018","3":"573","4":"0.021366247"},{"1":"Indiana","2":"2018","3":"188","4":"0.007010217"},{"1":"Iowa","2":"2018","3":"330","4":"0.012305168"},{"1":"Kansas","2":"2018","3":"546","4":"0.020359460"},{"1":"Kentucky","2":"2018","3":"976","4":"0.036393467"},{"1":"Louisiana","2":"2018","3":"0","4":"0.000000000"},{"1":"Maine","2":"2018","3":"127","4":"0.004735625"},{"1":"Maryland","2":"2018","3":"106","4":"0.003952569"},{"1":"Massachusetts","2":"2018","3":"507","4":"0.018905213"},{"1":"Michigan","2":"2018","3":"503","4":"0.018756059"},{"1":"Minnesota","2":"2018","3":"344","4":"0.012827206"},{"1":"Mississippi","2":"2018","3":"246","4":"0.009172944"},{"1":"Missouri","2":"2018","3":"250","4":"0.009322097"},{"1":"Montana","2":"2018","3":"296","4":"0.011037363"},{"1":"Nebraska","2":"2018","3":"726","4":"0.027071370"},{"1":"Nevada","2":"2018","3":"526","4":"0.019613692"},{"1":"New Hampshire","2":"2018","3":"109","4":"0.004064434"},{"1":"New Jersey","2":"2018","3":"961","4":"0.035834141"},{"1":"New Mexico","2":"2018","3":"771","4":"0.028749347"},{"1":"New York","2":"2018","3":"637","4":"0.023752703"},{"1":"North Carolina","2":"2018","3":"970","4":"0.036169737"},{"1":"North Dakota","2":"2018","3":"307","4":"0.011447535"},{"1":"Ohio","2":"2018","3":"900","4":"0.033559550"},{"1":"Oklahoma","2":"2018","3":"699","4":"0.026064583"},{"1":"Oregon","2":"2018","3":"762","4":"0.028413752"},{"1":"Pennsylvania","2":"2018","3":"475","4":"0.017711984"},{"1":"Rhode Island","2":"2018","3":"852","4":"0.031769707"},{"1":"South Carolina","2":"2018","3":"815","4":"0.030390037"},{"1":"South Dakota","2":"2018","3":"601","4":"0.022410321"},{"1":"Tennessee","2":"2018","3":"568","4":"0.021179805"},{"1":"Texas","2":"2018","3":"571","4":"0.021291670"},{"1":"Utah","2":"2018","3":"486","4":"0.018122157"},{"1":"Vermont","2":"2018","3":"470","4":"0.017525543"},{"1":"Virginia","2":"2018","3":"105","4":"0.003915281"},{"1":"Washington","2":"2018","3":"474","4":"0.017674696"},{"1":"West Virginia","2":"2018","3":"600","4":"0.022373033"},{"1":"Wisconsin","2":"2018","3":"997","4":"0.037176523"},{"1":"Wyoming","2":"2018","3":"593","4":"0.022112014"},{"1":"Alabama","2":"2019","3":"711","4":"0.026112825"},{"1":"Alaska","2":"2019","3":"131","4":"0.004811224"},{"1":"Arizona","2":"2019","3":"136","4":"0.004994858"},{"1":"Arkansas","2":"2019","3":"557","4":"0.020456883"},{"1":"California","2":"2019","3":"854","4":"0.031364772"},{"1":"Colorado","2":"2019","3":"768","4":"0.028206258"},{"1":"Connecticut","2":"2019","3":"586","4":"0.021521963"},{"1":"Delaware","2":"2019","3":"453","4":"0.016637285"},{"1":"District of Columbia","2":"2019","3":"180","4":"0.006610842"},{"1":"Florida","2":"2019","3":"958","4":"0.035184369"},{"1":"Georgia","2":"2019","3":"821","4":"0.030152784"},{"1":"Hawaii","2":"2019","3":"931","4":"0.034192743"},{"1":"Idaho","2":"2019","3":"0","4":"0.000000000"},{"1":"Illinois","2":"2019","3":"827","4":"0.030373145"},{"1":"Indiana","2":"2019","3":"728","4":"0.026737182"},{"1":"Iowa","2":"2019","3":"657","4":"0.024129572"},{"1":"Kansas","2":"2019","3":"501","4":"0.018400176"},{"1":"Kentucky","2":"2019","3":"332","4":"0.012193330"},{"1":"Louisiana","2":"2019","3":"490","4":"0.017996180"},{"1":"Maine","2":"2019","3":"206","4":"0.007565741"},{"1":"Maryland","2":"2019","3":"507","4":"0.018620538"},{"1":"Massachusetts","2":"2019","3":"859","4":"0.031548406"},{"1":"Michigan","2":"2019","3":"467","4":"0.017151462"},{"1":"Minnesota","2":"2019","3":"251","4":"0.009218452"},{"1":"Mississippi","2":"2019","3":"372","4":"0.013662406"},{"1":"Missouri","2":"2019","3":"972","4":"0.035698546"},{"1":"Montana","2":"2019","3":"527","4":"0.019355076"},{"1":"Nebraska","2":"2019","3":"232","4":"0.008520641"},{"1":"Nevada","2":"2019","3":"647","4":"0.023762304"},{"1":"New Hampshire","2":"2019","3":"818","4":"0.030042603"},{"1":"New Jersey","2":"2019","3":"168","4":"0.006170119"},{"1":"New Mexico","2":"2019","3":"336","4":"0.012340238"},{"1":"New York","2":"2019","3":"207","4":"0.007602468"},{"1":"North Carolina","2":"2019","3":"508","4":"0.018657265"},{"1":"North Dakota","2":"2019","3":"652","4":"0.023945938"},{"1":"Ohio","2":"2019","3":"186","4":"0.006831203"},{"1":"Oklahoma","2":"2019","3":"621","4":"0.022807404"},{"1":"Oregon","2":"2019","3":"471","4":"0.017298369"},{"1":"Pennsylvania","2":"2019","3":"956","4":"0.035110915"},{"1":"Rhode Island","2":"2019","3":"900","4":"0.033054209"},{"1":"South Carolina","2":"2019","3":"182","4":"0.006684296"},{"1":"South Dakota","2":"2019","3":"584","4":"0.021448509"},{"1":"Tennessee","2":"2019","3":"476","4":"0.017482004"},{"1":"Texas","2":"2019","3":"292","4":"0.010724254"},{"1":"Utah","2":"2019","3":"758","4":"0.027838989"},{"1":"Vermont","2":"2019","3":"158","4":"0.005802850"},{"1":"Virginia","2":"2019","3":"432","4":"0.015866020"},{"1":"Washington","2":"2019","3":"615","4":"0.022587043"},{"1":"West Virginia","2":"2019","3":"974","4":"0.035771999"},{"1":"Wisconsin","2":"2019","3":"842","4":"0.030924049"},{"1":"Wyoming","2":"2019","3":"431","4":"0.015829293"}],"options":{"columns":{"min":{},"max":[10]},"rows":{"min":[5],"max":[5]},"pages":{}}}
  </script>
</div>

---

## `ungroup()`

```r
german_speakers %>% 
  pivot_longer(cols = -state,
               names_to = "year", 
               values_to = "number") %>% 
  mutate(number = na_if(number, "-")) %>% 
  mutate(number = replace_na(number, 0)) %>% 
  mutate(number = as.numeric(number)) %>% 
  mutate(year = parse_number(year)) %>% 
* group_by(year) %>%
* mutate(pct = number / sum(number)) %>%
* slice_max(pct, 1) %>%
  arrange(year, state)
```

---

## `ungroup()`

---

## `ungroup()`

```r
german_speakers %>% 
  pivot_longer(cols = -state,
               names_to = "year", 
               values_to = "number") %>% 
  mutate(number = na_if(number, "-")) %>% 
  mutate(number = replace_na(number, 0)) %>% 
  mutate(number = as.numeric(number)) %>% 
  mutate(year = parse_number(year)) %>% 
  group_by(year) %>%
  mutate(pct = number / sum(number)) %>% 
* ungroup() %>%
  slice_max(pct, 1) 
```

---

## `ungroup()`

---

## My Turn

I'll calculate the percent of students at each school who are proficient in math. To do this, I'll need to use both:

- `group_by()` and `summarize()`

- `group_by()` and `mutate()`

And I can't forget to `ungroup()`!

---

## Your Turn

Create a new variable called `pct` that shows each race/ethnicity as a percentage of all students in each district

You'll need to use `group_by()` and `mutate()`

Don't forget to `ungroup()` at the end!

---

background-image:url("images/bind.jpg")

# Binding Data Frames

???

What if you want to do more than one year?

Discuss why bind_cols() doesn't give you tidy data

---

## bind_rows()

```r
german_speakers_2018
```

<div data-pagedtable="false">
  <script data-pagedtable-source type="application/json">
{"columns":[{"label":["state"],"name":[1],"type":["chr"],"align":["left"]},{"label":["number"],"name":[2],"type":["dbl"],"align":["right"]}],"data":[{"1":"Alabama","2":"395"},{"1":"Alaska","2":"201"},{"1":"Arizona","2":"858"},{"1":"Arkansas","2":"635"},{"1":"California","2":"318"},{"1":"Colorado","2":"498"},{"1":"Connecticut","2":"334"},{"1":"Delaware","2":"998"},{"1":"District of Columbia","2":"745"},{"1":"Florida","2":"924"},{"1":"Georgia","2":"293"},{"1":"Hawaii","2":"303"},{"1":"Idaho","2":"349"},{"1":"Illinois","2":"573"},{"1":"Indiana","2":"188"},{"1":"Iowa","2":"330"},{"1":"Kansas","2":"546"},{"1":"Kentucky","2":"976"},{"1":"Louisiana","2":"0"},{"1":"Maine","2":"127"},{"1":"Montana","2":"296"},{"1":"Nebraska","2":"726"},{"1":"Nevada","2":"526"},{"1":"New Hampshire","2":"109"},{"1":"New Jersey","2":"961"},{"1":"New Mexico","2":"771"},{"1":"New York","2":"637"},{"1":"North Carolina","2":"970"},{"1":"North Dakota","2":"307"},{"1":"Ohio","2":"900"},{"1":"Oklahoma","2":"699"},{"1":"Oregon","2":"762"},{"1":"Maryland","2":"106"},{"1":"Massachusetts","2":"507"},{"1":"Michigan","2":"503"},{"1":"Minnesota","2":"344"},{"1":"Mississippi","2":"246"},{"1":"Missouri","2":"250"},{"1":"Pennsylvania","2":"475"},{"1":"Rhode Island","2":"852"},{"1":"South Carolina","2":"815"},{"1":"South Dakota","2":"601"},{"1":"Tennessee","2":"568"},{"1":"Texas","2":"571"},{"1":"Utah","2":"486"},{"1":"Vermont","2":"470"},{"1":"Virginia","2":"105"},{"1":"Washington","2":"474"},{"1":"West Virginia","2":"600"},{"1":"Wisconsin","2":"997"},{"1":"Wyoming","2":"593"}],"options":{"columns":{"min":{},"max":[10]},"rows":{"min":[5],"max":[5]},"pages":{}}}
  </script>
</div>

]

```r
german_speakers_2019
```

<div data-pagedtable="false">
  <script data-pagedtable-source type="application/json">
{"columns":[{"label":["state"],"name":[1],"type":["chr"],"align":["left"]},{"label":["number"],"name":[2],"type":["dbl"],"align":["right"]}],"data":[{"1":"Alabama","2":"711"},{"1":"Alaska","2":"131"},{"1":"Arizona","2":"136"},{"1":"Arkansas","2":"557"},{"1":"California","2":"854"},{"1":"Colorado","2":"768"},{"1":"Connecticut","2":"586"},{"1":"Delaware","2":"453"},{"1":"District of Columbia","2":"180"},{"1":"Florida","2":"958"},{"1":"Georgia","2":"821"},{"1":"Hawaii","2":"931"},{"1":"Idaho","2":"0"},{"1":"Illinois","2":"827"},{"1":"Indiana","2":"728"},{"1":"Iowa","2":"657"},{"1":"Kansas","2":"501"},{"1":"Kentucky","2":"332"},{"1":"Louisiana","2":"490"},{"1":"Maine","2":"206"},{"1":"Montana","2":"527"},{"1":"Nebraska","2":"232"},{"1":"Nevada","2":"647"},{"1":"New Hampshire","2":"818"},{"1":"New Jersey","2":"168"},{"1":"New Mexico","2":"336"},{"1":"New York","2":"207"},{"1":"North Carolina","2":"508"},{"1":"North Dakota","2":"652"},{"1":"Ohio","2":"186"},{"1":"Oklahoma","2":"621"},{"1":"Oregon","2":"471"},{"1":"Maryland","2":"507"},{"1":"Massachusetts","2":"859"},{"1":"Michigan","2":"467"},{"1":"Minnesota","2":"251"},{"1":"Mississippi","2":"372"},{"1":"Missouri","2":"972"},{"1":"Pennsylvania","2":"956"},{"1":"Rhode Island","2":"900"},{"1":"South Carolina","2":"182"},{"1":"South Dakota","2":"584"},{"1":"Tennessee","2":"476"},{"1":"Texas","2":"292"},{"1":"Utah","2":"758"},{"1":"Vermont","2":"158"},{"1":"Virginia","2":"432"},{"1":"Washington","2":"615"},{"1":"West Virginia","2":"974"},{"1":"Wisconsin","2":"842"},{"1":"Wyoming","2":"431"}],"options":{"columns":{"min":{},"max":[10]},"rows":{"min":[5],"max":[5]},"pages":{}}}
  </script>
</div>
]

---

## bind_rows()

```r
german_speakers_2018_2019 <- bind_rows(german_speakers_2018,
                                       german_speakers_2019)
```

```r
german_speakers_2018_2019
```

<div data-pagedtable="false">
  <script data-pagedtable-source type="application/json">
{"columns":[{"label":[""],"name":["_rn_"],"type":[""],"align":["left"]},{"label":["state"],"name":[1],"type":["chr"],"align":["left"]},{"label":["number"],"name":[2],"type":["dbl"],"align":["right"]}],"data":[{"1":"Alabama","2":"395","_rn_":"1"},{"1":"Alaska","2":"201","_rn_":"2"},{"1":"Arizona","2":"858","_rn_":"3"},{"1":"Arkansas","2":"635","_rn_":"4"},{"1":"California","2":"318","_rn_":"5"},{"1":"Colorado","2":"498","_rn_":"6"},{"1":"Connecticut","2":"334","_rn_":"7"},{"1":"Delaware","2":"998","_rn_":"8"},{"1":"District of Columbia","2":"745","_rn_":"9"},{"1":"Florida","2":"924","_rn_":"10"},{"1":"Georgia","2":"293","_rn_":"11"},{"1":"Hawaii","2":"303","_rn_":"12"},{"1":"Idaho","2":"349","_rn_":"13"},{"1":"Illinois","2":"573","_rn_":"14"},{"1":"Indiana","2":"188","_rn_":"15"},{"1":"Iowa","2":"330","_rn_":"16"},{"1":"Kansas","2":"546","_rn_":"17"},{"1":"Kentucky","2":"976","_rn_":"18"},{"1":"Louisiana","2":"0","_rn_":"19"},{"1":"Maine","2":"127","_rn_":"20"},{"1":"Montana","2":"296","_rn_":"21"},{"1":"Nebraska","2":"726","_rn_":"22"},{"1":"Nevada","2":"526","_rn_":"23"},{"1":"New Hampshire","2":"109","_rn_":"24"},{"1":"New Jersey","2":"961","_rn_":"25"},{"1":"New Mexico","2":"771","_rn_":"26"},{"1":"New York","2":"637","_rn_":"27"},{"1":"North Carolina","2":"970","_rn_":"28"},{"1":"North Dakota","2":"307","_rn_":"29"},{"1":"Ohio","2":"900","_rn_":"30"},{"1":"Oklahoma","2":"699","_rn_":"31"},{"1":"Oregon","2":"762","_rn_":"32"},{"1":"Maryland","2":"106","_rn_":"33"},{"1":"Massachusetts","2":"507","_rn_":"34"},{"1":"Michigan","2":"503","_rn_":"35"},{"1":"Minnesota","2":"344","_rn_":"36"},{"1":"Mississippi","2":"246","_rn_":"37"},{"1":"Missouri","2":"250","_rn_":"38"},{"1":"Pennsylvania","2":"475","_rn_":"39"},{"1":"Rhode Island","2":"852","_rn_":"40"},{"1":"South Carolina","2":"815","_rn_":"41"},{"1":"South Dakota","2":"601","_rn_":"42"},{"1":"Tennessee","2":"568","_rn_":"43"},{"1":"Texas","2":"571","_rn_":"44"},{"1":"Utah","2":"486","_rn_":"45"},{"1":"Vermont","2":"470","_rn_":"46"},{"1":"Virginia","2":"105","_rn_":"47"},{"1":"Washington","2":"474","_rn_":"48"},{"1":"West Virginia","2":"600","_rn_":"49"},{"1":"Wisconsin","2":"997","_rn_":"50"},{"1":"Wyoming","2":"593","_rn_":"51"},{"1":"Alabama","2":"711","_rn_":"52"},{"1":"Alaska","2":"131","_rn_":"53"},{"1":"Arizona","2":"136","_rn_":"54"},{"1":"Arkansas","2":"557","_rn_":"55"},{"1":"California","2":"854","_rn_":"56"},{"1":"Colorado","2":"768","_rn_":"57"},{"1":"Connecticut","2":"586","_rn_":"58"},{"1":"Delaware","2":"453","_rn_":"59"},{"1":"District of Columbia","2":"180","_rn_":"60"},{"1":"Florida","2":"958","_rn_":"61"},{"1":"Georgia","2":"821","_rn_":"62"},{"1":"Hawaii","2":"931","_rn_":"63"},{"1":"Idaho","2":"0","_rn_":"64"},{"1":"Illinois","2":"827","_rn_":"65"},{"1":"Indiana","2":"728","_rn_":"66"},{"1":"Iowa","2":"657","_rn_":"67"},{"1":"Kansas","2":"501","_rn_":"68"},{"1":"Kentucky","2":"332","_rn_":"69"},{"1":"Louisiana","2":"490","_rn_":"70"},{"1":"Maine","2":"206","_rn_":"71"},{"1":"Montana","2":"527","_rn_":"72"},{"1":"Nebraska","2":"232","_rn_":"73"},{"1":"Nevada","2":"647","_rn_":"74"},{"1":"New Hampshire","2":"818","_rn_":"75"},{"1":"New Jersey","2":"168","_rn_":"76"},{"1":"New Mexico","2":"336","_rn_":"77"},{"1":"New York","2":"207","_rn_":"78"},{"1":"North Carolina","2":"508","_rn_":"79"},{"1":"North Dakota","2":"652","_rn_":"80"},{"1":"Ohio","2":"186","_rn_":"81"},{"1":"Oklahoma","2":"621","_rn_":"82"},{"1":"Oregon","2":"471","_rn_":"83"},{"1":"Maryland","2":"507","_rn_":"84"},{"1":"Massachusetts","2":"859","_rn_":"85"},{"1":"Michigan","2":"467","_rn_":"86"},{"1":"Minnesota","2":"251","_rn_":"87"},{"1":"Mississippi","2":"372","_rn_":"88"},{"1":"Missouri","2":"972","_rn_":"89"},{"1":"Pennsylvania","2":"956","_rn_":"90"},{"1":"Rhode Island","2":"900","_rn_":"91"},{"1":"South Carolina","2":"182","_rn_":"92"},{"1":"South Dakota","2":"584","_rn_":"93"},{"1":"Tennessee","2":"476","_rn_":"94"},{"1":"Texas","2":"292","_rn_":"95"},{"1":"Utah","2":"758","_rn_":"96"},{"1":"Vermont","2":"158","_rn_":"97"},{"1":"Virginia","2":"432","_rn_":"98"},{"1":"Washington","2":"615","_rn_":"99"},{"1":"West Virginia","2":"974","_rn_":"100"},{"1":"Wisconsin","2":"842","_rn_":"101"},{"1":"Wyoming","2":"431","_rn_":"102"}],"options":{"columns":{"min":{},"max":[10]},"rows":{"min":[5],"max":[5]},"pages":{}}}
  </script>
</div>

---

## bind_rows()

```r
german_speakers_2018 <- german_speakers_2018 %>% 
  mutate(year = 2018)

german_speakers_2019 <- german_speakers_2019 %>% 
  mutate(year = 2019)

german_speakers_2018_2019 <- bind_rows(german_speakers_2018,
                                       german_speakers_2019)
```

---

## bind_rows()

```r
german_speakers_2018_2019
```

<div data-pagedtable="false">
  <script data-pagedtable-source type="application/json">
{"columns":[{"label":[""],"name":["_rn_"],"type":[""],"align":["left"]},{"label":["state"],"name":[1],"type":["chr"],"align":["left"]},{"label":["number"],"name":[2],"type":["dbl"],"align":["right"]},{"label":["year"],"name":[3],"type":["dbl"],"align":["right"]}],"data":[{"1":"Alabama","2":"395","3":"2018","_rn_":"1"},{"1":"Alaska","2":"201","3":"2018","_rn_":"2"},{"1":"Arizona","2":"858","3":"2018","_rn_":"3"},{"1":"Arkansas","2":"635","3":"2018","_rn_":"4"},{"1":"California","2":"318","3":"2018","_rn_":"5"},{"1":"Colorado","2":"498","3":"2018","_rn_":"6"},{"1":"Connecticut","2":"334","3":"2018","_rn_":"7"},{"1":"Delaware","2":"998","3":"2018","_rn_":"8"},{"1":"District of Columbia","2":"745","3":"2018","_rn_":"9"},{"1":"Florida","2":"924","3":"2018","_rn_":"10"},{"1":"Georgia","2":"293","3":"2018","_rn_":"11"},{"1":"Hawaii","2":"303","3":"2018","_rn_":"12"},{"1":"Idaho","2":"349","3":"2018","_rn_":"13"},{"1":"Illinois","2":"573","3":"2018","_rn_":"14"},{"1":"Indiana","2":"188","3":"2018","_rn_":"15"},{"1":"Iowa","2":"330","3":"2018","_rn_":"16"},{"1":"Kansas","2":"546","3":"2018","_rn_":"17"},{"1":"Kentucky","2":"976","3":"2018","_rn_":"18"},{"1":"Louisiana","2":"0","3":"2018","_rn_":"19"},{"1":"Maine","2":"127","3":"2018","_rn_":"20"},{"1":"Montana","2":"296","3":"2018","_rn_":"21"},{"1":"Nebraska","2":"726","3":"2018","_rn_":"22"},{"1":"Nevada","2":"526","3":"2018","_rn_":"23"},{"1":"New Hampshire","2":"109","3":"2018","_rn_":"24"},{"1":"New Jersey","2":"961","3":"2018","_rn_":"25"},{"1":"New Mexico","2":"771","3":"2018","_rn_":"26"},{"1":"New York","2":"637","3":"2018","_rn_":"27"},{"1":"North Carolina","2":"970","3":"2018","_rn_":"28"},{"1":"North Dakota","2":"307","3":"2018","_rn_":"29"},{"1":"Ohio","2":"900","3":"2018","_rn_":"30"},{"1":"Oklahoma","2":"699","3":"2018","_rn_":"31"},{"1":"Oregon","2":"762","3":"2018","_rn_":"32"},{"1":"Maryland","2":"106","3":"2018","_rn_":"33"},{"1":"Massachusetts","2":"507","3":"2018","_rn_":"34"},{"1":"Michigan","2":"503","3":"2018","_rn_":"35"},{"1":"Minnesota","2":"344","3":"2018","_rn_":"36"},{"1":"Mississippi","2":"246","3":"2018","_rn_":"37"},{"1":"Missouri","2":"250","3":"2018","_rn_":"38"},{"1":"Pennsylvania","2":"475","3":"2018","_rn_":"39"},{"1":"Rhode Island","2":"852","3":"2018","_rn_":"40"},{"1":"South Carolina","2":"815","3":"2018","_rn_":"41"},{"1":"South Dakota","2":"601","3":"2018","_rn_":"42"},{"1":"Tennessee","2":"568","3":"2018","_rn_":"43"},{"1":"Texas","2":"571","3":"2018","_rn_":"44"},{"1":"Utah","2":"486","3":"2018","_rn_":"45"},{"1":"Vermont","2":"470","3":"2018","_rn_":"46"},{"1":"Virginia","2":"105","3":"2018","_rn_":"47"},{"1":"Washington","2":"474","3":"2018","_rn_":"48"},{"1":"West Virginia","2":"600","3":"2018","_rn_":"49"},{"1":"Wisconsin","2":"997","3":"2018","_rn_":"50"},{"1":"Wyoming","2":"593","3":"2018","_rn_":"51"},{"1":"Alabama","2":"711","3":"2019","_rn_":"52"},{"1":"Alaska","2":"131","3":"2019","_rn_":"53"},{"1":"Arizona","2":"136","3":"2019","_rn_":"54"},{"1":"Arkansas","2":"557","3":"2019","_rn_":"55"},{"1":"California","2":"854","3":"2019","_rn_":"56"},{"1":"Colorado","2":"768","3":"2019","_rn_":"57"},{"1":"Connecticut","2":"586","3":"2019","_rn_":"58"},{"1":"Delaware","2":"453","3":"2019","_rn_":"59"},{"1":"District of Columbia","2":"180","3":"2019","_rn_":"60"},{"1":"Florida","2":"958","3":"2019","_rn_":"61"},{"1":"Georgia","2":"821","3":"2019","_rn_":"62"},{"1":"Hawaii","2":"931","3":"2019","_rn_":"63"},{"1":"Idaho","2":"0","3":"2019","_rn_":"64"},{"1":"Illinois","2":"827","3":"2019","_rn_":"65"},{"1":"Indiana","2":"728","3":"2019","_rn_":"66"},{"1":"Iowa","2":"657","3":"2019","_rn_":"67"},{"1":"Kansas","2":"501","3":"2019","_rn_":"68"},{"1":"Kentucky","2":"332","3":"2019","_rn_":"69"},{"1":"Louisiana","2":"490","3":"2019","_rn_":"70"},{"1":"Maine","2":"206","3":"2019","_rn_":"71"},{"1":"Montana","2":"527","3":"2019","_rn_":"72"},{"1":"Nebraska","2":"232","3":"2019","_rn_":"73"},{"1":"Nevada","2":"647","3":"2019","_rn_":"74"},{"1":"New Hampshire","2":"818","3":"2019","_rn_":"75"},{"1":"New Jersey","2":"168","3":"2019","_rn_":"76"},{"1":"New Mexico","2":"336","3":"2019","_rn_":"77"},{"1":"New York","2":"207","3":"2019","_rn_":"78"},{"1":"North Carolina","2":"508","3":"2019","_rn_":"79"},{"1":"North Dakota","2":"652","3":"2019","_rn_":"80"},{"1":"Ohio","2":"186","3":"2019","_rn_":"81"},{"1":"Oklahoma","2":"621","3":"2019","_rn_":"82"},{"1":"Oregon","2":"471","3":"2019","_rn_":"83"},{"1":"Maryland","2":"507","3":"2019","_rn_":"84"},{"1":"Massachusetts","2":"859","3":"2019","_rn_":"85"},{"1":"Michigan","2":"467","3":"2019","_rn_":"86"},{"1":"Minnesota","2":"251","3":"2019","_rn_":"87"},{"1":"Mississippi","2":"372","3":"2019","_rn_":"88"},{"1":"Missouri","2":"972","3":"2019","_rn_":"89"},{"1":"Pennsylvania","2":"956","3":"2019","_rn_":"90"},{"1":"Rhode Island","2":"900","3":"2019","_rn_":"91"},{"1":"South Carolina","2":"182","3":"2019","_rn_":"92"},{"1":"South Dakota","2":"584","3":"2019","_rn_":"93"},{"1":"Tennessee","2":"476","3":"2019","_rn_":"94"},{"1":"Texas","2":"292","3":"2019","_rn_":"95"},{"1":"Utah","2":"758","3":"2019","_rn_":"96"},{"1":"Vermont","2":"158","3":"2019","_rn_":"97"},{"1":"Virginia","2":"432","3":"2019","_rn_":"98"},{"1":"Washington","2":"615","3":"2019","_rn_":"99"},{"1":"West Virginia","2":"974","3":"2019","_rn_":"100"},{"1":"Wisconsin","2":"842","3":"2019","_rn_":"101"},{"1":"Wyoming","2":"431","3":"2019","_rn_":"102"}],"options":{"columns":{"min":{},"max":[10]},"rows":{"min":[5],"max":[5]},"pages":{}}}
  </script>
</div>

---

## bind_cols()

```r
german_speakers_2018 <- german_speakers_2018 %>% 
  mutate(year = 2018)

german_speakers_2019 <- german_speakers_2019 %>% 
  mutate(year = 2019)

german_speakers_2018_2019 <- bind_cols(german_speakers_2018,
                                       german_speakers_2019)
```

---

## bind_cols()

```r
german_speakers_2018_2019
```

<div data-pagedtable="false">
  <script data-pagedtable-source type="application/json">
{"columns":[{"label":[""],"name":["_rn_"],"type":[""],"align":["left"]},{"label":["state...1"],"name":[1],"type":["chr"],"align":["left"]},{"label":["number...2"],"name":[2],"type":["dbl"],"align":["right"]},{"label":["year...3"],"name":[3],"type":["dbl"],"align":["right"]},{"label":["state...4"],"name":[4],"type":["chr"],"align":["left"]},{"label":["number...5"],"name":[5],"type":["dbl"],"align":["right"]},{"label":["year...6"],"name":[6],"type":["dbl"],"align":["right"]}],"data":[{"1":"Alabama","2":"395","3":"2018","4":"Alabama","5":"711","6":"2019","_rn_":"1"},{"1":"Alaska","2":"201","3":"2018","4":"Alaska","5":"131","6":"2019","_rn_":"2"},{"1":"Arizona","2":"858","3":"2018","4":"Arizona","5":"136","6":"2019","_rn_":"3"},{"1":"Arkansas","2":"635","3":"2018","4":"Arkansas","5":"557","6":"2019","_rn_":"4"},{"1":"California","2":"318","3":"2018","4":"California","5":"854","6":"2019","_rn_":"5"},{"1":"Colorado","2":"498","3":"2018","4":"Colorado","5":"768","6":"2019","_rn_":"6"},{"1":"Connecticut","2":"334","3":"2018","4":"Connecticut","5":"586","6":"2019","_rn_":"7"},{"1":"Delaware","2":"998","3":"2018","4":"Delaware","5":"453","6":"2019","_rn_":"8"},{"1":"District of Columbia","2":"745","3":"2018","4":"District of Columbia","5":"180","6":"2019","_rn_":"9"},{"1":"Florida","2":"924","3":"2018","4":"Florida","5":"958","6":"2019","_rn_":"10"},{"1":"Georgia","2":"293","3":"2018","4":"Georgia","5":"821","6":"2019","_rn_":"11"},{"1":"Hawaii","2":"303","3":"2018","4":"Hawaii","5":"931","6":"2019","_rn_":"12"},{"1":"Idaho","2":"349","3":"2018","4":"Idaho","5":"0","6":"2019","_rn_":"13"},{"1":"Illinois","2":"573","3":"2018","4":"Illinois","5":"827","6":"2019","_rn_":"14"},{"1":"Indiana","2":"188","3":"2018","4":"Indiana","5":"728","6":"2019","_rn_":"15"},{"1":"Iowa","2":"330","3":"2018","4":"Iowa","5":"657","6":"2019","_rn_":"16"},{"1":"Kansas","2":"546","3":"2018","4":"Kansas","5":"501","6":"2019","_rn_":"17"},{"1":"Kentucky","2":"976","3":"2018","4":"Kentucky","5":"332","6":"2019","_rn_":"18"},{"1":"Louisiana","2":"0","3":"2018","4":"Louisiana","5":"490","6":"2019","_rn_":"19"},{"1":"Maine","2":"127","3":"2018","4":"Maine","5":"206","6":"2019","_rn_":"20"},{"1":"Montana","2":"296","3":"2018","4":"Montana","5":"527","6":"2019","_rn_":"21"},{"1":"Nebraska","2":"726","3":"2018","4":"Nebraska","5":"232","6":"2019","_rn_":"22"},{"1":"Nevada","2":"526","3":"2018","4":"Nevada","5":"647","6":"2019","_rn_":"23"},{"1":"New Hampshire","2":"109","3":"2018","4":"New Hampshire","5":"818","6":"2019","_rn_":"24"},{"1":"New Jersey","2":"961","3":"2018","4":"New Jersey","5":"168","6":"2019","_rn_":"25"},{"1":"New Mexico","2":"771","3":"2018","4":"New Mexico","5":"336","6":"2019","_rn_":"26"},{"1":"New York","2":"637","3":"2018","4":"New York","5":"207","6":"2019","_rn_":"27"},{"1":"North Carolina","2":"970","3":"2018","4":"North Carolina","5":"508","6":"2019","_rn_":"28"},{"1":"North Dakota","2":"307","3":"2018","4":"North Dakota","5":"652","6":"2019","_rn_":"29"},{"1":"Ohio","2":"900","3":"2018","4":"Ohio","5":"186","6":"2019","_rn_":"30"},{"1":"Oklahoma","2":"699","3":"2018","4":"Oklahoma","5":"621","6":"2019","_rn_":"31"},{"1":"Oregon","2":"762","3":"2018","4":"Oregon","5":"471","6":"2019","_rn_":"32"},{"1":"Maryland","2":"106","3":"2018","4":"Maryland","5":"507","6":"2019","_rn_":"33"},{"1":"Massachusetts","2":"507","3":"2018","4":"Massachusetts","5":"859","6":"2019","_rn_":"34"},{"1":"Michigan","2":"503","3":"2018","4":"Michigan","5":"467","6":"2019","_rn_":"35"},{"1":"Minnesota","2":"344","3":"2018","4":"Minnesota","5":"251","6":"2019","_rn_":"36"},{"1":"Mississippi","2":"246","3":"2018","4":"Mississippi","5":"372","6":"2019","_rn_":"37"},{"1":"Missouri","2":"250","3":"2018","4":"Missouri","5":"972","6":"2019","_rn_":"38"},{"1":"Pennsylvania","2":"475","3":"2018","4":"Pennsylvania","5":"956","6":"2019","_rn_":"39"},{"1":"Rhode Island","2":"852","3":"2018","4":"Rhode Island","5":"900","6":"2019","_rn_":"40"},{"1":"South Carolina","2":"815","3":"2018","4":"South Carolina","5":"182","6":"2019","_rn_":"41"},{"1":"South Dakota","2":"601","3":"2018","4":"South Dakota","5":"584","6":"2019","_rn_":"42"},{"1":"Tennessee","2":"568","3":"2018","4":"Tennessee","5":"476","6":"2019","_rn_":"43"},{"1":"Texas","2":"571","3":"2018","4":"Texas","5":"292","6":"2019","_rn_":"44"},{"1":"Utah","2":"486","3":"2018","4":"Utah","5":"758","6":"2019","_rn_":"45"},{"1":"Vermont","2":"470","3":"2018","4":"Vermont","5":"158","6":"2019","_rn_":"46"},{"1":"Virginia","2":"105","3":"2018","4":"Virginia","5":"432","6":"2019","_rn_":"47"},{"1":"Washington","2":"474","3":"2018","4":"Washington","5":"615","6":"2019","_rn_":"48"},{"1":"West Virginia","2":"600","3":"2018","4":"West Virginia","5":"974","6":"2019","_rn_":"49"},{"1":"Wisconsin","2":"997","3":"2018","4":"Wisconsin","5":"842","6":"2019","_rn_":"50"},{"1":"Wyoming","2":"593","3":"2018","4":"Wyoming","5":"431","6":"2019","_rn_":"51"}],"options":{"columns":{"min":{},"max":[10]},"rows":{"min":[5],"max":[5]},"pages":{}}}
  </script>
</div>

---

## My Turn

I'll import 2017-2018 math proficiency data and then use bind_rows() to make a `third_grade_math_proficiency` data frame.

---

## Your Turn

1. Import 2017-2018 enrollment data into a data frame called `enrollment_by_race_ethnicity_17_18` and clean it using the code you used for the 2018-2019 data

2. Use bind_rows() to make a `enrollment_by_race_ethnicity` data frame.

Hint: You'll need to change some of your code from importing the 2018-2019 data to make the `race_ethnicity` variable get recoded correctly!

---

background-image:url("images/code-heart.jpg")

# Functions

---

## When to Use Functions?

You should consider writing a function whenever you’ve copied and pasted a block of code more than twice (i.e. you now have three copies of the same code).

---

## What is a Function?

Source: [R for the Rest of Us Blog](https://rfortherestofus.com/2019/10/how-to-make-functions-in-r/)

---

## Why Use Functions?

1. You can give a function an evocative name that makes your code easier to understand.

1. As requirements change, you only need to update code in one place, instead of many.

1. You eliminate the chance of making incidental mistakes when you copy and paste (i.e. updating a variable name in one place, but not in another).

---

## My Turn

I'll create a function to clean each year of math proficiency data, then use `bind_rows()` to bind them together

---

## Your Turn

Create a function to clean each year of enrollment data, then use `bind_rows()` to bind them together

Arguments you'll need to use:

- Data year

- Text to remove in the `str_remove()` line

---

background-image:url("images/highway.jpg")

# Data Merging

---

## Data Merging

All of the animations and explanations used here come from the [tidyexplain project by Garrick Aden-Buie](https://www.garrickadenbuie.com/project/tidyexplain/).

---

## Data Merging

```r
german_speakers_2019
```

<div data-pagedtable="false">
  <script data-pagedtable-source type="application/json">
{"columns":[{"label":["state"],"name":[1],"type":["chr"],"align":["left"]},{"label":["number"],"name":[2],"type":["dbl"],"align":["right"]}],"data":[{"1":"Alabama","2":"711"},{"1":"Alaska","2":"131"},{"1":"Arizona","2":"136"},{"1":"Arkansas","2":"557"},{"1":"California","2":"854"},{"1":"Colorado","2":"768"},{"1":"Connecticut","2":"586"},{"1":"Delaware","2":"453"},{"1":"District of Columbia","2":"180"},{"1":"Florida","2":"958"},{"1":"Georgia","2":"821"},{"1":"Hawaii","2":"931"},{"1":"Idaho","2":"0"},{"1":"Illinois","2":"827"},{"1":"Indiana","2":"728"},{"1":"Iowa","2":"657"},{"1":"Kansas","2":"501"},{"1":"Kentucky","2":"332"},{"1":"Louisiana","2":"490"},{"1":"Maine","2":"206"},{"1":"Montana","2":"527"},{"1":"Nebraska","2":"232"},{"1":"Nevada","2":"647"},{"1":"New Hampshire","2":"818"},{"1":"New Jersey","2":"168"},{"1":"New Mexico","2":"336"},{"1":"New York","2":"207"},{"1":"North Carolina","2":"508"},{"1":"North Dakota","2":"652"},{"1":"Ohio","2":"186"},{"1":"Oklahoma","2":"621"},{"1":"Oregon","2":"471"},{"1":"Maryland","2":"507"},{"1":"Massachusetts","2":"859"},{"1":"Michigan","2":"467"},{"1":"Minnesota","2":"251"},{"1":"Mississippi","2":"372"},{"1":"Missouri","2":"972"},{"1":"Pennsylvania","2":"956"},{"1":"Rhode Island","2":"900"},{"1":"South Carolina","2":"182"},{"1":"South Dakota","2":"584"},{"1":"Tennessee","2":"476"},{"1":"Texas","2":"292"},{"1":"Utah","2":"758"},{"1":"Vermont","2":"158"},{"1":"Virginia","2":"432"},{"1":"Washington","2":"615"},{"1":"West Virginia","2":"974"},{"1":"Wisconsin","2":"842"},{"1":"Wyoming","2":"431"}],"options":{"columns":{"min":{},"max":[10]},"rows":{"min":[5],"max":[5]},"pages":{}}}
  </script>
</div>

---

## Data Merging

```r
french_speakers_2019 <- read_excel(path = "data-raw/german-and-french-speakers.xlsx",
                                   sheet = "French speakers 2019")
```

<div data-pagedtable="false">
  <script data-pagedtable-source type="application/json">
{"columns":[{"label":["state"],"name":[1],"type":["chr"],"align":["left"]},{"label":["french_speakers"],"name":[2],"type":["dbl"],"align":["right"]}],"data":[{"1":"Alabama","2":"1678"},{"1":"Alaska","2":"1441"},{"1":"Arizona","2":"1002"},{"1":"Arkansas","2":"1558"},{"1":"California","2":"1935"},{"1":"Colorado","2":"1714"},{"1":"Connecticut","2":"1536"},{"1":"Delaware","2":"1824"},{"1":"District of Columbia","2":"1601"}],"options":{"columns":{"min":{},"max":[10]},"rows":{"min":[5],"max":[5]},"pages":{}}}
  </script>
</div>

---

## Joins

![](images/original-dfs.png)

---

## Joins

```r
 type_of_join(x, y,
              by = "id_variable"))
```

What if we don't have a variable with the same name in both data frames?

```r
type_of_join(x, y,
             by = c("id_variable_x" = "id_variable_y"))
```

What if we need to join on multiple variables with different names in both data frames?

```r
 type_of_join(x, y,
              by = c("id_variable_a" = "id_variable_b",
                     "id_variable_c" = "id_variable_d"))
```

---

## Which join should I use?

![](images/claus-wilke-left-join.png)

---

## Mutating joins

> A mutating join allows you to combine variables from two tables. It first matches observations by their keys, then copies across variables from one table to the other. - [R for Data Science](https://r4ds.had.co.nz/relational-data.html#mutating-joins)

---

### `left_join()`

All rows from x, and all columns from x and y. Rows in x with no match in y will have NA values in the new columns.

---

### `left_join()`

---

### `left_join()`

```r
left_join(german_speakers_2019,
          french_speakers_2019,
          by = "state")
```

---

### `right_join()`

All rows from y, and all columns from x and y. Rows in y with no match in x will have NA values in the new columns.

---

### `right_join()`

---

### `right_join()`

```r
right_join(german_speakers_2019,
           french_speakers_2019,
           by = "state")
```

---

### `full_join()`

All rows and all columns from both x and y. Where there are not matching values, returns NA for the one missing.

---

### `full_join()`

---

### `full_join()`

```r
full_join(german_speakers_2019,
          french_speakers_2019,
          by = "state")
```

---

### `inner_join()`

All rows and all columns from both x and y. Where there are not matching values, returns NA for the one missing.

---

### `inner_join()`

---

### `inner_join()`

```r
inner_join(german_speakers_2019,
           french_speakers_2019,
           by = "state")
```

---

## Filtering joins

> Filtering joins match observations in the same way as mutating joins, but affect the observations, not the variables - [R for Data Science](https://r4ds.had.co.nz/relational-data.html#filtering-joins)

---

### `semi_join()`

All rows from x where there are matching values in y, keeping just columns from x.

---

### `semi_join()`

---

### `semi_join()`

```r
semi_join(german_speakers_2019,
          french_speakers_2019,
          by = "state")
```

<div data-pagedtable="false">
  <script data-pagedtable-source type="application/json">
{"columns":[{"label":["state"],"name":[1],"type":["chr"],"align":["left"]},{"label":["number"],"name":[2],"type":["dbl"],"align":["right"]}],"data":[{"1":"Alabama","2":"711"},{"1":"Alaska","2":"131"},{"1":"Arizona","2":"136"},{"1":"Arkansas","2":"557"},{"1":"California","2":"854"},{"1":"Colorado","2":"768"},{"1":"Connecticut","2":"586"},{"1":"Delaware","2":"453"},{"1":"District of Columbia","2":"180"}],"options":{"columns":{"min":{},"max":[10]},"rows":{"min":[5],"max":[5]},"pages":{}}}
  </script>
</div>

---

### `anti_join()`

All rows from x where there are not matching values in y, keeping just columns from x.

---

### `anti_join()`

---

### `anti_join()`

```r
anti_join(german_speakers_2019,
          french_speakers_2019,
          by = "state")
```

<div data-pagedtable="false">
  <script data-pagedtable-source type="application/json">
{"columns":[{"label":["state"],"name":[1],"type":["chr"],"align":["left"]},{"label":["number"],"name":[2],"type":["dbl"],"align":["right"]}],"data":[{"1":"Florida","2":"958"},{"1":"Georgia","2":"821"},{"1":"Hawaii","2":"931"},{"1":"Idaho","2":"0"},{"1":"Illinois","2":"827"},{"1":"Indiana","2":"728"},{"1":"Iowa","2":"657"},{"1":"Kansas","2":"501"},{"1":"Kentucky","2":"332"},{"1":"Louisiana","2":"490"},{"1":"Maine","2":"206"},{"1":"Montana","2":"527"},{"1":"Nebraska","2":"232"},{"1":"Nevada","2":"647"},{"1":"New Hampshire","2":"818"},{"1":"New Jersey","2":"168"},{"1":"New Mexico","2":"336"},{"1":"New York","2":"207"},{"1":"North Carolina","2":"508"},{"1":"North Dakota","2":"652"},{"1":"Ohio","2":"186"},{"1":"Oklahoma","2":"621"},{"1":"Oregon","2":"471"},{"1":"Maryland","2":"507"},{"1":"Massachusetts","2":"859"},{"1":"Michigan","2":"467"},{"1":"Minnesota","2":"251"},{"1":"Mississippi","2":"372"},{"1":"Missouri","2":"972"},{"1":"Pennsylvania","2":"956"},{"1":"Rhode Island","2":"900"},{"1":"South Carolina","2":"182"},{"1":"South Dakota","2":"584"},{"1":"Tennessee","2":"476"},{"1":"Texas","2":"292"},{"1":"Utah","2":"758"},{"1":"Vermont","2":"158"},{"1":"Virginia","2":"432"},{"1":"Washington","2":"615"},{"1":"West Virginia","2":"974"},{"1":"Wisconsin","2":"842"},{"1":"Wyoming","2":"431"}],"options":{"columns":{"min":{},"max":[10]},"rows":{"min":[5],"max":[5]},"pages":{}}}
  </script>
</div>

---

## My Turn

1. Download the oregon-districts-and-schools.xlsx file into the data-raw folder.

1. Import a new data frame called `oregon_districts_and_schools` from oregon-districts-and-schools.xlsx

1. Merge the `oregon_districts_and_schools` data frame into the `third_grade_math_proficiency` data frame so I can see the names of the schools as well as associated districts

---

## Your Turn

1. Download the oregon-districts.xlsx file into the data-raw folder.

1. Import a new data frame called `oregon_districts` from oregon-districts.xlsx

1. Merge the `oregon_districts` data frame into the `enrollment_by_race_ethnicity` data frame so you can see the names of the districts

---

background-image:url("images/circle-pattern.jpg")

# Renaming Variables

---

## Renaming Variables

```r
french_and_german_speakers_2019 <- left_join(german_speakers_2019,
                                             french_speakers_2019,
                                             by = "state")
```

---

## `rename()`

```r
data_frame %>% 
  rename(new_variable_name = old_variable_name)
```

---

## `rename()`

```r
french_and_german_speakers_2019 <- left_join(german_speakers_2019,
                                             french_speakers_2019,
                                             by = "state") %>% 
* rename(german_speakers = number)
```

```r
french_and_german_speakers_2019
```

---

## `set_names()`

```r
french_and_german_speakers_2019 <- left_join(german_speakers_2019,
                                             french_speakers_2019,
                                             by = "state") %>% 
* set_names("state", "german_speakers", "french_speakers")
```

```r
french_and_german_speakers_2019
```

---

## My Turn

I'll use `rename()` and/or `set_names()` to give the `third_grade_math_proficiency` data frame more meaningful names.

---

## Your Turn

Use either `rename()` or `set_names()` to give the `enrollment_by_race_ethnicity` data frame more meaningful variable names. In particular, change the `pct` variable to something more descriptive.

---

background-image:url("images/files.jpg")

# Quick Interlude to Reorganize Your Code

---

## Your Turn

Reorganize your code so that you only create the `enrollment_by_race_ethnicity` data frame in one place.

---

background-image:url("images/ship-export.jpg")

# Exporting Data

---

## `write_csv()`

```r
write_csv(french_and_german_speakers_2019,
          path = "data/french_and_german_speakers_2019.csv")
```

---

## `write_rds()`

```r
write_rds(french_and_german_speakers_2019,
          path = "data/french_and_german_speakers_2019.rds")
```

---

## My Turn

1. Export my `third_grade_math_proficiency` data frame as a CSV

1. Export my `third_grade_math_proficiency` data frame as an RDS file

---

## Your Turn

Export the `enrollment_by_race_ethnicity` data frame as an RDS file in the data directory (you'll need to make this directory)