Grab_NDA_Data_Dictionary_Annotation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Get NDA data dictionary items pertaining to NIH Toolbox data and merge with Crosswalk"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "dict_keys(['shortName', 'title', 'sources', 'categories', 'dataType', 'status', 'publicStatus', 'publishDate', 'modifiedDate', 'dataElements'])\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>name</th>\n",
       "      <th>description</th>\n",
       "      <th>valueRange</th>\n",
       "      <th>notes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>subjectkey</td>\n",
       "      <td>The NDAR Global Unique Identifier (GUID) for r...</td>\n",
       "      <td>NDAR*</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>src_subject_id</td>\n",
       "      <td>Subject ID how it's defined in lab/project</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             name                                        description  \\\n",
       "0      subjectkey  The NDAR Global Unique Identifier (GUID) for r...   \n",
       "1  src_subject_id         Subject ID how it's defined in lab/project   \n",
       "\n",
       "  valueRange notes  \n",
       "0      NDAR*  None  \n",
       "1       None  None  "
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Programatically retreive data from the dictionary service for every hcp_variable that has \n",
    "# been mapped to the NDA by their data curators for line item comparisons between Toolbox Data Dicitonary and \n",
    "# NDA data dictionary\n",
    "\n",
    "\n",
    "import requests\n",
    "import json\n",
    "import pandas as pd\n",
    "\n",
    "#example to expand\n",
    "shortname = 'cogcomp01'\n",
    "varlist=['subjectkey','src_subject_id']\n",
    "r = requests.get('https://ndar.nih.gov/api/datadictionary/datastructure/{}'\n",
    "                 .format(shortname),\n",
    "                  headers={'Accept':'application/json'})\n",
    "structure = json.loads(r.text)\n",
    "print(structure.keys())\n",
    "df=pd.DataFrame(structure['dataElements'])\n",
    "df=df[['name','description','valueRange','notes']]\n",
    "dfxwalk=df.loc[df.name.isin(varlist)] \n",
    "dfxwalk\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": true
   },
   "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>validated</th>\n",
       "      <th>Measurement System</th>\n",
       "      <th>Domain</th>\n",
       "      <th>Item ID</th>\n",
       "      <th>Stem</th>\n",
       "      <th>Context</th>\n",
       "      <th>DataType</th>\n",
       "      <th>Responses</th>\n",
       "      <th>Inst</th>\n",
       "      <th>hcp_variable</th>\n",
       "      <th>...</th>\n",
       "      <th>hcp_variable_upload</th>\n",
       "      <th>nda_structure</th>\n",
       "      <th>nda_element</th>\n",
       "      <th>template</th>\n",
       "      <th>inst_short</th>\n",
       "      <th>Source</th>\n",
       "      <th>description</th>\n",
       "      <th>valueRange</th>\n",
       "      <th>notes</th>\n",
       "      <th>nda_structure_link</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Anxiety Summary Parent Report (3-7)</td>\n",
       "      <td>dummy_00095</td>\n",
       "      <td>...</td>\n",
       "      <td>nih_tlbx_rawscore</td>\n",
       "      <td>tlbx_fearanx01</td>\n",
       "      <td>nih_tlbx_rawscore</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Anxiety_Summary_3-7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>RawScore</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://nda.nih.gov/data_structure.html?short_...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Anxiety Summary Parent Report (3-7)</td>\n",
       "      <td>dummy_00096</td>\n",
       "      <td>...</td>\n",
       "      <td>nih_tlbx_theta</td>\n",
       "      <td>tlbx_fearanx01</td>\n",
       "      <td>nih_tlbx_theta</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Anxiety_Summary_3-7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Theta</td>\n",
       "      <td>NaN</td>\n",
       "      <td>999=NA/NK/missing</td>\n",
       "      <td>https://nda.nih.gov/data_structure.html?short_...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Anxiety Summary Parent Report (3-7)</td>\n",
       "      <td>dummy_00097</td>\n",
       "      <td>...</td>\n",
       "      <td>nih_tlbx_tscore</td>\n",
       "      <td>tlbx_fearanx01</td>\n",
       "      <td>nih_tlbx_tscore</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Anxiety_Summary_3-7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>T-score</td>\n",
       "      <td>0::200; 999</td>\n",
       "      <td>999=NA/NK/missing</td>\n",
       "      <td>https://nda.nih.gov/data_structure.html?short_...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Anxiety Summary Parent Report (3-7)</td>\n",
       "      <td>dummy_00098</td>\n",
       "      <td>...</td>\n",
       "      <td>nih_tlbx_se</td>\n",
       "      <td>tlbx_fearanx01</td>\n",
       "      <td>nih_tlbx_se</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Anxiety_Summary_3-7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Standard Error</td>\n",
       "      <td>NaN</td>\n",
       "      <td>999=NA/NK/missing</td>\n",
       "      <td>https://nda.nih.gov/data_structure.html?short_...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Anxiety Summary Parent Report (3-7)</td>\n",
       "      <td>dummy_00099</td>\n",
       "      <td>...</td>\n",
       "      <td>nih_tlbx_agegencsc</td>\n",
       "      <td>tlbx_fearanx01</td>\n",
       "      <td>nih_tlbx_agegencsc</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Anxiety_Summary_3-7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Age-Gender-Corrected T-score</td>\n",
       "      <td>0::250; 999</td>\n",
       "      <td>999=NA/NK/missing</td>\n",
       "      <td>https://nda.nih.gov/data_structure.html?short_...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  validated Measurement System Domain Item ID Stem Context DataType Responses  \\\n",
       "0       NaN                NaN    NaN     NaN  NaN     NaN      NaN       NaN   \n",
       "1       NaN                NaN    NaN     NaN  NaN     NaN      NaN       NaN   \n",
       "2       NaN                NaN    NaN     NaN  NaN     NaN      NaN       NaN   \n",
       "3       NaN                NaN    NaN     NaN  NaN     NaN      NaN       NaN   \n",
       "4       NaN                NaN    NaN     NaN  NaN     NaN      NaN       NaN   \n",
       "\n",
       "                                  Inst hcp_variable  ... hcp_variable_upload  \\\n",
       "0  Anxiety Summary Parent Report (3-7)  dummy_00095  ...   nih_tlbx_rawscore   \n",
       "1  Anxiety Summary Parent Report (3-7)  dummy_00096  ...      nih_tlbx_theta   \n",
       "2  Anxiety Summary Parent Report (3-7)  dummy_00097  ...     nih_tlbx_tscore   \n",
       "3  Anxiety Summary Parent Report (3-7)  dummy_00098  ...         nih_tlbx_se   \n",
       "4  Anxiety Summary Parent Report (3-7)  dummy_00099  ...  nih_tlbx_agegencsc   \n",
       "\n",
       "    nda_structure         nda_element template           inst_short Source  \\\n",
       "0  tlbx_fearanx01   nih_tlbx_rawscore      NaN  Anxiety_Summary_3-7    NaN   \n",
       "1  tlbx_fearanx01      nih_tlbx_theta      NaN  Anxiety_Summary_3-7    NaN   \n",
       "2  tlbx_fearanx01     nih_tlbx_tscore      NaN  Anxiety_Summary_3-7    NaN   \n",
       "3  tlbx_fearanx01         nih_tlbx_se      NaN  Anxiety_Summary_3-7    NaN   \n",
       "4  tlbx_fearanx01  nih_tlbx_agegencsc      NaN  Anxiety_Summary_3-7    NaN   \n",
       "\n",
       "                    description   valueRange              notes  \\\n",
       "0                      RawScore          NaN                NaN   \n",
       "1                         Theta          NaN  999=NA/NK/missing   \n",
       "2                       T-score  0::200; 999  999=NA/NK/missing   \n",
       "3                Standard Error          NaN  999=NA/NK/missing   \n",
       "4  Age-Gender-Corrected T-score  0::250; 999  999=NA/NK/missing   \n",
       "\n",
       "                                  nda_structure_link  \n",
       "0  https://nda.nih.gov/data_structure.html?short_...  \n",
       "1  https://nda.nih.gov/data_structure.html?short_...  \n",
       "2  https://nda.nih.gov/data_structure.html?short_...  \n",
       "3  https://nda.nih.gov/data_structure.html?short_...  \n",
       "4  https://nda.nih.gov/data_structure.html?short_...  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#read crosswalkfile\n",
    "pathin=\"/home/petra/UbWinSharedSpace1/ccf-nda-behavioral/PycharmToolbox/Ipad2NDA_withCrosswalk/NIHToolbox2NDA/\"\n",
    "crosswalkfile=\"Crosswalk_NIH_Toolbox_2_NDA.csv\"\n",
    "crosswalk=pd.read_csv(crosswalkfile,header=0,low_memory=False, encoding = \"ISO-8859-1\")\n",
    "crosswalk.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "#for a given structure (shortname), grab all the metadata for a list of elements as a dataframe\n",
    "def getNDAdetails(structure_name=shortname,crosswalk=crosswalk):\n",
    "    varlist=crosswalk.loc[crosswalk.nda_structure==structure_name,'nda_element'].to_list()\n",
    "    r = requests.get('https://ndar.nih.gov/api/datadictionary/datastructure/{}'\n",
    "                 .format(structure_name),\n",
    "                  headers={'Accept':'application/json'})\n",
    "    structure = json.loads(r.text)\n",
    "    df=pd.DataFrame(structure['dataElements'])\n",
    "    df2=df[['name','description','valueRange','notes']].copy()\n",
    "    dfxwalk=df2.loc[df2.name.isin(varlist)].copy() \n",
    "    dfxwalk['nda_structure']=structure_name\n",
    "    dfxwalk=dfxwalk.rename(columns={'name':'nda_element'})\n",
    "    return dfxwalk\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>nda_element</th>\n",
       "      <th>description</th>\n",
       "      <th>valueRange</th>\n",
       "      <th>notes</th>\n",
       "      <th>nda_structure</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>version_form</td>\n",
       "      <td>Form used/assessment name</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>tlbx_motor01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>en6m50_2m_10ft_marks</td>\n",
       "      <td>Endurance - 2 minute walk - Number of 10 ft marks</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>tlbx_motor01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>en6m50_2m_ft</td>\n",
       "      <td>Endurance - 2 minute walk - Feet</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>tlbx_motor01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>en6m50_2m_inches</td>\n",
       "      <td>Endurance - 2 minute walk - Inches</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>tlbx_motor01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>en6m50_2m_turns</td>\n",
       "      <td>Endurance - 2 minute walk - Number of course t...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>tlbx_motor01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>end_2m_standardsc</td>\n",
       "      <td>Endurance -Normative Standard Score</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>tlbx_motor01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>grip_standardsc_dom</td>\n",
       "      <td>9-Hole Grip Strength Dominant Hand Normative S...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>tlbx_motor01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>grip_standardsc_nondom</td>\n",
       "      <td>9-Hole Grip Strength Non-Dominant Hand Normati...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>tlbx_motor01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>lo4mt1</td>\n",
       "      <td>Locomotion - 4 meter walk - Trial 1 (Usual Pac...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>tlbx_motor01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>lo4mt2</td>\n",
       "      <td>Locomotion - 4 meter walk - Trial 2 (Fast Pace...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>tlbx_motor01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>loco_comscore</td>\n",
       "      <td>Locomotion - 4 meter walk - Computed Score</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>tlbx_motor01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>loco_rawscore</td>\n",
       "      <td>Locomotion - 4 meter walk - Raw Score</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>tlbx_motor01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>nih_tlbx_agecsc_dominant</td>\n",
       "      <td>Age-Corrected Standard Scores Dominant</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>tlbx_motor01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>nih_tlbx_agecsc_nondom</td>\n",
       "      <td>Age-Corrected Standard Scores Non-Dominant</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>tlbx_motor01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>nih_tlbx_domsc</td>\n",
       "      <td>Dominant Score</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>tlbx_motor01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>nih_tlbx_nondomsc</td>\n",
       "      <td>Non-Dominant Score</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>tlbx_motor01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>nih_tlbx_standardsc_nondom</td>\n",
       "      <td>Uncorrected Standard Scores Non-Dominant</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>tlbx_motor01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>raw_wet</td>\n",
       "      <td>2-Minute Walk Endurance Test</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>tlbx_motor01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>ninehole_pctl_nondom</td>\n",
       "      <td>9-Hole Pegboard Dexterity Test National Percen...</td>\n",
       "      <td>0::100</td>\n",
       "      <td>None</td>\n",
       "      <td>tlbx_motor01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>msgs_dominanttrial</td>\n",
       "      <td>Grip Strength Test Dominant hand: pounds of fo...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>tlbx_motor01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>msgs_nondominanttrial</td>\n",
       "      <td>Grip Strength Test Non-dominant hand: pounds o...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>tlbx_motor01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>grip_dom_pctl</td>\n",
       "      <td>Grip Strength Test National Percentile (age ad...</td>\n",
       "      <td>0::100</td>\n",
       "      <td>None</td>\n",
       "      <td>tlbx_motor01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>grip_nondom_pctl</td>\n",
       "      <td>Grip Strength Test National Percentile (age ad...</td>\n",
       "      <td>0::100</td>\n",
       "      <td>None</td>\n",
       "      <td>tlbx_motor01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>interview_language</td>\n",
       "      <td>Language Used in the Interview</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>tlbx_motor01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>wcst_ni</td>\n",
       "      <td>Number of Items</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>tlbx_motor01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>twomin_acss</td>\n",
       "      <td>2-Minute Walk Endurance Test Age-Corrected Sta...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>tlbx_motor01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>twomin_pctl</td>\n",
       "      <td>2-Minute Walk Endurance Test National Percenti...</td>\n",
       "      <td>0::100</td>\n",
       "      <td>None</td>\n",
       "      <td>tlbx_motor01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>dx9hdominanttime</td>\n",
       "      <td>9-Hole Pegboard Dexterity Test: Dominant Hand ...</td>\n",
       "      <td>None</td>\n",
       "      <td>Time is in seconds, to the hundredth of a second</td>\n",
       "      <td>tlbx_motor01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>dx9hnondominanttime</td>\n",
       "      <td>9-Hole Pegboard Dexterity Test: Non-Dominant H...</td>\n",
       "      <td>None</td>\n",
       "      <td>Time is in seconds, to the hundredth of a second</td>\n",
       "      <td>tlbx_motor01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>ninehole_pctl_dom</td>\n",
       "      <td>9-Hole Pegboard Dexterity Test National Percen...</td>\n",
       "      <td>0::100</td>\n",
       "      <td>None</td>\n",
       "      <td>tlbx_motor01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>comqother</td>\n",
       "      <td>Respondent - Other (text)</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>tlbx_motor01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>fneproc</td>\n",
       "      <td>name of trial procedure being run</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>tlbx_motor01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   nda_element  \\\n",
       "5                 version_form   \n",
       "8         en6m50_2m_10ft_marks   \n",
       "9                 en6m50_2m_ft   \n",
       "10            en6m50_2m_inches   \n",
       "11             en6m50_2m_turns   \n",
       "13           end_2m_standardsc   \n",
       "19         grip_standardsc_dom   \n",
       "20      grip_standardsc_nondom   \n",
       "25                      lo4mt1   \n",
       "26                      lo4mt2   \n",
       "27               loco_comscore   \n",
       "28               loco_rawscore   \n",
       "31    nih_tlbx_agecsc_dominant   \n",
       "32      nih_tlbx_agecsc_nondom   \n",
       "33              nih_tlbx_domsc   \n",
       "34           nih_tlbx_nondomsc   \n",
       "36  nih_tlbx_standardsc_nondom   \n",
       "44                     raw_wet   \n",
       "45        ninehole_pctl_nondom   \n",
       "46          msgs_dominanttrial   \n",
       "47       msgs_nondominanttrial   \n",
       "48               grip_dom_pctl   \n",
       "49            grip_nondom_pctl   \n",
       "50          interview_language   \n",
       "51                     wcst_ni   \n",
       "52                 twomin_acss   \n",
       "53                 twomin_pctl   \n",
       "54            dx9hdominanttime   \n",
       "55         dx9hnondominanttime   \n",
       "56           ninehole_pctl_dom   \n",
       "59                   comqother   \n",
       "60                     fneproc   \n",
       "\n",
       "                                          description valueRange  \\\n",
       "5                           Form used/assessment name       None   \n",
       "8   Endurance - 2 minute walk - Number of 10 ft marks       None   \n",
       "9                    Endurance - 2 minute walk - Feet       None   \n",
       "10                 Endurance - 2 minute walk - Inches       None   \n",
       "11  Endurance - 2 minute walk - Number of course t...       None   \n",
       "13                Endurance -Normative Standard Score       None   \n",
       "19  9-Hole Grip Strength Dominant Hand Normative S...       None   \n",
       "20  9-Hole Grip Strength Non-Dominant Hand Normati...       None   \n",
       "25  Locomotion - 4 meter walk - Trial 1 (Usual Pac...       None   \n",
       "26  Locomotion - 4 meter walk - Trial 2 (Fast Pace...       None   \n",
       "27         Locomotion - 4 meter walk - Computed Score       None   \n",
       "28              Locomotion - 4 meter walk - Raw Score       None   \n",
       "31             Age-Corrected Standard Scores Dominant       None   \n",
       "32         Age-Corrected Standard Scores Non-Dominant       None   \n",
       "33                                     Dominant Score       None   \n",
       "34                                 Non-Dominant Score       None   \n",
       "36           Uncorrected Standard Scores Non-Dominant       None   \n",
       "44                       2-Minute Walk Endurance Test       None   \n",
       "45  9-Hole Pegboard Dexterity Test National Percen...     0::100   \n",
       "46  Grip Strength Test Dominant hand: pounds of fo...       None   \n",
       "47  Grip Strength Test Non-dominant hand: pounds o...       None   \n",
       "48  Grip Strength Test National Percentile (age ad...     0::100   \n",
       "49  Grip Strength Test National Percentile (age ad...     0::100   \n",
       "50                     Language Used in the Interview       None   \n",
       "51                                    Number of Items       None   \n",
       "52  2-Minute Walk Endurance Test Age-Corrected Sta...       None   \n",
       "53  2-Minute Walk Endurance Test National Percenti...     0::100   \n",
       "54  9-Hole Pegboard Dexterity Test: Dominant Hand ...       None   \n",
       "55  9-Hole Pegboard Dexterity Test: Non-Dominant H...       None   \n",
       "56  9-Hole Pegboard Dexterity Test National Percen...     0::100   \n",
       "59                          Respondent - Other (text)       None   \n",
       "60                  name of trial procedure being run       None   \n",
       "\n",
       "                                               notes nda_structure  \n",
       "5                                               None  tlbx_motor01  \n",
       "8                                               None  tlbx_motor01  \n",
       "9                                               None  tlbx_motor01  \n",
       "10                                              None  tlbx_motor01  \n",
       "11                                              None  tlbx_motor01  \n",
       "13                                              None  tlbx_motor01  \n",
       "19                                              None  tlbx_motor01  \n",
       "20                                              None  tlbx_motor01  \n",
       "25                                              None  tlbx_motor01  \n",
       "26                                              None  tlbx_motor01  \n",
       "27                                              None  tlbx_motor01  \n",
       "28                                              None  tlbx_motor01  \n",
       "31                                              None  tlbx_motor01  \n",
       "32                                              None  tlbx_motor01  \n",
       "33                                              None  tlbx_motor01  \n",
       "34                                              None  tlbx_motor01  \n",
       "36                                              None  tlbx_motor01  \n",
       "44                                              None  tlbx_motor01  \n",
       "45                                              None  tlbx_motor01  \n",
       "46                                              None  tlbx_motor01  \n",
       "47                                              None  tlbx_motor01  \n",
       "48                                              None  tlbx_motor01  \n",
       "49                                              None  tlbx_motor01  \n",
       "50                                              None  tlbx_motor01  \n",
       "51                                              None  tlbx_motor01  \n",
       "52                                              None  tlbx_motor01  \n",
       "53                                              None  tlbx_motor01  \n",
       "54  Time is in seconds, to the hundredth of a second  tlbx_motor01  \n",
       "55  Time is in seconds, to the hundredth of a second  tlbx_motor01  \n",
       "56                                              None  tlbx_motor01  \n",
       "59                                              None  tlbx_motor01  \n",
       "60                                              None  tlbx_motor01  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#test\n",
    "#dfxwalk=getNDAdetails(structure_name='prang01')\n",
    "#dfxwalk\n",
    "#dfxwalk=getNDAdetails(structure_name='tlbx_wellbeing01')\n",
    "#dfxwalk\n",
    "dfxwalk=getNDAdetails(structure_name='tlbx_motor01')\n",
    "dfxwalk"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "could not process nan\n"
     ]
    }
   ],
   "source": [
    "#append all this information together for a merge\n",
    "allmetadata=pd.DataFrame()\n",
    "for structshort in crosswalk.nda_structure.unique():\n",
    "    try:\n",
    "        dfxwalk=getNDAdetails(structure_name=structshort)\n",
    "        allmetadata=pd.concat([allmetadata,dfxwalk])\n",
    "    except:\n",
    "        print('could not process {}'.format(structshort))\n",
    " \n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>nda_element</th>\n",
       "      <th>description</th>\n",
       "      <th>valueRange</th>\n",
       "      <th>notes</th>\n",
       "      <th>nda_structure</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>primary_language</td>\n",
       "      <td>Subject's Primary Language</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>tlbx_fearanx01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>version_form</td>\n",
       "      <td>Form used/assessment name</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>tlbx_fearanx01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>pedrepanx50</td>\n",
       "      <td>In the past 7 days I worried when I was at home</td>\n",
       "      <td>1::5</td>\n",
       "      <td>1=Never; 2=Almost Never; 3=Sometimes; 4=Often;...</td>\n",
       "      <td>tlbx_fearanx01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>pedrepanx51</td>\n",
       "      <td>In the past 7 days I got scared really easily</td>\n",
       "      <td>1::5</td>\n",
       "      <td>1=Never; 2=Almost Never; 3=Sometimes; 4=Often;...</td>\n",
       "      <td>tlbx_fearanx01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>pedproxanx29</td>\n",
       "      <td>Please indicate how often or true the behavior...</td>\n",
       "      <td>0::2</td>\n",
       "      <td>0=Never or not true; 1=Sometimes or somewhat t...</td>\n",
       "      <td>tlbx_fearanx01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         nda_element                                        description  \\\n",
       "5   primary_language                         Subject's Primary Language   \n",
       "7       version_form                          Form used/assessment name   \n",
       "8        pedrepanx50    In the past 7 days I worried when I was at home   \n",
       "9        pedrepanx51      In the past 7 days I got scared really easily   \n",
       "10      pedproxanx29  Please indicate how often or true the behavior...   \n",
       "\n",
       "   valueRange                                              notes  \\\n",
       "5        None                                               None   \n",
       "7        None                                               None   \n",
       "8        1::5  1=Never; 2=Almost Never; 3=Sometimes; 4=Often;...   \n",
       "9        1::5  1=Never; 2=Almost Never; 3=Sometimes; 4=Often;...   \n",
       "10       0::2  0=Never or not true; 1=Sometimes or somewhat t...   \n",
       "\n",
       "     nda_structure  \n",
       "5   tlbx_fearanx01  \n",
       "7   tlbx_fearanx01  \n",
       "8   tlbx_fearanx01  \n",
       "9   tlbx_fearanx01  \n",
       "10  tlbx_fearanx01  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "allmetadata.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "ename": "KeyError",
     "evalue": "'hcp_variable_upload'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-6-6bcb5daf2d46>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0;31m#hcp_variable_upload is supposed to be the same as nda_element (we have found typos from the NDA curators, which is understandable, but needs to be checked)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      2\u001b[0m \u001b[0;31m#merge the annotation by 'hcp_variable_upload' to see if any NDA descriptors are missing.\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 3\u001b[0;31m \u001b[0missues\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mpd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mmerge\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcrosswalk\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdrop\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'nda_element'\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m'description'\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m'valueRange'\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m'notes'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0mallmetadata\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0mhow\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m'left'\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0mon\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'nda_structure'\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m'hcp_variable_upload'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m~/.virtualenvs/PycharmToolbox/lib/python3.6/site-packages/pandas/core/reshape/merge.py\u001b[0m in \u001b[0;36mmerge\u001b[0;34m(left, right, how, on, left_on, right_on, left_index, right_index, sort, suffixes, copy, indicator, validate)\u001b[0m\n\u001b[1;32m     45\u001b[0m                          \u001b[0mright_index\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mright_index\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0msort\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0msort\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0msuffixes\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0msuffixes\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     46\u001b[0m                          \u001b[0mcopy\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mcopy\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mindicator\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mindicator\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 47\u001b[0;31m                          validate=validate)\n\u001b[0m\u001b[1;32m     48\u001b[0m     \u001b[0;32mreturn\u001b[0m \u001b[0mop\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_result\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     49\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.virtualenvs/PycharmToolbox/lib/python3.6/site-packages/pandas/core/reshape/merge.py\u001b[0m in \u001b[0;36m__init__\u001b[0;34m(self, left, right, how, on, left_on, right_on, axis, left_index, right_index, sort, suffixes, copy, indicator, validate)\u001b[0m\n\u001b[1;32m    527\u001b[0m         (self.left_join_keys,\n\u001b[1;32m    528\u001b[0m          \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mright_join_keys\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 529\u001b[0;31m          self.join_names) = self._get_merge_keys()\n\u001b[0m\u001b[1;32m    530\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    531\u001b[0m         \u001b[0;31m# validate the merge keys dtypes. We may need to coerce\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.virtualenvs/PycharmToolbox/lib/python3.6/site-packages/pandas/core/reshape/merge.py\u001b[0m in \u001b[0;36m_get_merge_keys\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    831\u001b[0m                         \u001b[0;32mif\u001b[0m \u001b[0mrk\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    832\u001b[0m                             right_keys.append(\n\u001b[0;32m--> 833\u001b[0;31m                                 right._get_label_or_level_values(rk))\n\u001b[0m\u001b[1;32m    834\u001b[0m                         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    835\u001b[0m                             \u001b[0;31m# work-around for merge_asof(right_index=True)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.virtualenvs/PycharmToolbox/lib/python3.6/site-packages/pandas/core/generic.py\u001b[0m in \u001b[0;36m_get_label_or_level_values\u001b[0;34m(self, key, axis)\u001b[0m\n\u001b[1;32m   1695\u001b[0m             \u001b[0mvalues\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0maxes\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0maxis\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_level_values\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_values\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1696\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1697\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mKeyError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1698\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1699\u001b[0m         \u001b[0;31m# Check for duplicates\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mKeyError\u001b[0m: 'hcp_variable_upload'"
     ]
    }
   ],
   "source": [
    "#hcp_variable_upload = mda_ement in the CROSSWALK is supposed to be the same as nda_element (we have found typos from the NDA curators, which is understandable, but needs to be checked)\n",
    "#merge the annotation by 'hcp_variable_upload' to see if any NDA descriptors are missing.\n",
    "issues=pd.merge(crosswalk.drop(columns=['description','valueRange','notes']),allmetadata,how='left',on=['nda_structure','hcp_variable_upload'])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#now merge this data into the crosswalk and write the new csv\n",
    "newcrosswalk=pd.merge(crosswalk.drop(columns=['description','valueRange','notes']),allmetadata,how='left',on=['nda_structure','nda_element'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>validated</th>\n",
       "      <th>Measurement System</th>\n",
       "      <th>Domain</th>\n",
       "      <th>Item ID</th>\n",
       "      <th>Stem</th>\n",
       "      <th>Context</th>\n",
       "      <th>DataType</th>\n",
       "      <th>Responses</th>\n",
       "      <th>Inst</th>\n",
       "      <th>hcp_variable</th>\n",
       "      <th>...</th>\n",
       "      <th>specialty_code</th>\n",
       "      <th>hcp_variable_upload</th>\n",
       "      <th>nda_structure</th>\n",
       "      <th>nda_element</th>\n",
       "      <th>template</th>\n",
       "      <th>inst_short</th>\n",
       "      <th>Source</th>\n",
       "      <th>description</th>\n",
       "      <th>valueRange</th>\n",
       "      <th>notes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Anxiety Summary Parent Report (3-7)</td>\n",
       "      <td>dummy_00095</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nih_tlbx_rawscore</td>\n",
       "      <td>tlbx_fearanx01</td>\n",
       "      <td>nih_tlbx_rawscore</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Anxiety_Summary_3-7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>RawScore</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Anxiety Summary Parent Report (3-7)</td>\n",
       "      <td>dummy_00096</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nih_tlbx_theta</td>\n",
       "      <td>tlbx_fearanx01</td>\n",
       "      <td>nih_tlbx_theta</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Anxiety_Summary_3-7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Theta</td>\n",
       "      <td>None</td>\n",
       "      <td>999=NA/NK/missing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Anxiety Summary Parent Report (3-7)</td>\n",
       "      <td>dummy_00097</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nih_tlbx_tscore</td>\n",
       "      <td>tlbx_fearanx01</td>\n",
       "      <td>nih_tlbx_tscore</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Anxiety_Summary_3-7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>T-score</td>\n",
       "      <td>0::200; 999</td>\n",
       "      <td>999=NA/NK/missing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Anxiety Summary Parent Report (3-7)</td>\n",
       "      <td>dummy_00098</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nih_tlbx_se</td>\n",
       "      <td>tlbx_fearanx01</td>\n",
       "      <td>nih_tlbx_se</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Anxiety_Summary_3-7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Standard Error</td>\n",
       "      <td>None</td>\n",
       "      <td>999=NA/NK/missing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Anxiety Summary Parent Report (3-7)</td>\n",
       "      <td>dummy_00099</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nih_tlbx_agegencsc</td>\n",
       "      <td>tlbx_fearanx01</td>\n",
       "      <td>nih_tlbx_agegencsc</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Anxiety_Summary_3-7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Age-Gender-Corrected T-score</td>\n",
       "      <td>0::250; 999</td>\n",
       "      <td>999=NA/NK/missing</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  validated Measurement System Domain Item ID Stem Context DataType Responses  \\\n",
       "0       NaN                NaN    NaN     NaN  NaN     NaN      NaN       NaN   \n",
       "1       NaN                NaN    NaN     NaN  NaN     NaN      NaN       NaN   \n",
       "2       NaN                NaN    NaN     NaN  NaN     NaN      NaN       NaN   \n",
       "3       NaN                NaN    NaN     NaN  NaN     NaN      NaN       NaN   \n",
       "4       NaN                NaN    NaN     NaN  NaN     NaN      NaN       NaN   \n",
       "\n",
       "                                  Inst hcp_variable  ... specialty_code  \\\n",
       "0  Anxiety Summary Parent Report (3-7)  dummy_00095  ...            NaN   \n",
       "1  Anxiety Summary Parent Report (3-7)  dummy_00096  ...            NaN   \n",
       "2  Anxiety Summary Parent Report (3-7)  dummy_00097  ...            NaN   \n",
       "3  Anxiety Summary Parent Report (3-7)  dummy_00098  ...            NaN   \n",
       "4  Anxiety Summary Parent Report (3-7)  dummy_00099  ...            NaN   \n",
       "\n",
       "  hcp_variable_upload   nda_structure         nda_element template  \\\n",
       "0   nih_tlbx_rawscore  tlbx_fearanx01   nih_tlbx_rawscore      NaN   \n",
       "1      nih_tlbx_theta  tlbx_fearanx01      nih_tlbx_theta      NaN   \n",
       "2     nih_tlbx_tscore  tlbx_fearanx01     nih_tlbx_tscore      NaN   \n",
       "3         nih_tlbx_se  tlbx_fearanx01         nih_tlbx_se      NaN   \n",
       "4  nih_tlbx_agegencsc  tlbx_fearanx01  nih_tlbx_agegencsc      NaN   \n",
       "\n",
       "            inst_short Source                   description   valueRange  \\\n",
       "0  Anxiety_Summary_3-7    NaN                      RawScore         None   \n",
       "1  Anxiety_Summary_3-7    NaN                         Theta         None   \n",
       "2  Anxiety_Summary_3-7    NaN                       T-score  0::200; 999   \n",
       "3  Anxiety_Summary_3-7    NaN                Standard Error         None   \n",
       "4  Anxiety_Summary_3-7    NaN  Age-Gender-Corrected T-score  0::250; 999   \n",
       "\n",
       "               notes  \n",
       "0               None  \n",
       "1  999=NA/NK/missing  \n",
       "2  999=NA/NK/missing  \n",
       "3  999=NA/NK/missing  \n",
       "4  999=NA/NK/missing  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "newcrosswalk.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "newcrosswalk.to_csv(pathin+crosswalkfile,index=False)#crosswalkfile,index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>validated</th>\n",
       "      <th>Measurement System</th>\n",
       "      <th>Domain</th>\n",
       "      <th>Item ID</th>\n",
       "      <th>Stem</th>\n",
       "      <th>Context</th>\n",
       "      <th>DataType</th>\n",
       "      <th>Responses</th>\n",
       "      <th>Inst</th>\n",
       "      <th>hcp_variable</th>\n",
       "      <th>...</th>\n",
       "      <th>specialty_code</th>\n",
       "      <th>hcp_variable_upload</th>\n",
       "      <th>nda_structure</th>\n",
       "      <th>nda_element</th>\n",
       "      <th>template</th>\n",
       "      <th>inst_short</th>\n",
       "      <th>Source</th>\n",
       "      <th>description</th>\n",
       "      <th>valueRange</th>\n",
       "      <th>notes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Anxiety Summary Parent Report (3-7)</td>\n",
       "      <td>dummy_00095</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nih_tlbx_rawscore</td>\n",
       "      <td>tlbx_fearanx01</td>\n",
       "      <td>nih_tlbx_rawscore</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Anxiety_Summary_3-7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>RawScore</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Anxiety Summary Parent Report (3-7)</td>\n",
       "      <td>dummy_00096</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nih_tlbx_theta</td>\n",
       "      <td>tlbx_fearanx01</td>\n",
       "      <td>nih_tlbx_theta</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Anxiety_Summary_3-7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Theta</td>\n",
       "      <td>None</td>\n",
       "      <td>999=NA/NK/missing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Anxiety Summary Parent Report (3-7)</td>\n",
       "      <td>dummy_00097</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nih_tlbx_tscore</td>\n",
       "      <td>tlbx_fearanx01</td>\n",
       "      <td>nih_tlbx_tscore</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Anxiety_Summary_3-7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>T-score</td>\n",
       "      <td>0::200; 999</td>\n",
       "      <td>999=NA/NK/missing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Anxiety Summary Parent Report (3-7)</td>\n",
       "      <td>dummy_00098</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nih_tlbx_se</td>\n",
       "      <td>tlbx_fearanx01</td>\n",
       "      <td>nih_tlbx_se</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Anxiety_Summary_3-7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Standard Error</td>\n",
       "      <td>None</td>\n",
       "      <td>999=NA/NK/missing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Anxiety Summary Parent Report (3-7)</td>\n",
       "      <td>dummy_00099</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nih_tlbx_agegencsc</td>\n",
       "      <td>tlbx_fearanx01</td>\n",
       "      <td>nih_tlbx_agegencsc</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Anxiety_Summary_3-7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Age-Gender-Corrected T-score</td>\n",
       "      <td>0::250; 999</td>\n",
       "      <td>999=NA/NK/missing</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  validated Measurement System Domain Item ID Stem Context DataType Responses  \\\n",
       "0       NaN                NaN    NaN     NaN  NaN     NaN      NaN       NaN   \n",
       "1       NaN                NaN    NaN     NaN  NaN     NaN      NaN       NaN   \n",
       "2       NaN                NaN    NaN     NaN  NaN     NaN      NaN       NaN   \n",
       "3       NaN                NaN    NaN     NaN  NaN     NaN      NaN       NaN   \n",
       "4       NaN                NaN    NaN     NaN  NaN     NaN      NaN       NaN   \n",
       "\n",
       "                                  Inst hcp_variable  ... specialty_code  \\\n",
       "0  Anxiety Summary Parent Report (3-7)  dummy_00095  ...            NaN   \n",
       "1  Anxiety Summary Parent Report (3-7)  dummy_00096  ...            NaN   \n",
       "2  Anxiety Summary Parent Report (3-7)  dummy_00097  ...            NaN   \n",
       "3  Anxiety Summary Parent Report (3-7)  dummy_00098  ...            NaN   \n",
       "4  Anxiety Summary Parent Report (3-7)  dummy_00099  ...            NaN   \n",
       "\n",
       "  hcp_variable_upload   nda_structure         nda_element template  \\\n",
       "0   nih_tlbx_rawscore  tlbx_fearanx01   nih_tlbx_rawscore      NaN   \n",
       "1      nih_tlbx_theta  tlbx_fearanx01      nih_tlbx_theta      NaN   \n",
       "2     nih_tlbx_tscore  tlbx_fearanx01     nih_tlbx_tscore      NaN   \n",
       "3         nih_tlbx_se  tlbx_fearanx01         nih_tlbx_se      NaN   \n",
       "4  nih_tlbx_agegencsc  tlbx_fearanx01  nih_tlbx_agegencsc      NaN   \n",
       "\n",
       "            inst_short Source                   description   valueRange  \\\n",
       "0  Anxiety_Summary_3-7    NaN                      RawScore         None   \n",
       "1  Anxiety_Summary_3-7    NaN                         Theta         None   \n",
       "2  Anxiety_Summary_3-7    NaN                       T-score  0::200; 999   \n",
       "3  Anxiety_Summary_3-7    NaN                Standard Error         None   \n",
       "4  Anxiety_Summary_3-7    NaN  Age-Gender-Corrected T-score  0::250; 999   \n",
       "\n",
       "               notes  \n",
       "0               None  \n",
       "1  999=NA/NK/missing  \n",
       "2  999=NA/NK/missing  \n",
       "3  999=NA/NK/missing  \n",
       "4  999=NA/NK/missing  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "newcrosswalk.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "PycharmToolbox",
   "language": "python",
   "name": "pycharmtoolbox"
  },
  "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.6.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}