Rename count column without extra select statement

[saschahofmann]

I am looking for a way to rename an aggregation column e.g. produced by .count() without ibis performing an extra select statement. Consider the following

import ibis
table = ibis.table(
    [
        ("id", "int32"),
        ("athlete", "string"),
        ("birthday", "date"),
        ("stars", "double"),
    ],
    name="olympians",
)

query = table.group_by(table.birthday).count()

ibis.bigquery.compile(query)

this returns

SELECT
  t0.`birthday`,
  count(1) AS `CountStar_olympians`
FROM olympians AS t0
GROUP BY
  1

First of all, it's not obvious to me how the column name was selected? Why is it called CountStar and not e.g. CountId?

I figured out that I can get a predictable column using .rename like this

ibis.bigquery.compile(query.rename(**{'count': query.columns[1]}))

but it wraps the query in a second select statement

SELECT
  t0.`birthday`,
  t0.`CountStar_olympians` AS `count`
FROM (
  SELECT
    t1.`birthday`,
    count(1) AS `CountStar_olympians`
  FROM olympians AS t1
  GROUP BY
    1
) AS t0

I doubt it has any performance implications but still it seems avoidable? Maybe the count function could accept an optional keyword argument name? So I could do something like this

ibis.bigquery.compile(table.group_by(table.birthday).count(name='count'))

returning

SELECT
  t0.`birthday`,
  count(1) AS `count`
FROM olympians AS t0
GROUP BY
  1

As a side note, I found the .rename syntax confusing shouldn't it be rename(old_name=new_name), I am pretty sure that is how its done in Pandas?

[lostmygithubaccount]

hi @saschahofmann, if you want to explicitly name your aggregations you can do something like this:

[ins] In [1]: import ibis

[ins] In [2]: ibis.options.interactive = True

[ins] In [3]: t = ibis.examples.penguins.fetch()

[nav] In [4]: expr = t.group_by(t.species).agg(CountPenguins=ibis._.count())

[ins] In [5]: expr
Out[5]:
┏━━━━━━━━━━━┳━━━━━━━━━━━━━━━┓
┃ species   ┃ CountPenguins ┃
┡━━━━━━━━━━━╇━━━━━━━━━━━━━━━┩
│ string    │ int64         │
├───────────┼───────────────┤
│ Adelie    │           152 │
│ Chinstrap │            68 │
│ Gentoo    │           124 │
└───────────┴───────────────┘

[ins] In [6]: ibis.to_sql(expr)
Out[6]:
SELECT
  t0.species,
  COUNT(*) AS "CountPenguins"
FROM main.penguins AS t0
GROUP BY
  1

or alternatively:

expr = t.group_by(t.species).agg(ibis._.count().name("CountPenguins"))

As a side note, I found the .rename syntax confusing shouldn't it be rename(old_name=new_name), I am pretty sure that is how its done in Pandas?

this feedback is definitely heard, but the rename syntax allows for better consistency with aggregations, mutations, and other parts of the Ibis API (like the above count example). this is a preview of our upcoming release blog where it's noted: https://pr-8200-fb33b2bc5070e5321315494001c92659ed0d9083--ibis-quarto.netlify.app/posts/ibis-v8-release/#rename -- this blog will be released sometime next week

[saschahofmann]

Ah great! Will definitely give that a try and then I can also see how that ties in with the renaming syntax. Thanks for the quick response

[saschahofmann]

Couldnt figure out how to mark above reply as answer but closing