notebooks/8.0_lastname_variations.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "76ce512b-f034-4ba8-85b0-f8b118c241a5",
   "metadata": {},
   "source": [
    "# Name Variations for last names"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce678a64-f21f-4577-a7d1-66391e801e36",
   "metadata": {
    "tags": []
   },
   "source": [
    "## First trying with llama 30B\n",
    "Taking too much time (~9 mins) to do inference on 60GB Memory with 10 core CPU"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "16b280e5-abb1-4046-b973-dd0b859ea685",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "from llama_cpp import Llama"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "4c37eb26-8e28-4a9f-bf96-81a45e481a2b",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "llama.cpp: loading model from /home/distill1994/workspace/llama.cpp/models/30B/ggml-model-q4_0.bin\n",
      "llama_model_load_internal: format     = ggjt v3 (latest)\n",
      "llama_model_load_internal: n_vocab    = 32000\n",
      "llama_model_load_internal: n_ctx      = 512\n",
      "llama_model_load_internal: n_embd     = 6656\n",
      "llama_model_load_internal: n_mult     = 256\n",
      "llama_model_load_internal: n_head     = 52\n",
      "llama_model_load_internal: n_layer    = 60\n",
      "llama_model_load_internal: n_rot      = 128\n",
      "llama_model_load_internal: ftype      = 2 (mostly Q4_0)\n",
      "llama_model_load_internal: n_ff       = 17920\n",
      "llama_model_load_internal: n_parts    = 1\n",
      "llama_model_load_internal: model size = 30B\n",
      "llama_model_load_internal: ggml ctx size =    0.13 MB\n",
      "llama_model_load_internal: mem required  = 19756.66 MB (+ 3124.00 MB per state)\n",
      ".\n",
      "llama_init_from_file: kv self size  =  780.00 MB\n",
      "AVX = 1 | AVX2 = 1 | AVX512 = 0 | AVX512_VBMI = 0 | AVX512_VNNI = 0 | FMA = 1 | NEON = 0 | ARM_FMA = 0 | F16C = 1 | FP16_VA = 0 | WASM_SIMD = 0 | BLAS = 0 | SSE3 = 1 | VSX = 0 | \n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'id': 'cmpl-4d2ff605-6fe2-4f8a-ac6a-0ee99d415524', 'object': 'text_completion', 'created': 1686511129, 'model': '/home/distill1994/workspace/llama.cpp/models/30B/ggml-model-q4_0.bin', 'choices': [{'text': 'Q: give me 5 most common alternate spellings of the last name garcia? A: 1. Garza 2. Garza 3. Garci 4. Garz 5. Garcillano', 'index': 0, 'logprobs': None, 'finish_reason': 'stop'}], 'usage': {'prompt_tokens': 22, 'completion_tokens': 26, 'total_tokens': 48}}\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n",
      "llama_print_timings:        load time = 552232.75 ms\n",
      "llama_print_timings:      sample time =    40.46 ms /    26 runs   (    1.56 ms per token)\n",
      "llama_print_timings: prompt eval time = 552228.07 ms /    22 tokens (25101.28 ms per token)\n",
      "llama_print_timings:        eval time = 495115.02 ms /    25 runs   (19804.60 ms per token)\n",
      "llama_print_timings:       total time = 1047753.49 ms\n"
     ]
    }
   ],
   "source": [
    "llm = Llama(model_path=\"/home/distill1994/workspace/llama.cpp/models/30B/ggml-model-q4_0.bin\")\n",
    "output = llm(\"Q: give me 5 most common alternate spellings of the last name garcia? A: \", max_tokens=100, stop=[\"Q:\", \"\\n\"], echo=True)\n",
    "print(output)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5e4774f1-0fa5-4c7e-9103-c885bf8aa126",
   "metadata": {},
   "source": [
    "## Next trying with llama 65B\n",
    "Taking too much time to do inference on 60GB Memory with 10 core CPU"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c94b4b44-291a-4af2-aa77-90bda89b189f",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "llama.cpp: loading model from /home/distill1994/workspace/llama.cpp/models/65B/ggml-model-q4_0.bin\n",
      "llama_model_load_internal: format     = ggjt v3 (latest)\n",
      "llama_model_load_internal: n_vocab    = 32000\n",
      "llama_model_load_internal: n_ctx      = 512\n",
      "llama_model_load_internal: n_embd     = 8192\n",
      "llama_model_load_internal: n_mult     = 256\n",
      "llama_model_load_internal: n_head     = 64\n",
      "llama_model_load_internal: n_layer    = 80\n",
      "llama_model_load_internal: n_rot      = 128\n",
      "llama_model_load_internal: ftype      = 2 (mostly Q4_0)\n",
      "llama_model_load_internal: n_ff       = 22016\n",
      "llama_model_load_internal: n_parts    = 1\n",
      "llama_model_load_internal: model size = 65B\n",
      "llama_model_load_internal: ggml ctx size =    0.18 MB\n",
      "llama_model_load_internal: mem required  = 38610.46 MB (+ 5120.00 MB per state)\n",
      ".\n",
      "llama_init_from_file: kv self size  = 1280.00 MB\n",
      "AVX = 1 | AVX2 = 1 | AVX512 = 0 | AVX512_VBMI = 0 | AVX512_VNNI = 0 | FMA = 1 | NEON = 0 | ARM_FMA = 0 | F16C = 1 | FP16_VA = 0 | WASM_SIMD = 0 | BLAS = 0 | SSE3 = 1 | VSX = 0 | \n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'id': 'cmpl-67063bfc-b735-4bd4-ba39-2621ba60d599', 'object': 'text_completion', 'created': 1686513951, 'model': '/home/distill1994/workspace/llama.cpp/models/65B/ggml-model-q4_0.bin', 'choices': [{'text': 'Q: give me 5 most common alternate spellings of the last name garcia? A: 1. GARCIA, 2. GARCÍA, 3. GARSÍA, 4. GARCEI', 'index': 0, 'logprobs': None, 'finish_reason': 'length'}], 'usage': {'prompt_tokens': 22, 'completion_tokens': 32, 'total_tokens': 54}}\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n",
      "llama_print_timings:        load time = 93866.74 ms\n",
      "llama_print_timings:      sample time =    33.38 ms /    32 runs   (    1.04 ms per token)\n",
      "llama_print_timings: prompt eval time = 93865.56 ms /    22 tokens ( 4266.62 ms per token)\n",
      "llama_print_timings:        eval time = 203079.49 ms /    31 runs   ( 6550.95 ms per token)\n",
      "llama_print_timings:       total time = 297587.78 ms\n"
     ]
    }
   ],
   "source": [
    "llm = Llama(model_path=\"/home/distill1994/workspace/llama.cpp/models/65B/ggml-model-q4_0.bin\")\n",
    "output1 = llm(\"Q: give me 5 most common alternate spellings of the last name garcia? A: \", max_tokens=32, stop=[\"Q:\", \"\\n\"], echo=True)\n",
    "print(output1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "208dcc36-18a6-4349-a07d-d1be161b43c8",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Llama.generate: prefix-match hit\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'id': 'cmpl-8c85ea5e-20d5-4a02-9944-7e0076d33564', 'object': 'text_completion', 'created': 1686521841, 'model': '/home/distill1994/workspace/llama.cpp/models/65B/ggml-model-q4_0.bin', 'choices': [{'text': 'Q: give me 5 most common alternate spellings of the last name garcia? A: 1. Garcea, Garaea, Gercia, Garcae, 2. Garzia, Garia, Giazria, Garziq 3. Gerza , Geraz, Gazer, Gezer 4. Garrica, Georica, Gorica, Grrica . 5. Gaarcia, Gaacia, Gaaria, Gaarja, Gaaroa', 'index': 0, 'logprobs': None, 'finish_reason': 'stop'}], 'usage': {'prompt_tokens': 22, 'completion_tokens': 86, 'total_tokens': 108}}\n",
      "CPU times: user 42min 43s, sys: 0 ns, total: 42min 43s\n",
      "Wall time: 8min 42s\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n",
      "llama_print_timings:        load time = 93866.74 ms\n",
      "llama_print_timings:      sample time =   148.68 ms /    86 runs   (    1.73 ms per token)\n",
      "llama_print_timings: prompt eval time =     0.00 ms /     1 tokens (    0.00 ms per token)\n",
      "llama_print_timings:        eval time = 517429.33 ms /    86 runs   ( 6016.62 ms per token)\n",
      "llama_print_timings:       total time = 522653.79 ms\n"
     ]
    }
   ],
   "source": [
    "%%time \n",
    "\n",
    "output1 = llm(\"Q: give me 5 most common alternate spellings of the last name garcia? A: \", max_tokens=500, stop=[\"Q:\", \"\\n\"], echo=True)\n",
    "print(output1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "29702063-3e1d-4b43-b845-33047a9ca3e9",
   "metadata": {},
   "source": [
    "## Hugging Face llama models require endpoints to be created due to size"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "3bec6c31-53fa-4181-aad2-df6d981256eb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'error': 'The model huggyllama/llama-65b is too large to be loaded automatically (130GB > 10GB). For commercial use please use PRO spaces (https://huggingface.co/spaces) or Inference Endpoints (https://huggingface.co/inference-endpoints).'}\n"
     ]
    }
   ],
   "source": [
    "import requests\n",
    "\n",
    "API_TOKEN = \"token\"\n",
    "\n",
    "API_URL = \"https://api-inference.huggingface.co/models/huggyllama/llama-65b\"\n",
    "headers = {\"Authorization\": f\"Bearer {API_TOKEN}\"}\n",
    "\n",
    "def query(payload):\n",
    "\tresponse = requests.post(API_URL, headers=headers, json=payload)\n",
    "\treturn response.json()\n",
    "\t\n",
    "output = query({\n",
    "\t\"inputs\": \"Can you please let us know more details about your \",\n",
    "})\n",
    "\n",
    "print(output)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e5e0adfa-8911-4cd3-b32d-05c61014658d",
   "metadata": {},
   "source": [
    "## Tried HF model 30B Lazarus\n",
    "https://huggingface.co/CalderaAI/30B-Lazarus - but results are not as expected"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "587d86f5-0bf2-4549-b0f5-aeacf64e227c",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current\n",
      "                                 Dload  Upload   Total   Spent    Left  Speed\n",
      "100   144    0    65  100    79     18     22  0:00:03  0:00:03 --:--:--    41\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[{\"generated_text\":\"\\n1. Garza\\n2. Garza\\n3. Garcia\\n4. Garcia\"}]"
     ]
    }
   ],
   "source": [
    "%%bash\n",
    "\n",
    "curl https://lakn0qwfmmct4iqv.us-east-1.aws.endpoints.huggingface.cloud \\\n",
    "-X POST \\\n",
    "-d '{\"inputs\":\"give me 10 most common alternate spellings of the last name garcia\"}' \\\n",
    "-H \"Authorization: Bearer token\" \\\n",
    "-H \"Content-Type: application/json\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "ecbd1bd1-cdca-4f2f-bbfd-92dd293df7ad",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'error': 'The model CalderaAI/30B-Lazarus is too large to be loaded automatically (65GB > 10GB). For commercial use please use PRO spaces (https://huggingface.co/spaces) or Inference Endpoints (https://huggingface.co/inference-endpoints).'}"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "output"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c22f0490-a5c4-4c3e-8447-8fa0caa920f6",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Get the top lastnames based on frequency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "a1e55908-a108-4df3-a75d-b1eb3f241211",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "91ed35c9-65a1-4e64-8c94-bf0c9a172e9f",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"data/fl_2022_LastName_train.csv.gz\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "31684b74-1e78-4216-9203-55c1e51c46f5",
   "metadata": {
    "tags": []
   },
   "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>name_last</th>\n",
       "      <th>asian</th>\n",
       "      <th>hispanic</th>\n",
       "      <th>nh_black</th>\n",
       "      <th>nh_white</th>\n",
       "      <th>other</th>\n",
       "      <th>total_n</th>\n",
       "      <th>race</th>\n",
       "      <th>race_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Neaman-Piotrowicz</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>nh_white</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Chaet</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>13.0</td>\n",
       "      <td>nh_white</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Veloz Perez</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>hispanic</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Spalter</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.928571</td>\n",
       "      <td>0.071429</td>\n",
       "      <td>42.0</td>\n",
       "      <td>nh_white</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Kearns-Edwards</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>nh_white</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           name_last  asian  hispanic  nh_black  nh_white     other  total_n   \n",
       "0  Neaman-Piotrowicz    0.0       0.0       0.0  1.000000  0.000000      1.0  \\\n",
       "1              Chaet    0.0       0.0       0.0  1.000000  0.000000     13.0   \n",
       "2        Veloz Perez    0.0       1.0       0.0  0.000000  0.000000      1.0   \n",
       "3            Spalter    0.0       0.0       0.0  0.928571  0.071429     42.0   \n",
       "4     Kearns-Edwards    0.0       0.0       0.0  1.000000  0.000000      1.0   \n",
       "\n",
       "       race  race_code  \n",
       "0  nh_white          3  \n",
       "1  nh_white          3  \n",
       "2  hispanic          1  \n",
       "3  nh_white          3  \n",
       "4  nh_white          3  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "da6261b5-bb64-471d-9f8c-c9e1f99c9695",
   "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>name_last</th>\n",
       "      <th>asian</th>\n",
       "      <th>hispanic</th>\n",
       "      <th>nh_black</th>\n",
       "      <th>nh_white</th>\n",
       "      <th>other</th>\n",
       "      <th>total_n</th>\n",
       "      <th>race</th>\n",
       "      <th>race_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Donald</td>\n",
       "      <td>0.004862</td>\n",
       "      <td>0.016207</td>\n",
       "      <td>0.515397</td>\n",
       "      <td>0.421394</td>\n",
       "      <td>0.042139</td>\n",
       "      <td>617.0</td>\n",
       "      <td>nh_black</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>Kiddy</td>\n",
       "      <td>0.037037</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.944444</td>\n",
       "      <td>0.018519</td>\n",
       "      <td>54.0</td>\n",
       "      <td>nh_white</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>Sleiman</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.236364</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.727273</td>\n",
       "      <td>0.036364</td>\n",
       "      <td>55.0</td>\n",
       "      <td>nh_white</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>228</th>\n",
       "      <td>Zylstra</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.012821</td>\n",
       "      <td>0.012821</td>\n",
       "      <td>0.974359</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>78.0</td>\n",
       "      <td>nh_white</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>232</th>\n",
       "      <td>Grogg</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.014706</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.955882</td>\n",
       "      <td>0.029412</td>\n",
       "      <td>68.0</td>\n",
       "      <td>nh_white</td>\n",
       "      <td>3</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>845131</th>\n",
       "      <td>Gal</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.088889</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.855556</td>\n",
       "      <td>0.055556</td>\n",
       "      <td>90.0</td>\n",
       "      <td>nh_white</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>845169</th>\n",
       "      <td>Valmyr</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.958333</td>\n",
       "      <td>0.027778</td>\n",
       "      <td>0.013889</td>\n",
       "      <td>72.0</td>\n",
       "      <td>nh_black</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>845282</th>\n",
       "      <td>Ocallaghan</td>\n",
       "      <td>0.017241</td>\n",
       "      <td>0.017241</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.948276</td>\n",
       "      <td>0.017241</td>\n",
       "      <td>58.0</td>\n",
       "      <td>nh_white</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>845305</th>\n",
       "      <td>Kimbler</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.984615</td>\n",
       "      <td>0.015385</td>\n",
       "      <td>65.0</td>\n",
       "      <td>nh_white</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>845310</th>\n",
       "      <td>Graeber</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.078431</td>\n",
       "      <td>0.039216</td>\n",
       "      <td>0.882353</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>51.0</td>\n",
       "      <td>nh_white</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>21631 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         name_last     asian  hispanic  nh_black  nh_white     other  total_n   \n",
       "11          Donald  0.004862  0.016207  0.515397  0.421394  0.042139    617.0  \\\n",
       "69           Kiddy  0.037037  0.000000  0.000000  0.944444  0.018519     54.0   \n",
       "76         Sleiman  0.000000  0.236364  0.000000  0.727273  0.036364     55.0   \n",
       "228        Zylstra  0.000000  0.012821  0.012821  0.974359  0.000000     78.0   \n",
       "232          Grogg  0.000000  0.014706  0.000000  0.955882  0.029412     68.0   \n",
       "...            ...       ...       ...       ...       ...       ...      ...   \n",
       "845131         Gal  0.000000  0.088889  0.000000  0.855556  0.055556     90.0   \n",
       "845169      Valmyr  0.000000  0.000000  0.958333  0.027778  0.013889     72.0   \n",
       "845282  Ocallaghan  0.017241  0.017241  0.000000  0.948276  0.017241     58.0   \n",
       "845305     Kimbler  0.000000  0.000000  0.000000  0.984615  0.015385     65.0   \n",
       "845310     Graeber  0.000000  0.078431  0.039216  0.882353  0.000000     51.0   \n",
       "\n",
       "            race  race_code  \n",
       "11      nh_black          2  \n",
       "69      nh_white          3  \n",
       "76      nh_white          3  \n",
       "228     nh_white          3  \n",
       "232     nh_white          3  \n",
       "...          ...        ...  \n",
       "845131  nh_white          3  \n",
       "845169  nh_black          2  \n",
       "845282  nh_white          3  \n",
       "845305  nh_white          3  \n",
       "845310  nh_white          3  \n",
       "\n",
       "[21631 rows x 9 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['total_n'] > 50]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "09ac12fe-c796-4aaa-96d6-74f86c562b91",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "last_name_values = df[df['total_n'] > 50]['name_last'].values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "dd1c1f88-302f-4ac3-89fb-83ad119d3422",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "numpy.ndarray"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(last_name_values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "f1bd2a61-475e-4534-95a5-9b09cda13d0c",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import numpy\n",
    "\n",
    "numpy.savetxt(\"last_names.csv\", last_name_values, fmt='%s', delimiter=\",\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8591637a-1353-48b3-ab41-bfeaccf2a6eb",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Trying to generate randomly"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "id": "2dd78497-439d-47f7-ba68-81f6c2b778d0",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'garbia', 'garhia', 'gartia', 'gercia', 'garmia', 'garpcia', 'gascia', 'garzia', 'gorcia', 'garjia', 'garcii', 'garcie', 'gagcia', 'gavcia', 'gabcia', 'garcaa', 'gakcia', 'garpia', 'garlia', 'gawcia', 'gapcia', 'garqia', 'gazcia', 'gurcia', 'gadcia', 'garcia', 'gaycia', 'garwia', 'garxia', 'gancia', 'gardia', 'gircia', 'garfia', 'garsia', 'garcea', 'garcwia', 'gajcia', 'garyia', 'garcoa', 'garciu', 'garvia', 'gaccia', 'gafcia', 'gamcia', 'garcua', 'garnia', 'gaqcia', 'garcio', 'garkia', 'gahcia', 'gargia', 'galcia', 'garria', 'gaxcia', 'gatcia'}\n"
     ]
    }
   ],
   "source": [
    "import random\n",
    "\n",
    "def generate_similar_spellings(last_name):\n",
    "    similar_spellings = set()\n",
    "    similar_spellings.add(last_name)\n",
    "    vowels = set('aeiou')\n",
    "    consonants = set('bcdfghjklmnpqrstvwxyz')\n",
    "    # iterate through each character in the last name except the first letter\n",
    "    for i in range(1, len(last_name)):\n",
    "        # check if the character is a vowel or a consonant\n",
    "        if last_name[i] in vowels:\n",
    "            # replace the vowel with a random vowel\n",
    "            for v in vowels:\n",
    "                if v != last_name[i]:\n",
    "                    similar_spellings.add(last_name[:i] + v + last_name[i+1:])\n",
    "        else:\n",
    "            # replace the consonant with a random consonant\n",
    "            for c in consonants:\n",
    "                if c != last_name[i]:\n",
    "                    similar_spellings.add(last_name[:i] + c + last_name[i+1:])\n",
    "            # add an extra consonant after the current consonant\n",
    "            similar_spellings.add(last_name[:i+1] + random.choice(list(consonants)) + last_name[i+1:])\n",
    "    # return the set of similar spellings with the first letter always the same\n",
    "    return set([last_name[0] + spelling[1:] for spelling in similar_spellings])\n",
    "\n",
    "# example usage\n",
    "last_name = 'garcia'\n",
    "similar_spellings = generate_similar_spellings(last_name)\n",
    "print(similar_spellings)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6209750b-98eb-4cd9-a221-a6dfbf8b1e52",
   "metadata": {},
   "source": [
    "Results are not as expected. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a53626f4-d7af-48ef-8d92-2f1490e7083a",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Using Open AI chatgpt 3.5 turbo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "087d123f-6ff4-4c2a-af8a-0238454d4778",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Defaulting to user installation because normal site-packages is not writeable\n",
      "Collecting openai\n",
      "  Downloading openai-0.27.8-py3-none-any.whl (73 kB)\n",
      "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m73.6/73.6 KB\u001b[0m \u001b[31m3.2 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
      "\u001b[?25hRequirement already satisfied: requests>=2.20 in /usr/local/lib/python3.10/dist-packages (from openai) (2.29.0)\n",
      "Requirement already satisfied: tqdm in /home/distill1994/.local/lib/python3.10/site-packages (from openai) (4.65.0)\n",
      "Collecting aiohttp\n",
      "  Using cached aiohttp-3.8.4-cp310-cp310-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (1.0 MB)\n",
      "Requirement already satisfied: certifi>=2017.4.17 in /usr/local/lib/python3.10/dist-packages (from requests>=2.20->openai) (2022.12.7)\n",
      "Requirement already satisfied: charset-normalizer<4,>=2 in /usr/local/lib/python3.10/dist-packages (from requests>=2.20->openai) (3.1.0)\n",
      "Requirement already satisfied: idna<4,>=2.5 in /usr/local/lib/python3.10/dist-packages (from requests>=2.20->openai) (3.4)\n",
      "Requirement already satisfied: urllib3<1.27,>=1.21.1 in /usr/local/lib/python3.10/dist-packages (from requests>=2.20->openai) (1.26.15)\n",
      "Collecting async-timeout<5.0,>=4.0.0a3\n",
      "  Using cached async_timeout-4.0.2-py3-none-any.whl (5.8 kB)\n",
      "Collecting multidict<7.0,>=4.5\n",
      "  Using cached multidict-6.0.4-cp310-cp310-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (114 kB)\n",
      "Collecting aiosignal>=1.1.2\n",
      "  Using cached aiosignal-1.3.1-py3-none-any.whl (7.6 kB)\n",
      "Requirement already satisfied: attrs>=17.3.0 in /usr/local/lib/python3.10/dist-packages (from aiohttp->openai) (23.1.0)\n",
      "Collecting yarl<2.0,>=1.0\n",
      "  Using cached yarl-1.9.2-cp310-cp310-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (268 kB)\n",
      "Collecting frozenlist>=1.1.1\n",
      "  Using cached frozenlist-1.3.3-cp310-cp310-manylinux_2_5_x86_64.manylinux1_x86_64.manylinux_2_17_x86_64.manylinux2014_x86_64.whl (149 kB)\n",
      "Installing collected packages: multidict, frozenlist, async-timeout, yarl, aiosignal, aiohttp, openai\n",
      "\u001b[33m  WARNING: The script openai is installed in '/home/distill1994/.local/bin' which is not on PATH.\n",
      "  Consider adding this directory to PATH or, if you prefer to suppress this warning, use --no-warn-script-location.\u001b[0m\u001b[33m\n",
      "\u001b[0mSuccessfully installed aiohttp-3.8.4 aiosignal-1.3.1 async-timeout-4.0.2 frozenlist-1.3.3 multidict-6.0.4 openai-0.27.8 yarl-1.9.2\n",
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    }
   ],
   "source": [
    "pip install openai"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "78e6dd6b-0cd2-430e-b684-684901e6a7fe",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Defaulting to user installation because normal site-packages is not writeable\n",
      "Requirement already satisfied: openai in /home/distill1994/.local/lib/python3.10/site-packages (0.27.8)\n",
      "Requirement already satisfied: requests>=2.20 in /usr/local/lib/python3.10/dist-packages (from openai) (2.29.0)\n",
      "Requirement already satisfied: aiohttp in /home/distill1994/.local/lib/python3.10/site-packages (from openai) (3.8.4)\n",
      "Requirement already satisfied: tqdm in /home/distill1994/.local/lib/python3.10/site-packages (from openai) (4.65.0)\n",
      "Requirement already satisfied: certifi>=2017.4.17 in /usr/local/lib/python3.10/dist-packages (from requests>=2.20->openai) (2022.12.7)\n",
      "Requirement already satisfied: urllib3<1.27,>=1.21.1 in /usr/local/lib/python3.10/dist-packages (from requests>=2.20->openai) (1.26.15)\n",
      "Requirement already satisfied: charset-normalizer<4,>=2 in /usr/local/lib/python3.10/dist-packages (from requests>=2.20->openai) (3.1.0)\n",
      "Requirement already satisfied: idna<4,>=2.5 in /usr/local/lib/python3.10/dist-packages (from requests>=2.20->openai) (3.4)\n",
      "Requirement already satisfied: frozenlist>=1.1.1 in /home/distill1994/.local/lib/python3.10/site-packages (from aiohttp->openai) (1.3.3)\n",
      "Requirement already satisfied: aiosignal>=1.1.2 in /home/distill1994/.local/lib/python3.10/site-packages (from aiohttp->openai) (1.3.1)\n",
      "Requirement already satisfied: async-timeout<5.0,>=4.0.0a3 in /home/distill1994/.local/lib/python3.10/site-packages (from aiohttp->openai) (4.0.2)\n",
      "Requirement already satisfied: multidict<7.0,>=4.5 in /home/distill1994/.local/lib/python3.10/site-packages (from aiohttp->openai) (6.0.4)\n",
      "Requirement already satisfied: attrs>=17.3.0 in /usr/local/lib/python3.10/dist-packages (from aiohttp->openai) (23.1.0)\n",
      "Requirement already satisfied: yarl<2.0,>=1.0 in /home/distill1994/.local/lib/python3.10/site-packages (from aiohttp->openai) (1.9.2)\n",
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    }
   ],
   "source": [
    "%pip install openai"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "39cfb95d-0f49-4d81-88f4-80750e9c03f9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "OpenAI API key:  ········\n"
     ]
    }
   ],
   "source": [
    "import getpass\n",
    "\n",
    "openai_api_key = getpass.getpass(\"OpenAI API key: \")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "6ad41e6e-c0b0-489a-b6ff-02eb6d069034",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import openai\n",
    "\n",
    "openai.api_key = openai_api_key"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "4501d561-7c3c-4374-bb4f-f737641c983f",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import csv\n",
    "\n",
    "# Function to get already processed names\n",
    "def get_processed_names(file):\n",
    "    if os.path.exists(file):\n",
    "        with open(file, 'r') as f:\n",
    "            reader = csv.DictReader(f)\n",
    "            return {row['Name'] for row in reader}\n",
    "    else:\n",
    "        return set()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "26ba47f1-f734-4fb9-a9e1-4e82dd6fb59a",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Get already processed names\n",
    "processed_names = get_processed_names('name_variations.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "94ad9ac2-dc0b-4180-8d08-bfd3af79fd71",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "9548"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(processed_names)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c79b5d30-e486-4850-868d-b9d75e4a12a2",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Rate limit exceeded. Sleeping for a minute.\n",
      "Rate limit exceeded. Sleeping for a minute.\n",
      "Rate limit exceeded. Sleeping for a minute.\n",
      "Rate limit exceeded. Sleeping for a minute.\n",
      "Rate limit exceeded. Sleeping for a minute.\n",
      "Rate limit exceeded. Sleeping for a minute.\n",
      "Rate limit exceeded. Sleeping for a minute.\n",
      "Rate limit exceeded. Sleeping for a minute.\n",
      "Rate limit exceeded. Sleeping for a minute.\n"
     ]
    }
   ],
   "source": [
    "import csv\n",
    "import time\n",
    "\n",
    "# Function to divide names into chunks\n",
    "def chunks(lst, n):\n",
    "    for i in range(0, len(lst), n):\n",
    "        yield lst[i:i + n]\n",
    "\n",
    "        \n",
    "with open('name_variations.csv', 'a', newline='') as csvfile:\n",
    "    fieldnames = ['Name', 'Alternate 1', 'Alternate 2', 'Alternate 3', 'Alternate 4', 'Alternate 5']\n",
    "    writer = csv.DictWriter(csvfile, fieldnames=fieldnames)\n",
    "\n",
    "    # If file is empty write the header\n",
    "    if csvfile.tell() == 0:\n",
    "        writer.writeheader()     \n",
    "        \n",
    "    c = 0\n",
    "    # Iterate over names in chunks\n",
    "    for chunk in chunks(list(last_name_values), 15):\n",
    "        # Skip names that have been processed\n",
    "        chunk = [name for name in chunk if name not in processed_names]\n",
    "        if not chunk:\n",
    "            continue\n",
    "        \n",
    "        # Form the prompt\n",
    "        # print(chunk)\n",
    "        prompt = \"give me 5 most common alternate spellings of the last names, format as csv - \"\n",
    "        names = \",\".join([f\" {name.lower()}\" for name in chunk])\n",
    "        prompt = prompt + names\n",
    "        # print(prompt)\n",
    "\n",
    "        messages = [{\"role\": \"user\", \"content\": prompt}]\n",
    "        \n",
    "        while True:\n",
    "            try:\n",
    "                # Make the API call\n",
    "                response = openai.ChatCompletion.create(\n",
    "                  model=\"gpt-3.5-turbo\",\n",
    "                  messages=messages,\n",
    "                  temperature=0# adjust depending on the number of variations and length of names\n",
    "                )\n",
    "            except:\n",
    "                print(\"Rate limit exceeded. Sleeping for a minute.\")\n",
    "                time.sleep(60)  # Sleep for a minute and then retry\n",
    "                continue\n",
    "\n",
    "            break  # If no errors, break the loop\n",
    "\n",
    "        # print(response)\n",
    "        # Process the result\n",
    "        results = response.choices[0].message.content.strip().split('\\n')\n",
    "        # print(results)\n",
    "\n",
    "        for name, result in zip(chunk, results):\n",
    "            alternates = result.split(',')\n",
    "            #print(f\"{name}: {alternates}\")\n",
    "            # Write the name and alternates to the CSV file\n",
    "            writer.writerow({\n",
    "                'Name': name,\n",
    "                'Alternate 1': alternates[0].strip() if len(alternates) > 0 else '',\n",
    "                'Alternate 2': alternates[1].strip() if len(alternates) > 1 else '',\n",
    "                'Alternate 3': alternates[2].strip() if len(alternates) > 2 else '',\n",
    "                'Alternate 4': alternates[3].strip() if len(alternates) > 3 else '',\n",
    "                'Alternate 5': alternates[4].strip() if len(alternates) > 4 else '',\n",
    "            })\n",
    "\n",
    "print(\"done\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "71e53d77-a33f-4ad3-ac4a-879872d2509a",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "result_df = pd.read_csv(\"name_variations.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "9c131f60-d992-4a1d-b2fe-1d39843796d2",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(22215, 6)"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "a5f2cb25-9e79-401b-bc42-744b79b98f16",
   "metadata": {
    "tags": []
   },
   "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>Name</th>\n",
       "      <th>Alternate 1</th>\n",
       "      <th>Alternate 2</th>\n",
       "      <th>Alternate 3</th>\n",
       "      <th>Alternate 4</th>\n",
       "      <th>Alternate 5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>18911</th>\n",
       "      <td>Ertle</td>\n",
       "      <td>ertle</td>\n",
       "      <td>ertel</td>\n",
       "      <td>ertal</td>\n",
       "      <td>ertol</td>\n",
       "      <td>ertul</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14751</th>\n",
       "      <td>Pasteur</td>\n",
       "      <td>pasteur</td>\n",
       "      <td>Pasteur</td>\n",
       "      <td>Pastor</td>\n",
       "      <td>Pastore</td>\n",
       "      <td>Pastorius</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3312</th>\n",
       "      <td>Fusco</td>\n",
       "      <td>fusco</td>\n",
       "      <td>fusk</td>\n",
       "      <td>fusc</td>\n",
       "      <td>fusko</td>\n",
       "      <td>fuscus</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13144</th>\n",
       "      <td>Lambrecht</td>\n",
       "      <td>lambrecht</td>\n",
       "      <td>lambreht</td>\n",
       "      <td>lambright</td>\n",
       "      <td>lambrect</td>\n",
       "      <td>lambret</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Thompson</td>\n",
       "      <td>thomson</td>\n",
       "      <td>thompsen</td>\n",
       "      <td>thompsin</td>\n",
       "      <td>thompsan</td>\n",
       "      <td>thompsun</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>215</th>\n",
       "      <td>Velez</td>\n",
       "      <td>velez</td>\n",
       "      <td>Velezz</td>\n",
       "      <td>Veles</td>\n",
       "      <td>Velesz</td>\n",
       "      <td>Veleszz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1053</th>\n",
       "      <td>Akins</td>\n",
       "      <td>Akins</td>\n",
       "      <td>Akens</td>\n",
       "      <td>Akyns</td>\n",
       "      <td>Akinn</td>\n",
       "      <td>Akines</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14757</th>\n",
       "      <td>Althouse</td>\n",
       "      <td>althouse</td>\n",
       "      <td>Althouse</td>\n",
       "      <td>Althaus</td>\n",
       "      <td>Althausen</td>\n",
       "      <td>Althous</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18645</th>\n",
       "      <td>Lucey</td>\n",
       "      <td>lucey</td>\n",
       "      <td>lucy</td>\n",
       "      <td>lucie</td>\n",
       "      <td>luci</td>\n",
       "      <td>luccey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12166</th>\n",
       "      <td>Ruzicka</td>\n",
       "      <td>ruzicka</td>\n",
       "      <td>ruzika</td>\n",
       "      <td>ruzickah</td>\n",
       "      <td>ruzickaa</td>\n",
       "      <td>ruzick</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Name Alternate 1 Alternate 2 Alternate 3 Alternate 4 Alternate 5\n",
       "18911      Ertle       ertle       ertel       ertal       ertol       ertul\n",
       "14751    Pasteur     pasteur     Pasteur      Pastor     Pastore   Pastorius\n",
       "3312       Fusco       fusco        fusk        fusc       fusko      fuscus\n",
       "13144  Lambrecht   lambrecht    lambreht   lambright    lambrect     lambret\n",
       "19      Thompson     thomson    thompsen    thompsin    thompsan    thompsun\n",
       "215        Velez       velez      Velezz       Veles      Velesz     Veleszz\n",
       "1053       Akins       Akins       Akens       Akyns       Akinn      Akines\n",
       "14757   Althouse    althouse    Althouse     Althaus   Althausen     Althous\n",
       "18645      Lucey       lucey        lucy       lucie        luci      luccey\n",
       "12166    Ruzicka     ruzicka      ruzika    ruzickah    ruzickaa      ruzick"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result_df.sample(n=10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "87e68171-7600-4e6b-b636-4a1af41b9767",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(22215, 6)"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result_df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1f4859c5-3d4b-4bd8-8d7d-85b92ebdc9cf",
   "metadata": {},
   "source": [
    "## Creating dataset with generated content"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "bcfc8135-7c1b-453d-9ef6-e6a3194b9d49",
   "metadata": {
    "tags": []
   },
   "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>name_last</th>\n",
       "      <th>asian</th>\n",
       "      <th>hispanic</th>\n",
       "      <th>nh_black</th>\n",
       "      <th>nh_white</th>\n",
       "      <th>other</th>\n",
       "      <th>total_n</th>\n",
       "      <th>race</th>\n",
       "      <th>race_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Neaman-Piotrowicz</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>nh_white</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Chaet</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>13.0</td>\n",
       "      <td>nh_white</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Veloz Perez</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>hispanic</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Spalter</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.928571</td>\n",
       "      <td>0.071429</td>\n",
       "      <td>42.0</td>\n",
       "      <td>nh_white</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Kearns-Edwards</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>nh_white</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           name_last  asian  hispanic  nh_black  nh_white     other  total_n   \n",
       "0  Neaman-Piotrowicz    0.0       0.0       0.0  1.000000  0.000000      1.0  \\\n",
       "1              Chaet    0.0       0.0       0.0  1.000000  0.000000     13.0   \n",
       "2        Veloz Perez    0.0       1.0       0.0  0.000000  0.000000      1.0   \n",
       "3            Spalter    0.0       0.0       0.0  0.928571  0.071429     42.0   \n",
       "4     Kearns-Edwards    0.0       0.0       0.0  1.000000  0.000000      1.0   \n",
       "\n",
       "       race  race_code  \n",
       "0  nh_white          3  \n",
       "1  nh_white          3  \n",
       "2  hispanic          1  \n",
       "3  nh_white          3  \n",
       "4  nh_white          3  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "866aeaea-f459-4627-9531-17c0fe1a9321",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df[df['name_last'] == \"ad\"]['total_n'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "edf40856-8b05-490f-8b5c-ff368fd00437",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "names = []\n",
    "race = []\n",
    "race_codes = []\n",
    "alt_cols = list(result_df.columns[1:])\n",
    "\n",
    "def process(x):\n",
    "    if len(df[df['name_last'] == x.Name]['total_n']):\n",
    "        match = df.loc[df[df['name_last'] == x.Name]['total_n'].idxmax()]\n",
    "        rc = match['race']\n",
    "        rcc = match['race_code']\n",
    "        for c in alt_cols:\n",
    "            if x[c]:\n",
    "                names.append(x[c])\n",
    "                race.append(rc)\n",
    "                race_codes.append(rcc)\n",
    "\n",
    "    \n",
    "result_df.apply(lambda x: process(x), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "d31b163c-6618-405c-9ba1-aafddb8c615c",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "101660\n",
      "101660\n",
      "101660\n"
     ]
    }
   ],
   "source": [
    "print(len(names))\n",
    "print(len(race))\n",
    "print(len(names))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "9d2065bf-9635-4a16-9909-dc692906edfa",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "gen_names = pd.DataFrame({'name_last': names, 'race': race, 'race_code': race_codes})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "a5bfa2ed-5a60-4efd-96dd-dec22a6b2613",
   "metadata": {
    "tags": []
   },
   "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>name_last</th>\n",
       "      <th>race</th>\n",
       "      <th>race_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>85192</th>\n",
       "      <td>roste</td>\n",
       "      <td>nh_white</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80403</th>\n",
       "      <td>rivera otero</td>\n",
       "      <td>hispanic</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99096</th>\n",
       "      <td>bassforde</td>\n",
       "      <td>nh_white</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26611</th>\n",
       "      <td>neubaur</td>\n",
       "      <td>nh_white</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99093</th>\n",
       "      <td>gregowe</td>\n",
       "      <td>nh_white</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1333</th>\n",
       "      <td>levey</td>\n",
       "      <td>nh_white</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46137</th>\n",
       "      <td>garretsen</td>\n",
       "      <td>nh_white</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55924</th>\n",
       "      <td>kirschenbom</td>\n",
       "      <td>nh_white</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16911</th>\n",
       "      <td>Harm</td>\n",
       "      <td>nh_white</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96698</th>\n",
       "      <td>hamontreee;hammontree</td>\n",
       "      <td>nh_white</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   name_last      race  race_code\n",
       "85192                  roste  nh_white          3\n",
       "80403           rivera otero  hispanic          1\n",
       "99096              bassforde  nh_white          3\n",
       "26611                neubaur  nh_white          3\n",
       "99093                gregowe  nh_white          3\n",
       "1333                   levey  nh_white          3\n",
       "46137              garretsen  nh_white          3\n",
       "55924            kirschenbom  nh_white          3\n",
       "16911                   Harm  nh_white          3\n",
       "96698  hamontreee;hammontree  nh_white          3"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gen_names.sample(n=10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "5dc4b3ba-b888-4d53-bb55-fd725a99f337",
   "metadata": {},
   "outputs": [],
   "source": [
    "gen_names['name_last'] = gen_names['name_last'].str.title()\n",
    "gen_names['name_last'] = gen_names['name_last'].str.replace(\"[^a-zA-Z' -]\", '', regex=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "a94dccd6-70e3-4efc-9ebc-c79aafef9cdb",
   "metadata": {},
   "outputs": [],
   "source": [
    "gen_names.dropna(inplace=True)\n",
    "gen_names.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "fffc36eb-6f89-4335-81aa-4b7ded647a17",
   "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>index</th>\n",
       "      <th>name_last</th>\n",
       "      <th>race</th>\n",
       "      <th>race_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>Williams</td>\n",
       "      <td>nh_black</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Willaims</td>\n",
       "      <td>nh_black</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Williems</td>\n",
       "      <td>nh_black</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Williames</td>\n",
       "      <td>nh_black</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>Williamms</td>\n",
       "      <td>nh_black</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index  name_last      race  race_code\n",
       "0      0   Williams  nh_black          2\n",
       "1      1   Willaims  nh_black          2\n",
       "2      2   Williems  nh_black          2\n",
       "3      3  Williames  nh_black          2\n",
       "4      4  Williamms  nh_black          2"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gen_names.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "47e73b98-00b3-48fa-a7a9-e81b96c72ab0",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "gen_names.to_csv(\"name_variations_with_race.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "dc403bae-3771-438e-9f87-b757acda1225",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "final_df = pd.concat([df[['name_last','race','race_code']], gen_names[['name_last','race','race_code']]])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "a2c5c123-7fc7-4578-aec3-a9deb2cee862",
   "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>name_last</th>\n",
       "      <th>race</th>\n",
       "      <th>race_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Neaman-Piotrowicz</td>\n",
       "      <td>nh_white</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Chaet</td>\n",
       "      <td>nh_white</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Veloz Perez</td>\n",
       "      <td>hispanic</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Spalter</td>\n",
       "      <td>nh_white</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Kearns-Edwards</td>\n",
       "      <td>nh_white</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           name_last      race  race_code\n",
       "0  Neaman-Piotrowicz  nh_white          3\n",
       "1              Chaet  nh_white          3\n",
       "2        Veloz Perez  hispanic          1\n",
       "3            Spalter  nh_white          3\n",
       "4     Kearns-Edwards  nh_white          3"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "cdefd82f-2f6e-4cf3-afc2-cad3d53ae7e4",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(100710, 4)"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gen_names.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "1cf6cbb6-4443-4dc8-84c4-7de550bc93df",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(946026, 3)"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "30715a4e-e411-4433-81d2-6636c9f49b13",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "final_df.to_csv(\"fl_2022_LastName_train_with_gen.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "e3f03cf2-a9b9-4d81-97f0-90367bd49e07",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "20M\tfl_2022_LastName_train_with_gen.csv\n",
      "164K\tlast_names.csv\n",
      "1.1M\tname_variations.csv\n",
      "2.4M\tname_variations_with_race.csv\n"
     ]
    }
   ],
   "source": [
    "!du -sh *.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f51bd150-0827-462b-a216-5d169d4ad96c",
   "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.10.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}