onion/4-Explore.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 4. Explore the Data\n",
    "\n",
    "> \"I don't know, what I don't know\"\n",
    "\n",
    "We want to first **visually explore** the data to see if we can confirm some of our initial hypotheses as well as make new hypothesis about the problem we are trying to solve.\n",
    "\n",
    "For this we will start by loading the data and understanding the data structure of the dataframe we have."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Lets read the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Import the library we need, which is Pandas\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You will find the variable `df` used quite often to store a `dataframe`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Read the csv file of Monthwise Quantity and Price csv file we have.\n",
    "df = pd.read_csv('MonthWiseMarketArrivals_clean.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Understand Data Structure and Types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data Structure\n",
    "\n",
    "So we have ten columns in our dataset. Let us understand what each one is.\n",
    "\n",
    "Three are about the location of the Wholesale Market where Onion where sold.\n",
    "- **state**: This is the 2/3 letter abbreviation for the state in India (PB is Punjab and so on)\n",
    "- **city**: This is the city in India (ABOHAR, BANGALORE and so on)\n",
    "- **market**: This is a string with the combination of the state and city\n",
    "\n",
    "Three are related to the \n",
    "- **month**: Month in January, February and so on. \n",
    "- **year**: Year in YYYY representastion\n",
    "- **date**: The combination of the two above.\n",
    "\n",
    "Four are about quantity and price in these wholesale market. \n",
    "- **quantity**: The quanity of Onion arriving in the market in that month in quintals (100 kg)\n",
    "- **priceMin**: The minimum price in the month in Rs./quintal\n",
    "- **priceMax**: The maximum price in the month in Rs./quintal\n",
    "- **priceMod**: The modal price in the month in Rs./quintal\n",
    "\n",
    "We would expect the following the columns to be of the following type\n",
    "- **CATEGORICAL**: state, city, market\n",
    "- **TIME INTERVAL**: month, year, date\n",
    "- **QUANTITATIVE**: quantity, priceMin, priceMax, priceModal\n",
    "\n",
    "Let us see what pandas dataframe is reading these columns as."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Get the typeof each column\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So we are getting the quantitive columns are correctly being shown as integers and the categorical columns are showing as objects(strings) which is fine. \n",
    "However, the `date` columns is being read as an object and not a Time-Interval. Let us at least fix the `date` column and make it into a datetime object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Changing the date column to a Time Interval columnn\n",
    "df.date = pd.DatetimeIndex(df.date)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Now checking for type of each column\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Let us see the dataframe again now\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Question 1 - How big is the Bangalore onion market compared to other cities in India?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let us try to do this examination for one of the year only. So we want to reduce our dataframe for only where the year = 2010. This process is called subset.\n",
    "\n",
    "### PRINCIPLE: Subset a Dataframe\n",
    "\n",
    "![](../img/subsetrows.png)\n",
    "\n",
    "How do you subset a dataframe on a given criteria\n",
    "\n",
    "`newDataframe` = `df`[ <`subset condition`> ] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2010 = df[df.year == 2010]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2010.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# We can also subset on multiple criterias\n",
    "df2010Bang = df[(df.year == 2010) & (df.city == 'BANGALORE')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2010Bang.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###  Principle: Split Apply Combine\n",
    "\n",
    "How do we get the sum of quantity for each city.\n",
    "\n",
    "We need to **SPLIT** the data by each city, **APPLY** the sum to the quantity row and then **COMBINE** the data again\n",
    "\n",
    "\n",
    "![](../img/splitapplycombine.png)\n",
    "\n",
    "\n",
    "In pandas, we use the `groupby` function to do this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Group by using city\n",
    "df2010City = df2010.groupby(['city']).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2010City.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# If we only want to apply the sum function on quantity, then we specify the quantity column\n",
    "df2010City = df2010.groupby(['city']).quantity.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Let us see this dataframe\n",
    "df2010City.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# To create a dataframe again, it is best to specify index as false\n",
    "df2010City = df2010.groupby(['city'], as_index=False).quantity.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2010City.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Sort the Dataframe by Quantity to see which one is on top\n",
    "df2010City = df2010City.sort_values(by = \"quantity\", ascending = False)\n",
    "df2010City.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### PRINCIPLE: Visual Exploration \n",
    "Lets load the libraries required for plotting in python"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Load the visualisation libraries - Matplotlib\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Let us see the output plots in the notebook itself\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Set some parameters to get good visuals - style to ggplot and size to 15,10\n",
    "plt.style.use('ggplot')\n",
    "plt.rcParams['figure.figsize'] = (15, 10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Plot the Data\n",
    "df2010City.plot(kind =\"bar\", x = 'city', y = 'quantity')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise: Find the State with Highest Quantity Sales in 2015?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Question 2 - Have the price variation in Onion prices in Bangalore really gone up over the years?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dfBang = df[df.city == 'BANGALORE']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dfBang.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dfBang.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Reduce the precision of numbers - so that it is easy to read\n",
    "pd.set_option('precision', 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dfBang.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### PRINCIPLE: Setting Index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dfBang.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dfBang.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Set the index as date\n",
    "dfBang = dfBang.sort_values(by = \"date\")\n",
    "dfBang.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Set the Index for the Dataframe\n",
    "dfBang.index = pd.PeriodIndex(dfBang.date, freq='M')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dfBang.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dfBang.priceMod.plot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dfBang.plot(kind = \"line\", y = ['priceMin', 'priceMod', 'priceMax'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To calculate the range of change, we will create a new price difference variable - which is the difference between the priceMin and priceMax"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dfBang['priceDiff'] = dfBang['priceMax'] - dfBang['priceMin']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dfBang.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dfBang.plot(kind = 'line', y = 'priceDiff')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### PRINCIPLE: Pivot Table\n",
    "\n",
    "Pivot table is a way to summarize data frame data into index (rows), columns and value \n",
    "\n",
    "![](../img/pivot.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Create new variable for Integer Month\n",
    "dfBang['monthVal'] = pd.DatetimeIndex(dfBang['date']).month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dfBangPivot = pd.pivot_table(dfBang, values = \"priceDiff\", columns = \"year\", index = \"monthVal\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dfBangPivot.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dfBangPivot.plot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dfBangPivot.plot(subplots = True, figsize=(15, 15), layout=(3, 5), sharey=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise: Find the price variation for LASALGAON city? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "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.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}