Create MapValue/dict with string keys and float values

[augcollet]

Hello,

I need your help to resolve a specific problem...

From the following data with postgresql backend :

import ibis
from ibis import _
import os

con = ibis.postgres.connect(
    user=os.getenv('POSTGRES_USER'),
    password=os.getenv('POSTGRES_PASSWORD'),
    host="postgres",
    port=os.getenv('POSTGRES_PORT'),
    database=os.getenv('POSTGRES_DB'),
)
ibis.set_backend(con)

t=ibis.memtable({
    'client_id':[0,1,0,2,3,0,1,2,3],
    'product':['a', 'b', 'a', 'a', 'b', 'c', 'a', 'a', 'b'],
    'amount':[1.2, 2.5, 4.2, 12.7, 1.2, 3.8, 1.4, 3.8, 3],
})

I'm trying to perform the following calculation :

I tried the following approach :

data=(
    t.group_by(['client_id', 'product'])
    .agg(
        sum_amount=_['amount'].sum()
    )
    .group_by(['client_id'])
    .agg(
        products_and_sum_amounts=ibis.map(
            _['product'].collect(),
            _['sum_amount'].collect()
        )
    )
)
data.execute()

I get the following error :

It seems that ibis uses hstore to store data from a .map, which is incompatible with numeric values.

I have to cast the values ​​to a string before using .collect to get a result.

How can I get around this? For example, how can I build a JSON object instead of MapValue?

( My goal is to exploit the resulting pandas dataset to use it with a DictVectorizer under sklearn.
https://scikit-learn.org/1.5/modules/generated/sklearn.feature_extraction.DictVectorizer.html )

Thank you in advance for your support!

[cpcloud]

This seems like a thing we might want to change, and we should probably move away from using hstore since it's a bit niche. At the time I decided to use it because it was the closest thing to a map in postgres (and implementation-wise I think it still is) but JSON is certainly much friendlier to users.

[cpcloud]

I can certainly take a look at using json/jsonb internally in the postgres backend to represent maps.

[cpcloud]

Closing in favor of #10484.