clean_raw_df.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import requests\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "url = 'https://raw.githubusercontent.com/OpenExoplanetCatalogue/oec_tables/master/comma_separated/open_exoplanet_catalogue.txt'\n",
    "response = requests.get(url)\n",
    "part = response.text.split('on\\r\\n# \\r\\n')\n",
    "len(part)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['TypeFlag',\n",
       " 'PlanetaryMassJpt',\n",
       " 'RadiusJpt',\n",
       " 'PeriodDays',\n",
       " 'SemiMajorAxisAU',\n",
       " 'Eccentricity',\n",
       " 'PeriastronDeg',\n",
       " 'LongitudeDeg',\n",
       " 'AscendingNodeDeg',\n",
       " 'InclinationDeg',\n",
       " 'SurfaceTempK',\n",
       " 'AgeGyr',\n",
       " 'DiscoveryMethod',\n",
       " 'DiscoveryYear',\n",
       " 'LastUpdated',\n",
       " 'RightAscension',\n",
       " 'Declination',\n",
       " 'DistFromSunParsec',\n",
       " 'HostStarMassSlrMass',\n",
       " 'HostStarRadiusSlrRad',\n",
       " 'HostStarMetallicity',\n",
       " 'HostStarTempK',\n",
       " 'HostStarAgeGyr',\n",
       " 'ListsPlanetIsOn']"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "column_list = pd.DataFrame.from_csv('oec.csv').columns.tolist()\n",
    "column_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['PlanetIdentifier',\n",
       " 'TypeFlag',\n",
       " 'PlanetaryMassJpt',\n",
       " 'RadiusJpt',\n",
       " 'PeriodDays',\n",
       " 'SemiMajorAxisAU',\n",
       " 'Eccentricity',\n",
       " 'PeriastronDeg',\n",
       " 'LongitudeDeg',\n",
       " 'AscendingNodeDeg',\n",
       " 'InclinationDeg',\n",
       " 'SurfaceTempK',\n",
       " 'AgeGyr',\n",
       " 'DiscoveryMethod',\n",
       " 'DiscoveryYear',\n",
       " 'LastUpdated',\n",
       " 'RightAscension',\n",
       " 'Declination',\n",
       " 'DistFromSunParsec',\n",
       " 'HostStarMassSlrMass',\n",
       " 'HostStarRadiusSlrRad',\n",
       " 'HostStarMetallicity',\n",
       " 'HostStarTempK',\n",
       " 'HostStarAgeGyr',\n",
       " 'ListsPlanetIsOn']"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "column_list.insert(0, 'PlanetIdentifier')\n",
    "column_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " Primary identifier of planet-PlanetIdentifier\n",
      " Binary flag [0=no known stellar binary companion; 1=P-type binary (circumbinary); 2=S-type binary; 3=orphan planet (no star)]-TypeFlag\n",
      " Planetary mass [Jupiter masses]-PlanetaryMassJpt\n",
      " Radius [Jupiter radii]-RadiusJpt\n",
      " Period [days]-PeriodDays\n",
      " Semi-major axis [Astronomical Units]-SemiMajorAxisAU\n",
      " Eccentricity-Eccentricity\n",
      " Periastron [degree]-PeriastronDeg\n",
      " Longitude [degree]-LongitudeDeg\n",
      " Ascending node [degree]-AscendingNodeDeg\n",
      " Inclination [degree]-InclinationDeg\n",
      " Surface or equilibrium temperature [K]-SurfaceTempK\n",
      " Age [Gyr]-AgeGyr\n",
      " Discovery method-DiscoveryMethod\n",
      " Discovery year [yyyy]-DiscoveryYear\n",
      " Last updated [yy/mm/dd]-LastUpdated\n",
      " Right ascension [hh mm ss]-RightAscension\n",
      " Declination [+/-dd mm ss]-Declination\n",
      " Distance from Sun [parsec]-DistFromSunParsec\n",
      " Host star mass [Solar masses]-HostStarMassSlrMass\n",
      " Host star radius [Solar radii]-HostStarRadiusSlrRad\n",
      " Host star metallicity [log relative to solar]-HostStarMetallicity\n",
      " Host star temperature [K]-HostStarTempK\n",
      " Host star age [Gyr]-HostStarAgeGyr\n",
      " A list of lists the planet is -ListsPlanetIsOn\n"
     ]
    }
   ],
   "source": [
    "compare_column_list = part[0].split('\\r\\n')[5:]\n",
    "for index,item in enumerate(compare_column_list):\n",
    "    #print(item)\n",
    "    print(item.split(':')[1] + '-' + column_list[index])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "rows = part[1].split('\\r\\n')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'AgeGyr': '',\n",
       " 'AscendingNodeDeg': '',\n",
       " 'Declination': '-30 22 38',\n",
       " 'DiscoveryMethod': 'microlensing',\n",
       " 'DiscoveryYear': '2005',\n",
       " 'DistFromSunParsec': '6500',\n",
       " 'Eccentricity': '',\n",
       " 'HostStarAgeGyr': '',\n",
       " 'HostStarMassSlrMass': '0.22',\n",
       " 'HostStarMetallicity': '',\n",
       " 'HostStarRadiusSlrRad': '',\n",
       " 'HostStarTempK': '3840.0',\n",
       " 'InclinationDeg': '',\n",
       " 'LastUpdated': '06/04/12',\n",
       " 'ListsPlanetIsOn': 'Confirmed planets',\n",
       " 'LongitudeDeg': '',\n",
       " 'PeriastronDeg': '',\n",
       " 'PeriodDays': '3500',\n",
       " 'PlanetIdentifier': 'OGLE-05-390L b',\n",
       " 'PlanetaryMassJpt': '0.017',\n",
       " 'RadiusJpt': '',\n",
       " 'RightAscension': '17 54 19',\n",
       " 'SemiMajorAxisAU': '2.1',\n",
       " 'SurfaceTempK': '',\n",
       " 'TypeFlag': '0'}"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dat_list = []\n",
    "rows[0].split(',')\n",
    "for row in rows:\n",
    "    dat = {}\n",
    "    for index,item in enumerate(row.split(',')):\n",
    "        try:\n",
    "            dat[column_list[index]] = item\n",
    "        except:\n",
    "            dat[column_list[-1]] += ', ' + item\n",
    "    dat_list.append(dat)\n",
    "dat_list[10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AgeGyr</th>\n",
       "      <th>AscendingNodeDeg</th>\n",
       "      <th>Declination</th>\n",
       "      <th>DiscoveryMethod</th>\n",
       "      <th>DiscoveryYear</th>\n",
       "      <th>DistFromSunParsec</th>\n",
       "      <th>Eccentricity</th>\n",
       "      <th>HostStarAgeGyr</th>\n",
       "      <th>HostStarMassSlrMass</th>\n",
       "      <th>HostStarMetallicity</th>\n",
       "      <th>...</th>\n",
       "      <th>ListsPlanetIsOn</th>\n",
       "      <th>LongitudeDeg</th>\n",
       "      <th>PeriastronDeg</th>\n",
       "      <th>PeriodDays</th>\n",
       "      <th>PlanetaryMassJpt</th>\n",
       "      <th>RadiusJpt</th>\n",
       "      <th>RightAscension</th>\n",
       "      <th>SemiMajorAxisAU</th>\n",
       "      <th>SurfaceTempK</th>\n",
       "      <th>TypeFlag</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PlanetIdentifier</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></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>Kepler-337 b</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>+47 09 50</td>\n",
       "      <td>transit</td>\n",
       "      <td>2014</td>\n",
       "      <td>674.39</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1.0210</td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td>Confirmed planets</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>3.292781</td>\n",
       "      <td></td>\n",
       "      <td>0.14034</td>\n",
       "      <td>19 20 14</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GJ 536</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>-02 39 17.5</td>\n",
       "      <td>RV</td>\n",
       "      <td>2016</td>\n",
       "      <td>10.03</td>\n",
       "      <td>0.08</td>\n",
       "      <td></td>\n",
       "      <td>0.52</td>\n",
       "      <td>-0.08</td>\n",
       "      <td>...</td>\n",
       "      <td>Confirmed planets</td>\n",
       "      <td></td>\n",
       "      <td>288.7</td>\n",
       "      <td>8.7076</td>\n",
       "      <td>0.0169</td>\n",
       "      <td></td>\n",
       "      <td>14 01 03.19</td>\n",
       "      <td>0.06661</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kepler-1094 b</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>+38 16 01</td>\n",
       "      <td>transit</td>\n",
       "      <td>2016</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1.14</td>\n",
       "      <td>0.06</td>\n",
       "      <td>...</td>\n",
       "      <td>Confirmed planets</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>78.100023100</td>\n",
       "      <td></td>\n",
       "      <td>0.284</td>\n",
       "      <td>19 29 12</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kepler-1142 b</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>+44 04 56</td>\n",
       "      <td>transit</td>\n",
       "      <td>2016</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.97</td>\n",
       "      <td>0.02</td>\n",
       "      <td>...</td>\n",
       "      <td>Confirmed planets</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>18.302724800</td>\n",
       "      <td></td>\n",
       "      <td>0.131</td>\n",
       "      <td>19 30 51</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kepler-823 b</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>+39 13 13</td>\n",
       "      <td>transit</td>\n",
       "      <td>2016</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.98</td>\n",
       "      <td>0.06</td>\n",
       "      <td>...</td>\n",
       "      <td>Confirmed planets</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>4.168090820</td>\n",
       "      <td></td>\n",
       "      <td>0.140</td>\n",
       "      <td>18 58 20</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kepler-582 b</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>+41 03 25</td>\n",
       "      <td>transit</td>\n",
       "      <td>2016</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.93</td>\n",
       "      <td>0.01</td>\n",
       "      <td>...</td>\n",
       "      <td>Confirmed planets</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>18.492356680</td>\n",
       "      <td></td>\n",
       "      <td>0.328</td>\n",
       "      <td>19 50 01</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HD 222076</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>-70 05 48</td>\n",
       "      <td></td>\n",
       "      <td>2016</td>\n",
       "      <td>83.5</td>\n",
       "      <td>0.08</td>\n",
       "      <td></td>\n",
       "      <td>1.07</td>\n",
       "      <td>0.05</td>\n",
       "      <td>...</td>\n",
       "      <td>Confirmed planets</td>\n",
       "      <td></td>\n",
       "      <td>241.0</td>\n",
       "      <td>871.0</td>\n",
       "      <td>1.56</td>\n",
       "      <td></td>\n",
       "      <td>23 38 08</td>\n",
       "      <td>1.83</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WASP-122 b</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>-42 24 35.1</td>\n",
       "      <td>transit</td>\n",
       "      <td>2015</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.32</td>\n",
       "      <td>...</td>\n",
       "      <td>Confirmed planets</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1.7100567</td>\n",
       "      <td>1.372</td>\n",
       "      <td>1.972</td>\n",
       "      <td>07 13 12.34</td>\n",
       "      <td>0.03107</td>\n",
       "      <td>1960</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FL Lyr b</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>+46 19 26.8632</td>\n",
       "      <td>timing</td>\n",
       "      <td>2015</td>\n",
       "      <td>130</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td>\"Controversial,  Planets in binary systems,  P...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>19 12 04.86170</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HAT-P-21 b</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>+41 01 41</td>\n",
       "      <td>transit</td>\n",
       "      <td>2010</td>\n",
       "      <td>254</td>\n",
       "      <td>0.228</td>\n",
       "      <td></td>\n",
       "      <td>0.947</td>\n",
       "      <td>0.01</td>\n",
       "      <td>...</td>\n",
       "      <td>Confirmed planets</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>4.124461</td>\n",
       "      <td>4.063</td>\n",
       "      <td>1.024</td>\n",
       "      <td>11 25 06</td>\n",
       "      <td>0.0494</td>\n",
       "      <td>1161.5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kepler-1359 b</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>+41 53 34</td>\n",
       "      <td>transit</td>\n",
       "      <td>2016</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.78</td>\n",
       "      <td>0.11</td>\n",
       "      <td>...</td>\n",
       "      <td>Confirmed planets</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>59.497095200</td>\n",
       "      <td></td>\n",
       "      <td>0.252</td>\n",
       "      <td>19 18 09</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HAT-P-67 b</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>+44 46 37.1</td>\n",
       "      <td></td>\n",
       "      <td>2017</td>\n",
       "      <td>320.0</td>\n",
       "      <td>0.24</td>\n",
       "      <td>1.24</td>\n",
       "      <td>1.642</td>\n",
       "      <td>-0.08</td>\n",
       "      <td>...</td>\n",
       "      <td>Confirmed planets</td>\n",
       "      <td></td>\n",
       "      <td>172.0</td>\n",
       "      <td>4.81010205</td>\n",
       "      <td>0.34</td>\n",
       "      <td>2.085</td>\n",
       "      <td>17 06 26.56</td>\n",
       "      <td>0.06505</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HAT-P-34 b</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>+18 06 17.42</td>\n",
       "      <td>transit</td>\n",
       "      <td>2012</td>\n",
       "      <td>257</td>\n",
       "      <td>0.441</td>\n",
       "      <td></td>\n",
       "      <td>1.392</td>\n",
       "      <td>0.22</td>\n",
       "      <td>...</td>\n",
       "      <td>Confirmed planets</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>5.4527</td>\n",
       "      <td>3.328</td>\n",
       "      <td>1.197</td>\n",
       "      <td>20 12 46.885</td>\n",
       "      <td>0.0677</td>\n",
       "      <td>1335.3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kepler-658 b</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>+47 34 43</td>\n",
       "      <td>transit</td>\n",
       "      <td>2016</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.63</td>\n",
       "      <td>-0.04</td>\n",
       "      <td>...</td>\n",
       "      <td>Confirmed planets</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1.287076760</td>\n",
       "      <td></td>\n",
       "      <td>0.145</td>\n",
       "      <td>18 51 56</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>eta Cet b</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>-10 10 56.1570</td>\n",
       "      <td>RV</td>\n",
       "      <td>2014</td>\n",
       "      <td>37.99</td>\n",
       "      <td>0.17</td>\n",
       "      <td></td>\n",
       "      <td>1.7</td>\n",
       "      <td>0.12</td>\n",
       "      <td>...</td>\n",
       "      <td>Confirmed planets</td>\n",
       "      <td></td>\n",
       "      <td>247.2</td>\n",
       "      <td>407.3</td>\n",
       "      <td>2.46</td>\n",
       "      <td></td>\n",
       "      <td>01 08 35.39148</td>\n",
       "      <td>1.27</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>eta Cet c</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>-10 10 56.1570</td>\n",
       "      <td>RV</td>\n",
       "      <td>2014</td>\n",
       "      <td>37.99</td>\n",
       "      <td>0.02</td>\n",
       "      <td></td>\n",
       "      <td>1.7</td>\n",
       "      <td>0.12</td>\n",
       "      <td>...</td>\n",
       "      <td>Confirmed planets</td>\n",
       "      <td></td>\n",
       "      <td>36.67</td>\n",
       "      <td>744.3</td>\n",
       "      <td>3.16</td>\n",
       "      <td></td>\n",
       "      <td>01 08 35.39148</td>\n",
       "      <td>1.93</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HD 108874 b</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>+22 52 47</td>\n",
       "      <td>RV</td>\n",
       "      <td>2003</td>\n",
       "      <td>68.5</td>\n",
       "      <td>0.07</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>0.14</td>\n",
       "      <td>...</td>\n",
       "      <td>Confirmed planets</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>395.4</td>\n",
       "      <td>1.36</td>\n",
       "      <td></td>\n",
       "      <td>12 30 26</td>\n",
       "      <td>1.051</td>\n",
       "      <td>256.8</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HD 108874 c</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>+22 52 47</td>\n",
       "      <td>RV</td>\n",
       "      <td>2005</td>\n",
       "      <td>68.5</td>\n",
       "      <td>0.25</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>0.14</td>\n",
       "      <td>...</td>\n",
       "      <td>Confirmed planets</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1605.8</td>\n",
       "      <td>1.018</td>\n",
       "      <td></td>\n",
       "      <td>12 30 26</td>\n",
       "      <td>2.68</td>\n",
       "      <td>160.2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kepler-1473 b</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>+48 59 46</td>\n",
       "      <td>transit</td>\n",
       "      <td>2016</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1.09</td>\n",
       "      <td>-0.01</td>\n",
       "      <td>...</td>\n",
       "      <td>Confirmed planets</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>14.427355100</td>\n",
       "      <td></td>\n",
       "      <td>0.106</td>\n",
       "      <td>19 22 33</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>20 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 AgeGyr AscendingNodeDeg     Declination DiscoveryMethod  \\\n",
       "PlanetIdentifier                                                           \n",
       "Kepler-337 b                                   +47 09 50         transit   \n",
       "GJ 536                                       -02 39 17.5              RV   \n",
       "Kepler-1094 b                                  +38 16 01         transit   \n",
       "Kepler-1142 b                                  +44 04 56         transit   \n",
       "Kepler-823 b                                   +39 13 13         transit   \n",
       "Kepler-582 b                                   +41 03 25         transit   \n",
       "HD 222076                                      -70 05 48                   \n",
       "WASP-122 b                                   -42 24 35.1         transit   \n",
       "FL Lyr b                                  +46 19 26.8632          timing   \n",
       "HAT-P-21 b                                     +41 01 41         transit   \n",
       "Kepler-1359 b                                  +41 53 34         transit   \n",
       "HAT-P-67 b                                   +44 46 37.1                   \n",
       "HAT-P-34 b                                  +18 06 17.42         transit   \n",
       "Kepler-658 b                                   +47 34 43         transit   \n",
       "eta Cet b                                 -10 10 56.1570              RV   \n",
       "eta Cet c                                 -10 10 56.1570              RV   \n",
       "HD 108874 b                                    +22 52 47              RV   \n",
       "HD 108874 c                                    +22 52 47              RV   \n",
       "Kepler-1473 b                                  +48 59 46         transit   \n",
       "                    NaN              NaN             NaN             NaN   \n",
       "\n",
       "                 DiscoveryYear DistFromSunParsec Eccentricity HostStarAgeGyr  \\\n",
       "PlanetIdentifier                                                               \n",
       "Kepler-337 b              2014            674.39                               \n",
       "GJ 536                    2016             10.03         0.08                  \n",
       "Kepler-1094 b             2016                                                 \n",
       "Kepler-1142 b             2016                                                 \n",
       "Kepler-823 b              2016                                                 \n",
       "Kepler-582 b              2016                                                 \n",
       "HD 222076                 2016              83.5         0.08                  \n",
       "WASP-122 b                2015                              0            5.0   \n",
       "FL Lyr b                  2015               130                               \n",
       "HAT-P-21 b                2010               254        0.228                  \n",
       "Kepler-1359 b             2016                                                 \n",
       "HAT-P-67 b                2017             320.0         0.24           1.24   \n",
       "HAT-P-34 b                2012               257        0.441                  \n",
       "Kepler-658 b              2016                                                 \n",
       "eta Cet b                 2014             37.99         0.17                  \n",
       "eta Cet c                 2014             37.99         0.02                  \n",
       "HD 108874 b               2003              68.5         0.07                  \n",
       "HD 108874 c               2005              68.5         0.25                  \n",
       "Kepler-1473 b             2016                                                 \n",
       "                           NaN               NaN          NaN            NaN   \n",
       "\n",
       "                 HostStarMassSlrMass HostStarMetallicity   ...     \\\n",
       "PlanetIdentifier                                           ...      \n",
       "Kepler-337 b                  1.0210                       ...      \n",
       "GJ 536                          0.52               -0.08   ...      \n",
       "Kepler-1094 b                   1.14                0.06   ...      \n",
       "Kepler-1142 b                   0.97                0.02   ...      \n",
       "Kepler-823 b                    0.98                0.06   ...      \n",
       "Kepler-582 b                    0.93                0.01   ...      \n",
       "HD 222076                       1.07                0.05   ...      \n",
       "WASP-122 b                       1.4                0.32   ...      \n",
       "FL Lyr b                                                   ...      \n",
       "HAT-P-21 b                     0.947                0.01   ...      \n",
       "Kepler-1359 b                   0.78                0.11   ...      \n",
       "HAT-P-67 b                     1.642               -0.08   ...      \n",
       "HAT-P-34 b                     1.392                0.22   ...      \n",
       "Kepler-658 b                    0.63               -0.04   ...      \n",
       "eta Cet b                        1.7                0.12   ...      \n",
       "eta Cet c                        1.7                0.12   ...      \n",
       "HD 108874 b                        1                0.14   ...      \n",
       "HD 108874 c                        1                0.14   ...      \n",
       "Kepler-1473 b                   1.09               -0.01   ...      \n",
       "                                 NaN                 NaN   ...      \n",
       "\n",
       "                                                    ListsPlanetIsOn  \\\n",
       "PlanetIdentifier                                                      \n",
       "Kepler-337 b                                      Confirmed planets   \n",
       "GJ 536                                            Confirmed planets   \n",
       "Kepler-1094 b                                     Confirmed planets   \n",
       "Kepler-1142 b                                     Confirmed planets   \n",
       "Kepler-823 b                                      Confirmed planets   \n",
       "Kepler-582 b                                      Confirmed planets   \n",
       "HD 222076                                         Confirmed planets   \n",
       "WASP-122 b                                        Confirmed planets   \n",
       "FL Lyr b          \"Controversial,  Planets in binary systems,  P...   \n",
       "HAT-P-21 b                                        Confirmed planets   \n",
       "Kepler-1359 b                                     Confirmed planets   \n",
       "HAT-P-67 b                                        Confirmed planets   \n",
       "HAT-P-34 b                                        Confirmed planets   \n",
       "Kepler-658 b                                      Confirmed planets   \n",
       "eta Cet b                                         Confirmed planets   \n",
       "eta Cet c                                         Confirmed planets   \n",
       "HD 108874 b                                       Confirmed planets   \n",
       "HD 108874 c                                       Confirmed planets   \n",
       "Kepler-1473 b                                     Confirmed planets   \n",
       "                                                                NaN   \n",
       "\n",
       "                 LongitudeDeg PeriastronDeg    PeriodDays PlanetaryMassJpt  \\\n",
       "PlanetIdentifier                                                             \n",
       "Kepler-337 b                                     3.292781                    \n",
       "GJ 536                                288.7        8.7076           0.0169   \n",
       "Kepler-1094 b                                78.100023100                    \n",
       "Kepler-1142 b                                18.302724800                    \n",
       "Kepler-823 b                                  4.168090820                    \n",
       "Kepler-582 b                                 18.492356680                    \n",
       "HD 222076                             241.0         871.0             1.56   \n",
       "WASP-122 b                                      1.7100567            1.372   \n",
       "FL Lyr b                                                                     \n",
       "HAT-P-21 b                                       4.124461            4.063   \n",
       "Kepler-1359 b                                59.497095200                    \n",
       "HAT-P-67 b                            172.0    4.81010205             0.34   \n",
       "HAT-P-34 b                                         5.4527            3.328   \n",
       "Kepler-658 b                                  1.287076760                    \n",
       "eta Cet b                             247.2         407.3             2.46   \n",
       "eta Cet c                             36.67         744.3             3.16   \n",
       "HD 108874 b                                         395.4             1.36   \n",
       "HD 108874 c                                        1605.8            1.018   \n",
       "Kepler-1473 b                                14.427355100                    \n",
       "                          NaN           NaN           NaN              NaN   \n",
       "\n",
       "                 RadiusJpt  RightAscension SemiMajorAxisAU SurfaceTempK  \\\n",
       "PlanetIdentifier                                                          \n",
       "Kepler-337 b       0.14034        19 20 14                                \n",
       "GJ 536                         14 01 03.19         0.06661                \n",
       "Kepler-1094 b        0.284        19 29 12                                \n",
       "Kepler-1142 b        0.131        19 30 51                                \n",
       "Kepler-823 b         0.140        18 58 20                                \n",
       "Kepler-582 b         0.328        19 50 01                                \n",
       "HD 222076                         23 38 08            1.83                \n",
       "WASP-122 b           1.972     07 13 12.34         0.03107         1960   \n",
       "FL Lyr b                    19 12 04.86170                                \n",
       "HAT-P-21 b           1.024        11 25 06          0.0494       1161.5   \n",
       "Kepler-1359 b        0.252        19 18 09                                \n",
       "HAT-P-67 b           2.085     17 06 26.56         0.06505                \n",
       "HAT-P-34 b           1.197    20 12 46.885          0.0677       1335.3   \n",
       "Kepler-658 b         0.145        18 51 56                                \n",
       "eta Cet b                   01 08 35.39148            1.27                \n",
       "eta Cet c                   01 08 35.39148            1.93                \n",
       "HD 108874 b                       12 30 26           1.051        256.8   \n",
       "HD 108874 c                       12 30 26            2.68        160.2   \n",
       "Kepler-1473 b        0.106        19 22 33                                \n",
       "                       NaN             NaN             NaN          NaN   \n",
       "\n",
       "                 TypeFlag  \n",
       "PlanetIdentifier           \n",
       "Kepler-337 b            0  \n",
       "GJ 536                  0  \n",
       "Kepler-1094 b           0  \n",
       "Kepler-1142 b           0  \n",
       "Kepler-823 b            0  \n",
       "Kepler-582 b            0  \n",
       "HD 222076               0  \n",
       "WASP-122 b              0  \n",
       "FL Lyr b                1  \n",
       "HAT-P-21 b              0  \n",
       "Kepler-1359 b           0  \n",
       "HAT-P-67 b              0  \n",
       "HAT-P-34 b              0  \n",
       "Kepler-658 b            0  \n",
       "eta Cet b               0  \n",
       "eta Cet c               0  \n",
       "HD 108874 b             0  \n",
       "HD 108874 c             0  \n",
       "Kepler-1473 b           0  \n",
       "                      NaN  \n",
       "\n",
       "[20 rows x 24 columns]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "raw_df = pd.DataFrame(dat_list).set_index('PlanetIdentifier')\n",
    "raw_df.tail(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Kepler-953 b&c have duplicates as found in the host star loop so they are cleaned below"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AgeGyr</th>\n",
       "      <th>AscendingNodeDeg</th>\n",
       "      <th>Declination</th>\n",
       "      <th>DiscoveryMethod</th>\n",
       "      <th>DiscoveryYear</th>\n",
       "      <th>DistFromSunParsec</th>\n",
       "      <th>Eccentricity</th>\n",
       "      <th>HostStarAgeGyr</th>\n",
       "      <th>HostStarMassSlrMass</th>\n",
       "      <th>HostStarMetallicity</th>\n",
       "      <th>...</th>\n",
       "      <th>ListsPlanetIsOn</th>\n",
       "      <th>LongitudeDeg</th>\n",
       "      <th>PeriastronDeg</th>\n",
       "      <th>PeriodDays</th>\n",
       "      <th>PlanetaryMassJpt</th>\n",
       "      <th>RadiusJpt</th>\n",
       "      <th>RightAscension</th>\n",
       "      <th>SemiMajorAxisAU</th>\n",
       "      <th>SurfaceTempK</th>\n",
       "      <th>TypeFlag</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PlanetIdentifier</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></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>Kepler-953 c</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>+44 37 28</td>\n",
       "      <td>transit</td>\n",
       "      <td>2016</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.94</td>\n",
       "      <td>0.015</td>\n",
       "      <td>...</td>\n",
       "      <td>Confirmed planets</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>9.10967112</td>\n",
       "      <td></td>\n",
       "      <td>0.106</td>\n",
       "      <td>19 15 53</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kepler-953 c</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>+44 37 28</td>\n",
       "      <td>transit</td>\n",
       "      <td>2016</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.95</td>\n",
       "      <td>0.26</td>\n",
       "      <td>...</td>\n",
       "      <td>Confirmed planets</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>9.109671120</td>\n",
       "      <td></td>\n",
       "      <td>0.106</td>\n",
       "      <td>19 15 53</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 AgeGyr AscendingNodeDeg Declination DiscoveryMethod  \\\n",
       "PlanetIdentifier                                                       \n",
       "Kepler-953 c                               +44 37 28         transit   \n",
       "Kepler-953 c                               +44 37 28         transit   \n",
       "\n",
       "                 DiscoveryYear DistFromSunParsec Eccentricity HostStarAgeGyr  \\\n",
       "PlanetIdentifier                                                               \n",
       "Kepler-953 c              2016                                                 \n",
       "Kepler-953 c              2016                                                 \n",
       "\n",
       "                 HostStarMassSlrMass HostStarMetallicity   ...     \\\n",
       "PlanetIdentifier                                           ...      \n",
       "Kepler-953 c                    0.94               0.015   ...      \n",
       "Kepler-953 c                    0.95                0.26   ...      \n",
       "\n",
       "                    ListsPlanetIsOn LongitudeDeg PeriastronDeg   PeriodDays  \\\n",
       "PlanetIdentifier                                                              \n",
       "Kepler-953 c      Confirmed planets                              9.10967112   \n",
       "Kepler-953 c      Confirmed planets                             9.109671120   \n",
       "\n",
       "                 PlanetaryMassJpt RadiusJpt RightAscension SemiMajorAxisAU  \\\n",
       "PlanetIdentifier                                                             \n",
       "Kepler-953 c                          0.106       19 15 53                   \n",
       "Kepler-953 c                          0.106       19 15 53                   \n",
       "\n",
       "                 SurfaceTempK TypeFlag  \n",
       "PlanetIdentifier                        \n",
       "Kepler-953 c                         0  \n",
       "Kepler-953 c                         0  \n",
       "\n",
       "[2 rows x 24 columns]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "raw_df.loc['Kepler-953 c']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "AgeGyr                                   \n",
       "AscendingNodeDeg                         \n",
       "Declination                     +44 37 28\n",
       "DiscoveryMethod                   transit\n",
       "DiscoveryYear                        2016\n",
       "DistFromSunParsec                        \n",
       "Eccentricity                             \n",
       "HostStarAgeGyr                           \n",
       "HostStarMassSlrMass                  0.95\n",
       "HostStarMetallicity                  0.26\n",
       "HostStarRadiusSlrRad                 0.99\n",
       "HostStarTempK                      5416.0\n",
       "InclinationDeg                           \n",
       "LastUpdated                      16/05/10\n",
       "ListsPlanetIsOn         Confirmed planets\n",
       "LongitudeDeg                             \n",
       "PeriastronDeg                            \n",
       "PeriodDays                    9.109671120\n",
       "PlanetaryMassJpt                         \n",
       "RadiusJpt                           0.106\n",
       "RightAscension                   19 15 53\n",
       "SemiMajorAxisAU                          \n",
       "SurfaceTempK                             \n",
       "TypeFlag                                0\n",
       "Name: Kepler-953 c, dtype: object"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "#columns_to_copy = ['SemiMajorAxisAU','InclinationDeg']\n",
    "new_keplerc_row = raw_df.loc['Kepler-953 c'].iloc[1] #the first is for some other planet\n",
    "new_keplerc_row\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "AgeGyr                                   \n",
       "AscendingNodeDeg                         \n",
       "Declination                     +44 37 28\n",
       "DiscoveryMethod                   transit\n",
       "DiscoveryYear                        2016\n",
       "DistFromSunParsec                        \n",
       "Eccentricity                             \n",
       "HostStarAgeGyr                           \n",
       "HostStarMassSlrMass                  0.95\n",
       "HostStarMetallicity                  0.26\n",
       "HostStarRadiusSlrRad                 0.99\n",
       "HostStarTempK                      5416.0\n",
       "InclinationDeg                      89.53\n",
       "LastUpdated                      16/05/10\n",
       "ListsPlanetIsOn         Confirmed planets\n",
       "LongitudeDeg                             \n",
       "PeriastronDeg                            \n",
       "PeriodDays                   88.406552580\n",
       "PlanetaryMassJpt                         \n",
       "RadiusJpt                           0.376\n",
       "RightAscension                   19 15 53\n",
       "SemiMajorAxisAU                   0.19757\n",
       "SurfaceTempK                             \n",
       "TypeFlag                                0\n",
       "Name: Kepler-953 b, dtype: object"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns_to_copy = ['SemiMajorAxisAU','InclinationDeg']\n",
    "new_keplerb_row = raw_df.loc['Kepler-953 b'].iloc[1] #the first is for some other planet\n",
    "for column in columns_to_copy:\n",
    "    new_keplerb_row[column] = raw_df.loc['Kepler-953 b'].iloc[0][column]\n",
    "new_keplerb_row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AgeGyr</th>\n",
       "      <th>AscendingNodeDeg</th>\n",
       "      <th>Declination</th>\n",
       "      <th>DiscoveryMethod</th>\n",
       "      <th>DiscoveryYear</th>\n",
       "      <th>DistFromSunParsec</th>\n",
       "      <th>Eccentricity</th>\n",
       "      <th>HostStarAgeGyr</th>\n",
       "      <th>HostStarMassSlrMass</th>\n",
       "      <th>HostStarMetallicity</th>\n",
       "      <th>...</th>\n",
       "      <th>ListsPlanetIsOn</th>\n",
       "      <th>LongitudeDeg</th>\n",
       "      <th>PeriastronDeg</th>\n",
       "      <th>PeriodDays</th>\n",
       "      <th>PlanetaryMassJpt</th>\n",
       "      <th>RadiusJpt</th>\n",
       "      <th>RightAscension</th>\n",
       "      <th>SemiMajorAxisAU</th>\n",
       "      <th>SurfaceTempK</th>\n",
       "      <th>TypeFlag</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PlanetIdentifier</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></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>HD 108874 b</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>+22 52 47</td>\n",
       "      <td>RV</td>\n",
       "      <td>2003</td>\n",
       "      <td>68.5</td>\n",
       "      <td>0.07</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>0.14</td>\n",
       "      <td>...</td>\n",
       "      <td>Confirmed planets</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>395.4</td>\n",
       "      <td>1.36</td>\n",
       "      <td></td>\n",
       "      <td>12 30 26</td>\n",
       "      <td>1.051</td>\n",
       "      <td>256.8</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HD 108874 c</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>+22 52 47</td>\n",
       "      <td>RV</td>\n",
       "      <td>2005</td>\n",
       "      <td>68.5</td>\n",
       "      <td>0.25</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>0.14</td>\n",
       "      <td>...</td>\n",
       "      <td>Confirmed planets</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1605.8</td>\n",
       "      <td>1.018</td>\n",
       "      <td></td>\n",
       "      <td>12 30 26</td>\n",
       "      <td>2.68</td>\n",
       "      <td>160.2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kepler-1473 b</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>+48 59 46</td>\n",
       "      <td>transit</td>\n",
       "      <td>2016</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1.09</td>\n",
       "      <td>-0.01</td>\n",
       "      <td>...</td>\n",
       "      <td>Confirmed planets</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>14.427355100</td>\n",
       "      <td></td>\n",
       "      <td>0.106</td>\n",
       "      <td>19 22 33</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kepler-953 b</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>+44 37 28</td>\n",
       "      <td>transit</td>\n",
       "      <td>2016</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.95</td>\n",
       "      <td>0.26</td>\n",
       "      <td>...</td>\n",
       "      <td>Confirmed planets</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>88.406552580</td>\n",
       "      <td></td>\n",
       "      <td>0.376</td>\n",
       "      <td>19 15 53</td>\n",
       "      <td>0.19757</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kepler-953 c</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>+44 37 28</td>\n",
       "      <td>transit</td>\n",
       "      <td>2016</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.95</td>\n",
       "      <td>0.26</td>\n",
       "      <td>...</td>\n",
       "      <td>Confirmed planets</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>9.109671120</td>\n",
       "      <td></td>\n",
       "      <td>0.106</td>\n",
       "      <td>19 15 53</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 AgeGyr AscendingNodeDeg Declination DiscoveryMethod  \\\n",
       "PlanetIdentifier                                                       \n",
       "HD 108874 b                                +22 52 47              RV   \n",
       "HD 108874 c                                +22 52 47              RV   \n",
       "Kepler-1473 b                              +48 59 46         transit   \n",
       "Kepler-953 b                               +44 37 28         transit   \n",
       "Kepler-953 c                               +44 37 28         transit   \n",
       "\n",
       "                 DiscoveryYear DistFromSunParsec Eccentricity HostStarAgeGyr  \\\n",
       "PlanetIdentifier                                                               \n",
       "HD 108874 b               2003              68.5         0.07                  \n",
       "HD 108874 c               2005              68.5         0.25                  \n",
       "Kepler-1473 b             2016                                                 \n",
       "Kepler-953 b              2016                                                 \n",
       "Kepler-953 c              2016                                                 \n",
       "\n",
       "                 HostStarMassSlrMass HostStarMetallicity   ...     \\\n",
       "PlanetIdentifier                                           ...      \n",
       "HD 108874 b                        1                0.14   ...      \n",
       "HD 108874 c                        1                0.14   ...      \n",
       "Kepler-1473 b                   1.09               -0.01   ...      \n",
       "Kepler-953 b                    0.95                0.26   ...      \n",
       "Kepler-953 c                    0.95                0.26   ...      \n",
       "\n",
       "                    ListsPlanetIsOn LongitudeDeg PeriastronDeg    PeriodDays  \\\n",
       "PlanetIdentifier                                                               \n",
       "HD 108874 b       Confirmed planets                                    395.4   \n",
       "HD 108874 c       Confirmed planets                                   1605.8   \n",
       "Kepler-1473 b     Confirmed planets                             14.427355100   \n",
       "Kepler-953 b      Confirmed planets                             88.406552580   \n",
       "Kepler-953 c      Confirmed planets                              9.109671120   \n",
       "\n",
       "                 PlanetaryMassJpt RadiusJpt RightAscension SemiMajorAxisAU  \\\n",
       "PlanetIdentifier                                                             \n",
       "HD 108874 b                  1.36                 12 30 26           1.051   \n",
       "HD 108874 c                 1.018                 12 30 26            2.68   \n",
       "Kepler-1473 b                         0.106       19 22 33                   \n",
       "Kepler-953 b                          0.376       19 15 53         0.19757   \n",
       "Kepler-953 c                          0.106       19 15 53                   \n",
       "\n",
       "                 SurfaceTempK TypeFlag  \n",
       "PlanetIdentifier                        \n",
       "HD 108874 b             256.8        0  \n",
       "HD 108874 c             160.2        0  \n",
       "Kepler-1473 b                        0  \n",
       "Kepler-953 b                         0  \n",
       "Kepler-953 c                         0  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = raw_df.drop('Kepler-953 b').drop('Kepler-953 c')\n",
    "df.loc['Kepler-953 b'] = new_keplerb_row\n",
    "df.loc['Kepler-953 c'] = new_keplerc_row\n",
    "\n",
    "df = df.dropna(how='all') # get rid of bottom allnan row\n",
    "\n",
    "df.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add HostStar Column\n",
    "3 basic types of planet name declarations   \n",
    "1. \"HostStar letter\"   \n",
    "2. \"HostStar.num\"   \n",
    "3. \"Planet\"   \n",
    "filtered below to pull host star "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0  -  Kepler-91 b Trojan\n",
      "0  -  SDSS J1110+0116\n",
      "2  -  TrES-2\n",
      "0  -  SIMP0136+0933\n",
      "3  -  PSO J318.5-22\n",
      "3  -  CFBDSIR2149\n",
      "0  -  OGLE-2014-BLG-0676L\n",
      "1  -  HD 131399 Ab\n",
      "0  -  SWEEPS-11\n",
      "0  -  KOI-72.X\n",
      "0  -  HD 86950\n",
      "0  -  HD 220842\n",
      "0  -  OGLE-2013-BLG-0102LB\n",
      "0  -  EPIC 220504338\n",
      "0  -  MOA-2011-BLG-262Lb\n",
      "3  -  WISE 0855-0714\n",
      "0  -  2MASS J21265040-8140293\n",
      "0  -  SWEEPS-4\n",
      "0  -  Lupus-TR 3b\n",
      "0  -  HIP 65426\n",
      "0  -  GJ 536\n",
      "0  -  HD 222076\n",
      "3656\n"
     ]
    }
   ],
   "source": [
    "#df.loc['Earth']\n",
    "#filtering for star column\n",
    "planets = df.index.values.tolist() #filtering this way reveals duplicates\n",
    "\n",
    "our_planets = [\n",
    "    'Mercury',\n",
    "    'Venus',\n",
    "    'Earth',\n",
    "    'Mars',\n",
    "    'Jupiter',\n",
    "    'Saturn',\n",
    "    'Uranus',\n",
    "    'Neptune',\n",
    "    'Pluto'\n",
    "]\n",
    "sun_column = []\n",
    "sun_list = []\n",
    "\n",
    "for index,planet in enumerate(planets):\n",
    "    #ensure index is right\n",
    "    #print(df.loc[planet,'PlanetaryMassJpt'])\n",
    "    #print(df.iloc[index, 1])\n",
    "    try:\n",
    "        if df.loc[planet,'TypeFlag'] == 3:\n",
    "            # TypeFlag 3 means lone planet without sun, could be none but would inhibit group_by\n",
    "            sun = planet\n",
    "        elif planet in our_planets:\n",
    "            sun = 'sun'\n",
    "        elif planet[-2] == ' ':\n",
    "            sun = planet[:-2]\n",
    "        elif planet[-3] == '.':\n",
    "            sun = planet[:-3]\n",
    "        else:\n",
    "            print(df.loc[planet,'TypeFlag'], ' - ', planet) #shouldn't list any 3s but does\n",
    "            sun = planet\n",
    "        \n",
    "        sun_column.append(sun)\n",
    "        if sun not in sun_list:\n",
    "            sun_list.append(sun)\n",
    "            \n",
    "    except IndexError:\n",
    "        print(df.loc[planet])\n",
    "    #print(planet)\n",
    "\n",
    "print(index)\n",
    "#print(sun_column)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AgeGyr</th>\n",
       "      <th>AscendingNodeDeg</th>\n",
       "      <th>Declination</th>\n",
       "      <th>DiscoveryMethod</th>\n",
       "      <th>DiscoveryYear</th>\n",
       "      <th>DistFromSunParsec</th>\n",
       "      <th>Eccentricity</th>\n",
       "      <th>HostStarAgeGyr</th>\n",
       "      <th>HostStarMassSlrMass</th>\n",
       "      <th>HostStarMetallicity</th>\n",
       "      <th>...</th>\n",
       "      <th>LongitudeDeg</th>\n",
       "      <th>PeriastronDeg</th>\n",
       "      <th>PeriodDays</th>\n",
       "      <th>PlanetaryMassJpt</th>\n",
       "      <th>RadiusJpt</th>\n",
       "      <th>RightAscension</th>\n",
       "      <th>SemiMajorAxisAU</th>\n",
       "      <th>SurfaceTempK</th>\n",
       "      <th>TypeFlag</th>\n",
       "      <th>HostStar</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PlanetIdentifier</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></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>KOI-1843.03</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>+40 13 14.7</td>\n",
       "      <td>transit</td>\n",
       "      <td>2012</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.52</td>\n",
       "      <td>0.07</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.1768913</td>\n",
       "      <td>0.0014</td>\n",
       "      <td>0.054</td>\n",
       "      <td>19 00 03.14</td>\n",
       "      <td>0.0048</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>KOI-1843</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kepler-974 b</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>+40 13 14.7</td>\n",
       "      <td>transit</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.52</td>\n",
       "      <td>0.07</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>4.194496510</td>\n",
       "      <td></td>\n",
       "      <td>0.140</td>\n",
       "      <td>19 00 03.14</td>\n",
       "      <td>0.039</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>Kepler-974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KOI-1843.02</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>+40 13 14.7</td>\n",
       "      <td>transit</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.52</td>\n",
       "      <td>0.07</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>6.356006</td>\n",
       "      <td></td>\n",
       "      <td>0.071</td>\n",
       "      <td>19 00 03.14</td>\n",
       "      <td>0.052</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>KOI-1843</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kepler-9 b</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>+38 24 03</td>\n",
       "      <td>transit</td>\n",
       "      <td>2010</td>\n",
       "      <td>650</td>\n",
       "      <td>0.0626</td>\n",
       "      <td></td>\n",
       "      <td>1.07</td>\n",
       "      <td>0.12</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>19.22418</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.84</td>\n",
       "      <td>19 02 17</td>\n",
       "      <td>0.143</td>\n",
       "      <td>707.2</td>\n",
       "      <td>0</td>\n",
       "      <td>Kepler-9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kepler-9 c</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>+38 24 03</td>\n",
       "      <td>transit</td>\n",
       "      <td>2010</td>\n",
       "      <td>650</td>\n",
       "      <td>0.0684</td>\n",
       "      <td></td>\n",
       "      <td>1.07</td>\n",
       "      <td>0.12</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>39.03106</td>\n",
       "      <td>0.17</td>\n",
       "      <td>0.82</td>\n",
       "      <td>19 02 17</td>\n",
       "      <td>0.229</td>\n",
       "      <td>557.9</td>\n",
       "      <td>0</td>\n",
       "      <td>Kepler-9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 AgeGyr AscendingNodeDeg  Declination DiscoveryMethod  \\\n",
       "PlanetIdentifier                                                        \n",
       "KOI-1843.03                               +40 13 14.7         transit   \n",
       "Kepler-974 b                              +40 13 14.7         transit   \n",
       "KOI-1843.02                               +40 13 14.7         transit   \n",
       "Kepler-9 b                                  +38 24 03         transit   \n",
       "Kepler-9 c                                  +38 24 03         transit   \n",
       "\n",
       "                 DiscoveryYear DistFromSunParsec Eccentricity HostStarAgeGyr  \\\n",
       "PlanetIdentifier                                                               \n",
       "KOI-1843.03               2012                                                 \n",
       "Kepler-974 b                                                                   \n",
       "KOI-1843.02                                                                    \n",
       "Kepler-9 b                2010               650       0.0626                  \n",
       "Kepler-9 c                2010               650       0.0684                  \n",
       "\n",
       "                 HostStarMassSlrMass HostStarMetallicity     ...      \\\n",
       "PlanetIdentifier                                             ...       \n",
       "KOI-1843.03                     0.52                0.07     ...       \n",
       "Kepler-974 b                    0.52                0.07     ...       \n",
       "KOI-1843.02                     0.52                0.07     ...       \n",
       "Kepler-9 b                      1.07                0.12     ...       \n",
       "Kepler-9 c                      1.07                0.12     ...       \n",
       "\n",
       "                 LongitudeDeg PeriastronDeg   PeriodDays PlanetaryMassJpt  \\\n",
       "PlanetIdentifier                                                            \n",
       "KOI-1843.03                                    0.1768913           0.0014   \n",
       "Kepler-974 b                                 4.194496510                    \n",
       "KOI-1843.02                                     6.356006                    \n",
       "Kepler-9 b                                      19.22418             0.25   \n",
       "Kepler-9 c                                      39.03106             0.17   \n",
       "\n",
       "                 RadiusJpt RightAscension SemiMajorAxisAU SurfaceTempK  \\\n",
       "PlanetIdentifier                                                         \n",
       "KOI-1843.03          0.054    19 00 03.14          0.0048                \n",
       "Kepler-974 b         0.140    19 00 03.14           0.039                \n",
       "KOI-1843.02          0.071    19 00 03.14           0.052                \n",
       "Kepler-9 b            0.84       19 02 17           0.143        707.2   \n",
       "Kepler-9 c            0.82       19 02 17           0.229        557.9   \n",
       "\n",
       "                 TypeFlag    HostStar  \n",
       "PlanetIdentifier                       \n",
       "KOI-1843.03             0    KOI-1843  \n",
       "Kepler-974 b            0  Kepler-974  \n",
       "KOI-1843.02             0    KOI-1843  \n",
       "Kepler-9 b              0    Kepler-9  \n",
       "Kepler-9 c              0    Kepler-9  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['HostStar'] = sun_column\n",
    "#check that the HostStar column lines up\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df.to_csv('cleaned_data.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "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.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}