NFT Bored Ape Analysis.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "66a72f97",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Libraries\n",
    "import pandas as pd\n",
    "from IPython.display import HTML\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1ff8f09b",
   "metadata": {},
   "source": [
    "# Questions I'm Trying to Answer\n",
    "- Overpaying for rarity\n",
    "- Who owns the most\n",
    "- Biggest winning trades and losing trades"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "422f010d",
   "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>transaction_hash</th>\n",
       "      <th>block_number</th>\n",
       "      <th>date</th>\n",
       "      <th>asset_id</th>\n",
       "      <th>seller</th>\n",
       "      <th>buyer</th>\n",
       "      <th>maker</th>\n",
       "      <th>taker</th>\n",
       "      <th>sale_price_eth</th>\n",
       "      <th>sale_price_usd</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0xb235f0321b0b50198399ec7f2bb759ef625f85673b4d...</td>\n",
       "      <td>14675897</td>\n",
       "      <td>2022-04-28</td>\n",
       "      <td>7438</td>\n",
       "      <td>0x2fdcca65899346af3a93a8daa6128bdbcb1ce3b3</td>\n",
       "      <td>0xcedf17dfafa947cd0e205fe2a3a183cf2fb3a0bc</td>\n",
       "      <td>0x2fdcca65899346af3a93a8daa6128bdbcb1ce3b3</td>\n",
       "      <td>0xcedf17dfafa947cd0e205fe2a3a183cf2fb3a0bc</td>\n",
       "      <td>153.200</td>\n",
       "      <td>442685.528567</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0x1cf83a120540049239c62de9aa6cbc42bae7494cdf02...</td>\n",
       "      <td>14675814</td>\n",
       "      <td>2022-04-28</td>\n",
       "      <td>1397</td>\n",
       "      <td>0xcedf17dfafa947cd0e205fe2a3a183cf2fb3a0bc</td>\n",
       "      <td>0x93d1f7636f56dd4498c778e212cbe3e2e15bcba9</td>\n",
       "      <td>0xcedf17dfafa947cd0e205fe2a3a183cf2fb3a0bc</td>\n",
       "      <td>0x93d1f7636f56dd4498c778e212cbe3e2e15bcba9</td>\n",
       "      <td>139.750</td>\n",
       "      <td>403820.513167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0xf9854d2f02c074f1a2043bc93c5e4e6e4e922e6908a8...</td>\n",
       "      <td>14675812</td>\n",
       "      <td>2022-04-28</td>\n",
       "      <td>5226</td>\n",
       "      <td>0x40c839b831c90173dc7fbce49a25274a4688ddd9</td>\n",
       "      <td>0xd30cad3f023888d5a81580fbb80f93102d69e9ef</td>\n",
       "      <td>0x40c839b831c90173dc7fbce49a25274a4688ddd9</td>\n",
       "      <td>0x00000000a50bb64b4bbeceb18715748dface08af</td>\n",
       "      <td>150.000</td>\n",
       "      <td>433438.833453</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0x38c03f0e17422b62f450262e2931a86efa201d3c22e9...</td>\n",
       "      <td>14675760</td>\n",
       "      <td>2022-04-28</td>\n",
       "      <td>6498</td>\n",
       "      <td>0x83e7fc69d7c99943afd9122ad0f8bb575e00597c</td>\n",
       "      <td>0x8dd47e819c53138aa18f8651d797e7969f34d1f1</td>\n",
       "      <td>0x83e7fc69d7c99943afd9122ad0f8bb575e00597c</td>\n",
       "      <td>0x8dd47e819c53138aa18f8651d797e7969f34d1f1</td>\n",
       "      <td>145.678</td>\n",
       "      <td>420950.015866</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0x85ec6a319062f3537420ba60342ff273bbad907bfd9d...</td>\n",
       "      <td>14675332</td>\n",
       "      <td>2022-04-28</td>\n",
       "      <td>8028</td>\n",
       "      <td>0xe87cc2ea916a7d471976b675d5f72302908e4590</td>\n",
       "      <td>0x98bea99727b297f5eca448d1640075f349c08547</td>\n",
       "      <td>0xe87cc2ea916a7d471976b675d5f72302908e4590</td>\n",
       "      <td>0x98bea99727b297f5eca448d1640075f349c08547</td>\n",
       "      <td>128.000</td>\n",
       "      <td>369867.804547</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    transaction_hash  block_number  \\\n",
       "0  0xb235f0321b0b50198399ec7f2bb759ef625f85673b4d...      14675897   \n",
       "1  0x1cf83a120540049239c62de9aa6cbc42bae7494cdf02...      14675814   \n",
       "2  0xf9854d2f02c074f1a2043bc93c5e4e6e4e922e6908a8...      14675812   \n",
       "3  0x38c03f0e17422b62f450262e2931a86efa201d3c22e9...      14675760   \n",
       "4  0x85ec6a319062f3537420ba60342ff273bbad907bfd9d...      14675332   \n",
       "\n",
       "         date  asset_id                                      seller  \\\n",
       "0  2022-04-28      7438  0x2fdcca65899346af3a93a8daa6128bdbcb1ce3b3   \n",
       "1  2022-04-28      1397  0xcedf17dfafa947cd0e205fe2a3a183cf2fb3a0bc   \n",
       "2  2022-04-28      5226  0x40c839b831c90173dc7fbce49a25274a4688ddd9   \n",
       "3  2022-04-28      6498  0x83e7fc69d7c99943afd9122ad0f8bb575e00597c   \n",
       "4  2022-04-28      8028  0xe87cc2ea916a7d471976b675d5f72302908e4590   \n",
       "\n",
       "                                        buyer  \\\n",
       "0  0xcedf17dfafa947cd0e205fe2a3a183cf2fb3a0bc   \n",
       "1  0x93d1f7636f56dd4498c778e212cbe3e2e15bcba9   \n",
       "2  0xd30cad3f023888d5a81580fbb80f93102d69e9ef   \n",
       "3  0x8dd47e819c53138aa18f8651d797e7969f34d1f1   \n",
       "4  0x98bea99727b297f5eca448d1640075f349c08547   \n",
       "\n",
       "                                        maker  \\\n",
       "0  0x2fdcca65899346af3a93a8daa6128bdbcb1ce3b3   \n",
       "1  0xcedf17dfafa947cd0e205fe2a3a183cf2fb3a0bc   \n",
       "2  0x40c839b831c90173dc7fbce49a25274a4688ddd9   \n",
       "3  0x83e7fc69d7c99943afd9122ad0f8bb575e00597c   \n",
       "4  0xe87cc2ea916a7d471976b675d5f72302908e4590   \n",
       "\n",
       "                                        taker  sale_price_eth  sale_price_usd  \n",
       "0  0xcedf17dfafa947cd0e205fe2a3a183cf2fb3a0bc         153.200   442685.528567  \n",
       "1  0x93d1f7636f56dd4498c778e212cbe3e2e15bcba9         139.750   403820.513167  \n",
       "2  0x00000000a50bb64b4bbeceb18715748dface08af         150.000   433438.833453  \n",
       "3  0x8dd47e819c53138aa18f8651d797e7969f34d1f1         145.678   420950.015866  \n",
       "4  0x98bea99727b297f5eca448d1640075f349c08547         128.000   369867.804547  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Import Sales Data\n",
    "sales = pd.read_csv(\"sales_0xBC4CA0EdA7647A8aB7C2061c2E118A18a936f13D_04302022.csv\")\n",
    "sales.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "117e0352",
   "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>asset_id</th>\n",
       "      <th>attribute_count</th>\n",
       "      <th>attribute_count_rarity_score</th>\n",
       "      <th>Hat_attribute</th>\n",
       "      <th>Hat_rarity_score</th>\n",
       "      <th>Background_attribute</th>\n",
       "      <th>Background_rarity_score</th>\n",
       "      <th>Clothes_attribute</th>\n",
       "      <th>Clothes_rarity_score</th>\n",
       "      <th>Mouth_attribute</th>\n",
       "      <th>Mouth_rarity_score</th>\n",
       "      <th>Fur_attribute</th>\n",
       "      <th>Fur_rarity_score</th>\n",
       "      <th>Eyes_attribute</th>\n",
       "      <th>Eyes_rarity_score</th>\n",
       "      <th>Earring_attribute</th>\n",
       "      <th>Earring_rarity_score</th>\n",
       "      <th>overall_rarity_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>1.878640</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.432624</td>\n",
       "      <td>Orange</td>\n",
       "      <td>7.855460</td>\n",
       "      <td>Striped Tee</td>\n",
       "      <td>24.271845</td>\n",
       "      <td>Discomfort</td>\n",
       "      <td>48.076923</td>\n",
       "      <td>Robot</td>\n",
       "      <td>37.735849</td>\n",
       "      <td>X Eyes</td>\n",
       "      <td>41.152263</td>\n",
       "      <td>Silver Hoop</td>\n",
       "      <td>11.337868</td>\n",
       "      <td>176.741472</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>3.937008</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.432624</td>\n",
       "      <td>Orange</td>\n",
       "      <td>7.855460</td>\n",
       "      <td>Vietnam Jacket</td>\n",
       "      <td>44.642857</td>\n",
       "      <td>Grin</td>\n",
       "      <td>14.025245</td>\n",
       "      <td>Robot</td>\n",
       "      <td>37.735849</td>\n",
       "      <td>Blue Beams</td>\n",
       "      <td>204.081633</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.423893</td>\n",
       "      <td>318.134569</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>3.937008</td>\n",
       "      <td>Sea Captain's Hat</td>\n",
       "      <td>32.894737</td>\n",
       "      <td>Aquamarine</td>\n",
       "      <td>7.898894</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.302227</td>\n",
       "      <td>Bored Cigarette</td>\n",
       "      <td>14.084507</td>\n",
       "      <td>Robot</td>\n",
       "      <td>37.735849</td>\n",
       "      <td>3d</td>\n",
       "      <td>20.533881</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.423893</td>\n",
       "      <td>123.810996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>3.937008</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.432624</td>\n",
       "      <td>Purple</td>\n",
       "      <td>7.745933</td>\n",
       "      <td>Bone Necklace</td>\n",
       "      <td>49.261084</td>\n",
       "      <td>Tongue Out</td>\n",
       "      <td>49.504950</td>\n",
       "      <td>Cheetah</td>\n",
       "      <td>24.630542</td>\n",
       "      <td>Bored</td>\n",
       "      <td>5.834306</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.423893</td>\n",
       "      <td>146.770340</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>1.878640</td>\n",
       "      <td>Party Hat 2</td>\n",
       "      <td>93.457944</td>\n",
       "      <td>Blue</td>\n",
       "      <td>8.051530</td>\n",
       "      <td>Navy Striped Tee</td>\n",
       "      <td>29.940120</td>\n",
       "      <td>Phoneme L</td>\n",
       "      <td>41.493776</td>\n",
       "      <td>Golden Brown</td>\n",
       "      <td>12.853470</td>\n",
       "      <td>Closed</td>\n",
       "      <td>14.084507</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.423893</td>\n",
       "      <td>203.183880</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   asset_id  attribute_count  attribute_count_rarity_score      Hat_attribute  \\\n",
       "0         0                6                      1.878640                NaN   \n",
       "1         1                5                      3.937008                NaN   \n",
       "2         2                5                      3.937008  Sea Captain's Hat   \n",
       "3         3                5                      3.937008                NaN   \n",
       "4         4                6                      1.878640        Party Hat 2   \n",
       "\n",
       "   Hat_rarity_score Background_attribute  Background_rarity_score  \\\n",
       "0          4.432624               Orange                 7.855460   \n",
       "1          4.432624               Orange                 7.855460   \n",
       "2         32.894737           Aquamarine                 7.898894   \n",
       "3          4.432624               Purple                 7.745933   \n",
       "4         93.457944                 Blue                 8.051530   \n",
       "\n",
       "  Clothes_attribute  Clothes_rarity_score  Mouth_attribute  \\\n",
       "0       Striped Tee             24.271845       Discomfort   \n",
       "1    Vietnam Jacket             44.642857             Grin   \n",
       "2               NaN              5.302227  Bored Cigarette   \n",
       "3     Bone Necklace             49.261084       Tongue Out   \n",
       "4  Navy Striped Tee             29.940120        Phoneme L   \n",
       "\n",
       "   Mouth_rarity_score Fur_attribute  Fur_rarity_score Eyes_attribute  \\\n",
       "0           48.076923         Robot         37.735849         X Eyes   \n",
       "1           14.025245         Robot         37.735849     Blue Beams   \n",
       "2           14.084507         Robot         37.735849             3d   \n",
       "3           49.504950       Cheetah         24.630542          Bored   \n",
       "4           41.493776  Golden Brown         12.853470         Closed   \n",
       "\n",
       "   Eyes_rarity_score Earring_attribute  Earring_rarity_score  \\\n",
       "0          41.152263       Silver Hoop             11.337868   \n",
       "1         204.081633               NaN              1.423893   \n",
       "2          20.533881               NaN              1.423893   \n",
       "3           5.834306               NaN              1.423893   \n",
       "4          14.084507               NaN              1.423893   \n",
       "\n",
       "   overall_rarity_score  \n",
       "0            176.741472  \n",
       "1            318.134569  \n",
       "2            123.810996  \n",
       "3            146.770340  \n",
       "4            203.183880  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Import Meta Data\n",
    "metadata = pd.read_csv(\"metadata_0xBC4CA0EdA7647A8aB7C2061c2E118A18a936f13D_04302022.csv\")\n",
    "metadata.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "0adb95e5",
   "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>transaction_hash</th>\n",
       "      <th>block_number</th>\n",
       "      <th>date</th>\n",
       "      <th>asset_id</th>\n",
       "      <th>from_address</th>\n",
       "      <th>to_address</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0x76b6196f390319fc3c0153bccb82c6e3667298a70d55...</td>\n",
       "      <td>14675899</td>\n",
       "      <td>2022-04-28</td>\n",
       "      <td>5037</td>\n",
       "      <td>0xff389e55ee4dd64d82b2b6fefef3f7aa63b218fd</td>\n",
       "      <td>0x3b968d2d299b895a5fcf3bba7a64ad0f566e6f88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0x76b6196f390319fc3c0153bccb82c6e3667298a70d55...</td>\n",
       "      <td>14675899</td>\n",
       "      <td>2022-04-28</td>\n",
       "      <td>5037</td>\n",
       "      <td>0x5f6ac80cdb9e87f3cfa6a90e5140b9a16a361d5c</td>\n",
       "      <td>0xdbfd76af2157dc15ee4e57f3f942bb45ba84af24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0x76b6196f390319fc3c0153bccb82c6e3667298a70d55...</td>\n",
       "      <td>14675899</td>\n",
       "      <td>2022-04-28</td>\n",
       "      <td>5037</td>\n",
       "      <td>0x3b968d2d299b895a5fcf3bba7a64ad0f566e6f88</td>\n",
       "      <td>0x70b97a0da65c15dfb0ffa02aee6fa36e507c2762</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0x76b6196f390319fc3c0153bccb82c6e3667298a70d55...</td>\n",
       "      <td>14675899</td>\n",
       "      <td>2022-04-28</td>\n",
       "      <td>5037</td>\n",
       "      <td>0x70b97a0da65c15dfb0ffa02aee6fa36e507c2762</td>\n",
       "      <td>0x5f6ac80cdb9e87f3cfa6a90e5140b9a16a361d5c</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0xb235f0321b0b50198399ec7f2bb759ef625f85673b4d...</td>\n",
       "      <td>14675897</td>\n",
       "      <td>2022-04-28</td>\n",
       "      <td>7438</td>\n",
       "      <td>0x2fdcca65899346af3a93a8daa6128bdbcb1ce3b3</td>\n",
       "      <td>0xcedf17dfafa947cd0e205fe2a3a183cf2fb3a0bc</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    transaction_hash  block_number  \\\n",
       "0  0x76b6196f390319fc3c0153bccb82c6e3667298a70d55...      14675899   \n",
       "1  0x76b6196f390319fc3c0153bccb82c6e3667298a70d55...      14675899   \n",
       "2  0x76b6196f390319fc3c0153bccb82c6e3667298a70d55...      14675899   \n",
       "3  0x76b6196f390319fc3c0153bccb82c6e3667298a70d55...      14675899   \n",
       "4  0xb235f0321b0b50198399ec7f2bb759ef625f85673b4d...      14675897   \n",
       "\n",
       "         date  asset_id                                from_address  \\\n",
       "0  2022-04-28      5037  0xff389e55ee4dd64d82b2b6fefef3f7aa63b218fd   \n",
       "1  2022-04-28      5037  0x5f6ac80cdb9e87f3cfa6a90e5140b9a16a361d5c   \n",
       "2  2022-04-28      5037  0x3b968d2d299b895a5fcf3bba7a64ad0f566e6f88   \n",
       "3  2022-04-28      5037  0x70b97a0da65c15dfb0ffa02aee6fa36e507c2762   \n",
       "4  2022-04-28      7438  0x2fdcca65899346af3a93a8daa6128bdbcb1ce3b3   \n",
       "\n",
       "                                   to_address  \n",
       "0  0x3b968d2d299b895a5fcf3bba7a64ad0f566e6f88  \n",
       "1  0xdbfd76af2157dc15ee4e57f3f942bb45ba84af24  \n",
       "2  0x70b97a0da65c15dfb0ffa02aee6fa36e507c2762  \n",
       "3  0x5f6ac80cdb9e87f3cfa6a90e5140b9a16a361d5c  \n",
       "4  0xcedf17dfafa947cd0e205fe2a3a183cf2fb3a0bc  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Import Transfer Data\n",
    "transfers = pd.read_csv(\"transfers_0xBC4CA0EdA7647A8aB7C2061c2E118A18a936f13D_04302022.csv\")\n",
    "transfers.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e922dccb",
   "metadata": {},
   "source": [
    "# Overpaying For Rarity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "89a55ac7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>$/Rarity</th>\n",
       "      <th>asset_id_left</th>\n",
       "      <th>sale_price_usd</th>\n",
       "      <th>overall_rarity_score</th>\n",
       "      <th>date</th>\n",
       "      <th>buyer</th>\n",
       "      <th>url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1859</th>\n",
       "      <td>15975.746473</td>\n",
       "      <td>3001</td>\n",
       "      <td>1.275556e+06</td>\n",
       "      <td>79.843251</td>\n",
       "      <td>2022-01-29</td>\n",
       "      <td>0xe21dc18513e3e68a52f9fcdacfd56948d43a11c6</td>\n",
       "      <td><a href=\"https://opensea.io/assets/ethereum/0xbc4ca0eda7647a8ab7c2061c2e118a18a936f13d/3001\" target=\"_blank\">https://opensea.io/assets/ethereum/0xbc4ca0eda7647a8ab7c2061c2e118a18a936f13d/3001</a></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4903</th>\n",
       "      <td>9743.491205</td>\n",
       "      <td>2087</td>\n",
       "      <td>2.195964e+06</td>\n",
       "      <td>225.377541</td>\n",
       "      <td>2021-09-30</td>\n",
       "      <td>0x79782b0ae9b71cff36bb32b4ef0935fd3c190350</td>\n",
       "      <td><a href=\"https://opensea.io/assets/ethereum/0xbc4ca0eda7647a8ab7c2061c2e118a18a936f13d/2087\" target=\"_blank\">https://opensea.io/assets/ethereum/0xbc4ca0eda7647a8ab7c2061c2e118a18a936f13d/2087</a></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1780</th>\n",
       "      <td>8917.785784</td>\n",
       "      <td>232</td>\n",
       "      <td>2.813689e+06</td>\n",
       "      <td>315.514270</td>\n",
       "      <td>2022-01-30</td>\n",
       "      <td>0x69c488bcda156379b6661f08a35db627e5d467dd</td>\n",
       "      <td><a href=\"https://opensea.io/assets/ethereum/0xbc4ca0eda7647a8ab7c2061c2e118a18a936f13d/232\" target=\"_blank\">https://opensea.io/assets/ethereum/0xbc4ca0eda7647a8ab7c2061c2e118a18a936f13d/232</a></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>250</th>\n",
       "      <td>8128.966197</td>\n",
       "      <td>3112</td>\n",
       "      <td>4.286647e+05</td>\n",
       "      <td>52.732988</td>\n",
       "      <td>2022-04-19</td>\n",
       "      <td>0x79e20edb7c098e799c345e3a5ecff87dbe39a853</td>\n",
       "      <td><a href=\"https://opensea.io/assets/ethereum/0xbc4ca0eda7647a8ab7c2061c2e118a18a936f13d/3112\" target=\"_blank\">https://opensea.io/assets/ethereum/0xbc4ca0eda7647a8ab7c2061c2e118a18a936f13d/3112</a></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4623</th>\n",
       "      <td>7927.427416</td>\n",
       "      <td>5199</td>\n",
       "      <td>1.532401e+06</td>\n",
       "      <td>193.303725</td>\n",
       "      <td>2021-10-14</td>\n",
       "      <td>0x020ca66c30bec2c4fe3861a94e4db4a498a35872</td>\n",
       "      <td><a href=\"https://opensea.io/assets/ethereum/0xbc4ca0eda7647a8ab7c2061c2e118a18a936f13d/5199\" target=\"_blank\">https://opensea.io/assets/ethereum/0xbc4ca0eda7647a8ab7c2061c2e118a18a936f13d/5199</a></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4558</th>\n",
       "      <td>7833.868942</td>\n",
       "      <td>8585</td>\n",
       "      <td>2.615460e+06</td>\n",
       "      <td>333.865658</td>\n",
       "      <td>2021-10-19</td>\n",
       "      <td>0x86292f24afffe8d4159282980db89e8ef4b92b0a</td>\n",
       "      <td><a href=\"https://opensea.io/assets/ethereum/0xbc4ca0eda7647a8ab7c2061c2e118a18a936f13d/8585\" target=\"_blank\">https://opensea.io/assets/ethereum/0xbc4ca0eda7647a8ab7c2061c2e118a18a936f13d/8585</a></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2216</th>\n",
       "      <td>7521.567504</td>\n",
       "      <td>1734</td>\n",
       "      <td>2.477616e+06</td>\n",
       "      <td>329.401551</td>\n",
       "      <td>2022-01-20</td>\n",
       "      <td>0x4f88c947e1c254cf0f56da819e024779fa202787</td>\n",
       "      <td><a href=\"https://opensea.io/assets/ethereum/0xbc4ca0eda7647a8ab7c2061c2e118a18a936f13d/1734\" target=\"_blank\">https://opensea.io/assets/ethereum/0xbc4ca0eda7647a8ab7c2061c2e118a18a936f13d/1734</a></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5227</th>\n",
       "      <td>6775.470019</td>\n",
       "      <td>3928</td>\n",
       "      <td>2.139568e+06</td>\n",
       "      <td>315.781464</td>\n",
       "      <td>2021-09-13</td>\n",
       "      <td>0xf50791538e9afc3d3af2953c313f2db96fd993ab</td>\n",
       "      <td><a href=\"https://opensea.io/assets/ethereum/0xbc4ca0eda7647a8ab7c2061c2e118a18a936f13d/3928\" target=\"_blank\">https://opensea.io/assets/ethereum/0xbc4ca0eda7647a8ab7c2061c2e118a18a936f13d/3928</a></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193</th>\n",
       "      <td>6671.114662</td>\n",
       "      <td>6069</td>\n",
       "      <td>3.942954e+05</td>\n",
       "      <td>59.104880</td>\n",
       "      <td>2022-04-20</td>\n",
       "      <td>0xa9fe952edd2958ab7dea126c6d8b4413afd3e771</td>\n",
       "      <td><a href=\"https://opensea.io/assets/ethereum/0xbc4ca0eda7647a8ab7c2061c2e118a18a936f13d/6069\" target=\"_blank\">https://opensea.io/assets/ethereum/0xbc4ca0eda7647a8ab7c2061c2e118a18a936f13d/6069</a></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147</th>\n",
       "      <td>6392.961124</td>\n",
       "      <td>3739</td>\n",
       "      <td>5.340754e+05</td>\n",
       "      <td>83.541154</td>\n",
       "      <td>2022-04-23</td>\n",
       "      <td>0x6449bbb550f1049236d803f0e43759999b9a2bee</td>\n",
       "      <td><a href=\"https://opensea.io/assets/ethereum/0xbc4ca0eda7647a8ab7c2061c2e118a18a936f13d/3739\" target=\"_blank\">https://opensea.io/assets/ethereum/0xbc4ca0eda7647a8ab7c2061c2e118a18a936f13d/3739</a></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Join The sales and the meta data\n",
    "sales_with_meta = sales.join(metadata, on = \"asset_id\",  lsuffix=\"_left\", rsuffix=\"_right\")\n",
    "\n",
    "# Take the sales price and divde the rarity to create a $/ rarity score\n",
    "# The higher the money, and lower rarity = higher score, which means a low value deal\n",
    "sales_with_meta['$/Rarity'] = sales_with_meta[\"sale_price_usd\"]/sales_with_meta[\"overall_rarity_score\"]\n",
    "\n",
    "# Create link to OpenSea page\n",
    "base_url = \"https://opensea.io/assets/ethereum/0xbc4ca0eda7647a8ab7c2061c2e118a18a936f13d/\"\n",
    "sales_with_meta['url'] =  base_url + sales_with_meta['asset_id_left'].astype(str)\n",
    "\n",
    "# Top 10 lowest values\n",
    "important_columns = ['$/Rarity','asset_id_left',\"sale_price_usd\",\"overall_rarity_score\", 'date','buyer', 'url']\n",
    "top_10 = sales_with_meta[important_columns].sort_values(['$/Rarity'],ascending=False)[0:10] \n",
    "\n",
    "#Show Table\n",
    "HTML(top_10.to_html(render_links=True, escape=False))\n"
   ]
  }
 ],
 "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.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}