Summary_Table.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This notebook has been abstracted from work done elsewhere to provide a clean workflow for producing a summary table. This notebook is particularly useful for querying the SIMBAD database for specific data of interest for a list of Kepler objects."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pickle"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('scores/Average','rb') as f:\n",
    "    avscores = pickle.load(f).set_index('KIC')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from astroquery.simbad import Simbad"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "Simbad.reset_votable_fields()\n",
    "Simbad.remove_votable_fields('main_id')\n",
    "Simbad.add_votable_fields('typed_id')\n",
    "Simbad.remove_votable_fields('coordinates')\n",
    "Simbad.add_votable_fields('otype')\n",
    "Simbad.add_votable_fields('coo')\n",
    "Simbad.add_votable_fields('biblio')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "from astropy.table import Table,vstack"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "nchunks = int(len(avscores)/1000)\n",
    "chunks = [range(i*1000,i*1000+1000) for i in range(nchunks)]\n",
    "lastchunk = range(nchunks*1000,nchunks*1000+len(avscores)%1000)\n",
    "chunks.append(lastchunk)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Output for follwoing block is suppressed, nonfatal errors are produced for every object that isn't in SIMBAD. Otherwise, the connection is occasionally reset, in which case it tries again until the connection is reestablished. The chunk size should prevent this from overwhelming the server and it *shouldn't* result in being temporarily blacklisted for submitting too many queries at once... Use at own risk."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def simbadquery(avscores,chunk,result_table):\n",
    "    try:\n",
    "        chunktable = Simbad.query_objects(['KIC{}'.format(i) for i in avscores.index[chunk]])\n",
    "        chunktable.remove_columns(['COO_ERR_MAJA','COO_ERR_MINA','COO_ERR_ANGLE'])\n",
    "        result_table = vstack([result_table,chunktable])\n",
    "    except ConnectionError:\n",
    "        simbadquery(avscores,chunk,result_table)\n",
    "        \n",
    "    return result_table\n",
    "\n",
    "result_table = Simbad.query_objects(['KIC{}'.format(i) for i in avscores.index[chunks[0]]])\n",
    "for chunk in chunks[72:]:\n",
    "    result_table = simbadquery(avscores,chunk,result_table)\n",
    "\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [],
   "source": [
    "simbad_summary_df = result_table.to_pandas().set_index(avscores.index).drop(columns='TYPED_ID')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "metadata": {},
   "outputs": [],
   "source": [
    "simbad_summary_df['OTYPE']=simbad_summary_df.loc[:,'OTYPE'].apply(lambda x:x.decode())\n",
    "simbad_summary_df['BIBLIO']=simbad_summary_df.loc[:,'BIBLIO'].apply(lambda x:x.decode())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "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>OTYPE</th>\n",
       "      <th>RA</th>\n",
       "      <th>DEC</th>\n",
       "      <th>BIBLIO</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KIC</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>757076</th>\n",
       "      <td>Star</td>\n",
       "      <td>19 24 09.2898</td>\n",
       "      <td>+36 35 53.121</td>\n",
       "      <td>2017ApJS..229...30M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>757099</th>\n",
       "      <td>Cepheid</td>\n",
       "      <td>19 24 10.3300</td>\n",
       "      <td>+36 35 37.602</td>\n",
       "      <td>2019MNRAS.484..834G|2016ApJ...829...23D|2014Ap...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>757137</th>\n",
       "      <td>Eruptive*</td>\n",
       "      <td>19 24 13.4198</td>\n",
       "      <td>+36 33 35.724</td>\n",
       "      <td>2018ApJS..236...42Y|2016ApJ...829...23D</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>757280</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>757450</th>\n",
       "      <td>RotV*</td>\n",
       "      <td>19 24 33.0185</td>\n",
       "      <td>+36 34 38.477</td>\n",
       "      <td>2019AJ....158...59S|2019MNRAS.482.1379H|2019MN...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100004295</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100004297</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100004298</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100004299</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100004300</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>201266 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               OTYPE             RA            DEC  \\\n",
       "KIC                                                  \n",
       "757076          Star  19 24 09.2898  +36 35 53.121   \n",
       "757099       Cepheid  19 24 10.3300  +36 35 37.602   \n",
       "757137     Eruptive*  19 24 13.4198  +36 33 35.724   \n",
       "757280                                               \n",
       "757450         RotV*  19 24 33.0185  +36 34 38.477   \n",
       "...              ...            ...            ...   \n",
       "100004295                                            \n",
       "100004297                                            \n",
       "100004298                                            \n",
       "100004299                                            \n",
       "100004300                                            \n",
       "\n",
       "                                                      BIBLIO  \n",
       "KIC                                                           \n",
       "757076                                   2017ApJS..229...30M  \n",
       "757099     2019MNRAS.484..834G|2016ApJ...829...23D|2014Ap...  \n",
       "757137               2018ApJS..236...42Y|2016ApJ...829...23D  \n",
       "757280                                                        \n",
       "757450     2019AJ....158...59S|2019MNRAS.482.1379H|2019MN...  \n",
       "...                                                      ...  \n",
       "100004295                                                     \n",
       "100004297                                                     \n",
       "100004298                                                     \n",
       "100004299                                                     \n",
       "100004300                                                     \n",
       "\n",
       "[201266 rows x 4 columns]"
      ]
     },
     "execution_count": 152,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "simbad_summary_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "summary_df = pd.DataFrame(index=avscores.index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "summary_df['median_rank']=avscores.rank(ascending=False).median(axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "summary_df['most_outlying_rank']=avscores.rank(ascending=False).min(axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from multiprocessing import Pool,cpu_count\n",
    "import pickle\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('scores/Average','rb') as f:\n",
    "    avscores = pickle.load(f).set_index('KIC')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def minq(kic):\n",
    "    minloc = av_scores.rank(ascending=False).loc[kic]==av_scores.rank(ascending=False).min(axis=1).loc[kic]\n",
    "    min_q = av_scores.loc[kic,minloc].index[0]\n",
    "    return min_q\n",
    "global av_scores #Pool.map can only take one variable... so I have to pass avscores as a global\n",
    "av_scores=avscores"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "useCpus = cpu_count()\n",
    "p = Pool(useCpus)\n",
    "min_q = p.map(minq,av_scores.index)\n",
    "p.close()\n",
    "p.join()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "summary_df['most_outlying_quarter']=min_q"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "201266"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(result_table)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "metadata": {},
   "outputs": [],
   "source": [
    "full_summary_df = pd.concat([summary_df,simbad_summary_df],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "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>median_rank</th>\n",
       "      <th>most_outlying_rank</th>\n",
       "      <th>most_outlying_quarter</th>\n",
       "      <th>OTYPE</th>\n",
       "      <th>RA</th>\n",
       "      <th>DEC</th>\n",
       "      <th>BIBLIO</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KIC</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>757076</th>\n",
       "      <td>38099.0</td>\n",
       "      <td>8427.0</td>\n",
       "      <td>Q14</td>\n",
       "      <td>Star</td>\n",
       "      <td>19 24 09.2898</td>\n",
       "      <td>+36 35 53.121</td>\n",
       "      <td>2017ApJS..229...30M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>757099</th>\n",
       "      <td>2530.0</td>\n",
       "      <td>1563.0</td>\n",
       "      <td>Q2</td>\n",
       "      <td>Cepheid</td>\n",
       "      <td>19 24 10.3300</td>\n",
       "      <td>+36 35 37.602</td>\n",
       "      <td>2019MNRAS.484..834G|2016ApJ...829...23D|2014Ap...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>757137</th>\n",
       "      <td>36285.0</td>\n",
       "      <td>13696.0</td>\n",
       "      <td>Q11</td>\n",
       "      <td>Eruptive*</td>\n",
       "      <td>19 24 13.4198</td>\n",
       "      <td>+36 33 35.724</td>\n",
       "      <td>2018ApJS..236...42Y|2016ApJ...829...23D</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>757280</th>\n",
       "      <td>39413.0</td>\n",
       "      <td>8991.0</td>\n",
       "      <td>Q10</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>757450</th>\n",
       "      <td>4610.0</td>\n",
       "      <td>2650.0</td>\n",
       "      <td>Q7</td>\n",
       "      <td>RotV*</td>\n",
       "      <td>19 24 33.0185</td>\n",
       "      <td>+36 34 38.477</td>\n",
       "      <td>2019AJ....158...59S|2019MNRAS.482.1379H|2019MN...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        median_rank  most_outlying_rank most_outlying_quarter      OTYPE  \\\n",
       "KIC                                                                        \n",
       "757076      38099.0              8427.0                   Q14       Star   \n",
       "757099       2530.0              1563.0                    Q2    Cepheid   \n",
       "757137      36285.0             13696.0                   Q11  Eruptive*   \n",
       "757280      39413.0              8991.0                   Q10              \n",
       "757450       4610.0              2650.0                    Q7      RotV*   \n",
       "\n",
       "                   RA            DEC  \\\n",
       "KIC                                    \n",
       "757076  19 24 09.2898  +36 35 53.121   \n",
       "757099  19 24 10.3300  +36 35 37.602   \n",
       "757137  19 24 13.4198  +36 33 35.724   \n",
       "757280                                 \n",
       "757450  19 24 33.0185  +36 34 38.477   \n",
       "\n",
       "                                                   BIBLIO  \n",
       "KIC                                                        \n",
       "757076                                2017ApJS..229...30M  \n",
       "757099  2019MNRAS.484..834G|2016ApJ...829...23D|2014Ap...  \n",
       "757137            2018ApJS..236...42Y|2016ApJ...829...23D  \n",
       "757280                                                     \n",
       "757450  2019AJ....158...59S|2019MNRAS.482.1379H|2019MN...  "
      ]
     },
     "execution_count": 156,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "full_summary_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "metadata": {},
   "outputs": [],
   "source": [
    "full_summary_df.to_csv('KIC_Summary.csv')"
   ]
  }
 ],
 "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
}