07_pd/pd.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "# pandas\n",
    "\n",
    "[pandas](https://pandas.pydata.org/) is an open source library for tabular heterogeneous data manipulation. The core structures are `Series` and `DataFrame` which can be seen as a collection of Series.  In addition `pandas` provides the necessary means for data cleaning and preparation. `pandas` uses NumPy array structure  as an extension type with methods for conversion in both directions.\n",
    "\n",
    "&#9888; A major difference between numpy arrays and `pandas` Series and DataFrame is in the way that <tt>pandas</tt> indices are used. In NumPy the index is implicitly assigned $0..(n-1)$ whereas `pandas` Series and DataFrame have similar behaviour but in addition allow labels as indices. In addition the indices are preserved after applying operations.\n",
    "\n",
    "Many parallels can be drawn between <tt>pandas</tt> and `tidyverse` R package. In terms of data structure, Series and DataFrame can be viewed as vectors and data.frame/tibble respectively. Furthermore, in terms of functionality most data manipulation operations available in tidyverse have a counterparts in <tt>pandas</tt>.\n"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "# convention\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "#\n",
    "from numpy.random import default_rng\n",
    "rng = default_rng()"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Series\n",
    "\n",
    "`Series` is a sequence of values, possibly of heterogeneous types. You can create Series with the <tt>pd.Series</tt> function.\n",
    "\n",
    "**Synopsis: &nbsp; &nbsp;**<tt>Series(data=None, index=None, dtype=None, name=None, copy=False)</tt>\n",
    " - data: array, iterable, dict, scalar\n",
    " - index: 1-dimensional array, otherwise $0..(n-1)$\n",
    " - dtype: [data types](https://pandas.pydata.org/docs/user_guide/basics.html#basics-dtypes), otherwise inferred\n",
    " - name: optional\n",
    " - copy: default False, data is not copied but is a reference"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "s = pd.Series([3,5,7])\n",
    "s = pd.Series({'a':3, 'b':5, 'c':7})\n",
    "s = pd.Series([3,5,7], index=['a','b','c'])\n",
    "s = pd.Series([3,5,7])"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "Input data to pd.Series is not copied by default. In the following scenario an update to Series `s` propogates to NumPy array `arr`:"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "arr = np.array(range(3,7+1,2)) # NumPy array [3,7] with step=2\n",
    "s = pd.Series(arr, copy=False) # default copy=False\n",
    "s[1] = -1                      # set value s[1] to -1"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "Series, besides the ordered indices $0..(n-1)$, may also be viewed as a dictionary where values are accessed based on mapped indices to values:"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "s = pd.Series({'a':3, 'b':5, 'c':7})\n",
    "s[1] == s['b']"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "Operations between Series are carried out based on matching indices as opposed to element-wise:"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "s1 = pd.Series({'a':3, 'b':5, 'c':2})\n",
    "s2 = pd.Series({'b':3, 'a':5, 'c':2})\n",
    "s1+s2"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "and they don't have to be the same size:"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "s3 = pd.Series({'b':3, 'a':5, 'c':2, 'd':10}) # there is no matching 'd' in s1 therefore d=NaN\n",
    "s1+s3"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "Index membership:"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "\"b\" in s1 # s1 : {'a':3, 'b':5, 'c':2}"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "In contrast to NumPy arrays, and R vectors, being homogenous containers, Series may take up values of different types:"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "s = pd.Series({'a':3, 'b':5, 'c':'7'})\n",
    "s.dtype\n",
    "[type(v) for v in s]"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Series methods and submodules\n",
    "\n",
    "An exhaustive review of [Series' methods and submodules](https://pandas.pydata.org/docs/reference/series.html#) is beyond the scope of this course. Here we only review several common uses.\n",
    "\n"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "s1 = pd.Series(['apple', 'watermelon', 'orange', 'pear', 'cherry', 'strawberry'],\n",
    "               index=list(\"abcdef\"))\n",
    "s2 = pd.Series(['apple', 'kiwi', 'orange', 'pear', 'cherry', 'grape'],\n",
    "               index=list(\"abcdef\"))\n",
    "s3 = pd.Series(np.log(np.arange(0.1,10,.1)), index=np.arange(0.1,10,.1))\n",
    "\n",
    "s4 = np.array([\"\"])\n",
    "\n",
    "s1.unique()\n",
    "s1.count()\n",
    "s1.compare(s2)\n",
    "s1.filter(['a','b'])\n",
    "s3.plot();\n",
    "s1.drop(['b','f'])\n",
    "s3.apply(lambda x: np.abs(x))"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Timestamp"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "dates = pd.Series(['1-4-1988', '1-1-1987', '1-12-2011', '1-6-2005', '1-5-2005'])\n",
    "tss = pd.to_datetime(dates,format=\"%d-%m-%Y\")\n",
    "tss.min(), tss.max()\n",
    "tss.sort_values()"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "# DataFrame\n",
    "\n",
    "The pandas' DataFrame is a 2-dimensional structure which may be viewed as a collection of Series. It has indices for both dimensions. We will use the terms observations and variables for rows and columns interchangeably. DataFrame, and Series, can hold dimensions $>2$ with the so called `hierarchical indexing` which is beyond the scope of this course.\n",
    "\n",
    "&#9888; We will be working with homogeneous Series in the context of DataFrames.\n",
    "\n",
    "To create a DataFrame use the function pd.DataFrame:\n",
    "\n",
    "**Synopsis: &nbsp; &nbsp;**<tt>DataFrame(data=None, index=None, columns=None, dtype=None, copy=None)</tt>\n",
    "\n",
    "Most arguments are familiar from pd.Series except the additional *columns* with which the indices of the second dimension are controlled.\n"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "df = pd.DataFrame(data=[[3,'a'], [5,'b'], [7,'c']],                    # list, tuple, or np.array\n",
    "                  columns=['x', 'y'])                                  #\n",
    "df = pd.DataFrame({'x': [3,5,7], 'y': ['a','b','c']})                  # dictionary of columns\n",
    "df = pd.DataFrame({3:'a', 5:'b', 7: 'c'}.items(), columns= ['x','y'])  # dictionary of rows"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## DataFrame : read/write\n",
    "\n",
    "You may want to store or share with others the DataFrame you just created. The most common data format to store a DataFrame is comma-separated-values (csv) format. Use `to_csv` method to export a DataFrame and `pd.read_csv` import:"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "df = pd.DataFrame({'x': rng.standard_normal(10), 'y': rng.standard_normal(10)})\n",
    "df.to_csv(\"df.csv\",index=False)   # write df to file 'df.csv', do not include index\n",
    "df = pd.read_csv(\"df.csv\")        # read df.csv into df object"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Inspect content"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "df = pd.DataFrame({'x': rng.standard_normal(10), 'y': rng.standard_normal(10)}) # x and y two random variables\n",
    "\n",
    "df.head()                   # top 5 (default) observation\n",
    "df.tail(2)                  # last 2 observations\n",
    "df.head(5).tail(2)          # composition\n",
    "df.shape                    # size of the dimensions\n",
    "df.size                     # total number of elements\n",
    "df.columns                  # the columns indices/names\n",
    "df.dtypes                   # listing of all columns' types\n",
    "df.describe()               # descriptive summary of all variables"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Select columns\n",
    "\n",
    "### Single column\n",
    "\n",
    "You can select a column from a DataFrame using the square bracket `df[\"column_name\"]` or `df.column_name`. When only one column name is given the result is a Series, with a list of columns the result is a DataFrame:"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "df[\"x\"]   # Series\n",
    "df.x      # <=>  df[\"x\"]\n",
    "df[[\"x\"]] # DataFrame"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "Only `valid python names` can be accessed through dot `.`:"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "pd.DataFrame({'valid_name': [1,2,3], 'another variable':[3,2,1]  }).valid_name"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "### Multiple columns\n",
    "\n",
    "Use a list of indices to select multiple columns:"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "df[['Periods', 'TotalSupply_1']]  # explicit\n",
    "df[df.columns[[1,2]]]             # use indices on df.columns"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Select rows\n",
    "\n",
    "### Using logical criteria\n",
    "\n",
    "Similar to NumPy logical masks we can filter out rows for which the logical condition succeeds. A condition on the variables of a DataFrame returns a logical value for each row in a format of a `Series` object:"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "df = pd.DataFrame({'x': rng.standard_normal(10), 'y': rng.standard_normal(10)}) # x and y two random variables\n",
    "df[((df.x < 0) & (df.y > 0))]  # parentheses are required"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "### Using index : loc method\n",
    "\n",
    "Rows in a Dataframe are by default indexed with $[0,n)$. The `DataFrame` method `loc` can be used in the following forms:\n",
    "\n",
    "- `df.loc[<row-label>]`                : select a row by numeric index\n",
    "- `df.loc[<row-label>,<column-label>]` : select the indexed entry\n",
    "\n",
    "Both row-label and column-label may take values such as, a single label,  list/array of labels, slices, boolean arrays and series. Though these indexing schemes may look similar to NumPy, there are two cautionary remarks:\n",
    "\n",
    "- The labels are not positional indices.\n",
    "- The slices used with `.loc` are inclusive of start and stop, i.e. [0,k].\n"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "df.loc[1]               # [.] row 1 as a Series\n",
    "df.loc[[1]]             # [[.]] row 1 as a DataFrame\n",
    "df.loc[1,'x']           # [.,.] labels\n",
    "df.loc[0:3, 'x':'y']    # [.,.] slices\n",
    "df.loc[df.x > df.y,'x'] # [.,.] boolean"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "**iloc:** Also take a look at the method `iloc` which is similar to `loc` except it only accepts positional integers or ranges for rows and columns indices."
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Update variables\n",
    "\n",
    "DataFrame's columns can be updated with an assignment `=` with or without a row selection:"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "df.x=range(df.shape[0])            # variable size and the size of the new values must match.\n",
    "df.loc[(df.x % 2 ==0),'y'] = None  # set y values to NaN where x is an even value"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "Value update according to a selection should only be done using `.loc` (or `.iloc`) method. For example both selections below are equivalent but only the `.loc` version can be used in an assignment:"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "s1 = df[0::2]['y']      # selection with composition (aka chained)\n",
    "s2 = df.loc[0::2, 'y']  # selection with loc\n",
    "s1.equals(s2)           # s1 == s2\n",
    "df[0::2]['y'] = -2      # warning\n",
    "df.loc[0::2, 'y'] = -2  # valid"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Merge Series and DataFrames\n",
    "\n",
    "To combine DataFrames use the `pd.concat` function:\n",
    "\n",
    "**Synopsis: &nbsp; &nbsp;**<tt>concat(objs, axis=0, ignore_index=False, copy=True)</tt>"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "s1 = pd.Series(list(\"abcd\"))          # ['a', 'b', 'c', 'd']\n",
    "s2 = pd.Series(range(4))              # [0, 4)\n",
    "pd.concat([s1,s2])                    # Series\n",
    "pd.concat([s1,s2], ignore_index=True)  # Series\n",
    "pd.concat([s1,s2], axis=1)            # DataFrame"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "df1 = pd.DataFrame({'a':range(3), 'b':list(\"abc\")})\n",
    "df2 = pd.DataFrame({'c':range(5), 'b':list(\"abcde\"[::-1])})\n",
    "pd.concat([df1,df2], axis=0, join='outer')  # along axis 0\n",
    "pd.concat([df1,df2], axis=1, join='outer')  # along axis 1"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Add row to DataFrame\n",
    "\n",
    "For this we can use the `pd.concat` function:"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "df = pd.DataFrame({'Year': [2021, 2021], 'Month': [11, 12],'Day': [9, 16]})\n",
    "new_row =  pd.DataFrame({'Year': [2023], 'Month': [3],'Day': [20]})\n",
    "pd.concat([df, new_row])"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Missing values\n",
    "\n",
    "Recall the special values None and NaN from the lectures representing no value and not a number. They are of different types and have different properties. In the context of DataFrames we have the notion of missing values, and they can be represented by both.\n",
    "\n",
    "NaN and None types:"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "s = pd.Series([\"0\", float('nan'), np.nan,  2, None])\n",
    "[type(v) for v in s]"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "### Handling missing data\n",
    "\n",
    "Possible actions when dealing with missing data are  *summarise*, *remove* or *replace* missing values.\n",
    "\n",
    "To be able to do any action on missing values you'll need to first find them. DataFrame and Series have the methods `isna` and `isnull` (alias to `isna`) for finding missing values. Both return a logical mask with `True` marking the location of the missing values. We will use `isna` throughout the lectures."
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "s.isna()      # isna: boolean marking missing value\n",
    "s[s.notna()]  # <=> s[~ s.isna()]"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "With `dropna` you may discard all missing from a Series object. With DataFrames you'll have more control in how to discard the missing\n",
    "\n",
    "**Synopsis: &nbsp; &nbsp;**<tt>pandas.DataFrame.dropna(axis=0, how='any', thresh=None, inplace=False)</tt>"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "sample_space = np.arange(10).tolist() + ([np.nan]*2)\n",
    "df = pd.DataFrame(rng.choice(sample_space,25).reshape(5,5))"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "df.dropna(axis=0) # default : drop rows having any missing values\n",
    "df.dropna(axis=1) # drop columns having any missing values\n",
    "df.dropna(axis=1, how='all') # drop columns having only missing values"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "With `fillna` we can replace the missing with values, either fixed or a set of values (Series,DataFrame etc.) according to the indices. We only illustrate scalars here:"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "df.fillna(0)\n",
    "df.fillna(df.mean(axis=0))"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Group operations\n",
    "\n",
    "When the data has categorical variables we may be interested in descriptive statistics on each group. This can be done by first grouping the data with `groupby` method and then summarise on those groups. I'll use the [diamonds](https://ggplot2.tidyverse.org/reference/diamonds.html) dataset for illustration."
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "diamonds = pd.read_csv(\"data/diamonds.csv\") # read diamonds.csv"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "grp = diamonds[diamonds.columns.drop(['color'])].groupby(['cut', 'clarity'], as_index=False) #\n",
    "grp.indices\n",
    "grp.ngroups\n",
    "df = grp.mean()"
   ],
   "metadata": {
    "collapsed": false
   }
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}