3-csvkit-intro.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Tools for CSV: csvkit\n",
    "\n",
    "Like `jq` for JSON, csvkit provides a suite of tools for working with data in comma separated value (CSV) formats. Again, one of the chief virtues of the `csvkit` tools is the way they can be combined with other common unix tools. Also [the documentation](https://csvkit.readthedocs.org/en/0.9.1/) is quite good."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import tarfile\n",
    "import re\n",
    "import os\n",
    "from itertools import count\n",
    "\n",
    "\n",
    "# You have a copy of this file in your `data` directory. Tate provides the data in a single TAR (tape archive) file\n",
    "DATA_PATH = '../data/tate-collection-1.2.tar.gz'\n",
    "DATA_FOBJ = tarfile.open(DATA_PATH)\n",
    "\n",
    "# We can use Python's tools for working with tar files to inspect the data package\n",
    "# For instance by listing the files it contains without unpacking it\n",
    "FILES = DATA_FOBJ.getmembers()\n",
    "for i, f in enumerate(FILES[:10]):\n",
    "    print('{0} \\t {1}'.format(i, f))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# This time we're only going to extract the CSV file\n",
    "DATA_FOBJ.extractall(path='../data/tate-collection', members=FILES[0:4])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "CSV_FILE_PATH = '../data/tate-collection/collection-1.2/artist_data.csv'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exploring\n",
    "\n",
    "`csvkit`'s `csvlook` tool gives us a way to inspect the contents of CSV files without opening them up in a program like Excel (especially when the file is so large it might crash our program)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%bash\n",
    "csvlook ../data/tate-collection/collection-1.2/artist_data.csv | head -n 5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What we're seeing here is effectively a plain text display of our CSV data … not super pretty but faster than opening Excel. Let's make this more useful"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Selecting and Slicing\n",
    "\n",
    "We can combine this facility with the `csvcut` tool to see only subsets of our data. First, let's use the `-n` flag to print out the column headings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "!csvcut -n ../data/tate-collection/collection-1.2/artist_data.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can look at just a subset of columns …"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "!csvcut -c 1,2,5,6,9 ../data/tate-collection/collection-1.2/artist_data.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once you know the column names you can also use those to subset or slice:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "!csvcut -c name,url ../data/tate-collection/collection-1.2/artist_data.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now combine these tools together using unix pipes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "!csvcut -c name,dates ../data/tate-collection/collection-1.2/artist_data.csv | csvlook | head -n 10"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In our case, this still doesn't look that helpful because some of these columns (like name) are really wide"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Summary Statistics\n",
    "The `csvkit` tools do offer other ways of peeking at out data that can prove useful to us &mdash; for instance through providing summary statistics."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "!csvcut -c name,yearOfBirth,yearOfDeath,url ../data/tate-collection/collection-1.2/artist_data.csv | \\\n",
    "csvstat"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Super useful!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Searching\n",
    "\n",
    "We can know use tools for searching (or \"grepping\") to find specific bits of information in our file &mdash; `csvkit` provides a `csvgrep` tool for doing just this"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "!csvcut -c name,yearOfBirth,yearOfDeath ../data/tate-collection/collection-1.2/artist_data.csv | \\\n",
    "csvgrep -c yearOfBirth -m 1497 | csvlook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "!csvcut -c name,yearOfBirth,yearOfDeath ../data/tate-collection/collection-1.2/artist_data.csv | \\\n",
    "csvgrep -c yearOfDeath -m 2005 | csvlook"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's sort by the year of birth to see which of our 2005 decedents were the oldest"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "!csvcut -c name,yearOfBirth,yearOfDeath ../data/tate-collection/collection-1.2/artist_data.csv | \\\n",
    "csvgrep -c yearOfDeath -m 2005 | csvsort -c yearOfBirth | csvlook"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Power Usage\n",
    "\n",
    "One of the really cool features of `csvkit` is that it allows us to move back and forth between CSVs and relational database structures quickly and easily &mdash; and even to treat our CSVs (for the purpose of rapid exploration) as though they were databases we could run SQL queries against"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "!csvsql --query \"select name from artist_data where yearOfBirth > 1700;\" \\\n",
    "../data/tate-collection/collection-1.2/artist_data.csv | csvlook"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "This facility is best for one-off ad hoc queries and, since `csvkit` is building a little in-memory database behind the scenes to make it possible, large datasets can be very slow to work with this way.\n",
    "\n",
    "However, `csvkit` SQL tools also make it really easy to turn CSVs into a proper database &mdash; by creating the necessary SQL statements for you and trying to guess the correct data types for columns. `csvsql` will even load the data for you if you have an SQL database installed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "!csvsql -i sqlite ../data/tate-collection/collection-1.2/artist_data.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "!csvsql --db sqlite:///tate_artists.db --insert ../data/tate-collection/collection-1.2/artist_data.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "!sql2csv --db sqlite:///tate_artists.db --query \"select * from artist_data\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Imported successfully, now we can run any SQL query we want"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "!sql2csv --db sqlite:///tate_artists.db --query \"select * from artist_data where gender='Female'\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "!sql2csv --db sqlite:///tate_artists.db --query \"select name,dates from artist_data where gender='Female'\" | \\\n",
    "    csvlook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.4.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}