DHT Curation Analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "6418a650-c436-48b8-b7a7-01d66a197ad0",
   "metadata": {},
   "source": [
    "# Digital Health Curation Analysis Scripts\n",
    "## For any questions, please contact the corresponding author of the publication\n",
    "==============================================================================================\n",
    "\n",
    "### Files needed to be placed in the datapath folder specified below include:\n",
    "- Grant portfolio, with curation fields added, downloaded from iSearch Grants Module: https://itools.od.nih.gov/isearch/grants/\n",
    "    - filename labeled here as: dht_deduplicate2-iSearch_-_Grants-export_2023-03-28-11-27-03.xlsx\n",
    "    - Please see the OPA website trainings to understand how to add curation fields to your iSearch portfolio: https://itools.od.nih.gov/help/curation"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "efd8d8e7-9e40-4e13-a940-9250c4f98b60",
   "metadata": {},
   "source": [
    "## Print-out statistics below are used in Supplemental Table 6 and reported in the text of the publication"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "0cd270f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "datapath='C:\\\\Users\\\\radmantc\\\\Documents\\\\Digital Health Portfolio Analysis\\\\MasterData\\\\'#data path\n",
    "outpath='C:\\\\Users\\\\radmantc\\\\\\OneDrive - National Institutes of Health\\\\\\Digital Health Portfolio Analysis\\\\'#data path\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "f2f497b6",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\radmantc\\AppData\\Local\\anaconda3\\Lib\\site-packages\\openpyxl\\styles\\stylesheet.py:226: UserWarning: Workbook contains no default style, apply openpyxl's default\n",
      "  warn(\"Workbook contains no default style, apply openpyxl's default\")\n"
     ]
    }
   ],
   "source": [
    "#load iSearch curation portfolio results\n",
    "dfi = pd.read_excel(datapath + 'dht_deduplicate2-iSearch_-_Grants-export_2023-03-28-11-27-03.xlsx')\n",
    "dfi['Fiscal Year']=dfi['Fiscal Year'].astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "47744368-4e2e-4c2e-8ab8-b36d43af839b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['index', 'Title', 'Abstract', 'Specific Aims',\n",
       "       'Public Health Relevance', 'Condition', 'MeSH Extracted', 'Target',\n",
       "       'Chemicals & Drugs', 'RCDC Categories',\n",
       "       ...\n",
       "       'Current Tools/Approaches (leonie.misquitta@nih.gov)',\n",
       "       'FinishedCoding? (leonie.misquitta@nih.gov)',\n",
       "       'requires2ndCuration? (leonie.misquitta@nih.gov)',\n",
       "       'Notes (leonie.misquitta@nih.gov)',\n",
       "       'v1 rationaleSelect (leonie.misquitta@nih.gov)', 'Added Date',\n",
       "       'Record Index', 'curator', 'toCurate', 'toCurate2'],\n",
       "      dtype='object', length=193)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfi.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "735a28dc",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\radmantc\\AppData\\Local\\Temp\\ipykernel_4320\\1786434662.py:20: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfc['highRelTot']=tmpHigh.sum(axis=1)\n",
      "C:\\Users\\radmantc\\AppData\\Local\\Temp\\ipykernel_4320\\1786434662.py:21: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfc['poorRelTot']=tmpLow.sum(axis=1)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[WinError 183] Cannot create a file when that file already exists: 'C:\\\\Users\\\\radmantc\\\\\\\\OneDrive - National Institutes of Health\\\\\\\\Digital Health Portfolio Analysis\\\\\\\\Outputs\\\\curations\\\\'\n"
     ]
    }
   ],
   "source": [
    "#curation sensitivity:\n",
    "#print(list(dfi.columns))\n",
    "\n",
    "#knock out the non-curated files\n",
    "dfc=dfi[~dfi['Relevant? (leonie.misquitta@nih.gov)'].isnull() | \n",
    "    ~dfi['Relevant? (christopher.hartshorn@nih.gov)'].isnull() | \n",
    "    ~dfi['Relevant? (audie.atienza@nih.gov)'].isnull() | \n",
    "    ~dfi['Relevant? (thomas.radman@nih.gov)'].isnull() | \n",
    "    ~dfi['Relevant? (josh.fessel@nih.gov)'].isnull() |\n",
    "    ~dfi['Relevant? (pablo.cure@nih.gov)'].isnull()]\n",
    "\n",
    "tmp=dfc[['Relevant? (leonie.misquitta@nih.gov)',\n",
    "         'Relevant? (christopher.hartshorn@nih.gov)',\n",
    "         'Relevant? (audie.atienza@nih.gov)',\n",
    "         'Relevant? (thomas.radman@nih.gov)',\n",
    "         'Relevant? (josh.fessel@nih.gov)',\n",
    "         'Relevant? (pablo.cure@nih.gov)']]\n",
    "tmpHigh=tmp=='highRelevance'\n",
    "tmpLow=tmp=='poorRelevance'\n",
    "dfc['highRelTot']=tmpHigh.sum(axis=1)\n",
    "dfc['poorRelTot']=tmpLow.sum(axis=1)\n",
    "\n",
    "#save DB:\n",
    "import os\n",
    "try:\n",
    "    os.mkdir(outpath + '\\\\Outputs\\\\curations\\\\')\n",
    "except OSError as error:\n",
    "    print(error) \n",
    "dfc.to_csv(outpath + '\\\\Outputs\\\\curations\\\\' + 'Relevance.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "486a64f5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2\n"
     ]
    }
   ],
   "source": [
    "#tied curations:\n",
    "ties=dfc[dfc['highRelTot']==dfc['poorRelTot']]\n",
    "nTies=ties.shape[0]\n",
    "print(nTies)\n",
    "ties.to_csv(outpath + '\\\\Outputs\\\\curations\\\\' + 'tiedRelevance.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "08f42443",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total Curated  =  482 \n",
      "High Relevance =  421 \n",
      "Poor Relevance  =  59 \n",
      "Ties           =  2\n"
     ]
    }
   ],
   "source": [
    "#knockout ties and count high vs. low rel counts:\n",
    "tmp=dfc[dfc['highRelTot']!=dfc['poorRelTot']]\n",
    "nCurated=tmp.shape[0]\n",
    "nHigh=tmp[tmp['highRelTot']>tmp['poorRelTot']].shape[0]\n",
    "print('Total Curated  = ', nCurated+nTies,\n",
    "     '\\nHigh Relevance = ',nHigh,\n",
    "     '\\nPoor Relevance  = ',nCurated-nHigh,\n",
    "     '\\nTies           = ',nTies)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "dd5950ee-4ed0-4b52-aebd-958fffb466e0",
   "metadata": {},
   "outputs": [],
   "source": [
    "dfi_join2=pd.merge(dfi,dfc, how='inner')  #dfi.join(dfc[['highRelTot','poorRelTot']])\n",
    "dfi_join2.to_csv(outpath + '\\\\Outputs\\\\curations\\\\' + 'Subset_Relevance.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "967442b6",
   "metadata": {},
   "outputs": [],
   "source": [
    "#join the derived counts back to the dfi DB:\n",
    "dfi_join=pd.merge(dfi,dfc, how='left')  #dfi.join(dfc[['highRelTot','poorRelTot']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "9ad1d3a3-2b76-4888-bc34-aebb9a136daa",
   "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>highRelTot</th>\n",
       "      <th>poorRelTot</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4468</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4469</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4470</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4471</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4472</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4473 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      highRelTot  poorRelTot\n",
       "0            1.0         0.0\n",
       "1            NaN         NaN\n",
       "2            NaN         NaN\n",
       "3            NaN         NaN\n",
       "4            NaN         NaN\n",
       "...          ...         ...\n",
       "4468         NaN         NaN\n",
       "4469         NaN         NaN\n",
       "4470         NaN         NaN\n",
       "4471         NaN         NaN\n",
       "4472         NaN         NaN\n",
       "\n",
       "[4473 rows x 2 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfi_join[['highRelTot','poorRelTot']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "bc62bae0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(4473, 193)\n",
      "(482, 195)\n",
      "(4473, 195)\n",
      "(482, 195)\n"
     ]
    }
   ],
   "source": [
    "print(dfi.shape) #all grants\n",
    "print(dfc.shape) #those from all grants w relevance\n",
    "print(dfi_join.shape) #why does join have more?\n",
    "print(dfi_join2.shape) #why does join have more?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "b6f1a418",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total Curated =  332 \n",
      "n (%) Remote Patient Monitoring/Clinical Decision Support =  107  ( 32.2 ) \n",
      "n (%) Data Gathering/Database Development =  194  ( 58.4 ) \n",
      "n (%) Interventional/Digital Rx =  131  ( 39.5 )\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\radmantc\\AppData\\Local\\Temp\\ipykernel_4320\\256597059.py:11: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfc[dfc.isna()]=''\n",
      "C:\\Users\\radmantc\\AppData\\Local\\Temp\\ipykernel_4320\\256597059.py:11: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfc[dfc.isna()]=''\n",
      "C:\\Users\\radmantc\\AppData\\Local\\Temp\\ipykernel_4320\\256597059.py:13: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfc['Current Use/Focus']=dfc['Current Use/Focus (thomas.radman@nih.gov)']\\\n",
      "C:\\Users\\radmantc\\AppData\\Local\\Temp\\ipykernel_4320\\256597059.py:22: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfc['isRemote']=dfc['Current Use/Focus'].str.contains('Remote Patient Monitoring/Clinical Decision Support')\n",
      "C:\\Users\\radmantc\\AppData\\Local\\Temp\\ipykernel_4320\\256597059.py:23: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfc['isDataGath']=dfc['Current Use/Focus'].str.contains('Data Gathering/Database Development')\n",
      "C:\\Users\\radmantc\\AppData\\Local\\Temp\\ipykernel_4320\\256597059.py:24: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfc['isIntervent']=dfc['Current Use/Focus'].str.contains('Interventional/Digital Rx')\n"
     ]
    }
   ],
   "source": [
    "### figure out the solutions for the categories.\n",
    "#start by creating a column that is the concatenation of all curated values\n",
    "tmp=dfi[[ 'Current Use/Focus (thomas.radman@nih.gov)',\n",
    "         'Current Use/Focus (pablo.cure@nih.gov)',\n",
    "        'Current Use/Focus (leonie.misquitta@nih.gov)',\n",
    "        'Current Use/Focus (christopher.hartshorn@nih.gov)',\n",
    "        'Current Use/Focus (audie.atienza@nih.gov)',\n",
    "        'Current Use/Focus (josh.fessel@nih.gov)']]\n",
    "\n",
    "dfc=tmp[tmp.notna().sum(axis=1)!=0]\n",
    "dfc[dfc.isna()]=''\n",
    "sep=' '\n",
    "dfc['Current Use/Focus']=dfc['Current Use/Focus (thomas.radman@nih.gov)']\\\n",
    "+sep+dfc['Current Use/Focus (pablo.cure@nih.gov)']\\\n",
    "+sep+dfc['Current Use/Focus (leonie.misquitta@nih.gov)']\\\n",
    "+sep+dfc['Current Use/Focus (christopher.hartshorn@nih.gov)']\\\n",
    "+sep+dfc['Current Use/Focus (audie.atienza@nih.gov)']\\\n",
    "+sep+dfc['Current Use/Focus (josh.fessel@nih.gov)']\n",
    "#print(list(tmp))\n",
    "#print(dfc['Current Use/Focus'].unique())\n",
    "\n",
    "dfc['isRemote']=dfc['Current Use/Focus'].str.contains('Remote Patient Monitoring/Clinical Decision Support')\n",
    "dfc['isDataGath']=dfc['Current Use/Focus'].str.contains('Data Gathering/Database Development')\n",
    "dfc['isIntervent']=dfc['Current Use/Focus'].str.contains('Interventional/Digital Rx')\n",
    "\n",
    "dfc.to_csv(outpath + '\\\\Outputs\\\\curations\\\\' + 'useFocus.csv')\n",
    "\n",
    "ntotal = dfc.shape[0]\n",
    "nremote = dfc['isRemote'].sum()\n",
    "ndatagath = dfc['isDataGath'].sum()\n",
    "ninterv = dfc['isIntervent'].sum()\n",
    "print( 'Total Curated = ', ntotal,\n",
    "     '\\nn (%) Remote Patient Monitoring/Clinical Decision Support = ',nremote,' (', round(nremote/ntotal*100,1), ')',\n",
    "     '\\nn (%) Data Gathering/Database Development = ',ndatagath,' (', round(ndatagath/ntotal*100,1), ')',\n",
    "     '\\nn (%) Interventional/Digital Rx = ',ninterv,' (', round(ninterv/ntotal*100,1), ')', \n",
    "     )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "a0f19d6e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(4473, 198)\n"
     ]
    }
   ],
   "source": [
    "#join the derived counts back to the dfi DB:\n",
    "dfi_join=dfi_join.join(dfc[['isRemote','isDataGath','isIntervent']], how='left')  #dfi_join=dfi_join.join(dfc[['isRemote','isDataGath','isIntervent']])\n",
    "print(dfi_join.shape)\n",
    "# remove the duplicates\n",
    "dfi_join.drop_duplicates(subset=['Appl ID'], keep='first', inplace=True, ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "4ba92a56-572c-4d42-95f8-798e488a1610",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(332, 203)\n"
     ]
    }
   ],
   "source": [
    "dfi_join2=pd.merge(dfi,dfc, how='inner', left_index=True, right_index=True)  #dfi.join(dfc[['highRelTot','poorRelTot']])\n",
    "dfi_join2.to_csv(outpath + '\\\\Outputs\\\\curations\\\\' + 'Subset_useFocus.csv')\n",
    "print(dfi_join2.shape)\n",
    "\n",
    "#dfi_join2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "0e274a8d-c64b-4420-9cce-529e962ca478",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(4473, 198) 107 194 131\n"
     ]
    }
   ],
   "source": [
    "print(dfi_join.shape, dfi_join['isRemote'].sum(), dfi_join['isDataGath'].sum(), dfi_join['isIntervent'].sum() )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "033d4e8a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total Curated =  329 \n",
      "n (%) Clinical/Regulatory Validation or Interventional =  135  ( 41.0 ) \n",
      "n (%) Purely R and D (e.g. prototype builds, device testing, V and V, early IDE) =  195  ( 59.3 )\n"
     ]
    }
   ],
   "source": [
    "#next category is current stage:\n",
    "tmp=dfi[['Current Stage (thomas.radman@nih.gov)',\n",
    "         'Current Stage (pablo.cure@nih.gov)',\n",
    "        'Current Stage (leonie.misquitta@nih.gov)',\n",
    "        'Current Stage (christopher.hartshorn@nih.gov)',\n",
    "        'Current Stage (audie.atienza@nih.gov)',\n",
    "        'Current Stage (josh.fessel@nih.gov)']]\n",
    "dfc=tmp[tmp.notna().sum(axis=1)!=0]\n",
    "#dfc[dfc.isna()]=''  #for some reason this line does not work for the stage variable???\n",
    "dfc = dfc.where(dfc.notna(), other=\"\")\n",
    "\n",
    "sep=' '\n",
    "dfc['Current Stage']=dfc['Current Stage (thomas.radman@nih.gov)']\\\n",
    "+sep+dfc['Current Stage (pablo.cure@nih.gov)']\\\n",
    "+sep+dfc['Current Stage (leonie.misquitta@nih.gov)']\\\n",
    "+sep+dfc['Current Stage (christopher.hartshorn@nih.gov)']\\\n",
    "+sep+dfc['Current Stage (audie.atienza@nih.gov)']\\\n",
    "+sep+dfc['Current Stage (josh.fessel@nih.gov)']\n",
    "#print(list(tmp))\n",
    "#print(dfc['Current Stage'].unique())\n",
    "\n",
    "dfc['isClin']=dfc['Current Stage'].str.contains('Clinical/Regulatory Validation or Interventional')\n",
    "dfc['isRnD']=dfc['Current Stage'].str.contains('Purely R and D')\n",
    "\n",
    "dfc.to_csv(outpath + '\\\\Outputs\\\\curations\\\\' + 'stage.csv')\n",
    "\n",
    "ntotal = dfc.shape[0]\n",
    "nclin = dfc['isClin'].sum()\n",
    "nRnD = dfc['isRnD'].sum()\n",
    "print( 'Total Curated = ', ntotal,\n",
    "     '\\nn (%) Clinical/Regulatory Validation or Interventional = ',nclin,' (', round(nclin/ntotal*100,1), ')',\n",
    "     '\\nn (%) Purely R and D (e.g. prototype builds, device testing, V and V, early IDE) = ',nRnD,' (', round(nRnD/ntotal*100,1), ')', \n",
    "     )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "10195842",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(4473, 200)\n"
     ]
    }
   ],
   "source": [
    "#join the derived counts back to the dfi DB:\n",
    "dfi_join=dfi_join.join(dfc[['isClin','isRnD']])\n",
    "print(dfi_join.shape)\n",
    "\n",
    "# remove the duplicates\n",
    "dfi_join.drop_duplicates(subset=['Appl ID'], keep='first', inplace=True, ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "bd067c4f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(4473, 200)\n",
      "135 195\n",
      "135 195\n"
     ]
    }
   ],
   "source": [
    "print(dfi_join.shape)\n",
    "print(dfc['isClin'].sum(),  dfc['isRnD'].sum() )\n",
    "print(dfi_join['isClin'].sum(),  dfi_join['isRnD'].sum() )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "eb9b867d-da0e-4bcf-823d-2cf210fc8c8b",
   "metadata": {},
   "outputs": [],
   "source": [
    "dfi_join2=pd.merge(dfi,dfc, how='inner', left_index=True, right_index=True)  #dfi.join(dfc[['highRelTot','poorRelTot']])\n",
    "dfi_join2.to_csv(outpath + '\\\\Outputs\\\\curations\\\\' + 'Subset_stage.csv')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "9abdd763",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total Curated =  327 \n",
      "n (%) Wearable =  150  ( 45.9 ) \n",
      "n (%) Telemedicine/Telehealth =  88  ( 26.9 ) \n",
      "n (%) Mobile Apps =  158  ( 48.3 ) \n",
      "n (%) In-home monitoring or other ambient measurement tools =  62  ( 19.0 )\n"
     ]
    }
   ],
   "source": [
    "#next category is Tools:\n",
    "tmp=dfi[['Current Tools/Approaches (thomas.radman@nih.gov)',\n",
    "         'Current Tools/Approaches (pablo.cure@nih.gov)',\n",
    "        'Current Tools/Approaches (leonie.misquitta@nih.gov)',\n",
    "        'Current Tools/Approaches (christopher.hartshorn@nih.gov)',\n",
    "        'Current Tools/Approaches (audie.atienza@nih.gov)',\n",
    "        'Current Tools/Approaches (josh.fessel@nih.gov)']]\n",
    "dfc=tmp[tmp.notna().sum(axis=1)!=0]\n",
    "#dfc[dfc.isna()]=''  #for some reason this line does not work for the stage variable???\n",
    "dfc = dfc.where(dfc.notna(), other=\"\")\n",
    "\n",
    "sep=' '\n",
    "dfc['Current Tools/Approaches']=dfc['Current Tools/Approaches (thomas.radman@nih.gov)']\\\n",
    "+sep+dfc['Current Tools/Approaches (pablo.cure@nih.gov)']\\\n",
    "+sep+dfc['Current Tools/Approaches (leonie.misquitta@nih.gov)']\\\n",
    "+sep+dfc['Current Tools/Approaches (christopher.hartshorn@nih.gov)']\\\n",
    "+sep+dfc['Current Tools/Approaches (audie.atienza@nih.gov)']\\\n",
    "+sep+dfc['Current Tools/Approaches (josh.fessel@nih.gov)']\n",
    "#print(list(tmp))\n",
    "#print(dfc['Current Tools/Approaches'].unique())\n",
    "\n",
    "dfc['isWear']=dfc['Current Tools/Approaches'].str.contains('Wearable')\n",
    "dfc['isTele']=dfc['Current Tools/Approaches'].str.contains('Telemedicine/Telehealth')\n",
    "dfc['isMobile']=dfc['Current Tools/Approaches'].str.contains('Mobile Apps')\n",
    "dfc['isHome']=dfc['Current Tools/Approaches'].str.contains('In-home monitoring or other ambient measurement tools')\n",
    "\n",
    "dfc.to_csv(outpath + '\\\\Outputs\\\\curations\\\\' + 'tools.csv')\n",
    "\n",
    "ntotal = dfc.shape[0]\n",
    "nwear = dfc['isWear'].sum()\n",
    "ntele = dfc['isTele'].sum()\n",
    "nmobile = dfc['isMobile'].sum()\n",
    "nhome = dfc['isHome'].sum()\n",
    "print( 'Total Curated = ', ntotal,\n",
    "     '\\nn (%) Wearable = ',nwear,' (', round(nwear/ntotal*100,1), ')',\n",
    "     '\\nn (%) Telemedicine/Telehealth = ',ntele,' (', round(ntele/ntotal*100,1), ')',\n",
    "     '\\nn (%) Mobile Apps = ',nmobile,' (', round(nmobile/ntotal*100,1), ')',\n",
    "      '\\nn (%) In-home monitoring or other ambient measurement tools = ',nhome,' (', round(nhome/ntotal*100,1), ')' \n",
    "     )\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "4cd4a7a6",
   "metadata": {},
   "outputs": [],
   "source": [
    "#join the derived counts back to the dfi DB:\n",
    "dfi_join=dfi_join.join(dfc[['isWear','isTele','isMobile','isHome']])\n",
    "# remove the duplicates\n",
    "dfi_join.drop_duplicates(subset=['Appl ID'], keep='first', inplace=True, ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "88fa70c3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "150 88 158 62\n",
      "150 88 158 62\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(4473, 204)"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(dfc['isWear'].sum(), dfc['isTele'].sum(), dfc['isMobile'].sum(), dfc['isHome'].sum() )\n",
    "print(dfi_join['isWear'].sum(), dfi_join['isTele'].sum(), dfi_join['isMobile'].sum(), dfi_join['isHome'].sum() )\n",
    "dfi_join.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "e22d52dc-2293-4b2c-b9ba-3f3d8432677e",
   "metadata": {},
   "outputs": [],
   "source": [
    "dfi_join2=pd.merge(dfi,dfc, how='inner', left_index=True, right_index=True)  #dfi.join(dfc[['highRelTot','poorRelTot']])\n",
    "dfi_join2.to_csv(outpath + '\\\\Outputs\\\\curations\\\\' + 'Subset_tools.csv')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "6ab724c4",
   "metadata": {},
   "outputs": [],
   "source": [
    "tmp=dfi_join[['Relevant? (leonie.misquitta@nih.gov)',\n",
    "         'Relevant? (christopher.hartshorn@nih.gov)',\n",
    "         'Relevant? (audie.atienza@nih.gov)',\n",
    "         'Relevant? (thomas.radman@nih.gov)',\n",
    "         'Relevant? (josh.fessel@nih.gov)',\n",
    "         'Relevant? (pablo.cure@nih.gov)',\n",
    "         'Current Use/Focus (thomas.radman@nih.gov)',\n",
    "         'Current Use/Focus (pablo.cure@nih.gov)',\n",
    "        'Current Use/Focus (leonie.misquitta@nih.gov)',\n",
    "        'Current Use/Focus (christopher.hartshorn@nih.gov)',\n",
    "        'Current Use/Focus (audie.atienza@nih.gov)',\n",
    "        'Current Use/Focus (josh.fessel@nih.gov)',\n",
    "        'Current Stage (thomas.radman@nih.gov)',\n",
    "        'Current Stage (pablo.cure@nih.gov)',\n",
    "        'Current Stage (leonie.misquitta@nih.gov)',\n",
    "        'Current Stage (christopher.hartshorn@nih.gov)',\n",
    "        'Current Stage (audie.atienza@nih.gov)',\n",
    "        'Current Stage (josh.fessel@nih.gov)',\n",
    "        'Current Tools/Approaches (thomas.radman@nih.gov)',\n",
    "        'Current Tools/Approaches (pablo.cure@nih.gov)',\n",
    "        'Current Tools/Approaches (leonie.misquitta@nih.gov)',\n",
    "        'Current Tools/Approaches (christopher.hartshorn@nih.gov)',\n",
    "        'Current Tools/Approaches (audie.atienza@nih.gov)',\n",
    "        'Current Tools/Approaches (josh.fessel@nih.gov)']]\n",
    "dfc=dfi_join[tmp.notna().sum(axis=1)!=0]\n",
    "dfc.to_csv(outpath + '\\\\Outputs\\\\curations\\\\' + 'allCurationDerivations.csv')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "d16ee36a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(482, 204)"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfc.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "bb920fd7-9ea6-41b1-8c93-c8e5a0f3e73c",
   "metadata": {},
   "outputs": [],
   "source": [
    "tmp=dfi_join[[ 'highRelTot','poorRelTot',\n",
    "              'isClin', 'isRnD',\n",
    "              'isRemote','isDataGath','isIntervent',\n",
    "              'isWear','isTele','isMobile','isHome'\n",
    "         ]]\n",
    "dfc2=dfi_join[tmp.notna().sum(axis=1)!=0]\n",
    "dfc2.to_csv(outpath + '\\\\Outputs\\\\curations\\\\' + 'allCurationDerivations2.csv')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "74ed7a57-c747-4135-b3e4-b391b7c68412",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}