Skip to content

Instantly share code, notes, and snippets.

@Evan-Kim2028

Evan-Kim2028/univ3_tokenbalances.ipynb

Created February 1, 2023 23:08
Show Gist options
  • Save Evan-Kim2028/df57d5528c7a35fb40cc308eba3dfead to your computer and use it in GitHub Desktop.
Save Evan-Kim2028/df57d5528c7a35fb40cc308eba3dfead to your computer and use it in GitHub Desktop.
Download ZIP
Display the source blob
Display the rendered blob
Raw
univ3_tokenbalances.ipynb
{
"cells": [
{
"cell_type": "code",
"execution_count": 17,
"metadata": {},
"outputs": [],
"source": [
"import subgrounds\n",
"import pandas as pd\n",
"\n",
"from subgrounds.subgrounds import Subgrounds"
]
},
{
"cell_type": "code",
"execution_count": 18,
"metadata": {},
"outputs": [],
"source": [
"# maximize row width to see tx ids\n",
"pd.set_option('display.max_columns', None)\n",
"pd.set_option('display.expand_frame_repr', False)\n",
"pd.set_option('max_colwidth', None)"
]
},
{
"cell_type": "code",
"execution_count": 19,
"metadata": {},
"outputs": [],
"source": [
"sg = Subgrounds()\n",
"\n",
"sub = sg.load_subgraph('https://api.thegraph.com/subgraphs/name/messari/uniswap-v3-ethereum')"
]
},
{
"cell_type": "code",
"execution_count": 20,
"metadata": {},
"outputs": [],
"source": [
"# https://info.uniswap.org/#/pools/0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640\n",
"# https://etherscan.io/address/0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640\n",
"\n",
"# USDC/WETH \n",
"lp_name = '0x88e6A0c2dDD26FEEb64F039a2c41296FcB3f5640'"
]
},
{
"cell_type": "code",
"execution_count": 21,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"FieldPath(https://api.thegraph.com/subgraphs/name/messari/uniswap-v3-ethereum, Query, ['liquidityPools'])"
]
},
"execution_count": 21,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"sub.Query.liquidityPools # check for a schema to query"
]
},
{
"cell_type": "code",
"execution_count": 22,
"metadata": {},
"outputs": [],
"source": [
"fieldpath = sub.Query.liquidityPools(\n",
" # where = {\n",
" # 'pool': lp_name\n",
" # }\n",
")"
]
},
{
"cell_type": "code",
"execution_count": 46,
"metadata": {},
"outputs": [],
"source": [
"col_query_list = list(fieldpath.__dict__.keys())\n",
"# remove string values that start with _\n",
"col_query_list = [col for col in col_query_list if not col.startswith('_')]"
]
},
{
"cell_type": "code",
"execution_count": 51,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"['id',\n",
" 'protocol',\n",
" 'name',\n",
" 'symbol',\n",
" 'inputTokens',\n",
" 'outputToken',\n",
" 'rewardTokens',\n",
" 'fees',\n",
" 'isSingleSided',\n",
" 'createdTimestamp',\n",
" 'createdBlockNumber',\n",
" 'totalValueLockedUSD',\n",
" 'cumulativeSupplySideRevenueUSD',\n",
" 'cumulativeProtocolSideRevenueUSD',\n",
" 'cumulativeTotalRevenueUSD',\n",
" 'cumulativeVolumeUSD',\n",
" 'inputTokenBalances',\n",
" 'inputTokenWeights',\n",
" 'outputTokenSupply',\n",
" 'outputTokenPriceUSD',\n",
" 'stakedOutputTokenAmount',\n",
" 'rewardTokenEmissionsAmount',\n",
" 'rewardTokenEmissionsUSD',\n",
" 'dailySnapshots',\n",
" 'hourlySnapshots',\n",
" 'deposits',\n",
" 'withdraws',\n",
" 'swaps']"
]
},
"execution_count": 51,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"col_query_list"
]
},
{
"cell_type": "code",
"execution_count": 41,
"metadata": {},
"outputs": [],
"source": [
"df1 = sg.query_df(fieldpath, col_query_list)"
]
},
{
"cell_type": "code",
"execution_count": 42,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"Index(['id', 'protocol', 'name', 'symbol', 'inputTokens', 'outputToken',\n",
" 'rewardTokens', 'fees', 'isSingleSided', 'createdTimestamp',\n",
" 'createdBlockNumber', 'totalValueLockedUSD',\n",
" 'cumulativeSupplySideRevenueUSD', 'cumulativeProtocolSideRevenueUSD',\n",
" 'cumulativeTotalRevenueUSD', 'cumulativeVolumeUSD'],\n",
" dtype='object')"
]
},
"execution_count": 42,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df1.columns"
]
},
{
"cell_type": "code",
"execution_count": 50,
"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>liquidityPools_id</th>\n",
" <th>liquidityPools_name</th>\n",
" <th>liquidityPools_inputTokenBalances</th>\n",
" <th>liquidityPools_createdBlockNumber</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>0x0001fcbba8eb491c3ccfeddc5a5caba1a98c4c28</td>\n",
" <td>Uniswap V3 BinanceCZ/Wrapped Ether 1%</td>\n",
" <td>30000999999999999716385</td>\n",
" <td>12842087</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>0x0001fcbba8eb491c3ccfeddc5a5caba1a98c4c28</td>\n",
" <td>Uniswap V3 BinanceCZ/Wrapped Ether 1%</td>\n",
" <td>13655515497330990</td>\n",
" <td>12842087</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>0x0002e63328169d7feea121f1e32e4f620abf0352</td>\n",
" <td>Uniswap V3 Wrapped NXM/ichi.farm 0.3%</td>\n",
" <td>793998807708161266250</td>\n",
" <td>14387035</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>0x0002e63328169d7feea121f1e32e4f620abf0352</td>\n",
" <td>Uniswap V3 Wrapped NXM/ichi.farm 0.3%</td>\n",
" <td>3444153501174</td>\n",
" <td>14387035</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>0x0003c9d75e102b8a9fe77280ca410e9135beb150</td>\n",
" <td>Uniswap V3 ButtonTranche bWBTC A/Tether USD 1%</td>\n",
" <td>1000001</td>\n",
" <td>15651911</td>\n",
" </tr>\n",
" <tr>\n",
" <th>...</th>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>195</th>\n",
" <td>0x026babd2ae9379525030fc2574e39bc156c10583</td>\n",
" <td>Uniswap V3 Wrapped BTC/USD Coin 0.01%</td>\n",
" <td>102054608</td>\n",
" <td>15531569</td>\n",
" </tr>\n",
" <tr>\n",
" <th>196</th>\n",
" <td>0x026c05d9c9a9e4b095c78e31824959009344561a</td>\n",
" <td>Uniswap V3 Imperial Credits/Wrapped Ether 0.01%</td>\n",
" <td>314006691664</td>\n",
" <td>15871766</td>\n",
" </tr>\n",
" <tr>\n",
" <th>197</th>\n",
" <td>0x026c05d9c9a9e4b095c78e31824959009344561a</td>\n",
" <td>Uniswap V3 Imperial Credits/Wrapped Ether 0.01%</td>\n",
" <td>299999999999715532</td>\n",
" <td>15871766</td>\n",
" </tr>\n",
" <tr>\n",
" <th>198</th>\n",
" <td>0x0273928ebcb46f954489dd1c40b1113f2aedd05e</td>\n",
" <td>Uniswap V3 USHI/Wrapped Ether 0.01%</td>\n",
" <td>14</td>\n",
" <td>15749360</td>\n",
" </tr>\n",
" <tr>\n",
" <th>199</th>\n",
" <td>0x0273928ebcb46f954489dd1c40b1113f2aedd05e</td>\n",
" <td>Uniswap V3 USHI/Wrapped Ether 0.01%</td>\n",
" <td>9</td>\n",
" <td>15749360</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>200 rows × 4 columns</p>\n",
"</div>"
],
"text/plain": [
" liquidityPools_id liquidityPools_name liquidityPools_inputTokenBalances liquidityPools_createdBlockNumber\n",
"0 0x0001fcbba8eb491c3ccfeddc5a5caba1a98c4c28 Uniswap V3 BinanceCZ/Wrapped Ether 1% 30000999999999999716385 12842087\n",
"1 0x0001fcbba8eb491c3ccfeddc5a5caba1a98c4c28 Uniswap V3 BinanceCZ/Wrapped Ether 1% 13655515497330990 12842087\n",
"2 0x0002e63328169d7feea121f1e32e4f620abf0352 Uniswap V3 Wrapped NXM/ichi.farm 0.3% 793998807708161266250 14387035\n",
"3 0x0002e63328169d7feea121f1e32e4f620abf0352 Uniswap V3 Wrapped NXM/ichi.farm 0.3% 3444153501174 14387035\n",
"4 0x0003c9d75e102b8a9fe77280ca410e9135beb150 Uniswap V3 ButtonTranche bWBTC A/Tether USD 1% 1000001 15651911\n",
".. ... ... ... ...\n",
"195 0x026babd2ae9379525030fc2574e39bc156c10583 Uniswap V3 Wrapped BTC/USD Coin 0.01% 102054608 15531569\n",
"196 0x026c05d9c9a9e4b095c78e31824959009344561a Uniswap V3 Imperial Credits/Wrapped Ether 0.01% 314006691664 15871766\n",
"197 0x026c05d9c9a9e4b095c78e31824959009344561a Uniswap V3 Imperial Credits/Wrapped Ether 0.01% 299999999999715532 15871766\n",
"198 0x0273928ebcb46f954489dd1c40b1113f2aedd05e Uniswap V3 USHI/Wrapped Ether 0.01% 14 15749360\n",
"199 0x0273928ebcb46f954489dd1c40b1113f2aedd05e Uniswap V3 USHI/Wrapped Ether 0.01% 9 15749360\n",
"\n",
"[200 rows x 4 columns]"
]
},
"execution_count": 50,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"sg.query_df(\n",
" [ \n",
" fieldpath.id,\n",
" fieldpath.name,\n",
" fieldpath.inputTokenBalances,\n",
" fieldpath.createdBlockNumber\n",
" ]\n",
")"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
}
],
"metadata": {
"kernelspec": {
"display_name": "ds",
"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.10.6"
},
"orig_nbformat": 4,
"vscode": {
"interpreter": {
"hash": "6e4771a491a32973a3d91a7498ed105fbed006d0c3342348d97c77c956876e4a"
}
}
},
"nbformat": 4,
"nbformat_minor": 2
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment