VELOAI.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "96230aea",
   "metadata": {},
   "outputs": [],
   "source": [
    "# our goal here is to take a bunch of historical data on users blockchain activity\n",
    "# we then want to use this to predict if the user will stay in an LP position or remove their LP position"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "a0f5dfe9",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd \n",
    "\n",
    "optiData = pd.read_csv(\"optiTrans.csv\")\n",
    "avaxData = pd.read_csv(\"avaxTrans.csv\")\n",
    "arbiData = pd.read_csv(\"arbiTrans.csv\")\n",
    "polyData = pd.read_csv(\"polyTrans.csv\")\n",
    "\n",
    "\"\"\"\n",
    "Historical transaction data aggregrated from different chains via flipside \n",
    "\"\"\"\n",
    "\n",
    "df = pd.concat([optiData, avaxData, arbiData, polyData])\n",
    "df['FIRST_TRANSACTION'] =  df['FIRST_TRANSACTION'].astype('datetime64[ns]')\n",
    "df['LAST_TRANSACTION'] =  df['LAST_TRANSACTION'].astype('datetime64[ns]')\n",
    "\n",
    "\n",
    "veloDf = pd.read_csv(\"lpInfo.csv\")\n",
    "veloDf['Time'] =  veloDf['BLOCK_TIMESTAMP'].astype('datetime64[ns]')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "fff54420",
   "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>FIRST_TRANSACTION</th>\n",
       "      <th>LAST_TRANSACTION</th>\n",
       "      <th>TOTAL</th>\n",
       "      <th>N_TRANSACTIONS</th>\n",
       "      <th>FROM_ADDRESS</th>\n",
       "      <th>TO_ADDRESS</th>\n",
       "      <th>CONTRACT_ADDRESS</th>\n",
       "      <th>CHAIN</th>\n",
       "      <th>SYMBOL</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-09-02 07:23:58</td>\n",
       "      <td>2022-10-17 13:47:16</td>\n",
       "      <td>73421879781634400000</td>\n",
       "      <td>741</td>\n",
       "      <td>0x85149247691df622eaf1a8bd0cafd40bc45154a9</td>\n",
       "      <td>0xff40b156a428758e2d37d95bbc3d1e185a394a66</td>\n",
       "      <td>0x4200000000000000000000000000000000000006</td>\n",
       "      <td>OPTI</td>\n",
       "      <td>WETH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-09-07 09:56:51</td>\n",
       "      <td>2022-09-30 14:11:08</td>\n",
       "      <td>1900009424984</td>\n",
       "      <td>2</td>\n",
       "      <td>0x0172e05392aba65366c4dbbb70d958bbf43304e4</td>\n",
       "      <td>0x0000000000000000000000000000000000000000</td>\n",
       "      <td>0xfb00ac187a8eb5afae4eace434f493eb62672df7</td>\n",
       "      <td>OPTI</td>\n",
       "      <td>variableDebtOptUSDT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-09-05 08:05:45</td>\n",
       "      <td>2022-09-08 05:35:11</td>\n",
       "      <td>1003058528997</td>\n",
       "      <td>5</td>\n",
       "      <td>0x0000000000000000000000000000000000000000</td>\n",
       "      <td>0x31754e3c96cad2459f66b47f158c4399e8d4f79a</td>\n",
       "      <td>0x625e7708f30ca75bfd92586e17077590c60eb4cd</td>\n",
       "      <td>OPTI</td>\n",
       "      <td>aOptUSDC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-11-24 07:11:58</td>\n",
       "      <td>2021-12-25 23:09:34</td>\n",
       "      <td>9.07651239690097e+22</td>\n",
       "      <td>3</td>\n",
       "      <td>0xbbb33d2e7bd7ddc722e53da9ca8ee97df41cfabf</td>\n",
       "      <td>0xf181ed90d6cfac84b8073fdea6d34aa744b41810</td>\n",
       "      <td>0x56900d66d74cb14e3c86895789901c9135c95b16</td>\n",
       "      <td>OPTI</td>\n",
       "      <td>hDAI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-12-26 06:26:30</td>\n",
       "      <td>2021-12-26 06:26:30</td>\n",
       "      <td>1.50083615885431e+22</td>\n",
       "      <td>1</td>\n",
       "      <td>0x0000000000000000000000000000000000000000</td>\n",
       "      <td>0x48a5a6a01ba89cddf97d2d552923d5a11401ed19</td>\n",
       "      <td>0x06bad08305074da59fe98d0e85bad8f524e167df</td>\n",
       "      <td>OPTI</td>\n",
       "      <td>aeD-0x41503100000000000000000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    FIRST_TRANSACTION    LAST_TRANSACTION                 TOTAL  \\\n",
       "0 2022-09-02 07:23:58 2022-10-17 13:47:16  73421879781634400000   \n",
       "1 2022-09-07 09:56:51 2022-09-30 14:11:08         1900009424984   \n",
       "2 2022-09-05 08:05:45 2022-09-08 05:35:11         1003058528997   \n",
       "3 2021-11-24 07:11:58 2021-12-25 23:09:34  9.07651239690097e+22   \n",
       "4 2021-12-26 06:26:30 2021-12-26 06:26:30  1.50083615885431e+22   \n",
       "\n",
       "   N_TRANSACTIONS                                FROM_ADDRESS  \\\n",
       "0             741  0x85149247691df622eaf1a8bd0cafd40bc45154a9   \n",
       "1               2  0x0172e05392aba65366c4dbbb70d958bbf43304e4   \n",
       "2               5  0x0000000000000000000000000000000000000000   \n",
       "3               3  0xbbb33d2e7bd7ddc722e53da9ca8ee97df41cfabf   \n",
       "4               1  0x0000000000000000000000000000000000000000   \n",
       "\n",
       "                                   TO_ADDRESS  \\\n",
       "0  0xff40b156a428758e2d37d95bbc3d1e185a394a66   \n",
       "1  0x0000000000000000000000000000000000000000   \n",
       "2  0x31754e3c96cad2459f66b47f158c4399e8d4f79a   \n",
       "3  0xf181ed90d6cfac84b8073fdea6d34aa744b41810   \n",
       "4  0x48a5a6a01ba89cddf97d2d552923d5a11401ed19   \n",
       "\n",
       "                             CONTRACT_ADDRESS CHAIN  \\\n",
       "0  0x4200000000000000000000000000000000000006  OPTI   \n",
       "1  0xfb00ac187a8eb5afae4eace434f493eb62672df7  OPTI   \n",
       "2  0x625e7708f30ca75bfd92586e17077590c60eb4cd  OPTI   \n",
       "3  0x56900d66d74cb14e3c86895789901c9135c95b16  OPTI   \n",
       "4  0x06bad08305074da59fe98d0e85bad8f524e167df  OPTI   \n",
       "\n",
       "                             SYMBOL  \n",
       "0                              WETH  \n",
       "1               variableDebtOptUSDT  \n",
       "2                          aOptUSDC  \n",
       "3                              hDAI  \n",
       "4  aeD-0x41503100000000000000000000  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "9f7aa627",
   "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>BLOCK_NUMBER</th>\n",
       "      <th>BLOCK_TIMESTAMP</th>\n",
       "      <th>TX_HASH</th>\n",
       "      <th>EVENT_INDEX</th>\n",
       "      <th>ORIGIN_FUNCTION_SIGNATURE</th>\n",
       "      <th>ORIGIN_FROM_ADDRESS</th>\n",
       "      <th>ORIGIN_TO_ADDRESS</th>\n",
       "      <th>POOL_ADDRESS</th>\n",
       "      <th>POOL_NAME</th>\n",
       "      <th>POOL_TYPE</th>\n",
       "      <th>...</th>\n",
       "      <th>TOKEN0_AMOUNT</th>\n",
       "      <th>TOKEN1_AMOUNT</th>\n",
       "      <th>TOKEN0_AMOUNT_USD</th>\n",
       "      <th>TOKEN1_AMOUNT_USD</th>\n",
       "      <th>TOKEN0_ADDRESS</th>\n",
       "      <th>TOKEN1_ADDRESS</th>\n",
       "      <th>LP_TOKEN_ACTION</th>\n",
       "      <th>LP_TOKEN_AMOUNT</th>\n",
       "      <th>LP_TOKEN_AMOUNT_USD</th>\n",
       "      <th>Time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>72291734</td>\n",
       "      <td>2023-02-04 18:06:29.000</td>\n",
       "      <td>0x4d1ae6db8d6270c7de84a626784dd6bc91635728a947...</td>\n",
       "      <td>6</td>\n",
       "      <td>0x7ae7cfb5</td>\n",
       "      <td>0xc8d71390f466aecef721dcab073b6190f0ec9639</td>\n",
       "      <td>0x1ec50880101022c11530a069690f5446d1464592</td>\n",
       "      <td>0xd16232ad60188b68076a235c65d692090caba155</td>\n",
       "      <td>StableV1 AMM - USDC/sUSD</td>\n",
       "      <td>stable</td>\n",
       "      <td>...</td>\n",
       "      <td>5312.423521</td>\n",
       "      <td>4680.172772</td>\n",
       "      <td>5312.42</td>\n",
       "      <td>4680.17</td>\n",
       "      <td>0x7f5c764cbc14f9669b88837ca1490cca17c31607</td>\n",
       "      <td>0x8c6f28f2f1a3c87f0f938b96d27520d9751ec8d9</td>\n",
       "      <td>mint</td>\n",
       "      <td>0.004996</td>\n",
       "      <td>9992.59</td>\n",
       "      <td>2023-02-04 18:06:29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>72305318</td>\n",
       "      <td>2023-02-04 20:45:12.000</td>\n",
       "      <td>0x1d1a9a56fbf48d912d52a25935f6c5f484234ac4559a...</td>\n",
       "      <td>4</td>\n",
       "      <td>0x5a47ddc3</td>\n",
       "      <td>0xbbb33d2e7bd7ddc722e53da9ca8ee97df41cfabf</td>\n",
       "      <td>0x9c12939390052919af3155f41bf4160fd3666a6f</td>\n",
       "      <td>0xb343dae0e7fe28c16ec5dca64cb0c1ac5f4690ac</td>\n",
       "      <td>StableV1 AMM - wstETH/sETH</td>\n",
       "      <td>stable</td>\n",
       "      <td>...</td>\n",
       "      <td>35.132389</td>\n",
       "      <td>75.894382</td>\n",
       "      <td>65320.95</td>\n",
       "      <td>127829.67</td>\n",
       "      <td>0x1f32b1c2345538c0c6f582fcb022739c4a194ebb</td>\n",
       "      <td>0xe405de8f52ba7559f9df3c368500b6e6ae6cee49</td>\n",
       "      <td>mint</td>\n",
       "      <td>55.097142</td>\n",
       "      <td>193150.62</td>\n",
       "      <td>2023-02-04 20:45:12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>72307312</td>\n",
       "      <td>2023-02-04 21:17:09.000</td>\n",
       "      <td>0x3b99010d3802e824defcb3e16e66ac25a4f73bcac3f9...</td>\n",
       "      <td>4</td>\n",
       "      <td>0x5a47ddc3</td>\n",
       "      <td>0xb755885151df63f2d7d3431c66f48c69b83850cb</td>\n",
       "      <td>0x9c12939390052919af3155f41bf4160fd3666a6f</td>\n",
       "      <td>0xb343dae0e7fe28c16ec5dca64cb0c1ac5f4690ac</td>\n",
       "      <td>StableV1 AMM - wstETH/sETH</td>\n",
       "      <td>stable</td>\n",
       "      <td>...</td>\n",
       "      <td>27.960187</td>\n",
       "      <td>60.400000</td>\n",
       "      <td>51886.56</td>\n",
       "      <td>101634.48</td>\n",
       "      <td>0x1f32b1c2345538c0c6f582fcb022739c4a194ebb</td>\n",
       "      <td>0xe405de8f52ba7559f9df3c368500b6e6ae6cee49</td>\n",
       "      <td>mint</td>\n",
       "      <td>43.848839</td>\n",
       "      <td>153521.04</td>\n",
       "      <td>2023-02-04 21:17:09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>72304851</td>\n",
       "      <td>2023-02-04 20:37:56.000</td>\n",
       "      <td>0xf6174298c9a6afe2e1d58f9392ad5bb2a80d95eca603...</td>\n",
       "      <td>44</td>\n",
       "      <td>0xde5f6268</td>\n",
       "      <td>0x82bd1487843704d2fd846788b69e9a7e06060f8f</td>\n",
       "      <td>0x0f21607f78ee4bdd6895a7517851531b03c2c58a</td>\n",
       "      <td>0xffb4fd14e840abd3ebd6cd026332b2302948a3fc</td>\n",
       "      <td>StableV1 AMM - DAI/MAI</td>\n",
       "      <td>stable</td>\n",
       "      <td>...</td>\n",
       "      <td>2.420696</td>\n",
       "      <td>3.374272</td>\n",
       "      <td>2.42</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0xda10009cbd5d07dd0cecc66161fc93d7c9000da1</td>\n",
       "      <td>0xdfa46478f9e5ea86d57387849598dbfb2e964b02</td>\n",
       "      <td>mint</td>\n",
       "      <td>2.894313</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-02-04 20:37:56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>72284884</td>\n",
       "      <td>2023-02-04 17:04:22.000</td>\n",
       "      <td>0x252c0b17449ed8dfa85d28c3e0b5e599085fb1ffa59b...</td>\n",
       "      <td>5</td>\n",
       "      <td>0x5a47ddc3</td>\n",
       "      <td>0x277d250383807fdb4840e7901a262f5c4c4be64b</td>\n",
       "      <td>0x9c12939390052919af3155f41bf4160fd3666a6f</td>\n",
       "      <td>0x794299137282e5d3af56616624ab918d1b2becf3</td>\n",
       "      <td>VolatileV1 AMM - wstETH/USDC</td>\n",
       "      <td>volatile</td>\n",
       "      <td>...</td>\n",
       "      <td>123.992948</td>\n",
       "      <td>229982.327549</td>\n",
       "      <td>229979.64</td>\n",
       "      <td>229982.33</td>\n",
       "      <td>0x1f32b1c2345538c0c6f582fcb022739c4a194ebb</td>\n",
       "      <td>0x7f5c764cbc14f9669b88837ca1490cca17c31607</td>\n",
       "      <td>mint</td>\n",
       "      <td>0.001020</td>\n",
       "      <td>459961.97</td>\n",
       "      <td>2023-02-04 17:04:22</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   BLOCK_NUMBER          BLOCK_TIMESTAMP  \\\n",
       "0      72291734  2023-02-04 18:06:29.000   \n",
       "1      72305318  2023-02-04 20:45:12.000   \n",
       "2      72307312  2023-02-04 21:17:09.000   \n",
       "3      72304851  2023-02-04 20:37:56.000   \n",
       "4      72284884  2023-02-04 17:04:22.000   \n",
       "\n",
       "                                             TX_HASH  EVENT_INDEX  \\\n",
       "0  0x4d1ae6db8d6270c7de84a626784dd6bc91635728a947...            6   \n",
       "1  0x1d1a9a56fbf48d912d52a25935f6c5f484234ac4559a...            4   \n",
       "2  0x3b99010d3802e824defcb3e16e66ac25a4f73bcac3f9...            4   \n",
       "3  0xf6174298c9a6afe2e1d58f9392ad5bb2a80d95eca603...           44   \n",
       "4  0x252c0b17449ed8dfa85d28c3e0b5e599085fb1ffa59b...            5   \n",
       "\n",
       "  ORIGIN_FUNCTION_SIGNATURE                         ORIGIN_FROM_ADDRESS  \\\n",
       "0                0x7ae7cfb5  0xc8d71390f466aecef721dcab073b6190f0ec9639   \n",
       "1                0x5a47ddc3  0xbbb33d2e7bd7ddc722e53da9ca8ee97df41cfabf   \n",
       "2                0x5a47ddc3  0xb755885151df63f2d7d3431c66f48c69b83850cb   \n",
       "3                0xde5f6268  0x82bd1487843704d2fd846788b69e9a7e06060f8f   \n",
       "4                0x5a47ddc3  0x277d250383807fdb4840e7901a262f5c4c4be64b   \n",
       "\n",
       "                            ORIGIN_TO_ADDRESS  \\\n",
       "0  0x1ec50880101022c11530a069690f5446d1464592   \n",
       "1  0x9c12939390052919af3155f41bf4160fd3666a6f   \n",
       "2  0x9c12939390052919af3155f41bf4160fd3666a6f   \n",
       "3  0x0f21607f78ee4bdd6895a7517851531b03c2c58a   \n",
       "4  0x9c12939390052919af3155f41bf4160fd3666a6f   \n",
       "\n",
       "                                 POOL_ADDRESS                     POOL_NAME  \\\n",
       "0  0xd16232ad60188b68076a235c65d692090caba155      StableV1 AMM - USDC/sUSD   \n",
       "1  0xb343dae0e7fe28c16ec5dca64cb0c1ac5f4690ac    StableV1 AMM - wstETH/sETH   \n",
       "2  0xb343dae0e7fe28c16ec5dca64cb0c1ac5f4690ac    StableV1 AMM - wstETH/sETH   \n",
       "3  0xffb4fd14e840abd3ebd6cd026332b2302948a3fc        StableV1 AMM - DAI/MAI   \n",
       "4  0x794299137282e5d3af56616624ab918d1b2becf3  VolatileV1 AMM - wstETH/USDC   \n",
       "\n",
       "  POOL_TYPE  ... TOKEN0_AMOUNT  TOKEN1_AMOUNT TOKEN0_AMOUNT_USD  \\\n",
       "0    stable  ...   5312.423521    4680.172772           5312.42   \n",
       "1    stable  ...     35.132389      75.894382          65320.95   \n",
       "2    stable  ...     27.960187      60.400000          51886.56   \n",
       "3    stable  ...      2.420696       3.374272              2.42   \n",
       "4  volatile  ...    123.992948  229982.327549         229979.64   \n",
       "\n",
       "  TOKEN1_AMOUNT_USD                              TOKEN0_ADDRESS  \\\n",
       "0           4680.17  0x7f5c764cbc14f9669b88837ca1490cca17c31607   \n",
       "1         127829.67  0x1f32b1c2345538c0c6f582fcb022739c4a194ebb   \n",
       "2         101634.48  0x1f32b1c2345538c0c6f582fcb022739c4a194ebb   \n",
       "3               NaN  0xda10009cbd5d07dd0cecc66161fc93d7c9000da1   \n",
       "4         229982.33  0x1f32b1c2345538c0c6f582fcb022739c4a194ebb   \n",
       "\n",
       "                               TOKEN1_ADDRESS  LP_TOKEN_ACTION  \\\n",
       "0  0x8c6f28f2f1a3c87f0f938b96d27520d9751ec8d9             mint   \n",
       "1  0xe405de8f52ba7559f9df3c368500b6e6ae6cee49             mint   \n",
       "2  0xe405de8f52ba7559f9df3c368500b6e6ae6cee49             mint   \n",
       "3  0xdfa46478f9e5ea86d57387849598dbfb2e964b02             mint   \n",
       "4  0x7f5c764cbc14f9669b88837ca1490cca17c31607             mint   \n",
       "\n",
       "   LP_TOKEN_AMOUNT LP_TOKEN_AMOUNT_USD                Time  \n",
       "0         0.004996             9992.59 2023-02-04 18:06:29  \n",
       "1        55.097142           193150.62 2023-02-04 20:45:12  \n",
       "2        43.848839           153521.04 2023-02-04 21:17:09  \n",
       "3         2.894313                 NaN 2023-02-04 20:37:56  \n",
       "4         0.001020           459961.97 2023-02-04 17:04:22  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "veloDf.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "b7ced985",
   "metadata": {},
   "outputs": [],
   "source": [
    "symbols = df.SYMBOL.value_counts()\n",
    "topAssets = symbols.index[0:15]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "d152baf4",
   "metadata": {},
   "outputs": [],
   "source": [
    "chains = df.CHAIN.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "e818bc1f",
   "metadata": {},
   "outputs": [],
   "source": [
    "date = pd.to_datetime(\"2023-1-1\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "ff456eb9",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_46420/1690657328.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  veloDfPred['Users_Pool'] = veloDfPred.ORIGIN_FROM_ADDRESS + \"SPLIT\" + veloDfPred.POOL_NAME\n"
     ]
    }
   ],
   "source": [
    "veloDfPred = veloDf[veloDf.Time < date]\n",
    "veloDfPred['Users_Pool'] = veloDfPred.ORIGIN_FROM_ADDRESS + \"SPLIT\" + veloDfPred.POOL_NAME\n",
    "\n",
    "users = veloDfPred.Users_Pool.unique()\n",
    "\n",
    "userDf = pd.DataFrame({'Users_Pool' : list(users)})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "caa4e15f",
   "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>BLOCK_NUMBER</th>\n",
       "      <th>BLOCK_TIMESTAMP</th>\n",
       "      <th>TX_HASH</th>\n",
       "      <th>EVENT_INDEX</th>\n",
       "      <th>ORIGIN_FUNCTION_SIGNATURE</th>\n",
       "      <th>ORIGIN_FROM_ADDRESS</th>\n",
       "      <th>ORIGIN_TO_ADDRESS</th>\n",
       "      <th>POOL_ADDRESS</th>\n",
       "      <th>POOL_NAME</th>\n",
       "      <th>POOL_TYPE</th>\n",
       "      <th>...</th>\n",
       "      <th>TOKEN1_AMOUNT</th>\n",
       "      <th>TOKEN0_AMOUNT_USD</th>\n",
       "      <th>TOKEN1_AMOUNT_USD</th>\n",
       "      <th>TOKEN0_ADDRESS</th>\n",
       "      <th>TOKEN1_ADDRESS</th>\n",
       "      <th>LP_TOKEN_ACTION</th>\n",
       "      <th>LP_TOKEN_AMOUNT</th>\n",
       "      <th>LP_TOKEN_AMOUNT_USD</th>\n",
       "      <th>Time</th>\n",
       "      <th>Users_Pool</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>29738931</td>\n",
       "      <td>2022-10-16 17:05:35.000</td>\n",
       "      <td>0x1e7bf43d6dcc302bf01e36fac0d1ff9e9eac38be0a33...</td>\n",
       "      <td>52</td>\n",
       "      <td>0xf5d07b60</td>\n",
       "      <td>0x90ceeaffb3da888d32b02cf359f9e6a44ac8fa29</td>\n",
       "      <td>0x9b50b06b81f033ca86d70f0a44f30bd7e0155737</td>\n",
       "      <td>0x0d693efd716021878d5979fab4cf8f6c1b7ce450</td>\n",
       "      <td>StableV1 AMM - sUSD/LUSD</td>\n",
       "      <td>stable</td>\n",
       "      <td>...</td>\n",
       "      <td>4.056210</td>\n",
       "      <td>6.56</td>\n",
       "      <td>4.14</td>\n",
       "      <td>0x8c6f28f2f1a3c87f0f938b96d27520d9751ec8d9</td>\n",
       "      <td>0xc40f949f8a4e094d1b49a23ea9241d289b7b2819</td>\n",
       "      <td>mint</td>\n",
       "      <td>48173.517087</td>\n",
       "      <td>10.70</td>\n",
       "      <td>2022-10-16 17:05:35</td>\n",
       "      <td>0x90ceeaffb3da888d32b02cf359f9e6a44ac8fa29SPLI...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>29768354</td>\n",
       "      <td>2022-10-16 21:49:07.000</td>\n",
       "      <td>0x35498a854a22affca132e110f342bcf55ca646bcc595...</td>\n",
       "      <td>5</td>\n",
       "      <td>0x5a47ddc3</td>\n",
       "      <td>0x5c70b3f22595caf8314f77dabc628363aaee18ad</td>\n",
       "      <td>0x9c12939390052919af3155f41bf4160fd3666a6f</td>\n",
       "      <td>0xe8537b6ff1039cb9ed0b71713f697ddbadbb717d</td>\n",
       "      <td>VolatileV1 AMM - VELO/USDC</td>\n",
       "      <td>volatile</td>\n",
       "      <td>...</td>\n",
       "      <td>399.998475</td>\n",
       "      <td>399.24</td>\n",
       "      <td>399.78</td>\n",
       "      <td>0x3c8b650257cfb5f272f799f5e2b4e65093a11a05</td>\n",
       "      <td>0x7f5c764cbc14f9669b88837ca1490cca17c31607</td>\n",
       "      <td>mint</td>\n",
       "      <td>0.002500</td>\n",
       "      <td>799.02</td>\n",
       "      <td>2022-10-16 21:49:07</td>\n",
       "      <td>0x5c70b3f22595caf8314f77dabc628363aaee18adSPLI...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>29752771</td>\n",
       "      <td>2022-10-16 19:17:26.000</td>\n",
       "      <td>0x6ee2d6dbc47e6a3b0ca3f459b7d99f6daa229194e2bd...</td>\n",
       "      <td>5</td>\n",
       "      <td>0x0dede6c4</td>\n",
       "      <td>0x8f360352100ddf1134b3bdcaa4792637bb5a4248</td>\n",
       "      <td>0x9c12939390052919af3155f41bf4160fd3666a6f</td>\n",
       "      <td>0xa4549b89a39f76d9d28415474aed7d06ec9935fe</td>\n",
       "      <td>StableV1 AMM - USDC/TUSD</td>\n",
       "      <td>stable</td>\n",
       "      <td>...</td>\n",
       "      <td>24914.482317</td>\n",
       "      <td>25080.88</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0x7f5c764cbc14f9669b88837ca1490cca17c31607</td>\n",
       "      <td>0xcb59a0a753fdb7491d5f3d794316f1ade197b21e</td>\n",
       "      <td>burn</td>\n",
       "      <td>0.025000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-10-16 19:17:26</td>\n",
       "      <td>0x8f360352100ddf1134b3bdcaa4792637bb5a4248SPLI...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>29738931</td>\n",
       "      <td>2022-10-16 17:05:35.000</td>\n",
       "      <td>0x1e7bf43d6dcc302bf01e36fac0d1ff9e9eac38be0a33...</td>\n",
       "      <td>12</td>\n",
       "      <td>0xf5d07b60</td>\n",
       "      <td>0x90ceeaffb3da888d32b02cf359f9e6a44ac8fa29</td>\n",
       "      <td>0x9b50b06b81f033ca86d70f0a44f30bd7e0155737</td>\n",
       "      <td>0x0d693efd716021878d5979fab4cf8f6c1b7ce450</td>\n",
       "      <td>StableV1 AMM - sUSD/LUSD</td>\n",
       "      <td>stable</td>\n",
       "      <td>...</td>\n",
       "      <td>37050.505771</td>\n",
       "      <td>59959.43</td>\n",
       "      <td>37791.52</td>\n",
       "      <td>0x8c6f28f2f1a3c87f0f938b96d27520d9751ec8d9</td>\n",
       "      <td>0xc40f949f8a4e094d1b49a23ea9241d289b7b2819</td>\n",
       "      <td>mint</td>\n",
       "      <td>48173.517087</td>\n",
       "      <td>97750.95</td>\n",
       "      <td>2022-10-16 17:05:35</td>\n",
       "      <td>0x90ceeaffb3da888d32b02cf359f9e6a44ac8fa29SPLI...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>29739124</td>\n",
       "      <td>2022-10-16 17:07:06.000</td>\n",
       "      <td>0xeabd81fa40bfa1e6a86905f70cbf58c15aaa7071c5b1...</td>\n",
       "      <td>14</td>\n",
       "      <td>0xf5d07b60</td>\n",
       "      <td>0x90ceeaffb3da888d32b02cf359f9e6a44ac8fa29</td>\n",
       "      <td>0x9b50b06b81f033ca86d70f0a44f30bd7e0155737</td>\n",
       "      <td>0x0d693efd716021878d5979fab4cf8f6c1b7ce450</td>\n",
       "      <td>StableV1 AMM - sUSD/LUSD</td>\n",
       "      <td>stable</td>\n",
       "      <td>...</td>\n",
       "      <td>1068.743400</td>\n",
       "      <td>1665.85</td>\n",
       "      <td>1090.12</td>\n",
       "      <td>0x8c6f28f2f1a3c87f0f938b96d27520d9751ec8d9</td>\n",
       "      <td>0xc40f949f8a4e094d1b49a23ea9241d289b7b2819</td>\n",
       "      <td>mint</td>\n",
       "      <td>0.155100</td>\n",
       "      <td>2755.97</td>\n",
       "      <td>2022-10-16 17:07:06</td>\n",
       "      <td>0x90ceeaffb3da888d32b02cf359f9e6a44ac8fa29SPLI...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    BLOCK_NUMBER          BLOCK_TIMESTAMP  \\\n",
       "85      29738931  2022-10-16 17:05:35.000   \n",
       "86      29768354  2022-10-16 21:49:07.000   \n",
       "87      29752771  2022-10-16 19:17:26.000   \n",
       "88      29738931  2022-10-16 17:05:35.000   \n",
       "89      29739124  2022-10-16 17:07:06.000   \n",
       "\n",
       "                                              TX_HASH  EVENT_INDEX  \\\n",
       "85  0x1e7bf43d6dcc302bf01e36fac0d1ff9e9eac38be0a33...           52   \n",
       "86  0x35498a854a22affca132e110f342bcf55ca646bcc595...            5   \n",
       "87  0x6ee2d6dbc47e6a3b0ca3f459b7d99f6daa229194e2bd...            5   \n",
       "88  0x1e7bf43d6dcc302bf01e36fac0d1ff9e9eac38be0a33...           12   \n",
       "89  0xeabd81fa40bfa1e6a86905f70cbf58c15aaa7071c5b1...           14   \n",
       "\n",
       "   ORIGIN_FUNCTION_SIGNATURE                         ORIGIN_FROM_ADDRESS  \\\n",
       "85                0xf5d07b60  0x90ceeaffb3da888d32b02cf359f9e6a44ac8fa29   \n",
       "86                0x5a47ddc3  0x5c70b3f22595caf8314f77dabc628363aaee18ad   \n",
       "87                0x0dede6c4  0x8f360352100ddf1134b3bdcaa4792637bb5a4248   \n",
       "88                0xf5d07b60  0x90ceeaffb3da888d32b02cf359f9e6a44ac8fa29   \n",
       "89                0xf5d07b60  0x90ceeaffb3da888d32b02cf359f9e6a44ac8fa29   \n",
       "\n",
       "                             ORIGIN_TO_ADDRESS  \\\n",
       "85  0x9b50b06b81f033ca86d70f0a44f30bd7e0155737   \n",
       "86  0x9c12939390052919af3155f41bf4160fd3666a6f   \n",
       "87  0x9c12939390052919af3155f41bf4160fd3666a6f   \n",
       "88  0x9b50b06b81f033ca86d70f0a44f30bd7e0155737   \n",
       "89  0x9b50b06b81f033ca86d70f0a44f30bd7e0155737   \n",
       "\n",
       "                                  POOL_ADDRESS                   POOL_NAME  \\\n",
       "85  0x0d693efd716021878d5979fab4cf8f6c1b7ce450    StableV1 AMM - sUSD/LUSD   \n",
       "86  0xe8537b6ff1039cb9ed0b71713f697ddbadbb717d  VolatileV1 AMM - VELO/USDC   \n",
       "87  0xa4549b89a39f76d9d28415474aed7d06ec9935fe    StableV1 AMM - USDC/TUSD   \n",
       "88  0x0d693efd716021878d5979fab4cf8f6c1b7ce450    StableV1 AMM - sUSD/LUSD   \n",
       "89  0x0d693efd716021878d5979fab4cf8f6c1b7ce450    StableV1 AMM - sUSD/LUSD   \n",
       "\n",
       "   POOL_TYPE  ... TOKEN1_AMOUNT TOKEN0_AMOUNT_USD TOKEN1_AMOUNT_USD  \\\n",
       "85    stable  ...      4.056210              6.56              4.14   \n",
       "86  volatile  ...    399.998475            399.24            399.78   \n",
       "87    stable  ...  24914.482317          25080.88               NaN   \n",
       "88    stable  ...  37050.505771          59959.43          37791.52   \n",
       "89    stable  ...   1068.743400           1665.85           1090.12   \n",
       "\n",
       "                                TOKEN0_ADDRESS  \\\n",
       "85  0x8c6f28f2f1a3c87f0f938b96d27520d9751ec8d9   \n",
       "86  0x3c8b650257cfb5f272f799f5e2b4e65093a11a05   \n",
       "87  0x7f5c764cbc14f9669b88837ca1490cca17c31607   \n",
       "88  0x8c6f28f2f1a3c87f0f938b96d27520d9751ec8d9   \n",
       "89  0x8c6f28f2f1a3c87f0f938b96d27520d9751ec8d9   \n",
       "\n",
       "                                TOKEN1_ADDRESS  LP_TOKEN_ACTION  \\\n",
       "85  0xc40f949f8a4e094d1b49a23ea9241d289b7b2819             mint   \n",
       "86  0x7f5c764cbc14f9669b88837ca1490cca17c31607             mint   \n",
       "87  0xcb59a0a753fdb7491d5f3d794316f1ade197b21e             burn   \n",
       "88  0xc40f949f8a4e094d1b49a23ea9241d289b7b2819             mint   \n",
       "89  0xc40f949f8a4e094d1b49a23ea9241d289b7b2819             mint   \n",
       "\n",
       "    LP_TOKEN_AMOUNT  LP_TOKEN_AMOUNT_USD                Time  \\\n",
       "85     48173.517087                10.70 2022-10-16 17:05:35   \n",
       "86         0.002500               799.02 2022-10-16 21:49:07   \n",
       "87         0.025000                  NaN 2022-10-16 19:17:26   \n",
       "88     48173.517087             97750.95 2022-10-16 17:05:35   \n",
       "89         0.155100              2755.97 2022-10-16 17:07:06   \n",
       "\n",
       "                                           Users_Pool  \n",
       "85  0x90ceeaffb3da888d32b02cf359f9e6a44ac8fa29SPLI...  \n",
       "86  0x5c70b3f22595caf8314f77dabc628363aaee18adSPLI...  \n",
       "87  0x8f360352100ddf1134b3bdcaa4792637bb5a4248SPLI...  \n",
       "88  0x90ceeaffb3da888d32b02cf359f9e6a44ac8fa29SPLI...  \n",
       "89  0x90ceeaffb3da888d32b02cf359f9e6a44ac8fa29SPLI...  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "veloDfPred.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "6f49c365",
   "metadata": {},
   "outputs": [],
   "source": [
    "dfMints = veloDfPred[veloDfPred.LP_TOKEN_ACTION == 'mint']\n",
    "dfBurns = veloDfPred[veloDfPred.LP_TOKEN_ACTION == 'burn']\n",
    "\n",
    "def getTotalMints(row) : \n",
    "    return dfMints[dfMints.Users_Pool == row.Users_Pool]['LP_TOKEN_AMOUNT'].sum()\n",
    "    \n",
    "def getTotalBurns(row) : \n",
    "    return dfBurns[dfBurns.Users_Pool == row.Users_Pool]['LP_TOKEN_AMOUNT'].sum()\n",
    "\n",
    "\n",
    "userDf['totalMints'] = userDf.apply(getTotalMints, axis = 1)\n",
    "userDf['totalBurns'] = userDf.apply(getTotalBurns, axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "609d8280",
   "metadata": {},
   "outputs": [],
   "source": [
    "userDf['NetMints'] = userDf['totalMints'] - userDf['totalBurns']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "a8bf99d9",
   "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>Users_Pool</th>\n",
       "      <th>totalMints</th>\n",
       "      <th>totalBurns</th>\n",
       "      <th>NetMints</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0x90ceeaffb3da888d32b02cf359f9e6a44ac8fa29SPLI...</td>\n",
       "      <td>96349.183968</td>\n",
       "      <td>49908.567778</td>\n",
       "      <td>4.644062e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0x5d368c382ae92fba52233b95c633c96fe49d0dc5SPLI...</td>\n",
       "      <td>0.085417</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>8.541745e-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0x5c70b3f22595caf8314f77dabc628363aaee18adSPLI...</td>\n",
       "      <td>112111.953212</td>\n",
       "      <td>112111.953212</td>\n",
       "      <td>1.455192e-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0xed78afd8acba4b56703c4c0ac6cdfa6b7433c1aaSPLI...</td>\n",
       "      <td>612.174074</td>\n",
       "      <td>591.017734</td>\n",
       "      <td>2.115634e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0xc4e70f1c8a7d74afbb5da56d4b3e307c125fefd7SPLI...</td>\n",
       "      <td>0.057269</td>\n",
       "      <td>0.018145</td>\n",
       "      <td>3.912416e-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>968</th>\n",
       "      <td>0x25431341a5800759268a6ac1d3cd91c029d7d9caSPLI...</td>\n",
       "      <td>0.299241</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.992408e-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>969</th>\n",
       "      <td>0xd6b1fbcbe39e33a3d5d9014b024f511be3564ee5SPLI...</td>\n",
       "      <td>24220.148513</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.422015e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>972</th>\n",
       "      <td>0xbf891e7efcc98a8239385d3172ba10ad593c7886SPLI...</td>\n",
       "      <td>38.478352</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.847835e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>974</th>\n",
       "      <td>0xb14d56ce29dade0316ea5df06d491636a7c4f3c8SPLI...</td>\n",
       "      <td>0.050238</td>\n",
       "      <td>0.050238</td>\n",
       "      <td>4.000002e-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>975</th>\n",
       "      <td>0x1b72bac3772050fdcaf468cce7e20deb3cb02d89SPLI...</td>\n",
       "      <td>1.729749</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.729749e+00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>356 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            Users_Pool     totalMints  \\\n",
       "0    0x90ceeaffb3da888d32b02cf359f9e6a44ac8fa29SPLI...   96349.183968   \n",
       "3    0x5d368c382ae92fba52233b95c633c96fe49d0dc5SPLI...       0.085417   \n",
       "5    0x5c70b3f22595caf8314f77dabc628363aaee18adSPLI...  112111.953212   \n",
       "6    0xed78afd8acba4b56703c4c0ac6cdfa6b7433c1aaSPLI...     612.174074   \n",
       "7    0xc4e70f1c8a7d74afbb5da56d4b3e307c125fefd7SPLI...       0.057269   \n",
       "..                                                 ...            ...   \n",
       "968  0x25431341a5800759268a6ac1d3cd91c029d7d9caSPLI...       0.299241   \n",
       "969  0xd6b1fbcbe39e33a3d5d9014b024f511be3564ee5SPLI...   24220.148513   \n",
       "972  0xbf891e7efcc98a8239385d3172ba10ad593c7886SPLI...      38.478352   \n",
       "974  0xb14d56ce29dade0316ea5df06d491636a7c4f3c8SPLI...       0.050238   \n",
       "975  0x1b72bac3772050fdcaf468cce7e20deb3cb02d89SPLI...       1.729749   \n",
       "\n",
       "        totalBurns      NetMints  \n",
       "0     49908.567778  4.644062e+04  \n",
       "3         0.000000  8.541745e-02  \n",
       "5    112111.953212  1.455192e-11  \n",
       "6       591.017734  2.115634e+01  \n",
       "7         0.018145  3.912416e-02  \n",
       "..             ...           ...  \n",
       "968       0.000000  2.992408e-01  \n",
       "969       0.000000  2.422015e+04  \n",
       "972       0.000000  3.847835e+01  \n",
       "974       0.050238  4.000002e-12  \n",
       "975       0.000000  1.729749e+00  \n",
       "\n",
       "[356 rows x 4 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "userDf[userDf['NetMints'] > 0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "67ecba27",
   "metadata": {},
   "outputs": [],
   "source": [
    "#df[['First Name', 'Last Name']] = df[\"Student_details\"].apply(lambda x: pd.Series(str(x).split(\"_\")))\n",
    "userDf[['user', 'pool']] = userDf.Users_Pool.apply(lambda x: pd.Series(str(x).split(\"SPLIT\")))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "122b2fc4",
   "metadata": {},
   "outputs": [],
   "source": [
    "def totalChainTransfersTo(row):\n",
    "    return dfChain[dfChain.TO_ADDRESS == row['user']]['N_TRANSACTIONS'].sum()\n",
    "def totalChainTransfersFrom(row):\n",
    "    return dfChain[dfChain.FROM_ADDRESS == row['user']]['N_TRANSACTIONS'].sum()\n",
    "\n",
    "def firstTransfer(row):\n",
    "    return dfChain[dfChain.FROM_ADDRESS == row['user']]['FIRST_TRANSACTION'].min()\n",
    "def lastTransfer(row):\n",
    "    return dfChain[dfChain.FROM_ADDRESS == row['user']]['LAST_TRANSACTION'].max()    \n",
    "\n",
    "def totalAssetTransfersTo(row):\n",
    "    return dfAsset[dfAsset.TO_ADDRESS == row['user']]['TOTAL'].sum()\n",
    "def totalAssetTransfersFrom(row):\n",
    "    return dfAsset[dfAsset.FROM_ADDRESS == row['user']]['TOTAL'].sum()\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "e83e138c",
   "metadata": {},
   "outputs": [],
   "source": [
    "for chain in chains : \n",
    "    dfChain = df[df.CHAIN == chain]\n",
    "    #asset = 'USDC'\n",
    "    userDf['totalChainTransfersTo' + chain] = userDf.apply(totalChainTransfersTo, axis = 1)\n",
    "    userDf['totalChainTransfersFrom' + chain] = userDf.apply(totalChainTransfersFrom, axis = 1)\n",
    "    userDf['firstTransfer' + chain] = userDf.apply(firstTransfer, axis = 1)\n",
    "    userDf['lastTransfer' + chain] = userDf.apply(lastTransfer, axis = 1)\n",
    "    userDf['firstTransfer' + chain] = (userDf['firstTransfer' + chain] - date).dt.days\n",
    "    userDf['lastTransfer' + chain] = (userDf['lastTransfer' + chain] - date).dt.days\n",
    "\n",
    "    for asset in topAssets :\n",
    "        dfAsset = dfChain[dfChain.SYMBOL == asset]\n",
    "        userDf['totalTransfersTo' + chain + asset] = userDf.apply(totalAssetTransfersTo, axis = 1)\n",
    "        userDf['totalChainTransfersTo' + chain + asset] = userDf.apply(totalAssetTransfersFrom, axis = 1)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "14cf3640",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Users_Pool', 'totalMints', 'totalBurns', 'NetMints', 'user', 'pool',\n",
       "       'totalChainTransfersToOPTI', 'totalChainTransfersFromOPTI',\n",
       "       'firstTransferOPTI', 'lastTransferOPTI',\n",
       "       ...\n",
       "       'totalChainTransfersToPOLYWMATIC', 'totalTransfersToPOLYWBTC',\n",
       "       'totalChainTransfersToPOLYWBTC', 'totalTransfersToPOLYUNI-V2',\n",
       "       'totalChainTransfersToPOLYUNI-V2', 'totalTransfersToPOLYMIM',\n",
       "       'totalChainTransfersToPOLYMIM', 'totalTransfersToPOLYDAI.e',\n",
       "       'totalChainTransfersToPOLYDAI.e', 'Withdraw'],\n",
       "      dtype='object', length=143)"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "userDf.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "be852f3e",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_46420/3477743160.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  veloDfY['Users_Pool'] = veloDfY.ORIGIN_FROM_ADDRESS + \"SPLIT\" + veloDfY.POOL_NAME\n"
     ]
    }
   ],
   "source": [
    "veloDfY = veloDf[veloDf.Time >= date]\n",
    "veloDfY['Users_Pool'] = veloDfY.ORIGIN_FROM_ADDRESS + \"SPLIT\" + veloDfY.POOL_NAME"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "836efc68",
   "metadata": {},
   "outputs": [],
   "source": [
    "veloDfBurned = veloDfY[veloDfY.LP_TOKEN_ACTION == 'burn']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "4d37517e",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "userDf['Withdraw'] = userDf.Users_Pool.isin(veloDfBurned.Users_Pool)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "cc3d51d8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Users_Pool', 'totalMints', 'totalBurns', 'NetMints', 'user', 'pool',\n",
       "       'totalChainTransfersToOPTI', 'totalChainTransfersFromOPTI',\n",
       "       'firstTransferOPTI', 'lastTransferOPTI',\n",
       "       ...\n",
       "       'totalChainTransfersToPOLYWMATIC', 'totalTransfersToPOLYWBTC',\n",
       "       'totalChainTransfersToPOLYWBTC', 'totalTransfersToPOLYUNI-V2',\n",
       "       'totalChainTransfersToPOLYUNI-V2', 'totalTransfersToPOLYMIM',\n",
       "       'totalChainTransfersToPOLYMIM', 'totalTransfersToPOLYDAI.e',\n",
       "       'totalChainTransfersToPOLYDAI.e', 'Withdraw'],\n",
       "      dtype='object', length=143)"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "userDf.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "649518fe",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['USDC', 'WETH', 'USDT', 'DAI', 'USDC.e', 'OP', 'sUSD', 'JLP', 'VELO',\n",
       "       'USDT.e', 'WMATIC', 'WBTC', 'UNI-V2', 'MIM', 'DAI.e'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "topAssets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "4e9bfbf9",
   "metadata": {},
   "outputs": [],
   "source": [
    "from fastai.tabular.all import *\n",
    "\n",
    "# Here we want to predict if a users will withdraw or not in the next X days based on their historical interactions with AAVE \n",
    "\n",
    "cat_names = ['pool']\n",
    "cont_names = ['totalMints', 'totalBurns']\n",
    "for chain in chains : \n",
    "    cont_names.append('totalChainTransfersTo' + chain)\n",
    "    cont_names.append('totalChainTransfersFrom' + chain)\n",
    "    cont_names.append('firstTransfer' + chain)\n",
    "    cont_names.append('lastTransfer' + chain)\n",
    "\n",
    "    for asset in ['USDC'] :\n",
    "        x = 1\n",
    "        #cont_names.append('totalTransfersTo' + chain + asset)\n",
    "        #cont_names.append('totalChainTransfersTo' + chain + asset)\n",
    "\n",
    "splits = RandomSplitter(valid_pct=0.25)(range_of(userDf))\n",
    "\n",
    "procs = [Categorify, FillMissing, Normalize]\n",
    "dls = TabularDataLoaders.from_df(userDf, '', procs=procs, cat_names=cat_names, cont_names=cont_names, \n",
    "                                 y_names='Withdraw', splits=splits, bs=32)\n",
    "learn = tabular_learner(dls, metrics=accuracy)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "8a7e261d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "<style>\n",
       "    /* Turns off some styling */\n",
       "    progress {\n",
       "        /* gets rid of default border in Firefox and Opera. */\n",
       "        border: none;\n",
       "        /* Needs to be in here for Safari polyfill so background images work as expected. */\n",
       "        background-size: auto;\n",
       "    }\n",
       "    progress:not([value]), progress:not([value])::-webkit-progress-bar {\n",
       "        background: repeating-linear-gradient(45deg, #7e7e7e, #7e7e7e 10px, #5c5c5c 10px, #5c5c5c 20px);\n",
       "    }\n",
       "    .progress-bar-interrupted, .progress-bar-interrupted::-webkit-progress-bar {\n",
       "        background: #F44336;\n",
       "    }\n",
       "</style>\n"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: left;\">\n",
       "      <th>epoch</th>\n",
       "      <th>train_loss</th>\n",
       "      <th>valid_loss</th>\n",
       "      <th>accuracy</th>\n",
       "      <th>time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0.755302</td>\n",
       "      <td>0.672699</td>\n",
       "      <td>0.656410</td>\n",
       "      <td>00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>0.709033</td>\n",
       "      <td>0.626402</td>\n",
       "      <td>0.723077</td>\n",
       "      <td>00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>0.653357</td>\n",
       "      <td>0.559573</td>\n",
       "      <td>0.774359</td>\n",
       "      <td>00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>0.603243</td>\n",
       "      <td>0.539794</td>\n",
       "      <td>0.789744</td>\n",
       "      <td>00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>0.565833</td>\n",
       "      <td>0.539245</td>\n",
       "      <td>0.800000</td>\n",
       "      <td>00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "learn.fit_one_cycle(5)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8e57f663",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}