---
title: "pandas"
subtitle: "Lecture 07"
author: "Dr. Colin Rundel"
footer: "Sta 663 - Spring 2025"
format:
  revealjs:
    theme: slides.scss
    transition: fade
    slide-number: true
    self-contained: true
execute:
  echo: true
---

```{r config}
#| include: false
options(
  width=80
)

local({
  hook_old <- knitr::knit_hooks$get("error")  # save the old hook
  knitr::knit_hooks$set(error = function(x, options) {
    x = sub("## \n## Detailed traceback:\n.*$", "", x)
    x = sub("Error in py_call_impl\\(.*?\\)\\: ", "", x)
    hook_old(x, options)
  })
})
```

```{python setup, include=FALSE}
import scipy
import numpy as np
import matplotlib.pyplot as plt
import pandas as pd

np.set_printoptions(edgeitems=3, linewidth=180)
```


## pandas

pandas is an implementation of data frames in Python - it takes much of its inspiration from R and NumPy.

> pandas aims to be the fundamental high-level building block for doing practical, real world data analysis in Python. Additionally, it has the broader goal of becoming the most powerful and flexible open source data analysis / manipulation tool available in any language.



. . .

Key features:

* DataFrame and Series (column) object classes

* Reading and writing tabular data

* Data munging (filtering, grouping, summarizing, joining, etc.)

* Data reshaping


# Series

## Series

The columns of a DataFrame are constructed using the `Series` class - these are a 1d array like object containing values of the same type (similar to an numpy array).

:::: {.columns .small}
::: {.column width='50%'}
```{python}
pd.Series([1,2,3,4])
pd.Series(["C","B","A"])
pd.Series([True])
```
:::

::: {.column width='50%'}
```{python}
pd.Series(range(5))
pd.Series([1,"A",True])
```
:::
::::


## Series methods

Once constructed the components of a series can be accessed via `array` and `index` attributes.

```{python}
s = pd.Series([4,2,1,3])
```

:::: {.columns}
::: {.column width='50%'}
```{python}
s
```
:::

::: {.column width='50%'}
```{python}
s.array
s.index
```
:::
::::

##

An index (row names) can also be explicitly provided when constructing a Series,

```{python}
t = pd.Series([4,2,1,3], index=["a","b","c","d"])
```

:::: {.columns}
::: {.column width='50%'}
```{python}
t
```
:::

::: {.column width='50%'}
```{python}
t.array
t.index
```
:::
::::


## Series + NumPy

Series objects are compatible with NumPy like functions (i.e. vectorized)

```{python}
t = pd.Series([4,2,1,3], index=["a","b","c","d"])
```

:::: {.columns}
::: {.column width='50%'}
```{python}
t + 1
t / 2 + 1
```
:::

::: {.column width='50%'}
```{python}
np.log(t)
np.exp(-t**2/2)
```
:::
::::



## Series indexing

Series can be indexed in the same was as NumPy arrays with the addition of being able to use index label(s) when selecting elements.

::: {.small}
```{python}
t = pd.Series([4,2,1,3], index=["a","b","c","d"])
```

:::: {.columns}
::: {.column width='50%'}
```{python}
t[1]
t[[1,2]]
t["c"]
t[["a","d"]]
```
:::

::: {.column width='50%'}
```{python}
t[t == 3]
t[t % 2 == 0]
t["d"] = 6
t
```
:::
::::

:::

## Index alignment

When performing operations with multiple series, generally pandas will attempt to align the operation by the index values,

::: {.small}
```{python}
m = pd.Series([1,2,3,4], index = ["a","b","c","d"])
n = pd.Series([4,3,2,1], index = ["d","c","b","a"])
o = pd.Series([1,1,1,1,1], index = ["b","d","a","c","e"])
```
:::

. . .

:::: {.columns .small}
::: {.column width='50%'}
```{python}
m + n
```

```{python}
n + m
```
:::

::: {.column width='50%' .fragment}
```{python}
n + o
```
:::
::::


## Series and dicts

Series can also be constructed from dictionaries, in which case the keys are used as the index,

::: {.small}
```{python}
d = {"anna": "A+", "bob": "B-", "carol": "C", "dave": "D+"}
pd.Series(d)
```
:::

. . .

Index order will follow key order, unless overriden by `index`,

::: {.small}
```{python}
pd.Series(d, index = ["dave","carol","bob","anna"])
```
:::


## Missing values

Pandas encodes missing values using NaN (mostly),

:::: {.columns .xsmall}
::: {.column width='50%'}
```{python}
s = pd.Series(
  {"anna": "A+", "bob": "B-", 
   "carol": "C", "dave": "D+"}, 
  index = ["erin","dave","carol","bob","anna"]
)
```

```{python}
s
pd.isna(s)
```

:::

::: {.column width='50%' .fragment}
```{python}
s = pd.Series(
  {"anna": 97, "bob": 82, 
   "carol": 75, "dave": 68}, 
  index = ["erin","dave","carol","bob","anna"],
  dtype = 'int64'
)
```

```{python}
s
pd.isna(s)
```
:::
::::

## Aside - why `np.isna()`? {.smaller}

```{python}
s = pd.Series([1,2,3,None])
s
```

:::: {.columns}
::: {.column width='50%'}
```{python}
pd.isna(s)
s == np.nan
```
:::

::: {.column width='50%' .fragment}
```{python}
np.nan == np.nan
np.nan != np.nan
np.isnan(np.nan)
np.isnan(0)
```
:::
::::

## Missing via `none`

In some cases `none` can also be used as a missing value, for example:

:::: {.columns .small}
::: {.column width='50%'}
```{python}
pd.Series([1,2,3,None])
pd.Series([True,False,None])
```
:::

::: {.column width='50%' .fragment}
```{python}
pd.isna( pd.Series([1,2,3,None]) )
pd.isna( pd.Series([True,False,None]) )
```
:::
::::

This can have a side effect of changing the dtype of the series.


## Native NAs

If instead of using base dtypes we use Pandas' built-in dtypes we get "native" support for missing values,

:::: {.columns .small}
::: {.column width='50%'}
```{python}
pd.Series(
  [1,2,3,None], 
  dtype = pd.Int64Dtype()
)
```
:::

::: {.column width='50%'}
```{python}
pd.Series(
  [True, False,None], 
  dtype = pd.BooleanDtype()
)
```
:::
::::


## String series

Series containing strings can their strings accessed via the `str` attribute,

::: {.xsmall}
```{python}
s = pd.Series(["the quick", "brown fox", "jumps over", "a lazy dog"])
```
:::


:::: {.columns .small}
::: {.column width='50%'}
```{python}
s
s.str.upper()
```
:::

::: {.column width='50%' .fragment}
```{python}
s.str.split(" ")
s.str.split(" ").str[1]
```
:::
::::

::: {.fragment .xsmall}
```{python}
#| error: true
pd.Series([1,2,3]).str
```
:::


## Categorical Series

:::: {.columns .xsmall}
::: {.column width='50%'}
```{python}
pd.Series(
  ["Mon", "Tue", "Wed", "Thur", "Fri"]
)
```
:::

::: {.column width='50%'}
```{python}
pd.Series(
  ["Mon", "Tue", "Wed", "Thur", "Fri"],
  dtype="category"
)
```
:::
::::


::: {.xsmall}
```{python}
pd.Series(
  ["Mon", "Tue", "Wed", "Thur", "Fri"], 
  dtype=pd.CategoricalDtype(ordered=True)
)
```
:::

## Category orders

::: {.small}
```{python}
pd.Series(
  ["Tue", "Thur", "Mon", "Sat"], 
  dtype=pd.CategoricalDtype(
    categories=["Mon", "Tue", "Wed", "Thur", "Fri"], 
    ordered=True
  )
)
```
:::

# DataFrames

## DataFrame

::: {.columns .medium}
::: {.column width=33%}
* Just like R a DataFrame is a collection of vectors (Series) with a common length (and a common index)

* Column dtypes can be heterogeneous

* Columns have names stored in the `columns` index.

* It can be useful to think of a dictionary of Series objects where the keys are the column names.
:::
::: {.column width=66% .xsmall}
```{python}
iris = pd.read_csv("data/iris.csv")
type(iris)
iris
```
:::
:::



## Constructing DataFrames

We just saw how to read a DataFrame via `read_csv()`, `DataFrames` can also be constructed via `DataFrame()`, in general this is done using a dictionary of columns / `Series`:

::: {.small}
```{python}
n = 5
d = {
  "id":     np.random.randint(100, 999, n),
  "weight": np.random.normal(70, 20, n),
  "height": np.random.normal(170, 15, n),
  "date":   pd.date_range(start='2/1/2022', periods=n, freq='D')
}
```
:::

::: {.small .fragment}
```{python}
df = pd.DataFrame(d); df
```
:::

::: {.aside}
See more IO functions [here](https://pandas.pydata.org/docs/reference/io.html)
:::

## DataFrame from nparray

2d ndarrays can also be used to construct a `DataFrame` - generally it is a good idea to provide column and row names (indexes)

:::: {.columns .small}
::: {.column width='50%'}
```{python}
pd.DataFrame(
  np.diag([1,2,3]),
  columns = ["x","y","z"]
)
```

```{python}
pd.DataFrame(
  np.diag([1,2,3]),
  index = ["x","y","z"]
)
```
:::

::: {.column width='50%'}
```{python}
pd.DataFrame(
  np.tri(5,3,-1),
  columns = ["x","y","z"],
  index = ["a","b","c","d","e"]
)
```
:::
::::

## DataFrame properties

:::: {.columns .xsmall}
::: {.column width='50%'}
```{python}
df.size
df.shape
df.info()
```
:::

::: {.column width='50%'}
```{python}
df.dtypes
df.columns
df.index
df.axes
```
:::
::::


## DataFrame indexing


:::: {.columns .small}
::: {.column width='50%'} 
### Selecting a column:

Columns can be selected by name or via `.` accessor,

```{python error=TRUE}
df[0]
df["id"]
df.id
```
:::

::: {.column width='50%' .fragment} 
### Selecting rows: 

a single slice is assumed to refer to the rows

```{python}
df[1:3]
df[0::2]
```
:::
::::

## Indexing by position

:::: {.columns .small}
::: {.column width='50%'}
```{python}
df.iloc[1]
df.iloc[[1]]
df.iloc[0:2]
df.iloc[1:3,1:3]
```
:::

::: {.column width='50%' .fragment}
```{python}
df.iloc[0:3, [0,3]]
df.iloc[0:3, [True, True, False, False]]
df.iloc[lambda x: x.index % 2 != 0]
```
:::
::::


## Index by name

::: {.xsmall}
```{python}
df.index = (["anna","bob","carol", "dave", "erin"])
df
```
:::

:::: {.columns .xsmall}
::: {.column width='50%'}
```{python}
df.loc["anna"]
```
:::

::: {.column width='50%'}
```{python}
df.loc[["anna"]]
```
:::
::::

:::: {.columns .xsmall}
::: {.column width='50%'}
```{python}
type(df.loc["anna"])
```
:::

::: {.column width='50%'}
```{python}
type(df.loc[["anna"]])
```
:::
::::

##

:::: {.columns .xsmall}
::: {.column width='50%'}
```{python}
df.loc["bob":"dave"]
```
:::

::: {.column width='50%'}
```{python}
df.loc[df.id < 300]
```
:::
::::

::: {.columns .xsmall}
::: {.column}
```{python, error=TRUE}
df.loc[:, "date"]
```
:::
::: {.column}
```{python, error=TRUE}
df.loc[["bob","erin"], "weight":"height"]
```
:::
:::

::: {.small}
```{python, error=TRUE}
df.loc[0:2, "weight":"height"]
```
:::

## Views vs. Copies

In general most pandas operations will generate a new object but some will return views, mostly the later occurs with subsetting. 

:::: {.columns .small}
::: {.column width='50%'}
```{python}
d = pd.DataFrame(np.arange(6).reshape(3,2), columns = ["x","y"])
d

v = d.iloc[0:2,0:2]; v

d.iloc[0,1] = -1; v
```
:::

::: {.column width='50%'}
```{python}
v.iloc[0,0] = np.pi
v
d
```
:::
::::




::: {.aside}
See the documetation [here](http://pandas-docs.github.io/pandas-docs-travis/user_guide/indexing.html#indexing-view-versus-copy) for more details
:::




## Element access

::: {.small}
```{python}
df
```
:::

:::: {.columns .small}
::: {.column width='50%'}
```{python error=TRUE}
df[0,0]
df.iat[0,0]
df.id[0]
df[0:1].id[0]
```
:::

::: {.column width='50%'}
```{python, error=TRUE}
df["anna", "id"]
df.at["anna", "id"]
df["id"]["anna"]
df["id"][0]
```
:::
::::


# Index objects

## Columns and index

When constructing a DataFrame we can specify the indexes for both the rows (`index`) and columns (`columns`),

:::: {.columns .small}
::: {.column width='50%'}
```{python}
df = pd.DataFrame(
  np.random.randn(5, 3), 
  columns=['A', 'B', 'C']
)
df

df.columns
df.index
```
:::

::: {.column width='50%'}
```{python}
df = pd.DataFrame(
  np.random.randn(3, 3), 
  index=['x','y','z'], 
  columns=['A', 'B', 'C']
)
df

df.columns
df.index
```
:::
::::


## Index objects

pandas' `Index` class and its subclasses provide the infrastructure necessary for lookups, data alignment, and other related tasks. You can think of them as being an immutable *multiset* (i.e. duplicate values are allowed).

::: {.small}
```{python}
pd.Index(['A','B','C'])
pd.Index(['A','B','C','A'])
pd.Index(range(5))
pd.Index(list(range(5)))
```
:::

::: {.aside}
Because they are sets they support set like operations, e.g. `union()`, `intersection()`, etc.
:::


## Index names

Index objects can have names which are shown when printing the DataFrame or Index,

::: {.small}
```{python}
df = pd.DataFrame(
  np.random.randn(3, 3), 
  index=pd.Index(['x','y','z'], name="rows"),
  columns=pd.Index(['A', 'B', 'C'], name="cols")
)
df
df.columns
df.index
```
:::




## Indexes and missing values

It is possible for an index to contain missing values (e.g. `np.nan`) but this is generally a bad idea and should be avoided.

::: {.small}
```{python}
pd.Index([1,2,3,np.nan,5])
pd.Index(["A","B",np.nan,"D", None])
```
:::

. . .

<br/>

Missing values can be replaced via the `fillna()` method,

::: {.small}
```{python}
pd.Index([1,2,3,np.nan,5]).fillna(0)
pd.Index(["A","B",np.nan,"D", None]).fillna("Z")
```
:::


## Changing a DataFrame's index

::: {.medium}
Existing columns can be made into an index via `set_index()` and removed via `reset_index()`,
:::

```{python}
#| include: false
data = pd.DataFrame({
  "a": ["bar","bar","foo","foo"],
  "b": ["one","two","one","two"],
  "c": ["z","y","x","w"],
  "d": [1,2,3,4]
})
```

::: {.xsmall}
```{python}
data
```
:::

. . .

:::: {.columns .xsmall}
::: {.column width='50%'}
```{python}
data.set_index('a')
data.set_index('c', drop=False)
```
:::

::: {.column width='50%' .fragment}
```{python}
data.set_index('a').reset_index()
data.set_index('c').reset_index(drop=True)
```
:::
::::


## Creating a new index

New index values can be attached to a DataFrame via `reindex()`,

::: {.columns .xsmall}
::: {.column}
```{python}
data
```
:::
::: {.column .fragment}
```{python}
data.reindex(columns = ["a","b","c","d","e"])
```
:::
:::


:::: {.columns .xsmall .fragment}
::: {.column width='50%'}
```{python}
data.reindex(["w","x","y","z"])
```
:::

::: {.column width='50%' .fragment}
```{python}
data.index = ["w","x","y","z"]; data
```
:::
::::

:::: {.columns .xsmall .fragment}
::: {.column width='50%'}
```{python}
data.reindex(range(4,0,-1))
```
:::
::: {.column width='50%'}
```{python}
data.index = range(4,0,-1); data
```
:::
::::

```{python include=FALSE}
data.index = range(4)
```


# MultiIndexes

## MultiIndex objects

These are a hierarchical analog of standard Index objects and are used to represent nested indexes. There are a number of methods for constructing them based on the initial object

:::: {.columns .xsmall}
::: {.column width='50%'}
```{python}
tuples = [('A','x'), ('A','y'),
          ('B','x'), ('B','y'),
          ('C','x'), ('C','y')]
pd.MultiIndex.from_tuples(
  tuples, names=["1st","2nd"]
)
```
:::

::: {.column width='50%'}
```{python}
pd.MultiIndex.from_product(
  [["A","B","C"],
   ["x","y"]], 
  names=["1st","2nd"]
)
```
:::
::::

## DataFrame with MultiIndex

::: {.small}
```{python}
idx = pd.MultiIndex.from_tuples(
  tuples, names=["1st","2nd"]
)

pd.DataFrame(
  np.random.rand(6,2), 
  index = idx, 
  columns=["m","n"]
)
```
:::


## Column MultiIndex

MultiIndexes can also be used for columns as well,

:::: {.columns .xsmall}
::: {.column width='50%'}
```{python error=TRUE}
cidx = pd.MultiIndex.from_product(
  [["A","B"],["x","y"]], names=["c1","c2"]
)

pd.DataFrame(
  np.random.rand(4,4), columns = cidx
)
```
:::

::: {.column width='50%' .fragment}
```{python error=TRUE}
ridx = pd.MultiIndex.from_product(
  [["m","n"],["l","p"]], names=["r1","r2"]
)

pd.DataFrame(
  np.random.rand(4,4), 
  index= ridx, columns = cidx
)
```
:::
::::



## MultiIndex indexing

```{python}
#| include: false
data = pd.DataFrame(np.random.rand(4,4), index= ridx, columns = cidx)
```

:::: {.columns .xsmall}
::: {.column width='50%'}

```{python}
data
```

```{python error=TRUE}
data["A"]
data["x"]
```
:::

::: {.column width='50%' .fragment}
```{python error=TRUE}
data["m"]
data["m","A"]
data["A","x"]
data["A"]["x"]
```
:::
::::


## MultiIndex indexing via `iloc`

:::: {.columns .xsmall}
::: {.column width='50%'}
```{python error=TRUE}
data.iloc[0]
type(data.iloc[0])
data.iloc[(0,1)]
data.iloc[[0,1]]
```
:::

::: {.column width='50%'}
```{python}
data.iloc[:,0]
type(data.iloc[:,0])
data.iloc[0,1]
data.iloc[0,[0,1]]
```
:::
::::

::: {.aside}
Note that tuples and lists are not treated the same by pandas when it comes to indexing
:::


## MultiIndex indexing via `loc`

:::: {.columns .xsmall}
::: {.column width='50%'}
```{python error=TRUE}
data.loc["m"]
data.loc["l"]
data.loc[:,"A"]
```
:::

::: {.column width='50%'}
```{python}
data.loc[("m","l")]
data.loc[:,("A","y")]
```
:::
::::


## Fancier indexing with `loc`

Index slices can also be used with combinations of indexes and index tuples,

:::: {.columns .xsmall}
::: {.column width='50%'}
```{python error=TRUE}
data.loc["m":"n"]
data.loc[("m","l"):("n","l")]
```
:::

::: {.column width='50%'}
```{python}
data.loc[("m","p"):"n"]
data.loc[[("m","p"),("n","l")]]
```
:::
:::: 


## Selecting nested levels

The previous methods don't give easy access to indexing on nested index levels, this is possible via the cross-section method `xs()`,

:::: {.columns .xsmall}
::: {.column width='50%'}
```{python error=TRUE}
data.xs("p", level="r2")
data.xs("m", level="r1")
```
:::

::: {.column width='50%'}
```{python}
data.xs("y", level="c2", axis=1)
data.xs("B", level="c1", axis=1)
```
:::
:::: 



## Setting MultiIndexes

It is also possible to construct a MultiIndex or modify an existing one using `set_index()` and `reset_index()`,

```{python}
#| include: false
data = pd.DataFrame({
  "a": ["bar","bar","foo"],
  "b": ["one","two","one"],
  "c": ["z","y","x"],
  "d": [1,2,3]
})
```

::: {.xsmall}
```{python}
data
```
:::

:::: {.columns .xsmall}
::: {.column width='50%'}
```{python}
data.set_index(['a','b'])
data.set_index('c', append=True)
```
:::

::: {.column width='50%'}
```{python}
data.set_index(['a','b']).reset_index()
data.set_index(['a','b']).reset_index(level=1)
```
:::
::::



# Working with DataFrames

```{python}
#| include: false

np.random.seed(42)

n = 5
d = {
  "id":     np.random.randint(100, 999, n),
  "weight": np.random.normal(70, 20, n),
  "height": np.random.normal(170, 15, n),
  "date":   pd.date_range(start='2/1/2025', periods=n, freq='D')
}

df = pd.DataFrame(d, index = (["anna","bob","carol", "dave", "erin"]))
```


## Filtering rows

The `query()` method can be used for filtering rows, it evaluates a string expression in the context of the data frame. 

:::: {.columns .xsmall}
::: {.column width='50%'}
```{python}
df.query('date == "2022-02-01"')
df.query('weight > 50')
```
:::

::: {.column width='50%'}
```{python}
df.query('weight > 50 & height < 165')

qid = 202
df.query('id == @qid')
```
:::
::::



::: {.aside}
For more details on query syntax see [here](https://pandas.pydata.org/docs/user_guide/indexing.html#indexing-query)
:::

## Selecting Columns

Beyond the use of `loc()` and `iloc()` there is also the `filter()` method which can be used to select columns (or indices) by name with pattern matching

:::: {.columns .xsmall}
::: {.column width='50%'}
```{python}
df.filter(items=["id","weight"])
df.filter(like = "i")
```
:::

::: {.column width='50%'}
```{python}
df.filter(regex="ght$")
df.filter(like="a", axis=0)
```
:::
::::



## Adding columns

Indexing with assignment allows for inplace modification of a DataFrame, while `assign()` creates a new object (but is chainable)

::: {.xsmall}
```{python}
df['student'] = [True, True, True, False, None]
df['age'] = [19, 22, 25, None, None]
df
```
:::

. . .

::: {.xsmall}
```{python}
df.assign(
  student = lambda x: np.where(x.student, "yes", "no"),
  rand = np.random.rand(5)
)
```
:::


## Removing columns (and rows)

Columns or rows can be removed via the `drop()` method,

::: {.xsmall}
```{python error=TRUE}
df.drop(['student'])
df.drop(['student'], axis=1)
df.drop(['anna','dave'])
```
:::

##

::: {.xsmall}
```{python error=TRUE}
df.drop(columns = df.columns == "age")
df.drop(columns = df.columns[df.columns == "age"])
df.drop(columns = df.columns[df.columns.str.contains("ght")])
```
:::



## Sorting

DataFrames can be sorted on one or more columns via `sort_values()`,

::: {.small}
```{python}
df
```

```{python error=TRUE}
df.sort_values(by=["student","id"], ascending=[True,False])
```
:::


## join vs merge vs concat

All three can be used to combine data frames,

* `concat()` stacks DataFrames on either axis, with basic alignment based on (row) indexes. `join` argument only supports "inner" and "outer".

* `merge()` aligns based on one or more shared columns. `how` supports "inner", "outer", "left", "right", and "cross".

* `join()` uses `merge()` behind the scenes, but prefers to join based on (row) indexes. Also has different default `how` compared to `merge()`, "left" vs "inner".
