Installation and Resources

Tidy data

1. Each variable must have its own column.
2. Each observation must have its own row.
3. Each value must have its own cell.

Initialize

Load necessary libraries

library("readxl")  # Import the data from Excel file
library("readr")  # Import the data from Excel file
library("dplyr")  # filter and reformat data frames
library("tidyr")  # make data tidy
library("stringr")  # manipulate strings
library("lubridate")  # manipulate date 
library("ggplot2")  # graphics

Oceanographic data

CARBOM cruise off Brazil

Oceanographic data

CARBOM cruise off Brazil

Oceanographic data

Microbial populations

Oceanographic data

Microbial populations

• Flow cytometry :
  • pico-eukaryotes
  • nano-eukaryotes

Read data

Text file - TAB delimited

Read data

Reading a text file

samples <- readr::read_tsv("data/CARBOM data.txt")

Read data

Reading a text file

samples <- readr::read_tsv("data/CARBOM data.txt")

Read data

Reading a text file

samples <- readr::read_tsv("data/CARBOM data.txt")

• readr::read_tsv() : read tab delimited files

• readr::read_csv() : read comma delimited files

• readr::write_tsv() : write tab delimited files

Read data

Excel sheet

Read data

Read the data - read_excel

samples <- readxl::read_excel("data/CARBOM data.xlsx", sheet = "Samples_boat")

Read data

Read the data - read_excel

samples <- readxl::read_excel("data/CARBOM data.xlsx", sheet = "Samples_boat")

• Can also select a range : e.g. A1:Q26
• Can skip lines

Read data

Bad data input under Excel

• There are missing values in the column station because only recorded when changed

Read data

Filling missing values - fill

samples <- tidyr::fill(samples, station)

Read data

Filling missing values - fill

samples <- tidyr::fill(samples, station)

• All missing values have been filled in.

Read data

dplyr - Manipulate tables

@allison_horst

Manipulate columns

List columns

colnames(samples)

 [1] "sample number" "transect"      "station"       "date"         
 [5] "time"          "depth"         "level"         "latitude"     
 [9] "longitude"     "picoeuks"      "nanoeuks"      "phosphates"   
[13] "nitrates"      "temperature"   "salinity"

Manipulate columns

Summarize columns

summary(samples$depth)

   Min. 1st Qu.  Median    Mean 3rd Qu.    Max.    NA's 
    5.0     5.0    50.0    56.6   100.0   140.0       3

Manipulate columns

Select specific columns - select

samples_select <- dplyr::select(samples, transect, `sample number`, station, 
    depth, latitude, longitude, picoeuks, nanoeuks)

Manipulate columns

Select specific columns - select

samples_select <- dplyr::select(samples, transect, `sample number`, station, 
    depth, latitude, longitude, picoeuks, nanoeuks)

Manipulate columns

Select a range of columns - select

samples_select <- dplyr::select(samples, transect:nanoeuks)

Manipulate columns

Select a range of columns - select

samples_select <- dplyr::select(samples, transect:nanoeuks)

Manipulate columns

Unselect columns - select

samples_select <- dplyr::select(samples, -nitrates, -phosphates)

Manipulate columns

Unselect columns - select

samples_select <- dplyr::select(samples, -nitrates, -phosphates)

Manipulate columns

Using the pipe operator - %>%

samples_select <- samples %>% dplyr::select(transect:nanoeuks)

Manipulate columns

Using the pipe operator - %>%

samples_select <- samples %>% dplyr::select(transect:nanoeuks)

• It is cleaner to write on 2 lines

  samples_select <- samples %>% 
    dplyr::select(transect:nanoeuks)

Manipulate columns

Renaming variables - rename

samples <- samples %>% dplyr::rename(sample_number = `sample number`)

Manipulate columns

Renaming variables - rename

samples <- samples %>% dplyr::rename(sample_number = `sample number`)

Manipulate columns

Creating new variables - mutate

  samples <- samples %>% 
    dplyr::mutate(pico_pct = picoeuks/(picoeuks+nanoeuks)*100)

Manipulate columns

Creating new variables - mutate

  samples <- samples %>% 
    dplyr::mutate(pico_pct = picoeuks/(picoeuks+nanoeuks)*100)

• You can also use transmute() but then it will drop all the other columns.
• It is much much better to compute new variables in R than in Excel, because you can easily track and correct errors.

Manipulate columns

Using the pipe operator you can chain operations

  samples_select <- samples  %>% 
    dplyr::select(sample_number:nanoeuks, level) %>% 
    dplyr::mutate(pico_pct = picoeuks/(picoeuks+nanoeuks)*100)

Manipulate columns

Using the pipe operator you can chain operations

  samples_select <- samples  %>% 
    dplyr::select(sample_number:nanoeuks, level) %>% 
    dplyr::mutate(pico_pct = picoeuks/(picoeuks+nanoeuks)*100)

Manipulate columns

Creating labels with mutate and stringr functions

  samples <- samples %>% 
    dplyr::mutate(sample_label = str_c("TR",transect,"St",station, sep="_"))

Manipulate columns

Creating labels with mutate and stringr functions

  samples <- samples %>% 
    dplyr::mutate(sample_label = str_c("TR",transect,"St",station, sep="_"))

Manipulate columns

Changing type of some columns - mutate

  samples <- samples %>% 
    dplyr::mutate(time = str_c(lubridate::hour(time), 
                               lubridate::minute(time), sep=":"))

Manipulate columns

Changing type of some columns - mutate

  samples <- samples %>% 
    dplyr::mutate(time = str_c(lubridate::hour(time), 
                               lubridate::minute(time), sep=":"))

• Use the lubridate package to manipulate dates

Manipulating rows

Order rows - arrange

samples <- samples %>% dplyr::arrange(transect, station)

Manipulating rows

Order rows - arrange

samples <- samples %>% dplyr::arrange(transect, station)

• Station 6 is not ordered numerically. It is because station is a character column.

Manipulating rows

Order rows - transform to numeric

  samples <- samples %>% 
    dplyr::mutate(station = as.numeric(station)) %>% 
    dplyr::arrange(transect, station)

Warning in evalq(as.numeric(station), <environment>): NAs introduced by
coercion

Manipulating rows

Order rows - transform to numeric

  samples <- samples %>% 
    dplyr::mutate(station = as.numeric(station)) %>% 
    dplyr::arrange(transect, station)

Warning in evalq(as.numeric(station), <environment>): NAs introduced by
coercion

• One station named "Bloom" could not be converted to numerical (-> NA)

Manipulating rows

Summarize rows - group_by / summarize

• Group by transect and station
• Compute mean of the percent picoplankton

  samples_mean <- samples %>% 
    dplyr::group_by(transect, station) %>% 
    dplyr::summarise(n_samples = n(), 
              mean_pico_percent = mean(pico_pct, na.rm=TRUE))

Manipulating rows

Summarize rows - group_by / summarize

• Group by transect and station
• Compute mean of the percent picoplankton

  samples_mean <- samples %>% 
    dplyr::group_by(transect, station) %>% 
    dplyr::summarise(n_samples = n(), 
              mean_pico_percent = mean(pico_pct, na.rm=TRUE))

Manipulating rows

Filtering rows - filter

• Get only the surface samples

samples_surf <- samples %>% dplyr::filter(level == "Surf")

Manipulating rows

Filtering rows - filter

• Get only the surface samples

samples_surf <- samples %>% dplyr::filter(level == "Surf")

• ! Use the logical operators == != > >= < <= is.na()

Joining tables

Joining tables

Very often you have tables that contain a common field and that you need to join together.

Joining tables

Very often you have tables that contain a common field and that you need to join together.

A common example in oceanography. After a cruise you have many tables

Tables :

• Stations - Station #, Longitude, Latitude
• Cast (CTD) - Station #, Cast #, Depth, Temp, Sal (continuous)
• Bottles - Cast #, Depth, Bottle #
• Water samples - Bottle #, Sample #
• Biological samples - Sample #, Analysis #

You want to know the Longitude and Latitude of a given biological sample.

Joining tables

Very often you have tables that contain a common field and that you need to join together.

A common example in oceanography. After a cruise you have many tables

Tables :

• Stations - Station #, Longitude, Latitude
• Cast (CTD) - Station #, Cast #, Depth, Temp, Sal (continuous)
• Bottles - Cast #, Depth, Bottle #
• Water samples - Bottle #, Sample #
• Biological samples - Sample #, Analysis #

You want to know the Longitude and Latitude of a given biological sample.

Analysis # -> Sample # -> Bottle # -> Cast # -> Station # -> Long, Lat

In order to join 2 tables, they must have a common field. It is called the KEY.

For example it can be Bottle #, Station #

Joining tables

Sequence samples (metabarcoding)

• Each sample has been split into 2 fractions by sorting : pico- and nano
• These separate samples have then been sequenced to determine the composition of the plankton community

Joining tables

Sequence samples (metabarcoding)

• Each sample has been split into 2 fractions by sorting : pico- and nano
• These separate samples have then been sequenced to determine the composition of the plankton community

Joining tables

Reading table with sequence samples

  sequences <- readxl::read_excel("data/CARBOM data.xlsx", 
                           sheet = "Samples_sequencing")

Joining tables

• Table sequences

  sample	sample_number	fraction	n_sequences
  X10n	10	Nano	53230
  X10p	10	Pico	47390
  X11n	11	Nano	24007
  X11p	11	Pico	31899
  X120n	120	Nano	70455

• Table samples

• The two tables have a common field called sample_number (KEY).

Joining tables

Joining sequence and sample tables.

sequences_join <- left_join(sequences, samples_select)

Joining tables

Joining sequence and sample tables.

sequences_join <- left_join(sequences, samples_select)

Joining tables

Joining columns with different names

• If the KEY do not have the same name in the two tables it is possible to specify the name of the two columns used for joining.

  sequences <- sequences %>% 
    rename(sample_code = sample_number)

Joining tables

Joining columns with different names

• If the KEY do not have the same name in the two tables it is possible to specify the name of the two columns used for joining.

  sequences <- sequences %>% 
    rename(sample_code = sample_number)

Joining tables

Joining columns with different names

  sequences_join <- left_join(sequences, samples_select, 
                            by= c("sample_code" = "sample_number"))

Joining tables

Joining columns with different names

  sequences_join <- left_join(sequences, samples_select, 
                            by= c("sample_code" = "sample_number"))

Joining tables

Joining with missing data

• Let us remove some samples from the sample table

  samples_select <- samples_select %>% 
    filter(sample_number != "10")

Joining tables

Joining with missing data

• Let us remove some samples from the sample table

  samples_select <- samples_select %>% 
    filter(sample_number != "10")

Joining tables

Joining with missing data

  sequences_join <- left_join(sequences, samples_select, 
                            by= c("sample_code" = "sample_number"))

Joining tables

Joining with missing data

  sequences_join <- left_join(sequences, samples_select, 
                            by= c("sample_code" = "sample_number"))

Wide vs long tables

@allison_horst

Wide vs long tables

Go from wide to long - gather

• This is very useful for statistical and plotting purposes

Wide vs long tables

Go from wide to long - gather

  samples_long <- samples_select  %>% 
    tidyr::gather(key="population", value="cell_ml", picoeuks, nanoeuks)

Wide vs long tables

Go from wide to long - gather

  samples_long <- samples_select  %>% 
    tidyr::gather(key="population", value="cell_ml", picoeuks, nanoeuks)

Wide vs long tables

Go from long to wide - spread

Wide vs long tables

Go from long to wide - spread

  samples_wide <- samples_long  %>% 
    tidyr::spread(key="population", value="cell_ml")

Wide vs long tables

Go from long to wide - spread

  samples_wide <- samples_long  %>% 
    tidyr::spread(key="population", value="cell_ml")

Next time: Data visualization (ggplot2)

• Chapter 28 of R for data science
• Fundamental of data visualization
• Data visualization: practical introduction