<jupyter_start><jupyter_text>Total swapsThis notebook shows a method to get Bancor trade volumes over a time period.Set the blocks and sum the volume to get 24h volume, daily trading volume, etc.This is a suitable method over larger timeframes. Setup---<jupyter_code>from graphqlclient import GraphQLClient
import pandas as pd
import json
from pandas.io.json import json_normalize
from datetime import datetime
ENDPOINT = "https://api.thegraph.com/subgraphs/name/blocklytics/bancor"
client = GraphQLClient(ENDPOINT)<jupyter_output><empty_output><jupyter_text>This examples takes ~24 hour period by manually selecting blocks. In practice, this should be automated for the relevant periods.<jupyter_code># Blocks
start_block = 9273388 # Inclusive
end_block = 9279958   # Inclusive?<jupyter_output><empty_output><jupyter_text>Fetch data---<jupyter_code># This function accepts a query starting with 'results'
# Returns a dataframe with flattened results

def fetch_results(query, block):
    # Start with an empty df
    df = pd.DataFrame()

    # Results must be paginated
    limit = 100
    offset = 0
    fetching_results = True
    # Fetch paginated results
    while fetching_results:
        _query = query.format(limit, offset, block)
        
        result = json.loads(client.execute(_query))
        _df = pd.DataFrame.from_records(json_normalize(result['data']['results']))

        # If df is empty, create it from new _df
        # Else append new _df to df
        if len(df.index) == 0:
            df = _df
        else:
            df = df.append(_df)

        # Prepare for pagination
        result_length = len(_df.index)
        if limit == result_length:
            offset += limit
        else:
            fetching_results = False

    df.reset_index(drop=True, inplace=True)
    print(df.shape)
    return df
query = """
    {{
      results:tokenSwapTotals(
        first:{0},
        skip:{1},
        block:{{number:{2}}}
        )
        {{
        fromToken {{id, symbol, decimals}}
        toToken {{id, symbol, decimals}}
        totalAmountPurchased
        totalAmountReturned
      }}
    }}
"""

df_start = fetch_results(query, start_block)
df_end = fetch_results(query, end_block)<jupyter_output>(467, 8)
(467, 8)<jupyter_text>Format data---Combine dataframes<jupyter_code>df = df_start\
    .merge(df_end, 
           how='outer',
           on=['fromToken.decimals',  'fromToken.id', 'fromToken.symbol', 'toToken.decimals', 'toToken.id', 'toToken.symbol'],
           suffixes=('_start', '_end')
          )<jupyter_output><empty_output><jupyter_text>Convert from wei<jupyter_code>df['totalAmountPurchased_start_f'] = df.apply(lambda row: float(row['totalAmountPurchased_start']) / 10 ** row['fromToken.decimals'], axis=1)
df['totalAmountPurchased_end_f'] = df.apply(lambda row: float(row['totalAmountPurchased_end']) / 10 ** row['fromToken.decimals'], axis=1)

df['totalAmountReturned_start_f'] = df.apply(lambda row: float(row['totalAmountReturned_start']) / 10 ** row['toToken.decimals'], axis=1)
df['totalAmountReturned_end_f'] = df.apply(lambda row: float(row['totalAmountReturned_end']) / 10 ** row['toToken.decimals'], axis=1)<jupyter_output><empty_output><jupyter_text>Get difference over time period<jupyter_code>df['totalAmountPurchased'] = df['totalAmountPurchased_end_f'] - df['totalAmountPurchased_start_f']
df['totalAmountReturned'] = df['totalAmountReturned_end_f'] - df['totalAmountReturned_start_f']<jupyter_output><empty_output><jupyter_text>Get volume in base token terms (USDB or BNT)<jupyter_code>df['volume_bnt'] = \
    df.apply(lambda row: row['totalAmountPurchased'] if row['fromToken.id'] == "<KEY>" else 0, axis=1)\
    + df.apply(lambda row: row['totalAmountReturned'] if row['toToken.id'] == "0x1f573d6fb3f13d689ff844b4ce37794d79a7ff1c" else 0, axis=1)

df['volume_usdb'] = \
    df.apply(lambda row: row['totalAmountPurchased'] if row['fromToken.id'] == "<KEY>" else 0, axis=1)\
    + df.apply(lambda row: row['totalAmountReturned'] if row['toToken.id'] == "<KEY>" else 0, axis=1)\<jupyter_output><empty_output><jupyter_text>Volumes over period--- BNT Volume<jupyter_code>df['volume_bnt'].sum()<jupyter_output><empty_output><jupyter_text>USDB VolumeAvoid double-counting volume by excluding BNT volume already counted above.<jupyter_code># df['volume_usdb'].sum() # This will double-count BNT volume.
df[df['volume_bnt'] == 0]['volume_usdb'].sum()<jupyter_output><empty_output><jupyter_text>Trade pairs not countedThese trade pairs were not counted as they did not include a base token of BNT or USDB.<jupyter_code>df[(df['volume_bnt'] == 0) & (df['volume_usdb'] == 0) & (df['totalAmountPurchased'] > 0)]<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1284013605442177, 'fraction_numerical': 0.01764455782312925, 'mean_word_length': 4.541813898704358, 'pattern_counts': {'":': 0, '<': 43, '<?xml version=': 0, '>': 44, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '367564', 'license': 'MIT', 'n_tokens_mistral': 1619, 'n_tokens_neox': 1542, 'n_words': 341, 'path': 'bancor/Volume via totals.ipynb', 'repo_name': 'calebsheridan/notebooks', 'size': '4815'}