pandas_examples.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "np.random.seed(123456)   # For reproducible random numbers"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "item0   -0.173215\n",
       "item1    0.119209\n",
       "item2   -1.044236\n",
       "item3   -0.861849\n",
       "item4   -2.104569\n",
       "dtype: float64"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "Index(['item0', 'item1', 'item2', 'item3', 'item4'], dtype='object')"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "array([-0.17321465,  0.11920871, -1.04423597, -0.86184896, -2.10456922])"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "item0    1\n",
       "item1    2\n",
       "item2    3\n",
       "item3    4\n",
       "item4    5\n",
       "dtype: int64"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "item0    0.826785\n",
       "item1    2.119209\n",
       "item2    1.955764\n",
       "item3    3.138151\n",
       "item4    2.895431\n",
       "dtype: float64"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# A series object is a homogeneous 1D array with labels called indexes\n",
    "# Think of it as a vector\n",
    "\n",
    "s1 = pd.Series(data=np.random.randn(5),index=[\"item\"+str(i) for i in range(5)])\n",
    "s1\n",
    "s1.index\n",
    "s1.values\n",
    "\n",
    "# Vectorized operations\n",
    "s2 = pd.Series([1,2,3,4,5], index=[\"item\"+str(i) for i in range(5)])\n",
    "s2\n",
    "\n",
    "s1+s2   # Alignment of indexes and values is maintained. only elements with the same index will be added"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5 entries, 0 to 4\n",
      "Data columns (total 3 columns):\n",
      "l1    5 non-null int64\n",
      "l2    5 non-null object\n",
      "l3    5 non-null object\n",
      "dtypes: int64(1), object(2)\n",
      "memory usage: 200.0+ bytes\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>l1</th>\n",
       "      <th>l2</th>\n",
       "      <th>l3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>a</td>\n",
       "      <td>violet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>d</td>\n",
       "      <td>blue</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>e</td>\n",
       "      <td>indigo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>b</td>\n",
       "      <td>green</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>c</td>\n",
       "      <td>yellow</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   l1 l2      l3\n",
       "0   0  a  violet\n",
       "1   1  d    blue\n",
       "2   2  e  indigo\n",
       "3   3  b   green\n",
       "4   4  c  yellow"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "RangeIndex(start=0, stop=5, step=1)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "Index(['l1', 'l2', 'l3'], dtype='object')"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "violet\n",
      "blue\n",
      "indigo\n",
      "green\n",
      "yellow\n",
      "l1\n",
      "l2\n",
      "l3\n"
     ]
    }
   ],
   "source": [
    "# DataFrame: Heterogeneous 2D table with row and column names\n",
    "\n",
    "# Create dictionary with three named lists\n",
    "dict1 = dict(l1 = list(range(5)), l2 = ['a','d','e','b','c'], \n",
    "             l3=['violet', 'blue', 'indigo','green', 'yellow']) \n",
    "# dict1\n",
    "\n",
    "\n",
    "df1 = pd.DataFrame(dict1)    # One way of constructing a DataFrame\n",
    "\n",
    "# Useful for a quick glance\n",
    "\n",
    "df1.info()                   \n",
    "df1.head()\n",
    "\n",
    "# Row labels are called indexes\n",
    "df1.index\n",
    "df1.columns\n",
    "\n",
    "# You can loop through rows and columns\n",
    "for i in df1.index:\n",
    "    print(df1['l3'][i])\n",
    "    \n",
    "for j in df1.columns:\n",
    "    print(j)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Row and column access\n",
    "\n",
    "Square bracket access was never implemented consistently. \n",
    "\n",
    "[Comment by pandas creator on stackoverflow](https://stackoverflow.com/questions/10665889/how-to-take-column-slices-of-dataframe-in-pandas)\n",
    "\n",
    "> DataFrame is inherently a dict-like object when you do df[...], however some conveniences, e.g. df[5:10] \n",
    "> were added for selecting rows (pandas.pydata.org/pandas-docs/stable/…) – Wes McKinney May 19 '12 at 16:52\n",
    "\n",
    "***Best to use .loc and .iloc for fully consistent access***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "df1['l1']  ;      # Returns column as Series object. df1.l1 also works.\n",
    "\n",
    "df1[['l1','l2']];  # Argument is a list of columns. Returns DataFrame\n",
    "\n",
    "df1[1:2];          # Argument is a slice i1:i2 indicating rows needed (so stops at i2-1). "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Access using labels"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>l2</th>\n",
       "      <th>l3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b</td>\n",
       "      <td>indigo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>c</td>\n",
       "      <td>blue</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>d</td>\n",
       "      <td>green</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  l2      l3\n",
       "1  b  indigo\n",
       "2  c    blue\n",
       "3  d   green"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "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>l2</th>\n",
       "      <th>l3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>violet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b</td>\n",
       "      <td>indigo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>c</td>\n",
       "      <td>blue</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>d</td>\n",
       "      <td>green</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>e</td>\n",
       "      <td>yellow</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  l2      l3\n",
       "0  a  violet\n",
       "1  b  indigo\n",
       "2  c    blue\n",
       "3  d   green\n",
       "4  e  yellow"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.loc[1:3,'l2':'l3']    # Row labels (index labels) were auto assigned, so integers.\n",
    "df1.loc[:,'l2':'l3']      # All rows"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Access using positions of rows or columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>l1</th>\n",
       "      <th>l2</th>\n",
       "      <th>l3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>a</td>\n",
       "      <td>violet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>b</td>\n",
       "      <td>indigo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>c</td>\n",
       "      <td>blue</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>d</td>\n",
       "      <td>green</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>e</td>\n",
       "      <td>yellow</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   l1 l2      l3\n",
       "0   0  a  violet\n",
       "1   1  b  indigo\n",
       "2   2  c    blue\n",
       "3   3  d   green\n",
       "4   4  e  yellow"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "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>l2</th>\n",
       "      <th>l3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>violet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b</td>\n",
       "      <td>indigo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>c</td>\n",
       "      <td>blue</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  l2      l3\n",
       "0  a  violet\n",
       "1  b  indigo\n",
       "2  c    blue"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "'blue'"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.iloc[0:3,1:3]    # Row and column numbers start at 0. Slice i1:i2 ends at i2-1\n",
    "df1.iloc[2,2]        # Single element"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Access using contents of rows\n",
    "\n",
    "Nice explanation provided in the first response to [this question](https://stackoverflow.com/questions/17071871/select-rows-from-a-dataframe-based-on-values-in-a-column-in-pandas) on StackOverflow."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>l1</th>\n",
       "      <th>l2</th>\n",
       "      <th>l3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>a</td>\n",
       "      <td>violet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>d</td>\n",
       "      <td>blue</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>e</td>\n",
       "      <td>indigo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>b</td>\n",
       "      <td>green</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>c</td>\n",
       "      <td>yellow</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   l1 l2      l3\n",
       "0   0  a  violet\n",
       "1   1  d    blue\n",
       "2   2  e  indigo\n",
       "3   3  b   green\n",
       "4   4  c  yellow"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "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>l1</th>\n",
       "      <th>l2</th>\n",
       "      <th>l3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>b</td>\n",
       "      <td>green</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>c</td>\n",
       "      <td>yellow</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   l1 l2      l3\n",
       "3   3  b   green\n",
       "4   4  c  yellow"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "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>l1</th>\n",
       "      <th>l2</th>\n",
       "      <th>l3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>b</td>\n",
       "      <td>green</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>c</td>\n",
       "      <td>yellow</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   l1 l2      l3\n",
       "3   3  b   green\n",
       "4   4  c  yellow"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1\n",
    "df1.loc[df1['l1'] > 2,:]\n",
    "df1.query('l1 > 2')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Sorting"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>l1</th>\n",
       "      <th>l2</th>\n",
       "      <th>l3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>d</td>\n",
       "      <td>blue</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>b</td>\n",
       "      <td>green</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>e</td>\n",
       "      <td>indigo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>a</td>\n",
       "      <td>violet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>c</td>\n",
       "      <td>yellow</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   l1 l2      l3\n",
       "1   1  d    blue\n",
       "3   3  b   green\n",
       "2   2  e  indigo\n",
       "0   0  a  violet\n",
       "4   4  c  yellow"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Sorting\n",
    "\n",
    "df1.sort_values(by='l3',ascending=True)\n",
    "\n",
    "# There is also a .sort_index() function\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Custom sorting\n",
    "\n",
    "Sorting by custom ordering has to be done by hand (i.e. no function provided) \n",
    "Idea: https://stackoverflow.com/questions/23279238/custom-sorting-with-pandas\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>l1</th>\n",
       "      <th>l2</th>\n",
       "      <th>l3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>a</td>\n",
       "      <td>violet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>e</td>\n",
       "      <td>indigo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>d</td>\n",
       "      <td>blue</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>b</td>\n",
       "      <td>green</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>c</td>\n",
       "      <td>yellow</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   l1 l2      l3\n",
       "0   0  a  violet\n",
       "2   2  e  indigo\n",
       "1   1  d    blue\n",
       "3   3  b   green\n",
       "4   4  c  yellow"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "Ordering = {'violet':0,'indigo':1,'blue':2,'green':3,'yellow':4}\n",
    "\n",
    "df1['Ordering'] = df1['l3'].map(Ordering)             # Nice use of .map() function of Series object\n",
    "\n",
    "df1=df1.sort_values(by='Ordering')\n",
    "\n",
    "df1=df1.drop(labels=['Ordering'],axis=1)             # This is how you drop a column\n",
    "df1\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Reading files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "os.getcwd()\n",
    "os.listdir();"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 398 entries, 0 to 397\n",
      "Data columns (total 9 columns):\n",
      "mpg             398 non-null float64\n",
      "cylinders       398 non-null int64\n",
      "displacement    398 non-null float64\n",
      "horsepower      392 non-null float64\n",
      "weight          398 non-null float64\n",
      "acceleration    398 non-null float64\n",
      "model_year      398 non-null int64\n",
      "origin          398 non-null int64\n",
      "car_name        398 non-null object\n",
      "dtypes: float64(5), int64(3), object(1)\n",
      "memory usage: 28.1+ KB\n"
     ]
    }
   ],
   "source": [
    "# Auto mpg data downloaded from UC Irvine ML repository\n",
    "\n",
    "DColumns = ['mpg','cylinders','displacement','horsepower','weight','acceleration','model_year',\n",
    "            'origin','car_name']\n",
    "\n",
    "df1 = pd.read_csv('auto-mpg.data',sep='\\s+',header=None,names=DColumns,na_values='?')\n",
    "\n",
    "df1.info()    # Useful to look at to see if columns have the right type\n",
    "              # In the text file 'horsepower' is '?' for some rows. It's read as an object if\n",
    "              # we use the na_values argument to read_csv()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 398 entries, 0 to 397\n",
      "Data columns (total 9 columns):\n",
      "mpg             398 non-null float64\n",
      "cylinders       398 non-null int64\n",
      "displacement    398 non-null float64\n",
      "horsepower      392 non-null float64\n",
      "weight          398 non-null float64\n",
      "acceleration    398 non-null float64\n",
      "model_year      398 non-null category\n",
      "origin          398 non-null category\n",
      "car_name        398 non-null object\n",
      "dtypes: category(2), float64(5), int64(1), object(1)\n",
      "memory usage: 23.4+ KB\n"
     ]
    }
   ],
   "source": [
    "# 'model_year' and 'origin' are categorical variables\n",
    "# set(df1['origin'])    # gets us unique values\n",
    "# origin seems to be region of manufacture '1' for US, '2' for Europe, '3' for Asia\n",
    "\n",
    "for col in ['model_year','origin']:\n",
    "    df1[col] = df1[col].astype('category')\n",
    "    \n",
    "df1.info()    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Grouping\n",
    "\n",
    "See the nice discussion of grouping in the [User Guide](https://pandas.pydata.org/pandas-docs/stable/user_guide/groupby.html).\n",
    "\n",
    "Grouping and related functionality provide powerful techniques for split-apply-combine tasks. The syntax typically requires the user to specify the splitting and the function(s) to apply. Combining the results in an understandable form is automatic."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Summarizing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>mpg</th>\n",
       "      <th>cylinders</th>\n",
       "      <th>displacement</th>\n",
       "      <th>horsepower</th>\n",
       "      <th>weight</th>\n",
       "      <th>acceleration</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>398.000000</td>\n",
       "      <td>398.000000</td>\n",
       "      <td>398.000000</td>\n",
       "      <td>392.000000</td>\n",
       "      <td>398.000000</td>\n",
       "      <td>398.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>23.514573</td>\n",
       "      <td>5.454774</td>\n",
       "      <td>193.425879</td>\n",
       "      <td>104.469388</td>\n",
       "      <td>2970.424623</td>\n",
       "      <td>15.568090</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>7.815984</td>\n",
       "      <td>1.701004</td>\n",
       "      <td>104.269838</td>\n",
       "      <td>38.491160</td>\n",
       "      <td>846.841774</td>\n",
       "      <td>2.757689</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>9.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>68.000000</td>\n",
       "      <td>46.000000</td>\n",
       "      <td>1613.000000</td>\n",
       "      <td>8.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>17.500000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>104.250000</td>\n",
       "      <td>75.000000</td>\n",
       "      <td>2223.750000</td>\n",
       "      <td>13.825000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>23.000000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>148.500000</td>\n",
       "      <td>93.500000</td>\n",
       "      <td>2803.500000</td>\n",
       "      <td>15.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>29.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>262.000000</td>\n",
       "      <td>126.000000</td>\n",
       "      <td>3608.000000</td>\n",
       "      <td>17.175000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>46.600000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>455.000000</td>\n",
       "      <td>230.000000</td>\n",
       "      <td>5140.000000</td>\n",
       "      <td>24.800000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              mpg   cylinders  displacement  horsepower       weight  \\\n",
       "count  398.000000  398.000000    398.000000  392.000000   398.000000   \n",
       "mean    23.514573    5.454774    193.425879  104.469388  2970.424623   \n",
       "std      7.815984    1.701004    104.269838   38.491160   846.841774   \n",
       "min      9.000000    3.000000     68.000000   46.000000  1613.000000   \n",
       "25%     17.500000    4.000000    104.250000   75.000000  2223.750000   \n",
       "50%     23.000000    4.000000    148.500000   93.500000  2803.500000   \n",
       "75%     29.000000    8.000000    262.000000  126.000000  3608.000000   \n",
       "max     46.600000    8.000000    455.000000  230.000000  5140.000000   \n",
       "\n",
       "       acceleration  \n",
       "count    398.000000  \n",
       "mean      15.568090  \n",
       "std        2.757689  \n",
       "min        8.000000  \n",
       "25%       13.825000  \n",
       "50%       15.500000  \n",
       "75%       17.175000  \n",
       "max       24.800000  "
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "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>mpg</th>\n",
       "      <th>cylinders</th>\n",
       "      <th>displacement</th>\n",
       "      <th>horsepower</th>\n",
       "      <th>weight</th>\n",
       "      <th>acceleration</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>origin</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20.083534</td>\n",
       "      <td>6.248996</td>\n",
       "      <td>245.901606</td>\n",
       "      <td>119.048980</td>\n",
       "      <td>3361.931727</td>\n",
       "      <td>15.033735</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>27.891429</td>\n",
       "      <td>4.157143</td>\n",
       "      <td>109.142857</td>\n",
       "      <td>80.558824</td>\n",
       "      <td>2423.300000</td>\n",
       "      <td>16.787143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>30.450633</td>\n",
       "      <td>4.101266</td>\n",
       "      <td>102.708861</td>\n",
       "      <td>79.835443</td>\n",
       "      <td>2221.227848</td>\n",
       "      <td>16.172152</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              mpg  cylinders  displacement  horsepower       weight  \\\n",
       "origin                                                                \n",
       "1       20.083534   6.248996    245.901606  119.048980  3361.931727   \n",
       "2       27.891429   4.157143    109.142857   80.558824  2423.300000   \n",
       "3       30.450633   4.101266    102.708861   79.835443  2221.227848   \n",
       "\n",
       "        acceleration  \n",
       "origin                \n",
       "1          15.033735  \n",
       "2          16.787143  \n",
       "3          16.172152  "
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "origin\n",
       "1    6.402892\n",
       "2    6.723930\n",
       "3    6.090048\n",
       "Name: mpg, dtype: float64"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "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>mpg</th>\n",
       "      <th>cylinders</th>\n",
       "      <th>displacement</th>\n",
       "      <th>horsepower</th>\n",
       "      <th>weight</th>\n",
       "      <th>acceleration</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>origin</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20.083534</td>\n",
       "      <td>6.248996</td>\n",
       "      <td>245.901606</td>\n",
       "      <td>119.048980</td>\n",
       "      <td>3361.931727</td>\n",
       "      <td>15.033735</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>27.891429</td>\n",
       "      <td>4.157143</td>\n",
       "      <td>109.142857</td>\n",
       "      <td>80.558824</td>\n",
       "      <td>2423.300000</td>\n",
       "      <td>16.787143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>30.450633</td>\n",
       "      <td>4.101266</td>\n",
       "      <td>102.708861</td>\n",
       "      <td>79.835443</td>\n",
       "      <td>2221.227848</td>\n",
       "      <td>16.172152</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              mpg  cylinders  displacement  horsepower       weight  \\\n",
       "origin                                                                \n",
       "1       20.083534   6.248996    245.901606  119.048980  3361.931727   \n",
       "2       27.891429   4.157143    109.142857   80.558824  2423.300000   \n",
       "3       30.450633   4.101266    102.708861   79.835443  2221.227848   \n",
       "\n",
       "        acceleration  \n",
       "origin                \n",
       "1          15.033735  \n",
       "2          16.787143  \n",
       "3          16.172152  "
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">mpg</th>\n",
       "      <th colspan=\"2\" halign=\"left\">cylinders</th>\n",
       "      <th colspan=\"2\" halign=\"left\">displacement</th>\n",
       "      <th colspan=\"2\" halign=\"left\">horsepower</th>\n",
       "      <th colspan=\"2\" halign=\"left\">weight</th>\n",
       "      <th colspan=\"2\" halign=\"left\">acceleration</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>origin</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20.083534</td>\n",
       "      <td>18.5</td>\n",
       "      <td>6.248996</td>\n",
       "      <td>6</td>\n",
       "      <td>245.901606</td>\n",
       "      <td>250.0</td>\n",
       "      <td>119.048980</td>\n",
       "      <td>105.0</td>\n",
       "      <td>3361.931727</td>\n",
       "      <td>3365.0</td>\n",
       "      <td>15.033735</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>27.891429</td>\n",
       "      <td>26.5</td>\n",
       "      <td>4.157143</td>\n",
       "      <td>4</td>\n",
       "      <td>109.142857</td>\n",
       "      <td>104.5</td>\n",
       "      <td>80.558824</td>\n",
       "      <td>76.5</td>\n",
       "      <td>2423.300000</td>\n",
       "      <td>2240.0</td>\n",
       "      <td>16.787143</td>\n",
       "      <td>15.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>30.450633</td>\n",
       "      <td>31.6</td>\n",
       "      <td>4.101266</td>\n",
       "      <td>4</td>\n",
       "      <td>102.708861</td>\n",
       "      <td>97.0</td>\n",
       "      <td>79.835443</td>\n",
       "      <td>75.0</td>\n",
       "      <td>2221.227848</td>\n",
       "      <td>2155.0</td>\n",
       "      <td>16.172152</td>\n",
       "      <td>16.4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              mpg        cylinders        displacement         horsepower  \\\n",
       "             mean median      mean median         mean median        mean   \n",
       "origin                                                                      \n",
       "1       20.083534   18.5  6.248996      6   245.901606  250.0  119.048980   \n",
       "2       27.891429   26.5  4.157143      4   109.142857  104.5   80.558824   \n",
       "3       30.450633   31.6  4.101266      4   102.708861   97.0   79.835443   \n",
       "\n",
       "                    weight         acceleration         \n",
       "       median         mean  median         mean median  \n",
       "origin                                                  \n",
       "1       105.0  3361.931727  3365.0    15.033735   15.0  \n",
       "2        76.5  2423.300000  2240.0    16.787143   15.7  \n",
       "3        75.0  2221.227848  2155.0    16.172152   16.4  "
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# First get a statistical summary of numeric variables\n",
    "df1.describe()\n",
    "\n",
    "# Statistics by a grouping variable\n",
    "\n",
    "df1.groupby('origin').mean()        # rows grouped by 'origin', mean for all variables \n",
    "\n",
    "df1.groupby('origin')['mpg'].std()  # rows grouped by 'origin', select 'mpg', compute standard deviation\n",
    "\n",
    "# Better to use the aggregate function\n",
    "\n",
    "df1.groupby('origin').aggregate('mean')\n",
    "df1.groupby('origin').aggregate(['mean','median'])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"8\" halign=\"left\">mpg</th>\n",
       "      <th colspan=\"8\" halign=\"left\">cylinders</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>origin</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>249.0</td>\n",
       "      <td>20.083534</td>\n",
       "      <td>6.402892</td>\n",
       "      <td>9.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>18.5</td>\n",
       "      <td>24.00</td>\n",
       "      <td>39.0</td>\n",
       "      <td>249.0</td>\n",
       "      <td>6.248996</td>\n",
       "      <td>1.661425</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>70.0</td>\n",
       "      <td>27.891429</td>\n",
       "      <td>6.723930</td>\n",
       "      <td>16.2</td>\n",
       "      <td>24.0</td>\n",
       "      <td>26.5</td>\n",
       "      <td>30.65</td>\n",
       "      <td>44.3</td>\n",
       "      <td>70.0</td>\n",
       "      <td>4.157143</td>\n",
       "      <td>0.500310</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>79.0</td>\n",
       "      <td>30.450633</td>\n",
       "      <td>6.090048</td>\n",
       "      <td>18.0</td>\n",
       "      <td>25.7</td>\n",
       "      <td>31.6</td>\n",
       "      <td>34.05</td>\n",
       "      <td>46.6</td>\n",
       "      <td>79.0</td>\n",
       "      <td>4.101266</td>\n",
       "      <td>0.590414</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          mpg                                                     cylinders  \\\n",
       "        count       mean       std   min   25%   50%    75%   max     count   \n",
       "origin                                                                        \n",
       "1       249.0  20.083534  6.402892   9.0  15.0  18.5  24.00  39.0     249.0   \n",
       "2        70.0  27.891429  6.723930  16.2  24.0  26.5  30.65  44.3      70.0   \n",
       "3        79.0  30.450633  6.090048  18.0  25.7  31.6  34.05  46.6      79.0   \n",
       "\n",
       "                                                     \n",
       "            mean       std  min  25%  50%  75%  max  \n",
       "origin                                               \n",
       "1       6.248996  1.661425  4.0  4.0  6.0  8.0  8.0  \n",
       "2       4.157143  0.500310  4.0  4.0  4.0  4.0  6.0  \n",
       "3       4.101266  0.590414  3.0  4.0  4.0  4.0  6.0  "
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Even more elegant: functions not implemented for the GroupBy object are inherited from DataFrame\n",
    "\n",
    "df1.groupby('origin')['mpg','cylinders'].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Group based transformation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>origin</th>\n",
       "      <th>mpg</th>\n",
       "      <th>mp2</th>\n",
       "      <th>mp3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>18.0</td>\n",
       "      <td>-0.705551</td>\n",
       "      <td>-0.325405</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>15.0</td>\n",
       "      <td>-1.089379</td>\n",
       "      <td>-0.793943</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>18.0</td>\n",
       "      <td>-0.705551</td>\n",
       "      <td>-0.325405</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>16.0</td>\n",
       "      <td>-0.961437</td>\n",
       "      <td>-0.637764</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>17.0</td>\n",
       "      <td>-0.833494</td>\n",
       "      <td>-0.481585</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  origin   mpg       mp2       mp3\n",
       "0      1  18.0 -0.705551 -0.325405\n",
       "1      1  15.0 -1.089379 -0.793943\n",
       "2      1  18.0 -0.705551 -0.325405\n",
       "3      1  16.0 -0.961437 -0.637764\n",
       "4      1  17.0 -0.833494 -0.481585"
      ]
     },
     "execution_count": 132,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "(23.514572864321607, 7.815984312565782)"
      ]
     },
     "execution_count": 132,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "(1.0711699534071862e-16, 0.9999999999999999)"
      ]
     },
     "execution_count": 132,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">mpg</th>\n",
       "      <th colspan=\"2\" halign=\"left\">mp3</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>origin</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20.083534</td>\n",
       "      <td>6.402892</td>\n",
       "      <td>1.944005e-16</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>27.891429</td>\n",
       "      <td>6.723930</td>\n",
       "      <td>2.410770e-16</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>30.450633</td>\n",
       "      <td>6.090048</td>\n",
       "      <td>2.178286e-16</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              mpg                     mp3     \n",
       "             mean       std          mean  std\n",
       "origin                                        \n",
       "1       20.083534  6.402892  1.944005e-16  1.0\n",
       "2       27.891429  6.723930  2.410770e-16  1.0\n",
       "3       30.450633  6.090048  2.178286e-16  1.0"
      ]
     },
     "execution_count": 132,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = df1.loc[:,['origin','mpg']]\n",
    "\n",
    "df2['mp2'] = (df2['mpg']-df2['mpg'].mean())/df2['mpg'].std()    # z score across all values\n",
    "\n",
    "df2['mp3'] = df2.groupby('origin')['mpg'].transform(lambda x: (x-x.mean())/x.std()) # group based z score\n",
    "  \n",
    "df2.head()\n",
    "\n",
    "# Checking standardization\n",
    "\n",
    "df2['mpg'].mean(),df2['mpg'].std()\n",
    "df2['mp2'].mean(),df2['mp2'].std()\n",
    "\n",
    "df2.groupby('origin')['mpg','mp3'].aggregate(['mean','std'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Applying an arbitrary function\n",
    "\n",
    "[From the documentation:](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.core.groupby.GroupBy.apply.html#pandas.core.groupby.GroupBy.apply)\n",
    "\n",
    "> The function passed to apply must take a dataframe as its first argument and return a DataFrame, Series or\n",
    "> scalar. apply will then take care of combining the results back together into a single dataframe or series. \n",
    "\n",
    "This is equivalent to writing a for loop that applies the function to each group of observations and collects the results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Average</th>\n",
       "      <th>Med</th>\n",
       "      <th>SD</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>origin</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <th>0</th>\n",
       "      <td>6.248996</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.661425</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <th>0</th>\n",
       "      <td>4.157143</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.500310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <th>0</th>\n",
       "      <td>4.101266</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.590414</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Average  Med        SD\n",
       "origin                           \n",
       "1      0  6.248996  6.0  1.661425\n",
       "2      0  4.157143  4.0  0.500310\n",
       "3      0  4.101266  4.0  0.590414"
      ]
     },
     "execution_count": 151,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "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>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>origin</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6.248996</td>\n",
       "      <td>6</td>\n",
       "      <td>1.661425</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4.157143</td>\n",
       "      <td>4</td>\n",
       "      <td>0.500310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.101266</td>\n",
       "      <td>4</td>\n",
       "      <td>0.590414</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            mean  median       std\n",
       "origin                            \n",
       "1       6.248996       6  1.661425\n",
       "2       4.157143       4  0.500310\n",
       "3       4.101266       4  0.590414"
      ]
     },
     "execution_count": 151,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "# First, we define an arbitrary function\n",
    "###########################\n",
    "def arbitrary(df,col='mpg'):\n",
    "    result = pd.DataFrame({'Average':[df[col].mean()],'Med':[df[col].median()],\n",
    "                        'SD': [df[col].std()]})\n",
    "    return(result)\n",
    "###########################\n",
    "\n",
    "\n",
    "\n",
    "# Then we split the df by 'origin' and apply the function to each group\n",
    "\n",
    "df1.groupby('origin').apply(arbitrary,col='cylinders')\n",
    "\n",
    "# We can cross-check the results\n",
    "\n",
    "df1.groupby('origin')['cylinders'].aggregate(['mean','median','std'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.469112</td>\n",
       "      <td>-0.282863</td>\n",
       "      <td>-1.509059</td>\n",
       "      <td>-1.135632</td>\n",
       "      <td>1.212112</td>\n",
       "      <td>-0.173215</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.119209</td>\n",
       "      <td>-1.044236</td>\n",
       "      <td>-0.861849</td>\n",
       "      <td>-2.104569</td>\n",
       "      <td>-0.494929</td>\n",
       "      <td>1.071804</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.721555</td>\n",
       "      <td>-0.706771</td>\n",
       "      <td>-1.039575</td>\n",
       "      <td>0.271860</td>\n",
       "      <td>-0.424972</td>\n",
       "      <td>0.567020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.276232</td>\n",
       "      <td>-1.087401</td>\n",
       "      <td>-0.673690</td>\n",
       "      <td>0.113648</td>\n",
       "      <td>-1.478427</td>\n",
       "      <td>0.524988</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.404705</td>\n",
       "      <td>0.577046</td>\n",
       "      <td>-1.715002</td>\n",
       "      <td>-1.039268</td>\n",
       "      <td>-0.370647</td>\n",
       "      <td>-1.157892</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>-1.344312</td>\n",
       "      <td>0.844885</td>\n",
       "      <td>1.075770</td>\n",
       "      <td>-0.109050</td>\n",
       "      <td>1.643563</td>\n",
       "      <td>-1.469388</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0.357021</td>\n",
       "      <td>-0.674600</td>\n",
       "      <td>-1.776904</td>\n",
       "      <td>-0.968914</td>\n",
       "      <td>-1.294524</td>\n",
       "      <td>0.413738</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0.276662</td>\n",
       "      <td>-0.472035</td>\n",
       "      <td>-0.013960</td>\n",
       "      <td>-0.362543</td>\n",
       "      <td>-0.006154</td>\n",
       "      <td>-0.923061</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0.895717</td>\n",
       "      <td>0.805244</td>\n",
       "      <td>-1.206412</td>\n",
       "      <td>2.565646</td>\n",
       "      <td>1.431256</td>\n",
       "      <td>1.340309</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>-1.170299</td>\n",
       "      <td>-0.226169</td>\n",
       "      <td>0.410835</td>\n",
       "      <td>0.813850</td>\n",
       "      <td>0.132003</td>\n",
       "      <td>-0.827317</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          0         1         2         3         4         5\n",
       "0  0.469112 -0.282863 -1.509059 -1.135632  1.212112 -0.173215\n",
       "1  0.119209 -1.044236 -0.861849 -2.104569 -0.494929  1.071804\n",
       "2  0.721555 -0.706771 -1.039575  0.271860 -0.424972  0.567020\n",
       "3  0.276232 -1.087401 -0.673690  0.113648 -1.478427  0.524988\n",
       "4  0.404705  0.577046 -1.715002 -1.039268 -0.370647 -1.157892\n",
       "5 -1.344312  0.844885  1.075770 -0.109050  1.643563 -1.469388\n",
       "6  0.357021 -0.674600 -1.776904 -0.968914 -1.294524  0.413738\n",
       "7  0.276662 -0.472035 -0.013960 -0.362543 -0.006154 -0.923061\n",
       "8  0.895717  0.805244 -1.206412  2.565646  1.431256  1.340309\n",
       "9 -1.170299 -0.226169  0.410835  0.813850  0.132003 -0.827317"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.469112</td>\n",
       "      <td>-0.282863</td>\n",
       "      <td>-1.509059</td>\n",
       "      <td>-1.135632</td>\n",
       "      <td>-1.509059</td>\n",
       "      <td>-0.173215</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.119209</td>\n",
       "      <td>-1.044236</td>\n",
       "      <td>-0.861849</td>\n",
       "      <td>-2.104569</td>\n",
       "      <td>-0.861849</td>\n",
       "      <td>1.071804</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.721555</td>\n",
       "      <td>-0.706771</td>\n",
       "      <td>-1.039575</td>\n",
       "      <td>0.271860</td>\n",
       "      <td>-1.039575</td>\n",
       "      <td>0.567020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.276232</td>\n",
       "      <td>-1.087401</td>\n",
       "      <td>-0.673690</td>\n",
       "      <td>0.113648</td>\n",
       "      <td>-0.673690</td>\n",
       "      <td>0.524988</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.404705</td>\n",
       "      <td>0.577046</td>\n",
       "      <td>-1.715002</td>\n",
       "      <td>-1.039268</td>\n",
       "      <td>-1.715002</td>\n",
       "      <td>-1.157892</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>-1.344312</td>\n",
       "      <td>0.844885</td>\n",
       "      <td>1.075770</td>\n",
       "      <td>-0.109050</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.469388</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0.357021</td>\n",
       "      <td>-0.674600</td>\n",
       "      <td>-1.776904</td>\n",
       "      <td>-0.968914</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.413738</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0.276662</td>\n",
       "      <td>-0.472035</td>\n",
       "      <td>-0.013960</td>\n",
       "      <td>-0.362543</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.923061</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0.895717</td>\n",
       "      <td>0.805244</td>\n",
       "      <td>-1.206412</td>\n",
       "      <td>2.565646</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.340309</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>-1.170299</td>\n",
       "      <td>-0.226169</td>\n",
       "      <td>0.410835</td>\n",
       "      <td>0.813850</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.827317</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          0         1         2         3         4         5\n",
       "0  0.469112 -0.282863 -1.509059 -1.135632 -1.509059 -0.173215\n",
       "1  0.119209 -1.044236 -0.861849 -2.104569 -0.861849  1.071804\n",
       "2  0.721555 -0.706771 -1.039575  0.271860 -1.039575  0.567020\n",
       "3  0.276232 -1.087401 -0.673690  0.113648 -0.673690  0.524988\n",
       "4  0.404705  0.577046 -1.715002 -1.039268 -1.715002 -1.157892\n",
       "5 -1.344312  0.844885  1.075770 -0.109050       NaN -1.469388\n",
       "6  0.357021 -0.674600 -1.776904 -0.968914       NaN  0.413738\n",
       "7  0.276662 -0.472035 -0.013960 -0.362543       NaN -0.923061\n",
       "8  0.895717  0.805244 -1.206412  2.565646       NaN  1.340309\n",
       "9 -1.170299 -0.226169  0.410835  0.813850       NaN -0.827317"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.random.seed(123456)\n",
    "df = pd.DataFrame(np.random.randn(10, 6))\n",
    "df\n",
    "df[4]=df[2][:5]\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Multi-level indexing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is best avoided. If some code produces a series or DataFrame with a multi-level index, you can always use pd.DataFrame.reset_index() to get the multi-level indices into the DataFrame as columns.\n",
    "\n",
    "Still, if you want to use the multi-level indexing functionality, the selection of data can get complex. See [the pandas documentation](https://pandas.pydata.org/pandas-docs/stable/user_guide/advanced.html#advanced-indexing-with-hierarchical-index) for details.\n",
    "\n",
    "In particular, using 'slice(None)' can be handy."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[(slice(None),'A1'), :]     # row index is a tuple because rows have a multi-index\n",
    "                                  # slice(None) is a way to say \"all values of this level of the index\""
   ]
  }
 ],
 "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.8.2"
  },
  "latex_envs": {
   "LaTeX_envs_menu_present": true,
   "autoclose": false,
   "autocomplete": true,
   "bibliofile": "biblio.bib",
   "cite_by": "apalike",
   "current_citInitial": 1,
   "eqLabelWithNumbers": true,
   "eqNumInitial": 1,
   "hotkeys": {
    "equation": "Ctrl-E",
    "itemize": "Ctrl-I"
   },
   "labels_anchors": false,
   "latex_user_defs": false,
   "report_style_numbering": false,
   "user_envs_cfg": false
  },
  "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": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}