Aragon ICO analysis

# coding: utf-8
# Visit for full code and support 'https://github.com/corpetty/py-etherscan-api' also, "https://github.com/corpetty/ICO_analysis"

# In[1]: #Original Code by Corey Petty (twitter @corpetty)

# Needed for adding my version of py_etherscan_api package on personal Mac
import sys
sys.path.append('/Library/Frameworks/Python.framework/Versions/3.5/lib/python3.5/site-packages/')


# In[1]:

import etherscan.accounts as accounts
import pandas as pd
import json


# In[2]:

from plotly import __version__
from plotly.offline import download_plotlyjs, init_notebook_mode, iplot
from plotly.offline import plot
from plotly.graph_objs import Scatter, Data, Box, Bar
from plotly.graph_objs import Scattergl
init_notebook_mode() # run at the start of every ipython notebook to use plotly.offline
                     # this injects the plotly.js source files into the notebook
from plotly.tools import FigureFactory as FF
import plotly.plotly as py
import plotly.graph_objs as go
from plotly import tools

get_ipython().magic('matplotlib inline')
import matplotlib as plt
plt.style.use('ggplot')


# In[5]:
# This module is written as an effort to provide python bindings to the EtherScan.io API, which can be found at: https://etherscan.io/apis In order to use this, you must attain an Etherscan user account, and generate an API key.

#In order to use the API, you must provide an API key at runtime, which can be found at the Etherscan.io API website. If you'd like to #use the provided examples without altering them, then the JSON file api_key.json must be stored in the base directory. Its format is #as follows: { "key" : "YourApiKeyToken" } with YourApiKeyToken is your provided API key token from EtherScan.io

with open('..\api_key.json', 'r') as key_file:
    key = json.loads(key_file.read())['key']


# In[6]:

api = accounts.Account(address="0x960b236A07cf122663c4303350609A66A7B288C0", api_key=key)


# In[7]:

transactions = api.get_all_transactions()
df = pd.DataFrame(transactions)


# In[7]:

df.value = df.value.astype(float)/ 1000000000000000000
df.timeStamp = pd.to_datetime(df.timeStamp, unit='s')
df.blockNumber = df.blockNumber.astype(int)


# In[8]:

# Check total of successful ETH transactions
df[df.isError == '0'].value.sum()


# In[9]:

df[(df.value != 0) & (df.isError == '0')].head()


# In[10]:

df[(df.value != 0) & (df.isError == '0')]['from'].value_counts()


# In[11]:

combined_df = pd.DataFrame(df[(df.value != 0) & (df.isError == '0')].groupby('from').sum().sort_values(by='value'))
size_df = pd.DataFrame(df[(df.value != 0) & (df.isError == '0')].groupby('from').size())


# In[12]:

pd.DataFrame(df[(df.value != 0) & (df.isError == '0')].groupby('from').size()).iloc[:,0].max()


# In[13]:

combined_df.join(size_df).sort_values('value', ascending=False)


# In[14]:

combined_df.sum()


# In[15]:

print('total txs:        {}'.format(len(df)))
print('total value txs:  {}'.format(len(df[(df.value != 0) & (df.isError == '0')])))
print('total error txs:  {}'.format(len(df[df.isError == '1'])))
print('total zero txs:   {}'.format(len(df[df.value == 0])))
print('unique address:   {}'.format(len(combined_df)))


# In[16]:

block_df = pd.DataFrame(df[df.isError == 0].groupby('timeStamp').sum().reset_index())
block_df.value_cum = block_df.value.cumsum()


# In[17]:

mask1 = (df.isError == '0') & (df.blockNumber >= 3723000) & (df.blockNumber <= 3723134) & (df.value !=0)
mask2 = (df.isError == '1') & (df.blockNumber >= 3723000) & (df.blockNumber <= 3723134) & (df.value !=0)
trace = go.Bar(
    x=df[mask1].timeStamp,
    y=df[mask1].value,
    name="successful",
#     marker=dict(
#         line=dict(
#             width=10,
#             color='#1f77b4',
#          ),
#     ),

)
trace2 = go.Bar(
    x=df[mask2].timeStamp,
    y=df[mask2].value,
    name="failed",
#     marker=dict(
#         line=dict(
#             width=10,
#             color='#ff7f0e',
#          ),
#     ),

)
layout = go.Layout(
    xaxis=go.XAxis(
        title='Time of Investment',
#         titlefont = dict(
#             size = 40,
#         ),
#         tickfont = dict(
#             size = 40
#         )
    ),
    yaxis=go.YAxis(
        title='ETH Invested',
#         titlefont = dict(
#             size = 40,
#         ),
#         tickfont = dict(
#             size = 40
#         )
    ),
    legend = {
        'x':0.8,
        'y':1,
#         'font' : dict(
#             size = 40
#         )
    },
#     margin={
#         'l': 200,
#         'r': 100,
#         'b': 200,
#     },
#     height=1500,
#     width=3000
#    barmode='stack'
)
data = [trace, trace2]
fig = go.Figure(data=data, layout=layout)
# iplot(fig)
py.iplot(fig, filename='Aragon_TimeSeries_Invested')


# In[18]:

combined = df[mask1].groupby('from').sum().reset_index()


# In[19]:

def label_exp_group(value):
    labels = ['0', '1', '2', '3', '4', '5', '6', '7', '8']
    for label in labels:
        if value <= 10**float(label):
            return label

combined['exp_group'] = combined.value.apply(label_exp_group)


# In[22]:

grouped = pd.DataFrame(combined.groupby('exp_group').sum())
grouped = grouped[['value']]
grouped_size = pd.DataFrame(combined.groupby('exp_group').size())
grouped['size'] = grouped_size[0].values
grouped['size_perc'] = [trans / len(combined) * 100 for trans in grouped['size'].values]
grouped['value_perc'] = [value / grouped.value.sum() * 100 for value in grouped.value.values]


# In[23]:

grouped


# In[24]:

trace = go.Bar(
    y=grouped.value_perc,
    name='Total ETH'
)
trace2 = go.Bar(
    y=grouped.size_perc,
    name='Addresses',
)
data = Data([trace, trace2])
layout = go.Layout(
    xaxis=go.XAxis(
        title='Investor Group',
        ticktext = [
                "$0 < x < 10^0$",
                "$10^0 < x < 10^1$",
                "$10^1 < x < 10^2$",
                "$10^2 < x < 10^3$",
                "$10^3 < x < 10^4$",
                "$10^4 < x < 10^5$",
                "$10^5 < x < 10^6$",
                "Exchange"
            ],
        tickvals = [ 0, 1, 2, 3, 4, 5,6 ],
        tickfont=dict(
#             size=40,
            color='black'
        ),
        titlefont=dict(
#             size=50,
            color='black'
        ),
    ),
    yaxis=go.YAxis(
        title='Percentage',
        tickfont=dict(
#             size=40,
            color='black'
        ),
        titlefont=dict(
#             size=50,
            color='black'
        ),
    ),
    legend=dict(
        x=0,
        y=1,
        font=dict(
#             size=40,
            color='black'
        ),
    ),
#     height=1500,
#     width=3000,
#      margin={
#         'b': 400,
#         'l': 200
#     },
#    barmode='stack'
)
fig = go.Figure(data=data, layout=layout)

# iplot(fig)
py.iplot(fig, filename='Aragon_investor_percentage_breakdown')


# In[65]:

import requests


# In[67]:

def get_token_amount(token, address):
    req = requests.get(url='https://api.etherscan.io/api?module=account&action=tokenbalance&tokenname='+token+'&address='+address+'&tag=latest&apikey='+key)
    try:
        print(token, ' amount: ', float(json.loads(req.text)['result'])/1e18)
    except:
        print('token not available')


# In[68]:

address='0x960b236A07cf122663c4303350609A66A7B288C0'
tokens = ['REP', 'DGD', 'MKR', 'GNT', 'MLN', 'SWT', 'SNGLS']
for token in tokens:
    get_token_amount(token, address)



# ## Lost Fees

# In[47]:

df.gasUsed = df.gasUsed.astype(int)
df[(df.blockNumber < '3723000')].gasUsed.sum()


# In[51]:

len(df[(df.blockNumber < '3723000') & (df.value != 0)])


# In[50]:

df.loc[0]


# ## Last Block and Timeframe

# In[62]:

df[(df.isError == '0') & (df.value != 0)]['blockNumber'].values[-1]


# In[64]:

(3723134 - 3723000) * 15 / 60


# In[97]:

df[df['from'] == '0xfbb1b73c4f0bda4f67dca266ce6ef42f520fbb98'].hash.values[0]


# In[98]:

df[df['from'] == '0xfbb1b73c4f0bda4f67dca266ce6ef42f520fbb98'].value.values[0]


# ## Out of Gas

# In[113]:

df.gas = df.gas.astype(int)
df.gasUsed = df.gasUsed.astype(int)
df.cumulativeGasUsed = df.cumulativeGasUsed.astype(int)
df[df.isError == '1'].head()


# In[122]:

print(len(df[(df.isError == '1') & (df.gasUsed == df.gas) &  (df.blockNumber > 3723000) & (df.blockNumber <= 3723134) ]))
df[(df.isError == '1') & (df.gasUsed == df.gas) &  (df.blockNumber > 3723000) & (df.blockNumber <= 3723134) ].value.sum()


# In[ ]:

6593