orthogroups_table_counts.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "2bcdd678",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "1117153d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# orthogroup data\n",
    "df = pd.read_csv(\"Orthogroups.tsv\", sep=\"\\t\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "f4309912",
   "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>Orthogroup</th>\n",
       "      <th>MEGAHIT-AR1_L2.36</th>\n",
       "      <th>MEGAHIT-AR2_L2.57</th>\n",
       "      <th>MEGAHIT-AR3_L2.6</th>\n",
       "      <th>MEGAHIT-AR4_L2.41</th>\n",
       "      <th>acidihalobacter_aeolianus</th>\n",
       "      <th>acidihalobacter_ferrooxydans</th>\n",
       "      <th>acidihalobacter_prosperus</th>\n",
       "      <th>acidihalobacter_yilgarnensis</th>\n",
       "      <th>acidithiobacillus_ferrooxidans</th>\n",
       "      <th>acidithiobacillus_thiooxidans</th>\n",
       "      <th>burkholderia_pseudomallei</th>\n",
       "      <th>e_coli</th>\n",
       "      <th>l_ferriphilum.gbk</th>\n",
       "      <th>leptospirillum_ferriphilum</th>\n",
       "      <th>leptospirillum_ferrooxidans</th>\n",
       "      <th>pseudomonas_aeruginosa_pao1</th>\n",
       "      <th>s_maltophilia</th>\n",
       "      <th>shewanella_oneidensis</th>\n",
       "      <th>stenotrophomonas_maltophilia</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>OG0000000</td>\n",
       "      <td>IEKBFFFE_00044, IEKBFFFE_00702, IEKBFFFE_00771...</td>\n",
       "      <td>OIDPMNJM_00058, OIDPMNJM_00059, OIDPMNJM_00104...</td>\n",
       "      <td>LNLMDAFO_00406, LNLMDAFO_00464, LNLMDAFO_00677...</td>\n",
       "      <td>KPCADNOF_00244</td>\n",
       "      <td>AOV15813.1, AOV15885.1, AOV15951.1, AOV15984.1...</td>\n",
       "      <td>APZ41780.1, APZ42158.1, APZ42214.1, APZ43198.1...</td>\n",
       "      <td>OBS08017.1, OBS08315.1, OBS08422.1, OBS08463.1...</td>\n",
       "      <td>AOU96933.1, AOU97077.1, AOU97078.1, AOU97114.1...</td>\n",
       "      <td>WP_012536716.1, WP_113526628.1</td>\n",
       "      <td>WP_010639275.1, WP_010639739.1, WP_010640967.1...</td>\n",
       "      <td>WP_004185903.1, WP_004190283.1, WP_004528082.1...</td>\n",
       "      <td>WP_000043761.1, WP_000859945.1, WP_000969032.1...</td>\n",
       "      <td>LFTS_00405, LFTS_00479, LFTS_00501, LFTS_00575...</td>\n",
       "      <td>WP_023525729.1, WP_036081380.1, WP_036081433.1...</td>\n",
       "      <td>WP_014448254.1, WP_014448371.1, WP_014448407.1...</td>\n",
       "      <td>NP_248976.1, NP_249266.1, NP_249538.1, NP_2495...</td>\n",
       "      <td>WP_005408197.1, WP_005409582.1, WP_005410383.1...</td>\n",
       "      <td>WP_011070544.1, WP_011070706.1, WP_011070785.1...</td>\n",
       "      <td>WP_005408197.1, WP_005409582.1, WP_005410383.1...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>OG0000001</td>\n",
       "      <td>IEKBFFFE_00199, IEKBFFFE_00468, IEKBFFFE_01147...</td>\n",
       "      <td>OIDPMNJM_00796, OIDPMNJM_00840, OIDPMNJM_01332...</td>\n",
       "      <td>LNLMDAFO_00299, LNLMDAFO_00569, LNLMDAFO_00571...</td>\n",
       "      <td>KPCADNOF_00032, KPCADNOF_00055, KPCADNOF_00119...</td>\n",
       "      <td>AOV15752.1, AOV15864.1, AOV16627.1, AOV16629.1...</td>\n",
       "      <td>APZ41721.1, APZ41924.1, APZ41950.1, APZ42977.1...</td>\n",
       "      <td>OBS08105.1, OBS08243.1, OBS09011.1, OBS09039.1...</td>\n",
       "      <td>AOU96748.1, AOU97348.1, AOU97386.1, AOU97654.1...</td>\n",
       "      <td>WP_009560941.1, WP_009567366.1, WP_012536141.1...</td>\n",
       "      <td>WP_010637203.1, WP_010637257.1, WP_010639175.1...</td>\n",
       "      <td>WP_004187662.1, WP_004187694.1, WP_004188321.1...</td>\n",
       "      <td>WP_000125282.1, WP_000148503.1, WP_000417791.1...</td>\n",
       "      <td>LFTS_00042, LFTS_00147, LFTS_00196, LFTS_00199...</td>\n",
       "      <td>WP_014961653.1, WP_014961812.1, WP_023524640.1...</td>\n",
       "      <td>WP_014448261.1, WP_014448478.1, WP_014448481.1...</td>\n",
       "      <td>NP_249788.1, NP_249790.1, NP_249887.1, NP_2500...</td>\n",
       "      <td>WP_005410795.1, WP_012478733.1, WP_012480113.1...</td>\n",
       "      <td>WP_011071927.1, WP_011072957.1, WP_011073030.1...</td>\n",
       "      <td>WP_005410795.1, WP_012478733.1, WP_012480113.1...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>OG0000002</td>\n",
       "      <td>IEKBFFFE_01121, IEKBFFFE_03469, IEKBFFFE_03582...</td>\n",
       "      <td>OIDPMNJM_00416, OIDPMNJM_01059, OIDPMNJM_01060...</td>\n",
       "      <td>LNLMDAFO_02060, LNLMDAFO_02107, LNLMDAFO_02976...</td>\n",
       "      <td>KPCADNOF_00344, KPCADNOF_02928, KPCADNOF_02976...</td>\n",
       "      <td>AOV15879.1, AOV16071.1, AOV16084.1, AOV16252.1...</td>\n",
       "      <td>APZ43007.1, APZ43009.1</td>\n",
       "      <td>OBS07867.1, OBS08015.1, OBS08539.1, OBS08571.1...</td>\n",
       "      <td>AOU96937.1, AOU97175.1, AOU97203.1, AOU97308.1...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>WP_235179950.1</td>\n",
       "      <td>WP_004187329.1, WP_004191606.1, WP_004195354.1...</td>\n",
       "      <td>WP_000483239.1, WP_000919536.1, WP_001098559.1...</td>\n",
       "      <td>LFTS_00048, LFTS_00183, LFTS_00227, LFTS_01548...</td>\n",
       "      <td>WP_023524366.1, WP_036083266.1, WP_052157684.1...</td>\n",
       "      <td>WP_014448507.1, WP_014448720.1, WP_081495456.1...</td>\n",
       "      <td>NP_248866.1, NP_248870.1, NP_249942.1, NP_2501...</td>\n",
       "      <td>WP_005409587.1, WP_005411156.1, WP_005413240.1...</td>\n",
       "      <td>WP_011070845.1, WP_011070920.1, WP_011071256.1...</td>\n",
       "      <td>WP_005409587.1, WP_005411156.1, WP_005413240.1...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>OG0000003</td>\n",
       "      <td>IEKBFFFE_00004, IEKBFFFE_00606, IEKBFFFE_01255</td>\n",
       "      <td>OIDPMNJM_01779, OIDPMNJM_03664, OIDPMNJM_04815</td>\n",
       "      <td>LNLMDAFO_01743, LNLMDAFO_02033, LNLMDAFO_02722</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AOV15817.1, AOV17986.1</td>\n",
       "      <td>APZ42156.1</td>\n",
       "      <td>OBS09277.1</td>\n",
       "      <td>AOU99153.1, AOU99802.1</td>\n",
       "      <td>WP_009562369.1</td>\n",
       "      <td>WP_010639365.1, WP_010642423.1</td>\n",
       "      <td>WP_004185837.1, WP_004187646.1, WP_004188318.1...</td>\n",
       "      <td>WP_000164036.1, WP_000357790.1, WP_000440317.1...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NP_248746.1, NP_248813.1, NP_248849.1, NP_2488...</td>\n",
       "      <td>WP_005408112.1, WP_005409107.1, WP_005410175.1...</td>\n",
       "      <td>WP_011070732.1, WP_011070865.1, WP_011071019.1...</td>\n",
       "      <td>WP_005408112.1, WP_005409107.1, WP_005410175.1...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>OG0000004</td>\n",
       "      <td>IEKBFFFE_00967, IEKBFFFE_01976, IEKBFFFE_02376...</td>\n",
       "      <td>OIDPMNJM_00353, OIDPMNJM_00657, OIDPMNJM_00880...</td>\n",
       "      <td>LNLMDAFO_00788, LNLMDAFO_00905, LNLMDAFO_01014...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AOV16082.1, AOV16671.1, AOV16881.1, AOV17015.1...</td>\n",
       "      <td>APZ42247.1, APZ42250.1, APZ42271.1, APZ43354.1...</td>\n",
       "      <td>OBS08013.1, OBS08101.1, OBS08245.1, OBS08899.1...</td>\n",
       "      <td>AOU96746.1, AOU96856.1, AOU96939.1, AOU97421.1...</td>\n",
       "      <td>WP_009564938.1, WP_012535757.1, WP_012535999.1...</td>\n",
       "      <td>WP_010637024.1, WP_010637323.1, WP_010637554.1...</td>\n",
       "      <td>WP_004193481.1, WP_004195334.1, WP_004195802.1...</td>\n",
       "      <td>WP_000365791.1, WP_000423261.1, WP_000442949.1...</td>\n",
       "      <td>LFTS_00112, LFTS_01618</td>\n",
       "      <td>WP_036079831.1, WP_036083373.1</td>\n",
       "      <td>WP_014449126.1, WP_014449532.1, WP_232502567.1</td>\n",
       "      <td>NP_248908.1, NP_250000.1, NP_250261.1, NP_2505...</td>\n",
       "      <td>WP_005407753.1, WP_005409730.1, WP_012479711.1...</td>\n",
       "      <td>WP_011071133.1, WP_011071304.1, WP_011072017.1...</td>\n",
       "      <td>WP_005407753.1, WP_005409730.1, WP_012479711.1...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Orthogroup                                  MEGAHIT-AR1_L2.36  \\\n",
       "0  OG0000000  IEKBFFFE_00044, IEKBFFFE_00702, IEKBFFFE_00771...   \n",
       "1  OG0000001  IEKBFFFE_00199, IEKBFFFE_00468, IEKBFFFE_01147...   \n",
       "2  OG0000002  IEKBFFFE_01121, IEKBFFFE_03469, IEKBFFFE_03582...   \n",
       "3  OG0000003     IEKBFFFE_00004, IEKBFFFE_00606, IEKBFFFE_01255   \n",
       "4  OG0000004  IEKBFFFE_00967, IEKBFFFE_01976, IEKBFFFE_02376...   \n",
       "\n",
       "                                   MEGAHIT-AR2_L2.57  \\\n",
       "0  OIDPMNJM_00058, OIDPMNJM_00059, OIDPMNJM_00104...   \n",
       "1  OIDPMNJM_00796, OIDPMNJM_00840, OIDPMNJM_01332...   \n",
       "2  OIDPMNJM_00416, OIDPMNJM_01059, OIDPMNJM_01060...   \n",
       "3     OIDPMNJM_01779, OIDPMNJM_03664, OIDPMNJM_04815   \n",
       "4  OIDPMNJM_00353, OIDPMNJM_00657, OIDPMNJM_00880...   \n",
       "\n",
       "                                    MEGAHIT-AR3_L2.6  \\\n",
       "0  LNLMDAFO_00406, LNLMDAFO_00464, LNLMDAFO_00677...   \n",
       "1  LNLMDAFO_00299, LNLMDAFO_00569, LNLMDAFO_00571...   \n",
       "2  LNLMDAFO_02060, LNLMDAFO_02107, LNLMDAFO_02976...   \n",
       "3     LNLMDAFO_01743, LNLMDAFO_02033, LNLMDAFO_02722   \n",
       "4  LNLMDAFO_00788, LNLMDAFO_00905, LNLMDAFO_01014...   \n",
       "\n",
       "                                   MEGAHIT-AR4_L2.41  \\\n",
       "0                                     KPCADNOF_00244   \n",
       "1  KPCADNOF_00032, KPCADNOF_00055, KPCADNOF_00119...   \n",
       "2  KPCADNOF_00344, KPCADNOF_02928, KPCADNOF_02976...   \n",
       "3                                                NaN   \n",
       "4                                                NaN   \n",
       "\n",
       "                           acidihalobacter_aeolianus  \\\n",
       "0  AOV15813.1, AOV15885.1, AOV15951.1, AOV15984.1...   \n",
       "1  AOV15752.1, AOV15864.1, AOV16627.1, AOV16629.1...   \n",
       "2  AOV15879.1, AOV16071.1, AOV16084.1, AOV16252.1...   \n",
       "3                             AOV15817.1, AOV17986.1   \n",
       "4  AOV16082.1, AOV16671.1, AOV16881.1, AOV17015.1...   \n",
       "\n",
       "                        acidihalobacter_ferrooxydans  \\\n",
       "0  APZ41780.1, APZ42158.1, APZ42214.1, APZ43198.1...   \n",
       "1  APZ41721.1, APZ41924.1, APZ41950.1, APZ42977.1...   \n",
       "2                             APZ43007.1, APZ43009.1   \n",
       "3                                         APZ42156.1   \n",
       "4  APZ42247.1, APZ42250.1, APZ42271.1, APZ43354.1...   \n",
       "\n",
       "                           acidihalobacter_prosperus  \\\n",
       "0  OBS08017.1, OBS08315.1, OBS08422.1, OBS08463.1...   \n",
       "1  OBS08105.1, OBS08243.1, OBS09011.1, OBS09039.1...   \n",
       "2  OBS07867.1, OBS08015.1, OBS08539.1, OBS08571.1...   \n",
       "3                                         OBS09277.1   \n",
       "4  OBS08013.1, OBS08101.1, OBS08245.1, OBS08899.1...   \n",
       "\n",
       "                        acidihalobacter_yilgarnensis  \\\n",
       "0  AOU96933.1, AOU97077.1, AOU97078.1, AOU97114.1...   \n",
       "1  AOU96748.1, AOU97348.1, AOU97386.1, AOU97654.1...   \n",
       "2  AOU96937.1, AOU97175.1, AOU97203.1, AOU97308.1...   \n",
       "3                             AOU99153.1, AOU99802.1   \n",
       "4  AOU96746.1, AOU96856.1, AOU96939.1, AOU97421.1...   \n",
       "\n",
       "                      acidithiobacillus_ferrooxidans  \\\n",
       "0                     WP_012536716.1, WP_113526628.1   \n",
       "1  WP_009560941.1, WP_009567366.1, WP_012536141.1...   \n",
       "2                                                NaN   \n",
       "3                                     WP_009562369.1   \n",
       "4  WP_009564938.1, WP_012535757.1, WP_012535999.1...   \n",
       "\n",
       "                       acidithiobacillus_thiooxidans  \\\n",
       "0  WP_010639275.1, WP_010639739.1, WP_010640967.1...   \n",
       "1  WP_010637203.1, WP_010637257.1, WP_010639175.1...   \n",
       "2                                     WP_235179950.1   \n",
       "3                     WP_010639365.1, WP_010642423.1   \n",
       "4  WP_010637024.1, WP_010637323.1, WP_010637554.1...   \n",
       "\n",
       "                           burkholderia_pseudomallei  \\\n",
       "0  WP_004185903.1, WP_004190283.1, WP_004528082.1...   \n",
       "1  WP_004187662.1, WP_004187694.1, WP_004188321.1...   \n",
       "2  WP_004187329.1, WP_004191606.1, WP_004195354.1...   \n",
       "3  WP_004185837.1, WP_004187646.1, WP_004188318.1...   \n",
       "4  WP_004193481.1, WP_004195334.1, WP_004195802.1...   \n",
       "\n",
       "                                              e_coli  \\\n",
       "0  WP_000043761.1, WP_000859945.1, WP_000969032.1...   \n",
       "1  WP_000125282.1, WP_000148503.1, WP_000417791.1...   \n",
       "2  WP_000483239.1, WP_000919536.1, WP_001098559.1...   \n",
       "3  WP_000164036.1, WP_000357790.1, WP_000440317.1...   \n",
       "4  WP_000365791.1, WP_000423261.1, WP_000442949.1...   \n",
       "\n",
       "                                   l_ferriphilum.gbk  \\\n",
       "0  LFTS_00405, LFTS_00479, LFTS_00501, LFTS_00575...   \n",
       "1  LFTS_00042, LFTS_00147, LFTS_00196, LFTS_00199...   \n",
       "2  LFTS_00048, LFTS_00183, LFTS_00227, LFTS_01548...   \n",
       "3                                                NaN   \n",
       "4                             LFTS_00112, LFTS_01618   \n",
       "\n",
       "                          leptospirillum_ferriphilum  \\\n",
       "0  WP_023525729.1, WP_036081380.1, WP_036081433.1...   \n",
       "1  WP_014961653.1, WP_014961812.1, WP_023524640.1...   \n",
       "2  WP_023524366.1, WP_036083266.1, WP_052157684.1...   \n",
       "3                                                NaN   \n",
       "4                     WP_036079831.1, WP_036083373.1   \n",
       "\n",
       "                         leptospirillum_ferrooxidans  \\\n",
       "0  WP_014448254.1, WP_014448371.1, WP_014448407.1...   \n",
       "1  WP_014448261.1, WP_014448478.1, WP_014448481.1...   \n",
       "2  WP_014448507.1, WP_014448720.1, WP_081495456.1...   \n",
       "3                                                NaN   \n",
       "4     WP_014449126.1, WP_014449532.1, WP_232502567.1   \n",
       "\n",
       "                         pseudomonas_aeruginosa_pao1  \\\n",
       "0  NP_248976.1, NP_249266.1, NP_249538.1, NP_2495...   \n",
       "1  NP_249788.1, NP_249790.1, NP_249887.1, NP_2500...   \n",
       "2  NP_248866.1, NP_248870.1, NP_249942.1, NP_2501...   \n",
       "3  NP_248746.1, NP_248813.1, NP_248849.1, NP_2488...   \n",
       "4  NP_248908.1, NP_250000.1, NP_250261.1, NP_2505...   \n",
       "\n",
       "                                       s_maltophilia  \\\n",
       "0  WP_005408197.1, WP_005409582.1, WP_005410383.1...   \n",
       "1  WP_005410795.1, WP_012478733.1, WP_012480113.1...   \n",
       "2  WP_005409587.1, WP_005411156.1, WP_005413240.1...   \n",
       "3  WP_005408112.1, WP_005409107.1, WP_005410175.1...   \n",
       "4  WP_005407753.1, WP_005409730.1, WP_012479711.1...   \n",
       "\n",
       "                               shewanella_oneidensis  \\\n",
       "0  WP_011070544.1, WP_011070706.1, WP_011070785.1...   \n",
       "1  WP_011071927.1, WP_011072957.1, WP_011073030.1...   \n",
       "2  WP_011070845.1, WP_011070920.1, WP_011071256.1...   \n",
       "3  WP_011070732.1, WP_011070865.1, WP_011071019.1...   \n",
       "4  WP_011071133.1, WP_011071304.1, WP_011072017.1...   \n",
       "\n",
       "                        stenotrophomonas_maltophilia  \n",
       "0  WP_005408197.1, WP_005409582.1, WP_005410383.1...  \n",
       "1  WP_005410795.1, WP_012478733.1, WP_012480113.1...  \n",
       "2  WP_005409587.1, WP_005411156.1, WP_005413240.1...  \n",
       "3  WP_005408112.1, WP_005409107.1, WP_005410175.1...  \n",
       "4  WP_005407753.1, WP_005409730.1, WP_012479711.1...  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "b75ef363",
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop duplicate genomes from orthogroups\n",
    "df.drop(labels=[\"leptospirillum_ferriphilum\",\"stenotrophomonas_maltophilia\"], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "5938065b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# candidate genes data\n",
    "candidates = pd.read_csv(\"candidategenes.csv\", header=None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "d132fac6",
   "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>0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>WP_012536059.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>WP_012536091.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>WP_012536114.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>WP_012536116.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>WP_012607166.1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                0\n",
       "0  WP_012536059.1\n",
       "1  WP_012536091.1\n",
       "2  WP_012536114.1\n",
       "3  WP_012536116.1\n",
       "4  WP_012607166.1"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "candidates.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "a6634b00",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1065, 1)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "candidates.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "ca6a101f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop duplicate candidate genes\n",
    "candidates.drop_duplicates(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "650ff402",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1039, 1)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "candidates.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "4e8319d5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert candidate genes df to a set\n",
    "candidates = set(candidates.iloc[:,0].tolist())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "36287c2e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# filter df to only show orthogroups corresponding to the list of candidate genes\n",
    "filterer = lambda x: \", \".join(y for y in x.split(\", \") if y in candidates)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "318a854e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# filtered dataframe output\n",
    "df_out = (df.set_index(\"Orthogroup\").iloc[:,8:]\n",
    "          .fillna(\"\")\n",
    "          .applymap(filterer)\n",
    "          .replace(\"\", np.nan)\n",
    "          .dropna(how=\"all\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "bd0d2fee",
   "metadata": {
    "scrolled": false
   },
   "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>acidithiobacillus_ferrooxidans</th>\n",
       "      <th>acidithiobacillus_thiooxidans</th>\n",
       "      <th>burkholderia_pseudomallei</th>\n",
       "      <th>e_coli</th>\n",
       "      <th>l_ferriphilum.gbk</th>\n",
       "      <th>leptospirillum_ferrooxidans</th>\n",
       "      <th>pseudomonas_aeruginosa_pao1</th>\n",
       "      <th>s_maltophilia</th>\n",
       "      <th>shewanella_oneidensis</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Orthogroup</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>OG0000001</th>\n",
       "      <td>NaN</td>\n",
       "      <td>WP_010639966.1</td>\n",
       "      <td>WP_004192300.1, WP_004526108.1</td>\n",
       "      <td>WP_001301332.1</td>\n",
       "      <td>LFTS_00568, LFTS_00573</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NP_249790.1, NP_254170.1, NP_254198.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OG0000002</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NP_250114.1</td>\n",
       "      <td>WP_012481655.1</td>\n",
       "      <td>WP_011071256.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OG0000004</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>WP_004196705.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OG0000006</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>WP_011072711.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OG0000007</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>WP_001157751.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NP_252790.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>WP_011073412.1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           acidithiobacillus_ferrooxidans acidithiobacillus_thiooxidans  \\\n",
       "Orthogroup                                                                \n",
       "OG0000001                             NaN                WP_010639966.1   \n",
       "OG0000002                             NaN                           NaN   \n",
       "OG0000004                             NaN                           NaN   \n",
       "OG0000006                             NaN                           NaN   \n",
       "OG0000007                             NaN                           NaN   \n",
       "\n",
       "                 burkholderia_pseudomallei          e_coli  \\\n",
       "Orthogroup                                                   \n",
       "OG0000001   WP_004192300.1, WP_004526108.1  WP_001301332.1   \n",
       "OG0000002                              NaN             NaN   \n",
       "OG0000004                   WP_004196705.1             NaN   \n",
       "OG0000006                              NaN             NaN   \n",
       "OG0000007                              NaN  WP_001157751.1   \n",
       "\n",
       "                 l_ferriphilum.gbk leptospirillum_ferrooxidans  \\\n",
       "Orthogroup                                                       \n",
       "OG0000001   LFTS_00568, LFTS_00573                         NaN   \n",
       "OG0000002                      NaN                         NaN   \n",
       "OG0000004                      NaN                         NaN   \n",
       "OG0000006                      NaN                         NaN   \n",
       "OG0000007                      NaN                         NaN   \n",
       "\n",
       "                      pseudomonas_aeruginosa_pao1   s_maltophilia  \\\n",
       "Orthogroup                                                          \n",
       "OG0000001   NP_249790.1, NP_254170.1, NP_254198.1             NaN   \n",
       "OG0000002                             NP_250114.1  WP_012481655.1   \n",
       "OG0000004                                     NaN             NaN   \n",
       "OG0000006                                     NaN             NaN   \n",
       "OG0000007                             NP_252790.1             NaN   \n",
       "\n",
       "           shewanella_oneidensis  \n",
       "Orthogroup                        \n",
       "OG0000001                    NaN  \n",
       "OG0000002         WP_011071256.1  \n",
       "OG0000004                    NaN  \n",
       "OG0000006         WP_011072711.1  \n",
       "OG0000007         WP_011073412.1  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_out.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "d89eed4c",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(779, 9)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_out.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "59fdf878",
   "metadata": {},
   "outputs": [],
   "source": [
    "# count overlaps\n",
    "overlaps = [count.str.split(\",\").explode().count() for i, count in df_out.iterrows()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "157462b9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "779"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(overlaps)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "3ecb3a9b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# add back in the MAGs and Ac. genomes\n",
    "df_out = pd.merge(df_out, df.set_index(\"Orthogroup\").iloc[:,0:8], left_index=True, right_index=True, how=\"left\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "0ae7314f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(779, 17)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_out.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "9a1ebe84",
   "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>acidithiobacillus_ferrooxidans</th>\n",
       "      <th>acidithiobacillus_thiooxidans</th>\n",
       "      <th>burkholderia_pseudomallei</th>\n",
       "      <th>e_coli</th>\n",
       "      <th>l_ferriphilum.gbk</th>\n",
       "      <th>leptospirillum_ferrooxidans</th>\n",
       "      <th>pseudomonas_aeruginosa_pao1</th>\n",
       "      <th>s_maltophilia</th>\n",
       "      <th>shewanella_oneidensis</th>\n",
       "      <th>MEGAHIT-AR1_L2.36</th>\n",
       "      <th>MEGAHIT-AR2_L2.57</th>\n",
       "      <th>MEGAHIT-AR3_L2.6</th>\n",
       "      <th>MEGAHIT-AR4_L2.41</th>\n",
       "      <th>acidihalobacter_aeolianus</th>\n",
       "      <th>acidihalobacter_ferrooxydans</th>\n",
       "      <th>acidihalobacter_prosperus</th>\n",
       "      <th>acidihalobacter_yilgarnensis</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Orthogroup</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>OG0000001</th>\n",
       "      <td>NaN</td>\n",
       "      <td>WP_010639966.1</td>\n",
       "      <td>WP_004192300.1, WP_004526108.1</td>\n",
       "      <td>WP_001301332.1</td>\n",
       "      <td>LFTS_00568, LFTS_00573</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NP_249790.1, NP_254170.1, NP_254198.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>IEKBFFFE_00199, IEKBFFFE_00468, IEKBFFFE_01147...</td>\n",
       "      <td>OIDPMNJM_00796, OIDPMNJM_00840, OIDPMNJM_01332...</td>\n",
       "      <td>LNLMDAFO_00299, LNLMDAFO_00569, LNLMDAFO_00571...</td>\n",
       "      <td>KPCADNOF_00032, KPCADNOF_00055, KPCADNOF_00119...</td>\n",
       "      <td>AOV15752.1, AOV15864.1, AOV16627.1, AOV16629.1...</td>\n",
       "      <td>APZ41721.1, APZ41924.1, APZ41950.1, APZ42977.1...</td>\n",
       "      <td>OBS08105.1, OBS08243.1, OBS09011.1, OBS09039.1...</td>\n",
       "      <td>AOU96748.1, AOU97348.1, AOU97386.1, AOU97654.1...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OG0000002</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NP_250114.1</td>\n",
       "      <td>WP_012481655.1</td>\n",
       "      <td>WP_011071256.1</td>\n",
       "      <td>IEKBFFFE_01121, IEKBFFFE_03469, IEKBFFFE_03582...</td>\n",
       "      <td>OIDPMNJM_00416, OIDPMNJM_01059, OIDPMNJM_01060...</td>\n",
       "      <td>LNLMDAFO_02060, LNLMDAFO_02107, LNLMDAFO_02976...</td>\n",
       "      <td>KPCADNOF_00344, KPCADNOF_02928, KPCADNOF_02976...</td>\n",
       "      <td>AOV15879.1, AOV16071.1, AOV16084.1, AOV16252.1...</td>\n",
       "      <td>APZ43007.1, APZ43009.1</td>\n",
       "      <td>OBS07867.1, OBS08015.1, OBS08539.1, OBS08571.1...</td>\n",
       "      <td>AOU96937.1, AOU97175.1, AOU97203.1, AOU97308.1...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OG0000004</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>WP_004196705.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>IEKBFFFE_00967, IEKBFFFE_01976, IEKBFFFE_02376...</td>\n",
       "      <td>OIDPMNJM_00353, OIDPMNJM_00657, OIDPMNJM_00880...</td>\n",
       "      <td>LNLMDAFO_00788, LNLMDAFO_00905, LNLMDAFO_01014...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AOV16082.1, AOV16671.1, AOV16881.1, AOV17015.1...</td>\n",
       "      <td>APZ42247.1, APZ42250.1, APZ42271.1, APZ43354.1...</td>\n",
       "      <td>OBS08013.1, OBS08101.1, OBS08245.1, OBS08899.1...</td>\n",
       "      <td>AOU96746.1, AOU96856.1, AOU96939.1, AOU97421.1...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OG0000006</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>WP_011072711.1</td>\n",
       "      <td>IEKBFFFE_00164, IEKBFFFE_00277</td>\n",
       "      <td>OIDPMNJM_00613, OIDPMNJM_01503, OIDPMNJM_01771...</td>\n",
       "      <td>LNLMDAFO_01300, LNLMDAFO_01567</td>\n",
       "      <td>KPCADNOF_00507, KPCADNOF_00785, KPCADNOF_00925...</td>\n",
       "      <td>AOV16094.1, AOV16958.1, AOV17286.1, AOV18352.1</td>\n",
       "      <td>APZ43650.1</td>\n",
       "      <td>OBS07968.1, OBS09251.1, OBS10380.1, OBS10754.1</td>\n",
       "      <td>AOU98102.1, AOU98326.1, AOU99126.1, AOU99558.1...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OG0000007</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>WP_001157751.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NP_252790.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>WP_011073412.1</td>\n",
       "      <td>IEKBFFFE_01744, IEKBFFFE_02448, IEKBFFFE_03754</td>\n",
       "      <td>OIDPMNJM_00388, OIDPMNJM_02720, OIDPMNJM_03837...</td>\n",
       "      <td>LNLMDAFO_00473, LNLMDAFO_00609, LNLMDAFO_01029</td>\n",
       "      <td>KPCADNOF_00225, KPCADNOF_00372, KPCADNOF_00375...</td>\n",
       "      <td>AOV15828.1, AOV15968.1, AOV17124.1, AOV17718.1</td>\n",
       "      <td>APZ44178.1, APZ44474.1</td>\n",
       "      <td>OBS08604.1, OBS09433.1, OBS10956.1</td>\n",
       "      <td>AOU97233.1, AOU97906.1, AOU99827.1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           acidithiobacillus_ferrooxidans acidithiobacillus_thiooxidans  \\\n",
       "Orthogroup                                                                \n",
       "OG0000001                             NaN                WP_010639966.1   \n",
       "OG0000002                             NaN                           NaN   \n",
       "OG0000004                             NaN                           NaN   \n",
       "OG0000006                             NaN                           NaN   \n",
       "OG0000007                             NaN                           NaN   \n",
       "\n",
       "                 burkholderia_pseudomallei          e_coli  \\\n",
       "Orthogroup                                                   \n",
       "OG0000001   WP_004192300.1, WP_004526108.1  WP_001301332.1   \n",
       "OG0000002                              NaN             NaN   \n",
       "OG0000004                   WP_004196705.1             NaN   \n",
       "OG0000006                              NaN             NaN   \n",
       "OG0000007                              NaN  WP_001157751.1   \n",
       "\n",
       "                 l_ferriphilum.gbk leptospirillum_ferrooxidans  \\\n",
       "Orthogroup                                                       \n",
       "OG0000001   LFTS_00568, LFTS_00573                         NaN   \n",
       "OG0000002                      NaN                         NaN   \n",
       "OG0000004                      NaN                         NaN   \n",
       "OG0000006                      NaN                         NaN   \n",
       "OG0000007                      NaN                         NaN   \n",
       "\n",
       "                      pseudomonas_aeruginosa_pao1   s_maltophilia  \\\n",
       "Orthogroup                                                          \n",
       "OG0000001   NP_249790.1, NP_254170.1, NP_254198.1             NaN   \n",
       "OG0000002                             NP_250114.1  WP_012481655.1   \n",
       "OG0000004                                     NaN             NaN   \n",
       "OG0000006                                     NaN             NaN   \n",
       "OG0000007                             NP_252790.1             NaN   \n",
       "\n",
       "           shewanella_oneidensis  \\\n",
       "Orthogroup                         \n",
       "OG0000001                    NaN   \n",
       "OG0000002         WP_011071256.1   \n",
       "OG0000004                    NaN   \n",
       "OG0000006         WP_011072711.1   \n",
       "OG0000007         WP_011073412.1   \n",
       "\n",
       "                                            MEGAHIT-AR1_L2.36  \\\n",
       "Orthogroup                                                      \n",
       "OG0000001   IEKBFFFE_00199, IEKBFFFE_00468, IEKBFFFE_01147...   \n",
       "OG0000002   IEKBFFFE_01121, IEKBFFFE_03469, IEKBFFFE_03582...   \n",
       "OG0000004   IEKBFFFE_00967, IEKBFFFE_01976, IEKBFFFE_02376...   \n",
       "OG0000006                      IEKBFFFE_00164, IEKBFFFE_00277   \n",
       "OG0000007      IEKBFFFE_01744, IEKBFFFE_02448, IEKBFFFE_03754   \n",
       "\n",
       "                                            MEGAHIT-AR2_L2.57  \\\n",
       "Orthogroup                                                      \n",
       "OG0000001   OIDPMNJM_00796, OIDPMNJM_00840, OIDPMNJM_01332...   \n",
       "OG0000002   OIDPMNJM_00416, OIDPMNJM_01059, OIDPMNJM_01060...   \n",
       "OG0000004   OIDPMNJM_00353, OIDPMNJM_00657, OIDPMNJM_00880...   \n",
       "OG0000006   OIDPMNJM_00613, OIDPMNJM_01503, OIDPMNJM_01771...   \n",
       "OG0000007   OIDPMNJM_00388, OIDPMNJM_02720, OIDPMNJM_03837...   \n",
       "\n",
       "                                             MEGAHIT-AR3_L2.6  \\\n",
       "Orthogroup                                                      \n",
       "OG0000001   LNLMDAFO_00299, LNLMDAFO_00569, LNLMDAFO_00571...   \n",
       "OG0000002   LNLMDAFO_02060, LNLMDAFO_02107, LNLMDAFO_02976...   \n",
       "OG0000004   LNLMDAFO_00788, LNLMDAFO_00905, LNLMDAFO_01014...   \n",
       "OG0000006                      LNLMDAFO_01300, LNLMDAFO_01567   \n",
       "OG0000007      LNLMDAFO_00473, LNLMDAFO_00609, LNLMDAFO_01029   \n",
       "\n",
       "                                            MEGAHIT-AR4_L2.41  \\\n",
       "Orthogroup                                                      \n",
       "OG0000001   KPCADNOF_00032, KPCADNOF_00055, KPCADNOF_00119...   \n",
       "OG0000002   KPCADNOF_00344, KPCADNOF_02928, KPCADNOF_02976...   \n",
       "OG0000004                                                 NaN   \n",
       "OG0000006   KPCADNOF_00507, KPCADNOF_00785, KPCADNOF_00925...   \n",
       "OG0000007   KPCADNOF_00225, KPCADNOF_00372, KPCADNOF_00375...   \n",
       "\n",
       "                                    acidihalobacter_aeolianus  \\\n",
       "Orthogroup                                                      \n",
       "OG0000001   AOV15752.1, AOV15864.1, AOV16627.1, AOV16629.1...   \n",
       "OG0000002   AOV15879.1, AOV16071.1, AOV16084.1, AOV16252.1...   \n",
       "OG0000004   AOV16082.1, AOV16671.1, AOV16881.1, AOV17015.1...   \n",
       "OG0000006      AOV16094.1, AOV16958.1, AOV17286.1, AOV18352.1   \n",
       "OG0000007      AOV15828.1, AOV15968.1, AOV17124.1, AOV17718.1   \n",
       "\n",
       "                                 acidihalobacter_ferrooxydans  \\\n",
       "Orthogroup                                                      \n",
       "OG0000001   APZ41721.1, APZ41924.1, APZ41950.1, APZ42977.1...   \n",
       "OG0000002                              APZ43007.1, APZ43009.1   \n",
       "OG0000004   APZ42247.1, APZ42250.1, APZ42271.1, APZ43354.1...   \n",
       "OG0000006                                          APZ43650.1   \n",
       "OG0000007                              APZ44178.1, APZ44474.1   \n",
       "\n",
       "                                    acidihalobacter_prosperus  \\\n",
       "Orthogroup                                                      \n",
       "OG0000001   OBS08105.1, OBS08243.1, OBS09011.1, OBS09039.1...   \n",
       "OG0000002   OBS07867.1, OBS08015.1, OBS08539.1, OBS08571.1...   \n",
       "OG0000004   OBS08013.1, OBS08101.1, OBS08245.1, OBS08899.1...   \n",
       "OG0000006      OBS07968.1, OBS09251.1, OBS10380.1, OBS10754.1   \n",
       "OG0000007                  OBS08604.1, OBS09433.1, OBS10956.1   \n",
       "\n",
       "                                 acidihalobacter_yilgarnensis  \n",
       "Orthogroup                                                     \n",
       "OG0000001   AOU96748.1, AOU97348.1, AOU97386.1, AOU97654.1...  \n",
       "OG0000002   AOU96937.1, AOU97175.1, AOU97203.1, AOU97308.1...  \n",
       "OG0000004   AOU96746.1, AOU96856.1, AOU96939.1, AOU97421.1...  \n",
       "OG0000006   AOU98102.1, AOU98326.1, AOU99126.1, AOU99558.1...  \n",
       "OG0000007                  AOU97233.1, AOU97906.1, AOU99827.1  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_out.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "0c1794de",
   "metadata": {},
   "outputs": [],
   "source": [
    "# count genes in organisms\n",
    "gene_counts = pd.DataFrame(\n",
    "    data = np.array([counts.str.split(\",\").str.len() for i, counts in df_out.iterrows()]).astype(int),\n",
    "    columns = [str(name)+\"_counts\" for name in df_out.columns],\n",
    "    index = df_out.index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "c0e3f4fa",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(779, 17)"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gene_counts.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "86a1dd24",
   "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>acidithiobacillus_ferrooxidans_counts</th>\n",
       "      <th>acidithiobacillus_thiooxidans_counts</th>\n",
       "      <th>burkholderia_pseudomallei_counts</th>\n",
       "      <th>e_coli_counts</th>\n",
       "      <th>l_ferriphilum.gbk_counts</th>\n",
       "      <th>leptospirillum_ferrooxidans_counts</th>\n",
       "      <th>pseudomonas_aeruginosa_pao1_counts</th>\n",
       "      <th>s_maltophilia_counts</th>\n",
       "      <th>shewanella_oneidensis_counts</th>\n",
       "      <th>MEGAHIT-AR1_L2.36_counts</th>\n",
       "      <th>MEGAHIT-AR2_L2.57_counts</th>\n",
       "      <th>MEGAHIT-AR3_L2.6_counts</th>\n",
       "      <th>MEGAHIT-AR4_L2.41_counts</th>\n",
       "      <th>acidihalobacter_aeolianus_counts</th>\n",
       "      <th>acidihalobacter_ferrooxydans_counts</th>\n",
       "      <th>acidihalobacter_prosperus_counts</th>\n",
       "      <th>acidihalobacter_yilgarnensis_counts</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Orthogroup</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>OG0000001</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>15</td>\n",
       "      <td>10</td>\n",
       "      <td>28</td>\n",
       "      <td>11</td>\n",
       "      <td>9</td>\n",
       "      <td>11</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OG0000002</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>12</td>\n",
       "      <td>2</td>\n",
       "      <td>12</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OG0000004</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>6</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OG0000006</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OG0000007</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            acidithiobacillus_ferrooxidans_counts  \\\n",
       "Orthogroup                                          \n",
       "OG0000001                                       0   \n",
       "OG0000002                                       0   \n",
       "OG0000004                                       0   \n",
       "OG0000006                                       0   \n",
       "OG0000007                                       0   \n",
       "\n",
       "            acidithiobacillus_thiooxidans_counts  \\\n",
       "Orthogroup                                         \n",
       "OG0000001                                      1   \n",
       "OG0000002                                      0   \n",
       "OG0000004                                      0   \n",
       "OG0000006                                      0   \n",
       "OG0000007                                      0   \n",
       "\n",
       "            burkholderia_pseudomallei_counts  e_coli_counts  \\\n",
       "Orthogroup                                                    \n",
       "OG0000001                                  2              1   \n",
       "OG0000002                                  0              0   \n",
       "OG0000004                                  1              0   \n",
       "OG0000006                                  0              0   \n",
       "OG0000007                                  0              1   \n",
       "\n",
       "            l_ferriphilum.gbk_counts  leptospirillum_ferrooxidans_counts  \\\n",
       "Orthogroup                                                                 \n",
       "OG0000001                          2                                   0   \n",
       "OG0000002                          0                                   0   \n",
       "OG0000004                          0                                   0   \n",
       "OG0000006                          0                                   0   \n",
       "OG0000007                          0                                   0   \n",
       "\n",
       "            pseudomonas_aeruginosa_pao1_counts  s_maltophilia_counts  \\\n",
       "Orthogroup                                                             \n",
       "OG0000001                                    3                     0   \n",
       "OG0000002                                    1                     1   \n",
       "OG0000004                                    0                     0   \n",
       "OG0000006                                    0                     0   \n",
       "OG0000007                                    1                     0   \n",
       "\n",
       "            shewanella_oneidensis_counts  MEGAHIT-AR1_L2.36_counts  \\\n",
       "Orthogroup                                                           \n",
       "OG0000001                              0                        10   \n",
       "OG0000002                              1                         4   \n",
       "OG0000004                              0                         7   \n",
       "OG0000006                              1                         2   \n",
       "OG0000007                              1                         3   \n",
       "\n",
       "            MEGAHIT-AR2_L2.57_counts  MEGAHIT-AR3_L2.6_counts  \\\n",
       "Orthogroup                                                      \n",
       "OG0000001                         15                       10   \n",
       "OG0000002                          6                        4   \n",
       "OG0000004                          7                        7   \n",
       "OG0000006                          5                        2   \n",
       "OG0000007                          4                        3   \n",
       "\n",
       "            MEGAHIT-AR4_L2.41_counts  acidihalobacter_aeolianus_counts  \\\n",
       "Orthogroup                                                               \n",
       "OG0000001                         28                                11   \n",
       "OG0000002                          4                                12   \n",
       "OG0000004                          0                                10   \n",
       "OG0000006                         11                                 4   \n",
       "OG0000007                          7                                 4   \n",
       "\n",
       "            acidihalobacter_ferrooxydans_counts  \\\n",
       "Orthogroup                                        \n",
       "OG0000001                                     9   \n",
       "OG0000002                                     2   \n",
       "OG0000004                                     6   \n",
       "OG0000006                                     1   \n",
       "OG0000007                                     2   \n",
       "\n",
       "            acidihalobacter_prosperus_counts  \\\n",
       "Orthogroup                                     \n",
       "OG0000001                                 11   \n",
       "OG0000002                                 12   \n",
       "OG0000004                                  8   \n",
       "OG0000006                                  4   \n",
       "OG0000007                                  3   \n",
       "\n",
       "            acidihalobacter_yilgarnensis_counts  \n",
       "Orthogroup                                       \n",
       "OG0000001                                    10  \n",
       "OG0000002                                    14  \n",
       "OG0000004                                     8  \n",
       "OG0000006                                     5  \n",
       "OG0000007                                     3  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gene_counts.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "1159d2cf",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final = pd.concat([df_out, gene_counts], axis=1).reset_index()\n",
    "df_final.insert(1, \"overlaps\", overlaps)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "ede2d0de",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(779, 36)"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_final.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "aa77cf80",
   "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>Orthogroup</th>\n",
       "      <th>overlaps</th>\n",
       "      <th>acidithiobacillus_ferrooxidans</th>\n",
       "      <th>acidithiobacillus_thiooxidans</th>\n",
       "      <th>burkholderia_pseudomallei</th>\n",
       "      <th>e_coli</th>\n",
       "      <th>l_ferriphilum.gbk</th>\n",
       "      <th>leptospirillum_ferrooxidans</th>\n",
       "      <th>pseudomonas_aeruginosa_pao1</th>\n",
       "      <th>s_maltophilia</th>\n",
       "      <th>...</th>\n",
       "      <th>s_maltophilia_counts</th>\n",
       "      <th>shewanella_oneidensis_counts</th>\n",
       "      <th>MEGAHIT-AR1_L2.36_counts</th>\n",
       "      <th>MEGAHIT-AR2_L2.57_counts</th>\n",
       "      <th>MEGAHIT-AR3_L2.6_counts</th>\n",
       "      <th>MEGAHIT-AR4_L2.41_counts</th>\n",
       "      <th>acidihalobacter_aeolianus_counts</th>\n",
       "      <th>acidihalobacter_ferrooxydans_counts</th>\n",
       "      <th>acidihalobacter_prosperus_counts</th>\n",
       "      <th>acidihalobacter_yilgarnensis_counts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>OG0000001</td>\n",
       "      <td>9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>WP_010639966.1</td>\n",
       "      <td>WP_004192300.1, WP_004526108.1</td>\n",
       "      <td>WP_001301332.1</td>\n",
       "      <td>LFTS_00568, LFTS_00573</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NP_249790.1, NP_254170.1, NP_254198.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>15</td>\n",
       "      <td>10</td>\n",
       "      <td>28</td>\n",
       "      <td>11</td>\n",
       "      <td>9</td>\n",
       "      <td>11</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>OG0000002</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NP_250114.1</td>\n",
       "      <td>WP_012481655.1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>12</td>\n",
       "      <td>2</td>\n",
       "      <td>12</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>OG0000004</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>WP_004196705.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>6</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>OG0000006</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>OG0000007</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>WP_001157751.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NP_252790.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 36 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Orthogroup  overlaps acidithiobacillus_ferrooxidans  \\\n",
       "0  OG0000001         9                            NaN   \n",
       "1  OG0000002         3                            NaN   \n",
       "2  OG0000004         1                            NaN   \n",
       "3  OG0000006         1                            NaN   \n",
       "4  OG0000007         3                            NaN   \n",
       "\n",
       "  acidithiobacillus_thiooxidans       burkholderia_pseudomallei  \\\n",
       "0                WP_010639966.1  WP_004192300.1, WP_004526108.1   \n",
       "1                           NaN                             NaN   \n",
       "2                           NaN                  WP_004196705.1   \n",
       "3                           NaN                             NaN   \n",
       "4                           NaN                             NaN   \n",
       "\n",
       "           e_coli       l_ferriphilum.gbk leptospirillum_ferrooxidans  \\\n",
       "0  WP_001301332.1  LFTS_00568, LFTS_00573                         NaN   \n",
       "1             NaN                     NaN                         NaN   \n",
       "2             NaN                     NaN                         NaN   \n",
       "3             NaN                     NaN                         NaN   \n",
       "4  WP_001157751.1                     NaN                         NaN   \n",
       "\n",
       "             pseudomonas_aeruginosa_pao1   s_maltophilia  ...  \\\n",
       "0  NP_249790.1, NP_254170.1, NP_254198.1             NaN  ...   \n",
       "1                            NP_250114.1  WP_012481655.1  ...   \n",
       "2                                    NaN             NaN  ...   \n",
       "3                                    NaN             NaN  ...   \n",
       "4                            NP_252790.1             NaN  ...   \n",
       "\n",
       "  s_maltophilia_counts shewanella_oneidensis_counts MEGAHIT-AR1_L2.36_counts  \\\n",
       "0                    0                            0                       10   \n",
       "1                    1                            1                        4   \n",
       "2                    0                            0                        7   \n",
       "3                    0                            1                        2   \n",
       "4                    0                            1                        3   \n",
       "\n",
       "  MEGAHIT-AR2_L2.57_counts MEGAHIT-AR3_L2.6_counts MEGAHIT-AR4_L2.41_counts  \\\n",
       "0                       15                      10                       28   \n",
       "1                        6                       4                        4   \n",
       "2                        7                       7                        0   \n",
       "3                        5                       2                       11   \n",
       "4                        4                       3                        7   \n",
       "\n",
       "  acidihalobacter_aeolianus_counts acidihalobacter_ferrooxydans_counts  \\\n",
       "0                               11                                   9   \n",
       "1                               12                                   2   \n",
       "2                               10                                   6   \n",
       "3                                4                                   1   \n",
       "4                                4                                   2   \n",
       "\n",
       "  acidihalobacter_prosperus_counts  acidihalobacter_yilgarnensis_counts  \n",
       "0                               11                                   10  \n",
       "1                               12                                   14  \n",
       "2                                8                                    8  \n",
       "3                                4                                    5  \n",
       "4                                3                                    3  \n",
       "\n",
       "[5 rows x 36 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_final.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "99c64b49",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final.to_csv(\"orthogroups_table.csv\", index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}