bangpypers_data_analysis_made_easy.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Analysis made easy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import html5lib\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reading data using read_html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### read_html will be handy when you want to extract the table data from html page. read_html will result a list containing one dataframe for each table in the html page"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "urls_dict = {\"http://www.siamindia.com/statistics.aspx?mpgid=8&pgidtrail=14\":\"Society of Indian Automobile Manufactures.html\",\n",
    "            \"http://www.valueresearchonline.com/funds/newsnapshot.asp?schemecode=3019\":\"Value Research Online.html\",\n",
    "            \"http://www.fdic.gov/bank/individual/failed/banklist.html\":\"FDIC_ Failed Bank List.html\"}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def get_url(url):\n",
    "    try:\n",
    "        import requests\n",
    "        r = requests.get(url,timeout=2)\n",
    "        return url\n",
    "    except:\n",
    "        return urls_dict[url]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "auto_sales_url = get_url(\"http://www.siamindia.com/statistics.aspx?mpgid=8&pgidtrail=14\")\n",
    "table_list = pd.read_html(auto_sales_url,header=0)\n",
    "auto_sales_df = table_list[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print(auto_sales_url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "len(table_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "auto_sales_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "auto_sales_df = auto_sales_df.set_index(\"Category\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "auto_sales_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# finding the shape of the data frame ( rows,  columns)\n",
    "auto_sales_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# extractig the column data\n",
    "auto_sales_df[\"2010-11\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "auto_sales_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "auto_sales_df.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "auto_sales_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# extracting the row data\n",
    "auto_sales_df.loc[\"Passenger Vehicles\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "auto_sales_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "auto_sales_df = auto_sales_df.drop(\"Grand Total\", axis=0)\n",
    "auto_sales_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "auto_sales_graph = auto_sales_df.T.plot(kind='barh',figsize=(14,6),title=\"Auto Sales\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# html page containing multiple tables in the same html\n",
    "funds_url = get_url('http://www.valueresearchonline.com/funds/newsnapshot.asp?schemecode=3019')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print(funds_url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_list = pd.read_html(funds_url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "len(df_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_list[0].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Extracting one of the multiple tables by matching to a string\n",
    "#filtered_df_list = pd.read_html(funds_url,match='Rank within Category')\n",
    "filtered_df_list = pd.read_html(funds_url,match='Rank within Category',header=0,index_col=0)\n",
    "#skiprows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "len(filtered_df_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "filtered_df_list[0].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Copying the data frame explictly into another dataframe\n",
    "# selecting only the required columns\n",
    "df = filtered_df_list[0].iloc[:,[0,1,2,4,5]].copy()\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Converting column data types\n",
    "for column in df.columns:    \n",
    "    df[column] = df[column].astype(np.float16)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df.loc[[\"Fund\",\"Category\",\"Nifty Free Float Midcap 100\"]].plot(figsize=(12,6),kind='bar',rot=0)\n",
    "#df.loc[[\"Fund\",\"Category\",\"Nifty Midcap 100\"]].T.plot(figsize=(12,6),kind='bar',rot=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# using attrs in read_html for filtering the data\n",
    "df_attr_list = pd.read_html(funds_url,\n",
    "                            attrs={\"id\":\"fund-snapshot-peer-comparison-table\"},\n",
    "                            header=0,index_col=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_attr_list[0].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df = df_attr_list[0].dropna(axis=1).copy()\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df[\"Assets (Cr)\"] = df[\"Assets (Cr)\"].astype(np.int32)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df[[\"1-Year Ret\",\"3-Year Ret\",\"5-Year Ret\"]].T.plot(figsize=(18,6),kind='bar',rot=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "bank_url = get_url('http://www.fdic.gov/bank/individual/failed/banklist.html')\n",
    "#bank_df_list = pd.read_html(url)\n",
    "bank_df_list = pd.read_html(bank_url,parse_dates=[5,6,9])\n",
    "bank_df = bank_df_list[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print(bank_url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "bank_df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# let us see if there are records with same Bank Name\n",
    "bank_df_name_duplicates = bank_df[bank_df[\"Bank Name\"].duplicated()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Check the data types for date columns\n",
    "bank_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# filtering for duplicated record and sorting by Bank Name\n",
    "bank_df[bank_df[\"Bank Name\"].isin(bank_df_name_duplicates[\"Bank Name\"])].sort_values(by=\"Bank Name\").head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# It looks like the Bank Name and City makes the unique combination\n",
    "# Let's check if there are any duplicates with this combination\n",
    "bank_df[bank_df[[\"Bank Name\",\"City\"]].duplicated()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print bank_df[\"Closing Date\"].min()\n",
    "print bank_df[\"Closing Date\"].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "bank_df[\"Closing Date\"].map(lambda x: x.year).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Let us find the number of banks closed in each year using map\n",
    "bank_df[\"Closing Date\"].map(lambda x: x.year).value_counts().plot(kind='bar')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Let us find the number of banks closed in each year using groupby\n",
    "bank_df_group = bank_df.groupby([bank_df[\"Closing Date\"].map(lambda x: x.year)])\n",
    "type(bank_df_group)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# plot the number of banks closed by each year\n",
    "bank_df_group.count()[\"Bank Name\"].plot(kind='bar')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Number of banks closed by each state between 2000 and 2015\n",
    "bank_df[\"ST\"].value_counts().plot(kind='bar',figsize=(20,4))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# get the state names from another url\n",
    "states_df = pd.read_html(\"http://www.infoplease.com/ipa/A0110468.html\", attrs={\"id\":\"A0110469\"},header=0)[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "states_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# check the dataframe for GA state\n",
    "states_df[states_df[\"Postal Code\"]==\"GA\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Join the state names to bank_df dataframe using merge\n",
    "bank_df_state_names= pd.merge(bank_df,states_df,\n",
    "                              how=\"left\",\n",
    "                              left_on=\"ST\",\n",
    "                              right_on=\"Postal Code\",\n",
    "                              sort=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "bank_df_state_names.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# number of banks closed by state name\n",
    "bank_df_state_names[\"State\"].value_counts().plot(kind='bar',figsize=(20,4))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reading data using read_clipboard"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### somtimes if you want to bring data copied in clip board into pandas dataframe, you can use read_clipboard"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Fund\tRating\tCategory\tLaunch\tExpense\t1-Year(Return)\t1-Year(Rank)\tNet Assets(Cr)\n",
    "Birla Sun Life MNC Fund \tUnrated\tEQ-OTH  \tApr-1994\t2.43\t26.11\t0\t2704\n",
    "SBI Pharma Fund \tUnrated\tEQ-PH  \tJul-1999\t2.68\t20.74\t2/6\t862\n",
    "Escorts High Yield Equity Fund \tUnrated\tEQ-SC  \tDec-2006\t2.5\t19.58\t5/41\t3\n",
    "Motilal Oswal MOSt Focused Multicap 35 Fund - Regular Plan \tUnrated\tEQ-MLC  \tApr-2014\t2.35\t19.4\t2/187\t2914\n",
    "Motilal Oswal MOSt Focused Midcap 30 Fund - Regular Plan \tUnrated\tEQ-MC  \tFeb-2014\t2.63\t19.28\t2/78\t816\n",
    "DSP BlackRock Micro Cap Fund - Regular Plan \t4*\tEQ-SC  \tJun-2007\t2.41\t18.84\t6/41\t2191\n",
    "UTI MNC Fund \tUnrated\tEQ-OTH  \tAug-2005\t2.75\t18.31\t0\t1699\n",
    "Motilal Oswal MOSt Shares NASDAQ - 100 ETF Fund \tUnrated\tEQ-INTL  \tMar-2011\t1.06\t17.68\t0\t76\n",
    "SBI Magnum Midcap Fund \t4*\tEQ-SC  \tMar-2005\t2.54\t16.4\t8/41\t1258\n",
    "Reliance Pharma Fund \tUnrated\tEQ-PH  \tJun-2004\t2.5\t16.28\t4/6\t1580"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#parse_dates=[3]\n",
    "#top10_funds_df = pd.read_clipboard()\n",
    "top10_funds_df = pd.read_clipboard(parse_dates=[3])\n",
    "top10_funds_df.head()\n",
    "#You may need to install xclip or xsel (with gtk or PyQt4 modules) on Linux to use these methods."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "top10_funds_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "column_names = ['Table Name','State Code','District Code','Area Name','Age',\n",
    "                    'Persons-Total','Males-Total','Females-Total',\n",
    "                    'Persons-Rural','Males-Rural','Females-Rural',\n",
    "                    'Persons-Urban','Males-Urban','Females-Urban']\n",
    "excel_df = pd.read_excel(\"DDW-0000C-13.xls\",skiprows=6, names = column_names)\n",
    "#excel_df.columns = column_names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "excel_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Filtering data for karnata state\n",
    "req_columns = [\"Age\",\"Males-Rural\",\"Females-Rural\",\"Males-Urban\",\"Females-Urban\"]\n",
    "kar_filter = (excel_df[\"Area Name\"] == \"State - KARNATAKA (29)\") & (excel_df[\"Age\"] != \"All ages\") & (excel_df[\"Age\"] != \"Age not stated\")\n",
    "excel_kar_df = excel_df[kar_filter][req_columns].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "excel_kar_df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "excel_kar_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# find unique values in Age Column\n",
    "excel_kar_df.Age.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Converting Age to integer data type\n",
    "excel_kar_df.Age = excel_kar_df.Age.apply(lambda x:int(str(x).replace('+','')))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "excel_kar_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# let's plot the graph and observe\n",
    "# save the graph to a file\n",
    "kar_graph = excel_kar_df.set_index(\"Age\").plot(figsize=(20,10))\n",
    "#kar_graph  = excel_kar_df.set_index(\"Age\").plot(figsize=(16,10),subplots=True)\n",
    "#kar_graph = excel_kar_df.set_index(\"Age\").plot(figsize=(16,10),subplots=True,ylim=(0,600000))\n",
    "plt.savefig(\"kar_graph.jpeg\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# let us observe the Age not stated by state\n",
    "age_filter = (excel_df[\"Age\"] == \"Age not stated\") & (excel_df[\"Area Name\"] != \"India\")\n",
    "required_columns = [\"Area Name\",\"Males-Rural\",\"Females-Rural\",\"Males-Urban\",\"Females-Urban\"]\n",
    "excel_df_age_ns = excel_df[age_filter][required_columns]\n",
    "excel_df_age_ns = excel_df_age_ns.set_index(\"Area Name\")\n",
    "age_graph = excel_df_age_ns.plot(kind='bar',figsize=(18,6))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# let us look at the distribution\n",
    "kar_age_graph = excel_kar_df.set_index(\"Age\").plot(figsize=(12,6),kind='box')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# describe gives a stats summary of all of the numeric fields\n",
    "excel_kar_df.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reading data using read_csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### One of the very commonly used method is read_csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# district wise rail fall data from 1951 to 2000\n",
    "data = pd.read_csv(\"distirct_railfaill_1951_2000.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "data.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# copy the Karnataka rain fall data into a separate dataframe\n",
    "data_kar = data[data[\"STATE/UT\"]==\"KARNATAKA\"].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# drop STATE_UT column and set DISTRICT as the index\n",
    "data_kar = data_kar.drop(\"STATE/UT\", axis=1)\n",
    "data_kar = data_kar.set_index(\"DISTRICT\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Keep only required columns which are monts from JAN to DEC\n",
    "data_kar = data_kar[[u'JAN', u'FEB', u'MAR', u'APR', u'MAY', u'JUN', u'JUL', u'AUG', u'SEP',\n",
    "       u'OCT', u'NOV', u'DEC']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "data_kar.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "data_kar.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Let us create the axes plot obect and use set_ylable and set_title methods\n",
    "#data_kar.plot?\n",
    "ax = data_kar.describe().T.plot(colormap=plt.cm.Accent_r ,figsize=(12,6),grid=True)\n",
    "ax.set_ylabel(\"rain fall(mm)\",fontdict={'size':12})\n",
    "ax.set_title(\"Rail fall in Karnata(1951-2000)\",fontdict={'size':12})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "### Using default values while reading csv file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# let us look at a bit complicated csv file and see how we can use all of the options\n",
    "# available with read_csv method\n",
    "!head -10 GLB.Ts+dSST.txt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#pd.read_csv?\n",
    "required_columns = [\"Year\",\"Jan\",\"Feb\",\"Mar\",\"Apr\",\"May\",\"Jun\",\"Jul\",\"Aug\",\"Sep\",\"Oct\",\"Nov\",\"Dec\"]\n",
    "glb_temp_df = pd.read_csv(\"GLB.Ts+dSST.txt\",skiprows=7,usecols=required_columns,\n",
    "                      skip_footer=12,engine='python',sep=\"\\s+\",na_values=['****','***'],\n",
    "                      index_col=0\n",
    "                      )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "glb_temp_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# there are headers in between the data sets. Let us remove these headers from the file\n",
    "# please note that index can have duplicates\n",
    "glb_temp_df.loc[\"Year\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "glb_temp_df = glb_temp_df[glb_temp_df[\"Jan\"] != \"Jan\"]\n",
    "glb_temp_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "glb_temp_df.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "glb_temp_df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "glb_temp_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# let us convert the teamperatue columns to int\n",
    "# let fill the NA values with forward fill\n",
    "for col in glb_temp_df.columns:\n",
    "    glb_temp_df[col] = glb_temp_df[col].ffill().astype(np.int64)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "glb_temp_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "glb_temp_df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# mean temp by year\n",
    "glb_temp_df.mean(axis=1).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# mean temp by month\n",
    "glb_temp_df.mean(axis=0).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "glb_temp_df.mean(axis=0).plot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "glb_temp_df.mean(axis=1).plot(figsize=(20,4))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exploring groupby functionality"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Let us take the rain fall data\n",
    "data.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# first create the group by object\n",
    "data_gb = data.groupby(\"STATE/UT\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "type(data_gb)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "len(data_gb)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "len(data[\"STATE/UT\"].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "for name,group in data_gb:\n",
    "    print(name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#gettig one groups data\n",
    "type(data_gb.get_group(\"KARNATAKA\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "data_gb_mean = data_gb.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "type(data_gb_mean)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "data_gb_mean.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#data_gb.groups['ANDAMAN And NICOBAR ISLANDS']\n",
    "data_gb.get_group('ANDAMAN And NICOBAR ISLANDS')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "months_q1 = [\"JAN\",\"FEB\",\"MAR\"]\n",
    "months_q2 = [\"APR\",\"MAY\",\"JUN\"]\n",
    "months_q3 = [\"JUL\",\"AUG\",\"SEP\"]\n",
    "months_q4 = [\"OCT\",\"NOV\",\"DEC\"]\n",
    "#months = [\"MAR\",\"JUN\",\"SEP\",\"DEC\"]\n",
    "ax= data_gb[months_q4].sum().plot(figsize=(20,8),kind='bar',subplots=True,legend=False,ylim=(0,6500))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "data_gb_quarter = data_gb.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "quarters = {'Q1':months_q1,'Q2':months_q2,'Q3':months_q3,'Q4':months_q4}\n",
    "for quarter in quarters:\n",
    "    #print quarters[quarter]\n",
    "    data_gb_quarter[quarter] = (data_gb_quarter[quarters[quarter]]).mean(axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "data_gb_quarter[['Q1','Q2','Q3','Q4']].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "ax = data_gb_quarter[['Q1','Q2','Q3','Q4']].plot(kind='bar',figsize=(16,6))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [Root]",
   "language": "python",
   "name": "Python [Root]"
  },
  "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.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}