Lecture 17 - Pandas Reindexing.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "from pandas import Series, DataFrame\n",
    "import pandas as pd\n",
    "from numpy.random import randn"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "A    1\n",
       "B    2\n",
       "C    3\n",
       "D    4\n",
       "dtype: int64"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create a Series for use\n",
    "series1 = Series([1,2,3,4], index=['A', 'B', 'C', 'D'])\n",
    "series1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "A    1.0\n",
       "B    2.0\n",
       "C    3.0\n",
       "D    4.0\n",
       "E    NaN\n",
       "F    NaN\n",
       "dtype: float64"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# using the reindex function, this is an example of how pandas handles missing data.  We create a second Series using the first\n",
    "# but invoke the reindex function adding 2 additional values\n",
    "series2 = series1.reindex(['A', 'B', 'C', 'D', 'E', 'F'])\n",
    "series2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "pandas gracefully adds the NaN values to the new indices missing value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "A    1.0\n",
       "B    2.0\n",
       "C    3.0\n",
       "D    4.0\n",
       "E    NaN\n",
       "F    NaN\n",
       "G    0.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# we can reindex again, this time using the fill_value function to set the value to Zero\n",
    "series2.reindex(['A', 'B', 'C', 'D', 'E', 'F', 'G'], fill_value=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        USA\n",
       "5     Mexico\n",
       "10    Canada\n",
       "dtype: object"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# we can use methods to fill values\n",
    "series3 = Series(['USA', 'Mexico', 'Canada'], index=[0,5,10])\n",
    "series3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n",
      "1\n",
      "2\n",
      "3\n",
      "4\n",
      "5\n",
      "6\n",
      "7\n",
      "8\n",
      "9\n",
      "10\n",
      "11\n",
      "12\n",
      "13\n",
      "14\n"
     ]
    }
   ],
   "source": [
    "# now we have a Series with questionable index.  We can call reindex and update\n",
    "# the lesson has us create a range\n",
    "ranger = range(15)\n",
    "for r in ranger: \n",
    "    print(r)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        USA\n",
       "1        USA\n",
       "2        USA\n",
       "3        USA\n",
       "4        USA\n",
       "5     Mexico\n",
       "6     Mexico\n",
       "7     Mexico\n",
       "8     Mexico\n",
       "9     Mexico\n",
       "10    Canada\n",
       "11    Canada\n",
       "12    Canada\n",
       "13    Canada\n",
       "14    Canada\n",
       "dtype: object"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# using reindex and the method='ffill' (forward fill) argument, we can populate all of the values with any existing value found\n",
    "# in the set.  This method 'forward fills' until the next existing value is found, the 'forward fills' using the next existing \n",
    "# value, etc.\n",
    "series4 = series3.reindex(ranger, method='ffill')\n",
    "series4"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is not the expected output and does not match the output of the video (he's using Python 2.7 and likely an earlier version of pandas')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The pandas 0.21.0 docs suggest the output should be as the lecture suggested\n",
    "http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.reindex.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Answer!\n",
    "in the lecture QA, another user discovered the Series var is immutable.  He simply cast the output to a new var to achieve the \n",
    "expected results.  Updating series3.reindex code and re-running...."
   ]
  },
  {
   "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>column1</th>\n",
       "      <th>column2</th>\n",
       "      <th>column3</th>\n",
       "      <th>column4</th>\n",
       "      <th>column5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <td>1.271590</td>\n",
       "      <td>-1.467061</td>\n",
       "      <td>-0.511015</td>\n",
       "      <td>-0.221016</td>\n",
       "      <td>0.582158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B</th>\n",
       "      <td>2.030178</td>\n",
       "      <td>-0.366906</td>\n",
       "      <td>-1.400952</td>\n",
       "      <td>-0.460804</td>\n",
       "      <td>-0.313815</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>D</th>\n",
       "      <td>1.098716</td>\n",
       "      <td>-0.908437</td>\n",
       "      <td>1.235179</td>\n",
       "      <td>1.894110</td>\n",
       "      <td>1.082023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E</th>\n",
       "      <td>-0.170423</td>\n",
       "      <td>1.183865</td>\n",
       "      <td>-0.051562</td>\n",
       "      <td>-1.531815</td>\n",
       "      <td>-0.890966</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F</th>\n",
       "      <td>-0.111480</td>\n",
       "      <td>-0.946000</td>\n",
       "      <td>0.960293</td>\n",
       "      <td>0.766318</td>\n",
       "      <td>-0.338645</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    column1   column2   column3   column4   column5\n",
       "A  1.271590 -1.467061 -0.511015 -0.221016  0.582158\n",
       "B  2.030178 -0.366906 -1.400952 -0.460804 -0.313815\n",
       "D  1.098716 -0.908437  1.235179  1.894110  1.082023\n",
       "E -0.170423  1.183865 -0.051562 -1.531815 -0.890966\n",
       "F -0.111480 -0.946000  0.960293  0.766318 -0.338645"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# This demo should fill a dataframe\n",
    "# we create a 5x5 dataframe with an index, exluding 'C'\n",
    "df_fill = DataFrame(randn(25).reshape(5,5), index=['A', 'B', 'D', 'E', 'F'],\n",
    "                   columns=['column1', 'column2', 'column3', 'column4', 'column5'])\n",
    "df_fill"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>column1</th>\n",
       "      <th>column2</th>\n",
       "      <th>column3</th>\n",
       "      <th>column4</th>\n",
       "      <th>column5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <td>1.271590</td>\n",
       "      <td>-1.467061</td>\n",
       "      <td>-0.511015</td>\n",
       "      <td>-0.221016</td>\n",
       "      <td>0.582158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B</th>\n",
       "      <td>2.030178</td>\n",
       "      <td>-0.366906</td>\n",
       "      <td>-1.400952</td>\n",
       "      <td>-0.460804</td>\n",
       "      <td>-0.313815</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>D</th>\n",
       "      <td>1.098716</td>\n",
       "      <td>-0.908437</td>\n",
       "      <td>1.235179</td>\n",
       "      <td>1.894110</td>\n",
       "      <td>1.082023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E</th>\n",
       "      <td>-0.170423</td>\n",
       "      <td>1.183865</td>\n",
       "      <td>-0.051562</td>\n",
       "      <td>-1.531815</td>\n",
       "      <td>-0.890966</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F</th>\n",
       "      <td>-0.111480</td>\n",
       "      <td>-0.946000</td>\n",
       "      <td>0.960293</td>\n",
       "      <td>0.766318</td>\n",
       "      <td>-0.338645</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    column1   column2   column3   column4   column5\n",
       "A  1.271590 -1.467061 -0.511015 -0.221016  0.582158\n",
       "B  2.030178 -0.366906 -1.400952 -0.460804 -0.313815\n",
       "C       NaN       NaN       NaN       NaN       NaN\n",
       "D  1.098716 -0.908437  1.235179  1.894110  1.082023\n",
       "E -0.170423  1.183865 -0.051562 -1.531815 -0.890966\n",
       "F -0.111480 -0.946000  0.960293  0.766318 -0.338645"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Hey! we forgot 'C'! let's reindex...\n",
    "# It should be noted that you have to call reindex to add indices, then best practice to save to separate variable\n",
    "# this takes advantage of pandas ability to deal with null values\n",
    "df_fill2 = df_fill.reindex(['A', 'B', 'C', 'D', 'E', 'F'])\n",
    "df_fill2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "# This Example adds new columns\n",
    "# best practices: make a new list of column names\n",
    "new_columns = ['column1', 'column2', 'column3', 'column4', 'column5', 'column6']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>column1</th>\n",
       "      <th>column2</th>\n",
       "      <th>column3</th>\n",
       "      <th>column4</th>\n",
       "      <th>column5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <td>1.271590</td>\n",
       "      <td>-1.467061</td>\n",
       "      <td>-0.511015</td>\n",
       "      <td>-0.221016</td>\n",
       "      <td>0.582158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B</th>\n",
       "      <td>2.030178</td>\n",
       "      <td>-0.366906</td>\n",
       "      <td>-1.400952</td>\n",
       "      <td>-0.460804</td>\n",
       "      <td>-0.313815</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>D</th>\n",
       "      <td>1.098716</td>\n",
       "      <td>-0.908437</td>\n",
       "      <td>1.235179</td>\n",
       "      <td>1.894110</td>\n",
       "      <td>1.082023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E</th>\n",
       "      <td>-0.170423</td>\n",
       "      <td>1.183865</td>\n",
       "      <td>-0.051562</td>\n",
       "      <td>-1.531815</td>\n",
       "      <td>-0.890966</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F</th>\n",
       "      <td>-0.111480</td>\n",
       "      <td>-0.946000</td>\n",
       "      <td>0.960293</td>\n",
       "      <td>0.766318</td>\n",
       "      <td>-0.338645</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    column1   column2   column3   column4   column5\n",
       "A  1.271590 -1.467061 -0.511015 -0.221016  0.582158\n",
       "B  2.030178 -0.366906 -1.400952 -0.460804 -0.313815\n",
       "C       NaN       NaN       NaN       NaN       NaN\n",
       "D  1.098716 -0.908437  1.235179  1.894110  1.082023\n",
       "E -0.170423  1.183865 -0.051562 -1.531815 -0.890966\n",
       "F -0.111480 -0.946000  0.960293  0.766318 -0.338645"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_fill2.reindex(columns=new_columns)\n",
    "df_fill2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "No change... I double checked my syntax with the lecture notes: same.  On to the docs!"
   ]
  },
  {
   "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>column1</th>\n",
       "      <th>column2</th>\n",
       "      <th>column3</th>\n",
       "      <th>column4</th>\n",
       "      <th>column5</th>\n",
       "      <th>column6</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <td>1.271590</td>\n",
       "      <td>-1.467061</td>\n",
       "      <td>-0.511015</td>\n",
       "      <td>-0.221016</td>\n",
       "      <td>0.582158</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B</th>\n",
       "      <td>2.030178</td>\n",
       "      <td>-0.366906</td>\n",
       "      <td>-1.400952</td>\n",
       "      <td>-0.460804</td>\n",
       "      <td>-0.313815</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>D</th>\n",
       "      <td>1.098716</td>\n",
       "      <td>-0.908437</td>\n",
       "      <td>1.235179</td>\n",
       "      <td>1.894110</td>\n",
       "      <td>1.082023</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E</th>\n",
       "      <td>-0.170423</td>\n",
       "      <td>1.183865</td>\n",
       "      <td>-0.051562</td>\n",
       "      <td>-1.531815</td>\n",
       "      <td>-0.890966</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F</th>\n",
       "      <td>-0.111480</td>\n",
       "      <td>-0.946000</td>\n",
       "      <td>0.960293</td>\n",
       "      <td>0.766318</td>\n",
       "      <td>-0.338645</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    column1   column2   column3   column4   column5  column6\n",
       "A  1.271590 -1.467061 -0.511015 -0.221016  0.582158      NaN\n",
       "B  2.030178 -0.366906 -1.400952 -0.460804 -0.313815      NaN\n",
       "C       NaN       NaN       NaN       NaN       NaN      NaN\n",
       "D  1.098716 -0.908437  1.235179  1.894110  1.082023      NaN\n",
       "E -0.170423  1.183865 -0.051562 -1.531815 -0.890966      NaN\n",
       "F -0.111480 -0.946000  0.960293  0.766318 -0.338645      NaN"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_fill3 = df_fill2.reindex(columns=new_columns)\n",
    "df_fill3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Answer!\n",
    "After taking a break to solve the 'ffill' issue, I took note that the latest versions of Python/pandas makes the df immutable.  In order to achieve the output as described in the lecture, you have to save the output to a new df variable."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}