DrugCombi_DataImport.Rmd

---
title: "CLL drug combinations ex-vivo: Data import and pre-processing"
author: "Britta Velten"
date: "15 April 2020"
output:
  BiocStyle::html_document:
    toc: true
    toc_depth: 3
params: 
    today: 200415
---

#Introduction

This file takes the raw data excel files as input and creates a dataframe object containing for each drug pair and patient the normalized viabilitites after drug treatment with only one of the two drugs and their combination. 

```{r, echo=F}
knitr::opts_chunk$set(warning=FALSE, message=FALSE)
```

```{r}
library(dplyr)
library(tidyverse)
library(magrittr)
library(xlsx)
library(dplyr)
options(stringsAsFactors = FALSE)
today <- params$today
```


```{r}
datadir <- "data"
outdir <- "out"
```


#Import data

##Introduction to data

The raw data contains 154 excel files with name composed of

- screening date "yyyy-mm-dd"
- screen number "SSx"
- patient number "Pxxxx"
- combination drug "drugname" (screens 1+2 have one combination drug, others two)

Additionally there are two files containing information about plates (different for 1-2 and 3-12), i.e. which drug in which concentration goes in which well. The drugs in the different wells are referred to as "base drug" or drugB (library drug in the manuscript) in the following, the plate-wise drug as "combination drug" or "drugC".

```{r}
file4screen12 <- "2013-11-18_EMBL_plate_info_Screen 1-2.xlsx"
file4screenRest <- "2013-11-18_EMBL_plate_info_Screen 3-6.xlsx"
```

And for each screen 1-12 viability data from day 0 (name differs between files, all end in "d0").

## Read out information base drugs
```{r}
# Read in entire files containg set-up table and information on base drugs
screen12PlateInfo <- read.xlsx(file.path(datadir,"PlateInfo",file4screen12),1)
screenRestPlateInfo <- read.xlsx(file.path(datadir,"PlateInfo",file4screenRest),1)

# Extract information on base drugs
DrugInfo12 <- screen12PlateInfo[!apply(screen12PlateInfo,1,function(r) all(is.na(r))),1:9]
DrugInfoRest <- screenRestPlateInfo[!apply(screenRestPlateInfo,1,function(r) all(is.na(r))),1:9]

# check that drug information is the same between the two setups
all(DrugInfo12==DrugInfoRest, na.rm=T)
is.na(DrugInfo12[is.na(DrugInfoRest)])
is.na(DrugInfoRest[is.na(DrugInfo12)])

# Use the same drug information for all plates
DrugInfoBase <- DrugInfo12
DrugInfoBase$Substance <- as.character(DrugInfoBase$Substance)
rm(DrugInfoRest, DrugInfo12)

# turn into labels for drug in the fromat DXXX
num2id = function(nums) { 
  sapply(nums, function(num) {
    if(is.na(num) | is.null(num) | nchar(num)>3) NA
    else if(num=="DM") "DM"
    else if(num=="DM+") "DM+"
  else paste0("D",paste(rep("0", 3-nchar(num)), collapse=""),num)
  })
}
DrugInfoBase[,1] <- num2id(as.vector(DrugInfoBase[,1]))


# concentration for Chaetoglobsin is not correct in the initial table: replace by 7uM 
# (the new version of the table should be 7uM here already)
DrugInfoBase[which(DrugInfoBase$Substance == "Chaetoglobosin A"), grepl("^c", colnames(DrugInfoBase))] <- sapply(0:4, function(i) 7 * 0.25^i)

# complete list of drugs with names and ids
DrugInfoAll <- read.csv(file.path(datadir, "DrugMetaData.csv"), row.names = 1)
```

## Read out information on plate setup
```{r}
# get plate set-up (different for screen 1-2 and others)
getPlateSetUp <- function(file){
  tableIdx<-which(file=="A", arr.ind = T)
  SetUp<-file[tableIdx[1]+0:15,tableIdx[2]+1:24]
  namesCol<-file[tableIdx[1]-2,tableIdx[2]+1:24]
  colnames(SetUp)<-as.matrix(namesCol)
  rownames(SetUp)<-letters[1:16]
  idx<-which(SetUp!="Nonsense", arr.ind = T)
  conc<-t(apply(SetUp,1, names))
  letter<-apply(SetUp,2, names)
  drugs<-apply(SetUp,2, num2id)
  SetUp<-data.frame(drug=as.vector(drugs), conc=as.vector(conc), letter=as.vector(letter), idx.row=idx[,1], idx.col=idx[,2])
  return(SetUp)
}

SetUp12 <- getPlateSetUp(screen12PlateInfo)
SetUpRest <- getPlateSetUp(screenRestPlateInfo)
```

Above code creates three data objects:

- 'DrugInfoBase' containing meta-data about drugs (concentrations corresponding to c1 to c5, name, target, dilution factor)
- 'SetUp12' and 'SetUpRest' containing the plate information, i.e. which drug in which concentration in which well


## Read in results per plate
Read in the raw values from the excel files matching them to the corresponding drug combination by using the infromation from the SetUp objects and the filename.

The raw values are then normalized by using the median value of the DM wells on the corresponding plate.
```{r}
# list the files containing results from all screens
allFiles <- list.files(file.path(datadir,"Results"),"*.xlsx")

# exclude IP screen (in-vivo ibrutinib treatments)
SSIP_fileID <- grep("SS-IP", allFiles)
allFileswoSSIP <- allFiles[-SSIP_fileID]
FilesSSIP <- allFiles[SSIP_fileID]

# function to import the raw result values on each plate
importRawData <- function(filename, SetUp12, SetUpRest){
  print(filename)
  data <- read.xlsx(file.path(datadir,"Results",filename),1)
  
  # info from filename
  NameComp <- strsplit(filename, "_")[[1]]
  ScreenDate <- NameComp[1]
  ScreenNo <- NameComp[2]
  PatientID <- NameComp[3] 
  CombiDrug <- NameComp[4] # this can be a single drug name (SS1-2) or a combiantion of two A-B (others)
  # Note for SS-IP this is the sample number, treat differently
  CombiDrug <- sub(".xlsx","", CombiDrug)
  
  # Plate ID, time and temperature
  PlateDate <- data[which(data=="Date:", arr.ind = T)+c(0,1)]
  PlateTime <- data[which(data=="Time:", arr.ind = T)+c(0,1)]
  PlateTemperature <- data[which(data=="Temperature:", arr.ind = T)+c(0,1)]

  # raw intensity values from table in excel file
  tableIdx <- which(data=="A", arr.ind = T)
  stopifnot(nrow(tableIdx)==1)
  PlateValues <- data[tableIdx[1]+0:15,tableIdx[2]+1:24]
  colnames(PlateValues) <- 1:24
  rownames(PlateValues) <- letters[1:16]
  
  # data.frame match with corresponding plate set up
  PlateValues <- rownames_to_column(PlateValues, var = "WellLetter")
  PlateValues <- gather(PlateValues, key="WellNo", value="rawValue", -WellLetter)
  PlateValues$WellNo <- as.numeric(PlateValues$WellNo)
  PlateValues$rawValue <- as.numeric(PlateValues$rawValue)
  
  # choose setup info based on screen number
  if(ScreenNo %in% c("SS1", "SS2")) {
    SetUp <- SetUp12 
  } else SetUp <- SetUpRest
  SetUp$letter <- as.character(SetUp$letter)
  
  # add well information from setup
  jointdf <- left_join(PlateValues, SetUp, by=c("WellLetter"="letter", "WellNo"="idx.col"))
  
  # rename drug to drug columns to base drug columns
  jointdf <- rename(jointdf, BaseDrugID = drug)
  jointdf <- rename(jointdf, concBaseDrug = conc)

  # combi drug depends on plate aera for all screens except 1-2
  if(ScreenNo %in% c("SS1", "SS2")) {
    jointdf$CombiDrug <- CombiDrug
  } else  {
    CombiDrugComp <- strsplit(CombiDrug, "-")[[1]]
    jointdf %<>% mutate(CombiDrug = ifelse(WellLetter %in% letters[1:8], CombiDrugComp[1],CombiDrugComp[2])) 
  }

  # "DM" in IDBaseDrugs signifies that no drug was added, set CombiDrug to NA, "DM+" means DMSO plus CombiDrug
  jointdf %<>% mutate(CombiDrug = ifelse(BaseDrugID=="DM", NA, CombiDrug))

  # calculate median of plate DMSO wells
  jointdf$pureDMSOMedian <- median(filter(jointdf, BaseDrugID=="DM")$rawValue)

  # Normalize values by the median of pure DMSO values
  jointdf %<>% mutate(normalizedValue = rawValue/pureDMSOMedian)
  
  # Add plate and screen info
  jointdf %<>% mutate(ScreenNo = ScreenNo,
                      ScreenDate = ScreenDate,
                      PlateTime = PlateTime,
                      PatientID = PatientID,
                      PlateDate = PlateDate
                      )
  # remove unnecessary columns
  jointdf %<>% select(-idx.row)
  return(jointdf)
  }

# construct dataframe from each file using the importRawData function 
listOfTables <- lapply(allFileswoSSIP, function(file) importRawData(file, SetUp12 = SetUp12, SetUpRest = SetUpRest))

# add a plate ID 
for(l in 1:length(listOfTables)) listOfTables[[l]]$PlateID <- sprintf("PL%03d", l)

# combine all plates into one dataframe
CompleteDF <- do.call(rbind,listOfTables)

# sanity check
stopifnot(nrow(CompleteDF)==24*16*length(allFileswoSSIP))
```

Add additional information on base drugs:
```{r}
# add true concentration values for base drugs based on the metadata in DrugInfoBase
ConcValueBaseDrug <- sapply(1:nrow(CompleteDF), function(i) {
  concID <- grep(CompleteDF$concBaseDrug[i],colnames(DrugInfoBase))
  drugID <- CompleteDF$BaseDrugID[i]
  if(any(is.na(concID)) | !drugID %in% DrugInfoBase$No)  return(NA)
  else return(DrugInfoBase[DrugInfoBase$No==drugID,concID])
})
CompleteDF$ConcValueBaseDrug <- ConcValueBaseDrug
CompleteDF <- left_join(CompleteDF, select(DrugInfoBase, Substance, No),
                        by = c("BaseDrugID"="No" ))
CompleteDF %<>% rename(BaseDrugName = Substance)
CompleteDF %<>%  rename(BaseDrugConc = ConcValueBaseDrug)
CompleteDF %<>%  rename(BaseDrugConcID = concBaseDrug)
CompleteDF %<>%  rename(BaseDrugID = BaseDrugID)
CompleteDF %<>% mutate(BaseDrugName = ifelse(BaseDrugID == "DM+"|BaseDrugID == "DM",
                                             "DMSO", BaseDrugName))
```

Add additional information on combination drugs:
```{r}
unique(CompleteDF$CombiDrug)
#AVL and AVl292 are the same substance, rename:
CompleteDF %<>% mutate(CombiDrug = ifelse(CombiDrug=="AVL", "AVL292", CombiDrug))

# construct df containing info on all combination compounds (constructed manually)
CombiDrugs <- unique(CompleteDF$CombiDrug)
CombiDrugs <- CombiDrugs[!is.na(CombiDrugs)]
# concentration info taken from "overview.xlsx"
# note: an old version of the table contained values off by 1/10 except for 5nM and 10nM
DrugInfoCombi <- data.frame(abrv=CombiDrugs, 
                            name=c("DMSO", "Ibrutinib","Ibrutinib",
                                  "AVL", "CAL-101", "R406", "Fludarabine",
                                  "IPI-145", "LGX", "YM155", "Abt-199",
                                  "Pomalidomide", "Afatinib",
                                  "Everolimus"),
                            conc=c(NA, 0.01, 0.1, 0.1,0.1, 0.1, 0.1,
                                   0.1, 0.1, 0.01, 0.005, 0.1, 0.1, 0.1))
DrugInfoCombi$id <- sapply(DrugInfoCombi$name, function(n) rownames(DrugInfoAll)[which(DrugInfoAll$SimpleName==n|DrugInfoAll$Substance==n)])
DrugInfoCombi %<>% mutate(id =ifelse(id == "D0DM", "DM", id))

# add additional info on drugs
library(BloodCancerMultiOmics2017)
data("drugs")
drugs <- rename(drugs, long_name=name)
DrugInfoCombi <-cbind(DrugInfoCombi, drugs[paste0("D_",substr(DrugInfoCombi$id, 2,4)) ,])
DrugInfoCombi %<>% mutate(long_name = ifelse(name=="DMSO", "DMSO", long_name))
rm(drugs)

# check right match
DrugInfoCombi

# add info to dataframe
CompleteDF <- left_join(CompleteDF,
                        select(DrugInfoCombi, name, conc, id, long_name, abrv),
                        by = c("CombiDrug" = "abrv"))
CompleteDF <- rename(CompleteDF, CombiDrugName = name, 
                     CombiDrugConc = conc,
                     CombiDrugID = id,
                     CombiDrug_longname = long_name)
```

# Use consistent drug names
Make drug names consistent between base compound and combination compounds:

For base compounds
```{r}
df_BNametoNiceName <- data.frame(
  BaseDrugName = unique(CompleteDF$BaseDrugName),
  niceName = c("DMSO", "Navitoclax", "Idelalisib",
               "Fludarabine", "Selumetinib", "AZD7762", "TAE684", "R406", "BAY 11-7085",
               "KU-60019", "Dasatinib", "Arsentrioxide", "PF 477736", "Venetoclax", 
               "Ruxolitinib", "Doxorubicine", "Silmitasertib", "Ibrutinib",
               "SNS-032", "Nutlin-3", "Afatinib", "NSC74859",
               "MK-2206", "YM155", "SGX-523", "Chaetoglobosin A", "Everolimus", "LY2603618",
               "Spebrutinib", "Duvelisib", "SD07", "Bayer-18", "Sotrastaurin")
)
df_match <- df_BNametoNiceName %>% 
    rowwise() %>% 
    mutate(Matches =  BaseDrugName == niceName | grepl(niceName, BaseDrugName))
stopifnot(sum(df_match$Matches) >= nrow(df_match) - 6)

# add to result data frame
CompleteDF %<>% left_join(df_BNametoNiceName, by = "BaseDrugName")
CompleteDF$BaseDrugName <- NULL
CompleteDF %<>% dplyr::rename(BaseDrugName = niceName)

# add to drug info
DrugInfoBase %<>% left_join(df_BNametoNiceName, by = c("Substance" = "BaseDrugName"))
```

For combination compounds
```{r}
df_CAbrvtoNiceName <- data.frame(
  CombiDrug = unique(CompleteDF$CombiDrug),
  niceName = c(NA , "DMSO", "Ibrutinib (10nM)", "Ibrutinib (100nM)",
               "Spebrutinib", "Idelalisib", "R406", "Fludarabine",
               "Duvelisib", "Encorafenib", "YM155", "Venetoclax",
               "Pomalidomide", "Afatinib", "Everolimus")
)
df_CNametoNiceName <- data.frame(
  CombiDrugName = unique(CompleteDF$CombiDrugName),
  niceName = c(NA , "DMSO", "Ibrutinib", "Spebrutinib", "Idelalisib",
               "R406", "Fludarabine", "Duvelisib", "Encorafenib",
               "YM155", "Venetoclax", "Pomalidomide", "Afatinib", "Everolimus")
)

df_match <- df_CAbrvtoNiceName %>% 
    rowwise() %>% 
    mutate(Matches =  CombiDrug == niceName | grepl(substr(CombiDrug,1,8), niceName))
stopifnot(sum(df_match$Matches, na.rm = TRUE) >= nrow(df_match) - 6)

df_match <- df_CNametoNiceName %>% 
    rowwise() %>% 
    mutate(Matches =  CombiDrugName == niceName)
stopifnot(sum(df_match$Matches, na.rm = TRUE) >= nrow(df_match) - 6)

# add to result data frame
CompleteDF %<>% left_join(df_CAbrvtoNiceName, by = "CombiDrug")
CompleteDF$CombiDrug <- NULL
CompleteDF %<>% dplyr::rename(CombiDrug = niceName)
CompleteDF %<>% left_join(df_CNametoNiceName, by = "CombiDrugName")
CompleteDF$CombiDrugName <- NULL
CompleteDF %<>% dplyr::rename(CombiDrugName = niceName)

# add to drug info
DrugInfoCombi %<>% left_join(df_CAbrvtoNiceName, by = c("abrv" = "CombiDrug"))
```

The SS-IP screen is not considered for any analysis.

Remove control wells from table (used only for normalization) (DM wells) (keep DM+ wells, which are DMSO as base compound but with a non-DMSO combi compound)
```{r}
CompleteDFwoControl <- filter(CompleteDF, BaseDrugID!="DM")
```

## Data Overview

```{r}
# combination partners
length(unique(CompleteDFwoControl$CombiDrug_longname))
unique(CompleteDFwoControl$CombiDrug_longname)

# number of samples
length(unique(CompleteDFwoControl$PatientID))

# numbmer of base compounds
length(unique(CompleteDFwoControl$BaseDrugID))
unique(CompleteDFwoControl$BaseDrugName)
```

* 13 combintation partners (incl DMSO), two concentrations for ibrutinib, others only one
* 54 patient samples (52 after removing the samples of screen 1)
* 33 base drugs (incl. DMSO)


```{r}
# columns re-ordering
FirstTable <- CompleteDFwoControl
FirstTable <- FirstTable[order(FirstTable$CombiDrug),
                       c("CombiDrug", "CombiDrugName", "CombiDrugConc",
                         "CombiDrugID", "CombiDrug_longname",
                         "BaseDrugName", "BaseDrugID", "BaseDrugConc", "BaseDrugConcID", 
                         "PatientID", "PlateID","WellNo","WellLetter",
                         "normalizedValue", "rawValue",
                         "pureDMSOMedian","ScreenNo", "ScreenDate",
                         "PlateDate", "PlateTime")]
head(FirstTable)
```



# Re-structure to obtain combination and single agent effects
Re-structure data frame to obtain table with combined effect and single effect for each patient and substance combination. As combined effect use the normalized viability value of the well of drug B on the plate with drug C. The effectB is given by the well of drug B on the DMSO plate and the effectC is given by the median of the DM+ wells on the plate with drug C.
```{r}
# seperate drug-drug combi, negative control for BaseDrug (DMSO plate) and for CombiDrug(DMplus)
DMSOtable <- filter(FirstTable, CombiDrug=="DMSO", BaseDrugID!="DM+")  # DMSO as combination partner, but non-DMSO base compound (--> effectB)
DMplustable <- filter(FirstTable, BaseDrugID=="DM+", CombiDrug!="DMSO")  # DMSO as base compound , but non-DMSO combination compound (--> effectC)
drugstable <- filter(FirstTable, BaseDrugID!="DM+", CombiDrug!="DMSO") # non-DMSO as base compound and as combination compound (--> effectBC)

#### add effect of combination drug along to drugstable ###
# same combi drug (id/conc), patient ID, screen and plate
matchingCols <- c("CombiDrug","CombiDrugID","CombiDrugName","CombiDrugConc","CombiDrug_longname",
                  "PatientID", "PlateID", "ScreenNo", "PlateDate", "PlateTime")
#several DMSO wells combined with combi compound: use median 
DMplustable_medians <- DMplustable %>%
  group_by_at(matchingCols) %>%
  summarize(effectC = median(normalizedValue))
mergedTable <- left_join(drugstable, select(DMplustable_medians, matchingCols, effectC), by = matchingCols)                    
mergedTable %<>% rename(effectBC= normalizedValue)

### add effect of base drug along to drugstable ###
# same base drug (id/conc), patient ID and screen, but can be on different plates (1-2 Cdrugs per plate),
# for screen 1 and 2 need to also add well info to match replicates, 
# later screen will have same letter modulo 8 but are determined by the combiDrug and PatientID
matchingCols <- c("BaseDrugName","BaseDrugID","BaseDrugConc","BaseDrugConcID", "PatientID", "ScreenDate", "ScreenNo", "repMatchLetter")
DMSOtable %<>% rename(effectB = normalizedValue) 
DMSOtable %<>% mutate(repMatchLetter = ifelse(ScreenNo %in% c("SS1", "SS2"),
                                              paste(WellLetter, WellNo, sep="_"), NA))
mergedTable %<>% mutate(repMatchLetter = ifelse(ScreenNo %in% c("SS1", "SS2"),
                                                paste(WellLetter, WellNo, sep="_"), NA))
mergedTable <- left_join(mergedTable, select(DMSOtable, matchingCols, effectB),
                         by = matchingCols)                    

head(mergedTable)
stopifnot(nrow(mergedTable) == nrow(drugstable))
```



#Data Export
Save data to be used in further analysis.
```{r}
# select columns to use for further analysis
df4ana <- select(mergedTable,
                 CDrugAbrv = CombiDrug,
                 CDrugName = CombiDrugName,
                 CDrugID = CombiDrugID,
                 CDrugConc = CombiDrugConc,
                 CDrugNameLong = CombiDrug_longname,
                 BDrugName = BaseDrugName,
                 BDrugID = BaseDrugID,
                 BDrugConc = BaseDrugConc,
                 BDrugConcId = BaseDrugConcID,
                 effectBC,
                 effectB,
                 effectC,
                 PatientID,
                 ScreenDate,
                 ScreenNo,
                 rawValueBC = rawValue
                 )

DrugMetaList = list(DrugInfoAll = DrugInfoAll,
                    DrugInfoBase = DrugInfoBase,
                    DrugInfoCombi = DrugInfoCombi)
SetUpList <- list(SetUpRest =SetUpRest,
                  SetUp12 = SetUp12)
```

```{r}
if(!dir.exists(outdir)) dir.create(outdir)
save(df4ana, CompleteDF, DrugMetaList, SetUpList,
     file=paste0(outdir, "/CLLCombiData_",today,".RData"))
```

* `CompleteDF` contains the raw and normalized values per well/plate/screen
* `df4ana` contains the normalized values for each single drug and drug-drug combination per sample and concentration)
* `DrugMetaList` contains info about the drugs used as base and combi compounds
* `SetUpList` contains the plate setups for the screens

The columns of `df4ana` are

 | column name | description |
 | ------------- |-------------|
| CDrugAbrv  | combination drug (per plate, as in excel file name) dinstinguished also the two Ibrutinib concentrations|
| CDrugName | full name of combination drug |
| CDrugNameLong | long name of combination drug |
| CDrugID    |  	ID of combination drug |
| CDrugConc  	| concentration of combination drug |
| BDrugName  | name of base drug|
| BDrugID   | 	ID of base drug|
| BDrugConc  | concentration of base drug|
| BDrugConcId |concentration label, “c1” to "c5"|
| effectBC    |	normalised viability value of the drug combination B+C (normalised by the median of DM wells on same plate) |
| effectB    |	 normalised viability value of the drug B (on DMSO plate) (normalised by the median of DM wells on same plate) |
| effectC     |	median of normalised viability value of the drug C (in DM+ wells) (normalised by the median of DM wells on same plate) |
|PatientID   |Patient ID P0XXX|
|ScreenDate  |date of screen yyyy-mm-dd|
|ScreenNo   | number of screen SSX from 1 to 13|

```{r}
sessionInfo()
```