PythonPandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### CS102: Python pandas library - storing and manipulating data in \"dataframes\" (tables)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# For compatibility across multiple platforms\n",
    "import os\n",
    "IB = os.environ.get('INSTABASE_URI',None) is not None\n",
    "open = ib.open if IB else open"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reading from CSV file into dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "f = open('Cities.csv','rU')\n",
    "cities = pd.read_csv(f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# Number of rows\n",
    "len(cities)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# First few rows\n",
    "cities.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Last 20 rows\n",
    "cities.tail(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sorting, selecting rows and columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Sorting by country then descending temperature\n",
    "cities.sort_values(['country','temperature'],ascending=[True,False])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Selecting a single column - returns a 'series'\n",
    "cities.city\n",
    "# Also show cities['city'], cities['temperature'], cities.temperature"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Selecting multiple columns - returns a dataframe\n",
    "cities[['city','temperature']]\n",
    "# Also show cities[['city']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Selecting rows based on condition\n",
    "# Note: no need to do type conversion - pandas infers types for columns\n",
    "cities[cities.longitude < 0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Selecting rows by number\n",
    "cities[15:20]\n",
    "# Show cities[:8] and cities[200:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Putting it together: selecting rows, selecting columns, sorting:\n",
    "# City and longitude of all cities with latitude > 50 and\n",
    "# temperature > 9, sorted by longitude\n",
    "temp1 = cities[(cities.latitude > 50) & (cities.temperature > 9)]\n",
    "temp2 = temp1[['city','longitude']]\n",
    "temp3 = temp2.sort_values('longitude')\n",
    "temp3\n",
    "# Show eliminating temp3, then temp2, then temp1 (use \\ for long lines)\n",
    "# Note similar functionality to SQL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### <font color=\"green\">Your Turn</font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read the Countries.csv file into a dataframe\n",
    "f = open('Countries.csv','rU')\n",
    "countries = pd.read_csv(f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Find all countries that are not in the EU and don't\n",
    "# have coastline, together with their populations,\n",
    "# sorted by population (smallest to largest)\n",
    "YOUR CODE HERE"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Aggregation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Minimum and maximum temperature\n",
    "print 'Minimum temperature:', min(cities.temperature)\n",
    "print 'Maximum temperature:', max(cities.temperature)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Average temperature\n",
    "print 'Using sum/count:', sum(cities.temperature)/len(cities.temperature)\n",
    "import numpy as np\n",
    "print 'Using numpy:', np.average(cities.temperature)\n",
    "print 'Using built-in mean:', cities.temperature.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Average temperature of cities in each country\n",
    "cities.groupby('country').mean().temperature\n",
    "# or [['temperature']]\n",
    "# Also show without column selection\n",
    "# Change mean() to min()\n",
    "# Or use .temperature.mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### <font color=\"green\">Your Turn</font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Find the average population of countries with coastline\n",
    "# and countries without coastline\n",
    "# Hint: You can use groupby!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Then modify to group by both coastline and EU"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Joining"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cities.merge(countries, on='country')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Joining is symmetric\n",
    "countries.merge(cities, on='country')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Miscellaneous features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# String operations - countries with 'ia' in their name\n",
    "countries[countries.country.str.contains('ia')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Plotting\n",
    "cities.plot.scatter(x='latitude', y='temperature')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add fahrenheit column\n",
    "cities['fahrenheit'] = (cities.temperature * 9/5) + 32\n",
    "cities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Sometimes temporary dataframe is needed\n",
    "# Cities with latitude > 50 not in the EU (error then fix)\n",
    "cities.merge(countries, on='country')[(latitude > 50) & (EU == 'no')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Notebook only displays result of last line\n",
    "# Before last line need to use print\n",
    "cities[cities.longitude > 35]\n",
    "cities[cities.longitude < -5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### <font color=\"green\">Your Turn</font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# For cities with 'k' in their name in countries with coastline, find\n",
    "# their average temperature, the name and country of the second-coldest\n",
    "# city, and the name and country of the second-warmest city."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.14"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}