---
title: "Playing with Primer Databses"
output: html_notebook
---

Lets bring some data in!

```{r}
PD <- read_csv("~/Documents/Roberts Lab/Primer Databse/PrimerDatabase - primers.csv")
primerbox <- read_csv("~/Documents/Roberts Lab/Primer Databse/primerbox.csv")

```


Well, that's annoying. Looks lik there's some leftover formatting stuff from pulling it down from Sheets. 

```{r}
PD <- PD[25:length(PD$`Primer Sequence`),1:17]

for( i in 1:ncol(PD))   {
  for(j in 1:nrow(PD))   {
      PD[j,i] <- tolower(PD[j,i])
      PD[j,i] <- gsub(" ", "_", PD[j,i])
      }
}

for( i in 1:ncol(primerbox))   {
  for(j in 1:nrow(primerbox))   {
      primerbox[j,i] <- tolower(primerbox[j,i])
      primerbox[j,i] <- gsub(" ", "_", primerbox[j,i])
      }
}

primerbox <- primerbox[-c(which(is.na(primerbox$Name) == TRUE)),]
```

Sufficiently sanitized, lets try a "join" 
```{r}
PD.test <- PD
PB.test <- primerbox
leftover = PB.test[1,]
counter = 1

for(i in 1:nrow(PB.test))   {
  for(j in 1:nrow(PD.test))   {
    if((!is.na(PD.test$sr_ID[j]) && !is.na(PB.test$`SR ID`[i])) && PD.test$sr_ID[j] == PB.test$`SR ID`[i])   {
      PD.test[j,18] <- PB.test$`Box Name`[i]
      PD.test[j,19] <- PB.test$Position[i]
      PD.test[j,20] <- PB.test$`IDT #`[i]
      PD.test[j,21] <- PB.test$Date[i]
      PD.test[j,22] <- PB.test$Notes[i]
    } else if(PD.test$`Primer name`[j] == PB.test$Name[i])  {
      PD.test[j,18] <- PB.test$`Box Name`[i]
      PD.test[j,19] <- PB.test$Position[i]
      PD.test[j,20] <- PB.test$`IDT #`[i]
      PD.test[j,21] <- PB.test$Date[i]
      PD.test[j,22] <- PB.test$Notes[i]
    } 
  }
}

colnames(PD.test)[18:22] <- c("Box_Name", "Box_Position", "IDT #", "Date", "Box_Notes")
```

The code below combines the two IDT # columns, setting column 17 to be the "master" column, but retains any values in column 20 that do not match. 

```{r}
for(i in 1:nrow(PD.test))   {
  if(!is.na(as.numeric(PD.test[i,17])) == TRUE && !is.na((as.numeric(PD.test[i,17]) != as.numeric(PD.test[i,20]))) && (as.numeric(PD.test[i,17]) == as.numeric(PD.test[i,20])) == TRUE )   {
    PD.test[i,20] <- NA
  }
}

for(i in 1:nrow(PD.test))   {
  
  if(!is.na(as.numeric(PD.test[i,20])) == TRUE && (is.na(PD.test[i,17]) == TRUE))   {
    PD.test[i, 17] <- PD.test[i,20]
    PD.test[i,20] <- NA
  }
}

#write.csv(PD.test, "Combined_primer_database.csv")
```

