hs_crosswalk_build_v2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction\n",
    "We have previously extracted and cleaned a dataset of high school (HS) data from students who have submitted applications to CU Boulder This data includes the HS name, its CEEB code, as well as some location information (ZIP, City, and State). The product of this notebook will be a table of HS names, CEEB, and NCES codes, as well as some additional location data where available. This \"crosswalk\" can then be used to build a more complete HS dataset.\n",
    "\n",
    "# Script\n",
    "Begin with some imports and preliminaries:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/peter/Desktop/ODA/ceeb_nces_crosswalk/venv/lib/python3.8/site-packages/fuzzywuzzy/fuzz.py:11: UserWarning: Using slow pure-python SequenceMatcher. Install python-Levenshtein to remove this warning\n",
      "  warnings.warn('Using slow pure-python SequenceMatcher. Install python-Levenshtein to remove this warning')\n"
     ]
    }
   ],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "import sys\n",
    "import os\n",
    "import os.path as path\n",
    "import pickle as p\n",
    "\n",
    "from urllib.request import urlopen\n",
    "import json\n",
    "\n",
    "from fuzzywuzzy import fuzz\n",
    "from fuzzywuzzy import process\n",
    "\n",
    "np.random.seed(8675309)\n",
    "\n",
    "# useful directories\n",
    "PROJ_DIR = path.abspath('/home/peter/Desktop/ODA/ceeb_nces_crosswalk')\n",
    "DATA_DIR = path.join(PROJ_DIR,'data')\n",
    "MTURK_DIR = path.join(PROJ_DIR,'mturk')\n",
    "\n",
    "# read in the data\n",
    "hs_df = pd.read_csv(path.join(DATA_DIR,'ucb_apps_hs.csv'),dtype=str)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Merging with the Davenport Crosswalk\n",
    "Let's now load the Davenport CEEB-NCES crosswalk ([source](https://ire.uncg.edu/research/NCES_CEEB_Table/)). We'll use this as the base, and drop all records in our dataset which can be found in the Davenport data.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# read in the CEEB-NCES crosswalk database TEST\n",
    "davenport_cross = pd.read_excel(path.join(DATA_DIR,'davenport_nces_ceeb_crosswalk.xlsx'),\n",
    "                               dtype=str)\n",
    "\n",
    "# Davenport provided multiple columns for the same items, condense those down\n",
    "davenport_cross.HS_CITY.fillna(davenport_cross.SAS_MATCH_CITY, inplace=True)\n",
    "davenport_cross.HS_STATE.fillna(davenport_cross.SAS_MATCH_STATE, inplace=True)\n",
    "davenport_cross.HS_ZIP.fillna(davenport_cross.SAS_MATCH_ZIP, inplace=True)\n",
    "\n",
    "# I will drop several of the Davenport columns, and rename the ones I keep\n",
    "davenport_keep_cols = {'HS_CEEB':'HS_CEEB',\n",
    "                       'NCESSCH':'HS_NCES',\n",
    "                       'HS_CITY':'HS_CITY',\n",
    "                       'HS_NAME':'MATCH_NAME',\n",
    "                       'HS_ZIP':'HS_POSTAL_CD',\n",
    "                       'HS_STATE':'HS_STATE'}\n",
    "\n",
    "davenport_cross.rename(columns=davenport_keep_cols,inplace=True)\n",
    "\n",
    "# some Davenport crosswalk members were missing an NCES or CEEB code, drop those here\n",
    "ucb_cross = davenport_cross[list(davenport_keep_cols.values())].dropna(subset=['HS_NCES','HS_CEEB'])\n",
    "\n",
    "# match the names which CU Boulder has for each school to the Davenport name (renamed to `match_name`)\n",
    "ucb_cross = ucb_cross.merge(hs_df[['HS_CEEB','HS_NAME']],on='HS_CEEB',how='left')\n",
    "\n",
    "# determine which CEEBs have been matched through the Davenport crosswalk and drop them from `hs_df`\n",
    "matched_ceebs = set(ucb_cross.HS_CEEB)\n",
    "hs_df_less_davenport = hs_df.loc[[_ not in matched_ceebs for _ in hs_df.HS_CEEB]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Merging in the NCES PSS \n",
    "I will now merge the NCES' [Private School Universe Survey](https://nces.ed.gov/surveys/pss/pssdata.asp) (PSS) into the crosswalk."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "pss_df = pd.read_csv(path.join(DATA_DIR,'nces_pss1718.csv'),dtype=str) \n",
    "# this dataset contains quite a large amount of columns, for now let's take only a small subset \n",
    "pss_cols = ['PPIN','PINST','PADDRS','PCITY','PSTABB','PZIP','PL_CIT','PL_ZIP']\n",
    "pss_df = pss_df[pss_cols]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I'm going to search through the PSS for schools in the HS dataset which aren't already contained in the Davenport crosswalk. First I will search for the HS's ZIP code in the PSS dataset. Then I'm going fuzzy match the HS name whose CEEB code we want to match against those which share its ZIP code. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# a little utility script to clean up a schoolname and remove irrelevant text\n",
    "def name_clean(name):\n",
    "    name = name.lower()\n",
    "    name = name.replace('high school','') # leaving \"high school\" in the school name increases apparent match for no reason  \n",
    "    name = name.replace('school','') # ditto\n",
    "    name = name.replace('academy','') # ditto\n",
    "    \n",
    "    return(name.strip())\n",
    "\n",
    "# clean and compare two school names\n",
    "def hs_name_match_score(name1,name2):\n",
    "    return(fuzz.ratio(name_clean(name1),name_clean(name2)))\n",
    "\n",
    "# rewrite `len()` to handle `np.nan`s\n",
    "def myLen(x):\n",
    "    if type(x)==str:\n",
    "        return(len(x))\n",
    "    else:\n",
    "        return(np.nan)\n",
    "\n",
    "hs_df_less_davenport = hs_df_less_davenport.assign(HS_POSTAL_CD=[str(_)[:5] for _ in hs_df_less_davenport.HS_POSTAL_CD])\n",
    "hs_df_less_davenport.replace({'nan':np.nan},inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# this may take a few minutes to run  \n",
    "match_cutoff = 70\n",
    "\n",
    "pss_matches = []\n",
    "for i in range(hs_df_less_davenport.shape[0]):\n",
    "    row = hs_df_less_davenport.iloc[[i]]\n",
    "    \n",
    "    zip_match = (pss_df.PZIP==row.HS_POSTAL_CD.item()) | (pss_df.PL_ZIP==row.HS_POSTAL_CD.item()) \n",
    "    scores = [hs_name_match_score(_,row.HS_NAME.item()) for _ in pss_df.loc[zip_match].PINST]\n",
    "    \n",
    "    try:\n",
    "        match = pss_df.loc[zip_match].iloc[[np.argmax(scores)]]\n",
    "    \n",
    "        if np.max(scores)>match_cutoff:\n",
    "            ret_row = {'HS_NAME': row.HS_NAME.item(),\n",
    "                       'HS_CEEB': row.HS_CEEB.item(),\n",
    "                       'HS_NCES': match.PPIN.item(),\n",
    "                       'MATCH_NAME': match.PINST.item(),\n",
    "                       'MATCH_SCORE': np.max(scores)}\n",
    "                        \n",
    "            pss_matches.append(ret_row)\n",
    "\n",
    "        else:\n",
    "            ret_row = {'HS_NAME': row.HS_NAME.item(),\n",
    "                       'HS_CEEB': row.HS_CEEB.item(),\n",
    "                       'HS_NCES': None,\n",
    "                       'MATCH_NAME': None,\n",
    "                       'MATCH_SCORE': None}\n",
    "    except:\n",
    "        ret_row = {'HS_NAME': row.HS_NAME.item(),\n",
    "                   'HS_CEEB': row.HS_CEEB.item(),\n",
    "                   'HS_NCES': None,\n",
    "                   'MATCH_NAME': None,\n",
    "                   'MATCH_SCORE': None} \n",
    "\n",
    "pss_matches.append(ret_row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We'll now add the new PSS crosswalk data into `ucb_cross`, and update the outstanding HS records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "pss_cross = pd.DataFrame(pss_matches).replace('None',np.nan).dropna()\n",
    "pss_cross = hs_df_less_davenport.merge(pss_cross[['HS_CEEB','HS_NCES','MATCH_NAME','MATCH_SCORE']],on='HS_CEEB').drop_duplicates() \n",
    "\n",
    "ucb_cross = pd.concat([ucb_cross,pss_cross]).drop_duplicates()\n",
    "\n",
    "matched_ceebs = set(ucb_cross.HS_CEEB)\n",
    "hs_df_less_davenport_pss = hs_df.loc[[_ not in matched_ceebs for _ in hs_df.HS_CEEB]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Merging in the NCES CCD \n",
    "Now let's try and use the Urban Institute's NCES Common Core of Data (CCD) directory [API](https://educationdata-stg.urban.org/documentation/index.html) to get the remaining NCES codes. I'll use a similar approach as with the PSS data: first narrowing the field to schools sharing a ZIP code, followed by fuzzy string matching on names. First define some functions to handle the API:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# the Urban Institute API uses a paged format which returns only\n",
    "# 1000 records at a time, plus a link to the next \"page\". Therefore I wrote this little utility functiion\n",
    "# to run through all the \"pages\" and collect the results, starting at a specified `url` \n",
    "def url_get(url):\n",
    "    with urlopen(url) as f:\n",
    "        response = json.loads(f.read())\n",
    "\n",
    "    count = response['count']\n",
    "\n",
    "    data = []\n",
    "    data += response['results']\n",
    "\n",
    "    while len(data)<count:\n",
    "        nextURL = response['next']\n",
    "\n",
    "        with urlopen(nextURL) as f:\n",
    "            reponse = json.loads(f.read())\n",
    "\n",
    "        data += response['results']\n",
    "\n",
    "    return(data)\n",
    "\n",
    "# look up schools within a certain ZIP code in the NCES' \"Common Core of Data\" (CCD)\n",
    "# year is also a variable, but I believe that later years are roughly supersets of earlier years\n",
    "# so it's advised to just use the most recent year for which data is available\n",
    "def ccd_directory_zip_lookup(year,zipcode):\n",
    "    url = f\"https://educationdata-stg.urban.org/api/v1/schools/ccd/directory/{year}/?zip_mailing={zipcode}\"\n",
    "    data_mailing = url_get(url)\n",
    "\n",
    "    url = f\"https://educationdata-stg.urban.org/api/v1/schools/ccd/directory/{year}/?zip_location={zipcode}\"\n",
    "    data_location = url_get(url)\n",
    "    \n",
    "    return(pd.concat([pd.DataFrame(data_mailing),pd.DataFrame(data_location)]))\n",
    "\n",
    "# Takes a row of the HS dataset, pulls down CCD records from Urban Institute (UI) API with the same ZIP code\n",
    "# and fuzzy matches the returned names against the row's HS name\n",
    "def ui_row_match(row,match_cutoff=match_cutoff):  \n",
    "    cands = ccd_directory_zip_lookup(2018,row.HS_POSTAL_CD.item())\n",
    "    \n",
    "    \n",
    "    if cands.shape[0]>0:\n",
    "        scores = [hs_name_match_score(_,row.HS_NAME.item()) for _ in cands.school_name]\n",
    "    \n",
    "        match = cands.iloc[[np.argmax(scores)]]\n",
    "    \n",
    "        if np.max(scores) > match_cutoff:\n",
    "            ret_row = {'HS_NAME': row.HS_NAME.item(),\n",
    "                       'HS_CEEB': row.HS_CEEB.item(),\n",
    "                       'HS_NCES': match.ncessch.item(),\n",
    "                       'MATCH_NAME': match.school_name.item(),\n",
    "                       'MATCH_SCORE': np.max(scores)}\n",
    "        else:\n",
    "            ret_row = {'HS_NAME': row.HS_NAME.item(),\n",
    "                       'HS_CEEB': row.HS_CEEB.item(),\n",
    "                       'HS_NCES': None,\n",
    "                       'MATCH_NAME': None,\n",
    "                       'MATCH_SCORE': None}\n",
    "\n",
    "    else:\n",
    "        ret_row = {'HS_NAME': row.HS_NAME.item(),\n",
    "                   'HS_CEEB': row.HS_CEEB.item(),\n",
    "                   'HS_NCES': None,\n",
    "                   'MATCH_NAME': None,\n",
    "                   'MATCH_SCORE': None}  \n",
    "    return(ret_row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now actually perform the API lookups. Because this takes a few hours, pre-saved results are saved and made available with the GitHub repo. The cell will default to the pre-saved results if they are available"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# WARNING: UrbanInstitute API appears to no longer filter on ZIP code and this cell will therefore hang if pre-saved results are not available\n",
    "\n",
    "save_fname = path.join(DATA_DIR,'presaved_ccd_match_df.csv')\n",
    "if path.isfile(save_fname):\n",
    "    ccd_colnames = {'hs_name':'HS_NAME',\n",
    "                    'hs_ceeb':'HS_CEEB',\n",
    "                    'ccd_name':'MATCH_NAME',\n",
    "                    'ccd_nces':'HS_NCES',\n",
    "                    'score':'MATCH_SCORE'}\n",
    "\n",
    "    ccd_match_df = pd.read_csv(save_fname,dtype={'hs_ceeb':str,'ccd_nces':str}).drop(columns=['Unnamed: 0'],axis=1).rename(columns=ccd_colnames)\n",
    "\n",
    "else:\n",
    "    # WARNING: this may take a long time to run, pre-saved results can be loaded in the cell below \n",
    "    ccd_matches = []\n",
    "    print('Unmatched HS records remaining:')\n",
    "    print(hs_df_less_davenport_pss.shape[0])\n",
    "    for i in range(hs_df_less_davenport_pss.shape[0]):\n",
    "        row = hs_df_less_davenport_pss.iloc[[i]]\n",
    "        if i%50==0:\n",
    "            with open(path.join(DATA_DIR,'ccd_matches_inprog.p'),'wb') as f:\n",
    "                p.dump(ccd_matches,f)\n",
    "            print(i)\n",
    "        else:\n",
    "            pass\n",
    "\n",
    "        if row.HS_POSTAL_CD.item()==None:\n",
    "            pass\n",
    "\n",
    "        else:\n",
    "            try:\n",
    "                ccd_matches.append(ui_row_match(row))\n",
    "            except:\n",
    "                pass\n",
    "            \n",
    "    ccd_match_df = pd.DataFrame(ccd_matches).replace('None',np.nan)\n",
    "    ccd_match_df.to_csv(path.join(DATA_DIR,'ccd_match_df.csv'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "ccd_cross = ccd_match_df.replace('None',np.nan).dropna()\n",
    "ccd_cross = hs_df_less_davenport_pss.merge(ccd_cross[['HS_CEEB','HS_NCES','MATCH_NAME','MATCH_SCORE']],on='HS_CEEB').drop_duplicates() \n",
    "\n",
    "ccd_cross.head()\n",
    "ucb_cross = pd.concat([ucb_cross,ccd_cross]).drop_duplicates()\n",
    "\n",
    "matched_ceebs = set(ucb_cross.HS_CEEB)\n",
    "hs_df_outstanding = hs_df.loc[[_ not in matched_ceebs for _ in hs_df.HS_CEEB]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Checking Match Quality\n",
    "Now let's do some sanity checks of match quality. First I'll overwrite the `MATCH_SCORE` column (which previously only had values for rows come from the PSS or CCD datasets) so that it includes entries for every row with a `HS_NAME` and a `MATCH_NAME` (some entries in the Davenport cross walk didn't have names, so these I'll leave the score as `NaN`)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of low-scoring matches by match source:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "MATCH_SOURCE\n",
       "0    358\n",
       "1     14\n",
       "2     79\n",
       "Name: MATCH_SCORE, dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def name_check_score(n1,n2):\n",
    "    if pd.isnull(n1) or pd.isnull(n2):\n",
    "        return(np.nan)\n",
    "    \n",
    "    try:\n",
    "        return(hs_name_match_score(str(n1),str(n2)))\n",
    "    \n",
    "    except:\n",
    "        return(np.nan)\n",
    "\n",
    "davenport_ceebs = set(davenport_cross.HS_CEEB)\n",
    "pss_ceebs = set(pss_cross.HS_CEEB)\n",
    "ccd_ceebs = set(ccd_cross.HS_CEEB)\n",
    "\n",
    "def src_check(ceeb):\n",
    "    if ceeb in davenport_ceebs:\n",
    "        return(0) # source 0 means NCES found in Davenport crosswalk\n",
    "    \n",
    "    elif ceeb in pss_ceebs:\n",
    "        return(1) # source 1 means NCES found in PSS\n",
    "    \n",
    "    elif ceeb in ccd_ceebs:\n",
    "        return(2) # source 2 means NCES found in CCD (the dataset behind the Urban Inst API)\n",
    "    \n",
    "    else:\n",
    "        return(-1) # source -1 means NCES not found yet\n",
    "    \n",
    "\n",
    "ucb_cross['MATCH_SOURCE'] = [src_check(_) for _ in ucb_cross.HS_CEEB]\n",
    "ucb_cross['MATCH_SCORE'] = [name_check_score(n1,n2) for n1,n2 in zip(ucb_cross.HS_NAME, ucb_cross.MATCH_NAME) ]\n",
    "\n",
    "ceeb_cts = ucb_cross.groupby('HS_CEEB').count()\n",
    "ceeb_cts['HS_CEEB'] = ceeb_cts.index\n",
    "ceeb_dupes = ceeb_cts.HS_CEEB.loc[ceeb_cts.HS_NAME>1]\n",
    "ucb_cross['HS_DUPED'] = [_ in ceeb_dupes for _ in ucb_cross.HS_CEEB]\n",
    "\n",
    "print('Number of low-scoring matches by match source:')\n",
    "ucb_cross.groupby('MATCH_SOURCE').MATCH_SCORE.apply(lambda x: sum(x < match_cutoff))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There do appear to be some rows from the PSS and CCD datasets with match scores below the cut-off threshold I previously assigned. However, closer inspection reveals that many of these all duplicates of another entry with a higher match score. Although the original `hs_df` dataset had been previously de-duplicated, there are still around 60 CEEB codes which appear more than once in the crosswalk. This might be due to typos in school names, errors/inconsitent ZIP codes (eg. mailing vs. location), etc. Additionally, HS names may change over time, but will retain their CEEB code. When I merged on CEEB codes, duplicated codes were matched with names which are possibly different than what was indicated in the original dataset. For now I will just drop these."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "drops = ((ucb_cross.MATCH_SCORE<match_cutoff) & (ucb_cross.MATCH_SOURCE!=0))\n",
    "ucb_cross = ucb_cross.loc[[not _ for _ in drops]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's look at the low-quality matches coming from the Davenport crosswalk. It appears as though there are considerably more. I'm going to trust Davenport for the data quality here (at least, compared to the high school names entered on to college applications as they tend to be full over abbreviations and typos). As before I'll overwrite the `HS_NAME` with the `MATCH_NAME` column, and then I'll drop all duplications of `HS_CEEB` and `HS_NCES`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "ucb_cross.HS_NAME.loc[ucb_cross.MATCH_SOURCE==0] = ucb_cross.MATCH_NAME.loc[ucb_cross.MATCH_SOURCE==0]\n",
    "ucb_cross = ucb_cross.drop_duplicates(subset=['HS_CEEB','HS_NCES']) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# MTurk Completion\n",
    "We've now used up all of our \"nice\" datasets which would allow for automated matching. At this point we'll move the rest over to MTurk. The code below collects and  merges the responses:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/peter/Desktop/ODA/ceeb_nces_crosswalk/venv/lib/python3.8/site-packages/pandas/core/indexing.py:670: 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",
      "  iloc._setitem_with_indexer(indexer, value)\n"
     ]
    }
   ],
   "source": [
    "def resp_clean(x):\n",
    "    x = str(x)\n",
    "    if 'NCES School ID: ' in x:\n",
    "        x = x.split('NCES School ID: ')\n",
    "        return(x[-1])\n",
    "    \n",
    "    elif ' ' in x:\n",
    "        return('NA')\n",
    "    \n",
    "    elif 'grade' in x.lower():\n",
    "        return('NA')\n",
    "    \n",
    "    else:\n",
    "        return(x)\n",
    "\n",
    "def resp_agg(x):\n",
    "    votes = x.value_counts()\n",
    "    if np.max(votes) > np.sum(votes)/2.:\n",
    "        return(votes.idxmax())\n",
    "    \n",
    "    else:\n",
    "        nces = np.argmax([len(_) for _ in votes.index])\n",
    "        return('NA')\n",
    "\n",
    "results = pd.read_csv(path.join(MTURK_DIR,'mturk_results_v3_graded.csv'))\n",
    "results = results.loc[results.Approve=='x']\n",
    "results.replace(' ', np.nan, inplace=True)\n",
    "\n",
    "results['Answer'] = results['Answer.HS_NCES'] \n",
    "results['Answer'].loc[results['Answer.HS_NO_NCES.on']] = 'NA'\n",
    "\n",
    "resps_full = results[['WorkerId','Answer','Input.HS_NAME','Input.HS_CEEB']].rename(columns={'Input.HS_NAME':'HS_NAME','Input.HS_CEEB':'HS_CEEB'})\n",
    "resps_full['Answer'] = resps_full.Answer.apply(resp_clean)\n",
    "resps_pivot = resps_full.pivot_table(index='HS_CEEB',values='Answer',columns='WorkerId', aggfunc = lambda x: x)\n",
    "\n",
    "max_width = 4\n",
    "resps = []\n",
    "for i in range(resps_pivot.shape[0]):\n",
    "    row = resps_pivot.iloc[i]\n",
    "    ceeb = resps_pivot.index[i]\n",
    "    row = list(row.loc[pd.notnull(row)])\n",
    "    \n",
    "    while len(row)<max_width:\n",
    "        row += [np.nan]\n",
    "    \n",
    "    resp_row = {f'resp_{i}':resp for i,resp in zip(range(max_width),row)}\n",
    "    resp_row['HS_CEEB'] = str(ceeb)\n",
    "    \n",
    "    resps.append(resp_row)\n",
    "    \n",
    "resps = pd.DataFrame(resps) \n",
    "resps['HS_NCES'] = [resp_agg(resps.iloc[i,:4]) for i in range(resps.shape[0])] \n",
    "name_ceeb_df = resps_full[['HS_CEEB','HS_NAME']].drop_duplicates()\n",
    "resps = resps.merge(name_ceeb_df, on='HS_CEEB')\n",
    "\n",
    "resps[['HS_NAME','HS_CEEB','HS_NCES']].to_csv(path.join(DATA_DIR,'mturk_crosswalk.csv'),index=False)\n",
    "\n",
    "mturk_cross = resps[['HS_NAME','HS_CEEB','HS_NCES']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge in the MTurk responses\n",
    "mturk_cross = mturk_cross.replace('NA',np.nan).dropna(subset=['HS_NCES'])\n",
    "mturk_cross = hs_df_outstanding.merge(mturk_cross.rename(columns={'HS_NAME':'MATCH_NAME'})[['HS_CEEB','HS_NCES','MATCH_NAME']],on='HS_CEEB').drop_duplicates() \n",
    "\n",
    "ucb_cross = pd.concat([ucb_cross,mturk_cross]).drop_duplicates()\n",
    "\n",
    "matched_ceebs = set(ucb_cross.HS_CEEB)\n",
    "hs_df_outstanding = hs_df.loc[[_ not in matched_ceebs for _ in hs_df.HS_CEEB]]\n",
    "\n",
    "hs_df_outstanding.to_csv(path.join(MTURK_DIR,'outstanding_hs.csv'),index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Collect data for second HiT and export"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exporting Crosswalk"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Original Davenport crosswalk size:\n",
      "(18767, 25)\n",
      "Augmented crosswalk size:\n",
      "(21391, 10)\n",
      "Growth as percent of original size:\n",
      "14%\n"
     ]
    }
   ],
   "source": [
    "print('Original Davenport crosswalk size:')\n",
    "print(davenport_cross.shape)\n",
    "print('Augmented crosswalk size:')\n",
    "print(ucb_cross.shape)\n",
    "print('Growth as percent of original size:')\n",
    "p = ucb_cross.shape[0] - davenport_cross.shape[0]\n",
    "p /= davenport_cross.shape[0]  \n",
    "print(str(round(100*p))+'%')\n",
    "\n",
    "ucb_cross.to_csv(path.join(PROJ_DIR,'oda_nces_ceeb_crosswalk.csv'),index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "ceeb_nces_crosswalk",
   "language": "python",
   "name": "ceeb_nces_crosswalk"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}