DuckDB Group by Pandas Index

I have a pandas dataframe with a multiindex. How can I reference the indexes in a duck db query?

import duckdb
import pandas as pd
import numpy as np

df = pd.DataFrame({
  'i1': np.arange(0, 100),
  'i2': np.arange(0, 100),
  'c': np.random.randint(0 , 10, 100)
}).set_index(['i1', 'i2'])

>>> duckdb.query('select sum(c) from df group by i1')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
RuntimeError: Binder Error: Referenced column "i1" not found in FROM clause!
Candidate bindings: "df.c"

DuckDB Group by Pandas Index

0 Answers