{ "metadata": { "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.5-final" }, "orig_nbformat": 2, "kernelspec": { "name": "python_defaultSpec_1601105039393", "display_name": "Python 3.8.5 64-bit ('bigquery': conda)", "metadata": { "interpreter": { "hash": "340b761486247bce2d7ad12b98c41ce8b51a3bb9007defa1bd9300181d8d0901" } } } }, "nbformat": 4, "nbformat_minor": 2, "cells": [ { "source": [ "# Plotting Ethereum transaction value and gas prices with BigQuery and Altair" ], "cell_type": "markdown", "metadata": {} }, { "source": [ "As part of getting a better handle on blockchain data, BigQuery, Altair, and Machine Learning, I pulled some Ethereum transaction data and plotted it." ], "cell_type": "markdown", "metadata": {} }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "import os\n", "os.environ[\"GOOGLE_APPLICATION_CREDENTIALS\"]=os.path.expanduser(\"~/.credentials/Notebook bigquery-c422e406404b.json\")" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [ { "output_type": "execute_result", "data": { "text/plain": "DataTransformerRegistry.enable('default')" }, "metadata": {}, "execution_count": 2 } ], "source": [ "import altair as alt\n", "alt.data_transformers.disable_max_rows()" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [], "source": [ "from google.cloud import bigquery\n", "client = bigquery.Client()" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [], "source": [ "query =\"\"\"\n", "SELECT\n", " EXTRACT(DATE FROM block_timestamp) AS date,\n", " AVG(value) AS average_value,\n", " AVG(gas_price) AS average_gas_price, \n", "FROM `bigquery-public-data.ethereum_blockchain.transactions`\n", "WHERE\n", " EXTRACT(YEAR FROM block_timestamp) = 2019\n", "GROUP BY date\n", "ORDER BY date\n", "\"\"\"" ] }, { "source": [ "We calculate some basic statistics on raw transaction value data for each day over 2019." ], "cell_type": "markdown", "metadata": {} }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "output_type": "execute_result", "data": { "text/plain": "[SchemaField('hash', 'STRING', 'REQUIRED', 'Hash of the transaction', (), None),\n SchemaField('nonce', 'INTEGER', 'REQUIRED', 'The number of transactions made by the sender prior to this one', (), None),\n SchemaField('transaction_index', 'INTEGER', 'REQUIRED', 'Integer of the transactions index position in the block', (), None),\n SchemaField('from_address', 'STRING', 'REQUIRED', 'Address of the sender', (), None),\n SchemaField('to_address', 'STRING', 'NULLABLE', 'Address of the receiver. null when its a contract creation transaction', (), None),\n SchemaField('value', 'NUMERIC', 'NULLABLE', 'Value transferred in Wei', (), None),\n SchemaField('gas', 'INTEGER', 'NULLABLE', 'Gas provided by the sender', (), None),\n SchemaField('gas_price', 'INTEGER', 'NULLABLE', 'Gas price provided by the sender in Wei', (), None),\n SchemaField('input', 'STRING', 'NULLABLE', 'The data sent along with the transaction', (), None),\n SchemaField('receipt_cumulative_gas_used', 'INTEGER', 'NULLABLE', 'The total amount of gas used when this transaction was executed in the block', (), None),\n SchemaField('receipt_gas_used', 'INTEGER', 'NULLABLE', 'The amount of gas used by this specific transaction alone', (), None),\n SchemaField('receipt_contract_address', 'STRING', 'NULLABLE', 'The contract address created, if the transaction was a contract creation, otherwise null', (), None),\n SchemaField('receipt_root', 'STRING', 'NULLABLE', '32 bytes of post-transaction stateroot (pre Byzantium)', (), None),\n SchemaField('receipt_status', 'INTEGER', 'NULLABLE', 'Either 1 (success) or 0 (failure) (post Byzantium)', (), None),\n SchemaField('block_timestamp', 'TIMESTAMP', 'REQUIRED', 'Timestamp of the block where this transaction was in', (), None),\n SchemaField('block_number', 'INTEGER', 'REQUIRED', 'Block number where this transaction was in', (), None),\n SchemaField('block_hash', 'STRING', 'REQUIRED', 'Hash of the block where this transaction was in', (), None)]" }, "metadata": {}, "execution_count": 5 } ], "source": [ "schema = client.get_table(\"bigquery-public-data.ethereum_blockchain.transactions\").schema\n", "schema" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "output_type": "execute_result", "data": { "text/plain": " date average_value average_gas_price\n0 2019-01-01 3.719103e+18 1.431514e+10\n1 2019-01-02 4.649915e+18 1.349952e+10\n2 2019-01-03 4.188781e+18 1.269504e+10\n3 2019-01-04 6.958368e+18 1.418197e+10\n4 2019-01-05 8.167590e+18 2.410475e+10", "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
dateaverage_valueaverage_gas_price
02019-01-013.719103e+181.431514e+10
12019-01-024.649915e+181.349952e+10
22019-01-034.188781e+181.269504e+10
32019-01-046.958368e+181.418197e+10
42019-01-058.167590e+182.410475e+10
\n
" }, "metadata": {}, "execution_count": 6 } ], "source": [ "values = client.query(query).to_dataframe(dtypes={'average_value': float, 'average_gas_price': float}, date_as_object=False)\n", "values.head()" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "output_type": "execute_result", "data": { "text/html": "\n
\n", "text/plain": "alt.LayerChart(...)" }, "metadata": {}, "execution_count": 7 } ], "source": [ "chart = alt.Chart(values).mark_line().encode(\n", " alt.X('date:T', axis=alt.Axis(format=(\"%x\"), labelAngle=270))\n", ").properties(width=600)\n", "\n", "\n", "alt.layer(\n", " chart.encode(alt.Y('average_value:Q', axis=alt.Axis(format=\",.2e\")), color=alt.value('darkred'), opacity=alt.value(0.65)),\n", " chart.encode(alt.Y('average_gas_price', axis=alt.Axis(format=\",.2e\")))\n", ").resolve_scale(y='independent')\n" ] } ] }