Importing Data

Importing data can be one of the most frustrating things to do in R for the beginner.

Ensuring that you deal with the most likely common issues in advance will help a lot.

1. Get some data

Oh, look! It’s a Harry Potter dataset! We haven’t seen one of those for a while …

Table 1. Box office history for all Harry Potter movies.

2. Preparing your data

Set up your spreadsheet the best way

• First row is usually for the column names (header).

• First column is often used to identify the sampling unit.

• Comments and meta-data kept in separate file or as commented-out rows at top of datasheet.

• See Best Practice lecture on data management.

Data needs to be readable by R

• Column names: short vs long, spaces vs periods

• Avoid symbols that have specific meaning in R: comments #, and single quote/apostrophe '.

• Avoid using the column delimiter within field (e.g., TAB or comma).

• Avoid other symbols unless within text fields: ?, $, %, ^, &, *, (, ), -, ,, <, >, /, |, \, [ , ] , {, }.

• Use international standards for specific fields (e.g., ISO 8601 for dates: YYYY-MM-DD).

• Check numeric field for extra characters, spaces etc. that would indicate to R that it is character.

• Check empty fields: are they empty or do they have invisible white space?

• Either remove text fields with more than one word, or prepare to specify the column separator when you read the data in.

• Check for extra columns/rows in the spreadsheet.

• Make sure that any missing values in your data set are indicated with NA (or your code of choice).

Use a standard file format

• Specific data often have specific standard format (e.g., phylogenetic tree as Newick, nucleotide sequence as FASTA).

• For tabular data use plain-text files:

  • Non-proprietary.
  • Can be opened in any almost any software.
  • Future-compatible.

Known issues with Excel (and other spreadsheet software)

• Auto-complete can introduce errors (especially with numbers).

  • Type out numbers fully.

• Auto-formatting Excel will often reformat input, especially dates.

  • Use separate columns for YYYY, MM, DD, HH, MM, etc. that could be auto-formatted

• Significant figures Excel will not always save, paste, or display the full number in a cell.

  • Make cells as wide as they need to be to fit entire values inside.
  • Copy and paste to text file.

See here and here for more details on Excel and spreadsheets. TL;DR: Do not use spreadsheets for anything apart from entering data!

3. Read your data in to R

R has several functions to read data in.

The read.table() function has the fewest defaults.

The others set typical defaults for the separator/delimitor character, and the decimal character.

You can easily set these in a call to read.table(), too.

Common arguments to all the read.foo() functions:

• file = : The name of the file, in quotes. You can specify an absolute or relative path.

• header = : If the first row of the file contains column names.

• sep = : The character used to separate columns (usually tab or comma).

File type: .txt | delimitor: white space (one or more spaces, tabs, newlines or carriage returns) | no header

read.table()

df <- read.table("my_data.txt", header= TRUE)

File: .csv | delimitor: comma | decimal: period | header

read.csv()

df <- read.csv("my_data.csv")

File: .csv | delimitor: semi-colon | decimal: comma | header

read.csv2()

df <- read.csv2("my_data.csv")

File: .txt | delimitor: tab | decimal: period | header

read.delim()

df <- read.delim("my_data.txt")

File: .txt | delimitor: tab | decimal: comma | header

read.delim2()

df <- read.delim2("my_data.txt")

4. Common issues importing data

i. The number of columns are not what you expected

Often a separator problem (i.e., R is using a different separator character/s to the separator you think/want).

Solution: Specify the sep = "" argument to your read.foo() function call.

ii. There are a lot more columns than you expected!

And often these cells are filled with NA’s.

Did you save.as within Excel?

It is likely you have some whitespace entered into cells to the right of the data in the spreadsheet, and Excel saved all those columns as well.

Solution: Select and delete all columns to the right of your data OR (better) copy and paste data into a plain-text file.

iii. White space in factors

You should have already removed extra white space from your data.

To check:

• In Excel sort all the data by the factor columns and check that both ends are the same.
• In R, run table() on the columns of interest, this will highlight any differences.

Further, the default separator character in R (sep = "") is generic white space, i.e., one or more spaces, tabs, newlines or carriage returns. If you change the separator, this default is gone.

Solution: Check your data for white space and remove it before you read it in, or you can set another argument to TRUE (strip.white = TRUE), to remove extra white space.

iv. Columns are not the kind of data you expect

If R sees numbers in a column, it will assume that it is numeric or integer.

If any other character is present, it will assume it is a character or factor variable.

Thus any comments or text (e.g., “missing”, “about 2”, “?”, etc.), will cause the entire numeric column to be treated as character.

Use summary() or table() to check all your columns carefully. Are they correct?

Solutions:

• Specify or correct missing value characters.
• Specify or correct decimal character (period vs comma).

Further Reading

Crawley, M. The R Book. Ch. 3 Data Input

Borer, et al. 2009. Some simple guidelines for effective data management. Bulletin of the ESA 90, 205–214.

White et al. 2013. Nine simple ways to make it easier to (re)use your data. Ideas in Ecology & Evolution 6. doi: https://doi.org/10.4033/iee.v6i2.4608