Five years ago I started a new role and I suddenly found myself, a staunch R fan, having to code in Python on a daily basis. Working with data, most of my Python work involved using pandas, the Python data frame library, and initially I found it quite hard and clunky to use, being used to the silky smooth API of R’s tidyverse. And you know what? It still feels hard and clunky, even now, 5 years later!

But, what seems even harder, is explaining to “Python people” what they are missing out on. From their perspective, pandas is this fantastic tool that makes Data Science in Python possible. And it is a fantastic tool, don’t get me wrong, but if you, like me, end up in many “pandas is great, but…”-type discussions and are lacking clear examples to link to; here’s a somewhat typical example of a simple analysis, built from the ground up, that flows nicely in R and the tidyverse but that becomes clunky and complicated using Python and pandas.

Let’s first step through a short analysis of purchases using R and the tidyverse. After that we’ll see how the same solution using Python and pandas compares.

Analyzing purhcases in R

We’ve been given a table of purchases with different amounts, where the customer could have received a discount and where each purchase happened in a country. Finance now wants to know: How much do we typically sell in each country? Let’s read in the data and take a look:

library(tidyverse)

purchases <- read_csv("purchases.csv")
purchases |> head()

# A tibble: 6 × 3
  country amount discount
  <chr>    <dbl>    <dbl>
1 USA       2000       10
2 USA       3500       15
3 USA       3000       20
4 Canada     120       12
5 Canada     180       18
6 Canada    3100       21

Now, without bothering with printing out the intermediate results, here’s how a quick pipeline could be built up, answering Finance’s question.

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

purchases$amount |> sum()

“Ah, they wanted it by country…”

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

“And I guess I should deduct the discount.” (#👈/👆/👇 marks lines that changed/moved)

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

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

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

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

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

# A tibble: 11 × 2
   country   total
   <chr>     <dbl>
 1 Australia   540
 2 Brazil      414
 3 Canada      270
 4 France      450
 5 Germany     513
 6 India       648
 7 Italy       567
 8 Japan       621
 9 Spain       594
10 UK          432
11 USA        8455

“And we’re done, let’s go for second breakfast!”

Analyzing purhcases in Python

We’re now going to take a look at how this little analysis would look in Python and pandas. One complication here is that pandas can be written in many different styles; it’s not like in the tidyverse where there’s often one obvious way to do something. Here we’re opting for writing pandas using the fluent method chaining API, as opposed to using the more “imperative” approach that results in a lot of repeats of df and statements like df[df["this"] == "that"] = calc_some(df["other_thing"]). We’re also opting for always returning a table with all the data in the data frame proper. We don’t want data hidden away in the index (that is, pandas’ really advanced system for row and column names). Having data in the index is generally annoying when one wants to process the data further or when turning the data into plots.

Again, let’s step through the R version of the analysis, and below let’s write the corresponding pandas code. Again, #👈/👆/👇 marks lines that have changed/moved.

Reading in the data

# R
library(tidyverse)

purchases <- read_csv("purchases.csv")
purchases |> head()

# A tibble: 6 × 3
  country amount discount
  <chr>    <dbl>    <dbl>
1 USA       2000       10
2 USA       3500       15
3 USA       3000       20
4 Canada     120       12
5 Canada     180       18
6 Canada    3100       21

This is basically the same in pandas. So far so good!

# Python 
import pandas as pd

purchases = pd.read_csv("purchases.csv")
purchases.head()

  country  amount  discount
0     USA    2000        10
1     USA    3500        15
2     USA    3000        20
3  Canada     120        12
4  Canada     180        18

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

# R
purchases$amount |> sum()

[1] 17210

This is also similar in pandas:

# Python 
purchases["amount"].sum()

17210

(However, 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. In R, it’s always the same built-in sum() function.)

“Ah, they wanted it by country…”

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

# A tibble: 11 × 2
   country   total
   <chr>     <dbl>
 1 Australia   600
 2 Brazil      460
 3 Canada     3400
 4 France      500
 5 Germany     570
 6 India       720
 7 Italy       630
 8 Japan       690
 9 Spain       660
10 UK          480
11 USA        8500

This is also very similar in Python:

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

country
Australia     600
Brazil        460
Canada       3400
France        500
Germany       570
India         720
Italy         630
Japan         690
Spain         660
UK            480
USA          8500
Name: amount, dtype: int64

Ah, but here we actually need to do more work. 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:

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

      country  total
0   Australia    600
1      Brazil    460
2      Canada   3400
3      France    500
4     Germany    570
5       India    720
6       Italy    630
7       Japan    690
8       Spain    660
9          UK    480
10        USA   8500

(Another thing that’s new here is that we now have to pass the sum method as a "sum" string.)

“And I guess I should deduct the discount.”

A tiny change in R…

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

# A tibble: 11 × 2
   country   total
   <chr>     <dbl>
 1 Australia   540
 2 Brazil      414
 3 Canada     3349
 4 France      450
 5 Germany     513
 6 India       648
 7 Italy       567
 8 Japan       621
 9 Spain       594
10 UK          432
11 USA        8455

… but a large change in Python. The .agg() method can only aggregate single columns. When this is not the case we have to fall back on .apply(), which can handle any type of aggregation. As we want to avoid a column with the enigmatic name 0, we also have to use .rename() to get back to total, again.

# Python 
(purchases
  .groupby("country")
  .apply(lambda df: (df["amount"] - df["discount"]).sum()) #👈
  .reset_index()
  .rename(columns={0: "total"})                            #👈
)

      country  total
0   Australia    540
1      Brazil    414
2      Canada   3349
3      France    450
4     Germany    513
5       India    648
6       Italy    567
7       Japan    621
8       Spain    594
9          UK    432
10        USA   8455

“Oh, and Maria asked me to remove any outliers.”

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

# A tibble: 11 × 2
   country   total
   <chr>     <dbl>
 1 Australia   540
 2 Brazil      414
 3 Canada      270
 4 France      450
 5 Germany     513
 6 India       648
 7 Italy       567
 8 Japan       621
 9 Spain       594
10 UK          432
11 USA        1990

This is also a simple change in Python, using .query():

# Python 
(purchases
  .query("amount <= amount.median() * 10") #👈
  .groupby("country")
  .apply(lambda df: (df["amount"] - df["discount"]).sum())
  .reset_index()
  .rename(columns={0: "total"})
)

      country  total
0   Australia    540
1      Brazil    414
2      Canada    270
3      France    450
4     Germany    513
5       India    648
6       Italy    567
7       Japan    621
8       Spain    594
9          UK    432
10        USA   1990

(But why is it called .query() when it filters? And why can’t we use DataFrame.filter() instead? Ah, that only filters on the index names. And why do we suddenly have to pass in Python code as a string? Ah, it’s actually not Python, but a language that’s similar to Python. Of course, all these questions have explanations, yet I still can never really remember what I’m allowed to put in a .query() string. Instead of .query() we could use .loc[], but then we need to do a fair bit of typing: .loc[lambda df: df["amount"] <= df["amount"].median() * 10]. Compare that to the R version filter(amount <= median(amount) * 10))

“I probably should use the median within each country”

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

# A tibble: 11 × 2
   country   total
   <chr>     <dbl>
 1 Australia   540
 2 Brazil      414
 3 Canada      270
 4 France      450
 5 Germany     513
 6 India       648
 7 Italy       567
 8 Japan       621
 9 Spain       594
10 UK          432
11 USA        8455

What’s just swapping two lines in R, becomes much more involved in Python. The reason for this is that .groupby() doesn’t return a pandas.DataFrame, it returns a pandas.api.typing.DataFrameGroupBy object, which doesn’t have the same set of methods as a regular data frame. Especially, it doesn’t have .query() nor .loc[]. There are two solutions here: A first solution is that we fall back on .apply(), this time returning a filtered version of each group, but then we also need to remove the country index completely with .reset_index(drop=True) as the filtered purchases already has a country column:

# Python 
(purchases
  .groupby("country")                                               #👈
  .apply(lambda df: df[df["amount"] <= df["amount"].median() * 10]) #👈
  .reset_index(drop=True)                                           #👈
  .groupby("country")
  .apply(lambda df: (df["amount"] - df["discount"]).sum())
  .reset_index()
  .rename(columns={0: "total"})
)

      country  total
0   Australia    540
1      Brazil    414
2      Canada    270
3      France    450
4     Germany    513
5       India    648
6       Italy    567
7       Japan    621
8       Spain    594
9          UK    432
10        USA   8455

(The fact that grouped and regular pandas data frames have different APIs is a constant source of confusion, to me. One example of this is .filter(), where DataFrameGroupBy.filter() does something completely different from DataFrame.filter(). And none of them actually filter away values!)

A second solution is that we first calculate the median amount per country and assign it to each row in purchases. The upside is now that we can continue to use .query(), but at the cost of introducing both .assign() and .transform() into the mix.

# Python 
(purchases
  .assign(country_median=lambda df:                         #👈
      df.groupby("country")["amount"].transform("median")   #👈
  )
  .query("amount <= country_median * 10")                   #👈                   
  .groupby("country")
  .apply(lambda df: (df["amount"] - df["discount"]).sum())
  .reset_index()
  .rename(columns={0: "total"})
)

      country  total
0   Australia    540
1      Brazil    414
2      Canada    270
3      France    450
4     Germany    513
5       India    648
6       Italy    567
7       Japan    621
8       Spain    594
9          UK    432
10        USA   8455

Compare this with, again, the final R solution:

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

This solution is not only shorter but also contains less ‘boilerplate’ code, such as lambda, reset_index, etc. The journey to the R solution was more straight forward and we could build it up one step at a time. With pandas, we often had to backtrack and switch out parts of the intermediate solution.

So, what’s your point?

My point is that, if you’re a “Python person”, then pandas is a great tool and people with extensive R experience may find working with pandas frustrating for valid reasons. Show them some compassion!

You might think my purchases analysis was just a little toy example, selected to highlight the clunkiness of the pandas API. And yes, partially, but my experience is that with larger, real-world code the problems with the pandas API, outlined in this post, remains. That is, pandas feels clunky when coming from R because:

• The naming of methods and arguments is often confusing (.filter() doesn’t filter values. Will .sum(axis=1) sum the rows or the columns?)
• Different methods are available for grouped and non-grouped data frames and methods with the same name can do very different things (for example DataFrame.filter() and DataFrameGroupBy.filter()).
• Many convenience function are missing from pandas, which means you’ll have to code them from scratch. For instance, moving the year to be the first column is df |> relocate(year) in the tidyverse. It’s df[["year"] + [col for col in df.columns if col != "year"]] in pandas.
• Pandas will constantly move columns into the index, and you’ll have to work hard to get that data out again. You’ll be typing .reset_index() many many times.