BdHNe join with NASA Swift GRBs.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Adding information to BdHNe table\n",
    "\n",
    "One of these days I published the online version of ICRANet Binary-driven Hyper-Novae table of Gamma-Ray Bursts (GRB) at the (BSDC/VO)[http://vo.bsdc.icranet.org] website; the table: http://vo.bsdc.icranet.org/bdhne/q/web/form.\n",
    "The (current) first version of the table contains exactly what is being published in the article (Ruffini et al, in preparation), which is:\n",
    "\n",
    "* GRB (id): the standard name for GRBs (*YYMMDD*[A-Z]);\n",
    "* Designation: as proposed by the authors of the article;\n",
    "* z: redshift;\n",
    "* E_iso: the energy computedby the authors after their model;\n",
    "* Instrument: observatory from where data was used;\n",
    "* GCN: the GCN corresponding to the event.\n",
    "\n",
    "What I'm going to do now is to add position (Right Ascension and Declination) and trigger time of those events.\n",
    "To do so, I should retrieve such information from online services/tables and join them with the current table.\n",
    "\n",
    "Swift GRBs data will be used to get such information (position and trigger time). \n",
    "(*\"--Why Swift? --Because I had to choose one; next time will be Fermi.\"*)\n",
    "After a search for which website could give me better the information, the [Mission's American website][NASAGRB].\n",
    "Through their service I can easily automate the query, retrieve and parsing process, plus they provide all the relevant information I am looking for (almost) all GRBs.\n",
    "For the records, below is the list of websites I looked into; The list was kindly provided by my colleaghe Milos Kovacevic:\n",
    "\n",
    "* http://www.swift.ac.uk/xrt_positions/\n",
    "* https://heasarc.gsfc.nasa.gov/W3Browse/fermi/fermigbrst.html\n",
    "* https://heasarc.gsfc.nasa.gov/W3Browse/all/batsegrb.html\n",
    "* http://www.astro.caltech.edu/grbox/grbox.php\n",
    "* (**Chosen**) https://swift.gsfc.nasa.gov/archive/grb_table/\n",
    "\n",
    "Data and software we already have (properly working/formatted):\n",
    "\n",
    "* The current BdHNe table is here copied as filename `ibdhne.csv`;\n",
    "* The Perl script to retrieve the table from [Swift GRB service][NASAGRB], called `SwiftGRBsNASA.pl`;\n",
    " * We will execute the script to generate an up-to-date table of GRBs named `SwiftGRBsNASA_table.csv`.\n",
    "\n",
    "[NASAGRB]: https://swift.gsfc.nasa.gov/archive/grb_table/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas\n",
    "pandas.set_option('display.max_columns',None)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Output will go in 'tab' dataframe. The output table will contain then the current columns of `ibdhne` plus\n",
    "position -- `ra`, `dec`, `error radius` -- and `trigger time`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# to-be-filled output structure\n",
    "tab = pandas.DataFrame()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The IBdHNe table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         GRB     designation      z   E_iso instrument    GCN\n",
      "0    050315A  IBdHNe 050315A  1.950    8.32      Swift   3099\n",
      "1    050318A  IBdHNe 050318A  1.440    3.70      Swift   3134\n",
      "2    050319A  IBdHNe 050319A  3.240   20.20      Swift   3119\n",
      "3    050401A  IBdHNe 050401A  2.900   92.00         KW   3179\n",
      "4    050408A  IBdHNe 050408A  1.240   10.80       HETE   3188\n",
      "5    050505A  IBdHNe 050505A  4.270   44.60      Swift   3364\n",
      "6    050525A  IBdHNe 050525A  0.606    2.31         KW   3474\n",
      "7    050730A  IBdHNe 050730A  3.970   42.80      Swift   3715\n",
      "8    050802A  IBdHNe 050802A  1.710    7.46      Swift   3737\n",
      "9    050814A  IBdHNe 050814A  5.300   26.80      Swift   3803\n",
      "10   050820A  IBdHNe 050820A  2.610   39.00         KW   3852\n",
      "11   050922C  IBdHNe 050922C  2.200   19.80         KW   4030\n",
      "12   051109A  IBdHNe 051109A  2.350   18.30         KW   4238\n",
      "13   060108A  IBdHNe 060108A  2.030    1.50      Swift   4445\n",
      "14   060115A  IBdHNe 060115A  3.530   19.00      Swift   4518\n",
      "15   060124A  IBdHNe 060124A  2.296   26.00         KW   4599\n",
      "16   060202A  IBdHNe 060202A  0.783    1.90      Swift   4635\n",
      "17   060206A  IBdHNe 060206A  4.050    3.90      Swift   4697\n",
      "18   060210A  IBdHNe 060210A  3.910  120.00      Swift   4734\n",
      "19   060418A  IBdHNe 060418A  1.490   12.00         KW   4989\n",
      "20   060502A  IBdHNe 060502A  1.510   11.00      Swift   5053\n",
      "21   060510B  IBdHNe 060510B  4.900   66.00      Swift   5107\n",
      "22   060512A  IBdHNe 060512A  2.100    2.38      Swift   5124\n",
      "23   060526A  IBdHNe 060526A  3.210    5.40      Swift   5174\n",
      "24   060605A  IBdHNe 060605A  3.800   10.10      Swift   5231\n",
      "25   060607A  IBdHNe 060607A  3.082   34.00      Swift   5242\n",
      "26   060707A  IBdHNe 060707A  3.430    5.30      Swift   5289\n",
      "27   060708A  IBdHNe 060708A  1.920    2.20      Swift   5295\n",
      "28   060714A  IBdHNe 060714A  2.710   19.00      Swift   5334\n",
      "29   060729A  IBdHNe 060729A  0.540    1.60      Swift   5370\n",
      "..       ...             ...    ...     ...        ...    ...\n",
      "143  140703A  IBdHNe 140703A  3.140    1.72      Fermi  16512\n",
      "144  140907A  IBdHNe 140907A  1.210    2.29      Fermi  16798\n",
      "145  141026A  IBdHNe 141026A  3.350    7.17      Swift  16960\n",
      "146  141109A  IBdHNe 141109A  2.993    5.05         KW  17055\n",
      "147  141121A  IBdHNe 141121A  1.470   14.60         KW  17108\n",
      "148  141221A  IBdHNe 141221A  1.452    1.94      Fermi  17216\n",
      "149  150206A  IBdHNe 150206A  2.087  151.00         KW  17427\n",
      "150  150314A  IBdHNe 150314A  1.758   98.10      Fermi  17579\n",
      "151  150403A  IBdHNe 150403A  2.060   91.00      Fermi  17674\n",
      "152  150514A  IBdHNe 150514A  0.807    1.14      Fermi  17819\n",
      "153  150821A  IBdHNe 150821A  0.755   15.10      Fermi  18190\n",
      "154  150910A  IBdHNe 150910A  1.360   22.30      Swift  18268\n",
      "155  151021A  IBdHNe 151021A  2.330  211.00         KW  18433\n",
      "156  151027A  IBdHNe 151027A  0.810    3.86      Fermi  18492\n",
      "157  151027B  IBdHNe 151027B  4.063   19.10      Swift  18514\n",
      "158  151111A  IBdHNe 151111A  3.500    5.13      Fermi  18582\n",
      "159  151112A  IBdHNe 151112A  4.100   12.40      Swift  18593\n",
      "160  151215A  IBdHNe 151215A  2.590    1.97      Swift  18699\n",
      "161  160121A  IBdHNe 160121A  1.960    2.62      Swift  18919\n",
      "162  160131A  IBdHNe 160131A  0.972   60.40         KW  18974\n",
      "163  160203A  IBdHNe 160203A  3.520   12.40      Swift  18998\n",
      "164  160227A  IBdHNe 160227A  2.380    5.69      Swift  19106\n",
      "165  160509A  IBdHNe 160509A  1.170   82.30      Fermi  19411\n",
      "166  160623A  IBdHNe 160623A  0.367   23.30         KW  19554\n",
      "167  160625B  IBdHNe 160625B  1.406  443.00      Fermi  19587\n",
      "168  160804A  IBdHNe 160804A  0.736    2.53      Fermi  19769\n",
      "169  161017A  IBdHNe 161017A  2.013    7.79      Fermi  20068\n",
      "170  161023A  IBdHNe 161023A  2.708   79.80         KW  20111\n",
      "171  161108A  IBdHNe 161108A  1.159    1.71      Swift  20151\n",
      "172  161117A  IBdHNe 161117A  1.549   24.80      Fermi  20192\n",
      "\n",
      "[173 rows x 6 columns]\n"
     ]
    }
   ],
   "source": [
    "ibd = pandas.read_csv('data.csv',sep=';')\n",
    "print ibd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Swift GRBs table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "grbstable = 'SwiftGRBsNASA_table.csv'\n",
    "import os\n",
    "if not os.path.exists(grbstable):\n",
    "    with open(grbstable,'w') as fpout:\n",
    "        import subprocess\n",
    "        import shlex\n",
    "        ret = subprocess.check_call( shlex.split('perl SwiftGRBsNASA.pl'), stdout=fpout, stderr=subprocess.STDOUT)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       GRB    Time|[UT]   Trigger|Number BAT RA|(J2000) BAT Dec|(J2000)  \\\n",
      "0  170325A  07:56:57.95  Ground Analysis        127.483          20.526   \n",
      "1  170318B     15:27:52           743086        284.229           6.335   \n",
      "2  170318A     12:11:56           743065        305.662          28.408   \n",
      "3  170317A     09:45:59           742866         93.087          50.500   \n",
      "4  170311A     08:08:42           741965        280.539         -30.041   \n",
      "5  170307A     20:24:21           741528         13.510           9.538   \n",
      "6  170306A     07:06:12           741220        263.087         -44.765   \n",
      "7  170208B     22:33:38           737463        127.145          -9.027   \n",
      "8  170208A     18:11:16           737438        166.544         -46.786   \n",
      "9  170206B     21:57:25           737125        271.784          11.175   \n",
      "\n",
      "  BAT 90%|Error Radius|[arcmin] BAT T90|[sec]  \\\n",
      "0                           2.0           0.3   \n",
      "1                           3.7         160.0   \n",
      "2                           1.2         133.7   \n",
      "3                           1.0         11.94   \n",
      "4                           2.0          6.32   \n",
      "5                           2.5         56.92   \n",
      "6                           2.4          17.8   \n",
      "7                           1.0        128.07   \n",
      "8                           1.4          7.45   \n",
      "9                           2.2         13.36   \n",
      "\n",
      "  BAT Fluence|(15-150 keV)|[10-7 erg/cm2] XRT RA|(J2000) XRT Dec|(J2000)  \\\n",
      "0                                    0.88            n/a             n/a   \n",
      "1                                     5.5    18:57:13.46      06:17:57.0   \n",
      "2                                      37    20:22:40.12      28:24:21.7   \n",
      "3                                      13    06:12:14.87      50:29:34.9   \n",
      "4                                     2.5    18:42:21.46     -30:02:45.5   \n",
      "5                                      13            n/a             n/a   \n",
      "6                                     8.3    17:32:16.56     -44:44:53.5   \n",
      "7                                      61    08:28:34.55     -09:01:47.7   \n",
      "8                                      15    11:06:15.52     -46:46:05.6   \n",
      "9                                     3.2    18:07:04.95      11:11:37.8   \n",
      "\n",
      "  XRT 90%|Error Radius|[arcsec] XRT Time to First|Observation|[sec]  \\\n",
      "0                           n/a                                 n/a   \n",
      "1                           1.8                               73.72   \n",
      "2                           1.7                              107.98   \n",
      "3                           1.4                               83.30   \n",
      "4                           1.5                              105.10   \n",
      "5                           n/a                                 n/a   \n",
      "6                           3.6                              133.50   \n",
      "7                           1.6                               61.77   \n",
      "8                           1.4                              120.19   \n",
      "9                           1.6                                  88   \n",
      "\n",
      "  XRT Early Flux|(0.3-10 keV)|[10-11 erg/cm2/s] UVOT RA|(J2000)  \\\n",
      "0                                           n/a             n/a   \n",
      "1                                          53.9             n/a   \n",
      "2                                          67.8             n/a   \n",
      "3                                           n/a     06:12:14.82   \n",
      "4                                           n/a             n/a   \n",
      "5                                           n/a             n/a   \n",
      "6                                           n/a             n/a   \n",
      "7                                           716             n/a   \n",
      "8                                           n/a             n/a   \n",
      "9                                           n/a             n/a   \n",
      "\n",
      "  UVOT Dec|(J2000) UVOT 90%|Error Radius|[arcsec]  \\\n",
      "0              n/a                            n/a   \n",
      "1              n/a                            n/a   \n",
      "2              n/a                            n/a   \n",
      "3       50:29:35.2                           0.43   \n",
      "4              n/a                            n/a   \n",
      "5              n/a                            n/a   \n",
      "6              n/a                            n/a   \n",
      "7              n/a                            n/a   \n",
      "8              n/a                            n/a   \n",
      "9              n/a                            n/a   \n",
      "\n",
      "  UVOT Time to|First Observation|[sec] UVOT Magnitude  \\\n",
      "0                                  n/a            n/a   \n",
      "1                                   84         V>19.4   \n",
      "2                                  117         V>17.0   \n",
      "3                                   87        V=18.36   \n",
      "4                                  110         V>20.3   \n",
      "5                                  n/a            n/a   \n",
      "6                                  140         V>18.1   \n",
      "7                                  127         V>19.6   \n",
      "8                                  125         V>19.3   \n",
      "9                                  106         V>18.2   \n",
      "\n",
      "                        Other Observatory Detections Redshift  \\\n",
      "0                                        Fermi (GBM)      NaN   \n",
      "1                                        Fermi (GBM)      NaN   \n",
      "2                                                NaN      NaN   \n",
      "3  SANTEL-650 (0.65m), Xinglong (0.8m), GMG (2.4m...      NaN   \n",
      "4                                                NaN      NaN   \n",
      "5                                        Fermi (GBM)      NaN   \n",
      "6                                         MPG (2.2m)      NaN   \n",
      "7                Konus-Wind, Fermi (GBM), WHT (4.2m)      NaN   \n",
      "8           Fermi (GBM), Konus-Wind, AstroSat (CZTI)      NaN   \n",
      "9                                                NaN      NaN   \n",
      "\n",
      "                                         Host Galaxy  \\\n",
      "0  BAT: the spacecraft was slewing at that time, ...   \n",
      "1                                                NaN   \n",
      "2                                                NaN   \n",
      "3                                                NaN   \n",
      "4                                                NaN   \n",
      "5                                                NaN   \n",
      "6                                                NaN   \n",
      "7                                 MPG: possible host   \n",
      "8                                                NaN   \n",
      "9                                                NaN   \n",
      "\n",
      "                                            Comments  \\\n",
      "0                                                NaN   \n",
      "1                                 UVOT: no detection   \n",
      "2                                 UVOT: no detection   \n",
      "3                                                NaN   \n",
      "4                                                NaN   \n",
      "5  Due to a Sun observing constraint, Swift canno...   \n",
      "6                                 UVOT: no detection   \n",
      "7                                 UVOT: no detection   \n",
      "8                                                NaN   \n",
      "9                                 UVOT: no detection   \n",
      "\n",
      "                                          References  \n",
      "0                                    BAT: GCN 20938|  \n",
      "1  BAT: GCN 20908| GCN 20923|XRT: GCN 20908| GCN ...  \n",
      "2  BAT: GCN 20904| GCN 20912|XRT: GCN 20904| GCN ...  \n",
      "3  BAT: GCN 20885| GCN 20896|XRT: GCN 20885| GCN ...  \n",
      "4  BAT: GCN 20846| GCN 20855|XRT: GCN 20846| GCN ...  \n",
      "5                         BAT: GCN 20832| GCN 20838|  \n",
      "6  BAT: GCN 20819| GCN 20829|XRT: GCN 20819| GCN ...  \n",
      "7  BAT: GCN 20632| GCN 20654|XRT: GCN 20632| GCN ...  \n",
      "8  BAT: GCN 20631| GCN 20653|XRT: GCN 20631| GCN ...  \n",
      "9  BAT: GCN 20618| GCN 20622|XRT: GCN 20618| GCN ...  \n"
     ]
    }
   ],
   "source": [
    "swf = pandas.read_csv(grbstable, sep=';')\n",
    "print swf.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Regarding our goal -- to recover position and trigger time for each GRB -- we promptly see the `Time|[UT]` column, *RA*, *Dec* and the *error radius* given by `BAT`, `XRT` and `UVOT` instruments.\n",
    "\n",
    "The *Time* column alone is incomplete since it does not inform the day the event happened; but this information is easily extracted from the GRB name itself.\n",
    "\n",
    "Positional information comes in three flavors (*BAT*, *XRT*, *UVOT*), where *error radius* is the relevant information to decide which one to use; *BAT* has the worse precision (~`arcmin`) but has measurements for every GRB, while *XRT* and *UVOT* have much better measurements (~`arcsec`) but are followed less events."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By all means, let us work a little bit over the data in table itself.\n",
    "For instance, let us handle the *Not Availables* (`n/a`) and then look some stats."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### GRB table pre-processing: fixing datatypes and finding nulls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "GRB                                              object\n",
      "Time|[UT]                                        object\n",
      "Trigger|Number                                   object\n",
      "BAT RA|(J2000)                                   object\n",
      "BAT Dec|(J2000)                                  object\n",
      "BAT 90%|Error Radius|[arcmin]                    object\n",
      "BAT T90|[sec]                                    object\n",
      "BAT Fluence|(15-150 keV)|[10-7 erg/cm2]          object\n",
      "XRT RA|(J2000)                                   object\n",
      "XRT Dec|(J2000)                                  object\n",
      "XRT 90%|Error Radius|[arcsec]                    object\n",
      "XRT Time to First|Observation|[sec]              object\n",
      "XRT Early Flux|(0.3-10 keV)|[10-11 erg/cm2/s]    object\n",
      "UVOT RA|(J2000)                                  object\n",
      "UVOT Dec|(J2000)                                 object\n",
      "UVOT 90%|Error Radius|[arcsec]                   object\n",
      "UVOT Time to|First Observation|[sec]             object\n",
      "UVOT Magnitude                                   object\n",
      "Other Observatory Detections                     object\n",
      "Redshift                                         object\n",
      "Host Galaxy                                      object\n",
      "Comments                                         object\n",
      "References                                       object\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "# First, let's have a glance on columns values; let's try to figure out which one has \"nulls\".\n",
    "#\n",
    "# One way of doing this is by looking the frequency of values -- if a given value is two frequent, probably \n",
    "# that is the value is being used as 'NA'(i.e, Null) for that column (this comes with the assumption that such \n",
    "# table has a somewhat broad distribution of values);\n",
    "#\n",
    "# Before going further, what are columns datatypes?\n",
    "print swf.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  BAT 90%|Error Radius|[arcmin] BAT Dec|(J2000)  \\\n",
      "0                           1.0         -26.667   \n",
      "1                           1.1         -44.177   \n",
      "2                           1.2          13.039   \n",
      "3                           1.8           9.139   \n",
      "4                           1.7         -26.146   \n",
      "\n",
      "  BAT Fluence|(15-150 keV)|[10-7 erg/cm2] BAT RA|(J2000) BAT T90|[sec]  \\\n",
      "0                                     n/a        241.273           n/a   \n",
      "1                                      14        144.521            48   \n",
      "2                                      13        282.338            64   \n",
      "3                                      12        120.198          48.0   \n",
      "4                                      11        270.992          11.0   \n",
      "\n",
      "                                            Comments      GRB  \\\n",
      "0                                 UVOT: no detection  150821A   \n",
      "1               XRT: no detection|UVOT: no detection   071129   \n",
      "2  No AT slew (Earth limb constraint)|UVOT: no de...  110808A   \n",
      "3                        No AT slew (Sun constraint)  121102A   \n",
      "4  Due to an observing constraint, Swift did not ...  150428B   \n",
      "\n",
      "                                         Host Galaxy  \\\n",
      "0                                 NOT: possible host   \n",
      "1                                   Ks=19.5 (Subaru)   \n",
      "2  NOT, Magellan, Gemini-South, MPI/ESO (2.2m), T...   \n",
      "3              UVW2 = 23.4 (Swift-UVOT: likely host)   \n",
      "4                   Possible: IC 328 (spiral & blue)   \n",
      "\n",
      "  Other Observatory Detections                     Redshift  \\\n",
      "0                  Fermi (GBM)      1.968 (VLT: absorption)   \n",
      "1                   Konus-Wind  1.44 (Magellan: absorption)   \n",
      "2           Konus-Wind, Suzaku     0.089 (Gemini: emission)   \n",
      "3      Fermi (GBM), Konus-Wind     0.3984 (VLT: absorption)   \n",
      "4                       Suzaku      1.505 (VLT: absorption)   \n",
      "\n",
      "                                          References Time|[UT]  \\\n",
      "0  BAT: GCN 16878| GCN 16892|XRT: GCN 16878| GCN ...       n/a   \n",
      "1  BAT: GCN 12288| GCN 12292|XRT: GCN 12288| GCN ...  18:28:02   \n",
      "2  BAT: GCN 19833| GCN 19844|XRT: GCN 19833| GCN ...  03:03:32   \n",
      "3  BAT: GCN 14181| GCN 14186|XRT: GCN 14183| GCN ...  22:07:25   \n",
      "4  BAT: GCN 14223| GCN 14229| GCN 14240| GCN 1424...  03:06:08   \n",
      "\n",
      "    Trigger|Number UVOT 90%|Error Radius|[arcsec] UVOT Dec|(J2000)  \\\n",
      "0            BATSS                            n/a              n/a   \n",
      "1  Ground Analysis                            0.5       36:18:08.8   \n",
      "2           146129                            0.6       15:33:02.3   \n",
      "3           305011                           0.50      -44:09:29.6   \n",
      "4           173904                           0.43      -23:30:04.1   \n",
      "\n",
      "  UVOT Magnitude UVOT RA|(J2000) UVOT Time to|First Observation|[sec]  \\\n",
      "0            n/a             n/a                                  n/a   \n",
      "1         V>20.1     20:37:01.80                                   82   \n",
      "2         V>20.3     23:32:48.53                                   78   \n",
      "3         V>20.0     15:40:02.29                                   75   \n",
      "4         V>19.9    19:42:40.049                                   84   \n",
      "\n",
      "  XRT 90%|Error Radius|[arcsec] XRT Dec|(J2000)  \\\n",
      "0                           1.4             n/a   \n",
      "1                           n/a     -68:09:40.2   \n",
      "2                           1.5      16:53:26.5   \n",
      "3                           1.6     -51:10:59.2   \n",
      "4                           1.7     -19:09:52.2   \n",
      "\n",
      "  XRT Early Flux|(0.3-10 keV)|[10-11 erg/cm2/s] XRT RA|(J2000)  \\\n",
      "0                                           n/a            n/a   \n",
      "1                                            10    11:45:33.04   \n",
      "2                                         0.999    12:41:54.72   \n",
      "3                                           126    17:36:44.53   \n",
      "4                                           130    01:37:07.66   \n",
      "\n",
      "  XRT Time to First|Observation|[sec]  \n",
      "0                                 n/a  \n",
      "1                                  78  \n",
      "2                               61.77  \n",
      "3                               91.40  \n",
      "4                              130.50  \n"
     ]
    }
   ],
   "source": [
    "# So, what are the frequency of values we find at each column?\n",
    "#\n",
    "# I am assuming that (1) few (correct) values are going to show up more than once, simply because we are dealing\n",
    "# with real number and free-form comments; in principle, there is no restriction on the values.\n",
    "# Following that, I assume that Null values will be among the most frequent values if not the only ones in the\n",
    "# columns they occur.\n",
    "freqs = pandas.DataFrame( {col:swf[col].value_counts().head().index.values for col in swf.columns} )\n",
    "print freqs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We see then that some columns do present Null values and they are all '**n/a**'.\n",
    "We should be able to mask them very easily."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  BAT 90%|Error Radius|[arcmin] BAT Dec|(J2000)  \\\n",
      "0                           1.0         -26.667   \n",
      "1                           1.1         -44.177   \n",
      "2                           1.2          13.039   \n",
      "3                           1.8           9.139   \n",
      "4                           1.7         -26.146   \n",
      "\n",
      "  BAT Fluence|(15-150 keV)|[10-7 erg/cm2] BAT RA|(J2000) BAT T90|[sec]  \\\n",
      "0                                      14        241.273            48   \n",
      "1                                      13        355.412            64   \n",
      "2                                      12        270.992          48.0   \n",
      "3                                      11         92.387            15   \n",
      "4                                      15         13.078           0.3   \n",
      "\n",
      "                                            Comments      GRB  \\\n",
      "0                                 UVOT: no detection  150821A   \n",
      "1               XRT: no detection|UVOT: no detection   071129   \n",
      "2  No AT slew (Earth limb constraint)|UVOT: no de...  110808A   \n",
      "3                        No AT slew (Sun constraint)  121102A   \n",
      "4  Due to an observing constraint, Swift did not ...  150428B   \n",
      "\n",
      "                                         Host Galaxy  \\\n",
      "0                                 NOT: possible host   \n",
      "1                                   Ks=19.5 (Subaru)   \n",
      "2  NOT, Magellan, Gemini-South, MPI/ESO (2.2m), T...   \n",
      "3              UVW2 = 23.4 (Swift-UVOT: likely host)   \n",
      "4                   Possible: IC 328 (spiral & blue)   \n",
      "\n",
      "  Other Observatory Detections                     Redshift  \\\n",
      "0                  Fermi (GBM)      1.968 (VLT: absorption)   \n",
      "1                   Konus-Wind  1.44 (Magellan: absorption)   \n",
      "2           Konus-Wind, Suzaku     0.089 (Gemini: emission)   \n",
      "3      Fermi (GBM), Konus-Wind     0.3984 (VLT: absorption)   \n",
      "4                       Suzaku      1.505 (VLT: absorption)   \n",
      "\n",
      "                                          References Time|[UT]  \\\n",
      "0  BAT: GCN 16878| GCN 16892|XRT: GCN 16878| GCN ...  07:48:39   \n",
      "1  BAT: GCN 12288| GCN 12292|XRT: GCN 12288| GCN ...  03:03:32   \n",
      "2  BAT: GCN 19833| GCN 19844|XRT: GCN 19833| GCN ...  22:07:25   \n",
      "3  BAT: GCN 14181| GCN 14186|XRT: GCN 14183| GCN ...  03:06:08   \n",
      "4  BAT: GCN 14223| GCN 14229| GCN 14240| GCN 1424...  13:57:11   \n",
      "\n",
      "    Trigger|Number UVOT 90%|Error Radius|[arcsec] UVOT Dec|(J2000)  \\\n",
      "0            BATSS                            0.5       36:18:08.8   \n",
      "1  Ground Analysis                            0.6      -63:10:55.6   \n",
      "2           146129                           0.50       00:17:04.9   \n",
      "3           305011                           0.43      -44:09:29.6   \n",
      "4           173904                           0.44      -23:30:04.1   \n",
      "\n",
      "  UVOT Magnitude UVOT RA|(J2000) UVOT Time to|First Observation|[sec]  \\\n",
      "0         V>20.1     20:37:01.80                                   82   \n",
      "1         V>20.3      23:41:38.1                                   84   \n",
      "2         V>20.0     20:29:16.30                                   75   \n",
      "3         V>19.9     15:40:02.29                                   78   \n",
      "4         V>19.6    19:42:40.049                                  110   \n",
      "\n",
      "  XRT 90%|Error Radius|[arcsec] XRT Dec|(J2000)  \\\n",
      "0                           1.4     -68:09:40.2   \n",
      "1                           1.5      04:07:30.2   \n",
      "2                           1.6     -51:10:59.2   \n",
      "3                           1.7     -19:09:52.2   \n",
      "4                           1.8     -64:45:38.9   \n",
      "\n",
      "  XRT Early Flux|(0.3-10 keV)|[10-11 erg/cm2/s] XRT RA|(J2000)  \\\n",
      "0                                            10    11:45:33.04   \n",
      "1                                           131    14:41:23.52   \n",
      "2                                           126    01:37:07.66   \n",
      "3                                         0.999    22:26:30.81   \n",
      "4                                           130    23:03:24.52   \n",
      "\n",
      "  XRT Time to First|Observation|[sec]  \n",
      "0                                  78  \n",
      "1                               40400  \n",
      "2                               93.93  \n",
      "3                               91.40  \n",
      "4                              130.50  \n"
     ]
    }
   ],
   "source": [
    "# Mask 'n/a' values\n",
    "#\n",
    "for col in swf.columns:\n",
    "    to_replace = swf[col] == 'n/a'\n",
    "    swf.loc[to_replace,col] = None\n",
    "freqs = pandas.DataFrame( {col:swf[col].value_counts().head().index.values for col in swf.columns} )\n",
    "print freqs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Nulls are properly flagged/masked now. This is important because we can now handle the data with confidence.\n",
    "For instance, let's see which columns have nils in it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Has null?\n",
      "  \u001b[34mGRB\u001b[0m: False\n",
      "  \u001b[41mTime|[UT]\u001b[0m: True\n",
      "  \u001b[34mTrigger|Number\u001b[0m: False\n",
      "  \u001b[41mBAT RA|(J2000)\u001b[0m: True\n",
      "  \u001b[41mBAT Dec|(J2000)\u001b[0m: True\n",
      "  \u001b[41mBAT 90%|Error Radius|[arcmin]\u001b[0m: True\n",
      "  \u001b[41mBAT T90|[sec]\u001b[0m: True\n",
      "  \u001b[41mBAT Fluence|(15-150 keV)|[10-7 erg/cm2]\u001b[0m: True\n",
      "  \u001b[41mXRT RA|(J2000)\u001b[0m: True\n",
      "  \u001b[41mXRT Dec|(J2000)\u001b[0m: True\n",
      "  \u001b[41mXRT 90%|Error Radius|[arcsec]\u001b[0m: True\n",
      "  \u001b[41mXRT Time to First|Observation|[sec]\u001b[0m: True\n",
      "  \u001b[41mXRT Early Flux|(0.3-10 keV)|[10-11 erg/cm2/s]\u001b[0m: True\n",
      "  \u001b[41mUVOT RA|(J2000)\u001b[0m: True\n",
      "  \u001b[41mUVOT Dec|(J2000)\u001b[0m: True\n",
      "  \u001b[41mUVOT 90%|Error Radius|[arcsec]\u001b[0m: True\n",
      "  \u001b[41mUVOT Time to|First Observation|[sec]\u001b[0m: True\n",
      "  \u001b[41mUVOT Magnitude\u001b[0m: True\n",
      "  \u001b[41mOther Observatory Detections\u001b[0m: True\n",
      "  \u001b[41mRedshift\u001b[0m: True\n",
      "  \u001b[41mHost Galaxy\u001b[0m: True\n",
      "  \u001b[41mComments\u001b[0m: True\n",
      "  \u001b[34mReferences\u001b[0m: False\n"
     ]
    }
   ],
   "source": [
    "from termcolor import colored\n",
    "print \"Has null?\"\n",
    "for col in swf.columns:\n",
    "    hasnil = any(swf[col].isnull())\n",
    "    col = colored(col,on_color='on_red') if hasnil else colored(col,'blue')\n",
    "    print '  {}: {}'.format(col,hasnil)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "          day         time\n",
      "0  2017-03-25  07:56:57.95\n",
      "1  2017-03-18     15:27:52\n",
      "2  2017-03-18     12:11:56\n",
      "3  2017-03-17     09:45:59\n",
      "4  2017-03-11     08:08:42\n",
      "5  2017-03-07     20:24:21\n",
      "6  2017-03-06     07:06:12\n",
      "7  2017-02-08     22:33:38\n",
      "8  2017-02-08     18:11:16\n",
      "9  2017-02-06     21:57:25\n"
     ]
    }
   ],
   "source": [
    "# Now let's fix/transform columns to formats we like better.\n",
    "#\n",
    "# For instance, the trigger time; we have to merge the information \n",
    "# (Year-Month-Day) from column 'GRB' with UT time from 'Time'.\n",
    "#\n",
    "import re\n",
    "rec = re.compile('(\\d+)?',re.IGNORECASE)\n",
    "dt = pandas.DataFrame()\n",
    "dt['day'] = swf.GRB.map(lambda s:'20{0}-{1}-{2}'.format(*re.findall('.{1,2}',rec.match(s).group(1))))\n",
    "\n",
    "# We just saw that \"Time\" has null values. I understand we can\n",
    "# fix this issue with a \"01:01\" time hour for what matters;\n",
    "#\n",
    "dt['time'] = swf['Time|[UT]'].fillna('01:01:01')\n",
    "print dt.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dt['trigger time(UT)'] = pandas.to_datetime( dt.apply(lambda x:' '.join([x.day,x.time]), axis=1), yearfirst=1, errors='raise')\n",
    "# option 'coerce' enables the processing to complete even if some value was not able to be converted, in such cases\n",
    "# 'NaT' (\"null\") is placed whenever convertion fails.\n",
    "#\n",
    "# That said, let's check whether the was any problem during conversion by verifying for Nulls\n",
    "any(dt['trigger time(UT)'].isnull())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "          day         time        trigger time(UT)\n",
      "0  2017-03-25  07:56:57.95 2017-03-25 07:56:57.950\n",
      "1  2017-03-18     15:27:52 2017-03-18 15:27:52.000\n",
      "2  2017-03-18     12:11:56 2017-03-18 12:11:56.000\n",
      "3  2017-03-17     09:45:59 2017-03-17 09:45:59.000\n",
      "4  2017-03-11     08:08:42 2017-03-11 08:08:42.000\n"
     ]
    }
   ],
   "source": [
    "# As you may notice, dataframe 'dt' was created to ease the handling of datetime conversion.\n",
    "# Column 'tt' has the timestamp right.\n",
    "print dt.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Let us now convert the position columns to degree units,\n",
    "# they are currently in sexagesimal units.\n",
    "dp = swf.filter(regex='(RA|Dec|Radius)')\n",
    "dp_bat = dp.filter(regex='BAT')\n",
    "dp_xrt = dp.filter(regex='XRT')\n",
    "dp_uvot = dp.filter(regex='UVOT')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   BAT RA|(J2000)  BAT Dec|(J2000) BAT 90%|Error Radius|[arcmin]\n",
      "0         127.483           20.526                           2.0\n",
      "1         284.229            6.335                           3.7\n",
      "2         305.662           28.408                           1.2\n",
      "3          93.087           50.500                           1.0\n",
      "4         280.539          -30.041                           2.0\n",
      "5          13.510            9.538                           2.5\n",
      "6         263.087          -44.765                           2.4\n",
      "7         127.145           -9.027                           1.0\n",
      "8         166.544          -46.786                           1.4\n",
      "9         271.784           11.175                           2.2\n",
      "       BAT RA|(J2000)  BAT Dec|(J2000)\n",
      "count     1122.000000      1122.000000\n",
      "mean       184.190464         2.152165\n",
      "std        104.756917        41.788303\n",
      "min          0.127000       -89.029000\n",
      "25%         93.541750       -29.822000\n",
      "50%        188.518500         1.820500\n",
      "75%        275.837000        35.527750\n",
      "max        358.751000        88.610000\n"
     ]
    }
   ],
   "source": [
    "cols = dp_bat.columns\n",
    "dp_bat[cols] = dp_bat[cols].apply(pandas.to_numeric, errors='ignore')\n",
    "print dp_bat.head(10)\n",
    "print dp_bat.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  XRT RA|(J2000) XRT Dec|(J2000)  XRT 90%|Error Radius|[arcsec]\n",
      "0           None            None                            NaN\n",
      "1    18:57:13.46      06:17:57.0                            1.8\n",
      "2    20:22:40.12      28:24:21.7                            1.7\n",
      "3    06:12:14.87      50:29:34.9                            1.4\n",
      "4    18:42:21.46     -30:02:45.5                            1.5\n",
      "5           None            None                            NaN\n",
      "6    17:32:16.56     -44:44:53.5                            3.6\n",
      "7    08:28:34.55     -09:01:47.7                            1.6\n",
      "8    11:06:15.52     -46:46:05.6                            1.4\n",
      "9    18:07:04.95      11:11:37.8                            1.6\n",
      "       XRT 90%|Error Radius|[arcsec]\n",
      "count                     897.000000\n",
      "mean                        1.777077\n",
      "std                         0.912496\n",
      "min                         1.400000\n",
      "25%                         1.400000\n",
      "50%                         1.500000\n",
      "75%                         1.700000\n",
      "max                        15.000000\n"
     ]
    }
   ],
   "source": [
    "cols = dp_xrt.columns\n",
    "dp_xrt[cols] = dp_xrt[cols].apply(pandas.to_numeric, errors='ignore')\n",
    "print dp_xrt.head(10)\n",
    "print dp_xrt.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  UVOT RA|(J2000) UVOT Dec|(J2000) UVOT 90%|Error Radius|[arcsec]\n",
      "0            None             None                           None\n",
      "1            None             None                           None\n",
      "2            None             None                           None\n",
      "3     06:12:14.82       50:29:35.2                           0.43\n",
      "4            None             None                           None\n",
      "5            None             None                           None\n",
      "6            None             None                           None\n",
      "7            None             None                           None\n",
      "8            None             None                           None\n",
      "9            None             None                           None\n",
      "       UVOT RA|(J2000) UVOT Dec|(J2000) UVOT 90%|Error Radius|[arcsec]\n",
      "count              293              293                            272\n",
      "unique             293              293                             47\n",
      "top        20:37:01.80       36:18:08.8                            0.5\n",
      "freq                 1                1                             62\n"
     ]
    }
   ],
   "source": [
    "cols = dp_uvot.columns\n",
    "dp_uvot[cols] = dp_uvot[cols].apply(pandas.to_numeric, errors='ignore')\n",
    "print dp_uvot.head(10)\n",
    "print dp_uvot.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING: IllegalSecondWarning: 'second' was found  to be '60.0', which is not in range [0,60). Treating as 0 sec, +1 min [astropy.coordinates.angle_utilities]\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Error on radius: TBD\n"
     ]
    }
   ],
   "source": [
    "# To transform those positions from sexagesimal to degrees we'll\n",
    "# Astropy.\n",
    "# We will try to fill most of the events with some (BAT,XRT,UVOT)\n",
    "# positional information; we should try first the UVOT (better precision),\n",
    "# if not available, XRT and finally we take (if available) the BAT position.\n",
    "# If there is no positional information for a given GRB, \"None\" will\n",
    "# be at the output.\n",
    "# Also, to homogenize the final list of positions/error, Error Radius\n",
    "# should be transformed to 'arcsec'.\n",
    "#\n",
    "from astropy.coordinates import SkyCoord\n",
    "from astropy.units import Unit\n",
    "ra,dec,rad = [],[],[]\n",
    "inst = []\n",
    "dd = pandas.DataFrame()\n",
    "for i in swf.index:\n",
    "    posinst = None\n",
    "    if dp_uvot.iloc[i].values.all():\n",
    "        posinst = 'UVOT'\n",
    "        _ra,_dec,_rad = dp_uvot.iloc[i]\n",
    "        _sc = SkyCoord(_ra,_dec,unit=('hourangle','deg'))\n",
    "        _ra = _sc.ra.deg\n",
    "        _dec = _sc.dec.deg\n",
    "        try:\n",
    "            _rad = _rad * Unit('arcsec')\n",
    "            _rad = _rad.value\n",
    "        except:\n",
    "            _rad = None\n",
    "    elif dp_xrt.iloc[i].values.all():\n",
    "        posinst = 'XRT'\n",
    "        _ra,_dec,_rad = dp_xrt.iloc[i]\n",
    "        _sc = SkyCoord(_ra,_dec,unit=('hourangle','deg'))\n",
    "        _ra = _sc.ra.deg\n",
    "        _dec = _sc.dec.deg\n",
    "        try:\n",
    "            _rad = _rad * Unit('arcsec')\n",
    "            _rad = _rad.value\n",
    "        except:\n",
    "            _rad = None\n",
    "    elif dp_bat.iloc[i].values.all():\n",
    "        posinst = 'BAT'\n",
    "        _ra,_dec,_rad = dp_bat.iloc[i]\n",
    "        _sc = SkyCoord(_ra,_dec,unit=('deg','deg'))\n",
    "        _ra = _sc.ra.deg\n",
    "        _dec = _sc.dec.deg\n",
    "        try:\n",
    "            _rad = _rad * Unit('arcmin')\n",
    "            _rad = _rad.to('arcsec')\n",
    "        except:\n",
    "            print 'Error on radius: {}'.format(_rad)\n",
    "            _rad = None\n",
    "    else:\n",
    "        _ra,_dec,_rad = None,None,None\n",
    "    ra.append(_ra)\n",
    "    dec.append(_dec)\n",
    "    rad.append(_rad)\n",
    "    inst.append(posinst)\n",
    "dd['ra(deg)'] = ra\n",
    "dd['dec(deg)'] = dec\n",
    "dd['error radius(arcsec)'] = rad"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1123\n"
     ]
    }
   ],
   "source": [
    "# By now we should check whether tables have the same size to be clear that nothing was missing in the midway\n",
    "assert len(dd) == len(swf)\n",
    "assert len(dd) == len(dt)\n",
    "print len(dd)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           ra(deg)     dec(deg)  error radius(arcsec)\n",
      "count  1120.000000  1120.000000            846.000000\n",
      "mean    184.454896     2.213356             33.223331\n",
      "std     104.652577    41.757044             59.433465\n",
      "min       0.127000   -89.008611              1.400000\n",
      "25%      93.618531   -29.731347              1.500000\n",
      "50%     188.798062     1.823028              1.700000\n",
      "75%     275.911313    35.527569             60.000000\n",
      "max     358.756750    88.610000            420.000000\n",
      "1123\n"
     ]
    }
   ],
   "source": [
    "dd = dd.join(dt['trigger time(UT)'])\n",
    "print dd.describe()\n",
    "print len(dd)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "          GRB     ra(deg)   dec(deg)  error radius(arcsec)  \\\n",
      "0     170325A  127.483000  20.526000                 120.0   \n",
      "1     170318B  284.306083   6.299167                   1.8   \n",
      "2     170318A  305.667167  28.406028                   1.7   \n",
      "3     170317A   93.061750  50.493111                   NaN   \n",
      "4     170311A  280.589417 -30.045972                   1.5   \n",
      "5     170307A   13.510000   9.538000                 150.0   \n",
      "6     170306A  263.069000 -44.748194                   3.6   \n",
      "7     170208B  127.143958  -9.029917                   1.6   \n",
      "8     170208A  166.564667 -46.768222                   1.4   \n",
      "9     170206B  271.770625  11.193833                   1.6   \n",
      "10    170205A  262.169542  -0.063139                   NaN   \n",
      "11    170203A  332.861000  25.186000                 132.0   \n",
      "12    170202A  152.514542   5.011611                   NaN   \n",
      "13    170131A  341.447000  64.006000                 180.0   \n",
      "14    170127B   19.977083 -30.358083                   2.0   \n",
      "15    170127A  174.359708 -45.835639                   2.4   \n",
      "16    170126A  263.633208 -64.760361                   NaN   \n",
      "17    170115A  332.138458  13.728361                   3.6   \n",
      "18    170113A   61.732750 -71.943028                   NaN   \n",
      "19    170112A   15.232000 -17.233000                 150.0   \n",
      "20    170111A   20.697375 -32.565056                   1.5   \n",
      "21    170101A  267.089000  11.642000                  66.0   \n",
      "22    161224A  204.815792 -18.029083                   4.1   \n",
      "23    161220A  350.847750 -47.497722                   3.7   \n",
      "24    161219B   91.714292 -26.791528                   NaN   \n",
      "25    161218A  245.250000  -4.113000                  60.0   \n",
      "26    161217A  150.651000  52.358972                   1.5   \n",
      "27    161214B    3.851208   7.352361                   1.5   \n",
      "28    161202A  356.902708  19.646472                   1.4   \n",
      "29    161129A  316.227500  32.134867                   NaN   \n",
      "...       ...         ...        ...                   ...   \n",
      "1093  050416B  133.848000  11.186000                  78.0   \n",
      "1094  050416A  188.477667  21.057444                   1.4   \n",
      "1095   050412  181.099000  -1.193000                  90.0   \n",
      "1096   050410   89.808792  79.603444                   1.7   \n",
      "1097   050406   34.467042 -50.188083                   1.9   \n",
      "1098   050401  247.873000   2.185000                  60.0   \n",
      "1099   050326    6.946208 -71.370583                   2.4   \n",
      "1100   050319  154.199583  43.548472                   1.4   \n",
      "1101   050318   49.712875 -46.396056                   1.4   \n",
      "1102   050315  306.475583 -42.600417                   1.4   \n",
      "1103   050306  282.308750  -9.153111                   6.0   \n",
      "1104   050223  271.385125 -62.472222                   3.7   \n",
      "1105  050219B   81.316917 -57.757806                   1.5   \n",
      "1106  050219A  166.412417 -40.684194                   1.9   \n",
      "1107  050215B  174.448208  40.797056                   2.2   \n",
      "1108  050215A  348.403000  49.328000                 156.0   \n",
      "1109   050202  290.584000 -38.730000                 138.0   \n",
      "1110   050128  219.573583 -34.765194                   1.7   \n",
      "1111   050126  278.115000  42.384000                  84.0   \n",
      "1112   050124  192.877208  13.044194                   1.7   \n",
      "1113   050117  358.470833  65.938889                  15.0   \n",
      "1114   041228  336.642000   5.032000                  60.0   \n",
      "1115   041226   79.689000  73.329000                 180.0   \n",
      "1116   041224   56.192000  -6.663000                 120.0   \n",
      "1117   041223  100.204000 -37.068000                  60.0   \n",
      "1118   041220  291.288000  60.598000                  60.0   \n",
      "1119  041219C  343.926000 -76.744000                  60.0   \n",
      "1120  041219B  167.674000 -33.458000                 420.0   \n",
      "1121  041219A    6.154000  62.847000                 420.0   \n",
      "1122   041217  164.793000 -17.942000                  78.0   \n",
      "\n",
      "            trigger time(UT)  \n",
      "0    2017-03-25 07:56:57.950  \n",
      "1    2017-03-18 15:27:52.000  \n",
      "2    2017-03-18 12:11:56.000  \n",
      "3    2017-03-17 09:45:59.000  \n",
      "4    2017-03-11 08:08:42.000  \n",
      "5    2017-03-07 20:24:21.000  \n",
      "6    2017-03-06 07:06:12.000  \n",
      "7    2017-02-08 22:33:38.000  \n",
      "8    2017-02-08 18:11:16.000  \n",
      "9    2017-02-06 21:57:25.000  \n",
      "10   2017-02-05 12:39:09.000  \n",
      "11   2017-02-03 00:03:41.000  \n",
      "12   2017-02-02 18:28:02.000  \n",
      "13   2017-01-31 23:15:13.000  \n",
      "14   2017-01-27 15:13:28.000  \n",
      "15   2017-01-27 14:21:15.000  \n",
      "16   2017-01-26 11:30:42.000  \n",
      "17   2017-01-15 20:50:08.000  \n",
      "18   2017-01-13 10:04:05.000  \n",
      "19   2017-01-12 02:02:00.000  \n",
      "20   2017-01-11 00:33:28.000  \n",
      "21   2017-01-01 02:26:01.000  \n",
      "22   2016-12-24 22:13:34.640  \n",
      "23   2016-12-20 08:33:30.000  \n",
      "24   2016-12-19 18:48:39.000  \n",
      "25   2016-12-18 03:47:36.000  \n",
      "26   2016-12-17 01:27:34.000  \n",
      "27   2016-12-14 17:20:10.000  \n",
      "28   2016-12-02 23:12:52.000  \n",
      "29   2016-11-29 07:11:39.000  \n",
      "...                      ...  \n",
      "1093 2005-04-16 22:35:54.000  \n",
      "1094 2005-04-16 11:04:44.000  \n",
      "1095 2005-04-12 05:44:03.000  \n",
      "1096 2005-04-10 12:14:34.000  \n",
      "1097 2005-04-06 15:58:48.000  \n",
      "1098 2005-04-01 14:20:15.000  \n",
      "1099 2005-03-26 09:53:56.000  \n",
      "1100 2005-03-19 09:31:18.000  \n",
      "1101 2005-03-18 15:44:37.000  \n",
      "1102 2005-03-15 20:59:43.000  \n",
      "1103 2005-03-06 03:33:12.000  \n",
      "1104 2005-02-23 03:09:06.000  \n",
      "1105 2005-02-19 21:05:51.000  \n",
      "1106 2005-02-19 12:40:01.000  \n",
      "1107 2005-02-15 02:33:43.000  \n",
      "1108 2005-02-15 02:15:29.000  \n",
      "1109 2005-02-02 03:35:15.000  \n",
      "1110 2005-01-28 04:19:55.000  \n",
      "1111 2005-01-26 12:00:54.000  \n",
      "1112 2005-01-24 11:30:03.000  \n",
      "1113 2005-01-17 12:52:36.000  \n",
      "1114 2004-12-28 10:49:14.000  \n",
      "1115 2004-12-26 20:34:19.000  \n",
      "1116 2004-12-24 20:20:58.000  \n",
      "1117 2004-12-23 14:06:18.000  \n",
      "1118 2004-12-20 22:58:27.000  \n",
      "1119 2004-12-19 20:30:34.000  \n",
      "1120 2004-12-19 15:38:48.000  \n",
      "1121 2004-12-19 01:42:18.000  \n",
      "1122 2004-12-17 07:28:25.000  \n",
      "\n",
      "[1123 rows x 5 columns]\n"
     ]
    }
   ],
   "source": [
    "# Before joining the tables, we must to add our 'primary' column to this positional table -- the column we \n",
    "# will use to join the tables.\n",
    "# The primary column is the 'GRB' name.\n",
    "#\n",
    "dd['GRB'] = swf['GRB']\n",
    "cols = list(dd.columns)\n",
    "cols = cols[-1:] + cols[:-1]\n",
    "dd = dd[cols]\n",
    "print dd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# We may now try the JOIN.\n",
    "# We want to join only the matching keys, the goal is to have `ibdhne` table upgraded with new (position,time)\n",
    "# properties, but not to have more objects in it.\n",
    "# To do this JOIN we define the 'primary' keys to be the indexes; pandas use it (the index) as the joining column.\n",
    "#\n",
    "ibd.set_index('GRB', inplace=True)\n",
    "dd.set_index('GRB', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "               z       E_iso           GCN     ra(deg)   dec(deg)  \\\n",
      "count  98.000000   98.000000     98.000000   98.000000  98.000000   \n",
      "mean    2.277969   35.830306  13689.938776  182.851527   5.936088   \n",
      "std     1.255099   58.487612   4135.107727  103.851478  44.172265   \n",
      "min     0.338000    1.020000   3474.000000    2.053667 -79.128778   \n",
      "25%     1.309750    5.792500  11343.000000  106.138052 -28.010576   \n",
      "50%     2.087500   14.150000  14449.000000  187.342083   9.102153   \n",
      "75%     3.030000   36.350000  16507.750000  273.550250  44.410854   \n",
      "max     5.910000  347.000000  20192.000000  358.264375  87.301167   \n",
      "\n",
      "       error radius(arcsec)  \n",
      "count             38.000000  \n",
      "mean               1.523684  \n",
      "std                0.344403  \n",
      "min                1.400000  \n",
      "25%                1.400000  \n",
      "50%                1.450000  \n",
      "75%                1.500000  \n",
      "max                3.500000  \n"
     ]
    }
   ],
   "source": [
    "tab = ibd.join(dd, how='inner')\n",
    "print tab.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We see that we lost almost half of our objects (98 out of 173). After some inspection on the side we easily get to the answer: the 'GRB' names are slightly different. The reason is due to the name convention of those events: because the GRB name is built from the date it was observed if more then one event is detect at the same day, alpha character (A,B,C,...) are appended to the end of the name; Some tables will append an '**A**' to the name event if only one event happens to be detected on a given day (to be consistent). And *that* is the reason for unmatching keys in our JOIN."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "98\n"
     ]
    }
   ],
   "source": [
    "# to solve the unmatching keys we'll use Python's (stdlib) 'difflib'\n",
    "from difflib import get_close_matches\n",
    "diff = ibd.reset_index().GRB.to_frame()\n",
    "diff['match'] = diff.GRB.map(lambda x:get_close_matches(x, dd.index, 1)[0])\n",
    "# to double-check if we are right, the following column 'equal' must sum '98'\n",
    "diff['equal'] = diff['GRB'] == diff['match']\n",
    "print sum(diff.equal)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>GRB</th>\n",
       "      <th>match</th>\n",
       "      <th>equal</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>050315A</td>\n",
       "      <td>050315</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>050318A</td>\n",
       "      <td>050318</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>050319A</td>\n",
       "      <td>050319</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>050401A</td>\n",
       "      <td>050401</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>050408A</td>\n",
       "      <td>051008</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>050505A</td>\n",
       "      <td>050505</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>050525A</td>\n",
       "      <td>050525A</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>050730A</td>\n",
       "      <td>050730</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>050802A</td>\n",
       "      <td>050802</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>050814A</td>\n",
       "      <td>050814</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>050820A</td>\n",
       "      <td>050820A</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>050922C</td>\n",
       "      <td>050922C</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>051109A</td>\n",
       "      <td>051109A</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>060108A</td>\n",
       "      <td>060108</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>060115A</td>\n",
       "      <td>060115</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>060124A</td>\n",
       "      <td>060124</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>060202A</td>\n",
       "      <td>060202</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>060206A</td>\n",
       "      <td>060206</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>060210A</td>\n",
       "      <td>060210</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>060418A</td>\n",
       "      <td>060418</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>060502A</td>\n",
       "      <td>060502A</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>060510B</td>\n",
       "      <td>060510B</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>060512A</td>\n",
       "      <td>060512</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>060526A</td>\n",
       "      <td>060526</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>060605A</td>\n",
       "      <td>060605</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>060607A</td>\n",
       "      <td>060607A</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>060707A</td>\n",
       "      <td>060707</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>060708A</td>\n",
       "      <td>060708</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>060714A</td>\n",
       "      <td>060714</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>060729A</td>\n",
       "      <td>060729</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143</th>\n",
       "      <td>140703A</td>\n",
       "      <td>140703A</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144</th>\n",
       "      <td>140907A</td>\n",
       "      <td>140907A</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145</th>\n",
       "      <td>141026A</td>\n",
       "      <td>141026A</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146</th>\n",
       "      <td>141109A</td>\n",
       "      <td>141109A</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147</th>\n",
       "      <td>141121A</td>\n",
       "      <td>141121A</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>148</th>\n",
       "      <td>141221A</td>\n",
       "      <td>141221A</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>150206A</td>\n",
       "      <td>150206A</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150</th>\n",
       "      <td>150314A</td>\n",
       "      <td>150314A</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>151</th>\n",
       "      <td>150403A</td>\n",
       "      <td>150403A</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152</th>\n",
       "      <td>150514A</td>\n",
       "      <td>150513A</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>153</th>\n",
       "      <td>150821A</td>\n",
       "      <td>150821A</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154</th>\n",
       "      <td>150910A</td>\n",
       "      <td>150910A</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155</th>\n",
       "      <td>151021A</td>\n",
       "      <td>151021A</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156</th>\n",
       "      <td>151027A</td>\n",
       "      <td>151027A</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157</th>\n",
       "      <td>151027B</td>\n",
       "      <td>151027B</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158</th>\n",
       "      <td>151111A</td>\n",
       "      <td>151111A</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159</th>\n",
       "      <td>151112A</td>\n",
       "      <td>151112A</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160</th>\n",
       "      <td>151215A</td>\n",
       "      <td>151215A</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161</th>\n",
       "      <td>160121A</td>\n",
       "      <td>160121A</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162</th>\n",
       "      <td>160131A</td>\n",
       "      <td>160131A</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>163</th>\n",
       "      <td>160203A</td>\n",
       "      <td>160203A</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>164</th>\n",
       "      <td>160227A</td>\n",
       "      <td>160227A</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>165</th>\n",
       "      <td>160509A</td>\n",
       "      <td>160709A</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166</th>\n",
       "      <td>160623A</td>\n",
       "      <td>160630A</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>167</th>\n",
       "      <td>160625B</td>\n",
       "      <td>160625A</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>168</th>\n",
       "      <td>160804A</td>\n",
       "      <td>160804A</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>169</th>\n",
       "      <td>161017A</td>\n",
       "      <td>161017A</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>170</th>\n",
       "      <td>161023A</td>\n",
       "      <td>161202A</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>161108A</td>\n",
       "      <td>161108A</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172</th>\n",
       "      <td>161117A</td>\n",
       "      <td>161117A</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>173 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         GRB    match  equal\n",
       "0    050315A   050315  False\n",
       "1    050318A   050318  False\n",
       "2    050319A   050319  False\n",
       "3    050401A   050401  False\n",
       "4    050408A   051008  False\n",
       "5    050505A   050505  False\n",
       "6    050525A  050525A   True\n",
       "7    050730A   050730  False\n",
       "8    050802A   050802  False\n",
       "9    050814A   050814  False\n",
       "10   050820A  050820A   True\n",
       "11   050922C  050922C   True\n",
       "12   051109A  051109A   True\n",
       "13   060108A   060108  False\n",
       "14   060115A   060115  False\n",
       "15   060124A   060124  False\n",
       "16   060202A   060202  False\n",
       "17   060206A   060206  False\n",
       "18   060210A   060210  False\n",
       "19   060418A   060418  False\n",
       "20   060502A  060502A   True\n",
       "21   060510B  060510B   True\n",
       "22   060512A   060512  False\n",
       "23   060526A   060526  False\n",
       "24   060605A   060605  False\n",
       "25   060607A  060607A   True\n",
       "26   060707A   060707  False\n",
       "27   060708A   060708  False\n",
       "28   060714A   060714  False\n",
       "29   060729A   060729  False\n",
       "..       ...      ...    ...\n",
       "143  140703A  140703A   True\n",
       "144  140907A  140907A   True\n",
       "145  141026A  141026A   True\n",
       "146  141109A  141109A   True\n",
       "147  141121A  141121A   True\n",
       "148  141221A  141221A   True\n",
       "149  150206A  150206A   True\n",
       "150  150314A  150314A   True\n",
       "151  150403A  150403A   True\n",
       "152  150514A  150513A  False\n",
       "153  150821A  150821A   True\n",
       "154  150910A  150910A   True\n",
       "155  151021A  151021A   True\n",
       "156  151027A  151027A   True\n",
       "157  151027B  151027B   True\n",
       "158  151111A  151111A   True\n",
       "159  151112A  151112A   True\n",
       "160  151215A  151215A   True\n",
       "161  160121A  160121A   True\n",
       "162  160131A  160131A   True\n",
       "163  160203A  160203A   True\n",
       "164  160227A  160227A   True\n",
       "165  160509A  160709A  False\n",
       "166  160623A  160630A  False\n",
       "167  160625B  160625A  False\n",
       "168  160804A  160804A   True\n",
       "169  161017A  161017A   True\n",
       "170  161023A  161202A  False\n",
       "171  161108A  161108A   True\n",
       "172  161117A  161117A   True\n",
       "\n",
       "[173 rows x 3 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "diff"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This defines our new index/primary key -- the so called 'match' column.\n",
    "We now redefine it to 'ibd' and go back to the business..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "ibd.reset_index(inplace=True)\n",
    "ibd['primary'] = ibd.GRB.map(lambda x:get_close_matches(x,dd.index,1)[0])\n",
    "ibd.set_index('primary', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                z       E_iso           GCN     ra(deg)    dec(deg)  \\\n",
      "count  173.000000  173.000000    173.000000  173.000000  173.000000   \n",
      "mean     2.244382   40.616358  11356.485549  178.858973    2.553452   \n",
      "std      1.264709   69.514891   4999.373872  102.756476   42.041703   \n",
      "min      0.338000    1.020000   3099.000000    0.913333  -79.128778   \n",
      "25%      1.314000    6.150000   7289.000000   89.275625  -29.613639   \n",
      "50%      2.060000   15.000000  11169.000000  183.501167    2.185000   \n",
      "75%      2.993000   36.500000  15796.000000  258.981208   35.515944   \n",
      "max      8.200000  443.000000  20192.000000  358.264375   87.301167   \n",
      "\n",
      "       error radius(arcsec)  \n",
      "count             78.000000  \n",
      "mean               5.615385  \n",
      "std               19.496054  \n",
      "min                1.400000  \n",
      "25%                1.400000  \n",
      "50%                1.400000  \n",
      "75%                1.500000  \n",
      "max              144.000000  \n"
     ]
    }
   ],
   "source": [
    "tab = ibd.join(dd, how='inner')\n",
    "print tab.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we have it! ;)\n",
    "\n",
    "That's it! Well...amost... Let's now clean/homogenize column names and wirte it down to CSV file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['GRB' 'designation' 'z' 'E_iso' 'instrument' 'GCN' 'ra(deg)' 'dec(deg)'\n",
      " 'error radius(arcsec)' 'trigger time(UT)']\n"
     ]
    }
   ],
   "source": [
    "print tab.columns.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "tab.rename(columns={'E_iso':'E_iso(1e52 erg)'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "tab['ra(deg)'] = tab['ra(deg)'].round(3)\n",
    "tab['dec(deg)'] = tab['dec(deg)'].round(3)\n",
    "tab['error radius(arcsec)'] = tab['error radius(arcsec)'].round(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "            GRB     designation           z  E_iso(1e52 erg) instrument  \\\n",
      "count       173             173  173.000000       173.000000        173   \n",
      "unique      173             173         NaN              NaN          5   \n",
      "top     081109A  IBdHNe 120327A         NaN              NaN      Fermi   \n",
      "freq          1               1         NaN              NaN         62   \n",
      "first       NaN             NaN         NaN              NaN        NaN   \n",
      "last        NaN             NaN         NaN              NaN        NaN   \n",
      "mean        NaN             NaN    2.244382        40.616358        NaN   \n",
      "std         NaN             NaN    1.264709        69.514891        NaN   \n",
      "min         NaN             NaN    0.338000         1.020000        NaN   \n",
      "25%         NaN             NaN    1.314000         6.150000        NaN   \n",
      "50%         NaN             NaN    2.060000        15.000000        NaN   \n",
      "75%         NaN             NaN    2.993000        36.500000        NaN   \n",
      "max         NaN             NaN    8.200000       443.000000        NaN   \n",
      "\n",
      "                 GCN     ra(deg)    dec(deg)  error radius(arcsec)  \\\n",
      "count     173.000000  173.000000  173.000000             78.000000   \n",
      "unique           NaN         NaN         NaN                   NaN   \n",
      "top              NaN         NaN         NaN                   NaN   \n",
      "freq             NaN         NaN         NaN                   NaN   \n",
      "first            NaN         NaN         NaN                   NaN   \n",
      "last             NaN         NaN         NaN                   NaN   \n",
      "mean    11356.485549  178.858965    2.553451              5.615385   \n",
      "std      4999.373872  102.756458   42.041692             19.496054   \n",
      "min      3099.000000    0.913000  -79.129000              1.400000   \n",
      "25%      7289.000000   89.276000  -29.614000              1.400000   \n",
      "50%     11169.000000  183.501000    2.185000              1.400000   \n",
      "75%     15796.000000  258.981000   35.516000              1.500000   \n",
      "max     20192.000000  358.264000   87.301000            144.000000   \n",
      "\n",
      "           trigger time(UT)  \n",
      "count                   173  \n",
      "unique                  170  \n",
      "top     2016-11-08 03:32:33  \n",
      "freq                      2  \n",
      "first   2005-03-15 20:59:43  \n",
      "last    2016-12-02 23:12:52  \n",
      "mean                    NaN  \n",
      "std                     NaN  \n",
      "min                     NaN  \n",
      "25%                     NaN  \n",
      "50%                     NaN  \n",
      "75%                     NaN  \n",
      "max                     NaN  \n"
     ]
    }
   ],
   "source": [
    "print tab.describe(include='all')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "144.0\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>z</th>\n",
       "      <th>E_iso(1e52 erg)</th>\n",
       "      <th>GCN</th>\n",
       "      <th>ra(deg)</th>\n",
       "      <th>dec(deg)</th>\n",
       "      <th>error radius(arcsec)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>173.000000</td>\n",
       "      <td>173.000000</td>\n",
       "      <td>173.000000</td>\n",
       "      <td>173.000000</td>\n",
       "      <td>173.000000</td>\n",
       "      <td>173.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>2.244382</td>\n",
       "      <td>40.616358</td>\n",
       "      <td>11356.485549</td>\n",
       "      <td>178.858965</td>\n",
       "      <td>2.553451</td>\n",
       "      <td>81.606936</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>1.264709</td>\n",
       "      <td>69.514891</td>\n",
       "      <td>4999.373872</td>\n",
       "      <td>102.756458</td>\n",
       "      <td>42.041692</td>\n",
       "      <td>70.278524</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.338000</td>\n",
       "      <td>1.020000</td>\n",
       "      <td>3099.000000</td>\n",
       "      <td>0.913000</td>\n",
       "      <td>-79.129000</td>\n",
       "      <td>1.400000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>1.314000</td>\n",
       "      <td>6.150000</td>\n",
       "      <td>7289.000000</td>\n",
       "      <td>89.276000</td>\n",
       "      <td>-29.614000</td>\n",
       "      <td>1.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>2.060000</td>\n",
       "      <td>15.000000</td>\n",
       "      <td>11169.000000</td>\n",
       "      <td>183.501000</td>\n",
       "      <td>2.185000</td>\n",
       "      <td>144.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>2.993000</td>\n",
       "      <td>36.500000</td>\n",
       "      <td>15796.000000</td>\n",
       "      <td>258.981000</td>\n",
       "      <td>35.516000</td>\n",
       "      <td>144.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>8.200000</td>\n",
       "      <td>443.000000</td>\n",
       "      <td>20192.000000</td>\n",
       "      <td>358.264000</td>\n",
       "      <td>87.301000</td>\n",
       "      <td>144.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                z  E_iso(1e52 erg)           GCN     ra(deg)    dec(deg)  \\\n",
       "count  173.000000       173.000000    173.000000  173.000000  173.000000   \n",
       "mean     2.244382        40.616358  11356.485549  178.858965    2.553451   \n",
       "std      1.264709        69.514891   4999.373872  102.756458   42.041692   \n",
       "min      0.338000         1.020000   3099.000000    0.913000  -79.129000   \n",
       "25%      1.314000         6.150000   7289.000000   89.276000  -29.614000   \n",
       "50%      2.060000        15.000000  11169.000000  183.501000    2.185000   \n",
       "75%      2.993000        36.500000  15796.000000  258.981000   35.516000   \n",
       "max      8.200000       443.000000  20192.000000  358.264000   87.301000   \n",
       "\n",
       "       error radius(arcsec)  \n",
       "count            173.000000  \n",
       "mean              81.606936  \n",
       "std               70.278524  \n",
       "min                1.400000  \n",
       "25%                1.500000  \n",
       "50%              144.000000  \n",
       "75%              144.000000  \n",
       "max              144.000000  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Last step to be taken here is to fill the missing values for 'error radius'.\n",
    "# The safest way to do it is to fill the Nulls with the maximum possible value,\n",
    "# doing so we will not give absurd, or erronous information to the future users\n",
    "# of such data since we are filling those gaps with the worst case available.\n",
    "mval = tab['error radius(arcsec)'].max()\n",
    "midx = tab['error radius(arcsec)'].isnull()\n",
    "tab.loc[midx,'error radius(arcsec)'] = mval\n",
    "print mval\n",
    "tab.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tab.to_csv('data_wposition.csv',sep=';', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}