{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "import gzip\n",
    "import pickle\n",
    "import re\n",
    "import copy\n",
    "from tqdm import tqdm\n",
    "import time\n",
    "import json\n",
    "from datetime import datetime, timedelta"
   ]
  },
  {
   "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>date</th>\n",
       "      <th>hashrate</th>\n",
       "      <th>coins_per_block</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2009-01-02</td>\n",
       "      <td>4.970000e+04</td>\n",
       "      <td>50.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2009-01-05</td>\n",
       "      <td>4.970000e+04</td>\n",
       "      <td>50.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2009-01-08</td>\n",
       "      <td>6.960000e+05</td>\n",
       "      <td>50.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2009-01-11</td>\n",
       "      <td>4.420000e+06</td>\n",
       "      <td>50.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2009-01-14</td>\n",
       "      <td>6.310000e+06</td>\n",
       "      <td>50.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1638</th>\n",
       "      <td>2022-06-18</td>\n",
       "      <td>2.350000e+20</td>\n",
       "      <td>6.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1639</th>\n",
       "      <td>2022-06-21</td>\n",
       "      <td>2.260000e+20</td>\n",
       "      <td>6.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1640</th>\n",
       "      <td>2022-06-24</td>\n",
       "      <td>1.900000e+20</td>\n",
       "      <td>6.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1641</th>\n",
       "      <td>2022-06-27</td>\n",
       "      <td>2.000000e+20</td>\n",
       "      <td>6.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1642</th>\n",
       "      <td>2022-06-30</td>\n",
       "      <td>2.510000e+20</td>\n",
       "      <td>6.25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1643 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           date      hashrate  coins_per_block\n",
       "0    2009-01-02  4.970000e+04            50.00\n",
       "1    2009-01-05  4.970000e+04            50.00\n",
       "2    2009-01-08  6.960000e+05            50.00\n",
       "3    2009-01-11  4.420000e+06            50.00\n",
       "4    2009-01-14  6.310000e+06            50.00\n",
       "...         ...           ...              ...\n",
       "1638 2022-06-18  2.350000e+20             6.25\n",
       "1639 2022-06-21  2.260000e+20             6.25\n",
       "1640 2022-06-24  1.900000e+20             6.25\n",
       "1641 2022-06-27  2.000000e+20             6.25\n",
       "1642 2022-06-30  2.510000e+20             6.25\n",
       "\n",
       "[1643 rows x 3 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Timestamp Graph,Intervals (Row),Network hashrate (TH/s),Bitcoins per block (BTC)\n",
    "# 02/01/2009,1,4.97E-08,50\n",
    "BitcoinData = pd.read_csv('BitcoinData_orig.csv') \n",
    "\n",
    "df2 = pd.DataFrame(columns=['date', 'hashrate','coins_per_block'])\n",
    "\n",
    "df2['date'] = pd.to_datetime(BitcoinData['Timestamp Graph'], format='%d/%m/%Y')\n",
    "df2['hashrate'] = BitcoinData['Network hashrate (TH/s)']*1e12\n",
    "df2['coins_per_block'] = BitcoinData['Bitcoins per block (BTC)']\n",
    "\n",
    "df2 = df2.dropna()\n",
    "df2 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "hashrate = json.load(open('hashrate.json'))\n",
    "\n",
    "# x\ty\n",
    "# 1231545600000\t1.065220e-07\n",
    "BitcoinData2 = pd.DataFrame(hashrate[\"hash-rate\"])\n",
    "BitcoinData2[\"date\"] = pd.to_datetime(BitcoinData2[\"x\"], unit='ms')\n",
    "BitcoinData2[\"hashrate\"] = BitcoinData2[\"y\"]*1e12\n",
    "BitcoinData2 = BitcoinData2.drop(columns=[\"x\", \"y\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>hashrate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2009-01-10</td>\n",
       "      <td>1.065220e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2009-01-14</td>\n",
       "      <td>2.521021e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2009-01-18</td>\n",
       "      <td>5.588855e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2009-01-22</td>\n",
       "      <td>5.681174e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2009-01-26</td>\n",
       "      <td>6.270595e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1384</th>\n",
       "      <td>2024-03-08</td>\n",
       "      <td>5.928970e+20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1385</th>\n",
       "      <td>2024-03-12</td>\n",
       "      <td>6.294408e+20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1386</th>\n",
       "      <td>2024-03-16</td>\n",
       "      <td>5.963760e+20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1387</th>\n",
       "      <td>2024-03-20</td>\n",
       "      <td>6.010169e+20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1388</th>\n",
       "      <td>2024-03-24</td>\n",
       "      <td>6.003264e+20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1389 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           date      hashrate\n",
       "0    2009-01-10  1.065220e+05\n",
       "1    2009-01-14  2.521021e+06\n",
       "2    2009-01-18  5.588855e+06\n",
       "3    2009-01-22  5.681174e+06\n",
       "4    2009-01-26  6.270595e+06\n",
       "...         ...           ...\n",
       "1384 2024-03-08  5.928970e+20\n",
       "1385 2024-03-12  6.294408e+20\n",
       "1386 2024-03-16  5.963760e+20\n",
       "1387 2024-03-20  6.010169e+20\n",
       "1388 2024-03-24  6.003264e+20\n",
       "\n",
       "[1389 rows x 2 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "BitcoinData2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>hashrate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1230</th>\n",
       "      <td>2022-07-01</td>\n",
       "      <td>2.146114e+20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1231</th>\n",
       "      <td>2022-07-05</td>\n",
       "      <td>2.179713e+20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1232</th>\n",
       "      <td>2022-07-09</td>\n",
       "      <td>2.061507e+20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1233</th>\n",
       "      <td>2022-07-13</td>\n",
       "      <td>2.059578e+20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1234</th>\n",
       "      <td>2022-07-17</td>\n",
       "      <td>1.968833e+20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1384</th>\n",
       "      <td>2024-03-08</td>\n",
       "      <td>5.928970e+20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1385</th>\n",
       "      <td>2024-03-12</td>\n",
       "      <td>6.294408e+20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1386</th>\n",
       "      <td>2024-03-16</td>\n",
       "      <td>5.963760e+20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1387</th>\n",
       "      <td>2024-03-20</td>\n",
       "      <td>6.010169e+20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1388</th>\n",
       "      <td>2024-03-24</td>\n",
       "      <td>6.003264e+20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>159 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           date      hashrate\n",
       "1230 2022-07-01  2.146114e+20\n",
       "1231 2022-07-05  2.179713e+20\n",
       "1232 2022-07-09  2.061507e+20\n",
       "1233 2022-07-13  2.059578e+20\n",
       "1234 2022-07-17  1.968833e+20\n",
       "...         ...           ...\n",
       "1384 2024-03-08  5.928970e+20\n",
       "1385 2024-03-12  6.294408e+20\n",
       "1386 2024-03-16  5.963760e+20\n",
       "1387 2024-03-20  6.010169e+20\n",
       "1388 2024-03-24  6.003264e+20\n",
       "\n",
       "[159 rows x 2 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp = BitcoinData2[BitcoinData2[\"date\"] > df2[\"date\"].max()]\n",
    "temp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df2 is not defined for values above 2022\n",
    "df2 = pd.concat([df2, temp])\n",
    "\n",
    "# if block is nan, it should be 6.25\n",
    "df2['coins_per_block'] = df2['coins_per_block'].fillna(6.25)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>hashrate</th>\n",
       "      <th>coins_per_block</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2009-01-02</td>\n",
       "      <td>4.970000e+04</td>\n",
       "      <td>50.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2009-01-05</td>\n",
       "      <td>4.970000e+04</td>\n",
       "      <td>50.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2009-01-08</td>\n",
       "      <td>6.960000e+05</td>\n",
       "      <td>50.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2009-01-11</td>\n",
       "      <td>4.420000e+06</td>\n",
       "      <td>50.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2009-01-14</td>\n",
       "      <td>6.310000e+06</td>\n",
       "      <td>50.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1384</th>\n",
       "      <td>2024-03-08</td>\n",
       "      <td>5.928970e+20</td>\n",
       "      <td>6.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1385</th>\n",
       "      <td>2024-03-12</td>\n",
       "      <td>6.294408e+20</td>\n",
       "      <td>6.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1386</th>\n",
       "      <td>2024-03-16</td>\n",
       "      <td>5.963760e+20</td>\n",
       "      <td>6.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1387</th>\n",
       "      <td>2024-03-20</td>\n",
       "      <td>6.010169e+20</td>\n",
       "      <td>6.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1388</th>\n",
       "      <td>2024-03-24</td>\n",
       "      <td>6.003264e+20</td>\n",
       "      <td>6.25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1802 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           date      hashrate  coins_per_block\n",
       "0    2009-01-02  4.970000e+04            50.00\n",
       "1    2009-01-05  4.970000e+04            50.00\n",
       "2    2009-01-08  6.960000e+05            50.00\n",
       "3    2009-01-11  4.420000e+06            50.00\n",
       "4    2009-01-14  6.310000e+06            50.00\n",
       "...         ...           ...              ...\n",
       "1384 2024-03-08  5.928970e+20             6.25\n",
       "1385 2024-03-12  6.294408e+20             6.25\n",
       "1386 2024-03-16  5.963760e+20             6.25\n",
       "1387 2024-03-20  6.010169e+20             6.25\n",
       "1388 2024-03-24  6.003264e+20             6.25\n",
       "\n",
       "[1802 rows x 3 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>hashrate</th>\n",
       "      <th>coins_per_block</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4.318540e+06</td>\n",
       "      <td>50.00</td>\n",
       "      <td>2009-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6.009000e+06</td>\n",
       "      <td>50.00</td>\n",
       "      <td>2009-02-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5.623000e+06</td>\n",
       "      <td>50.00</td>\n",
       "      <td>2009-03-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5.712000e+06</td>\n",
       "      <td>50.00</td>\n",
       "      <td>2009-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5.710000e+06</td>\n",
       "      <td>50.00</td>\n",
       "      <td>2009-05-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>178</th>\n",
       "      <td>4.722977e+20</td>\n",
       "      <td>6.25</td>\n",
       "      <td>2023-11-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179</th>\n",
       "      <td>5.008642e+20</td>\n",
       "      <td>6.25</td>\n",
       "      <td>2023-12-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>180</th>\n",
       "      <td>5.166878e+20</td>\n",
       "      <td>6.25</td>\n",
       "      <td>2024-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>181</th>\n",
       "      <td>5.686831e+20</td>\n",
       "      <td>6.25</td>\n",
       "      <td>2024-02-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>182</th>\n",
       "      <td>5.969255e+20</td>\n",
       "      <td>6.25</td>\n",
       "      <td>2024-03-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>183 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         hashrate  coins_per_block       date\n",
       "0    4.318540e+06            50.00 2009-01-01\n",
       "1    6.009000e+06            50.00 2009-02-01\n",
       "2    5.623000e+06            50.00 2009-03-01\n",
       "3    5.712000e+06            50.00 2009-04-01\n",
       "4    5.710000e+06            50.00 2009-05-01\n",
       "..            ...              ...        ...\n",
       "178  4.722977e+20             6.25 2023-11-01\n",
       "179  5.008642e+20             6.25 2023-12-01\n",
       "180  5.166878e+20             6.25 2024-01-01\n",
       "181  5.686831e+20             6.25 2024-02-01\n",
       "182  5.969255e+20             6.25 2024-03-01\n",
       "\n",
       "[183 rows x 3 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# for each month in df2, get the monthly average and store it with the date being the first day of the month\n",
    "\n",
    "# Create a 'month' column\n",
    "from datetime import timedelta\n",
    "df2['month'] = (df2['date']).dt.to_period('M')\n",
    "\n",
    "# Group by 'month' and calculate the mean\n",
    "df2 = df2.groupby('month')[[\"hashrate\",\"coins_per_block\"]].mean().reset_index()\n",
    "\n",
    "# Convert 'month' back to datetime (first day of the month)\n",
    "df2['date'] = df2['month'].dt.to_timestamp()\n",
    "df2 = df2.drop(columns=[\"month\"])\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2.to_csv('BitcoinData.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "py310",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}