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)

ts_col = df['timestamp'].div(1000.0)
df['timestamp'] = ts_col.apply(datetime.fromtimestamp)
print(df.describe())
summary = df.diff().describe()
print(summary)

maxidx = df.idxmax()

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

df = df.diff()
df = df.dropna()

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]) # std

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