06-Tables/Tables.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Tables introduction"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The Astropy [Table](http://docs.astropy.org/en/stable/table/index.html) class provides an extension of NumPy structured arrays for storing and manipulating heterogeneous tables of data. A few notable features of this package are:\n",
    "\n",
    "- Initialize a table from a wide variety of input data structures and types.\n",
    "- Modify a table by adding or removing columns, changing column names, or adding new rows of data.\n",
    "- Handle tables containing missing values.\n",
    "- Include table and column metadata as flexible data structures.\n",
    "- Specify a description, units, and output formatting for columns.\n",
    "- Perform operations like database joins, concatenation, and grouping.\n",
    "- Manipulate multidimensional columns.\n",
    "- Methods for reading and writing Table objects to files.\n",
    "- Integration with Astropy [Units and Quantities](http://astropy.readthedocs.org/en/stable/units/index.html).\n",
    "\n",
    "Tables vs. Pandas DataFrames\n",
    "------------------------------------\n",
    "\n",
    "The [Pandas](http://pandas.pydata.org/pandas-docs/stable/) package provides a powerful, high-performance table object via the [DataFrame](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.html#pandas.DataFrame) class.  This object is similar to Astropy Table in many ways and is rather broadly used in data science. If you have some familiarity with Pandas, you might wonder why it is not used instead of an astronomy-specific solution.\n",
    "\n",
    "There are a few reasons why Pandas is not used as a generalized table object in astronomy. These reasons include:\n",
    "* Pandas does not support table \"columns\" that are more complicated than simple numbers. Astropy `Table` does, enabling all of the features of Astropy objects to be used inside `Table`s. \n",
    "* Pandas does not support for multidimensional table columns. This is commonly used in standard FITS data products, for instance the Chandra energy response matrix used to analyze spectral data.  \n",
    "* Pandas' support for missing data relies on NaNs instead of masks. For some astronomical applications this is not desired, as it deletes potentially useful information and makes some standard astronomical analysis tasks overly complicated.\n",
    "\n",
    "However, Pandas DataFrames have some other features that make them more natural in certain contexts, so in some sense they are complementary to Astropy Tables.  The `to_pandas` and `from_pandas` functions detailed below allow these objects to be used interchangably (as long as one of the problems mentioned above does not apply).\n",
    "\n",
    "Documentation\n",
    "------------------\n",
    "\n",
    "For more information about the features presented below, you can read the\n",
    "[astropy.table](http://docs.astropy.org/en/stable/table/index.html) docs.\n",
    "\n",
    "*****\n",
    "*****\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Tutorial "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from astropy.table import Table\n",
    "\n",
    "import numpy as np\n",
    "\n",
    "%matplotlib inline \n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating tables\n",
    "\n",
    "There is great deal of flexibility in the way that a table can be initially constructed:\n",
    "\n",
    "- Read an existing table from a file or web URL\n",
    "- Add columns of data one by one\n",
    "- Add rows of data one by one\n",
    "- From an existing data structure in memory:\n",
    "\n",
    "  - List of data columns\n",
    "  - Dict of data columns\n",
    "  - List of row dicts\n",
    "  - NumPy homgeneous array or structured array\n",
    "  - List of row records\n",
    "  \n",
    "See the documentation section on [Constructing a table](http://astropy.readthedocs.org/en/stable/table/construct_table.html) for the details and plenty of examples."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "t = Table()\n",
    "t['name'] = ['larry', 'curly', 'moe', 'shemp']\n",
    "t['flux'] = [1.2, 2.2, 3.1, 4.3]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Looking at your table\n",
    "\n",
    "In IPython notebook, showing a table will produce a nice HTML representation of the table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "t"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you did the same in a terminal session you get a different view that isn't as pretty but does give a bit more information about the table:\n",
    "\n",
    "    >>> t\n",
    "    <Table rows=4 names=('name','flux')>\n",
    "    array([('source 1', 1.2), ('source 2', 2.2), ('source 3', 3.1),\n",
    "           ('source 4', 4.3)], \n",
    "          dtype=[('name', 'S8'), ('flux', '<f8')])\n",
    "\n",
    "To get a plain view which is the same in notebook and terminal use `print()`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "print(t)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To get the table column names and data types using the `colnames` and `dtype` properties:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "t.colnames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "t.dtype"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Astropy 1.1 and later provides a ``show_in_notebook()`` method that allows more interactive exploration of tables. It can be especially handy for large tables. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "t.show_in_notebook()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Accessing parts of the table\n",
    "\n",
    "We can access the columns and rows as for NumPy structured arrays. Notice that the outputs are `Column`, `Row`, or `Table` objects depending on the context."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "t['flux']  # Flux column (notice meta attributes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "t['flux'][1]  # Row 1 of flux column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "t[1]  # Row obj for with row 1 values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "t[1]['flux']  # Flux column of row 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "t[1:3]  # 2nd and 3rd rows in a new table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "t[[1, 3]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**One of the most powerful concepts is using boolean selection masks to filter tables**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "mask = t['flux'] > 3.0  # Define boolean mask for all flux values > 3\n",
    "t[mask]  # Create a new table with only the \"high flux\" sources"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Modifying the table\n",
    "\n",
    "Once the table exists with defined columns, there are a number of ways to modify the table in place. These are fully documented in the section [Modifying a table](http://astropy.readthedocs.org/en/stable/table/modify_table.html#modifying-a-table).\n",
    "\n",
    "To give a few examples, add rows with the [add_row()](http://astropy.readthedocs.org/en/stable/api/astropy.table.Table.html#astropy.table.Table.add_row) method, add new columns using dict-style assignment, or assign a unit to a row:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "t.add_row(('joe', 10.1))  # Add a new source at the end\n",
    "t['logflux'] = np.log10(t['flux'])  # Compute the log10 of the flux\n",
    "t"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that the `logflux` column has too many output digits given the precision of the input values. We can fix this by setting the format using normal Python formatting syntax:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "t['flux'].format = '%.2f'\n",
    "t['logflux'].format = '%.2f'\n",
    "t"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you want to annotate your columns with their unit, you can add a unit to any column.  Note that this can be a string *or* an Astropy unit object. If you are familiar with using Astropy `Quantity` objects, more on that in the context of tables is described below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "t['flux']. unit = 'Jy'\n",
    "t"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reading data from files into tables\n",
    "\n",
    "You can read data using the [Table.read()](http://astropy.readthedocs.org/en/stable/api/astropy.table.Table.html#astropy.table.Table.read) method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "t4 = Table.read('table_example.fits')\n",
    "t4"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Some formats, such as FITS and HDF5, are automatically identified by file extention while most others will require ``format`` to be explicitly provided.  `astropy` also provides a large set of ASCII file readers that will read a variety of obscure astronomy formats.  While these are actually *implemented* in the `astropy.io.ascii` sub-package, in general it is more convenient to use the \"unified\" I/O to read these directly as tables, like in the examples below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "Table.read('example.dat', format='ascii')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "Table.read('example.csv', format='ascii.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that the reader is often smart enough to determine the type of ASCII file even if you don't specify it (i.e., the `'ascii.csv'` in the above example). For example, an IPAC format file can be read by doing:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "Table.read('example.ipac', format='ascii')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Writing tables to files"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Similarly, tables can be written out to files in standard formats.  Many of the same formats are supported, although the exact details of which are written (and how to get them to look the way you want) can be found in the [docs for astropy.io.ascii](http://docs.astropy.org/en/stable/io/ascii/index.html)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "t.write('test.txt', format='ascii', overwrite=True)\n",
    "!cat test.txt"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Astropy also provides a special ASCII format called \"ECSV\" that is designed to also include metadata allowing the table to be reconstructed in Python."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "Table.read('test.txt' ,format='ascii')  # note that the units and formatting of the original table are lost"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "t.write('test.ecsv', format='ascii.ecsv', overwrite=True)\n",
    "Table.read('test.ecsv', format='ascii.ecsv')  # the units and formatting are as for the original table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Non-ASCII formats are available for writing too:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "t.write('test.fits', overwrite=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "t.write('test.vot', format='votable', overwrite=True)\n",
    "!head test.vot"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Storing more complex data as table columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Some of the most powerful usage of Astropy `Table` comes from combining two Astropy objects in a table. For example, the below illustrates a basic time-series that uses Astropy `Time` objects and a `Quantity`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from astropy.time import Time\n",
    "from astropy import units as u"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# observations were three times over the last three hours\n",
    "times = Time.now() - [2, 1, 0]*u.hour\n",
    "fluxes = [21.1, 21.2, 21.25] * u.mag\n",
    "\n",
    "ts =  Table(names=['obstime', 'flux'],\n",
    "            data=[times, fluxes])\n",
    "ts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ts['obstime'].jd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ts['flux'] + .12*u.mag"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One final related note: for historical reasons, `Table` does not always behave the way you'd expect with `Quantity` objects.  As a result, the `QTable` object was developed, which is identical to `Table` with the exception of when a column has a unit, it acts like a `Quantity`.  So if you intend to use a table as a `Quantity`, you're usually better off using `QTable`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ts['flux']  # this does *not* look/act exactly like a Quantity object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from astropy.table import QTable\n",
    "qts = QTable(ts)\n",
    "qts['flux'] # but this one does - it *is* a Quantity"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## High-level table operations\n",
    "\n",
    "So far we've only worked with one table at a time and viewed that table as a monolithic entity.  Astropy also supports high-level [Table operations](http://astropy.readthedocs.org/en/stable/table/operations.html) that manipulate multiple tables or view one table as a collection of sub-tables (groups).\n",
    "\n",
    " Documentation\t                                                                         | Description\n",
    "---------------------------------------------------------------------------------------- |-----------------------------------------\n",
    "[Grouped operations](http://astropy.readthedocs.org/en/stable/table/operations.html#id2) | Group tables and columns by keys\n",
    "[Stack vertically](http://astropy.readthedocs.org/en/stable/table/operations.html#id3)   | Concatenate input tables along rows\n",
    "[Stack horizontally](http://astropy.readthedocs.org/en/stable/table/operations.html#id4) | Concatenate input tables along columns\n",
    "[Join](http://astropy.readthedocs.org/en/stable/table/operations.html#join)              | Database-style join of two tables\n",
    "\n",
    "Here we'll introduce the join operation but go into more detail on the others in the exercises."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from astropy.table import join"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Recall our original table `t`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "t"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now say that we have some additional flux values from a different reference for a different, but overlapping, sample of sources:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "t2 = Table()\n",
    "t2['name'] = ['larry', 'moe', 'groucho']\n",
    "t2['flux2'] = [1.4, 3.5, 8.6]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can get a master table of flux measurements which are joined matching the values in the `name` column. This includes every row from each of the two tables, which is known as an **`outer`** join."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "t3 = join(t, t2, keys=['name'], join_type='outer')\n",
    "print(t3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "np.mean(t3['flux2'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Alternately, we could choose to keep only rows where both tables have a valid measurement using an **`inner`** join:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "join(t, t2, keys=['name'], join_type='inner')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Masked tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As discussed above, Astropy `Table` supports masking using more than just \"placeholder\" values — masked columns can have both a value *and* mask information."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "t2 = Table([['x', 'y', 'z'], \n",
    "            [1.1, 2.2, 3.3]],\n",
    "           names=['name', 'value'],\n",
    "           masked=True)\n",
    "t2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "t2['value'].mask = [False, True, False]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "print(t2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "t2['value'].fill_value = -99\n",
    "print(t2.filled())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Interfacing with Pandas\n",
    "Astropy `Table` includes  `to_pandas()` and `from_pandas()` [methods](http://docs.astropy.org/en/stable/table/pandas.html)  that facilitate conversion to/from [pandas](http://pandas.pydata.org) `DataFrame` objects.  There are a few caveats in making these conversions:\n",
    " - Tables with multi-dimensional columns cannot be converted.\n",
    " - Masked values are converted to `numpy.nan`. Numerical columns, int or float, are thus converted to ``numpy.float`` while string columns with missing values are converted to object columns with ``numpy.nan`` values to indicate missing or masked data. Therefore, you cannot always round-trip between `Table` and `DataFrame`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "t_2mass = Table.read('2mass.tbl', format='ascii')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "pd_2mass = t_2mass.to_pandas()\n",
    "pd_2mass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "t_pd = Table.from_pandas(pd_2mass)\n",
    "t_pd.show_in_notebook()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As an example of why this might be useful, Pandas can import/export Excel files (which Astropy's io does *not* support), and this provides a way to get such data into `astropy.table`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "pd_2mass.to_excel(\"2mass.xls\", index=False)\n",
    "excel_data = Table.from_pandas(pd.read_excel(\"2mass.xls\"))\n",
    "excel_data.show_in_notebook()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`DataFrame` has a built-in `plot()` method which is handy for quick visualization of its contents."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "pd_2mass.plot.scatter('j_m', 'h_m')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*****\n",
    "*****\n",
    "\n",
    "# Exercises\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Read the data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To start with, read in the two data files representing the master source list and observations source list.  The fields for the two tables are respectively documented in:\n",
    "\n",
    "- [master_sources](http://cxc.harvard.edu/csc/columns/master.html)\n",
    "- [obs_sources](http://cxc.harvard.edu/csc/columns/persrc.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "master_sources = Table.read('cdfs_master_sources.fits')\n",
    "obs_sources = Table.read('cdfs_obs_sources.fits')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**`master_sources`**\n",
    "\n",
    "Each distinct X-ray source identified on the sky is represented in the catalog by a single \"master source\" entry and one or more \"source observation\" entries, one for each observation in which the source has been detected. The master source entry records the best estimates of the properties of a source, based on the data extracted from the set of observations in which the source has been detected.  The subset of fields in our exercise table file are:\n",
    "\n",
    "Name | Description\n",
    "------ | ------------\n",
    "msid  | Master source ID\n",
    "name  | Source name in the Chandra catalog\n",
    "ra  | Source RA (deg)\n",
    "dec | Source Dec (deg)\n",
    "\n",
    "**`obs_sources`**\n",
    "\n",
    "The individual source entries record all of the properties about a detection extracted from a single observation, as well as associated file-based data products, which are observation-specific.  The subset of fields in our exercise table file are:\n",
    "\n",
    "Name | Description\n",
    "------ | ------------\n",
    "obsid | Observation ID\n",
    "obi | Observation interval\n",
    "targname | Target name\n",
    "gti_obs | Observation date\n",
    "flux_aper_b | Broad band (0.5 - 7 keV) flux (erg/cm2/sec)\n",
    "src_cnts_aper_b | Broad band source counts\n",
    "ra_b | Source RA (deg)\n",
    "dec_b | Source Dec (deg)\n",
    "livetime | Observation duration (sec)\n",
    "posid | Position ID\n",
    "theta | Off-axis angle (arcmin)\n",
    "msid | Master source ID"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exploring the data\n",
    "Do the following to explore the two tables:\n",
    "\n",
    "- Display the data for each table in IPython notebook using the normal way of showing the value of a variable.\n",
    "- Get a list of the column names for each table.  *Hint*: use `<TAB>` completion to conveniently discover all of the attributes and methods (e.g., type `master_sources.` and then hit the `<TAB>` key).\n",
    "- Find the length of each table.\n",
    "- Find the column datatypes for each table.\n",
    "\n",
    "Normally you display a table in IPython notebook by entering the variable name in a cell and pressing `shift-Enter`.  In a terminal session the default method is using something like `print(my_table)`.  In both cases the `Table` object prefers to display only a screenful of data to prevent having a zillion lines of output if the table is huge. If you really want to see all of the data you can use the [Table.pprint](http://astropy.readthedocs.org/en/stable/api/astropy.table.Table.html#astropy.table.Table.pprint) method. If you are using a Jupyter notebook interface, try the `show_in_notebook()` method.\n",
    "\n",
    "- Display all of the rows of the `master_sources` table using its `pprint()` method.\n",
    "- If you are working in a regular terminal window (not IPython notebook), try the `more()` method as well."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Modifying tables\n",
    "For our analysis we don't actually need the `obi` (observation interval) column in the `obs_sources` table.\n",
    "\n",
    "- Remove the `obi` column from the `obs_sources` table.\n",
    "\n",
    "The `gti_obs` column name is a bit obscure (GTI is a good time interval, FWIW).\n",
    "\n",
    "- Rename the `gti_obs` column to `obs_date`.\n",
    "\n",
    "It would be nice to have a count rate in addition to the source counts.\n",
    "\n",
    "- Add a new column `src_rate_aper_b` which is the source counts divided by observation duration in sec.\n",
    "\n",
    "Some of the sources have a negative net flux in the broad band."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Looking at the observation source data\n",
    "For each source detected in an individual observation (in the `obs_sources` table), let's look at the source flux values.\n",
    "\n",
    "- Use the matplotlib [`hist()`]( http://matplotlib.org/api/pyplot_api.html?highlight=pyplot.hist#matplotlib.pyplot.hist) function to make a histogram of the source fluxes.  Since the fluxes vary by orders of magnitude,\n",
    "  use the `numpy.log10` to put the fluxes in log space.\n",
    "\n",
    "- Also make the same plot but using only sources within 4 arcmin of the center.  *HINT*: use a boolean mask to select values of `theta` that are less than 4.0."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Join the master_sources and obs_sources tables\n",
    "\n",
    "The `master_sources` and `obs_sources` tables share a common `msid` column. What we now want is to join the master RA and Dec positions and master source names with the individual observations table.\n",
    "\n",
    "- Use the [table.join()](http://astropy.readthedocs.org/en/stable/table/operations.html#join) function to make a single table called `sources` that has the master RA, Dec, and name included for each observation source.\n",
    "\n",
    "*HINT*: the defaults for `keys` and `join_type='inner'`  are correct in this case, so the simplest possible call to `join()` will work!\n",
    "\n",
    "- *Intermediate*: Is the length of the new `sources` the same as `obs_sources`?  What happened?\n",
    "\n",
    "- *Advanced*: Make a scatter plot of the RA (x-axis) and Dec (y-axis) difference between the master source position and the observation source position.  You'll need to use `coordinates`!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Grouped properties of `sources`\n",
    "\n",
    "Finally, we can look at the variability properties of sources in the CDFS using the [`group_by()`](http://astropy.readthedocs.org/en/stable/table/operations.html#id2) functionality.  \n",
    "\n",
    "This method makes a new table in which all of the sources with identical master ID are next to each other.\n",
    "\n",
    "- Make a new table `g_sources` which is the `sources` table grouped by the `msid` key using the `group_by()` method.\n",
    "\n",
    "The `g_sources` table is just a regular table with all of the `sources` in a particular order.  The attribute `g_sources.groups` is an object that provides access to the `msid` subgroups.  You can access the $i^{th}$ group with `g_sources.groups[i]`.\n",
    "\n",
    "In addition, the `g_sources.groups.indices` attribute is an array with the indicies of the group boundaries.\n",
    "\n",
    "- Using `np.diff()` find the number of repeat observations of each master sources.  *HINT*: use the indices, Luke.\n",
    "- Print the 50th group and note which columns are the same for all group members and which are different.  Does this make sense?  In these few observations how many different target names were provided by observers?\n",
    "\n",
    "#### Aggregation\n",
    "\n",
    "The real power of grouping comes in the ability to create aggregate values for each of the groups, for instance the mean flux for each unique source.  This is done with the [`aggregate()`](http://astropy.readthedocs.org/en/stable/table/operations.html#aggregation) method, which takes a function reference as its input.  This function must take as input an array of values and return a single value.\n",
    "\n",
    "`aggregate` returns a new table that has a length equal to the number of groups.\n",
    "\n",
    "- Compute the mean of all columns for each unique source (i.e., each group) using `aggregate` and the `np.mean` function.  Call this table `g_sources_mean`.\n",
    "- Notice that aggregation cannot form a mean for certain columns and these are dropped from the output.  Use the `join()` function to restore the `master_sources` information to `g_sources_mean`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}