{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import gc\n",
    "sns.set_style(\"darkgrid\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "outliers = pd.read_parquet('../data/outliers.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_trades = pd.read_parquet('../data/all_trades_profitability.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_trades[\"creation_date\"] = all_trades[\"creation_timestamp\"].dt.date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/gp/02mb1d514ng739czlxw1lhh00000gn/T/ipykernel_40712/1825242321.py:6: UserWarning: Converting to PeriodArray/Index representation will drop timezone information.\n",
      "  all_trades[\"creation_timestamp\"].dt.to_period(\"W\").dt.strftime(\"%b-%d\")\n"
     ]
    }
   ],
   "source": [
    "all_trades = all_trades.sort_values(\n",
    "    by=\"creation_timestamp\", ascending=True\n",
    ")\n",
    "\n",
    "all_trades[\"month_year_week\"] = (\n",
    "    all_trades[\"creation_timestamp\"].dt.to_period(\"W\").dt.strftime(\"%b-%d\")\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 2 entries, 24957 to 9513\n",
      "Data columns (total 23 columns):\n",
      " #   Column                  Non-Null Count  Dtype              \n",
      "---  ------                  --------------  -----              \n",
      " 0   trader_address          2 non-null      object             \n",
      " 1   market_creator          2 non-null      object             \n",
      " 2   trade_id                2 non-null      object             \n",
      " 3   creation_timestamp      2 non-null      datetime64[us, UTC]\n",
      " 4   title                   2 non-null      object             \n",
      " 5   market_status           2 non-null      object             \n",
      " 6   collateral_amount       2 non-null      float64            \n",
      " 7   outcome_index           2 non-null      object             \n",
      " 8   trade_fee_amount        2 non-null      float64            \n",
      " 9   outcomes_tokens_traded  2 non-null      float64            \n",
      " 10  current_answer          2 non-null      int64              \n",
      " 11  is_invalid              2 non-null      bool               \n",
      " 12  winning_trade           2 non-null      int64              \n",
      " 13  earnings                2 non-null      float64            \n",
      " 14  redeemed                2 non-null      bool               \n",
      " 15  redeemed_amount         2 non-null      float64            \n",
      " 16  num_mech_calls          2 non-null      int64              \n",
      " 17  mech_fee_amount         2 non-null      float64            \n",
      " 18  net_earnings            2 non-null      float64            \n",
      " 19  roi                     2 non-null      float64            \n",
      " 20  staking                 2 non-null      object             \n",
      " 21  month_year              2 non-null      object             \n",
      " 22  month_year_week         2 non-null      object             \n",
      "dtypes: bool(2), datetime64[us, UTC](1), float64(8), int64(3), object(9)\n",
      "memory usage: 356.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "outliers.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>trader_address</th>\n",
       "      <th>market_creator</th>\n",
       "      <th>trade_id</th>\n",
       "      <th>creation_timestamp</th>\n",
       "      <th>title</th>\n",
       "      <th>market_status</th>\n",
       "      <th>collateral_amount</th>\n",
       "      <th>outcome_index</th>\n",
       "      <th>trade_fee_amount</th>\n",
       "      <th>outcomes_tokens_traded</th>\n",
       "      <th>...</th>\n",
       "      <th>earnings</th>\n",
       "      <th>redeemed</th>\n",
       "      <th>redeemed_amount</th>\n",
       "      <th>num_mech_calls</th>\n",
       "      <th>mech_fee_amount</th>\n",
       "      <th>net_earnings</th>\n",
       "      <th>roi</th>\n",
       "      <th>staking</th>\n",
       "      <th>month_year</th>\n",
       "      <th>month_year_week</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>24957</th>\n",
       "      <td>0x3666da333dadd05083fef9ff6ddee588d26e4307</td>\n",
       "      <td>quickstart</td>\n",
       "      <td>0x11cf6ec9649097127238ffb789b0703da448d9fa0x36...</td>\n",
       "      <td>2024-09-15 02:02:05+00:00</td>\n",
       "      <td>Will Apple launch the iPhone 16 by 15 Septembe...</td>\n",
       "      <td>CLOSED</td>\n",
       "      <td>1.000000e-05</td>\n",
       "      <td>0</td>\n",
       "      <td>2.000000e-07</td>\n",
       "      <td>0.020738</td>\n",
       "      <td>...</td>\n",
       "      <td>0.020738</td>\n",
       "      <td>True</td>\n",
       "      <td>0.020738</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.020727</td>\n",
       "      <td>2.032090e+03</td>\n",
       "      <td>non_agent</td>\n",
       "      <td>2024-09</td>\n",
       "      <td>Sep-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9513</th>\n",
       "      <td>0xf21c4230f137ffcee12e69786d854e62a7b4b0ae</td>\n",
       "      <td>pearl</td>\n",
       "      <td>0xa51ffc63bc0afd06e17130ff2e0ebedf0491b1730xf2...</td>\n",
       "      <td>2024-10-08 15:20:55+00:00</td>\n",
       "      <td>Will Donald Trump visit the city of Valdosta, ...</td>\n",
       "      <td>CLOSED</td>\n",
       "      <td>2.080980e-07</td>\n",
       "      <td>0</td>\n",
       "      <td>4.161961e-09</td>\n",
       "      <td>1.368652</td>\n",
       "      <td>...</td>\n",
       "      <td>1.368652</td>\n",
       "      <td>True</td>\n",
       "      <td>1.368652</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.368652</td>\n",
       "      <td>6.447997e+06</td>\n",
       "      <td>non_agent</td>\n",
       "      <td>2024-10</td>\n",
       "      <td>Oct-13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   trader_address market_creator  \\\n",
       "24957  0x3666da333dadd05083fef9ff6ddee588d26e4307     quickstart   \n",
       "9513   0xf21c4230f137ffcee12e69786d854e62a7b4b0ae          pearl   \n",
       "\n",
       "                                                trade_id  \\\n",
       "24957  0x11cf6ec9649097127238ffb789b0703da448d9fa0x36...   \n",
       "9513   0xa51ffc63bc0afd06e17130ff2e0ebedf0491b1730xf2...   \n",
       "\n",
       "             creation_timestamp  \\\n",
       "24957 2024-09-15 02:02:05+00:00   \n",
       "9513  2024-10-08 15:20:55+00:00   \n",
       "\n",
       "                                                   title market_status  \\\n",
       "24957  Will Apple launch the iPhone 16 by 15 Septembe...        CLOSED   \n",
       "9513   Will Donald Trump visit the city of Valdosta, ...        CLOSED   \n",
       "\n",
       "       collateral_amount outcome_index  trade_fee_amount  \\\n",
       "24957       1.000000e-05             0      2.000000e-07   \n",
       "9513        2.080980e-07             0      4.161961e-09   \n",
       "\n",
       "       outcomes_tokens_traded  ...  earnings  redeemed  redeemed_amount  \\\n",
       "24957                0.020738  ...  0.020738      True         0.020738   \n",
       "9513                 1.368652  ...  1.368652      True         1.368652   \n",
       "\n",
       "       num_mech_calls  mech_fee_amount  net_earnings           roi    staking  \\\n",
       "24957               0              0.0      0.020727  2.032090e+03  non_agent   \n",
       "9513                0              0.0      1.368652  6.447997e+06  non_agent   \n",
       "\n",
       "       month_year  month_year_week  \n",
       "24957     2024-09           Sep-15  \n",
       "9513      2024-10           Oct-13  \n",
       "\n",
       "[2 rows x 23 columns]"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "outliers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "24957    0x3666da333dadd05083fef9ff6ddee588d26e4307\n",
       "9513     0xf21c4230f137ffcee12e69786d854e62a7b4b0ae\n",
       "Name: trader_address, dtype: object"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "outliers.trader_address"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "tools = pd.read_parquet('../data/tools.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 331196 entries, 0 to 331195\n",
      "Data columns (total 23 columns):\n",
      " #   Column                   Non-Null Count   Dtype              \n",
      "---  ------                   --------------   -----              \n",
      " 0   request_id               331196 non-null  object             \n",
      " 1   request_block            331196 non-null  object             \n",
      " 2   prompt_request           331196 non-null  object             \n",
      " 3   tool                     331196 non-null  object             \n",
      " 4   nonce                    331196 non-null  object             \n",
      " 5   trader_address           331196 non-null  object             \n",
      " 6   deliver_block            331196 non-null  object             \n",
      " 7   error                    331196 non-null  int64              \n",
      " 8   error_message            3352 non-null    object             \n",
      " 9   prompt_response          330370 non-null  object             \n",
      " 10  mech_address             330462 non-null  object             \n",
      " 11  p_yes                    327844 non-null  float64            \n",
      " 12  p_no                     327844 non-null  float64            \n",
      " 13  confidence               327844 non-null  float64            \n",
      " 14  info_utility             327844 non-null  float64            \n",
      " 15  vote                     239273 non-null  object             \n",
      " 16  win_probability          327844 non-null  float64            \n",
      " 17  market_creator           331196 non-null  object             \n",
      " 18  title                    331196 non-null  object             \n",
      " 19  currentAnswer            261062 non-null  object             \n",
      " 20  request_time             331196 non-null  datetime64[ns, UTC]\n",
      " 21  request_month_year       331196 non-null  object             \n",
      " 22  request_month_year_week  331196 non-null  object             \n",
      "dtypes: datetime64[ns, UTC](1), float64(5), int64(1), object(16)\n",
      "memory usage: 58.1+ MB\n"
     ]
    }
   ],
   "source": [
    "tools.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['request_id', 'request_block', 'prompt_request', 'tool', 'nonce',\n",
       "       'trader_address', 'deliver_block', 'error', 'error_message',\n",
       "       'prompt_response', 'mech_address', 'p_yes', 'p_no', 'confidence',\n",
       "       'info_utility', 'vote', 'win_probability', 'market_creator', 'title',\n",
       "       'currentAnswer', 'request_time', 'request_month_year',\n",
       "       'request_month_year_week'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tools.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [],
   "source": [
    "trader = \"0x87f0fcfe810502555f8d1439793155cbfa2eb583\"\n",
    "selected_week = \"Nov-03\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [],
   "source": [
    "trader_data = all_trades.loc[(all_trades[\"trader_address\"]==trader)&(all_trades[\"month_year_week\"]==selected_week)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [],
   "source": [
    "trader_data_selected = trader_data.loc[trader_data[\"num_mech_calls\"]>200]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "title\n",
       "Will the U.S. Congress hold a hearing to discuss the security threats faced by former U.S. Presidents before November 1, 2024?    64\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trader_data_selected.title.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "creation_date\n",
       "2024-10-29    32\n",
       "2024-10-30    29\n",
       "2024-10-28     3\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trader_data_selected.creation_date.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "trader_address                   0x87f0fcfe810502555f8d1439793155cbfa2eb583\n",
       "market_creator                                                        pearl\n",
       "trade_id                  0xf58e542f0fa539fa332605a4de8d9affcc24bf0e0x87...\n",
       "creation_timestamp                                2024-10-28 21:16:00+00:00\n",
       "title                     Will the U.S. Congress hold a hearing to discu...\n",
       "market_status                                                        CLOSED\n",
       "collateral_amount                                                     0.025\n",
       "outcome_index                                                             0\n",
       "trade_fee_amount                                                    0.00025\n",
       "outcomes_tokens_traded                                             0.048743\n",
       "current_answer                                                            1\n",
       "is_invalid                                                            False\n",
       "winning_trade                                                         False\n",
       "earnings                                                                0.0\n",
       "redeemed                                                               True\n",
       "redeemed_amount                                                         0.0\n",
       "num_mech_calls                                                          206\n",
       "mech_fee_amount                                                        2.06\n",
       "net_earnings                                                       -2.08525\n",
       "roi                                                                    -1.0\n",
       "staking                                                               pearl\n",
       "creation_date                                                    2024-10-28\n",
       "month_year_week                                                      Nov-03\n",
       "Name: 26553, dtype: object"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trader_data_selected.iloc[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "trader_address                   0x87f0fcfe810502555f8d1439793155cbfa2eb583\n",
       "market_creator                                                        pearl\n",
       "trade_id                  0xf58e542f0fa539fa332605a4de8d9affcc24bf0e0x87...\n",
       "creation_timestamp                                2024-10-28 21:55:25+00:00\n",
       "title                     Will the U.S. Congress hold a hearing to discu...\n",
       "market_status                                                        CLOSED\n",
       "collateral_amount                                                     0.025\n",
       "outcome_index                                                             0\n",
       "trade_fee_amount                                                    0.00025\n",
       "outcomes_tokens_traded                                             0.048771\n",
       "current_answer                                                            1\n",
       "is_invalid                                                            False\n",
       "winning_trade                                                         False\n",
       "earnings                                                                0.0\n",
       "redeemed                                                               True\n",
       "redeemed_amount                                                         0.0\n",
       "num_mech_calls                                                          206\n",
       "mech_fee_amount                                                        2.06\n",
       "net_earnings                                                       -2.08525\n",
       "roi                                                                    -1.0\n",
       "staking                                                               pearl\n",
       "creation_date                                                    2024-10-28\n",
       "month_year_week                                                      Nov-03\n",
       "Name: 26583, dtype: object"
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trader_data_selected.iloc[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [],
   "source": [
    "tools_trader_data = tools.loc[tools[\"trader_address\"]==trader]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "request_month_year_week\n",
       "2024-10-28/2024-11-03    417\n",
       "2024-10-14/2024-10-20    181\n",
       "2024-10-21/2024-10-27    135\n",
       "2024-09-23/2024-09-29    125\n",
       "2024-10-07/2024-10-13    106\n",
       "2024-09-30/2024-10-06     88\n",
       "2024-09-16/2024-09-22     83\n",
       "2024-11-04/2024-11-10     58\n",
       "2024-11-11/2024-11-17     41\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tools_trader_data.request_month_year_week.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [],
   "source": [
    "title =\"Will the U.S. Congress hold a hearing to discuss the security threats faced by former U.S. Presidents before November 1, 2024?\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [],
   "source": [
    "selected_week_data = tools_trader_data.loc[(tools_trader_data[\"request_month_year_week\"]==\"2024-10-28/2024-11-03\") & (tools_trader_data[\"title\"]==title)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>request_id</th>\n",
       "      <th>request_block</th>\n",
       "      <th>prompt_request</th>\n",
       "      <th>tool</th>\n",
       "      <th>nonce</th>\n",
       "      <th>trader_address</th>\n",
       "      <th>deliver_block</th>\n",
       "      <th>error</th>\n",
       "      <th>error_message</th>\n",
       "      <th>prompt_response</th>\n",
       "      <th>...</th>\n",
       "      <th>confidence</th>\n",
       "      <th>info_utility</th>\n",
       "      <th>vote</th>\n",
       "      <th>win_probability</th>\n",
       "      <th>market_creator</th>\n",
       "      <th>title</th>\n",
       "      <th>currentAnswer</th>\n",
       "      <th>request_time</th>\n",
       "      <th>request_month_year</th>\n",
       "      <th>request_month_year_week</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1882</th>\n",
       "      <td>6070731428242628112067281008842699423624277259...</td>\n",
       "      <td>36773107</td>\n",
       "      <td>Please take over the role of a Data Scientist ...</td>\n",
       "      <td>prediction-offline-sme</td>\n",
       "      <td>5587828f-fcf6-4d3f-9cc3-27983ccc94a7</td>\n",
       "      <td>0x87f0fcfe810502555f8d1439793155cbfa2eb583</td>\n",
       "      <td>36773117</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>\\nYou are an LLM inside a multi-agent system t...</td>\n",
       "      <td>...</td>\n",
       "      <td>0.60</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Yes</td>\n",
       "      <td>0.55</td>\n",
       "      <td>pearl</td>\n",
       "      <td>Will the U.S. Congress hold a hearing to discu...</td>\n",
       "      <td>No</td>\n",
       "      <td>2024-10-30 20:12:35+00:00</td>\n",
       "      <td>2024-10</td>\n",
       "      <td>2024-10-28/2024-11-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>9425450168771161496526925826843497586130204397...</td>\n",
       "      <td>36754843</td>\n",
       "      <td>Please take over the role of a Data Scientist ...</td>\n",
       "      <td>prediction-offline-sme</td>\n",
       "      <td>030ad748-6d05-475d-99a8-4e19296a9761</td>\n",
       "      <td>0x87f0fcfe810502555f8d1439793155cbfa2eb583</td>\n",
       "      <td>36754856</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>\\nYou are an LLM inside a multi-agent system t...</td>\n",
       "      <td>...</td>\n",
       "      <td>0.60</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Yes</td>\n",
       "      <td>0.65</td>\n",
       "      <td>pearl</td>\n",
       "      <td>Will the U.S. Congress hold a hearing to discu...</td>\n",
       "      <td>No</td>\n",
       "      <td>2024-10-29 17:56:05+00:00</td>\n",
       "      <td>2024-10</td>\n",
       "      <td>2024-10-28/2024-11-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2461</th>\n",
       "      <td>2653517810834386215376709232162100681051077563...</td>\n",
       "      <td>36749585</td>\n",
       "      <td>Please take over the role of a Data Scientist ...</td>\n",
       "      <td>prediction-offline-sme</td>\n",
       "      <td>d089522e-e585-42db-8d97-6b49f3600409</td>\n",
       "      <td>0x87f0fcfe810502555f8d1439793155cbfa2eb583</td>\n",
       "      <td>36749594</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>\\nYou are an LLM inside a multi-agent system t...</td>\n",
       "      <td>...</td>\n",
       "      <td>0.60</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Yes</td>\n",
       "      <td>0.55</td>\n",
       "      <td>pearl</td>\n",
       "      <td>Will the U.S. Congress hold a hearing to discu...</td>\n",
       "      <td>No</td>\n",
       "      <td>2024-10-29 10:20:00+00:00</td>\n",
       "      <td>2024-10</td>\n",
       "      <td>2024-10-28/2024-11-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2737</th>\n",
       "      <td>1314824321782229000141984840300067466538324042...</td>\n",
       "      <td>36753259</td>\n",
       "      <td>Please take over the role of a Data Scientist ...</td>\n",
       "      <td>prediction-offline-sme</td>\n",
       "      <td>8d691113-76d5-4b65-8b18-b61a0499cd74</td>\n",
       "      <td>0x87f0fcfe810502555f8d1439793155cbfa2eb583</td>\n",
       "      <td>36753271</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>\\nYou are an LLM inside a multi-agent system t...</td>\n",
       "      <td>...</td>\n",
       "      <td>0.65</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Yes</td>\n",
       "      <td>0.55</td>\n",
       "      <td>pearl</td>\n",
       "      <td>Will the U.S. Congress hold a hearing to discu...</td>\n",
       "      <td>No</td>\n",
       "      <td>2024-10-29 15:40:05+00:00</td>\n",
       "      <td>2024-10</td>\n",
       "      <td>2024-10-28/2024-11-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2910</th>\n",
       "      <td>1889334396632607880382108537244783614667358553...</td>\n",
       "      <td>36770936</td>\n",
       "      <td>Please take over the role of a Data Scientist ...</td>\n",
       "      <td>prediction-offline-sme</td>\n",
       "      <td>fc7783d8-80d9-4658-8cdd-c7f1f37ded5f</td>\n",
       "      <td>0x87f0fcfe810502555f8d1439793155cbfa2eb583</td>\n",
       "      <td>36770953</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>\\nYou are an LLM inside a multi-agent system t...</td>\n",
       "      <td>...</td>\n",
       "      <td>0.50</td>\n",
       "      <td>0.0</td>\n",
       "      <td>No</td>\n",
       "      <td>0.60</td>\n",
       "      <td>pearl</td>\n",
       "      <td>Will the U.S. Congress hold a hearing to discu...</td>\n",
       "      <td>No</td>\n",
       "      <td>2024-10-30 17:05:35+00:00</td>\n",
       "      <td>2024-10</td>\n",
       "      <td>2024-10-28/2024-11-03</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             request_id request_block  \\\n",
       "1882  6070731428242628112067281008842699423624277259...      36773107   \n",
       "2017  9425450168771161496526925826843497586130204397...      36754843   \n",
       "2461  2653517810834386215376709232162100681051077563...      36749585   \n",
       "2737  1314824321782229000141984840300067466538324042...      36753259   \n",
       "2910  1889334396632607880382108537244783614667358553...      36770936   \n",
       "\n",
       "                                         prompt_request  \\\n",
       "1882  Please take over the role of a Data Scientist ...   \n",
       "2017  Please take over the role of a Data Scientist ...   \n",
       "2461  Please take over the role of a Data Scientist ...   \n",
       "2737  Please take over the role of a Data Scientist ...   \n",
       "2910  Please take over the role of a Data Scientist ...   \n",
       "\n",
       "                        tool                                 nonce  \\\n",
       "1882  prediction-offline-sme  5587828f-fcf6-4d3f-9cc3-27983ccc94a7   \n",
       "2017  prediction-offline-sme  030ad748-6d05-475d-99a8-4e19296a9761   \n",
       "2461  prediction-offline-sme  d089522e-e585-42db-8d97-6b49f3600409   \n",
       "2737  prediction-offline-sme  8d691113-76d5-4b65-8b18-b61a0499cd74   \n",
       "2910  prediction-offline-sme  fc7783d8-80d9-4658-8cdd-c7f1f37ded5f   \n",
       "\n",
       "                                  trader_address deliver_block  error  \\\n",
       "1882  0x87f0fcfe810502555f8d1439793155cbfa2eb583      36773117      0   \n",
       "2017  0x87f0fcfe810502555f8d1439793155cbfa2eb583      36754856      0   \n",
       "2461  0x87f0fcfe810502555f8d1439793155cbfa2eb583      36749594      0   \n",
       "2737  0x87f0fcfe810502555f8d1439793155cbfa2eb583      36753271      0   \n",
       "2910  0x87f0fcfe810502555f8d1439793155cbfa2eb583      36770953      0   \n",
       "\n",
       "     error_message                                    prompt_response  ...  \\\n",
       "1882          None  \\nYou are an LLM inside a multi-agent system t...  ...   \n",
       "2017          None  \\nYou are an LLM inside a multi-agent system t...  ...   \n",
       "2461          None  \\nYou are an LLM inside a multi-agent system t...  ...   \n",
       "2737          None  \\nYou are an LLM inside a multi-agent system t...  ...   \n",
       "2910          None  \\nYou are an LLM inside a multi-agent system t...  ...   \n",
       "\n",
       "     confidence  info_utility  vote  win_probability  market_creator  \\\n",
       "1882       0.60           0.0   Yes             0.55           pearl   \n",
       "2017       0.60           0.0   Yes             0.65           pearl   \n",
       "2461       0.60           0.0   Yes             0.55           pearl   \n",
       "2737       0.65           0.0   Yes             0.55           pearl   \n",
       "2910       0.50           0.0    No             0.60           pearl   \n",
       "\n",
       "                                                  title  currentAnswer  \\\n",
       "1882  Will the U.S. Congress hold a hearing to discu...             No   \n",
       "2017  Will the U.S. Congress hold a hearing to discu...             No   \n",
       "2461  Will the U.S. Congress hold a hearing to discu...             No   \n",
       "2737  Will the U.S. Congress hold a hearing to discu...             No   \n",
       "2910  Will the U.S. Congress hold a hearing to discu...             No   \n",
       "\n",
       "                  request_time request_month_year request_month_year_week  \n",
       "1882 2024-10-30 20:12:35+00:00            2024-10   2024-10-28/2024-11-03  \n",
       "2017 2024-10-29 17:56:05+00:00            2024-10   2024-10-28/2024-11-03  \n",
       "2461 2024-10-29 10:20:00+00:00            2024-10   2024-10-28/2024-11-03  \n",
       "2737 2024-10-29 15:40:05+00:00            2024-10   2024-10-28/2024-11-03  \n",
       "2910 2024-10-30 17:05:35+00:00            2024-10   2024-10-28/2024-11-03  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "selected_week_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Please take over the role of a Data Scientist to evaluate the given question. With the given question \"Will the U.S. Congress hold a hearing to discuss the security threats faced by former U.S. Presidents before November 1, 2024?\" and the `yes` option represented by `Yes` and the `no` option represented by `No`, what are the respective probabilities of `p_yes` and `p_no` occurring?'"
      ]
     },
     "execution_count": 117,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "selected_week_data.iloc[0].prompt_request"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Please take over the role of a Data Scientist to evaluate the given question. With the given question \"Will the U.S. Congress hold a hearing to discuss the security threats faced by former U.S. Presidents before November 1, 2024?\" and the `yes` option represented by `Yes` and the `no` option represented by `No`, what are the respective probabilities of `p_yes` and `p_no` occurring?'"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "selected_week_data.iloc[0].prompt_request"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(title in selected_week_data.iloc[0].prompt_request)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/gp/02mb1d514ng739czlxw1lhh00000gn/T/ipykernel_40712/3528210695.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  selected_week_data[\"request_date\"] = selected_week_data['request_time'].dt.date\n"
     ]
    }
   ],
   "source": [
    "tools[\"request_date\"] = tools['request_time'].dt.date\n",
    "selected_week_data[\"request_date\"] = selected_week_data['request_time'].dt.date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "request_date\n",
       "2024-10-30    108\n",
       "2024-10-29     92\n",
       "2024-10-28      6\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "selected_week_data.request_date.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "request_date\n",
       "2024-10-28      6\n",
       "2024-10-29     92\n",
       "2024-10-30    108\n",
       "Name: prompt_request, dtype: int64"
      ]
     },
     "execution_count": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "correct_number_of_mech_calls = selected_week_data.groupby('request_date')['prompt_request'].apply(lambda x: x.apply(lambda y: title in y).sum())\n",
    "correct_number_of_mech_calls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [],
   "source": [
    "num_mech_calls = (\n",
    "    selected_week_data[\"prompt_request\"]\n",
    "    .apply(lambda x: title in x)\n",
    "    .sum()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "206"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "num_mech_calls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 0 entries\n",
      "Data columns (total 23 columns):\n",
      " #   Column                   Non-Null Count  Dtype              \n",
      "---  ------                   --------------  -----              \n",
      " 0   request_id               0 non-null      object             \n",
      " 1   request_block            0 non-null      object             \n",
      " 2   prompt_request           0 non-null      object             \n",
      " 3   tool                     0 non-null      object             \n",
      " 4   nonce                    0 non-null      object             \n",
      " 5   trader_address           0 non-null      object             \n",
      " 6   deliver_block            0 non-null      object             \n",
      " 7   error                    0 non-null      int64              \n",
      " 8   error_message            0 non-null      object             \n",
      " 9   prompt_response          0 non-null      object             \n",
      " 10  mech_address             0 non-null      object             \n",
      " 11  p_yes                    0 non-null      float64            \n",
      " 12  p_no                     0 non-null      float64            \n",
      " 13  confidence               0 non-null      float64            \n",
      " 14  info_utility             0 non-null      float64            \n",
      " 15  vote                     0 non-null      object             \n",
      " 16  win_probability          0 non-null      float64            \n",
      " 17  market_creator           0 non-null      object             \n",
      " 18  title                    0 non-null      object             \n",
      " 19  currentAnswer            0 non-null      object             \n",
      " 20  request_time             0 non-null      datetime64[ns, UTC]\n",
      " 21  request_month_year       0 non-null      object             \n",
      " 22  request_month_year_week  0 non-null      object             \n",
      "dtypes: datetime64[ns, UTC](1), float64(5), int64(1), object(16)\n",
      "memory usage: 0.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "tools_trader_data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "tool\n",
       "claude-prediction-offline              73070\n",
       "prediction-request-reasoning           40602\n",
       "prediction-offline-sme                 35646\n",
       "claude-prediction-online               29455\n",
       "prediction-request-rag-claude          24377\n",
       "prediction-offline                      5765\n",
       "prediction-online                       1808\n",
       "prediction-online-sme                   1616\n",
       "prediction-request-rag                  1599\n",
       "prediction-request-reasoning-claude     1551\n",
       "prediction-url-cot-claude               1513\n",
       "superforcaster                           570\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tools.tool.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "inc_tools = [\n",
    "    \"prediction-online\",\n",
    "    \"prediction-offline\",\n",
    "    \"claude-prediction-online\",\n",
    "    \"claude-prediction-offline\",\n",
    "    \"prediction-offline-sme\",\n",
    "    \"prediction-online-sme\",\n",
    "    \"prediction-request-rag\",\n",
    "    \"prediction-request-reasoning\",\n",
    "    \"prediction-url-cot-claude\",\n",
    "    \"prediction-request-rag-claude\",\n",
    "    \"prediction-request-reasoning-claude\",\n",
    "    \"superforcaster\",\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "tools_inc = tools[tools[\"tool\"].isin(inc_tools)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "tool\n",
       "claude-prediction-offline              73070\n",
       "prediction-request-reasoning           40602\n",
       "prediction-offline-sme                 35646\n",
       "claude-prediction-online               29455\n",
       "prediction-request-rag-claude          24377\n",
       "prediction-offline                      5765\n",
       "prediction-online                       1808\n",
       "prediction-online-sme                   1616\n",
       "prediction-request-rag                  1599\n",
       "prediction-request-reasoning-claude     1551\n",
       "prediction-url-cot-claude               1513\n",
       "superforcaster                           570\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tools_inc.tool.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# filtering errors\n",
    "tools_non_error = tools_inc[tools_inc[\"error\"] != 1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "tools_superforcaster = tools_non_error.loc[tools_non_error[\"tool\"]==\"superforcaster\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "currentAnswer\n",
       "No     216\n",
       "Yes    183\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tools_superforcaster.currentAnswer.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "tools_non_error.loc[:, \"currentAnswer\"] = tools_non_error[\"currentAnswer\"].replace(\n",
    "    {\"no\": \"No\", \"yes\": \"Yes\"}\n",
    ")\n",
    "tools_non_error = tools_non_error[\n",
    "    tools_non_error[\"currentAnswer\"].isin([\"Yes\", \"No\"])\n",
    "]\n",
    "tools_non_error = tools_non_error[tools_non_error[\"vote\"].isin([\"Yes\", \"No\"])]\n",
    "tools_non_error[\"win\"] = (\n",
    "    tools_non_error[\"currentAnswer\"] == tools_non_error[\"vote\"]\n",
    ").astype(int)\n",
    "tools_non_error.columns = tools_non_error.columns.astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "wins = tools_non_error.groupby([\"tool\", \"win\"]).size().unstack().fillna(0)\n",
    "wins[\"tool_accuracy\"] = (wins[1] / (wins[0] + wins[1])) * 100\n",
    "wins.reset_index(inplace=True)\n",
    "wins[\"total_requests\"] = wins[0] + wins[1]\n",
    "wins.columns = wins.columns.astype(str)\n",
    "wins = wins[[\"tool\", \"tool_accuracy\", \"total_requests\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "timeline dataset\n",
      "                                           min                  max\n",
      "tool                                                               \n",
      "claude-prediction-offline  2024-09-05 11:53:05  2024-11-03 10:37:55\n",
      "claude-prediction-online   2024-09-05 11:10:25  2024-11-03 21:47:05\n",
      "prediction-offline         2024-09-05 10:39:05  2024-11-02 15:50:50\n",
      "prediction-offline-sme     2024-09-05 07:36:40  2024-11-03 00:11:45\n",
      "prediction-online          2024-09-05 11:43:35  2024-11-02 23:43:20\n"
     ]
    }
   ],
   "source": [
    "timeline = tools_non_error.groupby([\"tool\"])[\"request_time\"].agg([\"min\", \"max\"])\n",
    "print(\"timeline dataset\")\n",
    "print(timeline.head())\n",
    "acc_info = wins.merge(timeline, how=\"left\", on=\"tool\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>tool</th>\n",
       "      <th>tool_accuracy</th>\n",
       "      <th>total_requests</th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>prediction-request-rag-claude</td>\n",
       "      <td>60.852545</td>\n",
       "      <td>20691</td>\n",
       "      <td>2024-09-05 07:12:40</td>\n",
       "      <td>2024-11-03 00:02:55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>prediction-request-reasoning</td>\n",
       "      <td>59.186747</td>\n",
       "      <td>33200</td>\n",
       "      <td>2024-09-05 07:45:15</td>\n",
       "      <td>2024-11-03 16:59:10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>prediction-request-reasoning-claude</td>\n",
       "      <td>63.793103</td>\n",
       "      <td>1160</td>\n",
       "      <td>2024-09-05 08:52:10</td>\n",
       "      <td>2024-11-02 19:07:45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>prediction-url-cot-claude</td>\n",
       "      <td>62.389023</td>\n",
       "      <td>1239</td>\n",
       "      <td>2024-09-05 08:35:50</td>\n",
       "      <td>2024-11-02 23:46:25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>superforcaster</td>\n",
       "      <td>59.649123</td>\n",
       "      <td>399</td>\n",
       "      <td>2024-10-25 18:50:25</td>\n",
       "      <td>2024-11-02 15:56:20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   tool  tool_accuracy  total_requests  \\\n",
       "7         prediction-request-rag-claude      60.852545           20691   \n",
       "8          prediction-request-reasoning      59.186747           33200   \n",
       "9   prediction-request-reasoning-claude      63.793103            1160   \n",
       "10            prediction-url-cot-claude      62.389023            1239   \n",
       "11                       superforcaster      59.649123             399   \n",
       "\n",
       "                    min                  max  \n",
       "7   2024-09-05 07:12:40  2024-11-03 00:02:55  \n",
       "8   2024-09-05 07:45:15  2024-11-03 16:59:10  \n",
       "9   2024-09-05 08:52:10  2024-11-02 19:07:45  \n",
       "10  2024-09-05 08:35:50  2024-11-02 23:46:25  \n",
       "11  2024-10-25 18:50:25  2024-11-02 15:56:20  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "acc_info.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "acc_data = pd.read_csv(\"../data/tools_accuracy.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Updating accuracy information\n",
      "New tool superforcaster\n"
     ]
    }
   ],
   "source": [
    "print(\"Updating accuracy information\")\n",
    "tools_to_update = list(acc_info[\"tool\"].values)\n",
    "no_timeline_info = False\n",
    "existing_tools = list(acc_data[\"tool\"].values)\n",
    "for tool in tools_to_update:\n",
    "    new_accuracy = acc_info[acc_info[\"tool\"] == tool][\"tool_accuracy\"].values[0]\n",
    "    new_volume = acc_info[acc_info[\"tool\"] == tool][\"total_requests\"].values[0]\n",
    "    if no_timeline_info:\n",
    "        new_min_timeline = None\n",
    "        new_max_timeline = None\n",
    "    else:\n",
    "        new_min_timeline = acc_info[acc_info[\"tool\"] == tool][\"min\"].values[0]\n",
    "        new_max_timeline = acc_info[acc_info[\"tool\"] == tool][\"max\"].values[0]\n",
    "    if tool in existing_tools:\n",
    "        continue\n",
    "    else:\n",
    "        # new tool to add to the file\n",
    "        # tool,tool_accuracy,total_requests,min,max\n",
    "        print(f\"New tool {tool}\")\n",
    "        new_row = [{\n",
    "            \"tool\": tool,\n",
    "            \"tool_accuracy\": new_accuracy,\n",
    "            \"total_requests\": new_volume,\n",
    "            \"min\": new_min_timeline,\n",
    "            \"max\": new_max_timeline,\n",
    "        }]\n",
    "        tools_acc = pd.concat([acc_data, pd.DataFrame(new_row)], ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "tools_acc.to_csv(\"../data/tools_accuracy.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "HASH of the tools accuracy file: QmNm4BjhAebZjDsTgYhQcRHsobBy33FHzpJGQo9B3fB6jn\n"
     ]
    }
   ],
   "source": [
    "import ipfshttpclient\n",
    "\n",
    "ACCURACY_FILENAME = \"tools_accuracy.csv\"\n",
    "IPFS_SERVER = \"/dns/registry.autonolas.tech/tcp/443/https\"\n",
    "client = ipfshttpclient.connect(IPFS_SERVER)\n",
    "result = client.add(\"../data/tools_accuracy.csv\")\n",
    "print(f\"HASH of the tools accuracy file: {result['Hash']}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "tools_superforcaster = tools.loc[tools[\"tool\"]==\"superforcaster\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "trades = pd.read_parquet('../json_data/all_trades.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "old_trades = pd.read_parquet(\"../data/fpmmTrades.parquet\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_trades = pd.read_parquet(\"../data/new_fpmmTrades.parquet\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 10156 entries, 0 to 10155\n",
      "Data columns (total 24 columns):\n",
      " #   Column                         Non-Null Count  Dtype \n",
      "---  ------                         --------------  ----- \n",
      " 0   collateralAmount               10156 non-null  object\n",
      " 1   collateralAmountUSD            10156 non-null  object\n",
      " 2   collateralToken                10156 non-null  object\n",
      " 3   creationTimestamp              10156 non-null  object\n",
      " 4   trader_address                 10156 non-null  object\n",
      " 5   feeAmount                      10156 non-null  object\n",
      " 6   id                             10156 non-null  object\n",
      " 7   oldOutcomeTokenMarginalPrice   10156 non-null  object\n",
      " 8   outcomeIndex                   10156 non-null  object\n",
      " 9   outcomeTokenMarginalPrice      10156 non-null  object\n",
      " 10  outcomeTokensTraded            10156 non-null  object\n",
      " 11  title                          10156 non-null  object\n",
      " 12  transactionHash                10156 non-null  object\n",
      " 13  type                           10156 non-null  object\n",
      " 14  market_creator                 10156 non-null  object\n",
      " 15  fpmm.answerFinalizedTimestamp  6463 non-null   object\n",
      " 16  fpmm.arbitrationOccurred       10156 non-null  bool  \n",
      " 17  fpmm.currentAnswer             6463 non-null   object\n",
      " 18  fpmm.id                        10156 non-null  object\n",
      " 19  fpmm.isPendingArbitration      10156 non-null  bool  \n",
      " 20  fpmm.openingTimestamp          10156 non-null  object\n",
      " 21  fpmm.outcomes                  10156 non-null  object\n",
      " 22  fpmm.title                     10156 non-null  object\n",
      " 23  fpmm.condition.id              10156 non-null  object\n",
      "dtypes: bool(2), object(22)\n",
      "memory usage: 1.7+ MB\n"
     ]
    }
   ],
   "source": [
    "new_trades.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 36970 entries, 0 to 36969\n",
      "Data columns (total 24 columns):\n",
      " #   Column                         Non-Null Count  Dtype \n",
      "---  ------                         --------------  ----- \n",
      " 0   collateralAmount               36970 non-null  object\n",
      " 1   collateralAmountUSD            36970 non-null  object\n",
      " 2   collateralToken                36970 non-null  object\n",
      " 3   creationTimestamp              36970 non-null  object\n",
      " 4   trader_address                 36970 non-null  object\n",
      " 5   feeAmount                      36970 non-null  object\n",
      " 6   id                             36970 non-null  object\n",
      " 7   oldOutcomeTokenMarginalPrice   36970 non-null  object\n",
      " 8   outcomeIndex                   36970 non-null  object\n",
      " 9   outcomeTokenMarginalPrice      36970 non-null  object\n",
      " 10  outcomeTokensTraded            36970 non-null  object\n",
      " 11  title                          36970 non-null  object\n",
      " 12  transactionHash                36970 non-null  object\n",
      " 13  type                           36970 non-null  object\n",
      " 14  market_creator                 36970 non-null  object\n",
      " 15  fpmm.answerFinalizedTimestamp  33241 non-null  object\n",
      " 16  fpmm.arbitrationOccurred       36970 non-null  bool  \n",
      " 17  fpmm.currentAnswer             33241 non-null  object\n",
      " 18  fpmm.id                        36970 non-null  object\n",
      " 19  fpmm.isPendingArbitration      36970 non-null  bool  \n",
      " 20  fpmm.openingTimestamp          36970 non-null  object\n",
      " 21  fpmm.outcomes                  36970 non-null  object\n",
      " 22  fpmm.title                     36970 non-null  object\n",
      " 23  fpmm.condition.id              36970 non-null  object\n",
      "dtypes: bool(2), object(22)\n",
      "memory usage: 6.3+ MB\n"
     ]
    }
   ],
   "source": [
    "old_trades.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "merge_df = pd.concat([old_trades, new_trades], ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['collateralAmount', 'collateralAmountUSD', 'collateralToken',\n",
       "       'creationTimestamp', 'trader_address', 'feeAmount', 'id',\n",
       "       'oldOutcomeTokenMarginalPrice', 'outcomeIndex',\n",
       "       'outcomeTokenMarginalPrice', 'outcomeTokensTraded', 'title',\n",
       "       'transactionHash', 'type', 'market_creator',\n",
       "       'fpmm.answerFinalizedTimestamp', 'fpmm.arbitrationOccurred',\n",
       "       'fpmm.currentAnswer', 'fpmm.id', 'fpmm.isPendingArbitration',\n",
       "       'fpmm.openingTimestamp', 'fpmm.outcomes', 'fpmm.title',\n",
       "       'fpmm.condition.id'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merge_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "merge_df['fpmm.arbitrationOccurred'] = merge_df['fpmm.arbitrationOccurred'].astype(bool)\n",
    "merge_df['fpmm.isPendingArbitration'] = merge_df['fpmm.isPendingArbitration'].astype(bool)\n",
    "merge_df['fpmm.outcomes'] = merge_df['fpmm.outcomes'].apply(list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 47126 entries, 0 to 47125\n",
      "Data columns (total 24 columns):\n",
      " #   Column                         Non-Null Count  Dtype \n",
      "---  ------                         --------------  ----- \n",
      " 0   collateralAmount               47126 non-null  object\n",
      " 1   collateralAmountUSD            47126 non-null  object\n",
      " 2   collateralToken                47126 non-null  object\n",
      " 3   creationTimestamp              47126 non-null  object\n",
      " 4   trader_address                 47126 non-null  object\n",
      " 5   feeAmount                      47126 non-null  object\n",
      " 6   id                             47126 non-null  object\n",
      " 7   oldOutcomeTokenMarginalPrice   47126 non-null  object\n",
      " 8   outcomeIndex                   47126 non-null  object\n",
      " 9   outcomeTokenMarginalPrice      47126 non-null  object\n",
      " 10  outcomeTokensTraded            47126 non-null  object\n",
      " 11  title                          47126 non-null  object\n",
      " 12  transactionHash                47126 non-null  object\n",
      " 13  type                           47126 non-null  object\n",
      " 14  market_creator                 47126 non-null  object\n",
      " 15  fpmm.answerFinalizedTimestamp  39704 non-null  object\n",
      " 16  fpmm.arbitrationOccurred       47126 non-null  bool  \n",
      " 17  fpmm.currentAnswer             39704 non-null  object\n",
      " 18  fpmm.id                        47126 non-null  object\n",
      " 19  fpmm.isPendingArbitration      47126 non-null  bool  \n",
      " 20  fpmm.openingTimestamp          47126 non-null  object\n",
      " 21  fpmm.outcomes                  47126 non-null  object\n",
      " 22  fpmm.title                     47126 non-null  object\n",
      " 23  fpmm.condition.id              47126 non-null  object\n",
      "dtypes: bool(2), object(22)\n",
      "memory usage: 8.0+ MB\n"
     ]
    }
   ],
   "source": [
    "merge_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>collateralAmount</th>\n",
       "      <th>collateralAmountUSD</th>\n",
       "      <th>collateralToken</th>\n",
       "      <th>creationTimestamp</th>\n",
       "      <th>trader_address</th>\n",
       "      <th>feeAmount</th>\n",
       "      <th>id</th>\n",
       "      <th>oldOutcomeTokenMarginalPrice</th>\n",
       "      <th>outcomeIndex</th>\n",
       "      <th>outcomeTokenMarginalPrice</th>\n",
       "      <th>...</th>\n",
       "      <th>market_creator</th>\n",
       "      <th>fpmm.answerFinalizedTimestamp</th>\n",
       "      <th>fpmm.arbitrationOccurred</th>\n",
       "      <th>fpmm.currentAnswer</th>\n",
       "      <th>fpmm.id</th>\n",
       "      <th>fpmm.isPendingArbitration</th>\n",
       "      <th>fpmm.openingTimestamp</th>\n",
       "      <th>fpmm.outcomes</th>\n",
       "      <th>fpmm.title</th>\n",
       "      <th>fpmm.condition.id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>930596765045617408</td>\n",
       "      <td>0.9305977993411753386434828033666473</td>\n",
       "      <td>0xe91d153e0b41518a2ce8dd3d7944fa863463a97d</td>\n",
       "      <td>1728596605</td>\n",
       "      <td>0x01274796ce41aa8e8312e05a427ffb4b0d2148f6</td>\n",
       "      <td>9305967650456174</td>\n",
       "      <td>0x007068173910cf8719b6f2e66a18b6825c9dde820x01...</td>\n",
       "      <td>0.5581119797629801968338361802418564</td>\n",
       "      <td>0</td>\n",
       "      <td>0.611825749650855211231211687533889</td>\n",
       "      <td>...</td>\n",
       "      <td>quickstart</td>\n",
       "      <td>1728822710</td>\n",
       "      <td>False</td>\n",
       "      <td>0x00000000000000000000000000000000000000000000...</td>\n",
       "      <td>0x007068173910cf8719b6f2e66a18b6825c9dde82</td>\n",
       "      <td>False</td>\n",
       "      <td>1728691200</td>\n",
       "      <td>[Yes, No]</td>\n",
       "      <td>Will the emergency public warning tests planne...</td>\n",
       "      <td>0xa610166e379c42404bd27bf12a16119fdb5171990c3e...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1033247234796193800</td>\n",
       "      <td>1.033250126003339493791032993674525</td>\n",
       "      <td>0xe91d153e0b41518a2ce8dd3d7944fa863463a97d</td>\n",
       "      <td>1728505575</td>\n",
       "      <td>0x034c4ad84f7ac6638bf19300d5bbe7d9b981e736</td>\n",
       "      <td>10332472347961938</td>\n",
       "      <td>0x007068173910cf8719b6f2e66a18b6825c9dde820x03...</td>\n",
       "      <td>0.6602089902983034451244461308090707</td>\n",
       "      <td>0</td>\n",
       "      <td>0.7034159692833852946883644485233207</td>\n",
       "      <td>...</td>\n",
       "      <td>quickstart</td>\n",
       "      <td>1728822710</td>\n",
       "      <td>False</td>\n",
       "      <td>0x00000000000000000000000000000000000000000000...</td>\n",
       "      <td>0x007068173910cf8719b6f2e66a18b6825c9dde82</td>\n",
       "      <td>False</td>\n",
       "      <td>1728691200</td>\n",
       "      <td>[Yes, No]</td>\n",
       "      <td>Will the emergency public warning tests planne...</td>\n",
       "      <td>0xa610166e379c42404bd27bf12a16119fdb5171990c3e...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1206692368842898300</td>\n",
       "      <td>1.206691596248187968367063717078884</td>\n",
       "      <td>0xe91d153e0b41518a2ce8dd3d7944fa863463a97d</td>\n",
       "      <td>1728562895</td>\n",
       "      <td>0x05e8bbdb89c84a14d05194bbbae81caf2340db72</td>\n",
       "      <td>12066923688428983</td>\n",
       "      <td>0x007068173910cf8719b6f2e66a18b6825c9dde820x05...</td>\n",
       "      <td>0.1931459183043721864309864210684546</td>\n",
       "      <td>1</td>\n",
       "      <td>0.3033804066591317111055858533563476</td>\n",
       "      <td>...</td>\n",
       "      <td>quickstart</td>\n",
       "      <td>1728822710</td>\n",
       "      <td>False</td>\n",
       "      <td>0x00000000000000000000000000000000000000000000...</td>\n",
       "      <td>0x007068173910cf8719b6f2e66a18b6825c9dde82</td>\n",
       "      <td>False</td>\n",
       "      <td>1728691200</td>\n",
       "      <td>[Yes, No]</td>\n",
       "      <td>Will the emergency public warning tests planne...</td>\n",
       "      <td>0xa610166e379c42404bd27bf12a16119fdb5171990c3e...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>930598203274544384</td>\n",
       "      <td>0.9305992375717008091217928729793422</td>\n",
       "      <td>0xe91d153e0b41518a2ce8dd3d7944fa863463a97d</td>\n",
       "      <td>1728596645</td>\n",
       "      <td>0x17c17ca981b7e244d0bad80b632a082dc1db36e5</td>\n",
       "      <td>9305982032745443</td>\n",
       "      <td>0x007068173910cf8719b6f2e66a18b6825c9dde820x17...</td>\n",
       "      <td>0.611825749650855211231211687533889</td>\n",
       "      <td>0</td>\n",
       "      <td>0.6579972404391247884756597316198778</td>\n",
       "      <td>...</td>\n",
       "      <td>quickstart</td>\n",
       "      <td>1728822710</td>\n",
       "      <td>False</td>\n",
       "      <td>0x00000000000000000000000000000000000000000000...</td>\n",
       "      <td>0x007068173910cf8719b6f2e66a18b6825c9dde82</td>\n",
       "      <td>False</td>\n",
       "      <td>1728691200</td>\n",
       "      <td>[Yes, No]</td>\n",
       "      <td>Will the emergency public warning tests planne...</td>\n",
       "      <td>0xa610166e379c42404bd27bf12a16119fdb5171990c3e...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1798695965102918400</td>\n",
       "      <td>1.798696795931342313936125782275225</td>\n",
       "      <td>0xe91d153e0b41518a2ce8dd3d7944fa863463a97d</td>\n",
       "      <td>1728337780</td>\n",
       "      <td>0x1d942103400c1f1657dcbffd5e08904787ea936b</td>\n",
       "      <td>17986959651029184</td>\n",
       "      <td>0x007068173910cf8719b6f2e66a18b6825c9dde820x1d...</td>\n",
       "      <td>0.7636157369419787681755577286755703</td>\n",
       "      <td>0</td>\n",
       "      <td>0.8080447772492735383356100969932859</td>\n",
       "      <td>...</td>\n",
       "      <td>quickstart</td>\n",
       "      <td>1728822710</td>\n",
       "      <td>False</td>\n",
       "      <td>0x00000000000000000000000000000000000000000000...</td>\n",
       "      <td>0x007068173910cf8719b6f2e66a18b6825c9dde82</td>\n",
       "      <td>False</td>\n",
       "      <td>1728691200</td>\n",
       "      <td>[Yes, No]</td>\n",
       "      <td>Will the emergency public warning tests planne...</td>\n",
       "      <td>0xa610166e379c42404bd27bf12a16119fdb5171990c3e...</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>47121</th>\n",
       "      <td>25000000000000000</td>\n",
       "      <td>0.02499979865789804997003353275455942</td>\n",
       "      <td>0xe91d153e0b41518a2ce8dd3d7944fa863463a97d</td>\n",
       "      <td>1730855575</td>\n",
       "      <td>0xac3ebb0ab2e0dc9aff761a9841e91e02e537cdbf</td>\n",
       "      <td>250000000000000</td>\n",
       "      <td>0xfff7eca2cfb2e53781a6e3aeed843d23324d1c2d0xac...</td>\n",
       "      <td>0.5052754451243323545575756175989419</td>\n",
       "      <td>1</td>\n",
       "      <td>0.5070214284001796010550698533200886</td>\n",
       "      <td>...</td>\n",
       "      <td>pearl</td>\n",
       "      <td>1731371725</td>\n",
       "      <td>False</td>\n",
       "      <td>0x00000000000000000000000000000000000000000000...</td>\n",
       "      <td>0xfff7eca2cfb2e53781a6e3aeed843d23324d1c2d</td>\n",
       "      <td>False</td>\n",
       "      <td>1731283200</td>\n",
       "      <td>[Yes, No]</td>\n",
       "      <td>Will any government health agency endorse the ...</td>\n",
       "      <td>0x63a63448a35cc9ca2e846cca20f1b97209ec360dbf2e...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47122</th>\n",
       "      <td>25000000000000000</td>\n",
       "      <td>0.02499981839904798411426657752147203</td>\n",
       "      <td>0xe91d153e0b41518a2ce8dd3d7944fa863463a97d</td>\n",
       "      <td>1730862995</td>\n",
       "      <td>0xaeb8c31302361d42ec806faf406ef0c30b6eba5f</td>\n",
       "      <td>250000000000000</td>\n",
       "      <td>0xfff7eca2cfb2e53781a6e3aeed843d23324d1c2d0xae...</td>\n",
       "      <td>0.5139429145196996157280923864113064</td>\n",
       "      <td>1</td>\n",
       "      <td>0.5156576876136041031258720624505359</td>\n",
       "      <td>...</td>\n",
       "      <td>pearl</td>\n",
       "      <td>1731371725</td>\n",
       "      <td>False</td>\n",
       "      <td>0x00000000000000000000000000000000000000000000...</td>\n",
       "      <td>0xfff7eca2cfb2e53781a6e3aeed843d23324d1c2d</td>\n",
       "      <td>False</td>\n",
       "      <td>1731283200</td>\n",
       "      <td>[Yes, No]</td>\n",
       "      <td>Will any government health agency endorse the ...</td>\n",
       "      <td>0x63a63448a35cc9ca2e846cca20f1b97209ec360dbf2e...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47123</th>\n",
       "      <td>25000000000000000</td>\n",
       "      <td>0.02499991408382658297437242184020979</td>\n",
       "      <td>0xe91d153e0b41518a2ce8dd3d7944fa863463a97d</td>\n",
       "      <td>1730859010</td>\n",
       "      <td>0xb42a955a0e06b3e6bdf229c9abfd2fdad20688a7</td>\n",
       "      <td>250000000000000</td>\n",
       "      <td>0xfff7eca2cfb2e53781a6e3aeed843d23324d1c2d0xb4...</td>\n",
       "      <td>0.5122219051325870794270436616331854</td>\n",
       "      <td>1</td>\n",
       "      <td>0.5139429145196996157280923864113064</td>\n",
       "      <td>...</td>\n",
       "      <td>pearl</td>\n",
       "      <td>1731371725</td>\n",
       "      <td>False</td>\n",
       "      <td>0x00000000000000000000000000000000000000000000...</td>\n",
       "      <td>0xfff7eca2cfb2e53781a6e3aeed843d23324d1c2d</td>\n",
       "      <td>False</td>\n",
       "      <td>1731283200</td>\n",
       "      <td>[Yes, No]</td>\n",
       "      <td>Will any government health agency endorse the ...</td>\n",
       "      <td>0x63a63448a35cc9ca2e846cca20f1b97209ec360dbf2e...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47124</th>\n",
       "      <td>25000000000000000</td>\n",
       "      <td>0.02499989344099467588421247197782077</td>\n",
       "      <td>0xe91d153e0b41518a2ce8dd3d7944fa863463a97d</td>\n",
       "      <td>1730854545</td>\n",
       "      <td>0xce9e38ee41e5e4b20d6670e2cba28c06dcd9470c</td>\n",
       "      <td>250000000000000</td>\n",
       "      <td>0xfff7eca2cfb2e53781a6e3aeed843d23324d1c2d0xce...</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1</td>\n",
       "      <td>0.5017647318434458790959496906595747</td>\n",
       "      <td>...</td>\n",
       "      <td>pearl</td>\n",
       "      <td>1731371725</td>\n",
       "      <td>False</td>\n",
       "      <td>0x00000000000000000000000000000000000000000000...</td>\n",
       "      <td>0xfff7eca2cfb2e53781a6e3aeed843d23324d1c2d</td>\n",
       "      <td>False</td>\n",
       "      <td>1731283200</td>\n",
       "      <td>[Yes, No]</td>\n",
       "      <td>Will any government health agency endorse the ...</td>\n",
       "      <td>0x63a63448a35cc9ca2e846cca20f1b97209ec360dbf2e...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47125</th>\n",
       "      <td>25000000000000000</td>\n",
       "      <td>0.0249998566063460038750538987517341</td>\n",
       "      <td>0xe91d153e0b41518a2ce8dd3d7944fa863463a97d</td>\n",
       "      <td>1730896450</td>\n",
       "      <td>0xd0d8e2b90946dc8ac5f5f48a08d9d5e7e5c5b3a0</td>\n",
       "      <td>250000000000000</td>\n",
       "      <td>0xfff7eca2cfb2e53781a6e3aeed843d23324d1c2d0xd0...</td>\n",
       "      <td>0.5838868793092060520852154046865615</td>\n",
       "      <td>1</td>\n",
       "      <td>0.5853343937326606060459394462230992</td>\n",
       "      <td>...</td>\n",
       "      <td>pearl</td>\n",
       "      <td>1731371725</td>\n",
       "      <td>False</td>\n",
       "      <td>0x00000000000000000000000000000000000000000000...</td>\n",
       "      <td>0xfff7eca2cfb2e53781a6e3aeed843d23324d1c2d</td>\n",
       "      <td>False</td>\n",
       "      <td>1731283200</td>\n",
       "      <td>[Yes, No]</td>\n",
       "      <td>Will any government health agency endorse the ...</td>\n",
       "      <td>0x63a63448a35cc9ca2e846cca20f1b97209ec360dbf2e...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>47113 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          collateralAmount                    collateralAmountUSD  \\\n",
       "0       930596765045617408   0.9305977993411753386434828033666473   \n",
       "1      1033247234796193800    1.033250126003339493791032993674525   \n",
       "2      1206692368842898300    1.206691596248187968367063717078884   \n",
       "3       930598203274544384   0.9305992375717008091217928729793422   \n",
       "4      1798695965102918400    1.798696795931342313936125782275225   \n",
       "...                    ...                                    ...   \n",
       "47121    25000000000000000  0.02499979865789804997003353275455942   \n",
       "47122    25000000000000000  0.02499981839904798411426657752147203   \n",
       "47123    25000000000000000  0.02499991408382658297437242184020979   \n",
       "47124    25000000000000000  0.02499989344099467588421247197782077   \n",
       "47125    25000000000000000   0.0249998566063460038750538987517341   \n",
       "\n",
       "                                  collateralToken creationTimestamp  \\\n",
       "0      0xe91d153e0b41518a2ce8dd3d7944fa863463a97d        1728596605   \n",
       "1      0xe91d153e0b41518a2ce8dd3d7944fa863463a97d        1728505575   \n",
       "2      0xe91d153e0b41518a2ce8dd3d7944fa863463a97d        1728562895   \n",
       "3      0xe91d153e0b41518a2ce8dd3d7944fa863463a97d        1728596645   \n",
       "4      0xe91d153e0b41518a2ce8dd3d7944fa863463a97d        1728337780   \n",
       "...                                           ...               ...   \n",
       "47121  0xe91d153e0b41518a2ce8dd3d7944fa863463a97d        1730855575   \n",
       "47122  0xe91d153e0b41518a2ce8dd3d7944fa863463a97d        1730862995   \n",
       "47123  0xe91d153e0b41518a2ce8dd3d7944fa863463a97d        1730859010   \n",
       "47124  0xe91d153e0b41518a2ce8dd3d7944fa863463a97d        1730854545   \n",
       "47125  0xe91d153e0b41518a2ce8dd3d7944fa863463a97d        1730896450   \n",
       "\n",
       "                                   trader_address          feeAmount  \\\n",
       "0      0x01274796ce41aa8e8312e05a427ffb4b0d2148f6   9305967650456174   \n",
       "1      0x034c4ad84f7ac6638bf19300d5bbe7d9b981e736  10332472347961938   \n",
       "2      0x05e8bbdb89c84a14d05194bbbae81caf2340db72  12066923688428983   \n",
       "3      0x17c17ca981b7e244d0bad80b632a082dc1db36e5   9305982032745443   \n",
       "4      0x1d942103400c1f1657dcbffd5e08904787ea936b  17986959651029184   \n",
       "...                                           ...                ...   \n",
       "47121  0xac3ebb0ab2e0dc9aff761a9841e91e02e537cdbf    250000000000000   \n",
       "47122  0xaeb8c31302361d42ec806faf406ef0c30b6eba5f    250000000000000   \n",
       "47123  0xb42a955a0e06b3e6bdf229c9abfd2fdad20688a7    250000000000000   \n",
       "47124  0xce9e38ee41e5e4b20d6670e2cba28c06dcd9470c    250000000000000   \n",
       "47125  0xd0d8e2b90946dc8ac5f5f48a08d9d5e7e5c5b3a0    250000000000000   \n",
       "\n",
       "                                                      id  \\\n",
       "0      0x007068173910cf8719b6f2e66a18b6825c9dde820x01...   \n",
       "1      0x007068173910cf8719b6f2e66a18b6825c9dde820x03...   \n",
       "2      0x007068173910cf8719b6f2e66a18b6825c9dde820x05...   \n",
       "3      0x007068173910cf8719b6f2e66a18b6825c9dde820x17...   \n",
       "4      0x007068173910cf8719b6f2e66a18b6825c9dde820x1d...   \n",
       "...                                                  ...   \n",
       "47121  0xfff7eca2cfb2e53781a6e3aeed843d23324d1c2d0xac...   \n",
       "47122  0xfff7eca2cfb2e53781a6e3aeed843d23324d1c2d0xae...   \n",
       "47123  0xfff7eca2cfb2e53781a6e3aeed843d23324d1c2d0xb4...   \n",
       "47124  0xfff7eca2cfb2e53781a6e3aeed843d23324d1c2d0xce...   \n",
       "47125  0xfff7eca2cfb2e53781a6e3aeed843d23324d1c2d0xd0...   \n",
       "\n",
       "               oldOutcomeTokenMarginalPrice outcomeIndex  \\\n",
       "0      0.5581119797629801968338361802418564            0   \n",
       "1      0.6602089902983034451244461308090707            0   \n",
       "2      0.1931459183043721864309864210684546            1   \n",
       "3       0.611825749650855211231211687533889            0   \n",
       "4      0.7636157369419787681755577286755703            0   \n",
       "...                                     ...          ...   \n",
       "47121  0.5052754451243323545575756175989419            1   \n",
       "47122  0.5139429145196996157280923864113064            1   \n",
       "47123  0.5122219051325870794270436616331854            1   \n",
       "47124                                   0.5            1   \n",
       "47125  0.5838868793092060520852154046865615            1   \n",
       "\n",
       "                  outcomeTokenMarginalPrice  ... market_creator  \\\n",
       "0       0.611825749650855211231211687533889  ...     quickstart   \n",
       "1      0.7034159692833852946883644485233207  ...     quickstart   \n",
       "2      0.3033804066591317111055858533563476  ...     quickstart   \n",
       "3      0.6579972404391247884756597316198778  ...     quickstart   \n",
       "4      0.8080447772492735383356100969932859  ...     quickstart   \n",
       "...                                     ...  ...            ...   \n",
       "47121  0.5070214284001796010550698533200886  ...          pearl   \n",
       "47122  0.5156576876136041031258720624505359  ...          pearl   \n",
       "47123  0.5139429145196996157280923864113064  ...          pearl   \n",
       "47124  0.5017647318434458790959496906595747  ...          pearl   \n",
       "47125  0.5853343937326606060459394462230992  ...          pearl   \n",
       "\n",
       "      fpmm.answerFinalizedTimestamp fpmm.arbitrationOccurred  \\\n",
       "0                        1728822710                    False   \n",
       "1                        1728822710                    False   \n",
       "2                        1728822710                    False   \n",
       "3                        1728822710                    False   \n",
       "4                        1728822710                    False   \n",
       "...                             ...                      ...   \n",
       "47121                    1731371725                    False   \n",
       "47122                    1731371725                    False   \n",
       "47123                    1731371725                    False   \n",
       "47124                    1731371725                    False   \n",
       "47125                    1731371725                    False   \n",
       "\n",
       "                                      fpmm.currentAnswer  \\\n",
       "0      0x00000000000000000000000000000000000000000000...   \n",
       "1      0x00000000000000000000000000000000000000000000...   \n",
       "2      0x00000000000000000000000000000000000000000000...   \n",
       "3      0x00000000000000000000000000000000000000000000...   \n",
       "4      0x00000000000000000000000000000000000000000000...   \n",
       "...                                                  ...   \n",
       "47121  0x00000000000000000000000000000000000000000000...   \n",
       "47122  0x00000000000000000000000000000000000000000000...   \n",
       "47123  0x00000000000000000000000000000000000000000000...   \n",
       "47124  0x00000000000000000000000000000000000000000000...   \n",
       "47125  0x00000000000000000000000000000000000000000000...   \n",
       "\n",
       "                                          fpmm.id fpmm.isPendingArbitration  \\\n",
       "0      0x007068173910cf8719b6f2e66a18b6825c9dde82                     False   \n",
       "1      0x007068173910cf8719b6f2e66a18b6825c9dde82                     False   \n",
       "2      0x007068173910cf8719b6f2e66a18b6825c9dde82                     False   \n",
       "3      0x007068173910cf8719b6f2e66a18b6825c9dde82                     False   \n",
       "4      0x007068173910cf8719b6f2e66a18b6825c9dde82                     False   \n",
       "...                                           ...                       ...   \n",
       "47121  0xfff7eca2cfb2e53781a6e3aeed843d23324d1c2d                     False   \n",
       "47122  0xfff7eca2cfb2e53781a6e3aeed843d23324d1c2d                     False   \n",
       "47123  0xfff7eca2cfb2e53781a6e3aeed843d23324d1c2d                     False   \n",
       "47124  0xfff7eca2cfb2e53781a6e3aeed843d23324d1c2d                     False   \n",
       "47125  0xfff7eca2cfb2e53781a6e3aeed843d23324d1c2d                     False   \n",
       "\n",
       "       fpmm.openingTimestamp fpmm.outcomes  \\\n",
       "0                 1728691200     [Yes, No]   \n",
       "1                 1728691200     [Yes, No]   \n",
       "2                 1728691200     [Yes, No]   \n",
       "3                 1728691200     [Yes, No]   \n",
       "4                 1728691200     [Yes, No]   \n",
       "...                      ...           ...   \n",
       "47121             1731283200     [Yes, No]   \n",
       "47122             1731283200     [Yes, No]   \n",
       "47123             1731283200     [Yes, No]   \n",
       "47124             1731283200     [Yes, No]   \n",
       "47125             1731283200     [Yes, No]   \n",
       "\n",
       "                                              fpmm.title  \\\n",
       "0      Will the emergency public warning tests planne...   \n",
       "1      Will the emergency public warning tests planne...   \n",
       "2      Will the emergency public warning tests planne...   \n",
       "3      Will the emergency public warning tests planne...   \n",
       "4      Will the emergency public warning tests planne...   \n",
       "...                                                  ...   \n",
       "47121  Will any government health agency endorse the ...   \n",
       "47122  Will any government health agency endorse the ...   \n",
       "47123  Will any government health agency endorse the ...   \n",
       "47124  Will any government health agency endorse the ...   \n",
       "47125  Will any government health agency endorse the ...   \n",
       "\n",
       "                                       fpmm.condition.id  \n",
       "0      0xa610166e379c42404bd27bf12a16119fdb5171990c3e...  \n",
       "1      0xa610166e379c42404bd27bf12a16119fdb5171990c3e...  \n",
       "2      0xa610166e379c42404bd27bf12a16119fdb5171990c3e...  \n",
       "3      0xa610166e379c42404bd27bf12a16119fdb5171990c3e...  \n",
       "4      0xa610166e379c42404bd27bf12a16119fdb5171990c3e...  \n",
       "...                                                  ...  \n",
       "47121  0x63a63448a35cc9ca2e846cca20f1b97209ec360dbf2e...  \n",
       "47122  0x63a63448a35cc9ca2e846cca20f1b97209ec360dbf2e...  \n",
       "47123  0x63a63448a35cc9ca2e846cca20f1b97209ec360dbf2e...  \n",
       "47124  0x63a63448a35cc9ca2e846cca20f1b97209ec360dbf2e...  \n",
       "47125  0x63a63448a35cc9ca2e846cca20f1b97209ec360dbf2e...  \n",
       "\n",
       "[47113 rows x 24 columns]"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merge_df.drop_duplicates(subset=[col for col in merge_df.columns if col != 'fpmm.outcomes'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Checking column fpmm.answerFinalizedTimestamp:\n",
      "<class 'str'>\n",
      "Checking column fpmm.arbitrationOccurred:\n",
      "<class 'numpy.bool_'>\n",
      "Checking column fpmm.currentAnswer:\n",
      "<class 'str'>\n",
      "Checking column fpmm.id:\n",
      "<class 'str'>\n",
      "Checking column fpmm.isPendingArbitration:\n",
      "<class 'numpy.bool_'>\n",
      "Checking column fpmm.openingTimestamp:\n",
      "<class 'str'>\n",
      "Checking column fpmm.outcomes:\n",
      "<class 'numpy.ndarray'>\n",
      "Checking column fpmm.title:\n",
      "<class 'str'>\n",
      "Checking column fpmm.condition.id:\n",
      "<class 'str'>\n"
     ]
    }
   ],
   "source": [
    "for col in ['fpmm.answerFinalizedTimestamp', 'fpmm.arbitrationOccurred',\n",
    "       'fpmm.currentAnswer', 'fpmm.id', 'fpmm.isPendingArbitration',\n",
    "       'fpmm.openingTimestamp', 'fpmm.outcomes', 'fpmm.title',\n",
    "       'fpmm.condition.id']:\n",
    "    print(f\"Checking column {col}:\")\n",
    "    print(merge_df[col].iloc[0].__class__)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4861"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(trades)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>trader_address</th>\n",
       "      <th>market_creator</th>\n",
       "      <th>trade_id</th>\n",
       "      <th>creation_timestamp</th>\n",
       "      <th>title</th>\n",
       "      <th>market_status</th>\n",
       "      <th>collateral_amount</th>\n",
       "      <th>outcome_index</th>\n",
       "      <th>trade_fee_amount</th>\n",
       "      <th>outcomes_tokens_traded</th>\n",
       "      <th>current_answer</th>\n",
       "      <th>is_invalid</th>\n",
       "      <th>winning_trade</th>\n",
       "      <th>earnings</th>\n",
       "      <th>redeemed</th>\n",
       "      <th>redeemed_amount</th>\n",
       "      <th>num_mech_calls</th>\n",
       "      <th>mech_fee_amount</th>\n",
       "      <th>net_earnings</th>\n",
       "      <th>roi</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0x034c4ad84f7ac6638bf19300d5bbe7d9b981e736</td>\n",
       "      <td>quickstart</td>\n",
       "      <td>0x039ec9bcbcd776ce9b105ed981d0594a0b5d5f5b0x03...</td>\n",
       "      <td>2024-11-06 12:25:00+00:00</td>\n",
       "      <td>Will Wendy's announce any additional restauran...</td>\n",
       "      <td>CLOSED</td>\n",
       "      <td>1.360378</td>\n",
       "      <td>1</td>\n",
       "      <td>0.013604</td>\n",
       "      <td>1.680858</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>True</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-1.373982</td>\n",
       "      <td>-1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0x034c4ad84f7ac6638bf19300d5bbe7d9b981e736</td>\n",
       "      <td>quickstart</td>\n",
       "      <td>0x08181acebfc1b308fbfecbebd24d060fed0cd84e0x03...</td>\n",
       "      <td>2024-11-08 16:31:10+00:00</td>\n",
       "      <td>Will any major news outlet report on inaccurac...</td>\n",
       "      <td>CLOSED</td>\n",
       "      <td>1.690528</td>\n",
       "      <td>1</td>\n",
       "      <td>0.016905</td>\n",
       "      <td>2.377660</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>2.377660</td>\n",
       "      <td>True</td>\n",
       "      <td>2.377660</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.670226</td>\n",
       "      <td>0.392534</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0x034c4ad84f7ac6638bf19300d5bbe7d9b981e736</td>\n",
       "      <td>quickstart</td>\n",
       "      <td>0x08a5f2e0ca0d721a74662833a83cb634afa65e350x03...</td>\n",
       "      <td>2024-11-08 16:35:35+00:00</td>\n",
       "      <td>Will Microsoft unveil a new climate initiative...</td>\n",
       "      <td>CLOSED</td>\n",
       "      <td>2.282494</td>\n",
       "      <td>1</td>\n",
       "      <td>0.022825</td>\n",
       "      <td>3.376936</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>True</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-2.305319</td>\n",
       "      <td>-1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0x034c4ad84f7ac6638bf19300d5bbe7d9b981e736</td>\n",
       "      <td>quickstart</td>\n",
       "      <td>0x1d7c76bc561696cf66c010e66ea035347e7491a80x03...</td>\n",
       "      <td>2024-11-08 18:32:30+00:00</td>\n",
       "      <td>Will Microsoft complete the construction of it...</td>\n",
       "      <td>CLOSED</td>\n",
       "      <td>2.216210</td>\n",
       "      <td>1</td>\n",
       "      <td>0.022162</td>\n",
       "      <td>3.843271</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>3.843271</td>\n",
       "      <td>True</td>\n",
       "      <td>3.843271</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.604899</td>\n",
       "      <td>0.716994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0x034c4ad84f7ac6638bf19300d5bbe7d9b981e736</td>\n",
       "      <td>quickstart</td>\n",
       "      <td>0x245b1b25aa62caf1b9d4379e7dd393d47e3fe0eb0x03...</td>\n",
       "      <td>2024-11-06 14:23:20+00:00</td>\n",
       "      <td>Will Wendy's open at least 140 new restaurant ...</td>\n",
       "      <td>CLOSED</td>\n",
       "      <td>3.746832</td>\n",
       "      <td>0</td>\n",
       "      <td>0.037468</td>\n",
       "      <td>6.719134</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>6.719134</td>\n",
       "      <td>True</td>\n",
       "      <td>6.719134</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2.934833</td>\n",
       "      <td>0.775529</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4856</th>\n",
       "      <td>0x6b5d38596ccb989fd9ef8184b0ba76bde1ae3b4b</td>\n",
       "      <td>pearl</td>\n",
       "      <td>0xfb91d659d1c6acf665abbb7a42d4f18da4d8ce9e0x6b...</td>\n",
       "      <td>2024-11-02 19:13:20+00:00</td>\n",
       "      <td>Will Kamala Harris win the state of Michigan i...</td>\n",
       "      <td>CLOSED</td>\n",
       "      <td>0.025000</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000250</td>\n",
       "      <td>0.051386</td>\n",
       "      <td>-1</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>0.025000</td>\n",
       "      <td>False</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1</td>\n",
       "      <td>0.01</td>\n",
       "      <td>-0.010250</td>\n",
       "      <td>-0.290780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4857</th>\n",
       "      <td>0xc5bc3ae599aa5dc2f56faeb074e0544d39193790</td>\n",
       "      <td>pearl</td>\n",
       "      <td>0xfb91d659d1c6acf665abbb7a42d4f18da4d8ce9e0xc5...</td>\n",
       "      <td>2024-11-04 14:10:50+00:00</td>\n",
       "      <td>Will Kamala Harris win the state of Michigan i...</td>\n",
       "      <td>CLOSED</td>\n",
       "      <td>0.025000</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000250</td>\n",
       "      <td>0.048030</td>\n",
       "      <td>-1</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>0.025000</td>\n",
       "      <td>False</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2</td>\n",
       "      <td>0.02</td>\n",
       "      <td>-0.020250</td>\n",
       "      <td>-0.447514</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4858</th>\n",
       "      <td>0xc5bc3ae599aa5dc2f56faeb074e0544d39193790</td>\n",
       "      <td>pearl</td>\n",
       "      <td>0xfd8612f9cad2a0672844e2cb794b8e1b7294a9040xc5...</td>\n",
       "      <td>2024-11-04 14:20:10+00:00</td>\n",
       "      <td>Will any major cybersecurity firm announce new...</td>\n",
       "      <td>CLOSED</td>\n",
       "      <td>0.025000</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000250</td>\n",
       "      <td>0.046005</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>False</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3</td>\n",
       "      <td>0.03</td>\n",
       "      <td>-0.055250</td>\n",
       "      <td>-1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4859</th>\n",
       "      <td>0xc5bc3ae599aa5dc2f56faeb074e0544d39193790</td>\n",
       "      <td>pearl</td>\n",
       "      <td>0xfd8612f9cad2a0672844e2cb794b8e1b7294a9040xc5...</td>\n",
       "      <td>2024-11-06 17:48:50+00:00</td>\n",
       "      <td>Will any major cybersecurity firm announce new...</td>\n",
       "      <td>CLOSED</td>\n",
       "      <td>0.025000</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000250</td>\n",
       "      <td>0.043049</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>False</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3</td>\n",
       "      <td>0.03</td>\n",
       "      <td>-0.055250</td>\n",
       "      <td>-1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4860</th>\n",
       "      <td>0xc5bc3ae599aa5dc2f56faeb074e0544d39193790</td>\n",
       "      <td>pearl</td>\n",
       "      <td>0xffdd70e81b9af2aac15bf6820a4085be4d79254d0xc5...</td>\n",
       "      <td>2024-11-06 17:45:45+00:00</td>\n",
       "      <td>Will any country announce increased military a...</td>\n",
       "      <td>CLOSED</td>\n",
       "      <td>0.025000</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000250</td>\n",
       "      <td>0.043076</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>0.043076</td>\n",
       "      <td>False</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.007826</td>\n",
       "      <td>0.222004</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4861 rows × 20 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  trader_address market_creator  \\\n",
       "0     0x034c4ad84f7ac6638bf19300d5bbe7d9b981e736     quickstart   \n",
       "1     0x034c4ad84f7ac6638bf19300d5bbe7d9b981e736     quickstart   \n",
       "2     0x034c4ad84f7ac6638bf19300d5bbe7d9b981e736     quickstart   \n",
       "3     0x034c4ad84f7ac6638bf19300d5bbe7d9b981e736     quickstart   \n",
       "4     0x034c4ad84f7ac6638bf19300d5bbe7d9b981e736     quickstart   \n",
       "...                                          ...            ...   \n",
       "4856  0x6b5d38596ccb989fd9ef8184b0ba76bde1ae3b4b          pearl   \n",
       "4857  0xc5bc3ae599aa5dc2f56faeb074e0544d39193790          pearl   \n",
       "4858  0xc5bc3ae599aa5dc2f56faeb074e0544d39193790          pearl   \n",
       "4859  0xc5bc3ae599aa5dc2f56faeb074e0544d39193790          pearl   \n",
       "4860  0xc5bc3ae599aa5dc2f56faeb074e0544d39193790          pearl   \n",
       "\n",
       "                                               trade_id  \\\n",
       "0     0x039ec9bcbcd776ce9b105ed981d0594a0b5d5f5b0x03...   \n",
       "1     0x08181acebfc1b308fbfecbebd24d060fed0cd84e0x03...   \n",
       "2     0x08a5f2e0ca0d721a74662833a83cb634afa65e350x03...   \n",
       "3     0x1d7c76bc561696cf66c010e66ea035347e7491a80x03...   \n",
       "4     0x245b1b25aa62caf1b9d4379e7dd393d47e3fe0eb0x03...   \n",
       "...                                                 ...   \n",
       "4856  0xfb91d659d1c6acf665abbb7a42d4f18da4d8ce9e0x6b...   \n",
       "4857  0xfb91d659d1c6acf665abbb7a42d4f18da4d8ce9e0xc5...   \n",
       "4858  0xfd8612f9cad2a0672844e2cb794b8e1b7294a9040xc5...   \n",
       "4859  0xfd8612f9cad2a0672844e2cb794b8e1b7294a9040xc5...   \n",
       "4860  0xffdd70e81b9af2aac15bf6820a4085be4d79254d0xc5...   \n",
       "\n",
       "            creation_timestamp  \\\n",
       "0    2024-11-06 12:25:00+00:00   \n",
       "1    2024-11-08 16:31:10+00:00   \n",
       "2    2024-11-08 16:35:35+00:00   \n",
       "3    2024-11-08 18:32:30+00:00   \n",
       "4    2024-11-06 14:23:20+00:00   \n",
       "...                        ...   \n",
       "4856 2024-11-02 19:13:20+00:00   \n",
       "4857 2024-11-04 14:10:50+00:00   \n",
       "4858 2024-11-04 14:20:10+00:00   \n",
       "4859 2024-11-06 17:48:50+00:00   \n",
       "4860 2024-11-06 17:45:45+00:00   \n",
       "\n",
       "                                                  title market_status  \\\n",
       "0     Will Wendy's announce any additional restauran...        CLOSED   \n",
       "1     Will any major news outlet report on inaccurac...        CLOSED   \n",
       "2     Will Microsoft unveil a new climate initiative...        CLOSED   \n",
       "3     Will Microsoft complete the construction of it...        CLOSED   \n",
       "4     Will Wendy's open at least 140 new restaurant ...        CLOSED   \n",
       "...                                                 ...           ...   \n",
       "4856  Will Kamala Harris win the state of Michigan i...        CLOSED   \n",
       "4857  Will Kamala Harris win the state of Michigan i...        CLOSED   \n",
       "4858  Will any major cybersecurity firm announce new...        CLOSED   \n",
       "4859  Will any major cybersecurity firm announce new...        CLOSED   \n",
       "4860  Will any country announce increased military a...        CLOSED   \n",
       "\n",
       "      collateral_amount outcome_index  trade_fee_amount  \\\n",
       "0              1.360378             1          0.013604   \n",
       "1              1.690528             1          0.016905   \n",
       "2              2.282494             1          0.022825   \n",
       "3              2.216210             1          0.022162   \n",
       "4              3.746832             0          0.037468   \n",
       "...                 ...           ...               ...   \n",
       "4856           0.025000             0          0.000250   \n",
       "4857           0.025000             0          0.000250   \n",
       "4858           0.025000             0          0.000250   \n",
       "4859           0.025000             0          0.000250   \n",
       "4860           0.025000             0          0.000250   \n",
       "\n",
       "      outcomes_tokens_traded  current_answer  is_invalid  winning_trade  \\\n",
       "0                   1.680858               0       False          False   \n",
       "1                   2.377660               1       False           True   \n",
       "2                   3.376936               0       False          False   \n",
       "3                   3.843271               1       False           True   \n",
       "4                   6.719134               0       False           True   \n",
       "...                      ...             ...         ...            ...   \n",
       "4856                0.051386              -1        True          False   \n",
       "4857                0.048030              -1        True          False   \n",
       "4858                0.046005               1       False          False   \n",
       "4859                0.043049               1       False          False   \n",
       "4860                0.043076               0       False           True   \n",
       "\n",
       "      earnings  redeemed  redeemed_amount  num_mech_calls  mech_fee_amount  \\\n",
       "0     0.000000      True         0.000000               0             0.00   \n",
       "1     2.377660      True         2.377660               0             0.00   \n",
       "2     0.000000      True         0.000000               0             0.00   \n",
       "3     3.843271      True         3.843271               0             0.00   \n",
       "4     6.719134      True         6.719134               0             0.00   \n",
       "...        ...       ...              ...             ...              ...   \n",
       "4856  0.025000     False         0.000000               1             0.01   \n",
       "4857  0.025000     False         0.000000               2             0.02   \n",
       "4858  0.000000     False         0.000000               3             0.03   \n",
       "4859  0.000000     False         0.000000               3             0.03   \n",
       "4860  0.043076     False         0.000000               1             0.01   \n",
       "\n",
       "      net_earnings       roi  \n",
       "0        -1.373982 -1.000000  \n",
       "1         0.670226  0.392534  \n",
       "2        -2.305319 -1.000000  \n",
       "3         1.604899  0.716994  \n",
       "4         2.934833  0.775529  \n",
       "...            ...       ...  \n",
       "4856     -0.010250 -0.290780  \n",
       "4857     -0.020250 -0.447514  \n",
       "4858     -0.055250 -1.000000  \n",
       "4859     -0.055250 -1.000000  \n",
       "4860      0.007826  0.222004  \n",
       "\n",
       "[4861 rows x 20 columns]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trades.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "trades[\"creationTimestamp\"]= pd.to_datetime(\n",
    "        trades[\"creationTimestamp\"]\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'1730108820'"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "max(trades.creationTimestamp)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "str"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(trades.creationTimestamp.iloc[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_trades = pd.read_parquet('../data/all_trades_profitability.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 26861 entries, 0 to 26860\n",
      "Data columns (total 21 columns):\n",
      " #   Column                  Non-Null Count  Dtype              \n",
      "---  ------                  --------------  -----              \n",
      " 0   trader_address          26861 non-null  object             \n",
      " 1   market_creator          26861 non-null  object             \n",
      " 2   trade_id                26861 non-null  object             \n",
      " 3   creation_timestamp      26861 non-null  datetime64[ns, UTC]\n",
      " 4   title                   26861 non-null  object             \n",
      " 5   market_status           26861 non-null  object             \n",
      " 6   collateral_amount       26861 non-null  float64            \n",
      " 7   outcome_index           26861 non-null  object             \n",
      " 8   trade_fee_amount        26861 non-null  float64            \n",
      " 9   outcomes_tokens_traded  26861 non-null  float64            \n",
      " 10  current_answer          26861 non-null  int64              \n",
      " 11  is_invalid              26861 non-null  bool               \n",
      " 12  winning_trade           26861 non-null  bool               \n",
      " 13  earnings                26861 non-null  float64            \n",
      " 14  redeemed                26861 non-null  bool               \n",
      " 15  redeemed_amount         26861 non-null  float64            \n",
      " 16  num_mech_calls          26861 non-null  int64              \n",
      " 17  mech_fee_amount         26861 non-null  float64            \n",
      " 18  net_earnings            26861 non-null  float64            \n",
      " 19  roi                     26861 non-null  float64            \n",
      " 20  staking                 26861 non-null  object             \n",
      "dtypes: bool(3), datetime64[ns, UTC](1), float64(8), int64(2), object(7)\n",
      "memory usage: 3.8+ MB\n"
     ]
    }
   ],
   "source": [
    "all_trades.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "latest = max(all_trades.creation_timestamp)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1729903200"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "int(latest.timestamp())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "import re\n",
    "\n",
    "def extract_title(text: str) -> str:\n",
    "    question = re.search('\"([^\"]+)\"', text).group(1)\n",
    "    return question"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "tools[\"title\"] = tools.apply(lambda x: extract_title(x.prompt_request), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>request_id</th>\n",
       "      <th>request_block</th>\n",
       "      <th>prompt_request</th>\n",
       "      <th>tool</th>\n",
       "      <th>nonce</th>\n",
       "      <th>trader_address</th>\n",
       "      <th>deliver_block</th>\n",
       "      <th>error</th>\n",
       "      <th>error_message</th>\n",
       "      <th>prompt_response</th>\n",
       "      <th>...</th>\n",
       "      <th>confidence</th>\n",
       "      <th>info_utility</th>\n",
       "      <th>vote</th>\n",
       "      <th>win_probability</th>\n",
       "      <th>market_creator</th>\n",
       "      <th>title</th>\n",
       "      <th>currentAnswer</th>\n",
       "      <th>request_time</th>\n",
       "      <th>request_month_year</th>\n",
       "      <th>request_month_year_week</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>582</th>\n",
       "      <td>2220491272998867787999910776935393408411702877...</td>\n",
       "      <td>36720164</td>\n",
       "      <td>Please take over the role of a Data Scientist ...</td>\n",
       "      <td>superforcaster</td>\n",
       "      <td>9d205bb8-09d3-42f9-aa92-c58a31a4082e</td>\n",
       "      <td>0xd127e0434a284e04034b0e73e891c501f583ad3d</td>\n",
       "      <td>36720174</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>\\nYou are an advanced AI system which has been...</td>\n",
       "      <td>...</td>\n",
       "      <td>0.6</td>\n",
       "      <td>0.4</td>\n",
       "      <td>No</td>\n",
       "      <td>0.85</td>\n",
       "      <td>quickstart</td>\n",
       "      <td>Will any regulatory body impose penalties on C...</td>\n",
       "      <td>None</td>\n",
       "      <td>2024-10-27 16:05:20</td>\n",
       "      <td>2024-10</td>\n",
       "      <td>2024-10-21/2024-10-27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4164</th>\n",
       "      <td>7954746357421406217625419969909404056225427053...</td>\n",
       "      <td>36701081</td>\n",
       "      <td>Please take over the role of a Data Scientist ...</td>\n",
       "      <td>superforcaster</td>\n",
       "      <td>1b609b7e-e0d2-4bb8-ad6b-7d0e6e6610b5</td>\n",
       "      <td>0x8dd0f0f64e575a356545d9ed096122a1887e64bf</td>\n",
       "      <td>36701099</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>\\nYou are an advanced AI system which has been...</td>\n",
       "      <td>...</td>\n",
       "      <td>0.6</td>\n",
       "      <td>0.4</td>\n",
       "      <td>No</td>\n",
       "      <td>0.70</td>\n",
       "      <td>quickstart</td>\n",
       "      <td>Will any new human rights organizations public...</td>\n",
       "      <td>None</td>\n",
       "      <td>2024-10-26 12:40:25</td>\n",
       "      <td>2024-10</td>\n",
       "      <td>2024-10-21/2024-10-27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6054</th>\n",
       "      <td>4565494807135187962212494187805259514627250416...</td>\n",
       "      <td>36703764</td>\n",
       "      <td>Please take over the role of a Data Scientist ...</td>\n",
       "      <td>superforcaster</td>\n",
       "      <td>c8cabb44-c1e5-4e35-a7a3-005fb3eecc0e</td>\n",
       "      <td>0xacb24b20805c6e475d2c17edb2a997c1ba47de79</td>\n",
       "      <td>36703777</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>\\nYou are an advanced AI system which has been...</td>\n",
       "      <td>...</td>\n",
       "      <td>0.6</td>\n",
       "      <td>0.3</td>\n",
       "      <td>No</td>\n",
       "      <td>0.90</td>\n",
       "      <td>quickstart</td>\n",
       "      <td>Will any cybersecurity firm publicly announce ...</td>\n",
       "      <td>None</td>\n",
       "      <td>2024-10-26 16:31:55</td>\n",
       "      <td>2024-10</td>\n",
       "      <td>2024-10-21/2024-10-27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7265</th>\n",
       "      <td>7132897945614190217838117925907875446836020321...</td>\n",
       "      <td>36702802</td>\n",
       "      <td>Please take over the role of a Data Scientist ...</td>\n",
       "      <td>superforcaster</td>\n",
       "      <td>a8e456c8-b87a-41fd-ba17-a69d9b2dc195</td>\n",
       "      <td>0xacb24b20805c6e475d2c17edb2a997c1ba47de79</td>\n",
       "      <td>36702815</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>\\nYou are an advanced AI system which has been...</td>\n",
       "      <td>...</td>\n",
       "      <td>0.6</td>\n",
       "      <td>0.4</td>\n",
       "      <td>No</td>\n",
       "      <td>0.70</td>\n",
       "      <td>quickstart</td>\n",
       "      <td>Will any of the Caspian Sea countries publicly...</td>\n",
       "      <td>None</td>\n",
       "      <td>2024-10-26 15:09:35</td>\n",
       "      <td>2024-10</td>\n",
       "      <td>2024-10-21/2024-10-27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9149</th>\n",
       "      <td>2657251108286481564605188898254109837583373485...</td>\n",
       "      <td>36708789</td>\n",
       "      <td>Please take over the role of a Data Scientist ...</td>\n",
       "      <td>superforcaster</td>\n",
       "      <td>631c87dc-f7b7-42c7-a7db-4248834bc941</td>\n",
       "      <td>0xd11e4a1aa52a75ee7186da44b4c84555f0b9aa95</td>\n",
       "      <td>36708804</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>\\nYou are an advanced AI system which has been...</td>\n",
       "      <td>...</td>\n",
       "      <td>0.6</td>\n",
       "      <td>0.3</td>\n",
       "      <td>No</td>\n",
       "      <td>0.95</td>\n",
       "      <td>quickstart</td>\n",
       "      <td>Will any cybersecurity firm publicly announce ...</td>\n",
       "      <td>None</td>\n",
       "      <td>2024-10-26 23:43:50</td>\n",
       "      <td>2024-10</td>\n",
       "      <td>2024-10-21/2024-10-27</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             request_id request_block  \\\n",
       "582   2220491272998867787999910776935393408411702877...      36720164   \n",
       "4164  7954746357421406217625419969909404056225427053...      36701081   \n",
       "6054  4565494807135187962212494187805259514627250416...      36703764   \n",
       "7265  7132897945614190217838117925907875446836020321...      36702802   \n",
       "9149  2657251108286481564605188898254109837583373485...      36708789   \n",
       "\n",
       "                                         prompt_request            tool  \\\n",
       "582   Please take over the role of a Data Scientist ...  superforcaster   \n",
       "4164  Please take over the role of a Data Scientist ...  superforcaster   \n",
       "6054  Please take over the role of a Data Scientist ...  superforcaster   \n",
       "7265  Please take over the role of a Data Scientist ...  superforcaster   \n",
       "9149  Please take over the role of a Data Scientist ...  superforcaster   \n",
       "\n",
       "                                     nonce  \\\n",
       "582   9d205bb8-09d3-42f9-aa92-c58a31a4082e   \n",
       "4164  1b609b7e-e0d2-4bb8-ad6b-7d0e6e6610b5   \n",
       "6054  c8cabb44-c1e5-4e35-a7a3-005fb3eecc0e   \n",
       "7265  a8e456c8-b87a-41fd-ba17-a69d9b2dc195   \n",
       "9149  631c87dc-f7b7-42c7-a7db-4248834bc941   \n",
       "\n",
       "                                  trader_address deliver_block  error  \\\n",
       "582   0xd127e0434a284e04034b0e73e891c501f583ad3d      36720174      0   \n",
       "4164  0x8dd0f0f64e575a356545d9ed096122a1887e64bf      36701099      0   \n",
       "6054  0xacb24b20805c6e475d2c17edb2a997c1ba47de79      36703777      0   \n",
       "7265  0xacb24b20805c6e475d2c17edb2a997c1ba47de79      36702815      0   \n",
       "9149  0xd11e4a1aa52a75ee7186da44b4c84555f0b9aa95      36708804      0   \n",
       "\n",
       "     error_message                                    prompt_response  ...  \\\n",
       "582           None  \\nYou are an advanced AI system which has been...  ...   \n",
       "4164          None  \\nYou are an advanced AI system which has been...  ...   \n",
       "6054          None  \\nYou are an advanced AI system which has been...  ...   \n",
       "7265          None  \\nYou are an advanced AI system which has been...  ...   \n",
       "9149          None  \\nYou are an advanced AI system which has been...  ...   \n",
       "\n",
       "     confidence  info_utility  vote  win_probability  market_creator  \\\n",
       "582         0.6           0.4    No             0.85      quickstart   \n",
       "4164        0.6           0.4    No             0.70      quickstart   \n",
       "6054        0.6           0.3    No             0.90      quickstart   \n",
       "7265        0.6           0.4    No             0.70      quickstart   \n",
       "9149        0.6           0.3    No             0.95      quickstart   \n",
       "\n",
       "                                                  title  currentAnswer  \\\n",
       "582   Will any regulatory body impose penalties on C...           None   \n",
       "4164  Will any new human rights organizations public...           None   \n",
       "6054  Will any cybersecurity firm publicly announce ...           None   \n",
       "7265  Will any of the Caspian Sea countries publicly...           None   \n",
       "9149  Will any cybersecurity firm publicly announce ...           None   \n",
       "\n",
       "             request_time request_month_year request_month_year_week  \n",
       "582   2024-10-27 16:05:20            2024-10   2024-10-21/2024-10-27  \n",
       "4164  2024-10-26 12:40:25            2024-10   2024-10-21/2024-10-27  \n",
       "6054  2024-10-26 16:31:55            2024-10   2024-10-21/2024-10-27  \n",
       "7265  2024-10-26 15:09:35            2024-10   2024-10-21/2024-10-27  \n",
       "9149  2024-10-26 23:43:50            2024-10   2024-10-21/2024-10-27  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tools_superforcaster.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "superforcaster_trades = pd.merge(all_trades, tools_superforcaster, on=[\"title\",\"trader_address\"], how=\"inner\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 0 entries\n",
      "Data columns (total 42 columns):\n",
      " #   Column                   Non-Null Count  Dtype              \n",
      "---  ------                   --------------  -----              \n",
      " 0   trader_address           0 non-null      object             \n",
      " 1   market_creator_x         0 non-null      object             \n",
      " 2   trade_id                 0 non-null      object             \n",
      " 3   creation_timestamp       0 non-null      datetime64[ns, UTC]\n",
      " 4   title                    0 non-null      object             \n",
      " 5   market_status            0 non-null      object             \n",
      " 6   collateral_amount        0 non-null      float64            \n",
      " 7   outcome_index            0 non-null      object             \n",
      " 8   trade_fee_amount         0 non-null      float64            \n",
      " 9   outcomes_tokens_traded   0 non-null      float64            \n",
      " 10  current_answer           0 non-null      int64              \n",
      " 11  is_invalid               0 non-null      bool               \n",
      " 12  winning_trade            0 non-null      bool               \n",
      " 13  earnings                 0 non-null      float64            \n",
      " 14  redeemed                 0 non-null      bool               \n",
      " 15  redeemed_amount          0 non-null      float64            \n",
      " 16  num_mech_calls           0 non-null      int64              \n",
      " 17  mech_fee_amount          0 non-null      float64            \n",
      " 18  net_earnings             0 non-null      float64            \n",
      " 19  roi                      0 non-null      float64            \n",
      " 20  staking                  0 non-null      object             \n",
      " 21  request_id               0 non-null      object             \n",
      " 22  request_block            0 non-null      object             \n",
      " 23  prompt_request           0 non-null      object             \n",
      " 24  tool                     0 non-null      object             \n",
      " 25  nonce                    0 non-null      object             \n",
      " 26  deliver_block            0 non-null      object             \n",
      " 27  error                    0 non-null      int64              \n",
      " 28  error_message            0 non-null      object             \n",
      " 29  prompt_response          0 non-null      object             \n",
      " 30  mech_address             0 non-null      object             \n",
      " 31  p_yes                    0 non-null      float64            \n",
      " 32  p_no                     0 non-null      float64            \n",
      " 33  confidence               0 non-null      float64            \n",
      " 34  info_utility             0 non-null      float64            \n",
      " 35  vote                     0 non-null      object             \n",
      " 36  win_probability          0 non-null      float64            \n",
      " 37  market_creator_y         0 non-null      object             \n",
      " 38  currentAnswer            0 non-null      object             \n",
      " 39  request_time             0 non-null      object             \n",
      " 40  request_month_year       0 non-null      object             \n",
      " 41  request_month_year_week  0 non-null      object             \n",
      "dtypes: bool(3), datetime64[ns, UTC](1), float64(13), int64(3), object(22)\n",
      "memory usage: 132.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "superforcaster_trades.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Will the emergency public warning tests planned by Russia on Wednesday be successful?'"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_trades.iloc[0].title"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Will any regulatory body impose penalties on CrowdStrike regarding the software update issue by October 31, 2024?'"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tools_superforcaster.iloc[0].title"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "superforcaster_titles = tools_superforcaster.title.unique()\n",
    "for title in superforcaster_titles:\n",
    "    all_trades_title = all_trades.loc[all_trades[\"title\"]== title]\n",
    "    superforcaster_data = tools_superforcaster.loc[tools_superforcaster[\"title\"]==title]\n",
    "    matched_data = pd.merge(all_trades_title,superforcaster_data, on=[\"trader_address\"], how=\"inner\")\n",
    "    if len(matched_data) > 0 :\n",
    "        print(matched_data.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "hf_dashboards",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}