(01) Intro to Python/(19) Intro to Pandas (3) - Accessing and Creating Columns.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Intro to Pandas (3) - Accessing and Creating Columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the last lesson we saw how to implement some useful functions and methods to help us describe a DataFrame. \n",
    "\n",
    "In this lesson we'll learn how to access data in a DataFrame. We'll also learn how to create new columns from existing columns and from a list. These skills are essential to be able to plot data with Plotly as we'll often want to plot the data in different ways - being able to do some basic data manipulation will be really helpful. \n",
    "\n",
    "The content in the next few lessons is by no means an exhaustive list of what you can do with Pandas, and we will introduce some more functionality as we progress with the course, but the material presented here is enough to get us started."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Module Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#plotly.offline doesn't push your charts to the clouds\n",
    "import plotly.offline as pyo\n",
    "#allows us to create the Data and Figure objects\n",
    "from plotly.graph_objs import *\n",
    "#plotly.plotly pushes your charts to the cloud  \n",
    "import plotly.plotly as py\n",
    "\n",
    "#pandas is a data analysis library\n",
    "import pandas as pd\n",
    "from pandas import DataFrame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Accessing columns in a DataFrame\n",
    "\n",
    "First of all, I'm going to read some data into a DataFrame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "baseRateData = pd.read_csv(\"http://www.richard-muir.com/data/public/csv/BoEBaseRate.csv\")\n",
    "\n",
    "baseRateData.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We access the data held in each column in the same way that we would access a dictionary's values by the key. This is the most common aspect of a DataFrame which we'll use in this course. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "baseRateData['VALUE']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also access these values by using a <code> . </code>. Which option to use is not important, I prefer using the dictionary-like option because it's familiar to me, and the syntax highlighting makes it obvious that I'm accessing a column rather than a method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "baseRateData.VALUE"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can get the minimum, maximum and mean values for a particular column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "baseRateData['VALUE'].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "baseRateData['VALUE'].min()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "baseRateData['VALUE'].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating New Columns in a DataFrame\n",
    "\n",
    "We can create new columns in a DataFrame in the same way that we create new keys and values in a Dictionary.\n",
    "\n",
    "Here, I'm creating a new column which stores the base rate data as a proportion rather than a percentage:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "baseRateData['VALUE_PROP'] = baseRateData['VALUE'] / 100\n",
    "\n",
    "baseRateData.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also create a new column from a list. \n",
    "\n",
    "Imagine that we wanted to set the colour of the data point depending on the value of the base rate, for example if the base rate drops below 5, we could colour this green, otherwise it would be coloured red.\n",
    "\n",
    "In the cell below, I'm looping through each value in the VALUE column, and appending a new item to the list <code>colours</code> depending on the value of VALUE:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "colours = []\n",
    "\n",
    "for row in baseRateData['VALUE']:\n",
    "    if row < 5:\n",
    "        colours.append('Green')\n",
    "    else:\n",
    "        colours.append('Red')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I can now create a new column directly from the list:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "baseRateData['COLOUR'] = colours"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's check that this has worked by looking at the top and bottom five rows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "baseRateData.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "baseRateData.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### What have we learnt this lesson?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this lesson we've learnt how to get the data from a column in a DataFrame, and that this data exists in a format which can be passed straight to Plotly. We've also learnt how to create new columns by applying a mathematical operation directly to an existing column, as well as creating a new column from a list.\n",
    "\n",
    "In the next lesson we'll see how to make changes to the DataFrame itself; learning how to rename and drop columns and how to set the index."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you have any questions, please ask in the comments section or email <a href=\"mailto:me@richard-muir.com\">me@richard-muir.com</a>"
   ]
  }
 ],
 "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.4.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}