Seemingly inconsistent column reference syntax when chaining methods on pandas data frames

Question

I am a little confused as to why the syntax for referring to a column within a pandas data frame differs depending on which method is being called. Take the following sample method chain

import pandas as pd

iris = pd.read_csv('https://raw.githubusercontent.com/mwaskom/seaborn-data/master/iris.csv')
iris.columns = ['SepalLength', 'SepalWidth', 'PetalLength', 'PetalWidth', 'Species']
(iris
    .loc[:, ['SepalLength', 'PetalWidth', 'Species']]
    .where(iris['SepalLength'] > 4.6)
    .assign(PetalWidthx2 = lambda x_iris: x_iris['PetalWidth'] * 2)
    .groupby('Species')
    .agg({'SepalLength': 'mean', 'PetalWidthx2': 'std'}))

Here, there are three different kinds of syntax used to refer to columns within the iris data frame:

loc, groupby, and agg all understand that a string refers to a column in the data frame.
where needs the data frame to be explicitly referenced.
Explicitly referring to the data frame in the assign method would cause the operation to be performed on the original iris data frame, and not the copy that has been modified by the calls to loc and where. Here, lambda is needed to refer to the current state of the modified data frame copy.
In addition to the above, there is also query, which takes the entire method input as a string: iris.query('SepalLength > 4.6'), but here the pandas documentation explicilty states that this is for special use cases:

A use case for query() is when you have a collection of DataFrame objects that have a subset of column names (or index levels/names) in common. You can pass the same query to both frames without having to specify which frame you’re interested in querying

To provide an example of what I mean by consistent data frame column reference syntax, a comparison could be made to the R-package dplyr, where columns in the data frame are referenced with the same syntax for all the piped function calls.

library(dplyr)

# The iris data set is preloaded in R
colnames(iris) = c('SepalLength', 'SepalWidth', 'PetalLength', 'PetalWidth', 'Species')
iris %>% 
    select(SepalLength, PetalWidth, Species) %>% 
    filter(SepalLength > 4.6) %>%  
    mutate(PetalWidth2x = PetalWidth * 2) %>% 
    group_by(Species) %>% 
    summarise(SepalLength = mean(SepalLength), PetalWidth2x = sd(PetalWidth2x))

Are there advantages that pandas gains from having these different ways of referring to data frame columns, instead of applying the simplistic syntax used by loc, groupby and agg to all the methods (if so, which are these benefits)? Or is this more of a workaround for some underlying issue with using strings for data frame column names in the assign and where methods?

user_5088165 · Answer

datar implements dplyr syntax, without confusing the reference:

>>> from datar.all import f, select, filter, mutate, group_by, summarise, mean, sd
[2021-06-24 15:20:02][datar][WARNING] Builtin name "filter" has been overriden by datar.
>>> from datar.datasets import iris
>>> 
>>> iris >> \
...     select(f.Sepal_Length, f.Petal_Width, f.Species) >> \
...     filter(f.Sepal_Length > 4.6) >> \
...     mutate(PetalWidth2x = f.Petal_Width * 2) >> \
...     group_by(f.Species) >> \
...     summarise(SepalLength=mean(f.Sepal_Length), PetalWidth2x=sd(f.PetalWidth2x))
      Species  SepalLength  PetalWidth2x

Seemingly inconsistent column reference syntax when chaining methods on pandas data frames

2 Answers