Part 3.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Let's take it up to Eleven"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we're familiar with the basics of data cleaning and analysis in pandas, we're going to take it up a notch. The U.S. Census fortunately makes their data available in a clean and straightforward format. However, in many cases, the data we receive can be extremely messy and difficult to manage.\n",
    "\n",
    "That's why we're going to practice with a more unweildy. You can download the data [here](https://s3.amazonaws.com/nicar15/njaccidents.csv). You'll notice that it's quite a big file– about 1.7 million rows! These are reports from accidents in New Jersey between 2008 and 2013 from the New Jersey Department of Transportation. The data was originally scraped from [PDFs of crash reports](http://www.state.nj.us/transportation/refdata/accident/) filled out by clerk.\n",
    "\n",
    "Import pandas and let's load in our new and very messy data\n",
    "\n",
    "```python\n",
    "import pandas as pd\n",
    "njaccidents = pd.read_csv('njaccidents.csv')\n",
    "\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You may notice that you get this warning."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\"DtypeWarning: Columns (6,17) have mixed types. Specify dtype option on import or set low_memory=False. interactivity=interactivity, compiler=compiler, result=result)\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This dtype error happens when when a column has both strings and integer values. You can ignore this for now because we'll fix it soon. Open up the first few rows of our dataframe.\n",
    "\n",
    "```python\n",
    "njaccidents.head()\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's found out what we're working with, and get the column headers for all of the columns.\n",
    "\n",
    "```python\n",
    "njaccidents.columns\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Oh boy. There's our first problem. Notice that there's a leading space in every column header. We should take it out.\n",
    "\n",
    "```python\n",
    "njaccidents.rename(columns=lambda x: x.strip(), inplace=True)\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Remember earlier where we renamed the columns in our dataframe? This time, we're using the same rename function to do take out all of the leading spaces using strip(). Pythonistas will notice that we're using the lambda python to apply strip() to every single column header.\n",
    "\n",
    "```python\n",
    "njaccidents.columns\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Great. Let's describe() the dataframe.\n",
    "```python\n",
    "njaccidents.describe()\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "But let's see if we could describe() a column. Let's use the describe() function for the 'County Name' column header.\n",
    "\n",
    "```python\n",
    "njaccidents['County Name'].describe()\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Interesting. So there are 21 unique values in the 'County Name' column (for the 21 counties in New Jersey). We can see that the top county with the most rows is Middlesex County with 176,402 crashes. What are the names of the counties in New Jersey? Let's find out by using the unique() function on our 'County Name' column.\n",
    "```python\n",
    "njaccidents['County Name'].unique()\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Well, that's frustrating. Looks like we're going to need to strip out the spaces out of the these county values. This time we'll use the map() function which will strip the white space out of every string found in the column.\n",
    "\n",
    "```python\n",
    "njaccidents['County Name']=njaccidents['County Name'].map(str.strip)\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's test.\n",
    "\n",
    "```python\n",
    "njaccidents['County Name'].unique()\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Perfect. Speaking of strings, let's fix that dtype error we got at the beginning of the exercise. Type in dtypes at the end of our dataframe.\n",
    "\n",
    "```python\n",
    "njaccidents.dtypes\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This shows us the type of data type object (or dtypes) the values of every column are. Objects refer to strings. Int64 are integers. Float64 are floats.\n",
    "\n",
    "The warning at the beginning said it was column 6 & 17 that had mixed dtypes. If you look at your column list and count to the sixth column (Remember to count from zero!), you'll see that it's the 'Police Dept Code' column. Let's look at every unique value in that column.\n",
    "\n",
    "```python\n",
    "njaccidents['Police Dept Code'].unique()\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And there it is! As you can see, there are strings and integers mixed together in the same column.\n",
    "\n",
    "```python\n",
    "njaccidents['Crash Type Code'].unique()\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Same for column 17 or the 'Crash Type Code' column. Let's fix that by changing every value in both columns to a string using the astype() function.\n",
    "\n",
    "```python\n",
    "njaccidents['Police Dept Code']=njaccidents['Police Dept Code'].astype(str)\n",
    "njaccidents['Crash Type Code']=njaccidents['Crash Type Code'].astype(str)\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We're changing it to a string because we don't need to do math with these numbers since they are codes so it's more beneficial to use them as objects. If you wanted to change something to an integer or a float, you'll need to use astype(int) and astype(float) respectively.\n",
    "\n",
    "```python\n",
    "njaccidents['Police Dept Code'].unique()\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That took care of that!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's make our dataframe a little bit more manageable by weeding out some unnecessary columns. Let's also create a new dataframe called njcrashinfo.\n",
    "\n",
    "```python\n",
    "njcrashinfo = njaccidents[['County Name', 'Municipality Name', 'Crash Date', 'Crash Day Of Week', 'Crash Time', 'Total Killed', 'Total Injured', 'Pedestrians Killed', 'Pedestrians Injured', 'Total Vehicles Involved', 'Alcohol Involved', 'Cell Phone In Use Flag']]\n",
    "njcrashinfo\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# How many car accidents had alcohol involved?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's find out the unique values that come up in the column 'Alcohol Involved'.\n",
    "```python\n",
    "njcrashinfo['Alcohol Involved'].unique()\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We have only two unique values in the column. N for no and Y for yes.\n",
    "\n",
    "Let's find out how many incidents had Ns and how many had Ys. We're going to use the function value_counts() on the column 'Alcohol Involved'. We're also going to put the list in a new dataframe called alcohol so that it will look nicer in our notebook.\n",
    "\n",
    "```python\n",
    "alcohol = pd.DataFrame(njcrashinfo['Alcohol Involved'].value_counts())\n",
    "alcohol\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A lot more Ns than Ys. But just what percentage are the Ys compared to the Ns? First, let's get the total number of crashes in our data frame.\n",
    "\n",
    "```python\n",
    "njcrashcount =njcrashinfo['Alcohol Involved'].count()\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Careful. The count() function doesn't count NAs or null values. Always make sure to check for those using the isnull() function, followed by sum()\n",
    "\n",
    "```python\n",
    "njcrashinfo['Alcohol Involved'].isnull().sum()\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's create a new column named 'Percent' and divide every value of the 'Alcohol Involved' column by the total crashes from the 'njcrashcount' we created above and then multiply by 100.\n",
    "\n",
    "```python\n",
    "alcohol['Percent'] = (alcohol['Alcohol Involved']/njcrashcount)*100\n",
    "alcohol\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Mystery solved. Only 2.9 percent."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# How many total people were killed in every county?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's first use the value_counts() function to find out how many accidents were reported in each county.\n",
    "\n",
    "```python\n",
    "njcrashinfo['County Name'].value_counts()\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So let's split up every incident that happened in every county by using the groupby()\n",
    "```python\n",
    "njcrashinfo.groupby('County Name')\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That looks like it did nothing, but it actually DID split up the counties into their own seperate groups. We just need to know perform an action. If you notice, there are columns like 'Total Killed', 'Total Injured', 'Pedestrians Killed', etc. that have numbers or integers that can be summed up. Basically, we're going to add them all up by using the sum() function and make it into a new dataframe called njcountycrashes.\n",
    "\n",
    "```python\n",
    "njcountycrashes = njcrashinfo.groupby('County Name').sum()\n",
    "njcountycrashes\n",
    "```\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Well, that's grim. Let's just take out the 'Total Killed' column using iloc which asks what data we should slice by putting an integer based on its position. The first value represents the rows and is separated by comma from the second value which represents the columns. Therefore, if we want all of the rows, we put a colon. We then seperate using a comma. Then, because 'Total Killed' is the first column, we can slice it by putting in a zero. We will also sort it by using sort_values and adding the option 'ascending=False' because we want the values to descend. Let's make it into a new dataframe called countydeaths.\n",
    "\n",
    "```python\n",
    "countydeaths = njcrashinfo.groupby('County Name').sum().iloc[:,0].sort_values(ascending=False)\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's make 'countydeaths' into a dataframe.\n",
    "\n",
    "```python\n",
    "pd.DataFrame(countydeaths)\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# What about dates?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You may have noticed that the dates on the 'Crash Date' are strings and not Python date objects. This is bad because if you sort them you'll get '01/01/2008, 01/01/2009, 01/01/2010' etc. We want them to sort by date correctly, and in order to do that, we need to turn them into the Python date format. We will need to  `import datetime`  first. Then we will use  `apply()`  along with the lambda function to turn every string in that column into the format \"%m/%d/%Y\".\n",
    "\n",
    "```python\n",
    "from datetime import datetime\n",
    "njcrashinfo['Crash Date']=njcrashinfo['Crash Date'].apply(lambda x: datetime.strptime(x, \"%m/%d/%Y\").date())\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we're ready to groupby() the 'Crash Date' column every date in our dataframe and count how many accidents happened every day. And then we will slice the first column which is how many crashes happened each day using iloc. (Colon for all rows, comma, then 0 for the first column)\n",
    "\n",
    "```python\n",
    "crashesbydate = njcrashinfo.groupby('Crash Date').count().iloc[:,0]\n",
    "```\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's sort.\n",
    "\n",
    "```python\n",
    "crashesbydate.sort_values(ascending=False)\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Looks like on February 12, 2008 was a busy day for NJDOT with 3,050 accidents reported to happen that day."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's now save the following dataframes into csv.\n",
    "\n",
    "```python\n",
    "njcountycrashes.to_csv('_visuals/scatterplot.csv')\n",
    "countydeaths.to_csv('_visuals/bargraphs.csv')\n",
    "crashesbydate.to_csv('_visuals/linegraph.csv')\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "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
}