{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Tablesaw \n",
    "\n",
    "[Tablesaw](https://tablesaw.tech/) provides the ability to easily transform, summarize, and filter data, as well as computing descriptive statistics. It can also be used easily with libraries like Smile, which provides fundamental machine learning algorithms.\n",
    "\n",
    "This notebook has some basic demos of how to use Tablesaw, including visualizing the results for which it uses the BeakerX interactive visualization APIs. Tablesaw also provides its own visualization APIs if you wish to do visualization outside of BeakerX. The notebook covers basic table manipulation, k-means clustering, linear regression, and fetching financial data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%classpath add mvn\n",
    "tech.tablesaw tablesaw-beakerx 0.30.3\n",
    "com.jimmoores quandl-tablesaw 2.0.0\n",
    "com.github.haifengl smile-core 1.5.2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%import static tech.tablesaw.aggregate.AggregateFunctions.*\n",
    "%import tech.tablesaw.api.*\n",
    "%import tech.tablesaw.columns.*\n",
    "%import smile.clustering.*\n",
    "%import smile.regression.*\n",
    "\n",
    "// display Tablesaw tables with BeakerX table display widget\n",
    "tech.tablesaw.beakerx.TablesawDisplayer.register()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tornadoes = Table.read().csv(\"../resources/data/tornadoes_2014.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "//print dataset structure\n",
    "tornadoes.structure()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "//get header names\n",
    "tornadoes.columnNames()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "//displays the row and column counts\n",
    "tornadoes.shape()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "//displays the first n rows\n",
    "tornadoes.first(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tornadoes.structure()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "//summarize the data in each column\n",
    "tornadoes.summary()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "//Mapping operations\n",
    "def month = tornadoes.dateColumn(\"Date\").month()\n",
    "tornadoes.addColumns(month);\n",
    "tornadoes.columnNames()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "//Sorting by column\n",
    "tornadoes.sortOn(\"-Fatalities\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "//Descriptive statistics\n",
    "tornadoes.column(\"Fatalities\").summary()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "//Performing totals and sub-totals\n",
    "def injuriesByScale = tornadoes.summarize(\"Injuries\", median).by(\"Scale\")\n",
    "injuriesByScale.setName(\"Median injuries by Tornado Scale\")\n",
    "injuriesByScale"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "//Cross Tabs\n",
    "tornadoes.xTabCounts(\"State\", \"Scale\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## K-means clustering\n",
    "\n",
    "K-means is the most common form of “centroid” clustering. Unlike classification, clustering is an unsupervised learning method. The categories are not predetermined. Instead, the goal is to search for natural groupings in the dataset, such that the members of each group are similar to each other and different from the members of the other groups. The K represents the number of groups to find.\n",
    "\n",
    "We’ll use a well known Scotch Whiskey dataset, which is used to cluster whiskeys according to their taste based on data collected from tasting notes. As always, we start by loading data and printing its structure."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "whiskeyData = Table.read().csv(\"../resources/data/whiskey.csv\")\n",
    "whiskeyData.structure()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "kMeans = new KMeans(whiskeyData.as().doubleMatrix(\"Body\", \"Sweetness\", \"Smoky\", \"Medicinal\", \"Tobacco\", \"Honey\", \"Spicy\", \"Winey\", \"Nutty\", \"Malty\", \"Fruity\", \"Floral\"), 5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Table whiskeyClusters = Table.create(\"Clusters\", whiskeyData.stringColumn(\"Distillery\"), DoubleColumn.create(\"Cluster\", kMeans.getClusterLabel()));\n",
    "whiskeyClusters = whiskeyClusters.sortAscendingOn(\"Cluster\", \"Distillery\");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Play (Money)ball with Linear Regression\n",
    "\n",
    "In baseball, you make the playoffs by winning more games than your rivals. The number of games the rivals win is out of your control so the A’s looked instead at how many wins it took historically to make the playoffs. They decided that 95 wins would give them a strong chance.  Here’s how we might check that assumption in Tablesaw."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "baseball = Table.read().csv(\"../resources/data/baseball.csv\");\n",
    "\n",
    "// filter to the data available at the start of the 2002 season\n",
    "moneyball = baseball.where(baseball.numberColumn(\"year\").isLessThan(2002));\n",
    "wins = moneyball.nCol(\"W\");\n",
    "year = moneyball.nCol(\"Year\");\n",
    "playoffs = moneyball.column(\"Playoffs\");\n",
    "runDifference = moneyball.numberColumn(\"RS\").subtract(moneyball.numberColumn(\"RA\")).setName(\"RD\");\n",
    "moneyball.addColumns(runDifference);\n",
    "\n",
    "def Plot = new Plot(title: \"RD x Wins\", xLabel:\"RD\", yLabel: \"W\")\n",
    "Plot << new Points(x: moneyball.numberColumn(\"RD\").asDoubleArray(), y: moneyball.numberColumn(\"W\").asDoubleArray())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "winsModel = new OLS(moneyball.select(\"W\", \"RD\").smile().numericDataset(\"RD\"));"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "runsScored = new OLS(moneyball.select(\"OBP\", \"SLG\", \"RS\").smile().numericDataset(\"RS\"));"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "new Histogram(xLabel:\"X\",\n",
    "              yLabel:\"Proportion\",\n",
    "              data: Arrays.asList(runsScored.residuals()),\n",
    "              binCount: 25);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Financial and Economic Data\n",
    "\n",
    "You can fetch data from [Quandl](https://www.quandl.com/) and load it directly into Tablesaw"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%import com.jimmoores.quandl.*\n",
    "%import com.jimmoores.quandl.tablesaw.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "TableSawQuandlSession session = TableSawQuandlSession.create();\n",
    "Table table = session.getDataSet(DataSetRequest.Builder.of(\"WIKI/AAPL\").build());\n",
    "// Create a new column containing the year\n",
    "NumberColumn yearColumn = table.dateColumn(\"Date\").year();\n",
    "yearColumn.setName(\"Year\");\n",
    "table.addColumns(yearColumn);\n",
    "// Create max, min and total volume tables aggregated by year\n",
    "Table summaryMax = table.summarize(\"Adj. Close\", max).by(\"year\");\n",
    "Table summaryMin = table.summarize(\"Adj. Close\", min).by(\"year\");\n",
    "Table summaryVolume = table.summarize(\"Volume\", sum).by(\"year\");\n",
    "// Create a new table from each of these\n",
    "summary = Table.create(\"Summary\", summaryMax.column(0), summaryMax.column(1), \n",
    "                       summaryMin.column(1), summaryVolume.column(1));\n",
    "// Add back a DateColumn to the summary...will be used for plotting\n",
    "DateColumn yearDates = DateColumn.create(\"YearDate\");\n",
    "for (year in summary.column('Year')) {\n",
    "    yearDates.append(java.time.LocalDate.of((int) year, 1, 1));\n",
    "}\n",
    "summary.addColumns(yearDates)\n",
    "\n",
    "summary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "years = summary.column('YearDate').collect()\n",
    "\n",
    "plot = new TimePlot(title: 'Price Chart for AAPL', xLabel: 'Time', yLabel: 'Max [Adj. Close]')\n",
    "plot << new YAxis(label: 'Volume')\n",
    "plot << new Points(x: years, y: summary.column('Max [Adj. Close]').collect())\n",
    "plot << new Line(x: years, y: summary.column('Max [Adj. Close]').collect(), color: Color.blue)\n",
    "plot << new Stems(x: years, y: summary.column('Sum [Volume]').collect(), yAxis: 'Volume')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Groovy",
   "language": "groovy",
   "name": "groovy"
  },
  "language_info": {
   "codemirror_mode": "groovy",
   "file_extension": ".groovy",
   "mimetype": "",
   "name": "Groovy",
   "nbconverter_exporter": "",
   "version": "2.4.3"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": false,
   "sideBar": false,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": false,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}