Tables.Rmd

---
title: "Make your tables here"
author: "Katherine Heal"
date: "Dec 16 2020"
output: html_document
---

```{r setup, include=FALSE}
library(tidyverse)
library(cowplot)
```

# Main text table of quantitative summary
```{r, error = FALSE, warning=FALSE, message=FALSE}
#Overview of Quantitative Results
source('Tables/Rcode/Quantitative_overview.R')
remove(list = ls())
```

# Supp tables (CSVs)
```{r, error = FALSE, warning=FALSE, message=FALSE}
# Sample Descriptions (Table S1)
source('Tables/Rcode/Sample_descriptions.R')
remove(list = ls())

# Summary of AsLipid DB (Table S2)
source('Tables/RCode/DB_summary.R')
remove(list = ls())

#Full MS1 database of lipids (Table S3)
source('Tables/RCode/full_DB.R')
remove(list = ls())

# MS2 database of arsenic-containing fragments and lipid classes associated with each (Table S4)
source('Tables/RCode/full_DB_MS2.R')
remove(list = ls())

# Table S5 made manually but is the observed and expected m/z for the AsSugPeL
```

#Merge all the supplemental csv files into one big xcel file here
```{r, error = FALSE, warning=FALSE, message=FALSE}
library(XLConnect) 
file.remove("Tables/ManuscriptReady/SuppTables/Combined_SuppTables.xlsx")

#write out the order of the tables you want
supp.tables <- 
  c("Tables/ManuscriptReady/SuppTables/sample_descriptions_metadata.csv",
    "Tables/ManuscriptReady/SuppTables/DB_summary.csv",
    "Tables/ManuscriptReady/SuppTables/DB_summary_full_MS1.csv",
    "Tables/ManuscriptReady/SuppTables/DB_summary_full_MS2.csv",
    "Tables/ManuscriptReady/SuppTables/ppm_AsSugPeLs.csv")

files_nms <- basename(supp.tables) %>% 
  str_remove(".csv") %>%
  str_remove("Tables/ManuscriptReady/SuppTables/") %>%
  str_remove("RawOutput/") %>%
  str_remove("_") 

files_nms2 <- files_nms %>% as.data.frame() %>%
  mutate(sheet_ID = paste0("S", row_number()+1, "_", .))

f.out <- "Tables/ManuscriptReady/SuppTables/Combined_SuppTables.xlsx"
wb <- loadWorkbook(f.out, create=TRUE)

for (i in 1:length(supp.tables)) {
  ## ingest the CSV file
  temp_DT <- read_csv(supp.tables[[i]])

  ## Create the sheet where the file will be outputed to 
  createSheet(wb, name=files_nms2$sheet_ID[i])

  ## output the csv contents
  writeWorksheet(object=wb, data=temp_DT, sheet=i, header=TRUE, rownames=NULL)
}

saveWorkbook(wb)
remove(list = ls())

```