The number of unconfirmed transactions on the Bitcoin blockchain spiked in May 2017. Attempting to identify why led to an analysis of several key performance metrics for the network
Table of Contents¶
from IPython.display import HTML
HTML('''<script>
code_show=false;
function code_toggle() {
if (code_show){
$('div.input').show();
$('div.output_prompt').show();
} else {
$('div.input').hide();
$('div.output_prompt').hide();
}
code_show =! code_show
}
$( document ).ready(code_toggle);
</script>
<font> This analysis was made using Python. If you'd like to see the code used, click <a href="javascript:code_toggle()">here</a>.</font>
''')
## Steup - libraries
import quandl
import matplotlib.pyplot as plt
import pandas as pd
import datetime as dt
import numpy as np
import credentials # keep my quandl and plot.ly api keys private
import plotly.plotly as py
import plotly
#from plotly.graph_objs import Scatter, Layout
import plotly.graph_objs as go
#from plotly.graph_objs import *
## Setup - appearance
# get rid of the annoying 'SettingWithCopy' warning
pd.options.mode.chained_assignment = None # default='warn'
# more than one print of an unassigned variable
from IPython.core.interactiveshell import InteractiveShell;
InteractiveShell.ast_node_interactivity = "all";
# offline plotly
plotly.offline.init_notebook_mode()
color1 = '#137a28' # dark green
color2 = '#b3d1b9' # light transparent green
Unconfirmed transactions¶
# import mempool data downloaded from https://blockchain.info/charts/mempool-count?timespan=all
mempool = pd.read_csv(
'mempool-count.csv',
header=None,
names=['DateTime','size'],
parse_dates=[0],
infer_datetime_format=True
)
# split the datetime to date and time
temp = pd.DatetimeIndex(mempool['DateTime'])
mempool['Date'] = temp.date
mempool['Time'] = temp.time
del mempool['DateTime']
# reorder the columns
cols = ['Date','Time','size']
mempool = mempool[cols]
mempool.set_index('Date', inplace=True)
d2 = pd.to_datetime('2017-07-31').date()
mempool = pd.concat([mempool.loc[:d2]])
# there are 3 values per day. get average mempool size for each day
mempool = mempool.groupby([mempool.index.get_level_values('Date')]).mean()
The number of transactions waiting to be confirmed on the Bitcoin blockchain increased to an all time maximum on May 18th of 175,978. For comparison, the average value in 2016 was less than 10,000.
Once the the number of unconfirmed transactions had peaked, it fell about as quickly as it rose and by mid July was generally below 10,000 again.
The current state of the unconfirmed transaction pool along with the fee rates currently offered can be seen here.
mempool['size_av']=mempool['size'].rolling(window=7).mean();
series1 = go.Scatter(
x=mempool.index.get_level_values('Date'),
y=mempool['size'],
name='Daily average',
line = dict(
color = (color2),
width = 2,))
series2 = go.Scatter(
x=mempool.index.get_level_values('Date'),
y=mempool['size_av'],
name='Weekly average',
line = dict(
color = (color1),
width = 3,))
data = [series1, series2]
layout = go.Layout(
title='Unconfirmed transactions',
yaxis=dict(title='Number of transactions'),
legend=dict(orientation="h", yanchor='top', y=1, xanchor='center', x=0.5)
)
fig = go.Figure(data=data, layout=layout)
#plotly.offline.iplot(fig, filename='bitcoin_mempool')
py.iplot(fig, filename='bitcoin_mempool')
Median transaction confirmation time (minutes)¶
I would expect that the average time taken to confirm a transaction will increase with the size of the unconfirmed transaction pool. The figure below shows the median time in minutes for a new transaction to be confirmed.
# The Daily Median time taken for transactions to be accepted into a block, presumably in minutes
ATRCT = quandl.get("BCHAIN/ATRCT")
ATRCT = ATRCT.loc['2016-04-24':'2017-07-31']
ATRCT['artct_av']=ATRCT['Value'].rolling(window=7).mean();
series1 = go.Scatter(
x=mempool.index.get_level_values('Date'),
y=ATRCT['Value'],
name='Daily median',
line = dict(
color = (color2),
width = 2))
series2 = go.Scatter(
x=mempool.index.get_level_values('Date'),
y=ATRCT['artct_av'],
name='7 day average',
line = dict(
color = (color1),
width = 3))
data = [series1, series2]
layout = go.Layout(
title='Median time taken for transactions to be accepted into a block',
yaxis=dict(title='Time (minutes)'),
legend=dict(orientation="h", yanchor='top', y=1.1, xanchor='center', x=0.5)
)
fig = go.Figure(data=data, layout=layout)
#plotly.offline.iplot(fig, filename='btc_acceptance_time')
py.iplot(fig, filename='btc_acceptance_time')
The median transaction confirmation time does not increase noticeably when the pool of unconfirmed transactions increases, in fact the two features have only a weak Pearson correlation of 0.37 (details below). This is surprising because I expected that the time taken to confirm a transaction would increases when the pool of transactions waiting to be confirmed increases.
Perhaps this is because only valid transactions can be confirmed and included in the median average calculation, but invalid transactions are included in the pool of transactions awaiting confirmation. One way to test this would be to query the transactions awaiting confirmation and quantify if they are valid and what fee rate they are offering.
Average block size (daily, MB)¶
Each block in the Bitcoin network had a maximum size of 1MB before 1 August 2017. As the Bitcoin network has grown and transaction volume has increased the blocksize limit began to limit transaction volume.
Was the increase in unconfirmed transactions correlated to the blocks getting “filled up” to their maximum 1MB size?
# The Average block size in MB
AVBLS = quandl.get("BCHAIN/AVBLS")
av_bs = AVBLS.loc['2016-04-24':'2017-07-31']
av_bs['Size']=av_bs['Value']
del av_bs['Value']
av_bs['Size_av']=av_bs['Size'].rolling(window=7).mean();
series1 = go.Scatter(
x=mempool.index.get_level_values('Date'),
y=av_bs['Size'],
name='Average',
line = dict(
color = (color2),
width = 2))
series2 = go.Scatter(
x=mempool.index.get_level_values('Date'),
y=av_bs['Size_av'],
name='7 day average',
line = dict(
color = (color1),
width = 3))
data = [series1, series2]
layout = go.Layout(
title='Block size',
yaxis=dict(title='Block size (MB)'),
legend=dict(orientation="h", yanchor='top', y=1.1, xanchor='center', x=0.5)
)
fig = go.Figure(data=data, layout=layout)
#plotly.offline.iplot(fig, filename='btc_block_size')
py.iplot(fig, filename='btc_block_size')
From March through June the blocksizes seem to have frequently hit their maximum possible size, suggesting that the Bitcoin network was processing the maximum amount of data possible. The increase in unconfirmed transactions occurred from mid-April to end of June.
The average block size began a sharp decrease on July 2nd, and at the same time the median transaction confirmation time also began a quick reduction. By July 2nd the number of unconfirmed transactions had already fallen back to “normal” levels.
(Not all transactions are the same size, as a transaction can have any number of outputs and inputs, and a transaction with many inputs and/or outputs would be a larger amount of data than a transaction with only 1 input and 1 or 2 outputs.)
Lets confirm if the number of transactions increased over the same period:
Average number of transactions per (1MB) block¶
# The average number of transactions per block. each day?
NTRBL = quandl.get("BCHAIN/NTRBL")
NTRBL = NTRBL.loc['2016-04-24':'2017-07-31']
NTRBL['Size_av']=NTRBL['Value'].rolling(window=7).mean();
series1 = go.Scatter(
x=mempool.index.get_level_values('Date'),
y=NTRBL['Value'],
name='Average transactions per block',
line = dict(
color = (color2),
width = 2))
series2 = go.Scatter(
x=mempool.index.get_level_values('Date'),
y=NTRBL['Size_av'],
name='7 day average',
line = dict(
color = (color1),
width = 3))
data = [series1, series2]
layout = go.Layout(
title='Average number of transactions per block',
yaxis=dict(title='Number of transactions'),
legend=dict(orientation="h", yanchor='top', y=1.1, xanchor='center', x=0.5)
)
fig = go.Figure(data=data, layout=layout)
#plotly.offline.iplot(fig, filename='btc_tnsx_per_block')
py.iplot(fig, filename='btc_tnsx_per_block')
The average number of transactions per block hit a peak at the end of May 2017 and then saw two sharp declines. It fell quickly at the beginning of June and then again at the beginning of July.
In June the blocksizes remained more or less as large as possible which suggests the blocks were full of a few large transactions. At this time the size of the mempool was decreasing rapidly.
At the beginning of July the number of transactions per block reduced and the average blocksize was also rapidly reducing. This suggests that the volume of smaller transactions had reduced.
The difference in average blocksizes in early June and early July suggests that in early June the number of transactions reduced because the average size of transactions had increased, but in July the number of transactions per block reduced because fewer transactions were being created.
Perhaps Bitcoin exchanges and other organisations with high transaction volumes had changed their behaviour and begun posting larger transactions with many inputs and/or outputs, rather than posting many smaller transactions with fewer inputs and/or outputs.
Bitcoin is often held by speculators who expect the value of a Bitcoin to increase. Perhaps increases in transaction volume are correlated to increases in Bitcoins value.
Transaction fees earned by miners each day¶
Transaction fees are charged to users sending Bitcoin. Node operators (miners) collect unconfirmed transactions, confirm their validity and perform the proof-of-work requirements to submit these transactions as a new block of the blockchain.
In order to provide an incentive for node operators to process and confirm new transactions, and to compensate for the equipment and energy costs required to do so, a fee is charged to confirm each transaction. The size of the fee is proportional to the size (in bytes) of the transaction and is quantified as the fee rate (Satoshis/byte), otherwise miners would prefer smaller sized transactions as they could fit more into each block.
The pool of unconfirmed transactions is automatically sorted by transaction fee rate, so that miners confirm transactions with a higher fee rate before those with a lower fee rate.
Because of this, it is expected that as the number of unconfirmed transactions increases, the fees paid to ensure a transaction gets processed will also increase. This is shown in the figure below.
Perhaps one reason the number of unconfirmed transactions grew was because the fee rate offered for many of these transactions was below some threshold where it wasn’t worth the miners efforts to confirm them.
The total value of confirmation fees earned per day and the size of the unconfirmed transaction pool are plotted below:
# transaction fees - the total BTC value of transaction fees miners earn per day.
TRFEE = quandl.get("BCHAIN/TRFEE")
tn_fee = TRFEE.loc['2016-04-24':'2017-07-31']
tn_fee['Fee']=tn_fee['Value']
del tn_fee['Value']
tn_fee['Fee_av']=tn_fee['Fee'].rolling(window=1).mean();
trace1 = go.Scatter(
x=mempool.index.get_level_values('Date'),
y=mempool['size'],
name='Unconfirmed transactions' ) # used later
trace2 = go.Scatter(
x=tn_fee.index.get_level_values('Date'),
y=tn_fee['Fee_av'],
name='Transaction fee',
yaxis='y2' )
data2 = [trace1, trace2]
layout = go.Layout(
title='Total value (BTC) of transaction confirmation fees earned each day',
legend=dict(orientation="h", yanchor='top', y=1.1, xanchor='center', x=0.5),
xaxis=dict(
ticklen=7,
tickcolor='#ffffff',
),
yaxis=dict(
title='Number of unconfirmed transactions',
autorange=True,
showgrid=False,
zeroline=True,
showline=False,
autotick=True,
ticks='',
showticklabels=True,
rangemode='tozero'
),
yaxis2=dict(
title='Daily sum of confirmation fees (BTC)',
zeroline=False,
overlaying='y',
side='right',
rangemode='tozero'
)
)
fig = go.Figure(data=data2, layout=layout)
#plotly.offline.iplot(fig, filename='multiple-axes-double')
py.iplot(fig, filename='multiple-axes-double')
It looks as if confirmation fees correlate positively to the number of unconfirmed transactions. This is expected as users would need to pay higher fees when there are a lot of unconfirmed transactions in order to have their transactions moved towards the front of the queue and processed reasonably quickly.
However it looks as if changes to a miners fee rate lags behind changes in the size of the unconfirmed transaction pool by about 2 weeks. The variation in the transaction fee is also a lot smaller than variation in the size of the unconfirmed transaction pool.
This suggests that the method for calculating transaction the fee rate could be improved so that fee rate responds faster to changes in the number of transactions awaiting confirmation. This would make mining less profitable and more competitive, and would make the Bitcoin network cheaper for users.
Lets look at how expensive it is to use the Bitcoin network by analysing the transaction fee rate relative to transaction values.
Ratio of transaction fees to transaction volume¶
# The Average transaction confirmation fee rate (%)
CPTRV = quandl.get("BCHAIN/CPTRV")
CPTRV = CPTRV.loc['2016-04-24':'2017-07-31']
CPTRV['Fee_av']=CPTRV['Value'].rolling(window=7).mean();
series1 = go.Scatter(
x=mempool.index.get_level_values('Date'),
y=CPTRV['Value'],
name='Fee rate',
line = dict(
color = (color2),
width = 2))
series2 = go.Scatter(
x=mempool.index.get_level_values('Date'),
y=CPTRV['Fee_av'],
name='7 day average',
line = dict(
color = (color1),
width = 3))
data = [series1, series2]
layout = go.Layout(
title='Miners revenue as as percentage of the transaction volume',
yaxis=dict(title='Fee rate (%)'),
legend=dict(orientation="h", yanchor='top', y=1.1, xanchor='center', x=0.5)
)
fig = go.Figure(data=data, layout=layout)
#plotly.offline.iplot(fig, filename='btc_tnsx_fee_rate')
py.iplot(fig, filename='btc_tnsx_fee_rate')
The results show that a fee rate (Miners Revenue/Transaction Volume) of 0.5-1% is typical on the Bitcoin network. This is a bit cheaper than ecommerce payment methods.
Surprinsingly, there is a correlation of -0.25 with the number of unconfirmed transactions. This means the fee rate decreases when the number of unconfirmed transactions increases. The correlation is weak. One possible explanation for this may be that activity on the network increases when the price of Bitcoin increases. When the price of Bitcoin increases, more resources are allocated to mining because it is increasingly profitable. Also, more people decide to buy Bitcoin because it’s becoming so valuable. This leads to more transactions but even more miners competing to confirm transactions and claim the rewards. This increase in supply drives down the transaction confirmation fee rate.
Lets see how the number of transactions per day has changed in 2017 so far.
Number of transactions per day¶
# Number of Transactions from Popular Addresses
NTREP = quandl.get("BCHAIN/NTREP") # excluding popular addresses
NTRAN = quandl.get("BCHAIN/NTRAN") # from all addresses
NTREP = NTREP.loc['2017-01-13':'2017-07-31'] #excl. popular
NTRAN = NTRAN.loc['2017-01-13':'2017-07-31'] #all addresses
NTRFP = NTRAN - NTREP # Popular only
NTRFP['all'] = NTRAN['Value']
NTRFP['unpop'] = NTREP['Value']
NTRFP['pop'] = NTRFP['all'] - NTRFP['unpop']
#NTRFP.head()
NTRFP['pop_av']=NTRFP['Value'].rolling(window=7).mean();
NTRFP['unpop_av']=NTRFP['unpop'].rolling(window=7).mean();
NTRFP['all_av']=NTRFP['all'].rolling(window=7).mean();
series1 = go.Scatter(
x=NTRFP.index.get_level_values('Date'),
y=NTRFP['all'],
name='From all addresses',
line = dict(
color = (color2),
width = 2))
series2 = go.Scatter(
x=NTRFP.index.get_level_values('Date'),
y=NTRFP['all_av'],
name='From all addresses - 7 day average',
line = dict(
color = (color1),
width = 3))
series3 = go.Scatter(
x=NTRFP.index.get_level_values('Date'),
y=NTRFP['unpop'],
name='Transactions excluding 100 most popular addresses',
yaxis='y1',
line = dict(
color = ('#CEB7DF'),
width = 2))
series4 = go.Scatter(
x=NTRFP.index.get_level_values('Date'),
y=NTRFP['unpop_av'],
name='Transactions excluding 100 most popular addresses - 7 day average',
yaxis='y1',
line = dict(
color = ('#830DD4'),
width = 3))
series5 = go.Scatter(
x=NTRFP.index.get_level_values('Date'),
y=NTRFP['pop_av'],
name='Difference - 7 day average',
yaxis='y2',
line = dict(
color = ('#4FA6D4'),
width = 3))
data = [series1, series2, series3, series4, series5]
layout = go.Layout(
title='Bitcoin transactions per day',
xaxis=dict(
ticklen=5,
tickcolor='#ffffff',
),
yaxis=dict(
title='Transactions per day',
autorange=True,
showgrid=False,
zeroline=True,
showline=False,
autotick=True,
ticks='',
ticklen=7,
tickcolor='#ffffff',
showticklabels=True,
),
yaxis2=dict(
title='Difference',
zeroline=False,
overlaying='y',
side='right',
),
legend=dict(
orientation="v",
y=-0.45,
x=0,
)
)
fig = go.Figure(data=data, layout=layout)
#plotly.offline.iplot(fig, filename='btc_num_tnsxs')
py.iplot(fig, filename='btc_num_tnsxs')
The figure above shows the number of transactions posted each day from all addresses, and the number of transactions each day from addresses excluding the 100 most popular addresses. The difference between the two (the number of transactions from the 100 most popular addresses) is shown in blue using the axis on the right.
There is a positive correlation with the size of the unconfirmed transaction pool. Interestingly there is a stronger correlation with transactions created by the 100 most popular addresses (0.54) than for unpopular addresses (0.46). Possible reasons for this are discussed below.
Finally, lets consider the influence of the price of Bitcoin on the size of the unconfirmed transaction pool:
Bitcoin price¶
# The USD value of BTC
MKPRU = quandl.get("BCHAIN/MKPRU")
MKPRU = MKPRU.loc['2016-04-24':'2017-07-31']
MKPRU['mkt_av']=MKPRU['Value'].rolling(window=7).mean();
series1 = go.Scatter(
x=MKPRU.index.get_level_values('Date'),
y=MKPRU['Value'],
name='Daily',
line = dict(
color = (color2),
width = 2))
series2 = go.Scatter(
x=MKPRU.index.get_level_values('Date'),
y=MKPRU['mkt_av'],
name='7 day average',
line = dict(
color = (color1),
width = 3))
data = [series1, series2]
layout = go.Layout(
title='Bitcoin price',
yaxis=dict(title='Price (USD)'),
legend=dict(orientation="h", yanchor='top', y=1.1, xanchor='center', x=0.5)
)
fig = go.Figure(data=data, layout=layout)
#plotly.offline.iplot(fig, filename='btc_price')
py.iplot(fig, filename='btc_price')
Apart from showing a notable increase of around 500% in 13 months, the price has a correlation of just 0.42 with the size of the unconfirmed transaction pool.
The number of transactions coming from popular addresses is positively correlated (0.41) to Bitcoin price, suggesting that when the Bitcoin price increases trading activity on exchanges also increases. Transactions from less popular addresses is inversely correlated to Bitcoin price (-0.31) and this could be because when the Bitcoin price surges, individuals holding Bitcoin do not want to spend Bitcoin to make purchases, and will need to use an exchange to convert fiat currencies into Bitcoin.
Note that ordinarily a new address is used for each Bitcoin transaction.
Ratio of unique addresses to transactions¶
Finally, I want to compare the number of unique Bitcoin addresses used to the total number of transactions created. I initially expected the ratio of addresses to transactions to be close to 1, not realising that each transaction will contain at least 2 addresses (1 input and 1 output, and probably a 2nd output address which is equal to the input address for the change). If each transaction on average has 2 outputs, then the idea ratio of Bitcoin transactions to addresses will be 0.5.
# unique addresses used each day
NADDU = quandl.get("BCHAIN/NADDU")
NADDU = NADDU.loc['2016-04-24':'2017-07-31']
#number of transactions is NTRAN
RATIO = NTRAN / NADDU
d1 = pd.to_datetime('2017-01-13').date()
d2 = pd.to_datetime('2017-07-31').date()
RATIO = pd.concat([RATIO.loc[d1:d2]])
RATIO['weekly_av']=RATIO['Value'].rolling(window=7).mean();
series1 = go.Scatter(
x=RATIO.index.get_level_values('Date'),
y=RATIO['Value'],
name='Daily',
line = dict(
color = (color2),
width = 2))
series2 = go.Scatter(
x=RATIO.index.get_level_values('Date'),
y=RATIO['weekly_av'],
name='7 day average',
line = dict(
color = (color1),
width = 3))
data = [series1, series2]
layout = go.Layout(
title='Ratio of transactions to unique addresses',
yaxis=dict(title='Ratio'),
legend=dict(orientation="h", yanchor='top', y=1.1, xanchor='center', x=0.5)
)
fig = go.Figure(data=data, layout=layout)
#plotly.offline.iplot(fig, filename='btc_add_tnsx_ratio')
py.iplot(fig, filename='btc_add_tnsx_ratio')
The figure above shows that the ratio of unique Bitcoin transactions to unique addresses approaches 0.5. If users reuse an address for multiple transactions (which is bad) then the ratio will rise above 0.5, and if users create transactions with more than the usual minimum of 2 unique addresses then the ratio will dip below 0.5.
Correlation between each time series¶
The table below shows the Pearson correlation coefficients between each time series considered.
'''' # using daily averages
tseries = [
mempool['size'],
ATRCT['Value'],
av_bs['Size'],
NTRBL['Value'],
tn_fee['Fee'],
CPTRV['Value'],
MKPRU['mkt_av'],
NTRFP['Value'],
NTRFP['unpop'],
RATIO['Value']
]
cols = ['Unconf trnsx',
'Conf time',
'Block size',
'Trnsx/block',
'Conf fees',
'Fee rate',
'USD/BTC',
'Trnsx - pop addrs',
'Trnsx - unpop addrs',
'Tnsx : Addrs ratio']
tbl = np.zeros((len(tseries), len(tseries)))
for i in range(len(tseries)):
for j in range(len(tseries)):
tbl[i,j] = tseries[i].corr(tseries[j], method='pearson', min_periods=None)
pd.DataFrame(data=tbl, # values
index=cols, # 1st column as index
columns=cols) # 1st row as the column names
''';
# Using 7 day moving average
tseries = [
mempool['size_av'],
ATRCT['artct_av'],
av_bs['Size_av'],
NTRBL['Size_av'],
tn_fee['Fee_av'],
CPTRV['Fee_av'],
MKPRU['mkt_av'],
NTRFP['pop_av'],
NTRFP['unpop_av'],
RATIO['Value']]
cols = ['Unconf trnsx',
'Conf time',
'Block size',
'Trnsx/block',
'Conf fees',
'Fee rate',
'USD/BTC',
'Trnsx - pop addrs',
'Trnsx - unpop addrs',
'Tnsx : Addrs ratio']
tbl = np.zeros((len(tseries), len(tseries)))
for i in range(len(tseries)):
for j in range(len(tseries)):
tbl[i,j] = tseries[i].corr(tseries[j], method='pearson', min_periods=None)
pd.DataFrame(data=tbl, # values
index=cols, # 1st column as index
columns=cols) # 1st row as the column names