Example_CSVs.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Examples using CSV files"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Parsing comma-separtated-values (CSV) files is a common task. There are many tools available in Python to deal with this. Let's start by using the built-in `csv` module."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv # using Python module"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, we want to open an example file, and read the contents:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[['Name', 'DOB', 'Years', 'Degree'], ['Alice Jones', '12/1/1980', '3', 'MS'], ['Bob Smith', '1/1/1969', '4', 'BS'], ['John Book', '5/3/1980', '11', 'BA'], ['Billy Blanks', '6/9/2000', '8', 'AA']]\n"
     ]
    }
   ],
   "source": [
    "f = open('example_1.csv','rb') # use binary mode if on MS windows\n",
    "d = [i for i in csv.reader(f) ] # use list comprehension to read from file\n",
    "f.close() # close file\n",
    "print d"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Adding new fields as columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This reads the rows of the CSV file into a list-of-lists.\n",
    "\n",
    "Now, let's suppose we want to create columns for `last name`  and `first name` instead of having just one `name` field. The first element in the list `d` is the header, so we had the additional fields there:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Name', 'DOB', 'Years', 'Degree', 'Last Name', 'First Name']\n"
     ]
    }
   ],
   "source": [
    "d[0].append('Last Name')\n",
    "d[0].append('First Name')\n",
    "print d[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, we want to split the original `Name` field into first and last names and put these at the ends of their respective rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[['Name', 'DOB', 'Years', 'Degree', 'Last Name', 'First Name'], ['Alice Jones', '12/1/1980', '3', 'MS', 'Jones', 'Alice'], ['Bob Smith', '1/1/1969', '4', 'BS', 'Smith', 'Bob'], ['John Book', '5/3/1980', '11', 'BA', 'Book', 'John'], ['Billy Blanks', '6/9/2000', '8', 'AA', 'Blanks', 'Billy']]\n"
     ]
    }
   ],
   "source": [
    "for row in d[1:]: # start at 1st, not 0th column. Each row is a list\n",
    "    first,last= row[0].split() # split on white-space\n",
    "    row.append(last) # append to each row\n",
    "    row.append(first)\n",
    "    \n",
    "print d"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "metadata": {},
   "outputs": [],
   "source": [
    "#%qtconsole"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Writing updated CSV file"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, we want to write out our new data in CSV format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "metadata": {},
   "outputs": [],
   "source": [
    "f = open('example_1_out.csv','wb') # write mode binary\n",
    "fw = csv.writer(f) # create csv writer\n",
    "fw.writerows(d) \n",
    "f.close() # close file "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, opening the file `example_1_out.csv` using excel (or another reader) should show the new column. That covers the most direct and pure-Python way to dealing with CSV files. However, there are many other tools available. For example, `numpy` provides power methods to access these files."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Using Numpy to parse CSV files"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's see how to accomplish the same work as above by using Numpy."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[['Name' 'DOB' 'Years' 'Degree']\n",
      " ['Alice Jones' '12/1/1980' '3' 'MS']\n",
      " ['Bob Smith' '1/1/1969' '4' 'BS']\n",
      " ['John Book' '5/3/1980' '11' 'BA']\n",
      " ['Billy Blanks' '6/9/2000' '8' 'AA']]\n",
      "|S12\n"
     ]
    }
   ],
   "source": [
    "import numpy as np \n",
    "\n",
    "d = np.loadtxt('example_1.csv',delimiter=',',dtype=str)\n",
    "\n",
    "print d\n",
    "print d.dtype"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that we did not have to use `open` to get at the contents of the file. By default, the `delimiter` is any whitespace, so we had to change this to the comma character. The `dtype` specifies we want everything to be read in as a string. Numpy can figure out how long that string needs to be as it goes through the file so we don't have to specify that ahead of time (we probably don't know it anyway). In this case, the `dtype` turns out to be a twelve character string `S12`. Note that this is the maximum length is used for *all* strings so there is obviously a lot of extra space if most of the strings are short and just a few are long.\n",
    "\n",
    "Numpy provides many more ways of reading data via the `dtype`. For example,"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('Alice Jones', '12/1/1980', 3, 'MS') ('Bob Smith', '1/1/1969', 4, 'BS')\n",
      " ('John Book', '5/3/1980', 11, 'BA') ('Billy Blanks', '6/9/2000', 8, 'AA')]\n"
     ]
    }
   ],
   "source": [
    "dt = [('name',   'S64'),   # The first element of the tuple is our name for each respective column\n",
    "      ('dob',    'S64'),   # and the second element is the numpy dtype we want for that column.\n",
    "      ('years',  'int'),   # Here we want years as an integer, not a string\n",
    "      ('degree', 'S64'), ]\n",
    "\n",
    "d = np.loadtxt('example_1.csv',delimiter=',',dtype=dt,skiprows=1) # skip the header row\n",
    "print d"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The advantage of doing it this way is that now we can compute the `years` column using `numpy` tools. For example, here is the mean of the years."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "6.5\n"
     ]
    }
   ],
   "source": [
    "print d['years'].mean() # using numpy arrays"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now to get back to the main task at hand: splitting the name field into first and last name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "metadata": {},
   "outputs": [],
   "source": [
    "import string\n",
    "\n",
    "n=map(string.split,d['name'])\n",
    "w=array([tuple(i)+tuple(j) for i,j in zip(d,n)],    # list comprehension glues tuple-ized rows together\n",
    "         dtype=dt+[('first','S64'),('last','S64')]) # append new dtypes to existing list of dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That was kind of non-simple, but now we can write this to a CSV using `savetxt`.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "metadata": {},
   "outputs": [],
   "source": [
    "# the comments are set to '' to avoid hash marks on the first line.\n",
    "np.savetxt('np_output.csv',w,delimiter=',',fmt='%s',header='name,dob,years,degree,first,last',comments='')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, you can inspect the so-generated file and verify it is a CSV."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Using pandas to parse CSV files"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`pandas` is the real power tool for this job."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           Name        DOB  Years Degree\n",
      "0   Alice Jones  12/1/1980      3     MS\n",
      "1     Bob Smith   1/1/1969      4     BS\n",
      "2     John Book   5/3/1980     11     BA\n",
      "3  Billy Blanks   6/9/2000      8     AA\n",
      "<class 'pandas.core.frame.DataFrame'>\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "d = pd.read_csv('example_1.csv')\n",
    "print d\n",
    "print type(d)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, we have read the CSV file as a `pandas` DataFrame which is a super-structure that sits on top of `numpy`. Let's examine the columns of this DataFrame.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index([Name, DOB, Years, Degree], dtype=object)\n"
     ]
    }
   ],
   "source": [
    "print d.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that there is an extra space after the `Name `. This potentially makes it hard to access the columns using pandas slicing. For example,"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    12/1/1980\n",
      "1     1/1/1969\n",
      "2     5/3/1980\n",
      "3     6/9/2000\n",
      "Name: DOB, dtype: object\n",
      "0     Alice Jones\n",
      "1       Bob Smith\n",
      "2       John Book\n",
      "3    Billy Blanks\n",
      "Name: Name, dtype: object\n"
     ]
    }
   ],
   "source": [
    "print d.DOB # this works great when the column header name has no spaces in it.\n",
    "print d['Name'] # you can also refer to columns using this syntax"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Luckily, this is not hard to fix. We just need to create another column that is free of these trailing spaces:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "d['name']=d['Name '] # easily create extra column\n",
    "print d.name         # now you can access this column using this syntax"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pandas is a lot more powerful than this! We can parse the columns by types individually by providing a `dtype` for each column as a dictionary."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "d = pd.read_csv('example_1.csv',dtype={'Name ':'S64','DOB':'S64','Years':int,'Degree':'S64'})\n",
    "print d"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, we can compute along the columns as we did before with `numpy`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print d.Years.mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also parse the `DOB` field to get a true timestamp instead of a string using the `parse_dates` keyword."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "metadata": {},
   "outputs": [],
   "source": [
    "d = pd.read_csv('example_1.csv',dtype={'Name':'S64',\n",
    "                                       'DOB':'S64',\n",
    "                                       'Years':int,\n",
    "                                       'Degree':'S64'},parse_dates=[1])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, we can compute with these `datetime` objects as in the following."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "212 days, 0:00:00\n"
     ]
    }
   ],
   "source": [
    "# difference in birthdays between Alice Jones and John Book\n",
    "print d.DOB[0] -  d.DOB[2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we now how many days are between the respective birthdays of Alice Jones and John Book."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%qtconsole"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           Name                 DOB  Years Degree  first    last\n",
      "0   Alice Jones 1980-12-01 00:00:00      3     MS  Alice   Jones\n",
      "1     Bob Smith 1969-01-01 00:00:00      4     BS    Bob   Smith\n",
      "2     John Book 1980-05-03 00:00:00     11     BA   John    Book\n",
      "3  Billy Blanks 2000-06-09 00:00:00      8     AA  Billy  Blanks\n"
     ]
    }
   ],
   "source": [
    "d['first']=map(lambda x:string.split(x)[0],d['Name'])\n",
    "d['last']=map(lambda x:string.split(x)[1],d['Name'])\n",
    "print d"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           Name                 DOB  Years Degree  first    last\n",
      "0   Alice Jones 1980-12-01 00:00:00      3     MS  Alice   Jones\n",
      "1     Bob Smith 1969-01-01 00:00:00      4     BS    Bob   Smith\n",
      "2     John Book 1980-05-03 00:00:00     11     BA   John    Book\n",
      "3  Billy Blanks 2000-06-09 00:00:00      8     AA  Billy  Blanks\n"
     ]
    }
   ],
   "source": [
    "print d"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Inject into a sqlite database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas.io.sql as pd_sql\n",
    "import sqlite3 as sql # sqlite3 is built into Python\n",
    "\n",
    "con = sql.connect(\"example_1.db\")\n",
    "pd_sql.write_frame(d,'data',con) # write to DB as table named \"data\"\n",
    "con.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "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.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}