001_Python_Pandas_DataFrame.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<small><small><i>\n",
    "All the IPython Notebooks in this lecture series by Dr. Milan Parmar are available @ **[GitHub](https://github.com/milaan9/10_Python_Pandas_Module)**\n",
    "</i></small></small>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Python Pandas\n",
    "\n",
    "**[Pandas](https://pandas.pydata.org/)** is an open-source, BSD-licensed Python library. Python package providing fast, flexible, handy, and expressive data structures tool designed to make working with 'relationa' or 'labeled' data both easy and intuitive. It aims to be the fundamental high-level building block for doing practical, real world complex data analysis in Python.\n",
    "\n",
    "pandas is well suited for many different kinds of data:\n",
    "\n",
    "* Tabular data with heterogeneously-typed columns, as in an SQL table or Excel spreadsheet\n",
    "* Ordered and unordered (not necessarily fixed-frequency) time series data.\n",
    "* Arbitrary matrix data with row and column labels\n",
    "* Any other form of observational / statistical data sets."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Python DataFrame\n",
    "\n",
    "In this lesson, you will learn pandas DataFrame. It covers the basics of DataFrame, its attributes, functions, and how to use DataFrame for Data Analysis.\n",
    "\n",
    "DataFrame is the most widely used data structure in Python pandas. You can imagine it as a table in a database or a spreadsheet.\n",
    "\n",
    "Imagine you have an automobile showroom, and you want to analyze cars’ data to make business strategies. For example, you need to check how many vehicles you have in your showroom of type sedan, or the cars that give good mileage. For such analysis pandas DataFrame is used."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## What is DataFrame in Pandas\n",
    "\n",
    "Dataframe is a tabular(rows, columns) representation of data. It is a two-dimensional data structure with potentially heterogeneous data.\n",
    "\n",
    "Dataframe is a size-mutable structure that means data can be added or deleted from it, unlike data series, which does not allow operations that change its size.\n",
    "\n",
    "<div>\n",
    "<img src=\"img/dataframe.png\" width=\"600\"/>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## DataFrame creation\n",
    "\n",
    "Data is available in various forms and types like CSV, SQL table, JSON, or Python structures like list, dict etc. We need to convert all such different data formats into a DataFrame so that we can use pandas libraries to analyze such data efficiently.\n",
    "\n",
    "To create DataFrame, we can use either the DataFrame constructor or pandas built-in functions. Below are some examples."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### DataFrame constructor\n",
    "\n",
    "```python\n",
    "pandas.DataFrame(data=None, index=None, columns=None, dtype=None, copy=False)\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Parameters:\n",
    "\n",
    "* **`data`**: It takes input **`dict`**, **`list`**, **`set`**, **`ndarray`**, **`iterable`**, or DataFrame. If the input is not provided, then it creates an empty DataFrame. The resultant column order follows the insertion order.\n",
    "\n",
    "\n",
    "* **`index`**: (Optional) It takes the list of row index for the DataFrame. The default value is a range of integers 0, 1,…n.\n",
    "\n",
    "\n",
    "* **`columns`** : (Optional) It takes the list of columns for the DataFrame. The default value is a range of integers 0, 1,…n.\n",
    "\n",
    "\n",
    "* **`dtype`**: (Optional) By default, It infers the data type from the data, but this option applies any specific data type to the whole DataFrame.\n",
    "\n",
    "\n",
    "* **`copy`**: (Optional) Copy data from inputs. Boolean, Default False. Only affects DataFrame or 2D array-like inputs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Refer the following articles for more details:\n",
    "\n",
    "* **[Create pandas DataFrame from Python Dictionary](https://github.com/milaan9/10_Python_Pandas_Module/blob/main/001_Python_Pandas_Methods/001_Python_Pandas_DataFrame_from_Dictionary.ipynb)**\n",
    "* **[Create pandas DataFrame from Python List](https://github.com/milaan9/10_Python_Pandas_Module/blob/main/001_Python_Pandas_Methods/002_Python_Pandas_DataFrame_from_List.ipynb)**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dataframe from dict\n",
    "\n",
    "When we have data in **`dict`** or any default data structures in Python, we can convert it into DataFrame using the DataFrame constructor.\n",
    "\n",
    "To construct a DataFrame from a **`dict`** object, we can pass it to the DataFrame constructor **`pd.DataFrame(dict)`**. It creates DataFrame using, where **`dict`** keys will be column labels, and **`dict`** values will be the columns’ data. We can also use **`DataFrame.from_dict()`** function to **[Create DataFrame from dict](https://github.com/milaan9/10_Python_Pandas_Module/blob/main/001_Python_Pandas_Methods/001_Python_Pandas_DataFrame_from_Dictionary.ipynb)**.\n",
    "\n",
    "**Key and Imports:**\n",
    "\n",
    "| Operator | Description |\n",
    "|:----: |:---- |\n",
    "| **`df`** | **pandas DataFrame object** | \n",
    "| **`s`**  | **pandas Series object** |"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-06T16:31:40.726150Z",
     "start_time": "2021-07-06T16:31:40.693928Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Name': ['Joe', 'Nat'], 'Age': [20, 21], 'Marks': [85.1, 77.8]}"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Example: \n",
    "\n",
    "student_dict = {'Name':['Joe','Nat'], 'Age':[20,21], 'Marks':[85.10, 77.80]}\n",
    "student_dict"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**'Name'**, **'Age'** and **'Marks'** are the keys in the **`dict`** when you convert they will become the column labels of the DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-06T16:31:42.199775Z",
     "start_time": "2021-07-06T16:31:40.732987Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'Name': ['Joe', 'Nat'], 'Age': [20, 21], 'Marks': [85.1, 77.8]}\n",
      "  Name  Age  Marks\n",
      "0  Joe   20   85.1\n",
      "1  Nat   21   77.8\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Python dict object\n",
    "student_dict = {'Name': ['Joe', 'Nat'], 'Age': [20, 21], 'Marks': [85.10, 77.80]}\n",
    "print(student_dict)\n",
    "\n",
    "# Create DataFrame from dict\n",
    "student_df = pd.DataFrame(student_dict)\n",
    "print(student_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-06T16:31:42.231025Z",
     "start_time": "2021-07-06T16:31:42.203683Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    X   Y   Z\n",
      "0  78  84  86\n",
      "1  85  94  97\n",
      "2  96  89  96\n",
      "3  80  83  72\n",
      "4  86  86  83\n"
     ]
    }
   ],
   "source": [
    "# Example\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "# We pass a dict of {column name: column values}\n",
    "df = pd.DataFrame({'X':[78,85,96,80,86], 'Y':[84,94,89,83,86],'Z':[86,97,96,72,83]});\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-06T16:31:42.417065Z",
     "start_time": "2021-07-06T16:31:42.233957Z"
    },
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>1</td>\n",
       "      <td>True</td>\n",
       "      <td>0.496714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>2</td>\n",
       "      <td>True</td>\n",
       "      <td>-0.138264</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>3</td>\n",
       "      <td>False</td>\n",
       "      <td>0.647689</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   A      B         C\n",
       "a  1   True  0.496714\n",
       "b  2   True -0.138264\n",
       "c  3  False  0.647689"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Example\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "df = pd.DataFrame({'A': [1, 2, 3], 'B': [True, True, False],\n",
    "                   'C': [0.496714, -0.138264, 0.647689]},\n",
    "                  index=['a', 'b', 'c'])  # also this weird index thing\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Indexing\n",
    "\n",
    "Our first improvement over numpy arrays is labeled indexing. We can select subsets by column, row, or both. Column selection uses the regular python **`__getitem__`** machinery. Pass in a single column label **`'A'`** or a list of labels **`['A', 'C']`** to select subsets of the original **`DataFrame`**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-06T16:31:42.540108Z",
     "start_time": "2021-07-06T16:31:42.424873Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a    1\n",
       "b    2\n",
       "c    3\n",
       "Name: A, dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Single column, reduces to a Series\n",
    "df['A']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-06T16:31:42.679754Z",
     "start_time": "2021-07-06T16:31:42.544013Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>1</td>\n",
       "      <td>0.496714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>2</td>\n",
       "      <td>-0.138264</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>3</td>\n",
       "      <td>0.647689</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   A         C\n",
       "a  1  0.496714\n",
       "b  2 -0.138264\n",
       "c  3  0.647689"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cols = ['A', 'C']\n",
    "df[cols]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For row-wise selection, use the special **`.loc`** accessor."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-06T16:31:42.803777Z",
     "start_time": "2021-07-06T16:31:42.686591Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>1</td>\n",
       "      <td>True</td>\n",
       "      <td>0.496714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>2</td>\n",
       "      <td>True</td>\n",
       "      <td>-0.138264</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   A     B         C\n",
       "a  1  True  0.496714\n",
       "b  2  True -0.138264"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[['a', 'b']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can use ranges to select rows or columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-06T16:31:42.988833Z",
     "start_time": "2021-07-06T16:31:42.810616Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>1</td>\n",
       "      <td>True</td>\n",
       "      <td>0.496714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>2</td>\n",
       "      <td>True</td>\n",
       "      <td>-0.138264</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   A     B         C\n",
       "a  1  True  0.496714\n",
       "b  2  True -0.138264"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc['a':'b']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that the slice is *inclusive* on both sides,  unlike your typical slicing of a list. Sometimes, you'd rather slice by *position* instead of label. **`.iloc`** has you covered:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-06T16:31:43.127996Z",
     "start_time": "2021-07-06T16:31:42.997624Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>1</td>\n",
       "      <td>True</td>\n",
       "      <td>0.496714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>2</td>\n",
       "      <td>True</td>\n",
       "      <td>-0.138264</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   A     B         C\n",
       "a  1  True  0.496714\n",
       "b  2  True -0.138264"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.iloc[[0, 1]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-06T16:31:43.268131Z",
     "start_time": "2021-07-06T16:31:43.131902Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>1</td>\n",
       "      <td>True</td>\n",
       "      <td>0.496714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>2</td>\n",
       "      <td>True</td>\n",
       "      <td>-0.138264</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   A     B         C\n",
       "a  1  True  0.496714\n",
       "b  2  True -0.138264"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.iloc[:2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This follows the usual python slicing rules: closed on the left, open on the right.\n",
    "\n",
    "As I mentioned, you can slice both rows and columns. Use **`.loc`** for label or **`.iloc`** for position indexing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-06T16:31:43.407777Z",
     "start_time": "2021-07-06T16:31:43.275943Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc['a', 'B']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pandas, like NumPy, will reduce dimensions when possible. Select a single column and you get back `Series` (see below). Select a single row and single column, you get a scalar.\n",
    "\n",
    "You can get pretty fancy:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-06T16:31:43.532291Z",
     "start_time": "2021-07-06T16:31:43.411686Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>1</td>\n",
       "      <td>0.496714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>2</td>\n",
       "      <td>-0.138264</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   A         C\n",
       "a  1  0.496714\n",
       "b  2 -0.138264"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc['a':'b', ['A', 'C']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Summary\n",
    "\n",
    "- Use **`[]`** for selecting columns\n",
    "- Use **`.loc[row_lables, column_labels]`** for label-based indexing\n",
    "- Use **`.iloc[row_positions, column_positions]`** for positional index\n",
    "\n",
    "I've left out boolean and hierarchical indexing, which we'll see later."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Series\n",
    "\n",
    "You've already seen some **Series** up above. It's the 1-dimensional analog of the DataFrame. Each column in a **DataFrame** is in some sense a **Series**. You can select a **Series** from a DataFrame in a few ways:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-06T16:31:43.672914Z",
     "start_time": "2021-07-06T16:31:43.539127Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a    1\n",
       "b    2\n",
       "c    3\n",
       "Name: A, dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# __getitem__ like before\n",
    "df['A']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-06T16:31:43.800841Z",
     "start_time": "2021-07-06T16:31:43.680726Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a    1\n",
       "b    2\n",
       "c    3\n",
       "Name: A, dtype: int64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# .loc, like before\n",
    "df.loc[:, 'A']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-06T16:31:43.893614Z",
     "start_time": "2021-07-06T16:31:43.807679Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a    1\n",
       "b    2\n",
       "c    3\n",
       "Name: A, dtype: int64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# using `.` attribute lookup\n",
    "df.A"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-06T16:31:44.015688Z",
     "start_time": "2021-07-06T16:31:43.899477Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>mean</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>1</td>\n",
       "      <td>True</td>\n",
       "      <td>0.496714</td>\n",
       "      <td>a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>2</td>\n",
       "      <td>True</td>\n",
       "      <td>-0.138264</td>\n",
       "      <td>b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>3</td>\n",
       "      <td>False</td>\n",
       "      <td>0.647689</td>\n",
       "      <td>c</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   A      B         C mean\n",
       "a  1   True  0.496714    a\n",
       "b  2   True -0.138264    b\n",
       "c  3  False  0.647689    c"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['mean'] = ['a', 'b', 'c']\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-06T16:31:44.170958Z",
     "start_time": "2021-07-06T16:31:44.019593Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<bound method NDFrame._add_numeric_operations.<locals>.mean of    A      B         C mean\n",
       "a  1   True  0.496714    a\n",
       "b  2   True -0.138264    b\n",
       "c  3  False  0.647689    c>"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.mean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-06T16:31:44.262757Z",
     "start_time": "2021-07-06T16:31:44.176819Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a    a\n",
       "b    b\n",
       "c    c\n",
       "Name: mean, dtype: object"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['mean']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-06T16:31:44.407284Z",
     "start_time": "2021-07-06T16:31:44.265687Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0     2\n",
      "1     4\n",
      "2     6\n",
      "3     8\n",
      "4    10\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# Create DataSeries:\n",
    "\n",
    "import pandas as pd\n",
    "s = pd.Series([2, 4, 6, 8, 10])\n",
    "print(s)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You'll have to be careful with the last one. It won't work if you're column name isn't a valid python identifier (say it has a space) or if it conflicts with one of the (many) methods on **DataFrame**. The **`.`** accessor is extremely convient for interactive use though.\n",
    "\n",
    "You should never *assign* a column with **`.`** e.g. don't do\n",
    "\n",
    "```python\n",
    "# bad\n",
    "df.A = [1, 2, 3]\n",
    "```\n",
    "\n",
    "It's unclear whether your attaching the list **`[1, 2, 3]`** as an attribute of **`df`**, or whether you want it as a column. It's better to just say\n",
    "\n",
    "```python\n",
    "df['A'] = [1, 2, 3]\n",
    "# or\n",
    "df.loc[:, 'A'] = [1, 2, 3]\n",
    "```\n",
    "\n",
    "**Series** share many of the same methods as **DataFrame**s."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Index\n",
    "\n",
    "**`Index`** are something of a peculiarity to pandas.\n",
    "First off, they are not the kind of indexes you'll find in SQL, which are used to help the engine speed up certain queries.\n",
    "In pandas, **`Index`** are about lables. This helps with selection (like we did above) and automatic alignment when performing operations between two **DataFrames** or **Series**.\n",
    "\n",
    "R does have row labels, but they're nowhere near as powerful (or complicated) as in pandas. You can access the index of a **DataFrame** or **Series** with the **`.index`** attribute."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-06T16:31:44.530824Z",
     "start_time": "2021-07-06T16:31:44.413148Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['a', 'b', 'c'], dtype='object')"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-06T16:31:44.648498Z",
     "start_time": "2021-07-06T16:31:44.545471Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['A', 'B', 'C', 'mean'], dtype='object')"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are special kinds of `Index`es that you'll come across. Some of these are\n",
    "\n",
    "- **`MultiIndex`** for multidimensional (Hierarchical) labels\n",
    "- **`DatetimeIndex`** for datetimes\n",
    "- **`Float64Index`** for floats\n",
    "- **`CategoricalIndex`** for, you guessed it, **Categoricals**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* **How do you slice a DataFrame by row label?**\n",
    "  - Use **`.loc[label]`**. For position based use **`.iloc[integer]`**.\n",
    "* **How do you select a column of a DataFrame?**\n",
    "  - Standard **`__getitem__`**: **`df[column_name]`**\n",
    "* **Is the Index a column in the DataFrame?**\n",
    "  - No. It isn't included in any operations (**`mean`**, etc). It can be inserted as a regular column with **`df.reset_index()`**."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dataframe from CSV\n",
    "\n",
    "In the field of Data Science, **[CSV](https://en.wikipedia.org/wiki/Comma-separated_values)** files are used to store large datasets. To efficiently analyze such datasets, we need to convert them into pandas DataFrame.\n",
    "\n",
    "To create a DataFrame from CSV, we use the **`read_csv('file_name')`** function that takes the file name as input and returns DataFrame as output."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Example 1:** \n",
    "\n",
    "Let’s see how to read the **[stockprice_data.csv](https://github.com/milaan9/10_Python_Pandas_Module/blob/main/stockprice_data.csv)** file into the DataFrame and then convert it into Pandas Series\n",
    "\n",
    "<div>\n",
    "<img src=\"img/csvfile1.png\" width=\"300\" />\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-06T16:31:44.928769Z",
     "start_time": "2021-07-06T16:31:44.652403Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Closing price</th>\n",
       "      <th>Return</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1/1/2020</td>\n",
       "      <td>100</td>\n",
       "      <td>0.010000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2/1/2020</td>\n",
       "      <td>120</td>\n",
       "      <td>0.200000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3/1/2020</td>\n",
       "      <td>130</td>\n",
       "      <td>0.083333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4/1/2020</td>\n",
       "      <td>98</td>\n",
       "      <td>-0.246154</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5/1/2020</td>\n",
       "      <td>50</td>\n",
       "      <td>-0.489796</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6/1/2020</td>\n",
       "      <td>102</td>\n",
       "      <td>1.040000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7/1/2020</td>\n",
       "      <td>104</td>\n",
       "      <td>0.019608</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8/1/2020</td>\n",
       "      <td>150</td>\n",
       "      <td>0.442308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9/1/2020</td>\n",
       "      <td>160</td>\n",
       "      <td>0.066667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10/1/2020</td>\n",
       "      <td>109</td>\n",
       "      <td>-0.318750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11/1/2020</td>\n",
       "      <td>95</td>\n",
       "      <td>-0.128440</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date  Closing price    Return\n",
       "0    1/1/2020            100  0.010000\n",
       "1    2/1/2020            120  0.200000\n",
       "2    3/1/2020            130  0.083333\n",
       "3    4/1/2020             98 -0.246154\n",
       "4    5/1/2020             50 -0.489796\n",
       "5    6/1/2020            102  1.040000\n",
       "6    7/1/2020            104  0.019608\n",
       "7    8/1/2020            150  0.442308\n",
       "8    9/1/2020            160  0.066667\n",
       "9   10/1/2020            109 -0.318750\n",
       "10  11/1/2020             95 -0.128440"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "data = pd.read_csv(\"stockprice_data.csv\")\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-06T16:31:44.944393Z",
     "start_time": "2021-07-06T16:31:44.931699Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.frame.DataFrame"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-06T16:31:45.056696Z",
     "start_time": "2021-07-06T16:31:44.948300Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     0.010000\n",
       "1     0.200000\n",
       "2     0.083333\n",
       "3    -0.246154\n",
       "4    -0.489796\n",
       "5     1.040000\n",
       "6     0.019608\n",
       "7     0.442308\n",
       "8     0.066667\n",
       "9    -0.318750\n",
       "10   -0.128440\n",
       "Name: Return, dtype: float64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data1 = data.iloc[:,2]     # Convert Pandas Data Frame in to Pandas Series\n",
    "data1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-06T16:31:45.177792Z",
     "start_time": "2021-07-06T16:31:45.065487Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.series.Series"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(data1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Example 2:**\n",
    "\n",
    "Let’s see how to read the **[automobile_data.csv](https://github.com/milaan9/10_Python_Pandas_Module/blob/main/automobile_data.csv)** file into the DataFrame.\n",
    "\n",
    "<div>\n",
    "<img src=\"img/csvfile.png\" width=\"500\"/>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-06T16:31:45.458548Z",
     "start_time": "2021-07-06T16:31:45.182674Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    index      company   body-style  wheel-base  length engine-type  \\\n",
      "0       0  alfa-romero  convertible        88.6   168.8        dohc   \n",
      "1       1  alfa-romero  convertible        88.6   168.8        dohc   \n",
      "2       2  alfa-romero    hatchback        94.5   171.2        ohcv   \n",
      "3       3         audi        sedan        99.8   176.6         ohc   \n",
      "4       4         audi        sedan        99.4   176.6         ohc   \n",
      "..    ...          ...          ...         ...     ...         ...   \n",
      "56     81   volkswagen        sedan        97.3   171.7         ohc   \n",
      "57     82   volkswagen        sedan        97.3   171.7         ohc   \n",
      "58     86   volkswagen        sedan        97.3   171.7         ohc   \n",
      "59     87        volvo        sedan       104.3   188.8         ohc   \n",
      "60     88        volvo        wagon       104.3   188.8         ohc   \n",
      "\n",
      "   num-of-cylinders  horsepower  average-mileage    price  \n",
      "0              four         111               21  13495.0  \n",
      "1              four         111               21  16500.0  \n",
      "2               six         154               19  16500.0  \n",
      "3              four         102               24  13950.0  \n",
      "4              five         115               18  17450.0  \n",
      "..              ...         ...              ...      ...  \n",
      "56             four          85               27   7975.0  \n",
      "57             four          52               37   7995.0  \n",
      "58             four         100               26   9995.0  \n",
      "59             four         114               23  12940.0  \n",
      "60             four         114               23  13415.0  \n",
      "\n",
      "[61 rows x 10 columns]\n"
     ]
    }
   ],
   "source": [
    "cars = pd.read_csv(\"automobile_data.csv\")  # just give the name of file only if the file is in the same folder.\n",
    "print(cars)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## DataFrame Options\n",
    "\n",
    "When DataFrame is vast, and we can not display the whole data while printing. In that case, we need to change how DataFrame gets display on the console using the **`print()`** function. For that, pandas have provided many options and functions to customize the presentation of the DataFrame."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### To customize the display of DataFrame while printing\n",
    "\n",
    "When we display the DataFrame using **`print()`** function by default, it displays 10 rows (top 5 and bottom 5). Sometimes we may need to show more or lesser rows than the default view of the DataFrame.\n",
    "\n",
    "We can change the setting by using **`pd.options`** or **`pd.set_option()`** functions. Both can be used interchangeably.\n",
    "\n",
    "The below example will show a maximum of 20 and a minimum of 5 rows while printing DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-06T16:31:45.520071Z",
     "start_time": "2021-07-06T16:31:45.461482Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    index      company   body-style  wheel-base  length engine-type  \\\n",
      "0       0  alfa-romero  convertible        88.6   168.8        dohc   \n",
      "1       1  alfa-romero  convertible        88.6   168.8        dohc   \n",
      "..    ...          ...          ...         ...     ...         ...   \n",
      "59     87        volvo        sedan       104.3   188.8         ohc   \n",
      "60     88        volvo        wagon       104.3   188.8         ohc   \n",
      "\n",
      "   num-of-cylinders  horsepower  average-mileage    price  \n",
      "0              four         111               21  13495.0  \n",
      "1              four         111               21  16500.0  \n",
      "..              ...         ...              ...      ...  \n",
      "59             four         114               23  12940.0  \n",
      "60             four         114               23  13415.0  \n",
      "\n",
      "[61 rows x 10 columns]\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Setting maximum rows to be shown \n",
    "pd.options.display.max_rows = 20\n",
    "\n",
    "# Setting minimum rows to be shown\n",
    "pd.set_option(\"display.min_rows\", 5)\n",
    "\n",
    "# Print DataFrame\n",
    "print(cars)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## DataFrame metadata\n",
    "\n",
    "Sometimes we need to get metadata of the DataFrame and not the content inside it. Such metadata information is useful to understand the DataFrame as it gives more details about the DataFrame that we need to process.\n",
    "\n",
    "In this section, we cover the functions which provide such information of the DataFrame.\n",
    "\n",
    "Let’s take an example of student DataFrame which contains **'Name'**, **'Age'** and **'Marks'** of students as shown below:\n",
    "\n",
    "```python\n",
    "    Name  Age  Marks\n",
    "0    Joe   20  85.10\n",
    "1    Nat   21  77.80\n",
    "2  Harry   19  91.54\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Metadata info of DataFrame\n",
    "\n",
    "**`DataFrame.info()`** is a function of DataFrame that gives metadata of DataFrame. Which includes,\n",
    "\n",
    "* Number of rows and its range of index\n",
    "* Total number of columns\n",
    "* List of columns\n",
    "* Count of the total number of non-null values in the column\n",
    "* Data type of column\n",
    "* Count of columns in each data type\n",
    "* Memory usage by the DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-06T16:31:45.781304Z",
     "start_time": "2021-07-06T16:31:45.523493Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2 entries, 0 to 1\n",
      "Data columns (total 3 columns):\n",
      " #   Column  Non-Null Count  Dtype  \n",
      "---  ------  --------------  -----  \n",
      " 0   Name    2 non-null      object \n",
      " 1   Age     2 non-null      int64  \n",
      " 2   Marks   2 non-null      float64\n",
      "dtypes: float64(1), int64(1), object(1)\n",
      "memory usage: 176.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "# Example: In the below example, we got metadata information of student DataFrame.\n",
    "\n",
    "# get dataframe info\n",
    "student_df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get the statistics of DataFrame\n",
    "\n",
    "**`DataFrame.describe()`** is a function that gives mathematical statistics of the data in DataFrame. But, it applies to the columns that contain numeric values.\n",
    "\n",
    "In our example of student DataFrame, it gives descriptive statistics of **'Age'** and **'Marks'** columns only, that includes:\n",
    "\n",
    "1. **count**: Total number of non-null values in the column\n",
    "2. **mean**: an average of numbers\n",
    "3. **std**: a standard deviation value\n",
    "4. **min**: minimum value\n",
    "5. **25%**: 25th percentile\n",
    "6. **50%**: 50th percentile\n",
    "7. **75%**: 75th percentile\n",
    "8. **max**: maximum value\n",
    "\n",
    ">**Note:** Output of **`DataFrame.describe()`** function varies depending on the input DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-06T16:31:46.045947Z",
     "start_time": "2021-07-06T16:31:45.786186Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Age</th>\n",
       "      <th>Marks</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>2.000000</td>\n",
       "      <td>2.00000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>20.500000</td>\n",
       "      <td>81.45000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>0.707107</td>\n",
       "      <td>5.16188</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>20.000000</td>\n",
       "      <td>77.80000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>20.250000</td>\n",
       "      <td>79.62500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>20.500000</td>\n",
       "      <td>81.45000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>20.750000</td>\n",
       "      <td>83.27500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>21.000000</td>\n",
       "      <td>85.10000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Age     Marks\n",
       "count   2.000000   2.00000\n",
       "mean   20.500000  81.45000\n",
       "std     0.707107   5.16188\n",
       "min    20.000000  77.80000\n",
       "25%    20.250000  79.62500\n",
       "50%    20.500000  81.45000\n",
       "75%    20.750000  83.27500\n",
       "max    21.000000  85.10000"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Example\n",
    "\n",
    "# get dataframe description\n",
    "student_df.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## DataFrame Attributes\n",
    "\n",
    "DataFrame has provided many built-in attributes. Attributes do not modify the underlying data, unlike functions, but it is used to get more details about the DataFrame."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Following are majorly used attributes of the DataFrame:\n",
    "\n",
    "| Attribute | Description |\n",
    "|:---- |:---- |\n",
    "| **`DataFrame.index`**   | **It gives the Range of the row index** | \n",
    "| **`DataFrame.columns`** | **It gives a list of column labels** |\n",
    "| **`DataFrame.dtypes`**  | **It gives column names and their data type** | \n",
    "| **`DataFrame.values`**  | **It gives all the rows in DataFrame** |\n",
    "| **`DataFrame.empty`**   | **It is used to check if the DataFrame is empty** | \n",
    "| **`DataFrame.size`**    | **It gives a total number of values in DataFrame** |\n",
    "| **`DataFrame.shape`**   | **It a number of rows and columns in DataFrame** | "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-06T16:31:46.124074Z",
     "start_time": "2021-07-06T16:31:46.050834Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DataFrame :      Name  Age  Marks\n",
      "0    Joe   20  85.10\n",
      "1    Nat   21  77.80\n",
      "2  Harry   19  91.54\n",
      "DataFrame Index :  RangeIndex(start=0, stop=3, step=1)\n",
      "DataFrame Columns :  Index(['Name', 'Age', 'Marks'], dtype='object')\n",
      "DataFrame Column types :  Name      object\n",
      "Age        int64\n",
      "Marks    float64\n",
      "dtype: object\n",
      "DataFrame is empty? :  False\n",
      "DataFrame Shape :  (3, 3)\n",
      "DataFrame Size :  9\n",
      "DataFrame Values :  [['Joe' 20 85.1]\n",
      " ['Nat' 21 77.8]\n",
      " ['Harry' 19 91.54]]\n"
     ]
    }
   ],
   "source": [
    "# Example:\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "# Create DataFrame from dict\n",
    "student_dict = {'Name': ['Joe', 'Nat', 'Harry'], 'Age': [20, 21, 19], 'Marks': [85.10, 77.80, 91.54]}\n",
    "\n",
    "student_df = pd.DataFrame(student_dict)\n",
    "\n",
    "print(\"DataFrame : \", student_df)\n",
    "\n",
    "print(\"DataFrame Index : \", student_df.index)\n",
    "print(\"DataFrame Columns : \", student_df.columns)\n",
    "\n",
    "print(\"DataFrame Column types : \", student_df.dtypes)\n",
    "\n",
    "print(\"DataFrame is empty? : \", student_df.empty)\n",
    "\n",
    "print(\"DataFrame Shape : \", student_df.shape)\n",
    "print(\"DataFrame Size : \", student_df.size)\n",
    "\n",
    "print(\"DataFrame Values : \", student_df.values)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## DataFrame selection \n",
    "\n",
    "While dealing with the vast data in DataFrame, a data analyst always needs to select a particular row or column for the analysis. In such cases, functions that can choose a set of rows or columns like top rows, bottom rows, or data within an index range play a significant role."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Following are the functions that help in selecting the subset of the DataFrame:\n",
    "\n",
    "| Attribute | Description |\n",
    "|:---- |:---- |\n",
    "| **`DataFrame.head(n)`**  | **It is used to select top ‘n’ rows in DataFrame.** | \n",
    "| **`DataFrame.tail(n)`**  | **It is used to select bottom ‘n’ rows in DataFrame.** | \n",
    "| **`DataFrame.at`**       | **It is used to get and set the particular value of DataFrame using row and column labels.** | \n",
    "| **`DataFrame.iat`**      | **It is used to get and set the particular value of DataFrame using row and column index positions.** | \n",
    "| **`DataFrame.get(key)`** | **It is used to get the value of a key in DataFrame where Key is the column name.** | \n",
    "| **`DataFrame.loc()`**    | **It is used to select a group of data based on the row and column labels. It is used for slicing and filtering of the DataFrame.** | \n",
    "| **`DataFrame.iloc()`**   | **It is used to select a group of data based on the row and column index position. Use it for slicing and filtering the DataFrame.** | "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-06T16:31:46.358448Z",
     "start_time": "2021-07-06T16:31:46.134819Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DataFrame :      Name  Age  Marks\n",
      "0    Joe   20  85.10\n",
      "1    Nat   21  77.80\n",
      "2  Harry   19  91.54\n",
      "  Name  Age  Marks\n",
      "0  Joe   20   85.1\n",
      "1  Nat   21   77.8\n",
      "    Name  Age  Marks\n",
      "1    Nat   21  77.80\n",
      "2  Harry   19  91.54\n",
      "Joe\n",
      "Joe\n",
      "0      Joe\n",
      "1      Nat\n",
      "2    Harry\n",
      "Name: Name, dtype: object\n",
      "    Name\n",
      "0    Joe\n",
      "1    Nat\n",
      "2  Harry\n",
      "  Name  Age\n",
      "0  Joe   20\n",
      "1  Nat   21\n"
     ]
    }
   ],
   "source": [
    "# Example\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "# Create DataFrame from dict\n",
    "student_dict = {'Name': ['Joe', 'Nat', 'Harry'], 'Age': [20, 21, 19], 'Marks': [85.10, 77.80, 91.54]}\n",
    "\n",
    "student_df = pd.DataFrame(student_dict)\n",
    "\n",
    "# display dataframe\n",
    "print(\"DataFrame : \", student_df)\n",
    "\n",
    "# select top 2 rows\n",
    "print(student_df.head(2))\n",
    "\n",
    "# select bottom 2 rows\n",
    "print(student_df.tail(2))\n",
    "\n",
    "# select value at row index 0 and column 'Name'\n",
    "print(student_df.at[0, 'Name'])\n",
    "\n",
    "# select value at first row and first column\n",
    "print(student_df.iat[0, 0])\n",
    "\n",
    "# select values of 'Name' column\n",
    "print(student_df.get('Name'))\n",
    "\n",
    "# select values from row index 0 to 2 and 'Name' column\n",
    "print(student_df.loc[0:2, ['Name']])\n",
    "\n",
    "# select values from row index 0 to 2(exclusive) and column position 0 to 2(exclusive)\n",
    "print(student_df.iloc[0:2, 0:2])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## DataFrame modification\n",
    "\n",
    "DataFrame is similar to any excel sheet or a database table where we need to insert new data or **[drop columns]()** and rows if not required. Such data manipulation operations are very common on a DataFrame.\n",
    "\n",
    "In this section, we discuss the data manipulation functions of the DataFrame."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Insert columns\n",
    "\n",
    "Sometimes it is required to add a new column in the DataFrame. **`DataFrame.insert()`** function is used to insert a new column in DataFrame at the specified position.\n",
    "\n",
    "In the below example, we insert a new column **'Class'** as a third new column in the DataFrame with default value ‘**A**’ using the syntax:\n",
    "\n",
    "```python\n",
    "df.insert(loc = col_position, column = new_col_name, value = default_value)\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-06T16:31:46.451221Z",
     "start_time": "2021-07-06T16:31:46.365288Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    Name  Age  Marks\n",
      "0    Joe   20  85.10\n",
      "1    Nat   21  77.80\n",
      "2  Harry   19  91.54\n",
      "    Name  Age Class  Marks\n",
      "0    Joe   20     A  85.10\n",
      "1    Nat   21     A  77.80\n",
      "2  Harry   19     A  91.54\n"
     ]
    }
   ],
   "source": [
    "# Example:\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "# Create DataFrame from dict\n",
    "student_dict = {'Name': ['Joe', 'Nat', 'Harry'], 'Age': [20, 21, 19], 'Marks': [85.10, 77.80, 91.54]}\n",
    "student_df = pd.DataFrame(student_dict)\n",
    "print(student_df)\n",
    "\n",
    "# insert new column in dataframe and display\n",
    "student_df.insert(loc=2, column=\"Class\", value='A')\n",
    "print(student_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Drop columns\n",
    "\n",
    "DataFrame may contain redundant data, in such cases, we may need to delete such data that is not required. **`DataFrame.drop()`** function is used to **[delete the columns from DataFrame](https://github.com/milaan9/10_Python_Pandas_Module/blob/main/001_Python_Pandas_Methods/004_Python_Pandas_DataFrame_drop_columns.ipynb)**.\n",
    "\n",
    "Refer to the following articles to get more details\n",
    "\n",
    "* **[Drop duplicates](https://github.com/milaan9/10_Python_Pandas_Module/blob/main/001_Python_Pandas_Methods/005_Python_Pandas_DataFrame_drop_duplicates.ipynb)**\n",
    "* **[Drop columns with NA](https://github.com/milaan9/10_Python_Pandas_Module/blob/main/001_Python_Pandas_Methods/006_Python_Pandas_DataFrame_drop_columns_with_NA.ipynb)**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the below example, we delete the “**Age**” column from the student DataFrame using **`df.drop(columns=[col1,col2...])`**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-06T16:31:46.716357Z",
     "start_time": "2021-07-06T16:31:46.455127Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    Name  Age  Marks\n",
      "0    Joe   20  85.10\n",
      "1    Nat   21  77.80\n",
      "2  Harry   19  91.54\n",
      "    Name  Marks\n",
      "0    Joe  85.10\n",
      "1    Nat  77.80\n",
      "2  Harry  91.54\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Create DataFrame from dict\n",
    "student_dict = {'Name': ['Joe', 'Nat', 'Harry'], 'Age': [20, 21, 19], 'Marks': [85.10, 77.80, 91.54]}\n",
    "\n",
    "student_df = pd.DataFrame(student_dict)\n",
    "print(student_df)\n",
    "\n",
    "# delete column from dataframe\n",
    "student_df = student_df.drop(columns='Age')\n",
    "print(student_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Apply condition\n",
    "\n",
    "We may need to update the value in the DataFrame based on some condition. **`DataFrame.where() `**function is used to replace the value of DataFrame, where the condition is **`False`**.\n",
    "\n",
    "**Syntax:**\n",
    "```python\n",
    "where(filter, other=new_value)\n",
    "```\n",
    "\n",
    "It applies the filter condition on all the rows in the DataFrame, as follows:\n",
    "\n",
    "* If the filter condition returns **`False`**, then it updates the row with the value specified in **`other`** parameter.\n",
    "* If the filter condition returns **`True`**, then it does not update the row."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the below example, we want to replace the student marks with ‘0’ where marks are less than 80. We pass a filter condition **`df['Marks'] > 80`** to the function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-06T16:31:47.244185Z",
     "start_time": "2021-07-06T16:31:46.719287Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    Name  Age  Marks\n",
      "0    Joe   20  85.10\n",
      "1    Nat   21  77.80\n",
      "2  Harry   19  91.54\n",
      "    Name  Age  Marks\n",
      "0    Joe   20  85.10\n",
      "1    Nat   21   0.00\n",
      "2  Harry   19  91.54\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Create DataFrame from dict\n",
    "student_dict = {'Name': ['Joe', 'Nat', 'Harry'], 'Age': [20, 21, 19], 'Marks': [85.10, 77.80, 91.54]}\n",
    "\n",
    "student_df = pd.DataFrame(student_dict)\n",
    "print(student_df)\n",
    "\n",
    "# Define filter condition\n",
    "filter = student_df['Marks'] > 80\n",
    "\n",
    "student_df['Marks'].where(filter, other=0, inplace=True)\n",
    "print(student_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## DataFrame filter columns\n",
    "\n",
    "Datasets contain massive data that need to be analyzed. But, sometimes, we may want to analyze relevant data and filter out all the other data. In such a case, we can use **`DataFrame.filter() `** function to fetch only required data from DataFrame.\n",
    "\n",
    "It returns the subset of the DataFrame by applying conditions on each row index or column label as specified using the below syntax.\n",
    "\n",
    "**Syntax:**\n",
    "```python\n",
    "df.filter(like = filter_cond, axis = 'columns' or 'index')\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It applies the condition on each row index or column label.\n",
    "\n",
    "* If the condition passed then, it includes that row or column in the resultant DataFrame.\n",
    "* If the condition failed, then it does not have that row or column in the resulting DataFrame.\n",
    "\n",
    ">**Note:** It applies the filter on row index or column label, not on actual data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the below example, we only include the column with a column label that starts with ‘**N**’."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-06T16:31:47.310591Z",
     "start_time": "2021-07-06T16:31:47.264696Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    Name  Age  Marks\n",
      "0    Joe   20  85.10\n",
      "1    Nat   21  77.80\n",
      "2  Harry   19  91.54\n",
      "    Name\n",
      "0    Joe\n",
      "1    Nat\n",
      "2  Harry\n"
     ]
    }
   ],
   "source": [
    "\n",
    "import pandas as pd\n",
    "\n",
    "# Create DataFrame from dict\n",
    "student_dict = {'Name': ['Joe', 'Nat', 'Harry'], 'Age': [20, 21, 19], 'Marks': [85.10, 77.80, 91.54]}\n",
    "\n",
    "student_df = pd.DataFrame(student_dict)\n",
    "print(student_df)\n",
    "\n",
    "# apply filter on dataframe\n",
    "student_df = student_df.filter(like='N', axis='columns')\n",
    "print(student_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## DataFrame rename columns\n",
    "\n",
    "While working with DataFrame, we may need to **[rename the column](https://github.com/milaan9/10_Python_Pandas_Module/blob/main/001_Python_Pandas_Methods/007_Python_Pandas_DataFrame_rename_columns.ipynb)** or row index. We can use **`DataFrame.rename()`** function to alter the row or column labels.\n",
    "\n",
    "We need to pass a dictionary of key-value pairs as input to the function. Where key of the **`dict`** is the existing column label, and the value of **`dict`** is the new column label.\n",
    "\n",
    "```python\n",
    "df.rename(columns = {'old':'new'})\n",
    "```\n",
    "\n",
    "It can be used to rename single or multiple columns and row labels."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the below example, we rename column '**Marks**' to '**Percentage**' in the student DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-06T16:31:47.430221Z",
     "start_time": "2021-07-06T16:31:47.316454Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    Name  Age  Marks\n",
      "0    Joe   20  85.10\n",
      "1    Nat   21  77.80\n",
      "2  Harry   19  91.54\n",
      "    Name  Age  Percentage\n",
      "0    Joe   20       85.10\n",
      "1    Nat   21       77.80\n",
      "2  Harry   19       91.54\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Create DataFrame from dict\n",
    "student_dict = {'Name': ['Joe', 'Nat', 'Harry'], 'Age': [20, 21, 19], 'Marks': [85.10, 77.80, 91.54]}\n",
    "\n",
    "student_df = pd.DataFrame(student_dict)\n",
    "print(student_df)\n",
    "\n",
    "# rename column\n",
    "student_df = student_df.rename(columns={'Marks': 'Percentage'})\n",
    "print(student_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## DataFrame Join\n",
    "\n",
    "In most of the use cases of Data Analytics, data gathered from multiple sources, and we need to combine that data for further analysis. In such instances, join and merge operations are required.\n",
    "\n",
    "**`DataFrame.join()`** function is used to join one DataFrame with another DataFrame as **`df1.join(df2)`**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the below example, we joined two different DataFrames to create a new resultant DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-06T16:31:47.608934Z",
     "start_time": "2021-07-06T16:31:47.442917Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  Name  Age\n",
      "0  Joe   20\n",
      "1  Nat   21\n",
      "   Marks\n",
      "0   85.1\n",
      "1   77.8\n",
      "  Name  Age  Marks\n",
      "0  Joe   20   85.1\n",
      "1  Nat   21   77.8\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# create dataframe from dict \n",
    "student_dict = {'Name': ['Joe', 'Nat'], 'Age': [20, 21]}\n",
    "student_df = pd.DataFrame(student_dict)\n",
    "print(student_df)\n",
    "\n",
    "# create dataframe from dict \n",
    "marks_dict = {'Marks': [85.10, 77.80]}\n",
    "marks_df = pd.DataFrame(marks_dict)\n",
    "print(marks_df)\n",
    "\n",
    "# join dfs\n",
    "joined_df = student_df.join(marks_df)\n",
    "print(joined_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## DataFrame GroupBy\n",
    "\n",
    "**`GroupBy`** operation means splitting the data and then combining them based on some condition. Large data can be divided into logical groups to analyze it.\n",
    "\n",
    "**`DataFrame.groupby()`** function groups the DataFrame row-wise or column-wise based on the condition."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we want to analyze each class’s average marks, we need to combine the student data based on the ‘Class’ column and calculate its average using **`df.groupby(col_label).mean()`** as shown in the below example."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-06T16:31:47.771530Z",
     "start_time": "2021-07-06T16:31:47.625535Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    Name Class  Marks\n",
      "0    Joe     A  85.10\n",
      "1    Nat     B  77.80\n",
      "2  Harry     A  91.54\n",
      "       Marks\n",
      "Class       \n",
      "A      88.32\n",
      "B      77.80\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Create DataFrame from dict\n",
    "student_dict = {'Name': ['Joe', 'Nat', 'Harry'], 'Class': ['A', 'B', 'A'], 'Marks': [85.10, 77.80, 91.54]}\n",
    "student_df = pd.DataFrame(student_dict)\n",
    "print(student_df)\n",
    "\n",
    "# apply group by \n",
    "student_df = student_df.groupby('Class').mean()\n",
    "print(student_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## DataFrame Iteration\n",
    "\n",
    "DataFrame iteration means visiting each element in the DataFrame one by one. While analyzing a DataFrame, we may need to iterate over each row of the DataFrame.\n",
    "\n",
    "There are multiple ways to iterate a DataFrame. We will see the function **`DataFrame.iterrows()`**, which can loop a DataFrame row-wise. It returns the index and row of the DataFrame in each iteration of the for a loop."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-06T16:31:47.879924Z",
     "start_time": "2021-07-06T16:31:47.774458Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0 Name     Joe\n",
      "Age       20\n",
      "Marks     85\n",
      "Name: 0, dtype: object\n",
      "1 Name     Nat\n",
      "Age       21\n",
      "Marks     77\n",
      "Name: 1, dtype: object\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Create DataFrame from dict\n",
    "student_dict = {'Name': ['Joe', 'Nat'], 'Age': [20, 21], 'Marks': [85, 77]}\n",
    "student_df = pd.DataFrame(student_dict)\n",
    "\n",
    "# Iterate all the rows of DataFrame\n",
    "for index, row in student_df.iterrows():\n",
    "    print(index, row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## DataFrame Sorting\n",
    "\n",
    "Data Analyst always needs to perform different operations on the underlying data like merge, sort, concatenate, etc. The most frequently used operation is the sorting of data. Sorted data becomes easy to analyze and inferred.\n",
    "\n",
    "The **`DataFrame.sort_values()`** function is used to sort the DataFrame using one or more columns in ascending (default) or descending order."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the below example, we sort the student data based on the '**Marks**'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-06T16:31:48.006395Z",
     "start_time": "2021-07-06T16:31:47.884810Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    Name  Age  Marks\n",
      "0    Joe   20  85.10\n",
      "1    Nat   21  77.80\n",
      "2  Harry   19  91.54\n",
      "    Name  Age  Marks\n",
      "1    Nat   21  77.80\n",
      "0    Joe   20  85.10\n",
      "2  Harry   19  91.54\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Create DataFrame from dict\n",
    "student_dict = {'Name': ['Joe', 'Nat', 'Harry'], 'Age': [20, 21, 19], 'Marks': [85.10, 77.80, 91.54]}\n",
    "student_df = pd.DataFrame(student_dict)\n",
    "print(student_df)\n",
    "\n",
    "# rename column\n",
    "student_df = student_df.sort_values(by=['Marks'])\n",
    "print(student_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## DataFrame conversion\n",
    "\n",
    "After all the processing on DataFrame, we will get the expected data in the DataFrame. But, we may require to convert the DataFrame back to its original formats like CSV file or **`dict`**, or we may need to convert it to another format for further action like storing it into the Database as SQL table format.\n",
    "\n",
    "Pandas have provided plenty of functions to convert the DataFrames into many different formats."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For example, **`DataFrame.to_dict()`** function is used to converts the **[DataFrame into a Python dictionary](https://github.com/milaan9/10_Python_Pandas_Module/blob/main/001_Python_Pandas_Methods/008_Python_Pandas_DataFrame_to_Python_dictionary.ipynb)** object.\n",
    "\n",
    "Below is the example of a DataFrame which we need to convert into the Python **`dict`**.\n",
    "\n",
    "```python\n",
    "    Name  Class Marks\n",
    "0    Joe     A  85.10\n",
    "1    Nat     B  77.80\n",
    "2  Harry     A  91.54\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let’s see how we can use **`DataFrame.to_dict()`** function to convert the DataFrame into the Python dictionary. By default, it creates the dictionary with keys as column labels and values as mapping of the row index and data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-06T16:31:48.131391Z",
     "start_time": "2021-07-06T16:31:48.014205Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'Name': {1: 'Nat', 0: 'Joe', 2: 'Harry'}, 'Age': {1: 21, 0: 20, 2: 19}, 'Marks': {1: 77.8, 0: 85.1, 2: 91.54}}\n"
     ]
    }
   ],
   "source": [
    "# convert dataframe to dict\n",
    "dict = student_df.to_dict()\n",
    "print(dict)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Summary:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create Test Objects\n",
    "\n",
    "| Operator | Description |\n",
    "|:---- |:---- |\n",
    "| **`pd.DataFrame(np.random.rand(20,5))`** | **5 columns and 20 rows of random floats** | \n",
    "| **`pd.Series(my_list)`** | **Create a series from an iterable my_list** | \n",
    "| **`df.index = pd.date_range('1900/1/30', periods=df.shape[0])`** | **Add a date index** | "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "## Viewing/Inspecting Data\n",
    "\n",
    "| Operator | Description |\n",
    "|:---- |:---- |\n",
    "| **`df.head(n)`** | **First n rows of the DataFrame** | \n",
    "| **`df.tail(n)`** | **Last n rows of the DataFrame** | \n",
    "| **`df.shape`** | **Number of rows and columns** | \n",
    "| **`df.info()`** | **Index, Datatype and Memory information** | \n",
    "| **`df.describe()`** | **Summary statistics for numerical columns** | \n",
    "| **`s.value_counts(dropna=False)`** | **View unique values and counts** | \n",
    "| **`df.apply(pd.Series.value_counts)`** | **Unique values and counts for all columns** | "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Selection\n",
    "\n",
    "| Operator | Description |\n",
    "|:---- |:---- |\n",
    "| **`df[col]`** | **Returns column with label col as Series** | \n",
    "| **`df[[col1, col2]]`** | **Returns columns as a new DataFrame** | \n",
    "| **`s.iloc[0]`** | **Selection by position** | \n",
    "| **`s.loc['index_one']`** | **Selection by index** | \n",
    "| **`df.iloc[0,:]`** | **First row** | \n",
    "| **`df.iloc[0,0]`** | **First element of first column** | "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Cleaning\n",
    "\n",
    "| Operator | Description |\n",
    "|:---- |:---- |\n",
    "| **`df.columns = ['a','b','c']`** | **Rename columns** | \n",
    "| **`pd.isnull()`** | **Checks for null Values, Returns Boolean Arrray** | \n",
    "| **`pd.notnull()`** | **Opposite of pd.isnull()** | \n",
    "| **`df.dropna()`** | **Drop all rows that contain null values** | \n",
    "| **`df.dropna(axis=1)`** | **Drop all columns that contain null values** | \n",
    "| **`df.dropna(axis=1,thresh=n)`** | **Drop all rows have have less than n non null values** | \n",
    "| **`df.fillna(x)`** | **Replace all null values with x** | \n",
    "| **`s.fillna(s.mean())`** | **Replace all null values with the mean** | \n",
    "| **`s.astype(float)`** | **Convert the datatype of the series to float** | \n",
    "| **`s.replace(1,'one')`** | **Replace all values equal to 1 with 'one'** | \n",
    "| **`s.replace([2,3],['two', 'three'])`** | **Replace all 2 with 'two' and 3 with 'three'** | \n",
    "| **`df.rename(columns=lambda x: x + 1)`** | **Mass renaming of columns** | \n",
    "| **`df.rename(columns={'old_name': 'new_ name'})`** | **Selective renaming** | \n",
    "| **`df.set_index('column_one')`** | **Change the index** | \n",
    "| **`df.rename(index=lambda x: x + 1)`** | **Mass renaming of index** | "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Filter, Sort, and Groupby\n",
    "\n",
    "| Operator | Description |\n",
    "|:---- |:---- |\n",
    "| **`df[df[col] > 0.6]`** | **Rows where the column col is greater than 0.6** | \n",
    "| **`df[(df[col] > 0.6) & (df[col] < 0.8)]`** | **Rows where 0.8 > col > 0.6** | \n",
    "| **`df.sort_values(col1)`** | **Sort values by col1 in ascending order** | \n",
    "| **`df.sort_values(col2,ascending=False)`** | **Sort values by col2 in descending order.5** | \n",
    "| **`df.sort_values([col1,col2],ascending=[True,False])`** | **Sort values by col1 in ascending order then col2 in descending order** | \n",
    "| **`df.groupby(col)`** | **Returns a groupby object for values from one column** | \n",
    "| **`df.groupby([col1,col2])`** | **Returns groupby object for values from multiple columns** | \n",
    "| **`df.groupby(col1)[col2]`** | **Returns the mean of the values in col2, grouped by the values in col1** | \n",
    "| **`df.pivot_table(index=col1,values=[col2,col3],aggfunc=mean)`** | **Create a pivot table that groups by col1 and calculates the mean of col2 and col3** | \n",
    "| **`df.groupby(col1).agg(np.mean)`** | **Find the average across all columns for every unique col1 group** | \n",
    "| **`df.apply(np.mean)`** | **Apply the function np.mean() across each column** | \n",
    "| **`nf.apply(np.max,axis=1)`** | **Apply the function np.max() across each row** | "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Join/Combine\n",
    "\n",
    "| Operator | Description |\n",
    "|:---- |:---- |\n",
    "| **`df1.append(df2)`** | **Add the rows in df1 to the end of df2 (columns should be identical)** | \n",
    "| **`pd.concat([df1, df2],axis=1)`** | **Add the columns in df1 to the end of df2 (rows should be identical)** | \n",
    "| **`df1.join(df2,on=col1, how='inner')`** | **SQL-style join the columns in df1 with the columns on df2 where the rows for col have identical values. The 'how' can be 'left', 'right', 'outer' or 'inner'** | "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Statistics\n",
    "\n",
    "| Operator | Description |\n",
    "|:---- |:---- |\n",
    "| **`df.describe()`** | **Summary statistics for numerical columns** | \n",
    "| **`df.mean()`** | **Returns the mean of all columns** | \n",
    "| **`df.corr()`** | **Returns the correlation between columns in a DataFrame** | \n",
    "| **`df.count()`** | **Returns the number of non-null values in each DataFrame column** | \n",
    "| **`df.max()`** | **Returns the highest value in each column** | \n",
    "| **`df.min()`** | **Returns the lowest value in each column** | \n",
    "| **`df.median()`** | **Returns the median of each column** | \n",
    "| **`df.std()`** | **Returns the standard deviation of each column** |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Importing Data\n",
    "\n",
    "| Operator | Description |\n",
    "|:---- |:---- |\n",
    "| **`pd.read_csv(filename)`** | **From a CSV file** | \n",
    "| **`pd.read_table(filename)`** | **From a delimited text file (like TSV)** | \n",
    "| **`pd.read_excel(filename)`** | **From an Excel file** | \n",
    "| **`pd.read_sql(query, connection_object)`** | **Read from a SQL table/database** | \n",
    "| **`pd.read_json(json_string)`** | **Read from a JSON formatted string, URL or file.** | \n",
    "| **`pd.read_html(url)`** | **Parses an html URL, string or file and extracts tables to a list of dataframes** | \n",
    "| **`pd.read_clipboard()`** | **Takes the contents of your clipboard and passes it to read_table()** | \n",
    "| **`pd.DataFrame(dict)`** | **From a dict, keys for columns names, values for data as lists** |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exporting Data\n",
    "\n",
    "| Operator | Description |\n",
    "|:---- |:---- |\n",
    "| **`df.to_csv(filename)`** | **Write to a CSV file** | \n",
    "| **`df.to_excel(filename)`** | **Write to an Excel file** | \n",
    "| **`df.to_sql(table_name, connection_object)`** | **Write to a SQL table** | \n",
    "| **`df.to_json(filename)`** | **Write to a file in JSON format** |"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "hide_input": false,
  "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.8.8"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}