pandas_part3.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Reading, writing CSV and Excel file <br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Formatted Date</th>\n",
       "      <th>Summary</th>\n",
       "      <th>Precip Type</th>\n",
       "      <th>Temperature (C)</th>\n",
       "      <th>Apparent Temperature (C)</th>\n",
       "      <th>Humidity</th>\n",
       "      <th>Wind Speed (km/h)</th>\n",
       "      <th>Wind Bearing (degrees)</th>\n",
       "      <th>Visibility (km)</th>\n",
       "      <th>Loud Cover</th>\n",
       "      <th>Pressure (millibars)</th>\n",
       "      <th>Daily Summary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2006-04-01 00:00:00.000 +0200</td>\n",
       "      <td>Partly Cloudy</td>\n",
       "      <td>rain</td>\n",
       "      <td>9.472222</td>\n",
       "      <td>7.388889</td>\n",
       "      <td>0.89</td>\n",
       "      <td>14.1197</td>\n",
       "      <td>251.0</td>\n",
       "      <td>15.8263</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1015.13</td>\n",
       "      <td>Partly cloudy throughout the day.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2006-04-01 01:00:00.000 +0200</td>\n",
       "      <td>Partly Cloudy</td>\n",
       "      <td>rain</td>\n",
       "      <td>9.355556</td>\n",
       "      <td>7.227778</td>\n",
       "      <td>0.86</td>\n",
       "      <td>14.2646</td>\n",
       "      <td>259.0</td>\n",
       "      <td>15.8263</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1015.63</td>\n",
       "      <td>Partly cloudy throughout the day.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2006-04-01 02:00:00.000 +0200</td>\n",
       "      <td>Mostly Cloudy</td>\n",
       "      <td>rain</td>\n",
       "      <td>9.377778</td>\n",
       "      <td>9.377778</td>\n",
       "      <td>0.89</td>\n",
       "      <td>3.9284</td>\n",
       "      <td>204.0</td>\n",
       "      <td>14.9569</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1015.94</td>\n",
       "      <td>Partly cloudy throughout the day.</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Formatted Date        Summary Precip Type  Temperature (C)  \\\n",
       "0  2006-04-01 00:00:00.000 +0200  Partly Cloudy        rain         9.472222   \n",
       "1  2006-04-01 01:00:00.000 +0200  Partly Cloudy        rain         9.355556   \n",
       "2  2006-04-01 02:00:00.000 +0200  Mostly Cloudy        rain         9.377778   \n",
       "\n",
       "   Apparent Temperature (C)  Humidity  Wind Speed (km/h)  \\\n",
       "0                  7.388889      0.89            14.1197   \n",
       "1                  7.227778      0.86            14.2646   \n",
       "2                  9.377778      0.89             3.9284   \n",
       "\n",
       "   Wind Bearing (degrees)  Visibility (km)  Loud Cover  Pressure (millibars)  \\\n",
       "0                   251.0          15.8263         0.0               1015.13   \n",
       "1                   259.0          15.8263         0.0               1015.63   \n",
       "2                   204.0          14.9569         0.0               1015.94   \n",
       "\n",
       "                       Daily Summary  \n",
       "0  Partly cloudy throughout the day.  \n",
       "1  Partly cloudy throughout the day.  \n",
       "2  Partly cloudy throughout the day.  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Reading CSV file\n",
    "d=pd.read_csv('datasets/weather.csv')\n",
    "d.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>pclass</th>\n",
       "      <th>survived</th>\n",
       "      <th>name</th>\n",
       "      <th>sex</th>\n",
       "      <th>age</th>\n",
       "      <th>sibsp</th>\n",
       "      <th>parch</th>\n",
       "      <th>ticket</th>\n",
       "      <th>fare</th>\n",
       "      <th>cabin</th>\n",
       "      <th>embarked</th>\n",
       "      <th>boat</th>\n",
       "      <th>body</th>\n",
       "      <th>home.dest</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Allen, Miss. Elisabeth Walton</td>\n",
       "      <td>female</td>\n",
       "      <td>29</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>24160</td>\n",
       "      <td>211.3375</td>\n",
       "      <td>B5</td>\n",
       "      <td>S</td>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "      <td>St Louis, MO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Allison, Master. Hudson Trevor</td>\n",
       "      <td>male</td>\n",
       "      <td>0.9167</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>113781</td>\n",
       "      <td>151.5500</td>\n",
       "      <td>C22 C26</td>\n",
       "      <td>S</td>\n",
       "      <td>11</td>\n",
       "      <td>None</td>\n",
       "      <td>Montreal, PQ / Chesterville, ON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Allison, Miss. Helen Loraine</td>\n",
       "      <td>female</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>113781</td>\n",
       "      <td>151.5500</td>\n",
       "      <td>C22 C26</td>\n",
       "      <td>S</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Montreal, PQ / Chesterville, ON</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   pclass  survived                            name     sex     age  sibsp  \\\n",
       "0       1         1   Allen, Miss. Elisabeth Walton  female      29      0   \n",
       "1       1         1  Allison, Master. Hudson Trevor    male  0.9167      1   \n",
       "2       1         0    Allison, Miss. Helen Loraine  female       2      1   \n",
       "\n",
       "   parch  ticket      fare    cabin embarked  boat  body  \\\n",
       "0      0   24160  211.3375       B5        S     2  None   \n",
       "1      2  113781  151.5500  C22 C26        S    11  None   \n",
       "2      2  113781  151.5500  C22 C26        S  None  None   \n",
       "\n",
       "                         home.dest  \n",
       "0                     St Louis, MO  \n",
       "1  Montreal, PQ / Chesterville, ON  \n",
       "2  Montreal, PQ / Chesterville, ON  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Reading excel file\n",
    "df=pd.read_excel('datasets/titanic.xls')\n",
    "df.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### If you want to read only few rows rather than all"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Formatted Date</th>\n",
       "      <th>Summary</th>\n",
       "      <th>Precip Type</th>\n",
       "      <th>Temperature (C)</th>\n",
       "      <th>Apparent Temperature (C)</th>\n",
       "      <th>Humidity</th>\n",
       "      <th>Wind Speed (km/h)</th>\n",
       "      <th>Wind Bearing (degrees)</th>\n",
       "      <th>Visibility (km)</th>\n",
       "      <th>Loud Cover</th>\n",
       "      <th>Pressure (millibars)</th>\n",
       "      <th>Daily Summary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2006-04-01 00:00:00.000 +0200</td>\n",
       "      <td>Partly Cloudy</td>\n",
       "      <td>rain</td>\n",
       "      <td>9.472222</td>\n",
       "      <td>7.388889</td>\n",
       "      <td>0.89</td>\n",
       "      <td>14.1197</td>\n",
       "      <td>251.0</td>\n",
       "      <td>15.8263</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1015.13</td>\n",
       "      <td>Partly cloudy throughout the day.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2006-04-01 01:00:00.000 +0200</td>\n",
       "      <td>Partly Cloudy</td>\n",
       "      <td>rain</td>\n",
       "      <td>9.355556</td>\n",
       "      <td>7.227778</td>\n",
       "      <td>0.86</td>\n",
       "      <td>14.2646</td>\n",
       "      <td>259.0</td>\n",
       "      <td>15.8263</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1015.63</td>\n",
       "      <td>Partly cloudy throughout the day.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2006-04-01 02:00:00.000 +0200</td>\n",
       "      <td>Mostly Cloudy</td>\n",
       "      <td>rain</td>\n",
       "      <td>9.377778</td>\n",
       "      <td>9.377778</td>\n",
       "      <td>0.89</td>\n",
       "      <td>3.9284</td>\n",
       "      <td>204.0</td>\n",
       "      <td>14.9569</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1015.94</td>\n",
       "      <td>Partly cloudy throughout the day.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2006-04-01 03:00:00.000 +0200</td>\n",
       "      <td>Partly Cloudy</td>\n",
       "      <td>rain</td>\n",
       "      <td>8.288889</td>\n",
       "      <td>5.944444</td>\n",
       "      <td>0.83</td>\n",
       "      <td>14.1036</td>\n",
       "      <td>269.0</td>\n",
       "      <td>15.8263</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1016.41</td>\n",
       "      <td>Partly cloudy throughout the day.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2006-04-01 04:00:00.000 +0200</td>\n",
       "      <td>Mostly Cloudy</td>\n",
       "      <td>rain</td>\n",
       "      <td>8.755556</td>\n",
       "      <td>6.977778</td>\n",
       "      <td>0.83</td>\n",
       "      <td>11.0446</td>\n",
       "      <td>259.0</td>\n",
       "      <td>15.8263</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1016.51</td>\n",
       "      <td>Partly cloudy throughout the day.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2006-04-01 05:00:00.000 +0200</td>\n",
       "      <td>Partly Cloudy</td>\n",
       "      <td>rain</td>\n",
       "      <td>9.222222</td>\n",
       "      <td>7.111111</td>\n",
       "      <td>0.85</td>\n",
       "      <td>13.9587</td>\n",
       "      <td>258.0</td>\n",
       "      <td>14.9569</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1016.66</td>\n",
       "      <td>Partly cloudy throughout the day.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2006-04-01 06:00:00.000 +0200</td>\n",
       "      <td>Partly Cloudy</td>\n",
       "      <td>rain</td>\n",
       "      <td>7.733333</td>\n",
       "      <td>5.522222</td>\n",
       "      <td>0.95</td>\n",
       "      <td>12.3648</td>\n",
       "      <td>259.0</td>\n",
       "      <td>9.9820</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1016.72</td>\n",
       "      <td>Partly cloudy throughout the day.</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Formatted Date        Summary Precip Type  Temperature (C)  \\\n",
       "0  2006-04-01 00:00:00.000 +0200  Partly Cloudy        rain         9.472222   \n",
       "1  2006-04-01 01:00:00.000 +0200  Partly Cloudy        rain         9.355556   \n",
       "2  2006-04-01 02:00:00.000 +0200  Mostly Cloudy        rain         9.377778   \n",
       "3  2006-04-01 03:00:00.000 +0200  Partly Cloudy        rain         8.288889   \n",
       "4  2006-04-01 04:00:00.000 +0200  Mostly Cloudy        rain         8.755556   \n",
       "5  2006-04-01 05:00:00.000 +0200  Partly Cloudy        rain         9.222222   \n",
       "6  2006-04-01 06:00:00.000 +0200  Partly Cloudy        rain         7.733333   \n",
       "\n",
       "   Apparent Temperature (C)  Humidity  Wind Speed (km/h)  \\\n",
       "0                  7.388889      0.89            14.1197   \n",
       "1                  7.227778      0.86            14.2646   \n",
       "2                  9.377778      0.89             3.9284   \n",
       "3                  5.944444      0.83            14.1036   \n",
       "4                  6.977778      0.83            11.0446   \n",
       "5                  7.111111      0.85            13.9587   \n",
       "6                  5.522222      0.95            12.3648   \n",
       "\n",
       "   Wind Bearing (degrees)  Visibility (km)  Loud Cover  Pressure (millibars)  \\\n",
       "0                   251.0          15.8263         0.0               1015.13   \n",
       "1                   259.0          15.8263         0.0               1015.63   \n",
       "2                   204.0          14.9569         0.0               1015.94   \n",
       "3                   269.0          15.8263         0.0               1016.41   \n",
       "4                   259.0          15.8263         0.0               1016.51   \n",
       "5                   258.0          14.9569         0.0               1016.66   \n",
       "6                   259.0           9.9820         0.0               1016.72   \n",
       "\n",
       "                       Daily Summary  \n",
       "0  Partly cloudy throughout the day.  \n",
       "1  Partly cloudy throughout the day.  \n",
       "2  Partly cloudy throughout the day.  \n",
       "3  Partly cloudy throughout the day.  \n",
       "4  Partly cloudy throughout the day.  \n",
       "5  Partly cloudy throughout the day.  \n",
       "6  Partly cloudy throughout the day.  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=pd.read_csv('datasets/weather.csv',nrows=7)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Changing specific values with NaN while reading\n",
    " * While reading the dataset you can change the specific value with NaN\n",
    "   > df = pd.read_excel ( \" titanic.xls \" , na_values = [ list of element which you want to change to NaN ] ) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>pclass</th>\n",
       "      <th>survived</th>\n",
       "      <th>name</th>\n",
       "      <th>sex</th>\n",
       "      <th>age</th>\n",
       "      <th>sibsp</th>\n",
       "      <th>parch</th>\n",
       "      <th>ticket</th>\n",
       "      <th>fare</th>\n",
       "      <th>cabin</th>\n",
       "      <th>embarked</th>\n",
       "      <th>boat</th>\n",
       "      <th>body</th>\n",
       "      <th>home.dest</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Allen, Miss. Elisabeth Walton</td>\n",
       "      <td>female</td>\n",
       "      <td>29.0000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>24160</td>\n",
       "      <td>211.3375</td>\n",
       "      <td>B5</td>\n",
       "      <td>S</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>St Louis, MO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Allison, Master. Hudson Trevor</td>\n",
       "      <td>male</td>\n",
       "      <td>0.9167</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>113781</td>\n",
       "      <td>151.5500</td>\n",
       "      <td>C22 C26</td>\n",
       "      <td>S</td>\n",
       "      <td>11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Montreal, PQ / Chesterville, ON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Allison, Miss. Helen Loraine</td>\n",
       "      <td>female</td>\n",
       "      <td>2.0000</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>113781</td>\n",
       "      <td>151.5500</td>\n",
       "      <td>C22 C26</td>\n",
       "      <td>S</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Montreal, PQ / Chesterville, ON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Allison, Mr. Hudson Joshua Creighton</td>\n",
       "      <td>male</td>\n",
       "      <td>30.0000</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>113781</td>\n",
       "      <td>151.5500</td>\n",
       "      <td>C22 C26</td>\n",
       "      <td>S</td>\n",
       "      <td>NaN</td>\n",
       "      <td>135.0</td>\n",
       "      <td>Montreal, PQ / Chesterville, ON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Allison, Mrs. Hudson J C (Bessie Waldo Daniels)</td>\n",
       "      <td>female</td>\n",
       "      <td>25.0000</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>113781</td>\n",
       "      <td>151.5500</td>\n",
       "      <td>C22 C26</td>\n",
       "      <td>S</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Montreal, PQ / Chesterville, ON</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   pclass  survived                                             name     sex  \\\n",
       "0       1         1                    Allen, Miss. Elisabeth Walton  female   \n",
       "1       1         1                   Allison, Master. Hudson Trevor    male   \n",
       "2       1         0                     Allison, Miss. Helen Loraine  female   \n",
       "3       1         0             Allison, Mr. Hudson Joshua Creighton    male   \n",
       "4       1         0  Allison, Mrs. Hudson J C (Bessie Waldo Daniels)  female   \n",
       "\n",
       "       age  sibsp  parch  ticket      fare    cabin embarked boat   body  \\\n",
       "0  29.0000      0      0   24160  211.3375       B5        S    2    NaN   \n",
       "1   0.9167      1      2  113781  151.5500  C22 C26        S   11    NaN   \n",
       "2   2.0000      1      2  113781  151.5500  C22 C26        S  NaN    NaN   \n",
       "3  30.0000      1      2  113781  151.5500  C22 C26        S  NaN  135.0   \n",
       "4  25.0000      1      2  113781  151.5500  C22 C26        S  NaN    NaN   \n",
       "\n",
       "                         home.dest  \n",
       "0                     St Louis, MO  \n",
       "1  Montreal, PQ / Chesterville, ON  \n",
       "2  Montreal, PQ / Chesterville, ON  \n",
       "3  Montreal, PQ / Chesterville, ON  \n",
       "4  Montreal, PQ / Chesterville, ON  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel(\"datasets/titanic.xls\",na_values=[None]) \n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Changing specific values with NaN columns wise while reading"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>pclass</th>\n",
       "      <th>survived</th>\n",
       "      <th>name</th>\n",
       "      <th>sex</th>\n",
       "      <th>age</th>\n",
       "      <th>sibsp</th>\n",
       "      <th>parch</th>\n",
       "      <th>ticket</th>\n",
       "      <th>fare</th>\n",
       "      <th>cabin</th>\n",
       "      <th>embarked</th>\n",
       "      <th>boat</th>\n",
       "      <th>body</th>\n",
       "      <th>home.dest</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Allen, Miss. Elisabeth Walton</td>\n",
       "      <td>female</td>\n",
       "      <td>29</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>24160</td>\n",
       "      <td>211.3375</td>\n",
       "      <td>B5</td>\n",
       "      <td>S</td>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "      <td>St Louis, MO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Allison, Master. Hudson Trevor</td>\n",
       "      <td>male</td>\n",
       "      <td>0.9167</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>113781</td>\n",
       "      <td>151.5500</td>\n",
       "      <td>C22 C26</td>\n",
       "      <td>S</td>\n",
       "      <td>11</td>\n",
       "      <td>None</td>\n",
       "      <td>Montreal, PQ / Chesterville, ON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Allison, Miss. Helen Loraine</td>\n",
       "      <td>female</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>113781</td>\n",
       "      <td>151.5500</td>\n",
       "      <td>C22 C26</td>\n",
       "      <td>S</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Montreal, PQ / Chesterville, ON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Allison, Mr. Hudson Joshua Creighton</td>\n",
       "      <td>male</td>\n",
       "      <td>30</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>113781</td>\n",
       "      <td>151.5500</td>\n",
       "      <td>C22 C26</td>\n",
       "      <td>S</td>\n",
       "      <td>None</td>\n",
       "      <td>135</td>\n",
       "      <td>Montreal, PQ / Chesterville, ON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Allison, Mrs. Hudson J C (Bessie Waldo Daniels)</td>\n",
       "      <td>female</td>\n",
       "      <td>25</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>113781</td>\n",
       "      <td>151.5500</td>\n",
       "      <td>C22 C26</td>\n",
       "      <td>S</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Montreal, PQ / Chesterville, ON</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   pclass  survived                                             name     sex  \\\n",
       "0       1         1                    Allen, Miss. Elisabeth Walton  female   \n",
       "1       1         1                   Allison, Master. Hudson Trevor    male   \n",
       "2       1         0                     Allison, Miss. Helen Loraine  female   \n",
       "3       1         0             Allison, Mr. Hudson Joshua Creighton    male   \n",
       "4       1         0  Allison, Mrs. Hudson J C (Bessie Waldo Daniels)  female   \n",
       "\n",
       "      age  sibsp  parch  ticket      fare    cabin embarked  boat  body  \\\n",
       "0      29      0      0   24160  211.3375       B5        S     2  None   \n",
       "1  0.9167      1      2  113781  151.5500  C22 C26        S    11  None   \n",
       "2       2      1      2  113781  151.5500  C22 C26        S  None  None   \n",
       "3      30      1      2  113781  151.5500  C22 C26        S  None   135   \n",
       "4      25      1      2  113781  151.5500  C22 C26        S  None  None   \n",
       "\n",
       "                         home.dest  \n",
       "0                     St Louis, MO  \n",
       "1  Montreal, PQ / Chesterville, ON  \n",
       "2  Montreal, PQ / Chesterville, ON  \n",
       "3  Montreal, PQ / Chesterville, ON  \n",
       "4  Montreal, PQ / Chesterville, ON  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=pd.read_excel('datasets/titanic.xls')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "df=pd.read_excel('datasets/titanic.xls',na_values={'body':[None],'boat':[None],'parch':[2,0]})\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " * <b>na_values </b>argument is used to replace all the values passes in the list with NaN.You can also replace some invalid values column wise as you can in the above code. If you run the above code you will a dataframe where 'None' in body column is replaced by NaN,again None in the boat column is replaced by NaN and 2 & 0 in parch column are replaced by NaN."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Writing into CSV or excel \n",
    " * You can convert any dataframe in a new CSV or excel file file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>dates</th>\n",
       "      <th>day</th>\n",
       "      <th>temp</th>\n",
       "      <th>wind-speed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>02-01-12</td>\n",
       "      <td>sunny</td>\n",
       "      <td>45</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>03-01-12</td>\n",
       "      <td>rainy</td>\n",
       "      <td>46</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>04-01-12</td>\n",
       "      <td>hot</td>\n",
       "      <td>47</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>05-01-12</td>\n",
       "      <td>sunny</td>\n",
       "      <td>48</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>06-01-12</td>\n",
       "      <td>hot</td>\n",
       "      <td>49</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      dates    day  temp  wind-speed\n",
       "0  02-01-12  sunny    45          12\n",
       "1  03-01-12  rainy    46          34\n",
       "2  04-01-12    hot    47          45\n",
       "3  05-01-12  sunny    48          56\n",
       "4  06-01-12    hot    49          67"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Creatiing my own disctionary\n",
    "mydict={\n",
    "         'dates':['02-01-12','03-01-12','04-01-12','05-01-12','06-01-12'],\n",
    "         'day':['sunny','rainy','hot','sunny','hot'],\n",
    "         'wind-speed':[12,34,45,56,67],\n",
    "         'temp':[45,46,47,48,49]\n",
    "       }\n",
    "#Converting disction to dataframe object\n",
    "df=pd.DataFrame(mydict)\n",
    "\n",
    "#Printing the dataframe\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating a new file\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Creating a new csv file mycsv.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Suppose you have read a csv or excel file as dataframe object and you did some modification.Now you want to write a new csv or excel file that contains the modified dataframe. In that case you will need to_csv() or to_excel() function to create a new file.See the example below -"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('datasets/mycsv.csv',index=False)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Creating a new excel file myexcel.xls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_excel('datasets/myexcel.xls',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Writing into csv or excel only selected rows\n",
    "<br>\n",
    "Suppose you modified the dataframe and you want to write it in another csv file with only selected columns.In the following example we have created the new csv file mycsv_few_columns.csv by \"df\" dataframe allowing only three columns which we want i.e, day, temp, wind-speed and i have ignored the date column."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### how many columns in dataframe df ?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['dates', 'day', 'temp', 'wind-speed'], dtype='object')"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>day</th>\n",
       "      <th>temp</th>\n",
       "      <th>wind-speed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>sunny</td>\n",
       "      <td>45</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>rainy</td>\n",
       "      <td>46</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>hot</td>\n",
       "      <td>47</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>sunny</td>\n",
       "      <td>48</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>hot</td>\n",
       "      <td>49</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     day  temp  wind-speed\n",
       "0  sunny    45          12\n",
       "1  rainy    46          34\n",
       "2    hot    47          45\n",
       "3  sunny    48          56\n",
       "4    hot    49          67"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Writing only three columns day,temp,wind-speed\n",
    "df.to_csv('datasets/mycsv_few_columns.csv',columns=['day','temp','wind-speed'],index=False)\n",
    "\n",
    "#Again reading to see the result\n",
    "d=pd.read_csv('datasets/mycsv_few_columns.csv')\n",
    "d"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Converters :"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In various cases you dont have clean dataset. Invalid values in the dataset leads to a lot of problem while predicting or extracting the meaningful information.To avoid this problem we use converters.\n",
    "* Converters are basically functions which convert the specific value of a column in your desire value\n",
    "* Converter function passed into the disctionary like in the na_values.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is our dataset in which you can see that there are lots of invalid values are present.These values are noise in our dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>dates</th>\n",
       "      <th>day</th>\n",
       "      <th>temp</th>\n",
       "      <th>wind-speed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2/1/2012</td>\n",
       "      <td>sunny</td>\n",
       "      <td>45.0</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3/1/2012</td>\n",
       "      <td>rainy</td>\n",
       "      <td>46.0</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4/1/2012</td>\n",
       "      <td>hot</td>\n",
       "      <td>47.0</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5/1/2012</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6/1/2012</td>\n",
       "      <td>hot</td>\n",
       "      <td>49.0</td>\n",
       "      <td>Not available</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7/1/2012</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Not available</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>8/1/2012</td>\n",
       "      <td>hot</td>\n",
       "      <td>12.0</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>9/1/2012</td>\n",
       "      <td>rainy</td>\n",
       "      <td>23.0</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>10/1/2012</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>11/1/2012</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       dates    day  temp     wind-speed\n",
       "0   2/1/2012  sunny  45.0             12\n",
       "1   3/1/2012  rainy  46.0             34\n",
       "2   4/1/2012    hot  47.0             45\n",
       "3   5/1/2012    NaN   NaN             56\n",
       "4   6/1/2012    hot  49.0  Not available\n",
       "5   7/1/2012    NaN   NaN  Not available\n",
       "6   8/1/2012    hot  12.0             45\n",
       "7   9/1/2012  rainy  23.0             41\n",
       "8  10/1/2012    NaN   NaN            NaN\n",
       "9  11/1/2012    NaN   NaN            NaN"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=pd.read_csv('datasets/season.csv')\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### The following function will convert any cell of column having 'NaN' into 40, so here we will apply this function into 'temp' column and so for the 'day' and 'wind-speed' columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "def converter_for_temp(col):\n",
    "    if col=='NaN':\n",
    "        return 40\n",
    "    else:\n",
    "        return col\n",
    "def converter_for_day(col):\n",
    "    if col=='NaN':\n",
    "        return 'sunny'\n",
    "    else:\n",
    "        return col\n",
    "def converter_for_wind_speed(col):\n",
    "    if col=='Not available':\n",
    "        return 30\n",
    "    elif col==\"NaN\":\n",
    "        return 48\n",
    "    else:\n",
    "        return col"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>dates</th>\n",
       "      <th>day</th>\n",
       "      <th>temp</th>\n",
       "      <th>wind-speed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2/1/2012</td>\n",
       "      <td>sunny</td>\n",
       "      <td>45</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3/1/2012</td>\n",
       "      <td>rainy</td>\n",
       "      <td>46</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4/1/2012</td>\n",
       "      <td>hot</td>\n",
       "      <td>47</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5/1/2012</td>\n",
       "      <td>sunny</td>\n",
       "      <td>40</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6/1/2012</td>\n",
       "      <td>hot</td>\n",
       "      <td>49</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7/1/2012</td>\n",
       "      <td>sunny</td>\n",
       "      <td>40</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>8/1/2012</td>\n",
       "      <td>hot</td>\n",
       "      <td>12</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>9/1/2012</td>\n",
       "      <td>rainy</td>\n",
       "      <td>23</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>10/1/2012</td>\n",
       "      <td>sunny</td>\n",
       "      <td>40</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>11/1/2012</td>\n",
       "      <td>sunny</td>\n",
       "      <td>40</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       dates    day temp wind-speed\n",
       "0   2/1/2012  sunny   45         12\n",
       "1   3/1/2012  rainy   46         34\n",
       "2   4/1/2012    hot   47         45\n",
       "3   5/1/2012  sunny   40         56\n",
       "4   6/1/2012    hot   49         30\n",
       "5   7/1/2012  sunny   40         30\n",
       "6   8/1/2012    hot   12         45\n",
       "7   9/1/2012  rainy   23         41\n",
       "8  10/1/2012  sunny   40         48\n",
       "9  11/1/2012  sunny   40         48"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=pd.read_csv('datasets/season.csv',converters={\n",
    "    'day':converter_for_day,\n",
    "    'temp':converter_for_temp,\n",
    "    'wind-speed':converter_for_wind_speed\n",
    "   })\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can observe that previously the columns 'day', 'temp' and 'wind-speed' had some invalid data like 'NaN','NAN','Not available' but after applying the converter functions we got a cleaned dataset.Now we can apply some data analysis techniquw to predict something in our dataset."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Writing different dataframes into one file but different sheet names"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's assume that you have two different dataframes and you want to write it in the same excel sheet but different sheet names. \n",
    "<br>\n",
    "\n",
    "Let's take two disctionary one is \"weather1\" and another is \"house1\" and make it two dataframe \"weather\" & \"house\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Creating weather disctionary\n",
    "weather1={\n",
    "         'dates':['02-01-12','03-01-12','04-01-12','05-01-12','06-01-12'],\n",
    "         'day':['sunny','rainy','hot','sunny','hot'],\n",
    "         'wind-speed':[12,34,45,56,67],\n",
    "         'temp':[45,46,47,48,49]\n",
    "       }\n",
    "#Converting disction to dataframe object\n",
    "weather=pd.DataFrame(weather1)\n",
    "\n",
    "#Creating house disctionary\n",
    "house1={\n",
    "         'dates':['02-01-12','03-01-12','04-01-12','05-01-12','06-01-12'],\n",
    "         'price':[20000,30000,40000,50000,60000],\n",
    "         'bhk':[1,3,2,1,2],\n",
    "         'how-old':[2,5,2,7,4]\n",
    "       }\n",
    "\n",
    "#converting house disctionary to dataframe object\n",
    "\n",
    "house=pd.DataFrame(house1)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " Call the \"ExcelWriter\" and make a object \"writer\".Now call to_excel() function and pass three argument -\n",
    "  * <b>1. \"writer\" object :</b>\n",
    "  * <b>2. sheet_name : </b>\n",
    "  * <b>3. Index :</b> This is optional for you\n",
    "  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "with pd.ExcelWriter('datasets/weather_and_house.xls') as writer:\n",
    "   weather.to_excel(writer,sheet_name='Weather',index=False)\n",
    "   house.to_excel(writer,sheet_name='House',index=False)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Now access the sheets separately by passing its name while reading\n",
    "See the example"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>dates</th>\n",
       "      <th>day</th>\n",
       "      <th>temp</th>\n",
       "      <th>wind-speed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>02-01-12</td>\n",
       "      <td>sunny</td>\n",
       "      <td>45</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>03-01-12</td>\n",
       "      <td>rainy</td>\n",
       "      <td>46</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>04-01-12</td>\n",
       "      <td>hot</td>\n",
       "      <td>47</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>05-01-12</td>\n",
       "      <td>sunny</td>\n",
       "      <td>48</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>06-01-12</td>\n",
       "      <td>hot</td>\n",
       "      <td>49</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      dates    day  temp  wind-speed\n",
       "0  02-01-12  sunny    45          12\n",
       "1  03-01-12  rainy    46          34\n",
       "2  04-01-12    hot    47          45\n",
       "3  05-01-12  sunny    48          56\n",
       "4  06-01-12    hot    49          67"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d1=pd.read_excel('datasets/weather_and_house.xls','Weather')\n",
    "d1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>bhk</th>\n",
       "      <th>dates</th>\n",
       "      <th>how-old</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>02-01-12</td>\n",
       "      <td>2</td>\n",
       "      <td>20000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>03-01-12</td>\n",
       "      <td>5</td>\n",
       "      <td>30000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>04-01-12</td>\n",
       "      <td>2</td>\n",
       "      <td>40000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>05-01-12</td>\n",
       "      <td>7</td>\n",
       "      <td>50000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>06-01-12</td>\n",
       "      <td>4</td>\n",
       "      <td>60000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   bhk     dates  how-old  price\n",
       "0    1  02-01-12        2  20000\n",
       "1    3  03-01-12        5  30000\n",
       "2    2  04-01-12        2  40000\n",
       "3    1  05-01-12        7  50000\n",
       "4    2  06-01-12        4  60000"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d2=pd.read_excel('datasets/weather_and_house.xls','House')\n",
    "d2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "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.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}