5 Reading Data

R provides a number of ways to read data from a file, the most flexible of which is the scan() function. We use scan to read a vector of values from a file. It has the form:

Example:

scan(file = "", what = 0, n = -1, sep = "", skip = 0, quiet = FALSE)

Remember that scan() returns a vector.

5.1 Tabular Data

It is common for data to be arranged in tables, with columns corresponding to variables and rows corresponding to separate observations. These dataframes are usually read into R using the function read.table(), which has the form:

read.table(file, header = FALSE, sep = "") 
# read.table() returns a dataframe

read_table() reads a common variation of fixed-width files where columns are separated by white space.

There are two commonly used variants of read.table():

5.1.1 `read.csv()`

read.csv() is for comma-separated data and is equivalent to read.table(file, header = TRUE, sep = ",").

Sometimes, it could happen that the file extension is .csv, but the data is not comma separated. In that case, we can still use the read.csv() function, but in this case we have to specify the separator:

Example:

read.csv("iris_semicolon.csv", stringsAsFactors = FALSE, sep=";")
anscombe_tab_2 <- read.table("anscombe.txt", header=TRUE)

Usually, read_csv() uses the first line of the data for the column names, which is a very common convention. But it’s not uncommon for a few lines of metadata to be included at the top of the file. You can use skip = n to skip the first n lines or use comment = "#" to drop all lines that start with (e.g.) #.

Example:

read_csv("data/students.csv,
  skip = 2,
  comment = "#"
)

In other cases, the data might not have column names. You can use col_names = FALSE to tell read_csv() not to treat the first row as headings and instead label them sequentially from \(X_1\) to \(X_n\). Alternatively, you can pass col_names a character vector which will be used as the column names (e.g. col_names = c("x", "y", "z")):

Note that by default read_csv() only recognizes empty strings (““) in as NAs, however you surely want it to also recognize the character string “N/A”.

Example:

students <- read_csv("data/students.csv", na = c("N/A", ""))

If a .csv file contains both numeric and character variables, and we use read.csv(), the character variables get automatically converted to the factor type. We can prevent character variables from this automatic conversion to factor, by specifying stringsAsFactors=FALSE within the read.csv() function.

Example:

read.csv("iris.csv", stringsAsFactors=F)

readr provides a total of nine column types for you to use: col_logical(), col_logical(), col_factor()… Find more information here.

5.1.2 `read.delim()`

read_delim() reads in files with any delimiter, attempting to automatically guess the delimiter if you don’t specify it. For example, read.delim()is for tab-delimited data and is equivalent to read.table(file, header = TRUE, sep = "\t").

Example:

## skips the first 2 lines
anscombe <- read.csv("CSVanscombe.csv", skip=2)

5.1.3 Other file types

Once you’ve mastered read_csv(), using readr’s other functions is straightforward; it’s just a matter of knowing which function to reach for:

read_csv2(): reads semicolon-separated files. These use ; instead of , to separate fields and are common in countries that use , as the decimal marker.
read_tsv(): reads tab-delimited files.
read_fwf(): reads fixed-width files. You can specify fields by their widths with fwf_widths() or by their positions with fwf_positions().
read_log(): reads Apache-style log files.

5.2 Reading Multiple Files

Sometimes your data is split across multiple files instead of being contained in a single file. For example, you might have sales data for multiple months, with each month’s data in a separate file: 01-sales.csv for January, 02-sales.csv for February, and 03-sales.csv for March.

With read_csv()you can read these data in at once and stack them on top of each other in a single data frame.

Example:

sales_files <- c("data/01-sales.csv", "data/02-sales.csv", "data/03-sales.csv")
read_csv(sales_files, id = "file")

The id argument adds a new column called file to the resulting data frame that identifies the file the data come from. This is especially helpful in circumstances where the files you’re reading in do not have an identifying column that can help you trace the observations back to their original sources.

An alternative to read multiple data made available online is this:

Example:

sales_files <- c(
  "https://pos.it/r4ds-01-sales",
  "https://pos.it/r4ds-02-sales",
  "https://pos.it/r4ds-03-sales"
)
read_csv(sales_files, id = "file")

5.3 Reading Line by Line

Text files can be read line by line using the readLines() function:

Example:

con <- gzfile("words.gz") 
x <- readLines(con, 10)

This approach is useful because it allows you to read from a file without having to uncompress the file first, which would be a waste of space and time.

To read in lines of webpages it can be useful the readLines() function:

Example:

con <- url("http://www.jhsph.edu", "r") ## Read the web page 
x <- readLines(con) ## Print out the first few lines head(x)

5.4 Reading Excel Spreadsheets

If the dataset is stored in the .xls or .xlsx format you can use the readxl package. This package is non-core tidyverse, so you need to load it explicitly, but it is installed automatically when you install the tidyverse package.

Most of readxl’s functions allow you to load Excel spreadsheets into R:

read_xls() reads Excel files with xls format.
read_xlsx() read Excel files with xlsx format.
read_excel() can read files with both xls and xlsx format. It guesses the file type based on the input.

Example:

library(tidyverse)
library(readxl)
library(writexl)
(students <- read_excel("data/students.xlsx"))

# A tibble: 6 × 5
  `Student ID` `Full Name`      favourite.food     mealPlan            AGE  
         <dbl> <chr>            <chr>              <chr>               <chr>
1            1 Sunil Huffmann   Strawberry yoghurt Lunch only          4.0  
2            2 Barclay Lynn     French fries       Lunch only          5.0  
3            3 Jayendra Lyne    N/A                Breakfast and lunch 7.0  
4            4 Leon Rossini     Anchovies          Lunch only          <NA> 
5            5 Chidiegwu Dunkel Pizza              Breakfast and lunch five 
6            6 Güvenç Attila    Ice cream          Lunch only          6.0

An important feature that distinguishes spreadsheets from flat files is the notion of multiple sheets, called worksheets. You can read a single worksheet from a spreadsheet with the sheet argument in read_excel(). The default, which we’ve been relying on up until now, is the first sheet.

read_excel("data/penguins.xlsx", sheet = "Torgersen Island")

Alternatively, you can use excel_sheets() to get information on all worksheets in an Excel spreadsheet, and then read the one(s) you’re interested in.

excel_sheets("data/penguins.xlsx")

# Read a worksheet individually
penguins_biscoe <- read_excel("data/penguins.xlsx", sheet = "Biscoe Island", na = "NA")

To know the number of columns and rows of each worksheet you can use the function dim().

dim(penguins_torgersen)

When working with spreadsheet data, it’s important to keep in mind that the underlying data can be very different than what you see in the cell. For example, Excel has no notion of an integer. All numbers are stored as floating points, but you can choose to display the data with a customizable number of decimal points. Similarly, dates are actually stored as numbers, specifically the number of seconds since January 1, 1970. You can customize how you display the date by applying formatting in Excel. Confusingly, it’s also possible to have something that looks like a number but is actually a string (e.g., type ’10 into a cell in Excel).

5.4.1 Reading part of a sheet

It’s quite common to find cell entries in a spreadsheet that are not part of the data you want to read into R. You can use the readxl_example() function to locate the spreadsheet on your system in the directory where the package is installed. This function returns the path to the spreadsheet, which you can use in read_excel()as usual.

students_path <- readxl_example("students.xlsx")
read_excel(students_path, range = "A1:C4")

5.5 Reading Google Sheets

For loading data from a Google Sheet you will be using the googlesheets4 package. This package is non-core tidyverse, so you need to load it explicitly.

library(googlesheets4)
library(tidyverse)

The main function of the googlesheets4 package is read_sheet(), which reads a Google Sheet from a URL or a file id. This function also goes by the name range_read(). You can also create a brand new sheet with gs4_create() or write to an existing sheet with sheet_write() and friends.

The first argument to read_sheet()is the URL of the file to read, and it returns a tibble:

Example:

gs4_deauth()
students <- read_sheet('https://docs.google.com/spreadsheets/d/1V1nPp1tzOuutXFLb3G9Eyxi3qxeEhnOXUzL5_BcCQ0w')

✔ Reading from "students".

✔ Range 'Sheet1'.

students

# A tibble: 6 × 5
  `Student ID` `Full Name`      favourite.food     mealPlan            AGE      
         <dbl> <chr>            <chr>              <chr>               <list>   
1            1 Sunil Huffmann   Strawberry yoghurt Lunch only          <dbl [1]>
2            2 Barclay Lynn     French fries       Lunch only          <dbl [1]>
3            3 Jayendra Lyne    N/A                Breakfast and lunch <dbl [1]>
4            4 Leon Rossini     Anchovies          Lunch only          <NULL>   
5            5 Chidiegwu Dunkel Pizza              Breakfast and lunch <chr [1]>
6            6 Güvenç Attila    Ice cream          Lunch only          <dbl [1]>

It’s also possible to read individual sheets from Google Sheets as well. Let’s read the “Torgersen Island” sheet from the penguins Google Sheet:

penguins_sheet_id <- "1aFu8lnD_g0yjF5O-K6SFgSEWiHPpgvFCF0NY9D6LXnY"
read_sheet(penguins_sheet_id, sheet = "Torgersen Island")

✔ Reading from "penguins".

✔ Range ''Torgersen Island''.

# A tibble: 52 × 8
   species island    bill_length_mm bill_depth_mm flipper_length_mm body_mass_g
   <chr>   <chr>     <list>         <list>        <list>            <list>     
 1 Adelie  Torgersen <dbl [1]>      <dbl [1]>     <dbl [1]>         <dbl [1]>  
 2 Adelie  Torgersen <dbl [1]>      <dbl [1]>     <dbl [1]>         <dbl [1]>  
 3 Adelie  Torgersen <dbl [1]>      <dbl [1]>     <dbl [1]>         <dbl [1]>  
 4 Adelie  Torgersen <chr [1]>      <chr [1]>     <chr [1]>         <chr [1]>  
 5 Adelie  Torgersen <dbl [1]>      <dbl [1]>     <dbl [1]>         <dbl [1]>  
 6 Adelie  Torgersen <dbl [1]>      <dbl [1]>     <dbl [1]>         <dbl [1]>  
 7 Adelie  Torgersen <dbl [1]>      <dbl [1]>     <dbl [1]>         <dbl [1]>  
 8 Adelie  Torgersen <dbl [1]>      <dbl [1]>     <dbl [1]>         <dbl [1]>  
 9 Adelie  Torgersen <dbl [1]>      <dbl [1]>     <dbl [1]>         <dbl [1]>  
10 Adelie  Torgersen <dbl [1]>      <dbl [1]>     <dbl [1]>         <dbl [1]>  
# ℹ 42 more rows
# ℹ 2 more variables: sex <chr>, year <dbl>

You can obtain a list of all sheets within a Google Sheet with sheet_names():

sheet_names(penguins_sheet_id)

[1] "Torgersen Island" "Biscoe Island"    "Dream Island"

Note that we’re also using the gs4_example() function below to locate an example Google Sheet that comes with the googlesheets4 package.

deaths_url <- gs4_example("deaths")
deaths <- read_sheet(deaths_url, range = "A5:F15")

✔ Reading from "deaths".

✔ Range 'A5:F15'.

deaths

# A tibble: 10 × 6
   Name      Profession   Age `Has kids` `Date of birth`     `Date of death`    
   <chr>     <chr>      <dbl> <lgl>      <dttm>              <dttm>             
 1 David Bo… musician      69 TRUE       1947-01-08 00:00:00 2016-01-10 00:00:00
 2 Carrie F… actor         60 TRUE       1956-10-21 00:00:00 2016-12-27 00:00:00
 3 Chuck Be… musician      90 TRUE       1926-10-18 00:00:00 2017-03-18 00:00:00
 4 Bill Pax… actor         61 TRUE       1955-05-17 00:00:00 2017-02-25 00:00:00
 5 Prince    musician      57 TRUE       1958-06-07 00:00:00 2016-04-21 00:00:00
 6 Alan Ric… actor         69 FALSE      1946-02-21 00:00:00 2016-01-14 00:00:00
 7 Florence… actor         82 TRUE       1934-02-14 00:00:00 2016-11-24 00:00:00
 8 Harper L… author        89 FALSE      1926-04-28 00:00:00 2016-02-19 00:00:00
 9 Zsa Zsa … actor         99 TRUE       1917-02-06 00:00:00 2016-12-18 00:00:00
10 George M… musician      53 FALSE      1963-06-25 00:00:00 2016-12-25 00:00:00

When you attempt to read in a sheet that requires authentication, googlesheets4 will direct you to a web browser with a prompt to sign in to your Google account and grant permission to operate on your behalf with Google Sheets. However, if you want to specify a specific Google account, authentication scope, etc. you can do so with gs4_auth(), e.g., gs4_auth(email = "mine@example.com"), which will force the use of a token associated with a specific email.

5.6 RData Format

If you need to store more than one dataset in a single file we can use the *.RData format:

Example:

## example to load multiple datasets, and a vector of R objects from a single *.RData
load("robjects.RData")
## to check if objects have been loaded correctly
objects()

5.7 Import Stata/SPSS File

To import a Stata/SPSS file into R:

First you need to call the foreign library:
```
install.packages("foreign")
```

Then use read.data()/read.spss():

data <- read.spss(file="data.spss", to.data.frame=TRUE)

The output will always be a data frame:

write.foreign(data, "mydata.txt", "mydata.sps", package="SPSS")

5.8 JSON File

To read a JSON file:

install.packages("rjson") 
data <- fromJSON(file="data.json")
data2 <- as.data.frame(data)

5.9 `view()`

To view the data variable, you can use the view().

5.10 Manual Data Entry

Sometimes you’ll need to assemble a tibble “by hand” doing a little data entry in your R script.

There are two useful functions to help you do this which differ in whether you layout the tibble by columns or by rows.

tibble() works by column:

Example:

tidyr::tibble(
  x = c(1, 2, 5), 
  y = c("h", "m", "g"),
  z = c(0.08, 0.83, 0.60)
)

# A tibble: 3 × 3
      x y         z
  <dbl> <chr> <dbl>
1     1 h      0.08
2     2 m      0.83
3     5 g      0.6

tribble() (transposed tibble) lets you lay out your data row by row: column headings start with ~ and entries are separated by commas. This makes it possible to lay out small amounts of data in an easy to read form:

Example:
```
tidyr::tribble(
  ~x, ~y, ~z,
  1, "h", 0.08,
  2, "m", 0.83,
  5, "g", 0.60
)
```
```
# A tibble: 3 × 3
      x y         z
  <dbl> <chr> <dbl>
1     1 h      0.08
2     2 m      0.83
3     5 g      0.6 
```

5.1 Tabular Data

5.1.1 read.csv()

5.1.2 read.delim()

5.1.3 Other file types

5.2 Reading Multiple Files

5.3 Reading Line by Line

5.4 Reading Excel Spreadsheets

5.4.1 Reading part of a sheet

5.5 Reading Google Sheets

5.6 RData Format

5.7 Import Stata/SPSS File

5.8 JSON File

5.9 view()

5.10 Manual Data Entry

5.1.1 `read.csv()`

5.1.2 `read.delim()`

5.9 `view()`