text_plain_output_1.png
from bq_helper import BigQueryHelper

from datetime import datetime

from datetime import datetime, timedelta

from google.cloud import bigquery

from scipy.stats import norm

import numpy as np
import pandas as pd
from google.cloud import bigquery
from bq_helper import BigQueryHelper
client = bigquery.Client()
query = '\n    #standardSQL\n    SELECT\n        timestamp\n    FROM \n         `bigquery-public-data.bitcoin_blockchain.blocks`\n    ORDER BY\n        timestamp\n    '
bq_assistant = BigQueryHelper('bigquery-public-data', 'bitcoin_blockchain')
df = bq_assistant.query_to_pandas_safe(query, max_gb_scanned=1000)

original = df.copy()

from datetime import datetime

df = original.copy()
df = df.sort_values(by=['timestamp'], ascending=True)

# Data Exploration

# Convert timestamp to datetime. it will be easier to read (especially the time-delta's)
ts_col = df['timestamp'].div(1000.0)
df['timestamp'] = ts_col.apply(datetime.fromtimestamp)
print(df.describe())
summary = df.diff().describe()
print(summary)

# From results, we see that the time range is 2009-01-09 -> 2018-09-10
# Also, the greatest block delay was 1 day and 4:47:00! 
# Note that 75th percentile of block times is only 00:12:54. Clearly block times of 02:00:00 are rare. 

maxidx = df.idxmax()

from scipy.stats import norm
from datetime import datetime, timedelta
import numpy as np

# More data processing

df = df.diff()
df = df.dropna() # since we have a column of diffs, the first entry will need to be ignored

print(df.head())
print(df.describe())
print(df.dtypes)

# convert timedelta type to a float (seconds)

print(df['timestamp'][2].total_seconds())
df['timestamp'] = df['timestamp'].apply(lambda x: x.total_seconds())

float_summary = df.describe()
print(float_summary)

time_threshold = timedelta(hours=2).total_seconds()
print(float_summary["timestamp"][1]) # mean
print(float_summary["timestamp"][2]) # standard dev

df_cdf = norm.cdf(time_threshold, float_summary['timestamp'][1], float_summary['timestamp'][2])
print((1 - df_cdf) * df.shape[0])
print(df.shape[0])
print(1 - df_cdf)
print(df.timestamp.quantile(0.99))
print(len(df[df['timestamp'] > time_threshold]))