1. Loading and selecting data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas for beer - Drinking patterns in Sao Paoulo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Reading data\n",
    "\n",
    "Pandas has a fantastic ability to read data files - pretty much any modern data storage can be read in via Pandas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#pd.read_"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One of the main limitations of Python as a datascience language was reading in data - as an example, this is how you would read in a csv \"the old fashioned way\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Old Way"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv\n",
    "\n",
    "with open('data/Consumo_cerveja.csv') as f:\n",
    "    reader = csv.reader(f)\n",
    "    data = [line for line in reader]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# If you want to get fancy :-)\n",
    "with open('data/Consumo_cerveja.csv') as f:\n",
    "    reader = csv.DictReader(f)\n",
    "    data = [line for line in reader]\n",
    "data[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's try that the Pandas way!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas way"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('data/Consumo_cerveja.csv')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "My Brazilian Portugese is a bit rusty, and those names are a bit long to type, so I want something shorter and in english"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "translated_names = ['date',\n",
    "                    'median_temp',\n",
    "                    'min_temp',\n",
    "                    'max_temp',\n",
    "                    'precip',\n",
    "                    'weekend',\n",
    "                    'consumption']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('data/Consumo_cerveja.csv', header=0, names=translated_names)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I set header to be the first row, but tell pandas that I want them to be overwritten by my list of translated names - One line!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data types\n",
    "CSVs are inherently text based - in the old way, we saw that everything was a string, and I would have to spend some time parsing those\n",
    "Pandas does that conversion for you, but it's always good to check!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Temperatures are definitely numbers and not 'object' - something went wrong here. Any guesses?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('data/Consumo_cerveja.csv', header=0, names=translated_names, decimal=',', thousands='.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "CSVs are hard! Especially when working with data from different countries and standards.\n",
    "All that string parsing reduced to two parameters in .read_csv!\n",
    "\n",
    "With all the ways CSVs can go wrong, it's important to double check your data after you've loaded it\n",
    "\n",
    "![Fun Fact](images/fun_fact.resized.jpeg) While there is an official CSV standard no-one follows it! That's why pd.read_csv has 49 parameters..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Looks like some dirty data - what's gone wrong here?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Looks like there's only 365 values total, but we've read in 940 rows - giving us rows full of NaNs!\n",
    "Remember, you still have access to your normal shell toolbox when working in Jupyter Lab! (Or you could just open the file in your favorite text editor)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!tail data/Consumo_cerveja.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So this is no fault of Pandas - the data supplier actually included 576 empty lines.\n",
    "\n",
    "![Fun Fact](images/fun_fact.resized.jpeg) This can often happen when exporting from Excel and you don't realize you have a lot of blank cells!\n",
    "\n",
    "We know we have one year's worth of data, so we can simply read in 365 lines"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('data/Consumo_cerveja.csv', decimal=',', thousands='.', header=0, names=translated_names, nrows=365)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We still have one object left - the date. Pandas was built by a finance quant, so it has first-class support for handling datetimes. For now, just know that we can load in dates as datetimes, they will be useful later!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('data/Consumo_cerveja.csv', decimal=',', thousands='.', header=0, names=translated_names, nrows=365, parse_dates=['date'])\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Our data looks much better now! Let's start manipulating it!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Indexing\n",
    "First order of business is how to access our data. Pandas has many ways to get at your data!\n",
    "\n",
    "We are going to cover the following:\n",
    "- column selection\n",
    "- loc\n",
    "- iloc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Choose one column\n",
    "df['median_temp']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Choose multiple columns\n",
    "df[['median_temp', 'max_temp']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Choose index and columns\n",
    "df.loc[:, 'median_temp']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Choose first row and 'median_temp' column\n",
    "df.loc[0, 'median_temp']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Choose first row and all columns\n",
    "df.loc[0, :]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Choose first row and two columns\n",
    "df.loc[0, ['median_temp', 'min_temp']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Choose first row and second column\n",
    "df.iloc[0, 1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Choose first row and second + third column\n",
    "df.iloc[0, [1, 2]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![Fun Fact](images/fun_fact.resized.jpeg) There are actually two main datastructures in Pandas - the DataFrame and the Series! Think of a Series as a single row or column in a DataFrame - it's what we get back in our examples when we select out a row or column"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Boolean indexing\n",
    "\n",
    "A very common operation is selecting a subset of rows based on some criteria. Pandas borrows \"Boolean indexing\" from numpy, which means to index using an array of True or False - e.g. show me all the rows where something is true. It's much easier to show by example!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# I want only rows where it's a weekend\n",
    "df[df['weekend'] == 1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# I want only the rows where min_temp is greater than 23\n",
    "df[df['min_temp'] > 23]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also combine filters to set multiple conditions on our data\n",
    "\n",
    "![Warning](images/warning.resized.png) For unimportant technical reasons, don't use the python keywords \"and\", \"not\", \"or\". \n",
    "\n",
    "Use the bitwise operator symbols: \n",
    "- & (and)\n",
    "- | (or)\n",
    "- ~ (not)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# I want only the rows where min_temp is greater than 23 and it's the weekend\n",
    "df[(df['weekend'] == 1) & (df['min_temp'] > 23)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# I want only the rows where min_temp is greater than 23 or it's the weekend\n",
    "df[(df['min_temp'] > 23) | (df['weekend'] == 1)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Operations\n",
    "Now we know how to select our data - let's start trying to glean some insight from our data! Pandas comes with a rich array of data aggregation methods built-in"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Make a new dataframe called temperatures which only has min_temp and max_temp\n",
    "temperatures = df.loc[:, ['min_temp', 'max_temp']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "temperatures"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# What's the mean min+max temperature?\n",
    "temperatures.mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now I know the mean of each column - but what if I want to ask a different question - what if I want to know the midpoint of the temperature per day?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Take the mean across the columns\n",
    "temperatures.mean(axis='columns')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# The default is to take the mean across the rows or 'index'\n",
    "temperatures.mean(axis='index')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that these operations merely return the result, there is no modification of the source data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "temperatures"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Often we do want to persist our results, so we can use them in other calculations. In Pandas, this is easy - simply assign to a column name.\n",
    "\n",
    "![Warning](images/warning.resized.png) Assigning to a dataframe works just like in a dictionary - if the name already exists, then it will overwrite the values!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "temperatures['mean'] = temperatures.mean(axis='columns')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can use this new column in a new calculation. How far away is the mean from the median?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['median_temp'] - temperatures['mean']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that pandas does elementwise operations, so you can also do +, -, / and * and they will work as you expect"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get consumption in 1000's of liters\n",
    "df['consumption'] / 1000"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Saving Data\n",
    "\n",
    "In addition to reading from many datasources, pandas can also write to many datasources. Now that we have cleaned up our data, we would like to export it again, so it's easy to read in.\n",
    "There are a ton of choices, but the 4 I use most are:\n",
    "- to_csv\n",
    "- to_excel\n",
    "- to_parquet\n",
    "- to_sql\n",
    "- to_hdf\n",
    "\n",
    ".to_csv and .to_excel do what we expect, so I want to show off the other three"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SQL\n",
    "to_sql lets us dump the data directly into the database of our choice, great for working with big dataset! Pandas uses sqlalchemy under the hood, so we need to ensure sqlalchemy is installed and specify an engine, so pandas can connect"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "engine = 'sqlite:///beer.db'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_sql('consumption', engine, index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can use SQL to read back in only the parts we are interested in!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "only_weekend = pd.read_sql(\"select * from consumption where weekend = 1\", engine)\n",
    "only_weekend.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Note that slqlite3 doesn't support datetimes as its own datatype - other DBs will do this correctly!\n",
    "only_weekend.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Parquet\n",
    "to_parquet let's us save data as [parquet](https://parquet.apache.org/) file - a binary columnar storage format. Columnar data storage is great for analysis, as we are usually interested in retrieving data by columns, as opposed to rows. Columnar data storage is also easier to compress, giving us storage benefits as well. Parquet is an Apache project and is thus used widely in the Hadoop ecosystem.\n",
    "\n",
    "![warning](images/warning.resized.png) Parquet requires pyarrow to be installed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_parquet('consumption.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "parquet_df = pd.read_parquet('consumption.parquet')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# HDF5\n",
    "HDF5 is another great format for large datasets - it also allows you to specify metadata and other neat tricks. In addition you can ask it to create an index of data columns, allowing you to query it using simple comparisons. It's great when you want to store large datasets, but still want to be able to query subsets of it.\n",
    "\n",
    "![warning](images/warning.resized.png) HDF5 requires pytables to be installed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_hdf('table_data.hdf', 'consumption', format='table', data_columns=True, complevel=9)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_hdf('fixed_data.hdf', 'consumption', complevel=9)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.read_hdf('table_data.hdf', 'consumption', where='weekend == 1')"
   ]
  }
 ],
 "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.10.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}