1944_WIW_script.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# python 3.7.6\n",
    "import csv # version 1.0\n",
    "import os \n",
    "import re # version 2.2.1\n",
    "import pandas as pd # version 1.0.1\n",
    "import itertools"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'−', '–'}\n"
     ]
    }
   ],
   "source": [
    "# open,read,and slice file\n",
    "# this uses the new updated file\n",
    "fh = open(r\"1944_ocr_updated.txt\", \"r\", encoding=\"utf8\").read()\n",
    "# slice out introduction and end\n",
    "segment = fh[17354:-4770] \n",
    "\n",
    "segment = segment.replace(\"..\", \".\")\n",
    "segment = segment.replace(\"  \", \" \")\n",
    "segment = segment.replace(\"—\", \"-\")\n",
    "\n",
    "# \"-\\n\" indicates words broken by lines, so replace it with empty string\n",
    "segment = segment.replace(\"-\\n\",\"\").replace(\"_\\n\",\"\")\n",
    "\n",
    "# remove redundant strings and certain non-ASCII chars\n",
    "for apos in [\"‘\", \"’\"]:\n",
    "    segment = segment.replace(apos, \"\\'\")\n",
    "    \n",
    "for apos in [\"“\", \"”\"]:\n",
    "    segment = segment.replace(apos, \"\\\"\")\n",
    "        \n",
    "for char in [\"-\\n\", \"_\\n\", \"±\", \"■\", \"©\", \"™\", \"•\", \"£\", \"°\", \"«\", \"»\", \"§\"]:\n",
    "    segment = segment.replace(char, \"\")\n",
    "\n",
    "# replace misOCRed letters\n",
    "for char in [\"Ö\", \"Ó\"]:\n",
    "    segment = segment.replace(char, \"O\")\n",
    "segment = segment.replace(\"ô\", \"o\")\n",
    "\n",
    "for char in [\"ü\", \"ū\"]:\n",
    "    segment = segment.replace(char, \"u\")\n",
    "\n",
    "for char in [\"é\", \"ë\"]:\n",
    "    segment = segment.replace(char, \"e\")\n",
    "segment = segment.replace(\"É\", \"E\")\n",
    "\n",
    "segment = segment.replace(\"Ā\", \"A\")\n",
    "segment = segment.replace(\"ä\", \"a\")\n",
    "segment = segment.replace(\"Č\", \"C\")\n",
    "segment = segment.replace(\"Ś\", \"S\")\n",
    "segment = segment.replace(\"š\", \"s\")\n",
    "segment = segment.replace(\"í\", \"i\")\n",
    "segment = segment.replace(\"ź\", \"z\")\n",
    "segment = segment.replace(\"€\", \"C\")\n",
    "segment = segment.replace(\"„\", \".,\")\n",
    "\n",
    "# final check for non-ascii chars\n",
    "rem_ascii = re.findall(\"[^\\x00-\\x7F]\", segment)\n",
    "print(set(rem_ascii))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Names found: 2050\n",
      "BILLUPS, POPE BARROW-Lawyer.\n",
      "b. Oct. 11, 1889, Athens, Ga.; s. William D. and\n",
      "Elizabeth (Tucker) Blllups; m. Edna Pierre\n",
      "Lartigue, April 4, 1919; one adopted child, William Pope Robinson; educ. Fla. Baptist Acad.,\n",
      "1904-10; Fla. A. & M. Coll., 1911-12; N. Y. Univ.\n",
      "Law Sch., 1913-16; LL.B., 1916; Stenographer,\n",
      "Jacksonville, Fla., 1908-14; member of editorial\n",
      "staff, Atlantic City Review, 1914-15; admitted to\n",
      "the bar, 1917; practicing lawyer since 1917;\n",
      "elected Member of Assembly, N. Y. State Legislature, Nov., 1924, and served for term of 1925;\n",
      "mem. Monarch Lodge No. 45, I. B. P. O. E. of\n",
      "W. (Exalted Ruler); K. of P. (Grand Lodge\n",
      "Atty); I. B. P. O. of Moose (Supreme Lodge\n",
      "Atty.); J. R. McGill Lodge G. U. O. of O. F.;\n",
      "Clubmen's Beneficial League, Coachmen's Beneficial League, Sampson Lodge No. 65, F. & A.\n",
      "M.; Ires., 1927-Jan. 1929; A^sn. of Trade and\n",
      "Commerce; Met. Museum of Art; Museum of\n",
      "Natural History; Pol. Republican; Relig. A. M.\n",
      "E. Church; Office, 206 Broadway; Residence,\n",
      "26 W. 139th St., New York City.\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# find all names using regex\n",
    "# note that this method misses names without dashes at end\n",
    "name_list = re.findall(r\"[A-Z(?:Mc)]{4,}[\\.\\,][ ]?[A-Z(?:Mc)][\\s\\S]+?[-–—]\", segment)\n",
    "\n",
    "print(\"Names found:\" , len(name_list))\n",
    "\n",
    "# split base string into a list of strings, each starting with person name\n",
    "\n",
    "# format names into name|name1|name2|...\n",
    "name_template = \"({})\".format(\"|\".join(re.escape(s) for s in name_list))\n",
    "\n",
    "# split base string based on names\n",
    "segment_split = re.split(name_template, segment) \n",
    "\n",
    "# join back the names to the front of each string\n",
    "bio_split = [\"\".join(x) for x in itertools.zip_longest([\"\"] + segment_split[1::2], segment_split[::2], fillvalue='')]\n",
    "print(bio_split[150])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# revised approach\n",
    "bio_data = []\n",
    "for i, person in enumerate(bio_split[1:]): # 0th element unnecessary\n",
    "    \n",
    "    bio_dict = {}\n",
    "    \n",
    "    # name\n",
    "    bio_dict[\"name\"] = name_list[i].strip(\"-–—\").replace(\"\\n\",\" \") \n",
    "\n",
    "    # occupation\n",
    "    occ = re.search(r\"(?<=—)[\\s\\S]*?(?=b\\.)\", person) \n",
    "    if occ is not None:\n",
    "        # strip irrelevant chars from sides, then replace any remaining \\n within string\n",
    "        bio_dict[\"occ\"] = occ.group().strip(\"-–—.\\n ,\").replace(\"\\n\",\" \") \n",
    "    else:\n",
    "        # note that \"—\" might have been recognized as \"-\"\n",
    "        # next option: check \"-\"\n",
    "        occ2 = re.search(r\"(?<=-)[\\s\\S]*?(?=b\\.)\", person)\n",
    "        if occ2 is not None:\n",
    "            bio_dict[\"occ\"] = occ2.group().strip(\"-–—.\\n ,\").replace(\"\\n\",\" \")\n",
    "        else:\n",
    "            bio_dict[\"occ\"] = None\n",
    "    \n",
    "    # birth details: birthdate and birthplace\n",
    "    birthdetails = re.search(r\"(?<=b\\.).*?;\", person) \n",
    "    if birthdetails is not None:\n",
    "        # date of birth\n",
    "        birthdate = re.search(r\"[A-Z][a-zA-Z\\. ]+\\d+[,\\.].*?\\d{4}\", birthdetails.group())\n",
    "        if birthdate is not None:\n",
    "            bio_dict[\"birthdate\"] = birthdate.group()\n",
    "        else:\n",
    "            bio_dict[\"birthdate\"] = None\n",
    "        \n",
    "        # place of birth\n",
    "        birthplace = re.search(r\"[A-Z][A-Za-z\\.\\s]+,\\s[A-Za-z].*;\", birthdetails.group())\n",
    "        if birthplace is not None:\n",
    "            bio_dict[\"birthplace\"] = birthplace.group().strip(\"; \")\n",
    "        else:\n",
    "            bio_dict[\"birthplace\"] = None\n",
    "    else:\n",
    "        # no birthdetails found\n",
    "        bio_dict['birthdate'] = None\n",
    "        bio_dict['birthplace'] = None\n",
    "    \n",
    "    # address\n",
    "    \n",
    "    # first search for two addresses: address + residence\n",
    "    address_residence = re.search(r\"(?:Address|Office)[,\\.]([\\s\\S]+?)(?:Address|Residence)[,\\.]([\\s\\S]+)\", person)\n",
    "    \n",
    "    # if two addresses are found\n",
    "    if address_residence is not None: \n",
    "        # first group is curaddress\n",
    "        bio_dict[\"curaddress\"] = address_residence.group(1).replace(\"\\n\",\" \").strip(\";, \")\n",
    "        # second group is residence\n",
    "        bio_dict[\"residence\"] = address_residence.group(2).replace(\"\\n\",\" \").strip(\";, \")\n",
    "        \n",
    "    # else, try for one address\n",
    "    else:\n",
    "        address = re.search(r\"(?:Address|Office|Residence)[,\\.]([\\s\\S]+)\", person)\n",
    "        if address is not None:\n",
    "            # assume that single address is residence\n",
    "            bio_dict[\"residence\"] = address.group(1).replace(\"\\n\",\" \").strip(\";, \")\n",
    "        else:\n",
    "            bio_dict[\"curaddress\"] = None\n",
    "            bio_dict[\"residence\"] = None\n",
    "    \n",
    "    bio_data.append(bio_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# dataframe\n",
    "biodf = pd.DataFrame(bio_data).fillna('')\n",
    "\n",
    "# rearrange columns\n",
    "biodf.columns.values\n",
    "biodf = biodf[['name', 'birthplace', 'birthdate', 'occ', 'curaddress', 'residence']]\n",
    "biodf.index.name = \"persid\"\n",
    "\n",
    "# correct Illinois OCR errors\n",
    "illinois_errors = ['[,\\.][ ]?[TiI1lL]{3}[ \\.]', '[,\\.][ ]?[HLDnU][TiI1lL][ \\.]', '[,\\.][ ]?IUL[ \\.]', '[,\\.][ ]?m.', ',[ ]?IU', ', I1L', ', tit']\n",
    "biodf['curaddress'] = biodf['curaddress'].replace(to_replace = illinois_errors, value = ', Ill.', regex = True)\n",
    "biodf['residence'] = biodf['residence'].replace(to_replace = illinois_errors, value = ', Ill.', regex = True)\n",
    "biodf['birthplace'] = biodf['birthplace'].replace(to_replace = illinois_errors, value = ', Ill.', regex = True)\n",
    "\n",
    "# correct NY/NC errors\n",
    "biodf['curaddress'] = biodf['curaddress'].replace('N[ ]?[,\\.][ ]?Y[ ]?[\\s\\S]', 'N. Y.', regex = True)\n",
    "biodf['residence'] = biodf['residence'].replace('N[ ]?[,\\.][ ]?Y[ ]?[\\s\\S]', 'N. Y.', regex = True)\n",
    "biodf['curaddress'] = biodf['curaddress'].replace(', L\\. lv ', ', N. Y.', regex = True)\n",
    "biodf['residence'] = biodf['residence'].replace(', L\\. lv ', ', N. Y.', regex = True)\n",
    "biodf['curaddress'] = biodf['curaddress'].replace('N[ ]?[,\\.][ ]?C[ ]?[\\s\\S]', 'N. C.', regex = True)\n",
    "biodf['residence'] = biodf['residence'].replace('N[ ]?[,\\.][ ]?C[ ]?[\\s\\S]', 'N. C.', regex = True)\n",
    "\n",
    "# additional uncaught states\n",
    "biodf['curaddress'] = biodf['curaddress'].replace(', N\\. Carolina\\.', 'N. C.', regex = True)\n",
    "biodf['residence'] = biodf['residence'].replace(', N\\. Carolina\\.', 'N. C.', regex = True)\n",
    "biodf['curaddress'] = biodf['curaddress'].replace(' 9a', ', Ga', regex = True)\n",
    "biodf['residence'] = biodf['residence'].replace(' 9a', ', Ga', regex = True)\n",
    "biodf['curaddress'] = biodf['curaddress'].replace(', Mass,', ', Mass.', regex = True)\n",
    "biodf['residence'] = biodf['residence'].replace(', Mass,', ', Mass.', regex = True)\n",
    "biodf['curaddress'] = biodf['curaddress'].replace(', Louisiana\\.', ', La.', regex = True)\n",
    "biodf['residence'] = biodf['residence'].replace(', Louisiana\\.', ', La.', regex = True)\n",
    "biodf['curaddress'] = biodf['curaddress'].replace(', FU\\.', ', Fla.', regex = True)\n",
    "biodf['residence'] = biodf['residence'].replace(', FU\\.', ', Fla.', regex = True)\n",
    "biodf['curaddress'] = biodf['curaddress'].replace('Ha\\.', 'Pa.', regex = True)\n",
    "biodf['residence'] = biodf['residence'].replace('Ha\\.', 'Pa.', regex = True)\n",
    "biodf['curaddress'] = biodf['curaddress'].replace('Washington[\\.,][ ]?D[\\.,][\\s\\S]+', 'Washington, D. C.', regex = True)\n",
    "biodf['residence'] = biodf['residence'].replace('Washington[\\.,][ ]?D[\\.,][\\s\\S]+', 'Washington, D. C.', regex = True)\n",
    "biodf['curaddress'] = biodf['curaddress'].replace('Washington[\\s\\S]{0,10}C[ \\.,]', 'Washington, D. C.', regex = True)\n",
    "biodf['residence'] = biodf['residence'].replace('Washington[\\s\\S]{0,10}C[ \\.,]', 'Washington, D. C.', regex = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'^', ']', '|', '6', '*', ';', ':', '4', '1', '(', '2', '>', '_', '8', '~', '#', '$', '9', '!', '0', ')', '7', '5', '?', '\"', '\\x0c', '3', \"'\", '/'}\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>birthplace</th>\n",
       "      <th>birthdate</th>\n",
       "      <th>occ</th>\n",
       "      <th>curaddress</th>\n",
       "      <th>residence</th>\n",
       "      <th>name_maiden</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>persid</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>ALEXANDER, ERNEST RPhysician. b. Nashville, Te...</td>\n",
       "      <td>Nashville, Tenn.</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>234 W. 139th St., New York, N. Y. He completed...</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>ALLISON, ANDREW JAC \"SON</td>\n",
       "      <td>Nashville, Tenn.</td>\n",
       "      <td>Sept. 2, 1892</td>\n",
       "      <td>Alumn: Secretary</td>\n",
       "      <td>Fisk University</td>\n",
       "      <td>923 18th Ave., N., Nashville, Tenn. In 1918 he...</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>WWSW, Pittsburg, Pa. During I933 he made radio...</td>\n",
       "      <td></td>\n",
       "      <td>Aug. 1. 1887</td>\n",
       "      <td>Clergyman</td>\n",
       "      <td>3301 Indiana Ave.; Home</td>\n",
       "      <td>3932 Grand Blvd., Chicago, Ill. While Pastor i...</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>BAILEY, JOHN ALEXANDER HOLMES Dentist. b. May ...</td>\n",
       "      <td></td>\n",
       "      <td>May 11. 1894</td>\n",
       "      <td></td>\n",
       "      <td>120 Hamilton Ave.</td>\n",
       "      <td>444 S. Central Ave., Columbus, Ohio.</td>\n",
       "      <td>Williams</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157</th>\n",
       "      <td>BLACKWELL, JR., JAMES HEYWARDPhysician. b. Mar...</td>\n",
       "      <td></td>\n",
       "      <td>Dec. 10. 1922</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Prof.) James Heyward and Annie Estelle (Jardon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1908</th>\n",
       "      <td>WILSON, ARTHUR JEWELL b. Oct. 30, I900, Omaha,...</td>\n",
       "      <td>Omaha, Neb.</td>\n",
       "      <td>Oct. 30, 1900</td>\n",
       "      <td></td>\n",
       "      <td>3621 State St.</td>\n",
       "      <td>5825 Michigan Blvd., Chicago, Ill.</td>\n",
       "      <td>Jewell</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1981</th>\n",
       "      <td>DISHOND, MRS. GERALDYN HODGES *. July 29, IS94...</td>\n",
       "      <td>Sch., Chicago 111., lM-^</td>\n",
       "      <td></td>\n",
       "      <td>'12; Univ. of Chicago. 1912'15; Ph.B., 1915; T...</td>\n",
       "      <td>2370 Seventh Avenue, New York, N. Y.</td>\n",
       "      <td>245 West 139th St. New York, N. Y. First Negro...</td>\n",
       "      <td>Powell</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1989</th>\n",
       "      <td>FAULKNER, GEORGIA M. DE BAPTISTESocial Worker....</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>3736 S. Michigan Ave., Chicago, Ill.</td>\n",
       "      <td>Brisco</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2025</th>\n",
       "      <td>PRICE, WILLARD J. b. Sept. I9, ISSI, Danville,...</td>\n",
       "      <td>Danville, Va.</td>\n",
       "      <td>Sept. 19, 1881</td>\n",
       "      <td>Executive Secretary, Urban League</td>\n",
       "      <td></td>\n",
       "      <td>40 Putnam Ave., Brooklyn, N. Y. RAGLAND, JOHN ...</td>\n",
       "      <td>#603</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2042</th>\n",
       "      <td>WALLS, ELLIE A Educator. educ. A. B., Fisk Uni...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Jack Yates High School, Houston, Texas. WALRON...</td>\n",
       "      <td>127 East 23rd St. New York. N. Y. His book, \"T...</td>\n",
       "      <td>now the New York School of Social Work</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>131 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                     name  \\\n",
       "persid                                                      \n",
       "17      ALEXANDER, ERNEST RPhysician. b. Nashville, Te...   \n",
       "31                               ALLISON, ANDREW JAC \"SON   \n",
       "70      WWSW, Pittsburg, Pa. During I933 he made radio...   \n",
       "78      BAILEY, JOHN ALEXANDER HOLMES Dentist. b. May ...   \n",
       "157     BLACKWELL, JR., JAMES HEYWARDPhysician. b. Mar...   \n",
       "...                                                   ...   \n",
       "1908    WILSON, ARTHUR JEWELL b. Oct. 30, I900, Omaha,...   \n",
       "1981    DISHOND, MRS. GERALDYN HODGES *. July 29, IS94...   \n",
       "1989    FAULKNER, GEORGIA M. DE BAPTISTESocial Worker....   \n",
       "2025    PRICE, WILLARD J. b. Sept. I9, ISSI, Danville,...   \n",
       "2042    WALLS, ELLIE A Educator. educ. A. B., Fisk Uni...   \n",
       "\n",
       "                      birthplace       birthdate  \\\n",
       "persid                                             \n",
       "17              Nashville, Tenn.                   \n",
       "31              Nashville, Tenn.   Sept. 2, 1892   \n",
       "70                                  Aug. 1. 1887   \n",
       "78                                  May 11. 1894   \n",
       "157                                Dec. 10. 1922   \n",
       "...                          ...             ...   \n",
       "1908                 Omaha, Neb.   Oct. 30, 1900   \n",
       "1981    Sch., Chicago 111., lM-^                   \n",
       "1989                                               \n",
       "2025               Danville, Va.  Sept. 19, 1881   \n",
       "2042                                               \n",
       "\n",
       "                                                      occ  \\\n",
       "persid                                                      \n",
       "17                                                          \n",
       "31                                       Alumn: Secretary   \n",
       "70                                              Clergyman   \n",
       "78                                                          \n",
       "157                                                         \n",
       "...                                                   ...   \n",
       "1908                                                        \n",
       "1981    '12; Univ. of Chicago. 1912'15; Ph.B., 1915; T...   \n",
       "1989                                                        \n",
       "2025                    Executive Secretary, Urban League   \n",
       "2042                                                        \n",
       "\n",
       "                                               curaddress  \\\n",
       "persid                                                      \n",
       "17                                                          \n",
       "31                                        Fisk University   \n",
       "70                                3301 Indiana Ave.; Home   \n",
       "78                                      120 Hamilton Ave.   \n",
       "157                                                         \n",
       "...                                                   ...   \n",
       "1908                                       3621 State St.   \n",
       "1981                 2370 Seventh Avenue, New York, N. Y.   \n",
       "1989                                                        \n",
       "2025                                                        \n",
       "2042    Jack Yates High School, Houston, Texas. WALRON...   \n",
       "\n",
       "                                                residence  \\\n",
       "persid                                                      \n",
       "17      234 W. 139th St., New York, N. Y. He completed...   \n",
       "31      923 18th Ave., N., Nashville, Tenn. In 1918 he...   \n",
       "70      3932 Grand Blvd., Chicago, Ill. While Pastor i...   \n",
       "78                   444 S. Central Ave., Columbus, Ohio.   \n",
       "157                                                         \n",
       "...                                                   ...   \n",
       "1908                   5825 Michigan Blvd., Chicago, Ill.   \n",
       "1981    245 West 139th St. New York, N. Y. First Negro...   \n",
       "1989                 3736 S. Michigan Ave., Chicago, Ill.   \n",
       "2025    40 Putnam Ave., Brooklyn, N. Y. RAGLAND, JOHN ...   \n",
       "2042    127 East 23rd St. New York. N. Y. His book, \"T...   \n",
       "\n",
       "                                           name_maiden  \n",
       "persid                                                  \n",
       "17                                                      \n",
       "31                                                      \n",
       "70                                                      \n",
       "78                                            Williams  \n",
       "157     Prof.) James Heyward and Annie Estelle (Jardon  \n",
       "...                                                ...  \n",
       "1908                                            Jewell  \n",
       "1981                                            Powell  \n",
       "1989                                            Brisco  \n",
       "2025                                              #603  \n",
       "2042            now the New York School of Social Work  \n",
       "\n",
       "[131 rows x 7 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# cleaning dataframe\n",
    "\n",
    "# convert name col to combined string\n",
    "name_string = ''.join(biodf['name'].tolist())\n",
    "non_letters = re.findall(\"[^a-zA-Z,\\. ]\", name_string)\n",
    "print(set(non_letters))\n",
    "\n",
    "# replace 8 with S\n",
    "biodf['name'] = biodf['name'].str.replace(\"8\", \"S\")\n",
    "# replace 1 with I\n",
    "biodf['name'] = biodf['name'].str.replace(\"1\", \"I\")\n",
    "# remove ^\n",
    "biodf['name'] = biodf['name'].str.replace(\"^\", \"\")\n",
    "# remove !\n",
    "biodf['name'] = biodf['name'].str.replace(\"!\", \"\")\n",
    "\n",
    "# extract out bracketed names to name_maiden\n",
    "biodf['name_maiden'] = biodf['name'].str.extract(r\"\\(([\\s\\S]+)\\)\")\n",
    "biodf['name_maiden'] = biodf['name_maiden'].str.replace(\"nee \", \"\").replace(\"née \", \"\").replace(\"Mrs.\", \"\")\n",
    "biodf[\"name\"] = biodf[\"name\"].str.replace(r\"\\([\\s\\S]+\\)\", \"\").str.replace(\"  \", \" \")\n",
    "# clean residual spaces\n",
    "biodf[\"name\"] = biodf[\"name\"].str.strip(\" \")\n",
    "biodf['name_maiden'] = biodf['name_maiden'].str.strip(\" \").fillna('')\n",
    "\n",
    "# show all remaining names with non valid letters\n",
    "# should be just apostrophes left\n",
    "biodf[biodf['name'].str.contains('[^a-zA-Z,\\. ]')] \n",
    "\n",
    "# for now, names are not fully cleaned: some names have captured parts of other people"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Ala\\.|Ala\\.|Alaska\\.|Alaska\\.|Alas\\.|Ariz\\.|Ariz\\.|Az\\.|Ark\\.|Ark\\.|Calif\\.|Calif\\.|Ca\\.|Cal\\.|Colo\\.|Colo\\.|Conn\\.|Conn\\.|Ct\\.|Del\\.|Del\\.|De\\.|D\\.C\\.|D\\. C\\.|Wash\\. D\\.C\\.|Fla\\.|Fla\\.|Fl\\.|Flor\\.|Ga\\.|Ga\\.|Hawaii\\.|Hawaii\\.|H\\.I\\.|Idaho\\.|Idaho\\.|Id\\.|Ida\\.|Ill\\.|Ill\\.|Il\\.|Ills\\.|Ill's\\.|Ind\\.|Ind\\.|In\\.|Iowa\\.|Iowa\\.|Ia\\.|Ioa\\.|Kans\\.|Kan\\.|Ks\\.|Ka\\.|Ky\\.|Ky\\.|Ken\\.|Kent\\.|La\\.|La\\.|Maine\\.|Maine\\.|Me\\.|Md\\.|Md\\.|Mass\\.|Mass\\.|Mich\\.|Mich\\.|Minn\\.|Minn\\.|Mn\\.|Miss\\.|Miss\\.|Mo\\.|Mo\\.|Mont\\.|Mont\\.|Nebr\\.|Neb\\.|Nev\\.|Nev\\.|Nv\\.|N\\.H\\.|N\\. H\\.|N\\.J\\.|N\\. J\\.|N\\. Jersey\\.|N\\. Mex\\.|N\\. M\\.|New M\\.|N\\.Y\\.|N\\. Y\\.|N\\. York\\.|N\\. Y\\.|N\\.C\\.|N\\. C\\.|N\\. Car\\.|N\\. Dak\\.|N\\. D\\.|NoDak\\.|N\\.Dak\\.|Ohio\\.|Ohio\\.|O\\.|Oh\\.|Okla\\.|Okla\\.|Ok\\.|Oreg\\.|Ore\\.|Or\\.|Pa\\.|Pa\\.|Penn\\.|Penna\\.|R\\.I\\.|R\\. I\\.|R I\\. \\.|R\\. Isl\\.|R\\.Isl\\.|S\\.C\\.|S\\. C\\.|S\\. Car\\.|S\\. Dak\\.|S\\. D\\.|SoDak\\.|S\\.Dak\\.|Tenn\\.|Tenn\\.|Tex\\.|Texas\\.|Tx\\.|Utah\\.|Utah\\.|Ut\\.|Vt\\.|Vt\\.|Va\\.|Va\\.|Virg\\.|Wash\\.|Wash\\.|Wa\\.|Wn\\.|W\\. Va\\.|W\\.Va\\.|W\\.V\\.|W\\. Virg\\.|W\\.Virg\\.|Wis\\.|Wis\\.|Wi\\.|Wisc\\.|Wyo\\.|Wyo\\.|Wy\\.|Ont\\.|Kansas\\.\n",
      "Alabama|Alaska|Arizona|Arkansas|California|Colorado|Connecticut|Delaware|District of Columbia|Florida|Georgia|Hawaii|Idaho|Illinois|Indiana|Iowa|Kansas|Kentucky|Louisiana|Maine|Maryland|Massachusetts|Michigan|Minnesota|Mississippi|Missouri|Montana|Nebraska|Nevada|New Hampshire|New Jersey|New Mexico|New York|North Carolina|North Dakota|Ohio|Oklahoma|Oregon|Pennsylvania|Rhode Island|South Carolina|South Dakota|Tennessee|Texas|Utah|Vermont|Virginia|Washington|West Virginia|Wisconsin|Wyoming|Ontario\n",
      "['Ala', 'Ala', 'Alaska', 'Alaska', 'Alas', 'Ariz', 'Ariz', 'Az', 'Ark', 'Ark', 'Calif', 'Calif', 'Ca', 'Cal', 'Colo', 'Colo', 'Conn', 'Conn', 'Ct', 'Del', 'Del', 'De', 'D.C', 'D. C', 'Wash. D.C', 'Fla', 'Fla', 'Fl', 'Flor', 'Ga', 'Ga', 'Hawaii', 'Hawaii', 'H.I', 'Idaho', 'Idaho', 'Id', 'Ida', 'Ill', 'Ill', 'Il', 'Ills', \"Ill's\", 'Ind', 'Ind', 'In', 'Iowa', 'Iowa', 'Ia', 'Ioa', 'Kans', 'Kan', 'Ks', 'Ka', 'Ky', 'Ky', 'Ken', 'Kent', 'La', 'La', 'Maine', 'Maine', 'Me', 'Md', 'Md', 'Mass', 'Mass', 'Mich', 'Mich', 'Minn', 'Minn', 'Mn', 'Miss', 'Miss', 'Mo', 'Mo', 'Mont', 'Mont', 'Nebr', 'Neb', 'Nev', 'Nev', 'Nv', 'N.H', 'N. H', 'N.J', 'N. J', 'N. Jersey', 'N. Mex', 'N. M', 'New M', 'N.Y', 'N. Y', 'N. York', 'N. Y', 'N.C', 'N. C', 'N. Car', 'N. Dak', 'N. D', 'NoDak', 'N.Dak', 'Ohio', 'Ohio', 'O', 'Oh', 'Okla', 'Okla', 'Ok', 'Oreg', 'Ore', 'Or', 'Pa', 'Pa', 'Penn', 'Penna', 'R.I', 'R. I', 'R I. ', 'R. Isl', 'R.Isl', 'S.C', 'S. C', 'S. Car', 'S. Dak', 'S. D', 'SoDak', 'S.Dak', 'Tenn', 'Tenn', 'Tex', 'Texas', 'Tx', 'Utah', 'Utah', 'Ut', 'Vt', 'Vt', 'Va', 'Va', 'Virg', 'Wash', 'Wash', 'Wa', 'Wn', 'W. Va', 'W.Va', 'W.V', 'W. Virg', 'W.Virg', 'Wis', 'Wis', 'Wi', 'Wisc', 'Wyo', 'Wyo', 'Wy', 'Ont', 'Kansas']\n"
     ]
    }
   ],
   "source": [
    "# clean address and residence columns\n",
    "\n",
    "# read state name csv\n",
    "os.chdir(r\"C:\\Users\\byron\\OneDrive\\Documents\\University\\US\\NYU\\SPUR\\WIW\")\n",
    "statedf = pd.read_csv(\"state_names.csv\")\n",
    "\n",
    "# create state abbreviation list\n",
    "state_abbs = statedf[[\"SNAME1\", \"SNAME2\", \"SNAME3\", \"SNAME4\", \"SNAME5\"]].stack().reset_index()[0].tolist()\n",
    "state_abbs.append(\"Kansas\")\n",
    "\n",
    "# add . to any state abbreviation not ending with . to avoid false match\n",
    "state_abbs_new = [abb + \".\" if abb[-1] != \".\" else abb for abb in state_abbs ]\n",
    "        \n",
    "# formulate regex argument\n",
    "state_abbs_string = \"|\".join(state_abbs_new).replace(\".\", \"\\.\")\n",
    "print(state_abbs_string)\n",
    "\n",
    "# cut address/residence to state \n",
    "# note that this might cut addresses with state abbreviations at start of address\n",
    "# only alter matches with state name in string\n",
    "\n",
    "# first cut to state abbreviation\n",
    "# curaddress\n",
    "# not very useful for curaddress, as many do not list state names after address\n",
    "biodf.loc[biodf['curaddress'].str.len() > 200, 'curaddress'] = biodf.loc[biodf['curaddress'].str.len() > 200, 'curaddress'].str.extract(rf\"(^[\\s\\S]*?(?:{state_abbs_string}))\", expand=False)\n",
    "# residence\n",
    "biodf.loc[biodf['residence'].str.contains(rf\"{state_abbs_string}\"), 'residence'] = biodf.loc[biodf['residence'].str.contains(rf\"{state_abbs_string}\"), 'residence'].str.extract(rf\"(^[\\s\\S]*?(?:{state_abbs_string}))\", expand=False)\n",
    "\n",
    "# next, for remaining uncleaned cells, cut to state name\n",
    "state_names = statedf[\"FULLSNAME\"].tolist()\n",
    "state_names_string = \"|\".join(state_names).replace(\".\", \"\\.\")\n",
    "print(state_names_string)\n",
    "biodf.loc[biodf['curaddress'].str.len() > 200, 'curaddress'] = biodf.loc[biodf['curaddress'].str.len() > 200, 'curaddress'].str.extract(rf\"(^[\\s\\S]*?(?:{state_names_string}))\", expand=False)\n",
    "biodf.loc[biodf['residence'].str.len() > 200, 'residence'] = biodf.loc[biodf['residence'].str.len() > 200, 'residence'].str.extract(rf\"(^[\\s\\S]*?(?:{state_names_string}))\", expand=False)\n",
    "\n",
    "# finally, for remaining uncleaned cells, cut to state abbreviation without '.'\n",
    "# slice out '\\.' from end of every abb\n",
    "state_abbs_cut = [abb[:-1] for abb in state_abbs_new]\n",
    "print(state_abbs_cut)\n",
    "biodf.loc[biodf['curaddress'].str.len() > 200, 'curaddress'] = biodf.loc[biodf['curaddress'].str.len() > 200, 'curaddress'].str.extract(rf\"(^[\\s\\S]*?,[ ]?(?:{state_abbs_cut}))\", expand=False)\n",
    "biodf.loc[biodf['residence'].str.len() > 200, 'residence'] = biodf.loc[biodf['residence'].str.len() > 200, 'residence'].str.extract(rf\"(^[\\s\\S]*?,[ ]?(?:{state_abbs_cut}))\", expand=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'(', '^', '|', '%', ')', '\"', '\\x0c', '*', '/', ';', '&', ':', '\\\\', '#'}\n",
      "{'^', '*', ';', ':', '<', '(', '>', '_', '$', '#', '%', '!', '–', ')', '&', '?', '}', '\"', '\\x0c', '/', '\\\\'}\n",
      "{'&', '/', ';'}\n",
      "{'&', '/', ';'}\n"
     ]
    }
   ],
   "source": [
    "# find invalid letters from combined string\n",
    "address_string = ''.join(biodf['curaddress'].astype(str).tolist())\n",
    "non_letters = re.findall(r\"[^a-zA-Z,\\. 0-9\\-\\']\", address_string)\n",
    "print(set(non_letters))\n",
    "\n",
    "address_string = ''.join(biodf['residence'].astype(str).tolist())\n",
    "non_letters = re.findall(r\"[^a-zA-Z,\\. 0-9\\-\\']\", address_string)\n",
    "print(set(non_letters))\n",
    "\n",
    "biodf = biodf.fillna('')\n",
    "\n",
    "# replace all redundant chars\n",
    "for string in ['►', '\\*', '\\$', 'c/o', '\\^', '\\|', '\\?', '\\([\\s\\S]+?\\)', ':', '<', '>', '\\)', '\\(', '~', '\\\"', '%', '!', r'\\\\', '\\x0c', '#', '\\}', '_', '–']: \n",
    "    biodf['curaddress'] = biodf['curaddress'].str.replace(string, \"\")\n",
    "    biodf['residence'] = biodf['residence'].str.replace(string, \"\")\n",
    "\n",
    "# find the remaining list of non letters\n",
    "address_string = ''.join(biodf['curaddress'].astype(str).tolist())\n",
    "non_letters = re.findall(r\"[^a-zA-Z,\\. 0-9\\-\\']\", address_string)\n",
    "print(set(non_letters))\n",
    "\n",
    "address_string = ''.join(biodf['residence'].astype(str).tolist())\n",
    "non_letters = re.findall(r\"[^a-zA-Z,\\. 0-9\\-\\']\", address_string)\n",
    "print(set(non_letters))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Make the names easier for matching by expanding the commonly used acronyms \n",
    "\n",
    "# Make a list of common states acronyms (N.Y. is New York) from a csv file. Store it as dictionary.\n",
    "state_acron={}\n",
    "with open('state_names_expanded_2.csv', mode='r') as infile:\n",
    "    # skip the first line (header)\n",
    "    infile.readline()\n",
    "    reader = csv.reader(infile)\n",
    "    for row in reader:\n",
    "        for i in range(5):\n",
    "            if row[i] != \"\": state_acron[row[i]]=row[5]\n",
    "\n",
    "# Sometimes it's \"N Y University\", not \"N.Y. University\" in the data\n",
    "# So next we create the same dictionary of states but without dots. \n",
    "state_acron_no_dots={}\n",
    "with open('state_names_expanded_2.csv', mode='r') as infile:\n",
    "    infile.readline()\n",
    "    reader = csv.reader(infile)\n",
    "    for row in reader:\n",
    "        for i in range(5):\n",
    "            row[i] = re.sub(\"\\.\", \"\", row[i]) \n",
    "            if row[i] != \"\": state_acron_no_dots[row[i]]=row[5]                \n",
    "\n",
    "#create a function that expands the acronyms for a given column \n",
    "def expand_acronyms(list_institutions):\n",
    "    # Define the regex patterns before looping\n",
    "    pattern1 = re.compile(r'\\b(' + '|'.join(sorted(re.escape(k) for k in state_acron)) + r')\\b')\n",
    "    pattern2 = re.compile(r'(\\s|,|^)(' + '|'.join(re.escape(key) for key in state_acron_no_dots.keys()) + r')(\\s|,|$)')\n",
    "    # use the state acronyms dictionary to expand the names to the full ones.\n",
    "    list_institutions_return = list_institutions.copy()\n",
    "    for i, place in enumerate(list_institutions):\n",
    "        if place:\n",
    "            # there is no need to care about the acronyms being separate words, \n",
    "            # because at this point the acronyms in the dictionary always have dots.\n",
    "            place = re.sub(pattern1, lambda m: state_acron.get(m.group(0)), place)\n",
    "            #### use the acronym dictionary to make changes\n",
    "            place = re.sub('\\.', ' ', place)\n",
    "            place = re.sub(\"[\\(\\)]\", \"\", place)\n",
    "            #remove multiple spaces and change \"U S\" -> \"US\", \"Poly tech\" -> \"Polytech\", \"De Paul/w\" -> \"DePaul/w\"\n",
    "            place = re.sub(r'\\s+', ' ', place)\n",
    "            place = place.replace('U S', \n",
    "                                  'US')\n",
    "\n",
    "            #### finally, use the state dictionary without dots\n",
    "    \n",
    "            # use the dictionary of state acronyms to clean the list\n",
    "            place = re.sub(pattern2, lambda m: \" \" + state_acron_no_dots.get(m.group(0).strip(' ,')) + \" \", place)\n",
    "            #remove double, triple etc spaces\n",
    "            place = re.sub(r'\\s+', ' ', place).strip(' ,')\n",
    "            #remove spaces before commas\n",
    "            place = re.sub(r' ,', ',', place)\n",
    "            #### \"Nat. Sciences\" is \"Natural Sciences\", but in all other cases \"Nat\" means \"National\".  \n",
    "            place = re.sub('Nat Sci', 'Natural Sci', place)\n",
    "            place = re.sub('Nat\\s', 'National ', place)\n",
    "            list_institutions_return[i] = place\n",
    "#             if i<15 or i>82150:\n",
    "#                 print(i, place, list_institutions[i])\n",
    "    return(list_institutions_return)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Kentuckytown, Texas']"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "expand_acronyms([\"Kentuckytown, Tex.\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# replace NaN values for empty cells with an empty string\n",
    "biodf = biodf.fillna(\"\")\n",
    "\n",
    "# expand birthplace column state names\n",
    "bp_expanded = expand_acronyms(list(biodf['birthplace']))\n",
    "biodf['birthplace_exp'] = bp_expanded\n",
    "biodf = biodf[['name', 'name_maiden', 'birthplace', 'birthplace_exp', 'birthdate', 'occ', 'curaddress', 'residence']]    \n",
    "\n",
    "# cities with missing states usually have their states miscaptured in birthdate string\n",
    "# for entries with expanded birthplace = birthplace AND state abb in birthdate, extract out state to birthplace\n",
    "biodf.loc[(biodf[\"birthplace\"] == biodf[\"birthplace_exp\"]) & (biodf[\"birthdate\"].str.contains(rf\"{state_abbs_string}\")), \"birthplace\"] += \", \" + biodf.loc[(biodf[\"birthplace\"] == biodf[\"birthplace_exp\"]) & (biodf[\"birthdate\"].str.contains(rf\"{state_abbs_string}\")), \"birthdate\"].str.extract(rf\"({state_abbs_string})\", expand=False)\n",
    "# remove state abb from birthdate and clean entry\n",
    "biodf.loc[biodf[\"birthdate\"].str.contains(rf\"{state_abbs_string}\"), \"birthdate\"] = biodf.loc[biodf[\"birthdate\"].str.contains(rf\"{state_abbs_string}\"), \"birthdate\"].str.replace(rf\"{state_abbs_string}\", \"\")\n",
    "biodf.loc[biodf[\"birthdate\"].str.contains(rf\"{state_abbs_string}\"), \"birthdate\"] = biodf.loc[biodf[\"birthdate\"].str.contains(rf\"{state_abbs_string}\"), \"birthdate\"].str.strip(\" ,.\")\n",
    "\n",
    "# again, expand birthplace column state names\n",
    "bp_expanded = expand_acronyms(list(biodf['birthplace']))\n",
    "biodf['birthplace_exp'] = bp_expanded\n",
    "biodf = biodf[['name', 'name_maiden', 'birthplace', 'birthplace_exp', 'birthdate', 'occ', 'curaddress', 'residence']] \n",
    "\n",
    "# further expand unrecognized abbreviations/errors\n",
    "missed_abbs = {r'W[Iil][Ss]': \"Wisconsin\", \n",
    "                   \"Maas\": \"Massachusetts\", \n",
    "                       \"Miss/\": \"Mississippi\",\n",
    "                          \"Coon\": \"Connecticut\",\n",
    "                              \"fey\": \"Kentucky\",\n",
    "                                  \"8 C\": \"South Carolina\",\n",
    "                                   \"S, C\": \"South Carolina\",\n",
    "                                      \", la\": \", Iowa\",\n",
    "                                          r\"Col$\": \"Colorado\",\n",
    "                                              \"Teim\": \"Tennessee\",\n",
    "                                               \", Twin\": \", Tennessee\",\n",
    "                                                \", Term\": \", Tennessee\",\n",
    "                                                  \"N T\": \"New York\",\n",
    "                                                      \"Mb\": \"Missouri\",\n",
    "                                                          \"X C\": \"North Carolina\",\n",
    "                                                           \"N G\": \"North Carolina\",\n",
    "                                                            r\"North C\\b\": \"North Carolina\\b\",\n",
    "                                                              \", la\": \", Iowa\",\n",
    "                                                               \", Vs\": \", Virginia\",\n",
    "                                                                \", Kims\": \", Kansas\",\n",
    "                                                                    \"Los Angeles, Cat\": \"Los Angeles, California\",\n",
    "                                                                       \", Arte\": \", Arkansas\",\n",
    "                                                                          \"L I\": \"New York\"}\n",
    "\n",
    "# expand these abbs\n",
    "biodf['birthplace_exp'] = biodf['birthplace_exp'].replace(missed_abbs, regex=True)\n",
    "\n",
    "# remove remaining -s\n",
    "# this will alter a few words where - was actually intended\n",
    "biodf['birthplace_exp'] = biodf['birthplace_exp'].str.replace(r\"[ ]*-[ ]*\", \"\", regex = True)\n",
    "\n",
    "# cut to state name\n",
    "# remove Washington from state_names_string to prevent false match\n",
    "state_names_string_2 = state_names_string.replace(\"|Washington\", \"\")\n",
    "biodf.loc[biodf['birthplace_exp'].str.contains(rf\"{state_names_string_2}\"), 'birthplace_exp'] = biodf.loc[biodf['birthplace_exp'].str.contains(rf\"{state_names_string_2}\"), 'birthplace_exp'].str.extract(rf\"(^[\\s\\S]*?\\b(?:{state_names_string_2})\\b)\", expand=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Alberta|British Columbia|Manitoba|New Brunswick|Newfoundland and Labrador|Northwest Territories|Nova Scotia|Ontario|Prince Edward Island|Quebec|Saskatchewan|Alabama|Alaska|Arizona|Arkansas|California|Canada|Colorado|Connecticut|Delaware|District of Columbia|Florida|FOREIGN|Puerto Rico|US Virgin Islands|Georgia|Hawaii|Idaho|Illinois|Indiana|Iowa|Kansas|Kentucky|Louisiana|Maine|Maryland|Massachusetts|Michigan|Minnesota|Mississippi|Missouri|Montana|Nebraska|Nevada|New Hampshire|New Jersey|New Mexico|New York|North Carolina|North Dakota|Ohio|Oklahoma|Oregon|Pennsylvania|Rhode Island|South Carolina|South Dakota|Tennessee|Texas|Utah|Vermont|West Virginia|Virginia|Washington|Wisconsin|Wyoming|Yukon|Guam|American Samoa\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "name              EMMA. C. W. GRAY, A.B., A.M., PH.B.   \n",
       " ## p. ...\n",
       "name_maiden                                                    #2I6\n",
       "birthplace                                  Fraziers Bottom, W. Va.\n",
       "birthplace_exp                       Fraziers Bottom, West Virginia\n",
       "birthplace_loc                                      Fraziers Bottom\n",
       "birthplace_st                                         West Virginia\n",
       "birthdate                                            April 21, 1885\n",
       "occ               ss. \n",
       "  ## p. 211 (#217) ######################...\n",
       "curaddress                                                         \n",
       "residence         West uS Lakin, Mason  w for Colored Boys, He v...\n",
       "Name: 696, dtype: object"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# split birthplace_exp into location and state name\n",
    "\n",
    "# use expanded state name csv\n",
    "expstate_df = pd.read_csv(\"state_names_expanded_2.csv\").fillna(\"\")\n",
    "\n",
    "# function that only returns uniques from list (in order)\n",
    "def unique(sequence):\n",
    "    seen = set()\n",
    "    return [x for x in sequence if not (x in seen or seen.add(x))]\n",
    "\n",
    "# create state name list, also remove duplicates\n",
    "state_names = unique(list(expstate_df[\"FULLSNAME\"]))\n",
    "        \n",
    "# formulate regex argument\n",
    "state_names_string = \"|\".join(state_names)\n",
    "print(state_names_string)\n",
    "\n",
    "# first extract only state name\n",
    "biodf['birthplace_st'] = biodf['birthplace_exp'].str.extract(fr\"\\b({state_names_string})\\b$\")\n",
    "\n",
    "# then extract only the string before state name (anything behind is removed) by splitting birthplace_exp using birthplace_st\n",
    "# in this case note that FOREIGN state names apply to all outside US/Canada\n",
    "# FOREIGN states will have varying formats in birthplace_loc and birthplace_st due to varying foreign birthplace formats in dataset\n",
    "biodf['birthplace_loc'] = biodf.apply(lambda row : re.split(str(row['birthplace_st']), str(row['birthplace_exp']))[0], axis=1).str.strip(', ')\n",
    "\n",
    "biodf = biodf[['name', 'name_maiden', 'birthplace', 'birthplace_exp', 'birthplace_loc', 'birthplace_st', 'birthdate', 'occ', 'curaddress', 'residence']]  \n",
    "biodf.iloc[696]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# make a dictionary of foreign/non-foreign states\n",
    "is_state_foreign={}\n",
    "with open('state_names_expanded_2.csv', mode='r') as infile:\n",
    "    # skip the first line (header)\n",
    "    infile.readline()\n",
    "    reader = csv.reader(infile)\n",
    "    for row in reader:\n",
    "        if row[5] != \"\": is_state_foreign[row[5]] = (row[8])\n",
    "\n",
    "# note the definition for FOREIGN column\n",
    "# 0: US states, including US territories\n",
    "# 1: foreign states, including Canada\n",
    "# people without recorded birthplaces have empty entry\n",
    "biodf['FOREIGN'] = biodf['birthplace_st'].map(is_state_foreign) \n",
    "biodf = biodf[['name', 'name_maiden', 'birthplace', 'birthplace_loc', 'birthplace_st', 'FOREIGN', 'birthdate', 'occ', 'curaddress', 'residence']] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# limit all columns to 100 characters\n",
    "biodf = biodf.apply(lambda x: x.str.slice(0, 100))\n",
    "\n",
    "# further limit all columns except curaddress and residence to 70 characters\n",
    "biodf[['name', 'name_maiden', 'birthplace', 'birthplace_loc', 'birthplace_st', 'FOREIGN', 'birthdate', 'occ']] = biodf[['name', 'name_maiden', 'birthplace', 'birthplace_loc', 'birthplace_st', 'FOREIGN', 'birthdate', 'occ']].apply(lambda x: x.str.slice(0, 70)) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "# write to csv\n",
    "biodf.to_csv('1944_bio_data_3.csv', encoding=\"utf-8\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# count how many times a state appears in birthplace_st col\n",
    "# original dataframe is sorted by count\n",
    "state_count_df = biodf['birthplace_st'].value_counts().to_frame()\n",
    "state_count_df = state_count_df.rename(columns = {\"birthplace_st\": \"Count\"})\n",
    "state_count_df.index.name = \"State\"\n",
    "\n",
    "# for now, this is sorted alphabetically, not by count\n",
    "# for the sake of counting, Canadian states have been separated out of FOREIGN states\n",
    "state_count_df.sort_index() # remove this line if you want alphabetical order\n",
    "state_count_df.to_csv('1944_state_count.csv', encoding=\"utf-8\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total number of people in main text: 2050\n"
     ]
    }
   ],
   "source": [
    "print(\"Total number of people in main text:\", len(biodf))"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}