{ "cells": [ { "cell_type": "code", "execution_count": 47, "metadata": {}, "outputs": [], "source": [ "import pandas as pd\n", "import numpy as np\n", "import matplotlib.pyplot as plt\n", "from datetime import timedelta" ] }, { "cell_type": "code", "execution_count": 48, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "'\\nCould you make a Google drive with a giant excel sheet with many tabs. Here are tabs needed (I added columns name in \" \", some are quite long):\\n\\n•\\t\"Hardware mentions\" (which should have 14,530 rows right?): \\no\\tColumn 1: \"Date mentioned\"\\no\\tColumn 2: \"Link to the thread\" (if you have, otherwise no need of this column\\no\\tColumn 3: \"Hardware name\" (put the after-mapping name for consistency)\\no\\tColumn 3: \"Power efficiency (TH/J)\"\\no\\tColumn 4: \"Release date\" (so that\\'s not the date it was mentioned, but the date the hardware company released it)\\n'" ] }, "execution_count": 48, "metadata": {}, "output_type": "execute_result" } ], "source": [ "\"\"\"\n", "Could you make a Google drive with a giant excel sheet with many tabs. Here are tabs needed (I added columns name in \" \", some are quite long):\n", "\n", "•\t\"Hardware mentions\" (which should have 14,530 rows right?): \n", "o\tColumn 1: \"Date mentioned\"\n", "o\tColumn 2: \"Link to the thread\" (if you have, otherwise no need of this column\n", "o\tColumn 3: \"Hardware name\" (put the after-mapping name for consistency)\n", "o\tColumn 3: \"Power efficiency (TH/J)\"\n", "o\tColumn 4: \"Release date\" (so that's not the date it was mentioned, but the date the hardware company released it)\n", "\"\"\"" ] }, { "cell_type": "code", "execution_count": 49, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Date mentionedHardware namePower efficiency (TH/J)Release date
02010-09-09 12:59:39gtx4604.270000e-07Jul 2010
12010-10-06 20:25:1743503.460000e-07Jan 2009
22010-10-06 20:25:1757701.940100e-06Oct 2009
32010-10-06 20:25:1758701.906000e-06Sep 2009
42010-10-06 20:25:17gtx2602.100000e-07Dec 2009
...............
145302023-12-17 03:45:25microbt whatsminer m30s2.631579e-02Apr 2020
145312023-12-17 03:45:25microbt whatsminer m50s3.846100e-02Jul 2022
145322023-12-17 03:45:25microbt whatsminer m50s3.846100e-02Jul 2022
145332023-12-17 03:45:25microbt whatsminer m50s3.846100e-02Jul 2022
145342023-12-17 03:45:25microbt whatsminer m50s3.846100e-02Jul 2022
\n", "

14535 rows × 4 columns

\n", "
" ], "text/plain": [ " Date mentioned Hardware name Power efficiency (TH/J) \\\n", "0 2010-09-09 12:59:39 gtx460 4.270000e-07 \n", "1 2010-10-06 20:25:17 4350 3.460000e-07 \n", "2 2010-10-06 20:25:17 5770 1.940100e-06 \n", "3 2010-10-06 20:25:17 5870 1.906000e-06 \n", "4 2010-10-06 20:25:17 gtx260 2.100000e-07 \n", "... ... ... ... \n", "14530 2023-12-17 03:45:25 microbt whatsminer m30s 2.631579e-02 \n", "14531 2023-12-17 03:45:25 microbt whatsminer m50s 3.846100e-02 \n", "14532 2023-12-17 03:45:25 microbt whatsminer m50s 3.846100e-02 \n", "14533 2023-12-17 03:45:25 microbt whatsminer m50s 3.846100e-02 \n", "14534 2023-12-17 03:45:25 microbt whatsminer m50s 3.846100e-02 \n", "\n", " Release date \n", "0 Jul 2010 \n", "1 Jan 2009 \n", "2 Oct 2009 \n", "3 Sep 2009 \n", "4 Dec 2009 \n", "... ... \n", "14530 Apr 2020 \n", "14531 Jul 2022 \n", "14532 Jul 2022 \n", "14533 Jul 2022 \n", "14534 Jul 2022 \n", "\n", "[14535 rows x 4 columns]" ] }, "execution_count": 49, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# date,hardware_name,TH/J\n", "# 2010-09-09 12:59:39,gtx460,0.0000004270\n", "hardware = pd.read_csv(\"../bitcoinforum/5_processing_extracted_data/hardware_instances_with_efficiency.csv\")\n", "hardware.rename(columns={\"date\": \"Date mentioned\", \"hardware_name\": \"Hardware name\", \"TH/J\": \"Power efficiency (TH/J)\"}, inplace=True)\n", "\n", "# \\#,Type,Hardware name,Release date,First date used,Eff. (TH/J)\n", "# 1,GPU,8800gts,Feb 2007,2011-03-08,1.09E-07\n", "paper_list = pd.read_csv(\"../hardwarelist/paper_list.csv\")\n", "paper_list[\"Hardware name\"] = paper_list[\"Hardware name\"].str.lower()\n", "\n", "# Merge on hardware name to get the release date\n", "df1 = hardware.merge(paper_list[[\"Hardware name\", \"Release date\"]], on=\"Hardware name\", how=\"left\")\n", "df1.to_csv(\"csv/Hardware mentions.csv\", index=False)\n", "df1" ] }, { "cell_type": "code", "execution_count": 50, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "'\\n•\\t\"Hardware count by year\" \\no\\tHere you just remake tab S20\\n'" ] }, "execution_count": 50, "metadata": {}, "output_type": "execute_result" } ], "source": [ "\"\"\"\n", "•\t\"Hardware count by year\" \n", "o\tHere you just remake tab S20\n", "\"\"\"" ] }, { "cell_type": "code", "execution_count": 51, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
YearCount
120111147
22012803
320132001
420142379
520151899
620161320
720171569
820181160
92019575
102020363
112021644
122022422
132023246
\n", "
" ], "text/plain": [ " Year Count\n", "1 2011 1147\n", "2 2012 803\n", "3 2013 2001\n", "4 2014 2379\n", "5 2015 1899\n", "6 2016 1320\n", "7 2017 1569\n", "8 2018 1160\n", "9 2019 575\n", "10 2020 363\n", "11 2021 644\n", "12 2022 422\n", "13 2023 246" ] }, "execution_count": 51, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df2 = df1[[\"Date mentioned\"]].copy()\n", "df2[\"Date mentioned\"] = df2[\"Date mentioned\"].apply(lambda x: x[:4])\n", "df2 = df2.groupby(\"Date mentioned\").size().reset_index(name=\"Count\")\n", "df2.rename(columns={\"Date mentioned\": \"Year\"}, inplace=True)\n", "# remove 2010\n", "df2 = df2[df2[\"Year\"] != \"2010\"]\n", "df2.to_csv(\"csv/Hardware count by year.csv\", index=False)\n", "df2" ] }, { "cell_type": "code", "execution_count": 52, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "'\\n•\\t\"Power efficiency\" \\no\\tColumn 1: \"Quarter\" (I guess you did quarterly, so you can write like Q1 2011, Q2 2011....)\\no\\tColumn 2: \"Average power efficiency (TH/J)\" (here you put the average we calculated for each quarter)\\no\\tColumn 3: \"Max attainable power efficiency (TH/J)\" (here you put Pmax value for each quarter, you can put the Pmax value you have for each quarter beginning)\\n'" ] }, "execution_count": 52, "metadata": {}, "output_type": "execute_result" } ], "source": [ "\"\"\"\n", "•\t\"Power efficiency\" \n", "o\tColumn 1: \"Quarter\" (I guess you did quarterly, so you can write like Q1 2011, Q2 2011....)\n", "o\tColumn 2: \"Average power efficiency (TH/J)\" (here you put the average we calculated for each quarter)\n", "o\tColumn 3: \"Max attainable power efficiency (TH/J)\" (here you put Pmax value for each quarter, you can put the Pmax value you have for each quarter beginning)\n", "\"\"\"" ] }, { "cell_type": "code", "execution_count": 53, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
QuarterAverage power efficiency (TH/J)Max attainable power efficiency (TH/J)
02011Q11.676753e+063780000.0
12011Q21.662901e+063780000.0
22011Q31.777678e+0623300000.0
\n", "
" ], "text/plain": [ " Quarter Average power efficiency (TH/J) \\\n", "0 2011Q1 1.676753e+06 \n", "1 2011Q2 1.662901e+06 \n", "2 2011Q3 1.777678e+06 \n", "\n", " Max attainable power efficiency (TH/J) \n", "0 3780000.0 \n", "1 3780000.0 \n", "2 23300000.0 " ] }, "execution_count": 53, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# date,price,hashrate,coins_per_block,efficiency,max_efficiency\n", "# 2023-12-31,38658.06,5.008642376892796e+20,6.25,34412596500.0,54100000000.0\n", "monthly_stuff = pd.read_csv(\"../bitcoinforum/6_merging/monthly_stuff.csv\")\n", "monthly_stuff[\"date\"] = pd.to_datetime(monthly_stuff[\"date\"])\n", "monthly_stuff[\"Quarter\"] = monthly_stuff[\"date\"].dt.to_period(\"Q\")\n", "monthly_stuff[\"Quarter\"] = monthly_stuff[\"Quarter\"].astype(str)\n", "monthly_stuff = monthly_stuff[[\"Quarter\", \"efficiency\", \"max_efficiency\"]]\n", "df3 = monthly_stuff.groupby(\"Quarter\").agg({\"efficiency\": \"mean\", \"max_efficiency\": \"first\"}).reset_index()\n", "df3.rename(columns={\"efficiency\": \"Average power efficiency (TH/J)\", \"max_efficiency\": \"Max attainable power efficiency (TH/J)\"}, inplace=True)\n", "df3.to_csv(\"csv/Power efficiency.csv\", index=False)\n", "df3.head(3)" ] }, { "cell_type": "code", "execution_count": 54, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "'\\n•\\t\"Electricity consumption and carbon footprint\"\\no\\tHere you just remake Tab S18\\n'" ] }, "execution_count": 54, "metadata": {}, "output_type": "execute_result" } ], "source": [ "\"\"\"\n", "•\t\"Electricity consumption and carbon footprint\"\n", "o\tHere you just remake Tab S18\n", "\"\"\"" ] }, { "cell_type": "code", "execution_count": 55, "metadata": {}, "outputs": [], "source": [ "# the file is exported by plots/main.ipynb" ] }, { "cell_type": "code", "execution_count": 56, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "'\\n•\\t\"Bitcoin price and mining costs\" \\no\\tColumn 1: \"Bitcoin price\" each row is a day since Jan 2011, and you put the price everyday until end of December 2023\\no\\tColumn 2: \"Mining costs with C = 0.03 USD/kWh; PUE = 1.0\"\\no\\tColumn 3: \"Mining costs with C = 0.05 USD/kWh; PUE = 1.1\"\\no\\tColumn 4: \"Mining costs with C = 0.07 USD/kWh; PUE = 1.2\"\\no\\tColumn 5: \"Price-cost price with C = 0.03 USD/kWh; PUE = 1.0\"\\no\\tColumn 6: \"Price-cost price with C = 0.05 USD/kWh; PUE = 1.1\"\\no\\tColumn 7: \"Price-cost price with C = 0.07 USD/kWh; PUE = 1.2\"\\n'" ] }, "execution_count": 56, "metadata": {}, "output_type": "execute_result" } ], "source": [ "\"\"\"\n", "•\t\"Bitcoin price and mining costs\" \n", "o\tColumn 1: \"Bitcoin price\" each row is a day since Jan 2011, and you put the price everyday until end of December 2023\n", "o\tColumn 2: \"Mining costs with C = 0.03 USD/kWh; PUE = 1.0\"\n", "o\tColumn 3: \"Mining costs with C = 0.05 USD/kWh; PUE = 1.1\"\n", "o\tColumn 4: \"Mining costs with C = 0.07 USD/kWh; PUE = 1.2\"\n", "o\tColumn 5: \"Price-cost ratio with C = 0.03 USD/kWh; PUE = 1.0\"\n", "o\tColumn 6: \"Price-cost ratio with C = 0.05 USD/kWh; PUE = 1.1\"\n", "o\tColumn 7: \"Price-cost ratio with C = 0.07 USD/kWh; PUE = 1.2\"\n", "\"\"\"" ] }, { "cell_type": "code", "execution_count": 66, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
dateBitcoin priceMining costs with C = 0.03 USD/kWh; PUE = 1.0Mining costs with C = 0.05 USD/kWh; PUE = 1.1Mining costs with C = 0.07 USD/kWh; PUE = 1.2Price-cost ratio with C = 0.03 USD/kWh; PUE = 1.0Price-cost ratio with C = 0.05 USD/kWh; PUE = 1.1Price-cost ratio with C = 0.07 USD/kWh; PUE = 1.2
02023-12-3138658.060011643.74185321346.86006532602.4771903.3200721.8109481.185740
12023-10-3127978.100014776.11266827089.53989241373.1154711.8934681.0328010.676239
22023-09-3025816.570014485.65305326557.03059740559.8285481.7822170.9721180.636506
32023-08-3129629.240016929.70101231037.78518947403.1628351.7501340.9546180.625048
42023-07-3130589.050016393.18108430054.16532045900.9070341.8659621.0177970.666415
...........................
1492011-06-309.57000.4657750.8539201.30416920.54642211.2071397.338008
1502011-05-313.03310.1389760.2547890.38913321.82463811.9043487.794514
1512011-04-300.77410.0424950.0779080.11898718.2161099.9360596.505753
1522011-03-310.92020.0374270.0686160.10479524.58660013.4108728.780928
1532011-02-280.70000.0165070.0302620.04621942.40675323.13095615.145269
\n", "

154 rows × 8 columns

\n", "
" ], "text/plain": [ " date Bitcoin price Mining costs with C = 0.03 USD/kWh; PUE = 1.0 \\\n", "0 2023-12-31 38658.0600 11643.741853 \n", "1 2023-10-31 27978.1000 14776.112668 \n", "2 2023-09-30 25816.5700 14485.653053 \n", "3 2023-08-31 29629.2400 16929.701012 \n", "4 2023-07-31 30589.0500 16393.181084 \n", ".. ... ... ... \n", "149 2011-06-30 9.5700 0.465775 \n", "150 2011-05-31 3.0331 0.138976 \n", "151 2011-04-30 0.7741 0.042495 \n", "152 2011-03-31 0.9202 0.037427 \n", "153 2011-02-28 0.7000 0.016507 \n", "\n", " Mining costs with C = 0.05 USD/kWh; PUE = 1.1 \\\n", "0 21346.860065 \n", "1 27089.539892 \n", "2 26557.030597 \n", "3 31037.785189 \n", "4 30054.165320 \n", ".. ... \n", "149 0.853920 \n", "150 0.254789 \n", "151 0.077908 \n", "152 0.068616 \n", "153 0.030262 \n", "\n", " Mining costs with C = 0.07 USD/kWh; PUE = 1.2 \\\n", "0 32602.477190 \n", "1 41373.115471 \n", "2 40559.828548 \n", "3 47403.162835 \n", "4 45900.907034 \n", ".. ... \n", "149 1.304169 \n", "150 0.389133 \n", "151 0.118987 \n", "152 0.104795 \n", "153 0.046219 \n", "\n", " Price-cost ratio with C = 0.03 USD/kWh; PUE = 1.0 \\\n", "0 3.320072 \n", "1 1.893468 \n", "2 1.782217 \n", "3 1.750134 \n", "4 1.865962 \n", ".. ... \n", "149 20.546422 \n", "150 21.824638 \n", "151 18.216109 \n", "152 24.586600 \n", "153 42.406753 \n", "\n", " Price-cost ratio with C = 0.05 USD/kWh; PUE = 1.1 \\\n", "0 1.810948 \n", "1 1.032801 \n", "2 0.972118 \n", "3 0.954618 \n", "4 1.017797 \n", ".. ... \n", "149 11.207139 \n", "150 11.904348 \n", "151 9.936059 \n", "152 13.410872 \n", "153 23.130956 \n", "\n", " Price-cost ratio with C = 0.07 USD/kWh; PUE = 1.2 \n", "0 1.185740 \n", "1 0.676239 \n", "2 0.636506 \n", "3 0.625048 \n", "4 0.666415 \n", ".. ... \n", "149 7.338008 \n", "150 7.794514 \n", "151 6.505753 \n", "152 8.780928 \n", "153 15.145269 \n", "\n", "[154 rows x 8 columns]" ] }, "execution_count": 66, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# date,joules_per_coin\n", "# 2023-12-31,1397249022408.723\n", "joules_per_coin = pd.read_csv(\"joules_per_coin.csv\") # exported by plots/main.ipynb\n", "monthly_stuff = pd.read_csv(\"../bitcoinforum/6_merging/monthly_stuff.csv\")\n", "price = monthly_stuff[[\"date\", \"price\"]].copy()\n", "\n", "df4 = price.merge(joules_per_coin, on=\"date\", how=\"left\")\n", "df4[\"kwh_per_coin\"] = df4[\"joules_per_coin\"] / 3600000\n", "df4[\"Mining costs with C = 0.03 USD/kWh; PUE = 1.0\"] = df4[\"kwh_per_coin\"] * 0.03\n", "df4[\"Mining costs with C = 0.05 USD/kWh; PUE = 1.1\"] = df4[\"kwh_per_coin\"] * 0.05 * 1.1\n", "df4[\"Mining costs with C = 0.07 USD/kWh; PUE = 1.2\"] = df4[\"kwh_per_coin\"] * 0.07 * 1.2\n", "df4[\"Price-cost ratio with C = 0.03 USD/kWh; PUE = 1.0\"] = df4[\"price\"] / df4[\"Mining costs with C = 0.03 USD/kWh; PUE = 1.0\"]\n", "df4[\"Price-cost ratio with C = 0.05 USD/kWh; PUE = 1.1\"] = df4[\"price\"] / df4[\"Mining costs with C = 0.05 USD/kWh; PUE = 1.1\"]\n", "df4[\"Price-cost ratio with C = 0.07 USD/kWh; PUE = 1.2\"] = df4[\"price\"] / df4[\"Mining costs with C = 0.07 USD/kWh; PUE = 1.2\"]\n", "df4.rename(columns={\"price\": \"Bitcoin price\"}, inplace=True)\n", "df4.drop(columns=[\"joules_per_coin\", \"kwh_per_coin\"], inplace=True)\n", "df4.to_csv(\"csv/Bitcoin price and mining costs.csv\", index=False)\n", "df4" ] } ], "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 }