<jupyter_start><jupyter_text>Account Relationships on the NEAR Blockchain High Level Dapp and Account Specific Transaction Flow[Read free on Medium](https://medium.com/dropout-analytics/account-relationships-on-the-blockchain-2fcddc25a3b4?sk=074c2f1012d99f31b1d5b4a6775879f9)<jupyter_code>import psycopg2

# postgres://public_readonly:nearprotocol@172.16.31.10/mainnet_explorer
conn = psycopg2.connect(host="172.16.31.10", database="mainnet_explorer", user="public_readonly", password="<PASSWORD>")

cur = conn.cursor()
import pandas as pd

def create_pandas_table(sql_query, database=conn):
    table = pd.read_sql_query(sql_query, database)
    return table
query = '''
        select 
            signer_account_id as signer,
            receiver_account_id as receiver,
            date_trunc('minute', to_timestamp(block_timestamp/1000/1000/1000)) as ds
        from 
            transactions
        '''
df = create_pandas_table(query)

df
df.receiver.value_counts()[:10]
df.loc[df.receiver=='app.nearcrowd.near'].signer.value_counts()
df.loc[df.receiver=='jerry.zest.near'].signer.value_counts()
df.loc[df.receiver=='berryclub.ek.near'].signer.value_counts()
df.loc[df.receiver=='berryclub.ek.near']
df.loc[df.receiver=='berryclub.ek.near'].signer.value_counts()
print(df.loc[df.receiver=='berryclub.ek.near'].signer.value_counts()[:10])<jupyter_output><empty_output><jupyter_text>Let's try a simple visualization<jupyter_code>df.loc[df.receiver=='berryclub.ek.near'].signer.value_counts().plot(kind = 'barh');<jupyter_output><empty_output><jupyter_text>Issue: Too many unique value counts Visualize Flow with Graphistry<jupyter_code>## restart runtime after running this
# !pip install --user graphistry
import graphistry

graphistry.register(api=3, protocol="https", server="hub.graphistry.com", username="", password="")
g = graphistry.edges(df.loc[df.signer.isin(df.loc[df.receiver=='berryclub.ek.near'].signer.value_counts()[:10].keys())]).bind(source='signer', destination='receiver')

g.plot()<jupyter_output><empty_output><jupyter_text>Visualizing Transaction Volume For a Dapp (berryclub.ek.near)By month<jupyter_code>query = '''
        select
            count(*) n_transactions,
            date_trunc('month', to_timestamp(block_timestamp/1000/1000/1000)) ds
        from 
            transactions
            where 
                receiver_account_id = 'berryclub.ek.near'
            group by
                date_trunc('month', to_timestamp(block_timestamp/1000/1000/1000))
        '''
df = create_pandas_table(query)

df
df['ds'] = pd.to_datetime(df.ds).dt.to_period('m')

df.plot(kind='bar', x='ds', y='n_transactions', title='Berry Club Received Transactions by Month');<jupyter_output><empty_output><jupyter_text>By day<jupyter_code>query = '''
        select
            count(*) n_transactions,
            date_trunc('day', to_timestamp(block_timestamp/1000/1000/1000)) ds
        from 
            transactions
            where 
                receiver_account_id = 'berryclub.ek.near'
            group by
                date_trunc('day', to_timestamp(block_timestamp/1000/1000/1000))
        '''
df = create_pandas_table(query)

df.ds = pd.to_datetime(df.ds)

df.plot(x='ds', y='n_transactions', title='Berry Club Received Transactions by Day', figsize=(24, 6));<jupyter_output><empty_output><jupyter_text>By hour<jupyter_code>query = '''
        select
            count(*) n_transactions,
            date_trunc('hour', to_timestamp(block_timestamp/1000/1000/1000)) ds
        from 
            transactions
            where 
                receiver_account_id = 'berryclub.ek.near'
                and date_trunc('hour', to_timestamp(block_timestamp/1000/1000/1000)) between '2021-06-17' and '2021-06-24'
            group by
                date_trunc('hour', to_timestamp(block_timestamp/1000/1000/1000))
        '''
df = create_pandas_table(query)

df.ds = pd.to_datetime(df.ds)

df.plot(x='ds', y='n_transactions', title='Berry Club Received Transactions by Hour', figsize=(24, 6));<jupyter_output><empty_output><jupyter_text>By minute<jupyter_code>query = '''
        select
            count(*) n_transactions,
            date_trunc('minute', to_timestamp(block_timestamp/1000/1000/1000)) ds
        from 
            transactions
            where 
                receiver_account_id = 'berryclub.ek.near'
                and date_trunc('minute', to_timestamp(block_timestamp/1000/1000/1000)) between '2021-06-19' and '2021-06-20'
            group by
                date_trunc('minute', to_timestamp(block_timestamp/1000/1000/1000))
        '''
df = create_pandas_table(query)

df.ds = pd.to_datetime(df.ds)

df.plot(x='ds', y='n_transactions', title='Berry Club Received Transactions by Minute', figsize=(24, 6));<jupyter_output><empty_output><jupyter_text>Between an Account and a Dapp (blaze.near -> berryclub.ek.near) By month<jupyter_code>query = '''
        select
            count(*) n_transactions,
            date_trunc('month', to_timestamp(block_timestamp/1000/1000/1000)) ds
        from 
            transactions
            where 
                signer_account_id = 'blaze.near'
                and receiver_account_id = 'berryclub.ek.near'
            group by
                date_trunc('month', to_timestamp(block_timestamp/1000/1000/1000))
        '''
df = create_pandas_table(query)

df
df.ds = pd.to_datetime(df.ds).dt.to_period('m')

df.plot(kind='bar', x='ds', y='n_transactions', title='blaze.near -> Berry Club by Month');<jupyter_output><empty_output><jupyter_text>By day<jupyter_code>query = '''
        select
            count(*) n_transactions,
            date_trunc('day', to_timestamp(block_timestamp/1000/1000/1000)) ds
        from 
            transactions
            where 
                signer_account_id = 'blaze.near'
                and receiver_account_id = 'berryclub.ek.near'
            group by
                date_trunc('day', to_timestamp(block_timestamp/1000/1000/1000))
        '''
df = create_pandas_table(query)

df.ds = pd.to_datetime(df.ds)

df.plot(x='ds', y='n_transactions', title='blaze.near -> Berry Club by Day', figsize=(24, 6));<jupyter_output><empty_output><jupyter_text>Forecast Transaction Volume with Prophet> Prophet is a procedure for forecasting time series data based on an additive model where non-linear trends are fit with yearly, weekly, and daily seasonality, plus holiday effects. - [Intro to Facebook Prophet](https://medium.com/future-vision/intro-to-prophet-9d5b1cbd674e?source=friends_link&sk=5709431ddc156b076b3cc1c22be3dcbf) For a DappLet's forecast the next 7 days of transactions received by berryclub.ek.near.<jupyter_code>from fbprophet import Prophet

m = Prophet()
query = '''
        select
            date_part('year', date) || '-' || date_part('month', date) || '-' || date_part('day', date) ds,
            count(*) y
        from 
            (
                select
                    signer_account_id,
                    receiver_account_id,
                    date_trunc('hour', to_timestamp(block_timestamp/1000/1000/1000)) date
                from
                    transactions
            ) dt
            where
                receiver_account_id = 'berryclub.ek.near'
            group by
                date
        '''

df = create_pandas_table(query)

df['ds'] = pd.to_datetime(df.ds)

df.sample(2)
m.fit(df)
future = m.make_future_dataframe(7, freq='D')

forecast = m.predict(future)

forecast[['ds', 'yhat']].tail(7)
m.plot(forecast);
m.plot_components(forecast);<jupyter_output><empty_output><jupyter_text>Between 2 accountsLet's forecast the next 7 days of transactions between 2 accounts.<jupyter_code>m = Prophet()
query = '''
        select
            date_part('year', date) || '-' || date_part('month', date) || '-' || date_part('day', date) ds,
            count(*) y
        from 
            (
                select
                    signer_account_id,
                    receiver_account_id,
                    date_trunc('day', to_timestamp(block_timestamp/1000/1000/1000)) date
                from
                    transactions
            ) dt
            where 
                signer_account_id = 'blaze.near'
                and receiver_account_id = 'berryclub.ek.near'
            group by
                date
        '''

df = create_pandas_table(query)

df['ds'] = pd.to_datetime(df.ds)

df.sample(3)
m.fit(df)
future = m.make_future_dataframe(7, freq='D')

forecast = m.predict(future)

forecast[['ds', 'yhat']].tail(7)
m.plot(forecast);
m.plot_components(forecast);<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12643811737361998, 'fraction_numerical': 0.03916327716443928, 'mean_word_length': 2.967727063162748, 'pattern_counts': {'":': 0, '<': 46, '<?xml version=': 0, '>': 50, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 2, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '611371', 'license': 'Apache-2.0', 'n_tokens_mistral': 3066, 'n_tokens_neox': 2731, 'n_words': 462, 'path': '01_account_relationships_through_transactions.ipynb', 'repo_name': 'gumdropsteve/near-ds', 'size': '8609'}