test_notebook.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import streamlit as st\n",
    "import pandas as pd\n",
    "import requests\n",
    "import ast"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "nested_egg = pd.read_json('https://api.flipsidecrypto.com/api/v2/queries/290a91fb-8186-486d-992e-985839352809/data/latest')\n",
    "loot = pd.read_json('https://api.flipsidecrypto.com/api/v2/queries/2208cc91-e624-4b40-b666-f65c8b6405f8/data/latest')\n",
    "eggs = pd.read_json('https://api.flipsidecrypto.com/api/v2/queries/ee319c5f-4f03-41b5-b607-99f306fe1e2f/data/latest')\n",
    "meteor_dust = pd.read_json('https://api.flipsidecrypto.com/api/v2/queries/8eebec3c-db0e-4051-a5c2-50a5deb1070b/data/latest')\n",
    "meteors = pd.read_json('https://api.flipsidecrypto.com/api/v2/queries/b9778a1d-9b63-4ba5-a48f-7b28569b5603/data/latest')\n",
    "#IT IS ABSOLUTELY CRUCIAL THAT YOU DO NOT CHANGE THE ORIGINAL NAMES OF THE DATAFRAME COLUMNS ON VELOCITY\n",
    "\n",
    "#MUST STORE IN A DICTIONARY \n",
    "current_nft_dict = {'nested_egg':nested_egg, 'loot':loot, 'eggs':eggs, 'meteor_dust':meteor_dust, 'meteors':meteors}\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "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>nested_egg</th>\n",
       "      <th>loot</th>\n",
       "      <th>eggs</th>\n",
       "      <th>meteor_dust</th>\n",
       "      <th>meteors</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BLOCK_TIMESTAMP</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2022-02-02</th>\n",
       "      <td>6.25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-02-02</th>\n",
       "      <td>38.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-02-02</th>\n",
       "      <td>13.20</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-02-02</th>\n",
       "      <td>6.80</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-02-02</th>\n",
       "      <td>7.45</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-11-11</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-11-11</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-11-11</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-11-11</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-11-11</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>14527 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 nested_egg  loot  eggs  meteor_dust  meteors\n",
       "BLOCK_TIMESTAMP                                              \n",
       "2022-02-02             6.25   NaN   NaN          NaN      NaN\n",
       "2022-02-02            38.00   NaN   NaN          NaN      NaN\n",
       "2022-02-02            13.20   NaN   NaN          NaN      NaN\n",
       "2022-02-02             6.80   NaN   NaN          NaN      NaN\n",
       "2022-02-02             7.45   NaN   NaN          NaN      NaN\n",
       "...                     ...   ...   ...          ...      ...\n",
       "2021-11-11              NaN   NaN   NaN          NaN     8.88\n",
       "2021-11-11              NaN   NaN   NaN          NaN    10.00\n",
       "2021-11-11              NaN   NaN   NaN          NaN     0.50\n",
       "2021-11-11              NaN   NaN   NaN          NaN     0.80\n",
       "2021-11-11              NaN   NaN   NaN          NaN     0.99\n",
       "\n",
       "[14527 rows x 5 columns]"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nft_luna_price_df = []\n",
    "for nft in current_nft_dict:\n",
    "    #renames columns from Flipside dataframes according to NFT\n",
    "    nft_luna_price_df.append(current_nft_dict[nft][['BLOCK_TIMESTAMP', 'NFT_LUNA_PRICE']].rename({'NFT_LUNA_PRICE':f'{nft}'}, axis = 1))\n",
    "nft_luna_price_df = pd.concat(nft_luna_price_df)\n",
    "nft_luna_price_df['BLOCK_TIMESTAMP'] = pd.to_datetime(nft_luna_price_df['BLOCK_TIMESTAMP'])\n",
    "nft_luna_price_df.set_index('BLOCK_TIMESTAMP', inplace = True)\n",
    "nft_luna_price_df.index = nft_luna_price_df.index.round('D')\n",
    "nft_luna_price_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# START HERE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "nft_luna_price_df = []\n",
    "for nft in current_nft_dict:\n",
    "    #renames columns from Flipside dataframes according to NFT\n",
    "    nft_luna_price_df.append(current_nft_dict[nft][['BLOCK_TIMESTAMP', 'NFT_TYPE', 'NFT_LUNA_PRICE', 'NFT_UST_PRICE_AT_PURCHASE']])\n",
    "\n",
    "nft_luna_price_df = pd.concat(nft_luna_price_df)\n",
    "nft_luna_price_df['BLOCK_TIMESTAMP'] = pd.to_datetime(nft_luna_price_df['BLOCK_TIMESTAMP'])\n",
    "nft_luna_price_df.set_index('BLOCK_TIMESTAMP', inplace = True)\n",
    "nft_luna_price_df.index = nft_luna_price_df.index.round('D')\n",
    "nft_luna_price_df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_df = nft_luna_price_df.groupby(['BLOCK_TIMESTAMP', 'NFT_TYPE']).sum().rename(columns={'NFT_LUNA_PRICE':'TOTAL_LUNA', 'NFT_UST_PRICE_AT_PURCHASE':'TOTAL_UST'})\n",
    "average_df = nft_luna_price_df.groupby(['BLOCK_TIMESTAMP', 'NFT_TYPE']).mean().rename(columns={'NFT_LUNA_PRICE':'AVERAGE_LUNA', 'NFT_UST_PRICE_AT_PURCHASE':'AVERAGE_UST'})\n",
    "tx_count_df = nft_luna_price_df.groupby(['BLOCK_TIMESTAMP', 'NFT_TYPE']).count().rename(columns={'NFT_LUNA_PRICE':'TRANSACTION_COUNT'})\n",
    "tx_count_df = tx_count_df['TRANSACTION_COUNT']\n",
    "min_df = nft_luna_price_df.groupby(['BLOCK_TIMESTAMP', 'NFT_TYPE']).min().rename(columns={'NFT_LUNA_PRICE':'MIN_LUNA', 'NFT_UST_PRICE_AT_PURCHASE':'MIN_UST'})\n",
    "max_df = nft_luna_price_df.groupby(['BLOCK_TIMESTAMP', 'NFT_TYPE']).max().rename(columns={'NFT_LUNA_PRICE':'MAX_LUNA', 'NFT_UST_PRICE_AT_PURCHASE':'MAX_UST'})\n",
    "median_df = nft_luna_price_df.groupby(['BLOCK_TIMESTAMP', 'NFT_TYPE']).median().rename(columns={'NFT_LUNA_PRICE':'MEDIAN_LUNA', 'NFT_UST_PRICE_AT_PURCHASE':'MEDIAN_UST'})\n",
    "cum_sum = nft_luna_price_df.groupby(['BLOCK_TIMESTAMP', 'NFT_TYPE']).sum().rename(columns={'NFT_LUNA_PRICE':'TOTAL_LUNA_CUMULATIVE', 'NFT_UST_PRICE_AT_PURCHASE':'TOTAL_UST_CUMULATIVE'}).cumsum(axis=0)\n",
    "\n",
    "\n",
    "master_df = pd.concat([total_df, tx_count_df, average_df, min_df, max_df, median_df, cum_sum], axis = 1)\n",
    "master_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# RARITY TABLE - Meteors, Eggs, Dust\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "# nft_luna_price_df = []\n",
    "# for nft in current_nft_dict:\n",
    "#     #renames columns from Flipside dataframes according to NFT\n",
    "#     nft_luna_price_df.append(current_nft_dict[nft][['BLOCK_TIMESTAMP', 'NFT_LUNA_PRICE']].rename({'NFT_LUNA_PRICE':f'{nft}'}, axis = 1))\n",
    "# nft_luna_price_df = pd.concat(nft_luna_price_df)\n",
    "# nft_luna_price_df['BLOCK_TIMESTAMP'] = pd.to_datetime(nft_luna_price_df['BLOCK_TIMESTAMP'])\n",
    "# nft_luna_price_df.set_index('BLOCK_TIMESTAMP', inplace = True)\n",
    "# nft_luna_price_df.index = nft_luna_price_df.index.round('D')\n",
    "\n",
    "#meteors\n",
    "meteor_rarity = pd.read_json('https://api.flipsidecrypto.com/api/v2/queries/5d7b0f96-a537-46e5-9b3c-d5145072279f/data/latest')\n",
    "meteor_rarity_merge = pd.merge(meteors, meteor_rarity[['TOKEN_ID', 'RARITY']], on='TOKEN_ID')\n",
    "egg_rarity = pd.read_json('https://api.flipsidecrypto.com/api/v2/queries/284600cc-7b4c-4d47-ab50-dbdd528daf93/data/latest')\n",
    "egg_rarity_merge = pd.merge(eggs, egg_rarity[['TOKEN_ID', 'RARITY']], on='TOKEN_ID')\n",
    "dust_rarity = pd.read_json('https://api.flipsidecrypto.com/api/v2/queries/f4b78c34-07ec-4f5e-8099-7e6db2db24a9/data/latest')\n",
    "dust_rarity_merge = pd.merge(meteor_dust, dust_rarity[['TOKEN_ID', 'RARITY']], on='TOKEN_ID')\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "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_TIMESTAMP</th>\n",
       "      <th>HOUR</th>\n",
       "      <th>LUNA_UST_PRICE</th>\n",
       "      <th>NFT_LUNA_PRICE</th>\n",
       "      <th>NFT_TYPE</th>\n",
       "      <th>NFT_UST_PRICE_AT_PURCHASE</th>\n",
       "      <th>TOKEN_ID</th>\n",
       "      <th>TX_ID</th>\n",
       "      <th>RARITY</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-02-02 13:40:23.896</td>\n",
       "      <td>2022-02-02 13:00:00.000</td>\n",
       "      <td>52.998104</td>\n",
       "      <td>5.99000</td>\n",
       "      <td>Levana Meteor Dust</td>\n",
       "      <td>317.458645</td>\n",
       "      <td>2675</td>\n",
       "      <td>7A87BABF7003EDB84EA3E64EB06D36684A35615A96A700...</td>\n",
       "      <td>Ancient</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-02-02 13:18:43.616</td>\n",
       "      <td>2022-02-02 13:00:00.000</td>\n",
       "      <td>52.998104</td>\n",
       "      <td>0.57999</td>\n",
       "      <td>Levana Meteor Dust</td>\n",
       "      <td>30.738371</td>\n",
       "      <td>25494</td>\n",
       "      <td>187AC5C05179638DC31D97DDFBF881C183622BBFCD6DA8...</td>\n",
       "      <td>Common</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-02-02 11:48:24.137</td>\n",
       "      <td>2022-02-02 11:00:00.000</td>\n",
       "      <td>52.766880</td>\n",
       "      <td>2.00000</td>\n",
       "      <td>Levana Meteor Dust</td>\n",
       "      <td>105.533760</td>\n",
       "      <td>11368</td>\n",
       "      <td>A6E86B8A3860137ADA29196440A06E240E094020B78382...</td>\n",
       "      <td>Rare</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-02-02 10:33:58.898</td>\n",
       "      <td>2022-02-02 10:00:00.000</td>\n",
       "      <td>52.384832</td>\n",
       "      <td>1.75000</td>\n",
       "      <td>Levana Meteor Dust</td>\n",
       "      <td>91.673457</td>\n",
       "      <td>13625</td>\n",
       "      <td>4CEBB04BC55D5C6D77579B5B9D4DDD36E14C1B521E916A...</td>\n",
       "      <td>Common</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-02-01 23:56:28.591</td>\n",
       "      <td>2022-02-01 23:00:00.000</td>\n",
       "      <td>51.913699</td>\n",
       "      <td>0.50000</td>\n",
       "      <td>Levana Meteor Dust</td>\n",
       "      <td>25.956850</td>\n",
       "      <td>13625</td>\n",
       "      <td>7A9917F5876DB4C98D776C011A45FAB0BBCA89E5731EF4...</td>\n",
       "      <td>Common</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6696</th>\n",
       "      <td>2021-12-01 05:05:54.559</td>\n",
       "      <td>2021-12-01 05:00:00.000</td>\n",
       "      <td>56.933031</td>\n",
       "      <td>0.25000</td>\n",
       "      <td>Levana Meteor Dust</td>\n",
       "      <td>14.233258</td>\n",
       "      <td>22565</td>\n",
       "      <td>5232811606166150AD84C9864AE2845BCD2B697B5278D2...</td>\n",
       "      <td>Common</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6697</th>\n",
       "      <td>2021-12-01 05:06:26.995</td>\n",
       "      <td>2021-12-01 05:00:00.000</td>\n",
       "      <td>56.933031</td>\n",
       "      <td>0.25000</td>\n",
       "      <td>Levana Meteor Dust</td>\n",
       "      <td>14.233258</td>\n",
       "      <td>21171</td>\n",
       "      <td>22D3AC754B6BC296854667EBF64C90A53356EF61EBF482...</td>\n",
       "      <td>Common</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6698</th>\n",
       "      <td>2021-12-01 04:09:01.855</td>\n",
       "      <td>2021-12-01 04:00:00.000</td>\n",
       "      <td>56.933031</td>\n",
       "      <td>3.00000</td>\n",
       "      <td>Levana Meteor Dust</td>\n",
       "      <td>170.799092</td>\n",
       "      <td>3796</td>\n",
       "      <td>7C140B710214E49E9B0F25502376292D985EC180F8FF47...</td>\n",
       "      <td>Rare</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6699</th>\n",
       "      <td>2021-12-01 04:56:10.835</td>\n",
       "      <td>2021-12-01 04:00:00.000</td>\n",
       "      <td>56.933031</td>\n",
       "      <td>0.30000</td>\n",
       "      <td>Levana Meteor Dust</td>\n",
       "      <td>17.079909</td>\n",
       "      <td>22941</td>\n",
       "      <td>F26FF3F8A85F68ED8F61510A4B183674F1D18CC52F74DA...</td>\n",
       "      <td>Common</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6700</th>\n",
       "      <td>2021-12-01 04:42:14.081</td>\n",
       "      <td>2021-12-01 04:00:00.000</td>\n",
       "      <td>56.933031</td>\n",
       "      <td>6.50000</td>\n",
       "      <td>Levana Meteor Dust</td>\n",
       "      <td>370.064700</td>\n",
       "      <td>2784</td>\n",
       "      <td>C8CBF07E1C39481A47CE9F4492C700519BE5E1B78173CB...</td>\n",
       "      <td>Ancient</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6701 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              BLOCK_TIMESTAMP                     HOUR  LUNA_UST_PRICE  \\\n",
       "0     2022-02-02 13:40:23.896  2022-02-02 13:00:00.000       52.998104   \n",
       "1     2022-02-02 13:18:43.616  2022-02-02 13:00:00.000       52.998104   \n",
       "2     2022-02-02 11:48:24.137  2022-02-02 11:00:00.000       52.766880   \n",
       "3     2022-02-02 10:33:58.898  2022-02-02 10:00:00.000       52.384832   \n",
       "4     2022-02-01 23:56:28.591  2022-02-01 23:00:00.000       51.913699   \n",
       "...                       ...                      ...             ...   \n",
       "6696  2021-12-01 05:05:54.559  2021-12-01 05:00:00.000       56.933031   \n",
       "6697  2021-12-01 05:06:26.995  2021-12-01 05:00:00.000       56.933031   \n",
       "6698  2021-12-01 04:09:01.855  2021-12-01 04:00:00.000       56.933031   \n",
       "6699  2021-12-01 04:56:10.835  2021-12-01 04:00:00.000       56.933031   \n",
       "6700  2021-12-01 04:42:14.081  2021-12-01 04:00:00.000       56.933031   \n",
       "\n",
       "      NFT_LUNA_PRICE            NFT_TYPE  NFT_UST_PRICE_AT_PURCHASE  TOKEN_ID  \\\n",
       "0            5.99000  Levana Meteor Dust                 317.458645      2675   \n",
       "1            0.57999  Levana Meteor Dust                  30.738371     25494   \n",
       "2            2.00000  Levana Meteor Dust                 105.533760     11368   \n",
       "3            1.75000  Levana Meteor Dust                  91.673457     13625   \n",
       "4            0.50000  Levana Meteor Dust                  25.956850     13625   \n",
       "...              ...                 ...                        ...       ...   \n",
       "6696         0.25000  Levana Meteor Dust                  14.233258     22565   \n",
       "6697         0.25000  Levana Meteor Dust                  14.233258     21171   \n",
       "6698         3.00000  Levana Meteor Dust                 170.799092      3796   \n",
       "6699         0.30000  Levana Meteor Dust                  17.079909     22941   \n",
       "6700         6.50000  Levana Meteor Dust                 370.064700      2784   \n",
       "\n",
       "                                                  TX_ID   RARITY  \n",
       "0     7A87BABF7003EDB84EA3E64EB06D36684A35615A96A700...  Ancient  \n",
       "1     187AC5C05179638DC31D97DDFBF881C183622BBFCD6DA8...   Common  \n",
       "2     A6E86B8A3860137ADA29196440A06E240E094020B78382...     Rare  \n",
       "3     4CEBB04BC55D5C6D77579B5B9D4DDD36E14C1B521E916A...   Common  \n",
       "4     7A9917F5876DB4C98D776C011A45FAB0BBCA89E5731EF4...   Common  \n",
       "...                                                 ...      ...  \n",
       "6696  5232811606166150AD84C9864AE2845BCD2B697B5278D2...   Common  \n",
       "6697  22D3AC754B6BC296854667EBF64C90A53356EF61EBF482...   Common  \n",
       "6698  7C140B710214E49E9B0F25502376292D985EC180F8FF47...     Rare  \n",
       "6699  F26FF3F8A85F68ED8F61510A4B183674F1D18CC52F74DA...   Common  \n",
       "6700  C8CBF07E1C39481A47CE9F4492C700519BE5E1B78173CB...  Ancient  \n",
       "\n",
       "[6701 rows x 9 columns]"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# RARITY - Nested Eggs, Loot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Alternative using swagger; this method takes an exceptionally large amount of time\n",
    "def swagger_app(num):\n",
    "        rarity = requests.get(f\"https://fcd.terra.dev/wasm/contracts/terra1vhuyuwwr4rkdpez5f5lmuqavut28h5dt29rpn6/store?query_msg=%7B%22nft_info%22:%7B%22token_id%22:%22{num}%22%7D%7D\").json()\n",
    "        return rarity.get('result').get('extension').get('attributes')[1].get('value')\n",
    "        #print(num)\n",
    "\n",
    "nested_egg['RARITY'] = nested_egg['TOKEN_ID'].apply(lambda x: swagger_app(x))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#for nested eggs\n",
    "#This should be the faster version pulling directly from the server set up to scrape RE\n",
    "nested_rarity = pd.read_csv('http://165.22.125.123/nested_egg_nfts.csv ')\n",
    "nested_rarity_merge = pd.merge(nested_egg, nested_rarity[['token_id', 'traits']], left_on='TOKEN_ID', right_on='token_id', how='inner')\n",
    "nested_rarity_merge['RARITY'] = nested_rarity_merge['traits'].apply(lambda x: ast.literal_eval(x).get('Rarity'))\n",
    "nested_rarity_merge.pop('traits')\n",
    "nested_rarity_merge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "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_TIMESTAMP</th>\n",
       "      <th>HOUR</th>\n",
       "      <th>LUNA_UST_PRICE</th>\n",
       "      <th>NFT_LUNA_PRICE</th>\n",
       "      <th>NFT_TYPE</th>\n",
       "      <th>NFT_UST_PRICE_AT_PURCHASE</th>\n",
       "      <th>TOKEN_ID</th>\n",
       "      <th>TX_ID</th>\n",
       "      <th>token_id</th>\n",
       "      <th>RARITY</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-02-01 18:05:46.834</td>\n",
       "      <td>2022-02-01 18:00:00.000</td>\n",
       "      <td>52.204693</td>\n",
       "      <td>1.75</td>\n",
       "      <td>Levana Loot</td>\n",
       "      <td>91.358213</td>\n",
       "      <td>3110</td>\n",
       "      <td>530DC5DACA625F01A66CFC865EEA717DC0367A4DB21458...</td>\n",
       "      <td>3110</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-02-01 17:36:32.132</td>\n",
       "      <td>2022-02-01 17:00:00.000</td>\n",
       "      <td>52.207602</td>\n",
       "      <td>6.12</td>\n",
       "      <td>Levana Loot</td>\n",
       "      <td>319.510523</td>\n",
       "      <td>625</td>\n",
       "      <td>F9648A102B52659D339E1B7823DD5C5E124CDB93C5E51D...</td>\n",
       "      <td>625</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-02-01 17:11:26.029</td>\n",
       "      <td>2022-02-01 17:00:00.000</td>\n",
       "      <td>52.207602</td>\n",
       "      <td>6.07</td>\n",
       "      <td>Levana Loot</td>\n",
       "      <td>316.900143</td>\n",
       "      <td>1590</td>\n",
       "      <td>5A39F8EAEEC27731E60EAA3255A84E547956F1CF11AC66...</td>\n",
       "      <td>1590</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-02-01 16:39:16.438</td>\n",
       "      <td>2022-02-01 16:00:00.000</td>\n",
       "      <td>52.041715</td>\n",
       "      <td>5.63</td>\n",
       "      <td>Levana Loot</td>\n",
       "      <td>292.994855</td>\n",
       "      <td>3878</td>\n",
       "      <td>67E40CBE29C96F831C45EC6B4F3E7BC699BBD15152B577...</td>\n",
       "      <td>3878</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-02-01 16:34:49.595</td>\n",
       "      <td>2022-02-01 16:00:00.000</td>\n",
       "      <td>52.041715</td>\n",
       "      <td>6.00</td>\n",
       "      <td>Levana Loot</td>\n",
       "      <td>312.250290</td>\n",
       "      <td>2024</td>\n",
       "      <td>90D30E3F0192EE28BBD76AF3BD163320FC41416C541872...</td>\n",
       "      <td>2024</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>638</th>\n",
       "      <td>2022-01-12 13:36:49.066</td>\n",
       "      <td>2022-01-12 13:00:00.000</td>\n",
       "      <td>78.392752</td>\n",
       "      <td>7.00</td>\n",
       "      <td>Levana Loot</td>\n",
       "      <td>548.749264</td>\n",
       "      <td>336</td>\n",
       "      <td>98AA8E00EF2F994929ED619EAA924B60C86A48411BE0B5...</td>\n",
       "      <td>336</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>639</th>\n",
       "      <td>2022-01-12 12:28:57.408</td>\n",
       "      <td>2022-01-12 12:00:00.000</td>\n",
       "      <td>78.293709</td>\n",
       "      <td>2.50</td>\n",
       "      <td>Levana Loot</td>\n",
       "      <td>195.734272</td>\n",
       "      <td>20</td>\n",
       "      <td>34DA05F39CFECBDB2584F7B43A04E261FDECF4C6502447...</td>\n",
       "      <td>20</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>640</th>\n",
       "      <td>2022-01-12 12:29:40.436</td>\n",
       "      <td>2022-01-12 12:00:00.000</td>\n",
       "      <td>78.293709</td>\n",
       "      <td>2.50</td>\n",
       "      <td>Levana Loot</td>\n",
       "      <td>195.734272</td>\n",
       "      <td>62</td>\n",
       "      <td>939D8F43C21F1A4E846C829E81D01B35CAD4F46B18242D...</td>\n",
       "      <td>62</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>641</th>\n",
       "      <td>2022-01-12 12:45:08.249</td>\n",
       "      <td>2022-01-12 12:00:00.000</td>\n",
       "      <td>78.293709</td>\n",
       "      <td>2.50</td>\n",
       "      <td>Levana Loot</td>\n",
       "      <td>195.734272</td>\n",
       "      <td>129</td>\n",
       "      <td>8D108BCECDD68B4D5403A03262514CF893387B92F2B970...</td>\n",
       "      <td>129</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>642</th>\n",
       "      <td>2022-01-12 12:25:56.803</td>\n",
       "      <td>2022-01-12 12:00:00.000</td>\n",
       "      <td>78.293709</td>\n",
       "      <td>2.50</td>\n",
       "      <td>Levana Loot</td>\n",
       "      <td>195.734272</td>\n",
       "      <td>17</td>\n",
       "      <td>E2F7A7D8AC88F0D99CE630C618524C2E5FDA1D5750DDDD...</td>\n",
       "      <td>17</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>643 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             BLOCK_TIMESTAMP                     HOUR  LUNA_UST_PRICE  \\\n",
       "0    2022-02-01 18:05:46.834  2022-02-01 18:00:00.000       52.204693   \n",
       "1    2022-02-01 17:36:32.132  2022-02-01 17:00:00.000       52.207602   \n",
       "2    2022-02-01 17:11:26.029  2022-02-01 17:00:00.000       52.207602   \n",
       "3    2022-02-01 16:39:16.438  2022-02-01 16:00:00.000       52.041715   \n",
       "4    2022-02-01 16:34:49.595  2022-02-01 16:00:00.000       52.041715   \n",
       "..                       ...                      ...             ...   \n",
       "638  2022-01-12 13:36:49.066  2022-01-12 13:00:00.000       78.392752   \n",
       "639  2022-01-12 12:28:57.408  2022-01-12 12:00:00.000       78.293709   \n",
       "640  2022-01-12 12:29:40.436  2022-01-12 12:00:00.000       78.293709   \n",
       "641  2022-01-12 12:45:08.249  2022-01-12 12:00:00.000       78.293709   \n",
       "642  2022-01-12 12:25:56.803  2022-01-12 12:00:00.000       78.293709   \n",
       "\n",
       "     NFT_LUNA_PRICE     NFT_TYPE  NFT_UST_PRICE_AT_PURCHASE  TOKEN_ID  \\\n",
       "0              1.75  Levana Loot                  91.358213      3110   \n",
       "1              6.12  Levana Loot                 319.510523       625   \n",
       "2              6.07  Levana Loot                 316.900143      1590   \n",
       "3              5.63  Levana Loot                 292.994855      3878   \n",
       "4              6.00  Levana Loot                 312.250290      2024   \n",
       "..              ...          ...                        ...       ...   \n",
       "638            7.00  Levana Loot                 548.749264       336   \n",
       "639            2.50  Levana Loot                 195.734272        20   \n",
       "640            2.50  Levana Loot                 195.734272        62   \n",
       "641            2.50  Levana Loot                 195.734272       129   \n",
       "642            2.50  Levana Loot                 195.734272        17   \n",
       "\n",
       "                                                 TX_ID  token_id RARITY  \n",
       "0    530DC5DACA625F01A66CFC865EEA717DC0367A4DB21458...      3110   None  \n",
       "1    F9648A102B52659D339E1B7823DD5C5E124CDB93C5E51D...       625   None  \n",
       "2    5A39F8EAEEC27731E60EAA3255A84E547956F1CF11AC66...      1590   None  \n",
       "3    67E40CBE29C96F831C45EC6B4F3E7BC699BBD15152B577...      3878   None  \n",
       "4    90D30E3F0192EE28BBD76AF3BD163320FC41416C541872...      2024   None  \n",
       "..                                                 ...       ...    ...  \n",
       "638  98AA8E00EF2F994929ED619EAA924B60C86A48411BE0B5...       336   None  \n",
       "639  34DA05F39CFECBDB2584F7B43A04E261FDECF4C6502447...        20   None  \n",
       "640  939D8F43C21F1A4E846C829E81D01B35CAD4F46B18242D...        62   None  \n",
       "641  8D108BCECDD68B4D5403A03262514CF893387B92F2B970...       129   None  \n",
       "642  E2F7A7D8AC88F0D99CE630C618524C2E5FDA1D5750DDDD...        17   None  \n",
       "\n",
       "[643 rows x 10 columns]"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loot_rarity = pd.read_csv('http://165.22.125.123/loot_nfts.csv')\n",
    "loot_rarity_merge = pd.merge(loot, loot_rarity[['token_id', 'traits']], left_on='TOKEN_ID', right_on='token_id', how='inner')\n",
    "loot_rarity_merge['RARITY'] = loot_rarity_merge['traits'].apply(lambda x: ast.literal_eval(x).get('Rarity'))\n",
    "loot_rarity_merge.pop('traits')\n",
    "loot_rarity_merge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "ename": "KeyError",
     "evalue": "'TOKEN_ID'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[1;32m~\\AppData\\Local\\Temp/ipykernel_26312/1523452061.py\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      2\u001b[0m \u001b[0mmeteors\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread_json\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'https://api.flipsidecrypto.com/api/v2/queries/b9778a1d-9b63-4ba5-a48f-7b28569b5603/data/latest'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[0mmeteors_rarity\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread_json\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'https://api.flipsidecrypto.com/api/v2/queries/284600cc-7b4c-4d47-ab50-dbdd528daf93/data/latest'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 4\u001b[1;33m \u001b[0mmeteors_rarity_merge\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mmerge\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mmeteors\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmeteors_rarity\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mgroup_master\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mon\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'TOKEN_ID'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      5\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      6\u001b[0m \u001b[0mmeteors_rarity_merge\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mmeteors_rarity_merge\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'BLOCK_TIMESTAMP'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m'RARITY'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m'NFT_LUNA_PRICE'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m'NFT_UST_PRICE_AT_PURCHASE'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\Python310\\lib\\site-packages\\pandas\\core\\reshape\\merge.py\u001b[0m in \u001b[0;36mmerge\u001b[1;34m(left, right, how, on, left_on, right_on, left_index, right_index, sort, suffixes, copy, indicator, validate)\u001b[0m\n\u001b[0;32m    104\u001b[0m     \u001b[0mvalidate\u001b[0m\u001b[1;33m:\u001b[0m \u001b[0mstr\u001b[0m \u001b[1;33m|\u001b[0m \u001b[1;32mNone\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    105\u001b[0m ) -> DataFrame:\n\u001b[1;32m--> 106\u001b[1;33m     op = _MergeOperation(\n\u001b[0m\u001b[0;32m    107\u001b[0m         \u001b[0mleft\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    108\u001b[0m         \u001b[0mright\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\Python310\\lib\\site-packages\\pandas\\core\\reshape\\merge.py\u001b[0m in \u001b[0;36m__init__\u001b[1;34m(self, left, right, how, on, left_on, right_on, axis, left_index, right_index, sort, suffixes, copy, indicator, validate)\u001b[0m\n\u001b[0;32m    697\u001b[0m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mright_join_keys\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    698\u001b[0m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mjoin_names\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 699\u001b[1;33m         ) = self._get_merge_keys()\n\u001b[0m\u001b[0;32m    700\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    701\u001b[0m         \u001b[1;31m# validate the merge keys dtypes. We may need to coerce\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\Python310\\lib\\site-packages\\pandas\\core\\reshape\\merge.py\u001b[0m in \u001b[0;36m_get_merge_keys\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m   1094\u001b[0m                     \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0mis_rkey\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mrk\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1095\u001b[0m                         \u001b[1;32mif\u001b[0m \u001b[0mrk\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1096\u001b[1;33m                             \u001b[0mright_keys\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mappend\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mright\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_get_label_or_level_values\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mrk\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1097\u001b[0m                         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1098\u001b[0m                             \u001b[1;31m# work-around for merge_asof(right_index=True)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\Python310\\lib\\site-packages\\pandas\\core\\generic.py\u001b[0m in \u001b[0;36m_get_label_or_level_values\u001b[1;34m(self, key, axis)\u001b[0m\n\u001b[0;32m   1777\u001b[0m             \u001b[0mvalues\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0maxes\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0maxis\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget_level_values\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_values\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1778\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1779\u001b[1;33m             \u001b[1;32mraise\u001b[0m \u001b[0mKeyError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1780\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1781\u001b[0m         \u001b[1;31m# Check for duplicates\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mKeyError\u001b[0m: 'TOKEN_ID'"
     ]
    }
   ],
   "source": [
    "group_master = ['BLOCK_TIMESTAMP']\n",
    "meteors = pd.read_json('https://api.flipsidecrypto.com/api/v2/queries/b9778a1d-9b63-4ba5-a48f-7b28569b5603/data/latest')\n",
    "meteors_rarity = pd.read_json('https://api.flipsidecrypto.com/api/v2/queries/284600cc-7b4c-4d47-ab50-dbdd528daf93/data/latest')\n",
    "meteors_rarity_merge = pd.merge(meteors, meteors_rarity[['TOKEN_ID', 'RARITY']], on='TOKEN_ID')\n",
    "\n",
    "meteors_rarity_merge = meteors_rarity_merge[['BLOCK_TIMESTAMP', 'RARITY', 'NFT_LUNA_PRICE', 'NFT_UST_PRICE_AT_PURCHASE']]\n",
    "meteors_rarity_merge['BLOCK_TIMESTAMP'] = pd.to_datetime(meteors_rarity_merge['BLOCK_TIMESTAMP'])\n",
    "meteors_rarity_merge.set_index('BLOCK_TIMESTAMP', inplace = True)\n",
    "meteors_rarity_merge.index = meteors_rarity_merge.index.round('D')\n",
    "\n",
    "total_df = meteors_rarity_merge.groupby(group_master).sum().rename(columns={'NFT_LUNA_PRICE':'TOTAL_LUNA', 'NFT_UST_PRICE_AT_PURCHASE':'TOTAL_UST'})\n",
    "average_df = meteors_rarity_merge.groupby(group_master).mean().rename(columns={'NFT_LUNA_PRICE':'AVERAGE_LUNA', 'NFT_UST_PRICE_AT_PURCHASE':'AVERAGE_UST'})\n",
    "tx_count_df = meteors_rarity_merge.groupby(group_master).count().rename(columns={'NFT_LUNA_PRICE':'TRANSACTION_COUNT'})\n",
    "tx_count_df = tx_count_df['TRANSACTION_COUNT']\n",
    "min_df = meteors_rarity_merge.groupby(group_master).min().rename(columns={'NFT_LUNA_PRICE':'MIN_LUNA', 'NFT_UST_PRICE_AT_PURCHASE':'MIN_UST'})\n",
    "max_df = meteors_rarity_merge.groupby(group_master).max().rename(columns={'NFT_LUNA_PRICE':'MAX_LUNA', 'NFT_UST_PRICE_AT_PURCHASE':'MAX_UST'})\n",
    "median_df = meteors_rarity_merge.groupby(group_master).median().rename(columns={'NFT_LUNA_PRICE':'MEDIAN_LUNA', 'NFT_UST_PRICE_AT_PURCHASE':'MEDIAN_UST'})\n",
    "cum_sum = meteors_rarity_merge.groupby(group_master).sum().rename(columns={'NFT_LUNA_PRICE':'TOTAL_LUNA_CUMULATIVE', 'NFT_UST_PRICE_AT_PURCHASE':'TOTAL_UST_CUMULATIVE'}).cumsum(axis=0)\n",
    "\n",
    "meteors_master = pd.concat([total_df, tx_count_df, average_df, min_df, max_df, median_df, cum_sum], axis = 1).reset_index().sort_values(by=group_master, ascending=False)\n",
    "meteors_master"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "nft_ust_price_df = []\n",
    "for nft in current_nft_dict:\n",
    "    #renames columns from Flipside dataframes according to NFT\n",
    "    nft_ust_price_df.append(current_nft_dict[nft][['BLOCK_TIMESTAMP', 'NFT_UST_PRICE_AT_PURCHASE']].rename({'NFT_UST_PRICE_AT_PURCHASE':f'{nft}_UST_PRICE'}, axis = 1))\n",
    "nft_ust_price_df = pd.concat(nft_ust_price_df)\n",
    "nft_ust_price_df['BLOCK_TIMESTAMP'] = pd.to_datetime(nft_ust_price_df['BLOCK_TIMESTAMP'])\n",
    "nft_ust_price_df.set_index('BLOCK_TIMESTAMP', inplace = True)\n",
    "nft_ust_price_df.index = nft_ust_price_df.index.round('D')\n",
    "nft_ust_price_df.groupby('BLOCK_TIMESTAMP').sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "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_TIMESTAMP</th>\n",
       "      <th>TOTAL_LUNA</th>\n",
       "      <th>TOTAL_UST</th>\n",
       "      <th>TRANSACTION_COUNT</th>\n",
       "      <th>AVERAGE_LUNA</th>\n",
       "      <th>AVERAGE_UST</th>\n",
       "      <th>MIN_LUNA</th>\n",
       "      <th>MIN_UST</th>\n",
       "      <th>MAX_LUNA</th>\n",
       "      <th>MAX_UST</th>\n",
       "      <th>MEDIAN_LUNA</th>\n",
       "      <th>MEDIAN_UST</th>\n",
       "      <th>TOTAL_LUNA_CUMULATIVE</th>\n",
       "      <th>TOTAL_UST_CUMULATIVE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2022-02-02</td>\n",
       "      <td>92.090</td>\n",
       "      <td>4788.265028</td>\n",
       "      <td>5</td>\n",
       "      <td>18.418000</td>\n",
       "      <td>957.653006</td>\n",
       "      <td>6.99</td>\n",
       "      <td>361.763566</td>\n",
       "      <td>40.00</td>\n",
       "      <td>2085.309165</td>\n",
       "      <td>12.600</td>\n",
       "      <td>655.725608</td>\n",
       "      <td>6494.413</td>\n",
       "      <td>438693.687517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2022-02-01</td>\n",
       "      <td>712.240</td>\n",
       "      <td>37129.060703</td>\n",
       "      <td>34</td>\n",
       "      <td>20.948235</td>\n",
       "      <td>1092.031197</td>\n",
       "      <td>5.50</td>\n",
       "      <td>275.882123</td>\n",
       "      <td>200.00</td>\n",
       "      <td>10343.446056</td>\n",
       "      <td>6.150</td>\n",
       "      <td>322.005397</td>\n",
       "      <td>6402.323</td>\n",
       "      <td>433905.422489</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2022-01-31</td>\n",
       "      <td>489.150</td>\n",
       "      <td>23229.697428</td>\n",
       "      <td>15</td>\n",
       "      <td>32.610000</td>\n",
       "      <td>1548.646495</td>\n",
       "      <td>5.00</td>\n",
       "      <td>246.079980</td>\n",
       "      <td>170.00</td>\n",
       "      <td>8495.527716</td>\n",
       "      <td>6.000</td>\n",
       "      <td>280.966968</td>\n",
       "      <td>5690.083</td>\n",
       "      <td>396776.361786</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2022-01-30</td>\n",
       "      <td>127.840</td>\n",
       "      <td>6628.401073</td>\n",
       "      <td>19</td>\n",
       "      <td>6.728421</td>\n",
       "      <td>348.863214</td>\n",
       "      <td>5.25</td>\n",
       "      <td>274.830214</td>\n",
       "      <td>12.50</td>\n",
       "      <td>642.477332</td>\n",
       "      <td>5.750</td>\n",
       "      <td>291.799528</td>\n",
       "      <td>5200.933</td>\n",
       "      <td>373546.664359</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2022-01-29</td>\n",
       "      <td>260.650</td>\n",
       "      <td>12911.684152</td>\n",
       "      <td>14</td>\n",
       "      <td>18.617857</td>\n",
       "      <td>922.263154</td>\n",
       "      <td>5.25</td>\n",
       "      <td>272.159499</td>\n",
       "      <td>85.00</td>\n",
       "      <td>4137.476585</td>\n",
       "      <td>6.150</td>\n",
       "      <td>318.562213</td>\n",
       "      <td>5073.093</td>\n",
       "      <td>366918.263286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2022-01-28</td>\n",
       "      <td>733.815</td>\n",
       "      <td>38260.209880</td>\n",
       "      <td>12</td>\n",
       "      <td>61.151250</td>\n",
       "      <td>3188.350823</td>\n",
       "      <td>5.90</td>\n",
       "      <td>322.455030</td>\n",
       "      <td>400.00</td>\n",
       "      <td>20146.851333</td>\n",
       "      <td>11.750</td>\n",
       "      <td>672.324282</td>\n",
       "      <td>4812.443</td>\n",
       "      <td>354006.579134</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2022-01-27</td>\n",
       "      <td>155.080</td>\n",
       "      <td>9236.058513</td>\n",
       "      <td>28</td>\n",
       "      <td>5.538571</td>\n",
       "      <td>329.859233</td>\n",
       "      <td>4.00</td>\n",
       "      <td>251.464809</td>\n",
       "      <td>11.00</td>\n",
       "      <td>639.395557</td>\n",
       "      <td>4.970</td>\n",
       "      <td>289.963305</td>\n",
       "      <td>4078.628</td>\n",
       "      <td>315746.369254</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2022-01-26</td>\n",
       "      <td>427.150</td>\n",
       "      <td>26932.261334</td>\n",
       "      <td>11</td>\n",
       "      <td>38.831818</td>\n",
       "      <td>2448.387394</td>\n",
       "      <td>4.00</td>\n",
       "      <td>255.464921</td>\n",
       "      <td>345.00</td>\n",
       "      <td>21737.353963</td>\n",
       "      <td>8.300</td>\n",
       "      <td>522.956632</td>\n",
       "      <td>3923.548</td>\n",
       "      <td>306510.310741</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2022-01-25</td>\n",
       "      <td>81.630</td>\n",
       "      <td>5379.386262</td>\n",
       "      <td>7</td>\n",
       "      <td>11.661429</td>\n",
       "      <td>768.483752</td>\n",
       "      <td>5.00</td>\n",
       "      <td>305.072592</td>\n",
       "      <td>50.00</td>\n",
       "      <td>3317.746311</td>\n",
       "      <td>5.380</td>\n",
       "      <td>353.822734</td>\n",
       "      <td>3496.398</td>\n",
       "      <td>279578.049407</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2022-01-24</td>\n",
       "      <td>84.510</td>\n",
       "      <td>5691.292012</td>\n",
       "      <td>11</td>\n",
       "      <td>7.682727</td>\n",
       "      <td>517.390183</td>\n",
       "      <td>5.00</td>\n",
       "      <td>333.725184</td>\n",
       "      <td>13.00</td>\n",
       "      <td>893.414753</td>\n",
       "      <td>6.120</td>\n",
       "      <td>412.997449</td>\n",
       "      <td>3414.768</td>\n",
       "      <td>274198.663146</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2022-01-23</td>\n",
       "      <td>22.200</td>\n",
       "      <td>1430.327617</td>\n",
       "      <td>4</td>\n",
       "      <td>5.550000</td>\n",
       "      <td>357.581904</td>\n",
       "      <td>5.00</td>\n",
       "      <td>306.146308</td>\n",
       "      <td>6.20</td>\n",
       "      <td>427.673237</td>\n",
       "      <td>5.500</td>\n",
       "      <td>348.254036</td>\n",
       "      <td>3330.258</td>\n",
       "      <td>268507.371134</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2022-01-22</td>\n",
       "      <td>148.270</td>\n",
       "      <td>10042.729723</td>\n",
       "      <td>7</td>\n",
       "      <td>21.181429</td>\n",
       "      <td>1434.675675</td>\n",
       "      <td>5.75</td>\n",
       "      <td>373.842446</td>\n",
       "      <td>68.50</td>\n",
       "      <td>4400.484951</td>\n",
       "      <td>9.500</td>\n",
       "      <td>613.585347</td>\n",
       "      <td>3308.058</td>\n",
       "      <td>267077.043517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2022-01-21</td>\n",
       "      <td>443.559</td>\n",
       "      <td>36511.272285</td>\n",
       "      <td>25</td>\n",
       "      <td>17.742360</td>\n",
       "      <td>1460.450891</td>\n",
       "      <td>5.75</td>\n",
       "      <td>463.603108</td>\n",
       "      <td>179.99</td>\n",
       "      <td>14799.670281</td>\n",
       "      <td>6.850</td>\n",
       "      <td>575.042459</td>\n",
       "      <td>3159.788</td>\n",
       "      <td>257034.313794</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2022-01-20</td>\n",
       "      <td>279.410</td>\n",
       "      <td>22553.227596</td>\n",
       "      <td>23</td>\n",
       "      <td>12.148261</td>\n",
       "      <td>980.575113</td>\n",
       "      <td>5.00</td>\n",
       "      <td>411.996333</td>\n",
       "      <td>43.50</td>\n",
       "      <td>3470.857058</td>\n",
       "      <td>7.900</td>\n",
       "      <td>632.632729</td>\n",
       "      <td>2716.229</td>\n",
       "      <td>220523.041509</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2022-01-19</td>\n",
       "      <td>209.470</td>\n",
       "      <td>16550.526990</td>\n",
       "      <td>12</td>\n",
       "      <td>17.455833</td>\n",
       "      <td>1379.210582</td>\n",
       "      <td>6.00</td>\n",
       "      <td>464.984737</td>\n",
       "      <td>59.00</td>\n",
       "      <td>4637.947454</td>\n",
       "      <td>10.680</td>\n",
       "      <td>856.121110</td>\n",
       "      <td>2436.819</td>\n",
       "      <td>197969.813913</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2022-01-18</td>\n",
       "      <td>64.040</td>\n",
       "      <td>4925.493857</td>\n",
       "      <td>6</td>\n",
       "      <td>10.673333</td>\n",
       "      <td>820.915643</td>\n",
       "      <td>5.69</td>\n",
       "      <td>436.057721</td>\n",
       "      <td>29.85</td>\n",
       "      <td>2250.694432</td>\n",
       "      <td>6.500</td>\n",
       "      <td>508.371475</td>\n",
       "      <td>2227.349</td>\n",
       "      <td>181419.286923</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-01-17</td>\n",
       "      <td>214.950</td>\n",
       "      <td>18352.647624</td>\n",
       "      <td>21</td>\n",
       "      <td>10.235714</td>\n",
       "      <td>873.935601</td>\n",
       "      <td>6.05</td>\n",
       "      <td>520.471788</td>\n",
       "      <td>27.00</td>\n",
       "      <td>2331.975500</td>\n",
       "      <td>6.980</td>\n",
       "      <td>600.365399</td>\n",
       "      <td>2163.309</td>\n",
       "      <td>176493.793066</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-01-16</td>\n",
       "      <td>341.620</td>\n",
       "      <td>29269.461741</td>\n",
       "      <td>23</td>\n",
       "      <td>14.853043</td>\n",
       "      <td>1272.585293</td>\n",
       "      <td>5.50</td>\n",
       "      <td>475.316577</td>\n",
       "      <td>65.00</td>\n",
       "      <td>5589.982233</td>\n",
       "      <td>8.990</td>\n",
       "      <td>778.530098</td>\n",
       "      <td>1948.359</td>\n",
       "      <td>158141.145442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-01-15</td>\n",
       "      <td>806.620</td>\n",
       "      <td>65419.343476</td>\n",
       "      <td>79</td>\n",
       "      <td>10.210380</td>\n",
       "      <td>828.092955</td>\n",
       "      <td>1.50</td>\n",
       "      <td>121.650146</td>\n",
       "      <td>200.00</td>\n",
       "      <td>16185.586848</td>\n",
       "      <td>4.810</td>\n",
       "      <td>395.719579</td>\n",
       "      <td>1606.739</td>\n",
       "      <td>128871.683700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-01-14</td>\n",
       "      <td>163.330</td>\n",
       "      <td>13012.880410</td>\n",
       "      <td>17</td>\n",
       "      <td>9.607647</td>\n",
       "      <td>765.463554</td>\n",
       "      <td>3.30</td>\n",
       "      <td>267.067753</td>\n",
       "      <td>64.75</td>\n",
       "      <td>5160.075634</td>\n",
       "      <td>3.690</td>\n",
       "      <td>295.567105</td>\n",
       "      <td>800.119</td>\n",
       "      <td>63452.340224</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-01-13</td>\n",
       "      <td>636.789</td>\n",
       "      <td>50439.459815</td>\n",
       "      <td>26</td>\n",
       "      <td>24.491885</td>\n",
       "      <td>1939.979224</td>\n",
       "      <td>2.99</td>\n",
       "      <td>236.251173</td>\n",
       "      <td>450.00</td>\n",
       "      <td>35250.205446</td>\n",
       "      <td>3.695</td>\n",
       "      <td>304.769337</td>\n",
       "      <td>636.789</td>\n",
       "      <td>50439.459815</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   BLOCK_TIMESTAMP  TOTAL_LUNA     TOTAL_UST  TRANSACTION_COUNT  AVERAGE_LUNA  \\\n",
       "20      2022-02-02      92.090   4788.265028                  5     18.418000   \n",
       "19      2022-02-01     712.240  37129.060703                 34     20.948235   \n",
       "18      2022-01-31     489.150  23229.697428                 15     32.610000   \n",
       "17      2022-01-30     127.840   6628.401073                 19      6.728421   \n",
       "16      2022-01-29     260.650  12911.684152                 14     18.617857   \n",
       "15      2022-01-28     733.815  38260.209880                 12     61.151250   \n",
       "14      2022-01-27     155.080   9236.058513                 28      5.538571   \n",
       "13      2022-01-26     427.150  26932.261334                 11     38.831818   \n",
       "12      2022-01-25      81.630   5379.386262                  7     11.661429   \n",
       "11      2022-01-24      84.510   5691.292012                 11      7.682727   \n",
       "10      2022-01-23      22.200   1430.327617                  4      5.550000   \n",
       "9       2022-01-22     148.270  10042.729723                  7     21.181429   \n",
       "8       2022-01-21     443.559  36511.272285                 25     17.742360   \n",
       "7       2022-01-20     279.410  22553.227596                 23     12.148261   \n",
       "6       2022-01-19     209.470  16550.526990                 12     17.455833   \n",
       "5       2022-01-18      64.040   4925.493857                  6     10.673333   \n",
       "4       2022-01-17     214.950  18352.647624                 21     10.235714   \n",
       "3       2022-01-16     341.620  29269.461741                 23     14.853043   \n",
       "2       2022-01-15     806.620  65419.343476                 79     10.210380   \n",
       "1       2022-01-14     163.330  13012.880410                 17      9.607647   \n",
       "0       2022-01-13     636.789  50439.459815                 26     24.491885   \n",
       "\n",
       "    AVERAGE_UST  MIN_LUNA     MIN_UST  MAX_LUNA       MAX_UST  MEDIAN_LUNA  \\\n",
       "20   957.653006      6.99  361.763566     40.00   2085.309165       12.600   \n",
       "19  1092.031197      5.50  275.882123    200.00  10343.446056        6.150   \n",
       "18  1548.646495      5.00  246.079980    170.00   8495.527716        6.000   \n",
       "17   348.863214      5.25  274.830214     12.50    642.477332        5.750   \n",
       "16   922.263154      5.25  272.159499     85.00   4137.476585        6.150   \n",
       "15  3188.350823      5.90  322.455030    400.00  20146.851333       11.750   \n",
       "14   329.859233      4.00  251.464809     11.00    639.395557        4.970   \n",
       "13  2448.387394      4.00  255.464921    345.00  21737.353963        8.300   \n",
       "12   768.483752      5.00  305.072592     50.00   3317.746311        5.380   \n",
       "11   517.390183      5.00  333.725184     13.00    893.414753        6.120   \n",
       "10   357.581904      5.00  306.146308      6.20    427.673237        5.500   \n",
       "9   1434.675675      5.75  373.842446     68.50   4400.484951        9.500   \n",
       "8   1460.450891      5.75  463.603108    179.99  14799.670281        6.850   \n",
       "7    980.575113      5.00  411.996333     43.50   3470.857058        7.900   \n",
       "6   1379.210582      6.00  464.984737     59.00   4637.947454       10.680   \n",
       "5    820.915643      5.69  436.057721     29.85   2250.694432        6.500   \n",
       "4    873.935601      6.05  520.471788     27.00   2331.975500        6.980   \n",
       "3   1272.585293      5.50  475.316577     65.00   5589.982233        8.990   \n",
       "2    828.092955      1.50  121.650146    200.00  16185.586848        4.810   \n",
       "1    765.463554      3.30  267.067753     64.75   5160.075634        3.690   \n",
       "0   1939.979224      2.99  236.251173    450.00  35250.205446        3.695   \n",
       "\n",
       "    MEDIAN_UST  TOTAL_LUNA_CUMULATIVE  TOTAL_UST_CUMULATIVE  \n",
       "20  655.725608               6494.413         438693.687517  \n",
       "19  322.005397               6402.323         433905.422489  \n",
       "18  280.966968               5690.083         396776.361786  \n",
       "17  291.799528               5200.933         373546.664359  \n",
       "16  318.562213               5073.093         366918.263286  \n",
       "15  672.324282               4812.443         354006.579134  \n",
       "14  289.963305               4078.628         315746.369254  \n",
       "13  522.956632               3923.548         306510.310741  \n",
       "12  353.822734               3496.398         279578.049407  \n",
       "11  412.997449               3414.768         274198.663146  \n",
       "10  348.254036               3330.258         268507.371134  \n",
       "9   613.585347               3308.058         267077.043517  \n",
       "8   575.042459               3159.788         257034.313794  \n",
       "7   632.632729               2716.229         220523.041509  \n",
       "6   856.121110               2436.819         197969.813913  \n",
       "5   508.371475               2227.349         181419.286923  \n",
       "4   600.365399               2163.309         176493.793066  \n",
       "3   778.530098               1948.359         158141.145442  \n",
       "2   395.719579               1606.739         128871.683700  \n",
       "1   295.567105                800.119          63452.340224  \n",
       "0   304.769337                636.789          50439.459815  "
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nested_egg = pd.read_json('https://api.flipsidecrypto.com/api/v2/queries/290a91fb-8186-486d-992e-985839352809/data/latest')\n",
    "nested_rarity = pd.read_csv('http://165.22.125.123/nested_egg_nfts.csv')\n",
    "nested_rarity_merge = pd.merge(nested_egg, nested_rarity[['token_id', 'traits']], left_on='TOKEN_ID', right_on='token_id', how='inner')\n",
    "nested_rarity_merge['RARITY'] = nested_rarity_merge['traits'].apply(lambda x: ast.literal_eval(x).get('Rarity'))\n",
    "nested_rarity_merge.pop('traits')\n",
    "\n",
    "group_master = ['BLOCK_TIMESTAMP']\n",
    "\n",
    "nested_rarity_merge = nested_rarity_merge[['BLOCK_TIMESTAMP', 'NFT_LUNA_PRICE', 'NFT_UST_PRICE_AT_PURCHASE']]\n",
    "nested_rarity_merge['BLOCK_TIMESTAMP'] = pd.to_datetime(nested_rarity_merge['BLOCK_TIMESTAMP'])\n",
    "nested_rarity_merge.set_index('BLOCK_TIMESTAMP', inplace = True)\n",
    "nested_rarity_merge.index = nested_rarity_merge.index.round('D')\n",
    "\n",
    "total_df = nested_rarity_merge.groupby(group_master).sum().rename(columns={'NFT_LUNA_PRICE':'TOTAL_LUNA', 'NFT_UST_PRICE_AT_PURCHASE':'TOTAL_UST'})\n",
    "average_df = nested_rarity_merge.groupby(group_master).mean().rename(columns={'NFT_LUNA_PRICE':'AVERAGE_LUNA', 'NFT_UST_PRICE_AT_PURCHASE':'AVERAGE_UST'})\n",
    "tx_count_df = nested_rarity_merge.groupby(group_master).count().rename(columns={'NFT_LUNA_PRICE':'TRANSACTION_COUNT'})\n",
    "tx_count_df = tx_count_df['TRANSACTION_COUNT']\n",
    "min_df = nested_rarity_merge.groupby(group_master).min().rename(columns={'NFT_LUNA_PRICE':'MIN_LUNA', 'NFT_UST_PRICE_AT_PURCHASE':'MIN_UST'})\n",
    "max_df = nested_rarity_merge.groupby(group_master).max().rename(columns={'NFT_LUNA_PRICE':'MAX_LUNA', 'NFT_UST_PRICE_AT_PURCHASE':'MAX_UST'})\n",
    "median_df = nested_rarity_merge.groupby(group_master).median().rename(columns={'NFT_LUNA_PRICE':'MEDIAN_LUNA', 'NFT_UST_PRICE_AT_PURCHASE':'MEDIAN_UST'})\n",
    "cum_sum = nested_rarity_merge.groupby(group_master).sum().rename(columns={'NFT_LUNA_PRICE':'TOTAL_LUNA_CUMULATIVE', 'NFT_UST_PRICE_AT_PURCHASE':'TOTAL_UST_CUMULATIVE'}).cumsum(axis=0)\n",
    "\n",
    "nested_master = pd.concat([total_df, tx_count_df, average_df, min_df, max_df, median_df, cum_sum], axis = 1).reset_index().sort_values(by=group_master, ascending=False)\n",
    "nested_master"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "nested_egg = pd.read_json('https://api.flipsidecrypto.com/api/v2/queries/290a91fb-8186-486d-992e-985839352809/data/latest')\n",
    "loot = pd.read_json('https://api.flipsidecrypto.com/api/v2/queries/2208cc91-e624-4b40-b666-f65c8b6405f8/data/latest')\n",
    "eggs = pd.read_json('https://api.flipsidecrypto.com/api/v2/queries/ee319c5f-4f03-41b5-b607-99f306fe1e2f/data/latest')\n",
    "meteor_dust = pd.read_json('https://api.flipsidecrypto.com/api/v2/queries/8eebec3c-db0e-4051-a5c2-50a5deb1070b/data/latest')\n",
    "meteors = pd.read_json('https://api.flipsidecrypto.com/api/v2/queries/b9778a1d-9b63-4ba5-a48f-7b28569b5603/data/latest')\n",
    "#IT IS ABSOLUTELY CRUCIAL THAT YOU DO NOT CHANGE THE ORIGINAL NAMES OF THE DATAFRAME COLUMNS ON VELOCITY\n",
    "\n",
    "#MUST STORE IN A DICTIONARY \n",
    "current_nft_dict = {'nested_egg':nested_egg, 'loot':loot, 'eggs':eggs, 'meteor_dust':meteor_dust, 'meteors':meteors}\n",
    "\n",
    "meteor_rarity = pd.read_json('https://api.flipsidecrypto.com/api/v2/queries/5d7b0f96-a537-46e5-9b3c-d5145072279f/data/latest')\n",
    "meteor_rarity_merge = pd.merge(meteors, meteor_rarity[['TOKEN_ID', 'RARITY']], on='TOKEN_ID')\n",
    "egg_rarity = pd.read_json('https://api.flipsidecrypto.com/api/v2/queries/284600cc-7b4c-4d47-ab50-dbdd528daf93/data/latest')\n",
    "egg_rarity_merge = pd.merge(eggs, egg_rarity[['TOKEN_ID', 'RARITY']], on='TOKEN_ID')\n",
    "dust_rarity = pd.read_json('https://api.flipsidecrypto.com/api/v2/queries/f4b78c34-07ec-4f5e-8099-7e6db2db24a9/data/latest')\n",
    "dust_rarity_merge = pd.merge(meteor_dust, dust_rarity[['TOKEN_ID', 'RARITY']], on='TOKEN_ID')\n",
    "\n",
    "nested_rarity = pd.read_csv('http://165.22.125.123/nested_egg_nfts.csv')\n",
    "nested_rarity_merge = pd.merge(nested_egg, nested_rarity[['token_id', 'traits']], left_on='TOKEN_ID', right_on='token_id', how='inner')\n",
    "nested_rarity_merge['RARITY'] = nested_rarity_merge['traits'].apply(lambda x: ast.literal_eval(x).get('Rarity'))\n",
    "nested_rarity_merge.pop('traits')\n",
    "nested_rarity_merge.pop('token_id')\n",
    "\n",
    "loot = pd.read_json('https://api.flipsidecrypto.com/api/v2/queries/2208cc91-e624-4b40-b666-f65c8b6405f8/data/latest')\n",
    "loot_ROLE = pd.read_csv('http://165.22.125.123/loot_nfts.csv')\n",
    "loot_ROLE_merge = pd.merge(loot, loot_ROLE[['token_id', 'traits']], left_on='TOKEN_ID', right_on='token_id', how='inner')\n",
    "loot_ROLE_merge['ROLE'] = loot_ROLE_merge['traits'].apply(lambda x: ast.literal_eval(x).get('Role'))\n",
    "loot_ROLE_merge['FACTION'] = loot_ROLE_merge['traits'].apply(lambda x: ast.literal_eval(x).get('Faction'))\n",
    "loot_ROLE_merge.pop('traits')\n",
    "loot_ROLE_merge.pop('token_id')\n",
    "loot_ROLE_merge['NFT_TYPE'] = loot_ROLE_merge['NFT_TYPE'] + (' - ') + loot_ROLE_merge['ROLE']\n",
    "loot_ROLE_merge.pop('ROLE')\n",
    "loot_ROLE_merge = loot_ROLE_merge.rename(columns = {'FACTION':'RARITY'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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_TIMESTAMP</th>\n",
       "      <th>HOUR</th>\n",
       "      <th>LUNA_UST_PRICE</th>\n",
       "      <th>NFT_LUNA_PRICE</th>\n",
       "      <th>NFT_TYPE</th>\n",
       "      <th>NFT_UST_PRICE_AT_PURCHASE</th>\n",
       "      <th>TOKEN_ID</th>\n",
       "      <th>TX_ID</th>\n",
       "      <th>RARITY</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-02-02 15:55:12.555</td>\n",
       "      <td>2022-02-02 15:00:00.000</td>\n",
       "      <td>50.634081</td>\n",
       "      <td>2.550</td>\n",
       "      <td>Levana Meteors</td>\n",
       "      <td>129.116906</td>\n",
       "      <td>16693</td>\n",
       "      <td>78F747E773355733A900F9D412AC1F2F8EBBC57F9A04D7...</td>\n",
       "      <td>Common</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-02-02 13:44:05.019</td>\n",
       "      <td>2022-02-02 13:00:00.000</td>\n",
       "      <td>52.998104</td>\n",
       "      <td>2.599</td>\n",
       "      <td>Levana Meteors</td>\n",
       "      <td>137.742073</td>\n",
       "      <td>22495</td>\n",
       "      <td>D961FBCEBC1BD2C6DAC9F5F99C50F22BB411A05643D811...</td>\n",
       "      <td>Common</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-11-16 23:37:30.131</td>\n",
       "      <td>2021-11-16 23:00:00.000</td>\n",
       "      <td>43.645471</td>\n",
       "      <td>0.350</td>\n",
       "      <td>Levana Meteors</td>\n",
       "      <td>15.275915</td>\n",
       "      <td>22495</td>\n",
       "      <td>FFF1AF31732B69B282313ED75D486DBB551BDFE337A689...</td>\n",
       "      <td>Common</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-02-02 10:10:30.200</td>\n",
       "      <td>2022-02-02 10:00:00.000</td>\n",
       "      <td>52.384832</td>\n",
       "      <td>5.000</td>\n",
       "      <td>Levana Meteors</td>\n",
       "      <td>261.924162</td>\n",
       "      <td>4951</td>\n",
       "      <td>D31B9CE8FC96369CEE50A29757148CBF56BAC8118A6893...</td>\n",
       "      <td>Rare</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-02-02 08:24:11.382</td>\n",
       "      <td>2022-02-02 08:00:00.000</td>\n",
       "      <td>51.840996</td>\n",
       "      <td>2.770</td>\n",
       "      <td>Levana Meteors</td>\n",
       "      <td>143.599560</td>\n",
       "      <td>32761</td>\n",
       "      <td>F340C45D4B3A34BF3B8B54CA47AAEA8C35F924E41F8BCB...</td>\n",
       "      <td>Common</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>678</th>\n",
       "      <td>2022-01-12 13:24:42.820</td>\n",
       "      <td>2022-01-12 13:00:00.000</td>\n",
       "      <td>78.392752</td>\n",
       "      <td>7.000</td>\n",
       "      <td>Levana Loot - Member</td>\n",
       "      <td>548.749264</td>\n",
       "      <td>105</td>\n",
       "      <td>948780CADD2631B58095E5F22EBDB9BFF05BFCABAB85BF...</td>\n",
       "      <td>Council</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>679</th>\n",
       "      <td>2022-01-12 12:25:56.803</td>\n",
       "      <td>2022-01-12 12:00:00.000</td>\n",
       "      <td>78.293709</td>\n",
       "      <td>2.500</td>\n",
       "      <td>Levana Loot - Member</td>\n",
       "      <td>195.734272</td>\n",
       "      <td>17</td>\n",
       "      <td>E2F7A7D8AC88F0D99CE630C618524C2E5FDA1D5750DDDD...</td>\n",
       "      <td>Free Martians</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>680</th>\n",
       "      <td>2022-01-12 12:45:08.249</td>\n",
       "      <td>2022-01-12 12:00:00.000</td>\n",
       "      <td>78.293709</td>\n",
       "      <td>2.500</td>\n",
       "      <td>Levana Loot - Member</td>\n",
       "      <td>195.734272</td>\n",
       "      <td>129</td>\n",
       "      <td>8D108BCECDD68B4D5403A03262514CF893387B92F2B970...</td>\n",
       "      <td>Council</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>681</th>\n",
       "      <td>2022-01-12 12:29:40.436</td>\n",
       "      <td>2022-01-12 12:00:00.000</td>\n",
       "      <td>78.293709</td>\n",
       "      <td>2.500</td>\n",
       "      <td>Levana Loot - Member</td>\n",
       "      <td>195.734272</td>\n",
       "      <td>62</td>\n",
       "      <td>939D8F43C21F1A4E846C829E81D01B35CAD4F46B18242D...</td>\n",
       "      <td>Free Martians</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>682</th>\n",
       "      <td>2022-01-12 12:28:57.408</td>\n",
       "      <td>2022-01-12 12:00:00.000</td>\n",
       "      <td>78.293709</td>\n",
       "      <td>2.500</td>\n",
       "      <td>Levana Loot - Member</td>\n",
       "      <td>195.734272</td>\n",
       "      <td>20</td>\n",
       "      <td>34DA05F39CFECBDB2584F7B43A04E261FDECF4C6502447...</td>\n",
       "      <td>Council</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>14521 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             BLOCK_TIMESTAMP                     HOUR  LUNA_UST_PRICE  \\\n",
       "0    2022-02-02 15:55:12.555  2022-02-02 15:00:00.000       50.634081   \n",
       "1    2022-02-02 13:44:05.019  2022-02-02 13:00:00.000       52.998104   \n",
       "2    2021-11-16 23:37:30.131  2021-11-16 23:00:00.000       43.645471   \n",
       "3    2022-02-02 10:10:30.200  2022-02-02 10:00:00.000       52.384832   \n",
       "4    2022-02-02 08:24:11.382  2022-02-02 08:00:00.000       51.840996   \n",
       "..                       ...                      ...             ...   \n",
       "678  2022-01-12 13:24:42.820  2022-01-12 13:00:00.000       78.392752   \n",
       "679  2022-01-12 12:25:56.803  2022-01-12 12:00:00.000       78.293709   \n",
       "680  2022-01-12 12:45:08.249  2022-01-12 12:00:00.000       78.293709   \n",
       "681  2022-01-12 12:29:40.436  2022-01-12 12:00:00.000       78.293709   \n",
       "682  2022-01-12 12:28:57.408  2022-01-12 12:00:00.000       78.293709   \n",
       "\n",
       "     NFT_LUNA_PRICE              NFT_TYPE  NFT_UST_PRICE_AT_PURCHASE  \\\n",
       "0             2.550        Levana Meteors                 129.116906   \n",
       "1             2.599        Levana Meteors                 137.742073   \n",
       "2             0.350        Levana Meteors                  15.275915   \n",
       "3             5.000        Levana Meteors                 261.924162   \n",
       "4             2.770        Levana Meteors                 143.599560   \n",
       "..              ...                   ...                        ...   \n",
       "678           7.000  Levana Loot - Member                 548.749264   \n",
       "679           2.500  Levana Loot - Member                 195.734272   \n",
       "680           2.500  Levana Loot - Member                 195.734272   \n",
       "681           2.500  Levana Loot - Member                 195.734272   \n",
       "682           2.500  Levana Loot - Member                 195.734272   \n",
       "\n",
       "     TOKEN_ID                                              TX_ID  \\\n",
       "0       16693  78F747E773355733A900F9D412AC1F2F8EBBC57F9A04D7...   \n",
       "1       22495  D961FBCEBC1BD2C6DAC9F5F99C50F22BB411A05643D811...   \n",
       "2       22495  FFF1AF31732B69B282313ED75D486DBB551BDFE337A689...   \n",
       "3        4951  D31B9CE8FC96369CEE50A29757148CBF56BAC8118A6893...   \n",
       "4       32761  F340C45D4B3A34BF3B8B54CA47AAEA8C35F924E41F8BCB...   \n",
       "..        ...                                                ...   \n",
       "678       105  948780CADD2631B58095E5F22EBDB9BFF05BFCABAB85BF...   \n",
       "679        17  E2F7A7D8AC88F0D99CE630C618524C2E5FDA1D5750DDDD...   \n",
       "680       129  8D108BCECDD68B4D5403A03262514CF893387B92F2B970...   \n",
       "681        62  939D8F43C21F1A4E846C829E81D01B35CAD4F46B18242D...   \n",
       "682        20  34DA05F39CFECBDB2584F7B43A04E261FDECF4C6502447...   \n",
       "\n",
       "            RARITY  \n",
       "0           Common  \n",
       "1           Common  \n",
       "2           Common  \n",
       "3             Rare  \n",
       "4           Common  \n",
       "..             ...  \n",
       "678        Council  \n",
       "679  Free Martians  \n",
       "680        Council  \n",
       "681  Free Martians  \n",
       "682        Council  \n",
       "\n",
       "[14521 rows x 9 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "master_df = pd.concat([meteor_rarity_merge, dust_rarity_merge, egg_rarity_merge, nested_rarity_merge, loot_ROLE_merge])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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_TIMESTAMP</th>\n",
       "      <th>HOUR</th>\n",
       "      <th>LUNA_UST_PRICE</th>\n",
       "      <th>NFT_LUNA_PRICE</th>\n",
       "      <th>NFT_TYPE</th>\n",
       "      <th>NFT_UST_PRICE_AT_PURCHASE</th>\n",
       "      <th>TOKEN_ID</th>\n",
       "      <th>TX_ID</th>\n",
       "      <th>RARITY</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-02-02 13:47:43.804</td>\n",
       "      <td>2022-02-02 13:00:00.000</td>\n",
       "      <td>52.998104</td>\n",
       "      <td>2.10</td>\n",
       "      <td>Levana Loot - Member</td>\n",
       "      <td>111.296019</td>\n",
       "      <td>2606</td>\n",
       "      <td>7AFBA1DB4F744E71619C06160785B81A2C27512A14BB45...</td>\n",
       "      <td>Free Martians</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-01-16 03:59:31.121</td>\n",
       "      <td>2022-01-16 03:00:00.000</td>\n",
       "      <td>85.999727</td>\n",
       "      <td>1.80</td>\n",
       "      <td>Levana Loot - Member</td>\n",
       "      <td>154.799508</td>\n",
       "      <td>2606</td>\n",
       "      <td>2EB430054F2BEEBD21C01F3273C5DC8C50A2C184605E23...</td>\n",
       "      <td>Free Martians</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-02-02 13:26:35.924</td>\n",
       "      <td>2022-02-02 13:00:00.000</td>\n",
       "      <td>52.998104</td>\n",
       "      <td>2.38</td>\n",
       "      <td>Levana Loot - Member</td>\n",
       "      <td>126.135488</td>\n",
       "      <td>2072</td>\n",
       "      <td>F12B5039B3C9FD872863BBDBC88BF58DA41BAAB7036898...</td>\n",
       "      <td>Free Martians</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-02-02 13:20:20.613</td>\n",
       "      <td>2022-02-02 13:00:00.000</td>\n",
       "      <td>52.998104</td>\n",
       "      <td>2.40</td>\n",
       "      <td>Levana Loot - Member</td>\n",
       "      <td>127.195451</td>\n",
       "      <td>682</td>\n",
       "      <td>35197B4BD2BFF689E240567D2B5ADCE99606B14EB69906...</td>\n",
       "      <td>Free Martians</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-02-02 11:09:18.432</td>\n",
       "      <td>2022-02-02 11:00:00.000</td>\n",
       "      <td>52.766880</td>\n",
       "      <td>1.93</td>\n",
       "      <td>Levana Loot - Member</td>\n",
       "      <td>101.840079</td>\n",
       "      <td>5222</td>\n",
       "      <td>816D270CF27C8B42C4EE414B2932018990F8D53357E747...</td>\n",
       "      <td>Terrans</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>678</th>\n",
       "      <td>2022-01-12 13:24:42.820</td>\n",
       "      <td>2022-01-12 13:00:00.000</td>\n",
       "      <td>78.392752</td>\n",
       "      <td>7.00</td>\n",
       "      <td>Levana Loot - Member</td>\n",
       "      <td>548.749264</td>\n",
       "      <td>105</td>\n",
       "      <td>948780CADD2631B58095E5F22EBDB9BFF05BFCABAB85BF...</td>\n",
       "      <td>Council</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>679</th>\n",
       "      <td>2022-01-12 12:25:56.803</td>\n",
       "      <td>2022-01-12 12:00:00.000</td>\n",
       "      <td>78.293709</td>\n",
       "      <td>2.50</td>\n",
       "      <td>Levana Loot - Member</td>\n",
       "      <td>195.734272</td>\n",
       "      <td>17</td>\n",
       "      <td>E2F7A7D8AC88F0D99CE630C618524C2E5FDA1D5750DDDD...</td>\n",
       "      <td>Free Martians</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>680</th>\n",
       "      <td>2022-01-12 12:45:08.249</td>\n",
       "      <td>2022-01-12 12:00:00.000</td>\n",
       "      <td>78.293709</td>\n",
       "      <td>2.50</td>\n",
       "      <td>Levana Loot - Member</td>\n",
       "      <td>195.734272</td>\n",
       "      <td>129</td>\n",
       "      <td>8D108BCECDD68B4D5403A03262514CF893387B92F2B970...</td>\n",
       "      <td>Council</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>681</th>\n",
       "      <td>2022-01-12 12:29:40.436</td>\n",
       "      <td>2022-01-12 12:00:00.000</td>\n",
       "      <td>78.293709</td>\n",
       "      <td>2.50</td>\n",
       "      <td>Levana Loot - Member</td>\n",
       "      <td>195.734272</td>\n",
       "      <td>62</td>\n",
       "      <td>939D8F43C21F1A4E846C829E81D01B35CAD4F46B18242D...</td>\n",
       "      <td>Free Martians</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>682</th>\n",
       "      <td>2022-01-12 12:28:57.408</td>\n",
       "      <td>2022-01-12 12:00:00.000</td>\n",
       "      <td>78.293709</td>\n",
       "      <td>2.50</td>\n",
       "      <td>Levana Loot - Member</td>\n",
       "      <td>195.734272</td>\n",
       "      <td>20</td>\n",
       "      <td>34DA05F39CFECBDB2584F7B43A04E261FDECF4C6502447...</td>\n",
       "      <td>Council</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>683 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             BLOCK_TIMESTAMP                     HOUR  LUNA_UST_PRICE  \\\n",
       "0    2022-02-02 13:47:43.804  2022-02-02 13:00:00.000       52.998104   \n",
       "1    2022-01-16 03:59:31.121  2022-01-16 03:00:00.000       85.999727   \n",
       "2    2022-02-02 13:26:35.924  2022-02-02 13:00:00.000       52.998104   \n",
       "3    2022-02-02 13:20:20.613  2022-02-02 13:00:00.000       52.998104   \n",
       "4    2022-02-02 11:09:18.432  2022-02-02 11:00:00.000       52.766880   \n",
       "..                       ...                      ...             ...   \n",
       "678  2022-01-12 13:24:42.820  2022-01-12 13:00:00.000       78.392752   \n",
       "679  2022-01-12 12:25:56.803  2022-01-12 12:00:00.000       78.293709   \n",
       "680  2022-01-12 12:45:08.249  2022-01-12 12:00:00.000       78.293709   \n",
       "681  2022-01-12 12:29:40.436  2022-01-12 12:00:00.000       78.293709   \n",
       "682  2022-01-12 12:28:57.408  2022-01-12 12:00:00.000       78.293709   \n",
       "\n",
       "     NFT_LUNA_PRICE              NFT_TYPE  NFT_UST_PRICE_AT_PURCHASE  \\\n",
       "0              2.10  Levana Loot - Member                 111.296019   \n",
       "1              1.80  Levana Loot - Member                 154.799508   \n",
       "2              2.38  Levana Loot - Member                 126.135488   \n",
       "3              2.40  Levana Loot - Member                 127.195451   \n",
       "4              1.93  Levana Loot - Member                 101.840079   \n",
       "..              ...                   ...                        ...   \n",
       "678            7.00  Levana Loot - Member                 548.749264   \n",
       "679            2.50  Levana Loot - Member                 195.734272   \n",
       "680            2.50  Levana Loot - Member                 195.734272   \n",
       "681            2.50  Levana Loot - Member                 195.734272   \n",
       "682            2.50  Levana Loot - Member                 195.734272   \n",
       "\n",
       "     TOKEN_ID                                              TX_ID  \\\n",
       "0        2606  7AFBA1DB4F744E71619C06160785B81A2C27512A14BB45...   \n",
       "1        2606  2EB430054F2BEEBD21C01F3273C5DC8C50A2C184605E23...   \n",
       "2        2072  F12B5039B3C9FD872863BBDBC88BF58DA41BAAB7036898...   \n",
       "3         682  35197B4BD2BFF689E240567D2B5ADCE99606B14EB69906...   \n",
       "4        5222  816D270CF27C8B42C4EE414B2932018990F8D53357E747...   \n",
       "..        ...                                                ...   \n",
       "678       105  948780CADD2631B58095E5F22EBDB9BFF05BFCABAB85BF...   \n",
       "679        17  E2F7A7D8AC88F0D99CE630C618524C2E5FDA1D5750DDDD...   \n",
       "680       129  8D108BCECDD68B4D5403A03262514CF893387B92F2B970...   \n",
       "681        62  939D8F43C21F1A4E846C829E81D01B35CAD4F46B18242D...   \n",
       "682        20  34DA05F39CFECBDB2584F7B43A04E261FDECF4C6502447...   \n",
       "\n",
       "            RARITY  \n",
       "0    Free Martians  \n",
       "1    Free Martians  \n",
       "2    Free Martians  \n",
       "3    Free Martians  \n",
       "4          Terrans  \n",
       "..             ...  \n",
       "678        Council  \n",
       "679  Free Martians  \n",
       "680        Council  \n",
       "681  Free Martians  \n",
       "682        Council  \n",
       "\n",
       "[683 rows x 9 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "2be5faf79681da6f2a61fdfdd5405d65d042280f7fba6178067603e3a2925119"
  },
  "kernelspec": {
   "display_name": "Python 3.10.0 64-bit",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.0"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}