Converting_pdf_into_data_College_Board.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 507,
   "id": "5aee44e0",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np \n",
    "import os\n",
    "import tabula\n",
    "import requests\n",
    "import PyPDF2\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 508,
   "id": "1c72dcda",
   "metadata": {},
   "outputs": [],
   "source": [
    "from tabula import read_pdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 509,
   "id": "c4036104",
   "metadata": {},
   "outputs": [],
   "source": [
    "root_directory = 'C:\\\\Users\\Katri\\OneDrive\\Desktop\\College_Board_SAT_Data\\data'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 510,
   "id": "6e20cb3a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(columns=['Year','State',\"Total_number_of_HS_students\",\"Participation_rate\",'Demo_Group', 'Num_Test_Takers','Ave_Score', 'ERW_Ave_Score', 'Math_Ave_Score',\n",
    "       'Percent_met_both_benchmarks', 'Percent_met_no_benchmark',\n",
    "       'Percent_Pass_ERW', 'Percent_Pass_Math'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 511,
   "id": "da88de0f",
   "metadata": {},
   "outputs": [],
   "source": [
    "for subdir, dirs, files in os.walk(root_directory):\n",
    "    # Loop through the files in each directory\n",
    "    for file in files:\n",
    "        year = extract_year(str(file))\n",
    "        #Figure out what state the data is for \n",
    "        state= extract_state_name(str(file))\n",
    "        #Figure out on what pages the relevant data is stored\n",
    "        filename = os.path.join(subdir, file)\n",
    "        page_1, page_2 = find_pages_with_phrases(filename)\n",
    "        #Convert to table \n",
    "        table = tabula.read_pdf(filename,pages=page_2, multiple_tables=True,silent=True, area=[99.0, 9.0, 730, 589.0])\n",
    "        if len(table)>0:\n",
    "            #Figure out how many test takes there are \n",
    "            test_takers_total = table[0].loc[table[0][\"Total\"] == \"Total\", \"Test Takers\"].values[0]\n",
    "            n = total_number_of_hs_students(filename,page_1,test_takers_total)\n",
    "            #Change the entries with no_response\n",
    "            no_response(table[0])\n",
    "            #convert to a pandas data frame \n",
    "            df = df.append(page_five(table[0],state,year,n))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 512,
   "id": "1bcc74a0",
   "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>Year</th>\n",
       "      <th>State</th>\n",
       "      <th>Total_number_of_HS_students</th>\n",
       "      <th>Participation_rate</th>\n",
       "      <th>Demo_Group</th>\n",
       "      <th>Num_Test_Takers</th>\n",
       "      <th>Ave_Score</th>\n",
       "      <th>ERW_Ave_Score</th>\n",
       "      <th>Math_Ave_Score</th>\n",
       "      <th>Percent_met_both_benchmarks</th>\n",
       "      <th>Percent_met_no_benchmark</th>\n",
       "      <th>Percent_Pass_ERW</th>\n",
       "      <th>Percent_Pass_Math</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017</td>\n",
       "      <td>AL</td>\n",
       "      <td>49102</td>\n",
       "      <td>4.873529</td>\n",
       "      <td>Total</td>\n",
       "      <td>2393</td>\n",
       "      <td>1165.0</td>\n",
       "      <td>593.0</td>\n",
       "      <td>572.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>86.0</td>\n",
       "      <td>64.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017</td>\n",
       "      <td>AL</td>\n",
       "      <td>49102</td>\n",
       "      <td>4.873529</td>\n",
       "      <td>Took Essay1</td>\n",
       "      <td>887</td>\n",
       "      <td>1226.0</td>\n",
       "      <td>621.0</td>\n",
       "      <td>605.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>91.0</td>\n",
       "      <td>74.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017</td>\n",
       "      <td>AL</td>\n",
       "      <td>49102</td>\n",
       "      <td>4.873529</td>\n",
       "      <td>American Indian/Alaska Native</td>\n",
       "      <td>15</td>\n",
       "      <td>1049.0</td>\n",
       "      <td>538.0</td>\n",
       "      <td>511.0</td>\n",
       "      <td>47.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>73.0</td>\n",
       "      <td>47.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017</td>\n",
       "      <td>AL</td>\n",
       "      <td>49102</td>\n",
       "      <td>4.873529</td>\n",
       "      <td>Asian</td>\n",
       "      <td>199</td>\n",
       "      <td>1272.0</td>\n",
       "      <td>608.0</td>\n",
       "      <td>664.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>84.0</td>\n",
       "      <td>87.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017</td>\n",
       "      <td>AL</td>\n",
       "      <td>49102</td>\n",
       "      <td>4.873529</td>\n",
       "      <td>Black/African American</td>\n",
       "      <td>501</td>\n",
       "      <td>992.0</td>\n",
       "      <td>513.0</td>\n",
       "      <td>479.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>28.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year State Total_number_of_HS_students  Participation_rate  \\\n",
       "0  2017    AL                       49102            4.873529   \n",
       "1  2017    AL                       49102            4.873529   \n",
       "2  2017    AL                       49102            4.873529   \n",
       "3  2017    AL                       49102            4.873529   \n",
       "4  2017    AL                       49102            4.873529   \n",
       "\n",
       "                      Demo_Group Num_Test_Takers  Ave_Score  ERW_Ave_Score  \\\n",
       "0                          Total            2393     1165.0          593.0   \n",
       "1                    Took Essay1             887     1226.0          621.0   \n",
       "2  American Indian/Alaska Native              15     1049.0          538.0   \n",
       "3                          Asian             199     1272.0          608.0   \n",
       "4         Black/African American             501      992.0          513.0   \n",
       "\n",
       "   Math_Ave_Score  Percent_met_both_benchmarks  Percent_met_no_benchmark  \\\n",
       "0           572.0                         62.0                      12.0   \n",
       "1           605.0                         72.0                       7.0   \n",
       "2           511.0                         47.0                      27.0   \n",
       "3           664.0                         78.0                       6.0   \n",
       "4           479.0                         26.0                      35.0   \n",
       "\n",
       "   Percent_Pass_ERW  Percent_Pass_Math  \n",
       "0              86.0               64.0  \n",
       "1              91.0               74.0  \n",
       "2              73.0               47.0  \n",
       "3              84.0               87.0  \n",
       "4              64.0               28.0  "
      ]
     },
     "execution_count": 512,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 518,
   "id": "7de35334",
   "metadata": {},
   "outputs": [],
   "source": [
    "output_dir = './Output_data'\n",
    "if not os.path.exists(output_dir):\n",
    "    os.makedirs(output_dir)\n",
    "\n",
    "# Export the dataframe to a CSV file in the output directory\n",
    "output_file = os.path.join(output_dir, 'output_file.csv')\n",
    "df.to_csv(output_file, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 520,
   "id": "9d6830f6",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "output_file = os.path.join(output_dir, 'output_file.xlsx')\n",
    "df.to_excel(output_file, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 416,
   "id": "549625b5",
   "metadata": {},
   "outputs": [],
   "source": [
    "def total_number_of_hs_students(file,page,test_takers_total):\n",
    "    def delete_string_from_list(lst, string_to_delete):\n",
    "        return [s for s in lst if s != string_to_delete]\n",
    "    def find_next_element(lst):\n",
    "        # Find the index of the element containing \"SAT\", \"Participation\", and \"Rate\"\n",
    "        index = -1\n",
    "        for i, s in enumerate(lst):\n",
    "            if \"SAT\" in s and \"Participation\" in s and \"Rate\" in s:\n",
    "                index = i\n",
    "                break\n",
    "        if index == -1:\n",
    "            return None  # The target string is not found\n",
    "\n",
    "        # Find the first element AFTER the target string that does NOT contain a %\n",
    "        for i in range(index+1, len(lst)):\n",
    "            s = lst[i]\n",
    "            if \"%\" not in s:\n",
    "                return int(s.replace(\",\", \"\"))\n",
    "        return None  # No matching element is found\n",
    "    \n",
    "    with open(file, 'rb') as pdf_file:\n",
    "                # Create a PyPDF2 PdfFileReader object\n",
    "                pdf_reader = PyPDF2.PdfFileReader(pdf_file)\n",
    "                page = pdf_reader.getPage(page-1)\n",
    "                text = page.extractText()\n",
    "                text = text.replace('\\t', ' ') \n",
    "                text= text.split(\"\\n\")\n",
    "    return find_next_element(delete_string_from_list(text,test_takers_total))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 506,
   "id": "90ce66e9",
   "metadata": {},
   "outputs": [],
   "source": [
    "#This function takes the table from page 5 of the report, cleans the data and turns it into a dataframe \n",
    "def page_five(df,state,year,n):\n",
    "    df.dropna(subset=[list(df.columns)[0]],inplace=True)\n",
    "    df.dropna(axis=1,how='all',inplace=True)\n",
    "    df.reset_index(drop = True)\n",
    "    df.columns = [\"Demo_Group\",\"Num_Test_Takers\",\"Ave_Score\",\"ERW_Ave_Score\",\"Math_Ave_Score\",\"Percent_met_both_benchmarks\",\n",
    "              \"Percent\",\"Percent_met_no_benchmark\"]\n",
    "    \n",
    "    df[['Percent_Pass_ERW', 'Percent_Pass_Math']] = df['Percent'].str.split(' ', expand=True)\n",
    "    df.drop(columns=['Percent'], inplace=True)\n",
    "    df = df[~df['Num_Test_Takers'].str.contains('Test Takers')].copy()\n",
    "    df[\"Num_Test_Takers\"] = df[\"Num_Test_Takers\"].str.split().str[0]\n",
    "    \n",
    "    cols_to_convert = [\"Percent_met_both_benchmarks\", \"Percent_met_no_benchmark\", \"Percent_Pass_ERW\", \n",
    "                       \"Percent_Pass_Math\",\"Ave_Score\",\"ERW_Ave_Score\",\"Math_Ave_Score\"]\n",
    "    for col in cols_to_convert:\n",
    "        # Replace the '%' character with an empty string\n",
    "        df[col] = df[col].str.replace('%', '')\n",
    "        # Convert the values to integers\n",
    "        df[col] = pd.to_numeric(df[col], errors='coerce')\n",
    "        \n",
    "    df[\"Num_Test_Takers\"] = df[\"Num_Test_Takers\"].str.replace(\",\",\"\")\n",
    "    df[\"Num_Test_Takers\"] = pd.to_numeric(df[\"Num_Test_Takers\"], downcast='integer')\n",
    "    \n",
    "    df[\"State\"] = state\n",
    "    df[\"Year\"] = year\n",
    "    df[\"Total_number_of_HS_students\"] = n \n",
    "    df = df.reset_index(drop=True)\n",
    "    total_hs_students = df.at[0, 'Total_number_of_HS_students']\n",
    "    num_test_takers = df.at[0, 'Num_Test_Takers']\n",
    "    df['Participation_rate'] = (num_test_takers / total_hs_students) * 100\n",
    "    df['Demo_Group'] = df['Demo_Group'].replace('Native Hawaian/Other Pacific Islander', 'Native Hawaiian/Other Pacific Islander')\n",
    "    df = df[~df['Demo_Group'].isin(['Used at any point', 'Did not use'])]\n",
    "    df = df[['Year','State',\"Total_number_of_HS_students\",\"Participation_rate\",'Demo_Group', 'Num_Test_Takers','Ave_Score', 'ERW_Ave_Score', 'Math_Ave_Score',\n",
    "       'Percent_met_both_benchmarks', 'Percent_met_no_benchmark',\n",
    "       'Percent_Pass_ERW', 'Percent_Pass_Math']]\n",
    "    num_columns = ['Total_number_of_HS_students', 'Participation_rate', 'Num_Test_Takers', 'Ave_Score', 'ERW_Ave_Score',\n",
    "       'Math_Ave_Score', 'Percent_met_both_benchmarks',\n",
    "       'Percent_met_no_benchmark', 'Percent_Pass_ERW', 'Percent_Pass_Math']\n",
    "    for col in num_columns:\n",
    "        # Convert the values to integers\n",
    "        df[col] = pd.to_numeric(df[col], errors='coerce')\n",
    "\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 431,
   "id": "1de3f051",
   "metadata": {},
   "outputs": [],
   "source": [
    "def no_response(df):\n",
    "    mapping = {\n",
    "    'Two or More Races': 'Race: No Response',\n",
    "    'Another Language': 'Language: No Response',\n",
    "    'Graduate Degree': 'Parental Education: No Response',\n",
    "    'Male': 'Gender: No Response'}\n",
    "    # Iterate over the Demo_Group column and update the \"No Response\" entries\n",
    "    prev_entry = None\n",
    "    for i, entry in df[df.columns[0]].iteritems():\n",
    "        if entry == 'No Response' or entry == \"Another/ No Response\":\n",
    "            if prev_entry in mapping:\n",
    "                df.at[i, df.columns[0]] = mapping[prev_entry]\n",
    "        else:\n",
    "            prev_entry = entry"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 419,
   "id": "103b70d1",
   "metadata": {},
   "outputs": [],
   "source": [
    "#This function takes the pdf file and extracts from it the number \n",
    "#of high school students in the state that year. \n",
    "def helper_function(filename,page_1):\n",
    "    def find_next_element(lst):\n",
    "        # Find the index of the element containing \"SAT\", \"Participation\", and \"Rate\"\n",
    "        index = -1\n",
    "        for i, s in enumerate(lst):\n",
    "            if \"SAT\" in s and \"Participation\" in s and \"Rate\" in s:\n",
    "                index = i\n",
    "                break\n",
    "        if index == -1:\n",
    "            return None  # The target string is not found\n",
    "\n",
    "        # Find the first element AFTER the target string that does NOT contain a %\n",
    "        for i in range(index+1, len(lst)):\n",
    "            s = lst[i]\n",
    "            if \"%\" not in s:\n",
    "                return int(s.replace(\",\", \"\"))\n",
    "        return None  # No matching element is found\n",
    "    if type(page_1)==int:\n",
    "        with open(filename, 'rb') as pdf_file:\n",
    "                # Create a PyPDF2 PdfFileReader object\n",
    "                pdf_reader = PyPDF2.PdfFileReader(pdf_file)\n",
    "                page = pdf_reader.getPage(page_1-1)\n",
    "                text = page.extractText()\n",
    "                text = text.split(\"\\n\")\n",
    "                num_students = find_next_element(text)\n",
    "        return num_students\n",
    "    else: return None "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 420,
   "id": "b1c761c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Write a helper function that helps you find on what pages the correct tables are. \n",
    "def find_pages_with_phrases(pdf_file_path):\n",
    "    pdf_file = open(pdf_file_path, 'rb')\n",
    "    pdf_reader = PyPDF2.PdfFileReader(pdf_file)\n",
    "\n",
    "    page_1_keywords = ['Suite', 'Participation', 'Summary', 'NMSQT']\n",
    "    page_2_keywords = ['Participation', 'Performance', 'Hawaiian', 'Benchmark', 'Female']\n",
    "    page_1 = None\n",
    "    page_2 = None\n",
    "\n",
    "    for page in range(pdf_reader.numPages):\n",
    "        text = pdf_reader.getPage(page).extractText()\n",
    "\n",
    "        # Check if all page 1 keywords are in the text\n",
    "        if all(keyword in text for keyword in page_1_keywords):\n",
    "            page_1 = page + 1\n",
    "\n",
    "        # Check if all page 2 keywords are in the text\n",
    "        if all(keyword in text for keyword in page_2_keywords):\n",
    "            page_2 = page + 1\n",
    "\n",
    "        # If we've found both pages, we can break out of the loop early\n",
    "        if page_1 is not None and page_2 is not None:\n",
    "            break\n",
    "\n",
    "    pdf_file.close()\n",
    "    \n",
    "    return page_1, page_2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 421,
   "id": "643ec622",
   "metadata": {},
   "outputs": [],
   "source": [
    "#This function takes in the file name in a string format and extracts the year. \n",
    "def extract_year(input_string):\n",
    "    # Define the regular expression pattern to match four-digit years\n",
    "    pattern = r\"\\d{4}\"\n",
    "\n",
    "    # Search for the pattern in the input string\n",
    "    match = re.search(pattern, input_string)\n",
    "\n",
    "    # Check if a match was found\n",
    "    if match:\n",
    "        # Get the matched string (the four-digit year)\n",
    "        year = match.group()\n",
    "\n",
    "        # Return the year\n",
    "        return int(year)\n",
    "    else:\n",
    "        # Return None if no four-digit year was found in the input string\n",
    "        return None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 488,
   "id": "287f3d31",
   "metadata": {},
   "outputs": [],
   "source": [
    "#This function takes in the file name in a string format and extracts the year.\n",
    "\n",
    "def extract_state_name(input_string):\n",
    "    # Define a list of USA state names\n",
    "    states = ['alabama','alaska','arizona','arkansas','california', 'colorado', 'connecticut', 'delaware', 'florida', 'georgia',\n",
    " 'hawaii', 'idaho', 'illinois','indiana','iowa','kansas','kentucky','louisiana','maine',\n",
    " 'maryland', 'massachusetts','michigan','minnesota','mississippi','missouri','montana','nebraska',\n",
    " 'nevada','new-hampshire','new-jersey','new-mexico','new-york','north-carolina','north-dakota','ohio','oklahoma',\n",
    " 'oregon', 'pennsylvania', 'rhode-island', 'south-carolina', 'south-dakota', 'tennessee', 'texas', 'utah',\n",
    " 'vermont','virginia', 'washington', 'west-virginia', 'wisconsin', 'wyoming', 'puerto-rico', 'virgin-islands', 'district-columbia']\n",
    "    def state_name_to_code(state_name):\n",
    "        state_codes = {\n",
    "        'alabama': 'AL', 'alaska': 'AK', 'arizona': 'AZ', 'arkansas': 'AR', 'california': 'CA',\n",
    "        'colorado': 'CO', 'connecticut': 'CT', 'delaware': 'DE', 'florida': 'FL', 'georgia': 'GA',\n",
    "        'hawaii': 'HI', 'idaho': 'ID', 'illinois': 'IL', 'indiana': 'IN', 'iowa': 'IA', 'kansas': 'KS',\n",
    "        'kentucky': 'KY', 'louisiana': 'LA', 'maine': 'ME', 'maryland': 'MD', 'massachusetts': 'MA',\n",
    "        'michigan': 'MI', 'minnesota': 'MN', 'mississippi': 'MS', 'missouri': 'MO', 'montana': 'MT',\n",
    "        'nebraska': 'NE', 'nevada': 'NV', 'new-hampshire': 'NH', 'new-jersey': 'NJ', 'new-mexico': 'NM',\n",
    "        'new-york': 'NY', 'north-carolina': 'NC', 'north-dakota': 'ND', 'ohio': 'OH', 'oklahoma': 'OK',\n",
    "        'oregon': 'OR', 'pennsylvania': 'PA', 'rhode-island': 'RI', 'south-carolina': 'SC', 'south-dakota': 'SD',\n",
    "        'tennessee': 'TN', 'texas': 'TX', 'utah': 'UT', 'vermont': 'VT', 'virginia': 'VA', 'washington': 'WA',\n",
    "        'west-virginia': 'WV', 'wisconsin': 'WI', 'wyoming': 'WY', 'puerto-rico': 'PR', 'virgin-islands': 'VI',\n",
    "        'district-columbia': 'DC', \"district-of-columbia\":\"DC\"}\n",
    "        return state_codes.get(state_name, None)\n",
    "    # Define a regular expression pattern to match USA state names\n",
    "    \n",
    "    pattern = r'\\b(' + '|'.join(states) + r')\\b'\n",
    "\n",
    "    # Search for the pattern in the input string\n",
    "    match = re.search(pattern, input_string, re.IGNORECASE)\n",
    "\n",
    "    # Check if a match was found\n",
    " \n",
    "    if match:\n",
    "        # Get the matched string (the state name)\n",
    "        state_name = match.group()\n",
    "        state = state_name_to_code(state_name )\n",
    "        # Return the state name\n",
    "        return state\n",
    "    else:\n",
    "        # Return None if no state name was found in the input string\n",
    "        return None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "56387506",
   "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.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}