4.3 Active Addresses

The plot on the bottom of this page provides visualization of the total number of addresses that have been active on a daily basis for each of the three cryptocurrencies.

The whole process of acquiring the data is shown bellow. Clicking the toggle buttons expands each part of the code.

from google.cloud import bigquery
from google.oauth2 import service_account
import plotly.graph_objects as go
import plotly.tools as tls

from plotly.offline import plot, iplot, init_notebook_mode
from IPython.core.display import display, HTML
init_notebook_mode(connected = True)
config={'showLink': False, 'displayModeBar': False}

Aquiring the data

The data is obtained using the Google's BigQuery cloud-based warehouse.

credentials = service_account.Credentials.from_service_account_file(
    'cred.json')
project_id = 'intricate-yew-283611'
client = bigquery.Client(credentials= credentials,project=project_id)

Bitcoin

The following query (click the toggle button to expand the code) outputs the number of active Bitcoin addresses for each day

query_job1 = client.query("""
with cte as(SELECT  block_timestamp, ARRAY_TO_STRING(addresses, ',') as addresses
FROM `bigquery-public-data.crypto_bitcoin.transactions`,UNNEST(outputs) as ins
UNION ALL
SELECT  block_timestamp, ARRAY_TO_STRING(addresses, ',') as addresses
FROM `bigquery-public-data.crypto_bitcoin.transactions`,UNNEST(inputs) as ins)

select EXTRACT(DAY FROM block_timestamp) as Day,
EXTRACT(MONTH FROM block_timestamp) as Month ,
EXTRACT(YEAR FROM block_timestamp) as Year,
concat(Cast(EXTRACT(YEAR FROM block_timestamp) as String),'-',Cast(EXTRACT(MONTH FROM block_timestamp) as String),'-',Cast(EXTRACT(DAY FROM block_timestamp) as String)) as Datum,
COUNT(DISTINCT(addresses)) as distinct_addresses
from cte
group by Year,Month, Day, Datum
order by Year, Month, Day
 
 """)
results1 = query_job1.result()

bitcoin_df = results1.to_dataframe()
print(bitcoin_df)

      Day  Month  Year       Datum  distinct_addresses
0       3      1  2009  2009-01-03                   1
1       9      1  2009  2009-01-09                  14
2      10      1  2009  2009-01-10                  61
3      11      1  2009  2009-01-11                  93
4      12      1  2009  2009-01-12                 102
...   ...    ...   ...         ...                 ...
4205   14      7  2020  2020-07-14             1008030
4206   15      7  2020  2020-07-15              972945
4207   16      7  2020  2020-07-16              974469
4208   17      7  2020  2020-07-17              858601
4209   18      7  2020  2020-07-18              514853

[4210 rows x 5 columns]

Bitcoin Cash

The following query (click the toggle button to expand the code) outputs the number of active Bitcoin Cash addresses for each day

query_job2 = client.query("""
with cte as(SELECT  block_timestamp, ARRAY_TO_STRING(addresses, ',') as addresses
FROM `bigquery-public-data.crypto_bitcoin_cash.transactions`,UNNEST(outputs) as ins
UNION ALL
SELECT  block_timestamp, ARRAY_TO_STRING(addresses, ',') as addresses
FROM `bigquery-public-data.crypto_bitcoin_cash.transactions`,UNNEST(inputs) as ins)

select EXTRACT(DAY FROM block_timestamp) as Day,
EXTRACT(MONTH FROM block_timestamp) as Month ,
EXTRACT(YEAR FROM block_timestamp) as Year,
concat(Cast(EXTRACT(YEAR FROM block_timestamp) as String),'-',Cast(EXTRACT(MONTH FROM block_timestamp) as String),'-',Cast(EXTRACT(DAY FROM block_timestamp) as String)) as Datum,
COUNT(DISTINCT(addresses)) as distinct_addresses
from cte
group by Year,Month, Day, Datum
order by Year, Month, Day
 
 """)
results2 = query_job2.result()

cash_df = results2.to_dataframe()
print(cash_df)

      Day  Month  Year       Datum  distinct_addresses
0       1      8  2017  2017-08-01              305538
1       2      8  2017  2017-08-02               31224
2       3      8  2017  2017-08-03               73261
3       4      8  2017  2017-08-04               88173
4       5      8  2017  2017-08-05               53859
...   ...    ...   ...         ...                 ...
1078   14      7  2020  2020-07-14               95582
1079   15      7  2020  2020-07-15               64146
1080   16      7  2020  2020-07-16               67501
1081   17      7  2020  2020-07-17               62183
1082   18      7  2020  2020-07-18               43498

[1083 rows x 5 columns]

Litecoin

The following query (click the toggle button to expand the code) outputs the number of active Litecoin addresses for each day

query_job = client.query("""
with cte as(SELECT  block_timestamp, ARRAY_TO_STRING(addresses, ',') as addresses
FROM `bigquery-public-data.crypto_litecoin.transactions`,UNNEST(outputs) as ins
UNION ALL
SELECT  block_timestamp, ARRAY_TO_STRING(addresses, ',') as addresses
FROM `bigquery-public-data.crypto_litecoin.transactions`,UNNEST(inputs) as ins)

select EXTRACT(DAY FROM block_timestamp) as Day,
EXTRACT(MONTH FROM block_timestamp) as Month ,
EXTRACT(YEAR FROM block_timestamp) as Year,
concat(Cast(EXTRACT(YEAR FROM block_timestamp) as String),'-',Cast(EXTRACT(MONTH FROM block_timestamp) as String),'-',Cast(EXTRACT(DAY FROM block_timestamp) as String)) as Datum,
COUNT(DISTINCT(addresses)) as distinct_addresses
from cte
group by Year,Month, Day, Datum
order by Year, Month, Day
 
 """)
results = query_job.result()

lite_df = results.to_dataframe()
print(lite_df)

      Day  Month  Year       Datum  distinct_addresses
0       7     10  2011   2011-10-7                   1
1       8     10  2011   2011-10-8                   1
2      12     10  2011  2011-10-12                   1
3      13     10  2011  2011-10-13                9775
4      14     10  2011  2011-10-14                1530
...   ...    ...   ...         ...                 ...
3200   14      7  2020   2020-7-14              100687
3201   15      7  2020   2020-7-15               87232
3202   16      7  2020   2020-7-16               83412
3203   17      7  2020   2020-7-17               83708
3204   18      7  2020   2020-7-18               43068

[3205 rows x 5 columns]

Plot

fig3 = go.Figure()

fig3.add_trace(go.Scatter(x = bitcoin_df['Datum'], y = bitcoin_df['distinct_addresses'],name = 'Bitcoin'))
fig3.add_trace(go.Scatter(x = cash_df['Datum'], y = cash_df['distinct_addresses'], name = 'Bitcoin Cash'))
fig3.add_trace(go.Scatter(x = lite_df['Datum'], y = lite_df['distinct_addresses'], name = 'Litecoin', line = dict(color = 'Light Green')))


fig3.update_layout(paper_bgcolor = 'White',xaxis_title="Date",
    yaxis_title='Number of active addresses',plot_bgcolor="#FFFFFF",    hovermode="x",
    hoverdistance=100, 
    spikedistance=1000, 
)
fig3.update_xaxes(showspikes=True,spikethickness=2,
        spikedash="dot",
        spikecolor="#999999",
        spikemode="across",showgrid=False,ticks = 'outside', showline=True, linecolor='black',rangeslider_visible=True,
                rangeselector=dict(
        buttons=list([
            dict(count=1, label="1m", step="month", stepmode="backward"),
            dict(count=6, label="6m", step="month", stepmode="backward"),
            dict(count=1, label="YTD", step="year", stepmode="todate"),
            dict(count=1, label="1y", step="year", stepmode="backward"),
            dict(step="all")
        ])
    ))
fig3.update_yaxes(autorange = True, fixedrange= False,showgrid=False, ticks = 'outside', showline=True, linecolor='black')



plot(fig3, filename = 'fig3.html', config = config)
#ThebeLab
display(HTML('fig3.html'))