20241113_mpra_supplementary_tables.Rmd

---
title: "20240310 mpra supplementary tables"
output: html_document
date: "2024-05-07"
---

Supplementary table list:
1. Tcell MPRA results
  - derived from OLJR.C_Tcell_emVAR_glm_20240310.out
  - located at /nfs/jray/screens/ALL_MPRAs/Ho_et_al_analysis/Primary_Data/20240310_analysis/results
  - equivalent of mouri supp. table 3
2. Jurkat MPRA results 
  - derived from OLJR.A_Jurkat_emVAR_glm_20240310.out
  - located at /nfs/jray/screens/ALL_MPRAs/Ho_et_al_analysis/Primary_Data/20240310_analysis/results
  - equivalent of mouri supp. table 3
3. PICS enrichment all loci
 - derived from 20240310_tcell_glm_hg19_mpra_pics_enrichment_plot_all_loci_table.txt
 - located at /nfs/jray/screens/ALL_MPRAs/Ho_et_al_analysis/Downstream_Analysis/20240310_mpra_analysis/tcell/glm/data
 - equivalent of mouri supp. table 11
4. PICS enrichment emvars loci
 - derived from 20240310_tcell_glm_hg19_mpra_pics_enrichment_plot_dhs_loci_only_table.txt
 - located at /nfs/jray/screens/ALL_MPRAs/Ho_et_al_analysis/Downstream_Analysis/20240310_mpra_analysis/tcell/glm/data
 - equivalent of mouri supp. table 12
5. UK biobank enrichment all loci
 - derived from 20240310_tcell_glm_mpra_uk_biobank_enrichment_plot_all_loci_table.txt
 - located at /nfs/jray/screens/ALL_MPRAs/Ho_et_al_analysis/Downstream_Analysis/20240310_mpra_analysis/UK_biobank_finemapping_enrichment/data
6. UK biobank enrichment emvars loci
 - derived from 20240310_tcell_glm_mpra_uk_biobank_enrichment_plot_emvars_loci_only_table.txt
 - located at /nfs/jray/screens/ALL_MPRAs/Ho_et_al_analysis/Downstream_Analysis/20240310_mpra_analysis/UK_biobank_finemapping_enrichment/data
7. tcell motifbreakr mpra combined
 - derived from motif.mpra.dat_tcell_hocomoco.txt
 - located at /nfs/jray/screens/ALL_MPRAs/Ho_et_al_analysis/Downstream_Analysis/TF_analysis/motifbreakr/data/motif.mpra.data
 - equivalent of mouri supp. table 7
8. tcell motifbreakr logskew ttest
 - derived from t.test.all.bind.motif.dat_tcell_hocomoco.txt
 - located at /nfs/jray/screens/ALL_MPRAs/Ho_et_al_analysis/Downstream_Analysis/TF_analysis/motifbreakr/data/t.test.all.bind.motif.dat
9. jurkat motifbreakr mpra combined
 - derived from motif.mpra.dat_unstim_jurkat_hocomoco.txt
 - located at /nfs/jray/screens/ALL_MPRAs/Ho_et_al_analysis/Downstream_Analysis/TF_analysis/motifbreakr/data/motif.mpra.data
 - equivalent of mouri supp. table 7
10. jurkat motifbreakr logskew ttest
 - derived from t.test.all.bind.motif.dat_unstim_jurkat_hocomoco.txt
 - located at /nfs/jray/screens/ALL_MPRAs/Ho_et_al_analysis/Downstream_Analysis/TF_analysis/motifbreakr/data/t.test.all.bind.motif.dat
11. ChromHMM enrich
 - derived from 20240310_tcell_glm_hg19_chrommhmm_histone_data.txt
 - located at /nfs/jray/screens/ALL_MPRAs/Ho_et_al_analysis/Downstream_Analysis/20240310_mpra_analysis/tcell/glm/data
 - equivalent of mouri supp. table 4
12. Histone CAGE DHS enr
 - derived from 20240310_tcell_glm_hg19_histone_cage_dhs_data.txt
 - located at /nfs/jray/screens/ALL_MPRAs/Ho_et_al_analysis/Downstream_Analysis/20240310_mpra_analysis/tcell/glm/data
 - equivalent of mouri supp. table 5
13. T cell MPRA functional annotations
 - derived from 20240310_tcell_glm_mpra_merge_hg38_hg19.txt
 - located at /nfs/jray/screens/ALL_MPRAs/Ho_et_al_analysis/Downstream_Analysis/20240310_mpra_analysis/tcell/glm/data
 - large subset needed
 - equivalent of mouri supp. table 6
14. PICS by MPRA
 - derived from 20240310_tcell_glm_mpra_merge_hg38_hg19.txt
 - located at /nfs/jray/screens/ALL_MPRAs/Ho_et_al_analysis/Downstream_Analysis/20240310_mpra_analysis/tcell/glm/data
 - with extra columns needed from the pics plot (credible sets)
 - equivalent of mouri supp. table 10
14. UKBB by MPRA
15. Jurkat MPRA functional annotations
 - derived from 20240310_unstim_jurkat_glm_mpra_merge_hg38_hg19.txt
 - located at /nfs/jray/screens/ALL_MPRAs/Ho_et_al_analysis/Downstream_Analysis/20240310_mpra_analysis/unstim_jurkat/glm/data

```{r}
data.dir <- "~/Desktop/Ho et al. writings/Code_from_scratch2/data/"
```


```{r}
library(openxlsx)
library(tidyverse)
library(readxl)
library(stringr)
```


## Table 1 
```{r}
supp_table_1 <-read.delim(paste0(data.dir,"OLJR.C_Tcell_emVAR_glm_20240310.out"),header=T, stringsAsFactors = F,sep="\t")
names(supp_table_1)
supp_table_1 <- subset(supp_table_1, select=c(SNP,window,strand,allele,haplotype,comb,A_Ctrl_Mean,A_Exp_Mean,A_log2FC, A_log2FC_SE, A_logP, A_logPadj_BH, A_logPadj_BF, B_Ctrl_Mean, B_Exp_Mean, B_log2FC, B_log2FC_SE, B_logP, B_logPadj_BH, B_logPadj_BF, Log2Skew,Skew_logFDR,Skew_SE, skewStat,Skew_logP))
supp_table_1

# Add rsid
# read in one of the mouri et al. supplementary tables
rsid.dat<-read_excel(paste0(data.dir,"41588_2022_1056_MOESM4_ESM.xlsx"))
# renaming a column to merge it with the mpra data
rsid.dat$SNP <- rsid.dat$ld_snp 
# pick just the SNP and rsid column
rsid.dat<-unique(subset(rsid.dat, select=c(SNP, rsid)))
# merge the rsid data with the mpra
supp_table_1<-merge(supp_table_1, rsid.dat, by="SNP", all.x=T, all.y=F)
# 40 snps do not have a rsid
missing_rsid_snps <- subset(supp_table_1, is.na(rsid)==TRUE)
supp_table_1$SNP19 <- supp_table_1$SNP
supp_table_1 <- subset(supp_table_1, select=-c(SNP))

# Create complete hg38 liftover
tcell_mpra_merge_hg38_all <- read.table(paste0(data.dir, "20241111_tcell_mpra_merge_hg38_all.txt"),header=T, stringsAsFactors = F,sep="\t")
liftover <- subset(tcell_mpra_merge_hg38_all, select=c(SNP19,SNP38,chr,pos,ref_allele,alt_allele,ld_snp,lead_snp,mpra_sig))

supp_table_1 <- merge(liftover,supp_table_1, by="SNP19")


subset_mpra <- subset(supp_table_1, mpra_sig == "Enhancer_Skew")
nrow(subset_mpra)
subset_mpra2 <- subset(supp_table_1, mpra_sig == "Enhancer_nSkew")
nrow(subset_mpra2)
subset_mpra3 <- subset(supp_table_1, mpra_sig == "nEnhancer_nSkew")
nrow(subset_mpra3)

# This is going to have more emVars, pCREs and just overall variants than the paper reports because while I am eliminating the variants which are not associated with any disease, I am giving you all the variants regardless of how many plasmids (A or B Ctrl_Mean) there are for each variant. We have that >20 filter for the variants in the paper, but I am giving you all the data here. 
```

## Table 2
```{r}
supp_table_2 <-read.delim(paste0(data.dir, "OLJR.A_Jurkat_emVAR_glm_20240310.out"),header=T, stringsAsFactors = F,sep="\t")
supp_table_2$project <- "TGWAS"
supp_table_2 <- subset(supp_table_2, select=c(SNP,project,window,strand,allele,haplotype,comb,A_Ctrl_Mean,A_Exp_Mean,A_log2FC, A_log2FC_SE, A_logP, A_logPadj_BH, A_logPadj_BF, B_Ctrl_Mean, B_Exp_Mean, B_log2FC, B_log2FC_SE, B_logP, B_logPadj_BH, B_logPadj_BF, Log2Skew,Skew_logFDR,Skew_SE, skewStat,Skew_logP))

# Add rsid
# read in one of the mouri et al. supplementary tables
rsid.dat<-read_excel(paste0(data.dir,"41588_2022_1056_MOESM4_ESM.xlsx"))
# renaming a column to merge it with the mpra data
rsid.dat$SNP <- rsid.dat$ld_snp 
# pick just the SNP and rsid column
rsid.dat<-unique(subset(rsid.dat, select=c(SNP, rsid)))
# merge the rsid data with the mpra
supp_table_2<-merge(supp_table_2, rsid.dat, by="SNP", all.x=T, all.y=F)
# 40 snps do not have a rsid
missing_rsid_snps <- subset(supp_table_2, is.na(rsid)==TRUE)
supp_table_2$SNP19 <- supp_table_2$SNP
supp_table_2 <- subset(supp_table_2, select=-c(SNP))

# Create complete hg38 liftover
jurkat_mpra_merge_hg38_all <- read.table(paste0(data.dir, "20241111_jurkat_mpra_merge_hg38_all.txt"),header=T, stringsAsFactors = F,sep="\t")
liftover <- subset(jurkat_mpra_merge_hg38_all, select=c(SNP19,SNP38,chr,pos,ref_allele,alt_allele,ld_snp,lead_snp,mpra_sig))

supp_table_2 <- merge(liftover,supp_table_2, by="SNP19")
```

## Table 3
```{r}
supp_table_3 <-read.delim(paste0(data.dir,"20241111_tcell_mpra_pics_enrichment_plot_all_loci_table.txt"),header=T, stringsAsFactors = F,sep="\t")
supp_table_3 <- subset(supp_table_3, select=c(pics,mpra,a,b,c,d,fold,p,odds,lower.conf,upper.conf))
names(supp_table_3) <- c("PICS threshold","mpra","MPRA+, PICS+","MPRA+, PICS-","MPRA-, PICS+","MPRA-, PICS-","Enrichment","P-value","Odds", "Lower Conf. Int.", "Upper Conf. Int.")
supp_table_3
```

## Table 4 
```{r}
supp_table_4 <-read.delim(paste0(data.dir,"20241111_tcell_mpra_pics_enrichment_plot_dhs_loci_only_table.txt"),header=T, stringsAsFactors = F,sep="\t")
supp_table_4 <- subset(supp_table_4, select=c(pics,mpra,a,b,c,d,fold,p,odds,lower.conf,upper.conf))
names(supp_table_4) <- c("PICS threshold","mpra","MPRA+, PICS+","MPRA+, PICS-","MPRA-, PICS+","MPRA-, PICS-","Enrichment","P-value", "Odds", "Lower Conf. Int.", "Upper Conf. Int.")
supp_table_4
```


## Table 5
```{r}
supp_table_5 <-read.delim(paste0(data.dir, "mpra_uk_biobank_enrichment_plot_all_loci_table.txt"),header=T, stringsAsFactors = F,sep="\t")
supp_table_5 <- subset(supp_table_5, select=c(pip,mpra,a,b,c,d,fold,p,odds,lower.conf,upper.conf))
names(supp_table_5) <- c("UKBB PIP threshold","mpra","MPRA+, UKBB+","MPRA+, UKBB-","MPRA-, UKBB+","MPRA-, UKBB-","Enrichment","P-value", "Odds", "Lower Conf. Int.", "Upper Conf. Int.")
supp_table_5
```

## Table 6
```{r}
supp_table_6 <-read.delim(paste0(data.dir,"mpra_uk_biobank_enrichment_plot_emvars_loci_only_table.txt"),header=T, stringsAsFactors = F,sep="\t")
supp_table_6 <- subset(supp_table_6, select=c(pip,mpra,a,b,c,d,fold,p,odds,lower.conf,upper.conf))
names(supp_table_6) <- c("UKBB PIP threshold","mpra","MPRA+, UKBB+","MPRA+, UKBB-","MPRA-, UKBB+","MPRA-, UKBB-","Enrichment","P-value", "Odds", "Lower Conf. Int.", "Upper Conf. Int.")
supp_table_6
```


## Table 7 
```{r}
supp_table_7 <- read.delim(paste0(data.dir,"motifbreakr/tcell/","motif.mpra.dat_tcell_hocomoco.txt"),header=T, stringsAsFactors = F,sep="\t")
supp_table_7$SNP38 <- supp_table_7$SNP
supp_table_7 <- subset(supp_table_7, select=c("SNP38","REF","ALT","rsid","mpra_sig","A.log2FC","B.log2FC","LogSkew","geneSymbol","scoreRef","scoreAlt","alleleDiff", "unique_snp_tf"))
supp_table_7
hg19_38 <- read.table(paste0(data.dir, "20241111_tcell_mpra_merge_hg38_all.txt"),header=T, stringsAsFactors = F,sep="\t") 
hg19_38 <- subset(hg19_38, select=c(SNP19,SNP38))
supp_table_7 <- merge(hg19_38,supp_table_7,by="SNP38")
```


## Table 8
```{r}
supp_table_8 <- read.delim(paste0(data.dir,"motifbreakr/tcell/","t.test.all.bind.motif.dat_tcell_hocomoco.txt"),header=T, stringsAsFactors = F,sep="\t")
supp_table_8
```

## Table 9 
```{r}
supp_table_9 <- read.delim(paste0(data.dir, "motifbreakr/jurkat/","motif.mpra.dat_unstim_jurkat_hocomoco.txt"),header=T, stringsAsFactors = F,sep="\t")
supp_table_9
supp_table_9$SNP38 <- supp_table_9$SNP
supp_table_9 <- subset(supp_table_9, select=c("SNP38","REF","ALT","rsid","mpra_sig","A.log2FC","B.log2FC","LogSkew","geneSymbol","scoreRef","scoreAlt","alleleDiff", "unique_snp_tf"))
supp_table_9
hg19_38 <- read.table(paste0(data.dir, "20241111_jurkat_mpra_merge_hg38_all.txt"),header=T, stringsAsFactors = F,sep="\t") 
hg19_38 <- subset(hg19_38, select=c(SNP19,SNP38))
supp_table_9 <- merge(hg19_38,supp_table_9,by="SNP38")
```

## table 10 
```{r}
supp_table_10 <- read.delim(paste0(data.dir, "motifbreakr/jurkat/", "t.test.all.bind.motif.dat_unstim_jurkat_hocomoco.txt"),header=T, stringsAsFactors = F,sep="\t")
supp_table_10
```

## Table 11
```{r}
supp_table_11 <- read.delim(paste0(data.dir,"20241111_tcell_chrommhmm_histone_data.txt"),header=T, stringsAsFactors = F,sep="\t")
supp_table_11 <- subset(supp_table_11, select=c("chromhmm","mode","a","b","c","d","fold","p","odds","lower.conf","upper.conf"))
names(supp_table_11) <- c("chromHMM Annotation", "MPRA Effect", "TP","FP","FN","TN","Fold Enrichment","P-value", "Odds", "Lower Conf. Int.", "Upper Conf. Int.")
supp_table_11
```

## Table 12
```{r}
supp_table_12 <- read.delim(paste0(data.dir, "20241111_tcell_histone_cage_dhs_data.txt"),header=T, stringsAsFactors = F,sep="\t")
supp_table_12 <- subset(supp_table_12, select=c("mark","mode","a","b","c","d","fold","p","odds","lower.conf","upper.conf"))
names(supp_table_12) <- c("Annotation", "MPRA Effect", "TP","FP","FN","TN","Fold Enrichment","P-value", "Odds", "Lower Conf. Int.", "Upper Conf. Int.")
supp_table_12
```

## Table 13
```{r}
supp_table_13 <- read.delim(paste0(data.dir,"20241111_tcell_mpra_merge_hg38_all.txt"),header=T, stringsAsFactors = F,sep="\t")
supp_table_13 <- subset(supp_table_13, select=c(SNP19,SNP38, rsid, ld_snp, lead_snp,mpra_sig,dhs_hTH1,	dhs_hTH17,	dhs_hTH2,	dhs_CD4,	dhs_CD4pos_N,	dhs_hTR,	dhs_Jurkat,	dhs_CD8,	dhs_Tcell_merged,	dhs_all,delta_svm_nCD4,	asc,	atac_qtl_beta,atac_qtl_pval,	eqtl_beta, eqtl_pval,eqtl_gene,	tf_motifbreakr, tss,ananastra_tf,motifbreakr_tf_2024))
supp_table_13

# Add UKBB column
mpra_biobank_merge_all_traits <- read.table(paste0(data.dir,"mpra_biobank_merge_all_traits_hg19",".txt"),header=T, stringsAsFactors = F,sep="\t")
mpra_biobank_merge_all_traits
mpra_biobank_merge_all_traits$SNP19 <- mpra_biobank_merge_all_traits$SNP
mpra_biobank_subset <- subset(mpra_biobank_merge_all_traits, select=c(SNP19,trait,pip))
names(mpra_biobank_subset) <- c("SNP19","ukbb_top_trait","ukbb_top_pip")
supp_table_13 <- merge(supp_table_13,mpra_biobank_subset,by="SNP19",all.x=TRUE)

# Haplotype with emVar column
emvar_loci <- unique(subset(supp_table_13, mpra_sig=="Enhancer_Skew")$lead_snp)
supp_table_13$emvar_in_haplotype <- 0
for(i in 1:nrow(supp_table_13)){
if(supp_table_13$lead_snp[i]%in%emvar_loci){supp_table_13$emvar_in_haplotype[i] <- 1}  
}

supp_table_13
```

## Table 14
```{r}
supp_table_14 <- read.delim(paste0(data.dir,"20241111_tcell_mpra_merge_all.txt"),header=T, stringsAsFactors = F,sep="\t")
supp_table_14$SNP19 <- supp_table_14$SNP
supp_table_14 <- subset(supp_table_14, select=c(SNP19,chr,pos,ref_allele,alt_allele,snp_end,ld_snp,lead_snp,r2,rsid, mpra_sig,Crohns_pval,	Crohns_pics,Crohns_PP_running,	MS_pval,	MS_pics, MS_PP_running,	Psoriasis_pval,	Psoriasis_pics,Psoriasis_PP_running	,RA_pval,	RA_pics,RA_PP_running,	T1D_pval,	T1D_pics,T1D_PP_running,	UC_pval,	UC_pics,UC_PP_running,	IBD_pval,	IBD_pics,IBD_PP_running, dhs_Tcell_merged, dhs_all))
hg19_38 <- read.table(paste0(data.dir, "20241111_jurkat_mpra_merge_hg38_all.txt"),header=T, stringsAsFactors = F,sep="\t") 
hg19_38$lead_snp38 <- hg19_38$lead_snp
hg19_38$ld_snp38 <- hg19_38$ld_snp
hg19_38 <- subset(hg19_38, select=c(SNP19,SNP38,lead_snp38,ld_snp38))
supp_table_14 <- merge(hg19_38,supp_table_14,by="SNP19")
supp_table_14
# ADD: top_pval	top_disease	top_pics 80% credible set	90% credible set	95% credible set

  mpra.pics.plot <- supp_table_14
# order of gwas diseases
  gwas.order<- c("Crohns","MS","Psoriasis", "RA","T1D","UC", "IBD")

# Format the mpra.pics.plot data
  # replace _CS_ with _PP_
  names(mpra.pics.plot)<-gsub("_CS_", "_PP_", names(mpra.pics.plot))
  # Select only certain columns
  mpra.pics.plot<-subset(mpra.pics.plot, select=c(SNP19,SNP38,chr,pos,ref_allele,alt_allele,ld_snp,lead_snp,ld_snp38,lead_snp38,snp_end,r2,                                       rsid,Crohns_pval,Crohns_pics,Crohns_PP_running,MS_pval,MS_pics,MS_PP_running,
                            Psoriasis_pval,Psoriasis_pics,Psoriasis_PP_running,RA_pval,RA_pics,RA_PP_running,
                            T1D_pval,T1D_pics,T1D_PP_running,UC_pval,UC_pics,UC_PP_running,IBD_pval,IBD_pics,
                            IBD_PP_running, dhs_Tcell_merged, dhs_all, mpra_sig))
  mpra.pics.plot$dhs_merged <- mpra.pics.plot$dhs_Tcell_merged

  
  # Remove bad SNPs where it doesn't reach 5E-8 association p-value in the GWAS and remove MHC region. These are hg19 SNPs # Added the loci with 3000+ variants
  bad_snps<-c("22:50966914:T:C","3:105558837:G:A", "12:9905851:A:C",
            "13:40745693:G:A","16:1073552:A:G","17:38775150:C:T",
            "17:44073889:A:G","18:12830538:G:A","2:100764087:T:G",
            "21:36488822:T:C","21:45621817:A:G","6:127457260:A:G",
            "6:130348257:C:T","7:116895163:G:A","7:51028987:T:A",
            "2:204592021:G:A", "14:75961511:C:T")
  mpra.pics.plot<-subset(mpra.pics.plot,  !(chr=="chr6" & snp_end>29691116 & snp_end<33054976) & !(lead_snp%in%bad_snps))
  
# For each mpra variant, find the disease with the strongest association and its associated PICS data
  mpra.pics.plot$top_pval<-NA #Top GWAS p-value for the MPRA variant
  mpra.pics.plot$top_disease<-NA #Disease corresponding to top GWAS p-value
  mpra.pics.plot$top_PP_running<-NA #Cummulative sum of posterior probabilities for that variant
  mpra.pics.plot$top_pics<-NA #PICS probability for that variant in the top GWAS

  for(i in 1:nrow(mpra.pics.plot)){ #Run through each MPRA variant
  
  top_pval<-max(mpra.pics.plot[i,grepl("_pval",names(mpra.pics.plot))], na.rm=T) #Find the top GWAS p-value
  top_disease<-str_split_fixed(names(mpra.pics.plot)[which(mpra.pics.plot[i,]==top_pval)][1], "\\_", 2)[1] #Find the disease corresponding to the top GWAS p-value
  
  #Write out GWAS and PICS data for top GWAS p-value
  mpra.pics.plot[i,]$top_pval<-top_pval
  mpra.pics.plot[i,]$top_disease<-top_disease
  mpra.pics.plot[i,]$top_PP_running<-mpra.pics.plot[i,paste0(top_disease, "_PP_running")]
  mpra.pics.plot[i,]$top_pics<-mpra.pics.plot[i,paste0(top_disease, "_pics")]
}
  mpra.pics.plot$top_pics<-as.numeric(mpra.pics.plot$top_pics)
  mpra.pics.plot$top_PP_running<-as.numeric(mpra.pics.plot$top_PP_running)

### Sensitivity and specificity calculations ###
dat.pics<-mpra.pics.plot
dhs_loci<-F #TRUE if calculation only for loci where a GWAS SNP overlaps a DHS peak
if(dhs_loci==T){  
  dat.pics<-subset(dat.pics, lead_snp%in%subset(dat.pics, dhs_Tcell_merged>0)$lead_snp)
}

#Calculate credible sets
dat.pics<-dat.pics[order(dat.pics$lead_snp, -dat.pics$top_pics),] 
dat.pics<-subset(dat.pics, select=c(ld_snp, lead_snp, r2, top_PP_running, top_pics,top_pval,dhs_all, dhs_Tcell_merged, mpra_sig))
dat.pics$CS_80<-0 
dat.pics$CS_90<-0 
dat.pics$CS_95<-0 
for(i in 1:nrow(dat.pics)){
  top_pics<-max(subset(dat.pics, lead_snp==dat.pics[i,]$lead_snp)$top_pics)

  if(dat.pics[i,]$top_pics==top_pics){
    dat.pics[i,]$CS_80<-1
    dat.pics[i,]$CS_90<-1 
    dat.pics[i,]$CS_95<-1 
  }else{
    if(dat.pics[i,]$top_pics>=0.01){
      if(dat.pics[i,]$top_PP_running<=0.8){
        dat.pics[i,]$CS_80<-1
      }    
      if(dat.pics[i,]$top_PP_running<=0.9){
        dat.pics[i,]$CS_90<-1 
      }
      if(dat.pics[i,]$top_PP_running<=0.95){
        dat.pics[i,]$CS_95<-1 
      }
    }
  }
}

pics_cs_table <- dat.pics

supp_table_14 <- subset(supp_table_14, select=c(SNP38,SNP19,ld_snp,lead_snp,ld_snp38,lead_snp38,r2,rsid, mpra_sig,Crohns_pval,	Crohns_pics, MS_pval, MS_pics, Psoriasis_pval, Psoriasis_pics, RA_pval, RA_pics, T1D_pval, T1D_pics,	UC_pval, UC_pics, IBD_pval, IBD_pics))

pics_cs_table <- subset(pics_cs_table, select=c(ld_snp,top_pics,top_pval,CS_80,CS_90,CS_95))

supp_table_14 <- merge(supp_table_14,pics_cs_table,by="ld_snp", all.x=TRUE)


```

## Table 15
```{r}
supp_table_15 <- read.delim(paste0(data.dir,"uk_biobank_mpra_supplementary_table.txt"),header=T, stringsAsFactors = F,sep="\t")
```

## Table 16
```{r}
supp_table_16 <- read.delim(paste0(data.dir,"20241111_jurkat_mpra_merge_hg38_all.txt"),header=T, stringsAsFactors = F,sep="\t")
supp_table_16 <- subset(supp_table_16, select=c(SNP19,SNP38, rsid,  ld_snp, lead_snp,mpra_sig,dhs_hTH1,	dhs_hTH17,	dhs_hTH2,	dhs_CD4,	dhs_CD4pos_N,	dhs_hTR,	dhs_Jurkat,	dhs_CD8,	dhs_Tcell_merged,	dhs_all,delta_svm_nCD4,	asc,	atac_qtl_beta,atac_qtl_pval,	eqtl_beta, eqtl_pval,eqtl_gene,	tf_motifbreakr, tss,ananastra_tf,motifbreakr_tf_2024))
supp_table_16

# Add UKBB column
mpra_biobank_merge_all_traits <- read.table(paste0(data.dir,"mpra_biobank_merge_all_traits_hg19",".txt"),header=T, stringsAsFactors = F,sep="\t")
mpra_biobank_merge_all_traits
mpra_biobank_merge_all_traits$SNP19 <- mpra_biobank_merge_all_traits$SNP
mpra_biobank_subset <- subset(mpra_biobank_merge_all_traits, select=c(SNP19,trait,pip))
names(mpra_biobank_subset) <- c("SNP19","ukbb_top_trait","ukbb_top_pip")
supp_table_16 <- merge(supp_table_16,mpra_biobank_subset,by="SNP19",all.x=TRUE)

# Haplotype with emVar column
emvar_loci <- unique(subset(supp_table_16, mpra_sig=="Enhancer_Skew")$lead_snp)
supp_table_16$emvar_in_haplotype <- 0
for(i in 1:nrow(supp_table_16)){
if(supp_table_16$lead_snp[i]%in%emvar_loci){supp_table_16$emvar_in_haplotype[i] <- 1}  
}

supp_table_16
```
## Table 17
```{r}
supp_table_17 <- read.delim(paste0(data.dir,"20241111_tcell_encode_dhs_enrichment_table.txt"),header=T, stringsAsFactors = F,sep="\t")
```

## Table 18
```{r}
supp_table_18 <- read.table(paste0(data.dir,"tcell.precision.skew.dat.txt"), header=T, sep="\t")
```

## Table 19
```{r}
supp_table_19 <- read.table(paste0(data.dir,"unstim.jurkat.precision.skew.dat.txt"), header=T, sep="\t")
```

## Table 20
```{r}
supp_table_20 <- read.table(paste0(data.dir,"motifbreakr/disease_comparison/", "t.test.all.bind.motif.dat_","all_disease",".txt"), header = T,sep="\t")
supp_table_20 <- subset(supp_table_20, select=c("disease","tf","p","d","n","t","df","mu1","sd1","mu0","sd0","adj_p","max.p"))
```


Create the excel workbook for all of the tables
```{r}
wb <- createWorkbook()

# Table 1
addWorksheet(wb, sheetName = "1. Tcell MPRA results")
writeData(wb, sheet = "1. Tcell MPRA results", supp_table_1,keepNA =TRUE)
# Table 2
addWorksheet(wb, sheetName = "2. jurkat MPRA results")
writeData(wb, sheet = "2. jurkat MPRA results", supp_table_2,keepNA =TRUE)
# Table 3 
addWorksheet(wb, sheetName = "3. PICS enr all loci")
writeData(wb, sheet = "3. PICS enr all loci", supp_table_3,keepNA =TRUE)
# Table 4
addWorksheet(wb, sheetName = "4. PICS enr emVar loci")
writeData(wb, sheet = "4. PICS enr emVar loci", supp_table_4,keepNA =TRUE)
# Table 5 
addWorksheet(wb, sheetName = "5. UKBB enr all loci")
writeData(wb, sheet = "5. UKBB enr all loci", supp_table_5,keepNA =TRUE)
# Table 6 
addWorksheet(wb, sheetName = "6. UKBB enr emVar loci")
writeData(wb, sheet = "6. UKBB enr emVar loci", supp_table_6,keepNA =TRUE)
# Table 7 
addWorksheet(wb, sheetName = "7. Tcell motifbreakR results")
writeData(wb, sheet = "7. Tcell motifbreakR results", supp_table_7,keepNA =TRUE)
# Table 8
addWorksheet(wb, sheetName = "8. Tcell motifbreakR ttest")
writeData(wb, sheet = "8. Tcell motifbreakR ttest", supp_table_8,keepNA =TRUE)
# Table 9 
addWorksheet(wb, sheetName = "9. Jurkat motifbreakR results")
writeData(wb, sheet = "9. Jurkat motifbreakR results", supp_table_9,keepNA =TRUE)
# Table 10
addWorksheet(wb, sheetName = "10. Jurkat motifbreakR ttest")
writeData(wb, sheet = "10. Jurkat motifbreakR ttest", supp_table_10,keepNA =TRUE)
# Table 11
addWorksheet(wb, sheetName = "11. chromHMM enrich")
writeData(wb, sheet = "11. chromHMM enrich", supp_table_11,keepNA =TRUE)
# Table 12
addWorksheet(wb, sheetName = "12. Histone CAGE DHS Enrichment")
writeData(wb, sheet = "12. Histone CAGE DHS Enrichment", supp_table_12,keepNA =TRUE)
# Table 13
addWorksheet(wb, sheetName = "13. T-cell MPRA Func. Annot.")
writeData(wb, sheet = "13. T-cell MPRA Func. Annot.", supp_table_13,keepNA =TRUE)
# Table 14
addWorksheet(wb, sheetName = "14. PICS by MPRA")
writeData(wb, sheet = "14. PICS by MPRA", supp_table_14,keepNA =TRUE)
# Table 15
addWorksheet(wb, sheetName = "15. UKBB by MPRA")
writeData(wb, sheet = "15. UKBB by MPRA", supp_table_15,keepNA =TRUE)
# Table 16
addWorksheet(wb, sheetName = "16. Jurkat MPRA Func. Annot.")
writeData(wb, sheet = "16. Jurkat MPRA Func. Annot.", supp_table_16,keepNA =TRUE)
# Table 17
addWorksheet(wb, sheetName = "17. Encode DHS Enrichment")
writeData(wb, sheet = "17. Encode DHS Enrichment", supp_table_17,keepNA =TRUE)
# Table 18
addWorksheet(wb, sheetName = "18. Tcell DHS Grid Search")
writeData(wb, sheet = "18. Tcell DHS Grid Search", supp_table_18,keepNA =TRUE)
# Table 19
addWorksheet(wb, sheetName = "19. Jurkat DHS Grid Search")
writeData(wb, sheet = "19. Jurkat DHS Grid Search", supp_table_19,keepNA =TRUE)
# Table 20
addWorksheet(wb, sheetName = "20. T-cell TF test by disease")
writeData(wb, sheet = "20. T-cell TF test by disease", supp_table_20,keepNA =TRUE)

saveWorkbook(wb, paste0(data.dir, "ho_et_al_big_table12.xlsx"))
```