Search
4.1 Transactions

The plot on the bottom of this page provides visualization of the total number of transactions that have occurred 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 daily transactions for Bitcoin

query_job = client.query("""
 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(*) as TotalNoTransactions
FROM `intricate-yew-283611.Blockchain.Bitcoin_Transactions`
GROUP BY Year,Month, Day, Datum
Order By Year, Month, Day
 
 """)
results = query_job.result()

bitcoin_df = results.to_dataframe()
print(bitcoin_df)

      Day  Month  Year      Datum  TotalNoTransactions
0       3      1  2009   2009-1-3                    1
1       9      1  2009   2009-1-9                   14
2      10      1  2009  2009-1-10                   61
3      11      1  2009  2009-1-11                   93
4      12      1  2009  2009-1-12                  101
...   ...    ...   ...        ...                  ...
4204   13      7  2020  2020-7-13               310227
4205   14      7  2020  2020-7-14               359847
4206   15      7  2020  2020-7-15               316064
4207   16      7  2020  2020-7-16               345849
4208   17      7  2020  2020-7-17               215793

[4209 rows x 5 columns]

  • Bitcoin Cash

    The following query (click the toggle button to expand the code) outputs the number of daily transactions for Bitcoin Cash

query_job1 = client.query("""
 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(*) as TotalNoTransactions
FROM `intricate-yew-283611.Blockchain.Cash_Transactions`
GROUP BY Year,Month, Day, Datum
Order By Year, Month, Day
 
 """)
results_cash = query_job1.result()

cash_df = results_cash.to_dataframe()
print(cash_df)

      Day  Month  Year      Datum  TotalNoTransactions
0       1      8  2017   2017-8-1                76626
1       2      8  2017   2017-8-2                 7423
2       3      8  2017   2017-8-3                20944
3       4      8  2017   2017-8-4                26536
4       5      8  2017   2017-8-5                15207
...   ...    ...   ...        ...                  ...
1077   13      7  2020  2020-7-13                15976
1078   14      7  2020  2020-7-14                17389
1079   15      7  2020  2020-7-15                15482
1080   16      7  2020  2020-7-16                14520
1081   17      7  2020  2020-7-17                 8760

[1082 rows x 5 columns]

  • Litecoin

    The following plot (click the toggle button to expand the code) outputs the number of daily transactions for Litecoin

query_job2 = client.query("""
 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(*) as TotalNoTransactions
FROM `intricate-yew-283611.Blockchain.Litecoin_Transactions`
GROUP BY Year,Month, Day, Datum
Order By Year, Month, Day
 
 """)
results_lite = query_job2.result()

lite_df = results_lite.to_dataframe()
print(lite_df)

      Day  Month  Year       Datum  TotalNoTransactions
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                 9774
4      14     10  2011  2011-10-14                 1184
...   ...    ...   ...         ...                  ...
3199   13      7  2020   2020-7-13                36640
3200   14      7  2020   2020-7-14                37498
3201   15      7  2020   2020-7-15                37433
3202   16      7  2020   2020-7-16                38719
3203   17      7  2020   2020-7-17                29875

[3204 rows x 5 columns]

Plot 

fig = go.Figure()

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

fig.update_layout(paper_bgcolor = 'White',xaxis_title="Date",
    yaxis_title='Number of Transactions',plot_bgcolor="#FFFFFF",    hovermode="x",
    hoverdistance=100, 
    spikedistance=1000, 
)
fig.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")
        ])
    ))
fig.update_yaxes(showgrid=False, ticks = 'outside', showline=True, linecolor='black')
plot(fig, filename = 'fig.html', config = config)

#ThebeLab
display(HTML('fig.html'))