transform_magic_token_data_to_csv.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "4933fb49-d388-4847-8996-952c4858e4f7",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "from datetime import date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "34e7aea9-bd63-4411-ad38-0d1656ee5bed",
   "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>hash</th>\n",
       "      <th>from</th>\n",
       "      <th>to</th>\n",
       "      <th>value</th>\n",
       "      <th>rawContract.address</th>\n",
       "      <th>metadata.blockTimestamp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0x3d7c88327ec852ebf30b59b8ae4e42865e8fc71c057f...</td>\n",
       "      <td>0x0000000000000000000000000000000000000000</td>\n",
       "      <td>0xa4b1838cb086dddafa655f247716b502e87a0672</td>\n",
       "      <td>4.200000</td>\n",
       "      <td>0x539bde0d7dbd336b79148aa742883198bbf60342</td>\n",
       "      <td>2021-10-08 19:02:23+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0x67384ced75282bc4cee72708f6e1ec5c577376407fa4...</td>\n",
       "      <td>0x0000000000000000000000000000000000000000</td>\n",
       "      <td>0xa82fcefd303fa68864b787a5f118b09ce5a4c93d</td>\n",
       "      <td>50.000000</td>\n",
       "      <td>0x539bde0d7dbd336b79148aa742883198bbf60342</td>\n",
       "      <td>2021-10-10 10:56:05+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0x81b729a1634fb59dcc1a769963f41dc227e1826eeea2...</td>\n",
       "      <td>0x0000000000000000000000000000000000000000</td>\n",
       "      <td>0xe8d46499994f4cf0f80feea5cfc9d9d502985286</td>\n",
       "      <td>2177.000000</td>\n",
       "      <td>0x539bde0d7dbd336b79148aa742883198bbf60342</td>\n",
       "      <td>2021-10-10 11:35:00+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0xec1b7b175a2d3a557a234c3a4e744a30010bea996cd1...</td>\n",
       "      <td>0x0000000000000000000000000000000000000000</td>\n",
       "      <td>0x8a83716acd66d9e1fb18c9b79540b72e04f80ac0</td>\n",
       "      <td>1551.848954</td>\n",
       "      <td>0x539bde0d7dbd336b79148aa742883198bbf60342</td>\n",
       "      <td>2021-10-11 11:03:55+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0x0ae48d8d59cf9cf85c495fc0d53b79cd347a38411a7d...</td>\n",
       "      <td>0x0000000000000000000000000000000000000000</td>\n",
       "      <td>0x9dcc35ae915926f7f5e8c624254d91f755d55b71</td>\n",
       "      <td>193256.857645</td>\n",
       "      <td>0x539bde0d7dbd336b79148aa742883198bbf60342</td>\n",
       "      <td>2021-10-11 18:20:48+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2781995</th>\n",
       "      <td>0xa2ca60ac8063c0b1e1490ac4194332e5999a139adc39...</td>\n",
       "      <td>0x314f0a7bb5ddb4daecece9d3297e00e088713aff</td>\n",
       "      <td>0xb7e50106a5bd3cf21af210a755f9c8740890a8c9</td>\n",
       "      <td>0.500000</td>\n",
       "      <td>0x539bde0d7dbd336b79148aa742883198bbf60342</td>\n",
       "      <td>2022-08-29 08:47:54+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2781996</th>\n",
       "      <td>0xec249a8f5785da118f4f87558e745a054b0e6b454dac...</td>\n",
       "      <td>0xf44f17a6fc5d2f0ad1ff47e682570fa5a8eb9050</td>\n",
       "      <td>0x2546ad279bae9dd46696ec40aa2a95e50a15cfec</td>\n",
       "      <td>0.002000</td>\n",
       "      <td>0x539bde0d7dbd336b79148aa742883198bbf60342</td>\n",
       "      <td>2022-08-29 08:47:54+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2781997</th>\n",
       "      <td>0xa156096235db13ee734ad672e3fb0bb5c234e70ad123...</td>\n",
       "      <td>0x59d72ddb29da32847a4665d08ffc8464a7185fae</td>\n",
       "      <td>0x01d95ff4cf1a446e27eaa0072602e38dc43b702d</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0x539bde0d7dbd336b79148aa742883198bbf60342</td>\n",
       "      <td>2022-08-29 08:47:54+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2781998</th>\n",
       "      <td>0x9f74024d69887cc4cd67c7f2b2e95e004ae588b0c4bb...</td>\n",
       "      <td>0xafcc50574e437829ff4b809f01197a68690910f0</td>\n",
       "      <td>0xb9c9ed651eb173ca7fbc3a094da9ce33ec145a29</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>0x539bde0d7dbd336b79148aa742883198bbf60342</td>\n",
       "      <td>2022-08-29 08:47:54+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2781999</th>\n",
       "      <td>0xde2484455c782c1858c42198e548e4295a91698d4f52...</td>\n",
       "      <td>0x7e7fb3cceca5f2ac952edf221fd2a9f62e411980</td>\n",
       "      <td>0x5a7744e0f1935ffbfc16f3ece13df8ecd05e25b3</td>\n",
       "      <td>0.001000</td>\n",
       "      <td>0x539bde0d7dbd336b79148aa742883198bbf60342</td>\n",
       "      <td>2022-08-29 08:47:54+00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2782000 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                      hash  \\\n",
       "0        0x3d7c88327ec852ebf30b59b8ae4e42865e8fc71c057f...   \n",
       "1        0x67384ced75282bc4cee72708f6e1ec5c577376407fa4...   \n",
       "2        0x81b729a1634fb59dcc1a769963f41dc227e1826eeea2...   \n",
       "3        0xec1b7b175a2d3a557a234c3a4e744a30010bea996cd1...   \n",
       "4        0x0ae48d8d59cf9cf85c495fc0d53b79cd347a38411a7d...   \n",
       "...                                                    ...   \n",
       "2781995  0xa2ca60ac8063c0b1e1490ac4194332e5999a139adc39...   \n",
       "2781996  0xec249a8f5785da118f4f87558e745a054b0e6b454dac...   \n",
       "2781997  0xa156096235db13ee734ad672e3fb0bb5c234e70ad123...   \n",
       "2781998  0x9f74024d69887cc4cd67c7f2b2e95e004ae588b0c4bb...   \n",
       "2781999  0xde2484455c782c1858c42198e548e4295a91698d4f52...   \n",
       "\n",
       "                                               from  \\\n",
       "0        0x0000000000000000000000000000000000000000   \n",
       "1        0x0000000000000000000000000000000000000000   \n",
       "2        0x0000000000000000000000000000000000000000   \n",
       "3        0x0000000000000000000000000000000000000000   \n",
       "4        0x0000000000000000000000000000000000000000   \n",
       "...                                             ...   \n",
       "2781995  0x314f0a7bb5ddb4daecece9d3297e00e088713aff   \n",
       "2781996  0xf44f17a6fc5d2f0ad1ff47e682570fa5a8eb9050   \n",
       "2781997  0x59d72ddb29da32847a4665d08ffc8464a7185fae   \n",
       "2781998  0xafcc50574e437829ff4b809f01197a68690910f0   \n",
       "2781999  0x7e7fb3cceca5f2ac952edf221fd2a9f62e411980   \n",
       "\n",
       "                                                 to          value  \\\n",
       "0        0xa4b1838cb086dddafa655f247716b502e87a0672       4.200000   \n",
       "1        0xa82fcefd303fa68864b787a5f118b09ce5a4c93d      50.000000   \n",
       "2        0xe8d46499994f4cf0f80feea5cfc9d9d502985286    2177.000000   \n",
       "3        0x8a83716acd66d9e1fb18c9b79540b72e04f80ac0    1551.848954   \n",
       "4        0x9dcc35ae915926f7f5e8c624254d91f755d55b71  193256.857645   \n",
       "...                                             ...            ...   \n",
       "2781995  0xb7e50106a5bd3cf21af210a755f9c8740890a8c9       0.500000   \n",
       "2781996  0x2546ad279bae9dd46696ec40aa2a95e50a15cfec       0.002000   \n",
       "2781997  0x01d95ff4cf1a446e27eaa0072602e38dc43b702d       0.010000   \n",
       "2781998  0xb9c9ed651eb173ca7fbc3a094da9ce33ec145a29       5.000000   \n",
       "2781999  0x5a7744e0f1935ffbfc16f3ece13df8ecd05e25b3       0.001000   \n",
       "\n",
       "                                rawContract.address   metadata.blockTimestamp  \n",
       "0        0x539bde0d7dbd336b79148aa742883198bbf60342 2021-10-08 19:02:23+00:00  \n",
       "1        0x539bde0d7dbd336b79148aa742883198bbf60342 2021-10-10 10:56:05+00:00  \n",
       "2        0x539bde0d7dbd336b79148aa742883198bbf60342 2021-10-10 11:35:00+00:00  \n",
       "3        0x539bde0d7dbd336b79148aa742883198bbf60342 2021-10-11 11:03:55+00:00  \n",
       "4        0x539bde0d7dbd336b79148aa742883198bbf60342 2021-10-11 18:20:48+00:00  \n",
       "...                                             ...                       ...  \n",
       "2781995  0x539bde0d7dbd336b79148aa742883198bbf60342 2022-08-29 08:47:54+00:00  \n",
       "2781996  0x539bde0d7dbd336b79148aa742883198bbf60342 2022-08-29 08:47:54+00:00  \n",
       "2781997  0x539bde0d7dbd336b79148aa742883198bbf60342 2022-08-29 08:47:54+00:00  \n",
       "2781998  0x539bde0d7dbd336b79148aa742883198bbf60342 2022-08-29 08:47:54+00:00  \n",
       "2781999  0x539bde0d7dbd336b79148aa742883198bbf60342 2022-08-29 08:47:54+00:00  \n",
       "\n",
       "[2782000 rows x 6 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_transfers = pd.read_csv('/home/stubbs/Documents/skycatcher/TreasureDAO/df_transfers.csv' \n",
    "                          , usecols=['hash', 'from', 'to', 'value', 'rawContract.address', 'metadata.blockTimestamp']\n",
    "                            , parse_dates=['metadata.blockTimestamp']\n",
    "                          )\n",
    "                          \n",
    "df_transfers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "ab799ccf-8eba-4a0a-9fc5-d42304d9bdad",
   "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>hash</th>\n",
       "      <th>from</th>\n",
       "      <th>to</th>\n",
       "      <th>value</th>\n",
       "      <th>rawContract.address</th>\n",
       "      <th>metadata.blockTimestamp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0x3d7c88327ec852ebf30b59b8ae4e42865e8fc71c057f...</td>\n",
       "      <td>0x0000000000000000000000000000000000000000</td>\n",
       "      <td>0xa4b1838cb086dddafa655f247716b502e87a0672</td>\n",
       "      <td>4.200000</td>\n",
       "      <td>0x539bde0d7dbd336b79148aa742883198bbf60342</td>\n",
       "      <td>2021-10-08 19:02:23+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0x67384ced75282bc4cee72708f6e1ec5c577376407fa4...</td>\n",
       "      <td>0x0000000000000000000000000000000000000000</td>\n",
       "      <td>0xa82fcefd303fa68864b787a5f118b09ce5a4c93d</td>\n",
       "      <td>50.000000</td>\n",
       "      <td>0x539bde0d7dbd336b79148aa742883198bbf60342</td>\n",
       "      <td>2021-10-10 10:56:05+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0x81b729a1634fb59dcc1a769963f41dc227e1826eeea2...</td>\n",
       "      <td>0x0000000000000000000000000000000000000000</td>\n",
       "      <td>0xe8d46499994f4cf0f80feea5cfc9d9d502985286</td>\n",
       "      <td>2177.000000</td>\n",
       "      <td>0x539bde0d7dbd336b79148aa742883198bbf60342</td>\n",
       "      <td>2021-10-10 11:35:00+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0xec1b7b175a2d3a557a234c3a4e744a30010bea996cd1...</td>\n",
       "      <td>0x0000000000000000000000000000000000000000</td>\n",
       "      <td>0x8a83716acd66d9e1fb18c9b79540b72e04f80ac0</td>\n",
       "      <td>1551.848954</td>\n",
       "      <td>0x539bde0d7dbd336b79148aa742883198bbf60342</td>\n",
       "      <td>2021-10-11 11:03:55+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0x0ae48d8d59cf9cf85c495fc0d53b79cd347a38411a7d...</td>\n",
       "      <td>0x0000000000000000000000000000000000000000</td>\n",
       "      <td>0x9dcc35ae915926f7f5e8c624254d91f755d55b71</td>\n",
       "      <td>193256.857645</td>\n",
       "      <td>0x539bde0d7dbd336b79148aa742883198bbf60342</td>\n",
       "      <td>2021-10-11 18:20:48+00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                hash  \\\n",
       "0  0x3d7c88327ec852ebf30b59b8ae4e42865e8fc71c057f...   \n",
       "1  0x67384ced75282bc4cee72708f6e1ec5c577376407fa4...   \n",
       "2  0x81b729a1634fb59dcc1a769963f41dc227e1826eeea2...   \n",
       "3  0xec1b7b175a2d3a557a234c3a4e744a30010bea996cd1...   \n",
       "4  0x0ae48d8d59cf9cf85c495fc0d53b79cd347a38411a7d...   \n",
       "\n",
       "                                         from  \\\n",
       "0  0x0000000000000000000000000000000000000000   \n",
       "1  0x0000000000000000000000000000000000000000   \n",
       "2  0x0000000000000000000000000000000000000000   \n",
       "3  0x0000000000000000000000000000000000000000   \n",
       "4  0x0000000000000000000000000000000000000000   \n",
       "\n",
       "                                           to          value  \\\n",
       "0  0xa4b1838cb086dddafa655f247716b502e87a0672       4.200000   \n",
       "1  0xa82fcefd303fa68864b787a5f118b09ce5a4c93d      50.000000   \n",
       "2  0xe8d46499994f4cf0f80feea5cfc9d9d502985286    2177.000000   \n",
       "3  0x8a83716acd66d9e1fb18c9b79540b72e04f80ac0    1551.848954   \n",
       "4  0x9dcc35ae915926f7f5e8c624254d91f755d55b71  193256.857645   \n",
       "\n",
       "                          rawContract.address   metadata.blockTimestamp  \n",
       "0  0x539bde0d7dbd336b79148aa742883198bbf60342 2021-10-08 19:02:23+00:00  \n",
       "1  0x539bde0d7dbd336b79148aa742883198bbf60342 2021-10-10 10:56:05+00:00  \n",
       "2  0x539bde0d7dbd336b79148aa742883198bbf60342 2021-10-10 11:35:00+00:00  \n",
       "3  0x539bde0d7dbd336b79148aa742883198bbf60342 2021-10-11 11:03:55+00:00  \n",
       "4  0x539bde0d7dbd336b79148aa742883198bbf60342 2021-10-11 18:20:48+00:00  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_transfers.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "402a54c4-9ae5-461a-bb74-0cb79e0e73be",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2782000 entries, 0 to 2781999\n",
      "Data columns (total 6 columns):\n",
      " #   Column                   Dtype              \n",
      "---  ------                   -----              \n",
      " 0   hash                     object             \n",
      " 1   from                     object             \n",
      " 2   to                       object             \n",
      " 3   value                    float64            \n",
      " 4   rawContract.address      object             \n",
      " 5   metadata.blockTimestamp  datetime64[ns, UTC]\n",
      "dtypes: datetime64[ns, UTC](1), float64(1), object(4)\n",
      "memory usage: 127.3+ MB\n"
     ]
    }
   ],
   "source": [
    "df_transfers.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "54944aa1-5054-4ebb-9fb1-0310edd59613",
   "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>address</th>\n",
       "      <th>amount</th>\n",
       "      <th>tx_timestamp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0x3d7c88327ec852ebf30b59b8ae4e42865e8fc71c057f...</td>\n",
       "      <td>0x0000000000000000000000000000000000000000</td>\n",
       "      <td>-4.200000</td>\n",
       "      <td>2021-10-08 19:02:23+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0x67384ced75282bc4cee72708f6e1ec5c577376407fa4...</td>\n",
       "      <td>0x0000000000000000000000000000000000000000</td>\n",
       "      <td>-50.000000</td>\n",
       "      <td>2021-10-10 10:56:05+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0x81b729a1634fb59dcc1a769963f41dc227e1826eeea2...</td>\n",
       "      <td>0x0000000000000000000000000000000000000000</td>\n",
       "      <td>-2177.000000</td>\n",
       "      <td>2021-10-10 11:35:00+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0xec1b7b175a2d3a557a234c3a4e744a30010bea996cd1...</td>\n",
       "      <td>0x0000000000000000000000000000000000000000</td>\n",
       "      <td>-1551.848954</td>\n",
       "      <td>2021-10-11 11:03:55+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0x0ae48d8d59cf9cf85c495fc0d53b79cd347a38411a7d...</td>\n",
       "      <td>0x0000000000000000000000000000000000000000</td>\n",
       "      <td>-193256.857645</td>\n",
       "      <td>2021-10-11 18:20:48+00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             tx_hash  \\\n",
       "0  0x3d7c88327ec852ebf30b59b8ae4e42865e8fc71c057f...   \n",
       "1  0x67384ced75282bc4cee72708f6e1ec5c577376407fa4...   \n",
       "2  0x81b729a1634fb59dcc1a769963f41dc227e1826eeea2...   \n",
       "3  0xec1b7b175a2d3a557a234c3a4e744a30010bea996cd1...   \n",
       "4  0x0ae48d8d59cf9cf85c495fc0d53b79cd347a38411a7d...   \n",
       "\n",
       "                                      address         amount  \\\n",
       "0  0x0000000000000000000000000000000000000000      -4.200000   \n",
       "1  0x0000000000000000000000000000000000000000     -50.000000   \n",
       "2  0x0000000000000000000000000000000000000000   -2177.000000   \n",
       "3  0x0000000000000000000000000000000000000000   -1551.848954   \n",
       "4  0x0000000000000000000000000000000000000000 -193256.857645   \n",
       "\n",
       "               tx_timestamp  \n",
       "0 2021-10-08 19:02:23+00:00  \n",
       "1 2021-10-10 10:56:05+00:00  \n",
       "2 2021-10-10 11:35:00+00:00  \n",
       "3 2021-10-11 11:03:55+00:00  \n",
       "4 2021-10-11 18:20:48+00:00  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_transfers_out = df_transfers[['hash', 'from', 'value', 'metadata.blockTimestamp']].copy()\n",
    "df_transfers_out['value'] = df_transfers_out['value'].apply(lambda x: -1.0* x)\n",
    "df_transfers_out.rename(columns = {'hash': 'tx_hash', 'from':'address', 'value':'amount', 'metadata.blockTimestamp':'tx_timestamp'}, inplace=True)\n",
    "df_transfers_out.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "c942566a-fb0e-4f79-9721-455245e9eea5",
   "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>address</th>\n",
       "      <th>amount</th>\n",
       "      <th>tx_timestamp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0x3d7c88327ec852ebf30b59b8ae4e42865e8fc71c057f...</td>\n",
       "      <td>0xa4b1838cb086dddafa655f247716b502e87a0672</td>\n",
       "      <td>4.200000</td>\n",
       "      <td>2021-10-08 19:02:23+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0x67384ced75282bc4cee72708f6e1ec5c577376407fa4...</td>\n",
       "      <td>0xa82fcefd303fa68864b787a5f118b09ce5a4c93d</td>\n",
       "      <td>50.000000</td>\n",
       "      <td>2021-10-10 10:56:05+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0x81b729a1634fb59dcc1a769963f41dc227e1826eeea2...</td>\n",
       "      <td>0xe8d46499994f4cf0f80feea5cfc9d9d502985286</td>\n",
       "      <td>2177.000000</td>\n",
       "      <td>2021-10-10 11:35:00+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0xec1b7b175a2d3a557a234c3a4e744a30010bea996cd1...</td>\n",
       "      <td>0x8a83716acd66d9e1fb18c9b79540b72e04f80ac0</td>\n",
       "      <td>1551.848954</td>\n",
       "      <td>2021-10-11 11:03:55+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0x0ae48d8d59cf9cf85c495fc0d53b79cd347a38411a7d...</td>\n",
       "      <td>0x9dcc35ae915926f7f5e8c624254d91f755d55b71</td>\n",
       "      <td>193256.857645</td>\n",
       "      <td>2021-10-11 18:20:48+00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             tx_hash  \\\n",
       "0  0x3d7c88327ec852ebf30b59b8ae4e42865e8fc71c057f...   \n",
       "1  0x67384ced75282bc4cee72708f6e1ec5c577376407fa4...   \n",
       "2  0x81b729a1634fb59dcc1a769963f41dc227e1826eeea2...   \n",
       "3  0xec1b7b175a2d3a557a234c3a4e744a30010bea996cd1...   \n",
       "4  0x0ae48d8d59cf9cf85c495fc0d53b79cd347a38411a7d...   \n",
       "\n",
       "                                      address         amount  \\\n",
       "0  0xa4b1838cb086dddafa655f247716b502e87a0672       4.200000   \n",
       "1  0xa82fcefd303fa68864b787a5f118b09ce5a4c93d      50.000000   \n",
       "2  0xe8d46499994f4cf0f80feea5cfc9d9d502985286    2177.000000   \n",
       "3  0x8a83716acd66d9e1fb18c9b79540b72e04f80ac0    1551.848954   \n",
       "4  0x9dcc35ae915926f7f5e8c624254d91f755d55b71  193256.857645   \n",
       "\n",
       "               tx_timestamp  \n",
       "0 2021-10-08 19:02:23+00:00  \n",
       "1 2021-10-10 10:56:05+00:00  \n",
       "2 2021-10-10 11:35:00+00:00  \n",
       "3 2021-10-11 11:03:55+00:00  \n",
       "4 2021-10-11 18:20:48+00:00  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_transfers_in = df_transfers[['hash', 'to', 'value', 'metadata.blockTimestamp']].copy()\n",
    "df_transfers_in.rename(columns = {'hash': 'tx_hash', 'to':'address', 'value':'amount', 'metadata.blockTimestamp':'tx_timestamp'}, inplace=True)\n",
    "df_transfers_in.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "0c8eb358-798d-4ed6-b8a4-3256b1d08d5d",
   "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>address</th>\n",
       "      <th>amount</th>\n",
       "      <th>tx_timestamp</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0x3d7c88327ec852ebf30b59b8ae4e42865e8fc71c057f...</td>\n",
       "      <td>0xa4b1838cb086dddafa655f247716b502e87a0672</td>\n",
       "      <td>4.200000</td>\n",
       "      <td>2021-10-08 19:02:23+00:00</td>\n",
       "      <td>2021-10-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0x67384ced75282bc4cee72708f6e1ec5c577376407fa4...</td>\n",
       "      <td>0xa82fcefd303fa68864b787a5f118b09ce5a4c93d</td>\n",
       "      <td>50.000000</td>\n",
       "      <td>2021-10-10 10:56:05+00:00</td>\n",
       "      <td>2021-10-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0x81b729a1634fb59dcc1a769963f41dc227e1826eeea2...</td>\n",
       "      <td>0xe8d46499994f4cf0f80feea5cfc9d9d502985286</td>\n",
       "      <td>2177.000000</td>\n",
       "      <td>2021-10-10 11:35:00+00:00</td>\n",
       "      <td>2021-10-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0xec1b7b175a2d3a557a234c3a4e744a30010bea996cd1...</td>\n",
       "      <td>0x8a83716acd66d9e1fb18c9b79540b72e04f80ac0</td>\n",
       "      <td>1551.848954</td>\n",
       "      <td>2021-10-11 11:03:55+00:00</td>\n",
       "      <td>2021-10-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0x0ae48d8d59cf9cf85c495fc0d53b79cd347a38411a7d...</td>\n",
       "      <td>0x9dcc35ae915926f7f5e8c624254d91f755d55b71</td>\n",
       "      <td>193256.857645</td>\n",
       "      <td>2021-10-11 18:20:48+00:00</td>\n",
       "      <td>2021-10-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2781995</th>\n",
       "      <td>0xa2ca60ac8063c0b1e1490ac4194332e5999a139adc39...</td>\n",
       "      <td>0x314f0a7bb5ddb4daecece9d3297e00e088713aff</td>\n",
       "      <td>-0.500000</td>\n",
       "      <td>2022-08-29 08:47:54+00:00</td>\n",
       "      <td>2022-08-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2781996</th>\n",
       "      <td>0xec249a8f5785da118f4f87558e745a054b0e6b454dac...</td>\n",
       "      <td>0xf44f17a6fc5d2f0ad1ff47e682570fa5a8eb9050</td>\n",
       "      <td>-0.002000</td>\n",
       "      <td>2022-08-29 08:47:54+00:00</td>\n",
       "      <td>2022-08-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2781997</th>\n",
       "      <td>0xa156096235db13ee734ad672e3fb0bb5c234e70ad123...</td>\n",
       "      <td>0x59d72ddb29da32847a4665d08ffc8464a7185fae</td>\n",
       "      <td>-0.010000</td>\n",
       "      <td>2022-08-29 08:47:54+00:00</td>\n",
       "      <td>2022-08-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2781998</th>\n",
       "      <td>0x9f74024d69887cc4cd67c7f2b2e95e004ae588b0c4bb...</td>\n",
       "      <td>0xafcc50574e437829ff4b809f01197a68690910f0</td>\n",
       "      <td>-5.000000</td>\n",
       "      <td>2022-08-29 08:47:54+00:00</td>\n",
       "      <td>2022-08-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2781999</th>\n",
       "      <td>0xde2484455c782c1858c42198e548e4295a91698d4f52...</td>\n",
       "      <td>0x7e7fb3cceca5f2ac952edf221fd2a9f62e411980</td>\n",
       "      <td>-0.001000</td>\n",
       "      <td>2022-08-29 08:47:54+00:00</td>\n",
       "      <td>2022-08-29</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5564000 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   tx_hash  \\\n",
       "0        0x3d7c88327ec852ebf30b59b8ae4e42865e8fc71c057f...   \n",
       "1        0x67384ced75282bc4cee72708f6e1ec5c577376407fa4...   \n",
       "2        0x81b729a1634fb59dcc1a769963f41dc227e1826eeea2...   \n",
       "3        0xec1b7b175a2d3a557a234c3a4e744a30010bea996cd1...   \n",
       "4        0x0ae48d8d59cf9cf85c495fc0d53b79cd347a38411a7d...   \n",
       "...                                                    ...   \n",
       "2781995  0xa2ca60ac8063c0b1e1490ac4194332e5999a139adc39...   \n",
       "2781996  0xec249a8f5785da118f4f87558e745a054b0e6b454dac...   \n",
       "2781997  0xa156096235db13ee734ad672e3fb0bb5c234e70ad123...   \n",
       "2781998  0x9f74024d69887cc4cd67c7f2b2e95e004ae588b0c4bb...   \n",
       "2781999  0xde2484455c782c1858c42198e548e4295a91698d4f52...   \n",
       "\n",
       "                                            address         amount  \\\n",
       "0        0xa4b1838cb086dddafa655f247716b502e87a0672       4.200000   \n",
       "1        0xa82fcefd303fa68864b787a5f118b09ce5a4c93d      50.000000   \n",
       "2        0xe8d46499994f4cf0f80feea5cfc9d9d502985286    2177.000000   \n",
       "3        0x8a83716acd66d9e1fb18c9b79540b72e04f80ac0    1551.848954   \n",
       "4        0x9dcc35ae915926f7f5e8c624254d91f755d55b71  193256.857645   \n",
       "...                                             ...            ...   \n",
       "2781995  0x314f0a7bb5ddb4daecece9d3297e00e088713aff      -0.500000   \n",
       "2781996  0xf44f17a6fc5d2f0ad1ff47e682570fa5a8eb9050      -0.002000   \n",
       "2781997  0x59d72ddb29da32847a4665d08ffc8464a7185fae      -0.010000   \n",
       "2781998  0xafcc50574e437829ff4b809f01197a68690910f0      -5.000000   \n",
       "2781999  0x7e7fb3cceca5f2ac952edf221fd2a9f62e411980      -0.001000   \n",
       "\n",
       "                     tx_timestamp       date  \n",
       "0       2021-10-08 19:02:23+00:00 2021-10-08  \n",
       "1       2021-10-10 10:56:05+00:00 2021-10-10  \n",
       "2       2021-10-10 11:35:00+00:00 2021-10-10  \n",
       "3       2021-10-11 11:03:55+00:00 2021-10-11  \n",
       "4       2021-10-11 18:20:48+00:00 2021-10-11  \n",
       "...                           ...        ...  \n",
       "2781995 2022-08-29 08:47:54+00:00 2022-08-29  \n",
       "2781996 2022-08-29 08:47:54+00:00 2022-08-29  \n",
       "2781997 2022-08-29 08:47:54+00:00 2022-08-29  \n",
       "2781998 2022-08-29 08:47:54+00:00 2022-08-29  \n",
       "2781999 2022-08-29 08:47:54+00:00 2022-08-29  \n",
       "\n",
       "[5564000 rows x 5 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# transfer_in_n_out = pd.concat([df_transfers_in.head(), df_transfers_out.head()], axis=0)\n",
    "transfer_in_n_out=pd.concat([df_transfers_in, df_transfers_out], axis=0)\n",
    "transfer_in_n_out['date'] = pd.to_datetime(transfer_in_n_out['tx_timestamp'].apply(lambda x:x.date()))\n",
    "transfer_in_n_out"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "4e05623e-206d-4051-a6ce-616e22026619",
   "metadata": {},
   "outputs": [],
   "source": [
    "transfers_all = transfer_in_n_out[['date', 'address', 'amount']]"
   ]
  },
  {
   "cell_type": "raw",
   "id": "1c0425a4-4304-4932-b502-180daa2bd8bf",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "616e8a5e-3bd5-4c37-ba99-fc09fef7f7c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "start_day = pd.to_datetime(transfer_in_n_out['tx_timestamp'].dt.date).min()\n",
    "end_day = pd.to_datetime(transfer_in_n_out['tx_timestamp'].dt.date).max()\n",
    "# days = transfer_in_n_out\n",
    "\n",
    "# print(start_day, end_day)\n",
    "date_range = pd.DataFrame(pd.date_range(start_day, end_day, inclusive=\"both\"))\n",
    "date_range['key'] = 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "e60ef0f3-4f96-499f-b502-8098507895d0",
   "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>0</th>\n",
       "      <th>key</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0xa4b1838cb086dddafa655f247716b502e87a0672</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0xa82fcefd303fa68864b787a5f118b09ce5a4c93d</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0xe8d46499994f4cf0f80feea5cfc9d9d502985286</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0x8a83716acd66d9e1fb18c9b79540b72e04f80ac0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0x9dcc35ae915926f7f5e8c624254d91f755d55b71</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109618</th>\n",
       "      <td>0x37d6b26afb03d87b68a1f7f0994d8e9dec16dc48</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109619</th>\n",
       "      <td>0x8c2de6d43f26c479871de1c9bf7e4d683f79fa6e</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109620</th>\n",
       "      <td>0xf68136142ba1702be2e690864f6b3cfce11ad418</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109621</th>\n",
       "      <td>0x515c27dd10d117783346c020bdfef6f3081b2cb3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109622</th>\n",
       "      <td>0xa7f7b336225c8597ed085ef47724fa7a0fc7b290</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>109623 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 0  key\n",
       "0       0xa4b1838cb086dddafa655f247716b502e87a0672    1\n",
       "1       0xa82fcefd303fa68864b787a5f118b09ce5a4c93d    1\n",
       "2       0xe8d46499994f4cf0f80feea5cfc9d9d502985286    1\n",
       "3       0x8a83716acd66d9e1fb18c9b79540b72e04f80ac0    1\n",
       "4       0x9dcc35ae915926f7f5e8c624254d91f755d55b71    1\n",
       "...                                            ...  ...\n",
       "109618  0x37d6b26afb03d87b68a1f7f0994d8e9dec16dc48    1\n",
       "109619  0x8c2de6d43f26c479871de1c9bf7e4d683f79fa6e    1\n",
       "109620  0xf68136142ba1702be2e690864f6b3cfce11ad418    1\n",
       "109621  0x515c27dd10d117783346c020bdfef6f3081b2cb3    1\n",
       "109622  0xa7f7b336225c8597ed085ef47724fa7a0fc7b290    1\n",
       "\n",
       "[109623 rows x 2 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Get all the wallets\n",
    "wallets = pd.DataFrame(transfer_in_n_out['address'].unique())\n",
    "wallets['key'] = 1\n",
    "wallets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "68cedffa-9dc1-476b-a8b8-9d9c4309a31b",
   "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>0</th>\n",
       "      <th>key</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-10-08</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-10-09</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-10-10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-10-11</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-10-12</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>321</th>\n",
       "      <td>2022-08-25</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>322</th>\n",
       "      <td>2022-08-26</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>323</th>\n",
       "      <td>2022-08-27</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>324</th>\n",
       "      <td>2022-08-28</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>325</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>326 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             0  key\n",
       "0   2021-10-08    1\n",
       "1   2021-10-09    1\n",
       "2   2021-10-10    1\n",
       "3   2021-10-11    1\n",
       "4   2021-10-12    1\n",
       "..         ...  ...\n",
       "321 2022-08-25    1\n",
       "322 2022-08-26    1\n",
       "323 2022-08-27    1\n",
       "324 2022-08-28    1\n",
       "325 2022-08-29    1\n",
       "\n",
       "[326 rows x 2 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "date_range"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "ea9006ed-e61e-4ccf-a987-2f2de959adbe",
   "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>wallet_address</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0xa4b1838cb086dddafa655f247716b502e87a0672</td>\n",
       "      <td>2021-10-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0xa4b1838cb086dddafa655f247716b502e87a0672</td>\n",
       "      <td>2021-10-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0xa4b1838cb086dddafa655f247716b502e87a0672</td>\n",
       "      <td>2021-10-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0xa4b1838cb086dddafa655f247716b502e87a0672</td>\n",
       "      <td>2021-10-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0xa4b1838cb086dddafa655f247716b502e87a0672</td>\n",
       "      <td>2021-10-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35737093</th>\n",
       "      <td>0xa7f7b336225c8597ed085ef47724fa7a0fc7b290</td>\n",
       "      <td>2022-08-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35737094</th>\n",
       "      <td>0xa7f7b336225c8597ed085ef47724fa7a0fc7b290</td>\n",
       "      <td>2022-08-26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35737095</th>\n",
       "      <td>0xa7f7b336225c8597ed085ef47724fa7a0fc7b290</td>\n",
       "      <td>2022-08-27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35737096</th>\n",
       "      <td>0xa7f7b336225c8597ed085ef47724fa7a0fc7b290</td>\n",
       "      <td>2022-08-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35737097</th>\n",
       "      <td>0xa7f7b336225c8597ed085ef47724fa7a0fc7b290</td>\n",
       "      <td>2022-08-29</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>35737098 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      wallet_address       date\n",
       "0         0xa4b1838cb086dddafa655f247716b502e87a0672 2021-10-08\n",
       "1         0xa4b1838cb086dddafa655f247716b502e87a0672 2021-10-09\n",
       "2         0xa4b1838cb086dddafa655f247716b502e87a0672 2021-10-10\n",
       "3         0xa4b1838cb086dddafa655f247716b502e87a0672 2021-10-11\n",
       "4         0xa4b1838cb086dddafa655f247716b502e87a0672 2021-10-12\n",
       "...                                              ...        ...\n",
       "35737093  0xa7f7b336225c8597ed085ef47724fa7a0fc7b290 2022-08-25\n",
       "35737094  0xa7f7b336225c8597ed085ef47724fa7a0fc7b290 2022-08-26\n",
       "35737095  0xa7f7b336225c8597ed085ef47724fa7a0fc7b290 2022-08-27\n",
       "35737096  0xa7f7b336225c8597ed085ef47724fa7a0fc7b290 2022-08-28\n",
       "35737097  0xa7f7b336225c8597ed085ef47724fa7a0fc7b290 2022-08-29\n",
       "\n",
       "[35737098 rows x 2 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wallets_all_days=pd.merge(wallets, date_range, how='outer', on='key').drop(columns=['key']).rename(columns={'0_x':'wallet_address', '0_y':'date'})\n",
    "wallets_all_days\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "047f7c94-bfbc-4354-9e95-a756088f2f28",
   "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>wallet_address</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8802</th>\n",
       "      <td>0xb7e50106a5bd3cf21af210a755f9c8740890a8c9</td>\n",
       "      <td>2021-10-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8803</th>\n",
       "      <td>0xb7e50106a5bd3cf21af210a755f9c8740890a8c9</td>\n",
       "      <td>2021-10-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8804</th>\n",
       "      <td>0xb7e50106a5bd3cf21af210a755f9c8740890a8c9</td>\n",
       "      <td>2021-10-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8805</th>\n",
       "      <td>0xb7e50106a5bd3cf21af210a755f9c8740890a8c9</td>\n",
       "      <td>2021-10-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8806</th>\n",
       "      <td>0xb7e50106a5bd3cf21af210a755f9c8740890a8c9</td>\n",
       "      <td>2021-10-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9123</th>\n",
       "      <td>0xb7e50106a5bd3cf21af210a755f9c8740890a8c9</td>\n",
       "      <td>2022-08-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9124</th>\n",
       "      <td>0xb7e50106a5bd3cf21af210a755f9c8740890a8c9</td>\n",
       "      <td>2022-08-26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9125</th>\n",
       "      <td>0xb7e50106a5bd3cf21af210a755f9c8740890a8c9</td>\n",
       "      <td>2022-08-27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9126</th>\n",
       "      <td>0xb7e50106a5bd3cf21af210a755f9c8740890a8c9</td>\n",
       "      <td>2022-08-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9127</th>\n",
       "      <td>0xb7e50106a5bd3cf21af210a755f9c8740890a8c9</td>\n",
       "      <td>2022-08-29</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>326 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  wallet_address       date\n",
       "8802  0xb7e50106a5bd3cf21af210a755f9c8740890a8c9 2021-10-08\n",
       "8803  0xb7e50106a5bd3cf21af210a755f9c8740890a8c9 2021-10-09\n",
       "8804  0xb7e50106a5bd3cf21af210a755f9c8740890a8c9 2021-10-10\n",
       "8805  0xb7e50106a5bd3cf21af210a755f9c8740890a8c9 2021-10-11\n",
       "8806  0xb7e50106a5bd3cf21af210a755f9c8740890a8c9 2021-10-12\n",
       "...                                          ...        ...\n",
       "9123  0xb7e50106a5bd3cf21af210a755f9c8740890a8c9 2022-08-25\n",
       "9124  0xb7e50106a5bd3cf21af210a755f9c8740890a8c9 2022-08-26\n",
       "9125  0xb7e50106a5bd3cf21af210a755f9c8740890a8c9 2022-08-27\n",
       "9126  0xb7e50106a5bd3cf21af210a755f9c8740890a8c9 2022-08-28\n",
       "9127  0xb7e50106a5bd3cf21af210a755f9c8740890a8c9 2022-08-29\n",
       "\n",
       "[326 rows x 2 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wallets_all_days[wallets_all_days['wallet_address'] == str.lower('0xB7E50106A5bd3Cf21AF210A755F9C8740890A8c9')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "49e09060-18f9-4e3b-bf27-db7eaffacf70",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 35737098 entries, 0 to 35737097\n",
      "Data columns (total 2 columns):\n",
      " #   Column          Dtype         \n",
      "---  ------          -----         \n",
      " 0   wallet_address  object        \n",
      " 1   date            datetime64[ns]\n",
      "dtypes: datetime64[ns](1), object(1)\n",
      "memory usage: 818.0+ MB\n"
     ]
    }
   ],
   "source": [
    "wallets_all_days.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "9f39d54b-45cb-4f3d-bfe3-8dedbebb8565",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 5564000 entries, 0 to 2781999\n",
      "Data columns (total 5 columns):\n",
      " #   Column        Dtype              \n",
      "---  ------        -----              \n",
      " 0   tx_hash       object             \n",
      " 1   address       object             \n",
      " 2   amount        float64            \n",
      " 3   tx_timestamp  datetime64[ns, UTC]\n",
      " 4   date          datetime64[ns]     \n",
      "dtypes: datetime64[ns, UTC](1), datetime64[ns](1), float64(1), object(2)\n",
      "memory usage: 254.7+ MB\n"
     ]
    }
   ],
   "source": [
    "transfer_in_n_out.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "d8284549-48d6-471b-be66-114fe744cd41",
   "metadata": {},
   "outputs": [],
   "source": [
    "# wallet_all_days_n_changes = wallets_all_days[['date', 'wallet_address']].merge(transfer_in_n_out, how='left', left_on=['wallet_address', 'date'], right_on=['address', 'date']).drop(columns=['address']).fillna(0)\n",
    "# wallet_all_days_n_changes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "cb55984e-7381-4265-bf68-2a8d84bd832c",
   "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>date</th>\n",
       "      <th>wallet_address</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-10-08</td>\n",
       "      <td>0xa4b1838cb086dddafa655f247716b502e87a0672</td>\n",
       "      <td>4.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-10-09</td>\n",
       "      <td>0xa4b1838cb086dddafa655f247716b502e87a0672</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-10-10</td>\n",
       "      <td>0xa4b1838cb086dddafa655f247716b502e87a0672</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-10-11</td>\n",
       "      <td>0xa4b1838cb086dddafa655f247716b502e87a0672</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-10-12</td>\n",
       "      <td>0xa4b1838cb086dddafa655f247716b502e87a0672</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40768630</th>\n",
       "      <td>2022-08-25</td>\n",
       "      <td>0xa7f7b336225c8597ed085ef47724fa7a0fc7b290</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40768631</th>\n",
       "      <td>2022-08-26</td>\n",
       "      <td>0xa7f7b336225c8597ed085ef47724fa7a0fc7b290</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40768632</th>\n",
       "      <td>2022-08-27</td>\n",
       "      <td>0xa7f7b336225c8597ed085ef47724fa7a0fc7b290</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40768633</th>\n",
       "      <td>2022-08-28</td>\n",
       "      <td>0xa7f7b336225c8597ed085ef47724fa7a0fc7b290</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40768634</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xa7f7b336225c8597ed085ef47724fa7a0fc7b290</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>40768635 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               date                              wallet_address  amount\n",
       "0        2021-10-08  0xa4b1838cb086dddafa655f247716b502e87a0672     4.2\n",
       "1        2021-10-09  0xa4b1838cb086dddafa655f247716b502e87a0672     0.0\n",
       "2        2021-10-10  0xa4b1838cb086dddafa655f247716b502e87a0672     0.0\n",
       "3        2021-10-11  0xa4b1838cb086dddafa655f247716b502e87a0672     0.0\n",
       "4        2021-10-12  0xa4b1838cb086dddafa655f247716b502e87a0672     0.0\n",
       "...             ...                                         ...     ...\n",
       "40768630 2022-08-25  0xa7f7b336225c8597ed085ef47724fa7a0fc7b290     0.0\n",
       "40768631 2022-08-26  0xa7f7b336225c8597ed085ef47724fa7a0fc7b290     0.0\n",
       "40768632 2022-08-27  0xa7f7b336225c8597ed085ef47724fa7a0fc7b290     0.0\n",
       "40768633 2022-08-28  0xa7f7b336225c8597ed085ef47724fa7a0fc7b290     0.0\n",
       "40768634 2022-08-29  0xa7f7b336225c8597ed085ef47724fa7a0fc7b290     0.0\n",
       "\n",
       "[40768635 rows x 3 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wallet_all_days_n_changes = wallets_all_days[['date', 'wallet_address']].merge(transfers_all, how='left', left_on=['date', 'wallet_address'], right_on=['date', 'address']).drop(columns=['address']).fillna(0)\n",
    "\n",
    "wallet_all_days_n_changes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "c0f1f14d-2df9-4a9b-b8bf-d4a73929fa51",
   "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>date</th>\n",
       "      <th>wallet_address</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>59944</th>\n",
       "      <td>2021-10-08</td>\n",
       "      <td>0xb7e50106a5bd3cf21af210a755f9c8740890a8c9</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59945</th>\n",
       "      <td>2021-10-09</td>\n",
       "      <td>0xb7e50106a5bd3cf21af210a755f9c8740890a8c9</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59946</th>\n",
       "      <td>2021-10-10</td>\n",
       "      <td>0xb7e50106a5bd3cf21af210a755f9c8740890a8c9</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59947</th>\n",
       "      <td>2021-10-11</td>\n",
       "      <td>0xb7e50106a5bd3cf21af210a755f9c8740890a8c9</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59948</th>\n",
       "      <td>2021-10-12</td>\n",
       "      <td>0xb7e50106a5bd3cf21af210a755f9c8740890a8c9</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>463631</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xb7e50106a5bd3cf21af210a755f9c8740890a8c9</td>\n",
       "      <td>-0.001000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>463632</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xb7e50106a5bd3cf21af210a755f9c8740890a8c9</td>\n",
       "      <td>-0.001000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>463633</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xb7e50106a5bd3cf21af210a755f9c8740890a8c9</td>\n",
       "      <td>-0.001000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>463634</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xb7e50106a5bd3cf21af210a755f9c8740890a8c9</td>\n",
       "      <td>-217.470321</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>463635</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xb7e50106a5bd3cf21af210a755f9c8740890a8c9</td>\n",
       "      <td>-4.518705</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>403692 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             date                              wallet_address      amount\n",
       "59944  2021-10-08  0xb7e50106a5bd3cf21af210a755f9c8740890a8c9    0.000000\n",
       "59945  2021-10-09  0xb7e50106a5bd3cf21af210a755f9c8740890a8c9    0.000000\n",
       "59946  2021-10-10  0xb7e50106a5bd3cf21af210a755f9c8740890a8c9    0.000000\n",
       "59947  2021-10-11  0xb7e50106a5bd3cf21af210a755f9c8740890a8c9    0.000000\n",
       "59948  2021-10-12  0xb7e50106a5bd3cf21af210a755f9c8740890a8c9    0.000000\n",
       "...           ...                                         ...         ...\n",
       "463631 2022-08-29  0xb7e50106a5bd3cf21af210a755f9c8740890a8c9   -0.001000\n",
       "463632 2022-08-29  0xb7e50106a5bd3cf21af210a755f9c8740890a8c9   -0.001000\n",
       "463633 2022-08-29  0xb7e50106a5bd3cf21af210a755f9c8740890a8c9   -0.001000\n",
       "463634 2022-08-29  0xb7e50106a5bd3cf21af210a755f9c8740890a8c9 -217.470321\n",
       "463635 2022-08-29  0xb7e50106a5bd3cf21af210a755f9c8740890a8c9   -4.518705\n",
       "\n",
       "[403692 rows x 3 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wallet_all_days_n_changes[wallet_all_days_n_changes['wallet_address']==str.lower('0xB7E50106A5bd3Cf21AF210A755F9C8740890A8c9')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "e1628d05-7cd3-44e5-ad85-5cf7967eefa7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 40768635 entries, 0 to 40768634\n",
      "Data columns (total 3 columns):\n",
      " #   Column          Dtype         \n",
      "---  ------          -----         \n",
      " 0   date            datetime64[ns]\n",
      " 1   wallet_address  object        \n",
      " 2   amount          float64       \n",
      "dtypes: datetime64[ns](1), float64(1), object(1)\n",
      "memory usage: 1.2+ GB\n"
     ]
    }
   ],
   "source": [
    "# wallet_all_days_n_changes['amount']=wallet_all_days_n_changes['amount']\n",
    "wallet_all_days_n_changes.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c14f3442-b719-4874-a9da-b299c70a0a56",
   "metadata": {},
   "source": [
    "This is too large if we have all wallet -- split into two dataframe to process and then join them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "0884414f-b0a8-4f12-a9df-4eaef35e805c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import math\n",
    "len_wallet_mid = round(len(wallets)/2)\n",
    "len_wallet = len(wallets)\n",
    "\n",
    "wallets1 = wallets[0:len_wallet_mid-1][0].to_list()\n",
    "wallets2 = wallets[len_wallet_mid: len_wallet-1][0].to_list()\n",
    "\n",
    "wallet_list = [wallets1, wallets2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "96765dc5-619e-4aa8-bb9c-4e31d50043a3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0xa4b1838cb086dddafa655f247716b502e87a0672 0x99fd94f0ab556a7424cfbc859632a2561261bc0f\n",
      "0xafd9a39b5ee3edba2dca3fc2ea2fd1cd94b784b9 0x515c27dd10d117783346c020bdfef6f3081b2cb3\n",
      "54811\n",
      "54810\n"
     ]
    }
   ],
   "source": [
    "print(wallets1[0], wallets1[-1])\n",
    "print(wallets2[0], wallets2[-1])\n",
    "\n",
    "print(len(pd.unique(wallets1)))\n",
    "print(len(pd.unique(wallets2)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "3f11b7c6-dede-43e0-818a-3fc0256ac06e",
   "metadata": {},
   "outputs": [],
   "source": [
    "wallet_bals = wallet_all_days_n_changes.sort_values('date', ascending=True)\n",
    "\n",
    "wallet_bals1 = wallet_bals[wallet_bals['wallet_address'].isin(wallets1)]\n",
    "wallet_bals2 = wallet_bals[wallet_bals['wallet_address'].isin(wallets2)]\n",
    "\n",
    "wallet_bals1_sum = wallet_bals1.groupby(by=['date', 'wallet_address'])['amount'].sum().reset_index()\n",
    "wallet_bals2_sum = wallet_bals2.groupby(by=['date', 'wallet_address'])['amount'].sum().reset_index()\n",
    "\n",
    "# wallet_bals = pd.concat([wallet_bals1, wallet_bals2], axis=0)\n",
    "    \n",
    "\n",
    "# wallet_bals['net_change'] = wallet_bals.groupby(by=['date','wallet_address'])['amount'].sum().reset_index()\n",
    "# wallet_all_days_n_changes['cumsum'] = wallet_all_days_n_changes.groupby(['date','wallet_address'])['net_change'].cumsum()\n",
    "# wallet_bals"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "8c0049f0-17c5-4b58-b25e-9307258cdc15",
   "metadata": {},
   "outputs": [],
   "source": [
    "wallet_bals1_sum['cumsum'] = wallet_bals1_sum.groupby(['wallet_address'])['amount'].cumsum()\n",
    "wallet_bals2_sum['cumsum'] = wallet_bals2_sum.groupby(['wallet_address'])['amount'].cumsum()\n",
    "wallet_bals = pd.concat([wallet_bals1_sum, wallet_bals2_sum], axis=0)\n",
    "wallet_bals = wallet_bals[wallet_bals['cumsum']>=5].sort_values(by=['date', 'cumsum'], ascending=[False, False])\n",
    "wallet_bals.to_csv(f'{date.today()}_balances_by_day.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "1edd9c67-0afd-4f8d-aa8e-da3a666e53c9",
   "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>date</th>\n",
       "      <th>wallet_address</th>\n",
       "      <th>amount</th>\n",
       "      <th>cumsum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>17852848</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xb7e50106a5bd3cf21af210a755f9c8740890a8c9</td>\n",
       "      <td>-1.039404e+05</td>\n",
       "      <td>1.004872e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17798037</th>\n",
       "      <td>2022-08-28</td>\n",
       "      <td>0xb7e50106a5bd3cf21af210a755f9c8740890a8c9</td>\n",
       "      <td>2.175094e+05</td>\n",
       "      <td>1.015266e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17743226</th>\n",
       "      <td>2022-08-27</td>\n",
       "      <td>0xb7e50106a5bd3cf21af210a755f9c8740890a8c9</td>\n",
       "      <td>-2.933816e+05</td>\n",
       "      <td>9.935153e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17688415</th>\n",
       "      <td>2022-08-26</td>\n",
       "      <td>0xb7e50106a5bd3cf21af210a755f9c8740890a8c9</td>\n",
       "      <td>5.872489e+05</td>\n",
       "      <td>1.022853e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17633604</th>\n",
       "      <td>2022-08-25</td>\n",
       "      <td>0xb7e50106a5bd3cf21af210a755f9c8740890a8c9</td>\n",
       "      <td>2.055487e+05</td>\n",
       "      <td>9.641285e+06</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>642194</th>\n",
       "      <td>2021-10-19</td>\n",
       "      <td>0xb7e50106a5bd3cf21af210a755f9c8740890a8c9</td>\n",
       "      <td>1.552013e+03</td>\n",
       "      <td>1.652349e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>587383</th>\n",
       "      <td>2021-10-18</td>\n",
       "      <td>0xb7e50106a5bd3cf21af210a755f9c8740890a8c9</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>1.003356e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>532572</th>\n",
       "      <td>2021-10-17</td>\n",
       "      <td>0xb7e50106a5bd3cf21af210a755f9c8740890a8c9</td>\n",
       "      <td>4.547474e-13</td>\n",
       "      <td>1.003356e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>477761</th>\n",
       "      <td>2021-10-16</td>\n",
       "      <td>0xb7e50106a5bd3cf21af210a755f9c8740890a8c9</td>\n",
       "      <td>5.033557e+01</td>\n",
       "      <td>1.003356e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>422950</th>\n",
       "      <td>2021-10-15</td>\n",
       "      <td>0xb7e50106a5bd3cf21af210a755f9c8740890a8c9</td>\n",
       "      <td>5.000000e+01</td>\n",
       "      <td>5.000000e+01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>319 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               date                              wallet_address        amount  \\\n",
       "17852848 2022-08-29  0xb7e50106a5bd3cf21af210a755f9c8740890a8c9 -1.039404e+05   \n",
       "17798037 2022-08-28  0xb7e50106a5bd3cf21af210a755f9c8740890a8c9  2.175094e+05   \n",
       "17743226 2022-08-27  0xb7e50106a5bd3cf21af210a755f9c8740890a8c9 -2.933816e+05   \n",
       "17688415 2022-08-26  0xb7e50106a5bd3cf21af210a755f9c8740890a8c9  5.872489e+05   \n",
       "17633604 2022-08-25  0xb7e50106a5bd3cf21af210a755f9c8740890a8c9  2.055487e+05   \n",
       "...             ...                                         ...           ...   \n",
       "642194   2021-10-19  0xb7e50106a5bd3cf21af210a755f9c8740890a8c9  1.552013e+03   \n",
       "587383   2021-10-18  0xb7e50106a5bd3cf21af210a755f9c8740890a8c9  0.000000e+00   \n",
       "532572   2021-10-17  0xb7e50106a5bd3cf21af210a755f9c8740890a8c9  4.547474e-13   \n",
       "477761   2021-10-16  0xb7e50106a5bd3cf21af210a755f9c8740890a8c9  5.033557e+01   \n",
       "422950   2021-10-15  0xb7e50106a5bd3cf21af210a755f9c8740890a8c9  5.000000e+01   \n",
       "\n",
       "                cumsum  \n",
       "17852848  1.004872e+07  \n",
       "17798037  1.015266e+07  \n",
       "17743226  9.935153e+06  \n",
       "17688415  1.022853e+07  \n",
       "17633604  9.641285e+06  \n",
       "...                ...  \n",
       "642194    1.652349e+03  \n",
       "587383    1.003356e+02  \n",
       "532572    1.003356e+02  \n",
       "477761    1.003356e+02  \n",
       "422950    5.000000e+01  \n",
       "\n",
       "[319 rows x 4 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# wallet_bals.head(35)\n",
    "wallet_bals.head(20)\n",
    "wallet_bals[wallet_bals['wallet_address'] == str.lower('0xB7E50106A5bd3Cf21AF210A755F9C8740890A8c9')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "0f498d66-6dce-4ee8-b64b-72f9927f345b",
   "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>date</th>\n",
       "      <th>wallet_address</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-10-08</td>\n",
       "      <td>0x000000000000000000000000000000000000dead</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-10-08</td>\n",
       "      <td>0x00000000000be4d9faacf7f669f25118b1736666</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-10-08</td>\n",
       "      <td>0x00000000009726632680fb29d3f7a9734e3010e2</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-10-08</td>\n",
       "      <td>0x0000000000dfa724d4232675d9fa6c3f4945dccc</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-10-08</td>\n",
       "      <td>0x00000000cc0b822819f03424dacf9077fdaa58a3</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2021-10-08</td>\n",
       "      <td>0x00000002f32c0886ee65d68059fbdb76ef6a6996</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2021-10-08</td>\n",
       "      <td>0x0000000b4d325bb539676dac6ec3413d5974cf0f</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2021-10-08</td>\n",
       "      <td>0x00000037d496b5ea3436e6a4d65026cf13021f19</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2021-10-08</td>\n",
       "      <td>0x00000064001a7df8875e24c804b119e72b72eb7c</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2021-10-08</td>\n",
       "      <td>0x0000014ffa35ed6ad0d5c9c079c908716c113eb8</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2021-10-08</td>\n",
       "      <td>0x000018a882fc00df5aebe9169ff03d6a3943145b</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2021-10-08</td>\n",
       "      <td>0x000026b2da088fc2c145e7e9d93d0284218a7777</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2021-10-08</td>\n",
       "      <td>0x00002888686a979808a0126f7f8a266f0e83c5df</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2021-10-08</td>\n",
       "      <td>0x0000374e9e25713e7303c63746f4863c7e73b91f</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2021-10-08</td>\n",
       "      <td>0x00006ac96cdb98e907ba8aa418f0aa0c7e46afe7</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2021-10-08</td>\n",
       "      <td>0x0000b28d481fe8a891f9a3ee4e931e5dd5e07b0b</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2021-10-08</td>\n",
       "      <td>0x0000b349b786767c70fe4f514e264b8582a7953b</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2021-10-08</td>\n",
       "      <td>0x0000be2a6fba748a5b684ec0c2f8b53971c5cf7b</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2021-10-08</td>\n",
       "      <td>0x0000ce08fa224696a819877070bf378e8b131acf</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2021-10-08</td>\n",
       "      <td>0x0001c55d6d0720770b9ecf5f5446bcd5c536b740</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2021-10-08</td>\n",
       "      <td>0x0001f898c4763a7a0444d91600f00c7eec586b90</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2021-10-08</td>\n",
       "      <td>0x000308534d7748d1168785c404f082527d235133</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2021-10-08</td>\n",
       "      <td>0x00041f83818286276bd5a7507088b7c4dff1c5a4</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2021-10-08</td>\n",
       "      <td>0x000446d54925698ae48172facf20fb55a19a492f</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2021-10-08</td>\n",
       "      <td>0x000566b53e028d21e104e4320de61c2314ef4064</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>2021-10-08</td>\n",
       "      <td>0x0006e488a82b3048906907b93544ca30075b0f53</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>2021-10-08</td>\n",
       "      <td>0x0008327c7fea53e850a259ca5d6868ac22c07545</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>2021-10-08</td>\n",
       "      <td>0x00084e078b6aa79aa5254b81bbbafd3720ffcbfc</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>2021-10-08</td>\n",
       "      <td>0x000870277ac75f5501d25d9b84d7fc1cee10d418</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>2021-10-08</td>\n",
       "      <td>0x000872be5ec7dc8197ef6618ef507d9a449cac1a</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>2021-10-08</td>\n",
       "      <td>0x0008906ca2e1d42dfb6bbcda7f9b709a0cfa8dfc</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>2021-10-08</td>\n",
       "      <td>0x0009182a50362255303439084444465bfb4f5a82</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>2021-10-08</td>\n",
       "      <td>0x000948bee22729c277b213e6840cfc2b992bad14</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>2021-10-08</td>\n",
       "      <td>0x000a303fdb64069f919f8708f23060e0b8668d44</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>2021-10-08</td>\n",
       "      <td>0x000a942c8e294ef24888bd00b01a7dfdc907f5b8</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date                              wallet_address  amount\n",
       "0  2021-10-08  0x000000000000000000000000000000000000dead     0.0\n",
       "1  2021-10-08  0x00000000000be4d9faacf7f669f25118b1736666     0.0\n",
       "2  2021-10-08  0x00000000009726632680fb29d3f7a9734e3010e2     0.0\n",
       "3  2021-10-08  0x0000000000dfa724d4232675d9fa6c3f4945dccc     0.0\n",
       "4  2021-10-08  0x00000000cc0b822819f03424dacf9077fdaa58a3     0.0\n",
       "5  2021-10-08  0x00000002f32c0886ee65d68059fbdb76ef6a6996     0.0\n",
       "6  2021-10-08  0x0000000b4d325bb539676dac6ec3413d5974cf0f     0.0\n",
       "7  2021-10-08  0x00000037d496b5ea3436e6a4d65026cf13021f19     0.0\n",
       "8  2021-10-08  0x00000064001a7df8875e24c804b119e72b72eb7c     0.0\n",
       "9  2021-10-08  0x0000014ffa35ed6ad0d5c9c079c908716c113eb8     0.0\n",
       "10 2021-10-08  0x000018a882fc00df5aebe9169ff03d6a3943145b     0.0\n",
       "11 2021-10-08  0x000026b2da088fc2c145e7e9d93d0284218a7777     0.0\n",
       "12 2021-10-08  0x00002888686a979808a0126f7f8a266f0e83c5df     0.0\n",
       "13 2021-10-08  0x0000374e9e25713e7303c63746f4863c7e73b91f     0.0\n",
       "14 2021-10-08  0x00006ac96cdb98e907ba8aa418f0aa0c7e46afe7     0.0\n",
       "15 2021-10-08  0x0000b28d481fe8a891f9a3ee4e931e5dd5e07b0b     0.0\n",
       "16 2021-10-08  0x0000b349b786767c70fe4f514e264b8582a7953b     0.0\n",
       "17 2021-10-08  0x0000be2a6fba748a5b684ec0c2f8b53971c5cf7b     0.0\n",
       "18 2021-10-08  0x0000ce08fa224696a819877070bf378e8b131acf     0.0\n",
       "19 2021-10-08  0x0001c55d6d0720770b9ecf5f5446bcd5c536b740     0.0\n",
       "20 2021-10-08  0x0001f898c4763a7a0444d91600f00c7eec586b90     0.0\n",
       "21 2021-10-08  0x000308534d7748d1168785c404f082527d235133     0.0\n",
       "22 2021-10-08  0x00041f83818286276bd5a7507088b7c4dff1c5a4     0.0\n",
       "23 2021-10-08  0x000446d54925698ae48172facf20fb55a19a492f     0.0\n",
       "24 2021-10-08  0x000566b53e028d21e104e4320de61c2314ef4064     0.0\n",
       "25 2021-10-08  0x0006e488a82b3048906907b93544ca30075b0f53     0.0\n",
       "26 2021-10-08  0x0008327c7fea53e850a259ca5d6868ac22c07545     0.0\n",
       "27 2021-10-08  0x00084e078b6aa79aa5254b81bbbafd3720ffcbfc     0.0\n",
       "28 2021-10-08  0x000870277ac75f5501d25d9b84d7fc1cee10d418     0.0\n",
       "29 2021-10-08  0x000872be5ec7dc8197ef6618ef507d9a449cac1a     0.0\n",
       "30 2021-10-08  0x0008906ca2e1d42dfb6bbcda7f9b709a0cfa8dfc     0.0\n",
       "31 2021-10-08  0x0009182a50362255303439084444465bfb4f5a82     0.0\n",
       "32 2021-10-08  0x000948bee22729c277b213e6840cfc2b992bad14     0.0\n",
       "33 2021-10-08  0x000a303fdb64069f919f8708f23060e0b8668d44     0.0\n",
       "34 2021-10-08  0x000a942c8e294ef24888bd00b01a7dfdc907f5b8     0.0"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wallet_bals2_sum.head(35)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d7dda71a-a870-4a3c-a0dd-7dfd45a7145d",
   "metadata": {},
   "outputs": [],
   "source": [
    "wallet_all_days_n_changes = wallet_all_days_n_changes[wallet_all_days_n_changes['cumsum'] > 10.0]\n",
    "wallet_all_days_n_changes.to_csv(f'{date.today()}_balances_by_day1.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "27c86a2b-ae38-4f5e-921c-bcb1ec99f6ae",
   "metadata": {},
   "outputs": [],
   "source": [
    "wallet_all_days_n_changes[wallet_all_days_n_changes['wallet_address']=='0xb7e50106a5bd3cf21af210a755f9c8740890a8c9']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "968ba3f8-a643-43ea-a910-f68acb4e8277",
   "metadata": {},
   "outputs": [],
   "source": [
    "wallet_all_days_n_changes_condensed = wallet_all_days_n_changes[['date', 'wallet_address', 'amount', 'cumsum']].copy()\n",
    "wallet_all_days_n_changes_condensed = wallet_all_days_n_changes_condensed[wallet_all_days_n_changes_condensed['cumsum'] > 10]\n",
    "# wallet_all_days_n_changes_condensed.to_csv(f'{date.today()}_balances_by_day.csv')\n",
    "wallet_all_days_n_changes_condensed.to_csv(f'{date.today()}_balances_by_day.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "de423e0a-0cc5-400d-be5a-7aef8fde6b7a",
   "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>date</th>\n",
       "      <th>wallet_address</th>\n",
       "      <th>amount</th>\n",
       "      <th>cumsum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-10-08</td>\n",
       "      <td>0xa4b1838cb086dddafa655f247716b502e87a0672</td>\n",
       "      <td>4.2</td>\n",
       "      <td>4.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date                              wallet_address  amount  cumsum\n",
       "0 2021-10-08  0xa4b1838cb086dddafa655f247716b502e87a0672     4.2     4.2"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wallet_all_days_n_changes_condensed[wallet_all_days_n_changes_condensed['wallet_address']=='0xa4b1838cb086dddafa655f247716b502e87a0672']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "b0863110-99e9-4a42-acba-a9a3be5ae057",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 8596572 entries, 328 to 12691925\n",
      "Data columns (total 4 columns):\n",
      " #   Column          Dtype         \n",
      "---  ------          -----         \n",
      " 0   date            datetime64[ns]\n",
      " 1   wallet_address  object        \n",
      " 2   amount          float64       \n",
      " 3   cumsum          float64       \n",
      "dtypes: datetime64[ns](1), float64(2), object(1)\n",
      "memory usage: 327.9+ MB\n"
     ]
    }
   ],
   "source": [
    "wallet_all_days_n_changes_condensed.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "d74d2c69-02e0-4ced-87ce-72b3e8ee2c02",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date\n",
       "2021-10-08        1\n",
       "2021-10-09        1\n",
       "2021-10-10        3\n",
       "2021-10-11        5\n",
       "2021-10-12        7\n",
       "              ...  \n",
       "2022-08-25    57010\n",
       "2022-08-26    53665\n",
       "2022-08-27    52653\n",
       "2022-08-28    58438\n",
       "2022-08-29    47910\n",
       "Name: wallet_address, Length: 326, dtype: int64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "holders_per_day = wallet_all_days_n_changes_condensed.groupby('date')['wallet_address'].count()\n",
    "holders_per_day"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "b2ca72cc-a1a4-4532-9afe-b87dbc926695",
   "metadata": {},
   "outputs": [],
   "source": [
    "# wallet_bal_n_net_change=wallet_all_days_n_changes[(wallet_all_days_n_changes['wallet_address'] != '0x0000000000000000000000000000000000000000') & (wallet_all_days_n_changes['cumsum'] > 1)]\n",
    "# wallet_bal_n_net_change=wallet_bal_n_net_change[['date', 'wallet_address', 'amount']].groupby(['date', 'wallet_address'])['amount'].sum().reset_index()\n",
    "# wallet_bal_n_net_change['cumsum']=wallet_bal_n_net_change.groupby(['date','wallet_address'])['amount'].cumsum()\n",
    "# # wallet_bal_n_net_change\n",
    "# wallet_bal_n_net_change"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "e400d34d-d9e2-4074-8a2c-9e816373dcb9",
   "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>date</th>\n",
       "      <th>wallet_address</th>\n",
       "      <th>amount</th>\n",
       "      <th>cumsum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7379681</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xffc21269d65cc460978c7b9e3474c7d831ee3cbb</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379682</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xffc57ab280cb8d1e47e11ce225f113612cff3d8e</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379683</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xffc5d1b80aea3860d9dd80dd9ab3794f5f701432</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379684</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xffc8c5b5c1c50acd7df8484585761d0111fb4123</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379685</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xffce05977d9bbb31a2afeb10980f7e35247d1121</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379686</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xffcede1ec864f7f3811756a2989bfd5c7203e547</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379687</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xffcef28629bcb2d00218d2abac2f3ddcc6f423e5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379688</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xffd2df7ba9a04672933748dc86ce71f131ff4d50</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379689</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xffd89043d922750133c229848bb7b5b35f8e751a</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379690</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xffd92191808fcf707b5b203ddae891aee95f8be6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379691</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xffdd540346b3a999255f7a3cc040f430e5b99ca8</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379692</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xffde835eea127d07fdeb47f5b125ee80e07280f3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379693</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xffdf3ecbcb89a19870e251dce10f417908a65fb1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379694</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xffe0252a1ad68afc8e7b036ee1747cc3596248f2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379695</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xffe04c48af66f0d7a6b3e07f17e71e834040aa7f</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379696</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xffe11091221459fa81c7a368a00b8d2cd2c3cfbd</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379697</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xffe26302b49ab7df4ec05e70a01de31bb32ef09f</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379698</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xffe3d114e4439adec76b6617129a5ac5d51bf15e</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379699</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xffe3e5147929b8b9b129489463381dce6626eb05</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379700</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xffe4261a55f4d5ae916d1130ce4d9132f9adb262</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379701</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xffe62622808840fb235c29a09f11c89993bbe892</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379702</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xffe953af50e8e54bbfa03ae59fa63149c99113a9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379703</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xffec26c4ff03bbaf5119b797595f7e73062cbd98</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379704</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xffeccc607ccc708f4ed224e00c9f3bd33acc8d38</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379705</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xffece792c5cade47b45d9d7eba0794ccb315954d</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379706</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xffef5c967796c3fe447b0a6764c60e889d547c8f</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379707</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xfff0366e874d45559e4deb0cc5d1d8103eda667d</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379708</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xfff384ad1ca11a9990f448ecb5af1808d13387a4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379709</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xfff39ecb826261def51785b0ce6f24cb6792ee73</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379710</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xfff4da66c6561325d94beafd04c0957f3bfffcf7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379711</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xfff5b39356da493bdaf3602f48b20112708d7369</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379712</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xfff6b41a29881fc97bd6e8778529b1e37597d94a</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379713</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xfff6cca12d16d2788ce2dbdfe40b7b4ff87a68b3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379714</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xfff813069cb117d1111d477e7d232858975752e7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379715</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xfffb264bf1275e5ccb5444366a782eb89045515c</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379716</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xfffba8d8dd8733e3e1dfa406a642db013a72046c</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379717</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xfffd377f3be1b4cfe568276f631d899e5c27f00f</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379718</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xfffd846060550629a4f00635c608711b1dc98ef3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379719</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xfffe939c904abd3c7c687f5875b052d4efd93e5b</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379720</th>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>0xffff30cccea52ee7e0936a620df3e7be5b546ebb</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              date                              wallet_address  amount  cumsum\n",
       "7379681 2022-08-29  0xffc21269d65cc460978c7b9e3474c7d831ee3cbb     0.0     0.0\n",
       "7379682 2022-08-29  0xffc57ab280cb8d1e47e11ce225f113612cff3d8e     0.0     0.0\n",
       "7379683 2022-08-29  0xffc5d1b80aea3860d9dd80dd9ab3794f5f701432     0.0     0.0\n",
       "7379684 2022-08-29  0xffc8c5b5c1c50acd7df8484585761d0111fb4123     0.0     0.0\n",
       "7379685 2022-08-29  0xffce05977d9bbb31a2afeb10980f7e35247d1121     0.0     0.0\n",
       "7379686 2022-08-29  0xffcede1ec864f7f3811756a2989bfd5c7203e547     0.0     0.0\n",
       "7379687 2022-08-29  0xffcef28629bcb2d00218d2abac2f3ddcc6f423e5     0.0     0.0\n",
       "7379688 2022-08-29  0xffd2df7ba9a04672933748dc86ce71f131ff4d50     0.0     0.0\n",
       "7379689 2022-08-29  0xffd89043d922750133c229848bb7b5b35f8e751a     0.0     0.0\n",
       "7379690 2022-08-29  0xffd92191808fcf707b5b203ddae891aee95f8be6     0.0     0.0\n",
       "7379691 2022-08-29  0xffdd540346b3a999255f7a3cc040f430e5b99ca8     0.0     0.0\n",
       "7379692 2022-08-29  0xffde835eea127d07fdeb47f5b125ee80e07280f3     0.0     0.0\n",
       "7379693 2022-08-29  0xffdf3ecbcb89a19870e251dce10f417908a65fb1     0.0     0.0\n",
       "7379694 2022-08-29  0xffe0252a1ad68afc8e7b036ee1747cc3596248f2     0.0     0.0\n",
       "7379695 2022-08-29  0xffe04c48af66f0d7a6b3e07f17e71e834040aa7f     0.0     0.0\n",
       "7379696 2022-08-29  0xffe11091221459fa81c7a368a00b8d2cd2c3cfbd     0.0     0.0\n",
       "7379697 2022-08-29  0xffe26302b49ab7df4ec05e70a01de31bb32ef09f     0.0     0.0\n",
       "7379698 2022-08-29  0xffe3d114e4439adec76b6617129a5ac5d51bf15e     0.0     0.0\n",
       "7379699 2022-08-29  0xffe3e5147929b8b9b129489463381dce6626eb05     0.0     0.0\n",
       "7379700 2022-08-29  0xffe4261a55f4d5ae916d1130ce4d9132f9adb262     0.0     0.0\n",
       "7379701 2022-08-29  0xffe62622808840fb235c29a09f11c89993bbe892     0.0     0.0\n",
       "7379702 2022-08-29  0xffe953af50e8e54bbfa03ae59fa63149c99113a9     0.0     0.0\n",
       "7379703 2022-08-29  0xffec26c4ff03bbaf5119b797595f7e73062cbd98     0.0     0.0\n",
       "7379704 2022-08-29  0xffeccc607ccc708f4ed224e00c9f3bd33acc8d38     0.0     0.0\n",
       "7379705 2022-08-29  0xffece792c5cade47b45d9d7eba0794ccb315954d     0.0     0.0\n",
       "7379706 2022-08-29  0xffef5c967796c3fe447b0a6764c60e889d547c8f     0.0     0.0\n",
       "7379707 2022-08-29  0xfff0366e874d45559e4deb0cc5d1d8103eda667d     0.0     0.0\n",
       "7379708 2022-08-29  0xfff384ad1ca11a9990f448ecb5af1808d13387a4     0.0     0.0\n",
       "7379709 2022-08-29  0xfff39ecb826261def51785b0ce6f24cb6792ee73     0.0     0.0\n",
       "7379710 2022-08-29  0xfff4da66c6561325d94beafd04c0957f3bfffcf7     0.0     0.0\n",
       "7379711 2022-08-29  0xfff5b39356da493bdaf3602f48b20112708d7369     0.0     0.0\n",
       "7379712 2022-08-29  0xfff6b41a29881fc97bd6e8778529b1e37597d94a     0.0     0.0\n",
       "7379713 2022-08-29  0xfff6cca12d16d2788ce2dbdfe40b7b4ff87a68b3     0.0     0.0\n",
       "7379714 2022-08-29  0xfff813069cb117d1111d477e7d232858975752e7     0.0     0.0\n",
       "7379715 2022-08-29  0xfffb264bf1275e5ccb5444366a782eb89045515c     0.0     0.0\n",
       "7379716 2022-08-29  0xfffba8d8dd8733e3e1dfa406a642db013a72046c     0.0     0.0\n",
       "7379717 2022-08-29  0xfffd377f3be1b4cfe568276f631d899e5c27f00f     0.0     0.0\n",
       "7379718 2022-08-29  0xfffd846060550629a4f00635c608711b1dc98ef3     0.0     0.0\n",
       "7379719 2022-08-29  0xfffe939c904abd3c7c687f5875b052d4efd93e5b     0.0     0.0\n",
       "7379720 2022-08-29  0xffff30cccea52ee7e0936a620df3e7be5b546ebb     0.0     0.0"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# wallet_bal_n_net_change1=wallet_bal_n_net_change[['date', 'wallet_address', 'amount']].groupby(['date', 'wallet_address'])['amount'].sum().reset_index()\n",
    "\n",
    "# wallet_bal_n_net_change.tail(40)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "d14866a3-6ed0-49c1-9365-5e142edb13d7",
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'wallet_bal_n_net_change1' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "Input \u001b[0;32mIn [23]\u001b[0m, in \u001b[0;36m<cell line: 1>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0m wallet_bal_n_net_change1[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mcumsum\u001b[39m\u001b[38;5;124m'\u001b[39m]\u001b[38;5;241m=\u001b[39m\u001b[43mwallet_bal_n_net_change1\u001b[49m\u001b[38;5;241m.\u001b[39mgroupby(\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mwallet_address\u001b[39m\u001b[38;5;124m'\u001b[39m)[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mamount\u001b[39m\u001b[38;5;124m'\u001b[39m]\u001b[38;5;241m.\u001b[39mcumsum()\n\u001b[1;32m      2\u001b[0m wallet_bal_n_net_change1\n",
      "\u001b[0;31mNameError\u001b[0m: name 'wallet_bal_n_net_change1' is not defined"
     ]
    }
   ],
   "source": [
    "# wallet_bal_n_net_change1['cumsum']=wallet_bal_n_net_change1.groupby('wallet_address')['amount'].cumsum()\n",
    "# wallet_bal_n_net_change1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "12688e8c-0bfe-4886-804d-e83ed3807367",
   "metadata": {},
   "outputs": [],
   "source": [
    "wallet_bal_n_net_change"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1908a08b-226a-4685-b9bb-583a75cdfb15",
   "metadata": {},
   "outputs": [],
   "source": [
    "mints_burns = wallet_all_days_n_changes[wallet_all_days_n_changes['wallet_address'] == '0x0000000000000000000000000000000000000000'].copy()\n",
    "# mints_burns['amount'] = mints_burns['amount'].apply(lambda x: abs(x))\n",
    "# mints_burns_grp = mints_burns.groupby('date')[['amount', 'cumsum']].sum()\n",
    "mints_burns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6a174236-869f-4323-86aa-4e194ed94eed",
   "metadata": {},
   "outputs": [],
   "source": [
    "mints_burns_grp=mints_burns[['date', 'amount']].copy()\n",
    "mints_burns_grp = mints_burns_grp.groupby('date').agg({'amount':'sum'})\n",
    "mints_burns_grp['cumsum'] = mints_burns_grp['amount'].cumsum()\n",
    "mints_burns_grp\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d6efcd01-aafa-4970-8b20-aee098cdc7bb",
   "metadata": {},
   "outputs": [],
   "source": [
    "mints_burns_grp.to_csv(f'{date.today()}_supply_over_time.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "29105de6-ff5c-4c82-8d82-38ccf4a2ba98",
   "metadata": {},
   "outputs": [],
   "source": [
    "total_supply = abs(mints_burns_grp['cumsum'].min())\n",
    "total_supply"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "389dea44-f172-44d6-b0ac-43acdea5f87d",
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.plot(mints_burns_grp.index, mints_burns_grp['cumsum'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8ada8ff4-33bf-44a4-b96a-6a812f4260c3",
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.plot(holders_per_day)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c07b81d6-6732-477e-af42-90ad800440c0",
   "metadata": {},
   "outputs": [],
   "source": [
    "today = date.today()\n",
    "print(today)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d3d46572-79d0-4ddb-a3cb-43984a7f6f9c",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "1ea423b1-f13a-43b4-bc2a-92dd493cb6e9",
   "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>date</th>\n",
       "      <th>cumsum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-01-25</td>\n",
       "      <td>142.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-01-26</td>\n",
       "      <td>3285.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-01-27</td>\n",
       "      <td>3637.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-01-28</td>\n",
       "      <td>3771.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-01-29</td>\n",
       "      <td>3851.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>206</th>\n",
       "      <td>2022-08-19</td>\n",
       "      <td>36430.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>207</th>\n",
       "      <td>2022-08-20</td>\n",
       "      <td>36432.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>208</th>\n",
       "      <td>2022-08-21</td>\n",
       "      <td>36444.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>209</th>\n",
       "      <td>2022-08-22</td>\n",
       "      <td>36467.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>210</th>\n",
       "      <td>2022-08-23</td>\n",
       "      <td>36469.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>211 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           date   cumsum\n",
       "0    2022-01-25    142.0\n",
       "1    2022-01-26   3285.0\n",
       "2    2022-01-27   3637.0\n",
       "3    2022-01-28   3771.0\n",
       "4    2022-01-29   3851.0\n",
       "..          ...      ...\n",
       "206  2022-08-19  36430.0\n",
       "207  2022-08-20  36432.0\n",
       "208  2022-08-21  36444.0\n",
       "209  2022-08-22  36467.0\n",
       "210  2022-08-23  36469.0\n",
       "\n",
       "[211 rows x 2 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df =pd.read_csv('/home/stubbs/Documents/skycatcher/TreasureDAO/legion_holders_by_day.csv')\n",
    "df=df[df['address']!='0x0000000000000000000000000000000000000000']\n",
    "df = df[['date', 'cumsum']]\n",
    "df = df.groupby('date')['cumsum'].sum().reset_index()\n",
    "df"
   ]
  }
 ],
 "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.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}