UTXO_Altcoin.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Altcoin UTXO Automatic Updater.ipynb",
      "provenance": [],
      "collapsed_sections": [
        "zhwe8Oh1Vv7w",
        "G4qrxW8Y5p9v",
        "nBl78B9Vdp6G"
      ],
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/SciEcon/UTXO/blob/main/UTXO_Altcoin.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "aOKcNgBvwTEF"
      },
      "source": [
        "#Instructions:\n",
        "\n",
        "Before you run the program, please\n",
        "\n",
        "1. Add shortcut for the shared **UTXO folder** as a copy to your own google drive \n",
        "2. Make a copy of the Colab Notebook shared by your collaborator who made the last updates\n",
        "3. Change variables **currency** and **\"currency_brief\"** to currency of your choice. \n",
        "\n",
        "> The choice set = {'litecoin', 'dogecoin', 'dash', 'zcash', 'bitcoin_cash'}\n",
        "\n",
        "> The choice_brief set = {'ltc', 'dgc', 'dash', 'zec', 'bch'}\n",
        "\n",
        "4. Change the variable **last_update** and (update) **end** to the date of your choice\n",
        "\n",
        "After you run the program, please \n",
        "\n",
        "1.   Update the lastest update date below.\n",
        "2.   Share the latest Colab Notebook with Collaborators\n",
        "3.   Share the updated UTXO folder with Collaborators\n",
        "\n",
        "Up to now, \n",
        "\n",
        "*   Bitcoin (btc) is updated until 2021-02-10.\n",
        "*   litecoin (ltc) is updated until 2020-12-31.\n",
        "*   dogecoin (dgc) is updated until 2020-12-31.\n",
        "*   dash (dash) is updated until 2021-02-20.\n",
        "*   zcash (zec) is updated until 2020-12-31.\n",
        "*   bitcoin_cash (bch) is updated until 2020-12-31.\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "A5BBxnuqVx03"
      },
      "source": [
        "# Preliminaries"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "pN5ZNqf4UgTn"
      },
      "source": [
        "import numpy as np # linear algebra\n",
        "import pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n",
        "import decimal\n",
        "from datetime import datetime, date, timedelta, timezone"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Foxw5lL-ZqCP"
      },
      "source": [
        "Please change the input listed in this code box."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Txuu9OhqejbE"
      },
      "source": [
        "currency = \"zcash\" #the name of the altcoin\n",
        "currency_brief = \"zec\"\n",
        "last_update = date(2020,12,31)\n",
        "start = last_update + timedelta(days=1)\n",
        "end = date(2021,2,20)\n",
        "\n",
        "PROJECT_ID = 'crypto-291811'"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "y3FUxcgJVi1N",
        "outputId": "7324cdf3-4442-4fda-d86e-246462452fb2"
      },
      "source": [
        "#Connect to Google Cloud\n",
        "from google.colab import auth\n",
        "auth.authenticate_user()\n",
        "print('Authenticated')"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Authenticated\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "E7NvYDgcVk7v",
        "outputId": "35fc06a4-a401-44e3-ed83-bc3d172c9ee5"
      },
      "source": [
        "#Connect to Google Drive\n",
        "from google.colab import drive\n",
        "drive.mount('/content/drive')\n",
        "print('Authenticated')"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Mounted at /content/drive\n",
            "Authenticated\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "-n99kwqBV7vn",
        "outputId": "4d29c03c-4111-4946-fa4e-b004a5a92c0a"
      },
      "source": [
        "#Connect to Google BigQuery\n",
        "from google.cloud import bigquery\n",
        "client = bigquery.Client(project=PROJECT_ID, location='US')\n",
        "dataset_ref = client.dataset(currency+'_UTXO', project=PROJECT_ID)\n",
        "dataset = client.get_dataset(dataset_ref)\n",
        "tables = list(client.list_tables(dataset))\n",
        "\n",
        "# Print names of all tables in the dataset\n",
        "for table in tables:  \n",
        "  print(table.table_id)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "joint_all\n",
            "joint_all_partitionedbyborn12\n",
            "joint_all_partitionedbydeath12\n",
            "joint_all_pbyborn\n",
            "joint_all_pbydeath\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "5bVwfAJHaEfx"
      },
      "source": [
        "# Query the latest data in BigQuery"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "zhwe8Oh1Vv7w"
      },
      "source": [
        "## Creating a Table for variables of interest"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "8tFz0Q1cVyR-",
        "outputId": "058c1d3a-af2e-4011-f02d-fb273526a4b6"
      },
      "source": [
        "#Create joint_all\n",
        "\n",
        "table_id_inputs = \"crypto-291811.\" + currency + \"_UTXO.joint_all\"\n",
        "job_config = bigquery.QueryJobConfig(destination=table_id_inputs)\n",
        "job_config.write_disposition = \"WRITE_TRUNCATE\"\n",
        "\n",
        "sql = \"\"\"\n",
        "  SELECT\n",
        "    (outputs.value/POW(10,8)) AS UTXO,  \n",
        "    outputs.block_timestamp,\n",
        "    inputs.block_timestamp AS spent_block_timestamp,\n",
        "    #FORMAT_TIMESTAMP(\"%Y-%m-%d\", block_timestamp) AS block_date,\n",
        "    #FORMAT_TIMESTAMP(\"%Y-%m-%d\", spent_block_timestamp) AS spent_block_date,\n",
        "  FROM \n",
        "    `bigquery-public-data.crypto_\"\"\" + currency + \"\"\".outputs` AS outputs\n",
        "  LEFT JOIN \n",
        "    `bigquery-public-data.crypto_\"\"\" + currency + \"\"\".inputs` AS inputs\n",
        "  ON outputs.transaction_hash=inputs.spent_transaction_hash  \n",
        "  AND outputs.index = inputs.spent_output_index\n",
        "\"\"\"\n",
        "\n",
        "# Start the query, passing in the extra configuration.\n",
        "query_job_inputs = client.query(sql, job_config=job_config)  # Make an API request.\n",
        "query_job_inputs.result()  # Wait for the job to complete.\n",
        "\n",
        "\n",
        "print(\"Query results loaded to the table {}\".format(table_id_inputs))"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Query results loaded to the table crypto-291811.zcash_UTXO.joint_all\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "hog7UNQX3TEM"
      },
      "source": [
        "## Create partitioned tables"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "lPtoUbqf3Ssv",
        "outputId": "6bcd0ee7-5f05-49a3-e574-28ae160f887a"
      },
      "source": [
        "#Partition Table by born date for data after 2012\n",
        "\n",
        "job_config = bigquery.QueryJobConfig()\n",
        "sql = \"\"\"\n",
        "  CREATE OR REPLACE TABLE\n",
        "    `crypto-291811.\"\"\" + currency + \"\"\"_UTXO.joint_all_partitionedbyborn12`\n",
        "  PARTITION BY\n",
        "    DATE(block_timestamp) AS\n",
        "  SELECT\n",
        "    *\n",
        "  FROM\n",
        "    `crypto-291811.\"\"\" + currency + \"\"\"_UTXO.joint_all`\n",
        "  WHERE\n",
        "    block_timestamp > TIMESTAMP('2012-01-01 00:00:00+00')\n",
        "\"\"\"\n",
        "\n",
        "# Start the query, passing in the extra configuration.\n",
        "query_job_inputs = client.query(sql, job_config=job_config)  # Make an API request.\n",
        "query_job_inputs.result()  # Wait for the job to complete.\n",
        "\n",
        "print(\"Query results loaded to the table {}\".format(table_id_inputs))"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Query results loaded to the table crypto-291811.zcash_UTXO.joint_all\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "udq-ra9y3e6s",
        "outputId": "ce227b7f-d82d-49ea-8d9f-b7442427bacb"
      },
      "source": [
        "#Partition by death date for data after 2012\n",
        "\n",
        "job_config = bigquery.QueryJobConfig()\n",
        "\n",
        "sql = \"\"\"\n",
        "  CREATE OR REPLACE TABLE\n",
        "    `crypto-291811.\"\"\" + currency + \"\"\"_UTXO.joint_all_partitionedbydeath12`\n",
        "  PARTITION BY\n",
        "    DATE(spent_block_timestamp) AS\n",
        "  SELECT\n",
        "    *\n",
        "  FROM\n",
        "    `crypto-291811.\"\"\" + currency + \"\"\"_UTXO.joint_all`\n",
        "  WHERE\n",
        "    (spent_block_timestamp > TIMESTAMP('2012-01-01 00:00:00+00')\n",
        "    OR \n",
        "    spent_block_timestamp IS NULL)\n",
        "\"\"\"\n",
        "\n",
        "# Start the query, passing in the extra configuration.\n",
        "query_job_inputs = client.query(sql, job_config=job_config)  # Make an API request.\n",
        "query_job_inputs.result()  # Wait for the job to complete.\n",
        "\n",
        "print(\"Query results loaded to the table {}\".format(table_id_inputs))"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Query results loaded to the table crypto-291811.zcash_UTXO.joint_all\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Zkhi4SjO9Yoj"
      },
      "source": [
        "# Process the Data"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "G4qrxW8Y5p9v"
      },
      "source": [
        "## Define Functions"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "TEzTYHCp3pe7"
      },
      "source": [
        "def cal(x):\n",
        "    t=np.sign(x-0.999)+np.sign(x-29.999)+np.sign(x-90.999)+np.sign(x-181.999)+np.sign(x-364.999)+np.sign(x-365*2+0.001)+np.sign(x-365*3+0.001)+np.sign(x-365*4+0.001)+np.sign(x-365*5+0.001)+np.sign(x-365*10+0.001)+1\n",
        "    return t\n",
        "\n",
        "def Task1_born(data):\n",
        "    newborn = data['UTXO'].sum()\n",
        "    return(newborn)\n",
        "\n",
        "#Partitioning By Death Date\n",
        "def Task1_dead(data):\n",
        "    dead = data['UTXO'].sum()\n",
        "    return(dead)\n",
        "\n",
        "def Task2(data):\n",
        "    #data['Life_Length'] = data['spent_block_timestamp']- data['block_timestamp']\n",
        "    #data['Life_Length'] = data['Life_Length'].map(lambda x:x.days).apply(float)\n",
        "    sumUTXO = data['UTXO'].sum()\n",
        "    sumLength = (data['UTXO']*data['Life_Length']).sum()\n",
        "    if sumUTXO == 0:\n",
        "        WALE = 0.0\n",
        "    else:\n",
        "        WALE = sumLength/sumUTXO\n",
        "    return(WALE)\n",
        "def Task3(data):\n",
        "    data['Life_Length'] = data['spent_block_timestamp']- data['block_timestamp']\n",
        "    data['Life_Length'] = data['Life_Length'].map(lambda x:x.days).apply(float)\n",
        "    data['categorical'] = cal(data['Life_Length'])\n",
        "    categories = [-9, -7, -5, -3, -1, 1, 3, 5, 7, 9, 11]\n",
        "    result=pd.DataFrame(np.zeros((1, 11)), columns=categories)\n",
        "    for i in categories:  \n",
        "        result.loc[:,i] = data[data['categorical']==i]['UTXO'].sum()  \n",
        "    return result\n",
        "\n",
        "def Task4(data, date):  \n",
        "    categories = [-9, -7, -5, -3, -1, 1, 3, 5, 7, 9, 11]\n",
        "    result=pd.DataFrame(np.zeros((1, 11)), columns=categories)\n",
        "    if len(data)!= 0:\n",
        "      data['Age'] = data['block_timestamp'].apply(lambda x: (date-x).days)\n",
        "      data['categorical'] = cal(data['Age'])\n",
        "      for i in categories: \n",
        "        result.loc[:,i] = data[data['categorical']==i]['UTXO'].sum()\n",
        "    return result"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3ARnMOA3WrSc"
      },
      "source": [
        "## Defining the Processing Programs"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "dxBbfG-9XS0n"
      },
      "source": [
        "def STXOprogram(start, end):\n",
        "  duration=pd.date_range(start=start, end=end)\n",
        "  days = np.size(duration)\n",
        "  categories = [-9, -7, -5, -3, -1, 1, 3, 5, 7, 9, 11]\n",
        "  Result=pd.DataFrame(np.zeros((days, 11)), columns=categories)\n",
        "  Result['date'] = duration\n",
        "\n",
        "  for i in range(0, days):\n",
        "    start_date = start + timedelta(days=i)\n",
        "    end_date = start_date + timedelta(days=1)\n",
        "    \n",
        "  #Partitioning by Dead Date\n",
        "    query2 = \"\"\"\n",
        "          SELECT \n",
        "            *\n",
        "          FROM \n",
        "            `crypto-291811.\"\"\" + currency + \"\"\"_UTXO.joint_all_partitionedbydeath12`\n",
        "          WHERE\n",
        "            spent_block_timestamp >= TIMESTAMP('\"\"\" + str(start_date) + \"\"\" 00:00:00+00')\n",
        "          AND \n",
        "            spent_block_timestamp < TIMESTAMP('\"\"\" + str(end_date) + \"\"\" 00:00:00+00')\"\"\"\n",
        "    query_job2 = client.query(query2)\n",
        "    # Make an API request  to run the query and return a pandas DataFrame\n",
        "    data2 = query_job2.to_dataframe()  \n",
        "    \n",
        "    #Work on Task3\n",
        "    Result.iloc[i,0:11]=list(Task3(data2).iloc[0])\n",
        "    \n",
        "  #Partitioning by Born Date\n",
        "    query1 = \"\"\"\n",
        "          SELECT \n",
        "            *\n",
        "          FROM \n",
        "            `crypto-291811.\"\"\" + currency + \"\"\"_UTXO.joint_all_partitionedbyborn12`\n",
        "          WHERE\n",
        "            block_timestamp >= TIMESTAMP('\"\"\" + str(start_date) + \"\"\" 00:00:00+00')\n",
        "          AND \n",
        "            block_timestamp < TIMESTAMP('\"\"\" + str(end_date) + \"\"\" 00:00:00+00')\"\"\"\n",
        "    query_job1 = client.query(query1)\n",
        "    # Make an API request  to run the query and return a pandas DataFrame\n",
        "    data1 = query_job1.to_dataframe()\n",
        "    \n",
        "    #Work on Task 1 and Task 2\n",
        "    Result.loc[i,'newborn'] = Task1_born(data1)\n",
        "    Result.loc[i,'dead'] = Task1_dead(data2)\n",
        "    Result.loc[i,'WALE'] = Task2(data2)\n",
        "    Result.columns = ['-9', '-7', '-5', '-3', '-1', '1', '3', '5', '7', '9', '11', 'date', 'newborn', 'dead', 'WALE']\n",
        "  return Result"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "SB4oynVzYl07"
      },
      "source": [
        "def UTXOprogram(start, end):\n",
        "  duration=pd.date_range(start=start, end=end)\n",
        "  days = np.size(duration)\n",
        "  categories = [-9, -7, -5, -3, -1, 1, 3, 5, 7, 9, 11]\n",
        "  Dist_Alive=pd.DataFrame(np.zeros((days, 11)), columns=categories)\n",
        "  Dist_Alive['date'] = duration\n",
        "  start_date=start+timedelta(days=1) \n",
        "  end_date =end+timedelta(days=1) \n",
        "  # note the trick below, we only keep data whose block_timestamp<end_date, and spent_block_timestamp>start_date\n",
        "  #must be from joint_all\n",
        "  query = \"\"\"\n",
        "      SELECT \n",
        "        *\n",
        "      FROM \n",
        "        `crypto-291811.\"\"\" + currency + \"\"\"_UTXO.joint_all_partitionedbydeath12`\n",
        "      WHERE\n",
        "        block_timestamp < TIMESTAMP('\"\"\" + str(end_date) + \"\"\" 00:00:00+00')\n",
        "      AND \n",
        "        (spent_block_timestamp >= TIMESTAMP('\"\"\" + str(start_date) + \"\"\" 00:00:00+00')\n",
        "        OR\n",
        "        spent_block_timestamp IS NULL)\n",
        "     \"\"\"\n",
        "  query_job = client.query(query)\n",
        "\n",
        "# Make an API request  to run the query and return a pandas DataFrame\n",
        "  data = query_job.to_dataframe()\n",
        "  data['block_timestamp'] = pd.to_datetime(data['block_timestamp'], format='%Y-%m-%d')\n",
        "  data['spent_block_timestamp'] = pd.to_datetime(data['spent_block_timestamp'].fillna(pd.NaT), format='%Y-%m-%d')\n",
        "  for j in range(0, days):\n",
        "    working_date = pd.to_datetime(start_date + timedelta(days=j), utc=True)   \n",
        "    working_data = data.loc[((data.block_timestamp<working_date) & ((pd.isna(data.spent_block_timestamp) | (data.spent_block_timestamp>=working_date))))].copy()\n",
        "    Dist_Alive.iloc[j,0:11] = list(Task4(working_data, working_date).iloc[0])\n",
        "\n",
        "  return Dist_Alive"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-_r0juK9_FcR"
      },
      "source": [
        "## STXO Program"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "1DMrww7k_HWz"
      },
      "source": [
        "STXOresult = STXOprogram(start, end)\n",
        "\n",
        "oldaddress = '/content/drive/My Drive/UTXO/' +currency+ 'ResultSTXO' +str(last_update)+ '.csv'\n",
        "oldSTXOresult = pd.read_csv(oldaddress)\n",
        "oldSTXOresult = oldSTXOresult.drop(['Unnamed: 0'], axis = 1)\n",
        "newSTXOresult = oldSTXOresult.append(STXOresult)\n",
        "newaddress = '/content/drive/My Drive/UTXO/' +currency+ 'ResultSTXO' +str(end)+ '.csv'\n",
        "newSTXOresult.to_csv(newaddress)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "sJKx4FFyWZlG"
      },
      "source": [
        "## UTXO Program"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "kwRxsSKmbDAj"
      },
      "source": [
        "UTXOresult = UTXOprogram(start, end)\n",
        "\n",
        "oldaddress = '/content/drive/My Drive/UTXO/' +currency+ 'ResultUTXO' +str(last_update)+ '.csv'\n",
        "oldUTXOresult = pd.read_csv(oldaddress)\n",
        "oldUTXOresult = oldUTXOresult.drop(['Unnamed: 0'], axis = 1)\n",
        "UTXOresult.columns = ['-9', '-7', '-5', '-3', '-1', '1', '3', '5', '7', '9', '11', 'date']\n",
        "UTXOresult = UTXOresult[['date', '-9', '-7', '-5', '-3', '-1', '1', '3', '5', '7', '9', '11']]\n",
        "newUTXOresult = oldUTXOresult.append(UTXOresult)\n",
        "newUTXOresult = newUTXOresult.reset_index(drop = True)\n",
        "duration=pd.date_range(start= end-timedelta(days=len(newUTXOresult)-1), end=end)\n",
        "newUTXOresult['date'] = duration\n",
        "newaddress = '/content/drive/My Drive/UTXO/' +currency+ 'ResultUTXO' +str(end)+ '.csv'\n",
        "newUTXOresult.to_csv(newaddress)"
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}