chatgpt_3.5_turbo_clean.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openai import OpenAI\n",
    "import pandas as pd\n",
    "import os\n",
    "import json"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Load web scrape CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "metadata": {},
   "outputs": [],
   "source": [
    "chess_csv = pd.read_csv(\"20240422_20-19-51_coaches_table.csv\")\n",
    "chess_csv.rate = chess_csv.rate.astype(str)\n",
    "chess_csv.location = chess_csv.location.astype(str)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Run OpenAI 3.5 turbo to clean *rates*\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [],
   "source": [
    "results = {}\n",
    "\n",
    "# Define the system message\n",
    "system_msg = 'You are a helpful assistant who is multilingual and designed to output json with hourly_rate and currency as keys. Based on my inputs, your goal is to output the hourly rate as well as the currency used. Convert the currency to standardised ISO 4217 code. If a range is given, provide the average. If the information is unclear, output \"Unknown\".'\n",
    "\n",
    "client = OpenAI(\n",
    "    api_key=os.environ.get(\"OPENAI_API_KEY\"),\n",
    ")\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n",
      "10\n",
      "20\n",
      "30\n",
      "40\n",
      "50\n",
      "60\n",
      "70\n",
      "80\n",
      "90\n",
      "100\n",
      "110\n",
      "120\n",
      "130\n",
      "140\n",
      "150\n",
      "160\n",
      "170\n",
      "180\n",
      "190\n",
      "200\n",
      "210\n",
      "220\n",
      "230\n",
      "240\n",
      "250\n",
      "260\n",
      "270\n",
      "280\n",
      "290\n",
      "300\n",
      "310\n",
      "320\n",
      "330\n",
      "340\n",
      "350\n",
      "360\n",
      "370\n",
      "380\n",
      "390\n",
      "400\n",
      "410\n",
      "420\n",
      "430\n",
      "440\n",
      "450\n",
      "460\n",
      "470\n",
      "480\n",
      "490\n",
      "500\n",
      "510\n",
      "520\n",
      "530\n",
      "540\n",
      "550\n",
      "560\n",
      "570\n",
      "580\n",
      "590\n",
      "600\n",
      "610\n",
      "620\n",
      "630\n",
      "640\n",
      "650\n",
      "660\n",
      "670\n",
      "680\n",
      "690\n",
      "700\n",
      "710\n",
      "720\n",
      "730\n",
      "740\n",
      "750\n",
      "760\n",
      "770\n",
      "780\n",
      "790\n",
      "800\n",
      "810\n",
      "820\n",
      "830\n",
      "840\n",
      "850\n",
      "860\n",
      "870\n",
      "880\n",
      "890\n",
      "900\n",
      "910\n",
      "920\n",
      "930\n",
      "940\n",
      "950\n",
      "960\n",
      "970\n",
      "980\n",
      "990\n",
      "1000\n",
      "1010\n",
      "1020\n",
      "1030\n",
      "1040\n",
      "1050\n",
      "1060\n",
      "1070\n",
      "1080\n",
      "1090\n",
      "1100\n",
      "1110\n",
      "1120\n",
      "1130\n",
      "1140\n",
      "1150\n",
      "1160\n",
      "1170\n",
      "1180\n",
      "1190\n",
      "1200\n",
      "1210\n",
      "1220\n",
      "1230\n",
      "1240\n",
      "1250\n",
      "1260\n",
      "1270\n",
      "1280\n",
      "1290\n",
      "1300\n",
      "1310\n",
      "1320\n",
      "1330\n",
      "1340\n",
      "1350\n",
      "1360\n",
      "1370\n",
      "1380\n",
      "1390\n",
      "1400\n",
      "1410\n",
      "1420\n",
      "1430\n",
      "1440\n",
      "1450\n",
      "1460\n",
      "1470\n",
      "1480\n",
      "1490\n",
      "1500\n",
      "1510\n",
      "1520\n",
      "1530\n",
      "1540\n",
      "1550\n",
      "1560\n",
      "1570\n",
      "1580\n",
      "1590\n",
      "1600\n",
      "1610\n",
      "1620\n",
      "1630\n"
     ]
    }
   ],
   "source": [
    "for index, rate in enumerate(chess_csv.rate):\n",
    "    if index%10 == 0:\n",
    "        print(index)\n",
    "    response = client.chat.completions.create(model=\"gpt-3.5-turbo\",\n",
    "                                            messages=[{\"role\": \"system\", \"content\": system_msg},\n",
    "                                            {\"role\": \"user\", \"content\": rate}],\n",
    "                                            response_format={ \"type\": \"json_object\" })\n",
    "    results[index] = response.choices[0].message.content"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Run OpenAI 3.5 turbo to clean *location*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "metadata": {},
   "outputs": [],
   "source": [
    "results_locations = {}\n",
    "\n",
    "# Define the system message\n",
    "system_msg = 'You are a helpful assistant who is multilingual and designed to output json with \"country\" as key. Based on my inputs, your goal is to output the country that the region is from. If the information is unclear, output \"Unknown\"'\n",
    "\n",
    "client = OpenAI(\n",
    "    api_key=os.environ.get(\"OPENAI_API_KEY\"),\n",
    ")\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n",
      "10\n",
      "20\n",
      "30\n",
      "40\n",
      "50\n",
      "60\n",
      "70\n",
      "80\n",
      "90\n",
      "100\n",
      "110\n",
      "120\n",
      "130\n",
      "140\n",
      "150\n",
      "160\n",
      "170\n",
      "180\n",
      "190\n",
      "200\n",
      "210\n",
      "220\n",
      "230\n",
      "240\n",
      "250\n",
      "260\n",
      "270\n",
      "280\n",
      "290\n",
      "300\n",
      "310\n",
      "320\n",
      "330\n",
      "340\n",
      "350\n",
      "360\n",
      "370\n",
      "380\n",
      "390\n",
      "400\n",
      "410\n",
      "420\n",
      "430\n",
      "440\n",
      "450\n",
      "460\n",
      "470\n",
      "480\n",
      "490\n",
      "500\n",
      "510\n",
      "520\n",
      "530\n",
      "540\n",
      "550\n",
      "560\n",
      "570\n",
      "580\n",
      "590\n",
      "600\n",
      "610\n",
      "620\n",
      "630\n",
      "640\n",
      "650\n",
      "660\n",
      "670\n",
      "680\n",
      "690\n",
      "700\n",
      "710\n",
      "720\n",
      "730\n",
      "740\n",
      "750\n",
      "760\n",
      "770\n",
      "780\n",
      "790\n",
      "800\n",
      "810\n",
      "820\n",
      "830\n",
      "840\n",
      "850\n",
      "860\n",
      "870\n",
      "880\n",
      "890\n",
      "900\n",
      "910\n",
      "920\n",
      "930\n",
      "940\n",
      "950\n",
      "960\n",
      "970\n",
      "980\n",
      "990\n",
      "1000\n",
      "1010\n",
      "1020\n",
      "1030\n",
      "1040\n",
      "1050\n",
      "1060\n",
      "1070\n",
      "1080\n",
      "1090\n",
      "1100\n",
      "1110\n",
      "1120\n",
      "1130\n",
      "1140\n",
      "1150\n",
      "1160\n",
      "1170\n",
      "1180\n",
      "1190\n",
      "1200\n",
      "1210\n",
      "1220\n",
      "1230\n",
      "1240\n",
      "1250\n",
      "1260\n",
      "1270\n",
      "1280\n",
      "1290\n",
      "1300\n",
      "1310\n",
      "1320\n",
      "1330\n",
      "1340\n",
      "1350\n",
      "1360\n",
      "1370\n",
      "1380\n",
      "1390\n",
      "1400\n",
      "1410\n",
      "1420\n",
      "1430\n",
      "1440\n",
      "1450\n",
      "1460\n",
      "1470\n",
      "1480\n",
      "1490\n",
      "1500\n",
      "1510\n",
      "1520\n",
      "1530\n",
      "1540\n",
      "1550\n",
      "1560\n",
      "1570\n",
      "1580\n",
      "1590\n",
      "1600\n",
      "1610\n",
      "1620\n",
      "1630\n"
     ]
    }
   ],
   "source": [
    "for index, location in enumerate(chess_csv.location):\n",
    "    if index%10 == 0:\n",
    "        print(index)\n",
    "    response = client.chat.completions.create(model=\"gpt-3.5-turbo\",\n",
    "                                            messages=[{\"role\": \"system\", \"content\": system_msg},\n",
    "                                            {\"role\": \"user\", \"content\": location}],\n",
    "                                            response_format={ \"type\": \"json_object\" })\n",
    "    results_locations[index] = response.choices[0].message.content"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Process data into final dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "metadata": {},
   "outputs": [
    {
     "ename": "TypeError",
     "evalue": "the JSON object must be str, bytes or bytearray, not int",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[171], line 2\u001b[0m\n\u001b[0;32m      1\u001b[0m results_locations_df \u001b[38;5;241m=\u001b[39m pd\u001b[38;5;241m.\u001b[39mDataFrame([results_locations])\n\u001b[1;32m----> 2\u001b[0m location_dict_list \u001b[38;5;241m=\u001b[39m [\u001b[43mjson\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mloads\u001b[49m\u001b[43m(\u001b[49m\u001b[43mlocation\u001b[49m\u001b[43m)\u001b[49m \u001b[38;5;28;01mfor\u001b[39;00m location \u001b[38;5;129;01min\u001b[39;00m results_locations]\n\u001b[0;32m      3\u001b[0m location_dict_list\n",
      "File \u001b[1;32mc:\\Users\\kane9\\miniconda3\\envs\\scraping\\Lib\\json\\__init__.py:339\u001b[0m, in \u001b[0;36mloads\u001b[1;34m(s, cls, object_hook, parse_float, parse_int, parse_constant, object_pairs_hook, **kw)\u001b[0m\n\u001b[0;32m    337\u001b[0m \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[0;32m    338\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28misinstance\u001b[39m(s, (\u001b[38;5;28mbytes\u001b[39m, \u001b[38;5;28mbytearray\u001b[39m)):\n\u001b[1;32m--> 339\u001b[0m         \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mTypeError\u001b[39;00m(\u001b[38;5;124mf\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mthe JSON object must be str, bytes or bytearray, \u001b[39m\u001b[38;5;124m'\u001b[39m\n\u001b[0;32m    340\u001b[0m                         \u001b[38;5;124mf\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mnot \u001b[39m\u001b[38;5;132;01m{\u001b[39;00ms\u001b[38;5;241m.\u001b[39m\u001b[38;5;18m__class__\u001b[39m\u001b[38;5;241m.\u001b[39m\u001b[38;5;18m__name__\u001b[39m\u001b[38;5;132;01m}\u001b[39;00m\u001b[38;5;124m'\u001b[39m)\n\u001b[0;32m    341\u001b[0m     s \u001b[38;5;241m=\u001b[39m s\u001b[38;5;241m.\u001b[39mdecode(detect_encoding(s), \u001b[38;5;124m'\u001b[39m\u001b[38;5;124msurrogatepass\u001b[39m\u001b[38;5;124m'\u001b[39m)\n\u001b[0;32m    343\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m (\u001b[38;5;28mcls\u001b[39m \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m \u001b[38;5;129;01mand\u001b[39;00m object_hook \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m \u001b[38;5;129;01mand\u001b[39;00m\n\u001b[0;32m    344\u001b[0m         parse_int \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m \u001b[38;5;129;01mand\u001b[39;00m parse_float \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m \u001b[38;5;129;01mand\u001b[39;00m\n\u001b[0;32m    345\u001b[0m         parse_constant \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m \u001b[38;5;129;01mand\u001b[39;00m object_pairs_hook \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m \u001b[38;5;129;01mand\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m kw):\n",
      "\u001b[1;31mTypeError\u001b[0m: the JSON object must be str, bytes or bytearray, not int"
     ]
    }
   ],
   "source": [
    "results_locations_df = pd.DataFrame([results_locations])\n",
    "location_dict_list = [json.loads(location) for location in results_locations]\n",
    "location_dict_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "metadata": {},
   "outputs": [],
   "source": [
    "values = results.values()\n",
    "dict_list = [json.loads(value) for value in values]\n",
    "rates_df = pd.DataFrame(dict_list).loc[:,\"hourly_rate\":\"currency\"]\n",
    "df_with_cleaned_rates = pd.merge(chess_csv, rates_df, left_index=True, right_index=True)\n",
    "df_with_cleaned_rates.to_csv(\"20240422_20-19-51_coaches_chatgpt_table.csv\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "torch",
   "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.12.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}