DataStructures.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Python basic data storage – towards spreadsheets\n",
    "\n",
    "It may seem like a waste of time to go through this when I could just start showing you Pandas syntax, but **there were a lot of things that seemed really strange to me when I first learned Pandas. Perhaps if you see what problems Pandas avoids, it will make more sense why Pandas is built like it is.**\n",
    "\n",
    "The only spreadsheets I'd ever dealt with were in Excel, which has almost no constraints – you can type any value into any cell – but I didn't realize how that lack of constraints can cause problems.\n",
    "\n",
    "**I love Excel, it's very handy and I use it a lot, but it can be problematic in various ways:**\n",
    "- Operations are not \"reproducible\"\n",
    "    - there is no record of what you did to the data (editing, transforming, etc)\n",
    "    - so you don't know what you did\n",
    "    - and others don't know what you did\n",
    "    - so you have to manually take notes to share with others\n",
    "    - like when you publish your paper and others want to try to reproduce your work\n",
    "    - it's not easy to apply the same manipulations to new data\n",
    "    - or to a bunch of files containing the same types of data\n",
    "- You're allowed to mix data types in a column, which can cause problems with analysis\n",
    "- It's possible to sort one column and not sort the others\n",
    "- When joining tables together:\n",
    "    - copy/pasting new columns can result in misaligned rows\n",
    "    - copy/pasting new rows can result in misaligned columns\n",
    "    - joining based on a key column is supported, but formulas take a while to set up in each new case, and it's tedious to set up when joining mulitiple columns\n",
    "- You can't do some common transformations like to pivot/melt/gather wide data into tall (tidy) data\n",
    "- It's not Excel's fault, but few people learn how to use the super-useful/powerful Pivot Table functionality\n",
    "\n",
    "To help understand Pandas better, let's ask ourselves, **\"If we want to manipulate tablular data, like spreadsheets, in Python, what would be the natural data structures, and what problems does Pandas solve?\"** Along the way we'll review a few Python data structures, which will help us later, too."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "*To preserve the mystery, select from the notebook menus*\n",
    "\n",
    "`Edit -> Clear All Outputs`\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Let's review two basic native Python data containers\n",
    "\n",
    "*Note: there are more than these two, but **Lists** and **Dictionaries** are the two most common mutable (changable) data structures, and understanding them will help with understanding how Pandas data structures work, so they're worth a review.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Lists\n",
    "\n",
    "Besides a single variable, a list is perhaps the most basic data container in Python.\n",
    "\n",
    "- It stores multiple values\n",
    "- and it's super flexible – you're not confined to just one type of object in a list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['a', 'b', 'c', 'd', 'e']"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ll = ['a', 'b', 'c', 'd', 'e']\n",
    "ll"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### List access – simple indexing by integer\n",
    "\n",
    "- We access individual elements by integer position, starting with `0`, and ending with `length-1`\n",
    "- negative values count from the end starting at -1\n",
    "- **knowing the correct integer position can be error prone**\n",
    "- if you don't know where an element is, searching through the list is slow"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "first element\tll[0]\t a\n",
      "second element\tll[1]\t b\n",
      "last element\tll[4]\t e\n",
      "last element\tll[-1]\t e\n"
     ]
    }
   ],
   "source": [
    "print('first element\\tll[0]\\t', ll[0])\n",
    "print('second element\\tll[1]\\t', ll[1])\n",
    "print('last element\\tll[4]\\t', ll[4])\n",
    "print('last element\\tll[-1]\\t', ll[-1])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### List access – slicing a range of values\n",
    "\n",
    "- we can use the `m:n` \"slice\" notation to access a sequence (which remains a list)\n",
    "- notice the returned `m:n` slice starts with `m` but ends with `n-1`\n",
    "- if you leave out the beginning or ending index the slice goes all the way from the beginning or end\n",
    "- the `:` by itself returns all elements"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1:3 slice\tll[1:3]\t ['b', 'c']\n",
      "first 3 elems\tll[:3]\t ['a', 'b', 'c']\n",
      "index 3 to end\tll[3:]\t ['d', 'e']\n",
      "all elements\tll[:]\t ['a', 'b', 'c', 'd', 'e']\n"
     ]
    }
   ],
   "source": [
    "print('1:3 slice\\tll[1:3]\\t', ll[1:3])\n",
    "print('first 3 elems\\tll[:3]\\t', ll[:3])\n",
    "print('index 3 to end\\tll[3:]\\t', ll[3:])\n",
    "print('all elements\\tll[:]\\t', ll[:])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dictionaries\n",
    "\n",
    "Knowing the correct integer position to grab an item out of a list can be tricky and error prone, so often it's handy to name things and access them by the name. Python has a \"dictionary\" for just this purpose.\n",
    "\n",
    "- Dictionaries contain what are called \"**key, value pairs**\"\n",
    "- Keys have some constraints, but numbers and strings are fine (immutable)\n",
    "- Values can be anything\n",
    "- Dictionary contents are not guaranteed to be stored in any particular order\n",
    "- **Lookup is super fast!**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'aa': 1, 'bb': 2, 'cc': 3, 'dd': 4, 'ee': 5}"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dd = {'aa':1, 'bb':2, 'cc':3, 'dd':4, 'ee':5}\n",
    "dd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Dictionary access\n",
    "\n",
    "You access the \"values\" by putting the \"key\" in square brackets after the varible name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "'aa' key's value: 1\n",
      "'cc' key's value: 3\n"
     ]
    }
   ],
   "source": [
    "print(\"'aa' key's value:\", dd['aa'])\n",
    "print(\"'cc' key's value:\", dd['cc'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "---\n",
    "\n",
    "## Spreadsheets? (Tabular data)\n",
    "\n",
    "Let's define spreadsheets as:\n",
    "\n",
    "- Data in a grid of columns and rows\n",
    "- Within a column, all values have a consistent data type *(string, integer, floating point, boolean...)*\n",
    "- Not all columns in a spreadsheet need to be the same data type\n",
    "\n",
    "**If we want to store something like a spreadsheet, how might we think of doing that in Python?**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Lists of lists\n",
    "\n",
    "Lists can hold other lists, to create an array of values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[[1, 2, 3], [4, 5, 6], [7, 8, 9]]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lol = [[1,2,3], [4,5,6], [7,8,9]]\n",
    "lol"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "But we have to access elements by integer again"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lol[1][2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And we can only do math easily along one direction – the first-level lists are easiest."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "6\n",
      "15\n",
      "24\n"
     ]
    }
   ],
   "source": [
    "for l in lol:\n",
    "    print(sum(l))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(or changing the loop to a \"list comprehension\", for a more \"pythonic\" expression)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[6, 15, 24]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "[sum(l) for l in lol]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Numpy arrays\n",
    "\n",
    "For 2D arrays of values we can use Numpy arrays. This is getting us closer to a spreadsheet!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[1, 2, 3],\n",
       "       [4, 5, 6],\n",
       "       [7, 8, 9]])"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nn = np.array(lol)\n",
    "nn"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Numpy array access\n",
    "\n",
    "These arrays are built to access by integer position, kind of like lists, but easier than lists in 2 (and higher) dimensions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "first row and first column value: 1\n",
      "second row and third column value: 6\n"
     ]
    }
   ],
   "source": [
    "print('first row and first column value:', nn[0,0])\n",
    "print('second row and third column value:', nn[1,2])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Numpy math\n",
    "\n",
    "- With Numpy arrays we can easily do math in any direction. \n",
    "- They are especially good for doing things like matrix math (linear algebra) and image processing!\n",
    "\n",
    "*Note: Numpy arrays are stored more efficiently than Python lists and allow mathematical operations to be vectorized, which results in significantly higher performance than with looping constructs in Python\n",
    "[[ref]](https://medium.com/@ericvanrees/pandas-series-objects-and-numpy-arrays-15dfe05919d7)*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### axis=0 is down\n",
    "\n",
    "This notation is horrible, but you'll see it again in Pandas, so you just have to get used to it. (Accessing elements in a 2D array, or a Pandas DataFrame, is always done in the order [row,column], so this is the way I remember down over all the rows is first, and across over all the columns is after...)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([12, 15, 18])"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nn.sum(axis=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### axis=1 is across"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 6, 15, 24])"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nn.sum(axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### But in a Numpy array all the data has to be the same type\n",
    "\n",
    "If you try to store mixed types, it has to adjust to a \"lowest common denominator\" data type. In the example below it reverts to a 21-character unicode string representation \"<U21\", so the numbers are stored as strings instead of integers, so we can't do math on the numbers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([['1', '2', '3'],\n",
       "       ['a', 'b', 'c']], dtype='<U21')"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mixed_array = np.array([[1,2,3],['a','b','c']])\n",
    "mixed_array"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "---\n",
    "\n",
    "## Dictionary of lists (or arrays) would be a decent idea for a spreadsheet\n",
    "\n",
    "- *Dictionary key acts as a column name*\n",
    "- *Dictionary value is a list or array holding our rows of values (including Nulls or NaNs)*\n",
    "\n",
    "**This is really starting to look more like a spreadsheet!** We now have:\n",
    "- Column names\n",
    "- Columns don't need to be of the same type!\n",
    "- Easy math down the columns with the lists or arrays"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'name': array(['bernice', 'jinyue', 'haim'], dtype='<U7'),\n",
       " 'level': array([1, 2, 3]),\n",
       " 'grade': array([2.9, 3.8, nan])}"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dict_of_arrays = {'name':np.array(['bernice', 'jinyue', 'haim']),\n",
    "                  'level':np.array([1, 2, 3]), \n",
    "                  'grade':np.array([2.9, 3.8, np.nan])}\n",
    "dict_of_arrays"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### But math is slightly complicated on a dictionary of lists or arrays\n",
    "\n",
    "- If we want to do math down all the columns we have to\n",
    "    - iterate through columns\n",
    "    - deal with non-numeric types\n",
    "- across the rows is can't be done directly in this form\n",
    "- there is nothing to guarantee alignment of the values into rows\n",
    "    - **if you sort one column, the rest don't get sorted at the same time!**\n",
    "    - **if you add a new column, you need to be very careful that the rows are in the same order!**\n",
    "- NaNs (nulls) screw up stats like sum and mean\n",
    "\n",
    "Here's an example of trying to take the mean of the columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "can't do mean on name column\n",
      "mean of level column is 2.0\n",
      "mean of grade column is nan\n"
     ]
    }
   ],
   "source": [
    "for k,v in dict_of_arrays.items():\n",
    "    try:\n",
    "        print('mean of', k, 'column is', v.mean())\n",
    "    except TypeError:\n",
    "        print(\"can't do mean on\", k, \"column\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### It's hard to return a certain row\n",
    "\n",
    "In a dictionary of arrays it's easy to access a single column thorugh the \"column name\", which is the dictionary key, but with a spreadsheet we often want to access a certain row, based on one of the row values.\n",
    "\n",
    "**For example, if you want to see all of Jinyue's information, you have to**\n",
    "\n",
    "- Find the index of the \"name\" array corresponding to Jinyue\n",
    "- Grab the value from that index in all arrays and pair it with the column name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('name', 'jinyue'), ('level', 2), ('grade', 3.8)]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ind = np.argwhere( dict_of_arrays['name']=='jinyue' )[0,0]\n",
    "row = [(k, v[ind]) for k,v in dict_of_arrays.items()]\n",
    "row"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## List of Dictionaries makes row access easier\n",
    "\n",
    "An alternative spreadsheet storage method with the built-in Python data structures is to store each row as a dictionary of values. That has the advantage of keeping the row information all together, always keyed to the \"column name\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'name': 'jinyue', 'level': 2, 'grade': 3.8}\n"
     ]
    }
   ],
   "source": [
    "list_of_dicts = [{'name': 'bernice', 'level': 1, 'grade': 2.9},\n",
    "                 {'name': 'jinyue', 'level': 2, 'grade': 3.8},\n",
    "                 {'name': 'haim', 'level': 3, 'grade': np.nan}]\n",
    "\n",
    "for l in list_of_dicts:\n",
    "    if l['name']=='jinyue':\n",
    "        print(l)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### But, finding elements and math down columns is hard\n",
    "\n",
    "We basically need to re-collect all the values in a certain column and then perform the math operation on it:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "mean level = 2.0\n"
     ]
    }
   ],
   "source": [
    "math_level = [dd['level'] for dd in list_of_dicts]\n",
    "print('mean level =', sum(math_level)/len(math_level))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## Pandas DataFrame is sort of like a more flexible and efficient dictionary of arrays (or array of dictionaries)\n",
    "\n",
    "### Pandas Data Structures [[ref]](https://pandas.pydata.org/pandas-docs/stable/getting_started/overview.html)\n",
    "\n",
    "- **Series**: 1D labeled homogeneously-typed array\n",
    "    - Container for scalars (numbers), strings, or booleans (True/False)\n",
    "    - Each Series has an Index (names for all entries) and potentially an overall name\n",
    "    - Based on a Numpy array [[ref]](https://medium.com/@ericvanrees/pandas-series-objects-and-numpy-arrays-15dfe05919d7)\n",
    "- **DataFrame**: General 2D labeled, size-mutable tabular structure with potentially heterogeneously-typed columns\n",
    "    - Container for Series\n",
    "    - Overall Index (row names), and each Series (column) has a name\n",
    "\n",
    "Printing a DataFrame in a notebook shows the column names/labels along the top, and the \"index\", which are the row labels along the left-hand side (here just sequential integers).\n",
    "\n",
    "Here we easily initialize (create and insert values into) a new DataFrame using the dictionary of arrays"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>level</th>\n",
       "      <th>grade</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>bernice</td>\n",
       "      <td>1</td>\n",
       "      <td>2.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>jinyue</td>\n",
       "      <td>2</td>\n",
       "      <td>3.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>haim</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      name  level  grade\n",
       "0  bernice      1    2.9\n",
       "1   jinyue      2    3.8\n",
       "2     haim      3    NaN"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame(dict_of_arrays)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We could have created the DataFrame from the list of dictionaries just as easily"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>name</th>\n",
       "      <th>level</th>\n",
       "      <th>grade</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>bernice</td>\n",
       "      <td>1</td>\n",
       "      <td>2.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>jinyue</td>\n",
       "      <td>2</td>\n",
       "      <td>3.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>haim</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      name  level  grade\n",
       "0  bernice      1    2.9\n",
       "1   jinyue      2    3.8\n",
       "2     haim      3    NaN"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame(list_of_dicts)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Access is easy on a DataFrame\n",
    "\n",
    "- **Columns are each a Series with their own Index**\n",
    "- we can access each element by name so we don't make mistakes with integers\n",
    "- the Index is like a row number in Excel, but more flexible and powerful\n",
    "- if you don't specify an Index when you create the DataFrame, Pandas will create a sequential integer Index by default\n",
    "- **the Index can be other types of identifiers like strings or dates**\n",
    "\n",
    "Let's grab (select) just the column called \"grade\", which is a Series.\n",
    "\n",
    "**Besides the data access syntax that we'll cover in the next section, this output was always the most confusing thing to me about Pandas. I expected to get just an array of values when I asked for a column or row, and instead I got this weird structure with both labels and values. I didn't understand how helpful the Series was!**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    2.9\n",
       "1    3.8\n",
       "2    NaN\n",
       "Name: grade, dtype: float64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['grade']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Math is easy on a DataFrame\n",
    "\n",
    "- Default is down columns\n",
    "- NaNs/Nulls are ignored rather than leading to a NaN result\n",
    "\n",
    "*Note that a sum() works on strings*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "name     bernicejinyuehaim\n",
       "level                    6\n",
       "grade                  6.7\n",
       "dtype: object"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Columns used to be ignored if there was a TypeError (like trying to take the mean of strings), but now you have to avoid those columns if the aggregation function doesn't make sense with that column. \n",
    "- It's a little inconvenient, but I can see why they do this so it's more explicit what's happening\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "level    2.00\n",
       "grade    3.35\n",
       "dtype: float64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.mean(numeric_only=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- and we can also do calculations across rows\n",
    "\n",
    "*I know this notation is confusing: axis=0 is down columns, axis=1 is across rows*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    1.95\n",
       "1    2.90\n",
       "2    3.00\n",
       "dtype: float64"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.mean(axis=1, numeric_only=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### We can do things like Transpose\n",
    "\n",
    "As with a Numpy array, we can do flexible manipulations like transpose"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>name</th>\n",
       "      <td>bernice</td>\n",
       "      <td>jinyue</td>\n",
       "      <td>haim</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>level</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>grade</th>\n",
       "      <td>2.9</td>\n",
       "      <td>3.8</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             0       1     2\n",
       "name   bernice  jinyue  haim\n",
       "level        1       2     3\n",
       "grade      2.9     3.8   NaN"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.T"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### or even turn the DataFrame into a dictionary of dictionaries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'name': {0: 'bernice', 1: 'jinyue', 2: 'haim'},\n",
       " 'level': {0: 1, 1: 2, 2: 3},\n",
       " 'grade': {0: 2.9, 1: 3.8, 2: nan}}"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.to_dict()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### or a Numpy array if we need to"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([['bernice', 1, 2.9],\n",
       "       ['jinyue', 2, 3.8],\n",
       "       ['haim', 3, nan]], dtype=object)"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.to_numpy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### or a JSON object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'{\"name\":{\"0\":\"bernice\",\"1\":\"jinyue\",\"2\":\"haim\"},\"level\":{\"0\":1,\"1\":2,\"2\":3},\"grade\":{\"0\":2.9,\"1\":3.8,\"2\":null}}'"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.to_json()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### or save to a CSV or Excel file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('df_out.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Operations are vectorized – don't iterate through rows!\n",
    "\n",
    "When most of us learn programming, one of the most common methods we learn (besides if/then statements) are loops.\n",
    "\n",
    "If you find yourself **iterating through the elements** (rows or columns) of a DataFrame, there's a good chance you're doing something **much slower and more complicated** than it needs to be! ***I hardly ever need to loop through the elements of a DataFrame!***\n",
    "\n",
    "*Note that if we assign to a column name that doesn't exist, Pandas will just create a new column*\n",
    "\n",
    "#### String functions on columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>name</th>\n",
       "      <th>level</th>\n",
       "      <th>grade</th>\n",
       "      <th>NAME</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>bernice</td>\n",
       "      <td>1</td>\n",
       "      <td>2.9</td>\n",
       "      <td>BERNICE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>jinyue</td>\n",
       "      <td>2</td>\n",
       "      <td>3.8</td>\n",
       "      <td>JINYUE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>haim</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>HAIM</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      name  level  grade     NAME\n",
       "0  bernice      1    2.9  BERNICE\n",
       "1   jinyue      2    3.8   JINYUE\n",
       "2     haim      3    NaN     HAIM"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['NAME'] = df['name'].str.upper()\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Math on combinations of columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>level</th>\n",
       "      <th>grade</th>\n",
       "      <th>NAME</th>\n",
       "      <th>level_grade</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>bernice</td>\n",
       "      <td>1</td>\n",
       "      <td>2.9</td>\n",
       "      <td>BERNICE</td>\n",
       "      <td>0.344828</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>jinyue</td>\n",
       "      <td>2</td>\n",
       "      <td>3.8</td>\n",
       "      <td>JINYUE</td>\n",
       "      <td>0.526316</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>haim</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>HAIM</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      name  level  grade     NAME  level_grade\n",
       "0  bernice      1    2.9  BERNICE     0.344828\n",
       "1   jinyue      2    3.8   JINYUE     0.526316\n",
       "2     haim      3    NaN     HAIM          NaN"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['level_grade'] = df['level']/df['grade']\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Filling nulls with values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>level</th>\n",
       "      <th>grade</th>\n",
       "      <th>NAME</th>\n",
       "      <th>level_grade</th>\n",
       "      <th>grade_filled</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>bernice</td>\n",
       "      <td>1</td>\n",
       "      <td>2.9</td>\n",
       "      <td>BERNICE</td>\n",
       "      <td>0.344828</td>\n",
       "      <td>2.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>jinyue</td>\n",
       "      <td>2</td>\n",
       "      <td>3.8</td>\n",
       "      <td>JINYUE</td>\n",
       "      <td>0.526316</td>\n",
       "      <td>3.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>haim</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>HAIM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.35</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      name  level  grade     NAME  level_grade  grade_filled\n",
       "0  bernice      1    2.9  BERNICE     0.344828          2.90\n",
       "1   jinyue      2    3.8   JINYUE     0.526316          3.80\n",
       "2     haim      3    NaN     HAIM          NaN          3.35"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['grade_filled'] = df['grade'].fillna(df['grade'].mean())\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Boolean comparisons between columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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>name</th>\n",
       "      <th>level</th>\n",
       "      <th>grade</th>\n",
       "      <th>NAME</th>\n",
       "      <th>level_grade</th>\n",
       "      <th>grade_filled</th>\n",
       "      <th>grade_gt_level</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>bernice</td>\n",
       "      <td>1</td>\n",
       "      <td>2.9</td>\n",
       "      <td>BERNICE</td>\n",
       "      <td>0.344828</td>\n",
       "      <td>2.90</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>jinyue</td>\n",
       "      <td>2</td>\n",
       "      <td>3.8</td>\n",
       "      <td>JINYUE</td>\n",
       "      <td>0.526316</td>\n",
       "      <td>3.80</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>haim</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>HAIM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.35</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      name  level  grade     NAME  level_grade  grade_filled  grade_gt_level\n",
       "0  bernice      1    2.9  BERNICE     0.344828          2.90            True\n",
       "1   jinyue      2    3.8   JINYUE     0.526316          3.80            True\n",
       "2     haim      3    NaN     HAIM          NaN          3.35           False"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['grade_gt_level'] = df['grade'] > df['level']\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Series and DataFrame indices are automatically aligned\n",
    "\n",
    "**Both rows and columns in Pandas are ordered** (unlike dictionaries), and that order doesn't need to be numerical or alphabetical in either the column or row indexes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2     third\n",
       "0     first\n",
       "1    second\n",
       "dtype: object"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sd = pd.Series(index=[2,0,1], data=['third','first','second'])\n",
    "sd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Assigning our new Series as a column in our DataFrame matches on the index\n",
    "\n",
    "*Warning: entries that don't have a matching index value in the DataFrame will be dropped! (There are ways to get around this.)*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>name</th>\n",
       "      <th>level</th>\n",
       "      <th>grade</th>\n",
       "      <th>NAME</th>\n",
       "      <th>level_grade</th>\n",
       "      <th>grade_filled</th>\n",
       "      <th>grade_gt_level</th>\n",
       "      <th>s</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>bernice</td>\n",
       "      <td>1</td>\n",
       "      <td>2.9</td>\n",
       "      <td>BERNICE</td>\n",
       "      <td>0.344828</td>\n",
       "      <td>2.90</td>\n",
       "      <td>True</td>\n",
       "      <td>first</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>jinyue</td>\n",
       "      <td>2</td>\n",
       "      <td>3.8</td>\n",
       "      <td>JINYUE</td>\n",
       "      <td>0.526316</td>\n",
       "      <td>3.80</td>\n",
       "      <td>True</td>\n",
       "      <td>second</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>haim</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>HAIM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.35</td>\n",
       "      <td>False</td>\n",
       "      <td>third</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      name  level  grade     NAME  level_grade  grade_filled  grade_gt_level  \\\n",
       "0  bernice      1    2.9  BERNICE     0.344828          2.90            True   \n",
       "1   jinyue      2    3.8   JINYUE     0.526316          3.80            True   \n",
       "2     haim      3    NaN     HAIM          NaN          3.35           False   \n",
       "\n",
       "        s  \n",
       "0   first  \n",
       "1  second  \n",
       "2   third  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['s'] = sd\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Easy to drop rows with NAs\n",
    "\n",
    "[.dropna() documentation](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.dropna.html)\n",
    "\n",
    "Many options, including \n",
    "- drop any row that has an NA\n",
    "- specify certain columns in which to look for NAs\n",
    "- a threshold number of NAs to tolerate\n",
    "\n",
    "*Here we're not going to make the change in place, but just see what the DataFrame looks like with the NAs dropped.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>level</th>\n",
       "      <th>grade</th>\n",
       "      <th>NAME</th>\n",
       "      <th>level_grade</th>\n",
       "      <th>grade_filled</th>\n",
       "      <th>grade_gt_level</th>\n",
       "      <th>s</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>bernice</td>\n",
       "      <td>1</td>\n",
       "      <td>2.9</td>\n",
       "      <td>BERNICE</td>\n",
       "      <td>0.344828</td>\n",
       "      <td>2.9</td>\n",
       "      <td>True</td>\n",
       "      <td>first</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>jinyue</td>\n",
       "      <td>2</td>\n",
       "      <td>3.8</td>\n",
       "      <td>JINYUE</td>\n",
       "      <td>0.526316</td>\n",
       "      <td>3.8</td>\n",
       "      <td>True</td>\n",
       "      <td>second</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      name  level  grade     NAME  level_grade  grade_filled  grade_gt_level  \\\n",
       "0  bernice      1    2.9  BERNICE     0.344828           2.9            True   \n",
       "1   jinyue      2    3.8   JINYUE     0.526316           3.8            True   \n",
       "\n",
       "        s  \n",
       "0   first  \n",
       "1  second  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dropna()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Also easy to drop duplicates\n",
    "\n",
    "[.drop_duplicates() documentation](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.drop_duplicates.html)\n",
    "\n",
    "Many options here, too, including subsets of columns to consider and which row instance to keep."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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>col1</th>\n",
       "      <th>col2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>c</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>a</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>b</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  col1  col2\n",
       "0    a     1\n",
       "1    b     2\n",
       "2    c     3\n",
       "3    a     1\n",
       "4    b     2"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_dup = pd.DataFrame({'col1':['a','b','c','a','b'],'col2':[1,2,3,1,2]})\n",
    "df_dup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "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>col1</th>\n",
       "      <th>col2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>c</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  col1  col2\n",
       "0    a     1\n",
       "1    b     2\n",
       "2    c     3"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_dup.drop_duplicates()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## Pandas Essential Basic Functionality\n",
    "\n",
    "https://pandas.pydata.org/pandas-docs/stable/getting_started/overview.html\n",
    "\n",
    "Pandas is well suited for many different kinds of data, but most of the time we use it for **tabular data with heterogeneously-typed columns**, as in an SQL table or Excel spreadsheet\n",
    "\n",
    "Here are just a few of the things that Pandas does well:\n",
    "\n",
    "- Easy handling of missing data (represented as NaN)\n",
    "- Automatic and explicit data alignment\n",
    "- Powerful, flexible group by functionality to perform split-apply-combine operations on data sets, for both aggregating and transforming data\n",
    "- Intelligent label-based slicing, fancy indexing, and subsetting of large data sets\n",
    "- Intuitive merging and joining data sets\n",
    "- Flexible reshaping and pivoting of data sets\n",
    "- Robust IO tools for loading data from flat files (CSV and delimited), Excel files, databases, and saving / loading data from the ultrafast HDF5 format\n",
    "- Time series-specific functionality: date range generation and frequency conversion, moving window statistics, date shifting and lagging, etc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.7"
  },
  "toc-autonumbering": false,
  "toc-showcode": false,
  "toc-showmarkdowntxt": false,
  "toc-showtags": false
 },
 "nbformat": 4,
 "nbformat_minor": 4
}