Create more user friendly aliases from `col`

[timsaucer]

Is your feature request related to a problem or challenge? Please describe what you are trying to do.
This is a user enhancement request to make the python interface remove the default ?table? on columns.

Describe the solution you'd like
I have not yet reviewed the applicable code to offer a best solution yet. I'm adding this feature request to track.

Describe alternatives you've considered
User adds an .alias() command as below, which may be tedious.

Additional context
See the below example from @datapythonista that includes the desired use case

import datafusion
from datafusion import col, lit, functions as f
import pyarrow


# something like this would be implemented internally, so users can call `datafusion.read_*`
def _read_parquet(*args, **kwargs):
    ctx = datafusion.SessionContext()
    return ctx.read_parquet(*args, **kwargs)
datafusion.read_parquet = _read_parquet  # creating an alias of `read_*` functions so users don't need to know about `SessionContext` when the defaults are fine


df = (datafusion.read_parquet("buildings.parquet")
                .filter(  # `.filter()` accepting multiple conditions (which will be an AND) instead of having to use `&` with its operator precedence problems
                    col("is_offplan") == False,
                    col("rooms") >= 2,  # `.lit(2)` not being required, and Python literals working with operators
                )
                .aggregate(
                    [col("area_name_en")],
                    [f.mean(col("has_parking").cast(float))],  # `.cast()` accepting Python types, which would be internally converted to the PyArrow equivalent
                )
                .select(
                    col("area_name_en").alias("Area"),
                    col("AVG(has_parking)").alias("Percentage of buildings with parking"),  # removing the default `?table?` in column names, the column name was "AVG(?table?.has_parking)"
                )
     )

[Spaarsh]

@timsaucer I have been trying to triage this for a while.

The corresponding code where the ?table? string gets added in the datafusion is this:

pub const UNNAMED_TABLE: &str = "?table?";

From what I understood by going through datafusion-python's codebase, when we want to create a binding of some df code, we create a file in the src directory, import the relevant module there and over-right or extend the existing code.

I looked up for "logical" in the datafusion-python repo and came across this file: logical.rs. So I think changing the code here for removing the ?table? would be the way forward. Am I correct?

[sidshehria]

@timsaucer

Problem Statement:

• When using DataFusion in Python, column names often include a default alias like ?table? .

• This can make the column names less user-friendly when working with expressions such as .select() , .filter() , or .aggregate() .

• Example: A column name like "AVG(?table?.has_parking)" instead of just "AVG(has_parking)".

Proposed Solution:

• The goal is to automate aliasing internally, so users do not have to manually call .alias() every time.

Alternatives Considered:

• Manually using .alias() , but this is tedious and not user-friendly.

Solution Implementation

The best way to solve this issue is to modify the DataFusion API internally to:

• Remove ?table? from column names automatically.

• Ensure operators like == , >= , and .cast() work naturally with Python literals.

• Create an alias for read_parquet() so that users don’t need to manually handle SessionContext .

After going through datafusion-python 's codebase I think that we should focus on the following files:

1. datafusion/dataframe.py:

• Purpose: This file defines the DataFrame class and its associated methods, including .select(), .filter(), and .aggregate().

• Modification: Within these methods, implement logic to automatically remove or replace the default ?table? prefix in column names, thereby generating cleaner and more user-friendly aliases.

2. python/src/datafusion/dataframe.rs:

• Purpose: This Rust source file defines the DataFrame struct and its associated methods, including .select(), .filter(), and .aggregate().

• Modification: Within these methods, implement logic to automatically remove or replace the default ?table? prefix in column names, thereby generating cleaner and more user-friendly aliases.

[timsaucer]

I think @Spaarsh is on the right track for this one, that it more likely needs to be resolved in the upstream repo. Or we would have to do some form of work around.

In the file datafusion/expr/src/udaf.rs is where these column names are ultimately coming from it looks like. The column starts off as unqualified, but as the plan is built it gets the table projection and becomes qualified. Since there is no table specified the default is assigned.

My thinking is that what we want to do is to update the logic upstream such that when we add in that qualifier we compare it to the default name. If they match, we wrap it in an alias without the qualifier. We could potentially do this under a feature flag depending on how people feel about it.

--

As a side note, the above user request had a few things in it. This issue was just about the aliasing. The other topics - alias for read_parquet, using literals, etc - should not be included. Those are partially or fully complete anyways.

[Spaarsh]

@timsaucer I was thinking of going down the route of writing some workaround to this in our python interface since the issue originally mentioned it. Changing the logic upstream will take discussion. Should we implement a workaround here until then?

Either way I suppose we need to open an issue for this in the datafusion repo.

I am more towards making a workaround here first since people using python for working with data have a higher need for aliasing. They normally want to export the output in parquets (in this case) and/or refer to the output later down the pipeline. So this may significantly ease their woes. I am not sure of its impact on the upstream users.

[deanm0000]

Another potential friendly alias method is to use **kwargs in select and aggregate. Here's a select implementation:

    def select(self, *exprs: Expr | str, **kwargs: Expr | str) -> DataFrame:

        exprs_internal = [
            Expr.column(arg).expr if isinstance(arg, str) else arg.expr for arg in exprs
        ]
        for name, expr_or_str in kwargs.items():
            if isinstance(expr_or_str, str):
                expr_or_str=Expr.column(expr_or_str).expr
            exprs_internal.append(expr_or_str.alias(name))
        return DataFrame(self.df.select(*exprs_internal))