calculating_leakage_loss.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Calculating potential loss of transaction intent leakage\n",
    "\n",
    "- Input: a list of transaction hashes that we think have been leaked \n",
    "- Output: a file with the potential loss in dollars for each transaction as well as the total potential loss in dollar, ethereum, and original coin used in the transaction.\n",
    "\n",
    "## The methodology: \n",
    "1. We have a list of transaction hashes that we think have been leaked \n",
    "2. We get the details of this transaction using the infura and Etherscan APIs\n",
    "3. We simulate the result of each transaction if it would have been top of the block using the Tenderly API\n",
    "4. We calculate the difference in dollars for each transaction\n",
    "5. We sum the potential loss of each transaction to get the total potential loss in dollars. \n",
    "\n",
    "## Usage\n",
    "1. Have a csv file with one column called 'user_tx' having all the transactions hashes that you think were leeked inside the folder data/input\n",
    "2. open the config_example.py file and follow the instructions for the configuration\n",
    "3. Intall the requirements in your environment ``` pip install -r requirements.txt ``` \n",
    "4. run the Jupyter Notebook. The output file is called data/results/<INCIDENT_NAME>_final_results.csv - but the Jupyter Notebook is full of interesting information being printed out.\n",
    "\n",
    "## Final results data catalog\n",
    "\n",
    "The final result file has the following columns\n",
    "| Column | Meaning |\n",
    "| :--- | :--- |\n",
    "| ```tx_hash``` | the transaction hash of the potentially leaked transaction |\n",
    "| ```sender``` | address which initiated the transaction |\n",
    "| ```delta_eth``` | this is the potential loss for that transaction, it is the difference in ethereum between the value of the transaction at top of block vs. at the actual position in the block. A value of -1 means that the leaked transaction cost the sender 1 ethereum |\n",
    "| ```delta_dollar``` | difference in dollars between the value of the transaction at top of block vs. at the actual position in the block |\n",
    "| ```token_name_A``` | name of one of the two tokens exchanged by the sender |\n",
    "| ```token_contract_address_A``` |  token contract address of one of the two tokens exchanged by the sender |\n",
    "| ```delta_token_A``` | difference in token A between if the transaction was at top of block vs. at the actual position in the block.  A value of -1 means that the leaked transaction cost the sender 1 token A  |\n",
    "| ```token_name_B``` |  name of one of the two tokens exchanged by the sender |\n",
    "| ```token_contract_address_B``` | token contract address of one of the two tokens exchanged by the sender |\n",
    "| ```delta_token_B``` | difference in token B between if the transaction was at top of block vs. at the actual position in the block.  A value of -1 means that the leaked transaction cost the sender 1 token B\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np \n",
    "import json\n",
    "import requests \n",
    "from configurations import *"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>user_tx</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0x354e8386267ca643793de913739df3f9680895776f3a...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0xee8fd2c76181afa14ca0da158e0a01bba2d3df8e62c5...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0x5f3954cc3cb4fbb88803a910d852aab6566af2866acf...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0xbd9c86df3327871d630a0921c65cb7bec9b0c199d80c...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             user_tx\n",
       "0  0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...\n",
       "1  0x354e8386267ca643793de913739df3f9680895776f3a...\n",
       "2  0xee8fd2c76181afa14ca0da158e0a01bba2d3df8e62c5...\n",
       "3  0x5f3954cc3cb4fbb88803a910d852aab6566af2866acf...\n",
       "4  0xbd9c86df3327871d630a0921c65cb7bec9b0c199d80c..."
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_main = pd.read_csv(csv_file_path, usecols=['user_tx'])\n",
    "#drop the duplicate transaction in case there is any\n",
    "df_main = df_main.drop_duplicates(subset=['user_tx'])\n",
    "df_main.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 37 transactions\n"
     ]
    }
   ],
   "source": [
    "#make a list of transaction hash we need to analyse\n",
    "tx_hash_list = [x for x in df_main['user_tx'].to_list() if pd.notnull(x)]\n",
    "print(f'There are {len(tx_hash_list)} transactions')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## API calls - skip this if you already made them "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### infura\n",
    "With this API, we want to get all the inputs necessary to simulate the transaction again later on. infura gives us all of these inputs except fot the timestamp of the transaction, which is why we need to use the Etherscan API later on."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Call infura for the first transaction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create a df to store all the potenatially problematic transactions and the reason why \n",
    "problematic_transactions = pd.DataFrame(columns=['tx_hash or block', 'where_problem_happened'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>accessList</th>\n",
       "      <th>blockHash</th>\n",
       "      <th>blockNumber</th>\n",
       "      <th>chainId</th>\n",
       "      <th>from</th>\n",
       "      <th>gas</th>\n",
       "      <th>gasPrice</th>\n",
       "      <th>hash</th>\n",
       "      <th>input</th>\n",
       "      <th>maxFeePerGas</th>\n",
       "      <th>maxPriorityFeePerGas</th>\n",
       "      <th>nonce</th>\n",
       "      <th>r</th>\n",
       "      <th>s</th>\n",
       "      <th>to</th>\n",
       "      <th>transactionIndex</th>\n",
       "      <th>type</th>\n",
       "      <th>v</th>\n",
       "      <th>value</th>\n",
       "      <th>yParity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>None</td>\n",
       "      <td>0x2c1ca96b35f726a2171d3ebc050e390aca8dd1f83a23...</td>\n",
       "      <td>0x1283433</td>\n",
       "      <td>0x1</td>\n",
       "      <td>0x36a2ffb33c1b427c46c3d30adac3ca4e8ed36179</td>\n",
       "      <td>0x30eb5</td>\n",
       "      <td>0x11cd060113</td>\n",
       "      <td>0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...</td>\n",
       "      <td>0x4a25d94a000000000000000000000000000000000000...</td>\n",
       "      <td>0x187a3c7e80</td>\n",
       "      <td>0x5e69ec0</td>\n",
       "      <td>0x707</td>\n",
       "      <td>0xad8743a8c08357fb122fe099ce4ab1cc5155197bf17d...</td>\n",
       "      <td>0x6ac3aca96164f1bf4582dc771aa945939eccac0c562a...</td>\n",
       "      <td>0x7a250d5630b4cf539739df2c5dacb4c659f2488d</td>\n",
       "      <td>0x2</td>\n",
       "      <td>0x2</td>\n",
       "      <td>0x0</td>\n",
       "      <td>0x0</td>\n",
       "      <td>0x0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  accessList                                          blockHash blockNumber  \\\n",
       "0       None  0x2c1ca96b35f726a2171d3ebc050e390aca8dd1f83a23...   0x1283433   \n",
       "\n",
       "  chainId                                        from      gas      gasPrice  \\\n",
       "0     0x1  0x36a2ffb33c1b427c46c3d30adac3ca4e8ed36179  0x30eb5  0x11cd060113   \n",
       "\n",
       "                                                hash  \\\n",
       "0  0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...   \n",
       "\n",
       "                                               input  maxFeePerGas  \\\n",
       "0  0x4a25d94a000000000000000000000000000000000000...  0x187a3c7e80   \n",
       "\n",
       "  maxPriorityFeePerGas  nonce  \\\n",
       "0            0x5e69ec0  0x707   \n",
       "\n",
       "                                                   r  \\\n",
       "0  0xad8743a8c08357fb122fe099ce4ab1cc5155197bf17d...   \n",
       "\n",
       "                                                   s  \\\n",
       "0  0x6ac3aca96164f1bf4582dc771aa945939eccac0c562a...   \n",
       "\n",
       "                                           to transactionIndex type    v  \\\n",
       "0  0x7a250d5630b4cf539739df2c5dacb4c659f2488d              0x2  0x2  0x0   \n",
       "\n",
       "  value yParity  \n",
       "0   0x0     0x0  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#the url and the api key are stored in the config file.\n",
    "url = infura_url\n",
    "\n",
    "#Get the infura response for the first transaction in the list to create a dataframe\n",
    "payload = json.dumps({\n",
    "  \"jsonrpc\": \"2.0\",\n",
    "  \"method\": \"eth_getTransactionByHash\",\n",
    "  \"params\": [tx_hash_list[0]],\n",
    "  \"id\": 1\n",
    "})\n",
    "headers = {\n",
    "  'Content-Type': 'application/json'\n",
    "}\n",
    "\n",
    "response = requests.request(\"POST\", url, headers=headers, data=payload)\n",
    "result = response.json()['result']\n",
    "result = {k: None if not v else v for k, v in result.items()} # making sure none of the values are empty\n",
    "df_infura = pd.DataFrame(result, index=[0])\n",
    "df_infura.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Call infura for all other transactions - !! This will take a few minutes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>accessList</th>\n",
       "      <th>blockHash</th>\n",
       "      <th>blockNumber</th>\n",
       "      <th>chainId</th>\n",
       "      <th>from</th>\n",
       "      <th>gas</th>\n",
       "      <th>gasPrice</th>\n",
       "      <th>hash</th>\n",
       "      <th>input</th>\n",
       "      <th>maxFeePerGas</th>\n",
       "      <th>maxPriorityFeePerGas</th>\n",
       "      <th>nonce</th>\n",
       "      <th>r</th>\n",
       "      <th>s</th>\n",
       "      <th>to</th>\n",
       "      <th>transactionIndex</th>\n",
       "      <th>type</th>\n",
       "      <th>v</th>\n",
       "      <th>value</th>\n",
       "      <th>yParity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>None</td>\n",
       "      <td>0x2c1ca96b35f726a2171d3ebc050e390aca8dd1f83a23...</td>\n",
       "      <td>0x1283433</td>\n",
       "      <td>0x1</td>\n",
       "      <td>0x36a2ffb33c1b427c46c3d30adac3ca4e8ed36179</td>\n",
       "      <td>0x30eb5</td>\n",
       "      <td>0x11cd060113</td>\n",
       "      <td>0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...</td>\n",
       "      <td>0x4a25d94a000000000000000000000000000000000000...</td>\n",
       "      <td>0x187a3c7e80</td>\n",
       "      <td>0x5e69ec0</td>\n",
       "      <td>0x707</td>\n",
       "      <td>0xad8743a8c08357fb122fe099ce4ab1cc5155197bf17d...</td>\n",
       "      <td>0x6ac3aca96164f1bf4582dc771aa945939eccac0c562a...</td>\n",
       "      <td>0x7a250d5630b4cf539739df2c5dacb4c659f2488d</td>\n",
       "      <td>0x2</td>\n",
       "      <td>0x2</td>\n",
       "      <td>0x0</td>\n",
       "      <td>0x0</td>\n",
       "      <td>0x0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>None</td>\n",
       "      <td>0xdd26fb3d61779a2527538b20a6710090ef329f09a3c0...</td>\n",
       "      <td>0x128343e</td>\n",
       "      <td>0x1</td>\n",
       "      <td>0x4ffb89a61a6db0586aff308efcfce39207aed2b2</td>\n",
       "      <td>0x59970</td>\n",
       "      <td>0x1254a6dbb8</td>\n",
       "      <td>0x354e8386267ca643793de913739df3f9680895776f3a...</td>\n",
       "      <td>0x24856bc3000000000000000000000000000000000000...</td>\n",
       "      <td>0x15bb9caac7</td>\n",
       "      <td>0x854d13a5</td>\n",
       "      <td>0x491</td>\n",
       "      <td>0x498ee90a0565829b8b473ede7b76174226bc1d5ed8ad...</td>\n",
       "      <td>0x2289f4cb7afc50f5db6b839681ae3f5d999e1bcf0eff...</td>\n",
       "      <td>0x3fc91a3afd70395cd496c647d5a6cc9d4b2b7fad</td>\n",
       "      <td>0x1</td>\n",
       "      <td>0x2</td>\n",
       "      <td>0x1</td>\n",
       "      <td>0x0</td>\n",
       "      <td>0x1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>None</td>\n",
       "      <td>0x657b57c868eb09df8631cca1ddd349ee8ca921e33082...</td>\n",
       "      <td>0x1283449</td>\n",
       "      <td>0x1</td>\n",
       "      <td>0xbbf46514de004992d8dcbcec19f02f2e772ab51d</td>\n",
       "      <td>0x4ab7a</td>\n",
       "      <td>0x1172aa4101</td>\n",
       "      <td>0xee8fd2c76181afa14ca0da158e0a01bba2d3df8e62c5...</td>\n",
       "      <td>0x3593564c000000000000000000000000000000000000...</td>\n",
       "      <td>0x11bf4b06ba</td>\n",
       "      <td>0xe57e0</td>\n",
       "      <td>0x121</td>\n",
       "      <td>0xa80847747875f34798ef26d97d4ea1002a6ec099cf85...</td>\n",
       "      <td>0x193addaa244d04a85bfc29027fb9d26c6d5e8d1ab757...</td>\n",
       "      <td>0x3fc91a3afd70395cd496c647d5a6cc9d4b2b7fad</td>\n",
       "      <td>0x2</td>\n",
       "      <td>0x2</td>\n",
       "      <td>0x1</td>\n",
       "      <td>0x0</td>\n",
       "      <td>0x1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>None</td>\n",
       "      <td>0x4163d5676b7bd172a054a5d85dda15ae43563d24ac0c...</td>\n",
       "      <td>0x128344b</td>\n",
       "      <td>0x1</td>\n",
       "      <td>0xaed06a6a9737ac56b5c3c7ecdfb233d70d64fc38</td>\n",
       "      <td>0xe6be</td>\n",
       "      <td>0xfdc73a9d9</td>\n",
       "      <td>0x5f3954cc3cb4fbb88803a910d852aab6566af2866acf...</td>\n",
       "      <td>0x095ea7b3000000000000000000000000000000000022...</td>\n",
       "      <td>0x1469ab8236</td>\n",
       "      <td>0x99d399e3</td>\n",
       "      <td>0x0</td>\n",
       "      <td>0x4f715cf4d4321da48de6d3fcdb07cdfca6b0c28366ff...</td>\n",
       "      <td>0xddc690541719b963ec3ab29167402aa86ca7b0b14bdc...</td>\n",
       "      <td>0xdac17f958d2ee523a2206206994597c13d831ec7</td>\n",
       "      <td>0x4</td>\n",
       "      <td>0x2</td>\n",
       "      <td>0x0</td>\n",
       "      <td>0x0</td>\n",
       "      <td>0x0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>None</td>\n",
       "      <td>0x4163d5676b7bd172a054a5d85dda15ae43563d24ac0c...</td>\n",
       "      <td>0x128344b</td>\n",
       "      <td>0x1</td>\n",
       "      <td>0xaed06a6a9737ac56b5c3c7ecdfb233d70d64fc38</td>\n",
       "      <td>0x4b07c</td>\n",
       "      <td>0xfdc73a9d9</td>\n",
       "      <td>0xbd9c86df3327871d630a0921c65cb7bec9b0c199d80c...</td>\n",
       "      <td>0x24856bc3000000000000000000000000000000000000...</td>\n",
       "      <td>0x1469ab8236</td>\n",
       "      <td>0x99d399e3</td>\n",
       "      <td>0x1</td>\n",
       "      <td>0x86b62356a6073de56805fc48478b472a06a3ccbfb5cc...</td>\n",
       "      <td>0x42c6bb462a201bbc2e2621f591c4a2b7cb293206b369...</td>\n",
       "      <td>0x3fc91a3afd70395cd496c647d5a6cc9d4b2b7fad</td>\n",
       "      <td>0x5</td>\n",
       "      <td>0x2</td>\n",
       "      <td>0x0</td>\n",
       "      <td>0x0</td>\n",
       "      <td>0x0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  accessList                                          blockHash blockNumber  \\\n",
       "0       None  0x2c1ca96b35f726a2171d3ebc050e390aca8dd1f83a23...   0x1283433   \n",
       "0       None  0xdd26fb3d61779a2527538b20a6710090ef329f09a3c0...   0x128343e   \n",
       "0       None  0x657b57c868eb09df8631cca1ddd349ee8ca921e33082...   0x1283449   \n",
       "0       None  0x4163d5676b7bd172a054a5d85dda15ae43563d24ac0c...   0x128344b   \n",
       "0       None  0x4163d5676b7bd172a054a5d85dda15ae43563d24ac0c...   0x128344b   \n",
       "\n",
       "  chainId                                        from      gas      gasPrice  \\\n",
       "0     0x1  0x36a2ffb33c1b427c46c3d30adac3ca4e8ed36179  0x30eb5  0x11cd060113   \n",
       "0     0x1  0x4ffb89a61a6db0586aff308efcfce39207aed2b2  0x59970  0x1254a6dbb8   \n",
       "0     0x1  0xbbf46514de004992d8dcbcec19f02f2e772ab51d  0x4ab7a  0x1172aa4101   \n",
       "0     0x1  0xaed06a6a9737ac56b5c3c7ecdfb233d70d64fc38   0xe6be   0xfdc73a9d9   \n",
       "0     0x1  0xaed06a6a9737ac56b5c3c7ecdfb233d70d64fc38  0x4b07c   0xfdc73a9d9   \n",
       "\n",
       "                                                hash  \\\n",
       "0  0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...   \n",
       "0  0x354e8386267ca643793de913739df3f9680895776f3a...   \n",
       "0  0xee8fd2c76181afa14ca0da158e0a01bba2d3df8e62c5...   \n",
       "0  0x5f3954cc3cb4fbb88803a910d852aab6566af2866acf...   \n",
       "0  0xbd9c86df3327871d630a0921c65cb7bec9b0c199d80c...   \n",
       "\n",
       "                                               input  maxFeePerGas  \\\n",
       "0  0x4a25d94a000000000000000000000000000000000000...  0x187a3c7e80   \n",
       "0  0x24856bc3000000000000000000000000000000000000...  0x15bb9caac7   \n",
       "0  0x3593564c000000000000000000000000000000000000...  0x11bf4b06ba   \n",
       "0  0x095ea7b3000000000000000000000000000000000022...  0x1469ab8236   \n",
       "0  0x24856bc3000000000000000000000000000000000000...  0x1469ab8236   \n",
       "\n",
       "  maxPriorityFeePerGas  nonce  \\\n",
       "0            0x5e69ec0  0x707   \n",
       "0           0x854d13a5  0x491   \n",
       "0              0xe57e0  0x121   \n",
       "0           0x99d399e3    0x0   \n",
       "0           0x99d399e3    0x1   \n",
       "\n",
       "                                                   r  \\\n",
       "0  0xad8743a8c08357fb122fe099ce4ab1cc5155197bf17d...   \n",
       "0  0x498ee90a0565829b8b473ede7b76174226bc1d5ed8ad...   \n",
       "0  0xa80847747875f34798ef26d97d4ea1002a6ec099cf85...   \n",
       "0  0x4f715cf4d4321da48de6d3fcdb07cdfca6b0c28366ff...   \n",
       "0  0x86b62356a6073de56805fc48478b472a06a3ccbfb5cc...   \n",
       "\n",
       "                                                   s  \\\n",
       "0  0x6ac3aca96164f1bf4582dc771aa945939eccac0c562a...   \n",
       "0  0x2289f4cb7afc50f5db6b839681ae3f5d999e1bcf0eff...   \n",
       "0  0x193addaa244d04a85bfc29027fb9d26c6d5e8d1ab757...   \n",
       "0  0xddc690541719b963ec3ab29167402aa86ca7b0b14bdc...   \n",
       "0  0x42c6bb462a201bbc2e2621f591c4a2b7cb293206b369...   \n",
       "\n",
       "                                           to transactionIndex type    v  \\\n",
       "0  0x7a250d5630b4cf539739df2c5dacb4c659f2488d              0x2  0x2  0x0   \n",
       "0  0x3fc91a3afd70395cd496c647d5a6cc9d4b2b7fad              0x1  0x2  0x1   \n",
       "0  0x3fc91a3afd70395cd496c647d5a6cc9d4b2b7fad              0x2  0x2  0x1   \n",
       "0  0xdac17f958d2ee523a2206206994597c13d831ec7              0x4  0x2  0x0   \n",
       "0  0x3fc91a3afd70395cd496c647d5a6cc9d4b2b7fad              0x5  0x2  0x0   \n",
       "\n",
       "  value yParity  \n",
       "0   0x0     0x0  \n",
       "0   0x0     0x1  \n",
       "0   0x0     0x1  \n",
       "0   0x0     0x0  \n",
       "0   0x0     0x0  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get the infura response for all the other transactions in the list and append the results to the above dataframe\n",
    "for tx_hash in tx_hash_list[1:]:\n",
    "  payload = json.dumps({\n",
    "    \"jsonrpc\": \"2.0\",\n",
    "    \"method\": \"eth_getTransactionByHash\",\n",
    "    \"params\": [tx_hash],\n",
    "    \"id\": 1\n",
    "  })\n",
    "  headers = {\n",
    "    'Content-Type': 'application/json'\n",
    "  }\n",
    "\n",
    "  response = requests.request(\"POST\", url, headers=headers, data=payload)\n",
    "\n",
    "  if response.ok:\n",
    "    result = response.json()['result']\n",
    "    result = {k: None if not v else v for k, v in result.items()} # making sure none of the values are empty\n",
    "    df_temp = pd.DataFrame(result, index=[0])\n",
    "    df_infura = pd.concat([df_infura, df_temp])\n",
    "    \n",
    "  else: \n",
    "    print(f\"error code {response.status_code} for transaction {tx_hash}\")\n",
    "    new_row = {'tx_hash or block':  tx_hash, 'where_problem_happened': 'infura'}\n",
    "    problematic_transactions = pd.concat([problematic_transactions, pd.DataFrame([new_row])], ignore_index=True)\n",
    "\n",
    "df_infura.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "there were 0 transactions where the infura api call did not work\n"
     ]
    }
   ],
   "source": [
    "print(f\"there were {len(problematic_transactions)} transactions where the infura api call did not work\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### merge infura response to main"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['user_tx', 'accessList', 'blockHash', 'blockNumber', 'chainId', 'from',\n",
       "       'gas', 'gasPrice', 'hash', 'input', 'maxFeePerGas',\n",
       "       'maxPriorityFeePerGas', 'nonce', 'r', 's', 'to', 'transactionIndex',\n",
       "       'type', 'v', 'value', 'yParity'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_main = df_main.merge(df_infura, left_on = 'user_tx', right_on = 'hash', how ='outer')\n",
    "df_main.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### save the api results\n",
    "Store the results in a file so that we do not have to rerun the calls if we need this data again"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_main.to_csv(f'data/intermediary/{name_of_incident}_infura.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# read the file so if we want to rerun the notebook, there is no need to remake the infura calls\n",
    "df_main = pd.read_csv(f'data/intermediary/{name_of_incident}_infura.csv', index_col=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Etherscan \n",
    "We use this API to have the timestamp of the blocks rather than the transactions themselves to reduce the amount of API calls (tx and block time are the same for all tx in the block). We need the timestamp of the transactions because in the Tenderly API, if we do not override the timestamp, then it uses the current time as input variable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>user_tx</th>\n",
       "      <th>accessList</th>\n",
       "      <th>blockHash</th>\n",
       "      <th>blockNumber</th>\n",
       "      <th>chainId</th>\n",
       "      <th>from</th>\n",
       "      <th>gas</th>\n",
       "      <th>gasPrice</th>\n",
       "      <th>hash</th>\n",
       "      <th>input</th>\n",
       "      <th>...</th>\n",
       "      <th>nonce</th>\n",
       "      <th>r</th>\n",
       "      <th>s</th>\n",
       "      <th>to</th>\n",
       "      <th>transactionIndex</th>\n",
       "      <th>type</th>\n",
       "      <th>v</th>\n",
       "      <th>value</th>\n",
       "      <th>yParity</th>\n",
       "      <th>block_number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0x012778bb6330737bed53ca488e582500498d81e1db22...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0x85c6415cc35155eeabd6cd71e6814582f9dee28596a3...</td>\n",
       "      <td>0x12834ae</td>\n",
       "      <td>0x1</td>\n",
       "      <td>0x9ea02f652955b90c0dd4f256003e4e339d3a4184</td>\n",
       "      <td>0x352dd</td>\n",
       "      <td>0x14c7b6ec80</td>\n",
       "      <td>0x012778bb6330737bed53ca488e582500498d81e1db22...</td>\n",
       "      <td>0x3593564c000000000000000000000000000000000000...</td>\n",
       "      <td>...</td>\n",
       "      <td>0x0</td>\n",
       "      <td>0x383371f3a3fba29c35eeb4b29752ed5c7fc85c826c6d...</td>\n",
       "      <td>0xbe9f8b295d3edc205639cd2d7e275abbd9de4f89d567...</td>\n",
       "      <td>0x3fc91a3afd70395cd496c647d5a6cc9d4b2b7fad</td>\n",
       "      <td>0x2</td>\n",
       "      <td>0x0</td>\n",
       "      <td>0x26</td>\n",
       "      <td>0x2c68af0bb140000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19412142</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0xfa109ad08c3f05907ff7a33b2d91aa879d90a58108f8...</td>\n",
       "      <td>0x128344e</td>\n",
       "      <td>0x1</td>\n",
       "      <td>0x76ec733f445358232ea24aaf03d4536057439bfc</td>\n",
       "      <td>0x2cf7c</td>\n",
       "      <td>0x1160c85b1a</td>\n",
       "      <td>0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...</td>\n",
       "      <td>0x3593564c000000000000000000000000000000000000...</td>\n",
       "      <td>...</td>\n",
       "      <td>0xd</td>\n",
       "      <td>0x92c2fc3e1f4913b5a6350b581a783752f52d68550409...</td>\n",
       "      <td>0x5d87ebb02ea6728d51e088b3189cad517f06ec29f256...</td>\n",
       "      <td>0x3fc91a3afd70395cd496c647d5a6cc9d4b2b7fad</td>\n",
       "      <td>0x2</td>\n",
       "      <td>0x2</td>\n",
       "      <td>0x1</td>\n",
       "      <td>0x29a2241af62c0000</td>\n",
       "      <td>0x1</td>\n",
       "      <td>19412046</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0x1a5eafe643fb4e41e4666f045341584ea22bb73d5857...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0x38aaa61c38a7d204d23dee4dc36870bf024d67e428da...</td>\n",
       "      <td>0x12834bc</td>\n",
       "      <td>0x1</td>\n",
       "      <td>0x30049fd3dde7a46a6441a0d41dcb1cf5aaa93e77</td>\n",
       "      <td>0x2e24f</td>\n",
       "      <td>0x1339037b82</td>\n",
       "      <td>0x1a5eafe643fb4e41e4666f045341584ea22bb73d5857...</td>\n",
       "      <td>0x24856bc3000000000000000000000000000000000000...</td>\n",
       "      <td>...</td>\n",
       "      <td>0xe</td>\n",
       "      <td>0xada86d5a8be88ba11516d207073bd78ddd217519b75a...</td>\n",
       "      <td>0x1d1c892a228966aab4de1b132639834f304b8e1988f6...</td>\n",
       "      <td>0x3fc91a3afd70395cd496c647d5a6cc9d4b2b7fad</td>\n",
       "      <td>0x4</td>\n",
       "      <td>0x2</td>\n",
       "      <td>0x0</td>\n",
       "      <td>0x0</td>\n",
       "      <td>0x0</td>\n",
       "      <td>19412156</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0x1bcc89149f51daa2d79355a340d576af22555be34567...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0xaed11f387b43ccea87b2ad2a65e29e9235a452edd1eb...</td>\n",
       "      <td>0x128345f</td>\n",
       "      <td>0x1</td>\n",
       "      <td>0xc4ba8f21c0dd755cfeff899c4f791c634ca5c6db</td>\n",
       "      <td>0x28a31</td>\n",
       "      <td>0x117a8b7f8d</td>\n",
       "      <td>0x1bcc89149f51daa2d79355a340d576af22555be34567...</td>\n",
       "      <td>0x3593564c000000000000000000000000000000000000...</td>\n",
       "      <td>...</td>\n",
       "      <td>0x37d</td>\n",
       "      <td>0xfd94d547d9253e13977325ad8430ac84663c5520cdac...</td>\n",
       "      <td>0x7739e932e873075d6503aae95c1985825962aeadc9dc...</td>\n",
       "      <td>0x3fc91a3afd70395cd496c647d5a6cc9d4b2b7fad</td>\n",
       "      <td>0x2</td>\n",
       "      <td>0x2</td>\n",
       "      <td>0x1</td>\n",
       "      <td>0x1e87f85809dc0000</td>\n",
       "      <td>0x1</td>\n",
       "      <td>19412063</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0x2c1ca96b35f726a2171d3ebc050e390aca8dd1f83a23...</td>\n",
       "      <td>0x1283433</td>\n",
       "      <td>0x1</td>\n",
       "      <td>0x36a2ffb33c1b427c46c3d30adac3ca4e8ed36179</td>\n",
       "      <td>0x30eb5</td>\n",
       "      <td>0x11cd060113</td>\n",
       "      <td>0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...</td>\n",
       "      <td>0x4a25d94a000000000000000000000000000000000000...</td>\n",
       "      <td>...</td>\n",
       "      <td>0x707</td>\n",
       "      <td>0xad8743a8c08357fb122fe099ce4ab1cc5155197bf17d...</td>\n",
       "      <td>0x6ac3aca96164f1bf4582dc771aa945939eccac0c562a...</td>\n",
       "      <td>0x7a250d5630b4cf539739df2c5dacb4c659f2488d</td>\n",
       "      <td>0x2</td>\n",
       "      <td>0x2</td>\n",
       "      <td>0x0</td>\n",
       "      <td>0x0</td>\n",
       "      <td>0x0</td>\n",
       "      <td>19412019</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             user_tx  accessList  \\\n",
       "0  0x012778bb6330737bed53ca488e582500498d81e1db22...         NaN   \n",
       "1  0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...         NaN   \n",
       "2  0x1a5eafe643fb4e41e4666f045341584ea22bb73d5857...         NaN   \n",
       "3  0x1bcc89149f51daa2d79355a340d576af22555be34567...         NaN   \n",
       "4  0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...         NaN   \n",
       "\n",
       "                                           blockHash blockNumber chainId  \\\n",
       "0  0x85c6415cc35155eeabd6cd71e6814582f9dee28596a3...   0x12834ae     0x1   \n",
       "1  0xfa109ad08c3f05907ff7a33b2d91aa879d90a58108f8...   0x128344e     0x1   \n",
       "2  0x38aaa61c38a7d204d23dee4dc36870bf024d67e428da...   0x12834bc     0x1   \n",
       "3  0xaed11f387b43ccea87b2ad2a65e29e9235a452edd1eb...   0x128345f     0x1   \n",
       "4  0x2c1ca96b35f726a2171d3ebc050e390aca8dd1f83a23...   0x1283433     0x1   \n",
       "\n",
       "                                         from      gas      gasPrice  \\\n",
       "0  0x9ea02f652955b90c0dd4f256003e4e339d3a4184  0x352dd  0x14c7b6ec80   \n",
       "1  0x76ec733f445358232ea24aaf03d4536057439bfc  0x2cf7c  0x1160c85b1a   \n",
       "2  0x30049fd3dde7a46a6441a0d41dcb1cf5aaa93e77  0x2e24f  0x1339037b82   \n",
       "3  0xc4ba8f21c0dd755cfeff899c4f791c634ca5c6db  0x28a31  0x117a8b7f8d   \n",
       "4  0x36a2ffb33c1b427c46c3d30adac3ca4e8ed36179  0x30eb5  0x11cd060113   \n",
       "\n",
       "                                                hash  \\\n",
       "0  0x012778bb6330737bed53ca488e582500498d81e1db22...   \n",
       "1  0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...   \n",
       "2  0x1a5eafe643fb4e41e4666f045341584ea22bb73d5857...   \n",
       "3  0x1bcc89149f51daa2d79355a340d576af22555be34567...   \n",
       "4  0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...   \n",
       "\n",
       "                                               input  ...  nonce  \\\n",
       "0  0x3593564c000000000000000000000000000000000000...  ...    0x0   \n",
       "1  0x3593564c000000000000000000000000000000000000...  ...    0xd   \n",
       "2  0x24856bc3000000000000000000000000000000000000...  ...    0xe   \n",
       "3  0x3593564c000000000000000000000000000000000000...  ...  0x37d   \n",
       "4  0x4a25d94a000000000000000000000000000000000000...  ...  0x707   \n",
       "\n",
       "                                                   r  \\\n",
       "0  0x383371f3a3fba29c35eeb4b29752ed5c7fc85c826c6d...   \n",
       "1  0x92c2fc3e1f4913b5a6350b581a783752f52d68550409...   \n",
       "2  0xada86d5a8be88ba11516d207073bd78ddd217519b75a...   \n",
       "3  0xfd94d547d9253e13977325ad8430ac84663c5520cdac...   \n",
       "4  0xad8743a8c08357fb122fe099ce4ab1cc5155197bf17d...   \n",
       "\n",
       "                                                   s  \\\n",
       "0  0xbe9f8b295d3edc205639cd2d7e275abbd9de4f89d567...   \n",
       "1  0x5d87ebb02ea6728d51e088b3189cad517f06ec29f256...   \n",
       "2  0x1d1c892a228966aab4de1b132639834f304b8e1988f6...   \n",
       "3  0x7739e932e873075d6503aae95c1985825962aeadc9dc...   \n",
       "4  0x6ac3aca96164f1bf4582dc771aa945939eccac0c562a...   \n",
       "\n",
       "                                           to transactionIndex type     v  \\\n",
       "0  0x3fc91a3afd70395cd496c647d5a6cc9d4b2b7fad              0x2  0x0  0x26   \n",
       "1  0x3fc91a3afd70395cd496c647d5a6cc9d4b2b7fad              0x2  0x2   0x1   \n",
       "2  0x3fc91a3afd70395cd496c647d5a6cc9d4b2b7fad              0x4  0x2   0x0   \n",
       "3  0x3fc91a3afd70395cd496c647d5a6cc9d4b2b7fad              0x2  0x2   0x1   \n",
       "4  0x7a250d5630b4cf539739df2c5dacb4c659f2488d              0x2  0x2   0x0   \n",
       "\n",
       "                value yParity block_number  \n",
       "0   0x2c68af0bb140000     NaN     19412142  \n",
       "1  0x29a2241af62c0000     0x1     19412046  \n",
       "2                 0x0     0x0     19412156  \n",
       "3  0x1e87f85809dc0000     0x1     19412063  \n",
       "4                 0x0     0x0     19412019  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#converting the hexadecimal string into a decimal number\n",
    "def hex_to_decimal(hex_str):\n",
    "    return int(hex_str, 16)\n",
    "\n",
    "df_main['block_number'] = df_main['blockNumber'].apply(hex_to_decimal)\n",
    "df_main.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "there are 30 different blocks\n"
     ]
    }
   ],
   "source": [
    "#getting all the block numbers of the transactions we want to analyse\n",
    "block_number_list = list(set([x for x in df_main['block_number'].to_list() if pd.notnull(x)]))\n",
    "print(f'there are {len(block_number_list)} different blocks')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Get the ehterscan response for the first block in the list to create a dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>blockNumber</th>\n",
       "      <th>timeStamp</th>\n",
       "      <th>blockMiner</th>\n",
       "      <th>blockReward</th>\n",
       "      <th>uncles</th>\n",
       "      <th>uncleInclusionReward</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>19412096</td>\n",
       "      <td>1710162983</td>\n",
       "      <td>0x95222290dd7278aa3ddd389cc1e1d165cc4bafe5</td>\n",
       "      <td>85616354749290394</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  blockNumber   timeStamp                                  blockMiner  \\\n",
       "0    19412096  1710162983  0x95222290dd7278aa3ddd389cc1e1d165cc4bafe5   \n",
       "\n",
       "         blockReward uncles uncleInclusionReward  \n",
       "0  85616354749290394   None                    0  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "url_eth = f\"https://api.etherscan.io/api?module=block&action=getblockreward&blockno={block_number_list[0]}&apikey={eth_scan_api_key}\"\n",
    "\n",
    "response_eth = requests.request(\"POST\", url_eth)\n",
    "\n",
    "result = response_eth.json()['result']\n",
    "result = {k: None if not v else v for k, v in result.items()} # making sure none of the values are empty\n",
    "df_eth = pd.DataFrame(result, index=[0])\n",
    "df_eth.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Get the etherscan responses for the other blocks in the list - !!! This will take a few minutes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "for block in block_number_list[1:]:\n",
    "  url_temp = f\"https://api.etherscan.io/api?module=block&action=getblockreward&blockno={block}&apikey={eth_scan_api_key}\"\n",
    "  response_temp = requests.request(\"POST\", url_temp)\n",
    "\n",
    "  if response_temp.ok:\n",
    "    dct_temp = response_temp.json()['result']\n",
    "    dct_temp = {k: None if not v else v for k, v in dct_temp.items()} # making sure none of the values are empty\n",
    "    df_temp = pd.DataFrame(dct_temp, index=[0])\n",
    "    df_eth = pd.concat([df_eth, df_temp])\n",
    "    \n",
    "  else: \n",
    "    print(f\"error code {response_temp.status_code} for block {block}\")\n",
    "    new_row = {'tx_hash or block':  block, 'where_problem_happened': 'etherscan. This is a block number'}\n",
    "    problematic_transactions = pd.concat([problematic_transactions, pd.DataFrame([new_row])], ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "there were 0 blocks where the etherscan api call did not work\n"
     ]
    }
   ],
   "source": [
    "print(f\"there were {len(problematic_transactions[problematic_transactions['where_problem_happened'] ==  'etherscan. This is a block number'])} blocks where the etherscan api call did not work\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "# quick cleaning for later\n",
    "df_eth['blockNumber'] = df_eth['blockNumber'].astype(int)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### merge etherscan results to main"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['user_tx', 'accessList', 'blockHash', 'blockNumber_x', 'chainId',\n",
       "       'from', 'gas', 'gasPrice', 'hash', 'input', 'maxFeePerGas',\n",
       "       'maxPriorityFeePerGas', 'nonce', 'r', 's', 'to', 'transactionIndex',\n",
       "       'type', 'v', 'value', 'yParity', 'block_number', 'blockNumber_y',\n",
       "       'timeStamp', 'blockMiner', 'blockReward', 'uncles',\n",
       "       'uncleInclusionReward'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_main = df_main.merge(df_eth, left_on = 'block_number', right_on = 'blockNumber', how ='outer')\n",
    "df_main.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### save the results of etherscan \n",
    "Store the results in a file so that we do not have to rerun the calls if we need this data again\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_main.to_csv(f'data/intermediary/{name_of_incident}_etherscan.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_main = pd.read_csv(f'data/intermediary/{name_of_incident}_etherscan.csv', index_col=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Tenderly !! This will take a few minutes\n",
    "Here we finally do the simulation. We do it once at the original index position to get the amount of coin transferred originally. Then we do it again at index position 0."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "headers = {\n",
    "    'X-Access-Key': f'{tenderly_access_token}',\n",
    "    'content-type': 'application/json',\n",
    "}\n",
    "\n",
    "#creating an empty DataFrame for the results\n",
    "columns = ['tx_hash', 'index', 'type', 'raw_amount', 'dollar_value', 'token_contract_address', 'token_name', 'token_dollar_value', 'from', 'to', 'sender', 'timestamp']\n",
    "df_results = pd.DataFrame(columns = columns)\n",
    "\n",
    "\n",
    "#iterating over every row of the main dataframe (one row is one transaction)\n",
    "for index, row in df_main.iterrows():\n",
    "    tx_index_list = [0]\n",
    "    tx_index_list.append(int(row['transactionIndex'], 0))\n",
    "    # for each transaction, simulate twice: once for each index\n",
    "    for tx_index in tx_index_list:\n",
    "        json_data = {\n",
    "        'network_id': int(row['chainId'], 0),\n",
    "        'from': row['from'],\n",
    "        'to': row['to'],\n",
    "        'input': row['input'],\n",
    "        'block_number': row['block_number'],\n",
    "        'transaction_index': tx_index,\n",
    "        'simulation_type': 'quick',\n",
    "        'gas': int(row['gas'], 0),\n",
    "        'value': int(row['value'], 0),\n",
    "        'gas_price': int(row['gasPrice'], 0),\n",
    "        'l1_timestamp': int(row['timeStamp'])\n",
    "        }\n",
    "        \n",
    "        response = requests.post(\n",
    "        tenderly_api_url,\n",
    "        headers=headers,\n",
    "        json=json_data,\n",
    "        )\n",
    "\n",
    "        try:\n",
    "            for data in response.json()['transaction']['transaction_info']['asset_changes']:\n",
    "                tx_type = data['type']\n",
    "                tx_raw_amount = data['raw_amount']\n",
    "                tx_dollar_value = data['dollar_value']\n",
    "                sender = response.json()['transaction']['from']\n",
    "\n",
    "                #sometimes the following values are empty \n",
    "                try:\n",
    "                    contract_address = data['token_info']['contract_address']\n",
    "                except:\n",
    "                    contract_address = 'None'\n",
    "\n",
    "                try:\n",
    "                    token_name = data['token_info']['name']\n",
    "                except:\n",
    "                    token_name = 'None'\n",
    "                try:\n",
    "                    token_dollar_value = data['token_info']['dollar_value']\n",
    "                except:\n",
    "                    token_dollar_value = 'None'\n",
    "\n",
    "                try:\n",
    "                    tx_from = data['from']\n",
    "                except:\n",
    "                    tx_from = 'None'\n",
    "\n",
    "                try:\n",
    "                    tx_to = data['to']\n",
    "                except: \n",
    "                    tx_to = 'None'\n",
    "\n",
    "                new_row = {\n",
    "                    'tx_hash' : row['hash'],\n",
    "                    'from':tx_from,\n",
    "                    'to': tx_to,\n",
    "                    'index' : tx_index, \n",
    "                    'type': tx_type, \n",
    "                    'raw_amount': tx_raw_amount, \n",
    "                    'dollar_value' : tx_dollar_value, \n",
    "                    'token_contract_address': contract_address, \n",
    "                    'token_name': token_name, \n",
    "                    'token_dollar_value': token_dollar_value,\n",
    "                    'sender': sender, \n",
    "                    'timestamp': int(row['timeStamp'])\n",
    "                    }\n",
    "                df_results = pd.concat([df_results, pd.DataFrame([new_row])], ignore_index=True)\n",
    "        except:\n",
    "            new_row = {'tx_hash or block':  row['hash'], 'where_problem_happened': 'tenderly'}\n",
    "            problematic_transactions = pd.concat([problematic_transactions, pd.DataFrame([new_row])], ignore_index=True)\n",
    "            \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### creating a new dataframe with only the transactions where tenderly api returned something.\n",
    "This meanse that there are 2 index values for these good transactions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>tx_hash</th>\n",
       "      <th>index</th>\n",
       "      <th>type</th>\n",
       "      <th>raw_amount</th>\n",
       "      <th>dollar_value</th>\n",
       "      <th>token_contract_address</th>\n",
       "      <th>token_name</th>\n",
       "      <th>token_dollar_value</th>\n",
       "      <th>from</th>\n",
       "      <th>to</th>\n",
       "      <th>sender</th>\n",
       "      <th>timestamp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...</td>\n",
       "      <td>0</td>\n",
       "      <td>Transfer</td>\n",
       "      <td>807452531860501402532</td>\n",
       "      <td>None</td>\n",
       "      <td>0xb0699d63aef20df3f1cffa9ca2bb8670416271d2</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0x36a2ffb33c1b427c46c3d30adac3ca4e8ed36179</td>\n",
       "      <td>0xe1ebdf64f7f3a31723e767a561345f958233bb7d</td>\n",
       "      <td>0x36a2ffb33c1b427c46c3d30adac3ca4e8ed36179</td>\n",
       "      <td>1710162059</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...</td>\n",
       "      <td>0</td>\n",
       "      <td>Transfer</td>\n",
       "      <td>400000000000000000</td>\n",
       "      <td>1241.7200195312500001</td>\n",
       "      <td>0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2</td>\n",
       "      <td>WETH</td>\n",
       "      <td>3104.300048828125</td>\n",
       "      <td>0xe1ebdf64f7f3a31723e767a561345f958233bb7d</td>\n",
       "      <td>0x7a250d5630b4cf539739df2c5dacb4c659f2488d</td>\n",
       "      <td>0x36a2ffb33c1b427c46c3d30adac3ca4e8ed36179</td>\n",
       "      <td>1710162059</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...</td>\n",
       "      <td>0</td>\n",
       "      <td>Burn</td>\n",
       "      <td>400000000000000000</td>\n",
       "      <td>1241.7200195312500001</td>\n",
       "      <td>0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2</td>\n",
       "      <td>WETH</td>\n",
       "      <td>3104.300048828125</td>\n",
       "      <td>0x7a250d5630b4cf539739df2c5dacb4c659f2488d</td>\n",
       "      <td>None</td>\n",
       "      <td>0x36a2ffb33c1b427c46c3d30adac3ca4e8ed36179</td>\n",
       "      <td>1710162059</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...</td>\n",
       "      <td>0</td>\n",
       "      <td>Transfer</td>\n",
       "      <td>400000000000000000</td>\n",
       "      <td>1241.3440429687500001</td>\n",
       "      <td>None</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>3103.360107421875</td>\n",
       "      <td>0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2</td>\n",
       "      <td>0x7a250d5630b4cf539739df2c5dacb4c659f2488d</td>\n",
       "      <td>0x36a2ffb33c1b427c46c3d30adac3ca4e8ed36179</td>\n",
       "      <td>1710162059</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...</td>\n",
       "      <td>0</td>\n",
       "      <td>Transfer</td>\n",
       "      <td>400000000000000000</td>\n",
       "      <td>1241.3440429687500001</td>\n",
       "      <td>None</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>3103.360107421875</td>\n",
       "      <td>0x7a250d5630b4cf539739df2c5dacb4c659f2488d</td>\n",
       "      <td>0x36a2ffb33c1b427c46c3d30adac3ca4e8ed36179</td>\n",
       "      <td>0x36a2ffb33c1b427c46c3d30adac3ca4e8ed36179</td>\n",
       "      <td>1710162059</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             tx_hash index      type  \\\n",
       "0  0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...     0  Transfer   \n",
       "1  0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...     0  Transfer   \n",
       "2  0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...     0      Burn   \n",
       "3  0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...     0  Transfer   \n",
       "4  0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...     0  Transfer   \n",
       "\n",
       "              raw_amount           dollar_value  \\\n",
       "0  807452531860501402532                   None   \n",
       "1     400000000000000000  1241.7200195312500001   \n",
       "2     400000000000000000  1241.7200195312500001   \n",
       "3     400000000000000000  1241.3440429687500001   \n",
       "4     400000000000000000  1241.3440429687500001   \n",
       "\n",
       "                       token_contract_address token_name token_dollar_value  \\\n",
       "0  0xb0699d63aef20df3f1cffa9ca2bb8670416271d2       None               None   \n",
       "1  0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2       WETH  3104.300048828125   \n",
       "2  0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2       WETH  3104.300048828125   \n",
       "3                                        None   Ethereum  3103.360107421875   \n",
       "4                                        None   Ethereum  3103.360107421875   \n",
       "\n",
       "                                         from  \\\n",
       "0  0x36a2ffb33c1b427c46c3d30adac3ca4e8ed36179   \n",
       "1  0xe1ebdf64f7f3a31723e767a561345f958233bb7d   \n",
       "2  0x7a250d5630b4cf539739df2c5dacb4c659f2488d   \n",
       "3  0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2   \n",
       "4  0x7a250d5630b4cf539739df2c5dacb4c659f2488d   \n",
       "\n",
       "                                           to  \\\n",
       "0  0xe1ebdf64f7f3a31723e767a561345f958233bb7d   \n",
       "1  0x7a250d5630b4cf539739df2c5dacb4c659f2488d   \n",
       "2                                        None   \n",
       "3  0x7a250d5630b4cf539739df2c5dacb4c659f2488d   \n",
       "4  0x36a2ffb33c1b427c46c3d30adac3ca4e8ed36179   \n",
       "\n",
       "                                       sender   timestamp  \n",
       "0  0x36a2ffb33c1b427c46c3d30adac3ca4e8ed36179  1710162059  \n",
       "1  0x36a2ffb33c1b427c46c3d30adac3ca4e8ed36179  1710162059  \n",
       "2  0x36a2ffb33c1b427c46c3d30adac3ca4e8ed36179  1710162059  \n",
       "3  0x36a2ffb33c1b427c46c3d30adac3ca4e8ed36179  1710162059  \n",
       "4  0x36a2ffb33c1b427c46c3d30adac3ca4e8ed36179  1710162059  "
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped = df_results.groupby('tx_hash')\n",
    "df_results_good = grouped.filter(lambda x: x['index'].nunique() == 2)\n",
    "df_results_good.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "number of transactions for which tenderly worked: 28 \n",
      "number of transactions for which tenderly did not worked: 9 \n",
      "percentage of properly simulated transactions with tenderly: 75.68%\n"
     ]
    }
   ],
   "source": [
    "print(\n",
    "    'number of transactions for which tenderly worked:', df_results_good.tx_hash.nunique(),\n",
    "    \"\\nnumber of transactions for which tenderly did not worked:\", problematic_transactions[problematic_transactions['where_problem_happened'] == 'tenderly']['tx_hash or block'].nunique(),\n",
    "    '\\npercentage of properly simulated transactions with tenderly:', format(df_results_good.tx_hash.nunique()/(df_results_good.tx_hash.nunique() +  problematic_transactions[problematic_transactions['where_problem_happened'] == 'tenderly']['tx_hash or block'].nunique()), \".2%\")\n",
    "    )\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Save the tenderly results "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "#saving the transactions and it's simulated data where the tenderly api worked\n",
    "df_results_good.to_csv(f'data/intermediary/{name_of_incident}_tenderly.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Save the problematic transaction/block "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "problematic_transactions.to_csv(f'data/results/{name_of_incident}_transactions_or_blocks_with_api_problem.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Wrangling"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "problematic_transactions = pd.read_csv(f'data/results/{name_of_incident}_transactions_or_blocks_with_api_problem.csv', index_col=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "we are now working with 28 transactions that have been simulated\n"
     ]
    }
   ],
   "source": [
    "df_results_good = pd.read_csv(f'data/intermediary/{name_of_incident}_tenderly.csv', index_col=0)\n",
    "print(f'we are now working with {df_results_good.tx_hash.nunique()} transactions that have been simulated')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Some Cleanup and new columns\n",
    "add some new columns and transform the raw amount of certain tokens that have been returned by tenderly in much bigger amounts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_results_good['raw_amount'] = df_results_good['raw_amount'].astype(float)\n",
    "df_results_good['sender_is_involved'] = (df_results_good['from'] == df_results_good['sender']) |  (df_results_good['to'] == df_results_good['sender'])\n",
    "df_results_good['sender_gave_this'] = (df_results_good['from'] == df_results_good['sender'])\n",
    "df_results_good['token_name'] = df_results_good['token_name'].fillna(df_results_good['token_contract_address'])\n",
    "df_results_good['token_contract_address'] = df_results_good['token_contract_address'].fillna(df_results_good['token_name'])\n",
    "df_results_good['dollar_value_net']  = np.where(df_results_good['sender_gave_this'], - df_results_good['dollar_value'], df_results_good['dollar_value'])\n",
    "\n",
    "df_results_good.loc[df_results_good['token_name'] == 'WETH', 'raw_amount'] /= 1e+18\n",
    "df_results_good.loc[df_results_good['token_name'] == 'Ethereum', 'raw_amount'] /= 1e+18\n",
    "df_results_good.loc[df_results_good['token_name'] == 'USDC', 'raw_amount'] /= 1000000\n",
    "df_results_good.loc[df_results_good['token_name'] == 'Tether', 'raw_amount'] /= 1000000\n",
    "df_results_good.loc[df_results_good['token_name'] == 'Dai', 'raw_amount'] /= 1e+18\n",
    "df_results_good.loc[df_results_good['token_name'] == 'Wrapped Bitcoin', 'raw_amount'] /= 1e+8"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### get only the part of the transactions where the sender was directly involved"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "out of the 28 transactions, we could identify the sender for  28 of them\n"
     ]
    }
   ],
   "source": [
    "df_results_clean = df_results_good.copy()\n",
    "df_senders = df_results_clean[df_results_clean['sender_is_involved']]\n",
    "print(\n",
    "    'out of the', df_results_clean.tx_hash.nunique(), 'transactions,', \n",
    "    'we could identify the sender for ', df_senders.tx_hash.nunique(), 'of them',\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### add the transaction where we can not identify a sender in the problematic transactions df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "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>tx_hash or block</th>\n",
       "      <th>where_problem_happened</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0x6326451b6cee44291395fd3efa1f7144676392ade4f0...</td>\n",
       "      <td>tenderly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0x6326451b6cee44291395fd3efa1f7144676392ade4f0...</td>\n",
       "      <td>tenderly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>0x8f762515bc5eb815f6683323c4226373eb53922f0007...</td>\n",
       "      <td>tenderly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>0xe5be76c827fec38b17f44a4adef3a96553c5c88d017e...</td>\n",
       "      <td>tenderly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>0xe5be76c827fec38b17f44a4adef3a96553c5c88d017e...</td>\n",
       "      <td>tenderly</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     tx_hash or block where_problem_happened\n",
       "8   0x6326451b6cee44291395fd3efa1f7144676392ade4f0...               tenderly\n",
       "9   0x6326451b6cee44291395fd3efa1f7144676392ade4f0...               tenderly\n",
       "10  0x8f762515bc5eb815f6683323c4226373eb53922f0007...               tenderly\n",
       "11  0xe5be76c827fec38b17f44a4adef3a96553c5c88d017e...               tenderly\n",
       "12  0xe5be76c827fec38b17f44a4adef3a96553c5c88d017e...               tenderly"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transaction_with_no_senders = set(df_results_clean.tx_hash.unique()) - set(df_senders.tx_hash.unique())\n",
    "for transaction in transaction_with_no_senders:\n",
    "    new_row = {'tx_hash or block':  transaction, 'where_problem_happened': 'could not identify a sender'}\n",
    "    problematic_transactions = pd.concat([problematic_transactions, pd.DataFrame([new_row])], ignore_index=True)\n",
    "\n",
    "problematic_transactions.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### For each sender, aggregate the value of the tokens that are swapped"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "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>tx_hash</th>\n",
       "      <th>index</th>\n",
       "      <th>token_name</th>\n",
       "      <th>token_contract_address</th>\n",
       "      <th>sender_is_involved</th>\n",
       "      <th>sender_gave_this</th>\n",
       "      <th>sum_raw_amount</th>\n",
       "      <th>sum_dollar_value</th>\n",
       "      <th>token_dollar_value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0x012778bb6330737bed53ca488e582500498d81e1db22...</td>\n",
       "      <td>0</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>2.000000e-01</td>\n",
       "      <td>620.672021</td>\n",
       "      <td>3.103360e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0x012778bb6330737bed53ca488e582500498d81e1db22...</td>\n",
       "      <td>0</td>\n",
       "      <td>KAIJUNO8</td>\n",
       "      <td>0x4fe8d4775b7cb2546b9ee86182081cdf8f77b053</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>1.654453e+28</td>\n",
       "      <td>338.104092</td>\n",
       "      <td>2.043600e-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0x012778bb6330737bed53ca488e582500498d81e1db22...</td>\n",
       "      <td>2</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>2.000000e-01</td>\n",
       "      <td>620.672021</td>\n",
       "      <td>3.103360e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0x012778bb6330737bed53ca488e582500498d81e1db22...</td>\n",
       "      <td>2</td>\n",
       "      <td>KAIJUNO8</td>\n",
       "      <td>0x4fe8d4775b7cb2546b9ee86182081cdf8f77b053</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>1.110371e+28</td>\n",
       "      <td>226.915507</td>\n",
       "      <td>2.043600e-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...</td>\n",
       "      <td>0</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>3.000000e+00</td>\n",
       "      <td>9310.080322</td>\n",
       "      <td>3.103360e+03</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             tx_hash  index token_name  \\\n",
       "0  0x012778bb6330737bed53ca488e582500498d81e1db22...      0   Ethereum   \n",
       "1  0x012778bb6330737bed53ca488e582500498d81e1db22...      0   KAIJUNO8   \n",
       "2  0x012778bb6330737bed53ca488e582500498d81e1db22...      2   Ethereum   \n",
       "3  0x012778bb6330737bed53ca488e582500498d81e1db22...      2   KAIJUNO8   \n",
       "4  0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...      0   Ethereum   \n",
       "\n",
       "                       token_contract_address  sender_is_involved  \\\n",
       "0                                    Ethereum                True   \n",
       "1  0x4fe8d4775b7cb2546b9ee86182081cdf8f77b053                True   \n",
       "2                                    Ethereum                True   \n",
       "3  0x4fe8d4775b7cb2546b9ee86182081cdf8f77b053                True   \n",
       "4                                    Ethereum                True   \n",
       "\n",
       "   sender_gave_this  sum_raw_amount  sum_dollar_value  token_dollar_value  \n",
       "0              True    2.000000e-01        620.672021        3.103360e+03  \n",
       "1             False    1.654453e+28        338.104092        2.043600e-08  \n",
       "2              True    2.000000e-01        620.672021        3.103360e+03  \n",
       "3             False    1.110371e+28        226.915507        2.043600e-08  \n",
       "4              True    3.000000e+00       9310.080322        3.103360e+03  "
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped = df_senders.groupby(['tx_hash', 'index', 'token_name', 'token_contract_address', 'sender_is_involved', 'sender_gave_this'])\n",
    "aggregated_senders = grouped.agg(\n",
    "    sum_raw_amount=('raw_amount', 'sum'),               \n",
    "    sum_dollar_value=('dollar_value', 'sum'),            \n",
    "    token_dollar_value=('token_dollar_value', 'mean')  # all values are the same so first or mean is fine\n",
    ").reset_index()\n",
    "aggregated_senders.head() # one row per tx per token per index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### calculte the net amounts for each sender (negative if the sender paid in this token, positive if the sender received the token)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "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>tx_hash</th>\n",
       "      <th>index</th>\n",
       "      <th>token_name</th>\n",
       "      <th>token_contract_address</th>\n",
       "      <th>sender_is_involved</th>\n",
       "      <th>sender_gave_this</th>\n",
       "      <th>sum_raw_amount</th>\n",
       "      <th>sum_dollar_value</th>\n",
       "      <th>token_dollar_value</th>\n",
       "      <th>raw_amount_net</th>\n",
       "      <th>dollar_value_net</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0x012778bb6330737bed53ca488e582500498d81e1db22...</td>\n",
       "      <td>0</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>2.000000e-01</td>\n",
       "      <td>620.672021</td>\n",
       "      <td>3.103360e+03</td>\n",
       "      <td>2.000000e-01</td>\n",
       "      <td>620.672021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0x012778bb6330737bed53ca488e582500498d81e1db22...</td>\n",
       "      <td>0</td>\n",
       "      <td>KAIJUNO8</td>\n",
       "      <td>0x4fe8d4775b7cb2546b9ee86182081cdf8f77b053</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>1.654453e+28</td>\n",
       "      <td>338.104092</td>\n",
       "      <td>2.043600e-08</td>\n",
       "      <td>-1.654453e+28</td>\n",
       "      <td>-338.104092</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0x012778bb6330737bed53ca488e582500498d81e1db22...</td>\n",
       "      <td>2</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>2.000000e-01</td>\n",
       "      <td>620.672021</td>\n",
       "      <td>3.103360e+03</td>\n",
       "      <td>2.000000e-01</td>\n",
       "      <td>620.672021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0x012778bb6330737bed53ca488e582500498d81e1db22...</td>\n",
       "      <td>2</td>\n",
       "      <td>KAIJUNO8</td>\n",
       "      <td>0x4fe8d4775b7cb2546b9ee86182081cdf8f77b053</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>1.110371e+28</td>\n",
       "      <td>226.915507</td>\n",
       "      <td>2.043600e-08</td>\n",
       "      <td>-1.110371e+28</td>\n",
       "      <td>-226.915507</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...</td>\n",
       "      <td>0</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>3.000000e+00</td>\n",
       "      <td>9310.080322</td>\n",
       "      <td>3.103360e+03</td>\n",
       "      <td>3.000000e+00</td>\n",
       "      <td>9310.080322</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             tx_hash  index token_name  \\\n",
       "0  0x012778bb6330737bed53ca488e582500498d81e1db22...      0   Ethereum   \n",
       "1  0x012778bb6330737bed53ca488e582500498d81e1db22...      0   KAIJUNO8   \n",
       "2  0x012778bb6330737bed53ca488e582500498d81e1db22...      2   Ethereum   \n",
       "3  0x012778bb6330737bed53ca488e582500498d81e1db22...      2   KAIJUNO8   \n",
       "4  0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...      0   Ethereum   \n",
       "\n",
       "                       token_contract_address  sender_is_involved  \\\n",
       "0                                    Ethereum                True   \n",
       "1  0x4fe8d4775b7cb2546b9ee86182081cdf8f77b053                True   \n",
       "2                                    Ethereum                True   \n",
       "3  0x4fe8d4775b7cb2546b9ee86182081cdf8f77b053                True   \n",
       "4                                    Ethereum                True   \n",
       "\n",
       "   sender_gave_this  sum_raw_amount  sum_dollar_value  token_dollar_value  \\\n",
       "0              True    2.000000e-01        620.672021        3.103360e+03   \n",
       "1             False    1.654453e+28        338.104092        2.043600e-08   \n",
       "2              True    2.000000e-01        620.672021        3.103360e+03   \n",
       "3             False    1.110371e+28        226.915507        2.043600e-08   \n",
       "4              True    3.000000e+00       9310.080322        3.103360e+03   \n",
       "\n",
       "   raw_amount_net  dollar_value_net  \n",
       "0    2.000000e-01        620.672021  \n",
       "1   -1.654453e+28       -338.104092  \n",
       "2    2.000000e-01        620.672021  \n",
       "3   -1.110371e+28       -226.915507  \n",
       "4    3.000000e+00       9310.080322  "
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "aggregated_senders['raw_amount_net'] = aggregated_senders.apply(lambda row: row['sum_raw_amount'] if row['sender_gave_this'] else -row['sum_raw_amount'], axis=1)\n",
    "aggregated_senders['dollar_value_net'] = aggregated_senders.apply(lambda row: row['sum_dollar_value'] if row['sender_gave_this'] else -row['sum_dollar_value'], axis=1)\n",
    "\n",
    "aggregated_senders.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "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>tx_hash</th>\n",
       "      <th>index</th>\n",
       "      <th>token_name</th>\n",
       "      <th>token_dollar_value</th>\n",
       "      <th>raw_amount_net</th>\n",
       "      <th>dollar_value_net</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0x012778bb6330737bed53ca488e582500498d81e1db22...</td>\n",
       "      <td>0</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>3.103360e+03</td>\n",
       "      <td>2.000000e-01</td>\n",
       "      <td>620.672021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0x012778bb6330737bed53ca488e582500498d81e1db22...</td>\n",
       "      <td>0</td>\n",
       "      <td>KAIJUNO8</td>\n",
       "      <td>2.043600e-08</td>\n",
       "      <td>-1.654453e+28</td>\n",
       "      <td>-338.104092</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0x012778bb6330737bed53ca488e582500498d81e1db22...</td>\n",
       "      <td>2</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>3.103360e+03</td>\n",
       "      <td>2.000000e-01</td>\n",
       "      <td>620.672021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0x012778bb6330737bed53ca488e582500498d81e1db22...</td>\n",
       "      <td>2</td>\n",
       "      <td>KAIJUNO8</td>\n",
       "      <td>2.043600e-08</td>\n",
       "      <td>-1.110371e+28</td>\n",
       "      <td>-226.915507</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...</td>\n",
       "      <td>0</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>3.103360e+03</td>\n",
       "      <td>3.000000e+00</td>\n",
       "      <td>9310.080322</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             tx_hash  index token_name  \\\n",
       "0  0x012778bb6330737bed53ca488e582500498d81e1db22...      0   Ethereum   \n",
       "1  0x012778bb6330737bed53ca488e582500498d81e1db22...      0   KAIJUNO8   \n",
       "2  0x012778bb6330737bed53ca488e582500498d81e1db22...      2   Ethereum   \n",
       "3  0x012778bb6330737bed53ca488e582500498d81e1db22...      2   KAIJUNO8   \n",
       "4  0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...      0   Ethereum   \n",
       "\n",
       "   token_dollar_value  raw_amount_net  dollar_value_net  \n",
       "0        3.103360e+03    2.000000e-01        620.672021  \n",
       "1        2.043600e-08   -1.654453e+28       -338.104092  \n",
       "2        3.103360e+03    2.000000e-01        620.672021  \n",
       "3        2.043600e-08   -1.110371e+28       -226.915507  \n",
       "4        3.103360e+03    3.000000e+00       9310.080322  "
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Group by 'tx_hash', 'index', and 'token_name' and aggregate to get one row per group\n",
    "grouped_df = aggregated_senders.groupby(['tx_hash', 'index', 'token_name', 'token_dollar_value'], as_index=False, dropna=False).agg({\n",
    "    'raw_amount_net': 'sum',\\\n",
    "    'dollar_value_net': 'sum'\n",
    "})\n",
    "grouped_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add all the tokens where the sender is involved\n",
    "Only keep the rows where there are exactly 2 tokens"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "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>tx_hash</th>\n",
       "      <th>token_name_A</th>\n",
       "      <th>token_name_B</th>\n",
       "      <th>index</th>\n",
       "      <th>token_name</th>\n",
       "      <th>token_dollar_value</th>\n",
       "      <th>raw_amount_net</th>\n",
       "      <th>dollar_value_net</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0x012778bb6330737bed53ca488e582500498d81e1db22...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>KAIJUNO8</td>\n",
       "      <td>0</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>3.103360e+03</td>\n",
       "      <td>2.000000e-01</td>\n",
       "      <td>620.672021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0x012778bb6330737bed53ca488e582500498d81e1db22...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>KAIJUNO8</td>\n",
       "      <td>0</td>\n",
       "      <td>KAIJUNO8</td>\n",
       "      <td>2.043600e-08</td>\n",
       "      <td>-1.654453e+28</td>\n",
       "      <td>-338.104092</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0x012778bb6330737bed53ca488e582500498d81e1db22...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>KAIJUNO8</td>\n",
       "      <td>2</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>3.103360e+03</td>\n",
       "      <td>2.000000e-01</td>\n",
       "      <td>620.672021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0x012778bb6330737bed53ca488e582500498d81e1db22...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>KAIJUNO8</td>\n",
       "      <td>2</td>\n",
       "      <td>KAIJUNO8</td>\n",
       "      <td>2.043600e-08</td>\n",
       "      <td>-1.110371e+28</td>\n",
       "      <td>-226.915507</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>Jim</td>\n",
       "      <td>0</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>3.103360e+03</td>\n",
       "      <td>3.000000e+00</td>\n",
       "      <td>9310.080322</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             tx_hash token_name_A  \\\n",
       "0  0x012778bb6330737bed53ca488e582500498d81e1db22...     Ethereum   \n",
       "1  0x012778bb6330737bed53ca488e582500498d81e1db22...     Ethereum   \n",
       "2  0x012778bb6330737bed53ca488e582500498d81e1db22...     Ethereum   \n",
       "3  0x012778bb6330737bed53ca488e582500498d81e1db22...     Ethereum   \n",
       "4  0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...     Ethereum   \n",
       "\n",
       "  token_name_B  index token_name  token_dollar_value  raw_amount_net  \\\n",
       "0     KAIJUNO8      0   Ethereum        3.103360e+03    2.000000e-01   \n",
       "1     KAIJUNO8      0   KAIJUNO8        2.043600e-08   -1.654453e+28   \n",
       "2     KAIJUNO8      2   Ethereum        3.103360e+03    2.000000e-01   \n",
       "3     KAIJUNO8      2   KAIJUNO8        2.043600e-08   -1.110371e+28   \n",
       "4          Jim      0   Ethereum        3.103360e+03    3.000000e+00   \n",
       "\n",
       "   dollar_value_net  \n",
       "0        620.672021  \n",
       "1       -338.104092  \n",
       "2        620.672021  \n",
       "3       -226.915507  \n",
       "4       9310.080322  "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tokens_per_tx = grouped_df.groupby(['tx_hash'])['token_name'].unique().reset_index()\n",
    "normal_transactions = tokens_per_tx[tokens_per_tx['token_name'].apply(lambda x: len(x) == 2)]\n",
    "normal_transactions[['token_name_A', 'token_name_B']] = pd.DataFrame(normal_transactions['token_name'].tolist(), index=normal_transactions.index)\n",
    "normal_transactions = normal_transactions.drop(columns=['token_name'])\n",
    "tokens_df = pd.merge(normal_transactions, grouped_df, on='tx_hash', how = 'left')\n",
    "tokens_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Add the transactions where there are not two tokens where the sender is involved to the problematic transactions df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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>tx_hash or block</th>\n",
       "      <th>where_problem_happened</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0x6326451b6cee44291395fd3efa1f7144676392ade4f0...</td>\n",
       "      <td>tenderly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0x6326451b6cee44291395fd3efa1f7144676392ade4f0...</td>\n",
       "      <td>tenderly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>0x8f762515bc5eb815f6683323c4226373eb53922f0007...</td>\n",
       "      <td>tenderly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>0xe5be76c827fec38b17f44a4adef3a96553c5c88d017e...</td>\n",
       "      <td>tenderly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>0xe5be76c827fec38b17f44a4adef3a96553c5c88d017e...</td>\n",
       "      <td>tenderly</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     tx_hash or block where_problem_happened\n",
       "8   0x6326451b6cee44291395fd3efa1f7144676392ade4f0...               tenderly\n",
       "9   0x6326451b6cee44291395fd3efa1f7144676392ade4f0...               tenderly\n",
       "10  0x8f762515bc5eb815f6683323c4226373eb53922f0007...               tenderly\n",
       "11  0xe5be76c827fec38b17f44a4adef3a96553c5c88d017e...               tenderly\n",
       "12  0xe5be76c827fec38b17f44a4adef3a96553c5c88d017e...               tenderly"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transaction_without_two_tokens = set(df_senders.tx_hash.unique()) - set(tokens_df.tx_hash.unique()) \n",
    "for transaction in transaction_without_two_tokens:\n",
    "    new_row = {'tx_hash or block':  transaction, 'where_problem_happened': 'only one token or more than two'}\n",
    "    problematic_transactions = pd.concat([problematic_transactions, pd.DataFrame([new_row])], ignore_index=True)\n",
    "\n",
    "problematic_transactions.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Calculate the delta in tokens "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "#create two dataframes, for the token A and B at index 0\n",
    "token_A_df_0 = tokens_df[(tokens_df['token_name'] == tokens_df['token_name_A']) & (tokens_df['index'] == 0)][['tx_hash', 'token_dollar_value', 'dollar_value_net', 'raw_amount_net', 'token_name_A']]\n",
    "token_B_df_0 = tokens_df[(tokens_df['token_name'] == tokens_df['token_name_B']) & (tokens_df['index'] == 0)][['tx_hash', 'token_dollar_value', 'dollar_value_net', 'raw_amount_net', 'token_name_B']]\n",
    "\n",
    "token_A_df_0.rename(columns = {'token_dollar_value': 'token_A_dollar_value_index_0', 'dollar_value_net' : 'token_A_tx_dollar_value_index_0', 'raw_amount_net' : 'token_A_raw_amount_index_0'}, inplace = True)\n",
    "token_B_df_0.rename(columns = {'token_dollar_value': 'token_B_dollar_value_index_0', 'dollar_value_net' : 'token_B_tx_dollar_value_index_0', 'raw_amount_net' : 'token_B_raw_amount_index_0'}, inplace = True)\n",
    "\n",
    "token_A_df_0 = token_A_df_0.drop_duplicates()\n",
    "token_B_df_0 = token_B_df_0.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "#create two dataframes, for the token A and B at index other than 0\n",
    "token_A_df_other = tokens_df[(tokens_df['token_name'] == tokens_df['token_name_A']) & (tokens_df['index'] != 0)][['tx_hash', 'token_dollar_value','dollar_value_net', 'raw_amount_net', 'token_name_A']]\n",
    "token_B_df_other = tokens_df[(tokens_df['token_name'] == tokens_df['token_name_B']) & (tokens_df['index'] != 0)][['tx_hash', 'token_dollar_value', 'dollar_value_net', 'raw_amount_net',  'token_name_B']]\n",
    "\n",
    "token_A_df_other.rename(columns = {'token_dollar_value': 'token_A_dollar_value_index_other', 'dollar_value_net' : 'token_A_tx_dollar_value_index_other', 'raw_amount_net' : 'token_A_raw_amount_index_other'}, inplace = True)\n",
    "token_B_df_other.rename(columns = {'token_dollar_value': 'token_B_dollar_value_index_other', 'dollar_value_net' : 'token_B_tx_dollar_value_index_other', 'raw_amount_net' : 'token_B_raw_amount_index_other'}, inplace = True)\n",
    "\n",
    "token_A_df_other = token_A_df_other.drop_duplicates()\n",
    "token_B_df_other = token_B_df_other.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['tx_hash', 'token_B_dollar_value_index_other',\n",
       "       'token_B_tx_dollar_value_index_other', 'token_B_raw_amount_index_other',\n",
       "       'token_name_B', 'token_B_dollar_value_index_0',\n",
       "       'token_B_tx_dollar_value_index_0', 'token_B_raw_amount_index_0',\n",
       "       'token_A_dollar_value_index_other',\n",
       "       'token_A_tx_dollar_value_index_other', 'token_A_raw_amount_index_other',\n",
       "       'token_name_A', 'token_A_dollar_value_index_0',\n",
       "       'token_A_tx_dollar_value_index_0', 'token_A_raw_amount_index_0'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# merge the 4 dataframes to have one with all the data of the tx with one row per tx\n",
    "#token_B_df_other_grouped, token_B_df_0_grouped, token_A_df_other_grouped, token_A_df_0_grouped\n",
    "merged_df = pd.merge(token_B_df_other, token_B_df_0, on=['tx_hash', 'token_name_B'])\n",
    "merged_df = pd.merge(merged_df, token_A_df_other, on=['tx_hash'])\n",
    "merged_df = pd.merge(merged_df, token_A_df_0, on=['tx_hash', 'token_name_A'])\n",
    "merged_df.columns \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "def calculate_difference(row, column_0, column_other):\n",
    "    return row[column_0] - row[column_other]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['tx_hash', 'token_B_dollar_value_index_other',\n",
       "       'token_B_tx_dollar_value_index_other', 'token_B_raw_amount_index_other',\n",
       "       'token_name_B', 'token_B_dollar_value_index_0',\n",
       "       'token_B_tx_dollar_value_index_0', 'token_B_raw_amount_index_0',\n",
       "       'token_A_dollar_value_index_other',\n",
       "       'token_A_tx_dollar_value_index_other', 'token_A_raw_amount_index_other',\n",
       "       'token_name_A', 'token_A_dollar_value_index_0',\n",
       "       'token_A_tx_dollar_value_index_0', 'token_A_raw_amount_index_0',\n",
       "       'token_A_delta_raw_amount', 'token_B_delta_raw_amount',\n",
       "       'token_A_delta_dollar', 'token_B_delta_dollar'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# add new rows with the delta in tokens\n",
    "merged_df['token_A_delta_raw_amount'] = merged_df.apply(lambda row: calculate_difference(row, 'token_A_raw_amount_index_0', 'token_A_raw_amount_index_other'), axis=1)\n",
    "merged_df['token_B_delta_raw_amount'] = merged_df.apply(lambda row: calculate_difference(row, 'token_B_raw_amount_index_0', 'token_B_raw_amount_index_other'), axis=1)\n",
    "\n",
    "merged_df['token_A_delta_dollar'] = merged_df.apply(lambda row: calculate_difference(row, 'token_A_tx_dollar_value_index_0', 'token_A_tx_dollar_value_index_other'), axis=1)\n",
    "merged_df['token_B_delta_dollar'] = merged_df.apply(lambda row: calculate_difference(row, 'token_B_tx_dollar_value_index_0', 'token_B_tx_dollar_value_index_other'), axis=1)\n",
    "merged_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "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>tx_hash</th>\n",
       "      <th>token_name_A</th>\n",
       "      <th>token_A_delta_raw_amount</th>\n",
       "      <th>token_A_delta_dollar_tenderly</th>\n",
       "      <th>token_name_B</th>\n",
       "      <th>token_B_delta_raw_amount</th>\n",
       "      <th>token_B_delta_dollar_tenderly</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0x012778bb6330737bed53ca488e582500498d81e1db22...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>KAIJUNO8</td>\n",
       "      <td>-5.440819e+27</td>\n",
       "      <td>-111.188585</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>Jim</td>\n",
       "      <td>-3.297227e+24</td>\n",
       "      <td>-24.531371</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0x1a5eafe643fb4e41e4666f045341584ea22bb73d5857...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-2.469591e-02</td>\n",
       "      <td>-76.640316</td>\n",
       "      <td>enqAI</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0x1bcc89149f51daa2d79355a340d576af22555be34567...</td>\n",
       "      <td>ClosedAI</td>\n",
       "      <td>-1.446769e+21</td>\n",
       "      <td>-0.545750</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...</td>\n",
       "      <td>0xb0699d63aef20df3f1cffa9ca2bb8670416271d2</td>\n",
       "      <td>-4.175898e+19</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             tx_hash  \\\n",
       "0  0x012778bb6330737bed53ca488e582500498d81e1db22...   \n",
       "1  0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...   \n",
       "2  0x1a5eafe643fb4e41e4666f045341584ea22bb73d5857...   \n",
       "3  0x1bcc89149f51daa2d79355a340d576af22555be34567...   \n",
       "4  0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...   \n",
       "\n",
       "                                 token_name_A  token_A_delta_raw_amount  \\\n",
       "0                                    Ethereum              0.000000e+00   \n",
       "1                                    Ethereum              0.000000e+00   \n",
       "2                                    Ethereum             -2.469591e-02   \n",
       "3                                    ClosedAI             -1.446769e+21   \n",
       "4  0xb0699d63aef20df3f1cffa9ca2bb8670416271d2             -4.175898e+19   \n",
       "\n",
       "   token_A_delta_dollar_tenderly token_name_B  token_B_delta_raw_amount  \\\n",
       "0                       0.000000     KAIJUNO8             -5.440819e+27   \n",
       "1                       0.000000          Jim             -3.297227e+24   \n",
       "2                     -76.640316        enqAI              0.000000e+00   \n",
       "3                      -0.545750     Ethereum              0.000000e+00   \n",
       "4                       0.000000     Ethereum              0.000000e+00   \n",
       "\n",
       "   token_B_delta_dollar_tenderly  \n",
       "0                    -111.188585  \n",
       "1                     -24.531371  \n",
       "2                       0.000000  \n",
       "3                       0.000000  \n",
       "4                       0.000000  "
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nearly_final = merged_df[['tx_hash','token_name_A', 'token_A_delta_raw_amount', 'token_A_delta_dollar', 'token_name_B',  'token_B_delta_raw_amount', 'token_B_delta_dollar']]\n",
    "nearly_final = nearly_final.rename(columns = {'token_A_delta_dollar' : 'token_A_delta_dollar_tenderly', 'token_B_delta_dollar' : 'token_B_delta_dollar_tenderly'})\n",
    "nearly_final.head()  # one row per transaction with the delta in raw amounts, and also in tenderly dollars"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Use the stable coin of the transaction to calculate the difference in dollars \n",
    "Tenderly gives us a value for the tokens, but this value is a bit strange for \"meme\" tokens, or unstable tokens. Therefore, we use the stable token that is present in the transaction (there is always one), calculate the worth of that transaction in dollar based on the amount of this stable token and the dollar value of that stable token, and finally calculate the worth of the unstable token based on the ratio of amount of unstable swapped for stable."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "stable_coins = ['WETH', 'Ethereum', 'USDC', 'Tether', 'Wrapped Bitcoin', 'Dai']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/qq/p76g00dx6zv_qlpx33vlcr2c0000gn/T/ipykernel_39429/597281111.py:11: DeprecationWarning: DataFrameGroupBy.apply operated on the grouping columns. This behavior is deprecated, and in a future version of pandas the grouping columns will be excluded from the operation. Either pass `include_groups=False` to exclude the groupings or explicitly select the grouping columns after groupby to silence this warning.\n",
      "  selected_stable_token = df_senders.groupby('tx_hash').apply(select_stable_coin).reset_index(name='selected_stable_coin')\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>tx_hash</th>\n",
       "      <th>selected_stable_coin</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0x012778bb6330737bed53ca488e582500498d81e1db22...</td>\n",
       "      <td>Ethereum</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...</td>\n",
       "      <td>Ethereum</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0x1a5eafe643fb4e41e4666f045341584ea22bb73d5857...</td>\n",
       "      <td>Ethereum</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0x1bcc89149f51daa2d79355a340d576af22555be34567...</td>\n",
       "      <td>Ethereum</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...</td>\n",
       "      <td>Ethereum</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             tx_hash selected_stable_coin\n",
       "0  0x012778bb6330737bed53ca488e582500498d81e1db22...             Ethereum\n",
       "1  0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...             Ethereum\n",
       "2  0x1a5eafe643fb4e41e4666f045341584ea22bb73d5857...             Ethereum\n",
       "3  0x1bcc89149f51daa2d79355a340d576af22555be34567...             Ethereum\n",
       "4  0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...             Ethereum"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# find the stable coin in order of preference of the transaction\n",
    "def select_stable_coin(group):\n",
    "    for coin in stable_coins:\n",
    "        involved_row = group[(group['sender_is_involved'] == True) & (group['token_name'] == coin)]\n",
    "        if not involved_row.empty:\n",
    "            return coin\n",
    " \n",
    "    return None  \n",
    "\n",
    "# Group by 'tx_hash' and apply custom function to each group to find the stable coin for all the transactions\n",
    "selected_stable_token = df_senders.groupby('tx_hash').apply(select_stable_coin).reset_index(name='selected_stable_coin')\n",
    "selected_stable_token.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "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>tx_hash</th>\n",
       "      <th>token_name</th>\n",
       "      <th>dollar_value_net</th>\n",
       "      <th>raw_amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0x012778bb6330737bed53ca488e582500498d81e1db22...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-620.672021</td>\n",
       "      <td>0.200000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-9310.080322</td>\n",
       "      <td>3.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0x1a5eafe643fb4e41e4666f045341584ea22bb73d5857...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>15328.173017</td>\n",
       "      <td>4.939218</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0x1bcc89149f51daa2d79355a340d576af22555be34567...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-6827.392236</td>\n",
       "      <td>2.200000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>1241.344043</td>\n",
       "      <td>0.400000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             tx_hash token_name  \\\n",
       "0  0x012778bb6330737bed53ca488e582500498d81e1db22...   Ethereum   \n",
       "1  0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...   Ethereum   \n",
       "2  0x1a5eafe643fb4e41e4666f045341584ea22bb73d5857...   Ethereum   \n",
       "3  0x1bcc89149f51daa2d79355a340d576af22555be34567...   Ethereum   \n",
       "4  0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...   Ethereum   \n",
       "\n",
       "   dollar_value_net  raw_amount  \n",
       "0       -620.672021    0.200000  \n",
       "1      -9310.080322    3.000000  \n",
       "2      15328.173017    4.939218  \n",
       "3      -6827.392236    2.200000  \n",
       "4       1241.344043    0.400000  "
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create two dataframes with the dollar values and raw amounts of the stable coins at index 0 and the original index of the transaction\n",
    "stable_coins_df_index_0 = df_senders[(df_senders['token_name'].isin(stable_coins)) & (df_senders['index'] == 0) & df_senders['raw_amount'] != 0][['tx_hash', 'token_name', 'dollar_value_net', 'raw_amount', 'sender_is_involved', 'sender_gave_this']]\n",
    "stable_coins_df_index_0 = stable_coins_df_index_0.groupby(['tx_hash', 'token_name'])[['dollar_value_net', 'raw_amount']].sum().reset_index()\n",
    "\n",
    "\n",
    "stable_coins_df_index_other = df_senders[(df_senders['token_name'].isin(stable_coins)) & (df_senders['index'] != 0) & df_senders['raw_amount'] != 0][['tx_hash', 'token_name', 'token_dollar_value', 'dollar_value_net', 'raw_amount', 'sender_gave_this']]\n",
    "stable_coins_df_index_other = stable_coins_df_index_other.groupby(['tx_hash', 'token_name'])[['dollar_value_net', 'raw_amount']].sum().reset_index()\n",
    "stable_coins_df_index_other.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "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>tx_hash</th>\n",
       "      <th>selected_stable_coin</th>\n",
       "      <th>tx_stable_dollar_value_index_other_net</th>\n",
       "      <th>stable_raw_amount_index_other</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0x012778bb6330737bed53ca488e582500498d81e1db22...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-620.672021</td>\n",
       "      <td>0.200000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-9310.080322</td>\n",
       "      <td>3.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0x1a5eafe643fb4e41e4666f045341584ea22bb73d5857...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>15328.173017</td>\n",
       "      <td>4.939218</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0x1bcc89149f51daa2d79355a340d576af22555be34567...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-6827.392236</td>\n",
       "      <td>2.200000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>1241.344043</td>\n",
       "      <td>0.400000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             tx_hash selected_stable_coin  \\\n",
       "0  0x012778bb6330737bed53ca488e582500498d81e1db22...             Ethereum   \n",
       "1  0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...             Ethereum   \n",
       "2  0x1a5eafe643fb4e41e4666f045341584ea22bb73d5857...             Ethereum   \n",
       "3  0x1bcc89149f51daa2d79355a340d576af22555be34567...             Ethereum   \n",
       "4  0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...             Ethereum   \n",
       "\n",
       "   tx_stable_dollar_value_index_other_net  stable_raw_amount_index_other  \n",
       "0                             -620.672021                       0.200000  \n",
       "1                            -9310.080322                       3.000000  \n",
       "2                            15328.173017                       4.939218  \n",
       "3                            -6827.392236                       2.200000  \n",
       "4                             1241.344043                       0.400000  "
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# two dataframes with one row per tx_hash with the value of ther stable token if it was exchanged by the sender \n",
    "selected_stable_coins_df_index_0 = pd.merge(selected_stable_token, stable_coins_df_index_0, on = ['tx_hash'])\n",
    "selected_stable_coins_df_index_0 = selected_stable_coins_df_index_0.drop('token_name', axis = 1)\n",
    "selected_stable_coins_df_index_0 = selected_stable_coins_df_index_0.rename(columns = {'dollar_value_net' : 'tx_stable_dollar_value_index_0_net', 'raw_amount' : 'stable_raw_amount_index_0'})\n",
    "\n",
    "\n",
    "selected_stable_coins_df_index_other = pd.merge(selected_stable_token, stable_coins_df_index_other, on = ['tx_hash'])\n",
    "selected_stable_coins_df_index_other = selected_stable_coins_df_index_other.drop('token_name', axis = 1)\n",
    "selected_stable_coins_df_index_other = selected_stable_coins_df_index_other.rename(columns = { 'dollar_value_net' : 'tx_stable_dollar_value_index_other_net', 'raw_amount' : 'stable_raw_amount_index_other'})\n",
    "\n",
    "selected_stable_coins_df_index_other.head() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "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>tx_hash</th>\n",
       "      <th>token_name</th>\n",
       "      <th>sender_gave_this</th>\n",
       "      <th>raw_amount</th>\n",
       "      <th>selected_stable_coin</th>\n",
       "      <th>tx_stable_dollar_value_index_0_net</th>\n",
       "      <th>stable_raw_amount_index_0</th>\n",
       "      <th>other_token_value_index_0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0x012778bb6330737bed53ca488e582500498d81e1db22...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>True</td>\n",
       "      <td>2.000000e-01</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-620.672021</td>\n",
       "      <td>0.200000</td>\n",
       "      <td>3.103360e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0x012778bb6330737bed53ca488e582500498d81e1db22...</td>\n",
       "      <td>KAIJUNO8</td>\n",
       "      <td>False</td>\n",
       "      <td>1.654453e+28</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-620.672021</td>\n",
       "      <td>0.200000</td>\n",
       "      <td>3.751523e-26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>True</td>\n",
       "      <td>3.000000e+00</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-9310.080322</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>3.103360e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...</td>\n",
       "      <td>Jim</td>\n",
       "      <td>False</td>\n",
       "      <td>6.627504e+26</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-9310.080322</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>1.404764e-23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0x1a5eafe643fb4e41e4666f045341584ea22bb73d5857...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>False</td>\n",
       "      <td>4.963914e+00</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>15404.813333</td>\n",
       "      <td>4.963914</td>\n",
       "      <td>3.103360e+03</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             tx_hash token_name  \\\n",
       "0  0x012778bb6330737bed53ca488e582500498d81e1db22...   Ethereum   \n",
       "1  0x012778bb6330737bed53ca488e582500498d81e1db22...   KAIJUNO8   \n",
       "2  0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...   Ethereum   \n",
       "3  0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...        Jim   \n",
       "4  0x1a5eafe643fb4e41e4666f045341584ea22bb73d5857...   Ethereum   \n",
       "\n",
       "   sender_gave_this    raw_amount selected_stable_coin  \\\n",
       "0              True  2.000000e-01             Ethereum   \n",
       "1             False  1.654453e+28             Ethereum   \n",
       "2              True  3.000000e+00             Ethereum   \n",
       "3             False  6.627504e+26             Ethereum   \n",
       "4             False  4.963914e+00             Ethereum   \n",
       "\n",
       "   tx_stable_dollar_value_index_0_net  stable_raw_amount_index_0  \\\n",
       "0                         -620.672021                   0.200000   \n",
       "1                         -620.672021                   0.200000   \n",
       "2                        -9310.080322                   3.000000   \n",
       "3                        -9310.080322                   3.000000   \n",
       "4                        15404.813333                   4.963914   \n",
       "\n",
       "   other_token_value_index_0  \n",
       "0               3.103360e+03  \n",
       "1               3.751523e-26  \n",
       "2               3.103360e+03  \n",
       "3               1.404764e-23  \n",
       "4               3.103360e+03  "
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create a dataframe where we merge the dataframes to get two rows per transaction with the amount of tokens\n",
    "\n",
    "filtered_df_all = df_senders.groupby(['tx_hash', 'index', 'token_name', 'sender_gave_this'])['raw_amount'].sum().reset_index()\n",
    "df_null_0 = filtered_df_all[(filtered_df_all['index'] == 0 )]\n",
    "\n",
    "merged_null_0_all = pd.merge(df_null_0, selected_stable_coins_df_index_0, on=['tx_hash'], how = 'left')\n",
    "merged_null_0_all = merged_null_0_all.drop('index', axis = 1)\n",
    "merged_null_0_all['other_token_value_index_0'] = merged_null_0_all['tx_stable_dollar_value_index_0_net'].abs() / merged_null_0_all['raw_amount']\n",
    "merged_null_0_all.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "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>tx_hash</th>\n",
       "      <th>index</th>\n",
       "      <th>token_name</th>\n",
       "      <th>sender_gave_this</th>\n",
       "      <th>raw_amount</th>\n",
       "      <th>selected_stable_coin</th>\n",
       "      <th>tx_stable_dollar_value_index_0_net</th>\n",
       "      <th>stable_raw_amount_index_0</th>\n",
       "      <th>other_token_value_index_0</th>\n",
       "      <th>calculated_tx_dollar_value</th>\n",
       "      <th>calculated_tx_dollar_value_net</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>0xf575687f780edd7918193dcfe97f5f2d871ea275b9c7...</td>\n",
       "      <td>1</td>\n",
       "      <td>GEKKO</td>\n",
       "      <td>True</td>\n",
       "      <td>4.135947e+27</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>25282.906167</td>\n",
       "      <td>8.146946</td>\n",
       "      <td>6.112966e-24</td>\n",
       "      <td>25282.906167</td>\n",
       "      <td>-25282.906167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <td>0xf8196d4b1341fb7700603dd0abfebc5ee859b279eb9c...</td>\n",
       "      <td>0</td>\n",
       "      <td>Doggensnout Skeptic</td>\n",
       "      <td>False</td>\n",
       "      <td>1.555176e+21</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-9310.080322</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>5.986513e-18</td>\n",
       "      <td>9310.080322</td>\n",
       "      <td>9310.080322</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109</th>\n",
       "      <td>0xf8196d4b1341fb7700603dd0abfebc5ee859b279eb9c...</td>\n",
       "      <td>0</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>True</td>\n",
       "      <td>3.000000e+00</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-9310.080322</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>3.103360e+03</td>\n",
       "      <td>9310.080322</td>\n",
       "      <td>-9310.080322</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110</th>\n",
       "      <td>0xf8196d4b1341fb7700603dd0abfebc5ee859b279eb9c...</td>\n",
       "      <td>4</td>\n",
       "      <td>Doggensnout Skeptic</td>\n",
       "      <td>False</td>\n",
       "      <td>1.547439e+21</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-9310.080322</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>5.986513e-18</td>\n",
       "      <td>9263.763359</td>\n",
       "      <td>9263.763359</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>111</th>\n",
       "      <td>0xf8196d4b1341fb7700603dd0abfebc5ee859b279eb9c...</td>\n",
       "      <td>4</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>True</td>\n",
       "      <td>3.000000e+00</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-9310.080322</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>3.103360e+03</td>\n",
       "      <td>9310.080322</td>\n",
       "      <td>-9310.080322</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               tx_hash  index  \\\n",
       "107  0xf575687f780edd7918193dcfe97f5f2d871ea275b9c7...      1   \n",
       "108  0xf8196d4b1341fb7700603dd0abfebc5ee859b279eb9c...      0   \n",
       "109  0xf8196d4b1341fb7700603dd0abfebc5ee859b279eb9c...      0   \n",
       "110  0xf8196d4b1341fb7700603dd0abfebc5ee859b279eb9c...      4   \n",
       "111  0xf8196d4b1341fb7700603dd0abfebc5ee859b279eb9c...      4   \n",
       "\n",
       "              token_name  sender_gave_this    raw_amount selected_stable_coin  \\\n",
       "107                GEKKO              True  4.135947e+27             Ethereum   \n",
       "108  Doggensnout Skeptic             False  1.555176e+21             Ethereum   \n",
       "109             Ethereum              True  3.000000e+00             Ethereum   \n",
       "110  Doggensnout Skeptic             False  1.547439e+21             Ethereum   \n",
       "111             Ethereum              True  3.000000e+00             Ethereum   \n",
       "\n",
       "     tx_stable_dollar_value_index_0_net  stable_raw_amount_index_0  \\\n",
       "107                        25282.906167                   8.146946   \n",
       "108                        -9310.080322                   3.000000   \n",
       "109                        -9310.080322                   3.000000   \n",
       "110                        -9310.080322                   3.000000   \n",
       "111                        -9310.080322                   3.000000   \n",
       "\n",
       "     other_token_value_index_0  calculated_tx_dollar_value  \\\n",
       "107               6.112966e-24                25282.906167   \n",
       "108               5.986513e-18                 9310.080322   \n",
       "109               3.103360e+03                 9310.080322   \n",
       "110               5.986513e-18                 9263.763359   \n",
       "111               3.103360e+03                 9310.080322   \n",
       "\n",
       "     calculated_tx_dollar_value_net  \n",
       "107                   -25282.906167  \n",
       "108                     9310.080322  \n",
       "109                    -9310.080322  \n",
       "110                     9263.763359  \n",
       "111                    -9310.080322  "
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# calculate the transaction and net transaction dollar value\n",
    "\n",
    "calculated_dollar_df_all = pd.merge(filtered_df_all, merged_null_0_all.drop('raw_amount', axis = 1), on=['tx_hash', 'token_name', 'sender_gave_this'], how = 'left')\n",
    "calculated_dollar_df_all['calculated_tx_dollar_value'] = calculated_dollar_df_all['raw_amount']* calculated_dollar_df_all['other_token_value_index_0']\n",
    "calculated_dollar_df_all['calculated_tx_dollar_value_net']  = np.where(calculated_dollar_df_all['sender_gave_this'], - calculated_dollar_df_all['calculated_tx_dollar_value'], calculated_dollar_df_all['calculated_tx_dollar_value'])\n",
    "calculated_dollar_df_all.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "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>tx_hash</th>\n",
       "      <th>index</th>\n",
       "      <th>type</th>\n",
       "      <th>raw_amount_x</th>\n",
       "      <th>dollar_value</th>\n",
       "      <th>token_contract_address</th>\n",
       "      <th>token_name</th>\n",
       "      <th>token_dollar_value</th>\n",
       "      <th>from</th>\n",
       "      <th>to</th>\n",
       "      <th>...</th>\n",
       "      <th>sender_is_involved</th>\n",
       "      <th>sender_gave_this</th>\n",
       "      <th>dollar_value_net</th>\n",
       "      <th>raw_amount_y</th>\n",
       "      <th>selected_stable_coin</th>\n",
       "      <th>tx_stable_dollar_value_index_0_net</th>\n",
       "      <th>stable_raw_amount_index_0</th>\n",
       "      <th>other_token_value_index_0</th>\n",
       "      <th>calculated_tx_dollar_value</th>\n",
       "      <th>calculated_tx_dollar_value_net</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...</td>\n",
       "      <td>0</td>\n",
       "      <td>Transfer</td>\n",
       "      <td>8.074525e+20</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0xb0699d63aef20df3f1cffa9ca2bb8670416271d2</td>\n",
       "      <td>0xb0699d63aef20df3f1cffa9ca2bb8670416271d2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0x36a2ffb33c1b427c46c3d30adac3ca4e8ed36179</td>\n",
       "      <td>0xe1ebdf64f7f3a31723e767a561345f958233bb7d</td>\n",
       "      <td>...</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.074525e+20</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>1241.344043</td>\n",
       "      <td>0.400000</td>\n",
       "      <td>1.537359e-18</td>\n",
       "      <td>1241.344043</td>\n",
       "      <td>-1241.344043</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...</td>\n",
       "      <td>0</td>\n",
       "      <td>Transfer</td>\n",
       "      <td>4.000000e-01</td>\n",
       "      <td>1241.344043</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>3103.360107</td>\n",
       "      <td>0x7a250d5630b4cf539739df2c5dacb4c659f2488d</td>\n",
       "      <td>0x36a2ffb33c1b427c46c3d30adac3ca4e8ed36179</td>\n",
       "      <td>...</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>1241.344043</td>\n",
       "      <td>4.000000e-01</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>1241.344043</td>\n",
       "      <td>0.400000</td>\n",
       "      <td>3.103360e+03</td>\n",
       "      <td>1241.344043</td>\n",
       "      <td>1241.344043</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...</td>\n",
       "      <td>2</td>\n",
       "      <td>Transfer</td>\n",
       "      <td>8.492115e+20</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0xb0699d63aef20df3f1cffa9ca2bb8670416271d2</td>\n",
       "      <td>0xb0699d63aef20df3f1cffa9ca2bb8670416271d2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0x36a2ffb33c1b427c46c3d30adac3ca4e8ed36179</td>\n",
       "      <td>0xe1ebdf64f7f3a31723e767a561345f958233bb7d</td>\n",
       "      <td>...</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.492115e+20</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>1241.344043</td>\n",
       "      <td>0.400000</td>\n",
       "      <td>1.537359e-18</td>\n",
       "      <td>1305.542560</td>\n",
       "      <td>-1305.542560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...</td>\n",
       "      <td>2</td>\n",
       "      <td>Transfer</td>\n",
       "      <td>4.000000e-01</td>\n",
       "      <td>1241.344043</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>3103.360107</td>\n",
       "      <td>0x7a250d5630b4cf539739df2c5dacb4c659f2488d</td>\n",
       "      <td>0x36a2ffb33c1b427c46c3d30adac3ca4e8ed36179</td>\n",
       "      <td>...</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>1241.344043</td>\n",
       "      <td>4.000000e-01</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>1241.344043</td>\n",
       "      <td>0.400000</td>\n",
       "      <td>3.103360e+03</td>\n",
       "      <td>1241.344043</td>\n",
       "      <td>1241.344043</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0xee8fd2c76181afa14ca0da158e0a01bba2d3df8e62c5...</td>\n",
       "      <td>0</td>\n",
       "      <td>Transfer</td>\n",
       "      <td>3.500047e+04</td>\n",
       "      <td>35000.470928</td>\n",
       "      <td>0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48</td>\n",
       "      <td>USDC</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0xbbf46514de004992d8dcbcec19f02f2e772ab51d</td>\n",
       "      <td>0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640</td>\n",
       "      <td>...</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>-35000.470928</td>\n",
       "      <td>3.500047e+04</td>\n",
       "      <td>USDC</td>\n",
       "      <td>-35000.470928</td>\n",
       "      <td>35000.470928</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>35000.470928</td>\n",
       "      <td>-35000.470928</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             tx_hash  index      type  \\\n",
       "0  0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...      0  Transfer   \n",
       "1  0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...      0  Transfer   \n",
       "2  0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...      2  Transfer   \n",
       "3  0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...      2  Transfer   \n",
       "4  0xee8fd2c76181afa14ca0da158e0a01bba2d3df8e62c5...      0  Transfer   \n",
       "\n",
       "   raw_amount_x  dollar_value                      token_contract_address  \\\n",
       "0  8.074525e+20           NaN  0xb0699d63aef20df3f1cffa9ca2bb8670416271d2   \n",
       "1  4.000000e-01   1241.344043                                    Ethereum   \n",
       "2  8.492115e+20           NaN  0xb0699d63aef20df3f1cffa9ca2bb8670416271d2   \n",
       "3  4.000000e-01   1241.344043                                    Ethereum   \n",
       "4  3.500047e+04  35000.470928  0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48   \n",
       "\n",
       "                                   token_name  token_dollar_value  \\\n",
       "0  0xb0699d63aef20df3f1cffa9ca2bb8670416271d2                 NaN   \n",
       "1                                    Ethereum         3103.360107   \n",
       "2  0xb0699d63aef20df3f1cffa9ca2bb8670416271d2                 NaN   \n",
       "3                                    Ethereum         3103.360107   \n",
       "4                                        USDC            1.000000   \n",
       "\n",
       "                                         from  \\\n",
       "0  0x36a2ffb33c1b427c46c3d30adac3ca4e8ed36179   \n",
       "1  0x7a250d5630b4cf539739df2c5dacb4c659f2488d   \n",
       "2  0x36a2ffb33c1b427c46c3d30adac3ca4e8ed36179   \n",
       "3  0x7a250d5630b4cf539739df2c5dacb4c659f2488d   \n",
       "4  0xbbf46514de004992d8dcbcec19f02f2e772ab51d   \n",
       "\n",
       "                                           to  ... sender_is_involved  \\\n",
       "0  0xe1ebdf64f7f3a31723e767a561345f958233bb7d  ...               True   \n",
       "1  0x36a2ffb33c1b427c46c3d30adac3ca4e8ed36179  ...               True   \n",
       "2  0xe1ebdf64f7f3a31723e767a561345f958233bb7d  ...               True   \n",
       "3  0x36a2ffb33c1b427c46c3d30adac3ca4e8ed36179  ...               True   \n",
       "4  0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640  ...               True   \n",
       "\n",
       "   sender_gave_this  dollar_value_net  raw_amount_y  selected_stable_coin  \\\n",
       "0              True               NaN  8.074525e+20              Ethereum   \n",
       "1             False       1241.344043  4.000000e-01              Ethereum   \n",
       "2              True               NaN  8.492115e+20              Ethereum   \n",
       "3             False       1241.344043  4.000000e-01              Ethereum   \n",
       "4              True     -35000.470928  3.500047e+04                  USDC   \n",
       "\n",
       "   tx_stable_dollar_value_index_0_net stable_raw_amount_index_0  \\\n",
       "0                         1241.344043                  0.400000   \n",
       "1                         1241.344043                  0.400000   \n",
       "2                         1241.344043                  0.400000   \n",
       "3                         1241.344043                  0.400000   \n",
       "4                       -35000.470928              35000.470928   \n",
       "\n",
       "   other_token_value_index_0  calculated_tx_dollar_value  \\\n",
       "0               1.537359e-18                 1241.344043   \n",
       "1               3.103360e+03                 1241.344043   \n",
       "2               1.537359e-18                 1305.542560   \n",
       "3               3.103360e+03                 1241.344043   \n",
       "4               1.000000e+00                35000.470928   \n",
       "\n",
       "   calculated_tx_dollar_value_net  \n",
       "0                    -1241.344043  \n",
       "1                     1241.344043  \n",
       "2                    -1305.542560  \n",
       "3                     1241.344043  \n",
       "4                   -35000.470928  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_test_all = pd.merge(df_senders, calculated_dollar_df_all, on = ['tx_hash', 'index', 'token_name', 'sender_gave_this'], how = 'left')\n",
    "final_test_all['dollar_value_net']  = np.where(final_test_all['sender_gave_this'], - final_test_all['dollar_value'], final_test_all['dollar_value'])\n",
    "final_test_all.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "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>tx_hash</th>\n",
       "      <th>index</th>\n",
       "      <th>token_name</th>\n",
       "      <th>sender_gave_this</th>\n",
       "      <th>calculated_dollar_value_priority</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0x012778bb6330737bed53ca488e582500498d81e1db22...</td>\n",
       "      <td>0</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>True</td>\n",
       "      <td>-620.672021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0x012778bb6330737bed53ca488e582500498d81e1db22...</td>\n",
       "      <td>0</td>\n",
       "      <td>KAIJUNO8</td>\n",
       "      <td>False</td>\n",
       "      <td>620.672021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0x012778bb6330737bed53ca488e582500498d81e1db22...</td>\n",
       "      <td>2</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>True</td>\n",
       "      <td>-620.672021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0x012778bb6330737bed53ca488e582500498d81e1db22...</td>\n",
       "      <td>2</td>\n",
       "      <td>KAIJUNO8</td>\n",
       "      <td>False</td>\n",
       "      <td>416.558421</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...</td>\n",
       "      <td>0</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>True</td>\n",
       "      <td>-9310.080322</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             tx_hash  index token_name  \\\n",
       "0  0x012778bb6330737bed53ca488e582500498d81e1db22...      0   Ethereum   \n",
       "1  0x012778bb6330737bed53ca488e582500498d81e1db22...      0   KAIJUNO8   \n",
       "2  0x012778bb6330737bed53ca488e582500498d81e1db22...      2   Ethereum   \n",
       "3  0x012778bb6330737bed53ca488e582500498d81e1db22...      2   KAIJUNO8   \n",
       "4  0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...      0   Ethereum   \n",
       "\n",
       "   sender_gave_this  calculated_dollar_value_priority  \n",
       "0              True                       -620.672021  \n",
       "1             False                        620.672021  \n",
       "2              True                       -620.672021  \n",
       "3             False                        416.558421  \n",
       "4              True                      -9310.080322  "
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def prio_calculated(row):\n",
    "    if not pd.isnull(row['calculated_tx_dollar_value_net']):\n",
    "        return row['calculated_tx_dollar_value_net']\n",
    "    else:\n",
    "        return row['dollar_value_net']\n",
    "\n",
    "# Apply the function to create the new column\n",
    "\n",
    "final_test_all['calculated_dollar_value_priority'] = final_test_all.apply(prio_calculated, axis=1)\n",
    "\n",
    "results = final_test_all.groupby(['tx_hash', 'index', 'token_name', 'sender_gave_this'])[['calculated_dollar_value_priority']].sum().reset_index()\n",
    "results.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/qq/p76g00dx6zv_qlpx33vlcr2c0000gn/T/ipykernel_39429/2978873917.py:10: DeprecationWarning: DataFrameGroupBy.apply operated on the grouping columns. This behavior is deprecated, and in a future version of pandas the grouping columns will be excluded from the operation. Either pass `include_groups=False` to exclude the groupings or explicitly select the grouping columns after groupby to silence this warning.\n",
      "  result = results.groupby(['tx_hash', 'token_name']).apply(calculate_differences).reset_index()\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>tx_hash</th>\n",
       "      <th>token_name</th>\n",
       "      <th>difference_calculated</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0x012778bb6330737bed53ca488e582500498d81e1db22...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0x012778bb6330737bed53ca488e582500498d81e1db22...</td>\n",
       "      <td>KAIJUNO8</td>\n",
       "      <td>-204.113601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...</td>\n",
       "      <td>Jim</td>\n",
       "      <td>-46.318267</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0x1a5eafe643fb4e41e4666f045341584ea22bb73d5857...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-76.640316</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             tx_hash token_name  \\\n",
       "0  0x012778bb6330737bed53ca488e582500498d81e1db22...   Ethereum   \n",
       "1  0x012778bb6330737bed53ca488e582500498d81e1db22...   KAIJUNO8   \n",
       "2  0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...   Ethereum   \n",
       "3  0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...        Jim   \n",
       "4  0x1a5eafe643fb4e41e4666f045341584ea22bb73d5857...   Ethereum   \n",
       "\n",
       "   difference_calculated  \n",
       "0               0.000000  \n",
       "1            -204.113601  \n",
       "2               0.000000  \n",
       "3             -46.318267  \n",
       "4             -76.640316  "
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Define a function to calculate differences in dollars of each coin\n",
    "def calculate_differences(group):\n",
    "    if len(group) == 2:\n",
    "        difference_calculated = group.loc[group['index'] != 0, 'calculated_dollar_value_priority'].values[0] - \\\n",
    "                                group.loc[group['index'] == 0, 'calculated_dollar_value_priority'].values[0]\n",
    "        return pd.Series({'difference_calculated': difference_calculated})\n",
    "    else:\n",
    "        return pd.Series({'difference_calculated': np.nan})\n",
    "\n",
    "result = results.groupby(['tx_hash', 'token_name']).apply(calculate_differences).reset_index()\n",
    "\n",
    "# for each tx there is the same amount of rows as tokens where the sender is involved (so 2 in case of normal transactions)\n",
    "result.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "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>tx_hash</th>\n",
       "      <th>stable_token_name</th>\n",
       "      <th>sender_gave_this</th>\n",
       "      <th>stable_dollar_value_net</th>\n",
       "      <th>stable_raw_amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0x012778bb6330737bed53ca488e582500498d81e1db22...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>True</td>\n",
       "      <td>-620.672021</td>\n",
       "      <td>0.200000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>True</td>\n",
       "      <td>-9310.080322</td>\n",
       "      <td>3.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0x1a5eafe643fb4e41e4666f045341584ea22bb73d5857...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>False</td>\n",
       "      <td>15404.813333</td>\n",
       "      <td>4.963914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0x1bcc89149f51daa2d79355a340d576af22555be34567...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>True</td>\n",
       "      <td>-6827.392236</td>\n",
       "      <td>2.200000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>False</td>\n",
       "      <td>1241.344043</td>\n",
       "      <td>0.400000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             tx_hash stable_token_name  \\\n",
       "0  0x012778bb6330737bed53ca488e582500498d81e1db22...          Ethereum   \n",
       "1  0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...          Ethereum   \n",
       "2  0x1a5eafe643fb4e41e4666f045341584ea22bb73d5857...          Ethereum   \n",
       "3  0x1bcc89149f51daa2d79355a340d576af22555be34567...          Ethereum   \n",
       "4  0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...          Ethereum   \n",
       "\n",
       "   sender_gave_this  stable_dollar_value_net  stable_raw_amount  \n",
       "0              True              -620.672021           0.200000  \n",
       "1              True             -9310.080322           3.000000  \n",
       "2             False             15404.813333           4.963914  \n",
       "3              True             -6827.392236           2.200000  \n",
       "4             False              1241.344043           0.400000  "
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#calcualte the delta in stable coins for the tx \n",
    "stable_coins_df_index_0_2 = df_senders[(df_senders['token_name'].isin(stable_coins)) & (df_senders['index'] == 0) & df_senders['raw_amount'] != 0][['tx_hash', 'token_name', 'dollar_value_net', 'raw_amount',  'sender_gave_this']]\n",
    "stable_coins_df_index_0_2 = stable_coins_df_index_0_2.groupby(['tx_hash', 'token_name', 'sender_gave_this'])[['dollar_value_net', 'raw_amount']].sum().reset_index()\n",
    "stable_coins_df_index_0_2.rename(columns= {'token_name': 'stable_token_name', 'dollar_value_net': 'stable_dollar_value_net', 'raw_amount': 'stable_raw_amount'}, inplace = True)\n",
    "stable_coins_df_index_0_2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "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>tx_hash</th>\n",
       "      <th>other_token_name</th>\n",
       "      <th>sender_gave_this</th>\n",
       "      <th>other_dollar_value_net</th>\n",
       "      <th>other_raw_amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0x012778bb6330737bed53ca488e582500498d81e1db22...</td>\n",
       "      <td>KAIJUNO8</td>\n",
       "      <td>False</td>\n",
       "      <td>338.104092</td>\n",
       "      <td>1.654453e+28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...</td>\n",
       "      <td>Jim</td>\n",
       "      <td>False</td>\n",
       "      <td>4930.863232</td>\n",
       "      <td>6.627504e+26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0x1a5eafe643fb4e41e4666f045341584ea22bb73d5857...</td>\n",
       "      <td>enqAI</td>\n",
       "      <td>True</td>\n",
       "      <td>-4124.942422</td>\n",
       "      <td>1.600000e+23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0x1bcc89149f51daa2d79355a340d576af22555be34567...</td>\n",
       "      <td>ClosedAI</td>\n",
       "      <td>False</td>\n",
       "      <td>95.701486</td>\n",
       "      <td>2.537020e+23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...</td>\n",
       "      <td>0xb0699d63aef20df3f1cffa9ca2bb8670416271d2</td>\n",
       "      <td>True</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>8.074525e+20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             tx_hash  \\\n",
       "0  0x012778bb6330737bed53ca488e582500498d81e1db22...   \n",
       "1  0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...   \n",
       "2  0x1a5eafe643fb4e41e4666f045341584ea22bb73d5857...   \n",
       "3  0x1bcc89149f51daa2d79355a340d576af22555be34567...   \n",
       "4  0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...   \n",
       "\n",
       "                             other_token_name  sender_gave_this  \\\n",
       "0                                    KAIJUNO8             False   \n",
       "1                                         Jim             False   \n",
       "2                                       enqAI              True   \n",
       "3                                    ClosedAI             False   \n",
       "4  0xb0699d63aef20df3f1cffa9ca2bb8670416271d2              True   \n",
       "\n",
       "   other_dollar_value_net  other_raw_amount  \n",
       "0              338.104092      1.654453e+28  \n",
       "1             4930.863232      6.627504e+26  \n",
       "2            -4124.942422      1.600000e+23  \n",
       "3               95.701486      2.537020e+23  \n",
       "4                0.000000      8.074525e+20  "
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#calcualte the delta in other coins for the tx\n",
    "other_coins_df_index_0_2 = df_senders[(df_senders['index'] == 0) & (~df_senders.token_name.isin(stable_coins))][['tx_hash', 'token_name', 'dollar_value_net', 'raw_amount',  'sender_gave_this']]\n",
    "other_coins_df_index_0_2 = other_coins_df_index_0_2.groupby(['tx_hash', 'token_name', 'sender_gave_this'])[['dollar_value_net', 'raw_amount']].sum().reset_index()\n",
    "other_coins_df_index_0_2.rename(columns= {'token_name': 'other_token_name', 'dollar_value_net': 'other_dollar_value_net', 'raw_amount': 'other_raw_amount'}, inplace = True)\n",
    "other_coins_df_index_0_2.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "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>tx_hash</th>\n",
       "      <th>other_token_name</th>\n",
       "      <th>stable_token_name</th>\n",
       "      <th>ratio_other_to_stable_index_0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0x012778bb6330737bed53ca488e582500498d81e1db22...</td>\n",
       "      <td>KAIJUNO8</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>1.208858e-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...</td>\n",
       "      <td>Jim</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>4.526591e-27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0x1a5eafe643fb4e41e4666f045341584ea22bb73d5857...</td>\n",
       "      <td>enqAI</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>3.102446e-23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0x1bcc89149f51daa2d79355a340d576af22555be34567...</td>\n",
       "      <td>ClosedAI</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>8.671590e-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...</td>\n",
       "      <td>0xb0699d63aef20df3f1cffa9ca2bb8670416271d2</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>4.953852e-22</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             tx_hash  \\\n",
       "0  0x012778bb6330737bed53ca488e582500498d81e1db22...   \n",
       "1  0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...   \n",
       "2  0x1a5eafe643fb4e41e4666f045341584ea22bb73d5857...   \n",
       "3  0x1bcc89149f51daa2d79355a340d576af22555be34567...   \n",
       "4  0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...   \n",
       "\n",
       "                             other_token_name stable_token_name  \\\n",
       "0                                    KAIJUNO8          Ethereum   \n",
       "1                                         Jim          Ethereum   \n",
       "2                                       enqAI          Ethereum   \n",
       "3                                    ClosedAI          Ethereum   \n",
       "4  0xb0699d63aef20df3f1cffa9ca2bb8670416271d2          Ethereum   \n",
       "\n",
       "   ratio_other_to_stable_index_0  \n",
       "0                   1.208858e-29  \n",
       "1                   4.526591e-27  \n",
       "2                   3.102446e-23  \n",
       "3                   8.671590e-24  \n",
       "4                   4.953852e-22  "
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get the ratios between other token and stable token\n",
    "token_ratios = pd.merge(other_coins_df_index_0_2, stable_coins_df_index_0_2, on = ['tx_hash'])\n",
    "token_ratios['ratio_other_to_stable_index_0'] = token_ratios['stable_raw_amount'] / token_ratios['other_raw_amount'] \n",
    "token_ratios = token_ratios[['tx_hash', 'other_token_name', 'stable_token_name', 'ratio_other_to_stable_index_0']]\n",
    "token_ratios.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "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>tx_hash</th>\n",
       "      <th>token_name_A</th>\n",
       "      <th>token_A_delta_raw_amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0x012778bb6330737bed53ca488e582500498d81e1db22...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-0.065772</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-0.014925</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0x36453126f7b08f65178da484754a9195f7f044b032bc...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-0.037313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0x623bae0e273896d748142353d5cec32276bf7f49b0ec...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-0.054074</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0xc1a796c24a30a5c110b7cea4123085339445bb1953c0...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-0.232437</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             tx_hash token_name_A  \\\n",
       "0  0x012778bb6330737bed53ca488e582500498d81e1db22...     Ethereum   \n",
       "1  0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...     Ethereum   \n",
       "2  0x36453126f7b08f65178da484754a9195f7f044b032bc...     Ethereum   \n",
       "3  0x623bae0e273896d748142353d5cec32276bf7f49b0ec...     Ethereum   \n",
       "4  0xc1a796c24a30a5c110b7cea4123085339445bb1953c0...     Ethereum   \n",
       "\n",
       "   token_A_delta_raw_amount  \n",
       "0                 -0.065772  \n",
       "1                 -0.014925  \n",
       "2                 -0.037313  \n",
       "3                 -0.054074  \n",
       "4                 -0.232437  "
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nearly_final_empty_A = nearly_final[(nearly_final['token_name_A'].isin(stable_coins)) & (nearly_final['token_A_delta_raw_amount'] == 0)]\n",
    "nearly_final_empty_B = nearly_final[(nearly_final['token_name_B'].isin(stable_coins)) & (nearly_final['token_B_delta_raw_amount'] == 0)]\n",
    "nearly_final_empty_A = pd.merge(nearly_final_empty_A, token_ratios, on = 'tx_hash')\n",
    "nearly_final_empty_B = pd.merge(nearly_final_empty_B, token_ratios, on = 'tx_hash')\n",
    "nearly_final_empty_A['token_A_delta_raw_amount'] = nearly_final_empty_A['token_B_delta_raw_amount'] * nearly_final_empty_A['ratio_other_to_stable_index_0']\n",
    "nearly_final_empty_B['token_B_delta_raw_amount'] = nearly_final_empty_B['token_A_delta_raw_amount'] * nearly_final_empty_B['ratio_other_to_stable_index_0']\n",
    "\n",
    "nearly_final_empty_A[['tx_hash', 'token_name_A', 'token_A_delta_raw_amount']].head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "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>tx_hash</th>\n",
       "      <th>token_name_A</th>\n",
       "      <th>token_A_delta_raw_amount_x</th>\n",
       "      <th>token_A_delta_dollar_tenderly</th>\n",
       "      <th>token_name_B</th>\n",
       "      <th>token_B_delta_raw_amount_x</th>\n",
       "      <th>token_B_delta_dollar_tenderly</th>\n",
       "      <th>token_A_delta_raw_amount_y</th>\n",
       "      <th>token_B_delta_raw_amount_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0x012778bb6330737bed53ca488e582500498d81e1db22...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-6.577181e-02</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>KAIJUNO8</td>\n",
       "      <td>-5.440819e+27</td>\n",
       "      <td>-111.188585</td>\n",
       "      <td>-0.065772</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-1.492520e-02</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>Jim</td>\n",
       "      <td>-3.297227e+24</td>\n",
       "      <td>-24.531371</td>\n",
       "      <td>-0.014925</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0x1a5eafe643fb4e41e4666f045341584ea22bb73d5857...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-2.469591e-02</td>\n",
       "      <td>-76.640316</td>\n",
       "      <td>enqAI</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0x1bcc89149f51daa2d79355a340d576af22555be34567...</td>\n",
       "      <td>ClosedAI</td>\n",
       "      <td>-1.446769e+21</td>\n",
       "      <td>-0.545750</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-1.254579e-02</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.012546</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...</td>\n",
       "      <td>0xb0699d63aef20df3f1cffa9ca2bb8670416271d2</td>\n",
       "      <td>-4.175898e+19</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-2.068678e-02</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.020687</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             tx_hash  \\\n",
       "0  0x012778bb6330737bed53ca488e582500498d81e1db22...   \n",
       "1  0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...   \n",
       "2  0x1a5eafe643fb4e41e4666f045341584ea22bb73d5857...   \n",
       "3  0x1bcc89149f51daa2d79355a340d576af22555be34567...   \n",
       "4  0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...   \n",
       "\n",
       "                                 token_name_A  token_A_delta_raw_amount_x  \\\n",
       "0                                    Ethereum               -6.577181e-02   \n",
       "1                                    Ethereum               -1.492520e-02   \n",
       "2                                    Ethereum               -2.469591e-02   \n",
       "3                                    ClosedAI               -1.446769e+21   \n",
       "4  0xb0699d63aef20df3f1cffa9ca2bb8670416271d2               -4.175898e+19   \n",
       "\n",
       "   token_A_delta_dollar_tenderly token_name_B  token_B_delta_raw_amount_x  \\\n",
       "0                       0.000000     KAIJUNO8               -5.440819e+27   \n",
       "1                       0.000000          Jim               -3.297227e+24   \n",
       "2                     -76.640316        enqAI                         NaN   \n",
       "3                      -0.545750     Ethereum               -1.254579e-02   \n",
       "4                       0.000000     Ethereum               -2.068678e-02   \n",
       "\n",
       "   token_B_delta_dollar_tenderly  token_A_delta_raw_amount_y  \\\n",
       "0                    -111.188585                   -0.065772   \n",
       "1                     -24.531371                   -0.014925   \n",
       "2                       0.000000                         NaN   \n",
       "3                       0.000000                         NaN   \n",
       "4                       0.000000                         NaN   \n",
       "\n",
       "   token_B_delta_raw_amount_y  \n",
       "0                         NaN  \n",
       "1                         NaN  \n",
       "2                         NaN  \n",
       "3                   -0.012546  \n",
       "4                   -0.020687  "
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# merge them all to get the delata in all coins\n",
    "delta_stable = pd.merge(nearly_final,nearly_final_empty_A[['tx_hash', 'token_name_A', 'token_name_B', 'token_A_delta_raw_amount']], on = ['tx_hash', 'token_name_A', 'token_name_B'], how = 'left')\n",
    "delta_stable.loc[delta_stable['token_A_delta_raw_amount_x'] == 0, 'token_A_delta_raw_amount_x'] = delta_stable['token_A_delta_raw_amount_y']\n",
    "delta_stable_2 =  pd.merge(delta_stable, nearly_final_empty_B[['tx_hash', 'token_name_A', 'token_name_B', 'token_B_delta_raw_amount']], on = ['tx_hash', 'token_name_A', 'token_name_B'], how = 'left')\n",
    "delta_stable_2.loc[delta_stable_2['token_B_delta_raw_amount_x'] == 0, 'token_B_delta_raw_amount_x'] = delta_stable_2['token_B_delta_raw_amount_y']\n",
    "delta_stable_2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "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>tx_hash</th>\n",
       "      <th>token_name_A</th>\n",
       "      <th>token_A_delta_raw_amount_x</th>\n",
       "      <th>token_A_delta_dollar_tenderly</th>\n",
       "      <th>token_name_B</th>\n",
       "      <th>token_B_delta_raw_amount_x</th>\n",
       "      <th>token_B_delta_dollar_tenderly</th>\n",
       "      <th>token_A_delta_raw_amount_y</th>\n",
       "      <th>token_B_delta_raw_amount_y</th>\n",
       "      <th>token_name_stable</th>\n",
       "      <th>token_delta_stable</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0x012778bb6330737bed53ca488e582500498d81e1db22...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-6.577181e-02</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>KAIJUNO8</td>\n",
       "      <td>-5.440819e+27</td>\n",
       "      <td>-111.188585</td>\n",
       "      <td>-0.065772</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-0.065772</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-1.492520e-02</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>Jim</td>\n",
       "      <td>-3.297227e+24</td>\n",
       "      <td>-24.531371</td>\n",
       "      <td>-0.014925</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-0.014925</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0x1a5eafe643fb4e41e4666f045341584ea22bb73d5857...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-2.469591e-02</td>\n",
       "      <td>-76.640316</td>\n",
       "      <td>enqAI</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-0.024696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0x1bcc89149f51daa2d79355a340d576af22555be34567...</td>\n",
       "      <td>ClosedAI</td>\n",
       "      <td>-1.446769e+21</td>\n",
       "      <td>-0.545750</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-1.254579e-02</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.012546</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-0.012546</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...</td>\n",
       "      <td>0xb0699d63aef20df3f1cffa9ca2bb8670416271d2</td>\n",
       "      <td>-4.175898e+19</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-2.068678e-02</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.020687</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-0.020687</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             tx_hash  \\\n",
       "0  0x012778bb6330737bed53ca488e582500498d81e1db22...   \n",
       "1  0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...   \n",
       "2  0x1a5eafe643fb4e41e4666f045341584ea22bb73d5857...   \n",
       "3  0x1bcc89149f51daa2d79355a340d576af22555be34567...   \n",
       "4  0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...   \n",
       "\n",
       "                                 token_name_A  token_A_delta_raw_amount_x  \\\n",
       "0                                    Ethereum               -6.577181e-02   \n",
       "1                                    Ethereum               -1.492520e-02   \n",
       "2                                    Ethereum               -2.469591e-02   \n",
       "3                                    ClosedAI               -1.446769e+21   \n",
       "4  0xb0699d63aef20df3f1cffa9ca2bb8670416271d2               -4.175898e+19   \n",
       "\n",
       "   token_A_delta_dollar_tenderly token_name_B  token_B_delta_raw_amount_x  \\\n",
       "0                       0.000000     KAIJUNO8               -5.440819e+27   \n",
       "1                       0.000000          Jim               -3.297227e+24   \n",
       "2                     -76.640316        enqAI                         NaN   \n",
       "3                      -0.545750     Ethereum               -1.254579e-02   \n",
       "4                       0.000000     Ethereum               -2.068678e-02   \n",
       "\n",
       "   token_B_delta_dollar_tenderly  token_A_delta_raw_amount_y  \\\n",
       "0                    -111.188585                   -0.065772   \n",
       "1                     -24.531371                   -0.014925   \n",
       "2                       0.000000                         NaN   \n",
       "3                       0.000000                         NaN   \n",
       "4                       0.000000                         NaN   \n",
       "\n",
       "   token_B_delta_raw_amount_y token_name_stable  token_delta_stable  \n",
       "0                         NaN          Ethereum           -0.065772  \n",
       "1                         NaN          Ethereum           -0.014925  \n",
       "2                         NaN          Ethereum           -0.024696  \n",
       "3                   -0.012546          Ethereum           -0.012546  \n",
       "4                   -0.020687          Ethereum           -0.020687  "
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def select_stable_token(row, column_A, column_B):\n",
    "    if row['token_name_A'] in stable_coins:\n",
    "        return row[column_A]\n",
    "    elif row['token_name_B'] in stable_coins:\n",
    "        return row[column_B]\n",
    "    else:\n",
    "        return None  # Return None if neither token is in stable_coins\n",
    "\n",
    "\n",
    "delta_stable_2['token_name_stable'] = delta_stable_2.apply(lambda row: select_stable_token(row, 'token_name_A', 'token_name_B'), axis=1)\n",
    "delta_stable_2['token_delta_stable'] = delta_stable_2.apply(lambda row: select_stable_token(row, 'token_A_delta_raw_amount_x', 'token_B_delta_raw_amount_x'), axis=1)\n",
    "\n",
    "delta_stable_2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "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>tx_hash</th>\n",
       "      <th>difference_calculated</th>\n",
       "      <th>token_name_A</th>\n",
       "      <th>token_A_delta_raw_amount_x</th>\n",
       "      <th>token_A_delta_dollar_tenderly</th>\n",
       "      <th>token_name_B</th>\n",
       "      <th>token_B_delta_raw_amount_x</th>\n",
       "      <th>token_B_delta_dollar_tenderly</th>\n",
       "      <th>token_A_delta_raw_amount_y</th>\n",
       "      <th>token_B_delta_raw_amount_y</th>\n",
       "      <th>token_name_stable</th>\n",
       "      <th>token_delta_stable</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0x012778bb6330737bed53ca488e582500498d81e1db22...</td>\n",
       "      <td>-204.113601</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-6.577181e-02</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>KAIJUNO8</td>\n",
       "      <td>-5.440819e+27</td>\n",
       "      <td>-111.188585</td>\n",
       "      <td>-0.065772</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-0.065772</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...</td>\n",
       "      <td>-46.318267</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-1.492520e-02</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>Jim</td>\n",
       "      <td>-3.297227e+24</td>\n",
       "      <td>-24.531371</td>\n",
       "      <td>-0.014925</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-0.014925</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0x1a5eafe643fb4e41e4666f045341584ea22bb73d5857...</td>\n",
       "      <td>-76.640316</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-2.469591e-02</td>\n",
       "      <td>-76.640316</td>\n",
       "      <td>enqAI</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-0.024696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0x1bcc89149f51daa2d79355a340d576af22555be34567...</td>\n",
       "      <td>-38.934096</td>\n",
       "      <td>ClosedAI</td>\n",
       "      <td>-1.446769e+21</td>\n",
       "      <td>-0.545750</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-1.254579e-02</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.012546</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-0.012546</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...</td>\n",
       "      <td>-64.198517</td>\n",
       "      <td>0xb0699d63aef20df3f1cffa9ca2bb8670416271d2</td>\n",
       "      <td>-4.175898e+19</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-2.068678e-02</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.020687</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-0.020687</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             tx_hash  difference_calculated  \\\n",
       "0  0x012778bb6330737bed53ca488e582500498d81e1db22...            -204.113601   \n",
       "1  0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...             -46.318267   \n",
       "2  0x1a5eafe643fb4e41e4666f045341584ea22bb73d5857...             -76.640316   \n",
       "3  0x1bcc89149f51daa2d79355a340d576af22555be34567...             -38.934096   \n",
       "4  0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...             -64.198517   \n",
       "\n",
       "                                 token_name_A  token_A_delta_raw_amount_x  \\\n",
       "0                                    Ethereum               -6.577181e-02   \n",
       "1                                    Ethereum               -1.492520e-02   \n",
       "2                                    Ethereum               -2.469591e-02   \n",
       "3                                    ClosedAI               -1.446769e+21   \n",
       "4  0xb0699d63aef20df3f1cffa9ca2bb8670416271d2               -4.175898e+19   \n",
       "\n",
       "   token_A_delta_dollar_tenderly token_name_B  token_B_delta_raw_amount_x  \\\n",
       "0                       0.000000     KAIJUNO8               -5.440819e+27   \n",
       "1                       0.000000          Jim               -3.297227e+24   \n",
       "2                     -76.640316        enqAI                         NaN   \n",
       "3                      -0.545750     Ethereum               -1.254579e-02   \n",
       "4                       0.000000     Ethereum               -2.068678e-02   \n",
       "\n",
       "   token_B_delta_dollar_tenderly  token_A_delta_raw_amount_y  \\\n",
       "0                    -111.188585                   -0.065772   \n",
       "1                     -24.531371                   -0.014925   \n",
       "2                       0.000000                         NaN   \n",
       "3                       0.000000                         NaN   \n",
       "4                       0.000000                         NaN   \n",
       "\n",
       "   token_B_delta_raw_amount_y token_name_stable  token_delta_stable  \n",
       "0                         NaN          Ethereum           -0.065772  \n",
       "1                         NaN          Ethereum           -0.014925  \n",
       "2                         NaN          Ethereum           -0.024696  \n",
       "3                   -0.012546          Ethereum           -0.012546  \n",
       "4                   -0.020687          Ethereum           -0.020687  "
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dollar_diff = result.groupby('tx_hash')[['difference_calculated']].sum().reset_index()\n",
    "stable_delta = pd.merge(dollar_diff, delta_stable_2, on = 'tx_hash', how = 'left')\n",
    "stable_delta.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Calculate the value in dollars based on the price of the stable coin"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "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>tx_hash</th>\n",
       "      <th>token_name_stable</th>\n",
       "      <th>stable_token_dollar_value_0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>3103.360107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>0xee8fd2c76181afa14ca0da158e0a01bba2d3df8e62c5...</td>\n",
       "      <td>USDC</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>0x77929a2b313bd1f5b15c9dbc61cc598d21e6f3594b30...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>3103.360107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>3103.360107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>0x4f9b4cddc272c3803df798041fff63ae7e439982810f...</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>3103.360107</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              tx_hash token_name_stable  \\\n",
       "4   0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...          Ethereum   \n",
       "15  0xee8fd2c76181afa14ca0da158e0a01bba2d3df8e62c5...              USDC   \n",
       "29  0x77929a2b313bd1f5b15c9dbc61cc598d21e6f3594b30...          Ethereum   \n",
       "39  0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...          Ethereum   \n",
       "50  0x4f9b4cddc272c3803df798041fff63ae7e439982810f...          Ethereum   \n",
       "\n",
       "    stable_token_dollar_value_0  \n",
       "4                   3103.360107  \n",
       "15                     1.000000  \n",
       "29                  3103.360107  \n",
       "39                  3103.360107  \n",
       "50                  3103.360107  "
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stable_value_index_0 = df_senders[(df_senders['index'] == 0) & (df_senders['token_name'].isin(stable_coins))][['tx_hash', 'token_name', 'token_dollar_value']]\n",
    "stable_value_index_0.rename(columns = {'token_dollar_value' : 'stable_token_dollar_value_0', 'token_name' : 'token_name_stable'}, inplace = True)\n",
    "stable_value_index_0.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/qq/p76g00dx6zv_qlpx33vlcr2c0000gn/T/ipykernel_39429/1552369537.py:8: FutureWarning: A value is trying to be set on a copy of a DataFrame or Series through chained assignment using an inplace method.\n",
      "The behavior will change in pandas 3.0. This inplace method will never work because the intermediate object on which we are setting values always behaves as a copy.\n",
      "\n",
      "For example, when doing 'df[col].method(value, inplace=True)', try using 'df.method({col: value}, inplace=True)' or df[col] = df[col].method(value) instead, to perform the operation inplace on the original object.\n",
      "\n",
      "\n",
      "  stable_delta_with_dollar['delta_dollar'].fillna(stable_delta_with_dollar['difference_calculated'], inplace=True)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>tx_hash</th>\n",
       "      <th>difference_calculated</th>\n",
       "      <th>token_name_A</th>\n",
       "      <th>token_A_delta_raw_amount_x</th>\n",
       "      <th>token_A_delta_dollar_tenderly</th>\n",
       "      <th>token_name_B</th>\n",
       "      <th>token_B_delta_raw_amount_x</th>\n",
       "      <th>token_B_delta_dollar_tenderly</th>\n",
       "      <th>token_A_delta_raw_amount_y</th>\n",
       "      <th>token_B_delta_raw_amount_y</th>\n",
       "      <th>token_name_stable</th>\n",
       "      <th>token_delta_stable</th>\n",
       "      <th>stable_token_dollar_value_0</th>\n",
       "      <th>delta_dollar</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0x012778bb6330737bed53ca488e582500498d81e1db22...</td>\n",
       "      <td>-204.113601</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-6.577181e-02</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>KAIJUNO8</td>\n",
       "      <td>-5.440819e+27</td>\n",
       "      <td>-111.188585</td>\n",
       "      <td>-0.065772</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-0.065772</td>\n",
       "      <td>3103.360107</td>\n",
       "      <td>-204.113601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...</td>\n",
       "      <td>-46.318267</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-1.492520e-02</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>Jim</td>\n",
       "      <td>-3.297227e+24</td>\n",
       "      <td>-24.531371</td>\n",
       "      <td>-0.014925</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-0.014925</td>\n",
       "      <td>3103.360107</td>\n",
       "      <td>-46.318267</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0x1a5eafe643fb4e41e4666f045341584ea22bb73d5857...</td>\n",
       "      <td>-76.640316</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-2.469591e-02</td>\n",
       "      <td>-76.640316</td>\n",
       "      <td>enqAI</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-0.024696</td>\n",
       "      <td>3103.360107</td>\n",
       "      <td>-76.640316</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0x1bcc89149f51daa2d79355a340d576af22555be34567...</td>\n",
       "      <td>-38.934096</td>\n",
       "      <td>ClosedAI</td>\n",
       "      <td>-1.446769e+21</td>\n",
       "      <td>-0.545750</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-1.254579e-02</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.012546</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-0.012546</td>\n",
       "      <td>3103.360107</td>\n",
       "      <td>-38.934096</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...</td>\n",
       "      <td>-64.198517</td>\n",
       "      <td>0xb0699d63aef20df3f1cffa9ca2bb8670416271d2</td>\n",
       "      <td>-4.175898e+19</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-2.068678e-02</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.020687</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-0.020687</td>\n",
       "      <td>3103.360107</td>\n",
       "      <td>-64.198517</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             tx_hash  difference_calculated  \\\n",
       "0  0x012778bb6330737bed53ca488e582500498d81e1db22...            -204.113601   \n",
       "1  0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...             -46.318267   \n",
       "2  0x1a5eafe643fb4e41e4666f045341584ea22bb73d5857...             -76.640316   \n",
       "3  0x1bcc89149f51daa2d79355a340d576af22555be34567...             -38.934096   \n",
       "4  0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...             -64.198517   \n",
       "\n",
       "                                 token_name_A  token_A_delta_raw_amount_x  \\\n",
       "0                                    Ethereum               -6.577181e-02   \n",
       "1                                    Ethereum               -1.492520e-02   \n",
       "2                                    Ethereum               -2.469591e-02   \n",
       "3                                    ClosedAI               -1.446769e+21   \n",
       "4  0xb0699d63aef20df3f1cffa9ca2bb8670416271d2               -4.175898e+19   \n",
       "\n",
       "   token_A_delta_dollar_tenderly token_name_B  token_B_delta_raw_amount_x  \\\n",
       "0                       0.000000     KAIJUNO8               -5.440819e+27   \n",
       "1                       0.000000          Jim               -3.297227e+24   \n",
       "2                     -76.640316        enqAI                         NaN   \n",
       "3                      -0.545750     Ethereum               -1.254579e-02   \n",
       "4                       0.000000     Ethereum               -2.068678e-02   \n",
       "\n",
       "   token_B_delta_dollar_tenderly  token_A_delta_raw_amount_y  \\\n",
       "0                    -111.188585                   -0.065772   \n",
       "1                     -24.531371                   -0.014925   \n",
       "2                       0.000000                         NaN   \n",
       "3                       0.000000                         NaN   \n",
       "4                       0.000000                         NaN   \n",
       "\n",
       "   token_B_delta_raw_amount_y token_name_stable  token_delta_stable  \\\n",
       "0                         NaN          Ethereum           -0.065772   \n",
       "1                         NaN          Ethereum           -0.014925   \n",
       "2                         NaN          Ethereum           -0.024696   \n",
       "3                   -0.012546          Ethereum           -0.012546   \n",
       "4                   -0.020687          Ethereum           -0.020687   \n",
       "\n",
       "   stable_token_dollar_value_0  delta_dollar  \n",
       "0                  3103.360107   -204.113601  \n",
       "1                  3103.360107    -46.318267  \n",
       "2                  3103.360107    -76.640316  \n",
       "3                  3103.360107    -38.934096  \n",
       "4                  3103.360107    -64.198517  "
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stable_delta_with_dollar = pd.merge(stable_delta, stable_value_index_0, on = ['tx_hash', 'token_name_stable'], how = 'left')\n",
    "def calculate_tx_dollar_loss(row):\n",
    "    if row['token_name_stable'] is not None:\n",
    "        return row['token_delta_stable'] * row['stable_token_dollar_value_0']\n",
    "\n",
    "# Apply custom function to create 'delta_dollar' column\n",
    "stable_delta_with_dollar['delta_dollar'] = stable_delta_with_dollar.apply(calculate_tx_dollar_loss, axis=1)\n",
    "stable_delta_with_dollar['delta_dollar'].fillna(stable_delta_with_dollar['difference_calculated'], inplace=True)\n",
    "stable_delta_with_dollar.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add the sender row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "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>tx_hash</th>\n",
       "      <th>sender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...</td>\n",
       "      <td>0x36a2ffb33c1b427c46c3d30adac3ca4e8ed36179</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0xee8fd2c76181afa14ca0da158e0a01bba2d3df8e62c5...</td>\n",
       "      <td>0xbbf46514de004992d8dcbcec19f02f2e772ab51d</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0x77929a2b313bd1f5b15c9dbc61cc598d21e6f3594b30...</td>\n",
       "      <td>0xfb93f5128dae38523b8beea93a0f89c32b3f4932</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...</td>\n",
       "      <td>0x76ec733f445358232ea24aaf03d4536057439bfc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0x4f9b4cddc272c3803df798041fff63ae7e439982810f...</td>\n",
       "      <td>0x1518c211f611114decd1842894e08b803afcd27b</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             tx_hash  \\\n",
       "0  0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...   \n",
       "1  0xee8fd2c76181afa14ca0da158e0a01bba2d3df8e62c5...   \n",
       "2  0x77929a2b313bd1f5b15c9dbc61cc598d21e6f3594b30...   \n",
       "3  0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...   \n",
       "4  0x4f9b4cddc272c3803df798041fff63ae7e439982810f...   \n",
       "\n",
       "                                       sender  \n",
       "0  0x36a2ffb33c1b427c46c3d30adac3ca4e8ed36179  \n",
       "1  0xbbf46514de004992d8dcbcec19f02f2e772ab51d  \n",
       "2  0xfb93f5128dae38523b8beea93a0f89c32b3f4932  \n",
       "3  0x76ec733f445358232ea24aaf03d4536057439bfc  \n",
       "4  0x1518c211f611114decd1842894e08b803afcd27b  "
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "only_senders = df_senders[['tx_hash', 'sender']].drop_duplicates().reset_index(drop = True)\n",
    "only_senders.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "stable_delta_with_dollar = pd.merge(stable_delta_with_dollar, only_senders, on = 'tx_hash')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add the token_address row"
   ]
  },
  {
   "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>token_contract_address</th>\n",
       "      <th>token_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0xb0699d63aef20df3f1cffa9ca2bb8670416271d2</td>\n",
       "      <td>0xb0699d63aef20df3f1cffa9ca2bb8670416271d2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Ethereum</td>\n",
       "      <td>Ethereum</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48</td>\n",
       "      <td>USDC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>0x0b7f0e51cd1739d6c96982d55ad8fa634dd43a9c</td>\n",
       "      <td>Dream Machine Token</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>0x1db61c337e5216941f53e6a0e41eed9640aec8bb</td>\n",
       "      <td>0x1db61c337e5216941f53e6a0e41eed9640aec8bb</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        token_contract_address  \\\n",
       "0   0xb0699d63aef20df3f1cffa9ca2bb8670416271d2   \n",
       "4                                     Ethereum   \n",
       "15  0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48   \n",
       "16  0x0b7f0e51cd1739d6c96982d55ad8fa634dd43a9c   \n",
       "28  0x1db61c337e5216941f53e6a0e41eed9640aec8bb   \n",
       "\n",
       "                                    token_name  \n",
       "0   0xb0699d63aef20df3f1cffa9ca2bb8670416271d2  \n",
       "4                                     Ethereum  \n",
       "15                                        USDC  \n",
       "16                         Dream Machine Token  \n",
       "28  0x1db61c337e5216941f53e6a0e41eed9640aec8bb  "
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "token_name_address = df_senders[['token_contract_address', 'token_name']].drop_duplicates()\n",
    "token_name_address.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "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>tx_hash</th>\n",
       "      <th>difference_calculated</th>\n",
       "      <th>token_name_A</th>\n",
       "      <th>token_A_delta_raw_amount_x</th>\n",
       "      <th>token_A_delta_dollar_tenderly</th>\n",
       "      <th>token_name_B</th>\n",
       "      <th>token_B_delta_raw_amount_x</th>\n",
       "      <th>token_B_delta_dollar_tenderly</th>\n",
       "      <th>token_A_delta_raw_amount_y</th>\n",
       "      <th>token_B_delta_raw_amount_y</th>\n",
       "      <th>token_name_stable</th>\n",
       "      <th>token_delta_stable</th>\n",
       "      <th>stable_token_dollar_value_0</th>\n",
       "      <th>delta_dollar</th>\n",
       "      <th>sender</th>\n",
       "      <th>token_contract_address_A</th>\n",
       "      <th>token_contract_address_B</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0x012778bb6330737bed53ca488e582500498d81e1db22...</td>\n",
       "      <td>-204.113601</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-6.577181e-02</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>KAIJUNO8</td>\n",
       "      <td>-5.440819e+27</td>\n",
       "      <td>-111.188585</td>\n",
       "      <td>-0.065772</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-0.065772</td>\n",
       "      <td>3103.360107</td>\n",
       "      <td>-204.113601</td>\n",
       "      <td>0x9ea02f652955b90c0dd4f256003e4e339d3a4184</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>0x4fe8d4775b7cb2546b9ee86182081cdf8f77b053</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...</td>\n",
       "      <td>-46.318267</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-1.492520e-02</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>Jim</td>\n",
       "      <td>-3.297227e+24</td>\n",
       "      <td>-24.531371</td>\n",
       "      <td>-0.014925</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-0.014925</td>\n",
       "      <td>3103.360107</td>\n",
       "      <td>-46.318267</td>\n",
       "      <td>0x76ec733f445358232ea24aaf03d4536057439bfc</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>0xd807f7e2818db8eda0d28b5be74866338eaedb86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0x1a5eafe643fb4e41e4666f045341584ea22bb73d5857...</td>\n",
       "      <td>-76.640316</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-2.469591e-02</td>\n",
       "      <td>-76.640316</td>\n",
       "      <td>enqAI</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-0.024696</td>\n",
       "      <td>3103.360107</td>\n",
       "      <td>-76.640316</td>\n",
       "      <td>0x30049fd3dde7a46a6441a0d41dcb1cf5aaa93e77</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>0x710287d1d39dcf62094a83ebb3e736e79400068a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0x1bcc89149f51daa2d79355a340d576af22555be34567...</td>\n",
       "      <td>-38.934096</td>\n",
       "      <td>ClosedAI</td>\n",
       "      <td>-1.446769e+21</td>\n",
       "      <td>-0.545750</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-1.254579e-02</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.012546</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-0.012546</td>\n",
       "      <td>3103.360107</td>\n",
       "      <td>-38.934096</td>\n",
       "      <td>0xc4ba8f21c0dd755cfeff899c4f791c634ca5c6db</td>\n",
       "      <td>0x50b0696468f42cab1ddc76413a1312aff3cabdf6</td>\n",
       "      <td>Ethereum</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...</td>\n",
       "      <td>-64.198517</td>\n",
       "      <td>0xb0699d63aef20df3f1cffa9ca2bb8670416271d2</td>\n",
       "      <td>-4.175898e+19</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-2.068678e-02</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.020687</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-0.020687</td>\n",
       "      <td>3103.360107</td>\n",
       "      <td>-64.198517</td>\n",
       "      <td>0x36a2ffb33c1b427c46c3d30adac3ca4e8ed36179</td>\n",
       "      <td>0xb0699d63aef20df3f1cffa9ca2bb8670416271d2</td>\n",
       "      <td>Ethereum</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             tx_hash  difference_calculated  \\\n",
       "0  0x012778bb6330737bed53ca488e582500498d81e1db22...            -204.113601   \n",
       "1  0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...             -46.318267   \n",
       "2  0x1a5eafe643fb4e41e4666f045341584ea22bb73d5857...             -76.640316   \n",
       "3  0x1bcc89149f51daa2d79355a340d576af22555be34567...             -38.934096   \n",
       "4  0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...             -64.198517   \n",
       "\n",
       "                                 token_name_A  token_A_delta_raw_amount_x  \\\n",
       "0                                    Ethereum               -6.577181e-02   \n",
       "1                                    Ethereum               -1.492520e-02   \n",
       "2                                    Ethereum               -2.469591e-02   \n",
       "3                                    ClosedAI               -1.446769e+21   \n",
       "4  0xb0699d63aef20df3f1cffa9ca2bb8670416271d2               -4.175898e+19   \n",
       "\n",
       "   token_A_delta_dollar_tenderly token_name_B  token_B_delta_raw_amount_x  \\\n",
       "0                       0.000000     KAIJUNO8               -5.440819e+27   \n",
       "1                       0.000000          Jim               -3.297227e+24   \n",
       "2                     -76.640316        enqAI                         NaN   \n",
       "3                      -0.545750     Ethereum               -1.254579e-02   \n",
       "4                       0.000000     Ethereum               -2.068678e-02   \n",
       "\n",
       "   token_B_delta_dollar_tenderly  token_A_delta_raw_amount_y  \\\n",
       "0                    -111.188585                   -0.065772   \n",
       "1                     -24.531371                   -0.014925   \n",
       "2                       0.000000                         NaN   \n",
       "3                       0.000000                         NaN   \n",
       "4                       0.000000                         NaN   \n",
       "\n",
       "   token_B_delta_raw_amount_y token_name_stable  token_delta_stable  \\\n",
       "0                         NaN          Ethereum           -0.065772   \n",
       "1                         NaN          Ethereum           -0.014925   \n",
       "2                         NaN          Ethereum           -0.024696   \n",
       "3                   -0.012546          Ethereum           -0.012546   \n",
       "4                   -0.020687          Ethereum           -0.020687   \n",
       "\n",
       "   stable_token_dollar_value_0  delta_dollar  \\\n",
       "0                  3103.360107   -204.113601   \n",
       "1                  3103.360107    -46.318267   \n",
       "2                  3103.360107    -76.640316   \n",
       "3                  3103.360107    -38.934096   \n",
       "4                  3103.360107    -64.198517   \n",
       "\n",
       "                                       sender  \\\n",
       "0  0x9ea02f652955b90c0dd4f256003e4e339d3a4184   \n",
       "1  0x76ec733f445358232ea24aaf03d4536057439bfc   \n",
       "2  0x30049fd3dde7a46a6441a0d41dcb1cf5aaa93e77   \n",
       "3  0xc4ba8f21c0dd755cfeff899c4f791c634ca5c6db   \n",
       "4  0x36a2ffb33c1b427c46c3d30adac3ca4e8ed36179   \n",
       "\n",
       "                     token_contract_address_A  \\\n",
       "0                                    Ethereum   \n",
       "1                                    Ethereum   \n",
       "2                                    Ethereum   \n",
       "3  0x50b0696468f42cab1ddc76413a1312aff3cabdf6   \n",
       "4  0xb0699d63aef20df3f1cffa9ca2bb8670416271d2   \n",
       "\n",
       "                     token_contract_address_B  \n",
       "0  0x4fe8d4775b7cb2546b9ee86182081cdf8f77b053  \n",
       "1  0xd807f7e2818db8eda0d28b5be74866338eaedb86  \n",
       "2  0x710287d1d39dcf62094a83ebb3e736e79400068a  \n",
       "3                                    Ethereum  \n",
       "4                                    Ethereum  "
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge for token_name_A\n",
    "stable_delta_with_dollar = stable_delta_with_dollar.merge(\n",
    "    token_name_address.rename(columns={'token_name': 'token_name_A', 'token_contract_address': 'token_contract_address_A'}),\n",
    "    on='token_name_A', how='left')\n",
    "\n",
    "# Merge for token_name_B\n",
    "stable_delta_with_dollar = stable_delta_with_dollar.merge(\n",
    "    token_name_address.rename(columns={'token_name': 'token_name_B', 'token_contract_address': 'token_contract_address_B'}),\n",
    "    on='token_name_B', how='left')\n",
    "\n",
    "stable_delta_with_dollar.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###  Get the delta in ether at the same hour"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "#make sure the final df stable_delta_with_dollar has a timestamp column\n",
    "timestamps_per_transaction = df_senders[['tx_hash', 'timestamp']].drop_duplicates()\n",
    "stable_delta_with_dollar = pd.merge(stable_delta_with_dollar, timestamps_per_transaction, on = 'tx_hash')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1 entries, 0 to 0\n",
      "Data columns (total 2 columns):\n",
      " #   Column             Non-Null Count  Dtype  \n",
      "---  ------             --------------  -----  \n",
      " 0   timestamp          1 non-null      int64  \n",
      " 1   weth_dollar_value  1 non-null      float64\n",
      "dtypes: float64(1), int64(1)\n",
      "memory usage: 148.0 bytes\n"
     ]
    }
   ],
   "source": [
    "#value of the weth token at the different timestamps of the dataset\n",
    "weth_values = df_senders[df_senders['token_name'] == 'WETH'].groupby(['timestamp'])[['token_dollar_value']].mean().reset_index()\n",
    "weth_values.rename(columns= {'token_dollar_value' : 'weth_dollar_value'}, inplace = True)\n",
    "weth_values.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 25 entries, 0 to 24\n",
      "Data columns (total 1 columns):\n",
      " #   Column     Non-Null Count  Dtype\n",
      "---  ------     --------------  -----\n",
      " 0   timestamp  25 non-null     int64\n",
      "dtypes: int64(1)\n",
      "memory usage: 332.0 bytes\n"
     ]
    }
   ],
   "source": [
    "all_timestamps = pd.DataFrame({'timestamp': df_senders['timestamp'].unique()})\n",
    "all_timestamps.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "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>timestamp</th>\n",
       "      <th>weth_dollar_value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1710162059</td>\n",
       "      <td>3104.300049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1710162323</td>\n",
       "      <td>3104.300049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1710162371</td>\n",
       "      <td>3104.300049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1710162383</td>\n",
       "      <td>3104.300049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1710162587</td>\n",
       "      <td>3104.300049</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    timestamp  weth_dollar_value\n",
       "0  1710162059        3104.300049\n",
       "1  1710162323        3104.300049\n",
       "2  1710162371        3104.300049\n",
       "3  1710162383        3104.300049\n",
       "4  1710162587        3104.300049"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#one df with all the timestamps and the weth dollar value for the nearest timestamp\n",
    "weth_values_all = pd.merge_asof(all_timestamps, weth_values, on='timestamp', direction='nearest')\n",
    "weth_values_all['weth_dollar_value'] = weth_values_all['weth_dollar_value'].astype(float)\n",
    "weth_values_all.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "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>tx_hash</th>\n",
       "      <th>difference_calculated</th>\n",
       "      <th>token_name_A</th>\n",
       "      <th>token_A_delta_raw_amount_x</th>\n",
       "      <th>token_A_delta_dollar_tenderly</th>\n",
       "      <th>token_name_B</th>\n",
       "      <th>token_B_delta_raw_amount_x</th>\n",
       "      <th>token_B_delta_dollar_tenderly</th>\n",
       "      <th>token_A_delta_raw_amount_y</th>\n",
       "      <th>token_B_delta_raw_amount_y</th>\n",
       "      <th>token_name_stable</th>\n",
       "      <th>token_delta_stable</th>\n",
       "      <th>stable_token_dollar_value_0</th>\n",
       "      <th>delta_dollar</th>\n",
       "      <th>sender</th>\n",
       "      <th>token_contract_address_A</th>\n",
       "      <th>token_contract_address_B</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>weth_dollar_value</th>\n",
       "      <th>delta_eth</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0x012778bb6330737bed53ca488e582500498d81e1db22...</td>\n",
       "      <td>-204.113601</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-6.577181e-02</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>KAIJUNO8</td>\n",
       "      <td>-5.440819e+27</td>\n",
       "      <td>-111.188585</td>\n",
       "      <td>-0.065772</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-0.065772</td>\n",
       "      <td>3103.360107</td>\n",
       "      <td>-204.113601</td>\n",
       "      <td>0x9ea02f652955b90c0dd4f256003e4e339d3a4184</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>0x4fe8d4775b7cb2546b9ee86182081cdf8f77b053</td>\n",
       "      <td>1710163547</td>\n",
       "      <td>3104.300049</td>\n",
       "      <td>-0.065752</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...</td>\n",
       "      <td>-46.318267</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-1.492520e-02</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>Jim</td>\n",
       "      <td>-3.297227e+24</td>\n",
       "      <td>-24.531371</td>\n",
       "      <td>-0.014925</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-0.014925</td>\n",
       "      <td>3103.360107</td>\n",
       "      <td>-46.318267</td>\n",
       "      <td>0x76ec733f445358232ea24aaf03d4536057439bfc</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>0xd807f7e2818db8eda0d28b5be74866338eaedb86</td>\n",
       "      <td>1710162383</td>\n",
       "      <td>3104.300049</td>\n",
       "      <td>-0.014921</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0x1a5eafe643fb4e41e4666f045341584ea22bb73d5857...</td>\n",
       "      <td>-76.640316</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-2.469591e-02</td>\n",
       "      <td>-76.640316</td>\n",
       "      <td>enqAI</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-0.024696</td>\n",
       "      <td>3103.360107</td>\n",
       "      <td>-76.640316</td>\n",
       "      <td>0x30049fd3dde7a46a6441a0d41dcb1cf5aaa93e77</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>0x710287d1d39dcf62094a83ebb3e736e79400068a</td>\n",
       "      <td>1710163715</td>\n",
       "      <td>3104.300049</td>\n",
       "      <td>-0.024688</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0x1bcc89149f51daa2d79355a340d576af22555be34567...</td>\n",
       "      <td>-38.934096</td>\n",
       "      <td>ClosedAI</td>\n",
       "      <td>-1.446769e+21</td>\n",
       "      <td>-0.545750</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-1.254579e-02</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.012546</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-0.012546</td>\n",
       "      <td>3103.360107</td>\n",
       "      <td>-38.934096</td>\n",
       "      <td>0xc4ba8f21c0dd755cfeff899c4f791c634ca5c6db</td>\n",
       "      <td>0x50b0696468f42cab1ddc76413a1312aff3cabdf6</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>1710162587</td>\n",
       "      <td>3104.300049</td>\n",
       "      <td>-0.012542</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...</td>\n",
       "      <td>-64.198517</td>\n",
       "      <td>0xb0699d63aef20df3f1cffa9ca2bb8670416271d2</td>\n",
       "      <td>-4.175898e+19</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-2.068678e-02</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.020687</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>-0.020687</td>\n",
       "      <td>3103.360107</td>\n",
       "      <td>-64.198517</td>\n",
       "      <td>0x36a2ffb33c1b427c46c3d30adac3ca4e8ed36179</td>\n",
       "      <td>0xb0699d63aef20df3f1cffa9ca2bb8670416271d2</td>\n",
       "      <td>Ethereum</td>\n",
       "      <td>1710162059</td>\n",
       "      <td>3104.300049</td>\n",
       "      <td>-0.020681</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             tx_hash  difference_calculated  \\\n",
       "0  0x012778bb6330737bed53ca488e582500498d81e1db22...            -204.113601   \n",
       "1  0x08e622acdf6b27fe26f24e33815bb1a0789158f2d326...             -46.318267   \n",
       "2  0x1a5eafe643fb4e41e4666f045341584ea22bb73d5857...             -76.640316   \n",
       "3  0x1bcc89149f51daa2d79355a340d576af22555be34567...             -38.934096   \n",
       "4  0x1ee8d8e23e4a026ce8afbf2dc79c196c6b1d43d5e9f8...             -64.198517   \n",
       "\n",
       "                                 token_name_A  token_A_delta_raw_amount_x  \\\n",
       "0                                    Ethereum               -6.577181e-02   \n",
       "1                                    Ethereum               -1.492520e-02   \n",
       "2                                    Ethereum               -2.469591e-02   \n",
       "3                                    ClosedAI               -1.446769e+21   \n",
       "4  0xb0699d63aef20df3f1cffa9ca2bb8670416271d2               -4.175898e+19   \n",
       "\n",
       "   token_A_delta_dollar_tenderly token_name_B  token_B_delta_raw_amount_x  \\\n",
       "0                       0.000000     KAIJUNO8               -5.440819e+27   \n",
       "1                       0.000000          Jim               -3.297227e+24   \n",
       "2                     -76.640316        enqAI                         NaN   \n",
       "3                      -0.545750     Ethereum               -1.254579e-02   \n",
       "4                       0.000000     Ethereum               -2.068678e-02   \n",
       "\n",
       "   token_B_delta_dollar_tenderly  token_A_delta_raw_amount_y  \\\n",
       "0                    -111.188585                   -0.065772   \n",
       "1                     -24.531371                   -0.014925   \n",
       "2                       0.000000                         NaN   \n",
       "3                       0.000000                         NaN   \n",
       "4                       0.000000                         NaN   \n",
       "\n",
       "   token_B_delta_raw_amount_y token_name_stable  token_delta_stable  \\\n",
       "0                         NaN          Ethereum           -0.065772   \n",
       "1                         NaN          Ethereum           -0.014925   \n",
       "2                         NaN          Ethereum           -0.024696   \n",
       "3                   -0.012546          Ethereum           -0.012546   \n",
       "4                   -0.020687          Ethereum           -0.020687   \n",
       "\n",
       "   stable_token_dollar_value_0  delta_dollar  \\\n",
       "0                  3103.360107   -204.113601   \n",
       "1                  3103.360107    -46.318267   \n",
       "2                  3103.360107    -76.640316   \n",
       "3                  3103.360107    -38.934096   \n",
       "4                  3103.360107    -64.198517   \n",
       "\n",
       "                                       sender  \\\n",
       "0  0x9ea02f652955b90c0dd4f256003e4e339d3a4184   \n",
       "1  0x76ec733f445358232ea24aaf03d4536057439bfc   \n",
       "2  0x30049fd3dde7a46a6441a0d41dcb1cf5aaa93e77   \n",
       "3  0xc4ba8f21c0dd755cfeff899c4f791c634ca5c6db   \n",
       "4  0x36a2ffb33c1b427c46c3d30adac3ca4e8ed36179   \n",
       "\n",
       "                     token_contract_address_A  \\\n",
       "0                                    Ethereum   \n",
       "1                                    Ethereum   \n",
       "2                                    Ethereum   \n",
       "3  0x50b0696468f42cab1ddc76413a1312aff3cabdf6   \n",
       "4  0xb0699d63aef20df3f1cffa9ca2bb8670416271d2   \n",
       "\n",
       "                     token_contract_address_B   timestamp  weth_dollar_value  \\\n",
       "0  0x4fe8d4775b7cb2546b9ee86182081cdf8f77b053  1710163547        3104.300049   \n",
       "1  0xd807f7e2818db8eda0d28b5be74866338eaedb86  1710162383        3104.300049   \n",
       "2  0x710287d1d39dcf62094a83ebb3e736e79400068a  1710163715        3104.300049   \n",
       "3                                    Ethereum  1710162587        3104.300049   \n",
       "4                                    Ethereum  1710162059        3104.300049   \n",
       "\n",
       "   delta_eth  \n",
       "0  -0.065752  \n",
       "1  -0.014921  \n",
       "2  -0.024688  \n",
       "3  -0.012542  \n",
       "4  -0.020681  "
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stable_delta_with_dollar = pd.merge(stable_delta_with_dollar, weth_values_all, on = 'timestamp')\n",
    "#calculate the value of the tx loss in eth\n",
    "stable_delta_with_dollar['delta_eth'] = stable_delta_with_dollar['delta_dollar'] / stable_delta_with_dollar['weth_dollar_value']\n",
    "stable_delta_with_dollar.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Remove the transactions that are not interesting "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "problematic_hashes = problematic_transactions['tx_hash or block']\n",
    "stable_delta_with_dollar = stable_delta_with_dollar[~stable_delta_with_dollar['tx_hash'].isin(problematic_hashes)]\n",
    "stable_delta_with_dollar = stable_delta_with_dollar.drop_duplicates()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Cleanup by keeping only the interseting columns before exporting the file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "columns_to_keep = ['tx_hash', 'sender', 'delta_eth', 'delta_dollar', 'token_name_A', 'token_contract_address_A', 'token_A_delta_raw_amount_x', 'token_name_B', 'token_contract_address_B', 'token_B_delta_raw_amount_x']\n",
    "final = stable_delta_with_dollar[columns_to_keep]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [],
   "source": [
    "final = final.rename(columns = {'token_A_delta_raw_amount_x' : 'delta_token_A', 'token_B_delta_raw_amount_x' : 'delta_token_B'})\n",
    "final.to_csv(f'data/results/{name_of_incident}_final_results.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "FINAL STATS: \n",
      " - Out of the 37 original transactions, \n",
      "we were able to find deltas in ethereum and dollar by simulating if the transactions had been on top of block \n",
      "for 28 transactions in total. \n",
      " - This approach was therefore successful for 75.68% percent of transactions \n",
      " - The transaction list for which this approach did not work can be found in the folder data/results/incident2_transactions_or_blocks_with_api_problem \n",
      " - The total potential loss in dollars for this incident is -6451.374003850512\n"
     ]
    }
   ],
   "source": [
    "df_main = pd.read_csv(csv_file_path)\n",
    "tx_hash_list = [x for x in df_main['user_tx'].to_list() if pd.notnull(x)]\n",
    "print(\"FINAL STATS:\",\n",
    "      \"\\n - Out of the\", df_main['user_tx'].nunique(), \"original transactions,\",\n",
    "      \"\\nwe were able to find deltas in ethereum and dollar by simulating if the transactions had been on top of block\",\n",
    "      \"\\nfor\", final.tx_hash.nunique(), \"transactions in total.\", \n",
    "      \"\\n - This approach was therefore successful for\", '{:.2%}'.format(round(final.tx_hash.nunique()/df_main['user_tx'].nunique(), 4)), \"percent of transactions\",\n",
    "      f\"\\n - The transaction list for which this approach did not work can be found in the folder data/results/{name_of_incident}_transactions_or_blocks_with_api_problem\", \n",
    "      \"\\n - The total potential loss in dollars for this incident is\", final.delta_dollar.sum())"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "data_eng",
   "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.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}