02_transform_flow_monthly.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Load all the blocks\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "processed data/blocks_100000.csv\n",
      "processed data/blocks_300000.csv\n",
      "processed data/blocks_400000.csv\n",
      "processed data/blocks_500000.csv\n",
      "processed data/blocks_200000.csv\n",
      "processed data/blocks_629123.csv\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>height</th>\n",
       "      <th>subsidy</th>\n",
       "      <th>hash</th>\n",
       "      <th>strippedsize</th>\n",
       "      <th>size</th>\n",
       "      <th>weight</th>\n",
       "      <th>version</th>\n",
       "      <th>versionHex</th>\n",
       "      <th>merkleroot</th>\n",
       "      <th>time</th>\n",
       "      <th>mediantime</th>\n",
       "      <th>nonce</th>\n",
       "      <th>bits</th>\n",
       "      <th>difficulty</th>\n",
       "      <th>chainwork</th>\n",
       "      <th>nTx</th>\n",
       "      <th>previousblockhash</th>\n",
       "      <th>nextblockhash</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>50</td>\n",
       "      <td>000000006a625f06636b8bb6ac7b960a8d03705d1ace08...</td>\n",
       "      <td>215</td>\n",
       "      <td>215</td>\n",
       "      <td>860</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>9b0fc92260312ce44e74ef369f5c66bbb85848f2eddd5a...</td>\n",
       "      <td>1231469744</td>\n",
       "      <td>1231469665</td>\n",
       "      <td>1639830024</td>\n",
       "      <td>1d00ffff</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>0000000000000000000000000000000000000000000000...</td>\n",
       "      <td>1</td>\n",
       "      <td>00000000839a8e6886ab5951d76f411475428afc90947e...</td>\n",
       "      <td>0000000082b5015589a3fdf2d4baff403e6f0be035a5d9...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>50</td>\n",
       "      <td>0000000082b5015589a3fdf2d4baff403e6f0be035a5d9...</td>\n",
       "      <td>215</td>\n",
       "      <td>215</td>\n",
       "      <td>860</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>999e1c837c76a1b7fbb7e57baf87b309960f5ffefbf2a9...</td>\n",
       "      <td>1231470173</td>\n",
       "      <td>1231469744</td>\n",
       "      <td>1844305925</td>\n",
       "      <td>1d00ffff</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>0000000000000000000000000000000000000000000000...</td>\n",
       "      <td>1</td>\n",
       "      <td>000000006a625f06636b8bb6ac7b960a8d03705d1ace08...</td>\n",
       "      <td>000000004ebadb55ee9096c9a2f8880e09da59c0d68b1c...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>50</td>\n",
       "      <td>000000004ebadb55ee9096c9a2f8880e09da59c0d68b1c...</td>\n",
       "      <td>215</td>\n",
       "      <td>215</td>\n",
       "      <td>860</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>df2b060fa2e5e9c8ed5eaf6a45c13753ec8c63282b2688...</td>\n",
       "      <td>1231470988</td>\n",
       "      <td>1231469744</td>\n",
       "      <td>2850094635</td>\n",
       "      <td>1d00ffff</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>0000000000000000000000000000000000000000000000...</td>\n",
       "      <td>1</td>\n",
       "      <td>0000000082b5015589a3fdf2d4baff403e6f0be035a5d9...</td>\n",
       "      <td>000000009b7262315dbf071787ad3656097b892abffd1f...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>50</td>\n",
       "      <td>000000009b7262315dbf071787ad3656097b892abffd1f...</td>\n",
       "      <td>215</td>\n",
       "      <td>215</td>\n",
       "      <td>860</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>63522845d294ee9b0188ae5cac91bf389a0c3723f084ca...</td>\n",
       "      <td>1231471428</td>\n",
       "      <td>1231470173</td>\n",
       "      <td>2011431709</td>\n",
       "      <td>1d00ffff</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>0000000000000000000000000000000000000000000000...</td>\n",
       "      <td>1</td>\n",
       "      <td>000000004ebadb55ee9096c9a2f8880e09da59c0d68b1c...</td>\n",
       "      <td>000000003031a0e73735690c5a1ff2a4be82553b2a12b7...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6</td>\n",
       "      <td>50</td>\n",
       "      <td>000000003031a0e73735690c5a1ff2a4be82553b2a12b7...</td>\n",
       "      <td>215</td>\n",
       "      <td>215</td>\n",
       "      <td>860</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>20251a76e64e920e58291a30d4b212939aae976baca40e...</td>\n",
       "      <td>1231471789</td>\n",
       "      <td>1231470173</td>\n",
       "      <td>2538380312</td>\n",
       "      <td>1d00ffff</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>0000000000000000000000000000000000000000000000...</td>\n",
       "      <td>1</td>\n",
       "      <td>000000009b7262315dbf071787ad3656097b892abffd1f...</td>\n",
       "      <td>0000000071966c2b1d065fd446b1e485b2c9d9594acd20...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29116</th>\n",
       "      <td>629118</td>\n",
       "      <td>12.5</td>\n",
       "      <td>00000000000000000008ead3927d78e0893e20671c16fb...</td>\n",
       "      <td>812122</td>\n",
       "      <td>1562302</td>\n",
       "      <td>3998668</td>\n",
       "      <td>671080448</td>\n",
       "      <td>27ffe000</td>\n",
       "      <td>a78de8dc14f9a761b48cd42f05ca60950611811440d657...</td>\n",
       "      <td>1588721776</td>\n",
       "      <td>1588718081</td>\n",
       "      <td>3493232314</td>\n",
       "      <td>17117a39</td>\n",
       "      <td>1.610481e+13</td>\n",
       "      <td>00000000000000000000000000000000000000000f2f8c...</td>\n",
       "      <td>1743</td>\n",
       "      <td>00000000000000000001a0e29027aea9889839994d1446...</td>\n",
       "      <td>0000000000000000000d96cb2d59122b47d07123b1d6c5...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29117</th>\n",
       "      <td>629119</td>\n",
       "      <td>12.5</td>\n",
       "      <td>0000000000000000000d96cb2d59122b47d07123b1d6c5...</td>\n",
       "      <td>979494</td>\n",
       "      <td>1054881</td>\n",
       "      <td>3993363</td>\n",
       "      <td>536870912</td>\n",
       "      <td>20000000</td>\n",
       "      <td>adbbf3753f370ddeedeca89bafb589436d5f8139eee11c...</td>\n",
       "      <td>1588721978</td>\n",
       "      <td>1588720690</td>\n",
       "      <td>435201505</td>\n",
       "      <td>17117a39</td>\n",
       "      <td>1.610481e+13</td>\n",
       "      <td>00000000000000000000000000000000000000000f2f9a...</td>\n",
       "      <td>819</td>\n",
       "      <td>00000000000000000008ead3927d78e0893e20671c16fb...</td>\n",
       "      <td>0000000000000000000a011ad2d7272173f907730868da...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29118</th>\n",
       "      <td>629120</td>\n",
       "      <td>12.5</td>\n",
       "      <td>0000000000000000000a011ad2d7272173f907730868da...</td>\n",
       "      <td>959786</td>\n",
       "      <td>1113700</td>\n",
       "      <td>3993058</td>\n",
       "      <td>1073733632</td>\n",
       "      <td>3fffe000</td>\n",
       "      <td>666d44623fedac790150d3b61ff57b7fc8b31093c4cec6...</td>\n",
       "      <td>1588722370</td>\n",
       "      <td>1588721021</td>\n",
       "      <td>3630098624</td>\n",
       "      <td>17117a39</td>\n",
       "      <td>1.610481e+13</td>\n",
       "      <td>00000000000000000000000000000000000000000f2fa9...</td>\n",
       "      <td>1251</td>\n",
       "      <td>0000000000000000000d96cb2d59122b47d07123b1d6c5...</td>\n",
       "      <td>0000000000000000000735f5c486360d252540700774e7...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29119</th>\n",
       "      <td>629121</td>\n",
       "      <td>12.5</td>\n",
       "      <td>0000000000000000000735f5c486360d252540700774e7...</td>\n",
       "      <td>933831</td>\n",
       "      <td>1197207</td>\n",
       "      <td>3998700</td>\n",
       "      <td>1073733632</td>\n",
       "      <td>3fffe000</td>\n",
       "      <td>594fe615c989ada118e087a15086b52ba96bb36eac3099...</td>\n",
       "      <td>1588722868</td>\n",
       "      <td>1588721063</td>\n",
       "      <td>25328223</td>\n",
       "      <td>17117a39</td>\n",
       "      <td>1.610481e+13</td>\n",
       "      <td>00000000000000000000000000000000000000000f2fb8...</td>\n",
       "      <td>1532</td>\n",
       "      <td>0000000000000000000a011ad2d7272173f907730868da...</td>\n",
       "      <td>000000000000000000114eeef80cfe9083d2d471b11330...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29120</th>\n",
       "      <td>629122</td>\n",
       "      <td>12.5</td>\n",
       "      <td>000000000000000000114eeef80cfe9083d2d471b11330...</td>\n",
       "      <td>989585</td>\n",
       "      <td>1024605</td>\n",
       "      <td>3993360</td>\n",
       "      <td>536870912</td>\n",
       "      <td>20000000</td>\n",
       "      <td>42719c9a5fcec537ea4ea7094093748575b7c9395b9b82...</td>\n",
       "      <td>1588722918</td>\n",
       "      <td>1588721233</td>\n",
       "      <td>1399841657</td>\n",
       "      <td>17117a39</td>\n",
       "      <td>1.610481e+13</td>\n",
       "      <td>00000000000000000000000000000000000000000f2fc6...</td>\n",
       "      <td>301</td>\n",
       "      <td>0000000000000000000735f5c486360d252540700774e7...</td>\n",
       "      <td>0000000000000000000111cb38c5a2b76b25c49ee8a94b...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>529111 rows × 18 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       height subsidy                                               hash  \\\n",
       "0           2      50  000000006a625f06636b8bb6ac7b960a8d03705d1ace08...   \n",
       "1           3      50  0000000082b5015589a3fdf2d4baff403e6f0be035a5d9...   \n",
       "2           4      50  000000004ebadb55ee9096c9a2f8880e09da59c0d68b1c...   \n",
       "3           5      50  000000009b7262315dbf071787ad3656097b892abffd1f...   \n",
       "4           6      50  000000003031a0e73735690c5a1ff2a4be82553b2a12b7...   \n",
       "...       ...     ...                                                ...   \n",
       "29116  629118    12.5  00000000000000000008ead3927d78e0893e20671c16fb...   \n",
       "29117  629119    12.5  0000000000000000000d96cb2d59122b47d07123b1d6c5...   \n",
       "29118  629120    12.5  0000000000000000000a011ad2d7272173f907730868da...   \n",
       "29119  629121    12.5  0000000000000000000735f5c486360d252540700774e7...   \n",
       "29120  629122    12.5  000000000000000000114eeef80cfe9083d2d471b11330...   \n",
       "\n",
       "      strippedsize     size   weight     version versionHex  \\\n",
       "0              215      215      860           1          1   \n",
       "1              215      215      860           1          1   \n",
       "2              215      215      860           1          1   \n",
       "3              215      215      860           1          1   \n",
       "4              215      215      860           1          1   \n",
       "...            ...      ...      ...         ...        ...   \n",
       "29116       812122  1562302  3998668   671080448   27ffe000   \n",
       "29117       979494  1054881  3993363   536870912   20000000   \n",
       "29118       959786  1113700  3993058  1073733632   3fffe000   \n",
       "29119       933831  1197207  3998700  1073733632   3fffe000   \n",
       "29120       989585  1024605  3993360   536870912   20000000   \n",
       "\n",
       "                                              merkleroot        time  \\\n",
       "0      9b0fc92260312ce44e74ef369f5c66bbb85848f2eddd5a...  1231469744   \n",
       "1      999e1c837c76a1b7fbb7e57baf87b309960f5ffefbf2a9...  1231470173   \n",
       "2      df2b060fa2e5e9c8ed5eaf6a45c13753ec8c63282b2688...  1231470988   \n",
       "3      63522845d294ee9b0188ae5cac91bf389a0c3723f084ca...  1231471428   \n",
       "4      20251a76e64e920e58291a30d4b212939aae976baca40e...  1231471789   \n",
       "...                                                  ...         ...   \n",
       "29116  a78de8dc14f9a761b48cd42f05ca60950611811440d657...  1588721776   \n",
       "29117  adbbf3753f370ddeedeca89bafb589436d5f8139eee11c...  1588721978   \n",
       "29118  666d44623fedac790150d3b61ff57b7fc8b31093c4cec6...  1588722370   \n",
       "29119  594fe615c989ada118e087a15086b52ba96bb36eac3099...  1588722868   \n",
       "29120  42719c9a5fcec537ea4ea7094093748575b7c9395b9b82...  1588722918   \n",
       "\n",
       "       mediantime       nonce      bits    difficulty  \\\n",
       "0      1231469665  1639830024  1d00ffff  1.000000e+00   \n",
       "1      1231469744  1844305925  1d00ffff  1.000000e+00   \n",
       "2      1231469744  2850094635  1d00ffff  1.000000e+00   \n",
       "3      1231470173  2011431709  1d00ffff  1.000000e+00   \n",
       "4      1231470173  2538380312  1d00ffff  1.000000e+00   \n",
       "...           ...         ...       ...           ...   \n",
       "29116  1588718081  3493232314  17117a39  1.610481e+13   \n",
       "29117  1588720690   435201505  17117a39  1.610481e+13   \n",
       "29118  1588721021  3630098624  17117a39  1.610481e+13   \n",
       "29119  1588721063    25328223  17117a39  1.610481e+13   \n",
       "29120  1588721233  1399841657  17117a39  1.610481e+13   \n",
       "\n",
       "                                               chainwork   nTx  \\\n",
       "0      0000000000000000000000000000000000000000000000...     1   \n",
       "1      0000000000000000000000000000000000000000000000...     1   \n",
       "2      0000000000000000000000000000000000000000000000...     1   \n",
       "3      0000000000000000000000000000000000000000000000...     1   \n",
       "4      0000000000000000000000000000000000000000000000...     1   \n",
       "...                                                  ...   ...   \n",
       "29116  00000000000000000000000000000000000000000f2f8c...  1743   \n",
       "29117  00000000000000000000000000000000000000000f2f9a...   819   \n",
       "29118  00000000000000000000000000000000000000000f2fa9...  1251   \n",
       "29119  00000000000000000000000000000000000000000f2fb8...  1532   \n",
       "29120  00000000000000000000000000000000000000000f2fc6...   301   \n",
       "\n",
       "                                       previousblockhash  \\\n",
       "0      00000000839a8e6886ab5951d76f411475428afc90947e...   \n",
       "1      000000006a625f06636b8bb6ac7b960a8d03705d1ace08...   \n",
       "2      0000000082b5015589a3fdf2d4baff403e6f0be035a5d9...   \n",
       "3      000000004ebadb55ee9096c9a2f8880e09da59c0d68b1c...   \n",
       "4      000000009b7262315dbf071787ad3656097b892abffd1f...   \n",
       "...                                                  ...   \n",
       "29116  00000000000000000001a0e29027aea9889839994d1446...   \n",
       "29117  00000000000000000008ead3927d78e0893e20671c16fb...   \n",
       "29118  0000000000000000000d96cb2d59122b47d07123b1d6c5...   \n",
       "29119  0000000000000000000a011ad2d7272173f907730868da...   \n",
       "29120  0000000000000000000735f5c486360d252540700774e7...   \n",
       "\n",
       "                                           nextblockhash  \n",
       "0      0000000082b5015589a3fdf2d4baff403e6f0be035a5d9...  \n",
       "1      000000004ebadb55ee9096c9a2f8880e09da59c0d68b1c...  \n",
       "2      000000009b7262315dbf071787ad3656097b892abffd1f...  \n",
       "3      000000003031a0e73735690c5a1ff2a4be82553b2a12b7...  \n",
       "4      0000000071966c2b1d065fd446b1e485b2c9d9594acd20...  \n",
       "...                                                  ...  \n",
       "29116  0000000000000000000d96cb2d59122b47d07123b1d6c5...  \n",
       "29117  0000000000000000000a011ad2d7272173f907730868da...  \n",
       "29118  0000000000000000000735f5c486360d252540700774e7...  \n",
       "29119  000000000000000000114eeef80cfe9083d2d471b11330...  \n",
       "29120  0000000000000000000111cb38c5a2b76b25c49ee8a94b...  \n",
       "\n",
       "[529111 rows x 18 columns]"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import glob\n",
    "import pandas as pd\n",
    "\n",
    "header = ['height','subsidy','hash','strippedsize','size','weight','version','versionHex','merkleroot','time','mediantime','nonce','bits','difficulty','chainwork','nTx','previousblockhash','nextblockhash']\n",
    "blockdata = pd.DataFrame(columns = header)\n",
    "blockdata.set_index(\"height\")\n",
    "for file in glob.glob('data/blocks_*.csv'):\n",
    "    blockdata_chunk = pd.read_csv(file)\n",
    "    blockdata_chunk.columns = header\n",
    "    blockdata_chunk.set_index(\"height\")\n",
    "    # effectively appending the newly loaded Dataframe to the big one\n",
    "    blockdata = pd.concat([blockdata, blockdata_chunk])\n",
    "    print(\"processed {}\".format(file))\n",
    "blockdata\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>height</th>\n",
       "      <th>subsidy</th>\n",
       "      <th>hash</th>\n",
       "      <th>strippedsize</th>\n",
       "      <th>size</th>\n",
       "      <th>weight</th>\n",
       "      <th>version</th>\n",
       "      <th>versionHex</th>\n",
       "      <th>merkleroot</th>\n",
       "      <th>time</th>\n",
       "      <th>mediantime</th>\n",
       "      <th>nonce</th>\n",
       "      <th>bits</th>\n",
       "      <th>difficulty</th>\n",
       "      <th>chainwork</th>\n",
       "      <th>nTx</th>\n",
       "      <th>previousblockhash</th>\n",
       "      <th>nextblockhash</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2009-01-09 02:55:44</th>\n",
       "      <td>2</td>\n",
       "      <td>50</td>\n",
       "      <td>000000006a625f06636b8bb6ac7b960a8d03705d1ace08...</td>\n",
       "      <td>215</td>\n",
       "      <td>215</td>\n",
       "      <td>860</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>9b0fc92260312ce44e74ef369f5c66bbb85848f2eddd5a...</td>\n",
       "      <td>1231469744</td>\n",
       "      <td>1231469665</td>\n",
       "      <td>1639830024</td>\n",
       "      <td>1d00ffff</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>0000000000000000000000000000000000000000000000...</td>\n",
       "      <td>1</td>\n",
       "      <td>00000000839a8e6886ab5951d76f411475428afc90947e...</td>\n",
       "      <td>0000000082b5015589a3fdf2d4baff403e6f0be035a5d9...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-01-09 03:02:53</th>\n",
       "      <td>3</td>\n",
       "      <td>50</td>\n",
       "      <td>0000000082b5015589a3fdf2d4baff403e6f0be035a5d9...</td>\n",
       "      <td>215</td>\n",
       "      <td>215</td>\n",
       "      <td>860</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>999e1c837c76a1b7fbb7e57baf87b309960f5ffefbf2a9...</td>\n",
       "      <td>1231470173</td>\n",
       "      <td>1231469744</td>\n",
       "      <td>1844305925</td>\n",
       "      <td>1d00ffff</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>0000000000000000000000000000000000000000000000...</td>\n",
       "      <td>1</td>\n",
       "      <td>000000006a625f06636b8bb6ac7b960a8d03705d1ace08...</td>\n",
       "      <td>000000004ebadb55ee9096c9a2f8880e09da59c0d68b1c...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-01-09 03:16:28</th>\n",
       "      <td>4</td>\n",
       "      <td>50</td>\n",
       "      <td>000000004ebadb55ee9096c9a2f8880e09da59c0d68b1c...</td>\n",
       "      <td>215</td>\n",
       "      <td>215</td>\n",
       "      <td>860</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>df2b060fa2e5e9c8ed5eaf6a45c13753ec8c63282b2688...</td>\n",
       "      <td>1231470988</td>\n",
       "      <td>1231469744</td>\n",
       "      <td>2850094635</td>\n",
       "      <td>1d00ffff</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>0000000000000000000000000000000000000000000000...</td>\n",
       "      <td>1</td>\n",
       "      <td>0000000082b5015589a3fdf2d4baff403e6f0be035a5d9...</td>\n",
       "      <td>000000009b7262315dbf071787ad3656097b892abffd1f...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-01-09 03:23:48</th>\n",
       "      <td>5</td>\n",
       "      <td>50</td>\n",
       "      <td>000000009b7262315dbf071787ad3656097b892abffd1f...</td>\n",
       "      <td>215</td>\n",
       "      <td>215</td>\n",
       "      <td>860</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>63522845d294ee9b0188ae5cac91bf389a0c3723f084ca...</td>\n",
       "      <td>1231471428</td>\n",
       "      <td>1231470173</td>\n",
       "      <td>2011431709</td>\n",
       "      <td>1d00ffff</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>0000000000000000000000000000000000000000000000...</td>\n",
       "      <td>1</td>\n",
       "      <td>000000004ebadb55ee9096c9a2f8880e09da59c0d68b1c...</td>\n",
       "      <td>000000003031a0e73735690c5a1ff2a4be82553b2a12b7...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-01-09 03:29:49</th>\n",
       "      <td>6</td>\n",
       "      <td>50</td>\n",
       "      <td>000000003031a0e73735690c5a1ff2a4be82553b2a12b7...</td>\n",
       "      <td>215</td>\n",
       "      <td>215</td>\n",
       "      <td>860</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>20251a76e64e920e58291a30d4b212939aae976baca40e...</td>\n",
       "      <td>1231471789</td>\n",
       "      <td>1231470173</td>\n",
       "      <td>2538380312</td>\n",
       "      <td>1d00ffff</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>0000000000000000000000000000000000000000000000...</td>\n",
       "      <td>1</td>\n",
       "      <td>000000009b7262315dbf071787ad3656097b892abffd1f...</td>\n",
       "      <td>0000000071966c2b1d065fd446b1e485b2c9d9594acd20...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-05-05 23:36:16</th>\n",
       "      <td>629118</td>\n",
       "      <td>12.5</td>\n",
       "      <td>00000000000000000008ead3927d78e0893e20671c16fb...</td>\n",
       "      <td>812122</td>\n",
       "      <td>1562302</td>\n",
       "      <td>3998668</td>\n",
       "      <td>671080448</td>\n",
       "      <td>27ffe000</td>\n",
       "      <td>a78de8dc14f9a761b48cd42f05ca60950611811440d657...</td>\n",
       "      <td>1588721776</td>\n",
       "      <td>1588718081</td>\n",
       "      <td>3493232314</td>\n",
       "      <td>17117a39</td>\n",
       "      <td>1.610481e+13</td>\n",
       "      <td>00000000000000000000000000000000000000000f2f8c...</td>\n",
       "      <td>1743</td>\n",
       "      <td>00000000000000000001a0e29027aea9889839994d1446...</td>\n",
       "      <td>0000000000000000000d96cb2d59122b47d07123b1d6c5...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-05-05 23:39:38</th>\n",
       "      <td>629119</td>\n",
       "      <td>12.5</td>\n",
       "      <td>0000000000000000000d96cb2d59122b47d07123b1d6c5...</td>\n",
       "      <td>979494</td>\n",
       "      <td>1054881</td>\n",
       "      <td>3993363</td>\n",
       "      <td>536870912</td>\n",
       "      <td>20000000</td>\n",
       "      <td>adbbf3753f370ddeedeca89bafb589436d5f8139eee11c...</td>\n",
       "      <td>1588721978</td>\n",
       "      <td>1588720690</td>\n",
       "      <td>435201505</td>\n",
       "      <td>17117a39</td>\n",
       "      <td>1.610481e+13</td>\n",
       "      <td>00000000000000000000000000000000000000000f2f9a...</td>\n",
       "      <td>819</td>\n",
       "      <td>00000000000000000008ead3927d78e0893e20671c16fb...</td>\n",
       "      <td>0000000000000000000a011ad2d7272173f907730868da...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-05-05 23:46:10</th>\n",
       "      <td>629120</td>\n",
       "      <td>12.5</td>\n",
       "      <td>0000000000000000000a011ad2d7272173f907730868da...</td>\n",
       "      <td>959786</td>\n",
       "      <td>1113700</td>\n",
       "      <td>3993058</td>\n",
       "      <td>1073733632</td>\n",
       "      <td>3fffe000</td>\n",
       "      <td>666d44623fedac790150d3b61ff57b7fc8b31093c4cec6...</td>\n",
       "      <td>1588722370</td>\n",
       "      <td>1588721021</td>\n",
       "      <td>3630098624</td>\n",
       "      <td>17117a39</td>\n",
       "      <td>1.610481e+13</td>\n",
       "      <td>00000000000000000000000000000000000000000f2fa9...</td>\n",
       "      <td>1251</td>\n",
       "      <td>0000000000000000000d96cb2d59122b47d07123b1d6c5...</td>\n",
       "      <td>0000000000000000000735f5c486360d252540700774e7...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-05-05 23:54:28</th>\n",
       "      <td>629121</td>\n",
       "      <td>12.5</td>\n",
       "      <td>0000000000000000000735f5c486360d252540700774e7...</td>\n",
       "      <td>933831</td>\n",
       "      <td>1197207</td>\n",
       "      <td>3998700</td>\n",
       "      <td>1073733632</td>\n",
       "      <td>3fffe000</td>\n",
       "      <td>594fe615c989ada118e087a15086b52ba96bb36eac3099...</td>\n",
       "      <td>1588722868</td>\n",
       "      <td>1588721063</td>\n",
       "      <td>25328223</td>\n",
       "      <td>17117a39</td>\n",
       "      <td>1.610481e+13</td>\n",
       "      <td>00000000000000000000000000000000000000000f2fb8...</td>\n",
       "      <td>1532</td>\n",
       "      <td>0000000000000000000a011ad2d7272173f907730868da...</td>\n",
       "      <td>000000000000000000114eeef80cfe9083d2d471b11330...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-05-05 23:55:18</th>\n",
       "      <td>629122</td>\n",
       "      <td>12.5</td>\n",
       "      <td>000000000000000000114eeef80cfe9083d2d471b11330...</td>\n",
       "      <td>989585</td>\n",
       "      <td>1024605</td>\n",
       "      <td>3993360</td>\n",
       "      <td>536870912</td>\n",
       "      <td>20000000</td>\n",
       "      <td>42719c9a5fcec537ea4ea7094093748575b7c9395b9b82...</td>\n",
       "      <td>1588722918</td>\n",
       "      <td>1588721233</td>\n",
       "      <td>1399841657</td>\n",
       "      <td>17117a39</td>\n",
       "      <td>1.610481e+13</td>\n",
       "      <td>00000000000000000000000000000000000000000f2fc6...</td>\n",
       "      <td>301</td>\n",
       "      <td>0000000000000000000735f5c486360d252540700774e7...</td>\n",
       "      <td>0000000000000000000111cb38c5a2b76b25c49ee8a94b...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>529111 rows × 18 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     height subsidy  \\\n",
       "date                                  \n",
       "2009-01-09 02:55:44       2      50   \n",
       "2009-01-09 03:02:53       3      50   \n",
       "2009-01-09 03:16:28       4      50   \n",
       "2009-01-09 03:23:48       5      50   \n",
       "2009-01-09 03:29:49       6      50   \n",
       "...                     ...     ...   \n",
       "2020-05-05 23:36:16  629118    12.5   \n",
       "2020-05-05 23:39:38  629119    12.5   \n",
       "2020-05-05 23:46:10  629120    12.5   \n",
       "2020-05-05 23:54:28  629121    12.5   \n",
       "2020-05-05 23:55:18  629122    12.5   \n",
       "\n",
       "                                                                  hash  \\\n",
       "date                                                                     \n",
       "2009-01-09 02:55:44  000000006a625f06636b8bb6ac7b960a8d03705d1ace08...   \n",
       "2009-01-09 03:02:53  0000000082b5015589a3fdf2d4baff403e6f0be035a5d9...   \n",
       "2009-01-09 03:16:28  000000004ebadb55ee9096c9a2f8880e09da59c0d68b1c...   \n",
       "2009-01-09 03:23:48  000000009b7262315dbf071787ad3656097b892abffd1f...   \n",
       "2009-01-09 03:29:49  000000003031a0e73735690c5a1ff2a4be82553b2a12b7...   \n",
       "...                                                                ...   \n",
       "2020-05-05 23:36:16  00000000000000000008ead3927d78e0893e20671c16fb...   \n",
       "2020-05-05 23:39:38  0000000000000000000d96cb2d59122b47d07123b1d6c5...   \n",
       "2020-05-05 23:46:10  0000000000000000000a011ad2d7272173f907730868da...   \n",
       "2020-05-05 23:54:28  0000000000000000000735f5c486360d252540700774e7...   \n",
       "2020-05-05 23:55:18  000000000000000000114eeef80cfe9083d2d471b11330...   \n",
       "\n",
       "                    strippedsize     size   weight     version versionHex  \\\n",
       "date                                                                        \n",
       "2009-01-09 02:55:44          215      215      860           1          1   \n",
       "2009-01-09 03:02:53          215      215      860           1          1   \n",
       "2009-01-09 03:16:28          215      215      860           1          1   \n",
       "2009-01-09 03:23:48          215      215      860           1          1   \n",
       "2009-01-09 03:29:49          215      215      860           1          1   \n",
       "...                          ...      ...      ...         ...        ...   \n",
       "2020-05-05 23:36:16       812122  1562302  3998668   671080448   27ffe000   \n",
       "2020-05-05 23:39:38       979494  1054881  3993363   536870912   20000000   \n",
       "2020-05-05 23:46:10       959786  1113700  3993058  1073733632   3fffe000   \n",
       "2020-05-05 23:54:28       933831  1197207  3998700  1073733632   3fffe000   \n",
       "2020-05-05 23:55:18       989585  1024605  3993360   536870912   20000000   \n",
       "\n",
       "                                                            merkleroot  \\\n",
       "date                                                                     \n",
       "2009-01-09 02:55:44  9b0fc92260312ce44e74ef369f5c66bbb85848f2eddd5a...   \n",
       "2009-01-09 03:02:53  999e1c837c76a1b7fbb7e57baf87b309960f5ffefbf2a9...   \n",
       "2009-01-09 03:16:28  df2b060fa2e5e9c8ed5eaf6a45c13753ec8c63282b2688...   \n",
       "2009-01-09 03:23:48  63522845d294ee9b0188ae5cac91bf389a0c3723f084ca...   \n",
       "2009-01-09 03:29:49  20251a76e64e920e58291a30d4b212939aae976baca40e...   \n",
       "...                                                                ...   \n",
       "2020-05-05 23:36:16  a78de8dc14f9a761b48cd42f05ca60950611811440d657...   \n",
       "2020-05-05 23:39:38  adbbf3753f370ddeedeca89bafb589436d5f8139eee11c...   \n",
       "2020-05-05 23:46:10  666d44623fedac790150d3b61ff57b7fc8b31093c4cec6...   \n",
       "2020-05-05 23:54:28  594fe615c989ada118e087a15086b52ba96bb36eac3099...   \n",
       "2020-05-05 23:55:18  42719c9a5fcec537ea4ea7094093748575b7c9395b9b82...   \n",
       "\n",
       "                           time  mediantime       nonce      bits  \\\n",
       "date                                                                \n",
       "2009-01-09 02:55:44  1231469744  1231469665  1639830024  1d00ffff   \n",
       "2009-01-09 03:02:53  1231470173  1231469744  1844305925  1d00ffff   \n",
       "2009-01-09 03:16:28  1231470988  1231469744  2850094635  1d00ffff   \n",
       "2009-01-09 03:23:48  1231471428  1231470173  2011431709  1d00ffff   \n",
       "2009-01-09 03:29:49  1231471789  1231470173  2538380312  1d00ffff   \n",
       "...                         ...         ...         ...       ...   \n",
       "2020-05-05 23:36:16  1588721776  1588718081  3493232314  17117a39   \n",
       "2020-05-05 23:39:38  1588721978  1588720690   435201505  17117a39   \n",
       "2020-05-05 23:46:10  1588722370  1588721021  3630098624  17117a39   \n",
       "2020-05-05 23:54:28  1588722868  1588721063    25328223  17117a39   \n",
       "2020-05-05 23:55:18  1588722918  1588721233  1399841657  17117a39   \n",
       "\n",
       "                       difficulty  \\\n",
       "date                                \n",
       "2009-01-09 02:55:44  1.000000e+00   \n",
       "2009-01-09 03:02:53  1.000000e+00   \n",
       "2009-01-09 03:16:28  1.000000e+00   \n",
       "2009-01-09 03:23:48  1.000000e+00   \n",
       "2009-01-09 03:29:49  1.000000e+00   \n",
       "...                           ...   \n",
       "2020-05-05 23:36:16  1.610481e+13   \n",
       "2020-05-05 23:39:38  1.610481e+13   \n",
       "2020-05-05 23:46:10  1.610481e+13   \n",
       "2020-05-05 23:54:28  1.610481e+13   \n",
       "2020-05-05 23:55:18  1.610481e+13   \n",
       "\n",
       "                                                             chainwork   nTx  \\\n",
       "date                                                                           \n",
       "2009-01-09 02:55:44  0000000000000000000000000000000000000000000000...     1   \n",
       "2009-01-09 03:02:53  0000000000000000000000000000000000000000000000...     1   \n",
       "2009-01-09 03:16:28  0000000000000000000000000000000000000000000000...     1   \n",
       "2009-01-09 03:23:48  0000000000000000000000000000000000000000000000...     1   \n",
       "2009-01-09 03:29:49  0000000000000000000000000000000000000000000000...     1   \n",
       "...                                                                ...   ...   \n",
       "2020-05-05 23:36:16  00000000000000000000000000000000000000000f2f8c...  1743   \n",
       "2020-05-05 23:39:38  00000000000000000000000000000000000000000f2f9a...   819   \n",
       "2020-05-05 23:46:10  00000000000000000000000000000000000000000f2fa9...  1251   \n",
       "2020-05-05 23:54:28  00000000000000000000000000000000000000000f2fb8...  1532   \n",
       "2020-05-05 23:55:18  00000000000000000000000000000000000000000f2fc6...   301   \n",
       "\n",
       "                                                     previousblockhash  \\\n",
       "date                                                                     \n",
       "2009-01-09 02:55:44  00000000839a8e6886ab5951d76f411475428afc90947e...   \n",
       "2009-01-09 03:02:53  000000006a625f06636b8bb6ac7b960a8d03705d1ace08...   \n",
       "2009-01-09 03:16:28  0000000082b5015589a3fdf2d4baff403e6f0be035a5d9...   \n",
       "2009-01-09 03:23:48  000000004ebadb55ee9096c9a2f8880e09da59c0d68b1c...   \n",
       "2009-01-09 03:29:49  000000009b7262315dbf071787ad3656097b892abffd1f...   \n",
       "...                                                                ...   \n",
       "2020-05-05 23:36:16  00000000000000000001a0e29027aea9889839994d1446...   \n",
       "2020-05-05 23:39:38  00000000000000000008ead3927d78e0893e20671c16fb...   \n",
       "2020-05-05 23:46:10  0000000000000000000d96cb2d59122b47d07123b1d6c5...   \n",
       "2020-05-05 23:54:28  0000000000000000000a011ad2d7272173f907730868da...   \n",
       "2020-05-05 23:55:18  0000000000000000000735f5c486360d252540700774e7...   \n",
       "\n",
       "                                                         nextblockhash  \n",
       "date                                                                    \n",
       "2009-01-09 02:55:44  0000000082b5015589a3fdf2d4baff403e6f0be035a5d9...  \n",
       "2009-01-09 03:02:53  000000004ebadb55ee9096c9a2f8880e09da59c0d68b1c...  \n",
       "2009-01-09 03:16:28  000000009b7262315dbf071787ad3656097b892abffd1f...  \n",
       "2009-01-09 03:23:48  000000003031a0e73735690c5a1ff2a4be82553b2a12b7...  \n",
       "2009-01-09 03:29:49  0000000071966c2b1d065fd446b1e485b2c9d9594acd20...  \n",
       "...                                                                ...  \n",
       "2020-05-05 23:36:16  0000000000000000000d96cb2d59122b47d07123b1d6c5...  \n",
       "2020-05-05 23:39:38  0000000000000000000a011ad2d7272173f907730868da...  \n",
       "2020-05-05 23:46:10  0000000000000000000735f5c486360d252540700774e7...  \n",
       "2020-05-05 23:54:28  000000000000000000114eeef80cfe9083d2d471b11330...  \n",
       "2020-05-05 23:55:18  0000000000000000000111cb38c5a2b76b25c49ee8a94b...  \n",
       "\n",
       "[529111 rows x 18 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "blockdata[\"date\"]= pd.to_datetime(blockdata.time,unit='s')\n",
    "blockdata = blockdata.set_index(\"date\")\n",
    "blockdata"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>flow</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2009-01-31</th>\n",
       "      <td>127100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-02-28</th>\n",
       "      <td>169000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-03-31</th>\n",
       "      <td>173300.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-04-30</th>\n",
       "      <td>172100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-05-31</th>\n",
       "      <td>169150.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-31</th>\n",
       "      <td>59212.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-29</th>\n",
       "      <td>51925.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-03-31</th>\n",
       "      <td>53187.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-04-30</th>\n",
       "      <td>56412.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-05-31</th>\n",
       "      <td>9662.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>137 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                flow\n",
       "date                \n",
       "2009-01-31  127100.0\n",
       "2009-02-28  169000.0\n",
       "2009-03-31  173300.0\n",
       "2009-04-30  172100.0\n",
       "2009-05-31  169150.0\n",
       "...              ...\n",
       "2020-01-31   59212.5\n",
       "2020-02-29   51925.0\n",
       "2020-03-31   53187.5\n",
       "2020-04-30   56412.5\n",
       "2020-05-31    9662.5\n",
       "\n",
       "[137 rows x 1 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "monthly_flow = pd.DataFrame()\n",
    "monthly_flow['flow'] = blockdata.subsidy.resample(\"M\").sum()\n",
    "monthly_flow\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "monthly_flow.to_csv('./data/flow_monthly.csv')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.8.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}