Redemption_Analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "e6bcfe55",
   "metadata": {},
   "source": [
    "# Redemption Analysis\n",
    "\n",
    "Indigo Protocol is a synthetics based protocol on Cardano. Users can mint synthetic assets like iBTC/iETH/iUSD by providing ADA as collateral.In Indigo v2, the interest rate concept was introduced along with redemptions and v2.1 onwards an algorithmic interest was introduced, which varies according to the state of the protocol iAssets. \n",
    "\n",
    "This notebook discussed the steps involved in the generation of data for the algo interest dynamic model(iUSD_algo_model.xlsx) using various internal and external API's. \n",
    "\n",
    "This model creates dynamic model showcasing the interest rate trend based on the historical protocol data. \n",
    "This notebook focuses specifically on calculating the redeemable amount of iUSD in the protocol as per historical dates. It is one of the inputs of the algo interest mechanism. CDPs with CR between (LR,RMR) are redeemable upto RMR.\n",
    "\n",
    "This model was an integral part of the alog interest modelling process and was used extensively by the Indigo team to finalize algo interest parameters for all the iAssets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "643a66a4",
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import urllib.request\n",
    "import pandas as pd\n",
    "import json\n",
    "from datetime import datetime\n",
    "from typing import Final\n",
    "import time\n",
    "import glob\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1079fea3",
   "metadata": {},
   "source": [
    "***Calculating the amount of redeemable iUSD based on various inputs***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "2f1de7e0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Redeemable iUSD: 13675.977217450523\n",
      "Redeemable CDPs: 133\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\advai\\AppData\\Local\\Temp\\ipykernel_7884\\2017601759.py:30: FutureWarning: Setting an item of incompatible dtype is deprecated and will raise in a future error of pandas. Value '[2.00000000e+01 1.00000000e+01 1.68707414e+02 5.00000000e+01\n",
      " 5.00000000e+01 3.30000000e+01 2.51709290e+01 5.00000000e+01\n",
      " 1.45410960e+01 5.11000000e+02 1.81658494e+02 2.50000000e+03\n",
      " 3.00000000e+01 1.00000000e+02 1.50000000e+01 1.00000000e+01\n",
      " 1.00000000e+02 2.61203820e+01 3.00000000e+02 6.29324470e+01\n",
      " 1.00000000e+01 9.70000000e+02 1.80000000e+01 3.24490320e+01\n",
      " 9.45482000e+01 4.00000000e+01 4.00000000e+01 5.87650284e+02\n",
      " 1.00000000e+01 1.26626162e+02 1.00000000e+01 1.23950482e+02\n",
      " 2.11193110e+01 1.18044430e+04 2.00000000e+01 1.00000000e+01\n",
      " 5.00000000e+01 2.00000000e+01 6.17855080e+01 5.00000000e+01\n",
      " 1.48362839e+02 5.00000000e+01 3.50706192e+03 1.00000000e+01\n",
      " 1.00000000e+01 1.00000000e+02 1.00000000e+01 1.00000000e+01\n",
      " 1.50000000e+01 1.00000000e+01 1.00000000e+01 1.00000000e+01\n",
      " 1.00000000e+06 1.56097561e+05 7.08000000e+03 1.36303968e+02\n",
      " 1.00000000e+01 3.37815000e+03 3.91172082e+02 2.00000000e+04\n",
      " 5.85377980e+03 1.00000000e+01 2.00000000e+01 4.17000000e+04\n",
      " 1.00000000e+01 1.00000000e+01 7.85680484e+04 7.57847825e+02\n",
      " 1.00000000e+03 2.90000000e+03 3.55275000e+02 1.06427300e+01\n",
      " 4.00000000e+02 7.39024080e+01 1.00000000e+02 4.62635160e+02\n",
      " 7.60000000e+03 1.09380340e+01 1.00000000e+02 7.00177140e+03\n",
      " 4.50000000e+02 1.00000000e+01 5.00000000e+03 1.00000000e+01\n",
      " 2.40000000e+01 1.00000000e+01 2.00000000e+03 2.58442207e+02\n",
      " 1.00085150e+01 2.50000000e+03 1.00000000e+01 5.50000000e+02\n",
      " 7.30000000e+03 1.20000000e+03 1.39933820e+01 1.27366262e+02\n",
      " 4.35000000e+02 2.14672825e+04 7.06417760e+03 9.50000000e+03\n",
      " 1.00000000e+01 1.50000000e+01 1.24044780e+03 1.06611260e+01\n",
      " 1.10400000e+03 9.01000000e+03 1.28000000e+05 5.43740753e+03\n",
      " 2.10000000e+02 2.86105000e+05 1.36441254e+05 7.00000000e+03\n",
      " 1.00000000e+01 5.51463332e+03 2.80000000e+04 3.30000000e+04\n",
      " 1.00000000e+01 1.10000000e+03 5.60000000e+02 1.00000000e+01\n",
      " 1.67604255e+04 1.80000000e+04 1.05599682e+04 1.97000000e+03\n",
      " 4.00000000e+02 3.87778717e+03 6.00000000e+02 1.08247490e+01\n",
      " 1.50009070e+04 1.00000000e+01 1.00000000e+02 7.58000000e+02\n",
      " 9.43247180e+01 4.00000000e+02 5.00000000e+03 1.00000000e+01\n",
      " 1.50000000e+02 5.74247730e+02 4.50000000e+03 1.12790000e+04\n",
      " 2.00000000e+02 1.80194790e+01 1.00000000e+01 1.25000000e+03\n",
      " 6.86500000e+04 3.47496427e+03 7.46698807e+03 1.00000040e+01\n",
      " 1.96359180e+03 1.40000000e+02 2.50000000e+01 2.05398970e+05\n",
      " 2.00000000e+03 1.00000000e+01 1.00000000e+01 1.00000000e+02\n",
      " 9.92279370e+01 1.00000000e+01 4.99742412e+03 2.00000000e+01\n",
      " 8.50000000e+03 1.84020000e+04 1.85000000e+03 7.88479610e+01\n",
      " 7.00000000e+03 5.11051360e+01 2.55500000e+04 1.00000000e+01\n",
      " 5.99000000e+06 1.65000000e+02 4.08126845e+02 3.25000000e+04\n",
      " 4.20000000e+01 6.75000000e+03 1.95014016e+03 1.30000000e+03\n",
      " 1.00000000e+01 1.00000000e+01 2.00000000e+04 1.20000000e+02\n",
      " 3.00000000e+01 2.58400000e+04 1.80000000e+03 2.50000000e+01\n",
      " 6.39795200e+01 7.06518760e+01 1.00000000e+01 1.10000000e+02\n",
      " 2.02935357e+04 2.83426690e+04 7.00000000e+06 5.60098642e+04\n",
      " 8.60000000e+01 5.86600000e+03 2.50000000e+03 9.28250103e+04\n",
      " 3.74235450e+01 3.00000000e+04 5.00000000e+04 1.50000000e+02\n",
      " 1.37013464e+04 9.19672660e+03 7.68081620e+04 7.47090271e+03\n",
      " 2.83017270e+04 2.91824117e+03 1.25716805e+03 5.25000000e+03\n",
      " 1.76086820e+03 1.22661526e+05 6.00000000e+01 5.75000000e+04\n",
      " 3.41845012e+04 1.59873362e+04 9.61923639e+04 1.55070392e+04\n",
      " 1.39786514e+04 1.02501568e+04 8.60371090e+03 2.95612795e+05\n",
      " 3.09427179e+03 1.00775498e+05 8.66173998e+04 8.61858319e+04\n",
      " 7.67842329e+04 7.15070107e+04 9.58405554e+04 7.42003892e+04\n",
      " 5.48755635e+04 5.21674675e+04 2.32303593e+04 4.70488289e+04\n",
      " 5.03281119e+04 2.32022518e+04 2.13053948e+05 2.34942411e+05\n",
      " 1.63971760e+05 2.22406089e+04 2.37224848e+05 2.65389938e+03\n",
      " 1.05372571e+05 9.74972606e+04 2.40139315e+05 2.78349553e+04\n",
      " 3.67336838e+04 3.33978327e+04 1.38648331e+04 6.00829515e+03\n",
      " 3.89506180e+04 1.51810570e+04 2.02281925e+04 5.10000000e+05\n",
      " 2.56306273e+04 1.22596559e+04 1.70548276e+04 2.00191057e+04\n",
      " 7.70000000e+03 1.56000000e+05 9.35631167e+03 9.30927236e+03\n",
      " 6.00000000e+05 1.69350326e+05 6.53706917e+04 1.00000000e+01\n",
      " 4.81965495e+03 1.42743686e+05 4.05797458e+03 3.76000000e+05\n",
      " 1.20069784e+04 6.00000000e+04 5.56649968e+03 9.41746972e+04\n",
      " 3.20000000e+04 9.19251389e+03 8.77572214e+03 1.00200000e+04\n",
      " 4.43051696e+03 1.08790000e+04 5.04393532e+03 3.84063337e+03\n",
      " 4.95816886e+03 5.45124688e+03 3.06960424e+03 3.57652463e+03\n",
      " 3.55268244e+03 2.86514221e+03 1.99133162e+03 1.85605328e+03\n",
      " 6.26916772e+03 1.78285172e+03 1.98029454e+03 3.00000000e+03\n",
      " 6.00000000e+03 1.42123956e+03 1.41145849e+03 1.82090683e+03\n",
      " 1.78069650e+03 1.77481739e+03 1.41823571e+03 1.78625958e+03\n",
      " 2.50000000e+04 1.52518501e+03 1.11304904e+03 1.10024084e+03\n",
      " 1.26300000e+03 1.24214021e+03 9.45785336e+02 1.84126641e+03\n",
      " 1.26756858e+03 9.69141866e+02 1.93348970e+03 9.56620677e+02\n",
      " 5.67800972e+03 1.19576876e+03 8.81398021e+02 5.33234472e+03\n",
      " 1.23712728e+03 6.55956488e+02 8.31277572e+02 5.69900197e+02\n",
      " 6.88575428e+02 4.62694638e+02 7.62144827e+03 4.52626576e+02\n",
      " 4.74583887e+02 3.17236000e+04 2.15186706e+03 1.23157964e+05\n",
      " 3.41849000e+05 5.38466244e+04 3.12601642e+05 1.56257020e+01\n",
      " 6.54977860e+01 4.58997987e+04 9.82606360e+03 7.17605594e+04\n",
      " 1.10720000e+05 2.30791638e+04 4.01500000e+04 1.00000000e+01\n",
      " 2.36215395e+04 3.46500000e+03 4.08720807e+03 5.32379200e+03\n",
      " 4.69239972e+02 1.13293362e+06 5.39936759e+02 4.09239117e+02\n",
      " 9.00000000e+02 6.33000000e+02 3.64047039e+02 5.61088161e+02\n",
      " 5.00000000e+02 7.50000000e+02 1.60049634e+03 4.64000000e+05\n",
      " 4.03309640e+05 3.75105000e+05 8.47296793e+02 4.07893675e+04\n",
      " 2.99789168e+03 9.50000000e+03 1.40220100e+05 2.76602765e+05\n",
      " 5.50798762e+05 9.10866975e+04 3.90791233e+04 1.84222926e+04\n",
      " 1.06057885e+05 4.51549544e+04 1.42557896e+06 5.46892940e+01\n",
      " 9.32455124e+03 1.02398362e+04 1.20000000e+03 3.28872454e+02\n",
      " 2.88301430e+02 1.00000000e+03 2.50933953e+02 2.53785284e+02\n",
      " 1.30888233e+05 2.18775656e+02 1.91073678e+02 2.43389933e+02\n",
      " 2.11770044e+02 2.69064066e+03 1.71000000e+04 3.16038244e+03\n",
      " 7.00000000e+03 1.00000000e+03 4.30000000e+02 8.05514884e+03\n",
      " 5.07757591e+04 1.22766657e+04 9.66880329e+04 1.10000000e+03\n",
      " 1.03583304e+02 1.75000000e+02 1.70500000e+05 1.35390000e+04\n",
      " 2.91974000e+05 1.16913285e+06 1.00000000e+01 1.60000000e+05\n",
      " 1.00000000e+04 2.23227685e+03 1.38756976e+04 2.86011120e+01\n",
      " 1.00000000e+01 3.62147453e+04 2.17661777e+04 1.00000000e+05\n",
      " 2.66650674e+04 2.56086721e+03 1.54266037e+02 1.80036458e+04\n",
      " 4.16410000e+01 9.19865011e+03 2.01316986e+04 1.46250000e+03\n",
      " 2.20000000e+03 3.17130442e+03 2.12506335e+03 1.00000000e+05\n",
      " 1.59062758e+03 2.82723907e+03 1.50800000e+03 1.29659625e+04\n",
      " 4.54250298e+03 5.79835347e+03 4.07474857e+03 5.79159562e+06\n",
      " 5.50000000e+03 4.00000000e+02 8.07529001e+04 1.35262627e+02\n",
      " 8.33078600e+02 8.11148057e+04 3.97029374e+04 8.47498878e+03\n",
      " 2.50000000e+04 1.99439965e+04 3.30003435e+04 1.10000000e+04\n",
      " 1.18210059e+02 1.07494657e+02 1.10000000e+04 2.10000000e+02\n",
      " 4.70000000e+02 5.84637582e+02 2.00000000e+02 2.00000000e+02\n",
      " 8.66587290e+01 5.99000000e+04 4.00000000e+02 1.33970000e+04\n",
      " 9.77598820e+01 3.00000000e+02 9.24052380e+01 1.00000000e+02\n",
      " 7.89541200e+01 6.00000000e+01 4.63416270e+01 6.11343100e+01\n",
      " 1.74800031e+03 1.08431603e+03 1.16644622e+02 2.49010060e+01\n",
      " 5.00000000e+01 3.23423660e+01 2.20042150e+01 5.00000000e+01\n",
      " 6.00000000e+01 2.59409330e+01 1.20000000e+03 2.66273155e+03\n",
      " 2.00000000e+01 9.00000000e+04 5.00000000e+01 5.50000000e+04\n",
      " 3.00000000e+01 6.20000000e+03 2.50000000e+05 1.02000000e+06\n",
      " 7.20000000e+05 5.62500000e+03 8.51643491e+04 2.06781623e+04\n",
      " 1.45517736e+04 1.17457435e+03 2.65904260e+01 2.00000000e+01\n",
      " 1.78646173e+03 1.27144390e+01 1.28936950e+01 7.21454300e+00\n",
      " 1.50000000e+01 1.00000000e+01 5.45000000e+04 4.17813080e+04\n",
      " 4.00000000e+04 5.00000000e+03 3.89497456e+04 9.00000000e+02\n",
      " 5.39000000e+04 6.93833600e+00 7.25005139e+04 1.24401720e+01\n",
      " 7.43533450e+01 1.09990000e+04 5.77950000e+04 8.79491000e+00\n",
      " 2.39879651e+03 4.54580000e+05 2.60000000e+01 1.00000000e+03\n",
      " 2.50000000e+04 2.14000000e+04 1.37500000e+03 6.98937271e+03\n",
      " 4.66153658e+04 6.39800000e+04 2.59000000e+04 4.95000000e+06\n",
      " 4.95000000e+06 6.50000000e+02 3.12241679e+04 3.00000000e+04\n",
      " 2.02000000e+04 3.80000000e+03 1.84500000e+05 2.97272829e+03\n",
      " 2.70000000e+04 8.20000000e+02 4.11466465e+03 8.05500000e+04\n",
      " 3.48408353e+03 1.00000000e+04 2.49694155e+04 6.50000000e+03\n",
      " 1.72663734e+03 1.60182330e+03 4.91950000e+04 3.80000000e+04\n",
      " 4.07043025e+04 7.00000000e+03 1.57800000e+04 2.00000000e+04\n",
      " 1.26000000e+05 2.00000000e+04 4.95000000e+06 4.33800000e+06\n",
      " 6.02200000e+04 1.79500775e+05 2.03500000e+04 1.17000000e+04\n",
      " 5.88085684e+03 1.86672417e+04 2.75461775e+04 4.48953580e+01\n",
      " 1.38877691e+04 1.00022851e+04 1.00000000e+04 2.89766250e+01\n",
      " 1.00000000e+01 3.08211868e+05 1.25350000e+04 6.85000000e+04\n",
      " 4.00000000e+01 1.19000000e+04 3.24469202e+03 4.25000000e+03\n",
      " 1.00500000e+05 2.38763389e+06 2.00000000e+01 2.00000000e+04\n",
      " 7.00000000e+04 4.00000000e+01 7.17600000e+03 4.76713234e+03\n",
      " 1.50000000e+04 2.00000000e+03 4.75000000e+03 2.50000000e+03\n",
      " 2.99966960e+03 4.00979479e+05 1.00000000e+04 2.90000000e+03\n",
      " 5.43038236e+03 1.00000000e+03 1.00000000e+01 1.24514000e+06\n",
      " 4.55000002e+04 9.57270039e+05 1.00000000e+01 1.00000000e+01\n",
      " 1.00000000e+01 1.35000000e+04 6.67450000e+03 2.01260022e+03\n",
      " 3.03500000e+03 2.40000000e+02 1.10000000e+02 1.93950037e+02\n",
      " 4.56371860e+04 7.10944931e+04 2.00000000e+02 6.00000000e+01\n",
      " 5.00000000e+01 1.50000000e+02 1.00142240e+01 1.00000000e+03\n",
      " 1.42000000e+03 3.74900000e+03 3.15700000e+03 1.61000000e+04\n",
      " 9.03466135e+04 5.53001615e+04 1.00000000e+02 1.90000000e+04\n",
      " 2.00000000e+04 4.11687000e+05 2.69332075e+03 1.20531607e+04\n",
      " 1.00000000e+01 9.26300000e+03 1.50750000e+06 3.00000000e+05\n",
      " 1.00000000e+04 4.67098353e+04 5.26005802e+04 2.35421757e+04\n",
      " 2.89226089e+03 1.01101771e+04 1.02787547e+05 5.70000000e+03\n",
      " 7.67535402e+03 1.34176239e+04 1.50000000e+04 1.62400000e+04\n",
      " 8.00000000e+04 1.20000000e+04 3.95000000e+04 3.20000000e+03\n",
      " 4.34247336e+03 3.00000000e+03 2.50078353e+03 3.21349000e+03\n",
      " 1.04350000e+04 3.80200000e+03 9.50000000e+02 5.00000000e+03\n",
      " 7.80000000e+03 6.57801435e+02 5.20000000e+02 8.16065852e+03\n",
      " 3.02677282e+02 3.15871900e+02 1.00000000e+02 1.00000000e+02\n",
      " 1.00000000e+02 1.79440000e+04 5.00000000e+04 2.30243830e+01\n",
      " 2.00000000e+01 2.00000000e+01 2.00000000e+01 8.40082743e+04\n",
      " 4.03000000e+06 1.06200000e+04 3.00000000e+03 1.00000000e+01\n",
      " 9.69214141e+02 2.50000000e+04 6.00000000e+03 6.12356942e+04\n",
      " 8.00000000e+02 6.20000000e+04 1.00000000e+06 4.35592391e+05\n",
      " 1.00000000e+07 2.93104139e+03 2.44437115e+04 2.20000000e+03\n",
      " 9.08976845e+03 1.00000000e+07 1.02000000e+04 1.33990776e+04\n",
      " 5.75212090e+04 2.12000000e+03 1.00000000e+03 9.54643190e+03\n",
      " 2.95018107e+05 2.22292602e+02 8.00000000e+04 2.32204446e+05\n",
      " 1.00000000e+04]' has dtype incompatible with int64, please explicitly cast to a compatible dtype first.\n",
      "  df4.loc[:, 'collateralAmount'] = df4['collateralAmount'].astype(float) / 1000000\n",
      "C:\\Users\\advai\\AppData\\Local\\Temp\\ipykernel_7884\\2017601759.py:31: FutureWarning: Setting an item of incompatible dtype is deprecated and will raise in a future error of pandas. Value '[3.35111300e+00 3.16321000e-01 2.32483520e+01 8.37778400e+00\n",
      " 1.59330000e-02 6.37140000e-02 3.00136000e+00 8.21745900e+00\n",
      " 2.46092700e+00 6.88478930e+01 2.51009800e+01 3.65921745e+02\n",
      " 5.02149800e+00 1.24087220e+01 4.89494000e-01 1.69512400e+00\n",
      " 1.58300030e+01 4.19751900e+00 2.53137380e+01 1.00000000e+01\n",
      " 1.61118300e+00 1.05626552e+02 2.92231400e+00 5.15616800e+00\n",
      " 1.50000000e+01 6.18896400e+00 6.12521200e+00 8.00000000e+01\n",
      " 2.12188000e-01 1.66010820e+01 1.28692800e+00 1.54387660e+01\n",
      " 3.34230200e+00 1.50000000e+03 2.60212300e+00 1.28826600e+00\n",
      " 2.54693000e-01 1.94430000e-02 4.35309900e+00 6.29064100e+00\n",
      " 2.09890050e+01 3.60219000e-01 5.06560111e+02 1.40081000e+00\n",
      " 8.71268000e-01 1.37913530e+01 1.16368000e+00 3.84469000e-01\n",
      " 1.67991000e+00 1.41948600e+00 2.40706000e-01 1.14038000e-01\n",
      " 1.00966288e+05 1.57605913e+04 1.03406605e+03 1.75333450e+01\n",
      " 1.00000000e-01 5.00000000e+02 5.00000000e+01 2.79161526e+03\n",
      " 8.70000000e+02 1.27765900e+00 2.02227600e+00 5.00981780e+03\n",
      " 1.44041900e+00 1.00000000e-06 9.46668129e+03 9.01435970e+01\n",
      " 1.28892502e+02 4.19218011e+02 4.12079300e+01 1.19068000e-01\n",
      " 5.75135210e+01 1.14320540e+01 1.21113690e+01 7.01178890e+01\n",
      " 1.00527505e+03 6.43584000e-01 1.00000010e+01 1.04000000e+03\n",
      " 6.72968650e+01 1.61000000e-02 6.71984230e+02 4.30000000e-05\n",
      " 1.86085500e+00 1.61656300e+00 2.85240989e+02 3.29062940e+01\n",
      " 4.99724000e-01 3.00149917e+02 5.49100000e-03 4.56369590e+01\n",
      " 1.00044829e+03 1.47570502e+02 1.76611500e+00 1.83370760e+01\n",
      " 4.92445480e+01 3.14286623e+03 6.50000329e+02 1.40150013e+03\n",
      " 6.50200000e-03 1.58224800e+00 3.64427380e+01 1.00844100e+00\n",
      " 1.43350723e+02 1.14741834e+03 1.25784049e+04 7.90065251e+02\n",
      " 3.18238950e+01 3.19469699e+04 1.39027849e+04 9.84275862e+02\n",
      " 1.36398400e+00 7.35365431e+02 3.50408862e+03 4.33278213e+03\n",
      " 6.67461000e-01 1.30765274e+02 7.15949460e+01 5.55750000e-02\n",
      " 2.47765619e+03 2.40530074e+03 1.50000000e+03 2.72365542e+02\n",
      " 4.68918980e+01 5.00000000e+02 7.49820730e+01 8.34128000e-01\n",
      " 1.60000000e+03 5.55589000e-01 1.45104870e+01 1.99289470e+01\n",
      " 1.52998020e+01 4.39355070e+01 6.11800106e+02 2.72200000e-03\n",
      " 2.31962990e+01 8.12756760e+01 5.40000000e+02 1.08687439e+03\n",
      " 3.33043300e+00 3.00000000e+00 1.20123400e+00 1.33503350e+02\n",
      " 1.00018042e+04 4.66850015e+02 1.06910230e+03 6.48271000e-01\n",
      " 2.50000000e+02 1.74465000e+01 3.18564700e+00 2.26119449e+04\n",
      " 2.63676920e+02 2.45980000e-02 6.00000000e-06 1.46186000e+01\n",
      " 1.60000000e+01 7.09800000e-03 6.50000000e+02 2.39615700e+00\n",
      " 7.64824106e+02 2.34634003e+03 2.39574402e+02 1.00000000e+01\n",
      " 8.49602472e+02 6.03118100e+00 2.45338522e+03 1.00000000e-06\n",
      " 7.75957153e+05 2.22727300e+01 5.57156710e+01 4.01572565e+03\n",
      " 6.69354500e+00 1.01720811e+03 2.70462783e+02 1.70926260e+02\n",
      " 1.55804800e+00 1.25268300e+00 2.57384026e+03 1.75745960e+01\n",
      " 4.86315800e+00 2.00856907e+03 2.44107285e+02 3.86354800e+00\n",
      " 1.00000000e+01 1.00000000e+01 3.15910000e-02 7.03505400e+00\n",
      " 2.19301284e+03 3.88153147e+03 8.09130245e+05 7.75379638e+03\n",
      " 5.80983100e+00 7.04692398e+02 2.89410135e+02 9.95111904e+03\n",
      " 5.00000000e+00 4.16729000e+00 7.08026721e+02 1.18928050e+01\n",
      " 1.68985182e+03 1.14549196e+03 1.08995820e+04 1.09157879e+03\n",
      " 4.18618374e+03 1.74471064e+02 1.87332184e+02 3.30907282e+02\n",
      " 2.47494157e+02 9.81673608e+03 9.45643400e+00 1.07500004e+04\n",
      " 6.70727947e+03 3.23504846e+03 1.91060153e+04 3.11888365e+03\n",
      " 2.82560334e+03 2.02309279e+03 1.71334729e+03 5.69535356e+04\n",
      " 6.02177766e+02 1.95492862e+04 1.68429733e+04 1.66763481e+04\n",
      " 1.48701211e+04 1.38804710e+04 1.86630611e+04 1.44315287e+04\n",
      " 1.06766109e+04 1.01281746e+04 4.51028389e+03 9.14024724e+03\n",
      " 9.72426401e+03 4.50420012e+03 4.12229608e+04 4.54124380e+04\n",
      " 3.17217670e+04 4.33507954e+03 4.57254260e+04 5.15814849e+02\n",
      " 2.03737237e+04 1.87979013e+04 4.45846209e+04 5.41462945e+03\n",
      " 7.12123697e+03 6.50066033e+03 2.73218679e+03 1.16832523e+03\n",
      " 6.65809292e+03 2.94279952e+03 3.93991682e+03 9.47516169e+04\n",
      " 4.98034564e+03 2.39406505e+03 3.31995960e+03 3.87509509e+03\n",
      " 1.50000000e+03 2.90000000e+04 1.81458228e+03 1.83156786e+03\n",
      " 1.15936136e+05 3.24067193e+04 1.25801710e+04 1.93775600e+00\n",
      " 9.34230426e+02 2.72374603e+04 8.00445887e+02 7.21301812e+04\n",
      " 2.30921102e+03 1.14078987e+04 1.08248477e+03 1.78064323e+04\n",
      " 6.13000000e+03 1.79383975e+03 1.70865891e+03 1.92954206e+03\n",
      " 8.64506607e+02 2.08342447e+03 9.85687646e+02 7.48736760e+02\n",
      " 9.69706995e+02 1.05793149e+03 6.00188335e+02 6.99022498e+02\n",
      " 6.92287642e+02 5.58206743e+02 3.97617189e+02 3.62957826e+02\n",
      " 1.15590445e+03 3.46335709e+02 3.83783446e+02 5.80157230e+02\n",
      " 1.15571490e+03 2.78627854e+02 2.74353361e+02 3.53867034e+02\n",
      " 3.45391379e+02 3.54688978e+02 2.76223975e+02 3.54038859e+02\n",
      " 4.25053936e+03 2.99263751e+02 2.17018728e+02 2.15706957e+02\n",
      " 2.44521184e+02 2.43739138e+02 1.84178241e+02 3.58318558e+02\n",
      " 2.48665830e+02 1.88691903e+02 3.77372168e+02 1.86396938e+02\n",
      " 1.09578631e+03 2.37134938e+02 1.73246797e+02 1.01586217e+03\n",
      " 2.44494093e+02 1.28183082e+02 1.62252579e+02 1.11918279e+02\n",
      " 1.36097011e+02 9.12734000e+01 1.44628527e+03 8.89322920e+01\n",
      " 9.27441790e+01 6.00482951e+03 3.75500000e+02 2.31419139e+04\n",
      " 5.90410570e+04 9.73487707e+03 5.73190408e+04 3.09855400e+00\n",
      " 1.29517850e+01 8.64481276e+03 1.74364796e+03 1.32979676e+04\n",
      " 2.06617587e+04 4.32022219e+03 7.57965488e+03 1.89561100e+00\n",
      " 4.44518320e+03 6.53431458e+02 7.73499239e+02 1.00000000e+03\n",
      " 9.19755910e+01 2.02176812e+05 1.02115041e+02 8.03747260e+01\n",
      " 1.48691467e+02 1.23554509e+02 7.17625750e+01 1.08363943e+02\n",
      " 9.75268410e+01 1.43465633e+02 2.99075981e+02 8.57097039e+04\n",
      " 7.44924600e+04 6.93348457e+04 1.51351520e+02 7.47509152e+03\n",
      " 4.78776412e+02 1.75862520e+03 2.01934565e+04 5.04038555e+04\n",
      " 1.00025000e+05 1.67133665e+04 7.17154072e+03 3.42060533e+03\n",
      " 1.93509545e+04 8.22108400e+03 2.13950424e+05 1.09368420e+01\n",
      " 1.71788030e+03 1.88319081e+03 2.26563082e+02 6.48003880e+01\n",
      " 5.68126280e+01 1.88784358e+02 4.96324460e+01 5.05215740e+01\n",
      " 2.52095569e+04 4.30665360e+01 3.71388890e+01 4.78547490e+01\n",
      " 4.12249590e+01 5.00000000e+02 3.13947354e+03 5.82399569e+02\n",
      " 1.29887101e+03 1.87594677e+02 8.18481160e+01 8.89875883e+02\n",
      " 7.26727197e+03 2.24198090e+03 1.71350408e+04 2.03082693e+02\n",
      " 2.05085380e+01 2.00000000e-06 3.08932017e+04 2.35588538e+03\n",
      " 5.22017325e+04 1.89132994e+05 1.76311200e+00 2.80000000e+04\n",
      " 1.79014413e+03 3.85925425e+02 2.49325401e+03 5.70628400e+00\n",
      " 1.86211200e+00 6.31880757e+03 4.00000000e+03 1.75000000e+04\n",
      " 4.81984990e+03 4.61151416e+02 2.81555150e+01 3.26500000e+03\n",
      " 8.14417900e+00 1.66793766e+03 3.51682261e+03 2.65643847e+02\n",
      " 4.02133934e+02 5.63379981e+02 3.79846480e+02 1.10946543e+04\n",
      " 2.87567962e+02 5.00000000e+02 2.71105336e+02 1.61411300e+03\n",
      " 6.94806771e+02 1.01034057e+03 7.03683632e+02 8.84287189e+05\n",
      " 8.34831153e+02 7.43589920e+01 1.21089336e+04 2.66762070e+01\n",
      " 1.47638957e+02 1.40352982e+04 5.01067689e+03 1.45465287e+03\n",
      " 3.18923920e+03 3.41112304e+03 5.67854319e+03 1.89955235e+03\n",
      " 1.94014080e+01 2.12819950e+01 1.55521652e+03 3.96750450e+01\n",
      " 8.50158300e+01 1.04812778e+02 3.82575020e+01 3.80057500e+01\n",
      " 1.76104130e+01 9.53857413e+03 7.27036770e+01 1.92387559e+03\n",
      " 1.93089770e+01 5.51478000e+01 1.80000000e+01 1.89560990e+01\n",
      " 1.50000000e+01 1.15954430e+01 8.97805400e+00 1.15672120e+01\n",
      " 2.95248056e+02 1.91285788e+02 2.15515280e+01 5.11812700e+00\n",
      " 9.59750000e+00 6.31104600e+00 4.42745000e+00 9.36303200e+00\n",
      " 1.10051890e+01 5.00000000e+00 2.10580700e+02 4.65000000e+02\n",
      " 3.91366200e+00 7.30419914e+03 9.20362500e+00 8.30423707e+03\n",
      " 5.63511400e+00 9.71186851e+02 3.49792871e+04 1.50000000e+05\n",
      " 4.83212117e+04 5.84026609e+02 1.44551552e+04 3.51199108e+03\n",
      " 2.47412169e+03 1.74958237e+02 5.00000000e+00 3.68780500e+00\n",
      " 2.99210927e+02 2.55647000e+00 2.53024200e+00 1.48658800e+00\n",
      " 2.88733900e+00 1.88293600e+00 8.77485345e+03 5.70368847e+03\n",
      " 5.43604427e+03 7.19141577e+02 7.21800842e+02 1.24988936e+02\n",
      " 4.80165514e+03 1.42846400e+00 1.21689305e+04 2.51637500e+00\n",
      " 8.68315800e+00 1.64437596e+03 9.24989150e+03 1.74562800e+00\n",
      " 3.81799109e+02 7.71025649e+04 1.96431100e+00 1.07669700e+02\n",
      " 1.00155469e+03 2.77667350e+03 2.28630116e+02 7.39176628e+02\n",
      " 1.80277915e+03 1.03464054e+04 3.50000000e+03 5.45306982e+05\n",
      " 5.45306982e+05 8.29151380e+01 5.30482387e+03 4.95902644e+03\n",
      " 3.34063800e+03 5.55131447e+02 2.05014321e+04 5.14482713e+02\n",
      " 3.23481134e+03 1.18390831e+02 5.43539612e+02 1.13767511e+04\n",
      " 4.43630618e+02 1.52495527e+03 4.00803188e+03 7.88495950e+02\n",
      " 2.59521829e+02 2.63177927e+02 5.99244605e+03 3.68323284e+03\n",
      " 6.62766433e+03 7.84707396e+02 2.19733722e+03 3.28133602e+03\n",
      " 1.71112664e+04 1.85159000e+00 5.42541598e+05 5.42541598e+05\n",
      " 8.93217751e+03 2.34391545e+04 3.00028819e+03 1.55853897e+03\n",
      " 9.50000000e+02 2.94591027e+03 4.29623514e+03 4.02300800e+00\n",
      " 1.34085258e+03 1.39131457e+03 1.44992836e+03 5.42618100e+00\n",
      " 1.87431200e+00 5.16716389e+04 1.91758164e+03 1.16649854e+04\n",
      " 7.30971500e+00 1.83427044e+03 4.95824859e+02 7.29788121e+02\n",
      " 1.70847094e+04 3.99374284e+05 3.45837900e+00 3.17592839e+03\n",
      " 1.14883196e+04 6.76000000e+00 1.20811315e+03 8.04074415e+02\n",
      " 2.50110154e+03 3.45620128e+02 7.90276618e+02 4.27837932e+02\n",
      " 5.00000000e+02 6.58971067e+04 1.65752344e+03 4.89397516e+02\n",
      " 9.01502251e+02 1.72380708e+02 1.65812300e+00 2.02479370e+05\n",
      " 7.48413059e+03 1.55667181e+05 1.65752300e+00 1.61608500e+00\n",
      " 1.65752300e+00 2.21663650e+03 1.10121140e+03 3.35992900e+02\n",
      " 5.00273811e+02 4.14753820e+01 1.94547640e+01 3.30142860e+01\n",
      " 6.96936990e+03 1.09017680e+04 3.39024950e+01 1.02589960e+01\n",
      " 8.18602900e+00 2.56476530e+01 1.64084800e+00 1.63744887e+02\n",
      " 2.14435049e+02 2.90344418e+02 4.99576483e+02 2.41718523e+03\n",
      " 1.40000000e+04 7.91627768e+03 1.66010820e+01 2.77656567e+03\n",
      " 2.92270070e+03 4.23423857e+04 4.10000000e+02 1.82494430e+03\n",
      " 1.49401900e+00 1.46062324e+03 2.20518290e+05 4.04346405e+04\n",
      " 1.61044623e+03 7.16490438e+03 8.05946485e+03 3.65543382e+03\n",
      " 4.50000000e+02 1.60141185e+03 1.29063460e+04 9.14902333e+02\n",
      " 1.20656572e+03 2.06440033e+03 1.97469289e+03 2.30801703e+03\n",
      " 5.62461813e+03 1.68406020e+03 5.61617164e+03 3.20077285e+02\n",
      " 4.91432971e+02 4.86686822e+02 4.00875093e+02 5.02250000e+02\n",
      " 1.61091046e+03 5.85673253e+02 1.51200036e+02 7.69908288e+02\n",
      " 1.19191478e+03 1.04820377e+02 8.26524570e+01 1.19359341e+03\n",
      " 4.87997120e+01 5.00000000e+01 1.63720580e+01 1.63720580e+01\n",
      " 1.65821040e+01 2.71160117e+03 7.52465027e+03 4.13835800e+00\n",
      " 3.62926800e+00 3.61317600e+00 3.61810300e+00 1.14076055e+04\n",
      " 5.42541598e+05 1.11991474e+03 3.59581478e+02 1.84695400e+00\n",
      " 1.34511387e+02 3.49955736e+03 7.80870997e+02 6.93814340e+03\n",
      " 1.03633132e+02 8.54080580e+03 1.00587159e+05 4.40000000e+04\n",
      " 1.00011539e+06 3.50000000e+02 3.00000000e+03 1.73118800e+00\n",
      " 1.12356578e+03 9.76456177e+05 1.35772144e+03 1.70911394e+03\n",
      " 3.45368401e+02 1.51540526e+02 7.83619430e+01 1.13087113e+03\n",
      " 2.08555994e+04 2.82504620e+01 5.26936049e+03 1.73968381e+04\n",
      " 1.24439036e+03]' has dtype incompatible with int64, please explicitly cast to a compatible dtype first.\n",
      "  df4.loc[:, 'mintedAmount'] = df4['mintedAmount'].astype(float) / 1000000\n",
      "C:\\Users\\advai\\AppData\\Local\\Temp\\ipykernel_7884\\2017601759.py:32: FutureWarning: Setting an item of incompatible dtype is deprecated and will raise in a future error of pandas. Value '[0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 1.81267070e+01\n",
      " 4.60194600e+00 1.15914840e+01 5.28433800e+00 2.17829993e+02\n",
      " 2.15880000e-02 1.26758859e+02 1.85693608e+02 9.79415000e-01\n",
      " 4.68511400e+01 3.17610550e+01 4.69744060e+02 4.62701710e+01\n",
      " 1.17091745e+02 1.71566040e+01 5.55627200e+00 1.00102750e+01\n",
      " 7.81968800e+00 9.68923255e+02 4.70050000e-02 2.67225965e+02\n",
      " 4.05554576e+02 3.88163120e+01 7.19195103e+02 4.28613610e+01\n",
      " 1.25178240e+01 9.95380260e+01 5.57149320e+01 2.68330702e+03\n",
      " 2.64105800e+01 6.82202620e+02 6.14097542e+02 7.64044327e+02\n",
      " 6.56804154e+02 5.51829891e+02 6.30237165e+02 5.20375564e+02\n",
      " 3.78107497e+02 4.01224763e+02 2.00042772e+02 3.49321972e+02\n",
      " 4.71820915e+02 1.32857175e+02 1.42892670e+03 1.78486963e+03\n",
      " 1.19507855e+03 1.38558003e+02 2.03934762e+03 2.22961260e+01\n",
      " 7.89299357e+02 8.28622065e+02 1.46628413e+03 2.16688900e+02\n",
      " 3.00617934e+02 2.25004382e+02 2.51092350e+01 4.73474010e+01\n",
      " 0.00000000e+00 1.38820547e+02 1.31400614e+02 3.61470188e+03\n",
      " 0.00000000e+00 7.95938290e+01 1.14250920e+02 1.93451644e+02\n",
      " 2.21060000e+00 1.12431875e+03 8.39124630e+01 3.51780160e+01\n",
      " 2.20787574e+03 1.21325430e+03 3.48885880e+02 0.00000000e+00\n",
      " 4.51905610e+01 7.04061109e+02 0.00000000e+00 2.03598108e+02\n",
      " 9.46070150e+01 3.46768959e+02 4.57030380e+01 5.65634584e+02\n",
      " 0.00000000e+00 6.47947670e+01 6.63508580e+01 0.00000000e+00\n",
      " 3.09587020e+01 0.00000000e+00 3.28976360e+01 2.80737850e+01\n",
      " 3.08925780e+01 4.91965570e+01 1.90869950e+01 2.28608740e+01\n",
      " 2.65482300e+01 2.16018230e+01 1.28747900e+00 1.29026890e+01\n",
      " 3.85123710e+01 1.66623070e+01 1.79266560e+01 0.00000000e+00\n",
      " 0.00000000e+00 8.56959200e+00 1.28711900e+01 1.67611700e+01\n",
      " 1.55206590e+01 5.73429000e-01 1.38362660e+01 2.77966000e+00\n",
      " 0.00000000e+00 1.08280540e+01 8.91228500e+00 8.06080200e+00\n",
      " 8.21577000e-01 7.07735300e+00 7.06449600e+00 1.59853500e+01\n",
      " 7.33286700e+00 8.25641500e+00 1.25823720e+01 7.88116100e+00\n",
      " 1.12630000e+00 3.82114900e+00 5.63382900e+00 0.00000000e+00\n",
      " 3.83241100e+00 4.72200900e+00 6.33661700e+00 3.83025700e+00\n",
      " 3.01171300e+00 2.89270400e+00 0.00000000e+00 3.48717300e+00\n",
      " 4.03247100e+00 0.00000000e+00 1.14859750e+01 6.52447274e+02\n",
      " 2.35465415e+03 0.00000000e+00 1.96081539e+03 1.32602000e-01\n",
      " 6.89750000e-01 2.50037175e+02 2.85568560e+01 3.92706147e+02\n",
      " 5.79694089e+02 1.32532110e+02 0.00000000e+00 0.00000000e+00\n",
      " 1.28473055e+02 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 3.45223200e+00 7.78020122e+03 1.08788420e+01 3.21266200e+00\n",
      " 4.94201050e+01 0.00000000e+00 2.63643400e+00 3.82033400e+00\n",
      " 0.00000000e+00 0.00000000e+00 8.17223800e+00 0.00000000e+00\n",
      " 2.35124941e+03 2.15302958e+03 4.30176200e+00 2.07765144e+02\n",
      " 1.33727020e+01 0.00000000e+00 1.76434308e+03 1.88156881e+03\n",
      " 1.88380500e+03 4.64848862e+02 1.63862737e+02 0.00000000e+00\n",
      " 5.55684782e+02 1.56159579e+02 6.78062334e+03 3.74772000e-01\n",
      " 0.00000000e+00 5.49397820e+01 0.00000000e+00 2.19467600e+00\n",
      " 1.91990800e+00 0.00000000e+00 1.70324900e+00 7.38649000e-01\n",
      " 7.89628325e+02 1.53509400e+00 2.21843600e+00 1.96694400e+00\n",
      " 2.04021000e+00 0.00000000e+00 0.00000000e+00 2.56937580e+01\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 2.99494446e+02 6.68349820e+01 5.64817923e+02 0.00000000e+00\n",
      " 1.10320700e+00 8.03395440e+01 4.12541479e+02 3.13256350e+01\n",
      " 0.00000000e+00 1.55809404e+04 0.00000000e+00 7.78484000e+02\n",
      " 0.00000000e+00 1.64257750e+01 9.05238170e+01 2.06733000e-01\n",
      " 0.00000000e+00 1.83775054e+02 0.00000000e+00 7.34725162e+02\n",
      " 1.29007851e+02 1.33039610e+01 0.00000000e+00 0.00000000e+00\n",
      " 3.13623000e-01 6.38399790e+01 1.81689085e+02 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 9.63822300e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 7.51368400e+00 1.27395845e+03\n",
      " 3.92457500e+00 4.30931910e+01 3.04360530e+01 3.01063196e+04\n",
      " 2.87594250e+01 0.00000000e+00 3.38614219e+02 1.34746900e+00\n",
      " 4.39135200e+00 2.29665249e+02 1.83800002e+02 8.17468750e+01\n",
      " 2.13944263e+02 1.37498714e+02 0.00000000e+00 0.00000000e+00\n",
      " 6.09888000e-01 9.19063000e-01 0.00000000e+00 0.00000000e+00\n",
      " 2.45519300e+00 2.90708600e+00 0.00000000e+00 0.00000000e+00\n",
      " 7.72860000e-02 0.00000000e+00 0.00000000e+00 6.26078780e+01\n",
      " 5.63991000e-01 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 8.21498100e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 1.55403000e-01 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 3.08404855e+02 0.00000000e+00 3.35884120e+02\n",
      " 0.00000000e+00 0.00000000e+00 6.80408474e+02 3.28977700e+03\n",
      " 2.99709742e+03 0.00000000e+00 6.48744601e+02 8.23600780e+01\n",
      " 9.64318690e+01 4.25673300e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 7.02700000e-02 9.20190000e-02 5.78120000e-02\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 1.41059912e+02 0.00000000e+00 6.06266910e+01 0.00000000e+00\n",
      " 2.00289764e+02 5.78210000e-02 4.17947901e+02 1.35790000e-02\n",
      " 2.58401000e-01 5.16070930e+01 3.11332845e+02 6.10700000e-02\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 7.18830900e+01 0.00000000e+00 9.69410800e+00 4.82844140e+01\n",
      " 5.13276430e+01 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 4.68861310e+01 1.89002827e+02\n",
      " 0.00000000e+00 0.00000000e+00 9.24073099e+02 2.95000000e-04\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 4.89203434e+02\n",
      " 1.57016370e+01 0.00000000e+00 1.31980480e+02 4.01316000e-01\n",
      " 2.68042570e+01 8.51409700e+00 0.00000000e+00 1.69027707e+02\n",
      " 2.09245507e+02 0.00000000e+00 1.17566440e+01 0.00000000e+00\n",
      " 5.72090858e+02 6.38000000e-04 0.00000000e+00 1.87762796e+04\n",
      " 3.61172017e+02 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 3.26412500e+01 0.00000000e+00 2.10622142e+02 4.56650000e-02\n",
      " 3.46731060e+01 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 2.03766790e+03 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 5.16402140e+01 1.08118660e+01 1.66774700e+01\n",
      " 0.00000000e+00 1.42788288e+04 0.00000000e+00 4.17026614e+02\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 7.90000000e-04 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 1.23783650e+01\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 6.16360560e+01 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 2.42072576e+02 1.30048162e+02 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 1.18958390e+01 1.39351860e+01 1.44232110e+01\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 6.98472600e+01\n",
      " 0.00000000e+00 5.19602810e+01 1.97716698e+03 1.46563003e+03\n",
      " 0.00000000e+00 2.74722931e+02 0.00000000e+00 1.35369785e+02\n",
      " 0.00000000e+00 0.00000000e+00 3.26804187e+02 0.00000000e+00\n",
      " 2.95777510e+01 6.47038380e+01 3.46950000e+00 6.41559490e+01\n",
      " 0.00000000e+00 0.00000000e+00 6.72044950e+01 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 6.26426300e+00 1.69741000e+01\n",
      " 0.00000000e+00 1.65353120e+01 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 6.14943490e+01\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 0.00000000e+00 4.54890224e+02\n",
      " 1.28077795e+04 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 4.92009600e+00 0.00000000e+00 2.15277540e+01 2.21915319e+02\n",
      " 0.00000000e+00 2.15370379e+02 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 0.00000000e+00 1.54378879e+02 1.12307900e+00\n",
      " 5.98306770e+01 0.00000000e+00 0.00000000e+00 0.00000000e+00\n",
      " 0.00000000e+00 8.28447400e+00 5.74215600e+00 4.52356910e+01\n",
      " 7.26473592e+02 0.00000000e+00 0.00000000e+00 3.22359304e+02\n",
      " 0.00000000e+00]' has dtype incompatible with int64, please explicitly cast to a compatible dtype first.\n",
      "  df4.loc[:, 'interest_iasset_amount'] = df4['interest_iasset_amount'].astype(float) / 1000000\n"
     ]
    }
   ],
   "source": [
    "#Inputs\n",
    "interest_rate = 20.075\n",
    "interest_rate_introduced = 1714320140000\n",
    "timestamp = 1720117800\n",
    "ADA_price = 0.363161\n",
    "iAsset_price = 0.999155\n",
    "LR = 1.2\n",
    "RMR = 1.85\n",
    "\n",
    "# Fetch data from API\n",
    "headers = {\n",
    "    'content-type': 'application/json',\n",
    "}\n",
    "\n",
    "json_data = {\n",
    "    'timestamp': timestamp\n",
    "}\n",
    "\n",
    "response = requests.post('https://analytics.indigoprotocol.io/api/cdps', headers=headers, json=json_data)\n",
    "ans = response.json()\n",
    "\n",
    "df3 = pd.DataFrame(ans)\n",
    "df3 = df3.drop(['output_hash', 'output_index', 'fee_lovelaces_indy_stakers', 'fee_lovelaces_treasury', 'owner'], axis=1)\n",
    "\n",
    "# Filter for 'iUSD' asset\n",
    "df4 = df3[df3['asset'] == 'iUSD'].copy()  # Use `.copy()` to avoid SettingWithCopyWarning\n",
    "\n",
    "# Modify columns using .loc[]\n",
    "# Modify columns using .loc[], ensuring the correct dtype\n",
    "df4.loc[:, 'collateralAmount'] = df4['collateralAmount'].astype(float) / 1000000\n",
    "df4.loc[:, 'mintedAmount'] = df4['mintedAmount'].astype(float) / 1000000\n",
    "df4.loc[:, 'interest_iasset_amount'] = df4['interest_iasset_amount'].astype(float) / 1000000\n",
    "\n",
    "# Calculate interest accrued and corresponding CR, ensuring dtype consistency\n",
    "df4.loc[:, 'interest_last_updated'] = df4.apply(\n",
    "    lambda x: interest_rate_introduced if x['interest_last_updated'] < interest_rate_introduced else x['interest_last_updated'],\n",
    "    axis=1\n",
    ")\n",
    "\n",
    "df4.loc[:, 'interest_time'] = (timestamp * 1000) - df4['interest_last_updated']\n",
    "df4['interest_time'] = df4['interest_time'].astype(float)  # Ensure this column is float type\n",
    "\n",
    "df4.insert(6, \"adjustedinterestAmount\", df4['interest_iasset_amount'] + (\n",
    "    (df4['interest_time'] / (1000 * 60 * 60 * 24 * 365)) * (interest_rate / 100) * df4['mintedAmount']\n",
    ").astype(float))  # Explicitly cast the calculation result to float\n",
    "\n",
    "df4.insert(3, \"adjustedcollateralAmount\", ((df4['collateralAmount'] - (\n",
    "    df4['adjustedinterestAmount'] * iAsset_price / ADA_price\n",
    ")).astype(float)))  # Ensure float type for calculated results\n",
    "\n",
    "df4.insert(7, \"CR\", (((df4['adjustedcollateralAmount'] * ADA_price) / (\n",
    "    df4['mintedAmount'] * iAsset_price\n",
    ")).astype(float)))  # Ensure float type for CR calculation\n",
    "\n",
    "#calculating redeemable amount of iUSD\n",
    "df5 = df4[(df4['CR'] > 1.2) & (df4['CR'] < RMR)]\n",
    "df5.insert(5, column=\"reedemable\", value=(1/(RMR-1))*((RMR*df5['mintedAmount'])-((df5['adjustedcollateralAmount']*ADA_price)/iAsset_price)))\n",
    "print(\"Redeemable iUSD: \"+str(df5['reedemable'].sum()))\n",
    "\n",
    "#filtering dataframe\n",
    "df6 = df5.sort_values(by=['CR'], ascending=True)\n",
    "columns_to_drop = ['interest_last_updated']\n",
    "df6 = df6.drop(columns=columns_to_drop)\n",
    "# print(df6.to_string())\n",
    "print(\"Redeemable CDPs: \"+ str(df6['CR'].count()))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d5477f3b",
   "metadata": {},
   "source": [
    "**The results obtained above are:**\n",
    "\n",
    "Redeemable iUSD: 13675.977217450523\n",
    "\n",
    "Redeemable CDPs: 133\n",
    "\n",
    "**These are used as inputs in the algo interest model.**"
   ]
  }
 ],
 "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.12.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}