analysis.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Simple analysis to identify Spike mutations associated with GSIAD-annotated Pangolin linages\n",
    "# We align each\n",
    "# This analysis requires that you download the \"spikeprot\" spike protein sequences from GISAID,\n",
    "# as well as the metadata file from GISAID.\n",
    "# We cannot store this data in this repo due to licensing.\n",
    "#\n",
    "import os\n",
    "import sys\n",
    "import re\n",
    "\n",
    "import tqdm\n",
    "\n",
    "import pandas\n",
    "import seaborn\n",
    "from matplotlib import pyplot\n",
    "\n",
    "%config InlineBackend.figure_format='retina'\n",
    "\n",
    "# Yabul is a little library for fasta reading and alignment.\n",
    "# !pip install yabul\n",
    "import yabul"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(514759, 'EPI_ISL_402124')"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spike_df = yabul.read_fasta(\"spikeprot0223.fasta\").rename(columns={\"description\": \"sequence_id\"})\n",
    "\n",
    "# Parse out the EPI ISL ID\n",
    "spike_df[\"gisaid_epi_isl\"] = spike_df.sequence_id.map(lambda s: re.search(\"\\\\|(EPI_ISL_[0-9]+)\\\\|\", s).groups()[0])\n",
    "\n",
    "metadata_df = pandas.read_csv(\"metadata_2021-02-24_10-05.tsv.gz\", sep=None, engine='python')\n",
    "spike_df = spike_df.merge(metadata_df, on=\"gisaid_epi_isl\").sort_values(\"date\").drop_duplicates(\"gisaid_epi_isl\").set_index(\"gisaid_epi_isl\")\n",
    "spike_df = spike_df.rename(columns={\"sequence\": \"unaligned_sequence\"})\n",
    "spike_df\n",
    "\n",
    "reference, = spike_df.loc[\n",
    "    spike_df.sequence_id.str.lower().str.contains(\"wiv04\")\n",
    "].drop_duplicates().index\n",
    "reference\n",
    "\n",
    "len(spike_df), reference"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSSVLHSTQDLFLPFFSNVTWFHAIHVSGTNGTKRFDNPVLPFNDGVYFASTEKSNIIRGWIFGTTLDSKTQSLLIVNNATNVVIKVCEFQFCNDPFLGVYYHKNNKSWMESEFRVYSSANNCTFEYVSQPFLMDLEGKQGNFKNLREFVFKNIDGYFKIYSKHTPINLVRDLPQGFSALEPLVDLPIGINITRFQTLLALHRSYLTPGDSSSGWTAGAAAYYVGYLQPRTFLLKYNENGTITDAVDCALDPLSETKCTLKSFTVEKGIYQTSNFRVQPTESIVRFPNITNLCPFGEVFNATRFASVYAWNRKRISNCVADYSVLYNSASFSTFKCYGVSPTKLNDLCFTNVYADSFVIRGDEVRQIAPGQTGKIADYNYKLPDDFTGCVIAWNSNNLDSKVGGNYNYLYRLFRKSNLKPFERDISTEIYQAGSTPCNGVEGFNCYFPLQSYGFQPTNGVGYQPYRVVVLSFELLHAPATVCGPKKSTNLVKNKCVNFNFNGLTGTGVLTESNKKFLPFQQFGRDIADTTDAVRDPQTLEILDITPCSFGGVSVITPGTNTSNQVAVLYQDVNCTEVPVAIHADQLTPTWRVYSTGSNVFQTRAGCLIGAEHVNNSYECDIPIGAGICASYQTQTNSPRRARSVASQSIIAYTMSLGAENSVAYSNNSIAIPTNFTISVTTEILPVSMTKTSVDCTMYICGDSTECSNLLLQYGSFCTQLNRALTGIAVEQDKNTQEVFAQVKQIYKTPPIKDFGGFNFSQILPDPSKPSKRSFIEDLLFNKVTLADAGFIKQYGDCLGDIAARDLICAQKFNGLTVLPPLLTDEMIAQYTSALLAGTITSGWTFGAGAALQIPFAMQMAYRFNGIGVTQNVLYENQKLIANQFNSAIGKIQDSLSSTASALGKLQDVVNQNAQALNTLVKQLSSNFGAISSVLNDILSRLDKVEAEVQIDRLITGRLQSLQTYVTQQLIRAAEIRASANLAATKMSECVLGQSKRVDFCGKGYHLMSFPQSAPHGVVFLHVTYVPAQEKNFTTAPAICHDGKAHFPREGVFVSNGTHWFVTQRNFYEPQIITTDNTFVSGNCDVVIGIVNNTVYDPLQPELDSFKEELDKYFKNHTSPDVDLGDISGINASVVNIQKEIDRLNEVAKNLNESLIDLQELGKYEQYIKWPWYIWLGFIAGLIAIVMVTIMLCCMTSCCSCLKGCCSCGSCCKFDEDDSEPVLKGVKLHYT*'"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reference_seq = spike_df.loc[reference, \"unaligned_sequence\"]\n",
    "reference_seq"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSSVLHSTQDLFLPFFSNVTWFHAISGTNGTKRFDNPVLPFNDGVYFASTEKSNIIRGWIFGTTLDSKTQSLLIVNNATNVVIKVCEFQFCNDPFLGVYHKNNKSWMESEFRVYSSANNCTFEYVSQPFLMDLEGKQGNFKNLREFVFKNIDGYFKIYSKHTPINLVRDLPQGFSALEPLVDLPIGINITRFQTLLALHRSYLTPGDSSSGWTAGAAAYYVGYLQPRTFLLKYNENGTITDAVDCALDPLSETKCTLKSFTVEKGIYQTSNFRVQPTESIVRFPNITNLCPFGEVFNATRFASVYAWNRKRISNCVADYSVLYNSASFSTFKCYGVSPTKLNDLCFTNVYADSFVIRGDEVRQIAPGQTGKIADYNYKLPDDFTGCVIAWNSNNLDSKVGGNYNYLYRLFRKSNLKPFERDISTEIYQAGSTPCNGVEGFNCYFPLQSYGFQPTYGVGYQPYRVVVLSFELLHAPATVCGPKKSTNLVKNKCVNFNFNGLTGTGVLTESNKKFLPFQQFGRDIDDTTDAVRDPQTLEILDITPCSFGGVSVITPGTNTSNQVAVLYQGVNCTEVPVAIHADQLTPTWRVYSTGSNVFQTRAGCLIGAEHVNNSYECDIPIGAGICASYQTQTNSHRRARSVASQSIIAYTMSLGAENSVAYSNNSIAIPINFTISVTTEILPVSMTKTSVDCTMYICGDSTECSNLLLQYGSFCTQLNRALTGIAVEQDKNTQEVFAQVKQIYKTPPIKDFGGFNFSQILPDPSKPSKRSFIEDLLFNKVTLADAGFIKQYGDCLGDIAARDLICAQKFNGLTVLPPLLTDEMIAQYTSALLAGTITSGWTFGAGAALQIPFAMQMAYRFNGIGVTQNVLYENQKLIANQFNSAIGKIQDSLSSTASALGKLQDVVNQNAQALNTLVKQLSSNFGAISSVLNDILARLDKVEAEVQIDRLITGRLQSLQTYVTQQLIRAAEIRASANLAATKMSECVLGQSKRVDFCGKGYHLMSFPQSAPHGVVFLHVTYVPAQEKNFTTAPAICHDGKAHFPREGVFVSNGTHWFVTQRNFYEPQIITTHNTFVSGNCDVVIGIVNNTVYDPLQPELDSFKEELDKYFKNHTSPDVDLGDISGINASVVNIQKEIDRLNEVAKNLNESLIDLQELGKYEQYIKWPWYIWLGFIAGLIAIVMVTIMLCCMTSCCSCLKGCCSCGSCCKFDEDDSEPVLKGVKLHYT*',\n",
       " False,\n",
       " 1274,\n",
       " 1271)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "other_seq = spike_df.iloc[-1].unaligned_sequence\n",
    "other_seq, other_seq == reference_seq, len(reference_seq), len(other_seq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 74964/74964 [07:52<00:00, 158.76it/s]\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>unaligned_sequence</th>\n",
       "      <th>aligned_ref</th>\n",
       "      <th>aligned_query</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSSTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSSTRGVYYPDKVFRSS...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MFVFLFVLPLVSSQCVNLTTRTGIPPGYTNSSTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLFVLPLVSSQCVNLTTRTGIPPGYTNSSTRGVYYPDKVFRSS...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MFVFLFVLPLVSSQCVNLTTRTGIQPGYTNSSTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLFVLPLVSSQCVNLTTRTGIQPGYTNSSTRGVYYPDKVFRSS...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MFVFLFVLPLVSSQCVNLTTRTGIPPGYTNSSTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLFVLPLVSSQCVNLTTRTGIPPGYTNSSTRGVYYPDKVFRSS...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MFVFLFVLPLVSSQCVXXXTRTGIPPGYTNSSTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLFVLPLVSSQCVXXXTRTGIPPGYTNSSTRGVYYPDKVFRSS...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74959</th>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74960</th>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74961</th>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74962</th>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74963</th>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYXXKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYXXKVFRSS...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>74964 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      unaligned_sequence  \\\n",
       "0      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSSTRGVYYPDKVFRSS...   \n",
       "1      MFVFLFVLPLVSSQCVNLTTRTGIPPGYTNSSTRGVYYPDKVFRSS...   \n",
       "2      MFVFLFVLPLVSSQCVNLTTRTGIQPGYTNSSTRGVYYPDKVFRSS...   \n",
       "3      MFVFLFVLPLVSSQCVNLTTRTGIPPGYTNSSTRGVYYPDKVFRSS...   \n",
       "4      MFVFLFVLPLVSSQCVXXXTRTGIPPGYTNSSTRGVYYPDKVFRSS...   \n",
       "...                                                  ...   \n",
       "74959  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "74960  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "74961  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "74962  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "74963  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYXXKVFRSS...   \n",
       "\n",
       "                                             aligned_ref  \\\n",
       "0      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "1      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "2      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "3      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "4      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "...                                                  ...   \n",
       "74959  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "74960  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "74961  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "74962  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "74963  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "\n",
       "                                           aligned_query  \n",
       "0      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSSTRGVYYPDKVFRSS...  \n",
       "1      MFVFLFVLPLVSSQCVNLTTRTGIPPGYTNSSTRGVYYPDKVFRSS...  \n",
       "2      MFVFLFVLPLVSSQCVNLTTRTGIQPGYTNSSTRGVYYPDKVFRSS...  \n",
       "3      MFVFLFVLPLVSSQCVNLTTRTGIPPGYTNSSTRGVYYPDKVFRSS...  \n",
       "4      MFVFLFVLPLVSSQCVXXXTRTGIPPGYTNSSTRGVYYPDKVFRSS...  \n",
       "...                                                  ...  \n",
       "74959  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...  \n",
       "74960  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...  \n",
       "74961  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...  \n",
       "74962  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...  \n",
       "74963  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYXXKVFRSS...  \n",
       "\n",
       "[74964 rows x 3 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pairwise_alignments_df = []\n",
    "for seq in tqdm.tqdm(spike_df.unaligned_sequence.unique()):\n",
    "    result = yabul.align_pair(seq, reference_seq)\n",
    "    pairwise_alignments_df.append((\n",
    "        seq,\n",
    "        result.reference,\n",
    "        result.query\n",
    "    ))\n",
    "pairwise_alignments_df = pandas.DataFrame(\n",
    "    pairwise_alignments_df, columns=[\"unaligned_sequence\", \"aligned_ref\", \"aligned_query\"])\n",
    "pairwise_alignments_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "74964it [00:18, 4111.80it/s]\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>unaligned_sequence</th>\n",
       "      <th>aligned_ref</th>\n",
       "      <th>aligned_query</th>\n",
       "      <th>muts</th>\n",
       "      <th>num_muts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSSTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSSTRGVYYPDKVFRSS...</td>\n",
       "      <td>[p.F32S, p.S50L, p.T76I, p.Q218P, p.E324D, p.R...</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MFVFLFVLPLVSSQCVNLTTRTGIPPGYTNSSTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLFVLPLVSSQCVNLTTRTGIPPGYTNSSTRGVYYPDKVFRSS...</td>\n",
       "      <td>[p.V6F, p.L7V, p.Q23G, p.L24I, p.A27G, p.F32S,...</td>\n",
       "      <td>94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MFVFLFVLPLVSSQCVNLTTRTGIQPGYTNSSTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLFVLPLVSSQCVNLTTRTGIQPGYTNSSTRGVYYPDKVFRSS...</td>\n",
       "      <td>[p.V6F, p.L7V, p.Q23G, p.L24I, p.P25Q, p.A27G,...</td>\n",
       "      <td>94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MFVFLFVLPLVSSQCVNLTTRTGIPPGYTNSSTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLFVLPLVSSQCVNLTTRTGIPPGYTNSSTRGVYYPDKVFRSS...</td>\n",
       "      <td>[p.V6F, p.L7V, p.Q23G, p.L24I, p.A27G, p.F32S,...</td>\n",
       "      <td>93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MFVFLFVLPLVSSQCVXXXTRTGIPPGYTNSSTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLFVLPLVSSQCVXXXTRTGIPPGYTNSSTRGVYYPDKVFRSS...</td>\n",
       "      <td>[p.V6F, p.L7V, p.Q23G, p.L24I, p.A27G, p.F32S,...</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74959</th>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>[p.D614G, p.A846V]</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74960</th>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>[p.A520S, p.D614G]</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74961</th>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>[p.S255A, p.A263S, p.A264G, p.Y265W, p.Y266T, ...</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74962</th>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>[del.69-70, del.144, p.N501Y, p.A570D, p.D614G...</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74963</th>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYXXKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYXXKVFRSS...</td>\n",
       "      <td>[p.D614G]</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>74964 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      unaligned_sequence  \\\n",
       "0      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSSTRGVYYPDKVFRSS...   \n",
       "1      MFVFLFVLPLVSSQCVNLTTRTGIPPGYTNSSTRGVYYPDKVFRSS...   \n",
       "2      MFVFLFVLPLVSSQCVNLTTRTGIQPGYTNSSTRGVYYPDKVFRSS...   \n",
       "3      MFVFLFVLPLVSSQCVNLTTRTGIPPGYTNSSTRGVYYPDKVFRSS...   \n",
       "4      MFVFLFVLPLVSSQCVXXXTRTGIPPGYTNSSTRGVYYPDKVFRSS...   \n",
       "...                                                  ...   \n",
       "74959  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "74960  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "74961  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "74962  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "74963  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYXXKVFRSS...   \n",
       "\n",
       "                                             aligned_ref  \\\n",
       "0      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "1      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "2      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "3      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "4      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "...                                                  ...   \n",
       "74959  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "74960  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "74961  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "74962  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "74963  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "\n",
       "                                           aligned_query  \\\n",
       "0      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSSTRGVYYPDKVFRSS...   \n",
       "1      MFVFLFVLPLVSSQCVNLTTRTGIPPGYTNSSTRGVYYPDKVFRSS...   \n",
       "2      MFVFLFVLPLVSSQCVNLTTRTGIQPGYTNSSTRGVYYPDKVFRSS...   \n",
       "3      MFVFLFVLPLVSSQCVNLTTRTGIPPGYTNSSTRGVYYPDKVFRSS...   \n",
       "4      MFVFLFVLPLVSSQCVXXXTRTGIPPGYTNSSTRGVYYPDKVFRSS...   \n",
       "...                                                  ...   \n",
       "74959  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "74960  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "74961  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "74962  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "74963  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYXXKVFRSS...   \n",
       "\n",
       "                                                    muts  num_muts  \n",
       "0      [p.F32S, p.S50L, p.T76I, p.Q218P, p.E324D, p.R...        30  \n",
       "1      [p.V6F, p.L7V, p.Q23G, p.L24I, p.A27G, p.F32S,...        94  \n",
       "2      [p.V6F, p.L7V, p.Q23G, p.L24I, p.P25Q, p.A27G,...        94  \n",
       "3      [p.V6F, p.L7V, p.Q23G, p.L24I, p.A27G, p.F32S,...        93  \n",
       "4      [p.V6F, p.L7V, p.Q23G, p.L24I, p.A27G, p.F32S,...        45  \n",
       "...                                                  ...       ...  \n",
       "74959                                 [p.D614G, p.A846V]         2  \n",
       "74960                                 [p.A520S, p.D614G]         2  \n",
       "74961  [p.S255A, p.A263S, p.A264G, p.Y265W, p.Y266T, ...         8  \n",
       "74962  [del.69-70, del.144, p.N501Y, p.A570D, p.D614G...         9  \n",
       "74963                                          [p.D614G]         1  \n",
       "\n",
       "[74964 rows x 5 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Positions are 1-based inclusive\n",
    "\n",
    "def mut_tuple_to_human_mut(tpl):\n",
    "    # Helper function for call_muts.\n",
    "    # Given a tuple describing a mut, return\n",
    "    # a string giving the human readable version.\n",
    "    # The input tpl should have one of the following forms:\n",
    "    # - (\"sub\", position, reference aa, variant aa)\n",
    "    # - (\"del\", start position, end position)\n",
    "    # - (\"ins\", position, inserted sequence)\n",
    "    #\n",
    "    kind = tpl[0]\n",
    "    if kind == \"sub\":\n",
    "        (pos, ref_aa, aa) = tpl[1:]\n",
    "        return \"p.%s%d%s\" % (ref_aa, pos, aa)\n",
    "    elif kind == \"del\":\n",
    "        (start, end) = tpl[1:]\n",
    "        if start == end:\n",
    "            return \"del.%d\" % start\n",
    "        else:\n",
    "            return \"del.%d-%d\" % (start, end)\n",
    "    elif kind == \"ins\":\n",
    "        (pos, seq) = tpl[1:]\n",
    "        return \"ins.%d%s\" % (pos, seq)\n",
    "\n",
    "MUTS_CACHE = {}\n",
    "def call_muts(reference_seq, seq, exclude_x=True):\n",
    "    # Given a full length reference seq and a variant seq, both aligned (i.e. same length),\n",
    "    # return a list of strings giving the mutations in human-readable form.\n",
    "    \n",
    "    try:\n",
    "        return MUTS_CACHE[(reference_seq, seq)]\n",
    "    except KeyError:\n",
    "        pass\n",
    "    \n",
    "    pos = 1\n",
    "    assert len(seq) == len(reference_seq)\n",
    "    results = []\n",
    "    for (aligned_pos, (aa, ref_aa)) in enumerate(zip(seq, reference_seq)):\n",
    "        if aa != ref_aa:\n",
    "            if aa == \"X\" and exclude_x:\n",
    "                pass\n",
    "            elif aa == \"-\":\n",
    "                # Deletion\n",
    "                if results and results[-1][0] == \"del\" and results[-1][2] == pos - 1:\n",
    "                    # Merge with deletion of previous\n",
    "                    results[-1] = (\"del\", results[-1][1], pos)\n",
    "                else:\n",
    "                    # Start a new deletion\n",
    "                    results.append((\"del\", pos, pos))\n",
    "            elif ref_aa == \"-\":\n",
    "                # Insertion\n",
    "                if results and results[-1][0] == \"ins\" and results[-1][1] == pos:\n",
    "                    # Merge with previous\n",
    "                    results.append((\"ins\", pos, results[-1][2] + aa))\n",
    "            else:\n",
    "                # Simple mutation\n",
    "                results.append((\"sub\", pos, ref_aa, aa))\n",
    "        \n",
    "        if ref_aa != \"-\":\n",
    "            pos += 1\n",
    "                \n",
    "    human_results = [mut_tuple_to_human_mut(tpl) for tpl in results]\n",
    "    MUTS_CACHE[(reference_seq, seq)] = human_results\n",
    "    return human_results\n",
    "            \n",
    "pairwise_alignments_df[\"muts\"] = [\n",
    "    call_muts(row.aligned_ref, row.aligned_query)\n",
    "    for _, row in tqdm.tqdm(pairwise_alignments_df.iterrows())\n",
    "]\n",
    "pairwise_alignments_df[\"num_muts\"] = pairwise_alignments_df[\"muts\"].str.len()\n",
    "pairwise_alignments_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sequence_id</th>\n",
       "      <th>unaligned_sequence</th>\n",
       "      <th>strain</th>\n",
       "      <th>virus</th>\n",
       "      <th>genbank_accession</th>\n",
       "      <th>date</th>\n",
       "      <th>region</th>\n",
       "      <th>country</th>\n",
       "      <th>division</th>\n",
       "      <th>location</th>\n",
       "      <th>...</th>\n",
       "      <th>authors</th>\n",
       "      <th>url</th>\n",
       "      <th>title</th>\n",
       "      <th>paper_url</th>\n",
       "      <th>date_submitted</th>\n",
       "      <th>purpose_of_sequencing</th>\n",
       "      <th>aligned_ref</th>\n",
       "      <th>aligned_query</th>\n",
       "      <th>muts</th>\n",
       "      <th>num_muts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Spike|hCoV-19/bat/Yunnan/RaTG13/2013|2013-07-2...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSSTRGVYYPDKVFRSS...</td>\n",
       "      <td>bat/Yunnan/RaTG13/2013</td>\n",
       "      <td>ncov</td>\n",
       "      <td>?</td>\n",
       "      <td>2013-07-24</td>\n",
       "      <td>Asia</td>\n",
       "      <td>China</td>\n",
       "      <td>Yunnan</td>\n",
       "      <td>Pu'Er</td>\n",
       "      <td>...</td>\n",
       "      <td>Yan Zhu et al</td>\n",
       "      <td>https://www.gisaid.org</td>\n",
       "      <td>?</td>\n",
       "      <td>?</td>\n",
       "      <td>2020-01-24</td>\n",
       "      <td>?</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSSTRGVYYPDKVFRSS...</td>\n",
       "      <td>[p.F32S, p.S50L, p.T76I, p.Q218P, p.E324D, p.R...</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Spike|hCoV-19/pangolin/Guangxi/P4L/2017|2017-0...</td>\n",
       "      <td>MFVFLFVLPLVSSQCVNLTTRTGIPPGYTNSSTRGVYYPDKVFRSS...</td>\n",
       "      <td>pangolin/Guangxi/P4L/2017</td>\n",
       "      <td>ncov</td>\n",
       "      <td>?</td>\n",
       "      <td>2017</td>\n",
       "      <td>Asia</td>\n",
       "      <td>China</td>\n",
       "      <td>Guangxi</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Wu-Chun Cao et al</td>\n",
       "      <td>https://www.gisaid.org</td>\n",
       "      <td>?</td>\n",
       "      <td>?</td>\n",
       "      <td>2020-02-17</td>\n",
       "      <td>?</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLFVLPLVSSQCVNLTTRTGIPPGYTNSSTRGVYYPDKVFRSS...</td>\n",
       "      <td>[p.V6F, p.L7V, p.Q23G, p.L24I, p.A27G, p.F32S,...</td>\n",
       "      <td>94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Spike|hCoV-19/pangolin/Guangxi/P1E/2017|2017-0...</td>\n",
       "      <td>MFVFLFVLPLVSSQCVNLTTRTGIQPGYTNSSTRGVYYPDKVFRSS...</td>\n",
       "      <td>pangolin/Guangxi/P1E/2017</td>\n",
       "      <td>ncov</td>\n",
       "      <td>?</td>\n",
       "      <td>2017</td>\n",
       "      <td>Asia</td>\n",
       "      <td>China</td>\n",
       "      <td>Guangxi</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Wu-Chun Cao et al</td>\n",
       "      <td>https://www.gisaid.org</td>\n",
       "      <td>?</td>\n",
       "      <td>?</td>\n",
       "      <td>2020-02-17</td>\n",
       "      <td>?</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLFVLPLVSSQCVNLTTRTGIQPGYTNSSTRGVYYPDKVFRSS...</td>\n",
       "      <td>[p.V6F, p.L7V, p.Q23G, p.L24I, p.P25Q, p.A27G,...</td>\n",
       "      <td>94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Spike|hCoV-19/pangolin/Guangxi/P5L/2017|2017-0...</td>\n",
       "      <td>MFVFLFVLPLVSSQCVNLTTRTGIPPGYTNSSTRGVYYPDKVFRSS...</td>\n",
       "      <td>pangolin/Guangxi/P5L/2017</td>\n",
       "      <td>ncov</td>\n",
       "      <td>?</td>\n",
       "      <td>2017</td>\n",
       "      <td>Asia</td>\n",
       "      <td>China</td>\n",
       "      <td>Guangxi</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Wu-Chun Cao et al</td>\n",
       "      <td>https://www.gisaid.org</td>\n",
       "      <td>?</td>\n",
       "      <td>?</td>\n",
       "      <td>2020-02-17</td>\n",
       "      <td>?</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLFVLPLVSSQCVNLTTRTGIPPGYTNSSTRGVYYPDKVFRSS...</td>\n",
       "      <td>[p.V6F, p.L7V, p.Q23G, p.L24I, p.A27G, p.F32S,...</td>\n",
       "      <td>93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Spike|hCoV-19/pangolin/Guangxi/P3B/2017|2017-0...</td>\n",
       "      <td>MFVFLFVLPLVSSQCVXXXTRTGIPPGYTNSSTRGVYYPDKVFRSS...</td>\n",
       "      <td>pangolin/Guangxi/P3B/2017</td>\n",
       "      <td>ncov</td>\n",
       "      <td>?</td>\n",
       "      <td>2017</td>\n",
       "      <td>Asia</td>\n",
       "      <td>China</td>\n",
       "      <td>Guangxi</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Wu-Chun Cao et al</td>\n",
       "      <td>https://www.gisaid.org</td>\n",
       "      <td>?</td>\n",
       "      <td>?</td>\n",
       "      <td>2020-02-17</td>\n",
       "      <td>?</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLFVLPLVSSQCVXXXTRTGIPPGYTNSSTRGVYYPDKVFRSS...</td>\n",
       "      <td>[p.V6F, p.L7V, p.Q23G, p.L24I, p.A27G, p.F32S,...</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>514754</th>\n",
       "      <td>Spike|hCoV-19/USA/ME-HETL-J1279/2021|2021-02-0...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>USA/ME-HETL-J1279/2021</td>\n",
       "      <td>ncov</td>\n",
       "      <td>?</td>\n",
       "      <td>2021-02-05</td>\n",
       "      <td>North America</td>\n",
       "      <td>USA</td>\n",
       "      <td>Maine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Matluk et al</td>\n",
       "      <td>https://www.gisaid.org</td>\n",
       "      <td>?</td>\n",
       "      <td>?</td>\n",
       "      <td>2021-02-11</td>\n",
       "      <td>?</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>[p.D614G, p.A846V]</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>514755</th>\n",
       "      <td>Spike|hCoV-19/USA/ME-HETL-J1309/2021|2021-02-0...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>USA/ME-HETL-J1309/2021</td>\n",
       "      <td>ncov</td>\n",
       "      <td>?</td>\n",
       "      <td>2021-02-05</td>\n",
       "      <td>North America</td>\n",
       "      <td>USA</td>\n",
       "      <td>Maine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Matluk et al</td>\n",
       "      <td>https://www.gisaid.org</td>\n",
       "      <td>?</td>\n",
       "      <td>?</td>\n",
       "      <td>2021-02-11</td>\n",
       "      <td>?</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>[p.A520S, p.D614G]</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>514756</th>\n",
       "      <td>Spike|hCoV-19/USA/ME-HETL-J1280/2021|2021-02-0...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>USA/ME-HETL-J1280/2021</td>\n",
       "      <td>ncov</td>\n",
       "      <td>?</td>\n",
       "      <td>2021-02-05</td>\n",
       "      <td>North America</td>\n",
       "      <td>USA</td>\n",
       "      <td>Maine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Matluk et al</td>\n",
       "      <td>https://www.gisaid.org</td>\n",
       "      <td>?</td>\n",
       "      <td>?</td>\n",
       "      <td>2021-02-11</td>\n",
       "      <td>?</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>[p.S255A, p.A263S, p.A264G, p.Y265W, p.Y266T, ...</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>514757</th>\n",
       "      <td>Spike|hCoV-19/Australia/QLD1545/2021|2021-02-0...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>Australia/QLD1545/2021</td>\n",
       "      <td>ncov</td>\n",
       "      <td>?</td>\n",
       "      <td>2021-02-05</td>\n",
       "      <td>Oceania</td>\n",
       "      <td>Australia</td>\n",
       "      <td>Australia</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Son Nguyen et al et al</td>\n",
       "      <td>https://www.gisaid.org</td>\n",
       "      <td>?</td>\n",
       "      <td>?</td>\n",
       "      <td>2021-02-09</td>\n",
       "      <td>?</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>[del.69-70, del.144, p.N501Y, p.A570D, p.D614G...</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>514758</th>\n",
       "      <td>Spike|hCoV-19/USA/ME-HETL-J1293/2021|2021-02-0...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYXXKVFRSS...</td>\n",
       "      <td>USA/ME-HETL-J1293/2021</td>\n",
       "      <td>ncov</td>\n",
       "      <td>?</td>\n",
       "      <td>2021-02-05</td>\n",
       "      <td>North America</td>\n",
       "      <td>USA</td>\n",
       "      <td>Maine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Matluk et al</td>\n",
       "      <td>https://www.gisaid.org</td>\n",
       "      <td>?</td>\n",
       "      <td>?</td>\n",
       "      <td>2021-02-11</td>\n",
       "      <td>?</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYXXKVFRSS...</td>\n",
       "      <td>[p.D614G]</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>514759 rows × 33 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              sequence_id  \\\n",
       "0       Spike|hCoV-19/bat/Yunnan/RaTG13/2013|2013-07-2...   \n",
       "1       Spike|hCoV-19/pangolin/Guangxi/P4L/2017|2017-0...   \n",
       "2       Spike|hCoV-19/pangolin/Guangxi/P1E/2017|2017-0...   \n",
       "3       Spike|hCoV-19/pangolin/Guangxi/P5L/2017|2017-0...   \n",
       "4       Spike|hCoV-19/pangolin/Guangxi/P3B/2017|2017-0...   \n",
       "...                                                   ...   \n",
       "514754  Spike|hCoV-19/USA/ME-HETL-J1279/2021|2021-02-0...   \n",
       "514755  Spike|hCoV-19/USA/ME-HETL-J1309/2021|2021-02-0...   \n",
       "514756  Spike|hCoV-19/USA/ME-HETL-J1280/2021|2021-02-0...   \n",
       "514757  Spike|hCoV-19/Australia/QLD1545/2021|2021-02-0...   \n",
       "514758  Spike|hCoV-19/USA/ME-HETL-J1293/2021|2021-02-0...   \n",
       "\n",
       "                                       unaligned_sequence  \\\n",
       "0       MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSSTRGVYYPDKVFRSS...   \n",
       "1       MFVFLFVLPLVSSQCVNLTTRTGIPPGYTNSSTRGVYYPDKVFRSS...   \n",
       "2       MFVFLFVLPLVSSQCVNLTTRTGIQPGYTNSSTRGVYYPDKVFRSS...   \n",
       "3       MFVFLFVLPLVSSQCVNLTTRTGIPPGYTNSSTRGVYYPDKVFRSS...   \n",
       "4       MFVFLFVLPLVSSQCVXXXTRTGIPPGYTNSSTRGVYYPDKVFRSS...   \n",
       "...                                                   ...   \n",
       "514754  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "514755  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "514756  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "514757  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "514758  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYXXKVFRSS...   \n",
       "\n",
       "                           strain virus genbank_accession        date  \\\n",
       "0          bat/Yunnan/RaTG13/2013  ncov                 ?  2013-07-24   \n",
       "1       pangolin/Guangxi/P4L/2017  ncov                 ?        2017   \n",
       "2       pangolin/Guangxi/P1E/2017  ncov                 ?        2017   \n",
       "3       pangolin/Guangxi/P5L/2017  ncov                 ?        2017   \n",
       "4       pangolin/Guangxi/P3B/2017  ncov                 ?        2017   \n",
       "...                           ...   ...               ...         ...   \n",
       "514754     USA/ME-HETL-J1279/2021  ncov                 ?  2021-02-05   \n",
       "514755     USA/ME-HETL-J1309/2021  ncov                 ?  2021-02-05   \n",
       "514756     USA/ME-HETL-J1280/2021  ncov                 ?  2021-02-05   \n",
       "514757     Australia/QLD1545/2021  ncov                 ?  2021-02-05   \n",
       "514758     USA/ME-HETL-J1293/2021  ncov                 ?  2021-02-05   \n",
       "\n",
       "               region    country   division location  ...  \\\n",
       "0                Asia      China     Yunnan    Pu'Er  ...   \n",
       "1                Asia      China    Guangxi      NaN  ...   \n",
       "2                Asia      China    Guangxi      NaN  ...   \n",
       "3                Asia      China    Guangxi      NaN  ...   \n",
       "4                Asia      China    Guangxi      NaN  ...   \n",
       "...               ...        ...        ...      ...  ...   \n",
       "514754  North America        USA      Maine      NaN  ...   \n",
       "514755  North America        USA      Maine      NaN  ...   \n",
       "514756  North America        USA      Maine      NaN  ...   \n",
       "514757        Oceania  Australia  Australia      NaN  ...   \n",
       "514758  North America        USA      Maine      NaN  ...   \n",
       "\n",
       "                       authors                     url title paper_url  \\\n",
       "0                Yan Zhu et al  https://www.gisaid.org     ?         ?   \n",
       "1            Wu-Chun Cao et al  https://www.gisaid.org     ?         ?   \n",
       "2            Wu-Chun Cao et al  https://www.gisaid.org     ?         ?   \n",
       "3            Wu-Chun Cao et al  https://www.gisaid.org     ?         ?   \n",
       "4            Wu-Chun Cao et al  https://www.gisaid.org     ?         ?   \n",
       "...                        ...                     ...   ...       ...   \n",
       "514754            Matluk et al  https://www.gisaid.org     ?         ?   \n",
       "514755            Matluk et al  https://www.gisaid.org     ?         ?   \n",
       "514756            Matluk et al  https://www.gisaid.org     ?         ?   \n",
       "514757  Son Nguyen et al et al  https://www.gisaid.org     ?         ?   \n",
       "514758            Matluk et al  https://www.gisaid.org     ?         ?   \n",
       "\n",
       "        date_submitted purpose_of_sequencing  \\\n",
       "0           2020-01-24                     ?   \n",
       "1           2020-02-17                     ?   \n",
       "2           2020-02-17                     ?   \n",
       "3           2020-02-17                     ?   \n",
       "4           2020-02-17                     ?   \n",
       "...                ...                   ...   \n",
       "514754      2021-02-11                     ?   \n",
       "514755      2021-02-11                     ?   \n",
       "514756      2021-02-11                     ?   \n",
       "514757      2021-02-09                     ?   \n",
       "514758      2021-02-11                     ?   \n",
       "\n",
       "                                              aligned_ref  \\\n",
       "0       MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "1       MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "2       MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "3       MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "4       MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "...                                                   ...   \n",
       "514754  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "514755  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "514756  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "514757  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "514758  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "\n",
       "                                            aligned_query  \\\n",
       "0       MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSSTRGVYYPDKVFRSS...   \n",
       "1       MFVFLFVLPLVSSQCVNLTTRTGIPPGYTNSSTRGVYYPDKVFRSS...   \n",
       "2       MFVFLFVLPLVSSQCVNLTTRTGIQPGYTNSSTRGVYYPDKVFRSS...   \n",
       "3       MFVFLFVLPLVSSQCVNLTTRTGIPPGYTNSSTRGVYYPDKVFRSS...   \n",
       "4       MFVFLFVLPLVSSQCVXXXTRTGIPPGYTNSSTRGVYYPDKVFRSS...   \n",
       "...                                                   ...   \n",
       "514754  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "514755  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "514756  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "514757  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "514758  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYXXKVFRSS...   \n",
       "\n",
       "                                                     muts num_muts  \n",
       "0       [p.F32S, p.S50L, p.T76I, p.Q218P, p.E324D, p.R...       30  \n",
       "1       [p.V6F, p.L7V, p.Q23G, p.L24I, p.A27G, p.F32S,...       94  \n",
       "2       [p.V6F, p.L7V, p.Q23G, p.L24I, p.P25Q, p.A27G,...       94  \n",
       "3       [p.V6F, p.L7V, p.Q23G, p.L24I, p.A27G, p.F32S,...       93  \n",
       "4       [p.V6F, p.L7V, p.Q23G, p.L24I, p.A27G, p.F32S,...       45  \n",
       "...                                                   ...      ...  \n",
       "514754                                 [p.D614G, p.A846V]        2  \n",
       "514755                                 [p.A520S, p.D614G]        2  \n",
       "514756  [p.S255A, p.A263S, p.A264G, p.Y265W, p.Y266T, ...        8  \n",
       "514757  [del.69-70, del.144, p.N501Y, p.A570D, p.D614G...        9  \n",
       "514758                                          [p.D614G]        1  \n",
       "\n",
       "[514759 rows x 33 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "annotated_df = spike_df.merge(pairwise_alignments_df, on=\"unaligned_sequence\")\n",
    "annotated_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sequence_id</th>\n",
       "      <th>unaligned_sequence</th>\n",
       "      <th>strain</th>\n",
       "      <th>virus</th>\n",
       "      <th>genbank_accession</th>\n",
       "      <th>date</th>\n",
       "      <th>region</th>\n",
       "      <th>country</th>\n",
       "      <th>division</th>\n",
       "      <th>location</th>\n",
       "      <th>...</th>\n",
       "      <th>authors</th>\n",
       "      <th>url</th>\n",
       "      <th>title</th>\n",
       "      <th>paper_url</th>\n",
       "      <th>date_submitted</th>\n",
       "      <th>purpose_of_sequencing</th>\n",
       "      <th>aligned_ref</th>\n",
       "      <th>aligned_query</th>\n",
       "      <th>muts</th>\n",
       "      <th>num_muts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Spike|hCoV-19/Wuhan/IPBCAMS-WH-01/2019|2019-12...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>Wuhan/IPBCAMS-WH-01/2019</td>\n",
       "      <td>ncov</td>\n",
       "      <td>MT019529.1</td>\n",
       "      <td>2019-12-24</td>\n",
       "      <td>Asia</td>\n",
       "      <td>China</td>\n",
       "      <td>Hubei</td>\n",
       "      <td>Wuhan</td>\n",
       "      <td>...</td>\n",
       "      <td>Lili Ren et al</td>\n",
       "      <td>https://www.gisaid.org</td>\n",
       "      <td>?</td>\n",
       "      <td>?</td>\n",
       "      <td>2020-01-11</td>\n",
       "      <td>?</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>[]</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Spike|hCoV-19/Wuhan/Hu-1/2019|2019-12-31|EPI_I...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>Wuhan/Hu-1/2019</td>\n",
       "      <td>ncov</td>\n",
       "      <td>MN908947.3</td>\n",
       "      <td>2019-12-26</td>\n",
       "      <td>Asia</td>\n",
       "      <td>China</td>\n",
       "      <td>Hubei</td>\n",
       "      <td>Wuhan</td>\n",
       "      <td>...</td>\n",
       "      <td>Zhang et al</td>\n",
       "      <td>https://www.gisaid.org</td>\n",
       "      <td>A new coronavirus associated with human respir...</td>\n",
       "      <td>https://dx.doi.org/10.1038/s41586-020-2008-3</td>\n",
       "      <td>2020-01-12</td>\n",
       "      <td>?</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>[]</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Spike|hCoV-19/Wuhan/WH01/2019|2019-12-26|EPI_I...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>Wuhan/WH01/2019</td>\n",
       "      <td>ncov</td>\n",
       "      <td>LR757998.1</td>\n",
       "      <td>2019-12-26</td>\n",
       "      <td>Asia</td>\n",
       "      <td>China</td>\n",
       "      <td>Hubei</td>\n",
       "      <td>Wuhan</td>\n",
       "      <td>...</td>\n",
       "      <td>Weijun Chen et al</td>\n",
       "      <td>https://www.gisaid.org</td>\n",
       "      <td>Genomic characterisation and epidemiology of 2...</td>\n",
       "      <td>https://dx.doi.org/10.1016/S0140-6736(20)30251-8</td>\n",
       "      <td>2020-01-30</td>\n",
       "      <td>?</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>[]</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Spike|hCoV-19/Wuhan/IVDC-HB-GX02/2019|2019-12-...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>Wuhan/IVDC-HB-GX02/2019</td>\n",
       "      <td>ncov</td>\n",
       "      <td>?</td>\n",
       "      <td>2019-12-30</td>\n",
       "      <td>Asia</td>\n",
       "      <td>China</td>\n",
       "      <td>Hubei</td>\n",
       "      <td>Wuhan</td>\n",
       "      <td>...</td>\n",
       "      <td>Wenjie Tan et al</td>\n",
       "      <td>https://www.gisaid.org</td>\n",
       "      <td>?</td>\n",
       "      <td>?</td>\n",
       "      <td>2020-04-28</td>\n",
       "      <td>?</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>[]</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Spike|hCoV-19/Wuhan/IVDC-HB-05/2019|2019-12-30...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>Wuhan/IVDC-HB-05/2019</td>\n",
       "      <td>ncov</td>\n",
       "      <td>?</td>\n",
       "      <td>2019-12-30</td>\n",
       "      <td>Asia</td>\n",
       "      <td>China</td>\n",
       "      <td>Hubei</td>\n",
       "      <td>Wuhan</td>\n",
       "      <td>...</td>\n",
       "      <td>Wenjie Tan et al</td>\n",
       "      <td>https://www.gisaid.org</td>\n",
       "      <td>A Novel Coronavirus from Patients with Pneumon...</td>\n",
       "      <td>https://dx.doi.org/10.1056/NEJMoa2001017</td>\n",
       "      <td>2020-01-10</td>\n",
       "      <td>?</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>[]</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>514744</th>\n",
       "      <td>Spike|hCoV-19/USA/ME-HETL-J1296/2021|2021-02-0...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>USA/ME-HETL-J1296/2021</td>\n",
       "      <td>ncov</td>\n",
       "      <td>?</td>\n",
       "      <td>2021-02-04</td>\n",
       "      <td>North America</td>\n",
       "      <td>USA</td>\n",
       "      <td>Maine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Matluk et al</td>\n",
       "      <td>https://www.gisaid.org</td>\n",
       "      <td>?</td>\n",
       "      <td>?</td>\n",
       "      <td>2021-02-11</td>\n",
       "      <td>?</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>[p.L118F, p.N501T, p.D614G]</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>514746</th>\n",
       "      <td>Spike|hCoV-19/USA/ME-HETL-J1295/2021|2021-02-0...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>USA/ME-HETL-J1295/2021</td>\n",
       "      <td>ncov</td>\n",
       "      <td>?</td>\n",
       "      <td>2021-02-04</td>\n",
       "      <td>North America</td>\n",
       "      <td>USA</td>\n",
       "      <td>Maine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Matluk et al</td>\n",
       "      <td>https://www.gisaid.org</td>\n",
       "      <td>?</td>\n",
       "      <td>?</td>\n",
       "      <td>2021-02-11</td>\n",
       "      <td>?</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>[p.D614G, p.F643I, p.D1153Y]</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>514747</th>\n",
       "      <td>Spike|hCoV-19/Belgium/Jessa_55-2105-000762/202...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>Belgium/Jessa_55-2105-000762/2021</td>\n",
       "      <td>ncov</td>\n",
       "      <td>?</td>\n",
       "      <td>2021-02-04</td>\n",
       "      <td>Europe</td>\n",
       "      <td>Belgium</td>\n",
       "      <td>Hasselt</td>\n",
       "      <td>Diepenbeek</td>\n",
       "      <td>...</td>\n",
       "      <td>Jessa_cmdLab et al</td>\n",
       "      <td>https://www.gisaid.org</td>\n",
       "      <td>?</td>\n",
       "      <td>?</td>\n",
       "      <td>2021-02-08</td>\n",
       "      <td>?</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>[p.S98F, p.V143L, p.T208S, p.D614G, p.V1228L]</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>514750</th>\n",
       "      <td>Spike|hCoV-19/Belgium/Jessa_55-2105-001118/202...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>Belgium/Jessa_55-2105-001118/2021</td>\n",
       "      <td>ncov</td>\n",
       "      <td>?</td>\n",
       "      <td>2021-02-04</td>\n",
       "      <td>Europe</td>\n",
       "      <td>Belgium</td>\n",
       "      <td>Hasselt</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Jessa_cmdLab et al</td>\n",
       "      <td>https://www.gisaid.org</td>\n",
       "      <td>?</td>\n",
       "      <td>?</td>\n",
       "      <td>2021-02-09</td>\n",
       "      <td>?</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>[p.M153I, p.A411T, p.L611R, p.A713V, p.K1269N]</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>514752</th>\n",
       "      <td>Spike|hCoV-19/USA/ME-HETL-J1248/2021|2021-02-0...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>USA/ME-HETL-J1248/2021</td>\n",
       "      <td>ncov</td>\n",
       "      <td>?</td>\n",
       "      <td>2021-02-05</td>\n",
       "      <td>North America</td>\n",
       "      <td>USA</td>\n",
       "      <td>Maine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Matluk et al</td>\n",
       "      <td>https://www.gisaid.org</td>\n",
       "      <td>?</td>\n",
       "      <td>?</td>\n",
       "      <td>2021-02-11</td>\n",
       "      <td>?</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>[p.N501T, p.D614G, p.T1116N]</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>430519 rows × 33 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              sequence_id  \\\n",
       "16      Spike|hCoV-19/Wuhan/IPBCAMS-WH-01/2019|2019-12...   \n",
       "17      Spike|hCoV-19/Wuhan/Hu-1/2019|2019-12-31|EPI_I...   \n",
       "18      Spike|hCoV-19/Wuhan/WH01/2019|2019-12-26|EPI_I...   \n",
       "19      Spike|hCoV-19/Wuhan/IVDC-HB-GX02/2019|2019-12-...   \n",
       "20      Spike|hCoV-19/Wuhan/IVDC-HB-05/2019|2019-12-30...   \n",
       "...                                                   ...   \n",
       "514744  Spike|hCoV-19/USA/ME-HETL-J1296/2021|2021-02-0...   \n",
       "514746  Spike|hCoV-19/USA/ME-HETL-J1295/2021|2021-02-0...   \n",
       "514747  Spike|hCoV-19/Belgium/Jessa_55-2105-000762/202...   \n",
       "514750  Spike|hCoV-19/Belgium/Jessa_55-2105-001118/202...   \n",
       "514752  Spike|hCoV-19/USA/ME-HETL-J1248/2021|2021-02-0...   \n",
       "\n",
       "                                       unaligned_sequence  \\\n",
       "16      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "17      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "18      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "19      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "20      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "...                                                   ...   \n",
       "514744  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "514746  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "514747  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "514750  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "514752  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "\n",
       "                                   strain virus genbank_accession        date  \\\n",
       "16               Wuhan/IPBCAMS-WH-01/2019  ncov        MT019529.1  2019-12-24   \n",
       "17                        Wuhan/Hu-1/2019  ncov        MN908947.3  2019-12-26   \n",
       "18                        Wuhan/WH01/2019  ncov        LR757998.1  2019-12-26   \n",
       "19                Wuhan/IVDC-HB-GX02/2019  ncov                 ?  2019-12-30   \n",
       "20                  Wuhan/IVDC-HB-05/2019  ncov                 ?  2019-12-30   \n",
       "...                                   ...   ...               ...         ...   \n",
       "514744             USA/ME-HETL-J1296/2021  ncov                 ?  2021-02-04   \n",
       "514746             USA/ME-HETL-J1295/2021  ncov                 ?  2021-02-04   \n",
       "514747  Belgium/Jessa_55-2105-000762/2021  ncov                 ?  2021-02-04   \n",
       "514750  Belgium/Jessa_55-2105-001118/2021  ncov                 ?  2021-02-04   \n",
       "514752             USA/ME-HETL-J1248/2021  ncov                 ?  2021-02-05   \n",
       "\n",
       "               region  country division    location  ...             authors  \\\n",
       "16               Asia    China    Hubei       Wuhan  ...      Lili Ren et al   \n",
       "17               Asia    China    Hubei       Wuhan  ...         Zhang et al   \n",
       "18               Asia    China    Hubei       Wuhan  ...   Weijun Chen et al   \n",
       "19               Asia    China    Hubei       Wuhan  ...    Wenjie Tan et al   \n",
       "20               Asia    China    Hubei       Wuhan  ...    Wenjie Tan et al   \n",
       "...               ...      ...      ...         ...  ...                 ...   \n",
       "514744  North America      USA    Maine         NaN  ...        Matluk et al   \n",
       "514746  North America      USA    Maine         NaN  ...        Matluk et al   \n",
       "514747         Europe  Belgium  Hasselt  Diepenbeek  ...  Jessa_cmdLab et al   \n",
       "514750         Europe  Belgium  Hasselt         NaN  ...  Jessa_cmdLab et al   \n",
       "514752  North America      USA    Maine         NaN  ...        Matluk et al   \n",
       "\n",
       "                           url  \\\n",
       "16      https://www.gisaid.org   \n",
       "17      https://www.gisaid.org   \n",
       "18      https://www.gisaid.org   \n",
       "19      https://www.gisaid.org   \n",
       "20      https://www.gisaid.org   \n",
       "...                        ...   \n",
       "514744  https://www.gisaid.org   \n",
       "514746  https://www.gisaid.org   \n",
       "514747  https://www.gisaid.org   \n",
       "514750  https://www.gisaid.org   \n",
       "514752  https://www.gisaid.org   \n",
       "\n",
       "                                                    title  \\\n",
       "16                                                      ?   \n",
       "17      A new coronavirus associated with human respir...   \n",
       "18      Genomic characterisation and epidemiology of 2...   \n",
       "19                                                      ?   \n",
       "20      A Novel Coronavirus from Patients with Pneumon...   \n",
       "...                                                   ...   \n",
       "514744                                                  ?   \n",
       "514746                                                  ?   \n",
       "514747                                                  ?   \n",
       "514750                                                  ?   \n",
       "514752                                                  ?   \n",
       "\n",
       "                                               paper_url  date_submitted  \\\n",
       "16                                                     ?      2020-01-11   \n",
       "17          https://dx.doi.org/10.1038/s41586-020-2008-3      2020-01-12   \n",
       "18      https://dx.doi.org/10.1016/S0140-6736(20)30251-8      2020-01-30   \n",
       "19                                                     ?      2020-04-28   \n",
       "20              https://dx.doi.org/10.1056/NEJMoa2001017      2020-01-10   \n",
       "...                                                  ...             ...   \n",
       "514744                                                 ?      2021-02-11   \n",
       "514746                                                 ?      2021-02-11   \n",
       "514747                                                 ?      2021-02-08   \n",
       "514750                                                 ?      2021-02-09   \n",
       "514752                                                 ?      2021-02-11   \n",
       "\n",
       "       purpose_of_sequencing  \\\n",
       "16                         ?   \n",
       "17                         ?   \n",
       "18                         ?   \n",
       "19                         ?   \n",
       "20                         ?   \n",
       "...                      ...   \n",
       "514744                     ?   \n",
       "514746                     ?   \n",
       "514747                     ?   \n",
       "514750                     ?   \n",
       "514752                     ?   \n",
       "\n",
       "                                              aligned_ref  \\\n",
       "16      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "17      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "18      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "19      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "20      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "...                                                   ...   \n",
       "514744  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "514746  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "514747  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "514750  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "514752  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "\n",
       "                                            aligned_query  \\\n",
       "16      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "17      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "18      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "19      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "20      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "...                                                   ...   \n",
       "514744  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "514746  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "514747  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "514750  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "514752  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "\n",
       "                                                  muts num_muts  \n",
       "16                                                  []        0  \n",
       "17                                                  []        0  \n",
       "18                                                  []        0  \n",
       "19                                                  []        0  \n",
       "20                                                  []        0  \n",
       "...                                                ...      ...  \n",
       "514744                     [p.L118F, p.N501T, p.D614G]        3  \n",
       "514746                    [p.D614G, p.F643I, p.D1153Y]        3  \n",
       "514747   [p.S98F, p.V143L, p.T208S, p.D614G, p.V1228L]        5  \n",
       "514750  [p.M153I, p.A411T, p.L611R, p.A713V, p.K1269N]        5  \n",
       "514752                    [p.N501T, p.D614G, p.T1116N]        3  \n",
       "\n",
       "[430519 rows x 33 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "usable_spike_df = annotated_df.loc[\n",
    "    (annotated_df.num_muts < 30) &\n",
    "    (annotated_df.unaligned_sequence.str.count(\"X\") <= 2) &\n",
    "    (annotated_df.unaligned_sequence.str.len() >= 1250)\n",
    "]\n",
    "usable_spike_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unique muts 3576\n",
      "Major muts Index(['p.D614G', 'p.A222V', 'del.69-70', 'p.P681H', 'p.N501Y', 'del.144',\n",
      "       'p.T716I', 'p.A570D', 'p.D1118H', 'p.S982A',\n",
      "       ...\n",
      "       'p.I472L', 'p.E340K', 'p.L179I', 'p.A829S', 'p.V120F', 'p.P330L',\n",
      "       'p.D1163A', 'p.Q580H', 'p.V127A', 'p.D808E'],\n",
      "      dtype='object', length=1135)\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 1135/1135 [01:18<00:00, 14.48it/s]\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sequence_id</th>\n",
       "      <th>unaligned_sequence</th>\n",
       "      <th>strain</th>\n",
       "      <th>virus</th>\n",
       "      <th>genbank_accession</th>\n",
       "      <th>date</th>\n",
       "      <th>region</th>\n",
       "      <th>country</th>\n",
       "      <th>division</th>\n",
       "      <th>location</th>\n",
       "      <th>...</th>\n",
       "      <th>authors</th>\n",
       "      <th>url</th>\n",
       "      <th>title</th>\n",
       "      <th>paper_url</th>\n",
       "      <th>date_submitted</th>\n",
       "      <th>purpose_of_sequencing</th>\n",
       "      <th>aligned_ref</th>\n",
       "      <th>aligned_query</th>\n",
       "      <th>muts</th>\n",
       "      <th>num_muts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Spike|hCoV-19/Wuhan/IPBCAMS-WH-01/2019|2019-12...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>Wuhan/IPBCAMS-WH-01/2019</td>\n",
       "      <td>ncov</td>\n",
       "      <td>MT019529.1</td>\n",
       "      <td>2019-12-24</td>\n",
       "      <td>Asia</td>\n",
       "      <td>China</td>\n",
       "      <td>Hubei</td>\n",
       "      <td>Wuhan</td>\n",
       "      <td>...</td>\n",
       "      <td>Lili Ren et al</td>\n",
       "      <td>https://www.gisaid.org</td>\n",
       "      <td>?</td>\n",
       "      <td>?</td>\n",
       "      <td>2020-01-11</td>\n",
       "      <td>?</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>[]</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Spike|hCoV-19/Wuhan/Hu-1/2019|2019-12-31|EPI_I...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>Wuhan/Hu-1/2019</td>\n",
       "      <td>ncov</td>\n",
       "      <td>MN908947.3</td>\n",
       "      <td>2019-12-26</td>\n",
       "      <td>Asia</td>\n",
       "      <td>China</td>\n",
       "      <td>Hubei</td>\n",
       "      <td>Wuhan</td>\n",
       "      <td>...</td>\n",
       "      <td>Zhang et al</td>\n",
       "      <td>https://www.gisaid.org</td>\n",
       "      <td>A new coronavirus associated with human respir...</td>\n",
       "      <td>https://dx.doi.org/10.1038/s41586-020-2008-3</td>\n",
       "      <td>2020-01-12</td>\n",
       "      <td>?</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>[]</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Spike|hCoV-19/Wuhan/WH01/2019|2019-12-26|EPI_I...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>Wuhan/WH01/2019</td>\n",
       "      <td>ncov</td>\n",
       "      <td>LR757998.1</td>\n",
       "      <td>2019-12-26</td>\n",
       "      <td>Asia</td>\n",
       "      <td>China</td>\n",
       "      <td>Hubei</td>\n",
       "      <td>Wuhan</td>\n",
       "      <td>...</td>\n",
       "      <td>Weijun Chen et al</td>\n",
       "      <td>https://www.gisaid.org</td>\n",
       "      <td>Genomic characterisation and epidemiology of 2...</td>\n",
       "      <td>https://dx.doi.org/10.1016/S0140-6736(20)30251-8</td>\n",
       "      <td>2020-01-30</td>\n",
       "      <td>?</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>[]</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Spike|hCoV-19/Wuhan/IVDC-HB-GX02/2019|2019-12-...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>Wuhan/IVDC-HB-GX02/2019</td>\n",
       "      <td>ncov</td>\n",
       "      <td>?</td>\n",
       "      <td>2019-12-30</td>\n",
       "      <td>Asia</td>\n",
       "      <td>China</td>\n",
       "      <td>Hubei</td>\n",
       "      <td>Wuhan</td>\n",
       "      <td>...</td>\n",
       "      <td>Wenjie Tan et al</td>\n",
       "      <td>https://www.gisaid.org</td>\n",
       "      <td>?</td>\n",
       "      <td>?</td>\n",
       "      <td>2020-04-28</td>\n",
       "      <td>?</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>[]</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Spike|hCoV-19/Wuhan/IVDC-HB-05/2019|2019-12-30...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>Wuhan/IVDC-HB-05/2019</td>\n",
       "      <td>ncov</td>\n",
       "      <td>?</td>\n",
       "      <td>2019-12-30</td>\n",
       "      <td>Asia</td>\n",
       "      <td>China</td>\n",
       "      <td>Hubei</td>\n",
       "      <td>Wuhan</td>\n",
       "      <td>...</td>\n",
       "      <td>Wenjie Tan et al</td>\n",
       "      <td>https://www.gisaid.org</td>\n",
       "      <td>A Novel Coronavirus from Patients with Pneumon...</td>\n",
       "      <td>https://dx.doi.org/10.1056/NEJMoa2001017</td>\n",
       "      <td>2020-01-10</td>\n",
       "      <td>?</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>[]</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>514744</th>\n",
       "      <td>Spike|hCoV-19/USA/ME-HETL-J1296/2021|2021-02-0...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>USA/ME-HETL-J1296/2021</td>\n",
       "      <td>ncov</td>\n",
       "      <td>?</td>\n",
       "      <td>2021-02-04</td>\n",
       "      <td>North America</td>\n",
       "      <td>USA</td>\n",
       "      <td>Maine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Matluk et al</td>\n",
       "      <td>https://www.gisaid.org</td>\n",
       "      <td>?</td>\n",
       "      <td>?</td>\n",
       "      <td>2021-02-11</td>\n",
       "      <td>?</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>[p.L118F, p.N501T, p.D614G]</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>514746</th>\n",
       "      <td>Spike|hCoV-19/USA/ME-HETL-J1295/2021|2021-02-0...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>USA/ME-HETL-J1295/2021</td>\n",
       "      <td>ncov</td>\n",
       "      <td>?</td>\n",
       "      <td>2021-02-04</td>\n",
       "      <td>North America</td>\n",
       "      <td>USA</td>\n",
       "      <td>Maine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Matluk et al</td>\n",
       "      <td>https://www.gisaid.org</td>\n",
       "      <td>?</td>\n",
       "      <td>?</td>\n",
       "      <td>2021-02-11</td>\n",
       "      <td>?</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>[p.D614G, p.F643I, p.D1153Y]</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>514747</th>\n",
       "      <td>Spike|hCoV-19/Belgium/Jessa_55-2105-000762/202...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>Belgium/Jessa_55-2105-000762/2021</td>\n",
       "      <td>ncov</td>\n",
       "      <td>?</td>\n",
       "      <td>2021-02-04</td>\n",
       "      <td>Europe</td>\n",
       "      <td>Belgium</td>\n",
       "      <td>Hasselt</td>\n",
       "      <td>Diepenbeek</td>\n",
       "      <td>...</td>\n",
       "      <td>Jessa_cmdLab et al</td>\n",
       "      <td>https://www.gisaid.org</td>\n",
       "      <td>?</td>\n",
       "      <td>?</td>\n",
       "      <td>2021-02-08</td>\n",
       "      <td>?</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>[p.S98F, p.V143L, p.T208S, p.D614G, p.V1228L]</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>514750</th>\n",
       "      <td>Spike|hCoV-19/Belgium/Jessa_55-2105-001118/202...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>Belgium/Jessa_55-2105-001118/2021</td>\n",
       "      <td>ncov</td>\n",
       "      <td>?</td>\n",
       "      <td>2021-02-04</td>\n",
       "      <td>Europe</td>\n",
       "      <td>Belgium</td>\n",
       "      <td>Hasselt</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Jessa_cmdLab et al</td>\n",
       "      <td>https://www.gisaid.org</td>\n",
       "      <td>?</td>\n",
       "      <td>?</td>\n",
       "      <td>2021-02-09</td>\n",
       "      <td>?</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>[p.M153I, p.A411T, p.L611R, p.A713V, p.K1269N]</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>514752</th>\n",
       "      <td>Spike|hCoV-19/USA/ME-HETL-J1248/2021|2021-02-0...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>USA/ME-HETL-J1248/2021</td>\n",
       "      <td>ncov</td>\n",
       "      <td>?</td>\n",
       "      <td>2021-02-05</td>\n",
       "      <td>North America</td>\n",
       "      <td>USA</td>\n",
       "      <td>Maine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Matluk et al</td>\n",
       "      <td>https://www.gisaid.org</td>\n",
       "      <td>?</td>\n",
       "      <td>?</td>\n",
       "      <td>2021-02-11</td>\n",
       "      <td>?</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...</td>\n",
       "      <td>[p.N501T, p.D614G, p.T1116N]</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>430519 rows × 33 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              sequence_id  \\\n",
       "16      Spike|hCoV-19/Wuhan/IPBCAMS-WH-01/2019|2019-12...   \n",
       "17      Spike|hCoV-19/Wuhan/Hu-1/2019|2019-12-31|EPI_I...   \n",
       "18      Spike|hCoV-19/Wuhan/WH01/2019|2019-12-26|EPI_I...   \n",
       "19      Spike|hCoV-19/Wuhan/IVDC-HB-GX02/2019|2019-12-...   \n",
       "20      Spike|hCoV-19/Wuhan/IVDC-HB-05/2019|2019-12-30...   \n",
       "...                                                   ...   \n",
       "514744  Spike|hCoV-19/USA/ME-HETL-J1296/2021|2021-02-0...   \n",
       "514746  Spike|hCoV-19/USA/ME-HETL-J1295/2021|2021-02-0...   \n",
       "514747  Spike|hCoV-19/Belgium/Jessa_55-2105-000762/202...   \n",
       "514750  Spike|hCoV-19/Belgium/Jessa_55-2105-001118/202...   \n",
       "514752  Spike|hCoV-19/USA/ME-HETL-J1248/2021|2021-02-0...   \n",
       "\n",
       "                                       unaligned_sequence  \\\n",
       "16      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "17      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "18      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "19      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "20      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "...                                                   ...   \n",
       "514744  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "514746  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "514747  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "514750  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "514752  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "\n",
       "                                   strain virus genbank_accession        date  \\\n",
       "16               Wuhan/IPBCAMS-WH-01/2019  ncov        MT019529.1  2019-12-24   \n",
       "17                        Wuhan/Hu-1/2019  ncov        MN908947.3  2019-12-26   \n",
       "18                        Wuhan/WH01/2019  ncov        LR757998.1  2019-12-26   \n",
       "19                Wuhan/IVDC-HB-GX02/2019  ncov                 ?  2019-12-30   \n",
       "20                  Wuhan/IVDC-HB-05/2019  ncov                 ?  2019-12-30   \n",
       "...                                   ...   ...               ...         ...   \n",
       "514744             USA/ME-HETL-J1296/2021  ncov                 ?  2021-02-04   \n",
       "514746             USA/ME-HETL-J1295/2021  ncov                 ?  2021-02-04   \n",
       "514747  Belgium/Jessa_55-2105-000762/2021  ncov                 ?  2021-02-04   \n",
       "514750  Belgium/Jessa_55-2105-001118/2021  ncov                 ?  2021-02-04   \n",
       "514752             USA/ME-HETL-J1248/2021  ncov                 ?  2021-02-05   \n",
       "\n",
       "               region  country division    location  ...             authors  \\\n",
       "16               Asia    China    Hubei       Wuhan  ...      Lili Ren et al   \n",
       "17               Asia    China    Hubei       Wuhan  ...         Zhang et al   \n",
       "18               Asia    China    Hubei       Wuhan  ...   Weijun Chen et al   \n",
       "19               Asia    China    Hubei       Wuhan  ...    Wenjie Tan et al   \n",
       "20               Asia    China    Hubei       Wuhan  ...    Wenjie Tan et al   \n",
       "...               ...      ...      ...         ...  ...                 ...   \n",
       "514744  North America      USA    Maine         NaN  ...        Matluk et al   \n",
       "514746  North America      USA    Maine         NaN  ...        Matluk et al   \n",
       "514747         Europe  Belgium  Hasselt  Diepenbeek  ...  Jessa_cmdLab et al   \n",
       "514750         Europe  Belgium  Hasselt         NaN  ...  Jessa_cmdLab et al   \n",
       "514752  North America      USA    Maine         NaN  ...        Matluk et al   \n",
       "\n",
       "                           url  \\\n",
       "16      https://www.gisaid.org   \n",
       "17      https://www.gisaid.org   \n",
       "18      https://www.gisaid.org   \n",
       "19      https://www.gisaid.org   \n",
       "20      https://www.gisaid.org   \n",
       "...                        ...   \n",
       "514744  https://www.gisaid.org   \n",
       "514746  https://www.gisaid.org   \n",
       "514747  https://www.gisaid.org   \n",
       "514750  https://www.gisaid.org   \n",
       "514752  https://www.gisaid.org   \n",
       "\n",
       "                                                    title  \\\n",
       "16                                                      ?   \n",
       "17      A new coronavirus associated with human respir...   \n",
       "18      Genomic characterisation and epidemiology of 2...   \n",
       "19                                                      ?   \n",
       "20      A Novel Coronavirus from Patients with Pneumon...   \n",
       "...                                                   ...   \n",
       "514744                                                  ?   \n",
       "514746                                                  ?   \n",
       "514747                                                  ?   \n",
       "514750                                                  ?   \n",
       "514752                                                  ?   \n",
       "\n",
       "                                               paper_url  date_submitted  \\\n",
       "16                                                     ?      2020-01-11   \n",
       "17          https://dx.doi.org/10.1038/s41586-020-2008-3      2020-01-12   \n",
       "18      https://dx.doi.org/10.1016/S0140-6736(20)30251-8      2020-01-30   \n",
       "19                                                     ?      2020-04-28   \n",
       "20              https://dx.doi.org/10.1056/NEJMoa2001017      2020-01-10   \n",
       "...                                                  ...             ...   \n",
       "514744                                                 ?      2021-02-11   \n",
       "514746                                                 ?      2021-02-11   \n",
       "514747                                                 ?      2021-02-08   \n",
       "514750                                                 ?      2021-02-09   \n",
       "514752                                                 ?      2021-02-11   \n",
       "\n",
       "       purpose_of_sequencing  \\\n",
       "16                         ?   \n",
       "17                         ?   \n",
       "18                         ?   \n",
       "19                         ?   \n",
       "20                         ?   \n",
       "...                      ...   \n",
       "514744                     ?   \n",
       "514746                     ?   \n",
       "514747                     ?   \n",
       "514750                     ?   \n",
       "514752                     ?   \n",
       "\n",
       "                                              aligned_ref  \\\n",
       "16      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "17      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "18      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "19      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "20      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "...                                                   ...   \n",
       "514744  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "514746  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "514747  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "514750  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "514752  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "\n",
       "                                            aligned_query  \\\n",
       "16      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "17      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "18      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "19      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "20      MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "...                                                   ...   \n",
       "514744  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "514746  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "514747  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "514750  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "514752  MFVFLVLLPLVSSQCVNLTTRTQLPPAYTNSFTRGVYYPDKVFRSS...   \n",
       "\n",
       "                                                  muts num_muts  \n",
       "16                                                  []        0  \n",
       "17                                                  []        0  \n",
       "18                                                  []        0  \n",
       "19                                                  []        0  \n",
       "20                                                  []        0  \n",
       "...                                                ...      ...  \n",
       "514744                     [p.L118F, p.N501T, p.D614G]        3  \n",
       "514746                    [p.D614G, p.F643I, p.D1153Y]        3  \n",
       "514747   [p.S98F, p.V143L, p.T208S, p.D614G, p.V1228L]        5  \n",
       "514750  [p.M153I, p.A411T, p.L611R, p.A713V, p.K1269N]        5  \n",
       "514752                    [p.N501T, p.D614G, p.T1116N]        3  \n",
       "\n",
       "[430519 rows x 33 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import tqdm\n",
    "import collections\n",
    "mut_counts = collections.Counter()\n",
    "for muts in usable_spike_df[\"muts\"]:\n",
    "    mut_counts.update(muts)\n",
    "print(\"Unique muts\", len(mut_counts))\n",
    "mut_counts = pandas.Series(mut_counts).sort_values(ascending=False)\n",
    "\n",
    "major_muts = mut_counts[mut_counts >= 10].index\n",
    "print(\"Major muts\", major_muts)\n",
    "\n",
    "mut_df = pandas.DataFrame(index=usable_spike_df.index, columns=major_muts, dtype=bool)\n",
    "for mut in tqdm.tqdm(major_muts):\n",
    "    mut_df[mut] = usable_spike_df.muts.map(lambda muts: mut in muts)\n",
    "\n",
    "usable_spike_df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>lineage</th>\n",
       "      <th>sequences</th>\n",
       "      <th>mut</th>\n",
       "      <th>rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A.10</td>\n",
       "      <td>3</td>\n",
       "      <td>p.D614G</td>\n",
       "      <td>1.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A.11</td>\n",
       "      <td>5</td>\n",
       "      <td>p.L5F</td>\n",
       "      <td>0.200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A.11</td>\n",
       "      <td>5</td>\n",
       "      <td>p.V1122L</td>\n",
       "      <td>0.200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A.12</td>\n",
       "      <td>5</td>\n",
       "      <td>p.E1144Q</td>\n",
       "      <td>0.200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A.12</td>\n",
       "      <td>5</td>\n",
       "      <td>p.D614N</td>\n",
       "      <td>0.200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1589</th>\n",
       "      <td>R.2</td>\n",
       "      <td>8</td>\n",
       "      <td>p.Q677H</td>\n",
       "      <td>1.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1590</th>\n",
       "      <td>R.2</td>\n",
       "      <td>8</td>\n",
       "      <td>p.E484K</td>\n",
       "      <td>1.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1591</th>\n",
       "      <td>R.2</td>\n",
       "      <td>8</td>\n",
       "      <td>p.E1202Q</td>\n",
       "      <td>1.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1592</th>\n",
       "      <td>R.2</td>\n",
       "      <td>8</td>\n",
       "      <td>p.T732S</td>\n",
       "      <td>1.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1593</th>\n",
       "      <td>R.2</td>\n",
       "      <td>8</td>\n",
       "      <td>p.T549A</td>\n",
       "      <td>0.125</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1594 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     lineage  sequences       mut   rate\n",
       "0       A.10          3   p.D614G  1.000\n",
       "1       A.11          5     p.L5F  0.200\n",
       "2       A.11          5  p.V1122L  0.200\n",
       "3       A.12          5  p.E1144Q  0.200\n",
       "4       A.12          5   p.D614N  0.200\n",
       "...      ...        ...       ...    ...\n",
       "1589     R.2          8   p.Q677H  1.000\n",
       "1590     R.2          8   p.E484K  1.000\n",
       "1591     R.2          8  p.E1202Q  1.000\n",
       "1592     R.2          8   p.T732S  1.000\n",
       "1593     R.2          8   p.T549A  0.125\n",
       "\n",
       "[1594 rows x 4 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lineage_muts = []\n",
    "for lineage, sub_df in usable_spike_df.groupby(\"pangolin_lineage\"):\n",
    "    muts_matrix = mut_df.loc[sub_df.index]\n",
    "    means = muts_matrix.mean()\n",
    "    selected_muts = muts_matrix.columns[means > 0.1].values\n",
    "    for item in selected_muts:\n",
    "        lineage_muts.append((lineage, len(sub_df), item, means[item]))\n",
    "\n",
    "lineage_muts = pandas.DataFrame(lineage_muts, columns=[\"lineage\", \"sequences\", \"mut\", \"rate\"])\n",
    "lineage_muts\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>muts_80</th>\n",
       "      <th>muts_50</th>\n",
       "      <th>muts_10</th>\n",
       "      <th>num_sequences_for_lineage</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>lineage</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>B.1.177</th>\n",
       "      <td>[p.D614G, p.A222V]</td>\n",
       "      <td>[p.D614G, p.A222V, p.L18F]</td>\n",
       "      <td>[p.D614G, p.A222V, p.L18F]</td>\n",
       "      <td>65548</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B.1.1.7</th>\n",
       "      <td>[p.D614G, del.69-70, p.P681H, p.N501Y, del.144...</td>\n",
       "      <td>[p.D614G, del.69-70, p.P681H, p.N501Y, del.144...</td>\n",
       "      <td>[p.D614G, del.69-70, p.P681H, p.N501Y, del.144...</td>\n",
       "      <td>60070</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B.1</th>\n",
       "      <td>[p.D614G]</td>\n",
       "      <td>[p.D614G]</td>\n",
       "      <td>[p.D614G]</td>\n",
       "      <td>38315</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B.1.2</th>\n",
       "      <td>[p.D614G]</td>\n",
       "      <td>[p.D614G]</td>\n",
       "      <td>[p.D614G]</td>\n",
       "      <td>19785</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B.1.1.29</th>\n",
       "      <td>[p.D614G]</td>\n",
       "      <td>[p.D614G]</td>\n",
       "      <td>[p.D614G]</td>\n",
       "      <td>15525</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B.1.464</th>\n",
       "      <td>[p.D614G]</td>\n",
       "      <td>[p.D614G]</td>\n",
       "      <td>[p.D614G]</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A.10</th>\n",
       "      <td>[p.D614G]</td>\n",
       "      <td>[p.D614G]</td>\n",
       "      <td>[p.D614G]</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B.1.476</th>\n",
       "      <td>[p.D614G, p.S477N]</td>\n",
       "      <td>[p.D614G, p.S477N]</td>\n",
       "      <td>[p.D614G, p.S477N]</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B.1.1.206</th>\n",
       "      <td>[p.D614G]</td>\n",
       "      <td>[p.D614G]</td>\n",
       "      <td>[p.D614G]</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B.1.285</th>\n",
       "      <td>[p.D614G]</td>\n",
       "      <td>[p.D614G]</td>\n",
       "      <td>[p.D614G]</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>821 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                     muts_80  \\\n",
       "lineage                                                        \n",
       "B.1.177                                   [p.D614G, p.A222V]   \n",
       "B.1.1.7    [p.D614G, del.69-70, p.P681H, p.N501Y, del.144...   \n",
       "B.1                                                [p.D614G]   \n",
       "B.1.2                                              [p.D614G]   \n",
       "B.1.1.29                                           [p.D614G]   \n",
       "...                                                      ...   \n",
       "B.1.464                                            [p.D614G]   \n",
       "A.10                                               [p.D614G]   \n",
       "B.1.476                                   [p.D614G, p.S477N]   \n",
       "B.1.1.206                                          [p.D614G]   \n",
       "B.1.285                                            [p.D614G]   \n",
       "\n",
       "                                                     muts_50  \\\n",
       "lineage                                                        \n",
       "B.1.177                           [p.D614G, p.A222V, p.L18F]   \n",
       "B.1.1.7    [p.D614G, del.69-70, p.P681H, p.N501Y, del.144...   \n",
       "B.1                                                [p.D614G]   \n",
       "B.1.2                                              [p.D614G]   \n",
       "B.1.1.29                                           [p.D614G]   \n",
       "...                                                      ...   \n",
       "B.1.464                                            [p.D614G]   \n",
       "A.10                                               [p.D614G]   \n",
       "B.1.476                                   [p.D614G, p.S477N]   \n",
       "B.1.1.206                                          [p.D614G]   \n",
       "B.1.285                                            [p.D614G]   \n",
       "\n",
       "                                                     muts_10  \\\n",
       "lineage                                                        \n",
       "B.1.177                           [p.D614G, p.A222V, p.L18F]   \n",
       "B.1.1.7    [p.D614G, del.69-70, p.P681H, p.N501Y, del.144...   \n",
       "B.1                                                [p.D614G]   \n",
       "B.1.2                                              [p.D614G]   \n",
       "B.1.1.29                                           [p.D614G]   \n",
       "...                                                      ...   \n",
       "B.1.464                                            [p.D614G]   \n",
       "A.10                                               [p.D614G]   \n",
       "B.1.476                                   [p.D614G, p.S477N]   \n",
       "B.1.1.206                                          [p.D614G]   \n",
       "B.1.285                                            [p.D614G]   \n",
       "\n",
       "           num_sequences_for_lineage  \n",
       "lineage                               \n",
       "B.1.177                        65548  \n",
       "B.1.1.7                        60070  \n",
       "B.1                            38315  \n",
       "B.1.2                          19785  \n",
       "B.1.1.29                       15525  \n",
       "...                              ...  \n",
       "B.1.464                            3  \n",
       "A.10                               3  \n",
       "B.1.476                            2  \n",
       "B.1.1.206                          1  \n",
       "B.1.285                            1  \n",
       "\n",
       "[821 rows x 4 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lineage_muts_grouped = lineage_muts.groupby(\"lineage\").mut.unique().to_frame()\n",
    "lineage_muts_grouped[\"muts_80\"] = lineage_muts.loc[lineage_muts.rate >= 0.8].groupby(\"lineage\").mut.unique()\n",
    "lineage_muts_grouped[\"muts_50\"] = lineage_muts.loc[lineage_muts.rate >= 0.5].groupby(\"lineage\").mut.unique()\n",
    "lineage_muts_grouped[\"muts_10\"] = lineage_muts_grouped.mut\n",
    "del lineage_muts_grouped[\"mut\"]\n",
    "\n",
    "lineage_muts_grouped[\"num_sequences_for_lineage\"] = lineage_muts_grouped.index.map(\n",
    "    usable_spike_df.pangolin_lineage.value_counts())\n",
    "lineage_muts_grouped = lineage_muts_grouped.sort_values(\"num_sequences_for_lineage\", ascending=False)\n",
    "lineage_muts_grouped"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>num_sequences_for_lineage</th>\n",
       "      <th>mutations &gt;= 80%</th>\n",
       "      <th>mutations &gt;= 50%</th>\n",
       "      <th>mutations &gt;= 10%</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>lineage</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>B.1.177</th>\n",
       "      <td>65548</td>\n",
       "      <td>p.A222V p.D614G</td>\n",
       "      <td>p.L18F p.A222V p.D614G</td>\n",
       "      <td>p.L18F p.A222V p.D614G</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B.1.1.7</th>\n",
       "      <td>60070</td>\n",
       "      <td>del.69-70 del.144 p.N501Y p.A570D p.D614G p.P6...</td>\n",
       "      <td>del.69-70 del.144 p.N501Y p.A570D p.D614G p.P6...</td>\n",
       "      <td>del.69-70 del.144 p.N501Y p.A570D p.D614G p.P6...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B.1</th>\n",
       "      <td>38315</td>\n",
       "      <td>p.D614G</td>\n",
       "      <td>p.D614G</td>\n",
       "      <td>p.D614G</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B.1.2</th>\n",
       "      <td>19785</td>\n",
       "      <td>p.D614G</td>\n",
       "      <td>p.D614G</td>\n",
       "      <td>p.D614G</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B.1.1.29</th>\n",
       "      <td>15525</td>\n",
       "      <td>p.D614G</td>\n",
       "      <td>p.D614G</td>\n",
       "      <td>p.D614G</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B.1.1.123</th>\n",
       "      <td>50</td>\n",
       "      <td>p.D614G p.G769V</td>\n",
       "      <td>p.D614G p.G769V</td>\n",
       "      <td>p.D614G p.G769V</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B.1.36.26</th>\n",
       "      <td>50</td>\n",
       "      <td>p.L54F p.D614G</td>\n",
       "      <td>p.L54F p.D614G</td>\n",
       "      <td>p.L54F p.D80Y p.F157L p.D614G</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B.1.1.118</th>\n",
       "      <td>50</td>\n",
       "      <td>p.D614G</td>\n",
       "      <td>p.D614G p.S640F</td>\n",
       "      <td>p.D614G p.S640F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B.1.169</th>\n",
       "      <td>50</td>\n",
       "      <td>p.D614G</td>\n",
       "      <td>p.D614G</td>\n",
       "      <td>p.T604I p.D614G</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B.1.1.158</th>\n",
       "      <td>50</td>\n",
       "      <td>p.D614G</td>\n",
       "      <td>p.D614G</td>\n",
       "      <td>p.D614G</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>482 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           num_sequences_for_lineage  \\\n",
       "lineage                                \n",
       "B.1.177                        65548   \n",
       "B.1.1.7                        60070   \n",
       "B.1                            38315   \n",
       "B.1.2                          19785   \n",
       "B.1.1.29                       15525   \n",
       "...                              ...   \n",
       "B.1.1.123                         50   \n",
       "B.1.36.26                         50   \n",
       "B.1.1.118                         50   \n",
       "B.1.169                           50   \n",
       "B.1.1.158                         50   \n",
       "\n",
       "                                            mutations >= 80%  \\\n",
       "lineage                                                        \n",
       "B.1.177                                      p.A222V p.D614G   \n",
       "B.1.1.7    del.69-70 del.144 p.N501Y p.A570D p.D614G p.P6...   \n",
       "B.1                                                  p.D614G   \n",
       "B.1.2                                                p.D614G   \n",
       "B.1.1.29                                             p.D614G   \n",
       "...                                                      ...   \n",
       "B.1.1.123                                    p.D614G p.G769V   \n",
       "B.1.36.26                                     p.L54F p.D614G   \n",
       "B.1.1.118                                            p.D614G   \n",
       "B.1.169                                              p.D614G   \n",
       "B.1.1.158                                            p.D614G   \n",
       "\n",
       "                                            mutations >= 50%  \\\n",
       "lineage                                                        \n",
       "B.1.177                               p.L18F p.A222V p.D614G   \n",
       "B.1.1.7    del.69-70 del.144 p.N501Y p.A570D p.D614G p.P6...   \n",
       "B.1                                                  p.D614G   \n",
       "B.1.2                                                p.D614G   \n",
       "B.1.1.29                                             p.D614G   \n",
       "...                                                      ...   \n",
       "B.1.1.123                                    p.D614G p.G769V   \n",
       "B.1.36.26                                     p.L54F p.D614G   \n",
       "B.1.1.118                                    p.D614G p.S640F   \n",
       "B.1.169                                              p.D614G   \n",
       "B.1.1.158                                            p.D614G   \n",
       "\n",
       "                                            mutations >= 10%  \n",
       "lineage                                                       \n",
       "B.1.177                               p.L18F p.A222V p.D614G  \n",
       "B.1.1.7    del.69-70 del.144 p.N501Y p.A570D p.D614G p.P6...  \n",
       "B.1                                                  p.D614G  \n",
       "B.1.2                                                p.D614G  \n",
       "B.1.1.29                                             p.D614G  \n",
       "...                                                      ...  \n",
       "B.1.1.123                                    p.D614G p.G769V  \n",
       "B.1.36.26                      p.L54F p.D80Y p.F157L p.D614G  \n",
       "B.1.1.118                                    p.D614G p.S640F  \n",
       "B.1.169                                      p.T604I p.D614G  \n",
       "B.1.1.158                                            p.D614G  \n",
       "\n",
       "[482 rows x 4 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "write_df = lineage_muts_grouped.loc[lineage_muts_grouped.num_sequences_for_lineage >= 50].copy()\n",
    "\n",
    "def format(muts):\n",
    "    if len(muts) == 0:\n",
    "        return \"\"\n",
    "    return \" \".join(sorted(muts, key=lambda s: int(re.search(\"([0-9]+)\", s).groups()[0])))\n",
    "\n",
    "write_df[\"mutations >= 80%\"] = write_df.muts_80.fillna(\"\").map(format)\n",
    "write_df[\"mutations >= 50%\"] = write_df.muts_50.fillna(\"\").map(format)\n",
    "write_df[\"mutations >= 10%\"] = write_df.muts_10.fillna(\"\").map(format)\n",
    "\n",
    "\n",
    "del write_df['muts_10']\n",
    "del write_df['muts_50']\n",
    "del write_df['muts_80']\n",
    "\n",
    "write_df.to_csv(\"muts_by_lineage.csv\", index=True)\n",
    "write_df.to_excel(\"muts_by_lineage.xlsx\", index=True)\n",
    "\n",
    "write_df"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}