D02. Importing & Exporting Data with Pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# D02: Importing & Exporting data with Pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We'll start our introduction to pandas by looking at ways in which we read in, or import data in to be analysed and visualised."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pandas can read a variety of data in, in a variety of formats. In this lesson we'll look at some of the more common data types and the syntax and options you have to read these in."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "## CSV data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "CSV stands for comma separated values. It is essentially a plan text file, the likes of which you can create with notepad or a text editor where the data values are separated by commas. Note that the csv file can have variants (e.g. a tab separated values or TSV file) and often the data will be separated by other separators such as spaces, pipes or semi colons."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For now we're going to look at how we import a simple csv file using pandas. I've uploaded some data from gov.uk onto an AWS server where we can access it directly without the need to download it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "csv_path = 'https://s3-eu-west-1.amazonaws.com/dft-python/motsitelist2015.csv';  # Location of the file\n",
    "\n",
    "df1 = pd.read_csv(csv_path)   # Imports the csv\n",
    "df1.head(5)                   # Calling the head method wiuth a value will show the first x records of the data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we can see, pandas has imported our csv perfectly!\n",
    "\n",
    "Note how the telephone and fax number columns begin with an apostrophe. This is presumably done to stop software reading it these fields as numbers and stripping the leading 0 of the phone number out!\n",
    "\n",
    "You'll also notice that missing data is shown with a NaN value. This stands for 'Not a Number' and we'll be exploring that in greater detail later on.\n",
    "\n",
    "We do have a lot of control over the read_csv function as evidenced by this more detailed example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "path = 'https://s3-eu-west-1.amazonaws.com/dft-python/motsitelist2015.csv';  # Location of the file\n",
    "cols = ['Site', 'Name', 'Address 1',' Address 2', ' Address 3', 'Address 4','Postcode','Tel', 'Fax','1','2','3','4','4A','5','5A','7']\n",
    "\n",
    "df2 = pd.read_csv(filepath_or_buffer=csv_path,# Keyword argument for the file location\n",
    "                  sep=',',                    # The separator for the data fields. Pandas will try and determine this automatically\n",
    "                  header = 0,                 # The row of the input file to use for the headers\n",
    "                  names = cols,               # Custom column names - passed as a list\n",
    "                  index_col = 'Site',         # Column to use as an index - can also pass a number\n",
    "                  usecols = [0,1,2,3,4,5,6],  # Specify specific columns to import\n",
    "                 )\n",
    "df2.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can read more about the read_csv function at the Pandas API reference <a href = \"http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_csv.html\">here</a>."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## JSON Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "JSON stands for JavaScript Object Notation. It is a widely used data format on the internet due to the wide use of Javascript in web browsers. If you've not seen JSON data before, it can appear a little daunting as it's not really designed to be easily read by humans and also due to its nested nature. That said, it is a very efficient way to store unstructured data since not all the data items have to have the same 'shape' (e.g. rows / columns) and values can be nested within other data items and accesed by indexing.\n",
    "\n",
    "We'll now look at the above extract from the MOT dataset above, except in JSON format:"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "'{\"Name\":\n",
    "    {\"0003AW\":\"M AND T TRANSMISSIONS LIMITED\",\n",
    "    \"0003BY\":\"M & D SERVICES (BRISTOL) LTD\",\n",
    "    \"0004B1\":\"PANTEG SERVICE STATION\",\n",
    "    \"0005A2\":\"HAVELOCK ROAD GARAGE\",\n",
    "    \"0005B0\":\"STONEACRE\"},\n",
    "\"Address 1\":\n",
    "    {\"0003AW\":\"120 BRADWAY ROAD\",\n",
    "    \"0003BY\":\"UNIT 1 BALACLAVA ROAD IND EST\",\n",
    "    \"0004B1\":\"STATION RD\",\"0005A2\":\"HAVELOCK ROAD\",\n",
    "    \"0005B0\":\"ROWSON STREET\"},\" \n",
    "Address 2\":\n",
    "    {\"0003AW\":null,\n",
    "    \"0003BY\":\"RIDGWAY ROAD\",\n",
    "    \"0004B1\":\"GRIFFITHSTOWN\",\n",
    "    \"0005A2\":\"BALBY\",\n",
    "    \"0005B0\":\"NEW BRIGHTON\"},\" \n",
    "Address 3\":\n",
    "    {\"0003AW\":null,\n",
    "    \"0003BY\":\"FISHPONDS\",\n",
    "    \"0004B1\":null,\n",
    "    \"0005A2\":null,\n",
    "    \"0005B0\":null},\n",
    "\"Address 4\":\n",
    "    {\"0003AW\":\"SHEFFIELD\",\n",
    "    \"0003BY\":\"BRISTOL\",\n",
    "    \"0004B1\":\"PONTYPOOL\",\n",
    "    \"0005A2\":\"DONCASTER\",\n",
    "    \"0005B0\":\"WALLASEY\"},\n",
    "\"Postcode\":\n",
    "    {\"0003AW\":\"S17 4QW\",\n",
    "    \"0003BY\":\"BS16 3LJ\",\n",
    "    \"0004B1\":\"NP4 5JH\",\n",
    "    \"0005A2\":\"DN4 8AQ\",\n",
    "    \"0005B0\":\"CH45 2NA\"}}'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you can see the format isn't particularly easy on the eye and due to it's different structure and nesting, it's quite difficult to make sense of. Fortunately pandas copes very well with JSON and can make light work of it.\n",
    "\n",
    "We're now going to import an identical MOT dataset to our first example, except this time it's saved in JSON format."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "json_path = 'https://s3-eu-west-1.amazonaws.com/dft-python/motsitelist2015.json';  # Location of the file - note the json suffix!\n",
    "df3 = pd.read_json(json_path)\n",
    "df3.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we can see, Pandas as read the JSON file in a different order and it also appears to have an issue with the index. This is all stuff we can fix very easily, and indeed we're going to find out how in the upcoming chapters, but as a basic validation check we should see whether the JSON dataframe has the same number of records as the CSV dataframe. We can do this with the len() function we met in the basics section:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print(len(df1),len(df3))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we can see, both datasets have the same number of records which is encouraging! For now, we'll simply export our dataset as a csv so we can explore it in Excel or a similar program.\n",
    "\n",
    "Note that you can read more about the read_json function <a href = \"http://pandas.pydata.org/pandas-docs/stable/io.html#io-json-reader\">here</a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "out_path = \"C:\\\\Users\\\\Tom\\\\Desktop\\\\MOT File.csv\"  # Location of the outfile\n",
    "df3.to_csv(out_path)                                # Exporting the file as a CSV"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that we have to use double backslashes when specifying a string for the file location as Python uses single backslashes as an 'escape sequence'. There is more information about escape sequences in the further reading section.\n",
    "\n",
    "You can also read more about the to_csv function <a href = \"http://pandas.pydata.org/pandas-docs/version/0.17.1/generated/pandas.DataFrame.to_csv.html\">here</a>."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "## Other forms of data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "Note that you're not just limited to JSON and CSV data when reading data into pandas, although these are two of the more common types of data format out there. Pandas has many other similar functions for reading a wider variety of data in, including:\n",
    "\n",
    "* MS Excel\n",
    "* HTML\n",
    "* SQL\n",
    "* STATA\n",
    "* SAS\n",
    "\n",
    "You can find a full list of these functions <a href = \"http://pandas.pydata.org/pandas-docs/stable/io.html\">here</a> along with links to supporting documentation."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Further Reading"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a href = \"http://pandas.pydata.org/pandas-docs/stable/io.html\">Pandas IO tools</a> (Read / Export functions)<br/>\n",
    "<a href = \"http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_csv.html\">read_csv Function</a><br/>\n",
    "<a href = \"http://pandas.pydata.org/pandas-docs/version/0.17.1/generated/pandas.DataFrame.to_csv.html\">to_csv Fucntion</a><br/>\n",
    "<a href = \"http://pandas.pydata.org/pandas-docs/stable/io.html#io-json-reader\">read_json function</a><br/>\n",
    "<a href = \"http://beginnersbook.com/2015/04/json-tutorial/\">JSON Tutorial</a><br/>"
   ]
  }
 ],
 "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.5.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}