#!/usr/bin/env python
# coding: utf-8

# # Plotting Ethereum transaction value and gas prices with BigQuery and Altair

# As part of getting a better handle on blockchain data, BigQuery, Altair, and Machine Learning, I pulled some Ethereum transaction data and plotted it.

# In[1]:


import os
os.environ["GOOGLE_APPLICATION_CREDENTIALS"]=os.path.expanduser("~/.credentials/Notebook bigquery-c422e406404b.json")


# In[2]:


import altair as alt
alt.data_transformers.disable_max_rows()


# In[3]:


from google.cloud import bigquery
client = bigquery.Client()


# In[4]:


query ="""
SELECT
    EXTRACT(DATE FROM block_timestamp) AS date,
    AVG(value) AS average_value,
    AVG(gas_price) AS average_gas_price,    
FROM `bigquery-public-data.ethereum_blockchain.transactions`
WHERE
    EXTRACT(YEAR FROM block_timestamp) = 2019
GROUP BY date
ORDER BY date
"""


# We calculate some basic statistics on raw transaction value data for each day over 2019.

# In[5]:


schema = client.get_table("bigquery-public-data.ethereum_blockchain.transactions").schema
schema


# In[6]:


values = client.query(query).to_dataframe(dtypes={'average_value': float, 'average_gas_price': float}, date_as_object=False)
values.head()


# In[7]:


chart = alt.Chart(values).mark_line().encode(
    alt.X('date:T', axis=alt.Axis(format=("%x"), labelAngle=270))
).properties(width=600)


alt.layer(
    chart.encode(alt.Y('average_value:Q', axis=alt.Axis(format=",.2e")), color=alt.value('darkred'), opacity=alt.value(0.65)),
    chart.encode(alt.Y('average_gas_price', axis=alt.Axis(format=",.2e")))
).resolve_scale(y='independent')