Data_Curation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DATASET CURATION - MASKED ROI PROJECT\n",
    "\n",
    "\n",
    "**Objectives**: \n",
    "\n",
    "To create the following groups:\n",
    "1. **Positive group**: BIRADS 0 that became BIRADS 3, 4, 5, 6 in the subsequent diagnostic study\n",
    "2. **Negative group**: BIRADS 1, 2 and BIRADS 0 that became BIRADS 1, 2 in the subsequent diagnostic study\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Prep"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import random\n",
    "from tqdm import tqdm\n",
    "\n",
    "from IPython.display import display\n",
    "\n",
    "pd.set_option('display.max_rows', 50)\n",
    "pd.set_option('display.max_columns', 500)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_stats(df, suffix=None):\n",
    "    \"\"\"Provides a quick summary of a dataframe.\"\"\"\n",
    "    try:\n",
    "        print(f\"DF shape: {df.shape}\")\n",
    "        print(f\"# Patients: {df.empi_anon.nunique()}\")\n",
    "        print(f\"# Cases: {df.acc_anon.nunique()}\\n\")\n",
    "        print(f\"# Images: {df.png_path.nunique()}\\n\")\n",
    "    except Exception as e:\n",
    "        print(e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# EMBED\n",
    "metadata_full = pd.read_csv(\"/data/mammo/tables/metadata_all_cohort_with_ROI_HITI.csv\", dtype=str)\n",
    "magview_full = pd.read_csv(\"/data/mammo/tables/magview_all_cohorts_anon_HITI.csv\", dtype=str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Selecting the following columns\n",
    "\n",
    "meta_cols = [\n",
    "    \"empi_anon\",\n",
    "    \"acc_anon\",\n",
    "    \"ImageLateralityFinal\",\n",
    "    \"ViewPosition\",\n",
    "    \"study_date_anon\",\n",
    "    \"FinalImageType\",\n",
    "    \"png_path\",\n",
    "    \"StudyDescription\",\n",
    "    \"match_level\",\n",
    "    \"num_roi\",\n",
    "    \"ROI_coords\"\n",
    "]\n",
    "\n",
    "mag_cols = [\n",
    "    \"empi_anon\",\n",
    "    \"acc_anon\",\n",
    "    \"study_date_anon\",\n",
    "    \"desc\",\n",
    "    \"side\",\n",
    "    \"asses\",\n",
    "    \"path_severity\",\n",
    "    \"bside\",\n",
    "    'procdate_anon',\n",
    "    'pdate_anon',\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "metadata = metadata_full[meta_cols].copy()\n",
    "magview = magview_full[mag_cols].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "metadata.study_date_anon = pd.to_datetime(metadata.study_date_anon)\n",
    "magview.study_date_anon = pd.to_datetime(magview.study_date_anon)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "metadata.num_roi = metadata.num_roi.astype(int)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. METADATA: 2D MLO & CC"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DF shape: (1645747, 11)\n",
      "# Patients: 111988\n",
      "# Cases: 355306\n",
      "\n",
      "# Images: 1645747\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# EMBED 2D (MLO and CC)\n",
    "meta_2d = metadata.loc[(metadata.FinalImageType==\"2D\") & (metadata.ViewPosition.isin([\"MLO\", \"CC\"]))]\n",
    "get_stats(meta_2d)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_image_stats(df):\n",
    "    \"\"\"Provides a quick summary of the number of unique images and the ROIs.\"\"\"\n",
    "    temp_df = pd.merge(df, meta_2d, on=[\"empi_anon\", \"acc_anon\"], how=\"left\")\n",
    "    temp_df = temp_df.loc[\n",
    "        (temp_df.side==temp_df.ImageLateralityFinal)\n",
    "    ]\n",
    "    temp_df.drop_duplicates(subset=\"png_path\", inplace=True)\n",
    "    print(f\"# PNG PATH: {int(temp_df.png_path.nunique())}\")\n",
    "    print(f\"# ROI: {int(temp_df.num_roi.sum())}\")\n",
    "    print(f\"{temp_df.num_roi.value_counts()}\")\n",
    "    del temp_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Screening"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DF shape: (311129, 10)\n",
      "# Patients: 103704\n",
      "# Cases: 295885\n",
      "\n",
      "'DataFrame' object has no attribute 'png_path'\n"
     ]
    }
   ],
   "source": [
    "# SCREENING\n",
    "screening_magview = magview.loc[magview.desc.str.contains(\"screen\", case=False)].copy()\n",
    "get_stats(screening_magview)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.1. Creating entries for the negative contralateral breast in bilateral examinations\n",
    "\n",
    "```\n",
    "MAGVIEW only has entries if a finding exists.\n",
    "\n",
    "This means that if an exam is a bilateral exam and only one of the breast has a finding, the contralateral breast (negative) won't have an entry.\n",
    "\n",
    "This would be problematic at the time when we need to merge with METADATA, because the contralateral breast would be excluded.\n",
    "\n",
    "Therefore, we would need to create rows for the negative contralateral breast.\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_exam_laterality(row):\n",
    "    \"\"\"A convenient function to get the exam laterality to be used with DF.apply() instead of iterating over each row.\"\"\"\n",
    "    if (\"bilat\" in row.desc.lower()):\n",
    "        return \"B\"\n",
    "    elif (\"left\" in row.desc.lower()):\n",
    "        return \"L\"\n",
    "    elif (\"right\" in row.desc.lower()):\n",
    "        return \"R\"\n",
    "    else:\n",
    "        return None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Applying the get_exam_laterality function\n",
    "screening_magview[\"exam_laterality\"] = screening_magview.apply(get_exam_laterality, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "B    298848\n",
       "R      6360\n",
       "L      5921\n",
       "Name: exam_laterality, dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "screening_magview.exam_laterality.value_counts(dropna=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "NaN    208681\n",
       "L       43373\n",
       "R       43366\n",
       "B       15709\n",
       "Name: side, dtype: int64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "screening_magview.side.value_counts(dropna=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# side == nan --> B\n",
    "screening_magview.side = screening_magview.side.fillna(\"B\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create copy for assigning B to R\n",
    "screening_magview_r = screening_magview.loc[screening_magview.side==\"B\"].copy()\n",
    "screening_magview_r.side = screening_magview.side.str.replace(\"B\", \"R\")\n",
    "\n",
    "# assigning B to L\n",
    "screening_magview.side = screening_magview.side.str.replace(\"B\", \"L\")\n",
    "\n",
    "# appending R and L\n",
    "screening_magview = pd.concat([screening_magview, screening_magview_r])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "L    267763\n",
      "R    267756\n",
      "Name: side, dtype: int64\n",
      "(535519, 11)\n"
     ]
    }
   ],
   "source": [
    "print(screening_magview.side.value_counts(dropna=False))\n",
    "print(screening_magview.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>empi_anon</th>\n",
       "      <th>acc_anon</th>\n",
       "      <th>study_date_anon</th>\n",
       "      <th>desc</th>\n",
       "      <th>side</th>\n",
       "      <th>asses</th>\n",
       "      <th>path_severity</th>\n",
       "      <th>bside</th>\n",
       "      <th>procdate_anon</th>\n",
       "      <th>pdate_anon</th>\n",
       "      <th>exam_laterality</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>173182</th>\n",
       "      <td>10000865</td>\n",
       "      <td>3629273638679348</td>\n",
       "      <td>2013-01-07</td>\n",
       "      <td>MG Screening Bilateral</td>\n",
       "      <td>L</td>\n",
       "      <td>B</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>173182</th>\n",
       "      <td>10000865</td>\n",
       "      <td>3629273638679348</td>\n",
       "      <td>2013-01-07</td>\n",
       "      <td>MG Screening Bilateral</td>\n",
       "      <td>R</td>\n",
       "      <td>B</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31918</th>\n",
       "      <td>10000879</td>\n",
       "      <td>6992096043050201</td>\n",
       "      <td>2018-02-16</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>L</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31918</th>\n",
       "      <td>10000879</td>\n",
       "      <td>6992096043050201</td>\n",
       "      <td>2018-02-16</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>R</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42024</th>\n",
       "      <td>10000879</td>\n",
       "      <td>8162137067574239</td>\n",
       "      <td>2020-10-14</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>L</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26104</th>\n",
       "      <td>99999564</td>\n",
       "      <td>4369225803558884</td>\n",
       "      <td>2017-04-25</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>L</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26104</th>\n",
       "      <td>99999564</td>\n",
       "      <td>4369225803558884</td>\n",
       "      <td>2017-04-25</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>R</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31922</th>\n",
       "      <td>99999564</td>\n",
       "      <td>8832872399780580</td>\n",
       "      <td>2019-02-27</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>L</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31922</th>\n",
       "      <td>99999564</td>\n",
       "      <td>8832872399780580</td>\n",
       "      <td>2019-02-27</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>R</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127092</th>\n",
       "      <td>99999856</td>\n",
       "      <td>1897262968910242</td>\n",
       "      <td>2020-01-29</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>L</td>\n",
       "      <td>A</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>528630 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       empi_anon          acc_anon study_date_anon  \\\n",
       "173182  10000865  3629273638679348      2013-01-07   \n",
       "173182  10000865  3629273638679348      2013-01-07   \n",
       "31918   10000879  6992096043050201      2018-02-16   \n",
       "31918   10000879  6992096043050201      2018-02-16   \n",
       "42024   10000879  8162137067574239      2020-10-14   \n",
       "...          ...               ...             ...   \n",
       "26104   99999564  4369225803558884      2017-04-25   \n",
       "26104   99999564  4369225803558884      2017-04-25   \n",
       "31922   99999564  8832872399780580      2019-02-27   \n",
       "31922   99999564  8832872399780580      2019-02-27   \n",
       "127092  99999856  1897262968910242      2020-01-29   \n",
       "\n",
       "                                            desc side asses path_severity  \\\n",
       "173182                    MG Screening Bilateral    L     B           NaN   \n",
       "173182                    MG Screening Bilateral    R     B           NaN   \n",
       "31918   MG Screen Bilat w/Tomo/CAD Stnd Protocol    L     N           NaN   \n",
       "31918   MG Screen Bilat w/Tomo/CAD Stnd Protocol    R     N           NaN   \n",
       "42024   MG Screen Bilat w/Tomo/CAD Stnd Protocol    L     N           NaN   \n",
       "...                                          ...  ...   ...           ...   \n",
       "26104   MG Screen Bilat w/Tomo/CAD Stnd Protocol    L     N           NaN   \n",
       "26104   MG Screen Bilat w/Tomo/CAD Stnd Protocol    R     N           NaN   \n",
       "31922   MG Screen Bilat w/Tomo/CAD Stnd Protocol    L     N           NaN   \n",
       "31922   MG Screen Bilat w/Tomo/CAD Stnd Protocol    R     N           NaN   \n",
       "127092  MG Screen Bilat w/Tomo/CAD Stnd Protocol    L     A           NaN   \n",
       "\n",
       "       bside procdate_anon pdate_anon exam_laterality  \n",
       "173182   NaN           NaN        NaN               B  \n",
       "173182   NaN           NaN        NaN               B  \n",
       "31918    NaN           NaN        NaN               B  \n",
       "31918    NaN           NaN        NaN               B  \n",
       "42024    NaN           NaN        NaN               B  \n",
       "...      ...           ...        ...             ...  \n",
       "26104    NaN           NaN        NaN               B  \n",
       "26104    NaN           NaN        NaN               B  \n",
       "31922    NaN           NaN        NaN               B  \n",
       "31922    NaN           NaN        NaN               B  \n",
       "127092   NaN           NaN        NaN               B  \n",
       "\n",
       "[528630 rows x 11 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "screening_magview = screening_magview.sort_values([\"empi_anon\", \"acc_anon\", \"study_date_anon\"]).drop_duplicates()\n",
    "screening_magview"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>empi_anon</th>\n",
       "      <th>acc_anon</th>\n",
       "      <th>study_date_anon</th>\n",
       "      <th>desc</th>\n",
       "      <th>side</th>\n",
       "      <th>asses</th>\n",
       "      <th>path_severity</th>\n",
       "      <th>bside</th>\n",
       "      <th>procdate_anon</th>\n",
       "      <th>pdate_anon</th>\n",
       "      <th>exam_laterality</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>344811</th>\n",
       "      <td>47494328</td>\n",
       "      <td>2099425055413879</td>\n",
       "      <td>2013-11-21</td>\n",
       "      <td>MG Screening Bilateral w/CAD</td>\n",
       "      <td>L</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>255272</th>\n",
       "      <td>40212230</td>\n",
       "      <td>5767382270904809</td>\n",
       "      <td>2021-05-07</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>R</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       empi_anon          acc_anon study_date_anon  \\\n",
       "344811  47494328  2099425055413879      2013-11-21   \n",
       "255272  40212230  5767382270904809      2021-05-07   \n",
       "\n",
       "                                            desc side asses path_severity  \\\n",
       "344811              MG Screening Bilateral w/CAD    L     N           NaN   \n",
       "255272  MG Screen Bilat w/Tomo/CAD Stnd Protocol    R     N           NaN   \n",
       "\n",
       "       bside procdate_anon pdate_anon exam_laterality  \n",
       "344811   NaN           NaN        NaN               B  \n",
       "255272   NaN           NaN        NaN               B  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "exam_lat_b = screening_magview.loc[screening_magview.exam_laterality==\"B\"]\n",
    "exam_lat_b.sample(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>acc_anon</th>\n",
       "      <th>side</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>235918</th>\n",
       "      <td>8473976286914433</td>\n",
       "      <td>LR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48434</th>\n",
       "      <td>2539713197479326</td>\n",
       "      <td>LR</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                acc_anon side\n",
       "235918  8473976286914433   LR\n",
       "48434   2539713197479326   LR"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# We want to aggregate all the sides for each bilateral exam so that we can filter those having only a single side.\n",
    "exam_lat_b_agg = exam_lat_b.groupby('acc_anon')['side'].apply(''.join).reset_index()\n",
    "exam_lat_b_agg.sample(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "LR         225476\n",
       "L           26501\n",
       "R           26219\n",
       "RL           3200\n",
       "LL            634\n",
       "RR            603\n",
       "LLR           400\n",
       "LRR           199\n",
       "RLR           185\n",
       "LLRR           99\n",
       "RLL            56\n",
       "RRL            53\n",
       "LLL            45\n",
       "RRR            42\n",
       "LRLR           28\n",
       "RLLR           24\n",
       "LRL            23\n",
       "RRLL           15\n",
       "LLLR            8\n",
       "RRRR            6\n",
       "RRRL            5\n",
       "RRLR            4\n",
       "LLLRRR          4\n",
       "RRLLL           3\n",
       "RLLL            3\n",
       "LRRR            3\n",
       "LLLL            2\n",
       "LRLL            2\n",
       "LRRLR           2\n",
       "LLRL            2\n",
       "RRRLL           1\n",
       "LLLRLR          1\n",
       "RRLLR           1\n",
       "LLRRR           1\n",
       "LLLLLR          1\n",
       "LRLRR           1\n",
       "LLLLRR          1\n",
       "LRRRR           1\n",
       "LLLRR           1\n",
       "RLRR            1\n",
       "LRRLL           1\n",
       "RRLLLRR         1\n",
       "RRRLLL          1\n",
       "RRRRR           1\n",
       "LRRL            1\n",
       "LRLRLR          1\n",
       "Name: side, dtype: int64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "exam_lat_b_agg.side.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "exam_lat_b_side_r = exam_lat_b_agg.loc[~(exam_lat_b_agg.side.str.contains(\"L\"))].copy()\n",
    "exam_lat_b_side_l = exam_lat_b_agg.loc[~(exam_lat_b_agg.side.str.contains(\"R\"))].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "screening_magview_right_to_left = screening_magview.loc[screening_magview.acc_anon.isin(exam_lat_b_side_r.acc_anon)].copy().drop_duplicates()\n",
    "screening_magview_left_to_right = screening_magview.loc[screening_magview.acc_anon.isin(exam_lat_b_side_l.acc_anon)].copy().drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>empi_anon</th>\n",
       "      <th>acc_anon</th>\n",
       "      <th>study_date_anon</th>\n",
       "      <th>desc</th>\n",
       "      <th>side</th>\n",
       "      <th>asses</th>\n",
       "      <th>path_severity</th>\n",
       "      <th>bside</th>\n",
       "      <th>procdate_anon</th>\n",
       "      <th>pdate_anon</th>\n",
       "      <th>exam_laterality</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>161468</th>\n",
       "      <td>10001945</td>\n",
       "      <td>8351351741085824</td>\n",
       "      <td>2017-08-02</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>L</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37799</th>\n",
       "      <td>10033806</td>\n",
       "      <td>1069386741434572</td>\n",
       "      <td>2019-10-05</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>L</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42125</th>\n",
       "      <td>10033806</td>\n",
       "      <td>9337281572732866</td>\n",
       "      <td>2020-10-08</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>L</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>386461</th>\n",
       "      <td>10043340</td>\n",
       "      <td>8892551633769298</td>\n",
       "      <td>2020-02-06</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>L</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66818</th>\n",
       "      <td>10043985</td>\n",
       "      <td>1960584382049532</td>\n",
       "      <td>2018-04-18</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>L</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>283165</th>\n",
       "      <td>99975030</td>\n",
       "      <td>2279566061761287</td>\n",
       "      <td>2017-08-07</td>\n",
       "      <td>MG Screening Bilateral</td>\n",
       "      <td>L</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>345671</th>\n",
       "      <td>99980000</td>\n",
       "      <td>4817391795521519</td>\n",
       "      <td>2014-02-03</td>\n",
       "      <td>MG Screening Bilateral w/CAD</td>\n",
       "      <td>L</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155650</th>\n",
       "      <td>99982588</td>\n",
       "      <td>8416878517191964</td>\n",
       "      <td>2018-02-26</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>L</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>404779</th>\n",
       "      <td>99991060</td>\n",
       "      <td>5463528102254256</td>\n",
       "      <td>2016-03-08</td>\n",
       "      <td>MG Screening Bilateral</td>\n",
       "      <td>L</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>2016-03-28</td>\n",
       "      <td>2016-03-29 00:00:00</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>200795</th>\n",
       "      <td>99993980</td>\n",
       "      <td>8474749211883423</td>\n",
       "      <td>2019-03-21</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>L</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>27580 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       empi_anon          acc_anon study_date_anon  \\\n",
       "161468  10001945  8351351741085824      2017-08-02   \n",
       "37799   10033806  1069386741434572      2019-10-05   \n",
       "42125   10033806  9337281572732866      2020-10-08   \n",
       "386461  10043340  8892551633769298      2020-02-06   \n",
       "66818   10043985  1960584382049532      2018-04-18   \n",
       "...          ...               ...             ...   \n",
       "283165  99975030  2279566061761287      2017-08-07   \n",
       "345671  99980000  4817391795521519      2014-02-03   \n",
       "155650  99982588  8416878517191964      2018-02-26   \n",
       "404779  99991060  5463528102254256      2016-03-08   \n",
       "200795  99993980  8474749211883423      2019-03-21   \n",
       "\n",
       "                                            desc side asses path_severity  \\\n",
       "161468  MG Screen Bilat w/Tomo/CAD Stnd Protocol    L     N           NaN   \n",
       "37799   MG Screen Bilat w/Tomo/CAD Stnd Protocol    L     N           NaN   \n",
       "42125   MG Screen Bilat w/Tomo/CAD Stnd Protocol    L     N           NaN   \n",
       "386461  MG Screen Bilat w/Tomo/CAD Stnd Protocol    L     N           NaN   \n",
       "66818   MG Screen Bilat w/Tomo/CAD Stnd Protocol    L     N           NaN   \n",
       "...                                          ...  ...   ...           ...   \n",
       "283165                    MG Screening Bilateral    L     N           NaN   \n",
       "345671              MG Screening Bilateral w/CAD    L     N           NaN   \n",
       "155650  MG Screen Bilat w/Tomo/CAD Stnd Protocol    L     N           NaN   \n",
       "404779                    MG Screening Bilateral    L     N           NaN   \n",
       "200795  MG Screen Bilat w/Tomo/CAD Stnd Protocol    L     N           NaN   \n",
       "\n",
       "       bside procdate_anon           pdate_anon exam_laterality  \n",
       "161468   NaN           NaN                  NaN               B  \n",
       "37799    NaN           NaN                  NaN               B  \n",
       "42125    NaN           NaN                  NaN               B  \n",
       "386461   NaN           NaN                  NaN               B  \n",
       "66818    NaN           NaN                  NaN               B  \n",
       "...      ...           ...                  ...             ...  \n",
       "283165   NaN           NaN                  NaN               B  \n",
       "345671   NaN           NaN                  NaN               B  \n",
       "155650   NaN           NaN                  NaN               B  \n",
       "404779     R    2016-03-28  2016-03-29 00:00:00               B  \n",
       "200795   NaN           NaN                  NaN               B  \n",
       "\n",
       "[27580 rows x 11 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Creating the negative Left side\n",
    "screening_magview_right_to_left.loc[screening_magview_right_to_left.side==\"R\", \"side\"] = \"L\"\n",
    "screening_magview_right_to_left.loc[screening_magview_right_to_left.side==\"L\", \"asses\"] = \"N\"\n",
    "screening_magview_right_to_left.loc[screening_magview_right_to_left.side==\"L\", \"path_severity\"] = np.nan\n",
    "\n",
    "screening_magview_right_to_left"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>empi_anon</th>\n",
       "      <th>acc_anon</th>\n",
       "      <th>study_date_anon</th>\n",
       "      <th>desc</th>\n",
       "      <th>side</th>\n",
       "      <th>asses</th>\n",
       "      <th>path_severity</th>\n",
       "      <th>bside</th>\n",
       "      <th>procdate_anon</th>\n",
       "      <th>pdate_anon</th>\n",
       "      <th>exam_laterality</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>266782</th>\n",
       "      <td>10002247</td>\n",
       "      <td>4667123741504672</td>\n",
       "      <td>2015-02-04</td>\n",
       "      <td>MG Screening Bilateral</td>\n",
       "      <td>R</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>391750</th>\n",
       "      <td>10010842</td>\n",
       "      <td>2152357467079057</td>\n",
       "      <td>2014-07-05</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>R</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>L</td>\n",
       "      <td>2014-07-18</td>\n",
       "      <td>2014-07-18 00:00:00</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20834</th>\n",
       "      <td>10015693</td>\n",
       "      <td>1334581155737139</td>\n",
       "      <td>2015-10-11</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>R</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>403309</th>\n",
       "      <td>10019705</td>\n",
       "      <td>1199638332134935</td>\n",
       "      <td>2016-06-08</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>R</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>425750</th>\n",
       "      <td>10019705</td>\n",
       "      <td>3384492963323757</td>\n",
       "      <td>2020-07-19</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>R</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>263656</th>\n",
       "      <td>99985035</td>\n",
       "      <td>9268129462152986</td>\n",
       "      <td>2014-09-11</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>R</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29702</th>\n",
       "      <td>99986224</td>\n",
       "      <td>8107409307566891</td>\n",
       "      <td>2018-05-22</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>R</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>240347</th>\n",
       "      <td>99986388</td>\n",
       "      <td>1665558280896604</td>\n",
       "      <td>2017-10-01</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>R</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202020</th>\n",
       "      <td>99997273</td>\n",
       "      <td>2996543584580499</td>\n",
       "      <td>2018-09-13</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>R</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127092</th>\n",
       "      <td>99999856</td>\n",
       "      <td>1897262968910242</td>\n",
       "      <td>2020-01-29</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>R</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>27912 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       empi_anon          acc_anon study_date_anon  \\\n",
       "266782  10002247  4667123741504672      2015-02-04   \n",
       "391750  10010842  2152357467079057      2014-07-05   \n",
       "20834   10015693  1334581155737139      2015-10-11   \n",
       "403309  10019705  1199638332134935      2016-06-08   \n",
       "425750  10019705  3384492963323757      2020-07-19   \n",
       "...          ...               ...             ...   \n",
       "263656  99985035  9268129462152986      2014-09-11   \n",
       "29702   99986224  8107409307566891      2018-05-22   \n",
       "240347  99986388  1665558280896604      2017-10-01   \n",
       "202020  99997273  2996543584580499      2018-09-13   \n",
       "127092  99999856  1897262968910242      2020-01-29   \n",
       "\n",
       "                                            desc side asses path_severity  \\\n",
       "266782                    MG Screening Bilateral    R     N           NaN   \n",
       "391750  MG Screen Bilat w/Tomo/CAD Stnd Protocol    R     N           NaN   \n",
       "20834   MG Screen Bilat w/Tomo/CAD Stnd Protocol    R     N           NaN   \n",
       "403309  MG Screen Bilat w/Tomo/CAD Stnd Protocol    R     N           NaN   \n",
       "425750  MG Screen Bilat w/Tomo/CAD Stnd Protocol    R     N           NaN   \n",
       "...                                          ...  ...   ...           ...   \n",
       "263656  MG Screen Bilat w/Tomo/CAD Stnd Protocol    R     N           NaN   \n",
       "29702   MG Screen Bilat w/Tomo/CAD Stnd Protocol    R     N           NaN   \n",
       "240347  MG Screen Bilat w/Tomo/CAD Stnd Protocol    R     N           NaN   \n",
       "202020  MG Screen Bilat w/Tomo/CAD Stnd Protocol    R     N           NaN   \n",
       "127092  MG Screen Bilat w/Tomo/CAD Stnd Protocol    R     N           NaN   \n",
       "\n",
       "       bside procdate_anon           pdate_anon exam_laterality  \n",
       "266782   NaN           NaN                  NaN               B  \n",
       "391750     L    2014-07-18  2014-07-18 00:00:00               B  \n",
       "20834    NaN           NaN                  NaN               B  \n",
       "403309   NaN           NaN                  NaN               B  \n",
       "425750   NaN           NaN                  NaN               B  \n",
       "...      ...           ...                  ...             ...  \n",
       "263656   NaN           NaN                  NaN               B  \n",
       "29702    NaN           NaN                  NaN               B  \n",
       "240347   NaN           NaN                  NaN               B  \n",
       "202020   NaN           NaN                  NaN               B  \n",
       "127092   NaN           NaN                  NaN               B  \n",
       "\n",
       "[27912 rows x 11 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Creating the negative Right side\n",
    "screening_magview_left_to_right.loc[screening_magview_left_to_right.side==\"L\", \"side\"] = \"R\"\n",
    "screening_magview_left_to_right.loc[screening_magview_left_to_right.side==\"R\", \"asses\"] = \"N\"\n",
    "screening_magview_left_to_right.loc[screening_magview_left_to_right.side==\"R\", \"path_severity\"] = np.nan\n",
    "\n",
    "screening_magview_left_to_right"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>empi_anon</th>\n",
       "      <th>acc_anon</th>\n",
       "      <th>study_date_anon</th>\n",
       "      <th>desc</th>\n",
       "      <th>side</th>\n",
       "      <th>asses</th>\n",
       "      <th>path_severity</th>\n",
       "      <th>bside</th>\n",
       "      <th>procdate_anon</th>\n",
       "      <th>pdate_anon</th>\n",
       "      <th>exam_laterality</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>296187</th>\n",
       "      <td>44132453</td>\n",
       "      <td>1473567328233293</td>\n",
       "      <td>2019-08-11</td>\n",
       "      <td>MG Screening Bilateral</td>\n",
       "      <td>L</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>322144</th>\n",
       "      <td>20812558</td>\n",
       "      <td>3290510072755021</td>\n",
       "      <td>2016-03-01</td>\n",
       "      <td>MG Screening Bilateral</td>\n",
       "      <td>R</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       empi_anon          acc_anon study_date_anon                    desc  \\\n",
       "296187  44132453  1473567328233293      2019-08-11  MG Screening Bilateral   \n",
       "322144  20812558  3290510072755021      2016-03-01  MG Screening Bilateral   \n",
       "\n",
       "       side asses path_severity bside procdate_anon pdate_anon exam_laterality  \n",
       "296187    L     N           NaN   NaN           NaN        NaN               B  \n",
       "322144    R     N           NaN   NaN           NaN        NaN               B  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merging the original and the two negative contralaterals\n",
    "screening_magview_with_contralat = pd.concat([screening_magview, screening_magview_left_to_right, screening_magview_right_to_left]).sort_values([\"empi_anon\", \"acc_anon\", \"study_date_anon\"]).drop_duplicates()\n",
    "screening_magview_with_contralat.sample(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "# PNG PATH: 1298290\n",
      "# ROI: 25879\n",
      "0.0    1274855\n",
      "1.0      21185\n",
      "2.0       2060\n",
      "3.0        186\n",
      "4.0          4\n",
      "Name: num_roi, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "get_image_stats(screening_magview_with_contralat)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.2. BIRADS 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DF shape: (56790, 11)\n",
      "# Patients: 40331\n",
      "# Cases: 46292\n",
      "\n",
      "'DataFrame' object has no attribute 'png_path'\n",
      "# PNG PATH: 123773\n",
      "# ROI: 25308\n",
      "0.0    100868\n",
      "1.0     20687\n",
      "2.0      2037\n",
      "3.0       177\n",
      "4.0         4\n",
      "Name: num_roi, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "b0 = screening_magview_with_contralat.loc[screening_magview_with_contralat.asses.isin([\"A\"])]\n",
    "\n",
    "get_stats(b0)\n",
    "get_image_stats(b0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.3. BIRADS 1, 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DF shape: (526770, 11)\n",
      "# Patients: 99792\n",
      "# Cases: 286134\n",
      "\n",
      "'DataFrame' object has no attribute 'png_path'\n",
      "# PNG PATH: 1177163\n",
      "# ROI: 1006\n",
      "0.0    1176248\n",
      "1.0        840\n",
      "2.0         59\n",
      "3.0         16\n",
      "Name: num_roi, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "b12 = screening_magview_with_contralat.loc[screening_magview_with_contralat.asses.isin([\"B\", \"N\"])]\n",
    "\n",
    "get_stats(b12)\n",
    "get_image_stats(b12)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Diagnostic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DF shape: (117842, 10)\n",
      "# Patients: 49420\n",
      "# Cases: 87494\n",
      "\n",
      "'DataFrame' object has no attribute 'png_path'\n",
      "\n",
      "Asses Counts:\n",
      "B    45655\n",
      "P    28500\n",
      "N    21452\n",
      "S    15455\n",
      "A     2908\n",
      "K     2094\n",
      "M     1667\n",
      "X      111\n",
      "Name: asses, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "diag_magview = magview.loc[magview.desc.str.contains('diag', case=False)]\n",
    "\n",
    "get_stats(diag_magview)\n",
    "print()\n",
    "print(f\"Asses Counts:\\n{diag_magview.asses.value_counts()}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Screening BIRADS 0 and Diagnostic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "b0_dx = pd.merge(b0, diag_magview, on='empi_anon', suffixes=[None, \"_dx\"])\n",
    "b0_dx = b0_dx.loc[\n",
    "    (b0_dx.side==b0_dx.side_dx)\n",
    "    | (b0_dx.side_dx==\"B\")\n",
    "    | (b0_dx.side_dx.isna())\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>empi_anon</th>\n",
       "      <th>acc_anon</th>\n",
       "      <th>study_date_anon</th>\n",
       "      <th>desc</th>\n",
       "      <th>side</th>\n",
       "      <th>asses</th>\n",
       "      <th>path_severity</th>\n",
       "      <th>bside</th>\n",
       "      <th>procdate_anon</th>\n",
       "      <th>pdate_anon</th>\n",
       "      <th>exam_laterality</th>\n",
       "      <th>acc_anon_dx</th>\n",
       "      <th>study_date_anon_dx</th>\n",
       "      <th>desc_dx</th>\n",
       "      <th>side_dx</th>\n",
       "      <th>asses_dx</th>\n",
       "      <th>path_severity_dx</th>\n",
       "      <th>bside_dx</th>\n",
       "      <th>procdate_anon_dx</th>\n",
       "      <th>pdate_anon_dx</th>\n",
       "      <th>delta_date_dx</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>111674</th>\n",
       "      <td>99071319</td>\n",
       "      <td>4291883356856064</td>\n",
       "      <td>2018-02-08</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>R</td>\n",
       "      <td>A</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "      <td>9034500297208086</td>\n",
       "      <td>2018-02-26</td>\n",
       "      <td>MG Diagnostic Right w/CAD</td>\n",
       "      <td>R</td>\n",
       "      <td>B</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       empi_anon          acc_anon study_date_anon  \\\n",
       "111674  99071319  4291883356856064      2018-02-08   \n",
       "\n",
       "                                            desc side asses path_severity  \\\n",
       "111674  MG Screen Bilat w/Tomo/CAD Stnd Protocol    R     A           NaN   \n",
       "\n",
       "       bside procdate_anon pdate_anon exam_laterality       acc_anon_dx  \\\n",
       "111674   NaN           NaN        NaN               B  9034500297208086   \n",
       "\n",
       "       study_date_anon_dx                    desc_dx side_dx asses_dx  \\\n",
       "111674         2018-02-26  MG Diagnostic Right w/CAD       R        B   \n",
       "\n",
       "       path_severity_dx bside_dx procdate_anon_dx pdate_anon_dx  delta_date_dx  \n",
       "111674              NaN      NaN              NaN           NaN             18  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Getting only subsequent diagnostic studies within 3 months\n",
    "b0_dx[\"delta_date_dx\"] = (b0_dx.study_date_anon_dx - b0_dx.study_date_anon).dt.days\n",
    "b0_dx_3mo = b0_dx.loc[b0_dx.delta_date_dx.isin(range(0, 91))]\n",
    "b0_dx_3mo.sample(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5.1. BIRADS 0 (Screening) --> BIRADS 1, 2 (Diagnostic)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DF shape: (19967, 21)\n",
      "# Patients: 15602\n",
      "# Cases: 16961\n",
      "\n",
      "'DataFrame' object has no attribute 'png_path'\n",
      "# PNG PATH: 40123\n",
      "# ROI: 8519\n",
      "0.0    32591\n",
      "1.0     6607\n",
      "2.0      864\n",
      "3.0       60\n",
      "4.0        1\n",
      "Name: num_roi, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "b0_12dx = b0_dx_3mo.loc[b0_dx_3mo.asses_dx.isin([\"N\", \"B\"])].copy()\n",
    "get_stats(b0_12dx)\n",
    "get_image_stats(b0_12dx)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5.2. BIRADS 0 (Screening) --> BIRADS 3, 4, 5, 6 (Diagnostic)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DF shape: (18343, 21)\n",
      "# Patients: 11177\n",
      "# Cases: 11710\n",
      "\n",
      "'DataFrame' object has no attribute 'png_path'\n",
      "# PNG PATH: 28625\n",
      "# ROI: 7214\n",
      "0.0    22146\n",
      "1.0     5817\n",
      "2.0      591\n",
      "3.0       69\n",
      "4.0        2\n",
      "Name: num_roi, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "b0_3456dx = b0_dx_3mo.loc[b0_dx_3mo.asses_dx.isin([\"P\", \"S\", \"M\", \"K\"])].copy()\n",
    "get_stats(b0_3456dx)\n",
    "get_image_stats(b0_3456dx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. Negative group"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DF shape: (545663, 21)\n",
      "# Patients: 100783\n",
      "# Cases: 289024\n",
      "\n",
      "'DataFrame' object has no attribute 'png_path'\n",
      "# PNG PATH: 1216280\n",
      "# ROI: 9382\n",
      "0.0    1207956\n",
      "1.0       7343\n",
      "2.0        905\n",
      "3.0         75\n",
      "4.0          1\n",
      "Name: num_roi, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# Negative group = BIRADS_12 + BIRADS_0_12dx\n",
    "neg_group = pd.concat([b12, b0_12dx])\n",
    "neg_group.drop_duplicates(inplace=True)\n",
    "\n",
    "get_stats(neg_group)\n",
    "get_image_stats(neg_group)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DF shape: (2329305, 32)\n",
      "# Patients: 99792\n",
      "# Cases: 288000\n",
      "\n",
      "'DataFrame' object has no attribute 'png_path'\n",
      "# PNG PATH: 1206833\n",
      "# ROI: 6851\n",
      "0.0    1200713\n",
      "1.0       5437\n",
      "2.0        636\n",
      "3.0         46\n",
      "4.0          1\n",
      "Name: num_roi, dtype: int64\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>empi_anon</th>\n",
       "      <th>acc_anon</th>\n",
       "      <th>study_date_anon</th>\n",
       "      <th>desc</th>\n",
       "      <th>side</th>\n",
       "      <th>asses</th>\n",
       "      <th>path_severity</th>\n",
       "      <th>bside</th>\n",
       "      <th>procdate_anon</th>\n",
       "      <th>pdate_anon</th>\n",
       "      <th>exam_laterality</th>\n",
       "      <th>acc_anon_dx</th>\n",
       "      <th>study_date_anon_dx</th>\n",
       "      <th>desc_dx</th>\n",
       "      <th>side_dx</th>\n",
       "      <th>asses_dx</th>\n",
       "      <th>path_severity_dx</th>\n",
       "      <th>bside_dx</th>\n",
       "      <th>procdate_anon_dx</th>\n",
       "      <th>pdate_anon_dx</th>\n",
       "      <th>delta_date_dx</th>\n",
       "      <th>acc_anon_1yrfu</th>\n",
       "      <th>study_date_anon_1yrfu</th>\n",
       "      <th>desc_1yrfu</th>\n",
       "      <th>side_1yrfu</th>\n",
       "      <th>asses_1yrfu</th>\n",
       "      <th>path_severity_1yrfu</th>\n",
       "      <th>bside_1yrfu</th>\n",
       "      <th>procdate_anon_1yrfu</th>\n",
       "      <th>pdate_anon_1yrfu</th>\n",
       "      <th>exam_laterality_1yrfu</th>\n",
       "      <th>delta_date_1yrfu</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1251954</th>\n",
       "      <td>34595437</td>\n",
       "      <td>3721150955690973</td>\n",
       "      <td>2013-08-08</td>\n",
       "      <td>MG Screening Bilateral w/CAD</td>\n",
       "      <td>R</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</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>4047169165502113</td>\n",
       "      <td>2016-09-02</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>R</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "      <td>1121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3488116</th>\n",
       "      <td>78037395</td>\n",
       "      <td>7295645242502555</td>\n",
       "      <td>2019-08-22</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>R</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</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>7295645242502555</td>\n",
       "      <td>2019-08-22</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>R</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        empi_anon          acc_anon study_date_anon  \\\n",
       "1251954  34595437  3721150955690973      2013-08-08   \n",
       "3488116  78037395  7295645242502555      2019-08-22   \n",
       "\n",
       "                                             desc side asses path_severity  \\\n",
       "1251954              MG Screening Bilateral w/CAD    R     N           NaN   \n",
       "3488116  MG Screen Bilat w/Tomo/CAD Stnd Protocol    R     N           NaN   \n",
       "\n",
       "        bside procdate_anon pdate_anon exam_laterality acc_anon_dx  \\\n",
       "1251954   NaN           NaN        NaN               B         NaN   \n",
       "3488116   NaN           NaN        NaN               B         NaN   \n",
       "\n",
       "        study_date_anon_dx desc_dx side_dx asses_dx path_severity_dx bside_dx  \\\n",
       "1251954                NaT     NaN     NaN      NaN              NaN      NaN   \n",
       "3488116                NaT     NaN     NaN      NaN              NaN      NaN   \n",
       "\n",
       "        procdate_anon_dx pdate_anon_dx  delta_date_dx    acc_anon_1yrfu  \\\n",
       "1251954              NaN           NaN            NaN  4047169165502113   \n",
       "3488116              NaN           NaN            NaN  7295645242502555   \n",
       "\n",
       "        study_date_anon_1yrfu                                desc_1yrfu  \\\n",
       "1251954            2016-09-02  MG Screen Bilat w/Tomo/CAD Stnd Protocol   \n",
       "3488116            2019-08-22  MG Screen Bilat w/Tomo/CAD Stnd Protocol   \n",
       "\n",
       "        side_1yrfu asses_1yrfu path_severity_1yrfu bside_1yrfu  \\\n",
       "1251954          R           N                 NaN         NaN   \n",
       "3488116          R           N                 NaN         NaN   \n",
       "\n",
       "        procdate_anon_1yrfu pdate_anon_1yrfu exam_laterality_1yrfu  \\\n",
       "1251954                 NaN              NaN                     B   \n",
       "3488116                 NaN              NaN                     B   \n",
       "\n",
       "         delta_date_1yrfu  \n",
       "1251954              1121  \n",
       "3488116                 0  "
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Include only ones with negative follow-up after 1 year\n",
    "neg_group_b12 = pd.merge(neg_group, b12, on=[\"empi_anon\"], suffixes=(None, \"_1yrfu\"))\n",
    "\n",
    "neg_group_b12 = neg_group_b12.loc[\n",
    "    (neg_group_b12.side==neg_group_b12.side_1yrfu)\n",
    "]\n",
    "\n",
    "neg_group_b12[\"delta_date_1yrfu\"] = (neg_group_b12.study_date_anon_1yrfu - neg_group_b12.study_date_anon).dt.days\n",
    "\n",
    "get_stats(neg_group_b12)\n",
    "get_image_stats(neg_group_b12)\n",
    "\n",
    "neg_group_b12.sample(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DF shape: (892822, 32)\n",
      "# Patients: 60473\n",
      "# Cases: 185352\n",
      "\n",
      "'DataFrame' object has no attribute 'png_path'\n",
      "# PNG PATH: 813568\n",
      "# ROI: 4213\n",
      "0.0    809806\n",
      "1.0      3348\n",
      "2.0       378\n",
      "3.0        35\n",
      "4.0         1\n",
      "Name: num_roi, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "neg_group_1yrfu = neg_group_b12.loc[\n",
    "    (neg_group_b12.delta_date_1yrfu > 360)\n",
    "]\n",
    "get_stats(neg_group_1yrfu)\n",
    "get_image_stats(neg_group_1yrfu)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DF shape: (346327, 32)\n",
      "# Patients: 60473\n",
      "# Cases: 185352\n",
      "\n",
      "'DataFrame' object has no attribute 'png_path'\n",
      "# PNG PATH: 813568\n",
      "# ROI: 4213\n",
      "0.0    809806\n",
      "1.0      3348\n",
      "2.0       378\n",
      "3.0        35\n",
      "4.0         1\n",
      "Name: num_roi, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "neg_group_1yrfu_first_study = neg_group_1yrfu.sort_values([\"empi_anon\", \"acc_anon\", \"study_date_anon_1yrfu\"]).drop_duplicates(subset=[\"acc_anon\", \"side\"]) # to only get the first followup study\n",
    "get_stats(neg_group_1yrfu_first_study)\n",
    "get_image_stats(neg_group_1yrfu_first_study)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4.0    172\n",
       "2.0     52\n",
       "0.0     37\n",
       "1.0     13\n",
       "3.0      2\n",
       "5.0      2\n",
       "Name: path_severity, dtype: int64"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "neg_group_1yrfu_first_study.path_severity.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Exclude any patient with any biopsy result\n",
    "neg_group_1yrfu_first_study_no_biopsy = neg_group_1yrfu_first_study.loc[neg_group_1yrfu_first_study.path_severity.isna()].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DF shape: (807779, 40)\n",
      "# Patients: 58261\n",
      "# Cases: 178934\n",
      "\n",
      "# Images: 807779\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Merging with METADATA to get the images\n",
    "neg_group_1yrfu_first_study_no_biopsy_images = pd.merge(neg_group_1yrfu_first_study_no_biopsy, meta_2d, on=[\"empi_anon\", \"acc_anon\", \"study_date_anon\"])\n",
    "neg_group_1yrfu_first_study_no_biopsy_images = neg_group_1yrfu_first_study_no_biopsy_images.loc[\n",
    "    (neg_group_1yrfu_first_study_no_biopsy_images.side == neg_group_1yrfu_first_study_no_biopsy_images.ImageLateralityFinal)\n",
    "]\n",
    "neg_group_1yrfu_first_study_no_biopsy_images.drop_duplicates(subset=\"png_path\", inplace=True)\n",
    "get_stats(neg_group_1yrfu_first_study_no_biopsy_images)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ROIs = 4089\n",
      "0    804117\n",
      "1      3269\n",
      "2       360\n",
      "3        32\n",
      "4         1\n",
      "Name: num_roi, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(f\"ROIs = {neg_group_1yrfu_first_study_no_biopsy_images.num_roi.sum()}\")\n",
    "print(neg_group_1yrfu_first_study_no_biopsy_images.num_roi.value_counts())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. Positive Group"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DF shape: (28496, 29)\n",
      "# Patients: 10557\n",
      "# Cases: 11022\n",
      "\n",
      "# Images: 28496\n",
      "\n"
     ]
    }
   ],
   "source": [
    "pos_group_images = pd.merge(b0_3456dx, meta_2d, on=[\"empi_anon\", \"acc_anon\", \"study_date_anon\"])\n",
    "pos_group_images = pos_group_images.loc[\n",
    "    (pos_group_images.side == pos_group_images.ImageLateralityFinal)\n",
    "]\n",
    "pos_group_images.drop_duplicates(subset=\"png_path\", inplace=True)\n",
    "get_stats(pos_group_images)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ROIs  = 7204\n",
      "0    22025\n",
      "1     5811\n",
      "2      589\n",
      "3       69\n",
      "4        2\n",
      "Name: num_roi, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(f\"ROIs  = {pos_group_images.num_roi.sum()}\")\n",
    "print(pos_group_images.num_roi.value_counts())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8. Excluding Images from the Negative Group that are found in the Positive Group using acc_anon and side"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "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>empi_anon</th>\n",
       "      <th>acc_anon</th>\n",
       "      <th>study_date_anon_neg</th>\n",
       "      <th>desc_neg</th>\n",
       "      <th>side</th>\n",
       "      <th>asses_neg</th>\n",
       "      <th>path_severity_neg</th>\n",
       "      <th>bside_neg</th>\n",
       "      <th>procdate_anon_neg</th>\n",
       "      <th>pdate_anon_neg</th>\n",
       "      <th>exam_laterality_neg</th>\n",
       "      <th>acc_anon_dx_neg</th>\n",
       "      <th>study_date_anon_dx_neg</th>\n",
       "      <th>desc_dx_neg</th>\n",
       "      <th>side_dx_neg</th>\n",
       "      <th>asses_dx_neg</th>\n",
       "      <th>path_severity_dx_neg</th>\n",
       "      <th>bside_dx_neg</th>\n",
       "      <th>procdate_anon_dx_neg</th>\n",
       "      <th>pdate_anon_dx_neg</th>\n",
       "      <th>delta_date_dx_neg</th>\n",
       "      <th>acc_anon_1yrfu</th>\n",
       "      <th>study_date_anon_1yrfu</th>\n",
       "      <th>desc_1yrfu</th>\n",
       "      <th>side_1yrfu</th>\n",
       "      <th>asses_1yrfu</th>\n",
       "      <th>path_severity_1yrfu</th>\n",
       "      <th>bside_1yrfu</th>\n",
       "      <th>procdate_anon_1yrfu</th>\n",
       "      <th>pdate_anon_1yrfu</th>\n",
       "      <th>exam_laterality_1yrfu</th>\n",
       "      <th>delta_date_1yrfu</th>\n",
       "      <th>ImageLateralityFinal_neg</th>\n",
       "      <th>ViewPosition_neg</th>\n",
       "      <th>FinalImageType_neg</th>\n",
       "      <th>png_path_neg</th>\n",
       "      <th>StudyDescription_neg</th>\n",
       "      <th>match_level_neg</th>\n",
       "      <th>num_roi_neg</th>\n",
       "      <th>ROI_coords_neg</th>\n",
       "      <th>study_date_anon_pos</th>\n",
       "      <th>desc_pos</th>\n",
       "      <th>asses_pos</th>\n",
       "      <th>path_severity_pos</th>\n",
       "      <th>bside_pos</th>\n",
       "      <th>procdate_anon_pos</th>\n",
       "      <th>pdate_anon_pos</th>\n",
       "      <th>exam_laterality_pos</th>\n",
       "      <th>acc_anon_dx_pos</th>\n",
       "      <th>study_date_anon_dx_pos</th>\n",
       "      <th>desc_dx_pos</th>\n",
       "      <th>side_dx_pos</th>\n",
       "      <th>asses_dx_pos</th>\n",
       "      <th>path_severity_dx_pos</th>\n",
       "      <th>bside_dx_pos</th>\n",
       "      <th>procdate_anon_dx_pos</th>\n",
       "      <th>pdate_anon_dx_pos</th>\n",
       "      <th>delta_date_dx_pos</th>\n",
       "      <th>ImageLateralityFinal_pos</th>\n",
       "      <th>ViewPosition_pos</th>\n",
       "      <th>FinalImageType_pos</th>\n",
       "      <th>png_path_pos</th>\n",
       "      <th>StudyDescription_pos</th>\n",
       "      <th>match_level_pos</th>\n",
       "      <th>num_roi_pos</th>\n",
       "      <th>ROI_coords_pos</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>760</th>\n",
       "      <td>34789855</td>\n",
       "      <td>6476608733007008</td>\n",
       "      <td>2014-04-01</td>\n",
       "      <td>MG Screening Left w/Tomo/CAD</td>\n",
       "      <td>L</td>\n",
       "      <td>A</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>L</td>\n",
       "      <td>8993781340339809</td>\n",
       "      <td>2014-04-21</td>\n",
       "      <td>MG Diagnostic Left w/CAD</td>\n",
       "      <td>L</td>\n",
       "      <td>B</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20.0</td>\n",
       "      <td>9992904429283646</td>\n",
       "      <td>2016-07-13</td>\n",
       "      <td>MG Screening Left w/Tomo/CAD</td>\n",
       "      <td>L</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>L</td>\n",
       "      <td>834</td>\n",
       "      <td>L</td>\n",
       "      <td>CC</td>\n",
       "      <td>2D</td>\n",
       "      <td>/data/mammo/png/cohort_9/extracted-images/f500...</td>\n",
       "      <td>MG Screening Left w/Tomo/CAD</td>\n",
       "      <td>[]</td>\n",
       "      <td>0</td>\n",
       "      <td>()</td>\n",
       "      <td>2014-04-01</td>\n",
       "      <td>MG Screening Left w/Tomo/CAD</td>\n",
       "      <td>A</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>L</td>\n",
       "      <td>8993781340339809</td>\n",
       "      <td>2014-04-21</td>\n",
       "      <td>MG Diagnostic Left w/CAD</td>\n",
       "      <td>L</td>\n",
       "      <td>P</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20</td>\n",
       "      <td>L</td>\n",
       "      <td>MLO</td>\n",
       "      <td>2D</td>\n",
       "      <td>/data/mammo/png/cohort_9/extracted-images/f500...</td>\n",
       "      <td>MG Screening Left w/Tomo/CAD</td>\n",
       "      <td>[]</td>\n",
       "      <td>0</td>\n",
       "      <td>()</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>360</th>\n",
       "      <td>19703500</td>\n",
       "      <td>8975983036689789</td>\n",
       "      <td>2014-12-15</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>L</td>\n",
       "      <td>B</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</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>4273910518306474</td>\n",
       "      <td>2015-12-19</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>L</td>\n",
       "      <td>B</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "      <td>369</td>\n",
       "      <td>L</td>\n",
       "      <td>MLO</td>\n",
       "      <td>2D</td>\n",
       "      <td>/data/mammo/png/cohort_1/extracted-images/52f9...</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>[]</td>\n",
       "      <td>0</td>\n",
       "      <td>()</td>\n",
       "      <td>2014-12-15</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>A</td>\n",
       "      <td>4.0</td>\n",
       "      <td>L</td>\n",
       "      <td>2014-12-25</td>\n",
       "      <td>2014-12-26 00:00:00</td>\n",
       "      <td>B</td>\n",
       "      <td>3343455609167761</td>\n",
       "      <td>2014-12-20</td>\n",
       "      <td>MG Diagnostic Left w/CAD</td>\n",
       "      <td>L</td>\n",
       "      <td>S</td>\n",
       "      <td>4.0</td>\n",
       "      <td>L</td>\n",
       "      <td>2014-12-25</td>\n",
       "      <td>2014-12-26 00:00:00</td>\n",
       "      <td>5</td>\n",
       "      <td>L</td>\n",
       "      <td>CC</td>\n",
       "      <td>2D</td>\n",
       "      <td>/data/mammo/png/cohort_1/extracted-images/52f9...</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>[]</td>\n",
       "      <td>0</td>\n",
       "      <td>()</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    empi_anon          acc_anon study_date_anon_neg  \\\n",
       "760  34789855  6476608733007008          2014-04-01   \n",
       "360  19703500  8975983036689789          2014-12-15   \n",
       "\n",
       "                                     desc_neg side asses_neg  \\\n",
       "760              MG Screening Left w/Tomo/CAD    L         A   \n",
       "360  MG Screen Bilat w/Tomo/CAD Stnd Protocol    L         B   \n",
       "\n",
       "    path_severity_neg bside_neg procdate_anon_neg pdate_anon_neg  \\\n",
       "760               NaN       NaN               NaN            NaN   \n",
       "360               NaN       NaN               NaN            NaN   \n",
       "\n",
       "    exam_laterality_neg   acc_anon_dx_neg study_date_anon_dx_neg  \\\n",
       "760                   L  8993781340339809             2014-04-21   \n",
       "360                   B               NaN                    NaT   \n",
       "\n",
       "                  desc_dx_neg side_dx_neg asses_dx_neg path_severity_dx_neg  \\\n",
       "760  MG Diagnostic Left w/CAD           L            B                  NaN   \n",
       "360                       NaN         NaN          NaN                  NaN   \n",
       "\n",
       "    bside_dx_neg procdate_anon_dx_neg pdate_anon_dx_neg  delta_date_dx_neg  \\\n",
       "760          NaN                  NaN               NaN               20.0   \n",
       "360          NaN                  NaN               NaN                NaN   \n",
       "\n",
       "       acc_anon_1yrfu study_date_anon_1yrfu  \\\n",
       "760  9992904429283646            2016-07-13   \n",
       "360  4273910518306474            2015-12-19   \n",
       "\n",
       "                                   desc_1yrfu side_1yrfu asses_1yrfu  \\\n",
       "760              MG Screening Left w/Tomo/CAD          L           N   \n",
       "360  MG Screen Bilat w/Tomo/CAD Stnd Protocol          L           B   \n",
       "\n",
       "    path_severity_1yrfu bside_1yrfu procdate_anon_1yrfu pdate_anon_1yrfu  \\\n",
       "760                 NaN         NaN                 NaN              NaN   \n",
       "360                 NaN         NaN                 NaN              NaN   \n",
       "\n",
       "    exam_laterality_1yrfu  delta_date_1yrfu ImageLateralityFinal_neg  \\\n",
       "760                     L               834                        L   \n",
       "360                     B               369                        L   \n",
       "\n",
       "    ViewPosition_neg FinalImageType_neg  \\\n",
       "760               CC                 2D   \n",
       "360              MLO                 2D   \n",
       "\n",
       "                                          png_path_neg  \\\n",
       "760  /data/mammo/png/cohort_9/extracted-images/f500...   \n",
       "360  /data/mammo/png/cohort_1/extracted-images/52f9...   \n",
       "\n",
       "                         StudyDescription_neg match_level_neg  num_roi_neg  \\\n",
       "760              MG Screening Left w/Tomo/CAD              []            0   \n",
       "360  MG Screen Bilat w/Tomo/CAD Stnd Protocol              []            0   \n",
       "\n",
       "    ROI_coords_neg study_date_anon_pos  \\\n",
       "760             ()          2014-04-01   \n",
       "360             ()          2014-12-15   \n",
       "\n",
       "                                     desc_pos asses_pos path_severity_pos  \\\n",
       "760              MG Screening Left w/Tomo/CAD         A               NaN   \n",
       "360  MG Screen Bilat w/Tomo/CAD Stnd Protocol         A               4.0   \n",
       "\n",
       "    bside_pos procdate_anon_pos       pdate_anon_pos exam_laterality_pos  \\\n",
       "760       NaN               NaN                  NaN                   L   \n",
       "360         L        2014-12-25  2014-12-26 00:00:00                   B   \n",
       "\n",
       "      acc_anon_dx_pos study_date_anon_dx_pos               desc_dx_pos  \\\n",
       "760  8993781340339809             2014-04-21  MG Diagnostic Left w/CAD   \n",
       "360  3343455609167761             2014-12-20  MG Diagnostic Left w/CAD   \n",
       "\n",
       "    side_dx_pos asses_dx_pos path_severity_dx_pos bside_dx_pos  \\\n",
       "760           L            P                  NaN          NaN   \n",
       "360           L            S                  4.0            L   \n",
       "\n",
       "    procdate_anon_dx_pos    pdate_anon_dx_pos  delta_date_dx_pos  \\\n",
       "760                  NaN                  NaN                 20   \n",
       "360           2014-12-25  2014-12-26 00:00:00                  5   \n",
       "\n",
       "    ImageLateralityFinal_pos ViewPosition_pos FinalImageType_pos  \\\n",
       "760                        L              MLO                 2D   \n",
       "360                        L               CC                 2D   \n",
       "\n",
       "                                          png_path_pos  \\\n",
       "760  /data/mammo/png/cohort_9/extracted-images/f500...   \n",
       "360  /data/mammo/png/cohort_1/extracted-images/52f9...   \n",
       "\n",
       "                         StudyDescription_pos match_level_pos  num_roi_pos  \\\n",
       "760              MG Screening Left w/Tomo/CAD              []            0   \n",
       "360  MG Screen Bilat w/Tomo/CAD Stnd Protocol              []            0   \n",
       "\n",
       "    ROI_coords_pos  \n",
       "760             ()  \n",
       "360             ()  "
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge negatives and positive groups\n",
    "neg_pos = pd.merge(neg_group_1yrfu_first_study_no_biopsy_images, pos_group_images, on=[\"empi_anon\", \"acc_anon\", \"side\"], suffixes=[\"_neg\", \"_pos\"])\n",
    "neg_pos.sample(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "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>empi_anon</th>\n",
       "      <th>acc_anon</th>\n",
       "      <th>study_date_anon_neg</th>\n",
       "      <th>desc_neg</th>\n",
       "      <th>side</th>\n",
       "      <th>asses_neg</th>\n",
       "      <th>path_severity_neg</th>\n",
       "      <th>bside_neg</th>\n",
       "      <th>procdate_anon_neg</th>\n",
       "      <th>pdate_anon_neg</th>\n",
       "      <th>exam_laterality_neg</th>\n",
       "      <th>acc_anon_dx_neg</th>\n",
       "      <th>study_date_anon_dx_neg</th>\n",
       "      <th>desc_dx_neg</th>\n",
       "      <th>side_dx_neg</th>\n",
       "      <th>asses_dx_neg</th>\n",
       "      <th>path_severity_dx_neg</th>\n",
       "      <th>bside_dx_neg</th>\n",
       "      <th>procdate_anon_dx_neg</th>\n",
       "      <th>pdate_anon_dx_neg</th>\n",
       "      <th>delta_date_dx_neg</th>\n",
       "      <th>acc_anon_1yrfu</th>\n",
       "      <th>study_date_anon_1yrfu</th>\n",
       "      <th>desc_1yrfu</th>\n",
       "      <th>side_1yrfu</th>\n",
       "      <th>asses_1yrfu</th>\n",
       "      <th>path_severity_1yrfu</th>\n",
       "      <th>bside_1yrfu</th>\n",
       "      <th>procdate_anon_1yrfu</th>\n",
       "      <th>pdate_anon_1yrfu</th>\n",
       "      <th>exam_laterality_1yrfu</th>\n",
       "      <th>delta_date_1yrfu</th>\n",
       "      <th>ImageLateralityFinal_neg</th>\n",
       "      <th>ViewPosition_neg</th>\n",
       "      <th>FinalImageType_neg</th>\n",
       "      <th>png_path_neg</th>\n",
       "      <th>StudyDescription_neg</th>\n",
       "      <th>match_level_neg</th>\n",
       "      <th>num_roi_neg</th>\n",
       "      <th>ROI_coords_neg</th>\n",
       "      <th>study_date_anon_pos</th>\n",
       "      <th>desc_pos</th>\n",
       "      <th>asses_pos</th>\n",
       "      <th>path_severity_pos</th>\n",
       "      <th>bside_pos</th>\n",
       "      <th>procdate_anon_pos</th>\n",
       "      <th>pdate_anon_pos</th>\n",
       "      <th>exam_laterality_pos</th>\n",
       "      <th>acc_anon_dx_pos</th>\n",
       "      <th>study_date_anon_dx_pos</th>\n",
       "      <th>desc_dx_pos</th>\n",
       "      <th>side_dx_pos</th>\n",
       "      <th>asses_dx_pos</th>\n",
       "      <th>path_severity_dx_pos</th>\n",
       "      <th>bside_dx_pos</th>\n",
       "      <th>procdate_anon_dx_pos</th>\n",
       "      <th>pdate_anon_dx_pos</th>\n",
       "      <th>delta_date_dx_pos</th>\n",
       "      <th>ImageLateralityFinal_pos</th>\n",
       "      <th>ViewPosition_pos</th>\n",
       "      <th>FinalImageType_pos</th>\n",
       "      <th>png_path_pos</th>\n",
       "      <th>StudyDescription_pos</th>\n",
       "      <th>match_level_pos</th>\n",
       "      <th>num_roi_pos</th>\n",
       "      <th>ROI_coords_pos</th>\n",
       "      <th>acc_anon_side</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1043</th>\n",
       "      <td>43961961</td>\n",
       "      <td>5243068495140390</td>\n",
       "      <td>2013-02-05</td>\n",
       "      <td>MG Screening Bilateral w/CAD</td>\n",
       "      <td>L</td>\n",
       "      <td>A</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "      <td>1335360084337623</td>\n",
       "      <td>2013-03-04</td>\n",
       "      <td>MG Diagnostic Left</td>\n",
       "      <td>L</td>\n",
       "      <td>B</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>27.0</td>\n",
       "      <td>4149785248271308</td>\n",
       "      <td>2016-04-30</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>L</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "      <td>1180</td>\n",
       "      <td>L</td>\n",
       "      <td>MLO</td>\n",
       "      <td>2D</td>\n",
       "      <td>/data/mammo/png/cohort_5/extracted-images/527f...</td>\n",
       "      <td>MG Screening Bilateral w/CAD</td>\n",
       "      <td>[]</td>\n",
       "      <td>0</td>\n",
       "      <td>()</td>\n",
       "      <td>2013-02-05</td>\n",
       "      <td>MG Screening Bilateral w/CAD</td>\n",
       "      <td>A</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "      <td>7485410784607630</td>\n",
       "      <td>2013-03-02</td>\n",
       "      <td>MG Diagnostic Mammo Bilateral</td>\n",
       "      <td>L</td>\n",
       "      <td>S</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>25</td>\n",
       "      <td>L</td>\n",
       "      <td>CC</td>\n",
       "      <td>2D</td>\n",
       "      <td>/data/mammo/png/cohort_5/extracted-images/527f...</td>\n",
       "      <td>MG Screening Bilateral w/CAD</td>\n",
       "      <td>[]</td>\n",
       "      <td>0</td>\n",
       "      <td>()</td>\n",
       "      <td>5243068495140390L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>399</th>\n",
       "      <td>22680085</td>\n",
       "      <td>9748736436676015</td>\n",
       "      <td>2016-07-25</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>L</td>\n",
       "      <td>A</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "      <td>9677750844499584</td>\n",
       "      <td>2016-07-31</td>\n",
       "      <td>MG Diagnostic Left</td>\n",
       "      <td>L</td>\n",
       "      <td>B</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.0</td>\n",
       "      <td>3326862674671184</td>\n",
       "      <td>2019-09-30</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>L</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "      <td>1162</td>\n",
       "      <td>L</td>\n",
       "      <td>MLO</td>\n",
       "      <td>2D</td>\n",
       "      <td>/data/mammo/png/cohort_10/extracted-images/533...</td>\n",
       "      <td>MG Screening Digital BL w/Tomo/CAD</td>\n",
       "      <td>[]</td>\n",
       "      <td>0</td>\n",
       "      <td>()</td>\n",
       "      <td>2016-07-25</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>A</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "      <td>9677750844499584</td>\n",
       "      <td>2016-07-31</td>\n",
       "      <td>MG Diagnostic Left</td>\n",
       "      <td>L</td>\n",
       "      <td>P</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6</td>\n",
       "      <td>L</td>\n",
       "      <td>CC</td>\n",
       "      <td>2D</td>\n",
       "      <td>/data/mammo/png/cohort_10/extracted-images/533...</td>\n",
       "      <td>MG Screening Digital BL w/Tomo/CAD</td>\n",
       "      <td>[]</td>\n",
       "      <td>0</td>\n",
       "      <td>()</td>\n",
       "      <td>9748736436676015L</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     empi_anon          acc_anon study_date_anon_neg  \\\n",
       "1043  43961961  5243068495140390          2013-02-05   \n",
       "399   22680085  9748736436676015          2016-07-25   \n",
       "\n",
       "                                      desc_neg side asses_neg  \\\n",
       "1043              MG Screening Bilateral w/CAD    L         A   \n",
       "399   MG Screen Bilat w/Tomo/CAD Stnd Protocol    L         A   \n",
       "\n",
       "     path_severity_neg bside_neg procdate_anon_neg pdate_anon_neg  \\\n",
       "1043               NaN       NaN               NaN            NaN   \n",
       "399                NaN       NaN               NaN            NaN   \n",
       "\n",
       "     exam_laterality_neg   acc_anon_dx_neg study_date_anon_dx_neg  \\\n",
       "1043                   B  1335360084337623             2013-03-04   \n",
       "399                    B  9677750844499584             2016-07-31   \n",
       "\n",
       "             desc_dx_neg side_dx_neg asses_dx_neg path_severity_dx_neg  \\\n",
       "1043  MG Diagnostic Left           L            B                  NaN   \n",
       "399   MG Diagnostic Left           L            B                  NaN   \n",
       "\n",
       "     bside_dx_neg procdate_anon_dx_neg pdate_anon_dx_neg  delta_date_dx_neg  \\\n",
       "1043          NaN                  NaN               NaN               27.0   \n",
       "399           NaN                  NaN               NaN                6.0   \n",
       "\n",
       "        acc_anon_1yrfu study_date_anon_1yrfu  \\\n",
       "1043  4149785248271308            2016-04-30   \n",
       "399   3326862674671184            2019-09-30   \n",
       "\n",
       "                                    desc_1yrfu side_1yrfu asses_1yrfu  \\\n",
       "1043  MG Screen Bilat w/Tomo/CAD Stnd Protocol          L           N   \n",
       "399   MG Screen Bilat w/Tomo/CAD Stnd Protocol          L           N   \n",
       "\n",
       "     path_severity_1yrfu bside_1yrfu procdate_anon_1yrfu pdate_anon_1yrfu  \\\n",
       "1043                 NaN         NaN                 NaN              NaN   \n",
       "399                  NaN         NaN                 NaN              NaN   \n",
       "\n",
       "     exam_laterality_1yrfu  delta_date_1yrfu ImageLateralityFinal_neg  \\\n",
       "1043                     B              1180                        L   \n",
       "399                      B              1162                        L   \n",
       "\n",
       "     ViewPosition_neg FinalImageType_neg  \\\n",
       "1043              MLO                 2D   \n",
       "399               MLO                 2D   \n",
       "\n",
       "                                           png_path_neg  \\\n",
       "1043  /data/mammo/png/cohort_5/extracted-images/527f...   \n",
       "399   /data/mammo/png/cohort_10/extracted-images/533...   \n",
       "\n",
       "                    StudyDescription_neg match_level_neg  num_roi_neg  \\\n",
       "1043        MG Screening Bilateral w/CAD              []            0   \n",
       "399   MG Screening Digital BL w/Tomo/CAD              []            0   \n",
       "\n",
       "     ROI_coords_neg study_date_anon_pos  \\\n",
       "1043             ()          2013-02-05   \n",
       "399              ()          2016-07-25   \n",
       "\n",
       "                                      desc_pos asses_pos path_severity_pos  \\\n",
       "1043              MG Screening Bilateral w/CAD         A               NaN   \n",
       "399   MG Screen Bilat w/Tomo/CAD Stnd Protocol         A               NaN   \n",
       "\n",
       "     bside_pos procdate_anon_pos pdate_anon_pos exam_laterality_pos  \\\n",
       "1043       NaN               NaN            NaN                   B   \n",
       "399        NaN               NaN            NaN                   B   \n",
       "\n",
       "       acc_anon_dx_pos study_date_anon_dx_pos                    desc_dx_pos  \\\n",
       "1043  7485410784607630             2013-03-02  MG Diagnostic Mammo Bilateral   \n",
       "399   9677750844499584             2016-07-31             MG Diagnostic Left   \n",
       "\n",
       "     side_dx_pos asses_dx_pos path_severity_dx_pos bside_dx_pos  \\\n",
       "1043           L            S                  NaN          NaN   \n",
       "399            L            P                  NaN          NaN   \n",
       "\n",
       "     procdate_anon_dx_pos pdate_anon_dx_pos  delta_date_dx_pos  \\\n",
       "1043                  NaN               NaN                 25   \n",
       "399                   NaN               NaN                  6   \n",
       "\n",
       "     ImageLateralityFinal_pos ViewPosition_pos FinalImageType_pos  \\\n",
       "1043                        L               CC                 2D   \n",
       "399                         L               CC                 2D   \n",
       "\n",
       "                                           png_path_pos  \\\n",
       "1043  /data/mammo/png/cohort_5/extracted-images/527f...   \n",
       "399   /data/mammo/png/cohort_10/extracted-images/533...   \n",
       "\n",
       "                    StudyDescription_pos match_level_pos  num_roi_pos  \\\n",
       "1043        MG Screening Bilateral w/CAD              []            0   \n",
       "399   MG Screening Digital BL w/Tomo/CAD              []            0   \n",
       "\n",
       "     ROI_coords_pos      acc_anon_side  \n",
       "1043             ()  5243068495140390L  \n",
       "399              ()  9748736436676015L  "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create new KeyID of acc_anon + side on negative group and negative+positive group\n",
    "neg_pos[\"acc_anon_side\"] = neg_pos.acc_anon + neg_pos.side\n",
    "neg_pos.sample(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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>empi_anon</th>\n",
       "      <th>acc_anon</th>\n",
       "      <th>study_date_anon</th>\n",
       "      <th>desc</th>\n",
       "      <th>side</th>\n",
       "      <th>asses</th>\n",
       "      <th>path_severity</th>\n",
       "      <th>bside</th>\n",
       "      <th>procdate_anon</th>\n",
       "      <th>pdate_anon</th>\n",
       "      <th>exam_laterality</th>\n",
       "      <th>acc_anon_dx</th>\n",
       "      <th>study_date_anon_dx</th>\n",
       "      <th>desc_dx</th>\n",
       "      <th>side_dx</th>\n",
       "      <th>asses_dx</th>\n",
       "      <th>path_severity_dx</th>\n",
       "      <th>bside_dx</th>\n",
       "      <th>procdate_anon_dx</th>\n",
       "      <th>pdate_anon_dx</th>\n",
       "      <th>delta_date_dx</th>\n",
       "      <th>acc_anon_1yrfu</th>\n",
       "      <th>study_date_anon_1yrfu</th>\n",
       "      <th>desc_1yrfu</th>\n",
       "      <th>side_1yrfu</th>\n",
       "      <th>asses_1yrfu</th>\n",
       "      <th>path_severity_1yrfu</th>\n",
       "      <th>bside_1yrfu</th>\n",
       "      <th>procdate_anon_1yrfu</th>\n",
       "      <th>pdate_anon_1yrfu</th>\n",
       "      <th>exam_laterality_1yrfu</th>\n",
       "      <th>delta_date_1yrfu</th>\n",
       "      <th>ImageLateralityFinal</th>\n",
       "      <th>ViewPosition</th>\n",
       "      <th>FinalImageType</th>\n",
       "      <th>png_path</th>\n",
       "      <th>StudyDescription</th>\n",
       "      <th>match_level</th>\n",
       "      <th>num_roi</th>\n",
       "      <th>ROI_coords</th>\n",
       "      <th>acc_anon_side</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>885604</th>\n",
       "      <td>59718570</td>\n",
       "      <td>2415163519747488</td>\n",
       "      <td>2014-06-17</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>L</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</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>6969519729345303</td>\n",
       "      <td>2015-06-17</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>L</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "      <td>365</td>\n",
       "      <td>L</td>\n",
       "      <td>CC</td>\n",
       "      <td>2D</td>\n",
       "      <td>/data/mammo/png/cohort_7/extracted-images/5867...</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>[]</td>\n",
       "      <td>0</td>\n",
       "      <td>()</td>\n",
       "      <td>2415163519747488L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1279860</th>\n",
       "      <td>81763266</td>\n",
       "      <td>2409705155300596</td>\n",
       "      <td>2018-12-27</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>L</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</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>2090480540250219</td>\n",
       "      <td>2020-05-17</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>L</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "      <td>507</td>\n",
       "      <td>L</td>\n",
       "      <td>CC</td>\n",
       "      <td>2D</td>\n",
       "      <td>/data/mammo/png/cohort_8/extracted-images/8be0...</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>[]</td>\n",
       "      <td>0</td>\n",
       "      <td>()</td>\n",
       "      <td>2409705155300596L</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        empi_anon          acc_anon study_date_anon  \\\n",
       "885604   59718570  2415163519747488      2014-06-17   \n",
       "1279860  81763266  2409705155300596      2018-12-27   \n",
       "\n",
       "                                             desc side asses path_severity  \\\n",
       "885604   MG Screen Bilat w/Tomo/CAD Stnd Protocol    L     N           NaN   \n",
       "1279860  MG Screen Bilat w/Tomo/CAD Stnd Protocol    L     N           NaN   \n",
       "\n",
       "        bside procdate_anon pdate_anon exam_laterality acc_anon_dx  \\\n",
       "885604    NaN           NaN        NaN               B         NaN   \n",
       "1279860   NaN           NaN        NaN               B         NaN   \n",
       "\n",
       "        study_date_anon_dx desc_dx side_dx asses_dx path_severity_dx bside_dx  \\\n",
       "885604                 NaT     NaN     NaN      NaN              NaN      NaN   \n",
       "1279860                NaT     NaN     NaN      NaN              NaN      NaN   \n",
       "\n",
       "        procdate_anon_dx pdate_anon_dx  delta_date_dx    acc_anon_1yrfu  \\\n",
       "885604               NaN           NaN            NaN  6969519729345303   \n",
       "1279860              NaN           NaN            NaN  2090480540250219   \n",
       "\n",
       "        study_date_anon_1yrfu                                desc_1yrfu  \\\n",
       "885604             2015-06-17  MG Screen Bilat w/Tomo/CAD Stnd Protocol   \n",
       "1279860            2020-05-17  MG Screen Bilat w/Tomo/CAD Stnd Protocol   \n",
       "\n",
       "        side_1yrfu asses_1yrfu path_severity_1yrfu bside_1yrfu  \\\n",
       "885604           L           N                 NaN         NaN   \n",
       "1279860          L           N                 NaN         NaN   \n",
       "\n",
       "        procdate_anon_1yrfu pdate_anon_1yrfu exam_laterality_1yrfu  \\\n",
       "885604                  NaN              NaN                     B   \n",
       "1279860                 NaN              NaN                     B   \n",
       "\n",
       "         delta_date_1yrfu ImageLateralityFinal ViewPosition FinalImageType  \\\n",
       "885604                365                    L           CC             2D   \n",
       "1279860               507                    L           CC             2D   \n",
       "\n",
       "                                                  png_path  \\\n",
       "885604   /data/mammo/png/cohort_7/extracted-images/5867...   \n",
       "1279860  /data/mammo/png/cohort_8/extracted-images/8be0...   \n",
       "\n",
       "                                 StudyDescription match_level  num_roi  \\\n",
       "885604   MG Screen Bilat w/Tomo/CAD Stnd Protocol          []        0   \n",
       "1279860  MG Screen Bilat w/Tomo/CAD Stnd Protocol          []        0   \n",
       "\n",
       "        ROI_coords      acc_anon_side  \n",
       "885604          ()  2415163519747488L  \n",
       "1279860         ()  2409705155300596L  "
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "neg_group_1yrfu_first_study_no_biopsy_images[\"acc_anon_side\"] = neg_group_1yrfu_first_study_no_biopsy_images.acc_anon + neg_group_1yrfu_first_study_no_biopsy_images.side\n",
    "neg_group_1yrfu_first_study_no_biopsy_images.sample(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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>empi_anon</th>\n",
       "      <th>acc_anon</th>\n",
       "      <th>study_date_anon</th>\n",
       "      <th>desc</th>\n",
       "      <th>side</th>\n",
       "      <th>asses</th>\n",
       "      <th>path_severity</th>\n",
       "      <th>bside</th>\n",
       "      <th>procdate_anon</th>\n",
       "      <th>pdate_anon</th>\n",
       "      <th>exam_laterality</th>\n",
       "      <th>acc_anon_dx</th>\n",
       "      <th>study_date_anon_dx</th>\n",
       "      <th>desc_dx</th>\n",
       "      <th>side_dx</th>\n",
       "      <th>asses_dx</th>\n",
       "      <th>path_severity_dx</th>\n",
       "      <th>bside_dx</th>\n",
       "      <th>procdate_anon_dx</th>\n",
       "      <th>pdate_anon_dx</th>\n",
       "      <th>delta_date_dx</th>\n",
       "      <th>acc_anon_1yrfu</th>\n",
       "      <th>study_date_anon_1yrfu</th>\n",
       "      <th>desc_1yrfu</th>\n",
       "      <th>side_1yrfu</th>\n",
       "      <th>asses_1yrfu</th>\n",
       "      <th>path_severity_1yrfu</th>\n",
       "      <th>bside_1yrfu</th>\n",
       "      <th>procdate_anon_1yrfu</th>\n",
       "      <th>pdate_anon_1yrfu</th>\n",
       "      <th>exam_laterality_1yrfu</th>\n",
       "      <th>delta_date_1yrfu</th>\n",
       "      <th>ImageLateralityFinal</th>\n",
       "      <th>ViewPosition</th>\n",
       "      <th>FinalImageType</th>\n",
       "      <th>png_path</th>\n",
       "      <th>StudyDescription</th>\n",
       "      <th>match_level</th>\n",
       "      <th>num_roi</th>\n",
       "      <th>ROI_coords</th>\n",
       "      <th>acc_anon_side</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1043917</th>\n",
       "      <td>68580524</td>\n",
       "      <td>8507037717396147</td>\n",
       "      <td>2016-12-11</td>\n",
       "      <td>MG Screening Bilateral</td>\n",
       "      <td>L</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</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>9344738692043744</td>\n",
       "      <td>2018-03-04</td>\n",
       "      <td>MG Screening Bilateral w/CAD</td>\n",
       "      <td>L</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "      <td>448</td>\n",
       "      <td>L</td>\n",
       "      <td>MLO</td>\n",
       "      <td>2D</td>\n",
       "      <td>/data/mammo/png/cohort_3/extracted-images/5940...</td>\n",
       "      <td>MG Screening Bilateral</td>\n",
       "      <td>[]</td>\n",
       "      <td>0</td>\n",
       "      <td>()</td>\n",
       "      <td>8507037717396147L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67272</th>\n",
       "      <td>13784788</td>\n",
       "      <td>9704448443602447</td>\n",
       "      <td>2019-10-23</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>R</td>\n",
       "      <td>A</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "      <td>4035300999545663</td>\n",
       "      <td>2019-11-04</td>\n",
       "      <td>MG Diagnostic Right w/CAD</td>\n",
       "      <td>R</td>\n",
       "      <td>B</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12.0</td>\n",
       "      <td>5935121117587892</td>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>R</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "      <td>495</td>\n",
       "      <td>R</td>\n",
       "      <td>MLO</td>\n",
       "      <td>2D</td>\n",
       "      <td>/data/mammo/png/cohort_3/extracted-images/dac3...</td>\n",
       "      <td>MG Screen Bilat w/Tomo/CAD Stnd Protocol</td>\n",
       "      <td>[1]</td>\n",
       "      <td>1</td>\n",
       "      <td>((1447, 171, 1905, 501),)</td>\n",
       "      <td>9704448443602447R</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        empi_anon          acc_anon study_date_anon  \\\n",
       "1043917  68580524  8507037717396147      2016-12-11   \n",
       "67272    13784788  9704448443602447      2019-10-23   \n",
       "\n",
       "                                             desc side asses path_severity  \\\n",
       "1043917                    MG Screening Bilateral    L     N           NaN   \n",
       "67272    MG Screen Bilat w/Tomo/CAD Stnd Protocol    R     A           NaN   \n",
       "\n",
       "        bside procdate_anon pdate_anon exam_laterality       acc_anon_dx  \\\n",
       "1043917   NaN           NaN        NaN               B               NaN   \n",
       "67272     NaN           NaN        NaN               B  4035300999545663   \n",
       "\n",
       "        study_date_anon_dx                    desc_dx side_dx asses_dx  \\\n",
       "1043917                NaT                        NaN     NaN      NaN   \n",
       "67272           2019-11-04  MG Diagnostic Right w/CAD       R        B   \n",
       "\n",
       "        path_severity_dx bside_dx procdate_anon_dx pdate_anon_dx  \\\n",
       "1043917              NaN      NaN              NaN           NaN   \n",
       "67272                NaN      NaN              NaN           NaN   \n",
       "\n",
       "         delta_date_dx    acc_anon_1yrfu study_date_anon_1yrfu  \\\n",
       "1043917            NaN  9344738692043744            2018-03-04   \n",
       "67272             12.0  5935121117587892            2021-03-01   \n",
       "\n",
       "                                       desc_1yrfu side_1yrfu asses_1yrfu  \\\n",
       "1043917              MG Screening Bilateral w/CAD          L           N   \n",
       "67272    MG Screen Bilat w/Tomo/CAD Stnd Protocol          R           N   \n",
       "\n",
       "        path_severity_1yrfu bside_1yrfu procdate_anon_1yrfu pdate_anon_1yrfu  \\\n",
       "1043917                 NaN         NaN                 NaN              NaN   \n",
       "67272                   NaN         NaN                 NaN              NaN   \n",
       "\n",
       "        exam_laterality_1yrfu  delta_date_1yrfu ImageLateralityFinal  \\\n",
       "1043917                     B               448                    L   \n",
       "67272                       B               495                    R   \n",
       "\n",
       "        ViewPosition FinalImageType  \\\n",
       "1043917          MLO             2D   \n",
       "67272            MLO             2D   \n",
       "\n",
       "                                                  png_path  \\\n",
       "1043917  /data/mammo/png/cohort_3/extracted-images/5940...   \n",
       "67272    /data/mammo/png/cohort_3/extracted-images/dac3...   \n",
       "\n",
       "                                 StudyDescription match_level  num_roi  \\\n",
       "1043917                    MG Screening Bilateral          []        0   \n",
       "67272    MG Screen Bilat w/Tomo/CAD Stnd Protocol         [1]        1   \n",
       "\n",
       "                        ROI_coords      acc_anon_side  \n",
       "1043917                         ()  8507037717396147L  \n",
       "67272    ((1447, 171, 1905, 501),)  9704448443602447R  "
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Removing any images that are found in the positive group from the negative group using the created KeyID (acc_anon+side)\n",
    "neg_group_final = neg_group_1yrfu_first_study_no_biopsy_images.loc[~neg_group_1yrfu_first_study_no_biopsy_images.acc_anon_side.isin(neg_pos.acc_anon_side)]\n",
    "neg_group_final.sample(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DF shape: (806676, 41)\n",
      "# Patients: 58233\n",
      "# Cases: 178854\n",
      "\n",
      "# Images: 806676\n",
      "\n",
      "ROIs  = 3878\n",
      "0    803184\n",
      "1      3136\n",
      "2       327\n",
      "3        28\n",
      "4         1\n",
      "Name: num_roi, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "get_stats(neg_group_final)\n",
    "\n",
    "print(f\"ROIs  = {neg_group_final.num_roi.sum()}\")\n",
    "print(neg_group_final.num_roi.value_counts())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9. Saving and Exporting"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "columns_to_save = [\n",
    "    'empi_anon',\n",
    "    'acc_anon',\n",
    "    'desc',\n",
    "    'asses',\n",
    "    'asses_dx',\n",
    "    'path_severity',\n",
    "    'study_date_anon',\n",
    "    'study_date_anon_dx',\n",
    "    'side',\n",
    "    'ImageLateralityFinal',\n",
    "    'bside',\n",
    "    'ViewPosition',\n",
    "    'match_level',\n",
    "    'num_roi',\n",
    "    'ROI_coords',\n",
    "    'png_path',\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "break\n",
    "neg_group_final[columns_to_save].to_csv(\"NEGATIVE_GROUP.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "ename": "SyntaxError",
     "evalue": "'break' outside loop (2687054856.py, line 1)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;36m  Cell \u001b[0;32mIn[77], line 1\u001b[0;36m\u001b[0m\n\u001b[0;31m    break\u001b[0m\n\u001b[0m    ^\u001b[0m\n\u001b[0;31mSyntaxError\u001b[0m\u001b[0;31m:\u001b[0m 'break' outside loop\n"
     ]
    }
   ],
   "source": [
    "break\n",
    "pos_group_images[columns_to_save].to_csv(\"POSITIVE_GROUP.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# END"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "zero",
   "language": "python",
   "name": "zero"
  },
  "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.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}