R Exercises - Load and plot multiple files

Download data files

In this typical exercise, we will learn how to read multiple files and plot them all together. This is the basic step of data analysis.

Reading the data files

  • Load the tidyverse and readxl libraries
  • Store into flist the list of all .xls files in the Data/ folder. Take a look at the list.files() function for this, and especially at its parameters path, pattern, and full.names.
flist <- list.files(path="Data", pattern = ".xls", full.names = TRUE)
  • Open one of the .xls files with Excel and study the structure of the file. At the top of the file, the first group of 2-column data is the blank measurement (columns A and B, lines 8 through 3408), while the second group under the blank is the actual raw measurement (columns A and B, lines 3413 through 6813). Working on the first file in the list, successively:
    • Read and store the blank data in a tibble called blank.df
    • Read and store the raw data in a tibble called raw.df
    • Combine them into a tibble called alldata containing the columns w, blank, raw, and transmittance, the later being the transmittance (in percent) with respect to the blank measurement (i.e. transmittance = raw / blank * 100).

The final table should look like this:

blank.df <- read_excel(flist[1], 
            range = "A8:B3408", 
            col_names = c("w","blank"))
raw.df   <- read_excel(flist[1], 
            range = "A3413:B6813", 
            col_names = c("w","raw"))
# Base R version
alldata <- tibble(blank.df, raw=raw.df$raw)
alldata$transmittance <- alldata$raw/alldata$blank*100
alldata
# A tibble: 3,401 × 4
       w blank   raw transmittance
   <dbl> <dbl> <dbl>         <dbl>
 1  4000   100  94.2          94.2
 2  3999   100  94.3          94.3
 3  3998   100  94.2          94.2
 4  3997   100  94.2          94.2
 5  3996   100  94.2          94.2
 6  3995   100  94.2          94.2
 7  3994   100  94.2          94.2
 8  3993   100  94.2          94.2
 9  3992   100  94.2          94.2
10  3991   100  94.2          94.2
# ℹ 3,391 more rows
# Tidyverse version:
# alldata <- inner_join(blank.df, raw.df, by = "w") |> 
#     mutate(transmittance=raw/blank*100)
  • Wrap this procedure into a function readFTIR() that, given a file name, returns a tibble with the columns w, blank, raw, and transmittance. This means you just need to do the same thing as before but providing the file name as a variable, and finish the function by printing the alldata tibble – if you don’t print it as the last action of your function, it won’t return the tibble. Test it on the first file in the list.
readFTIR <- function(filename) {
    blank.df <- read_excel(filename, 
                range = "A8:B3408", 
                col_names = c("w","blank"))
    raw.df   <- read_excel(filename, 
                range = "A3413:B6813", 
                col_names = c("w","raw"))
    # Base R version
    alldata <- tibble(blank.df, raw=raw.df$raw)
    alldata$transmittance <- alldata$raw/alldata$blank*100
    alldata
    # Tidyverse version:
    # inner_join(blank.df, raw.df, by = "w") |> 
    #     mutate(transmittance=raw/blank*100)
}
  • Plot the first file in flist, and try to make your plot look like this:
readFTIR(flist[1]) |> 
    ggplot(aes(x=w, y=raw))+
        geom_line(size=1)+
        geom_line(aes(y=blank), lty=2, col="gray")+
        labs(x=expression(paste("Wavenumber [", cm^{-1},"]")),
             y="Transmittance [%]")+
        scale_x_continuous(breaks=seq(0,5000,500))+
        theme_bw()+
        theme(panel.grid=element_blank(),
              text=element_text(size=16))

  • Now we want to read all files in the list and store them in a tidy tibble. For this we will start easy and use a for loop. This is the step-by-step procedure:
    • Define an empty tibble called df
    • Define a for loop on all elements of flist
    • Then, within this for loop, successively:
      • Read the current file with the above-defined readFTIR() function and store it in tempdf
      • Add the column name to tempdf containing the current file name. If needed, trim the path from the name string using basename().
      • Stack tempdf below df by binding them row-wise (look at bind_rows()).

The final table should look like this:

df <- tibble()
for(file in flist){
    tempdf <- readFTIR(file)
    tempdf$name <- basename(file)
    df <- bind_rows(df, tempdf)
}
df
# A tibble: 34,010 × 5
       w blank   raw transmittance name            
   <dbl> <dbl> <dbl>         <dbl> <chr>           
 1  4000   100  94.2          94.2 sample_0_25C.xls
 2  3999   100  94.3          94.3 sample_0_25C.xls
 3  3998   100  94.2          94.2 sample_0_25C.xls
 4  3997   100  94.2          94.2 sample_0_25C.xls
 5  3996   100  94.2          94.2 sample_0_25C.xls
 6  3995   100  94.2          94.2 sample_0_25C.xls
 7  3994   100  94.2          94.2 sample_0_25C.xls
 8  3993   100  94.2          94.2 sample_0_25C.xls
 9  3992   100  94.2          94.2 sample_0_25C.xls
10  3991   100  94.2          94.2 sample_0_25C.xls
# ℹ 34,000 more rows
  • More advanced: you can do this without using a for loop using the tidyverse. For this, look into the map() function as described here.
df  <- tibble(name = flist) |> 
    mutate(data=map(name, ~readFTIR(.)),
           name=basename(name)) |> 
    unnest(data)
write.csv(df,"Data/alldata.csv", row.names=FALSE)

Plotting

In case you didn’t manage to do the above procedure, just load the alldata.csv file in the Data folder by running df <- read_csv("Data/alldata.csv").

We will now plot all these files together using ggplot2.

  • First, you can try several things, like:
    • Plot them all together in one frame with a color depending on the file name.
    • Plot them all on a different frame (look at facet_wrap()) for each file.

The final plots should look like this:

theme_set(theme_bw()+
          theme(strip.background = element_blank(),
                strip.text = element_text(face="bold")))
ggplot(data=df, aes(x=w, y=transmittance, color=name))+
    geom_line()+
    labs(x=expression(paste("Wavenumber [", cm^{-1},"]")),
         y="Transmittance [%]")

ggplot(data=df, aes(x=w, y=transmittance))+
    geom_line()+
    labs(x=expression(paste("Wavenumber [", cm^{-1},"]")),
         y="Transmittance [%]")+
    facet_wrap(~name)

  • You see in the file names that there are actually 5 samples with 2 temperatures for each sample. Based on the column name of the df tibble, create the columns sample and temperature that contain numerical values of the sample number and temperature. Think about using the function gsub() to replace (delete) text in a string, the substr(string, start, end) function to get a portion of a string. You can also take a look at the separate() function from the tidyverse (see here).
# Base version
df$sample      <- as.numeric(substr(df$name,8,8))
df$temperature <- substr(df$name,10,20)
df$temperature <- as.numeric(gsub("C.xls","",df$temperature))
# tidyverse version
df  <- tibble(name = flist) |> 
    mutate(data=map(name, ~readFTIR(.)),
           name=basename(name)) |> 
    separate(name, c(NA, "sample","temperature",NA), convert = TRUE) |> 
    mutate(temperature=as.numeric(gsub("C","",temperature))) |> 
    unnest(data)
  • Now it is time to make nicer plots:
    • Make a grid plot showing the data evolution for each sample as a function of temperature
    • Make a grid plot with one frame per sample, and the two temperatures in different colors.

The final plots should look like this:

ggplot(data=df, aes(x=w, y=transmittance))+
    geom_line()+
    labs(x=expression(paste("Wavenumber [", cm^{-1},"]")),
         y="Transmittance [%]")+
    facet_grid(paste("Sample", sample) ~ paste(temperature,"˚C"))

ggplot(data=df, aes(x=w, y=transmittance, color=factor(temperature)))+
    geom_line()+
    labs(x=expression(paste("Wavenumber [", cm^{-1},"]")),
         y="Transmittance [%]",
         color="Temperature [˚C]")+
    facet_wrap(~paste("Sample",sample))