4-analysis/4-1-pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### GESIS Fall Seminar in Computational Social Science 2022\n",
    "### Introduction to Computational Social Science with Python\n",
    "# Day 4-1: Introduction to `pandas`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Overview\n",
    "\n",
    "* Creating DataFrames\n",
    "* Accessing and filtering data\n",
    "* Computing summary statistics\n",
    "* Reading and writing data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## The `pandas` package is the foundation of data analysis in Python for Computational Social Scientists\n",
    "* Read/write tabular data, perform dataset calculations, manipulate data, integrate with other popular packages\n",
    "* Similar functionality to analysis with R data.frames, SQL tables, Excel spreadsheets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Creating DataFrames\n",
    "* Series\n",
    "* DataFrames"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Series\n",
    "* One-dimensional array of values with an index and (optionally) a title name.\n",
    "\n",
    "![Pandas Series](figs/01_table_series.svg \"Pandas Series\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# Creating Series\n",
    "\n",
    "# Values\n",
    "s = pd.Series([6, 8, 9, 3])\n",
    "display(s)\n",
    "\n",
    "# With list object\n",
    "l = [3, 1, -5, 2.2, 3]\n",
    "t = pd.Series(l)\n",
    "display(t)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# With manual index and name\n",
    "ix = [100, 101, 102, 103, 104]\n",
    "u = pd.Series(l, index=ix, name='Title Name')\n",
    "display(u)\n",
    "\n",
    "# With dict\n",
    "d = {4561:'Sasha', 68574:'Eren', 9875:'Jean'}\n",
    "v = pd.Series(d)\n",
    "display(v)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### DataFrames\n",
    "* Two-dimensional array of values (a table)\n",
    "* Each column in a DataFrame is a Series\n",
    "\n",
    "![Pandas DataFrame](figs/01_table_dataframe.svg \"Pandas DataFrame\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "# Creating DataFrames\n",
    "\n",
    "# Values\n",
    "df1 = pd.DataFrame({'col1':[6, 8, 9, 3], 'col2':[6, 2, 5, 1]})\n",
    "display(df1)\n",
    "\n",
    "# With manual index\n",
    "l = [3, 1, -5, 2.2, 3]\n",
    "ix = [100, 101, 102, 103, 104]\n",
    "df2 = pd.DataFrame(l, index=ix, columns=['Title Name'])\n",
    "display(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# With Series\n",
    "df3 = pd.DataFrame({'series1':s, 'series2':t})\n",
    "display(df3)\n",
    "\n",
    "# With empty DataFrame and Series\n",
    "# Note the fixed index, and NaN behaviour\n",
    "df4 = pd.DataFrame()\n",
    "df4['series1'] = s\n",
    "df4['series2'] = t\n",
    "df4['series3'] = u\n",
    "display(df4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## 🏋️‍♀️ PRACTICE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# Q1: Create a Series of character names from a Movie / TV show of your choice\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# Q2: Create a similar Series with the index 100, 101, 102, ...\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# Q3: Create similar DataFrame with the index 100, 101, 102, ... and columns Name, Age, Favourite Colour\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Accessing and Filtering Data\n",
    "\n",
    "We can select data based on row(s):\n",
    "![Subset Rows](figs/03_subset_rows.svg \"Subset Rows\")\n",
    "\n",
    "Column(s):\n",
    "![Subset Columns](figs/03_subset_columns.svg \"Subset Columns\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "Or Both:\n",
    "![Subset Both](figs/03_subset_columns_rows.svg \"Subset Both\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# Lets import some more interesting data, on weekly Spotify rankings\n",
    "\n",
    "songsdf = pd.read_csv('data/songs.csv', index_col=0) # We'll do some more later on reading files\n",
    "songnames = songsdf['track_name'].copy()\n",
    "\n",
    "display(songsdf)\n",
    "display(songnames)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# Accessing data in a Series\n",
    "\n",
    "display(songnames[1]) # Brackets [x] with a Series give you the entry at index x\n",
    "display(songnames.loc[1]) # .loc[x] give you the entry at index x\n",
    "display(songnames.iloc[1]) # .iloc[x] give you the entry at integer position x"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# Use ranges and slices\n",
    "\n",
    "display(songnames[[2, 5]])\n",
    "display(songnames.loc[1:3]) # Note the differing slice end behaviour\n",
    "display(songnames.iloc[1:4]) # Note the differing slice end behaviour"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# Accessing data in DataFrames\n",
    "\n",
    "# Columns\n",
    "display(songsdf['artist_names']) # Brackets [x] with a DataFrame returns the column x\n",
    "\n",
    "# Use ranges and slices\n",
    "\n",
    "display(songsdf[['label', 'peak_rank']]) # Double brackets [[x, y, ...]] with a DataFrame returns columns x, y, ...\n",
    "display(songsdf.iloc[:,:2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# Accessing data in DataFrames\n",
    "\n",
    "# Rows\n",
    "display(songsdf.loc[1]) # .loc[x] give you the row(s) at index x\n",
    "display(songsdf.iloc[3]) # .iloc[x] give you the rows(s) at integer position x\n",
    "\n",
    "\n",
    "# Use ranges and slices\n",
    "\n",
    "display(songsdf.loc[2:4])\n",
    "display(songsdf.iloc[2:4])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# Accessing data in DataFrames\n",
    "# We can also use loc and iloc with both rows and columns\n",
    "\n",
    "# Values\n",
    "display(songsdf.loc[1, 'artist_names'])\n",
    "\n",
    "# Use ranges and slices\n",
    "display(songsdf.loc[2:4, 'track_name'])\n",
    "display(songsdf.loc[:2, ['track_name', 'peak_rank']])\n",
    "display(songsdf.iloc[:2, 1:3])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### We can edit values in Series and DataFrames too"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "# Edit a single value\n",
    "\n",
    "songnames.loc[5] = \"Running Up That Hill\"\n",
    "display(songnames)\n",
    "\n",
    "display(songsdf)\n",
    "\n",
    "songsdf.loc[5, 'track_name'] = \"A Deal With God\"\n",
    "display(songsdf)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# Change an entire column\n",
    "\n",
    "songsdf['peak_rank'] = 500\n",
    "display(songsdf)\n",
    "\n",
    "songsdf['peak_rank'] = np.random.poisson(40, size=200)\n",
    "display(songsdf)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# Quick reload of the data to revert any changes made\n",
    "\n",
    "songsdf = pd.read_csv('data/songs.csv', index_col=0)\n",
    "songnames = songsdf['track_name'].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### And also filter based on certain conditions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "display(songsdf[songsdf['peak_rank'] == 1])\n",
    "display(songsdf[(songsdf['label'] == 'Columbia') & (songsdf['peak_rank'] < 10)])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## 🏋️‍♀️ PRACTICE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# Q4: Show the artist name and track name of the songs ranked 101-110 (inclusive)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# Q5: Show the track names of the songs by Harry Styles\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# Q6: Show the artist name, track name, peak rank, and weeks on chart of the songs which peaked at number 1 \n",
    "# OR are from the Republic Records label\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Computing Summary Statistics\n",
    "* Lots of useful `pandas` methods\n",
    "* `.min()`, `.max()`, `.idxmax()`, `.mean()`, `.describe()`, `.groupby()`, etc..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "display(songsdf.min())\n",
    "display(songsdf['peak_rank'].idxmax())\n",
    "display(songsdf.loc[songsdf['peak_rank'].idxmax()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# Describe for simple stats about numeric columns\n",
    "display(songsdf.describe())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "# Value_counts counts the number of times an entry appears in a column\n",
    "display(songsdf['artist_names'].value_counts())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### `groupby()` is a useful operation for aggregating entries and getting summary stats of groups\n",
    "\n",
    "<center>songsdf ----------> .groupby('artist_names') ----------> ['streams'].sum()</center>\n",
    "\n",
    "![groupby](figs/06_groupby.svg \"groupby\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# Let's import some more data on number of streams\n",
    "songsdf_data = pd.read_csv('data/songs_data.csv', index_col=0)\n",
    "display(songsdf_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# Group tracks by artist name, sum the streams per artist, (and sort the result)\n",
    "\n",
    "display(songsdf_data.groupby('artist_names')['streams'].sum())\n",
    "display(songsdf_data.groupby('artist_names')['streams'].sum().sort_values(ascending=False))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## 🏋️‍♀️ PRACTICE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# Q7: Calculate the average number of streams\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# Q8: Calculate the number of unique artist names (do not worry about multiple artists featured on one track)\n",
    "# Hint, search the web for the pandas documentation for the n unique elements in a series...\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# Q9: Find the artist with the highest total streams, and save it as a string variable `topartist`\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Reading and Writing Data\n",
    "* `pandas` is able to read and write data to/from many common file formats.\n",
    "* Allows you to save data as you work, and import/export data from/to other analysis software."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "Standard syntax depending on format `[...]`:\n",
    "\n",
    "`df = pd.read_[...]('filename')` and `df.to_[...]('filename')`\n",
    "\n",
    "![io](figs/02_io_readwrite.svg \"io\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### CSVs\n",
    "* Be aware of how the index and column titles are handled.\n",
    "* Other separators ('delimiters') also supported (tabs, whitespace, etc.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "# Reading\n",
    "csv_df = pd.read_csv('data/songs.csv')\n",
    "display(csv_df)\n",
    "\n",
    "csv_df = pd.read_csv('data/songs.csv', index_col=0)\n",
    "display(csv_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# Writing\n",
    "csv_df.to_csv('songs (copy).csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### JSON\n",
    "* We often acquire JSON data from API usage.\n",
    "* Not all JSON files are 'normalized' to a rectangular table format, so you may encounter errors or unexpected behaviour."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "# Reading\n",
    "json_df = pd.read_json('data/songs_data.json')\n",
    "display(json_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'results': [{'gender': 'male',\n",
       "   'name': {'title': 'Mr', 'first': 'Valtteri', 'last': 'Toro'},\n",
       "   'location': {'street': {'number': 5081, 'name': 'Satakennankatu'},\n",
       "    'city': 'Raisio',\n",
       "    'state': 'North Karelia',\n",
       "    'country': 'Finland',\n",
       "    'postcode': 84888,\n",
       "    'coordinates': {'latitude': '-26.1006', 'longitude': '-113.8609'},\n",
       "    'timezone': {'offset': '-3:30', 'description': 'Newfoundland'}},\n",
       "   'email': 'valtteri.toro@example.com',\n",
       "   'login': {'uuid': 'ee58cb5d-29f9-4a49-8151-444a80845356',\n",
       "    'username': 'goldencat327',\n",
       "    'password': 'ametuer',\n",
       "    'salt': 'jL7JPsKp',\n",
       "    'md5': '56a3eb4472291270e2dd27bccecbb395',\n",
       "    'sha1': '3a0c0dc92ed6024a2ccbae4bb13d618585ae5267',\n",
       "    'sha256': '5394af536455de6051244c72cda704da711effeb2c54cd4c156d70ecf5418a1e'},\n",
       "   'dob': {'date': '1958-12-14T04:06:47.397Z', 'age': 63},\n",
       "   'registered': {'date': '2008-02-28T04:35:45.759Z', 'age': 14},\n",
       "   'phone': '06-052-323',\n",
       "   'cell': '044-904-22-23',\n",
       "   'id': {'name': 'HETU', 'value': 'NaNNA253undefined'},\n",
       "   'picture': {'large': 'https://randomuser.me/api/portraits/men/67.jpg',\n",
       "    'medium': 'https://randomuser.me/api/portraits/med/men/67.jpg',\n",
       "    'thumbnail': 'https://randomuser.me/api/portraits/thumb/men/67.jpg'},\n",
       "   'nat': 'FI'}],\n",
       " 'info': {'seed': '307fd3568651d99c',\n",
       "  'results': 1,\n",
       "  'page': 1,\n",
       "  'version': '1.4'}}"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Reading non-normalized json data\n",
    "import json \n",
    "import pandas as pd\n",
    "with open('data/randomusers.json', 'r') as f:\n",
    "    randomuser = json.load(f)\n",
    "\n",
    "pd.json_normalize(randomuser['results'])\n",
    "\n",
    "randomuser"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "# Writing\n",
    "songsdf.to_json('songs.json')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### HTML\n",
    "* Can read HTML files, strings, or URLs directly.\n",
    "* Parsing HTML tables can be complicated, especially when there are merged cells, so it's worth checking over the values after import."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# Reading\n",
    "# pd.read_html reads all tables on the webpage and outputs them to a list, we want the first table\n",
    "\n",
    "# Read html file\n",
    "html_list_1 = pd.read_html('data/PL_table.html')\n",
    "display(html_list_1[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# Read html string\n",
    "table_string = \"\"\" <table>\n",
    "  <tr>\n",
    "    <th>Language</th>\n",
    "    <th>Released</th>\n",
    "    <th>Latest Version No.</th>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>Python</td>\n",
    "    <td>1991</td>\n",
    "    <td>3.10.6</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>R</td>\n",
    "    <td>1993</td>\n",
    "    <td>4.2.1</td>\n",
    "  </tr>\n",
    "</table> \"\"\"\n",
    "html_list_2 = pd.read_html(table_string)\n",
    "display(html_list_2[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# Read html from url\n",
    "html_list_3 = pd.read_html('https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_population')\n",
    "display(html_list_3[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# Writing\n",
    "\n",
    "html_list_3[0].to_html('countriesdf.html')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### HDF\n",
    "* Suitable for large, complex tables.\n",
    "* Typically smaller files (when we have large tables), and faster read/write."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "# Reading\n",
    "\n",
    "hdf_df = pd.read_hdf('data/pageviews_2021.h5', key='df')\n",
    "display(hdf_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "# Writing (note the different keys used)\n",
    "songsdf.to_hdf('songs.h5', key='songs')\n",
    "songsdf_data.to_hdf('songs.h5', key='songs_data')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# Reading again\n",
    "songsdf = pd.read_hdf('songs.h5', key='songs')\n",
    "songsdf_data = pd.read_hdf('songs.h5', key='songs_data')\n",
    "\n",
    "display(songsdf)\n",
    "display(songsdf_data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### Many more, including xlsx, sql, pickle, ..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## 🏋️‍♀️ PRACTICE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# Q10: Read the file 'PL_table.tsv' and save it as 'PL_table.json'\n",
    "# Hint: what might \".tsv\" stand for...\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# Q11: Read any table from Wikipedia, check it is formatted correctly, and save it as an hdf file\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# Q12: Read the file 'pageviews_2021.h5' with key 'df', subset it so that only includes rows from August\n",
    "# and save it as a csv.\n"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "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.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}