3-pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Pandas](https://pandas.pydata.org), built on top of [NumPy](https://numpy.org), is the main Python package for manipulating data. \n",
    "\n",
    "The outline is the following:\n",
    "* Series\n",
    "* Dataframes\n",
    "* Operations\n",
    "* Missing data\n",
    "* Concatenation\n",
    "* Merge\n",
    "* Grouping\n",
    "* Read / write"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-20T11:19:47.854184Z",
     "start_time": "2019-05-20T11:19:47.318740Z"
    }
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "## Series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 1D array\n",
    "age = pd.Series([27, 42, 33, 51])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "age"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "age[2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "age.dtype"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "age.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "age.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "age.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    " 1 / age"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# performance\n",
    "a = pd.Series(np.arange(1, 10**4))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time \n",
    "b = 1 / a"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "b = np.zeros(len(a))\n",
    "for i in range(len(a)):\n",
    "    b[i] = 1 / a[i]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "b = a.map(lambda x: 1 / x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "b = 1 / a.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-20T11:19:48.091504Z",
     "start_time": "2019-05-20T11:19:48.082662Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "# 1D array with index\n",
    "age = pd.Series([27, 42, 33, 51], index=['Albert', 'Barbara', 'Cathy', 'David'], name='Age')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "age"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-20T11:19:48.128566Z",
     "start_time": "2019-05-20T11:19:48.119470Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "age.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# no write access!\n",
    "try:\n",
    "    age.index[1] = 'Emilie'\n",
    "except:\n",
    "    print('This is not allowed.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# from dictionary\n",
    "age = pd.Series({'Albert': 27, 'Barbara': 42, 'Cathy': 33, 'David': 51}, name='Age')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "age"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# to dictionary\n",
    "age.to_dict()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-20T11:19:48.137381Z",
     "start_time": "2019-05-20T11:19:48.131055Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "# indexing\n",
    "age['Albert']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-20T11:19:48.144055Z",
     "start_time": "2019-05-20T11:19:48.139791Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "age[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# modification\n",
    "age['Albert'] = 28"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "age[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# insertion\n",
    "age['Zoe'] = 40"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "age"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# deletion\n",
    "age.drop('Cathy')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-20T11:19:48.154477Z",
     "start_time": "2019-05-20T11:19:48.145841Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "# slicing: final index included!\n",
    "age['Barbara':'David']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-20T11:19:48.165256Z",
     "start_time": "2019-05-20T11:19:48.156239Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "age[1:3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "age['barbara':'albert']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# boolean mask\n",
    "age[age <= 40]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 1D array with integer index\n",
    "age = pd.Series([27, 42, 33, 51], index=[10, 20, 30, 40])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "age"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# indexing\n",
    "age[20]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    age[0]\n",
    "except:\n",
    "    print(\"This index is not available.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# slicing based on implicit indexing\n",
    "age[1:4]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# slicing based on explicit indexing\n",
    "age.loc[10:30]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# slicing based on implicit indexing\n",
    "age.iloc[1:4]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "age.iloc[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# advanced indexing\n",
    "age = pd.Series({'Albert': 27, 'Barbara': 42, 'Cathy': 33, 'David': 51})\n",
    "age[['Barbara', 'Albert', 'Cathy']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# histogram\n",
    "values = np.random.randn(200)\n",
    "s = pd.Series(values)\n",
    "s.hist();"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# categories\n",
    "choice = np.random.choice(['Yes', 'No'], 100)\n",
    "s = pd.Series(choice, dtype='category')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "s"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "s.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# cut\n",
    "s = pd.Series(values)\n",
    "pd.cut(s, 3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.cut(s, 3).value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.cut(s, 3).value_counts(sort=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.cut(s, [-np.inf, -1, 0, 1, np.inf]).value_counts(sort=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# time index\n",
    "dates = pd.date_range(start='2020/01/01', end='2020/12/31')\n",
    "dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "values = np.cumsum(np.random.randn(len(dates)))\n",
    "ts = pd.Series(values, index=dates)\n",
    "ts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ts.plot();"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ts['2020-03-01':'2020-03-31'].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ts['2020-03'].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ts[:'2020-03-31'].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# shift\n",
    "ts.shift(30).dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# delta over 30 days\n",
    "(ts.shift(30).dropna() - ts.iloc[:-30]).plot();"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# time elapsed in days\n",
    "(dates - dates[30]).days"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "# DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# series sharing the same index\n",
    "age = pd.Series({'Albert': 27, \n",
    "                 'Barbara': 42, \n",
    "                 'Cathy': 33, \n",
    "                 'David': 51})\n",
    "gender = pd.Series({'Albert': 'M', \n",
    "                    'Barbara': 'F', \n",
    "                    'Cathy': 'F', \n",
    "                    'David': 'M'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-20T11:19:48.262026Z",
     "start_time": "2019-05-20T11:19:48.248305Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "df = pd.DataFrame({'Age': age, 'Gender': gender})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-20T11:19:48.272166Z",
     "start_time": "2019-05-20T11:19:48.264478Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-20T11:19:48.281907Z",
     "start_time": "2019-05-20T11:19:48.274577Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-20T11:19:48.292929Z",
     "start_time": "2019-05-20T11:19:48.284143Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-20T11:19:48.301831Z",
     "start_time": "2019-05-20T11:19:48.295306Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "df.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-20T11:19:48.310085Z",
     "start_time": "2019-05-20T11:19:48.304929Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "df.size"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# transpose\n",
    "df.T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.T.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-20T11:19:48.321746Z",
     "start_time": "2019-05-20T11:19:48.312699Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "# indexing -> columns\n",
    "df['Age']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.Age"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.Age.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.Gender.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-20T11:19:48.336780Z",
     "start_time": "2019-05-20T11:19:48.323760Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "# slicing -> rows\n",
    "df['Albert':'Cathy']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    df['Albert']\n",
    "except:\n",
    "    print(\"This is not a column!\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-20T11:19:48.354612Z",
     "start_time": "2019-05-20T11:19:48.345825Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "df.loc['Albert']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# insertion\n",
    "df.loc['Zoe'] = (23, 'F')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# deletion\n",
    "df.drop('Cathy')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# explicit index\n",
    "df.loc['Albert', 'Age']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# implicit index\n",
    "df.iloc[0, 0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# mixed index\n",
    "df.iloc[0]['Age']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc['Albert'][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# mask\n",
    "df.loc[df.Age > 30, 'Gender']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# sort\n",
    "df.sort_values(by=['Gender', 'Age'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# advanced indexing\n",
    "df.loc[['Barbara', 'David', 'Cathy']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# reset index\n",
    "df.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.reset_index()\n",
    "df.rename(columns={\"index\": \"Name\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# histogram\n",
    "values1 = np.random.randn(100)\n",
    "values2 = np.random.randn(100) + 2\n",
    "values3 = np.random.choice(['Y', 'N'], 100)\n",
    "df = pd.DataFrame({'A': values1, 'B': values2, 'C': values3})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.hist();"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# time index\n",
    "dates = pd.date_range('1/1/2021', periods=100)\n",
    "values1 = np.cumsum(np.random.randn(100))\n",
    "values2 = np.cumsum(np.random.randn(100))\n",
    "df = pd.DataFrame({'A': values1, 'B': values2}, index=dates)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.plot()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Operations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "height = pd.Series({'Albert': 1.8, \n",
    "                    'Barbara': 1.6, \n",
    "                    'Cathy': 1.7, \n",
    "                    'David': 1.9})\n",
    "weight = pd.Series({'Albert': 75, \n",
    "                    'Barbara': 52, \n",
    "                    'Cathy': 61, \n",
    "                    'David': 85})\n",
    "df = pd.DataFrame({'Height': height, 'Weight': weight})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "1 / df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "np.cumsum(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df / np.sum(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df.apply(lambda x: x / np.sum(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['BMI'] = df.Weight / df.Height**2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = np.round(df, 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Missing data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# series\n",
    "age = pd.Series({'Albert': 27, 'Barbara': None, 'Cathy': 33, 'David': 51})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# observe the type\n",
    "age"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "1 / age"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "age.isnull()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "age.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "age.fillna(0).astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# forward fill (with previous value)\n",
    "age.fillna(method='ffill')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# dataframe\n",
    "height = pd.Series({'Albert': 1.8, \n",
    "                    'Barbara': 1.6, \n",
    "                    'Cathy': None, \n",
    "                    'David': 1.9})\n",
    "weight = pd.Series({'Albert': None, \n",
    "                    'Barbara': 52, \n",
    "                    'Cathy': 61, \n",
    "                    'David': 85})\n",
    "df = pd.DataFrame({'Height': height, 'Weight': weight})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.isnull()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.fillna(method='bfill')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Concatenation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "s1 = pd.Series([27, 42, 33], index=['Albert', 'Barbara', 'Cathy'])\n",
    "s2 = pd.Series([23, 40], index=['Zoe', 'David'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat([s1, s2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "s1.append(s2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# duplicates\n",
    "s3 = pd.Series([29, 51, 40], index=['Albert', 'Zoe', 'David'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat([s1, s3])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat([s1, s3], ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# dataframes\n",
    "df1 = pd.DataFrame({'Age': [27, 42, 33], \n",
    "                    'Gender': ['M', 'F', 'F']}, \n",
    "                    index=['Albert', 'Barbara', 'Cathy'])\n",
    "df2 = pd.DataFrame({'Age': [23, 40], \n",
    "                    'Gender': ['F', 'M']}, \n",
    "                   index=['Zoe', 'David'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat([df1, df2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1.append(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# new columns\n",
    "df3 = pd.DataFrame({'Height': [1.8, 1.6, 1.7], \n",
    "                    'Weight': [85, 50, 60]}, \n",
    "                   index=['Albert', 'Barbara', 'Cathy'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat([df1, df3], axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# simple merge\n",
    "df1 = pd.DataFrame({'Name': ['Albert', 'Barbara', 'Cathy'], \n",
    "                    'Age': [27, 42, 33]})\n",
    "df2 = pd.DataFrame({'Name': ['Cathy', 'Barbara', 'Albert'], \n",
    "                    'Gender': ['F', 'F', 'M']})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(df1, df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# missing entries\n",
    "df1 = pd.DataFrame({'Name': ['Albert', 'Barbara', 'Cathy'], \n",
    "                    'Age': [27, 42, 33]})\n",
    "df2 = pd.DataFrame({'Name': ['Cathy', 'Zoe', 'Albert'], \n",
    "                    'Gender': ['F', 'F', 'M']})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(df1, df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(df1, df2, how='outer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(df1, df2, how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# several columns\n",
    "df1 = pd.DataFrame({'Name': ['Albert', 'Barbara', 'Cathy'], \n",
    "                    'Gender': ['M', 'F', 'F'], \n",
    "                    'Height': [1.8, 1.6, 1.7]})\n",
    "df2 = pd.DataFrame({'Name': ['Cathy', 'Barbara', 'David'], \n",
    "                    'Gender': ['F', 'F', 'M'], \n",
    "                    'Weight': [60, 50, 70]})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(df1, df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# discrepency\n",
    "df1 = pd.DataFrame({'Name': ['Albert', 'Barbara', 'Pat'], \n",
    "                    'Gender': ['M', 'F', 'F'], \n",
    "                    'Height': [1.8, 1.6, 1.7]})\n",
    "df2 = pd.DataFrame({'Name': ['Pat', 'Barbara', 'David'], \n",
    "                    'Gender': ['M', 'F', 'M'], \n",
    "                    'Weight': [60, 50, 70]})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(df1, df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(df1, df2, how='outer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# explicit columns\n",
    "df1 = pd.DataFrame({'Name': ['Albert', 'Barbara', 'Cathy'], \n",
    "                    'Gender': ['M', 'F', 'F'], \n",
    "                    'Height': [1.8, 1.6, 1.7]})\n",
    "df2 = pd.DataFrame({'Name': ['Cathy', 'Barbara', 'David'], \n",
    "                    'Sex': ['F', 'F', 'M'], \n",
    "                    'Weight': [60, 50, 70]})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(df1, df2, left_on=['Name', 'Gender'], right_on=['Name', 'Sex'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(df1, df2, left_on=['Name', 'Gender'], right_on=['Name', 'Sex']).drop('Sex', axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# duplicate\n",
    "df1 = pd.DataFrame({'Name': ['Albert', 'Barbara', 'Cathy'], 'City': ['London', 'Paris', 'Paris']})\n",
    "df2 = pd.DataFrame({'City': ['London', 'Paris'], 'Country': ['England', 'France']})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(df1, df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# join\n",
    "df1 = pd.DataFrame({'Name': ['Albert', 'Barbara', 'Cathy'],\n",
    "                    'Age': [43, 21, 52]})\n",
    "df2 = pd.DataFrame({'Height': [1.8, 1.6, 1.7], \n",
    "                    'Weight': [60, 50, 70]})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1.join(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# join on column\n",
    "df1 = pd.DataFrame({'Name': ['Barbara', 'Albert', 'Cathy'],\n",
    "                    'Age': [43, 21, 52]})\n",
    "df2 = pd.DataFrame({'Name': ['Albert', 'Barbara', 'Cathy'],\n",
    "                    'Height': [1.8, 1.6, 1.7], \n",
    "                    'Weight': [60, 50, 70]})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1.join(df2.set_index('Name'), on='Name')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Multi-index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "n_dates = 5\n",
    "dates = pd.date_range('1/1/2021', periods=n_dates)\n",
    "df = pd.DataFrame({'Date': dates, 'Rate': np.random.rand(n_dates), 'Currency': 'USD'})\n",
    "df = df.append(pd.DataFrame({'Date': dates, 'Rate': np.random.rand(n_dates), 'Currency': 'EUR'}))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.set_index(['Currency', 'Date'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Rate']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[('EUR', '2021-01-04')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc['EUR']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# cross section\n",
    "df.xs('EUR', level='Currency')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# cross section\n",
    "df.xs('2021-01-04', level='Date')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.unstack()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.swaplevel()\n",
    "df = df.unstack()\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.stack()\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Grouping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "n_dates = 100\n",
    "dates = pd.date_range('1/1/2021', periods=n_dates)\n",
    "df = pd.DataFrame()\n",
    "for bank in ['A', 'B']:\n",
    "    for currency in ['EUR', 'USD']:\n",
    "        rates = np.cumprod(np.random.rand(n_dates) + 0.5)\n",
    "        exposures = 100 + 10 * np.random.randn(n_dates)\n",
    "        df = df.append(pd.DataFrame({'Date': dates, \n",
    "                                     'Rate': rates, \n",
    "                                     'Exposure': exposures, \n",
    "                                     'Currency': currency, \n",
    "                                     'Bank': bank}))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.groupby('Currency')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.groupby('Currency').get_group('EUR')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# boolean mask (prefered)\n",
    "df[df.Currency == 'EUR']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.groupby('Currency').mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.groupby('Currency').aggregate({'Rate': np.mean, 'Exposure': np.max})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.groupby('Currency').aggregate({'Rate': [np.mean, np.median], 'Exposure': [np.min, np.max]})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df.groupby(['Currency', 'Bank']).mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.groupby(['Currency', 'Bank'])['Rate'].mean().unstack()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# equivalent\n",
    "df.pivot_table('Rate', index='Currency', columns='Bank')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.pivot_table('Rate', index='Currency', columns='Bank', margins=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.pivot_table(['Rate', 'Exposure'], \n",
    "               index='Currency', \n",
    "               columns='Bank', \n",
    "               aggfunc={'Rate': 'mean', 'Exposure':'max'}, \n",
    "               margins=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# loop on groups\n",
    "for bank, df_bank in df.groupby('Bank'):\n",
    "    print(bank)\n",
    "    print(df_bank.mean(numeric_only=True))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.groupby('Currency').transform(lambda x: x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.groupby('Currency').transform(lambda x: 1/x)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Read / write"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "age = pd.Series({'Albert': 27, \n",
    "                 'Barbara': 42, \n",
    "                 'Cathy': 33, \n",
    "                 'David': 51})\n",
    "gender = pd.Series({'Albert': 'M', \n",
    "                    'Barbara': 'F', \n",
    "                    'Cathy': 'F', \n",
    "                    'David': 'M'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame({'Age': age, 'Gender': gender})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-20T11:19:48.747435Z",
     "start_time": "2019-05-20T11:19:48.739130Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "# to csv\n",
    "df.to_csv('toto.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-20T11:19:48.765695Z",
     "start_time": "2019-05-20T11:19:48.750022Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "# from csv\n",
    "df = pd.read_csv('toto.csv', index_col=0)\n",
    "df"
   ]
  }
 ],
 "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.8.3"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": false,
   "sideBar": true,
   "skip_h1_title": true,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}