2_merge_TNO_diameter_data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "numpy     1.15.4\n",
      "pandas    0.23.4\n",
      "watermark 1.8.1\n",
      "ELEANOR LUTZ 2019-06-03 \n",
      "\n",
      "CPython 3.7.1\n",
      "IPython 7.2.0\n",
      "\n",
      "compiler   : MSC v.1900 64 bit (AMD64)\n",
      "system     : Windows\n",
      "release    : 10\n",
      "machine    : AMD64\n",
      "processor  : Intel64 Family 6 Model 63 Stepping 2, GenuineIntel\n",
      "CPU cores  : 12\n",
      "interpreter: 64bit\n"
     ]
    }
   ],
   "source": [
    "# Watermark is not required for this code, but is included for information. \n",
    "import watermark\n",
    "%load_ext watermark\n",
    "%watermark -a \"ELEANOR LUTZ\" -d -v -iv -m"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import os.path"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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>0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2060  Chiron          1977 UB       13.7  0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2060  Chiron          1977 UB       13.7  0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2060  Chiron          1977 UB       13.7  0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2060  Chiron          1977 UB       13.7  0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2060  Chiron          1977 UB       13.7  0...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   0\n",
       "0     2060  Chiron          1977 UB       13.7  0...\n",
       "1     2060  Chiron          1977 UB       13.7  0...\n",
       "2     2060  Chiron          1977 UB       13.7  0...\n",
       "3     2060  Chiron          1977 UB       13.7  0...\n",
       "4     2060  Chiron          1977 UB       13.7  0..."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>number</th>\n",
       "      <th>name</th>\n",
       "      <th>full_name</th>\n",
       "      <th>axis_AU</th>\n",
       "      <th>eccentricity</th>\n",
       "      <th>inclination</th>\n",
       "      <th>type</th>\n",
       "      <th>companions</th>\n",
       "      <th>magnitude</th>\n",
       "      <th>abs_magnitude</th>\n",
       "      <th>...</th>\n",
       "      <th>albedo_err2</th>\n",
       "      <th>albedo_notecode</th>\n",
       "      <th>albedo_colorcode</th>\n",
       "      <th>density</th>\n",
       "      <th>density_err1</th>\n",
       "      <th>density_err2</th>\n",
       "      <th>density_notecode</th>\n",
       "      <th>methods_da</th>\n",
       "      <th>methods_density</th>\n",
       "      <th>reference</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2060</td>\n",
       "      <td>Chiron</td>\n",
       "      <td>1977UB</td>\n",
       "      <td>13.7</td>\n",
       "      <td>0.38</td>\n",
       "      <td>7.</td>\n",
       "      <td>CEN</td>\n",
       "      <td>0</td>\n",
       "      <td>5.8</td>\n",
       "      <td>6.900</td>\n",
       "      <td>...</td>\n",
       "      <td>0.035</td>\n",
       "      <td>*</td>\n",
       "      <td>-</td>\n",
       "      <td>-9.999</td>\n",
       "      <td>-9.999</td>\n",
       "      <td>-9.999</td>\n",
       "      <td>-</td>\n",
       "      <td>T</td>\n",
       "      <td>-</td>\n",
       "      <td>L84a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2060</td>\n",
       "      <td>Chiron</td>\n",
       "      <td>1977UB</td>\n",
       "      <td>13.7</td>\n",
       "      <td>0.38</td>\n",
       "      <td>7.</td>\n",
       "      <td>CEN</td>\n",
       "      <td>0</td>\n",
       "      <td>5.8</td>\n",
       "      <td>-99.999</td>\n",
       "      <td>...</td>\n",
       "      <td>-9.999</td>\n",
       "      <td>-</td>\n",
       "      <td>G</td>\n",
       "      <td>-9.999</td>\n",
       "      <td>-9.999</td>\n",
       "      <td>-9.999</td>\n",
       "      <td>-</td>\n",
       "      <td>T</td>\n",
       "      <td>-</td>\n",
       "      <td>S91a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2060</td>\n",
       "      <td>Chiron</td>\n",
       "      <td>1977UB</td>\n",
       "      <td>13.7</td>\n",
       "      <td>0.38</td>\n",
       "      <td>7.</td>\n",
       "      <td>CEN</td>\n",
       "      <td>0</td>\n",
       "      <td>5.8</td>\n",
       "      <td>6.800</td>\n",
       "      <td>...</td>\n",
       "      <td>-9.999</td>\n",
       "      <td>*</td>\n",
       "      <td>G</td>\n",
       "      <td>-9.999</td>\n",
       "      <td>-9.999</td>\n",
       "      <td>-9.999</td>\n",
       "      <td>-</td>\n",
       "      <td>T</td>\n",
       "      <td>-</td>\n",
       "      <td>J92a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2060</td>\n",
       "      <td>Chiron</td>\n",
       "      <td>1977UB</td>\n",
       "      <td>13.7</td>\n",
       "      <td>0.38</td>\n",
       "      <td>7.</td>\n",
       "      <td>CEN</td>\n",
       "      <td>0</td>\n",
       "      <td>5.8</td>\n",
       "      <td>-99.999</td>\n",
       "      <td>...</td>\n",
       "      <td>-9.999</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-9.999</td>\n",
       "      <td>-9.999</td>\n",
       "      <td>-9.999</td>\n",
       "      <td>-</td>\n",
       "      <td>O</td>\n",
       "      <td>-</td>\n",
       "      <td>B93a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2060</td>\n",
       "      <td>Chiron</td>\n",
       "      <td>1977UB</td>\n",
       "      <td>13.7</td>\n",
       "      <td>0.38</td>\n",
       "      <td>7.</td>\n",
       "      <td>CEN</td>\n",
       "      <td>0</td>\n",
       "      <td>5.8</td>\n",
       "      <td>6.900</td>\n",
       "      <td>...</td>\n",
       "      <td>0.030</td>\n",
       "      <td>*</td>\n",
       "      <td>-</td>\n",
       "      <td>-9.999</td>\n",
       "      <td>-9.999</td>\n",
       "      <td>-9.999</td>\n",
       "      <td>-</td>\n",
       "      <td>T</td>\n",
       "      <td>-</td>\n",
       "      <td>C94a</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 35 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  number    name full_name axis_AU eccentricity inclination type companions  \\\n",
       "0   2060  Chiron    1977UB    13.7         0.38          7.  CEN          0   \n",
       "1   2060  Chiron    1977UB    13.7         0.38          7.  CEN          0   \n",
       "2   2060  Chiron    1977UB    13.7         0.38          7.  CEN          0   \n",
       "3   2060  Chiron    1977UB    13.7         0.38          7.  CEN          0   \n",
       "4   2060  Chiron    1977UB    13.7         0.38          7.  CEN          0   \n",
       "\n",
       "  magnitude abs_magnitude    ...    albedo_err2 albedo_notecode  \\\n",
       "0       5.8         6.900    ...          0.035               *   \n",
       "1       5.8       -99.999    ...         -9.999               -   \n",
       "2       5.8         6.800    ...         -9.999               *   \n",
       "3       5.8       -99.999    ...         -9.999               -   \n",
       "4       5.8         6.900    ...          0.030               *   \n",
       "\n",
       "  albedo_colorcode density density_err1 density_err2 density_notecode  \\\n",
       "0                -  -9.999       -9.999       -9.999                -   \n",
       "1                G  -9.999       -9.999       -9.999                -   \n",
       "2                G  -9.999       -9.999       -9.999                -   \n",
       "3                -  -9.999       -9.999       -9.999                -   \n",
       "4                -  -9.999       -9.999       -9.999                -   \n",
       "\n",
       "  methods_da methods_density reference  \n",
       "0          T               -      L84a  \n",
       "1          T               -      S91a  \n",
       "2          T               -      J92a  \n",
       "3          O               -      B93a  \n",
       "4          T               -      C94a  \n",
       "\n",
       "[5 rows x 35 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>number</th>\n",
       "      <th>name</th>\n",
       "      <th>full_name</th>\n",
       "      <th>type</th>\n",
       "      <th>diameter_km</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "      <td>1996TS66</td>\n",
       "      <td>CUB</td>\n",
       "      <td>163.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "      <td>1998WW31</td>\n",
       "      <td>CUB</td>\n",
       "      <td>170.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "      <td>1999OJ4</td>\n",
       "      <td>CUB</td>\n",
       "      <td>103.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "      <td>2000CF105</td>\n",
       "      <td>CUB</td>\n",
       "      <td>78.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "      <td>2000GM137</td>\n",
       "      <td>CEN</td>\n",
       "      <td>8.80</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  number name  full_name type  diameter_km\n",
       "0      0        1996TS66  CUB       163.00\n",
       "1      0        1998WW31  CUB       170.30\n",
       "2      0         1999OJ4  CUB       103.95\n",
       "3      0       2000CF105  CUB        78.30\n",
       "4      0       2000GM137  CEN         8.80"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "179 unique asteroids\n"
     ]
    }
   ],
   "source": [
    "# Manually assign column names from bundle_description.txt file\n",
    "colnames = ['number', 'name', 'full_name', 'axis_AU', 'eccentricity', 'inclination',\n",
    "            'type', 'companions', 'magnitude', 'abs_magnitude', 'abs_magnitude_err', \n",
    "            'diameter_km', 'e_diameter_err1', 'e_diameter_err2', 'e_diameter_notecode', \n",
    "            'p_diameter', 'p_diameter_err1', 'p_diameter_err2', 'p_diameter_notecode',\n",
    "            'c_diameter', 'c_diameter_err1', 'c_diameter_err2', 'c_diameter_notecode',\n",
    "            'albedo', 'albedo_err1', 'albedo_err2', 'albedo_notecode', 'albedo_colorcode', \n",
    "            'density', 'density_err1', 'density_err2', 'density_notecode', \n",
    "            'methods_da', 'methods_density', 'reference'\n",
    "           ]\n",
    "\n",
    "diams = pd.read_csv('./data/diameters/tno-centaur_diam-albedo-density/data/tno_centaur_diam_alb_dens.tab', \n",
    "                    header=None, sep='\\t', encoding='latin-1')\n",
    "\n",
    "display(diams.head())\n",
    "diams = diams[0].str.split(expand=True)\n",
    "diams[2] = diams[2] + diams[3]\n",
    "diams.drop([3], inplace=True, axis=1)\n",
    "\n",
    "diams.columns = colnames\n",
    "display(diams.head())\n",
    "\n",
    "diams = diams[['number', 'name', 'full_name', 'type', 'diameter_km', 'p_diameter']]\n",
    "diams['diameter_km'] = diams['diameter_km'].astype(float)\n",
    "diams['p_diameter'] = diams['p_diameter'].astype(float)\n",
    "diams.loc[~(diams['diameter_km'] > 0), 'diameter_km']=np.nan\n",
    "diams.loc[~(diams['p_diameter'] > 0), 'p_diameter']=np.nan\n",
    "diams['diameter_km'] = diams['diameter_km'].fillna(diams['p_diameter'])\n",
    "diams = diams[~pd.isnull(diams['diameter_km'])]\n",
    "\n",
    "diams.set_index(['number', 'name', 'full_name', 'type'], inplace=True)\n",
    "diams = diams.groupby(['number', 'name', 'full_name', 'type']).median()\n",
    "diams = diams.reset_index()\n",
    "diams['name'] = diams['name'].str.replace('-', '')\n",
    "diams.loc[diams['name'] == 'Pluto', 'full_name'] = '134340Pluto'\n",
    "diams.drop(['p_diameter'], axis=1, inplace=True)\n",
    "\n",
    "display(diams.head())\n",
    "savename = './data/diameters/TNO_Centaurs.csv'\n",
    "if not os.path.isfile(savename):\n",
    "    diams.to_csv(savename, index=False)\n",
    "\n",
    "print(len(diams), 'unique asteroids')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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>id</th>\n",
       "      <th>spkid</th>\n",
       "      <th>full_name</th>\n",
       "      <th>pdes</th>\n",
       "      <th>name</th>\n",
       "      <th>neo</th>\n",
       "      <th>pha</th>\n",
       "      <th>diameter</th>\n",
       "      <th>prefix</th>\n",
       "      <th>q</th>\n",
       "      <th>per</th>\n",
       "      <th>class</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>794557</th>\n",
       "      <td>bT3S3411</td>\n",
       "      <td>3246664</td>\n",
       "      <td>3411T3</td>\n",
       "      <td>3411 T-3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.073221</td>\n",
       "      <td>1428.185418</td>\n",
       "      <td>MBA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>794558</th>\n",
       "      <td>bT3S3521</td>\n",
       "      <td>3246672</td>\n",
       "      <td>3521T3</td>\n",
       "      <td>3521 T-3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.546423</td>\n",
       "      <td>1386.029296</td>\n",
       "      <td>MCA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>794559</th>\n",
       "      <td>bT3S4571</td>\n",
       "      <td>3248621</td>\n",
       "      <td>4571T3</td>\n",
       "      <td>4571 T-3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.813901</td>\n",
       "      <td>1484.222588</td>\n",
       "      <td>MBA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>794560</th>\n",
       "      <td>bT3S4658</td>\n",
       "      <td>3248624</td>\n",
       "      <td>4658T3</td>\n",
       "      <td>4658 T-3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.718022</td>\n",
       "      <td>1138.438109</td>\n",
       "      <td>MBA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>794561</th>\n",
       "      <td>bT3S5154</td>\n",
       "      <td>3248651</td>\n",
       "      <td>5154T3</td>\n",
       "      <td>5154 T-3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.239464</td>\n",
       "      <td>1985.374056</td>\n",
       "      <td>MBA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              id    spkid full_name      pdes name neo pha diameter prefix  \\\n",
       "794557  bT3S3411  3246664    3411T3  3411 T-3  NaN   N   N      NaN    NaN   \n",
       "794558  bT3S3521  3246672    3521T3  3521 T-3  NaN   N   N      NaN    NaN   \n",
       "794559  bT3S4571  3248621    4571T3  4571 T-3  NaN   N   N      NaN    NaN   \n",
       "794560  bT3S4658  3248624    4658T3  4658 T-3  NaN   N   N      NaN    NaN   \n",
       "794561  bT3S5154  3248651    5154T3  5154 T-3  NaN   N   N      NaN    NaN   \n",
       "\n",
       "               q          per class  \n",
       "794557  2.073221  1428.185418   MBA  \n",
       "794558  1.546423  1386.029296   MCA  \n",
       "794559  1.813901  1484.222588   MBA  \n",
       "794560  1.718022  1138.438109   MBA  \n",
       "794561  2.239464  1985.374056   MBA  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>id</th>\n",
       "      <th>spkid</th>\n",
       "      <th>full_name</th>\n",
       "      <th>pdes</th>\n",
       "      <th>name</th>\n",
       "      <th>neo</th>\n",
       "      <th>pha</th>\n",
       "      <th>diameter</th>\n",
       "      <th>prefix</th>\n",
       "      <th>q</th>\n",
       "      <th>per</th>\n",
       "      <th>class</th>\n",
       "      <th>diameter_km</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>794557</th>\n",
       "      <td>bT3S3411</td>\n",
       "      <td>3246664</td>\n",
       "      <td>3411T3</td>\n",
       "      <td>3411 T-3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.073221</td>\n",
       "      <td>1428.185418</td>\n",
       "      <td>MBA</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>794558</th>\n",
       "      <td>bT3S3521</td>\n",
       "      <td>3246672</td>\n",
       "      <td>3521T3</td>\n",
       "      <td>3521 T-3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.546423</td>\n",
       "      <td>1386.029296</td>\n",
       "      <td>MCA</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>794559</th>\n",
       "      <td>bT3S4571</td>\n",
       "      <td>3248621</td>\n",
       "      <td>4571T3</td>\n",
       "      <td>4571 T-3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.813901</td>\n",
       "      <td>1484.222588</td>\n",
       "      <td>MBA</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>794560</th>\n",
       "      <td>bT3S4658</td>\n",
       "      <td>3248624</td>\n",
       "      <td>4658T3</td>\n",
       "      <td>4658 T-3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.718022</td>\n",
       "      <td>1138.438109</td>\n",
       "      <td>MBA</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>794561</th>\n",
       "      <td>bT3S5154</td>\n",
       "      <td>3248651</td>\n",
       "      <td>5154T3</td>\n",
       "      <td>5154 T-3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.239464</td>\n",
       "      <td>1985.374056</td>\n",
       "      <td>MBA</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              id    spkid full_name      pdes name neo pha diameter prefix  \\\n",
       "794557  bT3S3411  3246664    3411T3  3411 T-3  NaN   N   N      NaN    NaN   \n",
       "794558  bT3S3521  3246672    3521T3  3521 T-3  NaN   N   N      NaN    NaN   \n",
       "794559  bT3S4571  3248621    4571T3  4571 T-3  NaN   N   N      NaN    NaN   \n",
       "794560  bT3S4658  3248624    4658T3  4658 T-3  NaN   N   N      NaN    NaN   \n",
       "794561  bT3S5154  3248651    5154T3  5154 T-3  NaN   N   N      NaN    NaN   \n",
       "\n",
       "               q          per class  diameter_km  \n",
       "794557  2.073221  1428.185418   MBA          NaN  \n",
       "794558  1.546423  1386.029296   MCA          NaN  \n",
       "794559  1.813901  1484.222588   MBA          NaN  \n",
       "794560  1.718022  1138.438109   MBA          NaN  \n",
       "794561  2.239464  1985.374056   MBA          NaN  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "44 diameter values that can be joined\n",
      "35 new diameter values added\n"
     ]
    },
    {
     "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>id</th>\n",
       "      <th>spkid</th>\n",
       "      <th>full_name</th>\n",
       "      <th>pdes</th>\n",
       "      <th>name</th>\n",
       "      <th>neo</th>\n",
       "      <th>pha</th>\n",
       "      <th>diameter</th>\n",
       "      <th>prefix</th>\n",
       "      <th>q</th>\n",
       "      <th>per</th>\n",
       "      <th>class</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>794557</th>\n",
       "      <td>bT3S3411</td>\n",
       "      <td>3246664</td>\n",
       "      <td>3411T3</td>\n",
       "      <td>3411 T-3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.073221</td>\n",
       "      <td>1428.185418</td>\n",
       "      <td>MBA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>794558</th>\n",
       "      <td>bT3S3521</td>\n",
       "      <td>3246672</td>\n",
       "      <td>3521T3</td>\n",
       "      <td>3521 T-3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.546423</td>\n",
       "      <td>1386.029296</td>\n",
       "      <td>MCA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>794559</th>\n",
       "      <td>bT3S4571</td>\n",
       "      <td>3248621</td>\n",
       "      <td>4571T3</td>\n",
       "      <td>4571 T-3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.813901</td>\n",
       "      <td>1484.222588</td>\n",
       "      <td>MBA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>794560</th>\n",
       "      <td>bT3S4658</td>\n",
       "      <td>3248624</td>\n",
       "      <td>4658T3</td>\n",
       "      <td>4658 T-3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.718022</td>\n",
       "      <td>1138.438109</td>\n",
       "      <td>MBA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>794561</th>\n",
       "      <td>bT3S5154</td>\n",
       "      <td>3248651</td>\n",
       "      <td>5154T3</td>\n",
       "      <td>5154 T-3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.239464</td>\n",
       "      <td>1985.374056</td>\n",
       "      <td>MBA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              id    spkid full_name      pdes name neo pha diameter prefix  \\\n",
       "794557  bT3S3411  3246664    3411T3  3411 T-3  NaN   N   N      NaN    NaN   \n",
       "794558  bT3S3521  3246672    3521T3  3521 T-3  NaN   N   N      NaN    NaN   \n",
       "794559  bT3S4571  3248621    4571T3  4571 T-3  NaN   N   N      NaN    NaN   \n",
       "794560  bT3S4658  3248624    4658T3  4658 T-3  NaN   N   N      NaN    NaN   \n",
       "794561  bT3S5154  3248651    5154T3  5154 T-3  NaN   N   N      NaN    NaN   \n",
       "\n",
       "               q          per class  \n",
       "794557  2.073221  1428.185418   MBA  \n",
       "794558  1.546423  1386.029296   MCA  \n",
       "794559  1.813901  1484.222588   MBA  \n",
       "794560  1.718022  1138.438109   MBA  \n",
       "794561  2.239464  1985.374056   MBA  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df = pd.read_csv('./data/all_asteroids.csv', low_memory=False)\n",
    "df['full_name'] = df['full_name'].str.replace(' ', '').str.replace('(', '').str.replace(')', '').str.replace('-', '')\n",
    "display(df.tail())\n",
    "\n",
    "diams = pd.read_csv('./data/diameters/TNO_Centaurs.csv', low_memory=False)\n",
    "diams.drop(['number', 'name', 'type'], axis=1, inplace=True)\n",
    "df = pd.merge(df, diams, on='full_name', how='left')\n",
    "display(df.tail())\n",
    "\n",
    "print(len(df[~pd.isnull(df['diameter_km'])]), 'diameter values that can be joined')\n",
    "original = len(df[~pd.isnull(df['diameter'])])\n",
    "df['diameter'] = df['diameter'].fillna(df['diameter_km'])\n",
    "print(len(df[~pd.isnull(df['diameter'])])-original, 'new diameter values added')\n",
    "df.drop(['diameter_km'], inplace=True, axis=1)\n",
    "\n",
    "savename = './data/all_asteroids_wrangled.csv'\n",
    "if not os.path.isfile(savename):\n",
    "    df.to_csv(savename, index=False)\n",
    "display(df.tail())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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>id</th>\n",
       "      <th>spkid</th>\n",
       "      <th>full_name</th>\n",
       "      <th>pdes</th>\n",
       "      <th>name</th>\n",
       "      <th>neo</th>\n",
       "      <th>pha</th>\n",
       "      <th>diameter</th>\n",
       "      <th>prefix</th>\n",
       "      <th>q</th>\n",
       "      <th>per</th>\n",
       "      <th>class</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>134339</th>\n",
       "      <td>a0134340</td>\n",
       "      <td>2134340</td>\n",
       "      <td>134340Pluto</td>\n",
       "      <td>134340</td>\n",
       "      <td>Pluto</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>2361</td>\n",
       "      <td>NaN</td>\n",
       "      <td>29.573992</td>\n",
       "      <td>90487.276927</td>\n",
       "      <td>TNO</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              id    spkid    full_name    pdes   name neo pha diameter prefix  \\\n",
       "134339  a0134340  2134340  134340Pluto  134340  Pluto   N   N     2361    NaN   \n",
       "\n",
       "                q           per class  \n",
       "134339  29.573992  90487.276927   TNO  "
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check that Pluto has been added appropriately\n",
    "df[df['full_name'] == '134340Pluto']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}