Normalisieren Sie eine Listenspalte in Duckdb SQLPython

Python-Programme
Anonymous
 Normalisieren Sie eine Listenspalte in Duckdb SQL

Post by Anonymous »

Sag, ich habe: < /p>

Code: Select all

import polars as pl

df = pl.DataFrame({'a':[1,1,2], 'b': [4,5,6]}).with_columns(c=pl.concat_list('a', 'b'))

print(df)
< /code>
shape: (3, 3)
┌─────┬─────┬───────────┐
│ a   ┆ b   ┆ c         │
│ --- ┆ --- ┆ ---       │
│ i64 ┆ i64 ┆ list[i64] │
╞═════╪═════╪═══════════╡
│ 1   ┆ 4   ┆ [1, 4]    │
│ 1   ┆ 5   ┆ [1, 5]    │
│ 2   ┆ 6   ┆ [2, 6]    │
└─────┴─────┴───────────┘
< /code>
I can normalise column 'c' by doing:
In [15]: df.with_columns(c_normalised = pl.col('c') / pl.col('c').list.sum())
Out[15]:
shape: (3, 4)
┌─────┬─────┬───────────┬──────────────────────┐
│ a   ┆ b   ┆ c         ┆ c_normalised         │
│ --- ┆ --- ┆ ---       ┆ ---                  │
│ i64 ┆ i64 ┆ list[i64] ┆ list[f64]            │
╞═════╪═════╪═══════════╪══════════════════════╡
│ 1   ┆ 4   ┆ [1, 4]    ┆ [0.2, 0.8]           │
│ 1   ┆ 5   ┆ [1, 5]    ┆ [0.166667, 0.833333] │
│ 2   ┆ 6   ┆ [2, 6]    ┆ [0.25, 0.75]         │
└─────┴─────┴───────────┴──────────────────────┘
< /code>
How can I do this in DuckDB? I've tried
In [17]: duckdb.sql("""
...: from df
...: select c / list_sum(c)
...: """)
---------------------------------------------------------------------------
BinderException                           Traceback (most recent call last)
Cell In[17], line 1
----> 1 duckdb.sql("""
2 from df
3 select c / list_sum(c)
4 """)

BinderException: Binder Error: No function matches the given name and argument types '/(BIGINT[], HUGEINT)'. You might need to add explicit type casts.
Candidate functions:
/(FLOAT, FLOAT) -> FLOAT
/(DOUBLE, DOUBLE) -> DOUBLE
/(INTERVAL, BIGINT) -> INTERVAL

Quick Reply

Change Text Case: 
   
  • Similar Topics
    Replies
    Views
    Last post