Weekly/Week_2/Loading-Data-in-Pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Loading Data in Pandas\n",
    "\n",
    "Pandas is a very powerful library for manipulating data. The documentation can be found online [here](http://pandas.pydata.org/pandas-docs/version/0.17.1/#). \n",
    "\n",
    "Things we'll cover in this tutorial including using Pandas to:\n",
    "- Load data from a .csv file, write to a .csv file, do some  transformations like column renamings and data/time munging.\n",
    "- Load excel data from a file\n",
    "- Load JSON data from a file\n",
    "- Load JSON data directly from an API"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In order to use the Pandas library we need to import it. The convention is to call it `pd`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are a variety of 'magic' functions that can be useful in Jupyter Notebooks. These are prefixed by the '%' character. Here we're interested to know the current directory which we can learn by using the `%pwd` magic function. To see an entire listing of available magic functions use `%quickref`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%pwd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we know the current directory we can construct the correct relative path to our data file and load the file into a variable called `robo_call_data`. Here's a [link to download](https://www.dropbox.com/s/c6aqckmv1ms1l5q/Telemarketing_RoboCall_Weekly_Data.csv?dl=0) the data to your local computer. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "robo_call_data = pd.read_csv(\"Data/Telemarketing_RoboCall_Weekly_Data.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's see what's in that variable and print it out by entering the variable name on a line by itself. In order to see it nicely printed like this it needs to be on the last line of the code block. You can also ``print robo_call_data`` but it won't look as nice."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "robo_call_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice how it has numbered the rows (that is called the index column) and how the first row is a header row with the names of the columns. Also note that for data that's missing it says \"NaN\". \n",
    "\n",
    "There are many parameters that make `read_csv` very flexible. Do not be afraid of reading the documentation which you [can find on the Pandas site](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_csv.html)\n",
    "\n",
    "Let's say we want to rename the columns so they're shorter and easier to type:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "robo_call_data = pd.read_csv(\"Data/Telemarketing_RoboCall_Weekly_Data.csv\", skiprows=[0], names=[\"issues\", 'time_issued', 'caller_id', 'phone_number', 'type_robo', 'type_telemarketing', 'state', 'date_issued', 'date_created'])\n",
    "robo_call_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's also parse the date columns. Note in the output they now follow a standard formatting of YYYY-MM-DD when printed, however internally they're stored as python [datetime objects](https://docs.python.org/2/library/datetime.html)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "robo_call_data = pd.read_csv(\"Data/Telemarketing_RoboCall_Weekly_Data.csv\", skiprows=[0], names=[\"issues\", 'time_issued', 'caller_id', 'phone_number', 'type_robo', 'type_telemarketing', 'state', 'date_issued', 'date_created'], parse_dates=['date_issued', 'date_created'])\n",
    "robo_call_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the real world data is messy. Very messy. This dataset was originally [downloaded from the FTC](https://consumercomplaints.fcc.gov/hc/en-us/articles/205239443-Data-on-Unwanted-Calls). Look at the `time_issued` column above. The first row is empty, denoted by a \"-\", in the 2nd row it has \"AM\" and in the 3rd row it has a different format, with periods, \"p.m.\". This all needs to be normalized into a uniform format so that python can understand the data as datetime objects.\n",
    "\n",
    "The code below does this normalization. For each row of the data it applies the `parse_time` function which first checks that it has valid data for the `time_issued` and `date_issued` columns. Then it removes any spurious characters that would throw off the time conversion. Finally, it merges the date_issued column with the time_issued column and converts it into a datetime object which it returns. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import datetime\n",
    "import string\n",
    "#pd.to_datetime(robo_call_data.time_issued, format='%I:%M %p', errors='ignore')\n",
    "#time.strptime(\"8:20 AM\", '%I:%M %p')\n",
    "def parse_time(t):\n",
    "    if t[\"time_issued\"] == \"-\" or pd.isnull(t[\"time_issued\"]):\n",
    "        return None\n",
    "    if pd.isnull(t[\"date_issued\"]):\n",
    "        return None\n",
    "    #print pd.isnull(t[\"date_issued\"])\n",
    "    t[\"time_issued\"] = t[\"time_issued\"].replace(\".\", \"\")\n",
    "    t[\"time_issued\"] = t[\"time_issued\"].replace(\",\", \"\")\n",
    "    t[\"time_issued\"] = t[\"time_issued\"].replace(\">\", \"\")\n",
    "    t[\"time_issued\"] = t[\"time_issued\"].replace(\"?\", \"\")\n",
    "    t[\"time_issued\"] = t[\"time_issued\"].replace(\"A:M:\", \"AM\")\n",
    "    t[\"time_issued\"] = t[\"time_issued\"].replace(\"P:M:\", \"PM\")\n",
    "    t[\"time_issued\"] = t[\"time_issued\"].replace(\"Mm\", \"M\")\n",
    "    t[\"time_issued\"] = t[\"time_issued\"].replace(\"/\", \"\")\n",
    "    t[\"time_issued\"] = t[\"time_issued\"].replace(\"'\", \"\")\n",
    "    #print t[\"time_issued\"]\n",
    "    return datetime.datetime.strptime(t['date_issued'].strftime('%Y-%m-%d') + ' ' + t['time_issued'], '%Y-%m-%d %I:%M %p')\n",
    "    \n",
    "robo_call_data['time_issued'] = robo_call_data.apply(lambda row: parse_time(row), axis=1)\n",
    "robo_call_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And we can then export this data back to .csv, using a parameter to strip the index column back out. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "robo_call_data.to_csv(\"Data/Telemarketing_RoboCall_Weekly_Data_Transformed.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With Pandas you can also load in files in excel format, first by loading the file, then by parsing each sheet. In this case there is just one sheet called `Sheet1`. Here is the [link to download](https://www.dropbox.com/s/4gnavcblspydmud/Telemarketing_RoboCall_Weekly_Data.xlsx?dl=0) the .xlsx file. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "xlsx_file = pd.ExcelFile(\"Data/Telemarketing_RoboCall_Weekly_Data.xlsx\")\n",
    "xlsx_data = xlsx_file.parse(\"Sheet1\")\n",
    "xlsx_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "At times you may come across other types of data besides .csv's. JSON is another popular format. You can also load JSON files into Pandas dataframes, but it's important to know that there is often a lot of nesting within JSON and you'll have to think through how to map each JSON variable to a column of your dataframe. Here is the [link to download](https://www.dropbox.com/s/zz8iqq4c4eutwm7/sample_tweets.json?dl=0) the sample_tweets.json file used below. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import json\n",
    "json_file = open('Data/sample_tweets.json')\n",
    "json_tweets = json.load(json_file)\n",
    "# Uncomment the next line to see the structure of the json file\n",
    "#json_tweets\n",
    "# There are many more columns we could create in this dataframe, but we just grab 5 here. \n",
    "df = pd.DataFrame(json_tweets[\"data\"], columns=[\"id\", \"text\", \"favorite_count\", \"retweet_count\", \"lang\"])\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And we can access data from APIs (Application Programming Interfaces) which are really just URLs that when you load them return data. For instance, we can use the NYT [Books API](http://developer.nytimes.com/io-docs) to get the latest list of the top selling non-fiction books, as a JSON data structure. Based on the API documentation we could retrieve other data for other lists, or in past weeks as well."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Requests is a library that allows you to load a URL\n",
    "# Json is a library that lets you work with json objects (e.g. to convert them from string)\n",
    "import requests, json \n",
    "url = 'http://api.nytimes.com/svc/books/v3/lists/hardcover-nonfiction.json?api-key=sample-key' \n",
    "api_response = requests.get(url).json()\n",
    "api_response"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that there is a bunch of metadata that we might not immediately need in our analysis (e.g. copyright information, or last modified status). What we really want in our data frame is the `books` list variable which we can load in with the built-in `read_json` pandas function: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "books_data = pd.read_json(json.dumps(api_response[\"results\"][\"books\"]))\n",
    "books_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}