scripts/make-misc-tsvs.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "dx download chr-subset-genes-variants/all_variants_chr21.tsv\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n",
      "Attaching package: ‘dplyr’\n",
      "\n",
      "\n",
      "The following objects are masked from ‘package:stats’:\n",
      "\n",
      "    filter, lag\n",
      "\n",
      "\n",
      "The following objects are masked from ‘package:base’:\n",
      "\n",
      "    intersect, setdiff, setequal, union\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "library(dplyr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 6 × 5</caption>\n",
       "<thead>\n",
       "\t<tr><th></th><th scope=col>chr</th><th scope=col>start</th><th scope=col>end</th><th scope=col>type</th><th scope=col>variant_id</th></tr>\n",
       "\t<tr><th></th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><th scope=row>1</th><td>21</td><td>5051593</td><td>5051594</td><td>INS</td><td>nssv14017801</td></tr>\n",
       "\t<tr><th scope=row>2</th><td>21</td><td>5064052</td><td>5066138</td><td>DEL</td><td>nssv14300595</td></tr>\n",
       "\t<tr><th scope=row>3</th><td>21</td><td>5064052</td><td>5066138</td><td>DEL</td><td>nssv14301211</td></tr>\n",
       "\t<tr><th scope=row>4</th><td>21</td><td>5064052</td><td>5066138</td><td>DEL</td><td>nssv14301212</td></tr>\n",
       "\t<tr><th scope=row>5</th><td>21</td><td>5064052</td><td>5066138</td><td>DEL</td><td>nssv14301213</td></tr>\n",
       "\t<tr><th scope=row>6</th><td>21</td><td>5064052</td><td>5066138</td><td>DEL</td><td>nssv14432733</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 6 × 5\n",
       "\\begin{tabular}{r|lllll}\n",
       "  & chr & start & end & type & variant\\_id\\\\\n",
       "  & <int> & <int> & <int> & <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t1 & 21 & 5051593 & 5051594 & INS & nssv14017801\\\\\n",
       "\t2 & 21 & 5064052 & 5066138 & DEL & nssv14300595\\\\\n",
       "\t3 & 21 & 5064052 & 5066138 & DEL & nssv14301211\\\\\n",
       "\t4 & 21 & 5064052 & 5066138 & DEL & nssv14301212\\\\\n",
       "\t5 & 21 & 5064052 & 5066138 & DEL & nssv14301213\\\\\n",
       "\t6 & 21 & 5064052 & 5066138 & DEL & nssv14432733\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 6 × 5\n",
       "\n",
       "| <!--/--> | chr &lt;int&gt; | start &lt;int&gt; | end &lt;int&gt; | type &lt;chr&gt; | variant_id &lt;chr&gt; |\n",
       "|---|---|---|---|---|---|\n",
       "| 1 | 21 | 5051593 | 5051594 | INS | nssv14017801 |\n",
       "| 2 | 21 | 5064052 | 5066138 | DEL | nssv14300595 |\n",
       "| 3 | 21 | 5064052 | 5066138 | DEL | nssv14301211 |\n",
       "| 4 | 21 | 5064052 | 5066138 | DEL | nssv14301212 |\n",
       "| 5 | 21 | 5064052 | 5066138 | DEL | nssv14301213 |\n",
       "| 6 | 21 | 5064052 | 5066138 | DEL | nssv14432733 |\n",
       "\n"
      ],
      "text/plain": [
       "  chr start   end     type variant_id  \n",
       "1 21  5051593 5051594 INS  nssv14017801\n",
       "2 21  5064052 5066138 DEL  nssv14300595\n",
       "3 21  5064052 5066138 DEL  nssv14301211\n",
       "4 21  5064052 5066138 DEL  nssv14301212\n",
       "5 21  5064052 5066138 DEL  nssv14301213\n",
       "6 21  5064052 5066138 DEL  nssv14432733"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "vars.df = read.table('all_variants_chr21.tsv', as.is=TRUE, header=TRUE)\n",
    "head(vars.df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# nstd102 inc. ClinGen CNVs\n",
    "```\n",
    "dx download Annotation/nstd102\\ \\(ClinVarSV\\ -\\ includes\\ ClinGen\\ CNVs\\)/nstd102.GRCh38.variant_call.tsv.gz\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Warning message in scan(file = file, what = what, sep = sep, quote = quote, dec = dec, :\n",
      "“EOF within quoted string”\n",
      "Warning message in scan(file = file, what = what, sep = sep, quote = quote, dec = dec, :\n",
      "“number of items read is not a multiple of the number of columns”\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 6 × 38</caption>\n",
       "<thead>\n",
       "\t<tr><th></th><th scope=col>X.variant_call_accession</th><th scope=col>variant_call_id</th><th scope=col>variant_call_type</th><th scope=col>experiment_id</th><th scope=col>sample_id</th><th scope=col>sampleset_id</th><th scope=col>assembly</th><th scope=col>chr</th><th scope=col>contig</th><th scope=col>outer_start</th><th scope=col>⋯</th><th scope=col>remap_alignment</th><th scope=col>remap_best_within_cluster</th><th scope=col>remap_coverage</th><th scope=col>remap_diff_chr</th><th scope=col>remap_failure_code</th><th scope=col>external_links</th><th scope=col>evidence</th><th scope=col>sequence</th><th scope=col>clinical_significance</th><th scope=col>clinical_source</th></tr>\n",
       "\t<tr><th></th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;lgl&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>⋯</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;lgl&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><th scope=row>1</th><td>nssv8639265</td><td>RCV000076578_96551 </td><td>deletion   </td><td>1</td><td>NA</td><td>1</td><td>GRCh38    </td><td>2</td><td></td><td>NA</td><td>⋯</td><td>          </td><td>NA</td><td>NA</td><td>NA</td><td></td><td>ClinGen:CA331581,ClinVar:RCV000076578.2,PubMed:11598466,PubMed:15604628,PubMed:20301390,PubMed:23408351,PubMed:23535968,PubMed:23788249,PubMed:24310308,PubMed:24493721,PubMed:25003300,PubMed:25070057,PubMed:25356965,PubMed:25452455,PubMed:25645574,PubMed:25711197,PubMed:27854360</td><td>NA</td><td></td><td>Pathogenic            </td><td>ClinVar</td></tr>\n",
       "\t<tr><th scope=row>2</th><td>nssv8639267</td><td>RCV000203260_215728</td><td>duplication</td><td>1</td><td>NA</td><td>1</td><td>GRCh38.p12</td><td>8</td><td></td><td>NA</td><td>⋯</td><td>First Pass</td><td> 0</td><td> 1</td><td>NA</td><td></td><td>ClinVar:RCV000203260.1,PubMed:20301641                                                                                                                                                                                                                                                 </td><td>NA</td><td></td><td>Pathogenic            </td><td>ClinVar</td></tr>\n",
       "\t<tr><th scope=row>3</th><td>nssv8639268</td><td>RCV000203462_216747</td><td>deletion   </td><td>1</td><td>NA</td><td>1</td><td>GRCh38.p12</td><td>2</td><td></td><td>NA</td><td>⋯</td><td>First Pass</td><td> 0</td><td> 1</td><td>NA</td><td></td><td>ClinVar:RCV000203462.1,PubMed:20301339                                                                                                                                                                                                                                                 </td><td>NA</td><td></td><td>Pathogenic            </td><td>ClinVar</td></tr>\n",
       "\t<tr><th scope=row>4</th><td>nssv8639269</td><td>RCV000201517_214150</td><td>deletion   </td><td>1</td><td>NA</td><td>1</td><td>GRCh38    </td><td>2</td><td></td><td>NA</td><td>⋯</td><td>          </td><td>NA</td><td>NA</td><td>NA</td><td></td><td>ClinVar:RCV000201517.1                                                                                                                                                                                                                                                                 </td><td>NA</td><td></td><td>Uncertain significance</td><td>ClinVar</td></tr>\n",
       "\t<tr><th scope=row>5</th><td>nssv8639270</td><td>RCV000203882_222922</td><td>deletion   </td><td>1</td><td>NA</td><td>1</td><td>GRCh38    </td><td>7</td><td></td><td>NA</td><td>⋯</td><td>          </td><td>NA</td><td>NA</td><td>NA</td><td></td><td>ClinVar:RCV000203882.1                                                                                                                                                                                                                                                                 </td><td>NA</td><td></td><td>Likely pathogenic     </td><td>ClinVar</td></tr>\n",
       "\t<tr><th scope=row>6</th><td>nssv8639272</td><td>RCV000144263_166057</td><td>deletion   </td><td>1</td><td>NA</td><td>1</td><td>GRCh38    </td><td>3</td><td></td><td>NA</td><td>⋯</td><td>          </td><td>NA</td><td>NA</td><td>NA</td><td></td><td>ClinGen:CA277981,ClinVar:RCV000144263.1,PubMed:20301627,dbSNP:rs1553721650                                                                                                                                                                                                             </td><td>NA</td><td></td><td>Pathogenic            </td><td>ClinVar</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 6 × 38\n",
       "\\begin{tabular}{r|lllllllllllllllllllll}\n",
       "  & X.variant\\_call\\_accession & variant\\_call\\_id & variant\\_call\\_type & experiment\\_id & sample\\_id & sampleset\\_id & assembly & chr & contig & outer\\_start & ⋯ & remap\\_alignment & remap\\_best\\_within\\_cluster & remap\\_coverage & remap\\_diff\\_chr & remap\\_failure\\_code & external\\_links & evidence & sequence & clinical\\_significance & clinical\\_source\\\\\n",
       "  & <chr> & <chr> & <chr> & <int> & <lgl> & <int> & <chr> & <chr> & <chr> & <int> & ⋯ & <chr> & <int> & <dbl> & <int> & <chr> & <chr> & <lgl> & <chr> & <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t1 & nssv8639265 & RCV000076578\\_96551  & deletion    & 1 & NA & 1 & GRCh38     & 2 &  & NA & ⋯ &            & NA & NA & NA &  & ClinGen:CA331581,ClinVar:RCV000076578.2,PubMed:11598466,PubMed:15604628,PubMed:20301390,PubMed:23408351,PubMed:23535968,PubMed:23788249,PubMed:24310308,PubMed:24493721,PubMed:25003300,PubMed:25070057,PubMed:25356965,PubMed:25452455,PubMed:25645574,PubMed:25711197,PubMed:27854360 & NA &  & Pathogenic             & ClinVar\\\\\n",
       "\t2 & nssv8639267 & RCV000203260\\_215728 & duplication & 1 & NA & 1 & GRCh38.p12 & 8 &  & NA & ⋯ & First Pass &  0 &  1 & NA &  & ClinVar:RCV000203260.1,PubMed:20301641                                                                                                                                                                                                                                                  & NA &  & Pathogenic             & ClinVar\\\\\n",
       "\t3 & nssv8639268 & RCV000203462\\_216747 & deletion    & 1 & NA & 1 & GRCh38.p12 & 2 &  & NA & ⋯ & First Pass &  0 &  1 & NA &  & ClinVar:RCV000203462.1,PubMed:20301339                                                                                                                                                                                                                                                  & NA &  & Pathogenic             & ClinVar\\\\\n",
       "\t4 & nssv8639269 & RCV000201517\\_214150 & deletion    & 1 & NA & 1 & GRCh38     & 2 &  & NA & ⋯ &            & NA & NA & NA &  & ClinVar:RCV000201517.1                                                                                                                                                                                                                                                                  & NA &  & Uncertain significance & ClinVar\\\\\n",
       "\t5 & nssv8639270 & RCV000203882\\_222922 & deletion    & 1 & NA & 1 & GRCh38     & 7 &  & NA & ⋯ &            & NA & NA & NA &  & ClinVar:RCV000203882.1                                                                                                                                                                                                                                                                  & NA &  & Likely pathogenic      & ClinVar\\\\\n",
       "\t6 & nssv8639272 & RCV000144263\\_166057 & deletion    & 1 & NA & 1 & GRCh38     & 3 &  & NA & ⋯ &            & NA & NA & NA &  & ClinGen:CA277981,ClinVar:RCV000144263.1,PubMed:20301627,dbSNP:rs1553721650                                                                                                                                                                                                              & NA &  & Pathogenic             & ClinVar\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 6 × 38\n",
       "\n",
       "| <!--/--> | X.variant_call_accession &lt;chr&gt; | variant_call_id &lt;chr&gt; | variant_call_type &lt;chr&gt; | experiment_id &lt;int&gt; | sample_id &lt;lgl&gt; | sampleset_id &lt;int&gt; | assembly &lt;chr&gt; | chr &lt;chr&gt; | contig &lt;chr&gt; | outer_start &lt;int&gt; | ⋯ ⋯ | remap_alignment &lt;chr&gt; | remap_best_within_cluster &lt;int&gt; | remap_coverage &lt;dbl&gt; | remap_diff_chr &lt;int&gt; | remap_failure_code &lt;chr&gt; | external_links &lt;chr&gt; | evidence &lt;lgl&gt; | sequence &lt;chr&gt; | clinical_significance &lt;chr&gt; | clinical_source &lt;chr&gt; |\n",
       "|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|\n",
       "| 1 | nssv8639265 | RCV000076578_96551  | deletion    | 1 | NA | 1 | GRCh38     | 2 | <!----> | NA | ⋯ | <!----> | NA | NA | NA | <!----> | ClinGen:CA331581,ClinVar:RCV000076578.2,PubMed:11598466,PubMed:15604628,PubMed:20301390,PubMed:23408351,PubMed:23535968,PubMed:23788249,PubMed:24310308,PubMed:24493721,PubMed:25003300,PubMed:25070057,PubMed:25356965,PubMed:25452455,PubMed:25645574,PubMed:25711197,PubMed:27854360 | NA | <!----> | Pathogenic             | ClinVar |\n",
       "| 2 | nssv8639267 | RCV000203260_215728 | duplication | 1 | NA | 1 | GRCh38.p12 | 8 | <!----> | NA | ⋯ | First Pass |  0 |  1 | NA | <!----> | ClinVar:RCV000203260.1,PubMed:20301641                                                                                                                                                                                                                                                  | NA | <!----> | Pathogenic             | ClinVar |\n",
       "| 3 | nssv8639268 | RCV000203462_216747 | deletion    | 1 | NA | 1 | GRCh38.p12 | 2 | <!----> | NA | ⋯ | First Pass |  0 |  1 | NA | <!----> | ClinVar:RCV000203462.1,PubMed:20301339                                                                                                                                                                                                                                                  | NA | <!----> | Pathogenic             | ClinVar |\n",
       "| 4 | nssv8639269 | RCV000201517_214150 | deletion    | 1 | NA | 1 | GRCh38     | 2 | <!----> | NA | ⋯ | <!----> | NA | NA | NA | <!----> | ClinVar:RCV000201517.1                                                                                                                                                                                                                                                                  | NA | <!----> | Uncertain significance | ClinVar |\n",
       "| 5 | nssv8639270 | RCV000203882_222922 | deletion    | 1 | NA | 1 | GRCh38     | 7 | <!----> | NA | ⋯ | <!----> | NA | NA | NA | <!----> | ClinVar:RCV000203882.1                                                                                                                                                                                                                                                                  | NA | <!----> | Likely pathogenic      | ClinVar |\n",
       "| 6 | nssv8639272 | RCV000144263_166057 | deletion    | 1 | NA | 1 | GRCh38     | 3 | <!----> | NA | ⋯ | <!----> | NA | NA | NA | <!----> | ClinGen:CA277981,ClinVar:RCV000144263.1,PubMed:20301627,dbSNP:rs1553721650                                                                                                                                                                                                              | NA | <!----> | Pathogenic             | ClinVar |\n",
       "\n"
      ],
      "text/plain": [
       "  X.variant_call_accession variant_call_id     variant_call_type experiment_id\n",
       "1 nssv8639265              RCV000076578_96551  deletion          1            \n",
       "2 nssv8639267              RCV000203260_215728 duplication       1            \n",
       "3 nssv8639268              RCV000203462_216747 deletion          1            \n",
       "4 nssv8639269              RCV000201517_214150 deletion          1            \n",
       "5 nssv8639270              RCV000203882_222922 deletion          1            \n",
       "6 nssv8639272              RCV000144263_166057 deletion          1            \n",
       "  sample_id sampleset_id assembly   chr contig outer_start ⋯ remap_alignment\n",
       "1 NA        1            GRCh38     2          NA          ⋯                \n",
       "2 NA        1            GRCh38.p12 8          NA          ⋯ First Pass     \n",
       "3 NA        1            GRCh38.p12 2          NA          ⋯ First Pass     \n",
       "4 NA        1            GRCh38     2          NA          ⋯                \n",
       "5 NA        1            GRCh38     7          NA          ⋯                \n",
       "6 NA        1            GRCh38     3          NA          ⋯                \n",
       "  remap_best_within_cluster remap_coverage remap_diff_chr remap_failure_code\n",
       "1 NA                        NA             NA                               \n",
       "2  0                         1             NA                               \n",
       "3  0                         1             NA                               \n",
       "4 NA                        NA             NA                               \n",
       "5 NA                        NA             NA                               \n",
       "6 NA                        NA             NA                               \n",
       "  external_links                                                                                                                                                                                                                                                                         \n",
       "1 ClinGen:CA331581,ClinVar:RCV000076578.2,PubMed:11598466,PubMed:15604628,PubMed:20301390,PubMed:23408351,PubMed:23535968,PubMed:23788249,PubMed:24310308,PubMed:24493721,PubMed:25003300,PubMed:25070057,PubMed:25356965,PubMed:25452455,PubMed:25645574,PubMed:25711197,PubMed:27854360\n",
       "2 ClinVar:RCV000203260.1,PubMed:20301641                                                                                                                                                                                                                                                 \n",
       "3 ClinVar:RCV000203462.1,PubMed:20301339                                                                                                                                                                                                                                                 \n",
       "4 ClinVar:RCV000201517.1                                                                                                                                                                                                                                                                 \n",
       "5 ClinVar:RCV000203882.1                                                                                                                                                                                                                                                                 \n",
       "6 ClinGen:CA277981,ClinVar:RCV000144263.1,PubMed:20301627,dbSNP:rs1553721650                                                                                                                                                                                                             \n",
       "  evidence sequence clinical_significance  clinical_source\n",
       "1 NA                Pathogenic             ClinVar        \n",
       "2 NA                Pathogenic             ClinVar        \n",
       "3 NA                Pathogenic             ClinVar        \n",
       "4 NA                Uncertain significance ClinVar        \n",
       "5 NA                Likely pathogenic      ClinVar        \n",
       "6 NA                Pathogenic             ClinVar        "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "clin.df = read.table('nstd102.GRCh38.variant_call.tsv.gz', as.is=TRUE, skip=1, comment='', sep='\\t', header=TRUE)\n",
    "head(clin.df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "clin.df %>% mutate(variant_id=X.variant_call_accession, sv_clinical_significance=clinical_significance) %>% \n",
    "    filter(variant_id %in% vars.df$variant_id) %>% select(variant_id, sv_clinical_significance) %>% \n",
    "    write.table('sv.clinical.variants.chr21.nstd120.tsv', row.names=FALSE, sep='\\t', quote=FALSE)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "dx upload sv.clinical.variants.chr21.nstd120.tsv --path chr-subset-genes-variants/\n",
    "dx upload make-misc-tsvs.ipynb --path chr-subset-genes-variants/\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Compact ClinVar TSV"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "dx download chr-subset-genes-variants/final_clinvar_dbvar_results.txt\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 6 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th></th><th scope=col>variant_id</th><th scope=col>effect</th></tr>\n",
       "\t<tr><th></th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><th scope=row>1</th><td>nssv15216117</td><td>Pathogenic   </td></tr>\n",
       "\t<tr><th scope=row>2</th><td>nssv14267889</td><td>Pathogenic   </td></tr>\n",
       "\t<tr><th scope=row>3</th><td>nssv14267890</td><td>Pathogenic   </td></tr>\n",
       "\t<tr><th scope=row>4</th><td>nssv14407870</td><td>Pathogenic   </td></tr>\n",
       "\t<tr><th scope=row>5</th><td>nssv15136031</td><td>Likely_benign</td></tr>\n",
       "\t<tr><th scope=row>6</th><td>nssv15136031</td><td>Benign       </td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 6 × 2\n",
       "\\begin{tabular}{r|ll}\n",
       "  & variant\\_id & effect\\\\\n",
       "  & <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t1 & nssv15216117 & Pathogenic   \\\\\n",
       "\t2 & nssv14267889 & Pathogenic   \\\\\n",
       "\t3 & nssv14267890 & Pathogenic   \\\\\n",
       "\t4 & nssv14407870 & Pathogenic   \\\\\n",
       "\t5 & nssv15136031 & Likely\\_benign\\\\\n",
       "\t6 & nssv15136031 & Benign       \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 6 × 2\n",
       "\n",
       "| <!--/--> | variant_id &lt;chr&gt; | effect &lt;chr&gt; |\n",
       "|---|---|---|\n",
       "| 1 | nssv15216117 | Pathogenic    |\n",
       "| 2 | nssv14267889 | Pathogenic    |\n",
       "| 3 | nssv14267890 | Pathogenic    |\n",
       "| 4 | nssv14407870 | Pathogenic    |\n",
       "| 5 | nssv15136031 | Likely_benign |\n",
       "| 6 | nssv15136031 | Benign        |\n",
       "\n"
      ],
      "text/plain": [
       "  variant_id   effect       \n",
       "1 nssv15216117 Pathogenic   \n",
       "2 nssv14267889 Pathogenic   \n",
       "3 nssv14267890 Pathogenic   \n",
       "4 nssv14407870 Pathogenic   \n",
       "5 nssv15136031 Likely_benign\n",
       "6 nssv15136031 Benign       "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "clinvar = read.table('final_clinvar_dbvar_results.txt', as.is=TRUE, sep='\\t')\n",
    "colnames(clinvar) = c('variant_id', 'effect')\n",
    "head(clinvar)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "`summarise()` regrouping output by 'variant_id' (override with `.groups` argument)\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A grouped_df: 6 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>variant_id</th><th scope=col>effect</th><th scope=col>n</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>essv100139</td><td>Pathogenic                                  </td><td> 1</td></tr>\n",
       "\t<tr><td>essv100355</td><td>Pathogenic                                  </td><td> 1</td></tr>\n",
       "\t<tr><td>essv100680</td><td>Benign                                      </td><td>26</td></tr>\n",
       "\t<tr><td>essv100680</td><td>Benign/Likely_benign                        </td><td> 7</td></tr>\n",
       "\t<tr><td>essv100680</td><td>Conflicting_interpretations_of_pathogenicity</td><td> 9</td></tr>\n",
       "\t<tr><td>essv100680</td><td>Likely_benign                               </td><td>74</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A grouped\\_df: 6 × 3\n",
       "\\begin{tabular}{lll}\n",
       " variant\\_id & effect & n\\\\\n",
       " <chr> & <chr> & <int>\\\\\n",
       "\\hline\n",
       "\t essv100139 & Pathogenic                                   &  1\\\\\n",
       "\t essv100355 & Pathogenic                                   &  1\\\\\n",
       "\t essv100680 & Benign                                       & 26\\\\\n",
       "\t essv100680 & Benign/Likely\\_benign                         &  7\\\\\n",
       "\t essv100680 & Conflicting\\_interpretations\\_of\\_pathogenicity &  9\\\\\n",
       "\t essv100680 & Likely\\_benign                                & 74\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A grouped_df: 6 × 3\n",
       "\n",
       "| variant_id &lt;chr&gt; | effect &lt;chr&gt; | n &lt;int&gt; |\n",
       "|---|---|---|\n",
       "| essv100139 | Pathogenic                                   |  1 |\n",
       "| essv100355 | Pathogenic                                   |  1 |\n",
       "| essv100680 | Benign                                       | 26 |\n",
       "| essv100680 | Benign/Likely_benign                         |  7 |\n",
       "| essv100680 | Conflicting_interpretations_of_pathogenicity |  9 |\n",
       "| essv100680 | Likely_benign                                | 74 |\n",
       "\n"
      ],
      "text/plain": [
       "  variant_id effect                                       n \n",
       "1 essv100139 Pathogenic                                    1\n",
       "2 essv100355 Pathogenic                                    1\n",
       "3 essv100680 Benign                                       26\n",
       "4 essv100680 Benign/Likely_benign                          7\n",
       "5 essv100680 Conflicting_interpretations_of_pathogenicity  9\n",
       "6 essv100680 Likely_benign                                74"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "clinvar.sum = clinvar %>% group_by(variant_id, effect) %>% summarize(n=n())\n",
    "head(clinvar.sum)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "write.table(clinvar.sum, file='final_clinvar_dbvar_results_summary.txt', sep='\\t', row.names=FALSE, quote=FALSE)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}