07-data_import.Rmd

# Data import

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


**Learning objectives:**

- **Read data** from disk using the `readr::read_*()` family of functions.
- **Compare and contrast** the `read_*()` functions with the base R equivalents.
- **Parse character data** into other data types using the `readr::parse_*()` functions.
  - List the complications that can arise when parsing **numerical strings**.
  - Understand how the various **character encodings** can make it tricky to parse characters.
  - Describe the common conventions used for **date/time data**.
- **Diagnose problems** that may arise when using the `read_*()` functions.
- **Write data** to disk using the `readr::write_*()` family of functions.

## Reading data from a file {-}

- Use `{readr}` 📦 (part of the `{tidyverse}`) 
- **CSV** (**c**omma-**s**eparated **v**alues) = most common rectangular data filetype
- 1st row = header row = column names
- Other rows = data
- Columns separated ("delimited") by commas


## `readr::read_csv` {-}

- 1st argument = file (path or URL)
  - `here::here()` = root of project
  - `data` = folder

```{r}
students <- readr::read_csv(here::here('data', 'students.csv'))
```

## Reading from URL {-}

```{r}
students <- readr::read_csv("https://pos.it/r4ds-students-csv")
```

## Transforming data during read {-}

- Can transform data during read:
  - Replace mislabeled NA values
  - Repair non-syntactic column names
  - Specify variable types
  
```{r}
students
```


## Mislabeled NA values {-}

- `read_csv()` default: only empty strings (`""`) = `NA`
- `favourite.food` has `"N/A"` (character), let's treat as `NA`

```{r, echo=TRUE, eval=FALSE}
students <- read_csv("data/students.csv", na = c("N/A", ""))
```

```{r, include=FALSE}
students <- read_csv("data/students.csv", na = c("N/A", ""))
```

```{r}
students
```

## Non-syntactic column names {-}

- \`Student ID\` and \`Full Name\` contain spaces (i.e. are **non-syntactic**)

```{r}
students |> 
  rename(
    student_id = `Student ID`,
    full_name = `Full Name`
  )
```


## `janitor` 📦 for non-syntactic column names {-}

[{janitor}](https://sfirke.github.io/janitor/) 📦

- Not part of {tidyverse}
- Handy functions for data cleaning with `|>`
- Eg: `janitor::clean_names()`
  - Uses heuristics to make all colnames snake case

```{r}
students |> janitor::clean_names()
```

## Mislabeled variable types {-}

- The column `meal_plan` is a categorical variable but should be a factor.

- Using `factor()`, the values in the `meal_plan` stayed the same, but the type changed from character (`<chr>`) to factor (`<fct>`).

```{r}
students |>
  janitor::clean_names() |>
  mutate(meal_plan = factor(meal_plan))
```

## Mislabeled variable types cont. {-}

- `age` is a character variable because there's a `five` instead of `5`.

- As seen below, we can use `if_else(test, yes, no)` to say if `age` is the character string `"five"`, make it `"5"`, and if not leave it as `age`.

```{r}
students <- students |>
  janitor::clean_names() |>
  mutate(
    meal_plan = factor(meal_plan),
    age = parse_number(if_else(age == "five", "5", age))
  )

students
```

## Other arguments {-}

- Other arguments in `read_csv()`: 
  - `comment = "#"`
  - `skip = n`
  - `col_names = FALSE`

```{r}
read_csv(
  "a,b,c
  1,2,3
  4,5,6"
)
```

  
## `skip = n` {-}

- Skip the first n lines of metadata to be included at the top of the file.

```{r}
read_csv(
  "The first line of metadata
  The second line of metadata
  x,y,z
  1,2,3",
  skip = 2
)  
```


## `comment = "#"` {-}

- Drop all lines that start with (e.g.) `#`:

```{r}
read_csv(
  "# A comment I want to skip
  x,y,z
  1,2,3",
  comment = "#"
)
```


## `col_names = FALSE` {-}

- Use `col_names = FALSE` to not treat the first row as headings and instead label them sequentially from `X1` to `Xn`:

```{r}
read_csv(
  "1,2,3
  4,5,6",
  col_names = FALSE
)
```

## `col_names` as a character vector {-}

- Pass `col_names` a character vector for column names.

```{r}
read_csv(
  "1,2,3
  4,5,6",
  col_names = c("x", "y", "z")
)
```


## Other types of files {-}

The [`readr`](https://readr.tidyverse.org/) package has other functions beside [`read_csv()`](https://readr.tidyverse.org/reference/read_delim.html) to read files into R, such as:

- [`read_csv2()`](https://readr.tidyverse.org/reference/read_delim.html) for semicolon-separated (`;`) files. 
  - Common in countries that use `,` as the decimal marker.

- [`read_tsv()`](https://readr.tidyverse.org/reference/read_delim.html) for tab-delimited files.

- [`read_delim()`](https://readr.tidyverse.org/reference/read_delim.html) reads in files with any delimiter, it tries to guess the delimiter if not specified.

- [`read_fwf()`](https://readr.tidyverse.org/reference/read_fwf.html) for fixed-width files. 
  - You can specify fields by their widths with `fwf_widths()` or by their positions with `fwf_positions()`.

- [`read_table()`](https://readr.tidyverse.org/reference/read_table.html). Columns are separated by white space.

- [`read_log()`](https://readr.tidyverse.org/reference/read_log.html) for Apache-style log files.

## Controlling column types {-}

- `readr` will try to guess the type of each column (i.e. whether it’s a logical, number, string, etc.) 

- `readr` uses a heuristic to figure out the column types. For each column, it pulls the values of 1,0002 rows and works through the following questions:

  - Does it contain only `F`, `T`, `FALSE`, `TRUE`, `false`, `true`, `f`, or `t` etc.? If so, it’s a logical.
  - Does it contain only numbers (e.g., `1`, `-4.5`, `5e6`, `Inf`)? If so, it’s a number.
  - Does it match the ISO8601 standard? If so, it’s a date or date-time.
  - Otherwise, it must be a string.

- This heuristic works well if you have a clean dataset, but not in real life.

```{r}
read_csv("
  logical,numeric,date,string
  TRUE,1,2021-01-15,abc
  false,4.5,2021-02-15,def
  T,Inf,2021-02-16,ghi
")

```

## Unexpected values {-}

- Most common way column detection fails is a column contains unexpected values so you get a character column.
  - The most common cause is a missing value, recorded using something other than `NA`.

```{r}
simple_csv <- "
  x
  10
  .
  20
  30"

read_csv(simple_csv)
```


## `col_types` {-}

- One approach is to tell `readr` that `x` is a numeric column using `col_types` argument:

```{r}
df <- read_csv(
  simple_csv, 
  col_types = list(x = col_double())
)
```

-  `read_csv()` reports that there was a problem, and tells us we can find out more with `problems()`

```{r}
problems(df)
```

## `problems()` {-}

- `problems()`tells us that there was a problem in row 3, col 1 where readr expected a double but got a `.`. 
- So set `na = "."`.

```{r}
read_csv(simple_csv, na = ".")
```

## Column types {-}

`readr` provides a total of nine column types for you to use:

- `col_logical()` and `col_double()`.

- `col_integer()` Reading integers explicitly can be useful, they occupy half the memory of doubles.

- `col_character()` reads strings, i.e., long series of digits but doesn’t make sense to apply mathematical operations to, i.e. phone numbers, social security numbers, credit card numbers, etc.

- `col_factor()`, `col_date()`, and `col_datetime()` create factors, dates, and date-times respectively.

- `col_number()` is a permissive numeric parser particularly useful for currencies. 

- `col_skip()` skips a column so it’s not included in the result.

##  Overriding the default column {-}

It’s also possible to override the default column by switching from `list()` to `cols()` and specifying `.default`, or to read in only the columns you specify using `cols_only()`.

```{r}
another_csv <- "
x,y,z
1,2,3"

read_csv(
  another_csv, 
  col_types = cols(.default = col_character())
)
```

```{r}
read_csv(
  another_csv,
  col_types = cols_only(x = col_character())
)
```

## Reading data from multiple files {-}

- `read_csv()`  can read data **stored in the same directory** split across multiple files instead of being contained in a single file -> stack them on top of each other in a single data frame.

- `id` argument adds a new column called `file` -> identifies the file the data come from:


```{r}
sales_files <- c("data/01-sales.csv", "data/02-sales.csv", "data/03-sales.csv")
head(stacked_csv <- read_csv(sales_files, id = "file"), 5)
class(stacked_csv)

```

## Reading data from multiple files cont. {-}

- If you have many files you want to read in **stored in the same directory**, you can use the base R `list.files()` function to find the files for you by matching a pattern in the file names. 

```{r}
sales_files <- list.files("data", pattern = "sales\\.csv$", full.names = TRUE)
sales_files
```

## Writing to a file {-}

- `readr` also has functions for writing data back to disk: `write_csv()` and `write_tsv()`. 

- Most important arguments are:
  - `x` (the data frame to save), and 
  - `file` (the location to save it). 
  - Also you may specify missing values with `na` or other, and if you want to append to an existing file.

<p style="color: #104E8B">**IMPORTANT** Note that the variable type information that you just set up is lost when you save to CSV because you’re starting over with reading from a plain text file again.</p>

```{r eval=FALSE}
students
write_csv(students, "students-2.csv")
read_csv("students-2.csv")
```

## RDS {-}

- `write_rds()` and `read_rds()` are uniform wrappers around the base functions `readRDS()` and `saveRDS()`. 

- These store data in R’s custom binary format called RDS, which means that when you reload the object, you are loading the exact same R object that you stored.

```{r eval=FALSE}
write_rds(students, "students.rds")
read_rds("students.rds")

```


## Arrow {-}

- The [`arrow`](https://arrow.apache.org/docs/r/) package allows you to read and write parquet files, a fast binary file format that can be shared across programming languages.

- Parquet tends to be much faster than RDS and is usable outside of R, but does require the `arrow` package.

```{r message=FALSE, eval=FALSE}
library(arrow)
```

``` {r eval=FALSE}
write_parquet(students, "students.parquet")
read_parquet("students.parquet")
```


## Data Entry {-}

- There are two useful functions to help you assemble a tibble “by hand”:
  - [`tibble()`](https://tibble.tidyverse.org/reference/tibble.html), and
  - [`tribble()`](https://tibble.tidyverse.org/reference/tribble.html), short for **tr**ansposed t**ibble**.

- `tibble() ` works by column, and `tribble() ` lets you lay out your data row by row. 

```{r}
tibble(
  x = c(1, 2, 5), 
  y = c("h", "m", "g"),
  z = c(0.08, 0.83, 0.60)
)
```

```{r}
tribble(
  ~x, ~y, ~z,
  1, "h", 0.08,
  2, "m", 0.83,
  5, "g", 0.60
)
```

## Summary {-}

- In this chapter, we’ve learned how csv files work, some of the problems you might encounter, and how to overcome them. 

- We’ll come to data import a few times in this book: 
  - [Chapter 21](https://r4ds.hadley.nz/spreadsheets) from Excel and Google Sheets, 
  - [Chapter 22](https://r4ds.hadley.nz/databases) will show you how to load data from databases, 
  - [Chapter 23](https://r4ds.hadley.nz/arrow) from parquet files, 
  - [Chapter 24](https://r4ds.hadley.nz/rectangling) from JSON, and 
  - [Chapter 25](https://r4ds.hadley.nz/webscraping) from websites.
  
## Exercises {-}

1. What function would you use to read a file where fields were separated with “|”?

> I would use `read_delim()` and include the argument `delim = '|''`. 

2. Apart from file, skip, and comment, what other arguments do read_csv() and read_tsv() have in common?

> `col_names`, `col_types`, `col_select`, `id`, `locale`, `na`, `quoted_na`, `quote`, `comment`, `trim_ws`, `n_max`, `guess_max`, `progress`, `name_repair`, `num_threads`, `show_col_types`, `skip_empty_rows`, `lazy`. 

3. What are the most important arguments to read_fwf()?

> `file`, `fwf_cols`, `fwf_positions`, `fwf_widths`, `fwf_empty`

4. Sometimes strings in a CSV file contain commas. To prevent them from causing problems, they need to be surrounded by a quoting character, like " or '. By default, read_csv() assumes that the quoting character will be ". To read the following text into a data frame, what argument to read_csv() do you need to specify?

> `quote = "''"` to specify that we are using `''` as quotes in a string. 

5. Identify what is wrong with each of the following inline CSV files. What happens when you run the code?

```{r}
read_csv("a,b\n1,2,3\n4,5,6") # Need to include a third column c
read_csv("a,b,c\n1,2\n1,2,3,4") # Seems like they are missing a value in first row c, and there's an extra value in second row c. 
read_csv("a,b\n\"1") # 1 is in quotes, should there be two values? One for each column? 
read_csv("a,b\n1,2\na,b") # second row has the same names as the column names, should they be numbers? 
read_csv("a;b\n1;3") # using ';' to delimit data. Either change it to read_csv2 or use commas ','
```


6. Practice referring to non-syntactic names in the following data frame by:

    a. Extracting the variable called 1.
    b. Plotting a scatterplot of 1 vs. 2.
    c. Creating a new column called 3, which is 2 divided by 1.
    d. Renaming the columns to one, two, and three.

```{r}
annoying <- tibble(
  `1` = 1:10,
  `2` = `1` * 2 + rnorm(length(`1`))
)
```

> Here is my answer 

```{r}
clean <- annoying %>% 
  janitor::clean_names() %>% 
  rename(one = x1, two = x2) %>% # Rename columns
  mutate(three = one/two) # Create a third column 

clean_one <- clean %>% select(one) #Extract column one

# Scatterplot of columns one and two
ggplot(clean, 
       aes(x=one, y=two))+
  geom_point()+
  theme_classic()
```


## Meeting Videos {-}

### Cohort 5 {-}

`r knitr::include_url("https://www.youtube.com/embed/s5b_QkLSYeE")`

<details>
  <summary> Meeting chat log </summary>
  
```
00:07:14	Njoki Njuki Lucy:	Hi Becki!
00:11:14	Ryan Metcalf:	Sorry team, hot spot is getting an update then will switch to computer
00:11:38	Federica Gazzelloni:	Hello Ryan!
00:17:34	Jon Harmon (jonthegeek):	this  that    another
is     fixed  width
00:18:56	Jon Harmon (jonthegeek):	this, that, another
00:21:12	lucus w:	Some of the healthcare data especially in clinical trials are created using fixed width.. the datasets will usually be shipped with some kind of a data dictionary defining the amongst others, the width of each column
00:21:32	lucus w:	So parsing the files will include providing each width specification
00:21:49	Sandra Muroy:	oh interesting! thanks Lucus
00:21:59	Federica Gazzelloni:	👍🏻
00:22:03	Jon Harmon (jonthegeek):	Now that you say that, I've also seen that in some of the US government data shared on data.gov.
00:22:09	lucus w:	Pretty popular in SAS language
00:22:33	Ryan Metcalf:	Excellent statement Lucas. I was initially thinking you’d have to know the data preparation process before choosing the correct read() function.
00:24:34	lucus w:	Yes.. maybe Njoki will get to it as well, but readr uses heuristic process to guess.. so it lazily just works
00:25:07	Federica Gazzelloni:	This is very interesting, I supposed to use read_csv() just with files
00:28:07	Federica Gazzelloni:	how can I use freed() to see the speed?
00:30:06	Jon Harmon (jonthegeek):	It's fread() not freed(). It's in the data.table package. You'd need a fairly large csv to notice the speed difference (and the latest version of readr is faster in some cases).
00:30:15	Federica Gazzelloni:	right
00:31:52	Federica Gazzelloni:	I’d like to know more about problems()
00:36:07	lucus w:	Its 1,000
00:36:20	Federica Gazzelloni:	thanks
00:41:45	Jon Harmon (jonthegeek):	1,000.2
00:41:52	Jon Harmon (jonthegeek):	1.000.2
00:43:12	lucus w:	Oh just read everything as a text and fix them accordingly later
00:43:31	lucus w:	*or
00:43:48	Jon Harmon (jonthegeek):	Yeah, but sometimes fixing them can be painful.
00:44:17	lucus w:	True
00:50:56	Jon Harmon (jonthegeek):	"you tee eff eight"
00:58:15	Jon Harmon (jonthegeek):	> x2 <- "\x82\xb1\x82\xf1\x82\xc9\x82\xbf\x82\xcd"
> parse_character(x2, locale = locale(encoding = "Shift-JIS"))
[1] "こんにちは"
00:58:37	Becki R. (she/her):	japanese
01:02:28	lucus w:	That’s probably a best way of putting it Ryan
01:05:15	Jon Harmon (jonthegeek):	From Wikipedia: "The objective of a heuristic is to produce a solution in a reasonable time frame that is good enough for solving the problem at hand. This solution may not be the best of all the solutions to this problem, or it may simply approximate the exact solution. But it is still valuable because finding it does not require a prohibitively long time."
01:05:44	Shamsuddeen Muhammad:	Like the def
01:05:45	Sandra Muroy:	thanks Jon!
01:12:53	Becki R. (she/her):	I need to get going. See you all next week!
01:13:14	Jon Harmon (jonthegeek):	We'll stop after this section!
01:13:40	lucus w:	date_names_langs()
01:14:12	lucus w:	So for Swahili it will be locale(“sw”)
01:15:08	Ryan Metcalf:	Outstanding job Lucy! Thank you everyone!
```
</details>

`r knitr::include_url("https://www.youtube.com/embed/ctRKvm8Z-_8")`

<details>
  <summary> Meeting chat log </summary>
  
```
00:04:46	Federica G. (she/her):	Hello!
00:05:19	Becki R. (she/her):	Hello!
00:06:09	Becki R. (she/her):	Yes!
00:06:39	Becki R. (she/her):	I'm fine with starting
00:06:40	Federica G. (she/her):	shall we wait until 5 past?
00:06:43	Susie:	Good idea
00:06:48	Becki R. (she/her):	5 past also sounds god
00:06:50	Becki R. (she/her):	good
00:08:17	Becki R. (she/her):	How far did you all get last week?
00:09:07	Becki R. (she/her):	thanks
00:39:13	Becki R. (she/her):	It's a bit muffled
00:41:06	Ryan Metcalf:	https://blog.revolutionanalytics.com/2016/05/feather-package.html
00:41:50	Shamsuddeen Muhammad:	https://github.com/wesm/feather
00:41:52	Ryan Metcalf:	Not direct answer. It appears the feather project “o create a standard data file format that can be used for data exchange by and between R, Python, and any other software that implements its open-source format"
00:43:58	Shamsuddeen Muhammad:	@Ryan that’s right. Also, speed is one of the concern.
00:48:20	Shamsuddeen Muhammad:	A good package I used also readtext: https://github.com/quanteda/readtext
00:50:25	Becki R. (she/her):	do you have a link to the import function information?
00:51:20	Federica G. (she/her):	you need to right click the file name and it appears
00:52:04	Sandra Muroy:	it's also located in the Environment tab
00:52:05	Federica G. (she/her):	the file name in the file pane
00:52:33	Becki R. (she/her):	Ah, thanks.
01:13:11	Ryan Metcalf:	From parse_date(): Month: "%m" (2 digits), "%b" (abbreviated name in current locale), "%B" (full name in current locale).
01:18:25	Ryan Metcalf:	readr() CRAN link: https://cran.r-project.org/web/packages/readr/readr.pdf
01:21:10	Federica G. (she/her):	here are the date formats: https://www.r-bloggers.com/2013/08/date-formats-in-r/
01:22:10	Federica G. (she/her):	at the bottom of the blog you find the codification
01:22:29	Sandra Muroy:	great! thank you Ryan and Federica
01:26:56	Njoki Njuki Lucy:	exercises: https://jrnold.github.io/r4ds-exercise-solutions/data-import.html
01:27:06	Njoki Njuki Lucy:	course book: https://r4ds.had.co.nz/data-import.html
01:27:36	Federica G. (she/her):	thanks
01:27:39	Becki R. (she/her):	thank you!
```
</details>

### Cohort 6 {-}

`r knitr::include_url("https://www.youtube.com/embed/QKB9wy-2_Ps")`

<details>
  <summary> Meeting chat log </summary>
  
```
00:04:29	Adeyemi Olusola:	Hello Daniel. Happy New Year
00:04:35	Adeyemi Olusola:	!!!
00:04:41	Daniel Adereti:	Happy new year, chief!
00:05:07	Daniel Adereti:	Let's give folks some 5 mins to join since it is early in the year
00:05:15	Adeyemi Olusola:	Okay.
00:06:14	Daniel Adereti:	Hello Freya!
00:08:29	Freya Watkins (she/her):	Hi and happy new year :)
00:08:57	Shannon:	Happy new year!
00:09:02	Daniel Adereti:	Happy new year Freya. Hello Shannon!
00:09:22	Adeyemi Olusola:	HI all. HNY!
00:41:09	Shannon:	I haven't used read_fwf()
00:41:43	Daniel Adereti:	Found this short article on fixed width files: https://www.techwalla.com/articles/what-is-a-delimited-a-fixed-width-file
00:42:03	Shannon:	Thanks, Daniel
00:43:16	Daniel Adereti:	The solutions book: https://jrnold.github.io/r4ds-exercise-solutions/data-import.html
00:43:41	Freya Watkins (she/her):	No
01:05:12	Daniel Adereti:	Hello Adeyemi, can you please share the Rmarkdown file you just used?
01:07:46	Shannon:	Thank you, Adeyemi, great lesson
01:07:53	Freya Watkins (she/her):	Thanks!
01:07:58	Daniel Adereti:	Thank you Adeyemi!
01:07:59	Adeyemi Olusola:	Thank you
```
</details>


### Cohort 7 {-}

`r knitr::include_url("https://www.youtube.com/embed/hb7eSpCMGDU")`

<details>
<summary> Meeting chat log </summary>

```
00:06:17	Oluwafemi Oyedele:	Let us give others some time to join !!!
00:08:02	Olukunle Bashir:	alright
00:18:27	Oluwafemi Oyedele:	read_csv function is from readr package
00:18:59	Christine (she/her):	do you need to load the package first?
00:19:25	Oluwafemi Oyedele:	yes @Christine
00:20:01	Oluwafemi Oyedele:	https://readr.tidyverse.org
00:28:16	Christine (she/her):	Thanks!
00:29:05	Oluwafemi Oyedele:	https://jrnold.github.io/r4ds-exercise-solutions/data-import.html
00:34:57	Dolleen Osundwa:	Thanks for this.
00:47:39	Olukunle Bashir:	thank you for the presentation
```
</details>


### Cohort 8 {-}

`r knitr::include_url("https://www.youtube.com/embed/b91ZvatZ1Ug")`