<jupyter_start><jupyter_text>Cryptocurrency Fundamentals Analysis Purpose:    - Explore relationships between cryptocurrency git/github repositoriy activity and price/volatility       Load Fundamentals Data The fundamentals data is accessible through the database that this repository's worker maintains. Update `config.py` with the database url for the source data<jupyter_code>import os, sys
import pathlib
import pandas as pd
sys.path.insert(1, "/home/jupyter/fundamental_crypto_value")
import fundamentals
import config
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm.session import Session
from fundamentals.github_api import get_repo_db_stats

DATABASE_URL = 'FAKE_DEMO_DB_URL'
engine = create_engine(DATABASE_URL)
Session = sessionmaker(bind=engine)
session = Session()

query = """
WITH 
	dates as (SELECT generate_series(date '2017-06-01', date '2018-02-13', '1 day')::date as dt),
    
	analysis_pr as (
        SELECT   repo_id, dates.dt, sum(case when pull_requests.created_at::date = dates.dt then 1 else 0 end) pr_count
        FROM     pull_requests INNER JOIN dates ON True
        GROUP BY repo_id, dates.dt
    ),
    
    analysis_issues as (
        SELECT   repo_id, dates.dt, sum(case when issues.opened_at::date = dates.dt then 1 else 0 end) issues_count
        FROM     issues INNER JOIN dates ON True
        GROUP BY repo_id, dates.dt
    ),
    
    analysis_commits as (
        SELECT   
        	repo_id,
        	dates.dt,
            sum(case when commits.last_modified::date = dates.dt then additions::int else 0 end) commit_add_sum,
            sum(case when commits.last_modified::date = dates.dt then deletions::int else 0 end) commit_del_sum,
            sum(case when last_modified::date = dates.dt then 1 else 0 end) commit_count
		FROM commits INNER JOIN dates on True
    	GROUP BY repo_id, dates.dt
    ),
    
    analysis_stars as (
        SELECT   repo_id, dates.dt, sum(case when stars.starred_at::date = dates.dt then 1 else 0 end) star_count
        FROM     stars INNER JOIN dates ON True
        GROUP BY repo_id, dates.dt
    )
    
SELECT
	tracked.repo_id,
    tracked.symbol,
    dates.dt,
    analysis_pr.pr_count,
    analysis_issues.issues_count,
    analysis_commits.commit_add_sum,
    analysis_commits.commit_del_sum,
    analysis_commits.commit_count,
    analysis_stars.star_count
FROM
	tracked
    INNER JOIN dates on True
    INNER JOIN analysis_pr on tracked.repo_id = analysis_pr.repo_id and dates.dt = analysis_pr.dt
    INNER JOIN analysis_issues on tracked.repo_id = analysis_issues.repo_id and dates.dt = analysis_issues.dt
    INNER JOIN analysis_commits on tracked.repo_id = analysis_commits.repo_id and dates.dt = analysis_commits.dt
    INNER JOIN analysis_stars on tracked.repo_id = analysis_stars.repo_id and dates.dt = analysis_stars.dt
WHERE
    tracked.symbol in ('XMR', 'EOS', 'XVG', 'XRB', 'DCR', 'ZRX', 'FCT', 'NEO', 'SNT', 'LSK', 'DASH', 'WAVES', 'DRGN', 'XRP', 'REP', 'BTC', 'ZEC', 'XLM', 'RHOC', 'ETN', 'OMG', 'ZCL', 'PIVX', 'BTS', 'STEEM', 'ARK', 'ADA', 'DGB', 'SC', 'STRAT', 'DOGE', 'BCN', 'GNT', 'QTUM', 'BCH', 'BTG', 'LTC', 'ETH')
"""
# Run the query
gh_data = pd.read_sql(query, con=engine)

# Extract symbols that were retrieved
symbols = set(gh_data.symbol.unique())

# Remap dt to date for convenience
gh_data = gh_data.rename(columns={'dt': 'date'})

# Convert date to string
gh_data['date'] = gh_data.date.apply(lambda x: str(x))
# Inspect a few records
gh_data.head(5)
gh_data.tail(5)
gh_data.describe()<jupyter_output><empty_output><jupyter_text>Load Currency Price Histories Price histories for 200 assets are available in the `daily_ohlc_coinmarketcap` subfolder. We read in each dataframe and name it's columns by prepending the symbol name<jupyter_code>PRICE_HISTORY_DIR = 'daily_ohlc_coinmarketcap'
dfs = []
hist_symbol = []
for path in pathlib.Path(PRICE_HISTORY_DIR).iterdir():
    filename = path.name
    symbol = filename.split('_', 1)[0]
    # Only Load Asset Histories where We have GitHub History
    if symbol in symbols:
        sdf = pd.read_csv(path,
                          usecols=[1,2,3,4,5,6,7],
                          header=0,
                          names=['{}_close'.format(symbol),
                                 '{}_date'.format(symbol),
                                 '{}_high'.format(symbol),
                                 '{}_low'.format(symbol),
                                 '{}_open'.format(symbol),
                                 '{}_usd_market_cap'.format(symbol),
                                 '{}_usd_volume'.format(symbol)])
        dfs.append(sdf)
        hist_symbol.append(symbol)
hist_symbol = set(hist_symbol)
# Inspect a few records
dfs[0].head(5)<jupyter_output><empty_output><jupyter_text>Join Fundamentals and Price History On Date Fundamentals data and price history data are both grouped by whole day on axis 1. If we pre-filter to 1 asset at a time, the components can be joined together into a single dataframe with fundamental and price history data<jupyter_code>assets = {}
for symbol in hist_symbol:
    sym_fundamentals = gh_data[gh_data.symbol == symbol].copy().sort_values('date')
    sym_fundamentals = sym_fundamentals.set_index('date')
    sym_hist = [x for x in dfs if x.columns[0][:len(symbol)] == symbol][0]
    sym_hist = sym_hist.rename(columns={'{}_date'.format(symbol): 'date'})
    sym_hist = sym_hist.set_index('date')
    sym_df = pd.concat([sym_fundamentals, sym_hist], axis=1, join='inner')
    sym_df = sym_df.rename(columns=dict((x, x.lstrip('{}_'.format(symbol))) for x in list(sym_df.columns)))
    del sym_df['repo_id']
    assets[symbol] = sym_df.copy()
# Phew! That was ugly.
# review a few records
assets['BTC'].tail()<jupyter_output><empty_output><jupyter_text>Looking for Relationships For the purposes of this analysis, we'll select bitcoin (BTC) for review as it has the longest price history and a the most fundamental record instances.Changing the 'symbol' parameter will adjust these metrics for any other asset that is trackedWhen looking for relationships we'll first visualize the data in charts. Next, we'll look for any strong relationships among:    - Fundamentals against returns    - Fundamentals against volatility<jupyter_code># Set out asset
symbol = 'BTC'<jupyter_output><empty_output><jupyter_text>Measure Daily Return and Volatility Our measure of volatility is the daily range as a percentage of the daily range midpoint<jupyter_code># Copy the frame so we don't accidentally update the original data
df = assets[symbol].copy()

# Compute daily return percentage
df['return_pcnt'] = (df.close - df.open) / df.close

# Compute daily range pcnt of daily midpoint as proxy for daily volatility
df['volatility_pcnt'] = (df.high - df.low) / ((df.high + df.low) / 2)<jupyter_output><empty_output><jupyter_text>Plotting the Asset<jupyter_code>%matplotlib inline
import matplotlib.pyplot as plt
# Note: 
#    Not pictured = here commit_additions, commit_deletions
fig = plt.figure(1, figsize=(15,15))

plt.subplots_adjust(hspace=0.4)

ax = plt.subplot(511)
plt.plot(df.index, df.close, color='black')
fig.suptitle('{} Fundamentals'.format(symbol), size=20)
for ix, label in enumerate(ax.xaxis.get_ticklabels()):
    label.set_visible(True) if ix % 80 == 0 else label.set_visible(False)
ax.set_ylabel('USD', size=16)
ax.set_title('Daily Close Price ($)', size=16)

ax = plt.subplot(512)
plt.bar(df.index, df.commit_count, color='lightcoral')
for ix, label in enumerate(ax.xaxis.get_ticklabels()):
    label.set_visible(True) if ix % 80 == 0 else label.set_visible(False)
ax.set_ylabel('Count', size=16)
ax.set_title('Git Commit', size=16)

ax = plt.subplot(513)
plt.bar(df.index, df.pr_count, color='darkblue')
for ix, label in enumerate(ax.xaxis.get_ticklabels()):
    label.set_visible(True) if ix % 80 == 0 else label.set_visible(False)
ax.set_ylabel('Count', size=16)
ax.set_title('GitHub Pull Requests', size=16)

ax = plt.subplot(514)
plt.bar(df.index, df.issues_count, color='darkred')
for ix, label in enumerate(ax.xaxis.get_ticklabels()):
    label.set_visible(True) if ix % 80 == 0 else label.set_visible(False)
ax.set_ylabel('Count', size=16)
ax.set_title('GitHub Issues', size=16)

ax = plt.subplot(515)
plt.bar(df.index, df.star_count, color='green')
for ix, label in enumerate(ax.xaxis.get_ticklabels()):
    label.set_visible(True) if ix % 80 == 0 else label.set_visible(False)
ax.set_ylabel('Count', size=16)
ax.set_title('GitHub Stargazers', size=16)<jupyter_output><empty_output><jupyter_text>Correlation in Returns and Volatility We can evaluate the directions of any relationships between cryptocurrency fundamentals against return percent and volatility percent using Pearson's correlation<jupyter_code>df.corr()[['return_pcnt', 'volatility_pcnt']].loc[['pr_count', 'issues_count', 'commit_add_sum',
       'commit_del_sum', 'commit_count', 'star_count']]<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11228813559322035, 'fraction_numerical': 0.012265834076717217, 'mean_word_length': 4.067231638418079, 'pattern_counts': {'":': 0, '<': 29, '<?xml version=': 0, '>': 29, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 6, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '620104', 'license': 'MIT', 'n_tokens_mistral': 3062, 'n_tokens_neox': 2885, 'n_words': 804, 'path': 'Github_Analysis.ipynb', 'repo_name': 'nmits/CapstoneProphetTests', 'size': '8968'}