polars

polars (2020) is a newer data frame library written in Rust on top of Apache Arrow’s memory layout, with bindings for Python, R, and JavaScript. Rather than extend NumPy conventions it was designed from scratch around a few ideas,

• No index - rows are only ever positions

• A strict schema - columns have explicit types and incompatible values are rejected by default; expressions can promote types or replace columns

• Expressions - computations on columns are objects, built up and then evaluated in a context

• Lazy evaluation - queries are planned and optimized before running, on all available cores

import polars as pl
pl.__version__

'1.44.2'

Series

A polars Series is a name, a dtype, and values - nothing else. With no index there is nothing to align on, so arithmetic is positional and lengths must match (or be length one).

pl.Series("x", [4, 2, 1, 3])

a = pl.Series([1, 2, 3])
b = pl.Series([10, 20, 30])
a + b

DataFrame

Print method shows the shape and column dtypes. Note that the integer columns stayed integers despite the missing values, and that "NA" had to be declared as a missing value marker.

penguins = pl.read_csv("data/penguins.csv", null_values="NA"); penguins

Subsetting

Without an index there is no .loc / .iloc distinction - df[...] takes column names or row positions and a slice is just a slice.

penguins["species"]

penguins[0:3, ["species", "island"]]

penguins[0, "species"]

'Adelie'

Missing values

Missing data is null in every dtype (like R’s NA) and NaN is a floating point value, distinct from missing.

Aggregation functions like mean() and sum() skip null by default and propagate NaN. Ordinary min() and max() ignore NaN; nan_min() and nan_max() propagate it.

s = pl.Series([1.0, None, np.nan])

s

s+1

s.is_null().to_list()

[False, True, False]

s.is_nan().to_list()

[False, None, True]

s.mean()

nan

s.fill_nan(None).mean()

1.0

Expressions

pl.col("x") does not return a column, it returns an expression - a description of a computation that is not attached to any DataFrame and has not been evaluated. Expressions compose with operators and methods and are only evaluated when handed to a context. This is polars’ answer to data masking - build an object that represents the computation.

mass_kg = pl.col("body_mass_g") / 1000; mass_kg

type(mass_kg)

<class 'polars.expr.expr.Expr'>

pl.col("body_mass_g").mean().alias("avg_mass")

Contexts

A context is a DataFrame method that evaluates expressions against its columns,

penguins.select(
  "species", mass_kg.alias("mass_kg")
).head(2)

penguins.filter(
  pl.col("body_mass_g") > 6000
).select("species", "body_mass_g")

Combining predicates

Predicates work like pandas boolean masks: & and | with each comparison parenthesized, and is_in() in place of isin(). The one addition is that filter() accepts several predicates and ANDs them.

penguins.filter(
  pl.col("species").is_in(["Adelie", "Gentoo"]),
  pl.col("body_mass_g") > 5000
).select(
  "species", "body_mass_g"
).head(3)

penguins.filter(
    (pl.col("species") == "Gentoo")
  | (pl.col("body_mass_g") > 5000)
).select(
  "species", "body_mass_g"
).head(3)

Expression expansion

One expression can expand to many columns - by listing names, by dtype, or with selectors (from polars.selectors). This is similar to dplyr’s across().

penguins.select(
  pl.col(
    "bill_length_mm", 
    "bill_depth_mm"
  ).max()
)

penguins.select(
  pl.col(
    pl.String
  ).n_unique()
)

import polars.selectors as cs
penguins.select(cs.ends_with("_mm").mean().name.suffix("_mean"))

group_by

group_by().agg() is the polars group_by() |> summarize() - each expression is evaluated once per group and the result has one row per group (in no guaranteed order). For a grouped mutate, add .over("group") to an expression and it is computed within each group but returns a value for every row.

mass = pl.col("body_mass_g")
(penguins
  .group_by("species")
  .agg(n = pl.len(), avg = mass.mean())
  .sort("species")
)

avg = mass.mean().over("species")
(penguins
  .with_columns(rel = mass / avg)
  .select("species", "rel")
  .head(2)
)

Interoperability

Default to_pandas() converts nullable integer columns to floats. Arrow extension arrays preserve their integer types and nulls.

penguins.to_pandas().dtypes

species                  str
island                   str
bill_length_mm       float64
bill_depth_mm        float64
flipper_length_mm    float64
body_mass_g          float64
sex                      str
year                   int64
dtype: object

penguins.to_pandas(
  use_pyarrow_extension_array=True
).dtypes

species              large_string[pyarrow]
island               large_string[pyarrow]
bill_length_mm             double[pyarrow]
bill_depth_mm              double[pyarrow]
flipper_length_mm           int64[pyarrow]
body_mass_g                 int64[pyarrow]
sex                  large_string[pyarrow]
year                        int64[pyarrow]
dtype: object

Exercise 1

Using polars and data/flights.parquet, repeat the questions from last lecture’s pandas exercise. Questions 1 and 2 are the core; 3 and 4 are extensions if time allows.

1. How many flights to LAX did each legacy carrier (AA, UA, DL, US) have in May from JFK, and what was their mean actual air time (air_time)? Report only carriers with matching records.

2. Which plane (tailnum) has the most flight records from each New York airport? Exclude missing tail numbers and return all ties.

3. Which five calendar dates (year, month, day) had the lowest mean departure delay? Skip missing delays and break ties by earliest date.

4. Which flight has the largest arrival delay as a percentage of its actual air time (100 * arr_delay / air_time)? Exclude missing values and nonpositive air times, and return all ties.

Tidy data

Tidy vs untidy

Happy families are all alike; every unhappy family is unhappy in its own way

— Leo Tolstoy, Anna Karenina

Is this data tidy? What are the variables, and what is an observation?

# A tibble: 317 × 7
  artist       track                   date.entered   wk1   wk2   wk3   wk4
  <chr>        <chr>                   <date>       <dbl> <dbl> <dbl> <dbl>
1 2 Pac        Baby Don't Cry (Keep... 2000-02-26      87    82    72    77
2 2Ge+her      The Hardest Part Of ... 2000-09-02      91    87    92    NA
3 3 Doors Down Kryptonite              2000-04-08      81    70    68    67
4 3 Doors Down Loser                   2000-10-21      76    76    72    69
5 504 Boyz     Wobble Wobble           2000-04-15      57    34    25    17
6 98^0         Give Me Just One Nig... 2000-08-19      51    39    34    26
# ℹ 311 more rows

Wide vs long

Wide to long - tidyr

pivot_longer() stacks a set of columns into a pair of columns - one holding the old column names and one holding the values. The columns to pivot are chosen with tidy selection.

table4a

# A tibble: 3 × 3
  country     `1999` `2000`
  <chr>        <dbl>  <dbl>
1 Afghanistan    745   2666
2 Brazil       37737  80488
3 China       212258 213766

pivot_longer(
  table4a,
  cols = `1999`:`2000`,
  names_to = "year",
  values_to = "cases"
)

# A tibble: 6 × 3
  country     year   cases
  <chr>       <chr>  <dbl>
1 Afghanistan 1999     745
2 Afghanistan 2000    2666
3 Brazil      1999   37737
4 Brazil      2000   80488
5 China       1999  212258
6 China       2000  213766

Cleaning up while pivoting

Column names are strings, so the new names column is a character vector that often needs further work. pivot_longer() can strip a prefix, convert the type, and drop the structural missing values created by the wide layout.

billboard |>
  pivot_longer(
    starts_with("wk"),
    names_to = "week", names_prefix = "wk", names_transform = as.integer,
    values_to = "rank", values_drop_na = TRUE
  )

# A tibble: 5,307 × 5
  artist track                   date.entered  week  rank
  <chr>  <chr>                   <date>       <int> <dbl>
1 2 Pac  Baby Don't Cry (Keep... 2000-02-26       1    87
2 2 Pac  Baby Don't Cry (Keep... 2000-02-26       2    82
3 2 Pac  Baby Don't Cry (Keep... 2000-02-26       3    72
4 2 Pac  Baby Don't Cry (Keep... 2000-02-26       4    77
5 2 Pac  Baby Don't Cry (Keep... 2000-02-26       5    87
6 2 Pac  Baby Don't Cry (Keep... 2000-02-26       6    94
# ℹ 5,301 more rows

Wide to long - pandas & polars

pandas calls this melt() and polars unpivot(). Both name the columns to keep (id_vars / index) and pivot everything else unless told otherwise. wide_pd and wide_pl hold the same data as table4a.

wide_pd.melt(
  id_vars="country",
  var_name="year",
  value_name="cases"
)

       country  year   cases
0  Afghanistan  1999     745
1       Brazil  1999   37737
2        China  1999  212258
3  Afghanistan  2000    2666
4       Brazil  2000   80488
5        China  2000  213766

wide_pl.unpivot(
  index="country",
  variable_name="year",
  value_name="cases"
)

Long to wide - tidyr

pivot_wider() is the inverse - the values of one column become new column names and the values of another fill them. The remaining columns (id_cols) identify the rows.

table2

# A tibble: 12 × 4
  country      year type           count
  <chr>       <dbl> <chr>          <dbl>
1 Afghanistan  1999 cases            745
2 Afghanistan  1999 population  19987071
3 Afghanistan  2000 cases           2666
4 Afghanistan  2000 population  20595360
5 Brazil       1999 cases          37737
6 Brazil       1999 population 172006362
# ℹ 6 more rows

pivot_wider(
  table2,
  id_cols = c(country, year),
  names_from = type,
  values_from = count
)

# A tibble: 6 × 4
  country      year  cases population
  <chr>       <dbl>  <dbl>      <dbl>
1 Afghanistan  1999    745   19987071
2 Afghanistan  2000   2666   20595360
3 Brazil       1999  37737  172006362
4 Brazil       2000  80488  174504898
5 China        1999 212258 1272915272
6 China        2000 213766 1280428583

Long to wide - pandas & polars

Both use pivot(). pandas moves the id columns into a row MultiIndex and names the column index after the pivoted column, polars returns ordinary columns. long_pd and long_pl hold the same data as table2.

long_pd.pivot(
  index=["country", "year"],
  columns="type",
  values="count"
)

type               cases  population
country     year                    
Afghanistan 1999     745    19987071
            2000    2666    20595360
Brazil      1999   37737   172006362
            2000   80488   174504898
China       1999  212258  1272915272
            2000  213766  1280428583

long_pl.pivot(
  on="type",
  index=["country", "year"],
  values="count"
)

Duplicates and aggregation

If more than one row maps to the same cell a pivot wider is ambiguous - tidyr warns and returns list columns, pandas and polars error. Each has a way to aggregate as part of the pivot: values_fn in tidyr, pivot_table() in pandas, and aggregate_function in polars.

penguins_pd.pivot_table(
  index="island",
  columns="species",
  values="body_mass_g",
  aggfunc="mean"
).round(1)

species    Adelie  Chinstrap  Gentoo
island                              
Biscoe     3709.7        NaN  5076.0
Dream      3688.4     3733.1     NaN
Torgersen  3706.4        NaN     NaN

penguins.pivot(
  on="species",
  index="island",
  values="body_mass_g",
  aggregate_function="mean"
)

Exercise 2

The code below counts the penguins of each species measured on each island, species and island pairs that do not appear were never observed together.

palmerpenguins::penguins |>
  count(island, species)

# A tibble: 5 × 3
  island    species       n
  <fct>     <fct>     <int>
1 Biscoe    Adelie       44
2 Biscoe    Gentoo      124
3 Dream     Adelie       56
4 Dream     Chinstrap    68
5 Torgersen Adelie       52

Using tidyr and then polars, construct a contingency table of these counts with islands as rows and species as columns, with zeros for the missing pairs.

Relational data

Tidy data is often spread across several tables, each describing one kind of thing - nycflights13 has flights, airlines, planes, airports, and weather. Tables are connected by keys, columns whose values identify the matching rows in the other table. Two small tables make the behavior easy to see,

x = tibble(
  id = c(1, 2, 3), 
  x = c("x1", "x2", "x3")
)
y = tibble(
  id = c(1, 2, 4), 
  y = c("y1", "y2", "y4")
)

x_pl = pl.DataFrame({
  "id": [1, 2, 3], 
  "x": ["x1", "x2", "x3"]
})
y_pl = pl.DataFrame({
  "id": [1, 2, 4], 
  "y": ["y1", "y2", "y4"]
})

x_pd = x_pl.to_pandas()
y_pd = y_pl.to_pandas()

Join types

Mutating joins add the columns of y to x, and differ only in which unmatched rows are kept. Filtering joins keep or drop rows of x based on whether they have a match in y, without adding any columns.

dplyr

One function per join type, with keys given by by. If by is omitted all columns with common names are used, with a message. Unmatched rows are filled with NA.

left_join(x, y, by = "id")

# A tibble: 3 × 3
     id x     y    
  <dbl> <chr> <chr>
1     1 x1    y1   
2     2 x2    y2   
3     3 x3    <NA> 

inner_join(x, y, by = "id")

# A tibble: 2 × 3
     id x     y    
  <dbl> <chr> <chr>
1     1 x1    y1   
2     2 x2    y2   

right_join(x, y, by = "id")

# A tibble: 3 × 3
     id x     y    
  <dbl> <chr> <chr>
1     1 x1    y1   
2     2 x2    y2   
3     4 <NA>  y4   

full_join(x, y, by = "id")

# A tibble: 4 × 3
     id x     y    
  <dbl> <chr> <chr>
1     1 x1    y1   
2     2 x2    y2   
3     3 x3    <NA> 
4     4 <NA>  y4   

pandas

A single method, merge(), with the type given by how (default is "inner"). indicator=True adds a column recording where each row came from.

x_pd.merge(y_pd, on="id")

   id   x   y
0   1  x1  y1
1   2  x2  y2

x_pd.merge(y_pd, on="id", how="left")

   id   x    y
0   1  x1   y1
1   2  x2   y2
2   3  x3  NaN

x_pd.merge(
  y_pd, on="id", how="outer", 
  indicator=True
)

   id    x    y      _merge
0   1   x1   y1        both
1   2   x2   y2        both
2   3   x3  NaN   left_only
3   4  NaN   y4  right_only

polars

join() also takes the join method via how with a default of "inner".

x_pl.join(y_pl, on="id", how="left")

x_pl.join(
  y_pl, on="id", how="full", 
  coalesce=True
)

Keys with different names

Keys rarely share a name across tables. dplyr uses join_by(), pandas and polars use left_on and right_on. pandas keeps both key columns in the result, dplyr and polars keep only the left.

left_join(x, y2, by = join_by(id == key))

# A tibble: 3 × 3
     id x     y    
  <dbl> <chr> <chr>
1     1 x1    y1   
2     2 x2    y2   
3     3 x3    <NA> 

x_pd.merge(
  y2_pd, how="left",
  left_on="id", right_on="key"
)

   id   x  key    y
0   1  x1  1.0   y1
1   2  x2  2.0   y2
2   3  x3  NaN  NaN

x_pl.join(
  y2_pl, how="left",
  left_on="id", right_on="key"
)

Duplicate keys

A row is repeated once for every match, so duplicated keys silently change the number of rows. If you expect a key to be unique, say so and the join will check - relationship in dplyr, validate in pandas and polars.

left_join(x, yd, by = "id")

# A tibble: 4 × 3
     id x     y    
  <dbl> <chr> <chr>
1     1 x1    y1   
2     1 x1    y2   
3     2 x2    y3   
4     3 x3    <NA> 

left_join(
  x, yd, by = "id", 
  relationship = "one-to-one"
)

Error in `left_join()`:
! Each row in `x` must match at most 1 row in `y`.
ℹ Row 1 of `x` matches multiple rows in `y`.

x_pl.join(yd_pl, on="id", how="left")

x_pl.join(
  yd_pl, on="id", how="left", 
  validate="1:1"
)

polars.exceptions.ComputeError: join keys did not fulfill 1:1 validation

Filtering joins

semi_join() keeps the rows of x with a match in y and anti_join() the rows without one. polars has both as how options, pandas has neither and uses isin() instead.

semi_join(x, y, by = "id")

# A tibble: 2 × 2
     id x    
  <dbl> <chr>
1     1 x1   
2     2 x2   

anti_join(x, y, by = "id")

# A tibble: 1 × 2
     id x    
  <dbl> <chr>
1     3 x3   

x_pl.join(y_pl, on="id", how="anti")

x_pd[~x_pd["id"].isin(y_pd["id"])]

   id   x
2   3  x3

Missing keys

dplyr and pandas treat NA as equal to NA, so rows with missing keys are paired up even though nothing is known about either. polars follows SQL, where null is never equal to anything, so a row with a null key is unmatched.

a = tibble(k = c(1, NA), a = c("a1", "a2"))
b = tibble(k = c(1, NA), b = c("b1", "b2"))

a_pl = pl.DataFrame({"k": [1, None], "a": ["a1", "a2"]})
b_pl = pl.DataFrame({"k": [1, None], "b": ["b1", "b2"]})
a_pd, b_pd = a_pl.to_pandas(), b_pl.to_pandas()

inner_join(a, b, by = "k")

# A tibble: 2 × 3
      k a     b    
  <dbl> <chr> <chr>
1     1 a1    b1   
2    NA a2    b2   

a_pd.merge(b_pd, on="k")

     k   a   b
0  1.0  a1  b1
1  NaN  a2  b2

a_pl.join(b_pl, on="k")

Missing keys - changing the default

dplyr switches to the SQL behavior with na_matches = "never" and polars to the dplyr behavior with nulls_equal=True. pandas has no equivalent, so the rows with missing keys must be dropped before merging.

inner_join(
  a, b, by = "k", 
  na_matches = "never"
)

# A tibble: 1 × 3
      k a     b    
  <dbl> <chr> <chr>
1     1 a1    b1   

a_pl.join(b_pl, on="k", nulls_equal=True)

a_pd.dropna(subset="k").merge(b_pd, on="k")

     k   a   b
0  1.0  a1  b1

Exercise 3

data/planes.parquet contains a record for each plane (tailnum) known to the FAA, including its manufacturer, model, and number of seats. Using dplyr and then polars, together with data/flights.parquet, answer the following.

1. How many flights have a tail number with no matching record in planes? Which carrier accounts for the most of them?

2. Which five manufacturers’ planes flew the most flights out of New York?

3. Both tables have a year column. What happens to these columns in a join, and do they mean the same thing?

Three data frames

Verbs

Takeaways