Pandas for Python in Jupyter Notebook.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas for Python in Jupyter Notebook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# This function is to show two data structures side by side\n",
    "# Used in Web McKinney's presentations: http://www.youtube.com/watch?v=w26x-z-BdWQ\n",
    "def side_by_side(*objs, **kwds):\n",
    "    from pandas.io.formats.printing import adjoin\n",
    "    space = kwds.get('space', 4)\n",
    "    reprs = [repr(obj).split('\\n') for obj in objs]\n",
    "    print(adjoin(space, *reprs))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Series\n",
    "### Creating Series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Series called upon list: ['Car', 'Bicycle', 'Bike', 'Bus']\n",
      "0        Car\n",
      "1    Bicycle\n",
      "2       Bike\n",
      "3        Bus\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "a=['Car','Bicycle','Bike','Bus'] # list of strings\n",
    "print(\"Series called upon list: {}\\n{}\".format(a,pd.Series(a)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Series called upon array: [1 2 3 4]\n",
      "0    1\n",
      "1    2\n",
      "2    3\n",
      "3    4\n",
      "dtype: int32\n"
     ]
    }
   ],
   "source": [
    "b=[1,2,3,4] # list of numbers\n",
    "c=np.array(b) # array\n",
    "print(\"Series called upon array: {}\\n{}\".format(c,pd.Series(c)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Series called upon dictionary: {'a': 10, 'b': 20, 'c': 30, 'd': 40}\n",
      "a    10\n",
      "b    20\n",
      "c    30\n",
      "d    40\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "d={'a':10,\n",
    "  'b':20,\n",
    "  'c':30,\n",
    "  'd':40} # dictionary\n",
    "print(\"Series called upon dictionary: {}\\n{}\".format(d,pd.Series(d)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Common Operations on Series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Series_1:\n",
      "Car        1\n",
      "Bicycle    2\n",
      "Bike       3\n",
      "Bus        4\n",
      "dtype: int64\n",
      "\n",
      "Series_2:\n",
      "Bike      1\n",
      "Scooty    2\n",
      "Auto      3\n",
      "Bus       4\n",
      "dtype: int64\n",
      "\n",
      "Union of Series_1 and Series_2:\n",
      "Auto       NaN\n",
      "Bicycle    NaN\n",
      "Bike       4.0\n",
      "Bus        8.0\n",
      "Car        NaN\n",
      "Scooty     NaN\n",
      "dtype: float64\n",
      "\n",
      "After dropping Nan Values:\n",
      "Bike    4.0\n",
      "Bus     8.0\n",
      "dtype: float64\n"
     ]
    }
   ],
   "source": [
    "# Union of the index\n",
    "\n",
    "series_1=pd.Series(b,a) # pd.Series(data,row_index)\n",
    "print(\"Series_1:\\n{}\".format(series_1)) \n",
    "series_2=pd.Series(data=b,index=['Bike','Scooty','Auto','Bus'])\n",
    "print(\"\\nSeries_2:\\n{}\".format(series_2))\n",
    "\n",
    "# (values of only bus and bike are added since they are common)\n",
    "print('\\nUnion of Series_1 and Series_2:\\n{}'.format(series_1+series_2))\n",
    "\n",
    "# Dropping Nan values\n",
    "print('\\nAfter dropping Nan Values:\\n{}'.format((series_1+series_2).dropna()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Series x:\n",
      "Blue      15.0\n",
      "Green     10.0\n",
      "Yellow     5.0\n",
      "Orange    30.0\n",
      "Purple    20.0\n",
      "Red       25.0\n",
      "White      NaN\n",
      "dtype: float64\n"
     ]
    }
   ],
   "source": [
    "x=pd.Series([15,10,5,30,20,25,np.nan],['Blue','Green','Yellow','Orange','Purple','Red','White'])\n",
    "print(\"Series x:\\n{}\".format(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Class of Series x:  <class 'pandas.core.series.Series'>\n"
     ]
    }
   ],
   "source": [
    "print('Class of Series x: ',type(x)) # class"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Values of series x are returned as array using the values attribute:\n",
      "[15. 10.  5. 30. 20. 25. nan]\n",
      "\n",
      "Indexes of series x are returned using the index attribute:\n",
      "Index(['Blue', 'Green', 'Yellow', 'Orange', 'Purple', 'Red', 'White'], dtype='object')\n"
     ]
    }
   ],
   "source": [
    "print(\"Values of series x are returned as array using the values attribute:\\n{}\".format(x.values)) # attribute\n",
    "print(\"\\nIndexes of series x are returned using the index attribute:\\n{}\".format(x.index)) # attribute"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Sum of elements of series:  105.0\n"
     ]
    }
   ],
   "source": [
    "print(\"Sum of elements of series: \",x.sum()) # function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Product of elements of series:  11250000.0\n"
     ]
    }
   ],
   "source": [
    "print(\"Product of elements of series: \",x.product()) # function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Mean of elements of series:  17.5\n"
     ]
    }
   ],
   "source": [
    "print(\"Mean of elements of series: \",x.mean()) # function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "First two elements in series:\n",
      "\n",
      "Blue     15.0\n",
      "Green    10.0\n",
      "dtype: float64\n"
     ]
    }
   ],
   "source": [
    "print(\"First two elements in series:\\n\\n{}\".format(x.head(2)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Last two elements in series:\n",
      "\n",
      "Red      25.0\n",
      "White     NaN\n",
      "dtype: float64\n"
     ]
    }
   ],
   "source": [
    "print(\"Last two elements in series:\\n\\n{}\".format(x.tail(2)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Summary of operations:\n",
      "\n",
      "count     6.000000\n",
      "mean     17.500000\n",
      "std       9.354143\n",
      "min       5.000000\n",
      "25%      11.250000\n",
      "50%      17.500000\n",
      "75%      23.750000\n",
      "max      30.000000\n",
      "dtype: float64\n"
     ]
    }
   ],
   "source": [
    "print(\"Summary of operations:\\n\\n{}\".format(x.describe()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of elements in series:  6\n"
     ]
    }
   ],
   "source": [
    "print(\"Number of elements in series: \",x.count()) # count() excludes the null values while counting"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Maximum in series is 30.0 at position Orange\n",
      "Minimum in series is 5.0 at position Yellow\n"
     ]
    }
   ],
   "source": [
    "print(\"Maximum in series is {} at position {}\".format(x.max(),x.idxmax())) # idxmax() returns the index of maximum element\n",
    "print(\"Minimum in series is {} at position {}\".format(x.min(),x.idxmin())) # idxmin() returns the index of minimum element"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Series sorted by value:\n",
      "Yellow     5.0\n",
      "Green     10.0\n",
      "Blue      15.0\n",
      "Purple    20.0\n",
      "Red       25.0\n",
      "Orange    30.0\n",
      "White      NaN\n",
      "dtype: float64\n",
      "\n",
      "Series sorted by index:\n",
      "Blue      15.0\n",
      "Green     10.0\n",
      "Orange    30.0\n",
      "Purple    20.0\n",
      "Red       25.0\n",
      "White      NaN\n",
      "Yellow     5.0\n",
      "dtype: float64\n"
     ]
    }
   ],
   "source": [
    "print(\"Series sorted by value:\\n{}\".format(x.sort_values())) # x.sort_values() returns the series by sorting values in ascending order\n",
    "print(\"\\nSeries sorted by index:\\n{}\".format(x.sort_index())) # x.sort_index() returns the series by sorting indexes in ascending order"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Sorted list of values from series:  [30.0, 25.0, 20.0, 15.0, 10.0, 5.0, nan]\n"
     ]
    }
   ],
   "source": [
    "print(\"Sorted list of values from series: \",sorted(x, reverse=True)) # returns the list in descending order"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Sample of elements:\n",
      "Red       25.0\n",
      "White      NaN\n",
      "Orange    30.0\n",
      "dtype: float64\n"
     ]
    }
   ],
   "source": [
    "print(\"Sample of elements:\\n{}\".format(x.sample(3))) # picks a random sample from series"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### in statements"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Series x:\n",
      "Blue      15.0\n",
      "Green     10.0\n",
      "Yellow     5.0\n",
      "Orange    30.0\n",
      "Purple    20.0\n",
      "Red       25.0\n",
      "White      NaN\n",
      "dtype: float64\n",
      "\n",
      "15 in x: False\n",
      "'Blue' in x: True\n",
      "'Red' in x.index: True\n",
      "20 in x.values: True\n"
     ]
    }
   ],
   "source": [
    "print(\"Series x:\\n{}\".format(x))\n",
    "\n",
    "print('\\n15 in x:',15 in x) # returns false, although 15 is present in the series as it checks for index rather than values\n",
    "print(\"'Blue' in x:\",'Blue' in x) # returns true\n",
    "print(\"'Red' in x.index:\",'Red' in x.index) # returns true, works similar to above\n",
    "print(\"20 in x.values:\",20 in x.values) # returns true as it checks in values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Extracting values using index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Series x:\n",
      "Blue      15.0\n",
      "Green     10.0\n",
      "Yellow     5.0\n",
      "Orange    30.0\n",
      "Purple    20.0\n",
      "Red       25.0\n",
      "White      NaN\n",
      "dtype: float64\n",
      "\n",
      "Extracting a single value x['Orange']: 30.0\n",
      "\n",
      "Extracting multiple values x[['Green','Yellow']]:\n",
      "Green     10.0\n",
      "Yellow     5.0\n",
      "dtype: float64\n",
      "\n",
      "Extracting Series with wrong index x[['green','Yellow']]:\n",
      "green     NaN\n",
      "Yellow    5.0\n",
      "dtype: float64\n",
      "\n",
      "Extracting Series x['Green':'Purple']:\n",
      "Green     10.0\n",
      "Yellow     5.0\n",
      "Orange    30.0\n",
      "Purple    20.0\n",
      "dtype: float64\n",
      "\n",
      "\n",
      "Using the get():\n",
      "\n",
      "Returning a value x.get('Orange'):  30.0\n",
      "\n",
      "Returning multiple values: x.get(['Orange','Red'])\n",
      "Orange    30.0\n",
      "Red       25.0\n",
      "dtype: float64\n",
      "\n",
      "Extracting value with wrong index x.get('green',default='Not a valid index'): Not a valid index\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\users\\shaur\\appdata\\local\\programs\\python\\python37\\lib\\site-packages\\pandas\\core\\series.py:1155: FutureWarning: \n",
      "Passing list-likes to .loc or [] with any missing label will raise\n",
      "KeyError in the future, you can use .reindex() as an alternative.\n",
      "\n",
      "See the documentation here:\n",
      "https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#deprecate-loc-reindex-listlike\n",
      "  return self.loc[key]\n"
     ]
    }
   ],
   "source": [
    "print(\"Series x:\\n{}\".format(x))\n",
    "\n",
    "print(\"\\nExtracting a single value x['Orange']:\",x['Orange']) # returns value\n",
    "print(\"\\nExtracting multiple values x[['Green','Yellow']]:\\n{}\".format(x[['Green','Yellow']])) # returns a series\n",
    "print(\"\\nExtracting Series with wrong index x[['green','Yellow']]:\\n{}\".format(x[['green','Yellow']])) # returns the series with value of 'green' as NaN, indexes are case-sensitive\n",
    "print(\"\\nExtracting Series x['Green':'Purple']:\\n{}\".format(x[\"Green\":\"Purple\"]))\n",
    "\n",
    "# get() is also used to return values\n",
    "print(\"\\n\\nUsing the get():\\n\\nReturning a value x.get('Orange'): \",x.get(\"Orange\")) # returns value\n",
    "print(\"\\nReturning multiple values: x.get(['Orange','Red'])\\n{}\".format(x.get([\"Orange\",\"Red\"]))) # returns a series\n",
    "\n",
    "# default parameter is returned when no such data is present in series\n",
    "print(\"\\nExtracting value with wrong index x.get('green',default='Not a valid index'):\",x.get(\"green\",default=\"Not a valid index\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Series a:\n",
      "A    1\n",
      "B    1\n",
      "C    4\n",
      "D    7\n",
      "E    9\n",
      "F    3\n",
      "G    7\n",
      "H    4\n",
      "I    1\n",
      "J    9\n",
      "dtype: int64\n",
      "\n",
      "Series b:\n",
      "Blue      C\n",
      "Orange    F\n",
      "Yellow    A\n",
      "Red       D\n",
      "Green     I\n",
      "dtype: object\n",
      "\n",
      "Mapping of b on a:\n",
      "Blue      4\n",
      "Orange    3\n",
      "Yellow    1\n",
      "Red       7\n",
      "Green     1\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "a=pd.Series([1,1,4,7,9,3,7,4,1,9],['A','B','C','D','E','F','G','H','I','J'])\n",
    "b=pd.Series(['C','F','A','D','I'],['Blue','Orange','Yellow','Red','Green'])\n",
    "print(\"Series a:\\n{}\".format(a))\n",
    "print(\"\\nSeries b:\\n{}\".format(b))\n",
    "\n",
    "# Values of Series b are mapped with indexes of series a\n",
    "print(\"\\nMapping of b on a:\\n{}\".format(b.map(a)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Series:\n",
      "A    1\n",
      "B    1\n",
      "C    4\n",
      "D    7\n",
      "E    9\n",
      "F    3\n",
      "G    7\n",
      "H    4\n",
      "I    1\n",
      "J    9\n",
      "dtype: int64\n",
      "\n",
      "Check whether values in series are unique:  False\n",
      "\n",
      "Unique values in series with its no of occurences:\n",
      "1    3\n",
      "9    2\n",
      "7    2\n",
      "4    2\n",
      "3    1\n",
      "dtype: int64\n",
      "\n",
      "Unique values in series with its no of occurences in ascending order:\n",
      "3    1\n",
      "4    2\n",
      "7    2\n",
      "9    2\n",
      "1    3\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "a=pd.Series([1,1,4,7,9,3,7,4,1,9],['A','B','C','D','E','F','G','H','I','J'])\n",
    "print(\"Series:\\n{}\".format(a))\n",
    "print(\"\\nCheck whether values in series are unique: \",a.is_unique) # returns boolean value (true/false)\n",
    "\n",
    "# value_counts returns the number of occurences of each value\n",
    "print(\"\\nUnique values in series with its no of occurences:\\n{}\".format(a.value_counts()))\n",
    "\n",
    "# ascending parameter returns the number of occurences of each value in ascending order\n",
    "print(\"\\nUnique values in series with its no of occurences in ascending order:\\n{}\".format(a.value_counts(ascending=True)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Series:\n",
      "A    1\n",
      "B    1\n",
      "C    4\n",
      "D    7\n",
      "E    9\n",
      "F    3\n",
      "G    7\n",
      "H    4\n",
      "I    1\n",
      "J    9\n",
      "dtype: int64\n",
      "\n",
      "Adding value 10 to each element:\n",
      "A    11\n",
      "B    11\n",
      "C    14\n",
      "D    17\n",
      "E    19\n",
      "F    13\n",
      "G    17\n",
      "H    14\n",
      "I    11\n",
      "J    19\n",
      "dtype: int64\n",
      "\n",
      "Subtracting value 10 to each element:\n",
      "A   -9\n",
      "B   -9\n",
      "C   -6\n",
      "D   -3\n",
      "E   -1\n",
      "F   -7\n",
      "G   -3\n",
      "H   -6\n",
      "I   -9\n",
      "J   -1\n",
      "dtype: int64\n",
      "\n",
      "Multiplying by value 10 with each element:\n",
      "A    10\n",
      "B    10\n",
      "C    40\n",
      "D    70\n",
      "E    90\n",
      "F    30\n",
      "G    70\n",
      "H    40\n",
      "I    10\n",
      "J    90\n",
      "dtype: int64\n",
      "\n",
      "Dividing by value 10 with each element:\n",
      "A    0.1\n",
      "B    0.1\n",
      "C    0.4\n",
      "D    0.7\n",
      "E    0.9\n",
      "F    0.3\n",
      "G    0.7\n",
      "H    0.4\n",
      "I    0.1\n",
      "J    0.9\n",
      "dtype: float64\n",
      "\n",
      "Implementing apply() on series using lambda:\n",
      "A      0.2500\n",
      "B      0.2500\n",
      "C     18.0625\n",
      "D     64.0000\n",
      "E    110.2500\n",
      "F      9.0000\n",
      "G     64.0000\n",
      "H     18.0625\n",
      "I      0.2500\n",
      "J    110.2500\n",
      "dtype: float64\n"
     ]
    }
   ],
   "source": [
    "a=pd.Series([1,1,4,7,9,3,7,4,1,9],['A','B','C','D','E','F','G','H','I','J'])\n",
    "print(\"Series:\\n{}\".format(a))\n",
    "print(\"\\nAdding value 10 to each element:\\n{}\".format(a.add(10)))\n",
    "print(\"\\nSubtracting value 10 to each element:\\n{}\".format(a.sub(10)))\n",
    "print(\"\\nMultiplying by value 10 with each element:\\n{}\".format(a.mul(10)))\n",
    "print(\"\\nDividing by value 10 with each element:\\n{}\".format(a.div(10)))\n",
    "\n",
    "# apply() is used to implement some function on each element of series\n",
    "print(\"\\nImplementing apply() on series using lambda:\\n{}\".format(a.apply(lambda x:(((5*x)-3)/4)**2))) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "A    1.0          A     NaN     \n",
      "B    2.0          B     NaN     \n",
      "C    3.0          C     3.0     \n",
      "D    4.0          D     4.0     \n",
      "E    5.0          E     5.0     \n",
      "F    6.0          F     6.0     \n",
      "G    7.0          G     NaN     \n",
      "H    8.0          H     NaN     \n",
      "I    9.0          I     NaN     \n",
      "P    NaN          P    14.0     \n",
      "Q    NaN          Q    15.0     \n",
      "R    NaN          R    16.0     \n",
      "Y    NaN          Y    19.0     \n",
      "Z    NaN          Z    18.0     \n",
      "dtype: float64    dtype: float64\n"
     ]
    }
   ],
   "source": [
    "s1=pd.Series([1,2,3,4,5,6,7,8,9],['A','B','C','D','E','F','G','H','I']) # pd.Series(data,row_index)\n",
    "# print(\"Series_1:\\n{}\".format(s1)) \n",
    "s2=pd.Series([4,5,6,14,15,16,3,18,19],['D','E','F','P','Q','R','C','Z','Y'])\n",
    "# print(\"\\nSeries_2:\\n{}\".format(s2))\n",
    "\n",
    "# Align s1 and s2 with an outer join = default\n",
    "a, b = s1.align(s2, join='outer')\n",
    "side_by_side(a, b)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "C    3          C    3      \n",
      "D    4          D    4      \n",
      "E    5          E    5      \n",
      "F    6          F    6      \n",
      "dtype: int64    dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# Align s1 and s2 with an inner join\n",
    "a, b = s1.align(s2, join='inner')\n",
    "side_by_side(a, b)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "A    1          A    NaN      \n",
      "B    2          B    NaN      \n",
      "C    3          C    3.0      \n",
      "D    4          D    4.0      \n",
      "E    5          E    5.0      \n",
      "F    6          F    6.0      \n",
      "G    7          G    NaN      \n",
      "H    8          H    NaN      \n",
      "I    9          I    NaN      \n",
      "dtype: int64    dtype: float64\n"
     ]
    }
   ],
   "source": [
    "# Align s1 and s2 with a left join\n",
    "a, b = s1.align(s2, join='left')\n",
    "side_by_side(a, b)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "D    4.0          D     4     \n",
      "E    5.0          E     5     \n",
      "F    6.0          F     6     \n",
      "P    0.0          P    14     \n",
      "Q    0.0          Q    15     \n",
      "R    0.0          R    16     \n",
      "C    3.0          C     3     \n",
      "Z    0.0          Z    18     \n",
      "Y    0.0          Y    19     \n",
      "dtype: float64    dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# Align s1 and s2 with a right join and set value to 0 if NaN\n",
    "a, b = s1.align(s2, join='right', fill_value=0)\n",
    "side_by_side(a, b)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### DataFrames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dataframe:\n",
      "   X  Y  Z\n",
      "A  3  6  8\n",
      "B  4  7  6\n",
      "C  8  8  2\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "df=pd.DataFrame(np.random.randint(1,9,(3,3)),['A','B','C'],['X','Y','Z']) # pd.DataFrame(data,row_index,column_index)\n",
    "print(\"Dataframe:\\n{}\".format(df))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Indexes of DataFrame:\n",
      "Index(['A', 'B', 'C'], dtype='object')\n",
      "\n",
      "Values of DataFrame:\n",
      "[[3 6 8]\n",
      " [4 7 6]\n",
      " [8 8 2]]\n",
      "\n",
      "Class of DataFrame: <class 'pandas.core.frame.DataFrame'>\n"
     ]
    }
   ],
   "source": [
    "# Functions in dataframes are quite similar to series\n",
    "\n",
    "print('Indexes of DataFrame:\\n{}'.format(df.index))\n",
    "print('\\nValues of DataFrame:\\n{}'.format(df.values))\n",
    "print('\\nClass of DataFrame: {}'.format(type(df)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Accessing a specific column (Y)\n",
      "A    6\n",
      "B    7\n",
      "C    8\n",
      "Name: Y, dtype: int32\n",
      "\n",
      "Accessing multiple columns (X and Z)\n",
      "   X  Z\n",
      "A  3  8\n",
      "B  4  6\n",
      "C  8  2\n",
      "\n",
      "Accessing a specific row (B)\n",
      "X    4\n",
      "Y    7\n",
      "Z    6\n",
      "Name: B, dtype: int32\n",
      "\n",
      "Accessing a specific row using index (C)\n",
      "X    8\n",
      "Y    8\n",
      "Z    2\n",
      "Name: C, dtype: int32\n",
      "\n",
      "Accessing a specific element (B,Z) using loc: 6\n",
      "\n",
      "Accessing a specific element (C,Z) using iloc: 2\n"
     ]
    }
   ],
   "source": [
    "# Extracting elements\n",
    "\n",
    "print('Accessing a specific column (Y)\\n{}'.format(df['Y'])) # retrieving a column\n",
    "print('\\nAccessing multiple columns (X and Z)\\n{}'.format(df[['X','Z']])) # retrieving multiple columns by passing a list of names of columns\n",
    "print('\\nAccessing a specific row (B)\\n{}'.format(df.loc['B'])) # df.loc(row_name) extracts the row elements\n",
    "print('\\nAccessing a specific row using index (C)\\n{}'.format(df.iloc[2])) # df.iloc(index) extracts the row elements index-wise\n",
    "print('\\nAccessing a specific element (B,Z) using loc: {}'.format(df.loc['B','Z'])) # df.loc(row_index,column_index) extracts the specific element\n",
    "print('\\nAccessing a specific element (C,Z) using iloc: {}'.format(df.iloc[2,2])) # df.iloc[Row,Column]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Filtering using between():\n",
      "    X  Y  Z\n",
      "A  3  6  8\n",
      "B  4  7  6\n",
      "\n",
      "Checking null values:\n",
      "        X      Y      Z\n",
      "A  False  False  False\n",
      "B  False  False  False\n",
      "C  False  False  False\n",
      "\n",
      "Checking non-null values:\n",
      "       X     Y     Z\n",
      "A  True  True  True\n",
      "B  True  True  True\n",
      "C  True  True  True\n"
     ]
    }
   ],
   "source": [
    "print(\"Filtering using between():\\n\",df[df['Z'].between(4,9)])\n",
    "print(\"\\nChecking null values:\\n\",df.isnull())\n",
    "print(\"\\nChecking non-null values:\\n\",df.notnull())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Matrix:\n",
      "[[3 6 8]\n",
      " [4 7 6]\n",
      " [8 8 2]]\n",
      "\n",
      "Class of Matrix:  <class 'numpy.ndarray'>\n"
     ]
    }
   ],
   "source": [
    "# Converting Dataframe into Matrix:\n",
    "\n",
    "m=df.values # two dimensional array\n",
    "print('Matrix:\\n{}'.format(m))\n",
    "print('\\nClass of Matrix: ',type(m))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dataset:\n",
      "    Exam1  Exam2  Exam3  Total Marks  Percentage\n",
      "0    240    232    232          704       93.87\n",
      "1    221    163    169          553       73.73\n",
      "2    244    236    239          719       95.87\n",
      "\n",
      "Dataset with specified columns using usecols:\n",
      "    Exam1  Exam2  Exam3\n",
      "0    240    232    232\n",
      "1    221    163    169\n",
      "2    244    236    239\n",
      "\n",
      "Dimension of dataframe:  2\n",
      "Shape of dataframe:  (45, 3)\n",
      "Size of dataframe:  135\n",
      "\n",
      "Columns of DataFrame:\n",
      " Index(['Exam1', 'Exam2', 'Exam3'], dtype='object')\n",
      "\n",
      "DataFrame Axes:\n",
      " [RangeIndex(start=0, stop=45, step=1), Index(['Exam1', 'Exam2', 'Exam3'], dtype='object')]\n",
      "\n",
      "Sum of elements with columns as index:\n",
      "Exam1    9676\n",
      "Exam2    8890\n",
      "Exam3    8472\n",
      "dtype: int64\n",
      "\n",
      "Sum of elements with rows as index:\n",
      "0    704\n",
      "1    553\n",
      "2    719\n",
      "dtype: int64\n",
      "\n",
      "New Column Total_Marks is added:\n",
      "    Exam1  Exam2  Exam3  Total_Marks\n",
      "0    240    232    232          704\n",
      "1    221    163    169          553\n",
      "2    244    236    239          719\n",
      "\n",
      "New Column Class is introduced using insert():\n",
      "    Exam1  Exam2  Exam3  Total_Marks      Class\n",
      "0    240    232    232          704  Class 7th\n",
      "1    221    163    169          553  Class 7th\n",
      "2    244    236    239          719  Class 7th\n",
      "\n",
      "Although the column Class is dropped, it still remains intact inside the original dataframe.\n",
      "    Exam1  Exam2  Exam3  Total_Marks      Class\n",
      "0    240    232    232          704  Class 7th\n",
      "1    221    163    169          553  Class 7th\n",
      "2    244    236    239          719  Class 7th\n",
      "\n",
      "After using the inplace attribute, it removes the column Class permanently from the original dataframe.\n",
      "    Exam1  Exam2  Exam3  Total_Marks\n",
      "0    240    232    232          704\n",
      "1    221    163    169          553\n",
      "2    244    236    239          719\n",
      "\n",
      "Column Total_Marks is deleted using del()\n",
      "    Exam1  Exam2  Exam3\n",
      "0    240    232    232\n",
      "1    221    163    169\n",
      "2    244    236    239\n",
      "\n",
      "Filtering dataset using conditions:\n",
      "     Exam1  Exam2  Exam3\n",
      "16    207    236    239 \n",
      "\n",
      "Filering using isin():\n",
      "     Exam1  Exam2  Exam3\n",
      "1     221    163    169\n",
      "6     225    198    203\n",
      "10    221    174    186\n",
      "15    225    214    200\n",
      "39    223    205    213 \n",
      "\n",
      "Filtering using between():\n",
      "     Exam1  Exam2  Exam3\n",
      "13    236    204    209\n",
      "18    211    208    205\n",
      "36    212    206    210 \n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 45 entries, 0 to 44\n",
      "Data columns (total 3 columns):\n",
      "Exam1    45 non-null int64\n",
      "Exam2    45 non-null int64\n",
      "Exam3    45 non-null int64\n",
      "dtypes: int64(3)\n",
      "memory usage: 1.2 KB\n"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "url=\"https://raw.githubusercontent.com/jamwine/Data/master/Class%20Result.csv\"\n",
    "df=pd.read_csv(url) # pd.read_csv(file) is used to read csv file and return it as a dataframe\n",
    "print('Dataset:\\n',df.head(3)) # the first line of the data is considered as column headings\n",
    "df=pd.read_csv(url, usecols=['Exam1','Exam2','Exam3']) # usecols describes which columns are to be added in the dataframe \n",
    "print('\\nDataset with specified columns using usecols:\\n',df.head(3))\n",
    "\n",
    "print(\"\\nDimension of dataframe: \",df.ndim)\n",
    "print(\"Shape of dataframe: \",df.shape) \n",
    "print(\"Size of dataframe: \",df.size) # No. of elements\n",
    "print('\\nColumns of DataFrame:\\n',df.columns)\n",
    "print('\\nDataFrame Axes:\\n',df.axes) # gives information about both indexes and values\n",
    "print(\"\\nSum of elements with columns as index:\\n{}\".format(df.sum())) # default axis=0\n",
    "print(\"\\nSum of elements with rows as index:\\n{}\".format(df.sum(axis=1).head(3)))\n",
    "\n",
    "# Defining new column\n",
    "df['Total_Marks']=df['Exam1']+df['Exam2']+df['Exam3']\n",
    "print('\\nNew Column Total_Marks is added:\\n', df.head(3))\n",
    "\n",
    "df.insert(4,'Class',\"Class 7th\") # df.insert(loc, column, value) inserts a new column at the speciifed index location in dataframe\n",
    "print(\"\\nNew Column Class is introduced using insert():\\n\",df.head(3))\n",
    "\n",
    "df.drop('Class',axis=1) # df.drop(name,axis) is used to remove the specified rows/columns \n",
    "print(\"\\nAlthough the column Class is dropped, it still remains intact inside the original dataframe.\\n\",df.head(3))\n",
    "\n",
    "df.drop('Class',axis=1, inplace=True) # inplace attribute is used to overwrite the new data into the original dataframe \n",
    "print(\"\\nAfter using the inplace attribute, it removes the column Class permanently from the original dataframe.\\n\",df.head(3))\n",
    "\n",
    "del df['Total_Marks'] # del is permanent in nature unlike inplace attribute\n",
    "print(\"\\nColumn Total_Marks is deleted using del()\\n\",df.head(3))\n",
    "\n",
    "print(\"\\nFiltering dataset using conditions:\\n\",df[(df['Exam1']<220) & ((df['Exam2']>220) | (df['Exam3']>220))],'\\n') # returning dataframe, filtering the dataset \n",
    "print(\"Filering using isin():\\n\",df[df['Exam1'].isin([229,225,221,223,227])],'\\n') # df['column_name'].isin([elements]) checks for the list of elements in the dataset and returns the boolean value \n",
    "print(\"Filtering using between():\\n\",df[df['Exam3'].between(205,210)],\"\\n\")\n",
    "\n",
    "df.info() # information about dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dataset without column headings:\n",
      "    0   1   2   3   4   5   6   7   8   9   10  11  12  13  14  15  16  17\n",
      "0   5   4  20   5  38  17   5   4  20   5  30  43  44 NaN NaN NaN NaN NaN\n",
      "1   5   3  15   5  33  15   5   4  18   5  29  41  43 NaN NaN NaN NaN NaN\n",
      "2   5   3  20   5  40  18   5   3  20   5  30  45  45 NaN NaN NaN NaN NaN \n",
      "\n",
      "Dataset after removing all columns containing NaN values:\n",
      "    0   1   2   3   4   5   6   7   8   9   10  11  12\n",
      "0   5   4  20   5  38  17   5   4  20   5  30  43  44\n",
      "1   5   3  15   5  33  15   5   4  18   5  29  41  43\n",
      "2   5   3  20   5  40  18   5   3  20   5  30  45  45\n",
      "\n",
      "Dataset after assigning columns:\n",
      "    A  B   C  D   E   F  G  H   I  J   K   L   M\n",
      "0  5  4  20  5  38  17  5  4  20  5  30  43  44\n",
      "1  5  3  15  5  33  15  5  4  18  5  29  41  43\n",
      "2  5  3  20  5  40  18  5  3  20  5  30  45  45\n",
      "\n",
      "Columns of dataframe: Index(['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M'], dtype='object')\n",
      "Columns of dataframe after renaming: Index(['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'P', 'Q', 'R'], dtype='object')\n",
      "\n",
      "Dataframe after dropping column R using drop():\n",
      "    A  B   C  D   E   F  G  H   I  J   P   Q\n",
      "0  5  4  20  5  38  17  5  4  20  5  30  43\n",
      "1  5  3  15  5  33  15  5  4  18  5  29  41\n",
      "2  5  3  20  5  40  18  5  3  20  5  30  45\n",
      "\n",
      "Dataframe after dropping column Q using pop():\n",
      "    A  B   C  D   E   F  G  H   I  J   P\n",
      "0  5  4  20  5  38  17  5  4  20  5  30\n",
      "1  5  3  15  5  33  15  5  4  18  5  29\n",
      "2  5  3  20  5  40  18  5  3  20  5  30\n",
      "\n",
      "Values of column Q in a list: [43, 41, 45, 43, 45, 45, 44, 44, 40, 45, 45, 44, 33, 45, 41, 40, 43, 35, 44, 40, 38, 43, 45, 39, 40, 40, 45, 44, 43, 30, 45, 40, 43, 41, 44, 42, 41, 42, 45, 45, 44, 43, 43, 45, 41]\n",
      "\n",
      "Dataframe after dropping column P using del:\n",
      "    A  B   C  D   E   F  G  H   I  J\n",
      "0  5  4  20  5  38  17  5  4  20  5\n",
      "1  5  3  15  5  33  15  5  4  18  5\n",
      "2  5  3  20  5  40  18  5  3  20  5\n"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "url=\"https://raw.githubusercontent.com/jamwine/Data/master/Exam%20Result.csv\" \n",
    "# this dataset doesn't contains any column headings\n",
    "df1=pd.read_csv(url,header=None) # header=None is used to prevent data in first row to be determined as the column headings\n",
    "print('Dataset without column headings:\\n',df1.head(3),'\\n')\n",
    "\n",
    "df1.dropna() # dropna() drops all rows containing any one NaN value, data is removed temporarily until we mention the parameter inplace=True\n",
    "df1.dropna(axis=1, how='all',inplace=True) # dropping columns which contains all its values as NaN (how='all') \n",
    "print('Dataset after removing all columns containing NaN values:\\n',df1.head(3))\n",
    "\n",
    "headers=['A','B','C','D','E','F','G','H','I','J','K','L','M']\n",
    "df1.columns=headers # assign the columns with a header\n",
    "print('\\nDataset after assigning columns:\\n',df1.head(3))\n",
    "\n",
    "# sep parameter describes the separator to be (,)  and index=False doesn't includes index to be stored in dataset\n",
    "df1.to_csv(\"Test.csv\", sep=',', index=False) # saving dataframe to test.csv file\n",
    "\n",
    "# renaming columns of dataframe\n",
    "print(\"\\nColumns of dataframe:\",df1.columns)\n",
    "df1.rename(columns={\"K\":\"P\",\"L\":\"Q\",\"M\":\"R\"},inplace=True) # rename(index, columns) is used to rename \n",
    "print(\"Columns of dataframe after renaming:\",df1.columns)\n",
    "\n",
    "df1.drop(labels=[\"R\"],axis=1,inplace=True) # drop() is used to remove elements\n",
    "print(\"\\nDataframe after dropping column R using drop():\\n\",df1.head(3))\n",
    "\n",
    "a=df1.pop(\"Q\")\n",
    "print(\"\\nDataframe after dropping column Q using pop():\\n\",df1.head(3))\n",
    "print(\"\\nValues of column Q in a list:\", list(a))\n",
    "\n",
    "del df1[\"P\"]\n",
    "print(\"\\nDataframe after dropping column P using del:\\n\",df1.head(3))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data extracted with index as column L using index_col\n",
      "     A  B   C  D   E   F  G  H   I  J   K   M\n",
      "L                                           \n",
      "30  5  3  10  5  20  14  5  3  10  5  15  25\n",
      "33  5  3  19  4  30  15  5  3  15  5  15  38\n",
      "35  5  3  15  4  20  14  5  2  15  5  20  38 \n",
      "\n",
      "Data extracted with index as column M using set_index()\n",
      "      L  A  B   C  D   E   F  G  H   I  J   K\n",
      "M                                           \n",
      "25  30  5  3  10  5  20  14  5  3  10  5  15\n",
      "38  33  5  3  19  4  30  15  5  3  15  5  15\n",
      "38  35  5  3  15  4  20  14  5  2  15  5  20 \n",
      "\n",
      "Single column extracted as a dataframe\n",
      "    M\n",
      "0  44\n",
      "1  43\n",
      "2  45 \n",
      "\n",
      "Single column extracted as a series using squeeze\n",
      "0    44\n",
      "1    43\n",
      "2    45\n",
      "Name: M, dtype: int64 \n",
      "\n"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "# the above cell has created a file test.csv\n",
    "a=pd.read_csv(\"Test.csv\",index_col='L') # index_col defines which column is to be used as index\n",
    "a.sort_index(inplace=True) # data is sorted upon index values\n",
    "print('Data extracted with index as column L using index_col\\n',a.head(3),'\\n')\n",
    "\n",
    "a.reset_index(inplace=True) # resets index for the dataframe\n",
    "a.set_index(\"M\",inplace=True) # sets index for the dataframe\n",
    "print('Data extracted with index as column M using set_index()\\n',a.head(3),'\\n')\n",
    "\n",
    "# if a single column is required from the data\n",
    "a=pd.read_csv(\"Test.csv\",usecols=['M']) # returns dataframe\n",
    "print('Single column extracted as a dataframe\\n{}'.format(a.head(3)),'\\n')\n",
    "\n",
    "a=pd.read_csv(\"Test.csv\",usecols=['M'],squeeze=True) # squeeze=True returns a series only if one column is extracted from the dataset\n",
    "print('Single column extracted as a series using squeeze\\n{}'.format(a.head(3)),'\\n')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Monthly Expenditure:\n",
      "          Date       Day Category Expenditure  Cost\n",
      "0  03-06-2017  Saturday     Rent      Hostel  9500\n",
      "1  03-06-2017  Saturday   Travel         Bus   212\n",
      "2  03-06-2017  Saturday   Travel        Auto    50\n",
      "\n",
      "Description of the above dataset:\n",
      "               Date     Day Category Expenditure         Cost\n",
      "count          154     154      154         154   154.000000\n",
      "unique          28       7        5          36          NaN\n",
      "top     26-06-2017  Monday     Food       Juice          NaN\n",
      "freq            17      30       61          21          NaN\n",
      "mean           NaN     NaN      NaN         NaN   187.493506\n",
      "std            NaN     NaN      NaN         NaN   793.529752\n",
      "min            NaN     NaN      NaN         NaN     8.000000\n",
      "25%            NaN     NaN      NaN         NaN    18.000000\n",
      "50%            NaN     NaN      NaN         NaN    33.500000\n",
      "75%            NaN     NaN      NaN         NaN   100.000000\n",
      "max            NaN     NaN      NaN         NaN  9500.000000\n",
      "\n",
      "Dropping rows containing NaN values in Day column using subset:\n",
      "               Date     Day Category Expenditure   Cost\n",
      "count          154     154      154         154  154.0\n",
      "unique          28       7        5          36    NaN\n",
      "top     26-06-2017  Monday     Food       Juice    NaN\n",
      "freq            17      30       61          21    NaN\n",
      "\n",
      "Changing NaN values to 0:\n",
      "               Date     Day Category Expenditure   Cost\n",
      "count          154     154      154         154  154.0\n",
      "unique          28       7        5          36    0.0\n",
      "top     26-06-2017  Monday     Food       Juice    0.0\n",
      "freq            17      30       61          21    0.0\n"
     ]
    }
   ],
   "source": [
    ">\n",
    "\n",
    "> Specify column names\n",
    "```python\n",
    "data=pd.read_csv(filepath, names=['Name1','Name2'])\n",
    "```\n",
    "# MONTHLY EXPENDITURE Dataset\n",
    "url=\"https://raw.githubusercontent.com/jamwine/Data/master/Monthly%20Expenditure.csv\"\n",
    "b=pd.read_csv(url)\n",
    "print(\"Monthly Expenditure:\\n\",b.head(3))\n",
    "\n",
    "c=b.describe(include=\"all\") # include=\"all\" describes the properties for string objects additionally \n",
    "print('\\nDescription of the above dataset:\\n',c)\n",
    "\n",
    "c.dropna(subset=[\"Day\"],inplace=True) # checks for null values only in those columns which are specified in subset\n",
    "print('\\nDropping rows containing NaN values in Day column using subset:\\n',c)\n",
    "\n",
    "c.fillna(0,inplace=True) # replaces NaN in the column with 0\n",
    "print('\\nChanging NaN values to 0:\\n',c)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dataset:\n",
      "          Date       Day Category Expenditure  Cost\n",
      "0  03-06-2017  Saturday     Rent      Hostel  9500\n",
      "1  03-06-2017  Saturday   Travel         Bus   212\n",
      "2  03-06-2017  Saturday   Travel        Auto    50\n",
      "\n",
      "Datatype of columns (Raw data):\n",
      " Date           object\n",
      "Day            object\n",
      "Category       object\n",
      "Expenditure    object\n",
      "Cost            int64\n",
      "dtype: object\n",
      "\n",
      "Datatype of column Date  (After changing):\n",
      " Date           datetime64[ns]\n",
      "Day                    object\n",
      "Category               object\n",
      "Expenditure            object\n",
      "Cost                    int64\n",
      "dtype: object\n",
      "\n",
      "Sample Dataset with parse dates parameter:\n",
      "         Date       Day Category Expenditure  Cost\n",
      "0 2017-03-06  Saturday     Rent      Hostel  9500\n",
      "1 2017-03-06  Saturday   Travel         Bus   212\n",
      "2 2017-03-06  Saturday   Travel        Auto    50\n",
      "\n",
      "Datatype of columns is changed using parse_dates parameter and astype()\n",
      " Date           datetime64[ns]\n",
      "Day                    object\n",
      "Category             category\n",
      "Expenditure            object\n",
      "Cost                  float64\n",
      "dtype: object\n",
      "\n",
      "Unique values in 'Category' column are:\n",
      " [Rent, Travel, Drinks, Food, Basics]\n",
      "Categories (5, object): [Rent, Travel, Drinks, Food, Basics]\n",
      "\n",
      "Length of Unique values in 'Category' column (skipping NaN) 5\n",
      "Length of Unique values in 'Day' column (counting NaN): 7\n",
      "\n",
      "Dataframe after sorting multiple columns:\n",
      "           Date        Day Category Expenditure    Cost\n",
      "0   2017-03-06   Saturday     Rent      Hostel  9500.0\n",
      "132 2017-06-28  Wednesday   Travel         Bus  1814.0\n",
      "131 2017-06-27    Tuesday     Rent        Room  1198.0\n",
      "\n",
      "Dataframe after creating a rank column:\n",
      "         Date       Day Category Expenditure    Cost  Rank\n",
      "0 2017-03-06  Saturday     Rent      Hostel  9500.0   1.0\n",
      "1 2017-03-06  Saturday   Travel         Bus   212.0  29.0\n",
      "2 2017-03-06  Saturday   Travel        Auto    50.0  61.5\n",
      "\n",
      "Dataframe after applying filter:\n",
      "           Date       Day Category Expenditure    Cost  Rank\n",
      "0   2017-03-06  Saturday     Rent      Hostel  9500.0   1.0\n",
      "131 2017-06-27   Tuesday     Rent        Room  1198.0   3.0\n",
      "\n",
      "Dataframe after applying filter using query():\n",
      "           Date       Day Category Expenditure    Cost  Rank\n",
      "0   2017-03-06  Saturday     Rent      Hostel  9500.0   1.0\n",
      "131 2017-06-27   Tuesday     Rent        Room  1198.0   3.0\n",
      "\n",
      "Dataframe after applying filter using contains():\n",
      "          Date        Day Category Expenditure    Cost  Rank\n",
      "0  2017-03-06   Saturday     Rent      Hostel  9500.0   1.0\n",
      "65 2017-06-17   Saturday     Rent      Ticket    40.0  69.5\n",
      "76 2017-06-21  Wednesday     Rent      Airtel   284.0  23.0\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "url=\"https://raw.githubusercontent.com/jamwine/Data/master/Monthly%20Expenditure.csv\"\n",
    "a=pd.read_csv(url)\n",
    "print(\"Dataset:\\n\",a.head(3))\n",
    "\n",
    "print('\\nDatatype of columns (Raw data):\\n',a.dtypes) # dtypes attribute returns the datatype of all columns\n",
    "a['Date']=pd.to_datetime(a['Date'])  # converts string (object) column to date\n",
    "print('\\nDatatype of column Date  (After changing):\\n',a.dtypes)\n",
    "\n",
    "# (alternative method for converting strings into dates)\n",
    "b=pd.read_csv(url, parse_dates=[\"Date\"]) # parse_date parameter converts columns into dates\n",
    "print(\"\\nSample Dataset with parse dates parameter:\\n\",b.head(3))\n",
    "\n",
    "b[\"Category\"]=b[\"Category\"].astype('category') # astype('category') is used to convert string objects into category\n",
    "b[\"Cost\"]=b[\"Cost\"].astype(\"float\") # astype('float') is used to convert int into float\n",
    "print('\\nDatatype of columns is changed using parse_dates parameter and astype()\\n',b.dtypes)\n",
    "\n",
    "print('\\nUnique values in \\'Category\\' column are:\\n',b['Category'].unique()) # returns an array of unique values\n",
    "print('\\nLength of Unique values in \\'Category\\' column (skipping NaN)',b['Category'].nunique()) # returns length of unique values (doesn't counts NaN Values)\n",
    "print('Length of Unique values in \\'Day\\' column (counting NaN):',b['Day'].nunique(dropna=False)) # returns length of unique values (counts NaN Values as dropna=False)\n",
    "\n",
    "print('\\nDataframe after sorting multiple columns:\\n',b.sort_values([\"Cost\",\"Category\"],ascending=[False,True]).head(3)) # sorting multiple columns using sort_values\n",
    "\n",
    "b[\"Rank\"]=b[\"Cost\"].rank(ascending=False) # rank() is used to assign ranks, ascending=False means the greatest will be ranked at first\n",
    "print('\\nDataframe after creating a rank column:\\n',b.head(3))\n",
    "\n",
    "# filtering dataframe based on certain condition\n",
    "# Method 1\n",
    "mask1=b['Cost']>1000\n",
    "mask2=b['Category']==\"Rent\"\n",
    "print(\"\\nDataframe after applying filter:\\n\",b[mask1 & mask2])\n",
    "\n",
    "# Method 2\n",
    "print(\"\\nDataframe after applying filter using query():\\n\",b.query(\"Cost>1000 & Category=='Rent'\"))\n",
    "\n",
    "# Method 3\n",
    "print(\"\\nDataframe after applying filter using contains():\\n\",b[b[\"Category\"].str.contains(\"Rent\")].head(3))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dataset:\n",
      "    Exam1  Exam2  Exam3  Total Marks  Percentage\n",
      "0    240    232    232          704       93.87\n",
      "1    221    163    169          553       73.73\n",
      "2    244    236    239          719       95.87\n",
      "\n",
      "Duplicate Records (First duplicate records are skipped as they are considered unique):\n",
      "     Exam1  Exam2  Exam3  Total Marks  Percentage\n",
      "23    188    160    152          500       66.67\n",
      "24    203    186    164          553       73.73\n",
      "28    241    232    231          704       93.87\n",
      "\n",
      "Duplicate Records (Last duplicate records are skipped as they are considered unique):\n",
      "    Exam1  Exam2  Exam3  Total Marks  Percentage\n",
      "0    240    232    232          704       93.87\n",
      "1    221    163    169          553       73.73\n",
      "8    190    146    164          500       66.67\n",
      "\n",
      "All Duplicate Records having same total marks:\n",
      "     Exam1  Exam2  Exam3  Total Marks  Percentage\n",
      "0     240    232    232          704       93.87\n",
      "1     221    163    169          553       73.73\n",
      "8     190    146    164          500       66.67\n",
      "23    188    160    152          500       66.67\n",
      "24    203    186    164          553       73.73\n",
      "28    241    232    231          704       93.87\n",
      "\n",
      "Returning a list of unique Exam1 marks:  [196, 234, 224, 230, 242, 214, 207, 211, 162, 237, 188, 243, 232, 241, 150, 161, 205, 222, 210, 223, 228, 167, 219]\n",
      "\n",
      "After removing duplicate records:\n",
      "     Exam1  Exam2  Exam3  Total Marks  Percentage\n",
      "38    238    234    234          706       94.13\n",
      "39    223    205    213          641       85.47\n",
      "41    228    222    214          664       88.53\n",
      "42    167    120    133          420       56.00\n",
      "44    219    180    156          555       74.00\n",
      "\n",
      "Extracting some rows from Dataframe:\n",
      "    Exam1  Exam2  Exam3  Total Marks  Percentage\n",
      "0    240    232    232          704       93.87\n",
      "1    221    163    169          553       73.73\n",
      "2    244    236    239          719       95.87 \n",
      "\n",
      "     Exam1  Exam2  Exam3  Total Marks  Percentage\n",
      "39    223    205    213          641       85.47\n",
      "41    228    222    214          664       88.53\n",
      "44    219    180    156          555       74.00\n",
      "\n",
      "Extracting a single row:\n",
      " Exam1          196.0\n",
      "Exam2          160.0\n",
      "Exam3          154.0\n",
      "Total Marks    510.0\n",
      "Percentage      68.0\n",
      "Name: 3, dtype: float64\n",
      "\n",
      "Sample rows from dataframe:\n",
      "     Exam1  Exam2  Exam3  Total Marks  Percentage\n",
      "0     240    232    232          704       93.87\n",
      "2     244    236    239          719       95.87\n",
      "20    162    167    145          474       63.20\n",
      "\n",
      "Sample columns from dataframe:\n",
      "     Percentage  Total Marks\n",
      "41       88.53          664\n",
      "42       56.00          420\n",
      "44       74.00          555\n",
      "\n",
      "Extracting two largest rbows from dataframe:\n",
      "    Exam1  Exam2  Exam3  Total Marks  Percentage\n",
      "9    242    242    242          726       96.80\n",
      "2    244    236    239          719       95.87\n",
      "\n",
      "Extracting two smallest rows from dataframe:\n",
      "     Exam1  Exam2  Exam3  Total Marks  Percentage\n",
      "31    161    127    125          413       55.07\n",
      "42    167    120    133          420       56.00\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "url=\"https://raw.githubusercontent.com/jamwine/Data/master/Class%20Result.csv\"\n",
    "a=pd.read_csv(url) # pd.read_csv(file) is used to read csv file and return it as a dataframe\n",
    "print('Dataset:\\n',a.head(3))\n",
    "\n",
    "x=(a[\"Total Marks\"].duplicated()) # returns true for duplicate values (first duplicate values are considered unique)\n",
    "print(\"\\nDuplicate Records (First duplicate records are skipped as they are considered unique):\\n\",a[x])\n",
    "\n",
    "y=(a[\"Total Marks\"].duplicated(keep=\"last\")) # returns true for duplicate values (last duplicate values are considered unique)\n",
    "print(\"\\nDuplicate Records (Last duplicate records are skipped as they are considered unique):\\n\",a[y])\n",
    "\n",
    "z=a[\"Total Marks\"].duplicated(keep=False) # returns all duplicate values including the first and the last value\n",
    "print(\"\\nAll Duplicate Records having same total marks:\\n\",a[z])\n",
    "\n",
    "p=~a[\"Exam1\"].duplicated(keep=False) # returns all unique values using tilde (~)\n",
    "print(\"\\nReturning a list of unique Exam1 marks: \",list(a[p][\"Exam1\"]))\n",
    "\n",
    "a.drop_duplicates(subset=[\"Exam1\"],keep=\"first\",inplace=True) # removes duplicates rows by checking values for columns mentioned in the subset\n",
    "print(\"\\nAfter removing duplicate records:\\n\",a.tail())\n",
    "\n",
    "print(\"\\nExtracting some rows from Dataframe:\\n\",a.loc[0:2],'\\n\\n',a.loc[[39,41,44]]) # loc[[list of rows]] is used to extract rows from the dataframe\n",
    "\n",
    "print(\"\\nExtracting a single row:\\n\",a.loc[3]) # returns series\n",
    "\n",
    "print(\"\\nSample rows from dataframe:\\n\",a.sample(frac=0.1)) # frac=0.1 means 10% of the dataset will be returned as sample\n",
    "print(\"\\nSample columns from dataframe:\\n\",a.sample(2,axis=1).tail(3))\n",
    "\n",
    "print(\"\\nExtracting two largest rbows from dataframe:\\n\",a.nlargest(2,\"Total Marks\")) # nlargest(number,column to be sorted)\n",
    "print(\"\\nExtracting two smallest rows from dataframe:\\n\",a.nsmallest(2,\"Total Marks\")) # nsmallest(number,column to be sorted)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   Exam1  Exam2  Exam3  Total Marks  Percentage    Grade\n",
      "0    240    232    232          704       93.87  Grade A\n",
      "1    221    163    169          553       73.73  Grade C\n",
      "2    244    236    239          719       95.87  Grade A\n",
      "3    196    160    154          510       68.00  Grade D\n",
      "4    234    196    180          610       81.33  Grade B\n",
      "\n",
      "Series before changing an element in column Exam2:\n",
      " 0    232\n",
      "1    163\n",
      "2    236\n",
      "Name: Exam2, dtype: int64\n",
      "\n",
      "Series after changing an element in column Exam2:\n",
      " 0    232\n",
      "1    170\n",
      "2    236\n",
      "Name: Exam2, dtype: int64\n",
      "\n",
      "Dataframe is also affected by the above change:\n",
      "    Exam1  Exam2  Exam3  Total Marks  Percentage    Grade\n",
      "0    240    232    232          704       93.87  Grade A\n",
      "1    221    170    169          553       73.73  Grade C\n",
      "2    244    236    239          719       95.87  Grade A\n",
      "\n",
      "Series before change in column Exam1:\n",
      " 0    240\n",
      "1    221\n",
      "2    244\n",
      "Name: Exam1, dtype: int64\n",
      "\n",
      "Series after change in column Exam1:\n",
      " 0    240\n",
      "1    230\n",
      "2    244\n",
      "Name: Exam1, dtype: int64\n",
      "\n",
      "Dataframe is not affected by the above change:\n",
      "    Exam1  Exam2  Exam3  Total Marks  Percentage    Grade\n",
      "0    240    232    232          704       93.87  Grade A\n",
      "1    221    170    169          553       73.73  Grade C\n",
      "2    244    236    239          719       95.87  Grade A\n",
      "\n",
      "Count of Grades:\n",
      " B    13\n",
      "A    12\n",
      "D    11\n",
      "C     9\n",
      "Name: Grade, dtype: int64\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\users\\shaur\\appdata\\local\\programs\\python\\python37\\lib\\site-packages\\ipykernel_launcher.py:25: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "url=\"https://raw.githubusercontent.com/jamwine/Data/master/Class%20Result.csv\"\n",
    "a=pd.read_csv(url) # pd.read_csv(file) is used to read csv file and return it as a dataframe\n",
    "\n",
    "# defining function\n",
    "def grade(row):\n",
    "    if row['Percentage']>=90:\n",
    "        s=\"Grade A\"\n",
    "    elif row['Percentage']>=80:\n",
    "        s=\"Grade B\"\n",
    "    elif row['Percentage']>=70:\n",
    "        s=\"Grade C\"\n",
    "    else:\n",
    "        s=\"Grade D\"\n",
    "    return s\n",
    "\n",
    "a[\"Grade\"]=a.apply(grade,axis=\"columns\") # apply() is used to apply function on all elements in the dataset\n",
    "print(a.head())\n",
    "\n",
    "# changing an element in column\n",
    "b=a[\"Exam2\"] # series is extracted\n",
    "print(\"\\nSeries before changing an element in column Exam2:\\n\",b.head(3)) # index 1 has value 163\n",
    "b[1]=170 # element is modified, SettingWithCopyWarning occurs: A value is trying to be set on a copy of a slice from a DataFrame\n",
    "print(\"\\nSeries after changing an element in column Exam2:\\n\",b.head(3)) # index 1 has a new value 170\n",
    "print(\"\\nDataframe is also affected by the above change:\\n\",a.head(3)) #  index 1 is changed to new value 170 from 163\n",
    "\n",
    "# in order to prevent values to be changed from original dataframe, copy() is used\n",
    "c=a[\"Exam1\"].copy()\n",
    "print(\"\\nSeries before change in column Exam1:\\n\",c.head(3)) # index 1 has value 223\n",
    "c[1]=230 # element is modified\n",
    "print(\"\\nSeries after change in column Exam1:\\n\",c.head(3)) # index 1 has a new value 230\n",
    "print(\"\\nDataframe is not affected by the above change:\\n\",a.head(3)) #  index 1 is not changed, still retains old value 221\n",
    "\n",
    "print(\"\\nCount of Grades:\\n\",a[\"Grade\"].str.split(\" \").str.get(1).value_counts()) # str.split() is used to split on series, str.get() extracts the element from the returned array"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dataset:\n",
      "    Exam1  Exam2  Exam3  Total Marks  Percentage\n",
      "0    240    232    232          704       93.87\n",
      "1    221    163    169          553       73.73\n",
      "2    244    236    239          719       95.87\n",
      "\n",
      "Bins: [413.0, 517.3333333333334, 621.6666666666666, 726.0]\n",
      "\n",
      "\n",
      "When using: pd.cut(a[\"Total Marks\"],bins)\n",
      "\n",
      "Maximum value is included:\n",
      "     Exam1  Exam2  Exam3  Total Marks  Percentage               Grade\n",
      "8     190    146    164          500       66.67    (413.0, 517.333]\n",
      "9     242    242    242          726       96.80    (621.667, 726.0]\n",
      "10    221    174    186          581       77.47  (517.333, 621.667]\n",
      "\n",
      "Minimum value is not included:\n",
      "     Exam1  Exam2  Exam3  Total Marks  Percentage             Grade\n",
      "30    236    203    192          631       84.13  (621.667, 726.0]\n",
      "31    161    127    125          413       55.07               NaN\n",
      "32    240    230      0          470       62.67  (413.0, 517.333]\n",
      "\n",
      "\n",
      "When using: pd.cut(a[\"Total Marks\"],bins,right=False)\n",
      "\n",
      "Maximum value is not included:\n",
      "     Exam1  Exam2  Exam3  Total Marks  Percentage               Grade\n",
      "8     190    146    164          500       66.67    [413.0, 517.333)\n",
      "9     242    242    242          726       96.80                 NaN\n",
      "10    221    174    186          581       77.47  [517.333, 621.667)\n",
      "\n",
      "Minimum value is included:\n",
      "     Exam1  Exam2  Exam3  Total Marks  Percentage             Grade\n",
      "30    236    203    192          631       84.13  [621.667, 726.0)\n",
      "31    161    127    125          413       55.07  [413.0, 517.333)\n",
      "32    240    230      0          470       62.67  [413.0, 517.333)\n",
      "\n",
      "\n",
      "When using: pd.cut(a[\"Total Marks\"],bins,include_lowest=True)\n",
      "\n",
      "Maximum value is included:\n",
      "     Exam1  Exam2  Exam3  Total Marks  Percentage               Grade\n",
      "8     190    146    164          500       66.67  (412.999, 517.333]\n",
      "9     242    242    242          726       96.80    (621.667, 726.0]\n",
      "10    221    174    186          581       77.47  (517.333, 621.667]\n",
      "\n",
      "Minimum value is included:\n",
      "     Exam1  Exam2  Exam3  Total Marks  Percentage               Grade\n",
      "30    236    203    192          631       84.13    (621.667, 726.0]\n",
      "31    161    127    125          413       55.07  (412.999, 517.333]\n",
      "32    240    230      0          470       62.67  (412.999, 517.333]\n",
      "\n",
      "\n",
      "When using: pd.cut(a[\"Total Marks\"],bins,include_lowest=True,labels=bin_names)\n",
      "where bin_names are ['Grade C', 'Grade B', 'Grade A']\n",
      "\n",
      "Grade with labels:\n",
      "    Exam1  Exam2  Exam3  Total Marks  Percentage    Grade\n",
      "0    240    232    232          704       93.87  Grade A\n",
      "1    221    163    169          553       73.73  Grade B\n",
      "2    244    236    239          719       95.87  Grade A\n"
     ]
    },
    {
     "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>...</th>\n",
       "      <th>35</th>\n",
       "      <th>36</th>\n",
       "      <th>37</th>\n",
       "      <th>38</th>\n",
       "      <th>39</th>\n",
       "      <th>40</th>\n",
       "      <th>41</th>\n",
       "      <th>42</th>\n",
       "      <th>43</th>\n",
       "      <th>44</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Exam1</th>\n",
       "      <td>240</td>\n",
       "      <td>221</td>\n",
       "      <td>244</td>\n",
       "      <td>196</td>\n",
       "      <td>234</td>\n",
       "      <td>224</td>\n",
       "      <td>225</td>\n",
       "      <td>230</td>\n",
       "      <td>190</td>\n",
       "      <td>242</td>\n",
       "      <td>...</td>\n",
       "      <td>203</td>\n",
       "      <td>212</td>\n",
       "      <td>210</td>\n",
       "      <td>238</td>\n",
       "      <td>223</td>\n",
       "      <td>238</td>\n",
       "      <td>228</td>\n",
       "      <td>167</td>\n",
       "      <td>238</td>\n",
       "      <td>219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Exam2</th>\n",
       "      <td>232</td>\n",
       "      <td>163</td>\n",
       "      <td>236</td>\n",
       "      <td>160</td>\n",
       "      <td>196</td>\n",
       "      <td>206</td>\n",
       "      <td>198</td>\n",
       "      <td>228</td>\n",
       "      <td>146</td>\n",
       "      <td>242</td>\n",
       "      <td>...</td>\n",
       "      <td>190</td>\n",
       "      <td>206</td>\n",
       "      <td>191</td>\n",
       "      <td>234</td>\n",
       "      <td>205</td>\n",
       "      <td>198</td>\n",
       "      <td>222</td>\n",
       "      <td>120</td>\n",
       "      <td>231</td>\n",
       "      <td>180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Exam3</th>\n",
       "      <td>232</td>\n",
       "      <td>169</td>\n",
       "      <td>239</td>\n",
       "      <td>154</td>\n",
       "      <td>180</td>\n",
       "      <td>191</td>\n",
       "      <td>203</td>\n",
       "      <td>220</td>\n",
       "      <td>164</td>\n",
       "      <td>242</td>\n",
       "      <td>...</td>\n",
       "      <td>191</td>\n",
       "      <td>210</td>\n",
       "      <td>182</td>\n",
       "      <td>234</td>\n",
       "      <td>213</td>\n",
       "      <td>200</td>\n",
       "      <td>214</td>\n",
       "      <td>133</td>\n",
       "      <td>231</td>\n",
       "      <td>156</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Total Marks</th>\n",
       "      <td>704</td>\n",
       "      <td>553</td>\n",
       "      <td>719</td>\n",
       "      <td>510</td>\n",
       "      <td>610</td>\n",
       "      <td>621</td>\n",
       "      <td>626</td>\n",
       "      <td>678</td>\n",
       "      <td>500</td>\n",
       "      <td>726</td>\n",
       "      <td>...</td>\n",
       "      <td>584</td>\n",
       "      <td>628</td>\n",
       "      <td>583</td>\n",
       "      <td>706</td>\n",
       "      <td>641</td>\n",
       "      <td>636</td>\n",
       "      <td>664</td>\n",
       "      <td>420</td>\n",
       "      <td>700</td>\n",
       "      <td>555</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Percentage</th>\n",
       "      <td>93.87</td>\n",
       "      <td>73.73</td>\n",
       "      <td>95.87</td>\n",
       "      <td>68</td>\n",
       "      <td>81.33</td>\n",
       "      <td>82.8</td>\n",
       "      <td>83.47</td>\n",
       "      <td>90.4</td>\n",
       "      <td>66.67</td>\n",
       "      <td>96.8</td>\n",
       "      <td>...</td>\n",
       "      <td>77.87</td>\n",
       "      <td>83.73</td>\n",
       "      <td>77.73</td>\n",
       "      <td>94.13</td>\n",
       "      <td>85.47</td>\n",
       "      <td>84.8</td>\n",
       "      <td>88.53</td>\n",
       "      <td>56</td>\n",
       "      <td>93.33</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Grade</th>\n",
       "      <td>Grade A</td>\n",
       "      <td>Grade B</td>\n",
       "      <td>Grade A</td>\n",
       "      <td>Grade C</td>\n",
       "      <td>Grade B</td>\n",
       "      <td>Grade B</td>\n",
       "      <td>Grade A</td>\n",
       "      <td>Grade A</td>\n",
       "      <td>Grade C</td>\n",
       "      <td>Grade A</td>\n",
       "      <td>...</td>\n",
       "      <td>Grade B</td>\n",
       "      <td>Grade A</td>\n",
       "      <td>Grade B</td>\n",
       "      <td>Grade A</td>\n",
       "      <td>Grade A</td>\n",
       "      <td>Grade A</td>\n",
       "      <td>Grade A</td>\n",
       "      <td>Grade C</td>\n",
       "      <td>Grade A</td>\n",
       "      <td>Grade B</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6 rows × 45 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  0        1        2        3        4        5        6   \\\n",
       "Exam1            240      221      244      196      234      224      225   \n",
       "Exam2            232      163      236      160      196      206      198   \n",
       "Exam3            232      169      239      154      180      191      203   \n",
       "Total Marks      704      553      719      510      610      621      626   \n",
       "Percentage     93.87    73.73    95.87       68    81.33     82.8    83.47   \n",
       "Grade        Grade A  Grade B  Grade A  Grade C  Grade B  Grade B  Grade A   \n",
       "\n",
       "                  7        8        9   ...       35       36       37  \\\n",
       "Exam1            230      190      242  ...      203      212      210   \n",
       "Exam2            228      146      242  ...      190      206      191   \n",
       "Exam3            220      164      242  ...      191      210      182   \n",
       "Total Marks      678      500      726  ...      584      628      583   \n",
       "Percentage      90.4    66.67     96.8  ...    77.87    83.73    77.73   \n",
       "Grade        Grade A  Grade C  Grade A  ...  Grade B  Grade A  Grade B   \n",
       "\n",
       "                  38       39       40       41       42       43       44  \n",
       "Exam1            238      223      238      228      167      238      219  \n",
       "Exam2            234      205      198      222      120      231      180  \n",
       "Exam3            234      213      200      214      133      231      156  \n",
       "Total Marks      706      641      636      664      420      700      555  \n",
       "Percentage     94.13    85.47     84.8    88.53       56    93.33       74  \n",
       "Grade        Grade A  Grade A  Grade A  Grade A  Grade C  Grade A  Grade B  \n",
       "\n",
       "[6 rows x 45 columns]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# creating bins (converting quantitative values into categorical values)\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "url=\"https://raw.githubusercontent.com/jamwine/Data/master/Class%20Result.csv\"\n",
    "a=pd.read_csv(url)\n",
    "print(\"Dataset:\\n\",a.head(3))\n",
    "bins=np.linspace(min(a[\"Total Marks\"]),max(a[\"Total Marks\"]),4)\n",
    "print(\"\\nBins:\",list(bins))\n",
    "\n",
    "a[\"Grade\"]=pd.cut(a[\"Total Marks\"],bins) # pd.cut(column,bins) is used to create bins on the specified column\n",
    "# by default, parameter right=true, max value is included while min value is not included\n",
    "print('\\n\\nWhen using: pd.cut(a[\"Total Marks\"],bins)')\n",
    "print(\"\\nMaximum value is included:\\n\",a[8:11]) \n",
    "print(\"\\nMinimum value is not included:\\n\",a[30:33])\n",
    "\n",
    "# right=false is used to exclude the right-most values, max value is excluded while min value is included\n",
    "a[\"Grade\"]=pd.cut(a[\"Total Marks\"],bins,right=False)\n",
    "print('\\n\\nWhen using: pd.cut(a[\"Total Marks\"],bins,right=False)')\n",
    "print(\"\\nMaximum value is not included:\\n\",a[8:11])\n",
    "print(\"\\nMinimum value is included:\\n\",a[30:33])\n",
    "\n",
    "# include_lowest parameter is used to include the lowest value in the bin, both min and max value is included\n",
    "a[\"Grade\"]=pd.cut(a[\"Total Marks\"],bins,include_lowest =True)\n",
    "print('\\n\\nWhen using: pd.cut(a[\"Total Marks\"],bins,include_lowest=True)')\n",
    "print(\"\\nMaximum value is included:\\n\",a[8:11])\n",
    "print(\"\\nMinimum value is included:\\n\",a[30:33])\n",
    "\n",
    "bin_names=[\"Grade C\",\"Grade B\",\"Grade A\"]\n",
    "print('\\n\\nWhen using: pd.cut(a[\"Total Marks\"],bins,include_lowest=True,labels=bin_names)\\nwhere bin_names are {}'.format(bin_names))\n",
    "a[\"Grade\"]=pd.cut(a[\"Total Marks\"],bins,include_lowest =True,labels=bin_names)\n",
    "print(\"\\nGrade with labels:\\n\",a.head(3))\n",
    "\n",
    "a.transpose() # transpose of dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dataset:\n",
      "          Date       Day Category Expenditure  Cost\n",
      "0  03-06-2017  Saturday     Rent      Hostel  9500\n",
      "1  03-06-2017  Saturday   Travel         Bus   212\n",
      "2  03-06-2017  Saturday   Travel        Auto    50\n",
      "\n",
      "Converting categorical value into quantitative value: \n",
      "      Sunday  Thursday  Tuesday\n",
      "40        1         0        0\n",
      "26        0         1        0\n",
      "47        0         0        1\n",
      "105       1         0        0\n",
      "58        0         1        0\n"
     ]
    }
   ],
   "source": [
    "# converting categorical values into quantitative values\n",
    "\n",
    "pd.options.display.max_columns=8 # this defines the number of columns displayed while printing a dataframe\n",
    "\n",
    "url=\"https://raw.githubusercontent.com/jamwine/Data/master/Monthly%20Expenditure.csv\"\n",
    "b=pd.read_csv(url)\n",
    "print(\"Dataset:\\n\",b.head(3))\n",
    "print(\"\\nConverting categorical value into quantitative value: \\n\",pd.get_dummies(b[\"Day\"].sample(5))) # get_dummies(column) is used to convert each unique categorical value in the column into quantitative value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DataFrame:\n",
      "              Name\n",
      "0     Trishant Dev\n",
      "1  Bhaskar Kaushik\n",
      "2     Sakshi Singh\n",
      "\n",
      "Splitting the names:\n",
      "          0         1           2\n",
      "5   Pankaj  Agarwaal        None\n",
      "6  Shaurya   Khurana        None\n",
      "7   Akshay     Kumar  Kusneniwar\n",
      "\n",
      "Adding columns to dataframe:\n",
      "                       Name          Dep First Name         Last Name\n",
      "0             Trishant Dev  Electronics   Trishant               Dev\n",
      "1          Bhaskar Kaushik  Electronics    Bhaskar           Kaushik\n",
      "2             Sakshi Singh  Electronics     Sakshi             Singh\n",
      "3               Dhruv Garg  Electronics      Dhruv              Garg\n",
      "4              Yogesh Goel  Electronics     Yogesh              Goel\n",
      "5          Pankaj Agarwaal  Electronics     Pankaj          Agarwaal\n",
      "6          Shaurya Khurana  Electronics    Shaurya           Khurana\n",
      "7  Akshay Kumar Kusneniwar  Electronics     Akshay  Kumar Kusneniwar\n",
      "\n",
      "Multi-indexes:\n",
      " MultiIndex([('Team_1', 1),\n",
      "            ('Team_1', 2),\n",
      "            ('Team_1', 3),\n",
      "            ('Team_1', 4),\n",
      "            ('Team_2', 1),\n",
      "            ('Team_2', 2),\n",
      "            ('Team_2', 3),\n",
      "            ('Team_2', 4)],\n",
      "           )\n",
      "\n",
      "Extracting index level-wise:\n",
      " Index first level:\n",
      " Index(['Team_1', 'Team_1', 'Team_1', 'Team_1', 'Team_2', 'Team_2', 'Team_2',\n",
      "       'Team_2'],\n",
      "      dtype='object') \n",
      "Index second level:\n",
      " Int64Index([1, 2, 3, 4, 1, 2, 3, 4], dtype='int64')\n",
      "\n",
      "Sorting Indexes:\n",
      "                            Name          Dep First Name Last Name\n",
      "Team   Project                                                   \n",
      "Team_1 3           Sakshi Singh  Electronics     Sakshi     Singh\n",
      "       2        Bhaskar Kaushik  Electronics    Bhaskar   Kaushik\n",
      "       1           Trishant Dev  Electronics   Trishant       Dev\n",
      "\n",
      "Extracting columns from multi-index using loc():\n",
      " Name          Shaurya Khurana\n",
      "Dep               Electronics\n",
      "First Name            Shaurya\n",
      "Last Name             Khurana\n",
      "Name: (Team_2, 3), dtype: object\n",
      "\n",
      "Extracting columns from multi-index using iloc():\n",
      " Name          Akshay Kumar Kusneniwar\n",
      "Dep                       Electronics\n",
      "First Name                     Akshay\n",
      "Last Name            Kumar Kusneniwar\n",
      "Name: (Team_2, 4), dtype: object\n",
      "\n",
      "Extracting single column value from multi-index: Kaushik\n",
      "\n",
      "Extracting elements with xs():\n",
      "                    Name          Dep First Name Last Name\n",
      "Team                                                     \n",
      "Team_1  Bhaskar Kaushik  Electronics    Bhaskar   Kaushik\n",
      "Team_2  Pankaj Agarwaal  Electronics     Pankaj  Agarwaal\n",
      "\n",
      "After Swapping Index:\n",
      "                            Name          Dep First Name Last Name\n",
      "Project Team                                                     \n",
      "1       Team_1     Trishant Dev  Electronics   Trishant       Dev\n",
      "2       Team_1  Bhaskar Kaushik  Electronics    Bhaskar   Kaushik\n",
      "3       Team_1     Sakshi Singh  Electronics     Sakshi     Singh\n",
      "\n",
      "Dataframe is converted into series:\n",
      " Team    Project            \n",
      "Team_1  1        Name          Trishant Dev\n",
      "                 Dep            Electronics\n",
      "                 First Name        Trishant\n",
      "                 Last Name              Dev\n",
      "dtype: object\n",
      "\n",
      "Series is converted into dataframe using to_frame():\n",
      "                                       0\n",
      "Team   Project                         \n",
      "Team_1 1       Name        Trishant Dev\n",
      "               Dep          Electronics\n",
      "               First Name      Trishant\n",
      "               Last Name            Dev\n",
      "\n",
      "Series is converted into dataframe using unstack():\n",
      "                            Name          Dep First Name Last Name\n",
      "Team   Project                                                   \n",
      "Team_1 1           Trishant Dev  Electronics   Trishant       Dev\n",
      "       2        Bhaskar Kaushik  Electronics    Bhaskar   Kaushik\n",
      "       3           Sakshi Singh  Electronics     Sakshi     Singh\n"
     ]
    },
    {
     "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>Project</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Team</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Team_1</th>\n",
       "      <th>Name</th>\n",
       "      <td>Trishant Dev</td>\n",
       "      <td>Bhaskar Kaushik</td>\n",
       "      <td>Sakshi Singh</td>\n",
       "      <td>Dhruv Garg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dep</th>\n",
       "      <td>Electronics</td>\n",
       "      <td>Electronics</td>\n",
       "      <td>Electronics</td>\n",
       "      <td>Electronics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>First Name</th>\n",
       "      <td>Trishant</td>\n",
       "      <td>Bhaskar</td>\n",
       "      <td>Sakshi</td>\n",
       "      <td>Dhruv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Last Name</th>\n",
       "      <td>Dev</td>\n",
       "      <td>Kaushik</td>\n",
       "      <td>Singh</td>\n",
       "      <td>Garg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Team_2</th>\n",
       "      <th>Name</th>\n",
       "      <td>Yogesh Goel</td>\n",
       "      <td>Pankaj Agarwaal</td>\n",
       "      <td>Shaurya Khurana</td>\n",
       "      <td>Akshay Kumar Kusneniwar</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dep</th>\n",
       "      <td>Electronics</td>\n",
       "      <td>Electronics</td>\n",
       "      <td>Electronics</td>\n",
       "      <td>Electronics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>First Name</th>\n",
       "      <td>Yogesh</td>\n",
       "      <td>Pankaj</td>\n",
       "      <td>Shaurya</td>\n",
       "      <td>Akshay</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Last Name</th>\n",
       "      <td>Goel</td>\n",
       "      <td>Agarwaal</td>\n",
       "      <td>Khurana</td>\n",
       "      <td>Kumar Kusneniwar</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Project                       1                2                3  \\\n",
       "Team                                                                \n",
       "Team_1 Name        Trishant Dev  Bhaskar Kaushik     Sakshi Singh   \n",
       "       Dep          Electronics      Electronics      Electronics   \n",
       "       First Name      Trishant          Bhaskar           Sakshi   \n",
       "       Last Name            Dev          Kaushik            Singh   \n",
       "Team_2 Name         Yogesh Goel  Pankaj Agarwaal  Shaurya Khurana   \n",
       "       Dep          Electronics      Electronics      Electronics   \n",
       "       First Name        Yogesh           Pankaj          Shaurya   \n",
       "       Last Name           Goel         Agarwaal          Khurana   \n",
       "\n",
       "Project                                  4  \n",
       "Team                                        \n",
       "Team_1 Name                     Dhruv Garg  \n",
       "       Dep                     Electronics  \n",
       "       First Name                    Dhruv  \n",
       "       Last Name                      Garg  \n",
       "Team_2 Name        Akshay Kumar Kusneniwar  \n",
       "       Dep                     Electronics  \n",
       "       First Name                   Akshay  \n",
       "       Last Name          Kumar Kusneniwar  "
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "names=[\"Trishant Dev\",\"Bhaskar Kaushik\",\"Sakshi Singh\",\"Dhruv Garg\",\"Yogesh Goel\",\"Pankaj Agarwaal\",\"Shaurya Khurana\", \"Akshay Kumar Kusneniwar\"]\n",
    "a=pd.DataFrame(names,columns=[\"Name\"])\n",
    "print(\"DataFrame:\\n{}\".format(a.head(3)))\n",
    "print(\"\\nSplitting the names:\\n\",a[\"Name\"].str.split(\" \", expand=True).tail(3)) # expand=True splits the strings and returns the result in different columns; None is for missing values\n",
    "\n",
    "a[\"Dep\"]=\"Electronics\" \n",
    "a[[\"First Name\",\"Last Name\"]]=a[\"Name\"].str.split(\" \", expand=True, n=1) # n=1 splits the string once, thus returning two columns only\n",
    "print(\"\\nAdding columns to dataframe:\\n\",a)\n",
    "\n",
    "# creating multi_index using zip()\n",
    "x=[\"Team_1\",\"Team_1\",\"Team_1\",\"Team_1\",\"Team_2\",\"Team_2\",\"Team_2\",\"Team_2\"]\n",
    "y=[1,2,3,4,1,2,3,4]\n",
    "z=list(zip(x,y)) \n",
    "multi_index=pd.MultiIndex.from_tuples(z)\n",
    "\n",
    "a.set_index(multi_index,inplace=True) # multi-index (team and project) for each row in dataframe\n",
    "print(\"\\nMulti-indexes:\\n\",a.index)\n",
    "\n",
    "print(\"\\nExtracting index level-wise:\\n\",\"Index first level:\\n\",a.index.get_level_values(0),\"\\nIndex second level:\\n\",a.index.get_level_values(1)) # get_level_values() is used to extract index level-wise\n",
    "a.index.set_names([\"Team\",\"Project\"],inplace=True) # index name changed from Dep to Department\n",
    "\n",
    "print(\"\\nSorting Indexes:\\n\",a.sort_index(ascending=False).tail(3))\n",
    "\n",
    "print(\"\\nExtracting columns from multi-index using loc():\\n\",a.loc[(\"Team_2\",3)]) # a.loc[(multi-index)) returns all columns of the specified index\n",
    "print(\"\\nExtracting columns from multi-index using iloc():\\n\",a.iloc[(7)])\n",
    "\n",
    "print(\"\\nExtracting single column value from multi-index:\",a.loc[(\"Team_1\",2),\"Last Name\"]) # returns value for specified column\n",
    "\n",
    "print(\"\\nExtracting elements with xs():\\n\",a.xs(2,level=\"Project\")) # xs() is used to extract from index\n",
    "\n",
    "print(\"\\nAfter Swapping Index:\\n\",a.swaplevel().head(3)) # in order to ensure efficiency, we should have common indexes at outer levels\n",
    "\n",
    "b=a.stack()\n",
    "print(\"\\nDataframe is converted into series:\\n\",b.head(4)) # stack() is used to return one column for whole dataframe\n",
    "print(\"\\nSeries is converted into dataframe using to_frame():\\n\",b.to_frame().head(4)) # to_frame() returns dataframe with indexes and columns\n",
    "print(\"\\nSeries is converted into dataframe using unstack():\\n\",b.unstack().head(3)) # unstack() is used to unstack the stacked column\n",
    "b.unstack(\"Project\") # unstack(column_index|column name) is used to define table-like structure by unstacking the specified column index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dataset having length: 150 \n",
      "         Date Country  Cost\n",
      "0 2017-01-01   India    10\n",
      "1 2017-01-02   India    20\n",
      "2 2017-01-03   India    30\n",
      "\n",
      "Dataset using pivot() having new length: 30 \n",
      " Country     China  England  India  Russia  US\n",
      "Date                                         \n",
      "2017-01-01      2        4     10       5   3\n",
      "2017-01-02      4        8     20      10   6\n",
      "2017-01-03      6       12     30      15   9\n",
      "2017-01-04      8       16     40      20  12\n",
      "2017-01-05     10       20     50      25  15\n",
      "\n",
      "Dataset is unpivoted using melt():\n",
      "   Country  Cost\n",
      "0   China     2\n",
      "1   China     4\n",
      "2   China     6\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "url=\"https://raw.githubusercontent.com/jamwine/Data/master/Test%20Data.csv\"\n",
    "a=pd.read_csv(url,parse_dates=[\"Date\"])\n",
    "print(\"Dataset having length:\",len(a),\"\\n\",a.head(3))\n",
    "\n",
    "b=a.pivot(index=\"Date\",columns=\"Country\",values=\"Cost\") # pivot() is used to reshape the dataset, index must be unique\n",
    "print(\"\\nDataset using pivot() having new length:\",len(b),\"\\n\",b.head())\n",
    "print(\"\\nDataset is unpivoted using melt():\\n\",pd.melt(b,value_name=\"Cost\").head(3)) # melt() is used to unpivot the dataset in separate rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dataset:\n",
      "          Date       Day Category Expenditure  Cost\n",
      "0  03-06-2017  Saturday     Rent      Hostel  9500\n",
      "1  03-06-2017  Saturday   Travel         Bus   212\n",
      "2  03-06-2017  Saturday   Travel        Auto    50\n",
      "\n",
      "Data extracted using pivot_table():\n",
      " Category              Basics  Drinks  Food  Rent  Travel\n",
      "Day       Date                                          \n",
      "Friday    09-06-2017       0      25   150     0      10\n",
      "          16-06-2017       0      18    75     0      60\n",
      "          23-06-2017       0      30   699     0     300\n",
      "          30-06-2017       0      25   560    10     664\n",
      "Monday    05-06-2017     296      40    20     0      36\n",
      "          12-06-2017       0      25    81     0       0\n",
      "          19-06-2017       0      35    40     0       0\n",
      "          26-06-2017       0     110   490   408     872\n",
      "Saturday  03-06-2017       0      10     0  9500     262\n",
      "          10-06-2017       0       0   165     0      40\n",
      "          17-06-2017      80       0     0    40       0\n",
      "          24-06-2017      50      81   487   155     640\n",
      "Sunday    04-06-2017       0      20   165     0     165\n",
      "          11-06-2017      35      15    55     0       0\n",
      "          18-06-2017       0       0   290     0       0\n",
      "          25-06-2017     823       0   240     0    1104\n",
      "Thursday  08-06-2017       0      18    12     0      10\n",
      "          15-06-2017       0      35   106     0      60\n",
      "          22-06-2017       0      20    80     0      10\n",
      "          29-06-2017     886      70   110     0     110\n",
      "Tuesday   06-06-2017       0      28   183     0     160\n",
      "          13-06-2017       0      25    30     0      10\n",
      "          20-06-2017       0      10    55     0      10\n",
      "          27-06-2017       0      10   595  1298     986\n",
      "Wednesday 07-06-2017       0      28     0     0      10\n",
      "          14-06-2017       0      30   110     0      50\n",
      "          21-06-2017       0      20   110   284      10\n",
      "          28-06-2017    1270     160   520     0    1844\n",
      "\n",
      "Data extracted using pivot_table():\n",
      "            Cost\n",
      "Category       \n",
      "Basics     3440\n",
      "Drinks      888\n",
      "Food       5428\n",
      "Rent      11695\n",
      "Travel     7423\n"
     ]
    }
   ],
   "source": [
    "url=\"https://raw.githubusercontent.com/jamwine/Data/master/Monthly%20Expenditure.csv\"\n",
    "c=pd.read_csv(url)\n",
    "print(\"Dataset:\\n\",c.head(3))\n",
    "\n",
    "# pivot_table is used to aggregate data based on our specified conditions, values represents data, index can be single/multi-level,columns represents unique categories and can be single/multi-level\n",
    "print(\"\\nData extracted using pivot_table():\\n\",np.around(c.pivot_table(values=\"Cost\",index=[\"Day\",\"Date\"],columns=\"Category\",aggfunc=\"sum\",fill_value=0),0))\n",
    "print(\"\\nData extracted using pivot_table():\\n\",np.around(c.pivot_table(values=\"Cost\",index=\"Category\",aggfunc=\"sum\",fill_value=0),0))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dataset: <class 'pandas.core.frame.DataFrame'> \n",
      "\n",
      "         Date       Day Category Expenditure  Cost\n",
      "0 2017-03-06  Saturday     Rent      Hostel  9500\n",
      "1 2017-03-06  Saturday   Travel         Bus   212\n",
      "2 2017-03-06  Saturday   Travel        Auto    50\n",
      "\n",
      "Dataset with groupby(): <class 'pandas.core.groupby.generic.DataFrameGroupBy'> having length: 5 and size:\n",
      " Category\n",
      "Basics     8\n",
      "Drinks    35\n",
      "Food      61\n",
      "Rent      14\n",
      "Travel    36\n",
      "dtype: int64\n",
      "\n",
      "First rows in each category:\n",
      "                Date       Day  Expenditure  Cost\n",
      "Category                                        \n",
      "Basics   2017-05-06    Monday  Supermarket   296\n",
      "Drinks   2017-03-06  Saturday          Tea    10\n",
      "Food     2017-04-06    Sunday       Dinner   120\n",
      "Rent     2017-03-06  Saturday       Hostel  9500\n",
      "Travel   2017-03-06  Saturday          Bus   212\n",
      "\n",
      "Last rows in each category:\n",
      "                Date       Day  Expenditure  Cost\n",
      "Category                                        \n",
      "Basics   2017-06-29  Thursday  Supermarket   886\n",
      "Drinks   2017-06-30    Friday        Juice    25\n",
      "Food     2017-06-30    Friday      Biscuit   260\n",
      "Rent     2017-06-30    Friday       Ticket    10\n",
      "Travel   2017-06-30    Friday         Bike   664\n",
      "\n",
      "Grouping of dataset:\n",
      " {'Basics': Int64Index([16, 39, 66, 95, 105, 138, 139, 144], dtype='int64'), 'Drinks': Int64Index([  3,   4,   9,  11,  15,  21,  22,  24,  25,  28,  32,  41,  43,\n",
      "             48,  53,  58,  63,  69,  72,  78,  83,  87,  90,  93,  97, 104,\n",
      "            117, 119, 121, 127, 134, 135, 145, 146, 153],\n",
      "           dtype='int64'), 'Food': Int64Index([  5,   6,   8,  13,  18,  20,  27,  30,  31,  33,  35,  37,  38,\n",
      "             40,  42,  44,  45,  47,  50,  51,  52,  55,  56,  57,  59,  61,\n",
      "             62,  64,  67,  68,  71,  73,  75,  77,  79,  81,  82,  84,  85,\n",
      "             88,  89,  92,  94,  96,  98, 103, 106, 113, 120, 124, 129, 130,\n",
      "            133, 136, 137, 141, 142, 147, 148, 149, 150],\n",
      "           dtype='int64'), 'Rent': Int64Index([0, 65, 76, 91, 99, 100, 101, 108, 111, 116, 118, 128, 131, 151], dtype='int64'), 'Travel': Int64Index([  1,   2,   7,  10,  12,  14,  17,  19,  23,  26,  29,  34,  36,\n",
      "             46,  49,  54,  60,  70,  74,  80,  86, 102, 107, 109, 110, 112,\n",
      "            114, 115, 122, 123, 125, 126, 132, 140, 143, 152],\n",
      "           dtype='int64')}\n",
      "\n",
      "Retrieving a category 'Basics' from group:\n",
      "           Date        Day  Expenditure  Cost\n",
      "16  2017-05-06     Monday  Supermarket   296\n",
      "39  2017-11-06     Sunday  Supermarket    35\n",
      "66  2017-06-17   Saturday  Supermarket    80\n",
      "95  2017-06-24   Saturday       Pillow    50\n",
      "105 2017-06-25     Sunday  Supermarket   823\n",
      "138 2017-06-28  Wednesday      Clothes   800\n",
      "139 2017-06-28  Wednesday       Mandir   470\n",
      "144 2017-06-29   Thursday  Supermarket   886\n",
      "\n",
      "Max. in each category:\n",
      "                Date        Day  Expenditure  Cost\n",
      "Category                                         \n",
      "Basics   2017-11-06  Wednesday  Supermarket   886\n",
      "Drinks   2017-12-06  Wednesday        Water    90\n",
      "Food     2017-12-06  Wednesday        Sweet   345\n",
      "Rent     2017-06-30  Wednesday       Ticket  9500\n",
      "Travel   2017-10-06  Wednesday          Cab  1814\n",
      "\n",
      "Min. in each category:\n",
      "                Date     Day Expenditure  Cost\n",
      "Category                                     \n",
      "Basics   2017-05-06  Monday     Clothes    35\n",
      "Drinks   2017-03-06  Friday       Juice     9\n",
      "Food     2017-04-06  Friday     Biscuit    10\n",
      "Rent     2017-03-06  Friday      Airtel     8\n",
      "Travel   2017-03-06  Friday        Auto    10\n",
      "\n",
      "Sum of each category:\n",
      "            Cost\n",
      "Category       \n",
      "Basics     3440\n",
      "Drinks      888\n",
      "Food       5428\n",
      "Rent      11695\n",
      "Travel     7423\n",
      "\n",
      "Aggregation of various operations in each category:\n",
      "            Cost                      \n",
      "            sum        mean   max min\n",
      "Category                             \n",
      "Basics     3440  430.000000   886  35\n",
      "Drinks      888   25.371429    90   9\n",
      "Food       5428   88.983607   345  10\n",
      "Rent      11695  835.357143  9500   8\n",
      "Travel     7423  206.194444  1814  10\n",
      "\n",
      "New DataFrame with top values:\n",
      "           Date        Day Category  Expenditure  Cost\n",
      "144 2017-06-29   Thursday   Basics  Supermarket   886\n",
      "134 2017-06-28  Wednesday   Drinks        Shake    90\n",
      "130 2017-06-27    Tuesday     Food       Dinner   345\n",
      "0   2017-03-06   Saturday     Rent       Hostel  9500\n",
      "132 2017-06-28  Wednesday   Travel          Bus  1814\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "url=\"https://raw.githubusercontent.com/jamwine/Data/master/Monthly%20Expenditure.csv\"\n",
    "a=pd.read_csv(url,parse_dates=[\"Date\"])\n",
    "print(\"Dataset:\",type(a),\"\\n\\n\",a.head(3))\n",
    "\n",
    "b=a.groupby(\"Category\") # groupby() is used for grouping dataset by the specified column\n",
    "# length returns the unique columns in groupby() and size returns the number of rows in each category\n",
    "print(\"\\nDataset with groupby():\",type(b),\"having length:\",len(b), \"and size:\\n\",b.size())\n",
    "\n",
    "print(\"\\nFirst rows in each category:\\n\",b.first()) # first() returns the first occurences of rows in each category\n",
    "print(\"\\nLast rows in each category:\\n\",b.last()) # last() returns the last occurences of rows in each category\n",
    "\n",
    "print(\"\\nGrouping of dataset:\\n\",b.groups) # groups parameter returns the dictionary with each category as key and index of each row as value\n",
    "\n",
    "print(\"\\nRetrieving a category 'Basics' from group:\\n\",b.get_group(\"Basics\")) # get_group(category_value) returns all the entries of specified value\n",
    "\n",
    "print(\"\\nMax. in each category:\\n\",b.max()) # returns maximum in each category\n",
    "print(\"\\nMin. in each category:\\n\",b.min()) # returns minimum in each category\n",
    "\n",
    "print(\"\\nSum of each category:\\n\",b.sum())\n",
    "print(\"\\nAggregation of various operations in each category:\\n\",b.agg([\"sum\",\"mean\",\"max\",\"min\"])) # agg() is used to perform operations on the grouped dataset\n",
    "\n",
    "c=pd.DataFrame(columns=a.columns)\n",
    "for category,data in b:\n",
    "    c=c.append(data.nlargest(1,\"Cost\")) # dataframe is created keeping maximum value entries in each category\n",
    "print(\"\\nNew DataFrame with top values:\\n\",c)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Length of a: 154 \n",
      "Length of b: 54 \n",
      "Length of c: 208\n",
      "\n",
      "Sample of dataset:\n",
      "           Date     Day Category Expenditure  Cost\n",
      "51  30-07-2017  Sunday   Drinks         Tea    10\n",
      "52  31-07-2017  Monday   Travel        Auto    10\n",
      "53  31-07-2017  Monday     Food        Roll    70\n",
      "\n",
      "Sample of dataset with ignore_index parameter:\n",
      "            Date     Day Category Expenditure  Cost\n",
      "205  30-07-2017  Sunday   Drinks         Tea    10\n",
      "206  31-07-2017  Monday   Travel        Auto    10\n",
      "207  31-07-2017  Monday     Food        Roll    70\n",
      "\n",
      "Sample of dataset with keys defined for each dataset:\n",
      "            Date       Day Category Expenditure  Cost\n",
      "A 0  03-06-2017  Saturday     Rent      Hostel  9500\n",
      "  1  03-06-2017  Saturday   Travel         Bus   212\n",
      "  2  03-06-2017  Saturday   Travel        Auto    50 \n",
      "             Date     Day Category Expenditure  Cost\n",
      "B 51  30-07-2017  Sunday   Drinks         Tea    10\n",
      "  52  31-07-2017  Monday   Travel        Auto    10\n",
      "  53  31-07-2017  Monday     Food        Roll    70\n",
      "\n",
      "Extracting single data record:\n",
      " Date           06-06-2017\n",
      "Day               Tuesday\n",
      "Category           Drinks\n",
      "Expenditure         Juice\n",
      "Cost                   18\n",
      "Name: (A, 21), dtype: object \n",
      "\n",
      " Date           14-07-2017\n",
      "Day                Friday\n",
      "Category             Food\n",
      "Expenditure        Snacks\n",
      "Cost                   10\n",
      "Name: (B, 28), dtype: object\n",
      "\n",
      "Datasets are appended using append():\n",
      "          Date     Day Category Expenditure  Cost\n",
      "0  03-07-2017  Monday     Rent      Hostel  7500\n",
      "1  03-07-2017  Monday   Travel        Auto    10\n",
      "2  03-07-2017  Monday     Food   Chocolate    10\n",
      "\n",
      "Inner join using merge():\n",
      "        Date_x     Day Category Expenditure  Cost      Date_y\n",
      "0  04-06-2017  Sunday   Drinks         Tea    10  30-07-2017\n",
      "1  05-06-2017  Monday   Travel        Auto    10  03-07-2017\n",
      "2  05-06-2017  Monday   Travel        Auto    10  24-07-2017\n",
      "\n",
      "Inner join with suffixes parameter:\n",
      "        Date_A     Day Category Expenditure  Cost      Date_B\n",
      "0  04-06-2017  Sunday   Drinks         Tea    10  30-07-2017\n",
      "1  05-06-2017  Monday   Travel        Auto    10  03-07-2017\n",
      "2  05-06-2017  Monday   Travel        Auto    10  24-07-2017\n",
      "\n",
      "Outer join using merge():\n",
      "        Date_A       Day Category Expenditure  Cost_A Date_B  Cost_B\n",
      "0  03-06-2017  Saturday     Rent      Hostel  9500.0    NaN     NaN\n",
      "1  03-06-2017  Saturday   Travel         Bus   212.0    NaN     NaN\n",
      "2  10-06-2017  Saturday   Travel         Bus    10.0    NaN     NaN\n",
      "\n",
      "Outer join with indicator:\n",
      "        Date_A       Day Category Expenditure  Cost_A Date_B  Cost_B     _merge\n",
      "0  03-06-2017  Saturday     Rent      Hostel  9500.0    NaN     NaN  left_only\n",
      "1  03-06-2017  Saturday   Travel         Bus   212.0    NaN     NaN  left_only\n",
      "2  10-06-2017  Saturday   Travel         Bus    10.0    NaN     NaN  left_only\n",
      "\n",
      "Summary of outer join:\n",
      " left_only     96\n",
      "both          87\n",
      "right_only    14\n",
      "Name: _merge, dtype: int64\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\users\\shaur\\appdata\\local\\programs\\python\\python37\\lib\\site-packages\\ipykernel_launcher.py:18: FutureWarning: \n",
      ".ix is deprecated. Please use\n",
      ".loc for label based indexing or\n",
      ".iloc for positional indexing\n",
      "\n",
      "See the documentation here:\n",
      "http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#ix-indexer-is-deprecated\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "url1=\"https://raw.githubusercontent.com/jamwine/Data/master/Monthly%20Expenditure.csv\"\n",
    "url2=\"https://raw.githubusercontent.com/jamwine/Data/master/Monthly%20Expenditure%202.csv\"\n",
    "a=pd.read_csv(url1)\n",
    "b=pd.read_csv(url2)\n",
    "c=pd.concat([a,b]) # concat([data1,data2]) joins two data sources\n",
    "print(\"Length of a:\",len(a),\"\\nLength of b:\",len(b),\"\\nLength of c:\",len(c))\n",
    "\n",
    "print(\"\\nSample of dataset:\\n\",c.tail(3))\n",
    "\n",
    "c=pd.concat([a,b],ignore_index=True) # ignore_index parameter is used for indexing data in an efficient manner by concatenating two datasets\n",
    "print(\"\\nSample of dataset with ignore_index parameter:\\n\",c.tail(3))\n",
    "\n",
    "c=pd.concat([a,b], keys=[\"A\",\"B\"]) # index of each dataset is assigned using the keys\n",
    "print(\"\\nSample of dataset with keys defined for each dataset:\\n\",c.head(3),\"\\n\",c.tail(3))\n",
    "\n",
    "print(\"\\nExtracting single data record:\\n\",c.ix[\"A\",21],\"\\n\\n\",c.iloc[182])\n",
    "\n",
    "d=b.append(a,ignore_index=True) # another method to join two datasets\n",
    "print(\"\\nDatasets are appended using append():\\n\",d.head(3))\n",
    "\n",
    "inner=a.merge(b,how=\"inner\",on=[\"Day\",\"Category\",\"Expenditure\",\"Cost\"]) # inner join using merge(),'how' parameter defines type of join,'on' parameter takes multiple columns for joining\n",
    "print(\"\\nInner join using merge():\\n\",inner.head(3))\n",
    "\n",
    "inner=a.merge(b,how=\"inner\",on=[\"Day\",\"Category\",\"Expenditure\",\"Cost\"],suffixes=[\"_A\",\"_B\"]) # 'suffixes' parameter is used to define columns from two different datasets\n",
    "print(\"\\nInner join with suffixes parameter:\\n\",inner.head(3))\n",
    "\n",
    "outer=a.merge(b,how=\"outer\",on=[\"Day\",\"Expenditure\",\"Category\"],suffixes=[\"_A\",\"_B\"])\n",
    "print(\"\\nOuter join using merge():\\n\",outer.head(3))\n",
    "\n",
    "outer_with_indicator=a.merge(b,how=\"outer\",on=[\"Day\",\"Expenditure\",\"Category\"],suffixes=[\"_A\",\"_B\"],indicator=True) # indicator parameter denotes the data from a particular dataset\n",
    "print(\"\\nOuter join with indicator:\\n\",outer_with_indicator.head(3)) # _merge column represents data from a particular dataset\n",
    "print(\"\\nSummary of outer join:\\n\",outer_with_indicator[\"_merge\"].value_counts()) # represents summary of records taken from each dataset in the outer join\n",
    "\n",
    "# similarly, merge() is used for left  & right join.'sort' parameter is used to sort the resulting dataset based on the matched column\n",
    "# if the two datasets have a different column name on which the data is to be joined, we use 'left_on' and 'right_on' parameters\n",
    "# 'left_index' and 'right_index' parameters are used to join datasets when the dataset contains the column as index \n",
    "# in order to attach a new column which is present in different dataset, we use join() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DataFrame:\n",
      "         A        B        C\n",
      "0   69.420  172.825  199.935\n",
      "1  115.040  419.690  177.560\n",
      "2   89.415  297.185  117.710\n",
      "3  122.175  144.215  188.645\n",
      "4  249.690  241.425  183.235\n",
      "\n",
      "75th quartile:\n",
      "A    122.175\n",
      "B    297.185\n",
      "C    188.645\n",
      "Name: 0.75, dtype: float64\n",
      "\n",
      "Sample standard deviation:\n",
      "A     70.576514\n",
      "B    109.673008\n",
      "C     32.217711\n",
      "dtype: float64\n",
      "\n",
      "Population standard deviation:\n",
      "A    63.125553\n",
      "B    98.094520\n",
      "C    28.816397\n",
      "dtype: float64\n",
      "\n",
      "Z-Score:\n",
      "          A         B         C\n",
      "0 -0.846287 -0.749893  0.823088\n",
      "1 -0.199897  1.501026  0.128594\n",
      "2 -0.562978  0.384023 -1.729080\n",
      "3 -0.098801 -1.010759  0.472659\n",
      "4  1.707962 -0.124397  0.304739\n",
      "\n",
      "Z-Score using scipy library:\n",
      "[[-0.84628719 -0.74989281  0.82308764]\n",
      " [-0.19989653  1.50102567  0.12859386]\n",
      " [-0.56297765  0.38402339 -1.72907998]\n",
      " [-0.09880057 -1.01075918  0.47265927]\n",
      " [ 1.70796194 -0.12439706  0.30473921]]\n",
      "\n",
      "InterQuartile Range:\n",
      "A     32.760\n",
      "B    124.360\n",
      "C     11.085\n",
      "dtype: float64\n",
      "\n",
      "Cumulative Sum:\n",
      "         A         B        C\n",
      "0   69.420   172.825  199.935\n",
      "1  184.460   592.515  377.495\n",
      "2  273.875   889.700  495.205\n",
      "3  396.050  1033.915  683.850\n",
      "4  645.740  1275.340  867.085\n",
      "\n",
      "Sum:\n",
      "A     645.740\n",
      "B    1275.340\n",
      "C     867.085\n",
      "dtype: float64\n",
      "\n",
      "Crossover Position:\n",
      "A    4\n",
      "B    1\n",
      "C    2\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "df = pd.DataFrame({'A': [13884, 23008, 17883, 24435, 49938],\n",
    "                   'B': [34565, 83938, 59437, 28843, 48285],\n",
    "                   'C': [39987, 35512, 23542, 37729, 36647]})\n",
    "\n",
    "## Dividing Values by constant\n",
    "df=df/100\n",
    "df=df.div(2)\n",
    "\n",
    "print('DataFrame:\\n{}'.format(df))\n",
    "print('\\n75th quartile:\\n{}'.format(df.quantile(q=.75)))\n",
    "print('\\nSample standard deviation:\\n{}'.format(df.std()))\n",
    "print('\\nPopulation standard deviation:\\n{}'.format(df.std(ddof=0)))\n",
    "\n",
    "def zscore(series):\n",
    "    result = (series - series.mean()) / series.std()\n",
    "    return result\n",
    "\n",
    "# Calculating Z-Score\n",
    "print('\\nZ-Score:\\n{}'.format(df.apply(zscore)))\n",
    "\n",
    "# The same result values as using scipy stats zscore with a dynamic degrees of freedom of 1 \n",
    "from scipy import stats\n",
    "print('\\nZ-Score using scipy library:\\n{}'.format(stats.zscore(df, ddof=1))) # returns numpy ndarray\n",
    "\n",
    "# Calculate inter quartile range with a lambda expression\n",
    "print('\\nInterQuartile Range:\\n{}'.format(df.apply(lambda x: x.quantile(q=.75) - x.quantile(q=.25))))\n",
    "\n",
    "df1=df.cumsum()\n",
    "print('\\nCumulative Sum:\\n{}'.format(df1))\n",
    "print('\\nSum:\\n{}'.format(df.sum()))\n",
    "\n",
    "# Find indices where elements should be inserted to maintain order.\n",
    "# Searching where 400 mark crossed the data i.e. crossover point for cumulative sum\n",
    "print(\"\\nCrossover Position:\\n{}\".format(df1.apply(lambda x:x.searchsorted(400))))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Datetime Functions using Pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Date and Time using datetime(): 1995-06-26 08:05:25 <class 'datetime.datetime'>\n",
      "Date and Time as a string: 05/12/1994 8:15:30 AM <class 'str'>\n",
      "Date and Time using timestamp(): 1995-06-26 08:05:25 1994-05-12 08:15:30 <class 'pandas._libs.tslibs.timestamps.Timestamp'>\n",
      "\n",
      "Dates: [datetime.date(2017, 6, 26), '2015/11/30', '2013-1-6', 'Aug 15th, 2018', '3rd July 1947', '2015'] <class 'list'>\n",
      "\n",
      "Date and Time using to_datetime(): DatetimeIndex(['2017-06-26', '2015-11-30', '2013-01-06', '2018-08-15',\n",
      "               '1947-07-03', '2015-01-01'],\n",
      "              dtype='datetime64[ns]', freq=None)\n",
      "\n",
      "Date and Time using DatetimeIndex(): DatetimeIndex(['2017-06-26', '2015-11-30', '2013-01-06', '2018-08-15',\n",
      "               '1947-07-03', '2015-01-01'],\n",
      "              dtype='datetime64[ns]', freq=None) <class 'pandas.core.indexes.datetimes.DatetimeIndex'>\n",
      "\n",
      "Extracting element from the list: 2015-11-30 00:00:00\n",
      "\n",
      "Series:\n",
      " 0         2017-06-26\n",
      "1         2015/11/30\n",
      "2           2013-1-6\n",
      "3     Aug 15th, 2018\n",
      "4      3rd July 1947\n",
      "5               2015\n",
      "6    31st April 2014\n",
      "7        Sample_text\n",
      "8         1345673680\n",
      "dtype: object\n",
      "\n",
      "Series converted into dates:\n",
      " 0   2017-06-26\n",
      "1   2015-11-30\n",
      "2   2013-01-06\n",
      "3   2018-08-15\n",
      "4   1947-07-03\n",
      "5   2015-01-01\n",
      "6          NaT\n",
      "7          NaT\n",
      "8          NaT\n",
      "dtype: datetime64[ns]\n",
      "\n",
      "Weekday Name:\n",
      " 0       Monday\n",
      "1       Monday\n",
      "2       Sunday\n",
      "3    Wednesday\n",
      "4     Thursday\n",
      "5     Thursday\n",
      "6          NaN\n",
      "7          NaN\n",
      "8          NaN\n",
      "dtype: object\n",
      "\n",
      "Month End:\n",
      " 0    False\n",
      "1     True\n",
      "2    False\n",
      "3    False\n",
      "4    False\n",
      "5    False\n",
      "6    False\n",
      "7    False\n",
      "8    False\n",
      "dtype: bool\n",
      "\n",
      "Time in seconds since 1970-01-01 is converted into timestamp: 2016-06-11 19:34:40\n",
      "\n",
      "Dates using date_range():\n",
      " DatetimeIndex(['2018-10-26', '2018-10-27', '2018-10-28', '2018-10-29',\n",
      "               '2018-10-30', '2018-10-31', '2018-11-01', '2018-11-02',\n",
      "               '2018-11-03', '2018-11-04', '2018-11-05', '2018-11-06'],\n",
      "              dtype='datetime64[ns]', freq='D')\n",
      "DatetimeIndex(['2018-10-26', '2018-10-28', '2018-10-30', '2018-11-01',\n",
      "               '2018-11-03', '2018-11-05'],\n",
      "              dtype='datetime64[ns]', freq='2D')\n",
      "DatetimeIndex(['2018-10-26', '2018-10-29', '2018-10-30', '2018-10-31',\n",
      "               '2018-11-01', '2018-11-02', '2018-11-05', '2018-11-06'],\n",
      "              dtype='datetime64[ns]', freq='B')\n",
      "DatetimeIndex(['2018-10-28', '2018-11-04', '2018-11-11'], dtype='datetime64[ns]', freq='W-SUN')\n",
      "DatetimeIndex(['2018-10-31'], dtype='datetime64[ns]', freq='M')\n",
      "DatetimeIndex(['2018-10-26', '2018-10-27', '2018-10-28', '2018-10-29',\n",
      "               '2018-10-30', '2018-10-31', '2018-11-01', '2018-11-02'],\n",
      "              dtype='datetime64[ns]', freq='D')\n",
      "DatetimeIndex(['2018-10-26 00:00:00', '2018-10-26 05:00:00',\n",
      "               '2018-10-26 10:00:00', '2018-10-26 15:00:00'],\n",
      "              dtype='datetime64[ns]', freq='5H')\n",
      "DatetimeIndex(['2018-10-20', '2018-10-22', '2018-10-24', '2018-10-26'], dtype='datetime64[ns]', freq='2D')\n"
     ]
    }
   ],
   "source": [
    "import datetime as dt\n",
    "import pandas as pd\n",
    "\n",
    "a=dt.datetime(1995,6,26,8,5,25)\n",
    "print(\"Date and Time using datetime():\",a, type(a))\n",
    "b=\"05/12/1994 8:15:30 AM\"\n",
    "print(\"Date and Time as a string:\",b, type(b))\n",
    "print(\"Date and Time using timestamp():\",pd.Timestamp(a), pd.Timestamp(b), type(pd.Timestamp(b))) # timestamp objects\n",
    "\n",
    "dates=[dt.date(2017,6,26),\"2015/11/30\",\"2013-1-6\",\"Aug 15th, 2018\",\"3rd July 1947\",\"2015\"]\n",
    "print(\"\\nDates:\",dates, type(dates))\n",
    "print(\"\\nDate and Time using to_datetime():\",pd.to_datetime(dates)) # to_datetime() is used to convert strings of various formats into datetime\n",
    "\n",
    "x=pd.DatetimeIndex(dates) # datetimeindex object contains list of timestamp objects\n",
    "print(\"\\nDate and Time using DatetimeIndex():\",x, type(x)) \n",
    "print(\"\\nExtracting element from the list:\",x[1])\n",
    "\n",
    "dates=[dt.date(2017,6,26),\"2015/11/30\",\"2013-1-6\",\"Aug 15th, 2018\",\"3rd July 1947\",\"2015\",\"31st April 2014\",\"Sample_text\",\"1345673680\"]\n",
    "y=pd.Series(dates)\n",
    "print(\"\\nSeries:\\n\",y)\n",
    "\n",
    "z=pd.to_datetime(y,errors='coerce') # errors='coerce' is used to return NaT for invalid dates without throwing any error\n",
    "print(\"\\nSeries converted into dates:\\n\",z)\n",
    "\n",
    "print(\"\\nWeekday Name:\\n\",z.dt.weekday_name) # dt.weekday_name parameter returns day of that date\n",
    "\n",
    "print(\"\\nMonth End:\\n\",z.dt.is_month_end) # Similarly, dt.is_quarter_end parameter returns boolean value of that date\n",
    "\n",
    "print(\"\\nTime in seconds since 1970-01-01 is converted into timestamp:\",pd.to_datetime(\"1465673680\",unit='s')) # unit='s' represents seconds\n",
    "\n",
    "print(\"\\nDates using date_range():\\n\",pd.date_range(start=\"26th Oct 2018\",end=\"6th Nov 2018\")) # date_range() is used to return a range of dates between two intervals\n",
    "print(pd.date_range(start=\"26th Oct 2018\",end=\"6th Nov 2018\",freq='2D')) # frequency means 2 days\n",
    "print(pd.date_range(start=\"26th Oct 2018\",end=\"6th Nov 2018\",freq='B')) # frequency means business days\n",
    "print(pd.date_range(start=\"26th Oct 2018\",end=\"13th Nov 2018\",freq='W')) # frequency means week (displaying sunday by default)\n",
    "print(pd.date_range(start=\"26th Oct 2018\",end=\"13th Nov 2018\",freq='M')) # frequency means Month's end\n",
    "print(pd.date_range(start=\"26th Oct 2018\",periods=8,freq='D')) # periods parameter denotes number of dates\n",
    "print(pd.date_range(start=\"26th Oct 2018\",periods=4,freq='5H')) # frequency means every 5 hours, will start from the start date\n",
    "print(pd.date_range(end=\"26th Oct 2018\",periods=4,freq='2D')) # frequency means every 2 days, will end at the end date"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Options and Settings for Pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "pd.get_option(\"max_columns\"): 8\n",
      "pd.get_option(\"max_rows\"): 60\n",
      "\n",
      "Printing Information about option:\n",
      "display.max_rows : int\n",
      "    If max_rows is exceeded, switch to truncate view. Depending on\n",
      "    `large_repr`, objects are either centrally truncated or printed as\n",
      "    a summary view. 'None' value means unlimited.\n",
      "\n",
      "    In case python/IPython is running in a terminal and `large_repr`\n",
      "    equals 'truncate' this can be set to 0 and pandas will auto-detect\n",
      "    the height of the terminal and print a truncated object which fits\n",
      "    the screen height. The IPython notebook, IPython qtconsole, or\n",
      "    IDLE do not run in a terminal and hence it is not possible to do\n",
      "    correct auto-detection.\n",
      "    [default: 60] [currently: 10]\n"
     ]
    }
   ],
   "source": [
    "# pd.get_option('Option Name') fetches the value of option\n",
    "# pd.set_option('Option Name') sets the value of option\n",
    "\n",
    "print('pd.get_option(\"max_columns\"):',pd.get_option(\"max_columns\"))\n",
    "print('pd.get_option(\"max_rows\"):',pd.get_option(\"max_rows\"))\n",
    "\n",
    "pd.reset_option('max_rows') # resets the option value to default\n",
    "pd.set_option(\"max_rows\",10) # Setting new max_rows\n",
    "print('\\nPrinting Information about option:')\n",
    "pd.describe_option('max_rows')\n",
    "pd.set_option(\"precision\",2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reading Other Files\n",
    "\n",
    "> Different delimiters - tab-separated file (.tsv)\n",
    "```python\n",
    "data=pd.read_csv(filepath, sep='\\t')\n",
    "```\n",
    ">\n",
    "\n",
    "> Different delimiters - space-separated file\n",
    "```python\n",
    "data=pd.read_csv(filepath, delim_whitespace=True)\n",
    "```\n",
    ">\n",
    "\n",
    "> Specify column names\n",
    "```python\n",
    "data=pd.read_csv(filepath, names=['Name1','Name2'])\n",
    "```\n",
    ">\n",
    "\n",
    "> Custom missing values\n",
    "```python\n",
    "data=pd.read_csv(filepath, na_values=['NA',99])\n",
    "```\n",
    ">\n",
    "\n",
    "> Read JSON files as dataframe\n",
    "```python\n",
    "data=pd.read_json(filepath)\n",
    "```\n",
    ">\n",
    "\n",
    "> Write dataframe file to JSON\n",
    "```python\n",
    "data.to_json('outfile.json')\n",
    "```"
   ]
  }
 ],
 "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}