blockchain/Token Balances.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 227,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>address</th>\n",
       "      <th>amount</th>\n",
       "      <th>decimals</th>\n",
       "      <th>holder</th>\n",
       "      <th>isERC20</th>\n",
       "      <th>isERC721</th>\n",
       "      <th>isERC777</th>\n",
       "      <th>isERC884</th>\n",
       "      <th>isERC998</th>\n",
       "      <th>name</th>\n",
       "      <th>symbol</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0x131a99859a8bfa3251d899f0675607766736ffae</td>\n",
       "      <td>5094263311975284645421934999836566192569296108...</td>\n",
       "      <td>18</td>\n",
       "      <td>0x3f5ce5fbfe3e9af3971dd833d26ba9b5c936f0be</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>��</td>\n",
       "      <td>�؛A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0xa997b1167874ba010fe05772055815ddf203d86d</td>\n",
       "      <td>4000000000000000000000000000</td>\n",
       "      <td>18</td>\n",
       "      <td>0x3f5ce5fbfe3e9af3971dd833d26ba9b5c936f0be</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>Thunder Token</td>\n",
       "      <td>TT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0xf2eab3a2034d3f6b63734d2e08262040e3ff7b48</td>\n",
       "      <td>3278375664000000000000000000</td>\n",
       "      <td>18</td>\n",
       "      <td>0x3f5ce5fbfe3e9af3971dd833d26ba9b5c936f0be</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>CANDY</td>\n",
       "      <td>CANDY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0x6cb9daff67f07a93da266e81757cd1436cb3656d</td>\n",
       "      <td>1900010011000000000000000000</td>\n",
       "      <td>18</td>\n",
       "      <td>0x3f5ce5fbfe3e9af3971dd833d26ba9b5c936f0be</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>Matic Network tokens</td>\n",
       "      <td>MATIC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0x901cca6d2b8ee210484dc6ef1f1db71121c041b2</td>\n",
       "      <td>1900000000000000000000000000</td>\n",
       "      <td>18</td>\n",
       "      <td>0x3f5ce5fbfe3e9af3971dd833d26ba9b5c936f0be</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>Matic Token</td>\n",
       "      <td>MATIC</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      address  \\\n",
       "0  0x131a99859a8bfa3251d899f0675607766736ffae   \n",
       "1  0xa997b1167874ba010fe05772055815ddf203d86d   \n",
       "2  0xf2eab3a2034d3f6b63734d2e08262040e3ff7b48   \n",
       "3  0x6cb9daff67f07a93da266e81757cd1436cb3656d   \n",
       "4  0x901cca6d2b8ee210484dc6ef1f1db71121c041b2   \n",
       "\n",
       "                                              amount decimals  \\\n",
       "0  5094263311975284645421934999836566192569296108...       18   \n",
       "1                       4000000000000000000000000000       18   \n",
       "2                       3278375664000000000000000000       18   \n",
       "3                       1900010011000000000000000000       18   \n",
       "4                       1900000000000000000000000000       18   \n",
       "\n",
       "                                       holder  isERC20  isERC721  isERC777  \\\n",
       "0  0x3f5ce5fbfe3e9af3971dd833d26ba9b5c936f0be     True      True      True   \n",
       "1  0x3f5ce5fbfe3e9af3971dd833d26ba9b5c936f0be     True     False     False   \n",
       "2  0x3f5ce5fbfe3e9af3971dd833d26ba9b5c936f0be     True     False     False   \n",
       "3  0x3f5ce5fbfe3e9af3971dd833d26ba9b5c936f0be    False     False     False   \n",
       "4  0x3f5ce5fbfe3e9af3971dd833d26ba9b5c936f0be     True     False     False   \n",
       "\n",
       "   isERC884  isERC998                  name symbol  \n",
       "0      True      True                    ��    �؛A  \n",
       "1     False     False         Thunder Token     TT  \n",
       "2     False     False                 CANDY  CANDY  \n",
       "3     False     False  Matic Network tokens  MATIC  \n",
       "4     False     False           Matic Token  MATIC  "
      ]
     },
     "execution_count": 227,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import json\n",
    "import pandas as pd\n",
    "import requests\n",
    "\n",
    "# Retrieve data from API (top 20 tokens by biggest holders)\n",
    "wallet_address='0x47ec58D5704082527a90362a6293029d7Ed5fc4E'.lower()\n",
    "url = \"https://api.amberdata.com/blockchains/addresses/\" + wallet_address + \"/tokens?size=20\"\n",
    "headers = {'content-type': 'application/json', 'x-api-key': 'API_KEY_HERE'}\n",
    "\n",
    "response = requests.get(url, headers=headers, verify=True)\n",
    "if (not response.ok): # response.status_code\n",
    "    response.raise_for_status()\n",
    "\n",
    "# Parse JSON data\n",
    "jData = json.loads(response.content)\n",
    "\n",
    "# Load & transform JSON data into Data Frame\n",
    "df = pd.DataFrame(jData['payload']['records'])\n",
    "df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 228,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0xa997b1167874ba010fe05772055815ddf203d86d TT 18 4000000000000000000000000000\n",
      "0xf2eab3a2034d3f6b63734d2e08262040e3ff7b48 CANDY 18 3278375664000000000000000000\n",
      "0x6cb9daff67f07a93da266e81757cd1436cb3656d MATIC 18 1900010011000000000000000000\n",
      "0x901cca6d2b8ee210484dc6ef1f1db71121c041b2 MATIC 18 1900000000000000000000000000\n",
      "0x2ff2b86c156583b1135c584fd940a1996fa4230b findtherabbit.me 18 1553026016000000000000000000\n",
      "0x713a1c4876673a982a828753166c452ba9721994 findtherabbit.me 18 1553024698000000000000000000\n",
      "0xf0ef703a04cf96212031b51d373e8469687f308e ESC 18 1000000000000000000000000000\n",
      "0x5c679a0a79d495affe049c02483519d51e37f32b DMHCO 18 1000000000000000000000000000\n",
      "0xdbdd4517a581f173236157deea4be58918d6a059 SML 18 898993724000000000000000000\n",
      "0x95efd1fe6099f65a7ed524def487483221094947 CBM 18 865457936676940760000000000\n",
      "0x3506424f91fd33084466f402d5d97f05f8e3b4af CHZ 18 833092632308763625892621516\n",
      "0xe685e67c37807dc57592d638778f18ea87a4031a CELR 18 597014925000000000000000000\n",
      "0x52cf51b8d2d03251076dbe2043d6f884107049c8 CELR 18 597014925000000000000000000\n",
      "0xdf1d6405df92d981a2fb3ce68f6a03bac6c0e41f VRA 18 492827657241213991469104535\n",
      "0x85117168851e99b6fa06bc8a58e035dc50587541 FET(Fetch.AI) 18 334369296750000000000000000\n",
      "0x87210f1d3422ba75b6c40c63c78d79324dabcd55 EOST 18 315637640000000000000000000\n",
      "0x0c6f5f7d555e7518f6841a79436bd2b1eef03381 COCOS 18 304988878788500489997510000\n",
      "0xa15c7ebe1f07caf6bff097d8a589fb8ac49ae5b3 NPXS 18 293469903005694155157637943\n",
      "0x9f235d23354857efe6c541db92a9ef1877689bcb BOLT 18 265389503880740436918748612\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>value</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>address</th>\n",
       "      <th>symbol</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4.000000e+27</td>\n",
       "      <td>1553817600000</td>\n",
       "      <td>0xa997b1167874ba010fe05772055815ddf203d86d</td>\n",
       "      <td>TT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3.278376e+27</td>\n",
       "      <td>1516665600000</td>\n",
       "      <td>0xf2eab3a2034d3f6b63734d2e08262040e3ff7b48</td>\n",
       "      <td>CANDY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.900010e+27</td>\n",
       "      <td>1556236800000</td>\n",
       "      <td>0x6cb9daff67f07a93da266e81757cd1436cb3656d</td>\n",
       "      <td>MATIC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.900000e+27</td>\n",
       "      <td>1555804800000</td>\n",
       "      <td>0x901cca6d2b8ee210484dc6ef1f1db71121c041b2</td>\n",
       "      <td>MATIC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.553026e+27</td>\n",
       "      <td>1553040000000</td>\n",
       "      <td>0x2ff2b86c156583b1135c584fd940a1996fa4230b</td>\n",
       "      <td>findtherabbit.me</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          value      timestamp                                     address  \\\n",
       "0  4.000000e+27  1553817600000  0xa997b1167874ba010fe05772055815ddf203d86d   \n",
       "0  3.278376e+27  1516665600000  0xf2eab3a2034d3f6b63734d2e08262040e3ff7b48   \n",
       "0  1.900010e+27  1556236800000  0x6cb9daff67f07a93da266e81757cd1436cb3656d   \n",
       "0  1.900000e+27  1555804800000  0x901cca6d2b8ee210484dc6ef1f1db71121c041b2   \n",
       "0  1.553026e+27  1553040000000  0x2ff2b86c156583b1135c584fd940a1996fa4230b   \n",
       "\n",
       "             symbol  \n",
       "0                TT  \n",
       "0             CANDY  \n",
       "0             MATIC  \n",
       "0             MATIC  \n",
       "0  findtherabbit.me  "
      ]
     },
     "execution_count": 228,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Download token balances for each top 20 holders\n",
    "balances = pd.DataFrame(columns=['symbol', 'timestamp', 'wallet_address', 'value'])\n",
    "frames = []\n",
    "\n",
    "for index, row in df.iloc[1:].iterrows():\n",
    "    print(row['address'], row['symbol'], row['decimals'], row['amount'])\n",
    "\n",
    "    # Download historical holders\n",
    "    url = \"https://api.amberdata.com/blockchains/tokens/\" + row['address'] + \"/holders/historical?holderAddresses=\" + wallet_address + \"&timeFrame=30d&timeFormat=iso\"\n",
    "    response = requests.get(url, headers=headers, verify=True)\n",
    "    if (not response.ok): # response.content, response.status_code\n",
    "        response.raise_for_status()\n",
    "\n",
    "    # Parse JSON data\n",
    "    jData2 = json.loads(response.content)\n",
    "    df2 = pd.DataFrame(jData2['payload']['data'])\n",
    "    df2['address'] = row['address']\n",
    "    df2['symbol'] = row['symbol']\n",
    "    df2.rename({wallet_address: 'value'}, axis=1, inplace=True)\n",
    "    frames.append(df2)\n",
    "\n",
    "balances = pd.concat(frames, sort=False)\n",
    "balances.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 229,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>timestamp</th>\n",
       "      <th>TT</th>\n",
       "      <th>CANDY</th>\n",
       "      <th>MATIC</th>\n",
       "      <th>findtherabbit.me</th>\n",
       "      <th>ESC</th>\n",
       "      <th>DMHCO</th>\n",
       "      <th>SML</th>\n",
       "      <th>CBM</th>\n",
       "      <th>CHZ</th>\n",
       "      <th>CELR</th>\n",
       "      <th>VRA</th>\n",
       "      <th>FET(Fetch.AI)</th>\n",
       "      <th>EOST</th>\n",
       "      <th>COCOS</th>\n",
       "      <th>NPXS</th>\n",
       "      <th>BOLT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4.000000e+27</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.516666e+12</td>\n",
       "      <td>3.278376e+27</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.556237e+12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.900010e+27</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.555805e+12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.900000e+27</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.553040e+12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.553026e+27</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      timestamp            TT         CANDY         MATIC  findtherabbit.me  \\\n",
       "0  4.000000e+27           NaN           NaN           NaN               NaN   \n",
       "1  1.516666e+12  3.278376e+27           NaN           NaN               NaN   \n",
       "2  1.556237e+12           NaN  1.900010e+27           NaN               NaN   \n",
       "3  1.555805e+12           NaN  1.900000e+27           NaN               NaN   \n",
       "4  1.553040e+12           NaN           NaN  1.553026e+27               NaN   \n",
       "\n",
       "   ESC  DMHCO  SML  CBM  CHZ  CELR  VRA  FET(Fetch.AI)  EOST  COCOS  NPXS  \\\n",
       "0  NaN    NaN  NaN  NaN  NaN   NaN  NaN            NaN   NaN    NaN   NaN   \n",
       "1  NaN    NaN  NaN  NaN  NaN   NaN  NaN            NaN   NaN    NaN   NaN   \n",
       "2  NaN    NaN  NaN  NaN  NaN   NaN  NaN            NaN   NaN    NaN   NaN   \n",
       "3  NaN    NaN  NaN  NaN  NaN   NaN  NaN            NaN   NaN    NaN   NaN   \n",
       "4  NaN    NaN  NaN  NaN  NaN   NaN  NaN            NaN   NaN    NaN   NaN   \n",
       "\n",
       "   BOLT  \n",
       "0  None  \n",
       "1  None  \n",
       "2  None  \n",
       "3  None  \n",
       "4  None  "
      ]
     },
     "execution_count": 229,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import datetime\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "# balances['timestamp'] = balances['timestamp'].apply(lambda x: datetime.datetime.fromtimestamp(x/1000))\n",
    "# balances['value'] = balances['value'].astype('float64')\n",
    "\n",
    "symbols = balances['symbol'].unique()\n",
    "symbol_indexes = {}\n",
    "timestamps = balances['timestamp'].unique()\n",
    "# print(symbols)\n",
    "\n",
    "i = 0\n",
    "for s in symbols:\n",
    "    symbol_indexes[s] = i\n",
    "    i += 1\n",
    "    \n",
    "token_balances = pd.DataFrame(columns=np.append(symbols, 'timestamp'))\n",
    "\n",
    "pivot = {}\n",
    "for s in symbols:\n",
    "    for t in timestamps:\n",
    "        if not t in pivot:\n",
    "            pivot[t] = {}\n",
    "        if balances.loc[(balances.symbol==s) & (balances.timestamp==t)].value.empty == False:\n",
    "            pivot[t][s] = balances.loc[(balances.symbol==s) & (balances.timestamp==t)].value.values[0]\n",
    "#         else:\n",
    "#             pivot[t][s] = None\n",
    "\n",
    "rows = []\n",
    "for t in pivot.keys():\n",
    "    row = [None] * (1 + symbols.size)\n",
    "    for s in pivot[t].keys():\n",
    "        row[0] = t\n",
    "        row[symbol_indexes[s]] = pivot[t][s]\n",
    "        rows.append(row)\n",
    "\n",
    "token_balances = pd.DataFrame(rows, columns=np.append(['timestamp'], symbols))\n",
    "token_balances.head(5)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}