I was curious how much revenue is coming from fees from the transactions which miners include in only the next one or two blocks.,
One problem with using OP's data (confirmation_times.csv) for this is that it uses a timestamp, instead of block #. So there's no real way to know how many confirmations it took for a transaction to get mined. But using the average of 1 block = 10 minutes, I should be able to get kind of close.
What information this provides us is that miners receive ~%75 of their revenue from transactions they include within the first two blocks (well, within the first 20 minutes, to be technically accurate).
http://i.imgur.com/26SmxdY.pngGenerated using the following Python3 source for use in a Jupyter notebook.
import pandas as pd
from plotly import graph_objs as go
from plotly.offline import iplot, init_notebook_mode
init_notebook_mode() # Jupyter notebook
data = []
daily_threshold = 10 # Ignore days w/less than N btc in fees.
filename = 'confirmation_times.csv'
col_names = ['conf_time', 'first_confirmed', 'fee', 'size']
labels = ['0+ minutes', '10+ minutes', '20+ minutes', '30+ minutes', '1+ hours']
bins = [0, 1*10*60, 2*10*60, 3*10*60, 6*10*60, 9999*10*60]
df = pd.read_csv('./{}'.format(filename), usecols=col_names, parse_dates=['first_confirmed'])
df['fee'] = df['fee'] / 100000000 # Use BTCs, not Satoshis.
df['date'] = pd.to_datetime(df['first_confirmed']).dt.date
df['time'] = pd.cut(df['conf_time'], bins, labels=labels)
grouped = df.groupby(['date', 'time'])
df2 = grouped['fee'].sum().to_frame().rename(columns = lambda x: x + '_sum')
s = df2.unstack()['fee_sum'].sum(1).ge(daily_threshold)
plot_df = df2.loc[s.index[s].tolist()]['fee_sum'].unstack()
for time_bin in list(plot_df):
data.append(
go.Bar(
x=['{:%Y-%m-%d}'.format(dt) for dt in list(plot_df[time_bin].index)],
y=plot_df[time_bin].tolist(),
name=time_bin))
layout = go.Layout(
barmode='stack', title='Bitcoin Mining Fee Revenue By Confirmation Speed',
xaxis=go.XAxis(title='Date', type='category'),
yaxis=go.YAxis(title='Total Fees (BTC)'))
fig=go.Figure(data=data, layout=layout)
iplot(fig, filename='stacked-bar')
