Ditching Excel for Python!.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <center>Ditching Excel for Python</center>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After spending almost a decade with my first love Excel, its time to move on and search for a better half who in thick and thin of my daily tasks is with me and is much better and faster and who can give me a cutting edge in the challenging technological times where new technology is getting ditched by something new at a very rapid pace.\n",
    "The idea is to replicate almost all excel functionalities in Python, be it using a simple filter or a complex task of creating an array of data from the rows and crunching them to get fancy results"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The approach followed here is to start from simple tasks and move to complex computational tasks.\n",
    "I've tried and designed it such a way that this can be used a universal notebook and you just just have to change the input file and you can get the same result.\n",
    "However I will encourage you to please replicate the steps yourself for your better understanding."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The inspiration to create something like this came from the non-availablity of a free tutorial which literally gives all. I heavily read and follow Python documentation and you will find a lot of inspiration from that site."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "_Our Input and Ouput is both an Excel file :)_"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<pre>------------------------------------------------------------------------------------------</pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Importing Excel Files into a Pandas DataFrame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Initial step is to import excel files into dtaframe so we can perform all our tasks on it.\n",
    "<br>I will be demonstrating the __read_excel__ method of Pandas which supports __xls__ and __xlsx__ file extensions.\n",
    "<br>__read_csv__ is same as using read_excel, we wont go in depth but I will share an example."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Though __read_excel__ method includes million arguments but I will make you familiarise with the most common ones that will come very handy in day to day operations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I'll be using the Iris sample dataset which is freely available online for educational purpose.\n",
    "<br>Please follow the below link to download the dataset and ensure to save it in the same folder where you are saving your python file"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "https://archive.ics.uci.edu/ml/datasets/iris"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The first step is to import necessary libraries in Python"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can import the spreadsheet data into Python using the following code:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "pandas.read_excel(io, sheet_name=0, header=0, names=None, index_col=None, parse_cols=None, usecols=None, squeeze=False, dtype=None, engine=None, converters=None, true_values=None, false_values=None, skiprows=None, nrows=None, na_values=None, keep_default_na=True, verbose=False, parse_dates=False, date_parser=None, thousands=None, comment=None, skip_footer=0, skipfooter=0, convert_float=True, mangle_dupe_cols=True, **kwds)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since there's a plethora of arguments available, lets look at the most used one's."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Important Pandas read_excel Options"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "|\tArgument\t|\tDescription\t|\n",
    "|\t\t-|\t\t|\n",
    "|\tio\t|\tA string containing the pathname of the given Excel file.\t|\n",
    "|\tsheet_name\t|\tThe Excel sheet name, or sheet number, of the data you want to import. The sheet number can be an integer where 0 is the first sheet, 1 is the second, etc. If a list of sheet names/numbers are given, then the output will be a dictionary of DataFrames. The default is to read all the sheets and output a dictionary of DataFrames.\t|\n",
    "|\theader\t|\tRow number to use for the list of column labels. The default is 0, indicating that the first row is assumed to contain the column labels. If the data does not have a row of column labels, None should be used.\t|\n",
    "|\tnames\t|\tA separate Python list input of column names. This option is None by default. This option is the equivalent of assigning a list of column names to the columns attribute of the output DataFrame.\t|\n",
    "|\tindex_col\t|\tSpecifies which column should be used for row indices. The default option is None, meaning that all columns are included in the data, and a range of numbers is used as the row indices.\t|\n",
    "|\tusecols\t|\tAn integer, list of integers, or string that specifies the columns to be imported into the DataFrame. The default is to import all columns. If a string is given, then Pandas uses the standard Excel format to select columns (e.g. \"A:C,F,G\" will import columns A, B, C, F, and G).\t|\n",
    "|\tskiprows\t|\tThe number of rows to skip at the top of the Excel sheet. Default is 0. This option is useful for skipping rows in Excel that contain explanatory information about the data below it.\t|\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we are using the path for our local file by default its separated by \"\\\" however python accepts \"/\", \n",
    "so make to change the slashes or simply add the file in the same folder where your python file is.\n",
    "Should you require detailed explanation on the above, refer to the below medium article.\n",
    "https://medium.com/@ageitgey/python-3-quick-tip-the-easy-way-to-deal-with-file-paths-on-windows-mac-and-linux-11a072b58d5f"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can use Python to scan files in a directory and pick out the ones we want."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "wkbks = glob(os.path.join(os.pardir, 'input', 'xlsx_files_all', 'Ir*.xls'))\n",
    "sorted(wkbks)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "filename = 'Iris.xlsx'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_excel(filename)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import a specifc sheet"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By default, the first sheet in the file is imported to the dataframe as it is.\n",
    "<br>Using the sheet_name argument we can explicitly mention the sheet that we want to import. Defuat value is 0 i.e. teh first sheet in the file.\n",
    "    <br>We can either mention the name of the sheet(s) or pass an integer value to refer to the index of the sheet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.read_excel(filename,sheet_name='Sheet2')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(df1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Using a column from the sheet as an Index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Unless explicitly mentioned, an index column is added to the dataframe which by default starts from a 0.\n",
    "<br>Using the index_col argumement we can manipulate the index column in our dataframe, if we set the value 0 from none, it will use the first column as our index."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_excel(filename,sheet_name='Sheet1', index_col=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Skip rows and columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The default read_excel parameters assumes that the first row is a list of column names, which is incorporated automatically as column labels within the DataFrame.\n",
    "<br>Using the arguments like skiprows and header we can manipulate the behaviour of the imported dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_excel(filename, sheet_name='Sheet1', header=None, skiprows=1, index_col=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import a specifc column(s)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using the usecols argument we can specify if we have import a specific column in our dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_excel(filename, sheet_name='Sheet1', header=None, skiprows=1, usecols='B,D')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_excel(filename)\n",
    "#Importing the file again to the dataframe in the original shape to use it for further analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "_Its not the end of the features available however its a start and you can play around with them as per your requirements_"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<pre>------------------------------------------------------------------------------------------</pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Lets have a look at the data from 10,000 feet"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As now we have our dataframe, lets look at the data from multiple angles just to get a hang of it/\n",
    "<br>Pandas have plenty of functions available that we can use. We'll use some of them to have a glimpse of our dataset."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## \"Head\" to \"Tail\": \n",
    "To view the first or last __five__ rows.\n",
    "<br>_Default is five, however the argument allows us to use a specific number_"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## View specific column's data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df['SepalLength'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Getting the name of all columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Info Method\n",
    "Gives a summary of Dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Shape Method\n",
    "Returns the dimensions of Dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print('Total rows in Dataframe is: ',  df.shape[0])\n",
    "print('Total columns in Dataframe is: ',  df.shape[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Look at the datatypes in Dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<pre>------------------------------------------------------------------------------------------</pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Slice and Dice i.e. Excel filters"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Descriptive reporting is all about data subsets and aggregations, the moment we are to understand our data a little bit we start using filters to look at the smaller sets of data or view a particular column maybe to have a better understanding.\n",
    "<br>Python offers a lot of different methods to slice and dice the dataframes, we'll play around with a couple of them to have an understanding of how it works"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## View a specific column"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There exists three main methods to select columns:\n",
    "\n",
    "* Use dot notation: e.g. data.column_name\n",
    "* Use square braces and the name of the column:, e.g. data['column_name']\n",
    "* Use numeric indexing and the iloc selector data.loc[:, 'column_number']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Name'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.iloc[:,[4]].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[:,['Name']].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## View multiple columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[['Name', 'PetalLength']].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#Pass a variable as a list\n",
    "SpecificColumnList = ['Name', 'PetalLength']\n",
    "df[SpecificColumnList].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## View specific row's data\n",
    "The method used here is slicing using the loc function, where we can specify the start and end row separated by colon\n",
    "<br>Remember, __index starts from a 0 and not 1__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[20:30] "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Slice rows and columns together"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[20:30, ['Name']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Filter data in a column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df['Name'] == 'Iris-versicolor'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Filter multiple values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df['Name'].isin(['Iris-versicolor', 'Iris-virginica'])]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Filter multiple values using a list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Filter_Value = ['Iris-versicolor', 'Iris-virginica']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df['Name'].isin(Filter_Value)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Filter values NOT in list or not equal to in Excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[~df['Name'].isin(Filter_Value)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Filter usinng using multiple conditions in multiple columns\n",
    "__The input should always be a list__\n",
    "<br>We can use this method to replicate advanced filter function in excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "width = [2]\n",
    "Flower_Name = ['Iris-setosa']\n",
    "df[~df['Name'].isin(Flower_Name) & df['PetalWidth'].isin(width)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Filter using numeric conditions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df['SepalLength'] == 5.1].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df['SepalLength'] > 5.1].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Replicate the custom filter in Excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df['Name'].map(lambda x: x.endswith('sa'))]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Combine two filters to get the result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df['Name'].map(lambda x: x.endswith('sa')) & (df['SepalLength'] > 5.1)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Contains function in Excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df['Name'].str.contains('set')]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Get the unique values from dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['SepalLength'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we want to view the entire dataframe with the unique values, we can use the drop_duplicates method"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop_duplicates(subset=['Name'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop_duplicates(subset=['Name']).iloc[:,[3,4]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Sort Values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sort data by a certain column, by default the sorting is ascending"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.sort_values(by = ['SepalLength'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.sort_values(by = ['SepalLength'], ascending = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<pre>------------------------------------------------------------------------------------------</pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Statistical summary of data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## __DataFrame Describe method:__ \n",
    "_Generate descriptive statistics that summarize the central tendency, dispersion and shape of a dataset’s distribution, excluding NaN values._"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Summary stats of character columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.describe(include = ['object'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.describe(include = 'all')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<pre>------------------------------------------------------------------------------------------</pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <centre>Data Aggregation</centre>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Counting the unique values of a particular column. \n",
    "_Resulting output is a Series. You can refer it as a Single column Pivot Table_"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.value_counts(df['Name'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Count cells\n",
    "Count non-NA cells for each column or row."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df.count(axis=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Sum\n",
    "Summarising the data to get a snapshot of either by rows or columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.sum(axis = 0) # 0 for column wise total"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Its replicates the method of adding a total column against each row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.sum(axis =1) # row wise sum"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Add a total column to the existing dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Total'] = df.sum(axis =1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Sum of specific columns, use the loc methos and pass the column names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Total_loc']=df.loc[:,['SepalLength', 'SepalWidth']].sum(axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Or, we can use the below method"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Total_DFSum']= df['SepalLength'] + df['SepalWidth']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Don't like the new column, delete it using drop method"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(['Total_DFSum'], axis = 1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Adding sum-total beneath each column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Sum_Total = df[['SepalLength', 'SepalWidth', 'Total']].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Sum_Total"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "T_Sum = pd.DataFrame(data=Sum_Total).T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "T_Sum"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "T_Sum = T_Sum.reindex(columns=df.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "T_Sum"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Row_Total = df.append(T_Sum,ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Row_Total"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A lot has been done above, the approach that we are using is:\n",
    "* Sum_Total: Do the sum of columns\n",
    "* T_Sum: Convert the series output to dataframe and transpose\n",
    "* Re-index to add missing columns\n",
    "* Row_Total: append T_Sum to existing dataframe"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Sum based on criteria i.e. Sumif in Excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df['Name'] == 'Iris-versicolor'].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Sumifs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df['Name'].map(lambda x: x.endswith('sa')) & (df['SepalLength'] > 5.1)].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Averageif"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df['Name'] == 'Iris-versicolor'].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Averageifs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df['Name'].map(lambda x: x.endswith('sa')) & (df['SepalLength'] > 5.1)].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Max"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df['Name'] == 'Iris-versicolor'].max()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Min"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df['Name'] == 'Iris-versicolor'].min()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Groupby i.e. Subtotals in Excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[['Name','SepalLength']].groupby('Name').sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "GroupBy = df.groupby('Name').sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Group_By.append(pd.DataFrame(df[['SepalLength','SepalWidth','PetalLength','PetalWidth']].sum()).T)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<pre>------------------------------------------------------------------------------------------</pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pivot Tables in Dataframes i.e. Pivot Tables in Excel"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Who doesn'l love a Pivot Table in Excel, its one the best ways to analyse your data, have a quick overview of the information, helps you slice and dice the data with a super easy interface, helps you plots graphs basis on the data, add calculative columns etc.\n",
    "<br>No, we wont have an interface to work, we'll have to explicitly write the code to get the output, No, it wont generate charts for you, but I don't think we can complete a tutorial without learning about the Pivot tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.pivot_table(df, index= 'Name')#Same as Groupby"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.pivot_table(df, values='SepalWidth', index= 'SepalLength',columns='Name', aggfunc = np.sum)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A simple Pivot table showing us the sum of SepalWidth in values, SepalLength in Row Column and Name in Column Labels"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lets see if we can complicate it a bit."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.pivot_table(df, values='SepalWidth', index= 'SepalLength',columns='Name', aggfunc = np.sum, fill_value=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Blanks are now replaced with 0's by using the fill_value argument"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.pivot_table(df, values=['SepalWidth', 'PetalWidth'], index= 'SepalLength',columns='Name', aggfunc = np.sum, fill_value=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.pivot_table(df, values=['SepalWidth', 'PetalWidth'], index= ['SepalLength', 'PetalLength'],columns='Name', aggfunc = np.sum, fill_value=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.pivot_table(df, values=['SepalWidth', 'PetalWidth'], index= 'SepalLength',columns='Name', \n",
    "               aggfunc = {'SepalWidth': np.sum, 'PetalWidth': np.mean}, fill_value=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can have individual calculations on values using dictionary method and can also have multiple calculations on values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.pivot_table(df, values=['SepalWidth', 'PetalWidth'], index= 'SepalLength',columns='Name', \n",
    "               aggfunc = {'SepalWidth': np.sum, 'PetalWidth': np.mean}, fill_value=0, margins=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we use margins argument, we can have total row added"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<pre>------------------------------------------------------------------------------------------</pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Vlookup"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What a magical formula is vlookup in Excel, I think its the first thing that everyone wants to learn before learning how to even add. Looks fascinating when someone is applying vlookup, looks like magic when we get the output. Makes life easy. I can with very much confidence can say its the backbone of every data wrangling action performed on the spreadsheet.\n",
    "<br>\n",
    "<br>__Unfortunately__ we dont have a vlookup function in Pandas!\n",
    "<br>\n",
    "<br>Since we dont have a \"Vlookup\" function in Pandas, Merge is used as an alternate which is same as SQL. There are a total of four merge options available:\n",
    "* ‘left’ — Use the shared column from the left dataframe and match to right dataframe. Fill in any N/A as NaN\n",
    "\n",
    "* ‘right’ — Use the shared column from the right dataframe and match to left dataframe. Fill in any N/A as NaN\n",
    "\n",
    "* ‘inner’ — Only show data where the two shared columns overlap. Default method.\n",
    "\n",
    "* ‘outer’ — Return all records when there is a match in either left or right dataframe.\n",
    "<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.read_excel(filename)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "lookup = df.merge(df,on='Name')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "lookup"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The above might not be the best example to suppport the concept, however the working is the same."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<pre>------------------------------------------------------------------------------------------</pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I am hoping this tutorial made some sense, though I agree this could have been more elaborative which I will be pursuing soon.\n",
    "<br>__Watch out the space for more of it.__"
   ]
  }
 ],
 "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}