{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# enable to update the pmax evolution table, otherwise turn it off as it breaks the rest\n",
    "# PMAX_EVOLUTION = True\n",
    "PMAX_EVOLUTION = False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "pd.set_option('display.max_rows', 10)\n",
    "\n",
    "# Read the CSV file\n",
    "df = pd.read_csv('hardware_merged.csv')\n",
    "asicminervalue_with_date = pd.read_csv('hardware_asicminervalue.csv', usecols=['hardware_index', 'hardware_name', 'hardware_release_date'])\n",
    "asicminervalue_with_date = asicminervalue_with_date.drop(columns=['hardware_index'])\n",
    "\n",
    "# drop duplicate hardware_name rows in asicminervalue_with_date\n",
    "asicminervalue_with_date = asicminervalue_with_date.drop_duplicates(subset='hardware_name', keep='first')\n",
    "\n",
    "# lowercase values in asicminervalue_with_date\n",
    "asicminervalue_with_date['hardware_name'] = asicminervalue_with_date['hardware_name'].str.lower()\n",
    "asicminervalue_with_date['hardware_name'] = asicminervalue_with_date['hardware_name'].str.replace(\"bitmain \",\"\")\n",
    "asicminervalue_with_date.rename(columns={'hardware_release_date': 'release_date'}, inplace=True)\n",
    "\n",
    "\n",
    "# Sort the dataframe by efficiency (Mhash/J) in descending order\n",
    "df_sorted = df.sort_values(by='Mhash/J', ascending=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sorted # hardware_name\tMhash/J"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "asicminervalue_with_date # \thardware_name\thardware_release_date\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# left join\n",
    "result = pd.merge(df_sorted, asicminervalue_with_date, on='hardware_name', how='left')\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "result.to_csv('paper_list.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# take rows where Mhash/J < 10.3\n",
    "gpus = result[result['Mhash/J'] < 10.3]\n",
    "gpus = gpus[[\"hardware_name\"]]\n",
    "# gpus.to_csv('gpu_list.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gpu_and_date = pd.read_csv('gpu_and_date.csv',delimiter=';')\n",
    "gpu_and_date # hardware_name\trelease_date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# left join\n",
    "result = pd.merge(result, gpu_and_date, on='hardware_name', how='left') # hardware_name\tMhash/J\trelease_date_x\trelease_date_y\n",
    "# merge the two release_date columns\n",
    "result['release_date'] = result['release_date_x'].combine_first(result['release_date_y'])\n",
    "result = result.drop(columns=['release_date_x', 'release_date_y'])\n",
    "result.to_csv('paper_list.csv', index=False)\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "hardware_instances = pd.read_csv('../bitcoinforum/5_processing_extracted_data/hardware_instances_with_efficiency.csv') # date\thardware TH/J\n",
    "hardware_instances.rename(columns = {'hardware':'hardware_name'}, inplace = True)\n",
    "hardware_instances.rename(columns = {'date':'date_used'}, inplace = True)\n",
    "hardware_instances.drop(columns=['TH/J'], inplace=True)\n",
    "hardware_instances"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # for each thing in \"result\", find the first date in hardware_instances\n",
    "# result['first_date_used'] = None\n",
    "# for index, row in result.iterrows():\n",
    "#     hardware_name = row['hardware_name']\n",
    "#     release_date = hardware_instances[hardware_instances['hardware_name'] == hardware_name]['date_used'].min()\n",
    "#     result.at[index, 'first_date_used'] = release_date\n",
    "\n",
    "# for each thing in \"result\", find the first date in hardware_instances that comes after the release date\n",
    "result['first_date_used'] = None\n",
    "for index, row in result.iterrows():\n",
    "    hardware_name = row['hardware_name']\n",
    "    release_date = row['release_date']\n",
    "    release_date = pd.to_datetime(release_date, errors='coerce')\n",
    "    if release_date is not pd.NaT:\n",
    "        date_used = hardware_instances[hardware_instances['hardware_name'] == hardware_name]['date_used']\n",
    "        # Convert 'date_used' and 'release_date' to datetime\n",
    "        date_used = pd.to_datetime(date_used, errors='coerce')\n",
    "        date_used = date_used[date_used >= release_date].min()\n",
    "        result.at[index, 'first_date_used'] = str(date_used).replace(\"NaT\",\"*\")\n",
    "    else:\n",
    "        release_date = hardware_instances[hardware_instances['hardware_name'] == hardware_name]['date_used'].min()\n",
    "        result.at[index, 'first_date_used'] = release_date\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# replace dates like Mar 4th, 2009 with Mar 2009\n",
    "result[\"release_date\"] = result[\"release_date\"].str.replace(r'(\\w{3}) \\d{1,2}(?:th|st|nd|rd), (\\d{4})', r'\\1 \\2', regex=True)\n",
    "# in first_date_used, remove the time\n",
    "result[\"first_date_used\"] = result[\"first_date_used\"].str.split(\" \").str[0]\n",
    "# replace \"unknown\" with \"*\"\n",
    "result[\"release_date\"] = result[\"release_date\"].replace(\"unknown\", \"*\")\n",
    "# replace NaN with \"*\"\n",
    "result[\"first_date_used\"] = result[\"first_date_used\"].replace(float('nan'), \"*\")\n",
    "result[\"release_date\"] = result[\"release_date\"].replace(float('nan'), \"*\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# add type columns: GPU if Mhash/J < 10.3, ASIC otherwise\n",
    "result['type'] = None\n",
    "for index, row in result.iterrows():\n",
    "    if row['Mhash/J'] < 10.3:\n",
    "        result.at[index, 'type'] = 'GPU'\n",
    "    else:\n",
    "        result.at[index, 'type'] = 'ASIC'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert efficiency to TH/J and scientific notation\n",
    "result['TH/J'] = result['Mhash/J'] / 1e6\n",
    "result['TH/J'] = result['TH/J'].apply(lambda x: '%.2E' % x)\n",
    "result = result[['type', 'hardware_name', 'release_date', 'first_date_used', 'TH/J']]\n",
    "result = result.rename(columns={\"hardware_name\": \"Hardware name\"})\n",
    "result = result.rename(columns={\"type\": \"Type\"})\n",
    "result = result.rename(columns={\"release_date\": \"Release date\"})\n",
    "result = result.rename(columns={\"first_date_used\": \"First date used\"})\n",
    "result = result.rename(columns={\"TH/J\": \"Eff. (TH/J)\"})\n",
    "# Make first hardware_name letter uppercase\n",
    "result['Hardware name'] = result['Hardware name'].str.capitalize()\n",
    "# if hardware_name starts with \"Gtx\", replace with \"GTX\"\n",
    "result['Hardware name'] = result['Hardware name'].str.replace(\"Gtx\", \"GTX\")\n",
    "result['Hardware name'] = result['Hardware name'].str.replace(\"Gts\", \"GTS\")\n",
    "result['Hardware name'] = result['Hardware name'].str.replace(\"Gt\", \"GT\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"corrections = [\")\n",
    "for row in result.iterrows():\n",
    "    release_date = row[1]['Release date']\n",
    "    if release_date == \"*\":\n",
    "        print(\"{\" + f\"\"\"\n",
    "    'Hardware name': '{row[1]['Hardware name']}', 'Release date': \"\" # First date used = {row[1]['First date used']}\n",
    "    \"\"\".strip().replace(\"#\",\"},#\"))\n",
    "print(\"]\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "corrections = [\n",
    "{'Hardware name': 'Ion', 'Release date': 'February 2009' },# First date used = 2011-07-06\n",
    "{'Hardware name': 'Quadro nvs 135m', 'Release date': 'May 9th, 2007' },# First date used = *\n",
    "{'Hardware name': '8800gts', 'Release date': 'Feb 12th, 2007' },# First date used = 2011-03-08\n",
    "{'Hardware name': 'Quadro fx 1600m', 'Release date': \"Jun 1st, 2007\" },# First date used = *\n",
    "{'Hardware name': '8800 gts', 'Release date': \"Feb 12th, 2007\" },# First date used = 2011-05-23\n",
    "{'Hardware name': '9600gso512', 'Release date': \"Oct 23rd, 2008\" },# First date used = 2011-05-28\n",
    "{'Hardware name': '9600gt', 'Release date': \"Feb 21st, 2008\" },# First date used = 2011-06-10\n",
    "{'Hardware name': '9800gx2', 'Release date': \"Feb 21st, 2008\" },# First date used = 2011-04-14\n",
    "{'Hardware name': '9800gtx', 'Release date': \"May 27th, 2009\" },# First date used = 2012-09-27\n",
    "{'Hardware name': '9600gso', 'Release date': \"Apr 28th, 2008\" },# First date used = 2011-05-22\n",
    "{'Hardware name': 'GTX280', 'Release date': \"Jun 16th, 2008\" },# First date used = 2011-04-25\n",
    "{'Hardware name': 'GTX260c216', 'Release date': \"Sep 16th, 2008\" },# First date used = 2011-05-23\n",
    "{'Hardware name': 'Quadro nvs 3100m', 'Release date': \"Jul 23rd, 2013\" },# First date used = *\n",
    "{'Hardware name': '9800gt ee', 'Release date': \"Jul 21st, 2008\" },# First date used = *\n",
    "{'Hardware name': '8800gt', 'Release date': \"Oct 29th, 2007\" },# First date used = 2010-12-23\n",
    "{'Hardware name': '9800gt', 'Release date': \"Jul 21st, 2008\" },# First date used = 2011-05-16\n",
    "{'Hardware name': '4550', 'Release date': \"Sep 30th, 2008\" },# First date used = 2012-04-19\n",
    "{'Hardware name': '4570m', 'Release date': \"Jan 9th, 2009\" },# First date used = 2012-09-13\n",
    "{'Hardware name': '9400m', 'Release date': \"Oct 15th, 2008\" },# First date used = 2011-06-24\n",
    "{'Hardware name': 'GTX 560m', 'Release date': \"May 30th, 2011\" },# First date used = *\n",
    "{'Hardware name': 'Quadro fx 2000m', 'Release date': \"Jan 13th, 2011\" },# First date used = *\n",
    "{'Hardware name': 'Quadro fx 3600m', 'Release date': \"Feb 23rd, 2008\" },# First date used = *\n",
    "{'Hardware name': '6310m', 'Release date': \"Nov 9th, 2010\" },# First date used = 2013-05-17\n",
    "{'Hardware name': '5450', 'Release date': \"Jan 7th, 2010\" },# First date used = 2012-01-25\n",
    "{'Hardware name': '5550', 'Release date': \"Feb 9th, 2010\" },# First date used = 2011-07-27\n",
    "{'Hardware name': 'GTS 350m', 'Release date': \"Jan 7th, 2010\" },# First date used = *\n",
    "{'Hardware name': '5650', 'Release date': \"Jan 7th, 2010\" },# First date used = 2011-04-15\n",
    "{'Hardware name': '5670', 'Release date': \"Jan 14th, 2010\" },# First date used = 2011-05-08\n",
    "{'Hardware name': '5570', 'Release date': \"Feb 9th, 2010\" },# First date used = 2011-02-28\n",
    "{'Hardware name': 'Bitforce sha256 single', 'Release date': \"May 2012\" },# First date used = 2012-05-09\n",
    "{'Hardware name': 'Bitcoin dominator x5000', 'Release date': \"January 30, 2013\" },# First date used = *\n",
    "{'Hardware name': 'Lancelot', 'Release date': \"February 11, 2013\" },# First date used = 2013-02-02\n",
    "{'Hardware name': 'Icarus', 'Release date': \"November 9, 2011\" },# First date used = 2011-12-04\n",
    "{'Hardware name': 'Modminer quad', 'Release date': \"2012-06-28\" },# First date used = 2012-06-28\n",
    "{'Hardware name': 'Butterflylabs mini rig', 'Release date': \"May 14, 2012\" },# First date used = 2012-05-14\n",
    "{'Hardware name': 'X6500 fpga miner', 'Release date': \"2011-08-30\" },# First date used = 2011-08-30\n",
    "{'Hardware name': 'Avalon batch 1', 'Release date': \"2013-01-13\" },# First date used = 2013-01-13\n",
    "{'Hardware name': 'Avalon batch 3', 'Release date': \"2013-03-25\" },# First date used = 2013-03-25\n",
    "{'Hardware name': 'Avalon batch 2', 'Release date': \"2013-02-26\" },# First date used = 2013-02-26\n",
    "{'Hardware name': 'Asicminer be blade', 'Release date': \"2013-05-15\" },# First date used = 2013-05-15\n",
    "{'Hardware name': 'Asicminer be sapphire', 'Release date': \"2013-05-15\" },# First date used = *\n",
    "{'Hardware name': 'Bitmine.ch avalon clone 85gh', 'Release date': \"2013-07-11\" },# First date used = 2013-07-11\n",
    "{'Hardware name': 'Terrahash dx large', 'Release date': \"2013-06-18\" },# First date used = 2013-06-18\n",
    "{'Hardware name': 'Terrahash klondike 16', 'Release date': \"2013-05-28\" },# First date used = 2013-05-28\n",
    "{'Hardware name': 'Terrahash dx mini', 'Release date': \"2013-05-28\" },# First date used = *\n",
    "{'Hardware name': 'Terrahash klondike 64', 'Release date': \"2013-05-28\" },# First date used = 2013-05-30\n",
    "{'Hardware name': 'Asicminer be cube', 'Release date': \"2013-11-09\" },# First date used = 2013-11-09\n",
    "{'Hardware name': 'Klondike', 'Release date': \"2013-05-10\" },# First date used = 2013-05-10\n",
    "{'Hardware name': 'Bfl sc', 'Release date': \"2013-01-29\" },# First date used = 2013-01-29\n",
    "{'Hardware name': 'Bfl single \\'sc\\'', 'Release date': \"2013-04-05\" },# First date used = 2013-04-05\n",
    "{'Hardware name': 'Knc jupiter', 'Release date': \"2013-05-27\" },# First date used = 2013-05-27\n",
    "{'Hardware name': 'Knc saturn', 'Release date': \"2013-06-05\" },# First date used = 2013-06-05\n",
    "{'Hardware name': 'Kncminer mercury', 'Release date': \"2013-08-03\" },# First date used = 2013-08-03\n",
    "{'Hardware name': 'Antminer s1', 'Release date': \"October 5, 2013\" },# First date used = 2013-11-26\n",
    "{'Hardware name': 'Metabank', 'Release date': \"2013-06-12\" },# First date used = 2013-06-12\n",
    "{'Hardware name': 'Nanofury nf2', 'Release date': \"2014-03-25\" },# First date used = 2014-03-25\n",
    "{'Hardware name': 'Cointerra terraminer iv', 'Release date': \"2013-09-03\" },# First date used = 2013-09-03\n",
    "{'Hardware name': '1 th/s coincraft miner', 'Release date': \"2013-12-17\" },# First date used = 2013-12-17\n",
    "{'Hardware name': 'Nanofury / icefury', 'Release date': \"\" },# First date used = 2013-10-27\n",
    "{'Hardware name': 'Antminer u1', 'Release date': \"\" },# First date used = 2013-12-26\n",
    "{'Hardware name': 'Rkminer t1 800g', 'Release date': \"\" },# First date used = 2014-09-01\n",
    "{'Hardware name': 'Hashbuster micro', 'Release date': \"Mar 31, 2014\" },# First date used = *\n",
    "{'Hardware name': 'Asicminer be tube', 'Release date': \"October 2014\" },# First date used = 2013-05-19\n",
    "{'Hardware name': 'Antminer s2', 'Release date': \"\" },# First date used = 2014-03-23\n",
    "{'Hardware name': 'Hashfast baby jet', 'Release date': \"\" },# First date used = 2013-08-04\n",
    "{'Hardware name': 'Hashfast sierra', 'Release date': \"\" },# First date used = 2013-09-22\n",
    "{'Hardware name': 'Hashfast sierra evo 3', 'Release date': \"Mar 7, 2014\" },# First date used = *\n",
    "{'Hardware name': 'Rkminer rocket box', 'Release date': \"\" },# First date used = 2014-08-12\n",
    "{'Hardware name': 'Btc garden am-v1', 'Release date': \"\" },# First date used = 2014-07-10\n",
    "{'Hardware name': '1th dragon bitcoin miner', 'Release date': \"\" },# First date used = 2014-03-06\n",
    "{'Hardware name': 'Rkminer r4-box', 'Release date': \"\" },# First date used = 2014-06-18\n",
    "{'Hardware name': 'Rkminer r3-box', 'Release date': \"\" },# First date used = 2014-07-11\n",
    "{'Hardware name': '2 th/s coincraft miner', 'Release date': \"\" },# First date used = 2013-11-18\n",
    "{'Hardware name': 'Blue fury', 'Release date': \"\" },# First date used = 2013-09-24\n",
    "{'Hardware name': 'Black arrow prospero x-3', 'Release date': \"\" },# First date used = 2013-11-19\n",
    "{'Hardware name': 'Antminer u3', 'Release date': \"\" },# First date used = 2014-06-12\n",
    "{'Hardware name': 'Antminer u2+', 'Release date': \"\" },# First date used = 2014-03-03\n",
    "{'Hardware name': 'Black arrow prospero x-1', 'Release date': \"\" },# First date used = 2013-11-28\n",
    "{'Hardware name': 'Red/bluefury', 'Release date': \"\" },# First date used = 2013-09-19\n",
    "{'Hardware name': 'Hashcoins apollo v3', 'Release date': \"\" },# First date used = 2014-07-07\n",
    "{'Hardware name': 'Spondooliestech sp10 dawson', 'Release date': \"\" },# First date used = 2014-04-18\n",
    "{'Hardware name': 'Twinfury', 'Release date': \"\" },# First date used = 2013-12-23\n",
    "{'Hardware name': 'Bi*fury', 'Release date': \"\" },# First date used = 2013-11-25\n",
    "{'Hardware name': 'Butterflylabs (bfl) jalapeno', 'Release date': \"2013-04-22\" },# First date used = 2013-01-16\n",
    "{'Hardware name': 'Antminer s3+', 'Release date': \"\" },# First date used = 2014-08-18\n",
    "{'Hardware name': 'Asicminer be prisma', 'Release date': \"\" },# First date used = 2014-09-29\n",
    "{'Hardware name': 'Bfl monarch', 'Release date': \"\" },# First date used = 2013-12-21\n",
    "{'Hardware name': 'Knc neptune', 'Release date': \"\" },# First date used = 2013-11-27\n",
    "{'Hardware name': 'Antminer s4', 'Release date': \"\" },# First date used = 2014-10-02\n",
    "{'Hardware name': 'Hashcoins zeus v3', 'Release date': \"2014-10-02\" },# First date used = *\n",
    "{'Hardware name': 'Spondooliestech sp30 yukon', 'Release date': \"\" },# First date used = 2014-04-18\n",
    "{'Hardware name': 'Spondooliestech sp35 yukon', 'Release date': \"\" },# First date used = 2014-12-08\n",
    "{'Hardware name': 'Spondooliestech sp20 jackson', 'Release date': \"\" },# First date used = 2014-11-10\n",
    "{'Hardware name': 'Spondooliestech sp31 yukon', 'Release date': \"\" },# First date used = 2014-11-17\n",
    "{'Hardware name': 'Bitmine coincraft a1', 'Release date': \"December 2013\" },# First date used = *\n",
    "{'Hardware name': 'Antminer s5+', 'Release date': \"\" },# First date used = 2015-08-18\n",
    "{'Hardware name': 'Avalon6', 'Release date': \"\" },# First date used = 2015-11-12\n",
    "{'Hardware name': 'Whatsminer m2', 'Release date': \"\" },# First date used = 2017-10-01\n",
    "{'Hardware name': 'Avalon721', 'Release date': \"\" },# First date used = 2017-01-20\n",
    "{'Hardware name': 'Avalon741', 'Release date': \"\" },# First date used = 2017-02-09\n",
    "{'Hardware name': 'Whatsminer m3', 'Release date': \"\" },# First date used = 2019-06-09\n",
    "{'Hardware name': 'Avalon761', 'Release date': \"December 13, 2017\" },# First date used = *\n",
    "{'Hardware name': 'Ebit e9+', 'Release date': \"\" },# First date used = 2017-10-08\n",
    "{'Hardware name': 'Ebit e9', 'Release date': \"\" },# First date used = 2017-07-20\n",
    "{'Hardware name': 'Avalon821', 'Release date': \"\" },# First date used = 2018-03-17\n",
    "{'Hardware name': 'Ebit e9++', 'Release date': \"\" },# First date used = 2018-02-20\n",
    "{'Hardware name': 'Ebit e10', 'Release date': \"\" },# First date used = 2018-02-20\n",
    "]\n",
    "\n",
    "\n",
    "# Apply corrections\n",
    "for correction in corrections:\n",
    "    result.loc[result['Hardware name'] == correction['Hardware name'], 'Release date'] = correction['Release date']\n",
    "\n",
    "# In rows where Release date is \"\", replace with First date used\n",
    "result.loc[result['Release date'] == \"\", 'Release date'] = result['First date used']\n",
    "\n",
    "\n",
    "# Function to parse and format dates\n",
    "def standardize_date(date_str):\n",
    "    try:\n",
    "        # Parse the date\n",
    "        parsed_date = pd.to_datetime(date_str, errors='coerce')\n",
    "        # Format the date to \"Month Year\"\n",
    "        return parsed_date.strftime('%b %Y') if parsed_date else date_str\n",
    "    except Exception as e:\n",
    "        return date_str\n",
    "\n",
    "# Apply the function to the release_date column\n",
    "result[\"Release date\"] = result[\"Release date\"].apply(standardize_date)\n",
    "\n",
    "# Sort the dataframe by release date\n",
    "result[\"Release date\"] = pd.to_datetime(result[\"Release date\"], errors='coerce')\n",
    "result = result.sort_values(by='Release date', ascending=True)\n",
    "result[\"Release date\"] = result[\"Release date\"].apply(standardize_date)\n",
    "\n",
    "# Sort the dataframe by name\n",
    "# result = result.sort_values(by='Hardware name', ascending=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "hardware_to_remove = [\n",
    "    \"8800 gts\",\n",
    "    \"9800gt ee\",\n",
    "    \"9800gtx+\",\n",
    "    \"9800gtx\",\n",
    "    \"9800gx2\",\n",
    "    \"GTX260c216\",\n",
    "]\n",
    "\n",
    "# Remove hardware from the list\n",
    "result = result[~result['Hardware name'].isin(hardware_to_remove)]\n",
    "\n",
    "# Remove rows where hardware name is duplicated\n",
    "result = result.drop_duplicates(subset='Hardware name', keep='first')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "if not PMAX_EVOLUTION:\n",
    "    # remove rows where first date used is *\n",
    "    result = result[result['First date used'] != \"*\"]\n",
    "# Reset index\n",
    "result = result.reset_index(drop=True)\n",
    "result[\"\\#\"] = result.index + 1\n",
    "# move n to the left\n",
    "cols = result.columns.tolist()\n",
    "cols = cols[-1:] + cols[:-1]\n",
    "result = result[cols]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "result.to_csv('paper_list.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "len(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# pd.set_option('display.max_rows', None)\n",
    "result.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pmax_evolution = result.copy().head(1)\n",
    "current_max = pmax_evolution.iloc[0]['Eff. (TH/J)']\n",
    "for row in result.iterrows():\n",
    "    eff = row[1]['Eff. (TH/J)']\n",
    "    if float(eff) > float(current_max):\n",
    "        current_max = eff\n",
    "        # pmax_evolution = pmax_evolution.append(row[1])\n",
    "        pmax_evolution = pd.concat([pmax_evolution, row[1].to_frame().T], ignore_index=True)\n",
    "pmax_evolution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "extra_2024_hardware = pd.DataFrame({\n",
    "    'Hardware name': ['MicroBT whatsminer m66s', 'Antminer S21 Hyd', 'Antminer S21 Pro', 'Antminer S21 XP', 'Antminer S21 XP Hyd'],\n",
    "    'Release date': ['Nov 2023', 'Feb 2024', 'Jul 2024', 'Oct 2024', 'Nov 2024'],\n",
    "    'Eff. (TH/J)': [54100, 62500, 66270.178420, 74074.074074, 83333.333333]\n",
    "})\n",
    "extra_2024_hardware['\\#'] = extra_2024_hardware.index + 282\n",
    "extra_2024_hardware['Type'] = 'ASIC'\n",
    "extra_2024_hardware['First date used'] = \"*\"\n",
    "# convert eff to scientific notation string\n",
    "extra_2024_hardware['Eff. (TH/J)'] = extra_2024_hardware['Eff. (TH/J)'].apply(lambda x: '%.2E' % (x*1e-6))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "if PMAX_EVOLUTION:\n",
    "    result = pmax_evolution\n",
    "    \n",
    "# add extra hardware\n",
    "result = pd.concat([result, extra_2024_hardware], ignore_index=True) # .iloc[1:]\n",
    "\n",
    "# create latex with only x at a time\n",
    "first_iteration = True\n",
    "start = 0\n",
    "\n",
    "while start < len(result):\n",
    "    if first_iteration:\n",
    "        end = min(start + 25, len(result))\n",
    "        first_iteration = False\n",
    "    else:\n",
    "        end = min(start + 41, len(result))\n",
    "    \n",
    "    if PMAX_EVOLUTION: \n",
    "        caption = \"Continuation of Table~\\\\ref{tab:pmax_evolution}.\" if start > 0 else \"Evolution of the most efficient hardware.\"\n",
    "\n",
    "    else:\n",
    "        caption = \"Continuation of Table~\\\\ref{tab:hardware_list}.\" if start > 0 else \"List of hardware used in the paper.\"\n",
    "    latex_str = result[start:end].to_latex(index=False, \n",
    "                                           caption=caption,\n",
    "                                           label=(\"tab:pmax_evolution\" if PMAX_EVOLUTION else \"tab:hardware_list\") if start == 0 else \"\",\n",
    "                                           column_format='|' + '|'.join(['l'] * len(result.columns)) + '|')\n",
    "    \n",
    "    # Insert [H] and \\centering\n",
    "    latex_str = latex_str.replace(\"\\\\begin{table}\", \"\\\\begin{table}[H]\\n\\\\centering\").replace(\"\\caption\", \"\\caption*\")\n",
    "    print(latex_str)\n",
    "    \n",
    "    start = end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "if PMAX_EVOLUTION: \n",
    "    1/0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create new df where for each day from jan 2011 to dec 2023, the efficiency of the most efficient hardware released so far is calculated\n",
    "\n",
    "# convert result['Release date'] to datetime (middle of the month)\n",
    "result['Release date'] = pd.to_datetime(result['Release date'], errors='coerce')\n",
    "result['Release date'] = result['Release date'] + pd.offsets.MonthBegin(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "result['Eff. (TH/J)'] = result['Eff. (TH/J)'].astype(float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a new dataframe with all the dates from Jan 2011 to Dec 2023\n",
    "dates = pd.date_range(start='2011-01-01', end='2023-12-31', freq='D')\n",
    "efficiency_df = pd.DataFrame(dates, columns=['date'])\n",
    "efficiency_df['max_efficiency'] = None\n",
    "\n",
    "# For each date, find the most efficient hardware released so far\n",
    "for index, row in efficiency_df.iterrows():\n",
    "    date = row['date']\n",
    "    # Find the hardware released before the date\n",
    "    hardware_released = result[result['Release date'] <= date]\n",
    "    # Find the most efficient hardware released before the date\n",
    "    most_efficient_hardware = hardware_released['Eff. (TH/J)'].max()\n",
    "    efficiency_df.at[index, 'max_efficiency'] = most_efficient_hardware\n",
    "\n",
    "    # print when new best is found\n",
    "    if index > 0 and most_efficient_hardware != efficiency_df.at[index-1, 'max_efficiency']:\n",
    "        print(f\"{date}: {most_efficient_hardware}\")\n",
    "\n",
    "efficiency_df['max_efficiency'] = efficiency_df['max_efficiency'].astype(float)*1e12\n",
    "efficiency_df.to_csv('pmaxv2.csv', index=False)\n",
    "efficiency_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "efficiency_df[\"max_efficiency\"] = pd.to_numeric(efficiency_df[\"max_efficiency\"], errors='coerce')\n",
    "efficiency_df.plot(x='date', y='max_efficiency', figsize=(20, 10), title='Efficiency of the most efficient hardware over time')"
   ]
  }
 ],
 "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
}