---
title: "pandas / polars"
subtitle: "Lecture 09"
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)
  })
  
  hook_warn_old <- knitr::knit_hooks$get("warning")  # save the old hook
  knitr::knit_hooks$set(warning = function(x, options) {
    x = sub("<string>:1: ", "", x)
    hook_warn_old(x, options)
  })
})
```

```{python setup, include=FALSE}
import numpy as np
import pandas as pd

import matplotlib.pyplot as plt

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

```{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".


## Pivoting - long to wide 

::: {.small}
```{python include=FALSE}
df = pd.DataFrame({
   "country": ["A","A","A","A","B","B","B","B","C","C","C","C"],
    "year":   [1999,1999,2000,2000,1999,1999,2000,2000,1999,1999,2000,2000],
    "type":   ["cases","pop","cases","pop","cases","pop","cases","pop","cases","pop","cases","pop"],
    "count":  ["0.7K", "19M", "2K", "20M", "37K", "172M", " 80K", "174M", "212K", "1T", "213K", "1T"]
})
```
:::

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

::: {.column width='50%' .fragment}
```{python}
df_wide = df.pivot(
  index=["country","year"], 
  columns="type", 
  values="count"
)
df_wide
```
:::
::::

## pivot indexes

:::: {.columns .small}
::: {.column width='50%'}
```{python}
df_wide.index
df_wide.columns
```
:::

::: {.column width='50%' .fragment}
```{python}
( df_wide
  .reset_index()
  .rename_axis(
    columns=None
  )
)
```
:::
::::


## Wide to long (melt)

```{python include=FALSE}
df = pd.DataFrame({
  "country": ["A","B","C"],
  "1999":    ["0.7K","37K","212K"],
  "2000":    ["2K","80K","213K"]
})
```

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

::: {.column width='50%' .fragment}
```{python}
df_long = df.melt(
  id_vars="country", 
  var_name="year",
  value_name="value"
)
df_long
```
:::
::::


## Exercise 1 - Tidying

How would you tidy the following data frame so that the rate column is split into cases and population columns?

::: {.small}
```{python}
df = pd.DataFrame({
  "country": ["A","A","B","B","C","C"],
  "year":    [1999, 2000, 1999, 2000, 1999, 2000],
  "rate":    ["0.7K/19M", "2K/20M", "37K/172M", "80K/174M", "212K/1T", "213K/1T"]
})
df
```
:::




# Split-Apply-Combine

```{r include=FALSE}
library(tidyverse)
d = readr::read_csv(
  "https://raw.githubusercontent.com/UBC-MDS/programming-in-python-for-data-science/master/data/cereal.csv"
)

d %>%
  mutate(
    mfr = case_when(
      mfr == "A" ~ "Maltex",
      mfr == "G" ~ "General Mills",
      mfr == "K" ~ "Kellogg's",
      mfr == "N" ~ "Nabisco",
      mfr == "P" ~ "Post",
      mfr == "Q" ~ "Quaker Oats",
      mfr == "R" ~ "Ralston Purina"
    )
  ) %>%
  select(-sodium, -potass, -vitamins, -shelf, -weight, -cups) %>%
  select(-(protein:carbo)) %>%
  write_csv("data/cereal.csv")
```

## cereal data 

::: {.small}
```{python}
cereal = pd.read_csv("https://sta663-sp25.github.io/slides/data/cereal.csv")
cereal
```
:::

::: {.aside}
From [UBC-MDS/programming-in-python-for-data-science](https://raw.githubusercontent.com/UBC-MDS/programming-in-python-for-data-science/master/data/)
:::

## groupby

Groups can be created within a DataFrame via `groupby()` - these groups are then used by the standard summary methods (e.g. `sum()`, `mean()`, `std()`, etc.).

::: {.small}
```{python}
cereal.groupby("type")
cereal.groupby("type").groups
cereal.groupby("mfr").groups
```
:::

## groupby and arregation methods

::: {.small}
```{python}
#| error: true
cereal.groupby("type").mean()
```
:::

. . .

:::: {.columns .small}
::: {.column width='50%'}
```{python error=TRUE}
( cereal
  .groupby("type")
  .mean(numeric_only=True)
)
```
:::

::: {.column width='50%' .fragment}
```{python error=TRUE}
cereal.groupby("mfr").size()
```
:::
::::

## Selecting groups

Groups can be accessed via `get_group()` 

::: {.small}
```{python error=TRUE}
cereal.groupby("type").get_group("Hot")

cereal.groupby("mfr").get_group("Post")
```
:::

## Iterating groups

`DataFrameGroupBy`'s can also be iterated over,


::: {.xsmall}
```{python}
for name, group in cereal.groupby("type"):
  print(f"# {name}\n{group}\n\n")
```
:::



## Aggregation


The `aggregate()` function or `agg()` method can be used to compute summary statistics for each group,

::: {.small}
```{python}
#| error: true
cereal.groupby("mfr").agg("mean")
```
:::

. . .

::: {.small}
```{python}
#| error: true
cereal.groupby("mfr").agg("mean", numeric_only = True)
```
:::

::: {.aside}
Think `summarize()` from dplyr.
:::


## Aggregation by column

::: {.small}
```{python}
cereal.groupby("mfr").agg({
  "calories": ['min', 'max'],
  "sugars":   ['median'],
  "rating":   ['sum', 'count']
})
```
:::


::: {.aside}
Note that this results in a DataFrame with a column multiindex.
:::

## Named aggregation

It is also possible to use special syntax to aggregate specific columns into a named output column,

::: {.medium}
```{python}
cereal.groupby("mfr", as_index=False).agg(
  min_cal = ("calories", "min"),
  max_cal = ("calories", max),
  med_sugar = ("sugars", "median"),
  avg_rating = ("rating", np.mean)
)
```
:::

::: {.aside}
Tuples can also be passed using `pd.NamedAgg()` but this offers no additional functionality.
:::


## Transformation

The `transform()` method returns a DataFrame with the aggregated result matching the size (or length 1) of the input group(s),

:::: {.columns .small}
::: {.column width='50%'}
```{python}
( cereal
  .groupby("mfr")
  .transform(
    np.mean, numeric_only=True
  )
)
```
:::

::: {.column width='50%'}
```{python}
( cereal
  .groupby("type")
  .transform(
    "mean", numeric_only=True
  )
)
```
:::
::::


## Practical transformation

`transform()` will generally be most useful via a user defined function, the lambda is applied to each column of each group.

::: {.small}
```{python}
( cereal
  .drop(["name","type"], axis=1)
  .groupby("mfr")
  .transform( lambda x: (x - np.mean(x))/np.std(x, axis=0) ) 
)
```
:::


## Filtering groups

::: {.small}
`filter()` also respects groups and allows for the inclusion / exclusion of groups based on user specified criteria,
:::

::: {.panel-tabset}

### filter 
::: {.xsmall}
```{python}
( cereal
  .groupby("mfr")
  .filter(lambda x: len(x) > 10)
)
```
:::

### Group sizes

::: {.columns .xsmall}
```{python}
( cereal
  .groupby("mfr")
  .size()
)
```
:::


:::

# ![](imgs/polars_github_banner.svg){fig-align="center"}

## polars

> Polars is a blazingly fast DataFrame library for manipulating structured data. The core is written in Rust, and available for Python, R and NodeJS.
> 
> The goal of Polars is to provide a lightning fast DataFrame library that:
>
> * Utilizes all available cores on your machine.
> * Optimizes queries to reduce unneeded work/memory allocations.
> * Handles datasets much larger than your available RAM.
> * A consistent and predictable API.
> * Adheres to a strict schema (data-types should be known before running the query).

::: {.small}
```{python}
import polars as pl
pl.__version__
```
:::

## Series

::: {.small}
Just like Pandas, Polars also has a `Series` type used for columns. For a complete list of polars dtypes see [here](https://docs.pola.rs/api/python/stable/reference/datatypes.html).
:::

::: {.columns .xsmall}
::: {.column}
```{python}
pl.Series("ints", [1, 2, 3, 4, 5])
pl.Series("dbls", [1., 2., 3., 4., 5.])
```
:::
::: {.column}
```{python}
pl.Series("bools", [True, False, True, False, True])
pl.Series("strs", ["A", "B", "C", "D", "E"])
```
:::
:::



## Missing values

::: {.small}
In Polars, missing data is represented by the value `null`. This missing value `null` is used for all data types, including numerical types.
:::

::: {.columns .xsmall}
::: {.column width=33%}
```{python}
pl.Series("ints", 
  [1, 2, 3, None])
pl.Series("dbls", 
  [1., 2., 3., None])
```
:::
::: {.column width=33% .fragment}
```{python}
pl.Series("bools", 
  [True, False, True, None])
pl.Series("strs", 
  ["A", "B", "C", None])
```
:::
::: {.column width=33% .fragment}
```{python}
#| error: true
pl.Series("ints", 
  [1, 2, 3, np.nan])
pl.Series("dbls", 
  [1., 2., 3., np.nan])
```
:::
:::


## Missing value checking

Checking for missing values can be done via the `is_null()` method

::: {.columns .xsmall}
::: {.column}
```{python}
pl.Series("ints", 
  [1, 2, 3, None]).is_null()
pl.Series("dbls", 
  [1., 2., 3., None]).is_null()
```
:::
::: {.column}
```{python}
pl.Series("dbls", 
  [1., 2., 3., np.nan]).is_null()
pl.Series("bools", 
  [True, False, True, None]).is_null()
```
:::
:::

## DataFrames

Data Frames can be constructed in the same was as Pandas, 

```{python}
#| include: false
np.random.seed(42)
```

::: {.xsmall}
```{python}
df = pl.DataFrame(
  {
    "name":   ["anna","bob","carol", "dave", "erin"],
    "id":     np.random.randint(100, 999, 5),
    "weight": np.random.normal(70, 20, 5),
    "height": np.random.normal(170, 15, 5),
    "date":   pd.date_range(start='2/1/2025', periods=5, freq='D')
  },
  schema_overrides = {"id": pl.UInt16, "weight": pl.Float32}
)
df
```
:::


## Expressions

Polars makes use of lazy evaluation to improve its flexibility and computational performance.

```{python}
bmi_expr = pl.col("weight") / (pl.col("height") ** 2)
bmi_expr
```

<br/>

This represents a potential computation that can be executed later. Much of the power of Polars comes from the ability to chain together / compose these expressions.

## Contexts

Contexts are the environments in which expressions are evaluated - examples of common contexts include: `select`, `with_columns`, `filter`, and `group_by`.

::: {.columns .xsmall}
::: {.column width=33%}
```{python}
df.select(bmi = bmi_expr)
```
:::
::: {.column width=66%}
```{python}
df.with_columns(bmi = bmi_expr)
```
:::
:::

## `filter()`

::: {.columns .xsmall}
::: {.column}
```{python}
df.filter(
  pl.col("height") > 160,
  pl.col("id") < 500
)
```
:::
::: {.column}
```{python}
df.filter(
  (pl.col("height") > 160) | 
  (pl.col("id") < 500)
)
```
:::
:::

## `group_by()` & `agg()`

::: {.small}
```{python}
df.group_by(
  id_range = pl.col("id") - pl.col("id") % 100
).agg(
  pl.len(),
  pl.col("name"),
  bmi_expr.alias("bmi"),
  pl.col("weight", "height").mean().name.prefix("avg_"),
  med_height = pl.col("height").median()
)
```
:::


## More expression expansion

::: {.small}
```{python}
num_cols = pl.col(pl.Float64, pl.Float32)

df.with_columns(
  ((num_cols - num_cols.mean())/num_cols.std()).name.suffix("_std")
)
```
:::

## NYC Taxi Data

::: {.small}
```{python}
df = pl.scan_parquet(
  "~/Scratch/nyctaxi/*_fix.parquet"
)
df
```
:::

. . .

::: {.small}

```{python}
df.select(pl.len()).collect()
df.select(pl.len()).explain()
```
:::

::: {.aside}
These data are available on `rstudio.stat.duke.edu` in the `/data/nyctaxi` directory.
:::

## Large lazy queries

::: {.xsmall}
```{python}
zone_lookup = pl.read_csv(
  "https://d37ci6vzurychx.cloudfront.net/misc/taxi_zone_lookup.csv"
).rename(
  {"LocationID": "pickup_zone"}
)
```
:::

. . .

::: {.xsmall}
```{python}
query = (
  df
  .filter(pl.col("trip_distance") > 0)
  .rename({"PULocationID": "pickup_zone"})
  .group_by("pickup_zone")
  .agg(
    num_rides = pl.len(),
    avg_fare_per_mile = (pl.col("fare_amount") / pl.col("trip_distance")).mean().round(2)
  ).join(
    zone_lookup.lazy(),
    on = "pickup_zone",
    how = "left"
  )
  .sort("pickup_zone")
)
```
:::

## Plan

::: {.xsmall}
```{python}
query
```
:::

## Result

::: {.xsmall}
```{python}
query.collect()
```
:::


## Performance

```{python}
#| eval: false
%timeit query.collect()
```
```
1.14 s ± 62 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
```