Data wrangling in R, Python, and Julia

Note

This post is a replication of Rasmus Bååth’s excellent Why pandas feels clunky when coming from R (licensed CC BY 4.0), which compares tidyverse with pandas.

I extend and redo the analysis with side-by-side comparisons across R (base), R (tidyverse), Python (pandas), Python (polars), and Julia (TidierData.jl), using the newest versions of each at the time of writing.

To showcase the difference between them we use a concrete example, where we have to make small changes throughout.

Note

For pandas, we write our code using the fluent method chaining API rather than the more “imperative” style, which tends to repeat df over and over in statements like df[df["this"] == "that"] = calc_some(df["other_thing"]). We also keep all the data in the data frame proper rather than pushing it into the index, since data stored in the index tends to get in the way when processing it further or plotting it.

The task

Say we have a CSV of purchases with a country, an amount, and a discount:

R (base)

purchases <- read.csv("data-wrangling-tour/purchases.csv")
purchases |> head()

  country amount discount
1  France 138.95     6.95
2  France 147.56     7.38
3 Germany 182.22     9.11
4  Sweden 103.53     5.18
5     USA 491.26    24.56
6     USA 605.89    30.29

R (tidyverse)

suppressPackageStartupMessages(library(tidyverse))
purchases <- read_csv("data-wrangling-tour/purchases.csv", show_col_types = FALSE)
purchases |> head()

# A tibble: 6 × 3
  country amount discount
  <chr>    <dbl>    <dbl>
1 France    139.     6.95
2 France    148.     7.38
3 Germany   182.     9.11
4 Sweden    104.     5.18
5 USA       491.    24.6 
6 USA       606.    30.3 

Python (pandas)

import pandas as pd

purchases = pd.read_csv("data-wrangling-tour/purchases.csv")
purchases.head()

   country  amount  discount
0   France  138.95      6.95
1   France  147.56      7.38
2  Germany  182.22      9.11
3   Sweden  103.53      5.18
4      USA  491.26     24.56

Python (polars)

import polars as pl

purchases_pl = pl.read_csv("data-wrangling-tour/purchases.csv")
print(purchases_pl.head())

shape: (5, 3)
┌─────────┬────────┬──────────┐
│ country ┆ amount ┆ discount │
│ ---     ┆ ---    ┆ ---      │
│ str     ┆ f64    ┆ f64      │
╞═════════╪════════╪══════════╡
│ France  ┆ 138.95 ┆ 6.95     │
│ France  ┆ 147.56 ┆ 7.38     │
│ Germany ┆ 182.22 ┆ 9.11     │
│ Sweden  ┆ 103.53 ┆ 5.18     │
│ USA     ┆ 491.26 ┆ 24.56    │
└─────────┴────────┴──────────┘

Julia (TidierData.jl)

using TidierData, CSV

purchases_jl = CSV.read("data-wrangling-tour/purchases.csv", DataFrame);
first(purchases_jl, 6)

6×3 DataFrame
 Row │ country  amount   discount
     │ String7  Float64  Float64
─────┼────────────────────────────
   1 │ France    138.95      6.95
   2 │ France    147.56      7.38
   3 │ Germany   182.22      9.11
   4 │ Sweden    103.53      5.18
   5 │ USA       491.26     24.56
   6 │ USA       605.89     30.29

Basically the same across all five. So far so good!

Finance now wants to know: How much do we typically sell in each country?

“How much do we sell..? Let’s take the total sum!”

R (base)

purchases$amount |> sum()

[1] 10782.31

R (tidyverse)

purchases$amount |> sum()

[1] 10782.31

Python (pandas)

purchases["amount"].sum()

np.float64(10782.310000000003)

(Note that this method, pandas.Series.sum(), is not the same as pandas.DataFrame.sum(), or numpy.sum(), or the built-in sum function, each of which has different arguments and behaviors.)

Python (polars)

purchases_pl["amount"].sum()

10782.310000000001

Julia (TidierData.jl)

sum(purchases_jl.amount)

10782.31

At this point there is little to distinguish the APIs.

“Ah, they wanted it by country…”

R (base)

aggregate() gets us most of the way there, but it leaves the sum column named after the original variable, so we have to rename it ourselves. The _ placeholder lets us route purchases into the data argument:

purchases |>
  aggregate(amount ~ country, data = _, FUN = sum) |>
  setNames(c("country", "total"))

  country   total
1  France 1477.51
2 Germany 1435.82
3  Sweden 2626.99
4     USA 5241.99

R (tidyverse)

purchases |>
  group_by(country) |>
  summarize(total = sum(amount))

# A tibble: 4 × 2
  country total
  <chr>   <dbl>
1 France  1478.
2 Germany 1436.
3 Sweden  2627.
4 USA     5242.

Python (pandas)

Okay, easy enough right?

(purchases
  .groupby("country")["amount"]
  .sum()
)

country
France     1477.51
Germany    1435.82
Sweden     2626.99
USA        5241.99
Name: amount, dtype: float64

Oh no, what happened? The output has now turned into a pandas.Series, not a data frame, and country got moved to the index. We can solve this by using .reset_index(). Also, we’re not happy with the amount column name, but .sum() does not allow us to specify a different name. Instead of .sum() we can use the .agg() method to get around this.

Using #👈/👆/👇 to mark lines that changed/moved:

(purchases
  .groupby("country")
  .agg(total=("amount", "sum")) #👈
  .reset_index()                #👈
)

   country    total
0   France  1477.51
1  Germany  1435.82
2   Sweden  2626.99
3      USA  5241.99

Python (polars)

print(purchases_pl
  .group_by("country")
  .agg(total=pl.col("amount").sum())
)

shape: (4, 2)
┌─────────┬─────────┐
│ country ┆ total   │
│ ---     ┆ ---     │
│ str     ┆ f64     │
╞═════════╪═════════╡
│ Germany ┆ 1435.82 │
│ France  ┆ 1477.51 │
│ Sweden  ┆ 2626.99 │
│ USA     ┆ 5241.99 │
└─────────┴─────────┘

The structure mirrors tidyverse/TidierData.jl closely, but each expression needs to wrap column references in pl.col() as a string, so not as nice as those.

Note that polars doesn’t preserve ordering. You need to explicitly set maintain_order=True in .group_by() for that.

Julia (TidierData.jl)

@chain purchases_jl begin
  @group_by(country)
  @summarize(total = sum(amount))
end

4×2 DataFrame
 Row │ country  total
     │ String7  Float64
─────┼──────────────────
   1 │ France   1477.51
   2 │ Germany  1435.82
   3 │ Sweden   2626.99
   4 │ USA      5241.99

Already a bit annoying in pandas.

“And I guess I should deduct the discount.” As mentioned in the Python (pandas) block above I will from now on use #👈/👆/👇 to mark lines that changed/moved:

R (base)

purchases |>
  aggregate(amount - discount ~ country, data = _, FUN = sum) |> #👈
  setNames(c("country", "total"))

  country   total
1  France 1403.63
2 Germany 1364.02
3  Sweden 2495.63
4     USA 4979.91

R (tidyverse)

purchases |>
  group_by(country) |>
  summarize(total = sum(amount - discount)) #👈

# A tibble: 4 × 2
  country total
  <chr>   <dbl>
1 France  1404.
2 Germany 1364.
3 Sweden  2496.
4 USA     4980.

Python (pandas)

(purchases
  .assign(net=lambda df: df["amount"] - df["discount"])  #👈
  .groupby("country")["net"]                             #👈
  .sum()                                                 #👈
  .reset_index(name="total")                             #👈
)

   country    total
0   France  1403.63
1  Germany  1364.02
2   Sweden  2495.63
3      USA  4979.91

Python (polars)

print(purchases_pl
  .with_columns(net=pl.col("amount") - pl.col("discount")) #👈
  .group_by("country")
  .agg(total=pl.col("net").sum())                          #👈
)

shape: (4, 2)
┌─────────┬─────────┐
│ country ┆ total   │
│ ---     ┆ ---     │
│ str     ┆ f64     │
╞═════════╪═════════╡
│ Germany ┆ 1364.02 │
│ USA     ┆ 4979.91 │
│ Sweden  ┆ 2495.63 │
│ France  ┆ 1403.63 │
└─────────┴─────────┘

Julia (TidierData.jl)

@chain purchases_jl begin
  @group_by(country)
  @summarize(total = sum(amount - discount)) #👈
end

4×2 DataFrame
 Row │ country  total
     │ String7  Float64
─────┼──────────────────
   1 │ France   1403.63
   2 │ Germany  1364.02
   3 │ Sweden   2495.63
   4 │ USA      4979.91

Notice how many changes pandas needed!

“Oh, and Maria asked me to remove any outliers. Let’s remove everything 10x larger than the median.”

R (base)

purchases |>
  subset(amount <= median(amount) * 10) |>                        #👈
  aggregate(amount - discount ~ country, data = _, FUN = sum) |>
  setNames(c("country", "total"))

  country   total
1  France 1403.63
2 Germany 1364.02
3  Sweden  975.63
4     USA 2889.91

R (tidyverse)

purchases |>
  filter(amount <= median(amount) * 10) |> #👈
  group_by(country) |>
  summarize(total = sum(amount - discount))

# A tibble: 4 × 2
  country total
  <chr>   <dbl>
1 France  1404.
2 Germany 1364.
3 Sweden   976.
4 USA     2890.

Python (pandas)

(purchases
  .query("amount <= amount.median() * 10") #👈
  .assign(net=lambda df: df["amount"] - df["discount"])
  .groupby("country")["net"]
  .sum()
  .reset_index(name="total")
)

   country    total
0   France  1403.63
1  Germany  1364.02
2   Sweden   975.63
3      USA  2889.91

Python (polars)

print(purchases_pl
  .filter(pl.col("amount") <= pl.col("amount").median() * 10) #👈
  .with_columns(net=pl.col("amount") - pl.col("discount"))
  .group_by("country")
  .agg(total=pl.col("net").sum())
)

shape: (4, 2)
┌─────────┬─────────┐
│ country ┆ total   │
│ ---     ┆ ---     │
│ str     ┆ f64     │
╞═════════╪═════════╡
│ Sweden  ┆ 975.63  │
│ Germany ┆ 1364.02 │
│ France  ┆ 1403.63 │
│ USA     ┆ 2889.91 │
└─────────┴─────────┘

Julia (TidierData.jl)

@chain purchases_jl begin
  @filter(amount <= median(amount) * 10) #👈
  @group_by(country)
  @summarize(total = sum(amount - discount))
end

4×2 DataFrame
 Row │ country  total
     │ String7  Float64
─────┼──────────────────
   1 │ France   1403.63
   2 │ Germany  1364.02
   3 │ Sweden    975.63
   4 │ USA      2889.91

This was fairly easy for all five versions.

“I probably should use the median within each country. Prices are quite different across the globe…”

R (base)

There’s no built-in way to filter by a per-group threshold in one step, but transform() plus ave() replicates this:

purchases |>
  transform(country_median = ave(amount, country, FUN = median)) |> #👈
  subset(amount <= country_median * 10) |>
  aggregate(amount - discount ~ country, data = _, FUN = sum) |>
  setNames(c("country", "total"))

  country   total
1  France 1403.63
2 Germany 1364.02
3  Sweden  975.63
4     USA 4979.91

R (tidyverse)

purchases |>
  group_by(country) |>                     #👆
  filter(amount <= median(amount) * 10) |>
  summarize(total = sum(amount - discount))

# A tibble: 4 × 2
  country total
  <chr>   <dbl>
1 France  1404.
2 Germany 1364.
3 Sweden   976.
4 USA     4980.

Python (pandas)

Following the naive approach you end up with something ugly like this, since a DataFrameGroupBy object doesn’t have a .query() or boolean-indexing shortcut of its own, so filtering within groups needs .apply() again, and the surrounding pipeline has to be rebuilt around it:

(purchases
  .groupby("country")                                               #👈
  .apply(lambda df: df[df["amount"] <= df["amount"].median() * 10]) #👈
  .reset_index(level=0)                                             #👈 pandas now drops "country" here, so it has to be pulled back out of the index
  .reset_index(drop=True)                                           #👈
  .assign(net=lambda df: df["amount"] - df["discount"])
  .groupby("country")["net"]
  .sum()
  .reset_index(name="total")
)

   country    total
0   France  1403.63
1  Germany  1364.02
2   Sweden   975.63
3      USA  4979.91

A more pandas idiomatic solution is the following:

(purchases
  .assign(country_median=lambda df:                         #👈
      df.groupby("country")["amount"].transform("median")   #👈
  )
  .query("amount <= country_median * 10")                   #👈
  .assign(net=lambda df: df["amount"] - df["discount"])
  .groupby("country")["net"]
  .sum()
  .reset_index(name="total")
)

   country    total
0   France  1403.63
1  Germany  1364.02
2   Sweden   975.63
3      USA  4979.91

Even then, several changes were needed (and very verbose).

Python (polars)

print(purchases_pl
  .filter(pl.col("amount") <= pl.col("amount").median().over("country") * 10) #👈
  .with_columns(net=pl.col("amount") - pl.col("discount"))
  .group_by("country")
  .agg(total=pl.col("net").sum())
)

shape: (4, 2)
┌─────────┬─────────┐
│ country ┆ total   │
│ ---     ┆ ---     │
│ str     ┆ f64     │
╞═════════╪═════════╡
│ USA     ┆ 4979.91 │
│ Sweden  ┆ 975.63  │
│ France  ┆ 1403.63 │
│ Germany ┆ 1364.02 │
└─────────┴─────────┘

Julia (TidierData.jl)

@chain purchases_jl begin
  @group_by(country)                     #👆
  @filter(amount <= median(amount) * 10)
  @summarize(total = sum(amount - discount))
end

4×2 DataFrame
 Row │ country  total
     │ String7  Float64
─────┼──────────────────
   1 │ France   1403.63
   2 │ Germany  1364.02
   3 │ Sweden    975.63
   4 │ USA      4979.91

Conclusion

As we add grouping, transformations, filtering, and group-wise calculations, I want to be able to make a small change without having to restructure the rest of the pipeline. For this particular kind of analysis my ranking would be, as I tried to highlight above:

1. tidyverse and TidierData.jl. They make small changes in the analysis trivial to implement, and no need for strings when referencing columns.
2. base R and polars. They require a few more changes, but are still very reasonable.
3. pandas. Last by a large margin. None of the individual pandas lines are unreasonable in isolation, but stringing together this analysis is far less smooth than in any of the alternatives.

Tip

But polars is much faster than dplyr!

Not what this blogpost is about, but you can find a tidy-syntax interface to most backends, e.g. tidytable for data.table backend.

Versions used

Versions used in this post: R 4.6.1, dplyr (grammar backend for tidyverse) 1.2.1; Python 3.12.3, pandas 3.0.5, polars 1.43.2; Julia 1.12.7, TidierData.jl 0.17.2.