alchemy.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2023-06-16T17:10:31.097792300Z",
     "start_time": "2023-06-16T17:10:28.373616500Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "#\n",
    "# DATA PROCESSING\n",
    "#"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 249,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# we can load it from .env\n",
    "# load .env in current folder\n",
    "\n",
    "OPENAI_API_KEY = os.getenv(\"OPENAI_KEY\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2023-06-16T17:10:32.216055500Z",
     "start_time": "2023-06-16T17:10:32.005885800Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['captchalogue_code', 'name', 'consumable', 'catalogue', 'lootonly',\n",
      "       'refrance', 'power', 'aggrieve', 'aggress', 'assail', 'assault',\n",
      "       'abuse', 'accuse', 'abjure', 'abstain', 'description', 'art', 'credit',\n",
      "       'abstratus', 'size', 'effects', 'Build_Grist_Cost', 'Amber_Cost',\n",
      "       'Amethyst_Cost', 'Artifact_Grist_Cost', 'Blood_Cost', 'Caulk_Cost',\n",
      "       'Chalk_Cost', 'Cobalt_Cost', 'Copper_Cost', 'Diamond_Cost',\n",
      "       'Emerald_Cost', 'Frosting_Cost', 'Garnet_Cost', 'Gold_Cost',\n",
      "       'Iodine_Cost', 'Jet_Cost', 'Malachite_Cost', 'Marble_Cost',\n",
      "       'Mercury_Cost', 'Obsidian_Cost', 'Opal_Cost', 'Polychromite_Cost',\n",
      "       'Quartz_Cost', 'Rainbow_Cost', 'Redstone_Cost', 'Rock_Candy_Cost',\n",
      "       'Rose_Quartz_Cost', 'Ruby_Cost', 'Rust_Cost', 'Shale_Cost',\n",
      "       'Star_Sapphire_Cost', 'Sulfur_Cost', 'Sunstone_Cost', 'Tar_Cost',\n",
      "       'Titanium_Cost', 'Topaz_Cost', 'Uranium_Cost', 'End_Of_Grists',\n",
      "       'hybrid'],\n",
      "      dtype='object')\n"
     ]
    },
    {
     "data": {
      "text/plain": "     captchalogue_code                                    name  consumable  \\\n0             PCHOOOOO                             Rocket Pack           0   \n1             nZ7Un6BI                             Claw Hammer           0   \n2             00000000                Perfectly Generic Object           1   \n3             126GH48G                             Pogo Hammer           0   \n4             OQXZ7nNN                            Shitty sword           0   \n...                ...                                     ...         ...   \n9199          5008eA0G           Appleture Science Jackniphone           0   \n9200          !Jtsczzk                             James Bonds           0   \n9201          YnEz!p!V                         Rare Contraband           0   \n9202          ef3MKaSq                   Some science bullshit           0   \n9203          resqobna  THE OVERSEER PROJECT SYSTEM ALERT CODE           0   \n\n      catalogue  lootonly  refrance  power   aggrieve    aggress     assail  \\\n0             0         1         0     20          0          0          0   \n1             1         0         0      2          0          0          0   \n2             0         0         0      0          0          0          0   \n3             0         0         0     25          1          0          0   \n4             1         0         0      2          0          0          0   \n...         ...       ...       ...    ...        ...        ...        ...   \n9199          0         0         0    168          0          0          0   \n9200          0         0         0      0          0          0          0   \n9201          0         0         0      0          0          0          0   \n9202          0         0         0      0          0          0          0   \n9203          0         0         0      1  100000000  100000000  100000000   \n\n      ...     Shale_Cost  Star_Sapphire_Cost    Sulfur_Cost  Sunstone_Cost  \\\n0     ...          40000                   0              0              0   \n1     ...              0                   0              0              0   \n2     ...              0                   0              0              0   \n3     ...             16                   0              0              0   \n4     ...              0                   0              0              0   \n...   ...            ...                 ...            ...            ...   \n9199  ...              0                   0              0              0   \n9200  ...              0                   0              0              0   \n9201  ...              0                   0              0              0   \n9202  ...              0                   0              0              0   \n9203  ...  1000000000000       1000000000000  1000000000000  1000000000000   \n\n           Tar_Cost  Titanium_Cost     Topaz_Cost   Uranium_Cost  \\\n0             50000              0              0              0   \n1                 0              0              0              0   \n2                 0              0              0              0   \n3                 0              0              0              0   \n4                 0              0              0              0   \n...             ...            ...            ...            ...   \n9199              0             15              0           1855   \n9200              0              0              0              0   \n9201              0              0              0              0   \n9202              0              0              0           1000   \n9203  1000000000000  1000000000000  1000000000000  1000000000000   \n\n     End_Of_Grists hybrid  \n0              NaN      0  \n1              NaN      0  \n2              NaN      0  \n3              NaN      0  \n4              NaN      0  \n...            ...    ...  \n9199           NaN      0  \n9200           NaN      0  \n9201           NaN      0  \n9202           NaN      0  \n9203           NaN      0  \n\n[9204 rows x 60 columns]",
      "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>captchalogue_code</th>\n      <th>name</th>\n      <th>consumable</th>\n      <th>catalogue</th>\n      <th>lootonly</th>\n      <th>refrance</th>\n      <th>power</th>\n      <th>aggrieve</th>\n      <th>aggress</th>\n      <th>assail</th>\n      <th>...</th>\n      <th>Shale_Cost</th>\n      <th>Star_Sapphire_Cost</th>\n      <th>Sulfur_Cost</th>\n      <th>Sunstone_Cost</th>\n      <th>Tar_Cost</th>\n      <th>Titanium_Cost</th>\n      <th>Topaz_Cost</th>\n      <th>Uranium_Cost</th>\n      <th>End_Of_Grists</th>\n      <th>hybrid</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>PCHOOOOO</td>\n      <td>Rocket Pack</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>20</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>40000</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>50000</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>nZ7Un6BI</td>\n      <td>Claw Hammer</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>00000000</td>\n      <td>Perfectly Generic Object</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>126GH48G</td>\n      <td>Pogo Hammer</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>25</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>16</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>OQXZ7nNN</td>\n      <td>Shitty sword</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</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      <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      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>9199</th>\n      <td>5008eA0G</td>\n      <td>Appleture Science Jackniphone</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>168</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>15</td>\n      <td>0</td>\n      <td>1855</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9200</th>\n      <td>!Jtsczzk</td>\n      <td>James Bonds</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9201</th>\n      <td>YnEz!p!V</td>\n      <td>Rare Contraband</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9202</th>\n      <td>ef3MKaSq</td>\n      <td>Some science bullshit</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1000</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9203</th>\n      <td>resqobna</td>\n      <td>THE OVERSEER PROJECT SYSTEM ALERT CODE</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <td>100000000</td>\n      <td>100000000</td>\n      <td>100000000</td>\n      <td>...</td>\n      <td>1000000000000</td>\n      <td>1000000000000</td>\n      <td>1000000000000</td>\n      <td>1000000000000</td>\n      <td>1000000000000</td>\n      <td>1000000000000</td>\n      <td>1000000000000</td>\n      <td>1000000000000</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n  </tbody>\n</table>\n<p>9204 rows × 60 columns</p>\n</div>"
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# We're going to load two SQLite .csv/.columns into dataframes\n",
    "# The filenames are overseer_v1_captchalogue.csv/.columsn and overseer_v2_captchalogue.csv/.columns\n",
    "\n",
    "# Load the first dataframe\n",
    "overseer1_df = pd.read_csv('input/overseer_v1_captchalogue.csv')\n",
    "print(overseer1_df.columns)\n",
    "overseer1_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "outputs": [
    {
     "data": {
      "text/plain": "          ID      code                          name  \\\n0          1  nZ7Un6BI                   Claw Hammer   \n1          2  00000000      Perfectly Generic Object   \n2          3  OQXZ7nNN                  Shitty sword   \n3          4  WN6?TpMT         Picture of Hella Jeff   \n4          5  MPd81h!6                  Fountain Pen   \n...      ...       ...                           ...   \n13182  22417  2IvTerXl  Faygo-brand weighted die-yos   \n13183  22418  9Qcmy4CH         Glassy Rain Protector   \n13184  22419  !1n1q0xX                    HourSickle   \n13185  22420  D03Gm70O                      Bochucks   \n13186  22421  x1n1q0RX            Sacrificial Sickle   \n\n                                             description  \\\n0                          An ordinary household hammer.   \n1      A medium-sized green cube. It is entirely gene...   \n2                                        A shitty sword.   \n3      It's a picture of one of the characters from S...   \n4      They say the pen is mightier than the 0I0Y5nML...   \n...                                                  ...   \n13182  This heavyweight squarish double yo-yo is lite...   \n13183  It's umbrela, but made with glass? Anyway it i...   \n13184  I don't think this would work as a clock hand ...   \n13185  A bo-staff that can be turned into nunchucks w...   \n13186  You and your friends are inside this thing...y...   \n\n                                              consumable  base  loot  \\\n0                                                    NaN     1     0   \n1      PRESERVE|SITUATIONAL:STRIFE:None|USEMESSAGE:Yo...     0     0   \n2                                                    NaN     1     0   \n3                                                    NaN     1     0   \n4                                                    NaN     1     0   \n...                                                  ...   ...   ...   \n13182                                                NaN     0     0   \n13183                                                NaN     0     0   \n13184                                                NaN     0     0   \n13185                                                NaN     0     0   \n13186                                                NaN     0     0   \n\n       refrance  session  power  ...           credit  \\\n0             0        0      2  ...             Anti   \n1             0        0      0  ...  omnipotentVirus   \n2             0        0      2  ...             Anti   \n3             0        0      0  ...  omnipotentVirus   \n4             0        0      1  ...     BigYip69Weed   \n...         ...      ...    ...  ...              ...   \n13182         0     6888     60  ...              NaN   \n13183         0     7012     11  ...              NaN   \n13184         0     6936     90  ...              NaN   \n13185         0     7031     12  ...              NaN   \n13186         0     6936    700  ...              NaN   \n\n                      abstratus  wearable     size  \\\n0                    hammerkind      none    small   \n1                    notaweapon      none  average   \n2                     bladekind      none  average   \n3                    notaweapon      none  average   \n4                       penkind      none    small   \n...                         ...       ...      ...   \n13182                  yoyokind      none  average   \n13183  mirrorkind, umbrellakind      none  average   \n13184       keykind, sicklekind      none  average   \n13185      ninjakind, staffkind      none  average   \n13186      yoyokind, sicklekind      none  average   \n\n                                                 effects  status  \\\n0                                                    NaN     NaN   \n1      SHUNT::Your Alchemiter is now a Perfectly Gene...     NaN   \n2                                                    NaN     NaN   \n3                                                    NaN     NaN   \n4                                                    NaN     NaN   \n...                                                  ...     ...   \n13182                                                NaN     NaN   \n13183                                                NaN     NaN   \n13184                                                NaN     NaN   \n13185                                                NaN     NaN   \n13186                                                NaN     NaN   \n\n                                              gristcosts  \\\n0                                         Build_Grist:2|   \n1                                         Build_Grist:2|   \n2                                         Build_Grist:3|   \n3                                           Artifact:-1|   \n4                                         Build_Grist:1|   \n...                                                  ...   \n13182  Blood:233|Build_Grist:778|Rust:389|Sandstone:2...   \n13183                    Build_Grist:63|Jet:13|Shale:19|   \n13184         Aquamarine:1286|Build_Grist:857|Shale:857|   \n13185                                   Build_Grist:116|   \n13186  Aquamarine:17060|Build_Grist:85301|Sandstone:3...   \n\n                                                   notes  \\\n0                                                    NaN   \n1                                                    NaN   \n2                                                    NaN   \n3                                                    NaN   \n4                                                    NaN   \n...                                                  ...   \n13182  I kind of feel like I may have weighted the gr...   \n13183                                                NaN   \n13184                                                NaN   \n13185                                        Awesomeness   \n13186                                                NaN   \n\n                                                nonsense old  \n0                            hope you like hammers dude!   0  \n1                                        not a statement   0  \n2                              all swords are shitty lol   0  \n3      how HIGH do you even have to BE just to DO som...   0  \n4       The SUPER APOSTROPHE 64 IIIIII\\'\\'\\'\\'\\'\\'\\'\\'\\'   0  \n...                                                  ...  ..  \n13182                                    not a statement   0  \n13183                                    not a statement   0  \n13184                                    not a statement   0  \n13185                                    not a statement   0  \n13186                                    not a statement   0  \n\n[13187 rows x 29 columns]",
      "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>ID</th>\n      <th>code</th>\n      <th>name</th>\n      <th>description</th>\n      <th>consumable</th>\n      <th>base</th>\n      <th>loot</th>\n      <th>refrance</th>\n      <th>session</th>\n      <th>power</th>\n      <th>...</th>\n      <th>credit</th>\n      <th>abstratus</th>\n      <th>wearable</th>\n      <th>size</th>\n      <th>effects</th>\n      <th>status</th>\n      <th>gristcosts</th>\n      <th>notes</th>\n      <th>nonsense</th>\n      <th>old</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>1</td>\n      <td>nZ7Un6BI</td>\n      <td>Claw Hammer</td>\n      <td>An ordinary household hammer.</td>\n      <td>NaN</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>2</td>\n      <td>...</td>\n      <td>Anti</td>\n      <td>hammerkind</td>\n      <td>none</td>\n      <td>small</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Build_Grist:2|</td>\n      <td>NaN</td>\n      <td>hope you like hammers dude!</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2</td>\n      <td>00000000</td>\n      <td>Perfectly Generic Object</td>\n      <td>A medium-sized green cube. It is entirely gene...</td>\n      <td>PRESERVE|SITUATIONAL:STRIFE:None|USEMESSAGE:Yo...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>omnipotentVirus</td>\n      <td>notaweapon</td>\n      <td>none</td>\n      <td>average</td>\n      <td>SHUNT::Your Alchemiter is now a Perfectly Gene...</td>\n      <td>NaN</td>\n      <td>Build_Grist:2|</td>\n      <td>NaN</td>\n      <td>not a statement</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>3</td>\n      <td>OQXZ7nNN</td>\n      <td>Shitty sword</td>\n      <td>A shitty sword.</td>\n      <td>NaN</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>2</td>\n      <td>...</td>\n      <td>Anti</td>\n      <td>bladekind</td>\n      <td>none</td>\n      <td>average</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Build_Grist:3|</td>\n      <td>NaN</td>\n      <td>all swords are shitty lol</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>4</td>\n      <td>WN6?TpMT</td>\n      <td>Picture of Hella Jeff</td>\n      <td>It's a picture of one of the characters from S...</td>\n      <td>NaN</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>omnipotentVirus</td>\n      <td>notaweapon</td>\n      <td>none</td>\n      <td>average</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Artifact:-1|</td>\n      <td>NaN</td>\n      <td>how HIGH do you even have to BE just to DO som...</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>5</td>\n      <td>MPd81h!6</td>\n      <td>Fountain Pen</td>\n      <td>They say the pen is mightier than the 0I0Y5nML...</td>\n      <td>NaN</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <td>...</td>\n      <td>BigYip69Weed</td>\n      <td>penkind</td>\n      <td>none</td>\n      <td>small</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Build_Grist:1|</td>\n      <td>NaN</td>\n      <td>The SUPER APOSTROPHE 64 IIIIII\\'\\'\\'\\'\\'\\'\\'\\'\\'</td>\n      <td>0</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      <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      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>13182</th>\n      <td>22417</td>\n      <td>2IvTerXl</td>\n      <td>Faygo-brand weighted die-yos</td>\n      <td>This heavyweight squarish double yo-yo is lite...</td>\n      <td>NaN</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>6888</td>\n      <td>60</td>\n      <td>...</td>\n      <td>NaN</td>\n      <td>yoyokind</td>\n      <td>none</td>\n      <td>average</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Blood:233|Build_Grist:778|Rust:389|Sandstone:2...</td>\n      <td>I kind of feel like I may have weighted the gr...</td>\n      <td>not a statement</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>13183</th>\n      <td>22418</td>\n      <td>9Qcmy4CH</td>\n      <td>Glassy Rain Protector</td>\n      <td>It's umbrela, but made with glass? Anyway it i...</td>\n      <td>NaN</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>7012</td>\n      <td>11</td>\n      <td>...</td>\n      <td>NaN</td>\n      <td>mirrorkind, umbrellakind</td>\n      <td>none</td>\n      <td>average</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Build_Grist:63|Jet:13|Shale:19|</td>\n      <td>NaN</td>\n      <td>not a statement</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>13184</th>\n      <td>22419</td>\n      <td>!1n1q0xX</td>\n      <td>HourSickle</td>\n      <td>I don't think this would work as a clock hand ...</td>\n      <td>NaN</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>6936</td>\n      <td>90</td>\n      <td>...</td>\n      <td>NaN</td>\n      <td>keykind, sicklekind</td>\n      <td>none</td>\n      <td>average</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Aquamarine:1286|Build_Grist:857|Shale:857|</td>\n      <td>NaN</td>\n      <td>not a statement</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>13185</th>\n      <td>22420</td>\n      <td>D03Gm70O</td>\n      <td>Bochucks</td>\n      <td>A bo-staff that can be turned into nunchucks w...</td>\n      <td>NaN</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>7031</td>\n      <td>12</td>\n      <td>...</td>\n      <td>NaN</td>\n      <td>ninjakind, staffkind</td>\n      <td>none</td>\n      <td>average</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Build_Grist:116|</td>\n      <td>Awesomeness</td>\n      <td>not a statement</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>13186</th>\n      <td>22421</td>\n      <td>x1n1q0RX</td>\n      <td>Sacrificial Sickle</td>\n      <td>You and your friends are inside this thing...y...</td>\n      <td>NaN</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>6936</td>\n      <td>700</td>\n      <td>...</td>\n      <td>NaN</td>\n      <td>yoyokind, sicklekind</td>\n      <td>none</td>\n      <td>average</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Aquamarine:17060|Build_Grist:85301|Sandstone:3...</td>\n      <td>NaN</td>\n      <td>not a statement</td>\n      <td>0</td>\n    </tr>\n  </tbody>\n</table>\n<p>13187 rows × 29 columns</p>\n</div>"
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load the second dataframe\n",
    "overseer2_df = pd.read_csv('input/overseer_v2_captchalogue.csv')\n",
    "overseer2_df"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Codes in the first dataframe that are not in the second dataframe: 44\n",
      "Codes in the second dataframe that are not in the first dataframe: 4027\n",
      "Codes that are in both dataframes: 9160\n"
     ]
    },
    {
     "data": {
      "text/plain": "     captchalogue_code                                               name  \\\n8267          c0NbG0a5                Electrified Derse Skull Wandstabber   \n8312          cmNbq0ab      Long-Distance Electrostabber Eight-Skull Wand   \n8326          6G71m0W5            Black Electromagic Eight-Skull Stabwand   \n8355          NG7Lm9X5        Soul-slicing Obsidian Electroskull Octawand   \n8359          LG3Lm9X5   Fiery Soul-rending Obsidian Skullectric Bonewand   \n8378          PGBrm9h1  Soul-searing Obsidian-Skull Bonewand of the Th...   \n8394          HG35m9X1  Fiery Soul-severing Obsidian Scienceskull Elec...   \n9167          wwzhtV7k  Faygo - Squiddletastic Candy Corn Ascension fl...   \n9168          4204011Z                              Chocolate Chip Cookie   \n9169          N2mAHKez                             Cherry Blood Coca-Cola   \n9170          000W248K                               Club\\'n Booty Briefs   \n9171          PwAmQTzs                                               DEEZ   \n9172          ITSAHEAD                                Half-Eaten Imp Head   \n9173          X0Z032u0                                           Bi Bacon   \n9174          AIVGncG6       Helvetic Army Gunblades of Alchemical Energy   \n9175          L4EVTb!!                                    TY Teddy Hammer   \n9176          lxIUxCzK                        Maliwan\\'s Slingularityshot   \n9177          zVd77KE5                                         Bugswatter   \n9178          UOkoi!!!                                       UO\\'s Shrine   \n9179          SOc24KE5                             Optimatum\\'s De-bugger   \n9180          lv2tCE!d                                       Steel Violin   \n9181          CuVx8Lpx  Poor Drawing  of a Horse Attacking a Football ...   \n9182          a3rM5H1d                                       Midas\\'s Die   \n9183          n?!JthBk                    Chocolaty Needlewands of Chance   \n9184          gHrW4Fh9            Silver Rocket Gauntlets of the Enforcer   \n9185          hQq!xV?K                            Reality-Directing Baton   \n9186          a24K0H0d                               Midas\\'s Die-Dropper   \n9187          THFM!6kk                         Spectral Grandfather Clock   \n9188          30?!1G?U                         Spandex-Covered Golden Axe   \n9189          !x!?!!t!                                     Fission Hacker   \n9190          YPE5ZUQj                               Enigmatic Void Armor   \n9191          W2IA5bC4                              Shitty Fake Headstone   \n9192          !v!!l?!z                                   Helm of Twilight   \n9193          7Gk3n00P                        Blank Shitty God-Tier Shirt   \n9194          WAR?xnR2                                 Sandy Cash Slammer   \n9195          afOmQ?G0                                     Bag of Bribery   \n9196          gLo0Ov3s                                      Rubber Gloves   \n9197          ORuYi5Jb                                 Bear Bear Bun Buns   \n9198          acassa??                                       How on Earth   \n9199          5008eA0G                      Appleture Science Jackniphone   \n9200          !Jtsczzk                                        James Bonds   \n9201          YnEz!p!V                                    Rare Contraband   \n9202          ef3MKaSq                              Some science bullshit   \n9203          resqobna             THE OVERSEER PROJECT SYSTEM ALERT CODE   \n\n      consumable  catalogue  lootonly  refrance  power   aggrieve    aggress  \\\n8267           0          0         0         0    350          0          0   \n8312           0          0         0         0    575          0          0   \n8326           0          0         0         0   1600          0          0   \n8355           0          0         0         0   2500          0          0   \n8359           0          0         0         0   3800          0          0   \n8378           0          0         0         0   9999          0          0   \n8394           0          0         0         0   6000          0          0   \n9167           1          0         0         0      0          0          0   \n9168           1          0         0         0      0          0          0   \n9169           1          0         0         0      0          0          0   \n9170           0          0         0         0      2          0          0   \n9171           1          0         0         0   5000          0          0   \n9172           1          0         1         0    150          0          0   \n9173           1          0         0         0      0          0          0   \n9174           0          0         0         0   9999       9999       9999   \n9175           0          0         0         0     35          0          0   \n9176           0          0         0         0     51          0          0   \n9177           0          0         0         0    404          0          0   \n9178           0          0         1         0      0          0          0   \n9179           0          0         0         0   3141       5926       5358   \n9180           0          0         0         0     10          0          0   \n9181           0          0         0         0      0          0          0   \n9182           0          0         0         0   1500          0          0   \n9183           0          0         0         0    200          0          0   \n9184           0          0         0         0   2999          0        -20   \n9185           0          0         0         0     20          0          0   \n9186           0          0         0         0   2300          0          0   \n9187           0          0         0         0     55          0          0   \n9188           0          0         0         0    120          0          0   \n9189           0          0         0         0    120          0          0   \n9190           0          0         0         0   1000          0          0   \n9191           0          0         0         0      5          0          0   \n9192           0          0         0         0    111          0          0   \n9193           0          0         0         0      3          0          0   \n9194           0          0         0         0    127          0          0   \n9195           0          0         0         0   9999       9999       9999   \n9196           0          1         0         0      2          0          0   \n9197           1          0         0         0      0          0          0   \n9198           0          0         0         0   9198          0          0   \n9199           0          0         0         0    168          0          0   \n9200           0          0         0         0      0          0          0   \n9201           0          0         0         0      0          0          0   \n9202           0          0         0         0      0          0          0   \n9203           0          0         0         0      1  100000000  100000000   \n\n         assail  ...     Shale_Cost  Star_Sapphire_Cost    Sulfur_Cost  \\\n8267          0  ...              0                   0              0   \n8312          0  ...              0                   0              0   \n8326          0  ...              0                   0              0   \n8355          0  ...              0                   0              0   \n8359          0  ...              0                   0              0   \n8378          0  ...              0                   0              0   \n8394          0  ...              0                   0              0   \n9167          0  ...              0                   0              0   \n9168          0  ...              0                   0              0   \n9169          0  ...              0                   0              0   \n9170          0  ...              0                   0              0   \n9171          0  ...              0                   0              0   \n9172          0  ...              0                   0              0   \n9173          0  ...              0                   0              0   \n9174       9999  ...              0                   0        3000000   \n9175          0  ...            105                   0              0   \n9176          0  ...              0                   0              0   \n9177          0  ...              0                   0              0   \n9178          0  ...              0                   0              0   \n9179       9793  ...              0             1000000              0   \n9180          0  ...              0                   0              0   \n9181          0  ...              0                   0              0   \n9182          0  ...              0                   0              0   \n9183          0  ...              0                   0              0   \n9184          0  ...              0                   0              0   \n9185          0  ...              0                   0              0   \n9186          0  ...              0                   0              0   \n9187          0  ...              0                   0              0   \n9188          0  ...              0                   0              0   \n9189          0  ...              0                   0              0   \n9190        150  ...         111111                   0              0   \n9191          0  ...              0                   0              0   \n9192          0  ...              0              177777              0   \n9193          0  ...              0                   0              0   \n9194          0  ...              0                   0              0   \n9195       9999  ...              0             1000000              0   \n9196          0  ...              0                   0              0   \n9197          0  ...              0                   0              0   \n9198          0  ...              0                   0              0   \n9199          0  ...              0                   0              0   \n9200          0  ...              0                   0              0   \n9201          0  ...              0                   0              0   \n9202          0  ...              0                   0              0   \n9203  100000000  ...  1000000000000       1000000000000  1000000000000   \n\n      Sunstone_Cost       Tar_Cost  Titanium_Cost     Topaz_Cost  \\\n8267              0              0           2500              0   \n8312              0              0           8300              0   \n8326              0          50000              0              0   \n8355              0         200000              0              0   \n8359         200000         300000              0              0   \n8378        2000000        3000000              0              0   \n8394         999999        1000000         850000              0   \n9167              0              0              0              0   \n9168              0              0              0              0   \n9169              0             50              0              0   \n9170              0              0              0              0   \n9171              0              0              0              0   \n9172              0              0              0              0   \n9173              0              0              0              0   \n9174        1000000        1000000        1000000              0   \n9175              0              0              0              0   \n9176              0              0              0              0   \n9177              0              0              0              0   \n9178              0              0              0              0   \n9179        1000000              0        1000000              0   \n9180              0              0              0              0   \n9181              0              3              0              0   \n9182              0              0              0              0   \n9183              0              0              0              0   \n9184              0              0         522222              0   \n9185              0              0              0              0   \n9186              0              0              0              0   \n9187              0              0              0              0   \n9188              0              0              0            250   \n9189              0            333              0              0   \n9190              0              0              0              0   \n9191              0              0              0              0   \n9192              0              0         711111         177777   \n9193              0              0              0              0   \n9194           5000              0           2500              0   \n9195              0              0              0        1000000   \n9196              0              3              0              0   \n9197              0              0              0              0   \n9198              0              0              0              0   \n9199              0              0             15              0   \n9200              0              0              0              0   \n9201              0              0              0              0   \n9202              0              0              0              0   \n9203  1000000000000  1000000000000  1000000000000  1000000000000   \n\n       Uranium_Cost End_Of_Grists hybrid  \n8267              0           NaN      0  \n8312              0           NaN      0  \n8326              0           NaN      0  \n8355              0           NaN      0  \n8359              0           NaN      0  \n8378              0           NaN      0  \n8394              0           NaN      0  \n9167              0           NaN      0  \n9168              0           NaN      0  \n9169              0           NaN      0  \n9170              0           NaN      0  \n9171              0           NaN      0  \n9172              0           NaN      1  \n9173              0           NaN      0  \n9174        3000000           NaN      0  \n9175              0           NaN      0  \n9176            122           NaN      0  \n9177              0           NaN      0  \n9178              0           NaN      0  \n9179              0           NaN      0  \n9180              0           NaN      0  \n9181              0           NaN      0  \n9182              0           NaN      0  \n9183              0           NaN      0  \n9184              0           NaN      0  \n9185             10           NaN      0  \n9186              0           NaN      0  \n9187            144           NaN      0  \n9188              0           NaN      0  \n9189            555           NaN      0  \n9190              0           NaN      0  \n9191              0           NaN      0  \n9192              0           NaN      0  \n9193              0           NaN      0  \n9194              0           NaN      0  \n9195              0           NaN      0  \n9196              0           NaN      0  \n9197              0           NaN      0  \n9198              0           NaN      0  \n9199           1855           NaN      0  \n9200              0           NaN      0  \n9201              0           NaN      0  \n9202           1000           NaN      0  \n9203  1000000000000           NaN      0  \n\n[44 rows x 60 columns]",
      "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>captchalogue_code</th>\n      <th>name</th>\n      <th>consumable</th>\n      <th>catalogue</th>\n      <th>lootonly</th>\n      <th>refrance</th>\n      <th>power</th>\n      <th>aggrieve</th>\n      <th>aggress</th>\n      <th>assail</th>\n      <th>...</th>\n      <th>Shale_Cost</th>\n      <th>Star_Sapphire_Cost</th>\n      <th>Sulfur_Cost</th>\n      <th>Sunstone_Cost</th>\n      <th>Tar_Cost</th>\n      <th>Titanium_Cost</th>\n      <th>Topaz_Cost</th>\n      <th>Uranium_Cost</th>\n      <th>End_Of_Grists</th>\n      <th>hybrid</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>8267</th>\n      <td>c0NbG0a5</td>\n      <td>Electrified Derse Skull Wandstabber</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>350</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>2500</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>8312</th>\n      <td>cmNbq0ab</td>\n      <td>Long-Distance Electrostabber Eight-Skull Wand</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>575</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>8300</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>8326</th>\n      <td>6G71m0W5</td>\n      <td>Black Electromagic Eight-Skull Stabwand</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1600</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>50000</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>8355</th>\n      <td>NG7Lm9X5</td>\n      <td>Soul-slicing Obsidian Electroskull Octawand</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>2500</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>200000</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>8359</th>\n      <td>LG3Lm9X5</td>\n      <td>Fiery Soul-rending Obsidian Skullectric Bonewand</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>3800</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>200000</td>\n      <td>300000</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>8378</th>\n      <td>PGBrm9h1</td>\n      <td>Soul-searing Obsidian-Skull Bonewand of the Th...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>9999</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>2000000</td>\n      <td>3000000</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>8394</th>\n      <td>HG35m9X1</td>\n      <td>Fiery Soul-severing Obsidian Scienceskull Elec...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>6000</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>999999</td>\n      <td>1000000</td>\n      <td>850000</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9167</th>\n      <td>wwzhtV7k</td>\n      <td>Faygo - Squiddletastic Candy Corn Ascension fl...</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9168</th>\n      <td>4204011Z</td>\n      <td>Chocolate Chip Cookie</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9169</th>\n      <td>N2mAHKez</td>\n      <td>Cherry Blood Coca-Cola</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>50</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9170</th>\n      <td>000W248K</td>\n      <td>Club\\'n Booty Briefs</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9171</th>\n      <td>PwAmQTzs</td>\n      <td>DEEZ</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>5000</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9172</th>\n      <td>ITSAHEAD</td>\n      <td>Half-Eaten Imp Head</td>\n      <td>1</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>150</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>9173</th>\n      <td>X0Z032u0</td>\n      <td>Bi Bacon</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9174</th>\n      <td>AIVGncG6</td>\n      <td>Helvetic Army Gunblades of Alchemical Energy</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>9999</td>\n      <td>9999</td>\n      <td>9999</td>\n      <td>9999</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>3000000</td>\n      <td>1000000</td>\n      <td>1000000</td>\n      <td>1000000</td>\n      <td>0</td>\n      <td>3000000</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9175</th>\n      <td>L4EVTb!!</td>\n      <td>TY Teddy Hammer</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>35</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>105</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9176</th>\n      <td>lxIUxCzK</td>\n      <td>Maliwan\\'s Slingularityshot</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>51</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>122</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9177</th>\n      <td>zVd77KE5</td>\n      <td>Bugswatter</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>404</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9178</th>\n      <td>UOkoi!!!</td>\n      <td>UO\\'s Shrine</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9179</th>\n      <td>SOc24KE5</td>\n      <td>Optimatum\\'s De-bugger</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>3141</td>\n      <td>5926</td>\n      <td>5358</td>\n      <td>9793</td>\n      <td>...</td>\n      <td>0</td>\n      <td>1000000</td>\n      <td>0</td>\n      <td>1000000</td>\n      <td>0</td>\n      <td>1000000</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9180</th>\n      <td>lv2tCE!d</td>\n      <td>Steel Violin</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>10</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9181</th>\n      <td>CuVx8Lpx</td>\n      <td>Poor Drawing  of a Horse Attacking a Football ...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>3</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9182</th>\n      <td>a3rM5H1d</td>\n      <td>Midas\\'s Die</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1500</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9183</th>\n      <td>n?!JthBk</td>\n      <td>Chocolaty Needlewands of Chance</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>200</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9184</th>\n      <td>gHrW4Fh9</td>\n      <td>Silver Rocket Gauntlets of the Enforcer</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>2999</td>\n      <td>0</td>\n      <td>-20</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>522222</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9185</th>\n      <td>hQq!xV?K</td>\n      <td>Reality-Directing Baton</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>20</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>10</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9186</th>\n      <td>a24K0H0d</td>\n      <td>Midas\\'s Die-Dropper</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>2300</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9187</th>\n      <td>THFM!6kk</td>\n      <td>Spectral Grandfather Clock</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>55</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>144</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9188</th>\n      <td>30?!1G?U</td>\n      <td>Spandex-Covered Golden Axe</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>120</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>250</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9189</th>\n      <td>!x!?!!t!</td>\n      <td>Fission Hacker</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>120</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>333</td>\n      <td>0</td>\n      <td>0</td>\n      <td>555</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9190</th>\n      <td>YPE5ZUQj</td>\n      <td>Enigmatic Void Armor</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1000</td>\n      <td>0</td>\n      <td>0</td>\n      <td>150</td>\n      <td>...</td>\n      <td>111111</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9191</th>\n      <td>W2IA5bC4</td>\n      <td>Shitty Fake Headstone</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>5</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9192</th>\n      <td>!v!!l?!z</td>\n      <td>Helm of Twilight</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>111</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>177777</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>711111</td>\n      <td>177777</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9193</th>\n      <td>7Gk3n00P</td>\n      <td>Blank Shitty God-Tier Shirt</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>3</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9194</th>\n      <td>WAR?xnR2</td>\n      <td>Sandy Cash Slammer</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>127</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>5000</td>\n      <td>0</td>\n      <td>2500</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9195</th>\n      <td>afOmQ?G0</td>\n      <td>Bag of Bribery</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>9999</td>\n      <td>9999</td>\n      <td>9999</td>\n      <td>9999</td>\n      <td>...</td>\n      <td>0</td>\n      <td>1000000</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1000000</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9196</th>\n      <td>gLo0Ov3s</td>\n      <td>Rubber Gloves</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>3</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9197</th>\n      <td>ORuYi5Jb</td>\n      <td>Bear Bear Bun Buns</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9198</th>\n      <td>acassa??</td>\n      <td>How on Earth</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>9198</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9199</th>\n      <td>5008eA0G</td>\n      <td>Appleture Science Jackniphone</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>168</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>15</td>\n      <td>0</td>\n      <td>1855</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9200</th>\n      <td>!Jtsczzk</td>\n      <td>James Bonds</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9201</th>\n      <td>YnEz!p!V</td>\n      <td>Rare Contraband</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9202</th>\n      <td>ef3MKaSq</td>\n      <td>Some science bullshit</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1000</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9203</th>\n      <td>resqobna</td>\n      <td>THE OVERSEER PROJECT SYSTEM ALERT CODE</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <td>100000000</td>\n      <td>100000000</td>\n      <td>100000000</td>\n      <td>...</td>\n      <td>1000000000000</td>\n      <td>1000000000000</td>\n      <td>1000000000000</td>\n      <td>1000000000000</td>\n      <td>1000000000000</td>\n      <td>1000000000000</td>\n      <td>1000000000000</td>\n      <td>1000000000000</td>\n      <td>NaN</td>\n      <td>0</td>\n    </tr>\n  </tbody>\n</table>\n<p>44 rows × 60 columns</p>\n</div>"
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Let's do some data exploration first\n",
    "# 1.Are there any codes in the first dataframe that are not in the second dataframe?\n",
    "# 2.Are there any codes in the second dataframe that are not in the first dataframe?\n",
    "# 3.Are there any codes that are in both dataframes?\n",
    "\n",
    "first_df_codes = overseer1_df['captchalogue_code']\n",
    "second_df_codes = overseer2_df['code']\n",
    "\n",
    "print(\n",
    "    f\"Codes in the first dataframe that are not in the second dataframe: {len(first_df_codes[~first_df_codes.isin(second_df_codes)])}\")\n",
    "print(\n",
    "    f\"Codes in the second dataframe that are not in the first dataframe: {len(second_df_codes[~second_df_codes.isin(first_df_codes)])}\")\n",
    "print(f\"Codes that are in both dataframes: {len(first_df_codes[first_df_codes.isin(second_df_codes)])}\")\n",
    "\n",
    "# Show the items with codes that are only in the first dataframe, using overseer1_df\n",
    "overseer1_df[~first_df_codes.isin(second_df_codes)]\n",
    "# They seem like dev items, no point in porting them, we'll just use the second dataframe"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "outputs": [
    {
     "data": {
      "text/plain": "         ID      code                 name  \\\n2684  11838  -LRcant-           LR\\'s Fuck   \n2992  12146  -LRnote-  Note From A Dev: LR   \n3664  12818  -abCw1ki            Wikia Pin   \n5820  14974  -N07H1N9              Nothing   \n7245  16399  lol@test  Status Testing Item   \n\n                                            description consumable  base  \\\n2684  How in the hell did you get ahold of one of th...        NaN     0   \n2992                              LR: You're Welcome ;p        NaN     0   \n3664  A pin that is bestowed <i>ONLY</i> upon the Wi...        NaN     0   \n5820                        Ask and thou shall recieve.        NaN     0   \n7245  This item is being used to test status effects...        NaN     0   \n\n      loot  refrance  session  power  ...  \\\n2684     0         0        0      0  ...   \n2992     0         0        0      0  ...   \n3664     0         0        0    100  ...   \n5820     0         0        0      0  ...   \n7245     0         0        0   9999  ...   \n\n                                                 credit   abstratus  \\\n2684  pops like five boners and runs into overseer m...  notaweapon   \n2992                                                NaN  notaweapon   \n3664                                                NaN  notaweapon   \n5820                                                NaN  notaweapon   \n7245                                                NaN    metakind   \n\n       wearable       size  effects  status  \\\n2684        NaN  ginormous      NaN     NaN   \n2992        NaN    average      NaN     NaN   \n3664  accessory    average      NaN     NaN   \n5820        NaN    average      NaN     NaN   \n7245        NaN      large      NaN     NaN   \n\n                                             gristcosts  notes  \\\n2684                            Artifact:9999999999999|    NaN   \n2992                                     Build_Grist:0|    NaN   \n3664                             Build_Grist:100000000|    NaN   \n5820                                     Build_Grist:1|    NaN   \n7245  Build_Grist:9223372036854775807|Amber:92233720...    NaN   \n\n             nonsense old  \n2684  not a statement   1  \n2992  not a statement   1  \n3664  not a statement   1  \n5820  not a statement   1  \n7245  not a statement   1  \n\n[5 rows x 29 columns]",
      "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>ID</th>\n      <th>code</th>\n      <th>name</th>\n      <th>description</th>\n      <th>consumable</th>\n      <th>base</th>\n      <th>loot</th>\n      <th>refrance</th>\n      <th>session</th>\n      <th>power</th>\n      <th>...</th>\n      <th>credit</th>\n      <th>abstratus</th>\n      <th>wearable</th>\n      <th>size</th>\n      <th>effects</th>\n      <th>status</th>\n      <th>gristcosts</th>\n      <th>notes</th>\n      <th>nonsense</th>\n      <th>old</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>2684</th>\n      <td>11838</td>\n      <td>-LRcant-</td>\n      <td>LR\\'s Fuck</td>\n      <td>How in the hell did you get ahold of one of th...</td>\n      <td>NaN</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>pops like five boners and runs into overseer m...</td>\n      <td>notaweapon</td>\n      <td>NaN</td>\n      <td>ginormous</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Artifact:9999999999999|</td>\n      <td>NaN</td>\n      <td>not a statement</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>2992</th>\n      <td>12146</td>\n      <td>-LRnote-</td>\n      <td>Note From A Dev: LR</td>\n      <td>LR: You're Welcome ;p</td>\n      <td>NaN</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>NaN</td>\n      <td>notaweapon</td>\n      <td>NaN</td>\n      <td>average</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Build_Grist:0|</td>\n      <td>NaN</td>\n      <td>not a statement</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>3664</th>\n      <td>12818</td>\n      <td>-abCw1ki</td>\n      <td>Wikia Pin</td>\n      <td>A pin that is bestowed &lt;i&gt;ONLY&lt;/i&gt; upon the Wi...</td>\n      <td>NaN</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>100</td>\n      <td>...</td>\n      <td>NaN</td>\n      <td>notaweapon</td>\n      <td>accessory</td>\n      <td>average</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Build_Grist:100000000|</td>\n      <td>NaN</td>\n      <td>not a statement</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>5820</th>\n      <td>14974</td>\n      <td>-N07H1N9</td>\n      <td>Nothing</td>\n      <td>Ask and thou shall recieve.</td>\n      <td>NaN</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>NaN</td>\n      <td>notaweapon</td>\n      <td>NaN</td>\n      <td>average</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Build_Grist:1|</td>\n      <td>NaN</td>\n      <td>not a statement</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>7245</th>\n      <td>16399</td>\n      <td>lol@test</td>\n      <td>Status Testing Item</td>\n      <td>This item is being used to test status effects...</td>\n      <td>NaN</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>9999</td>\n      <td>...</td>\n      <td>NaN</td>\n      <td>metakind</td>\n      <td>NaN</td>\n      <td>large</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Build_Grist:9223372036854775807|Amber:92233720...</td>\n      <td>NaN</td>\n      <td>not a statement</td>\n      <td>1</td>\n    </tr>\n  </tbody>\n</table>\n<p>5 rows × 29 columns</p>\n</div>"
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Later on we've discovered that there are some items with invalid codes in the second dataframe\n",
    "# nan is not valid\n",
    "# -LRcant- is not valid\n",
    "# -LRnote- is not valid\n",
    "# -abCw1ki is not valid\n",
    "# -N07H1N9 is not valid\n",
    "# lol@test is not valid\n",
    "# Let's see the items with those codes\n",
    "overseer2_df[overseer2_df['code'].isin(['nan', '-LRcant-', '-LRnote-', '-abCw1ki', '-N07H1N9', 'lol@test'])]"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "outputs": [
    {
     "data": {
      "text/plain": "           code                          name  \\\n0      nZ7Un6BI                   Claw Hammer   \n1      00000000      Perfectly Generic Object   \n2      OQXZ7nNN                  Shitty sword   \n3      WN6?TpMT         Picture of Hella Jeff   \n4      MPd81h!6                  Fountain Pen   \n...         ...                           ...   \n13182  2IvTerXl  Faygo-brand weighted die-yos   \n13183  9Qcmy4CH         Glassy Rain Protector   \n13184  !1n1q0xX                    HourSickle   \n13185  D03Gm70O                      Bochucks   \n13186  x1n1q0RX            Sacrificial Sickle   \n\n                                             description  \\\n0                          An ordinary household hammer.   \n1      A medium-sized green cube. It is entirely gene...   \n2                                        A shitty sword.   \n3      It's a picture of one of the characters from S...   \n4      They say the pen is mightier than the 0I0Y5nML...   \n...                                                  ...   \n13182  This heavyweight squarish double yo-yo is lite...   \n13183  It's umbrela, but made with glass? Anyway it i...   \n13184  I don't think this would work as a clock hand ...   \n13185  A bo-staff that can be turned into nunchucks w...   \n13186  You and your friends are inside this thing...y...   \n\n                      abstratus  power wearable     size  \\\n0                    hammerkind      2     none    small   \n1                    notaweapon      0     none  average   \n2                     bladekind      2     none  average   \n3                    notaweapon      0     none  average   \n4                       penkind      1     none    small   \n...                         ...    ...      ...      ...   \n13182                  yoyokind     60     none  average   \n13183  mirrorkind, umbrellakind     11     none  average   \n13184       keykind, sicklekind     90     none  average   \n13185      ninjakind, staffkind     12     none  average   \n13186      yoyokind, sicklekind    700     none  average   \n\n                                              gristcosts  base  \n0                                         Build_Grist:2|     1  \n1                                         Build_Grist:2|     0  \n2                                         Build_Grist:3|     1  \n3                                           Artifact:-1|     1  \n4                                         Build_Grist:1|     1  \n...                                                  ...   ...  \n13182  Blood:233|Build_Grist:778|Rust:389|Sandstone:2...     0  \n13183                    Build_Grist:63|Jet:13|Shale:19|     0  \n13184         Aquamarine:1286|Build_Grist:857|Shale:857|     0  \n13185                                   Build_Grist:116|     0  \n13186  Aquamarine:17060|Build_Grist:85301|Sandstone:3...     0  \n\n[13182 rows x 9 columns]",
      "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>code</th>\n      <th>name</th>\n      <th>description</th>\n      <th>abstratus</th>\n      <th>power</th>\n      <th>wearable</th>\n      <th>size</th>\n      <th>gristcosts</th>\n      <th>base</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>nZ7Un6BI</td>\n      <td>Claw Hammer</td>\n      <td>An ordinary household hammer.</td>\n      <td>hammerkind</td>\n      <td>2</td>\n      <td>none</td>\n      <td>small</td>\n      <td>Build_Grist:2|</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>00000000</td>\n      <td>Perfectly Generic Object</td>\n      <td>A medium-sized green cube. It is entirely gene...</td>\n      <td>notaweapon</td>\n      <td>0</td>\n      <td>none</td>\n      <td>average</td>\n      <td>Build_Grist:2|</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>OQXZ7nNN</td>\n      <td>Shitty sword</td>\n      <td>A shitty sword.</td>\n      <td>bladekind</td>\n      <td>2</td>\n      <td>none</td>\n      <td>average</td>\n      <td>Build_Grist:3|</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>WN6?TpMT</td>\n      <td>Picture of Hella Jeff</td>\n      <td>It's a picture of one of the characters from S...</td>\n      <td>notaweapon</td>\n      <td>0</td>\n      <td>none</td>\n      <td>average</td>\n      <td>Artifact:-1|</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>MPd81h!6</td>\n      <td>Fountain Pen</td>\n      <td>They say the pen is mightier than the 0I0Y5nML...</td>\n      <td>penkind</td>\n      <td>1</td>\n      <td>none</td>\n      <td>small</td>\n      <td>Build_Grist:1|</td>\n      <td>1</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>13182</th>\n      <td>2IvTerXl</td>\n      <td>Faygo-brand weighted die-yos</td>\n      <td>This heavyweight squarish double yo-yo is lite...</td>\n      <td>yoyokind</td>\n      <td>60</td>\n      <td>none</td>\n      <td>average</td>\n      <td>Blood:233|Build_Grist:778|Rust:389|Sandstone:2...</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>13183</th>\n      <td>9Qcmy4CH</td>\n      <td>Glassy Rain Protector</td>\n      <td>It's umbrela, but made with glass? Anyway it i...</td>\n      <td>mirrorkind, umbrellakind</td>\n      <td>11</td>\n      <td>none</td>\n      <td>average</td>\n      <td>Build_Grist:63|Jet:13|Shale:19|</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>13184</th>\n      <td>!1n1q0xX</td>\n      <td>HourSickle</td>\n      <td>I don't think this would work as a clock hand ...</td>\n      <td>keykind, sicklekind</td>\n      <td>90</td>\n      <td>none</td>\n      <td>average</td>\n      <td>Aquamarine:1286|Build_Grist:857|Shale:857|</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>13185</th>\n      <td>D03Gm70O</td>\n      <td>Bochucks</td>\n      <td>A bo-staff that can be turned into nunchucks w...</td>\n      <td>ninjakind, staffkind</td>\n      <td>12</td>\n      <td>none</td>\n      <td>average</td>\n      <td>Build_Grist:116|</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>13186</th>\n      <td>x1n1q0RX</td>\n      <td>Sacrificial Sickle</td>\n      <td>You and your friends are inside this thing...y...</td>\n      <td>yoyokind, sicklekind</td>\n      <td>700</td>\n      <td>none</td>\n      <td>average</td>\n      <td>Aquamarine:17060|Build_Grist:85301|Sandstone:3...</td>\n      <td>0</td>\n    </tr>\n  </tbody>\n</table>\n<p>13182 rows × 9 columns</p>\n</div>"
     },
     "execution_count": 112,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# We want to keep the columns \"code\", \"name\", \"description\", \"abstratus\", \"power\", \"wearable\", \"size\", \"gristcosts\", \"base\" from the v2 dataframe\n",
    "overseer2_df_no_cruft = overseer2_df[\n",
    "    ['code', 'name', 'description', 'abstratus', 'power', 'wearable', 'size', 'gristcosts', 'base']]\n",
    "# Remove items with invalid codes\n",
    "overseer2_df_no_cruft = overseer2_df_no_cruft[~overseer2_df_no_cruft['code'].isin(\n",
    "    ['nan', '-LRcant-', '-LRnote-', '-abCw1ki', '-N07H1N9', 'lol@test'])]\n",
    "overseer2_df_no_cruft"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "a0a0a0a0\n",
      "b3b3b3b3\n"
     ]
    }
   ],
   "source": [
    "# In order to fine-tune the model, we need to create a prompt for each item\n",
    "# We're missing one key piece of information though, the alchemy part\n",
    "# \"code\" actually contains information on the \"parenthood\" of the item\n",
    "# For example, the Claw Hammer with code nZ7Un6BI and the Pogo Ride with code DQMmJLeK combine using && to create the Pogo Hammer with code 126GH48G (nZ7Un6BI && DQMmJLeK => 126GH48G)\n",
    "# And they can also combine using the || operation to create the Hammerhead Pogo Ride with code 00080020 (nZ7Un6BI || DQMmJLeK => 00080020)\n",
    "# We'll have the new columns 'and_parent_1', 'and_parent_2', 'or_parent_1', 'or_parent_2' with the found parents\n",
    "# For now we're just going to get the list of parent codes to child codes, and then try to find them in those columns\n",
    "# Here's an example:\n",
    "# 1. First item has code b1b1b1b1, save it in variable.\n",
    "# 2. Second item has the code a2a2a2a2, get two new variables from doing (b1b1b1b1 && a2a2a2a2) and (b1b1b1b1 || a2a2a2a2)\n",
    "# and_child = a0a0a0a0, or_child = b3b3b3b3\n",
    "# 3. Make an intermediate dictionary with the key being the child and with four keys for the parents (and_parent_1, and_parent_2, or_parent_1, or_parent_2)\n",
    "# 3. Repeat with first and third item, and first and fourth item...\n",
    "# 4. End once all the combinations have been done (13187 * 13186 * 2) or so\n",
    "# 5. Now we can use the dictionary to find the parents of each item and store it in the original dataframe\n",
    "\n",
    "# Here's an adapted alchemy library from https://github.com/pyrrhicPachyderm/punchcardalchemy/blob/gh-pages/alchemy.js\n",
    "\n",
    "def code_to_holes(code):\n",
    "    code = code.ljust(8, '0')\n",
    "    code_nums = []\n",
    "    for c in code:\n",
    "        if c.isdigit():\n",
    "            code_nums.append(int(c))\n",
    "        elif c.isupper():\n",
    "            code_nums.append(ord(c) - ord('A') + 10)\n",
    "        elif c.islower():\n",
    "            code_nums.append(ord(c) - ord('a') + 36)\n",
    "        elif c == '?':\n",
    "            code_nums.append(62)\n",
    "        elif c == '!':\n",
    "            code_nums.append(63)\n",
    "    binary = []\n",
    "    for num in code_nums:\n",
    "        for j in range(5, -1, -1):\n",
    "            binary.append(bool(num & (1 << j)))\n",
    "    return binary\n",
    "\n",
    "\n",
    "def holes_to_code(binary):\n",
    "    code_nums = []\n",
    "    for i in range(8):\n",
    "        num = 0\n",
    "        for j in range(5, -1, -1):\n",
    "            if binary[i * 6 + (5 - j)]:\n",
    "                num += (1 << j)\n",
    "        code_nums.append(num)\n",
    "    code = ''\n",
    "    for c in code_nums:\n",
    "        if 0 <= c <= 9:\n",
    "            code += chr(c + ord('0'))\n",
    "        elif 10 <= c <= 35:\n",
    "            code += chr(c - 10 + ord('A'))\n",
    "        elif 36 <= c <= 61:\n",
    "            code += chr(c - 36 + ord('a'))\n",
    "        elif c == 62:\n",
    "            code += '?'\n",
    "        elif c == 63:\n",
    "            code += '!'\n",
    "    return code\n",
    "\n",
    "\n",
    "def alchemy_or(a, b):\n",
    "    return [x or y for x, y in zip(a, b)]\n",
    "\n",
    "\n",
    "def alchemy_and(a, b):\n",
    "    return [x and y for x, y in zip(a, b)]\n",
    "\n",
    "\n",
    "def get_and_result(code_1: str, code_2: str):\n",
    "    return holes_to_code(alchemy_and(code_to_holes(code_1), code_to_holes(code_2)))\n",
    "\n",
    "\n",
    "def get_or_result(code_1: str, code_2: str):\n",
    "    return holes_to_code(alchemy_or(code_to_holes(code_1), code_to_holes(code_2)))\n",
    "\n",
    "\n",
    "def is_valid_code(code: str):\n",
    "    # length has to be 8, must contain only 0-9, A-Z, a-z, ?, !\n",
    "    is_valid = True\n",
    "    if len(code) != 8:\n",
    "        is_valid = False\n",
    "    for c in code:\n",
    "        if not (c.isdigit() or c.isupper() or c.islower() or c == '?' or c == '!'):\n",
    "            is_valid = False\n",
    "    if not is_valid:\n",
    "        print(f\"{code} is not valid\")\n",
    "    return is_valid\n",
    "\n",
    "\n",
    "# test the functions\n",
    "\n",
    "print(get_and_result('b1b1b1b1', 'a2a2a2a2'))\n",
    "print(get_or_result('b1b1b1b1', 'a2a2a2a2'))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0 / 13182\n",
      "100 / 13182\n",
      "200 / 13182\n",
      "300 / 13182\n",
      "400 / 13182\n",
      "500 / 13182\n",
      "600 / 13182\n",
      "700 / 13182\n",
      "800 / 13182\n",
      "900 / 13182\n",
      "1000 / 13182\n",
      "1100 / 13182\n",
      "1200 / 13182\n",
      "1300 / 13182\n",
      "1400 / 13182\n",
      "1500 / 13182\n",
      "1600 / 13182\n",
      "1700 / 13182\n",
      "1800 / 13182\n",
      "1900 / 13182\n",
      "2000 / 13182\n",
      "2100 / 13182\n",
      "2200 / 13182\n",
      "2300 / 13182\n",
      "2400 / 13182\n",
      "2500 / 13182\n",
      "2600 / 13182\n",
      "2700 / 13182\n",
      "2800 / 13182\n",
      "2900 / 13182\n",
      "3000 / 13182\n",
      "3100 / 13182\n",
      "3200 / 13182\n",
      "3300 / 13182\n",
      "3400 / 13182\n",
      "3500 / 13182\n",
      "3600 / 13182\n",
      "3700 / 13182\n",
      "3800 / 13182\n",
      "3900 / 13182\n",
      "4000 / 13182\n",
      "4100 / 13182\n",
      "4200 / 13182\n",
      "4300 / 13182\n",
      "4400 / 13182\n",
      "4500 / 13182\n",
      "4600 / 13182\n",
      "4700 / 13182\n",
      "4800 / 13182\n",
      "4900 / 13182\n",
      "5000 / 13182\n",
      "5100 / 13182\n",
      "5200 / 13182\n",
      "5300 / 13182\n",
      "5400 / 13182\n",
      "5500 / 13182\n",
      "5600 / 13182\n",
      "5700 / 13182\n",
      "5800 / 13182\n",
      "5900 / 13182\n",
      "6000 / 13182\n",
      "6100 / 13182\n",
      "6200 / 13182\n",
      "6300 / 13182\n",
      "6400 / 13182\n",
      "6500 / 13182\n",
      "6600 / 13182\n",
      "6700 / 13182\n",
      "6800 / 13182\n",
      "6900 / 13182\n",
      "7000 / 13182\n",
      "7100 / 13182\n",
      "7200 / 13182\n",
      "7300 / 13182\n",
      "7400 / 13182\n",
      "7500 / 13182\n",
      "7600 / 13182\n",
      "7700 / 13182\n",
      "7800 / 13182\n",
      "7900 / 13182\n",
      "8000 / 13182\n",
      "8100 / 13182\n",
      "8200 / 13182\n",
      "8300 / 13182\n",
      "8400 / 13182\n",
      "8500 / 13182\n",
      "8600 / 13182\n",
      "8700 / 13182\n",
      "8800 / 13182\n",
      "8900 / 13182\n",
      "9000 / 13182\n",
      "9100 / 13182\n",
      "9200 / 13182\n",
      "9300 / 13182\n",
      "9400 / 13182\n",
      "9500 / 13182\n",
      "9600 / 13182\n",
      "9700 / 13182\n",
      "9800 / 13182\n",
      "9900 / 13182\n",
      "10000 / 13182\n",
      "10100 / 13182\n",
      "10200 / 13182\n",
      "10300 / 13182\n",
      "10400 / 13182\n",
      "10500 / 13182\n",
      "10600 / 13182\n",
      "10700 / 13182\n",
      "10800 / 13182\n",
      "10900 / 13182\n",
      "11000 / 13182\n",
      "11100 / 13182\n",
      "11200 / 13182\n",
      "11300 / 13182\n",
      "11400 / 13182\n",
      "11500 / 13182\n",
      "11600 / 13182\n",
      "11700 / 13182\n",
      "11800 / 13182\n",
      "11900 / 13182\n",
      "12000 / 13182\n",
      "12100 / 13182\n",
      "12200 / 13182\n",
      "12300 / 13182\n",
      "12400 / 13182\n",
      "12500 / 13182\n",
      "12600 / 13182\n",
      "12700 / 13182\n",
      "12800 / 13182\n",
      "12900 / 13182\n",
      "13000 / 13182\n",
      "13100 / 13182\n"
     ]
    }
   ],
   "source": [
    "# Now we're going to create the dictionary\n",
    "import os, json\n",
    "\n",
    "alchemy_dict = {}\n",
    "\n",
    "overseer2_df_no_cruft = overseer2_df_no_cruft.reset_index(drop=True)\n",
    "# coerce str\n",
    "overseer2_df_no_cruft['code'] = overseer2_df_no_cruft['code'].astype(str)\n",
    "\n",
    "# We'll try to use .apply() to speed up the process\n",
    "# storing the results in the four columns and_parent_1, and_parent_2, or_parent_1, or_parent_2\n",
    "\n",
    "invalid_item_codes = []\n",
    "\n",
    "dataframe_to_process = overseer2_df_no_cruft.copy()\n",
    "rows = list(dataframe_to_process.iterrows())\n",
    "# We're only interested in saving codes that exist in the original dataframe\n",
    "valid_codes = dataframe_to_process['code'].unique()\n",
    "\n",
    "for code in valid_codes:\n",
    "    alchemy_dict[code] = {\n",
    "        '&1': [],\n",
    "        '&2': [],\n",
    "        '|1': [],\n",
    "        '|2': []\n",
    "    }\n",
    "\n",
    "# get the length of itertools.combinations(rows, 2)\n",
    "total_count = int(len(rows) * (len(rows) - 1) / 2)\n",
    "\n",
    "if not os.path.exists('output/alchemy_dict.json'):\n",
    "    for first_item_index, first_item in dataframe_to_process.iterrows():\n",
    "        # print progress\n",
    "        if first_item_index % 100 == 0:\n",
    "            print(f\"{first_item_index} / {len(rows)}\")\n",
    "        first_item_code = first_item.code\n",
    "        for second_item_index, second_item in dataframe_to_process.iloc[:first_item_index].iterrows():\n",
    "            second_item_code = second_item.code\n",
    "            and_result = get_and_result(first_item_code, second_item_code)\n",
    "            or_result = get_or_result(first_item_code, second_item_code)\n",
    "            if and_result in valid_codes and and_result != first_item_code and and_result != second_item_code:\n",
    "                alchemy_dict[and_result]['&1'].append(first_item_code)\n",
    "                alchemy_dict[and_result]['&2'].append(second_item_code)\n",
    "            if or_result in valid_codes and or_result != first_item_code and or_result != second_item_code:\n",
    "                alchemy_dict[or_result]['|1'].append(first_item_code)\n",
    "                alchemy_dict[or_result]['|2'].append(second_item_code)\n",
    "else:\n",
    "    print(\"Loading alchemy_dict from file\")\n",
    "    with open('output/alchemy_dict.json', 'r') as f:\n",
    "        alchemy_dict = json.load(f)"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "outputs": [],
   "source": [
    "# That is a very very expensive calculation, so let's save that dictionary to a file and skip it next time\n",
    "import json\n",
    "\n",
    "with open('output/alchemy_dict.json', 'w') as f:\n",
    "    json.dump(alchemy_dict, f)"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Loading simplified alchemy_dict from file\n"
     ]
    }
   ],
   "source": [
    "# We have not really saved the json in the most efficient way\n",
    "# We will reopen it and wipe out objects that have empty lists, and do some other optimizations\n",
    "# 1. Check every value for duplicates (i.e. &1[0] == &2[0], or &1[0] == &2[1]. if so, remove both values from their lists)\n",
    "# 2. Get a list of all keys with &1, &2, |1, |2 equaling empty lists\n",
    "\n",
    "# Check if the simplified dictionary exists\n",
    "if os.path.exists('output/alchemy_dict_simplified.json'):\n",
    "    print(\"Loading simplified alchemy_dict from file\")\n",
    "    with open('output/alchemy_dict_simplified.json', 'r') as f:\n",
    "        loaded_alchemy_dict = json.load(f)\n",
    "else:\n",
    "    print(\"Simplifying alchemy_dict\")\n",
    "    loaded_alchemy_dict = alchemy_dict.copy()\n",
    "    for code, parents in alchemy_dict.items():\n",
    "        # remove pairs that give us no useful information\n",
    "        # i.e. complex codes leading back to themselves\n",
    "        and1 = parents['&1']\n",
    "        and2 = parents['&2']\n",
    "        or1 = parents['|1']\n",
    "        or2 = parents['|2']\n",
    "        for i in range(len(and1)):\n",
    "            if and1[i] == and2[i] or code in [and1[i], and2[i]]:\n",
    "                # remove the pair from both lists\n",
    "                loaded_alchemy_dict[code]['&1'].remove(and1[i])\n",
    "                loaded_alchemy_dict[code]['&2'].remove(and2[i])\n",
    "        for i in range(len(or1)):\n",
    "            if or1[i] == or2[i] or code in [or1[i], or2[i]]:\n",
    "                # remove the pair from both lists\n",
    "                loaded_alchemy_dict[code]['|1'].remove(or1[i])\n",
    "                loaded_alchemy_dict[code]['|2'].remove(or2[i])\n",
    "    # remove keys with empty lists\n",
    "    keys_to_del = set()\n",
    "    for code, parents in loaded_alchemy_dict.items():\n",
    "        if len(parents['&1']) == 0 and len(parents['&2']) == 0 and len(parents['|1']) == 0 and len(parents['|2']) == 0:\n",
    "            keys_to_del.add(code)\n",
    "    for key in keys_to_del:\n",
    "        del loaded_alchemy_dict[key]\n",
    "    # save the simplified dictionary\n",
    "    with open('output/alchemy_dict_simplified.json', 'w') as f:\n",
    "        json.dump(loaded_alchemy_dict, f)"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "# Now loaded_alchemy_dict contains the parent-child relationships\n",
    "# For example:\n",
    "# \"P!2LkeMs\": {\"&1\": [\"V!2TkwUt\"], \"&2\": [\"v!2LleNs\"], \"|1\": [], \"|2\": []}\n",
    "\n",
    "analyzable_dict = loaded_alchemy_dict.copy()\n",
    "# this is going to save the two parents and child in three element tuples\n",
    "promiscuous_codes = []\n",
    "excludable_codes = []\n",
    "for code, parents in analyzable_dict.items():\n",
    "    for and1, and2 in zip(parents['&1'], parents['&2']):\n",
    "        promiscuous_codes.append((and1, and2, code))\n",
    "    for or1, or2 in zip(parents['|1'], parents['|2']):\n",
    "        excludable_codes.append((or1, or2, code))\n",
    "\n",
    "# sort by the third element, so we can see the codes in order\n",
    "promiscuous_codes.sort(key=lambda x: x[2])\n",
    "excludable_codes.sort(key=lambda x: x[2])\n",
    "\n",
    "# now let's use the original datafarme to display the actual names of the codes\n",
    "# remember the data is available at overseer2_df_no_cruft\n",
    "with open('output/promiscuous_codes.json', 'w') as f:\n",
    "    for code1, code2, code3 in promiscuous_codes:\n",
    "        print(\n",
    "            f\"{overseer2_df_no_cruft[overseer2_df_no_cruft['code'] == code1]['name'].values[0]} && {overseer2_df_no_cruft[overseer2_df_no_cruft['code'] == code2]['name'].values[0]} = {overseer2_df_no_cruft[overseer2_df_no_cruft['code'] == code3]['name'].values[0]}\")\n",
    "        # dump in file, one per line\n",
    "        f.write(\n",
    "            f\"{overseer2_df_no_cruft[overseer2_df_no_cruft['code'] == code1]['name'].values[0]} && {overseer2_df_no_cruft[overseer2_df_no_cruft['code'] == code2]['name'].values[0]} = {overseer2_df_no_cruft[overseer2_df_no_cruft['code'] == code3]['name'].values[0]}\\n\")\n",
    "\n",
    "with open('output/excludable_codes.json', 'w') as f:\n",
    "    for code1, code2, code3 in excludable_codes:\n",
    "        print(\n",
    "            f\"{overseer2_df_no_cruft[overseer2_df_no_cruft['code'] == code1]['name'].values[0]} || {overseer2_df_no_cruft[overseer2_df_no_cruft['code'] == code2]['name'].values[0]} = {overseer2_df_no_cruft[overseer2_df_no_cruft['code'] == code3]['name'].values[0]}\")\n",
    "        # dump in file, one per line\n",
    "        f.write(\n",
    "            f\"{overseer2_df_no_cruft[overseer2_df_no_cruft['code'] == code1]['name'].values[0]} || {overseer2_df_no_cruft[overseer2_df_no_cruft['code'] == code2]['name'].values[0]} = {overseer2_df_no_cruft[overseer2_df_no_cruft['code'] == code3]['name'].values[0]}\\n\")\n",
    "\n",
    "\n",
    "\n"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 214,
   "outputs": [],
   "source": [
    "import random\n",
    "\n",
    "\n",
    "# At this point we have all the data we need to make the dataset\n",
    "# Input: the parents and their descriptions in JSON format, plus the operation\n",
    "# Output: a JSON of the resulting object\n",
    "# example:\n",
    "\n",
    "def create_training_line_from_tuple(prompt: str, completion: str):\n",
    "    return {\n",
    "        \"prompt\": prompt + \"###\",\n",
    "        \"completion\": \" \" + completion + \" END\"\n",
    "    }\n",
    "\n",
    "def normalize_string(item_string: str):\n",
    "    # 1. remove \\s\n",
    "    item_string = item_string.replace(\"\\\\\", \"\")\n",
    "    # 2. remove double spaces\n",
    "    item_string = item_string.replace(\"  \", \" \")\n",
    "    # 3. remove trailing spaces\n",
    "    item_string = item_string.strip()\n",
    "    # 4. if there is a period at the end, remove it\n",
    "    if len(item_string) > 0:\n",
    "        if item_string[-1] == \".\":\n",
    "            item_string = item_string[:-1]\n",
    "    return item_string\n",
    "\n",
    "def create_prompt_and_completion_from_triad(parent1, parent2, child, operation: str):\n",
    "    # We're going to try a single JSON line format, removing the \\s from the original name\n",
    "    first_parent_name = normalize_string(parent1['name'])\n",
    "    second_parent_name = normalize_string(parent2['name'])\n",
    "    child_name = normalize_string(child['name'])\n",
    "    child_description = normalize_string(child['description'])\n",
    "    # check for nans\n",
    "    if \"nan\" in [first_parent_name, second_parent_name, child_name,\n",
    "                 child_description]:\n",
    "        return None\n",
    "    # now we need to create the prompt and completion\n",
    "    prompt_line = f\"{first_parent_name} {operation} {second_parent_name}\"\n",
    "    completion_line = f\"{child_name} ({child_description})\"\n",
    "    return create_training_line_from_tuple(prompt_line, completion_line)\n",
    "\n",
    "\n",
    "# get the data from overseer2_df_no_cruft\n",
    "def get_data_from_dataframe(p1_code: str, p2_code: str, c_code: str, operation):\n",
    "    # get the first element that matches the code if it exists\n",
    "    parent1_elements = overseer2_df_no_cruft[overseer2_df_no_cruft['code'] == p1_code]\n",
    "    if len(parent1_elements) == 0:\n",
    "        print(f\"ERROR: Couldn't find code {p1_code}\")\n",
    "        return None\n",
    "    parent1 = parent1_elements.iloc[0]\n",
    "    parent2_elements = overseer2_df_no_cruft[overseer2_df_no_cruft['code'] == p2_code]\n",
    "    if len(parent2_elements) == 0:\n",
    "        print(f\"ERROR: Couldn't find code {p2_code}\")\n",
    "        return None\n",
    "    parent2 = parent2_elements.iloc[0]\n",
    "    child_elements = overseer2_df_no_cruft[overseer2_df_no_cruft['code'] == c_code]\n",
    "    if len(child_elements) == 0:\n",
    "        print(f\"ERROR: Couldn't find code {c_code}\")\n",
    "        return None\n",
    "    child = child_elements.iloc[0]\n",
    "    return create_prompt_and_completion_from_triad(parent1, parent2, child, operation)\n",
    "\n",
    "\n",
    "# ensure overseer2_df_no_cruft names and descriptions are strings\n",
    "overseer2_df_no_cruft['name'] = overseer2_df_no_cruft['name'].astype(str)\n",
    "overseer2_df_no_cruft['description'] = overseer2_df_no_cruft['description'].astype(str)\n",
    "\n",
    "# now go through the lists\n",
    "training_lines = []\n",
    "for parent1_code, parent2_code, child_code in promiscuous_codes:\n",
    "    data = get_data_from_dataframe(parent1_code, parent2_code, child_code, '&&')\n",
    "    if data is not None:\n",
    "        training_lines.append(data)\n",
    "\n",
    "for parent1_code, parent2_code, child_code in excludable_codes:\n",
    "    data = get_data_from_dataframe(parent1_code, parent2_code, child_code, '||')\n",
    "    if data is not None:\n",
    "        training_lines.append(data)\n",
    "\n",
    "# shuffle the training lines\n",
    "training_lines_shuffled = random.sample(training_lines, len(training_lines))\n",
    "\n",
    "# write the training lines to a .jsonl file, making sure each line is a single JSON object\n",
    "with open('output/finetuned_overseer_alchemy.jsonl', 'w') as f:\n",
    "    for line in training_lines_shuffled:\n",
    "        f.write(json.dumps(line) + '\\n')\n"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "#\n",
    "# FINETUNING GPT\n",
    "#"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Better to launch the commands in console, jupyter isn't that good at handling the output\n",
    "# ! env OPENAI_API_KEY=\"sk-something\" openai api fine_tunes.create -t finetuned_overseer_alchemy.jsonl -m davinci --suffix \"alchemy-simple\"\n",
    "# ! OPENAI_API_KEY=\"sk-something\" openai api fine_tunes.follow -i ft-modelname\n",
    "# With davinci and 7285 finetuning elements, it took 7 minutes to tell me it would cost 27 bucks and enter the queue (be warned that with ada it just hung forever in the queue,\n",
    "# I think openAI prioritizes big bucks), 35 minutes to start training, and 23 minutes per epoch out of 4 epochs, so well over one hour to train."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 222,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#\n",
    "# GENERATING RESPONSES\n",
    "#\n",
    "\n",
    "import openai\n",
    "\n",
    "openai.api_key = 'sk-something'\n",
    "\n",
    "MODEL_NAME = \"davinci:something\"\n",
    "\n",
    "\n",
    "def pretty_print(q, a):\n",
    "    print(f\"Alchemize: {q}\\nResult: {a}\")\n",
    "\n",
    "\n",
    "def text_to_prompt(text: str):\n",
    "    return text + \"###\"\n",
    "\n",
    "\n",
    "def query_openai_model(prompt: str):\n",
    "    response = openai.Completion.create(\n",
    "        model=MODEL_NAME,\n",
    "        prompt=prompt,\n",
    "        max_tokens=100,\n",
    "        stop=[\" END\"]\n",
    "    )\n",
    "    text = response.choices[0].text\n",
    "    # remove empty space at the beginning\n",
    "    processed_text = text[1:]\n",
    "    return processed_text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 238,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Alchemize: Problem Sleuth game && Hammer\n",
      "Result: Hammersleuth (The case has now become a weapon. It also has some useful functions for solving cases)\n"
     ]
    }
   ],
   "source": [
    "def get_text(item_1_name: str, item_2_name: str, op: str):\n",
    "    return f\"{item_1_name} {op} {item_2_name}\"\n",
    "\n",
    "def get_alchemy_result(item_1_name: str, item_2_name: str, op: str):\n",
    "    promptable_text = get_text(item_1_name, item_2_name, op)\n",
    "    return query_openai_model(text_to_prompt(promptable_text))\n",
    "\n",
    "item_1 = 'Problem Sleuth game'\n",
    "item_2 = 'Hammer'\n",
    "operation = '&&'\n",
    "\n",
    "text = get_text(item_1, item_2, operation)\n",
    "\n",
    "answer = query_openai_model(text_to_prompt(text))\n",
    "\n",
    "pretty_print(text, answer)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}