R for Data Science Chapter 8

Data Import with readr

Getting Started

These functions are part of readr (which is loaded by tidyverse)

read_csv() reads comma separated values

read_csv2() semicolon separated

read_tsv() Tab delimited files

read_delim()(read any delimited files

read_fwf() reads fixed width files- use fwf_widths() or fwf_positions()

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

read_log reads Apache style log files (also check out webreadr)

They all have similar syntax. The first argument is the path to the file to read.

read_csv(file, col_names = TRUE, col_types = NULL, locale = default_locale(), na = c(“”, “NA”), quoted_na = TRUE, comment = “”, trim_ws = TRUE, skip = 0, n_max = Inf, guess_max = min(1000, n_max), progress = interactive())

library(tidyverse)
read_csv("g:\\Downloads\\Earthquake.csv")

Parsed with column specification:
cols(
  .default = col_double(),
  time = col_datetime(format = ""),
  magType = col_character(),
  nst = col_character(),
  gap = col_integer(),
  net = col_character(),
  id = col_character(),
  updated = col_datetime(format = ""),
  place = col_character(),
  type = col_character(),
  magNst = col_integer(),
  status = col_character(),
  locationSource = col_character(),
  magSource = col_character()
)
See spec(...) for full column specifications.
4175 parsing failures.
 row col               expected actual
1964 gap no trailing characters     .9
1975 gap no trailing characters     .4
1987 gap no trailing characters     .7
1991 gap no trailing characters     .8
2007 gap no trailing characters     .6
.... ... ...................... ......
See problems(...) for more details.

You can use skip argument to skip the first few lines - typically used for skipping blank lines.

library(tidyverse)
read_csv("g:\\Downloads\\Earthquake.csv", skip = 2)

Missing column names filled in: 'X8' [8]Duplicated column names deduplicated: 'us' => 'us_1' [22], 'us' => 'us_2' [23]Parsed with column specification:
cols(
  .default = col_double(),
  `2017-04-10T10:38:53.620Z` = col_datetime(format = ""),
  mb = col_character(),
  X8 = col_character(),
  `45` = col_integer(),
  us = col_character(),
  us10008g7h = col_character(),
  `2017-04-10T12:42:59.585Z` = col_datetime(format = ""),
  `137km ESE of Pondaguitan, Philippines` = col_character(),
  earthquake = col_character(),
  `204` = col_integer(),
  reviewed = col_character(),
  us_1 = col_character(),
  us_2 = col_character()
)
See spec(...) for full column specifications.
4175 parsing failures.
 row col               expected actual
1962  45 no trailing characters     .9
1973  45 no trailing characters     .4
1985  45 no trailing characters     .7
1989  45 no trailing characters     .8
2005  45 no trailing characters     .6
.... ... ...................... ......
See problems(...) for more details.

You can supply inline CSV (Each row must be in a new line.)

read_csv("a,b,c
         1,2,3
         4,5,6")

You can use colnames = FALSE to tell R that there are no headers. (If you get Unused argument error, make sure that you are using read_csv and not read.csv)

library(tidyverse)
read_csv("g:\\Downloads\\Earthquake.csv", col_names = FALSE)

Parsed with column specification:
cols(
  .default = col_character()
)
See spec(...) for full column specifications.

You can also use header = FALSE when your csv data does not have headers in them.

library(tidyverse)
read.csv("g:\\Downloads\\Earthquake.csv", header = FALSE)

Or you can pass character vectors for column names (doesnt work)

library(tidyverse)
read_csv(file = "g:\\Downloads\\query-noheader.csv", 
         col_names = c("x","y","z","wilson","jane","William"))

Parsed with column specification:
cols(
  x = col_datetime(format = ""),
  y = col_double(),
  z = col_double(),
  wilson = col_integer(),
  jane = col_double(),
  William = col_character()
)
70 parsing failures.
row col  expected     actual
  1  -- 6 columns 22 columns
  2  -- 6 columns 22 columns
  3  -- 6 columns 22 columns
  4  -- 6 columns 22 columns
  5  -- 6 columns 22 columns
... ... ......... ..........
See problems(...) for more details.

How to treat NA

To specify the values or values that are used to repesent missing values in your table.

read_csv("a,b,c
         1,2,-", 
         na ="-")

Compared to Base R

There are two main alternatives to readr: base R and data.table’s fread(). The most important differences are discussed below.

Base R

Compared to the corresponding base functions, readr functions:

Use a consistent naming scheme for the parameters (e.g. col_names and col_types not header and colClasses).

Are much faster (up to 10x).

Leave strings as is by default, and automatically parse common date/time formats.

Have a helpful progress bar if loading is going to take a while.

All functions work exactly the same way regardless of the current locale. To override the US-centric defaults, use locale().

They produce tibbles. Base R function inherit some behavior from your OS and environment variables, so import code that works on one computer might not work on other computers.

data.table and fread()

data.table has a function similar to read_csv() called fread. Compared to fread, readr functions:

Are slower (currently ~1.2-2x slower. If you want absolutely the best performance, use data.table::fread().

Use a slightly more sophisticated parser, recognising both doubled (“”“”) and backslash escapes (“"”), and can produce factors and date/times directly.

Forces you to supply all parameters, where fread() saves you work by automatically guessing the delimiter, whether or not the file has a header, and how many lines to skip.

Are built on a different underlying infrastructure. Readr functions are designed to be quite general, which makes it easier to add support for new rectangular data formats. fread() is designed to be as fast as possible.

Exercises

Other arguments:
read_csv(file, col_names = TRUE, col_types = NULL, locale = default_locale(), na = c(“”, “NA”), quoted_na = TRUE, comment = “”, trim_ws = TRUE, skip = 0, n_max = Inf, guess_max = min(1000, n_max), progress = interactive())

Parsing a Vector

The parse_* () functions take a character vector and return a more specialized vector like a logical, integer, or date.

str(parse_logical(c("TRUE","FALSE","NA")))

 logi [1:3] TRUE FALSE NA

str(parse_integer(c("1","2","3")))

 int [1:3] 1 2 3

str(parse_date(c("2001-01-01","1979-10-14")))

 Date[1:2], format: "2001-01-01" "1979-10-14"

The na argument specifies which strings should be treated as missing.

parse_integer(c("1","2",".","4"), na=".")

[1]  1  2 NA  4

If parsing fails, you will get a warning. Plus, the failures will be missing in the output. If there are many failures, better use problems() to get the complete set.

x <-  parse_integer(c("123","345","abc","123.45"))

2 parsing failures.
row col               expected actual
  3  -- an integer                abc
  4  -- no trailing characters    .45

x

[1] 123 345  NA  NA
attr(,"problems")

problems(x)

Eight particualarly important parsers:

parse_logical() for true or false values

parse_integer() for whole numbers

parse_double() strict numeric parser

parse_number()

parse_character() be aware of character encodings

parse_factor() creates factors for categorical values

Parse_datetime()

parse_date()

parse_time

x <- parse_number("$10,000")
y <- parse_number("10%")
z <- parse_number("It cost $123.56")
x

[1] 10000

y

[1] 10

z

[1] 123.56

Using locale argument to specify , as the decimal point

parse_double("1.23")

[1] 1.23

parse_double("1,23", locale = locale(decimal_mark = ","))

[1] 1.23

Using grouping mark.

# US
parse_number("$123,456,789.00")

[1] 123456789

# Europe
parse_number("$123.456.789,00",
             locale=locale(grouping_mark = "."))

[1] 123456789

# Switzerland
parse_number("$123'456'789.00",
             locale=locale(grouping_mark = "'"))

[1] 123456789

Readr’s default locale is US centric.

Strings

# reader uses UTF-8 encoding 
charToRaw("Wilson was here")

 [1] 57 69 6c 73 6f 6e 20 77 61 73 20 68 65 72 65

x1 <-  "El Ni\xf1o was particularly bad this year"
parse_character(x1, locale = locale(encoding = "Latin1"))

[1] "El Niño was particularly bad this year"

If you dont know the encoding method that was used, you can try guess_encoding() function

guess_encoding(charToRaw(x1))

Factors

Parse_factor() to generate cateorical variables that have a known set of possible values.

fruit <-  c("apple","banana")
parse_factor(c("apple","banana", "bananana"), levels = fruit)

1 parsing failure.
row col           expected   actual
  3  -- value in level set bananana

[1] apple  banana <NA>  
attr(,"problems")

Levels: apple banana

Dates, Date-times and Times

We can use 3 different parsers depending on whether we want date, time or date-time.

Parse_date() expects an ISO8601 date-time.

parse_datetime("2010-10-01T2010")

[1] "2010-10-01 20:10:00 UTC"

# if time is omitted, it will be set to midnight
parse_datetime("2010-10-01")

[1] "2010-10-01 UTC"

Parse_date expects a four digit year, a - or /, the month, a - or / then the day.

parse_date("2010-10-01")

[1] "2010-10-01"

parse_date("2010/10/01")

[1] "2010-10-01"

parse_date("2010-10/01")

[1] "2010-10-01"

parse_date("10-01-2010")

1 parsing failure.
row col   expected     actual
  1  -- date like  10-01-2010

[1] NA

Parse_time() expect the hour, : minutes, optionally : and seconds and an optional am/pm specifier

library(hms)
parse_time("1:10 am")

01:10:00

parse_time("02:10 am")

02:10:00

parse_time("20:10:01")

20:10:01

parse_time("8:10:01 pm")

20:10:01

You can supply your own data formats.

Year: “%Y” (4 digits). “%y” (2 digits); 00-69 -> 2000-2069, 70-99 -> 1970-1999.

Month: “%m” (2 digits), “%b” (abbreviated name in current locale), “%B” (full name in current locale).

Day: “%d” (2 digits), “%e” (optional leading space)

Hour: “%H”

Minutes: “%M”

Seconds: “%S” (integer seconds), “%OS” (partial seconds)

Time zone: “%Z” (as name, e.g. “America/Chicago”), “%z” (as offset from UTC, e.g. “+0800”)

AM/PM indicator: “%p”.

Non-digits: “%.” skips one non-digit character, “%+” skips one or more non-digit characters, “%*" skips any number of non-digits characters.

Automatic parsers: “%AD” parses with a flexible YMD parser, “%AT” parses with a flexible HMS parser.

Shortcuts: “%D” = “%m/%d/%y”, “%F” = “%Y-%m-%d”, “%R” = “%H:%M”, “%T” = “%H:%M:%S”, “%x” = “%y/%m/%d”.

parse_date("01/02/15", "%m/%d/%y")

[1] "2015-01-02"

parse_date("01/02/15", "%d/%m/%y")

[1] "2015-02-01"

parse_date("01/02/15", "%y/%m/%y")

[1] "2015-02-01"

parse_date("01/02/15", "%D")

[1] "2015-01-02"

parse_date("01/02/15", "%x")

[1] "2001-02-15"

parse_date("1 January 2015", "%d %B %Y")

[1] "2015-01-01"

parse_date("1 Jan 2015", "%d %b %Y")

[1] "2015-01-01"

Parsing a File

readr uses a heuristic to figure out the type of each column: it reads the first 1000 rows and uses some moderately conservative heuristics to figure out the type of each column. The heuristic tries each of the following types, stopping when it finds a match. Logical, integer, double, number, time, date, date-time. Catch all is string.

challenge <-  read_csv(readr_example("challenge.csv"))

Parsed with column specification:
cols(
  x = col_integer(),
  y = col_character()
)
1000 parsing failures.
 row col               expected             actual
1001   x no trailing characters .23837975086644292
1002   x no trailing characters .41167997173033655
1003   x no trailing characters .7460716762579978 
1004   x no trailing characters .723450553836301  
1005   x no trailing characters .614524137461558  
.... ... ...................... ..................
See problems(...) for more details.

problems(challenge)

The error message shows that the integer column contains data with decimals in it. So we use parse_double.

challenge <-  read_csv(readr_example("challenge.csv"),
col_types = cols(
  x = col_double(),
  y = col_character()
)
)
# look at the last few rows
tail(challenge)

Aha Y is a date field, not a character field.

challenge <-  read_csv(readr_example("challenge.csv"),
col_types = cols(
  x = col_double(),
  y = col_date()
)
)
# look at the last few rows
tail(challenge)

It is best practice to use col_types to ensure consistent and reproducible data import script. To be really strict, we can also use stop_for_problems() which will throw an error and stop the script if there are any errors.

Other Strategies

Using guess_max argument

challenge2 <-  read_csv(
  readr_example("challenge.csv"),
  guess_max = 1001
  )

Parsed with column specification:
cols(
  x = col_double(),
  y = col_date(format = "")
)

challenge2

Sometimes it is easier to diagnose problems if you just read in all the columns as character vectors.

challenge2 <-  read_csv(
  readr_example("challenge.csv"),
  col_types = cols(.default = col_character())
  )
challenge2

This is useful in conjunction with the type_convert() which applies the parsing heuristics to the character columns in a data frame.

df <-  tribble(
  ~x, ~ y, 
  "1", "1.21",
  "2","2.32",
  "3", "4.56"
)
df

# Note the column types after the convertion
type_convert(df)

Parsed with column specification:
cols(
  x = col_integer(),
  y = col_double()
)

Note: for very large files, you might want to set n_max to a smallish number like 10,000 or 100,000.

Writing to a File

Two functions write_csv and write_tsv(). Always uses UTF-8 encoding Saving dates and date-times in ISO8601 format so they are esily parsed elsewhere.

If you want to export a CSV file to Excel, use write_excel_csv() this writes a special character (a ’byte order mark“) at the start of the file, which tells Excel that you’re using the UTF-8 encoding.

# to append to an existing file. 
# Note: type information is lost when you save to csv format
write_csv(challenge, "Challenge.csv")

But saving in CSV format is a little unreliable for caching interim results. You need to re-create the coumn specification every time you load in. There are two alternatives: writerds() and read_rds() will store in R’s custom binary format called RDS.

write_rds(challenge, "challenge.rds")
read_rds("challenge.rds")

You can also use the feather package to implement a fast binary file format tha tcan be shared across programming languages.

library(feather)
write_feather(challenge, "challenge.feather")
read_feather("challenge.feather")

Notes: Feather is faster than RDS and can be used outside of R but RDS supposrts list-columns which feather does not.

Other Types of Data

haven reads SPSS, Stata and SAS files
readxl reads Excel files (both xls. and xlsx)
DBI allows you to run SQL queries against a database and return a data frame.
jsonlite for hierarchical data
xml2 for XML data
See also Jenny Bryan at https://jennybc.github.io/purrr-tutorial