library(tidyverse)
# install.packages("readxl")
library(readxl)

Load data and separate into row and column.

qpcr_data <- readxl::read_excel(
  "qPCR example.xlsx",
  col_names = c("well", "fluor", "value")
) %>%
  separate(well, into = c("row", "col"),sep = 1) %>%
  # don't need fluor
  select(-fluor)

Spread it out into plate format. By doing this, I could more easily see that this data has NA values!

spread(qpcr_data, row, value)

## # A tibble: 12 x 9
##    col       A     B     C     D     E     F     G     H
##    <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
##  1 01     22.7  22.7  22.8  22.7  36.1  36.4  35.2  35.5
##  2 02     23.0  23.1  23.0  23.0  NA    34.4  35.4  35.5
##  3 03     23.3  23.2  23.4  23.4  37.1  37.0  35.1  35.7
##  4 04     25.0  24.7  25.0  24.8  37.9  38.5  37.0  36.8
##  5 05     22.5  22.3  21.9  22.0  22.1  22.1  35.3  36.1
##  6 06     21.2  21.3  20.2  20.2  20.9  20.9  34.3  34.3
##  7 07     30.1  29.9  20.6  20.5  29.6  29.6  21.6  21.7
##  8 08     30.9  30.7  22.0  21.9  29.1  29.0  21.0  21.1
##  9 09     30.8  30.6  21.3  21.2  28.5  28.7  22.1  22.5
## 10 10     32.2  32.1  27.2  27.3  28.2  28.1  22.0  22.1
## 11 11     30.1  30.1  21.8  21.8  NA    NA    NA    NA  
## 12 12     26.5  26.3  22.3  22.1  33.0  32.5  NA    NA

Calculate means of of cols. Note that we have to explicitly tell the sum function to ignore NA values

vals <- c(1,2,3,10,NA)
sum(vals)

## [1] NA

sum(vals, na.rm = TRUE)

## [1] 16

If we calculate mean this way, we end up with NAs

qpcr_data %>%
  group_by(col) %>%
  summarize(mean(value))

## # A tibble: 12 x 2
##    col   `mean(value)`
##    <chr>         <dbl>
##  1 01             29.3
##  2 02             NA  
##  3 03             29.8
##  4 04             31.2
##  5 05             25.5
##  6 06             24.1
##  7 07             25.4
##  8 08             25.7
##  9 09             25.7
## 10 10             27.4
## 11 11             NA  
## 12 12             NA

Using na.rm = TRUE gives the correct answer by ignoring NA values.

qpcr_data_stats <-
  qpcr_data %>%
  group_by(col) %>%
  summarize(value_mean = mean(value, na.rm = TRUE))

qpcr_data_stats

## # A tibble: 12 x 2
##    col   value_mean
##    <chr>      <dbl>
##  1 01          29.3
##  2 02          28.2
##  3 03          29.8
##  4 04          31.2
##  5 05          25.5
##  6 06          24.1
##  7 07          25.4
##  8 08          25.7
##  9 09          25.7
## 10 10          27.4
## 11 11          25.9
## 12 12          27.1

Make a plot …

ggplot(qpcr_data_stats, aes(x = col, y = value_mean)) + geom_col()

… and now make it look a bit nicer

library(cowplot)
ggplot(qpcr_data_stats, aes(x = col, y = value_mean)) +
  geom_col() + 
  labs(
    title = "Analysis of qPCR data",
    x = "Column",
    y = "Mean of values"
  )