notebooks/data_acquisition.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Digital Twin Data Acquisition\n",
    "\n",
    "In this notebook, we will use Big Query and The Graph to obtain the signals required for the Rai Digital Twin. We will get state snapshots and calculate the absolute changes between time periods. \n",
    "\n",
    "\n",
    "## Mapping of specification states to data\n",
    "The quantity state variables of the system are:\n",
    "* ETH in collateral = collateral\n",
    "* ETH in Uniswap = reserve1\n",
    "* RAI in Uniswap = reserve0 \n",
    "* RAI drawn from SAFEs = erc20CoinTotalSupply\n",
    "\n",
    "The metric state variables of the system are:\n",
    "\n",
    "* Market Price of RAI in ETH = marketPriceEth\n",
    "* Market Price of RAI in USD = marketPriceUsd\n",
    "* Market Price of ETH in USD = ETH Price (OSM)\n",
    "\n",
    "The metric control variables of the system are:\n",
    "\n",
    "* Redemption Price of RAI in USD = RedemptionPrice\n",
    "* Redemption Price of RAI in ETH = RedemptionPriceinEth"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import libraries\n",
    "import os\n",
    "from google.cloud import bigquery\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import scipy.stats as stats\n",
    "import numpy as np\n",
    "import json\n",
    "import requests\n",
    "import os\n",
    "# The Graph URL\n",
    "url = 'https://api.thegraph.com/subgraphs/name/reflexer-labs/rai-mainnet'\n",
    "\n",
    "%matplotlib inline\n",
    "\n",
    "# constants\n",
    "constant = 1000000000000000000\n",
    "\n",
    "#defining creditionals\n",
    "os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = '/Users/aclarkdata/Downloads/raidata-5e1723d42cf5.json'\n",
    "\n",
    "# initializing agent\n",
    "client = bigquery.Client()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>block_number</th>\n",
       "      <th>ETH Price (OSM)</th>\n",
       "      <th>block_timestamp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>12371493</td>\n",
       "      <td>3349.780000</td>\n",
       "      <td>2021-05-05 02:23:19+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12371765</td>\n",
       "      <td>3349.780000</td>\n",
       "      <td>2021-05-05 03:22:45+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12372918</td>\n",
       "      <td>3297.580000</td>\n",
       "      <td>2021-05-05 07:34:45+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12373045</td>\n",
       "      <td>3274.547289</td>\n",
       "      <td>2021-05-05 08:04:07+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12373322</td>\n",
       "      <td>3342.258435</td>\n",
       "      <td>2021-05-05 09:03:55+00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   block_number  ETH Price (OSM)           block_timestamp\n",
       "0      12371493      3349.780000 2021-05-05 02:23:19+00:00\n",
       "1      12371765      3349.780000 2021-05-05 03:22:45+00:00\n",
       "2      12372918      3297.580000 2021-05-05 07:34:45+00:00\n",
       "3      12373045      3274.547289 2021-05-05 08:04:07+00:00\n",
       "4      12373322      3342.258435 2021-05-05 09:03:55+00:00"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# SQL query\n",
    "sql = \"\"\"\n",
    "SELECT \n",
    "* \n",
    "FROM `blockchain-etl.ethereum_rai.ExternallyFundedOSM_event_UpdateResult`\n",
    "ORDER By block_timestamp ASC\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "eth_price_OSM = client.query(sql).to_dataframe()\n",
    "eth_price_OSM['ETH Price (OSM)'] = eth_price_OSM['newMedian'].astype(float)/constant\n",
    "# subset\n",
    "eth_price_OSM = eth_price_OSM[['block_number','ETH Price (OSM)','block_timestamp']]\n",
    "\n",
    "eth_price_OSM.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>block_number</th>\n",
       "      <th>ETH Price (OSM)</th>\n",
       "      <th>block_timestamp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>425</th>\n",
       "      <td>12584395</td>\n",
       "      <td>2702.280000</td>\n",
       "      <td>2021-06-07 01:05:54+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>426</th>\n",
       "      <td>12585445</td>\n",
       "      <td>2757.389589</td>\n",
       "      <td>2021-06-07 05:07:45+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>427</th>\n",
       "      <td>12586509</td>\n",
       "      <td>2773.590000</td>\n",
       "      <td>2021-06-07 09:06:20+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>428</th>\n",
       "      <td>12587589</td>\n",
       "      <td>2761.044990</td>\n",
       "      <td>2021-06-07 13:15:51+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>429</th>\n",
       "      <td>12588614</td>\n",
       "      <td>2828.150972</td>\n",
       "      <td>2021-06-07 17:06:11+00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     block_number  ETH Price (OSM)           block_timestamp\n",
       "425      12584395      2702.280000 2021-06-07 01:05:54+00:00\n",
       "426      12585445      2757.389589 2021-06-07 05:07:45+00:00\n",
       "427      12586509      2773.590000 2021-06-07 09:06:20+00:00\n",
       "428      12587589      2761.044990 2021-06-07 13:15:51+00:00\n",
       "429      12588614      2828.150972 2021-06-07 17:06:11+00:00"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eth_price_OSM.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>block_number</th>\n",
       "      <th>ETH Price (OSM)</th>\n",
       "      <th>block_timestamp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11848351</td>\n",
       "      <td>1803.656430</td>\n",
       "      <td>2021-02-13 12:46:18+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11860755</td>\n",
       "      <td>1803.656430</td>\n",
       "      <td>2021-02-15 10:16:16+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11861008</td>\n",
       "      <td>1763.974936</td>\n",
       "      <td>2021-02-15 11:13:28+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11861237</td>\n",
       "      <td>1763.974936</td>\n",
       "      <td>2021-02-15 12:07:36+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11861502</td>\n",
       "      <td>1775.977923</td>\n",
       "      <td>2021-02-15 13:07:59+00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   block_number  ETH Price (OSM)           block_timestamp\n",
       "0      11848351      1803.656430 2021-02-13 12:46:18+00:00\n",
       "1      11860755      1803.656430 2021-02-15 10:16:16+00:00\n",
       "2      11861008      1763.974936 2021-02-15 11:13:28+00:00\n",
       "3      11861237      1763.974936 2021-02-15 12:07:36+00:00\n",
       "4      11861502      1775.977923 2021-02-15 13:07:59+00:00"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# SQL query\n",
    "sql = \"\"\"\n",
    "SELECT \n",
    "* \n",
    "FROM `blockchain-etl.ethereum_rai.OSM_event_UpdateResult`\n",
    "WHERE block_number < 12371493\n",
    "ORDER By block_timestamp ASC\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "eth_price_OSM_old = client.query(sql).to_dataframe()\n",
    "eth_price_OSM_old['ETH Price (OSM)'] = eth_price_OSM_old['newMedian'].astype(float)/constant\n",
    "# subset\n",
    "eth_price_OSM_old = eth_price_OSM_old[['block_number','ETH Price (OSM)','block_timestamp']]\n",
    "\n",
    "eth_price_OSM_old.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>block_number</th>\n",
       "      <th>ETH Price (OSM)</th>\n",
       "      <th>block_timestamp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1885</th>\n",
       "      <td>12370391</td>\n",
       "      <td>3390.623840</td>\n",
       "      <td>2021-05-04 22:09:45+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1886</th>\n",
       "      <td>12370625</td>\n",
       "      <td>3347.555806</td>\n",
       "      <td>2021-05-04 23:10:04+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1887</th>\n",
       "      <td>12370903</td>\n",
       "      <td>3301.810000</td>\n",
       "      <td>2021-05-05 00:10:50+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1888</th>\n",
       "      <td>12371173</td>\n",
       "      <td>3264.914896</td>\n",
       "      <td>2021-05-05 01:09:41+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1889</th>\n",
       "      <td>12371432</td>\n",
       "      <td>3334.690000</td>\n",
       "      <td>2021-05-05 02:10:02+00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      block_number  ETH Price (OSM)           block_timestamp\n",
       "1885      12370391      3390.623840 2021-05-04 22:09:45+00:00\n",
       "1886      12370625      3347.555806 2021-05-04 23:10:04+00:00\n",
       "1887      12370903      3301.810000 2021-05-05 00:10:50+00:00\n",
       "1888      12371173      3264.914896 2021-05-05 01:09:41+00:00\n",
       "1889      12371432      3334.690000 2021-05-05 02:10:02+00:00"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eth_price_OSM_old.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "eth_price = eth_price_OSM_old.append(eth_price_OSM)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>block_number</th>\n",
       "      <th>ETH Price (OSM)</th>\n",
       "      <th>block_timestamp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11848351</td>\n",
       "      <td>1803.656430</td>\n",
       "      <td>2021-02-13 12:46:18+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11860755</td>\n",
       "      <td>1803.656430</td>\n",
       "      <td>2021-02-15 10:16:16+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11861008</td>\n",
       "      <td>1763.974936</td>\n",
       "      <td>2021-02-15 11:13:28+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11861237</td>\n",
       "      <td>1763.974936</td>\n",
       "      <td>2021-02-15 12:07:36+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11861502</td>\n",
       "      <td>1775.977923</td>\n",
       "      <td>2021-02-15 13:07:59+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>425</th>\n",
       "      <td>12584395</td>\n",
       "      <td>2702.280000</td>\n",
       "      <td>2021-06-07 01:05:54+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>426</th>\n",
       "      <td>12585445</td>\n",
       "      <td>2757.389589</td>\n",
       "      <td>2021-06-07 05:07:45+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>427</th>\n",
       "      <td>12586509</td>\n",
       "      <td>2773.590000</td>\n",
       "      <td>2021-06-07 09:06:20+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>428</th>\n",
       "      <td>12587589</td>\n",
       "      <td>2761.044990</td>\n",
       "      <td>2021-06-07 13:15:51+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>429</th>\n",
       "      <td>12588614</td>\n",
       "      <td>2828.150972</td>\n",
       "      <td>2021-06-07 17:06:11+00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2320 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     block_number  ETH Price (OSM)           block_timestamp\n",
       "0        11848351      1803.656430 2021-02-13 12:46:18+00:00\n",
       "1        11860755      1803.656430 2021-02-15 10:16:16+00:00\n",
       "2        11861008      1763.974936 2021-02-15 11:13:28+00:00\n",
       "3        11861237      1763.974936 2021-02-15 12:07:36+00:00\n",
       "4        11861502      1775.977923 2021-02-15 13:07:59+00:00\n",
       "..            ...              ...                       ...\n",
       "425      12584395      2702.280000 2021-06-07 01:05:54+00:00\n",
       "426      12585445      2757.389589 2021-06-07 05:07:45+00:00\n",
       "427      12586509      2773.590000 2021-06-07 09:06:20+00:00\n",
       "428      12587589      2761.044990 2021-06-07 13:15:51+00:00\n",
       "429      12588614      2828.150972 2021-06-07 17:06:11+00:00\n",
       "\n",
       "[2320 rows x 3 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eth_price"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "blocknumbers = eth_price.block_number.values.tolist()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>block_number</th>\n",
       "      <th>EthInUniswap</th>\n",
       "      <th>RaiInUniswap</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2315</th>\n",
       "      <td>12584395</td>\n",
       "      <td>6564.157346459202112906</td>\n",
       "      <td>6014011.618217051683984889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2316</th>\n",
       "      <td>12585445</td>\n",
       "      <td>6527.685380199155840328</td>\n",
       "      <td>6047050.67719801013286767</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2317</th>\n",
       "      <td>12586509</td>\n",
       "      <td>6565.000884794524909542</td>\n",
       "      <td>6039329.122833301597860332</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2318</th>\n",
       "      <td>12587589</td>\n",
       "      <td>6495.694866208574513864</td>\n",
       "      <td>6107233.207612577182605722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2319</th>\n",
       "      <td>12588614</td>\n",
       "      <td>6541.01895026488098977</td>\n",
       "      <td>6065035.164579718018465007</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      block_number             EthInUniswap                RaiInUniswap\n",
       "2315      12584395  6564.157346459202112906  6014011.618217051683984889\n",
       "2316      12585445  6527.685380199155840328   6047050.67719801013286767\n",
       "2317      12586509  6565.000884794524909542  6039329.122833301597860332\n",
       "2318      12587589  6495.694866208574513864  6107233.207612577182605722\n",
       "2319      12588614   6541.01895026488098977  6065035.164579718018465007"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "uniswap_url = 'https://api.thegraph.com/subgraphs/name/uniswap/uniswap-v2'\n",
    "pair = []\n",
    "for i in blocknumbers:\n",
    "    query = '''\n",
    "    {\n",
    "      pairs(block: {number:%s}, where: {id: \"0x8ae720a71622e824f576b4a8c03031066548a3b1\"}){\n",
    "        id,\n",
    "        token0 {\n",
    "          id\n",
    "        },\n",
    "        token1 {\n",
    "          id\n",
    "        }\n",
    "        reserve0,\n",
    "        reserve1,\n",
    "        totalSupply,\n",
    "        reserveETH,\n",
    "        reserveUSD,\n",
    "        token0Price,\n",
    "        token1Price\n",
    "      }\n",
    "    }\n",
    "    \n",
    "    ''' % i\n",
    "    r = requests.post(uniswap_url, json = {'query':query})\n",
    "    s = json.loads(r.content)['data']['pairs']\n",
    "    pair.append(s)\n",
    "        \n",
    "pairState = pd.DataFrame(pair)\n",
    "\n",
    "pairState['block_number'] = blocknumbers\n",
    "pairState['data'] = pairState[0]\n",
    "del pairState[0]\n",
    "pairState = pairState[1:]\n",
    "\n",
    "\n",
    "pairState['EthInUniswap'] = pairState.data.apply(lambda x: x['reserve1'])\n",
    "pairState['RaiInUniswap'] = pairState.data.apply(lambda x: x['reserve0'])\n",
    "\n",
    "# subset \n",
    "pairState = pairState[['block_number','EthInUniswap','RaiInUniswap']]\n",
    "pairState.tail()\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>block_number</th>\n",
       "      <th>data</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11860755</td>\n",
       "      <td>{'id': '0x8ae720a71622e824f576b4a8c03031066548...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11861008</td>\n",
       "      <td>{'id': '0x8ae720a71622e824f576b4a8c03031066548...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11861237</td>\n",
       "      <td>{'id': '0x8ae720a71622e824f576b4a8c03031066548...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11861502</td>\n",
       "      <td>{'id': '0x8ae720a71622e824f576b4a8c03031066548...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>11861791</td>\n",
       "      <td>{'id': '0x8ae720a71622e824f576b4a8c03031066548...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2315</th>\n",
       "      <td>12584395</td>\n",
       "      <td>{'id': '0x8ae720a71622e824f576b4a8c03031066548...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2316</th>\n",
       "      <td>12585445</td>\n",
       "      <td>{'id': '0x8ae720a71622e824f576b4a8c03031066548...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2317</th>\n",
       "      <td>12586509</td>\n",
       "      <td>{'id': '0x8ae720a71622e824f576b4a8c03031066548...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2318</th>\n",
       "      <td>12587589</td>\n",
       "      <td>{'id': '0x8ae720a71622e824f576b4a8c03031066548...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2319</th>\n",
       "      <td>12588614</td>\n",
       "      <td>{'id': '0x8ae720a71622e824f576b4a8c03031066548...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2319 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      block_number                                               data\n",
       "1         11860755  {'id': '0x8ae720a71622e824f576b4a8c03031066548...\n",
       "2         11861008  {'id': '0x8ae720a71622e824f576b4a8c03031066548...\n",
       "3         11861237  {'id': '0x8ae720a71622e824f576b4a8c03031066548...\n",
       "4         11861502  {'id': '0x8ae720a71622e824f576b4a8c03031066548...\n",
       "5         11861791  {'id': '0x8ae720a71622e824f576b4a8c03031066548...\n",
       "...            ...                                                ...\n",
       "2315      12584395  {'id': '0x8ae720a71622e824f576b4a8c03031066548...\n",
       "2316      12585445  {'id': '0x8ae720a71622e824f576b4a8c03031066548...\n",
       "2317      12586509  {'id': '0x8ae720a71622e824f576b4a8c03031066548...\n",
       "2318      12587589  {'id': '0x8ae720a71622e824f576b4a8c03031066548...\n",
       "2319      12588614  {'id': '0x8ae720a71622e824f576b4a8c03031066548...\n",
       "\n",
       "[2319 rows x 2 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test  = pd.DataFrame(pair)\n",
    "test['block_number'] = blocknumbers\n",
    "test['data'] = test[0]\n",
    "del test[0]\n",
    "test = test[1:]\n",
    "test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'id': '0x8ae720a71622e824f576b4a8c03031066548a3b1',\n",
       " 'reserve0': '18908049.836669973479224348',\n",
       " 'reserve1': '20412.543157392702619562',\n",
       " 'reserveETH': '40825.08631478540523912400000000001',\n",
       " 'reserveUSD': '115195549.3881934103316527484790104',\n",
       " 'token0': {'id': '0x03ab458634910aad20ef5f1c8ee96f1d6ac54919'},\n",
       " 'token0Price': '926.2956453234561924515887747803531',\n",
       " 'token1': {'id': '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'},\n",
       " 'token1Price': '0.001079568931419090007655332093230422',\n",
       " 'totalSupply': '599054.284772156919258854'}"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test[test['block_number'] == 12345529]['data'].values[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>coinAddress</th>\n",
       "      <th>coinUniswapPair</th>\n",
       "      <th>createdAt</th>\n",
       "      <th>createdAtBlock</th>\n",
       "      <th>currentCoinMedianizerUpdate</th>\n",
       "      <th>currentRedemptionPrice</th>\n",
       "      <th>currentRedemptionRate</th>\n",
       "      <th>debtAvailableToSettle</th>\n",
       "      <th>erc20CoinTotalSupply</th>\n",
       "      <th>globalDebt</th>\n",
       "      <th>globalDebtCeiling</th>\n",
       "      <th>lastPeriodicUpdate</th>\n",
       "      <th>safeCount</th>\n",
       "      <th>systemSurplus</th>\n",
       "      <th>totalActiveSafeCount</th>\n",
       "      <th>wethAddress</th>\n",
       "      <th>block_number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0x03ab458634910aad20ef5f1c8ee96f1d6ac54919</td>\n",
       "      <td>None</td>\n",
       "      <td>1529678381</td>\n",
       "      <td>5834717</td>\n",
       "      <td>None</td>\n",
       "      <td>{'value': '3.14'}</td>\n",
       "      <td>{'annualizedRate': '1', 'createdAt': '16132260...</td>\n",
       "      <td>0</td>\n",
       "      <td>2788.440432971667032725</td>\n",
       "      <td>2788.522357955470745187436691184972</td>\n",
       "      <td>115792089237316195423570985008687.9</td>\n",
       "      <td>1613338681</td>\n",
       "      <td>3</td>\n",
       "      <td>0.06853325908830422221834559248594176</td>\n",
       "      <td>3</td>\n",
       "      <td>0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2</td>\n",
       "      <td>11860755</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0x03ab458634910aad20ef5f1c8ee96f1d6ac54919</td>\n",
       "      <td>None</td>\n",
       "      <td>1529678381</td>\n",
       "      <td>5834717</td>\n",
       "      <td>None</td>\n",
       "      <td>{'value': '3.14'}</td>\n",
       "      <td>{'annualizedRate': '1', 'createdAt': '16132260...</td>\n",
       "      <td>0</td>\n",
       "      <td>2788.440432971667032725</td>\n",
       "      <td>2788.657935201718685876567866781341</td>\n",
       "      <td>115792089237316195423570985008687.9</td>\n",
       "      <td>1613384616</td>\n",
       "      <td>3</td>\n",
       "      <td>0.1087511150258265757085590152709335</td>\n",
       "      <td>3</td>\n",
       "      <td>0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2</td>\n",
       "      <td>11861008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0x03ab458634910aad20ef5f1c8ee96f1d6ac54919</td>\n",
       "      <td>None</td>\n",
       "      <td>1529678381</td>\n",
       "      <td>5834717</td>\n",
       "      <td>None</td>\n",
       "      <td>{'value': '3.14'}</td>\n",
       "      <td>{'annualizedRate': '1', 'createdAt': '16132260...</td>\n",
       "      <td>0</td>\n",
       "      <td>2788.440432971667032725</td>\n",
       "      <td>2788.657935201718685876567866781341</td>\n",
       "      <td>115792089237316195423570985008687.9</td>\n",
       "      <td>1613384616</td>\n",
       "      <td>3</td>\n",
       "      <td>0.1087511150258265757085590152709335</td>\n",
       "      <td>3</td>\n",
       "      <td>0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2</td>\n",
       "      <td>11861237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0x03ab458634910aad20ef5f1c8ee96f1d6ac54919</td>\n",
       "      <td>None</td>\n",
       "      <td>1529678381</td>\n",
       "      <td>5834717</td>\n",
       "      <td>None</td>\n",
       "      <td>{'value': '3.14'}</td>\n",
       "      <td>{'annualizedRate': '1', 'createdAt': '16132260...</td>\n",
       "      <td>0</td>\n",
       "      <td>3048.440432971667032725</td>\n",
       "      <td>3048.645954953510904626288422086589</td>\n",
       "      <td>115792089237316195423570985008687.9</td>\n",
       "      <td>1613393080</td>\n",
       "      <td>3</td>\n",
       "      <td>0.1161617985274885650688366678947734</td>\n",
       "      <td>3</td>\n",
       "      <td>0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2</td>\n",
       "      <td>11861502</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0x03ab458634910aad20ef5f1c8ee96f1d6ac54919</td>\n",
       "      <td>None</td>\n",
       "      <td>1529678381</td>\n",
       "      <td>5834717</td>\n",
       "      <td>None</td>\n",
       "      <td>{'value': '3.14'}</td>\n",
       "      <td>{'annualizedRate': '1', 'createdAt': '16132260...</td>\n",
       "      <td>0</td>\n",
       "      <td>3048.440432971667032725</td>\n",
       "      <td>3048.645954953510904626288422086589</td>\n",
       "      <td>115792089237316195423570985008687.9</td>\n",
       "      <td>1613393080</td>\n",
       "      <td>3</td>\n",
       "      <td>0.1161617985274885650688366678947734</td>\n",
       "      <td>3</td>\n",
       "      <td>0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2</td>\n",
       "      <td>11861791</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  coinAddress coinUniswapPair   createdAt  \\\n",
       "1  0x03ab458634910aad20ef5f1c8ee96f1d6ac54919            None  1529678381   \n",
       "2  0x03ab458634910aad20ef5f1c8ee96f1d6ac54919            None  1529678381   \n",
       "3  0x03ab458634910aad20ef5f1c8ee96f1d6ac54919            None  1529678381   \n",
       "4  0x03ab458634910aad20ef5f1c8ee96f1d6ac54919            None  1529678381   \n",
       "5  0x03ab458634910aad20ef5f1c8ee96f1d6ac54919            None  1529678381   \n",
       "\n",
       "  createdAtBlock currentCoinMedianizerUpdate currentRedemptionPrice  \\\n",
       "1        5834717                        None      {'value': '3.14'}   \n",
       "2        5834717                        None      {'value': '3.14'}   \n",
       "3        5834717                        None      {'value': '3.14'}   \n",
       "4        5834717                        None      {'value': '3.14'}   \n",
       "5        5834717                        None      {'value': '3.14'}   \n",
       "\n",
       "                               currentRedemptionRate debtAvailableToSettle  \\\n",
       "1  {'annualizedRate': '1', 'createdAt': '16132260...                     0   \n",
       "2  {'annualizedRate': '1', 'createdAt': '16132260...                     0   \n",
       "3  {'annualizedRate': '1', 'createdAt': '16132260...                     0   \n",
       "4  {'annualizedRate': '1', 'createdAt': '16132260...                     0   \n",
       "5  {'annualizedRate': '1', 'createdAt': '16132260...                     0   \n",
       "\n",
       "      erc20CoinTotalSupply                           globalDebt  \\\n",
       "1  2788.440432971667032725  2788.522357955470745187436691184972   \n",
       "2  2788.440432971667032725  2788.657935201718685876567866781341   \n",
       "3  2788.440432971667032725  2788.657935201718685876567866781341   \n",
       "4  3048.440432971667032725  3048.645954953510904626288422086589   \n",
       "5  3048.440432971667032725  3048.645954953510904626288422086589   \n",
       "\n",
       "                     globalDebtCeiling lastPeriodicUpdate safeCount  \\\n",
       "1  115792089237316195423570985008687.9         1613338681         3   \n",
       "2  115792089237316195423570985008687.9         1613384616         3   \n",
       "3  115792089237316195423570985008687.9         1613384616         3   \n",
       "4  115792089237316195423570985008687.9         1613393080         3   \n",
       "5  115792089237316195423570985008687.9         1613393080         3   \n",
       "\n",
       "                           systemSurplus totalActiveSafeCount  \\\n",
       "1  0.06853325908830422221834559248594176                    3   \n",
       "2   0.1087511150258265757085590152709335                    3   \n",
       "3   0.1087511150258265757085590152709335                    3   \n",
       "4   0.1161617985274885650688366678947734                    3   \n",
       "5   0.1161617985274885650688366678947734                    3   \n",
       "\n",
       "                                  wethAddress  block_number  \n",
       "1  0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2      11860755  \n",
       "2  0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2      11861008  \n",
       "3  0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2      11861237  \n",
       "4  0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2      11861502  \n",
       "5  0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2      11861791  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "state = []\n",
    "for i in blocknumbers:\n",
    "    query = '''\n",
    "    {\n",
    "      systemState(block: {number:%s},id:\"current\") { \n",
    "        coinUniswapPair {\n",
    "          label\n",
    "          reserve0\n",
    "          reserve1\n",
    "          token0Price\n",
    "          token1Price\n",
    "          totalSupply\n",
    "        }\n",
    "        currentCoinMedianizerUpdate{\n",
    "          value\n",
    "        }\n",
    "        currentRedemptionRate {\n",
    "          eightHourlyRate\n",
    "          annualizedRate\n",
    "          hourlyRate\n",
    "          createdAt\n",
    "        }\n",
    "        currentRedemptionPrice {\n",
    "          value\n",
    "        }\n",
    "        erc20CoinTotalSupply\n",
    "        globalDebt\n",
    "        globalDebtCeiling\n",
    "        safeCount,\n",
    "        totalActiveSafeCount\n",
    "        coinAddress\n",
    "        wethAddress\n",
    "        systemSurplus\n",
    "        debtAvailableToSettle\n",
    "        lastPeriodicUpdate\n",
    "        createdAt\n",
    "        createdAtBlock\n",
    "      }\n",
    "    }\n",
    "    ''' % i\n",
    "    r = requests.post(url, json = {'query':query})\n",
    "    s = json.loads(r.content)['data']['systemState']\n",
    "    state.append(s)\n",
    "        \n",
    "systemState = pd.DataFrame(state)\n",
    "\n",
    "systemState['block_number'] = blocknumbers\n",
    "\n",
    "systemState = systemState[1:]\n",
    "\n",
    "systemState.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "## reserve 0= rai\n",
    "## reserve 1 = eth\n",
    "\n",
    "# The quantity state variables of the system are:\n",
    "# * ETH in collateral = collateral\n",
    "# * ETH in Uniswap = reserve1\n",
    "# * RAI in Uniswap = reserve0 \n",
    "# * RAI drawn from SAFEs = erc20CoinTotalSupply\n",
    "systemState.coinUniswapPair.values[-100:-99][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'label': 'UNISWAP_POOL_COIN',\n",
       " 'reserve0': '6065035.164579718018465007',\n",
       " 'reserve1': '6541.01895026488098977',\n",
       " 'token0Price': '927.2309422577215081656982523330028',\n",
       " 'token1Price': '0.001078479971305845955331113621049587',\n",
       " 'totalSupply': '189343.548493613835638332'}"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "systemState.coinUniswapPair.values[-1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "systemState['RedemptionRateAnnualizedRate'] = systemState.currentRedemptionRate.apply(lambda x: x['annualizedRate'])    \n",
    "systemState['RedemptionRateHourlyRate'] = systemState.currentRedemptionRate.apply(lambda x: x['hourlyRate'])\n",
    "systemState['RedemptionRateEightHourlyRate'] = systemState.currentRedemptionRate.apply(lambda x: x['eightHourlyRate'])\n",
    "systemState['RedemptionPrice'] = systemState.currentRedemptionPrice.apply(lambda x: x['value'])\n",
    "# systemState['EthInUniswap'] = systemState.coinUniswapPair.apply(lambda x: x['reserve1'])\n",
    "# systemState['RaiInUniswap'] = systemState.coinUniswapPair.apply(lambda x: x['reserve0'])\n",
    "systemState['RaiDrawnFromSAFEs'] = systemState['erc20CoinTotalSupply']\n",
    "#systemState['RAIInUniswapV2(RAI/ETH)'] = systemState.coinUniswapPair.apply(lambda x: x['reserve0'])\n",
    "del systemState['currentRedemptionRate']\n",
    "del systemState['currentRedemptionPrice']\n",
    "\n",
    "\n",
    "# subset\n",
    "\n",
    "systemState = systemState[['debtAvailableToSettle','globalDebt',\n",
    "                           'globalDebtCeiling','systemSurplus','totalActiveSafeCount',\n",
    "                           'block_number', 'RedemptionRateAnnualizedRate',\n",
    "                           'RedemptionRateHourlyRate', 'RedemptionRateEightHourlyRate',\n",
    "                        'RedemptionPrice','RaiDrawnFromSAFEs']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>marketPriceEth</th>\n",
       "      <th>marketPriceUsd</th>\n",
       "      <th>block_number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.002589462210048543650949087456484599</td>\n",
       "      <td>4.709364363675082915930020079963684</td>\n",
       "      <td>11848351</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.002481355746535171105569862943787003</td>\n",
       "      <td>4.362594137056344705089877147817243</td>\n",
       "      <td>11860755</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.002481355746535171105569862943787003</td>\n",
       "      <td>4.419265710853707004204445293455575</td>\n",
       "      <td>11861008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.002481355746535171105569862943787003</td>\n",
       "      <td>4.419265710853707004204445293455575</td>\n",
       "      <td>11861237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.001768934955701819449591545208406114</td>\n",
       "      <td>3.215446177235759811208395560891868</td>\n",
       "      <td>11861502</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           marketPriceEth  \\\n",
       "0  0.002589462210048543650949087456484599   \n",
       "1  0.002481355746535171105569862943787003   \n",
       "2  0.002481355746535171105569862943787003   \n",
       "3  0.002481355746535171105569862943787003   \n",
       "4  0.001768934955701819449591545208406114   \n",
       "\n",
       "                        marketPriceUsd  block_number  \n",
       "0  4.709364363675082915930020079963684      11848351  \n",
       "1  4.362594137056344705089877147817243      11860755  \n",
       "2  4.419265710853707004204445293455575      11861008  \n",
       "3  4.419265710853707004204445293455575      11861237  \n",
       "4  3.215446177235759811208395560891868      11861502  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hourly = []\n",
    "for i in blocknumbers:\n",
    "    query = '''\n",
    "    {\n",
    "      hourlyStats(where: {blockNumber_gt: %s}) { \n",
    "        marketPriceUsd # price of COIN in USD (uni pool price * ETH median price)\n",
    "        marketPriceEth # Price of COIN in ETH (uni pool price)\n",
    "      }\n",
    "    }\n",
    "    ''' % i\n",
    "    r = requests.post(url, json = {'query':query})\n",
    "    s = json.loads(r.content)['data']['hourlyStats'][0]\n",
    "    hourly.append(s)\n",
    "        \n",
    "hourlyStats = pd.DataFrame(hourly)\n",
    "\n",
    "hourlyStats['block_number'] = blocknumbers\n",
    "\n",
    "hourlyStats.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "safehistories = []\n",
    "for i in blocknumbers[1:]:\n",
    "    query = '''\n",
    "    {\n",
    "      safes(block: {number:%s}) {\n",
    "            collateral\n",
    "            debt\n",
    "      }\n",
    "    }\n",
    "    ''' % i\n",
    "    r = requests.post(url, json = {'query':query})\n",
    "    s = json.loads(r.content)['data']['safes']\n",
    "    t = pd.DataFrame(s)\n",
    "    t['collateral'] = t['collateral'].astype(float)\n",
    "    t['debt'] = t['debt'].astype(float)\n",
    "    safehistories.append(pd.DataFrame(t.sum().to_dict(),index=[0]))\n",
    "\n",
    "safe_history = pd.concat(safehistories)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "safehistories = []\n",
    "for i in blocknumbers[1:]:\n",
    "    query = '''\n",
    "    {\n",
    "      safes(block: {number:%s}) {\n",
    "            collateral\n",
    "            debt\n",
    "      }\n",
    "    }\n",
    "    ''' % i\n",
    "    r = requests.post(url, json = {'query':query})\n",
    "    s = json.loads(r.content)['data']['safes']\n",
    "    t = pd.DataFrame(s)\n",
    "    t['collateral'] = t['collateral'].astype(float)\n",
    "    t['debt'] = t['debt'].astype(float)\n",
    "    safehistories.append(pd.DataFrame(t.sum().to_dict(),index=[0]))\n",
    "\n",
    "safe_history = pd.concat(safehistories)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>collateral</th>\n",
       "      <th>debt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>14.89236</td>\n",
       "      <td>2788.385291</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>14.89236</td>\n",
       "      <td>2788.385291</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>14.89236</td>\n",
       "      <td>2788.385291</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>16.89236</td>\n",
       "      <td>3048.358490</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>16.89236</td>\n",
       "      <td>3048.358490</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   collateral         debt\n",
       "0    14.89236  2788.385291\n",
       "0    14.89236  2788.385291\n",
       "0    14.89236  2788.385291\n",
       "0    16.89236  3048.358490\n",
       "0    16.89236  3048.358490"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "safe_history.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "safe_history['block_number'] = blocknumbers[1:]\n",
    "safe_history.reset_index(inplace=True)\n",
    "del safe_history['index']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "states = hourlyStats.merge(systemState,how='inner',on='block_number')\n",
    "states = states.merge(safe_history,how='inner',on='block_number')\n",
    "states = states.merge(pairState,how='inner',on='block_number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "states = states.merge(eth_price,how='inner',on='block_number')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>marketPriceEth</th>\n",
       "      <th>marketPriceUsd</th>\n",
       "      <th>block_number</th>\n",
       "      <th>debtAvailableToSettle</th>\n",
       "      <th>globalDebt</th>\n",
       "      <th>globalDebtCeiling</th>\n",
       "      <th>systemSurplus</th>\n",
       "      <th>totalActiveSafeCount</th>\n",
       "      <th>RedemptionRateAnnualizedRate</th>\n",
       "      <th>RedemptionRateHourlyRate</th>\n",
       "      <th>RedemptionRateEightHourlyRate</th>\n",
       "      <th>RedemptionPrice</th>\n",
       "      <th>RaiDrawnFromSAFEs</th>\n",
       "      <th>collateral</th>\n",
       "      <th>debt</th>\n",
       "      <th>EthInUniswap</th>\n",
       "      <th>RaiInUniswap</th>\n",
       "      <th>ETH Price (OSM)</th>\n",
       "      <th>block_timestamp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.002481355746535171105569862943787003</td>\n",
       "      <td>4.362594137056344705089877147817243</td>\n",
       "      <td>11860755</td>\n",
       "      <td>0</td>\n",
       "      <td>2788.522357955470745187436691184972</td>\n",
       "      <td>115792089237316195423570985008687.9</td>\n",
       "      <td>0.06853325908830422221834559248594176</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3.14</td>\n",
       "      <td>2788.440432971667032725</td>\n",
       "      <td>14.89236</td>\n",
       "      <td>2788.385291</td>\n",
       "      <td>1.679713392792866803</td>\n",
       "      <td>676.933726709008306816</td>\n",
       "      <td>1803.656430</td>\n",
       "      <td>2021-02-15 10:16:16+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.002481355746535171105569862943787003</td>\n",
       "      <td>4.419265710853707004204445293455575</td>\n",
       "      <td>11861008</td>\n",
       "      <td>0</td>\n",
       "      <td>2788.657935201718685876567866781341</td>\n",
       "      <td>115792089237316195423570985008687.9</td>\n",
       "      <td>0.1087511150258265757085590152709335</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3.14</td>\n",
       "      <td>2788.440432971667032725</td>\n",
       "      <td>14.89236</td>\n",
       "      <td>2788.385291</td>\n",
       "      <td>1.679713392792866803</td>\n",
       "      <td>676.933726709008306816</td>\n",
       "      <td>1763.974936</td>\n",
       "      <td>2021-02-15 11:13:28+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.002481355746535171105569862943787003</td>\n",
       "      <td>4.419265710853707004204445293455575</td>\n",
       "      <td>11861237</td>\n",
       "      <td>0</td>\n",
       "      <td>2788.657935201718685876567866781341</td>\n",
       "      <td>115792089237316195423570985008687.9</td>\n",
       "      <td>0.1087511150258265757085590152709335</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3.14</td>\n",
       "      <td>2788.440432971667032725</td>\n",
       "      <td>14.89236</td>\n",
       "      <td>2788.385291</td>\n",
       "      <td>1.679713392792866803</td>\n",
       "      <td>676.933726709008306816</td>\n",
       "      <td>1763.974936</td>\n",
       "      <td>2021-02-15 12:07:36+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.001768934955701819449591545208406114</td>\n",
       "      <td>3.215446177235759811208395560891868</td>\n",
       "      <td>11861502</td>\n",
       "      <td>0</td>\n",
       "      <td>3048.645954953510904626288422086589</td>\n",
       "      <td>115792089237316195423570985008687.9</td>\n",
       "      <td>0.1161617985274885650688366678947734</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3.14</td>\n",
       "      <td>3048.440432971667032725</td>\n",
       "      <td>16.89236</td>\n",
       "      <td>3048.358490</td>\n",
       "      <td>1.418568601331794594</td>\n",
       "      <td>801.933726709008306816</td>\n",
       "      <td>1775.977923</td>\n",
       "      <td>2021-02-15 13:07:59+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.001768934955701819449591545208406114</td>\n",
       "      <td>3.215446177235759811208395560891868</td>\n",
       "      <td>11861791</td>\n",
       "      <td>0</td>\n",
       "      <td>3048.645954953510904626288422086589</td>\n",
       "      <td>115792089237316195423570985008687.9</td>\n",
       "      <td>0.1161617985274885650688366678947734</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3.14</td>\n",
       "      <td>3048.440432971667032725</td>\n",
       "      <td>16.89236</td>\n",
       "      <td>3048.358490</td>\n",
       "      <td>1.418568601331794594</td>\n",
       "      <td>801.933726709008306816</td>\n",
       "      <td>1805.792735</td>\n",
       "      <td>2021-02-15 14:07:59+00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           marketPriceEth  \\\n",
       "0  0.002481355746535171105569862943787003   \n",
       "1  0.002481355746535171105569862943787003   \n",
       "2  0.002481355746535171105569862943787003   \n",
       "3  0.001768934955701819449591545208406114   \n",
       "4  0.001768934955701819449591545208406114   \n",
       "\n",
       "                        marketPriceUsd  block_number debtAvailableToSettle  \\\n",
       "0  4.362594137056344705089877147817243      11860755                     0   \n",
       "1  4.419265710853707004204445293455575      11861008                     0   \n",
       "2  4.419265710853707004204445293455575      11861237                     0   \n",
       "3  3.215446177235759811208395560891868      11861502                     0   \n",
       "4  3.215446177235759811208395560891868      11861791                     0   \n",
       "\n",
       "                            globalDebt                    globalDebtCeiling  \\\n",
       "0  2788.522357955470745187436691184972  115792089237316195423570985008687.9   \n",
       "1  2788.657935201718685876567866781341  115792089237316195423570985008687.9   \n",
       "2  2788.657935201718685876567866781341  115792089237316195423570985008687.9   \n",
       "3  3048.645954953510904626288422086589  115792089237316195423570985008687.9   \n",
       "4  3048.645954953510904626288422086589  115792089237316195423570985008687.9   \n",
       "\n",
       "                           systemSurplus totalActiveSafeCount  \\\n",
       "0  0.06853325908830422221834559248594176                    3   \n",
       "1   0.1087511150258265757085590152709335                    3   \n",
       "2   0.1087511150258265757085590152709335                    3   \n",
       "3   0.1161617985274885650688366678947734                    3   \n",
       "4   0.1161617985274885650688366678947734                    3   \n",
       "\n",
       "  RedemptionRateAnnualizedRate RedemptionRateHourlyRate  \\\n",
       "0                            1                        1   \n",
       "1                            1                        1   \n",
       "2                            1                        1   \n",
       "3                            1                        1   \n",
       "4                            1                        1   \n",
       "\n",
       "  RedemptionRateEightHourlyRate RedemptionPrice        RaiDrawnFromSAFEs  \\\n",
       "0                             1            3.14  2788.440432971667032725   \n",
       "1                             1            3.14  2788.440432971667032725   \n",
       "2                             1            3.14  2788.440432971667032725   \n",
       "3                             1            3.14  3048.440432971667032725   \n",
       "4                             1            3.14  3048.440432971667032725   \n",
       "\n",
       "   collateral         debt          EthInUniswap            RaiInUniswap  \\\n",
       "0    14.89236  2788.385291  1.679713392792866803  676.933726709008306816   \n",
       "1    14.89236  2788.385291  1.679713392792866803  676.933726709008306816   \n",
       "2    14.89236  2788.385291  1.679713392792866803  676.933726709008306816   \n",
       "3    16.89236  3048.358490  1.418568601331794594  801.933726709008306816   \n",
       "4    16.89236  3048.358490  1.418568601331794594  801.933726709008306816   \n",
       "\n",
       "   ETH Price (OSM)           block_timestamp  \n",
       "0      1803.656430 2021-02-15 10:16:16+00:00  \n",
       "1      1763.974936 2021-02-15 11:13:28+00:00  \n",
       "2      1763.974936 2021-02-15 12:07:36+00:00  \n",
       "3      1775.977923 2021-02-15 13:07:59+00:00  \n",
       "4      1805.792735 2021-02-15 14:07:59+00:00  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "states.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>marketPriceEth</th>\n",
       "      <th>marketPriceUsd</th>\n",
       "      <th>block_number</th>\n",
       "      <th>debtAvailableToSettle</th>\n",
       "      <th>globalDebt</th>\n",
       "      <th>globalDebtCeiling</th>\n",
       "      <th>systemSurplus</th>\n",
       "      <th>totalActiveSafeCount</th>\n",
       "      <th>RedemptionRateAnnualizedRate</th>\n",
       "      <th>RedemptionRateHourlyRate</th>\n",
       "      <th>RedemptionRateEightHourlyRate</th>\n",
       "      <th>RedemptionPrice</th>\n",
       "      <th>RaiDrawnFromSAFEs</th>\n",
       "      <th>collateral</th>\n",
       "      <th>debt</th>\n",
       "      <th>EthInUniswap</th>\n",
       "      <th>RaiInUniswap</th>\n",
       "      <th>ETH Price (OSM)</th>\n",
       "      <th>block_timestamp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2309</th>\n",
       "      <td>0.00112240697297394549615101797283226</td>\n",
       "      <td>3.009869913772518142732162447740107</td>\n",
       "      <td>12579185</td>\n",
       "      <td>0</td>\n",
       "      <td>11254666.38987890039495928507191416</td>\n",
       "      <td>115792089237316195423570985008687.9</td>\n",
       "      <td>334422.2097696048411166656167537195</td>\n",
       "      <td>398</td>\n",
       "      <td>1.0280557716144304</td>\n",
       "      <td>1.0000031586143612</td>\n",
       "      <td>1.000025269194242</td>\n",
       "      <td>3.007462336651507650240484082</td>\n",
       "      <td>10829646.548524888029772788</td>\n",
       "      <td>1083.900454</td>\n",
       "      <td>250083.541093</td>\n",
       "      <td>6625.838114615076905674</td>\n",
       "      <td>5943514.593703697883733875</td>\n",
       "      <td>2683.690690</td>\n",
       "      <td>2021-06-06 06:05:41+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2310</th>\n",
       "      <td>0.001119742851210339752900229042003111</td>\n",
       "      <td>2.997289250994206395982904533877571</td>\n",
       "      <td>12580301</td>\n",
       "      <td>0</td>\n",
       "      <td>11255257.42502759395701312239432585</td>\n",
       "      <td>115792089237316195423570985008687.9</td>\n",
       "      <td>334495.6948952217890414417844415978</td>\n",
       "      <td>398</td>\n",
       "      <td>0.9917512017587372</td>\n",
       "      <td>0.9999990544516076</td>\n",
       "      <td>0.9999924356378951</td>\n",
       "      <td>3.007482317021276222905288762</td>\n",
       "      <td>10830146.548524888029772788</td>\n",
       "      <td>1083.900454</td>\n",
       "      <td>250083.541093</td>\n",
       "      <td>6589.450447779259016653</td>\n",
       "      <td>5976558.639784811539357153</td>\n",
       "      <td>2707.672804</td>\n",
       "      <td>2021-06-06 10:05:55+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2311</th>\n",
       "      <td>0.001108387134859624426138202562191223</td>\n",
       "      <td>2.987795712618607961022027524294928</td>\n",
       "      <td>12581396</td>\n",
       "      <td>0</td>\n",
       "      <td>11254262.85066739514570282239432585</td>\n",
       "      <td>115792089237316195423570985008687.9</td>\n",
       "      <td>334495.6948952217890414417844415978</td>\n",
       "      <td>397</td>\n",
       "      <td>0.9917512017587372</td>\n",
       "      <td>0.9999990544516076</td>\n",
       "      <td>0.9999924356378951</td>\n",
       "      <td>3.007470946112948759641228752</td>\n",
       "      <td>10829145.874404995566708985</td>\n",
       "      <td>1083.900454</td>\n",
       "      <td>250083.541093</td>\n",
       "      <td>6637.300038771896535518</td>\n",
       "      <td>5932304.599899358670044537</td>\n",
       "      <td>2725.020000</td>\n",
       "      <td>2021-06-06 14:05:50+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2312</th>\n",
       "      <td>0.001108387134859624426138202562191223</td>\n",
       "      <td>2.987795712618607961022027524294928</td>\n",
       "      <td>12582289</td>\n",
       "      <td>0</td>\n",
       "      <td>11254262.85066739514570282239432585</td>\n",
       "      <td>115792089237316195423570985008687.9</td>\n",
       "      <td>334495.6948952217890414417844415978</td>\n",
       "      <td>397</td>\n",
       "      <td>0.9848502766846086</td>\n",
       "      <td>0.9999982573470972</td>\n",
       "      <td>0.999986058861809</td>\n",
       "      <td>3.007457913531166416987794407</td>\n",
       "      <td>10829145.874404995566708985</td>\n",
       "      <td>1083.900454</td>\n",
       "      <td>250083.541093</td>\n",
       "      <td>6595.238341540219075928</td>\n",
       "      <td>5970269.322165615521018742</td>\n",
       "      <td>2664.550000</td>\n",
       "      <td>2021-06-06 17:35:43+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2313</th>\n",
       "      <td>0.001110815700425048432070674732878301</td>\n",
       "      <td>3.01484838493587938693177994526334</td>\n",
       "      <td>12583291</td>\n",
       "      <td>0</td>\n",
       "      <td>11269458.84471393850414366224594458</td>\n",
       "      <td>115792089237316195423570985008687.9</td>\n",
       "      <td>334641.1279151782159872447426343482</td>\n",
       "      <td>398</td>\n",
       "      <td>0.9848502766846086</td>\n",
       "      <td>0.9999982573470972</td>\n",
       "      <td>0.999986058861809</td>\n",
       "      <td>3.007442141195426393007003677</td>\n",
       "      <td>10844145.874404995566708985</td>\n",
       "      <td>1083.900454</td>\n",
       "      <td>250083.541093</td>\n",
       "      <td>6624.69863615400826378</td>\n",
       "      <td>5958843.293272671861700994</td>\n",
       "      <td>2700.841159</td>\n",
       "      <td>2021-06-06 21:05:55+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2314</th>\n",
       "      <td>0.00108158676228900473873979918684649</td>\n",
       "      <td>3.017312488066069800526750719988266</td>\n",
       "      <td>12584395</td>\n",
       "      <td>0</td>\n",
       "      <td>11273343.52215627828906702101612578</td>\n",
       "      <td>115792089237316195423570985008687.9</td>\n",
       "      <td>334713.0306017743527719623165356281</td>\n",
       "      <td>397</td>\n",
       "      <td>1.004843332270226</td>\n",
       "      <td>1.000000551557352</td>\n",
       "      <td>1.000004412467334</td>\n",
       "      <td>3.007421877781552256694398164</td>\n",
       "      <td>10847800.017651307208637556</td>\n",
       "      <td>1083.900454</td>\n",
       "      <td>250083.541093</td>\n",
       "      <td>6564.157346459202112906</td>\n",
       "      <td>6014011.618217051683984889</td>\n",
       "      <td>2702.280000</td>\n",
       "      <td>2021-06-07 01:05:54+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2315</th>\n",
       "      <td>0.001079482499594968644440033313078683</td>\n",
       "      <td>3.010808833251978593543698264644376</td>\n",
       "      <td>12585445</td>\n",
       "      <td>0</td>\n",
       "      <td>11273343.52215627828906702101612578</td>\n",
       "      <td>115792089237316195423570985008687.9</td>\n",
       "      <td>334713.0306017743527719623165356281</td>\n",
       "      <td>397</td>\n",
       "      <td>1.004843332270226</td>\n",
       "      <td>1.000000551557352</td>\n",
       "      <td>1.000004412467334</td>\n",
       "      <td>3.007429604409038912451083644</td>\n",
       "      <td>10847800.017651307208637556</td>\n",
       "      <td>1083.900454</td>\n",
       "      <td>250083.541093</td>\n",
       "      <td>6527.685380199155840328</td>\n",
       "      <td>6047050.67719801013286767</td>\n",
       "      <td>2757.389589</td>\n",
       "      <td>2021-06-07 05:07:45+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2316</th>\n",
       "      <td>0.001085634109767479233156514044862049</td>\n",
       "      <td>3.013380911523254671540900690877811</td>\n",
       "      <td>12586509</td>\n",
       "      <td>0</td>\n",
       "      <td>11271749.17020874565881833601612578</td>\n",
       "      <td>115792089237316195423570985008687.9</td>\n",
       "      <td>334713.0306017743527719623165356281</td>\n",
       "      <td>395</td>\n",
       "      <td>0.9733785853054229</td>\n",
       "      <td>0.9999969198470134</td>\n",
       "      <td>0.9999753590417518</td>\n",
       "      <td>3.007431267158340112904837268</td>\n",
       "      <td>10846195.825558383918809618</td>\n",
       "      <td>1083.900454</td>\n",
       "      <td>250083.541093</td>\n",
       "      <td>6565.000884794524909542</td>\n",
       "      <td>6039329.122833301597860332</td>\n",
       "      <td>2773.590000</td>\n",
       "      <td>2021-06-07 09:06:20+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2317</th>\n",
       "      <td>0.001076494745281016098063717208212789</td>\n",
       "      <td>2.994557766505518994467129012274117</td>\n",
       "      <td>12587589</td>\n",
       "      <td>0</td>\n",
       "      <td>11280625.25524269030657278130476457</td>\n",
       "      <td>115792089237316195423570985008687.9</td>\n",
       "      <td>334865.7694774930324295870965585239</td>\n",
       "      <td>395</td>\n",
       "      <td>0.9733785853054229</td>\n",
       "      <td>0.9999969198470134</td>\n",
       "      <td>0.9999753590417518</td>\n",
       "      <td>3.007392744675455428885367562</td>\n",
       "      <td>10854795.825558383918809618</td>\n",
       "      <td>1083.900454</td>\n",
       "      <td>250083.541093</td>\n",
       "      <td>6495.694866208574513864</td>\n",
       "      <td>6107233.207612577182605722</td>\n",
       "      <td>2761.044990</td>\n",
       "      <td>2021-06-07 13:15:51+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2318</th>\n",
       "      <td>0.001110000090153203759873940113515362</td>\n",
       "      <td>2.986133628122973482368267810746214</td>\n",
       "      <td>12588614</td>\n",
       "      <td>0</td>\n",
       "      <td>11280625.25524269030657278130476457</td>\n",
       "      <td>115792089237316195423570985008687.9</td>\n",
       "      <td>334865.7694774930324295870965585239</td>\n",
       "      <td>395</td>\n",
       "      <td>0.970124160242656</td>\n",
       "      <td>0.99999653753848</td>\n",
       "      <td>0.9999723006435193</td>\n",
       "      <td>3.007354546298602186165903776</td>\n",
       "      <td>10854795.825558383918809618</td>\n",
       "      <td>1083.900454</td>\n",
       "      <td>250083.541093</td>\n",
       "      <td>6541.01895026488098977</td>\n",
       "      <td>6065035.164579718018465007</td>\n",
       "      <td>2828.150972</td>\n",
       "      <td>2021-06-07 17:06:11+00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              marketPriceEth  \\\n",
       "2309   0.00112240697297394549615101797283226   \n",
       "2310  0.001119742851210339752900229042003111   \n",
       "2311  0.001108387134859624426138202562191223   \n",
       "2312  0.001108387134859624426138202562191223   \n",
       "2313  0.001110815700425048432070674732878301   \n",
       "2314   0.00108158676228900473873979918684649   \n",
       "2315  0.001079482499594968644440033313078683   \n",
       "2316  0.001085634109767479233156514044862049   \n",
       "2317  0.001076494745281016098063717208212789   \n",
       "2318  0.001110000090153203759873940113515362   \n",
       "\n",
       "                           marketPriceUsd  block_number debtAvailableToSettle  \\\n",
       "2309  3.009869913772518142732162447740107      12579185                     0   \n",
       "2310  2.997289250994206395982904533877571      12580301                     0   \n",
       "2311  2.987795712618607961022027524294928      12581396                     0   \n",
       "2312  2.987795712618607961022027524294928      12582289                     0   \n",
       "2313   3.01484838493587938693177994526334      12583291                     0   \n",
       "2314  3.017312488066069800526750719988266      12584395                     0   \n",
       "2315  3.010808833251978593543698264644376      12585445                     0   \n",
       "2316  3.013380911523254671540900690877811      12586509                     0   \n",
       "2317  2.994557766505518994467129012274117      12587589                     0   \n",
       "2318  2.986133628122973482368267810746214      12588614                     0   \n",
       "\n",
       "                               globalDebt  \\\n",
       "2309  11254666.38987890039495928507191416   \n",
       "2310  11255257.42502759395701312239432585   \n",
       "2311  11254262.85066739514570282239432585   \n",
       "2312  11254262.85066739514570282239432585   \n",
       "2313  11269458.84471393850414366224594458   \n",
       "2314  11273343.52215627828906702101612578   \n",
       "2315  11273343.52215627828906702101612578   \n",
       "2316  11271749.17020874565881833601612578   \n",
       "2317  11280625.25524269030657278130476457   \n",
       "2318  11280625.25524269030657278130476457   \n",
       "\n",
       "                        globalDebtCeiling  \\\n",
       "2309  115792089237316195423570985008687.9   \n",
       "2310  115792089237316195423570985008687.9   \n",
       "2311  115792089237316195423570985008687.9   \n",
       "2312  115792089237316195423570985008687.9   \n",
       "2313  115792089237316195423570985008687.9   \n",
       "2314  115792089237316195423570985008687.9   \n",
       "2315  115792089237316195423570985008687.9   \n",
       "2316  115792089237316195423570985008687.9   \n",
       "2317  115792089237316195423570985008687.9   \n",
       "2318  115792089237316195423570985008687.9   \n",
       "\n",
       "                            systemSurplus totalActiveSafeCount  \\\n",
       "2309  334422.2097696048411166656167537195                  398   \n",
       "2310  334495.6948952217890414417844415978                  398   \n",
       "2311  334495.6948952217890414417844415978                  397   \n",
       "2312  334495.6948952217890414417844415978                  397   \n",
       "2313  334641.1279151782159872447426343482                  398   \n",
       "2314  334713.0306017743527719623165356281                  397   \n",
       "2315  334713.0306017743527719623165356281                  397   \n",
       "2316  334713.0306017743527719623165356281                  395   \n",
       "2317  334865.7694774930324295870965585239                  395   \n",
       "2318  334865.7694774930324295870965585239                  395   \n",
       "\n",
       "     RedemptionRateAnnualizedRate RedemptionRateHourlyRate  \\\n",
       "2309           1.0280557716144304       1.0000031586143612   \n",
       "2310           0.9917512017587372       0.9999990544516076   \n",
       "2311           0.9917512017587372       0.9999990544516076   \n",
       "2312           0.9848502766846086       0.9999982573470972   \n",
       "2313           0.9848502766846086       0.9999982573470972   \n",
       "2314            1.004843332270226        1.000000551557352   \n",
       "2315            1.004843332270226        1.000000551557352   \n",
       "2316           0.9733785853054229       0.9999969198470134   \n",
       "2317           0.9733785853054229       0.9999969198470134   \n",
       "2318            0.970124160242656         0.99999653753848   \n",
       "\n",
       "     RedemptionRateEightHourlyRate                RedemptionPrice  \\\n",
       "2309             1.000025269194242  3.007462336651507650240484082   \n",
       "2310            0.9999924356378951  3.007482317021276222905288762   \n",
       "2311            0.9999924356378951  3.007470946112948759641228752   \n",
       "2312             0.999986058861809  3.007457913531166416987794407   \n",
       "2313             0.999986058861809  3.007442141195426393007003677   \n",
       "2314             1.000004412467334  3.007421877781552256694398164   \n",
       "2315             1.000004412467334  3.007429604409038912451083644   \n",
       "2316            0.9999753590417518  3.007431267158340112904837268   \n",
       "2317            0.9999753590417518  3.007392744675455428885367562   \n",
       "2318            0.9999723006435193  3.007354546298602186165903776   \n",
       "\n",
       "                RaiDrawnFromSAFEs   collateral           debt  \\\n",
       "2309  10829646.548524888029772788  1083.900454  250083.541093   \n",
       "2310  10830146.548524888029772788  1083.900454  250083.541093   \n",
       "2311  10829145.874404995566708985  1083.900454  250083.541093   \n",
       "2312  10829145.874404995566708985  1083.900454  250083.541093   \n",
       "2313  10844145.874404995566708985  1083.900454  250083.541093   \n",
       "2314  10847800.017651307208637556  1083.900454  250083.541093   \n",
       "2315  10847800.017651307208637556  1083.900454  250083.541093   \n",
       "2316  10846195.825558383918809618  1083.900454  250083.541093   \n",
       "2317  10854795.825558383918809618  1083.900454  250083.541093   \n",
       "2318  10854795.825558383918809618  1083.900454  250083.541093   \n",
       "\n",
       "                 EthInUniswap                RaiInUniswap  ETH Price (OSM)  \\\n",
       "2309  6625.838114615076905674  5943514.593703697883733875      2683.690690   \n",
       "2310  6589.450447779259016653  5976558.639784811539357153      2707.672804   \n",
       "2311  6637.300038771896535518  5932304.599899358670044537      2725.020000   \n",
       "2312  6595.238341540219075928  5970269.322165615521018742      2664.550000   \n",
       "2313   6624.69863615400826378  5958843.293272671861700994      2700.841159   \n",
       "2314  6564.157346459202112906  6014011.618217051683984889      2702.280000   \n",
       "2315  6527.685380199155840328   6047050.67719801013286767      2757.389589   \n",
       "2316  6565.000884794524909542  6039329.122833301597860332      2773.590000   \n",
       "2317  6495.694866208574513864  6107233.207612577182605722      2761.044990   \n",
       "2318   6541.01895026488098977  6065035.164579718018465007      2828.150972   \n",
       "\n",
       "               block_timestamp  \n",
       "2309 2021-06-06 06:05:41+00:00  \n",
       "2310 2021-06-06 10:05:55+00:00  \n",
       "2311 2021-06-06 14:05:50+00:00  \n",
       "2312 2021-06-06 17:35:43+00:00  \n",
       "2313 2021-06-06 21:05:55+00:00  \n",
       "2314 2021-06-07 01:05:54+00:00  \n",
       "2315 2021-06-07 05:07:45+00:00  \n",
       "2316 2021-06-07 09:06:20+00:00  \n",
       "2317 2021-06-07 13:15:51+00:00  \n",
       "2318 2021-06-07 17:06:11+00:00  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "states.tail(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "os.chdir('..')\n",
    "# export\n",
    "states.to_csv('data/states.csv')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}