Asyncpg: pandas dataframe

[kernelmachine]

How would you recommend converting Records to a pandas dataframe?

Also, what do you think about giving fetch the option of returning a dataframe instead of list? There might be performance concerns.

[jettify]

What is your use case? Sync driver + sqlalchemy usually is more then
sufficient for such things, you even have it for free
http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_sql.html

On Fri, 5 Aug 2016 at 13:26 Suchin [email protected] wrote:

How would you recommend converting Records to a pandas dataframe?

Also, what do you think about giving fetch the option of returning a
dataframe instead of list? There might be performance concerns.

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
https://github.com/MagicStack/asyncpg/issues/17, or mute the thread
https://github.com/notifications/unsubscribe-auth/AANoZ9YrtEJwJj72uUlP2dMZrv6ErQ5Gks5qc3JjgaJpZM4Jd5HD
.

[1st1]

I'm also curious to hear about the use cases. Also, does Pandas provide an async API?

We can add an API to register a custom row-decoder callback (function accepting bytes and returning decoded row). That would make it possible for Pandas to implement their own decoding to any kind of data-types.

[wesm]

Efficiently decoding data to the exact memory representation that pandas requires is currently somewhat complicated. It uses NumPy arrays as its internal representation, but null-handling adds some complexity

• float32/float64 columns — normal np.float32/float64 arrays with NaN for nulls
• any integer columns: for no nulls, use int8/16/32/64, otherwise use float64 (with nulls encoded as NaN). Users should be warned about data loss for int64 values outside the representable range with floating point numbers
• boolean columns: numpy.bool_ for no nulls, and numpy.object_ if there are nulls (encode null as None)
• string columns: numpy.object_ dtype with PyString/PyUnicode objects internally. Use None to encode nulls. It is preferable to avoid creating two copies of the same string (see https://github.com/cloudera/hs2client/blob/master/python/hs2client/converters.h#L193 where I deal with this problem for HiveServer2)
• datetime columns: nanoseconds since the UNIX epoch as numpy.int64 (logically: datetime64[ns]) array

The business logic for producing these arrays is best written in C/C++/Cython with limited involvement of the Python C API (only where numpy.object_ arrays are involved). I am happy to help advise on the implementation / do some code review, the C++ code in https://github.com/cloudera/hs2client/blob/master/python/hs2client/converters.h can form a reference for what pandas expects in the NumPy arrays (it does not do timestamp conversion — depending on PG's datetime representation you may be able to perform the conversion in C or leave it to pandas's internal timestamp methods to convert).

pandas does not have any async APIs at the moment AFAIK

[wesm]

As a possible added benefit for pandas users, it would be nice to have the option for any string columns to be returned as pandas categorical dtype, which has significant performance benefits in analytics

[1st1]

@wesm I think we can built-in this functionality into asyncpg -- instead of Records we can return a list of columns (numpy arrays, with the decoding semantics you've outlined). Would that be enough to integrate pandas?

Also, would it be possible to provide a benchmark that we can tweak to use asyncpg and work with?

[wesm]

Yeah, that would definitely work. Could put this NumPy deserializer in an optional extension so that people can still use asyncpg if they don't have NumPy installed (since you will have to build against NumPy's headers, etc.)

We should be able to help kick the tires and come up with some typical benchmarking scenarios (i.e. numeric heavy reads, string heavy reads, etc.)

[1st1]

Yeah, that would definitely work. Could put this NumPy deserializer in an optional extension so that people can still use asyncpg if they don't have NumPy installed (since you will have to build against NumPy's headers, etc.)

I actually wanted to make an optional dependency. Let's say hide this functionality behind a argument to the connect function (return_numpy_dataframes=False).

We should be able to help kick the tires and come up with some typical benchmarking scenarios (i.e. numeric heavy reads, string heavy reads, etc.)

That is something we need to have before we can start the process (we aren't Pandas users ourselves).

[wesm]

That is something we need to have before we can start the process (we aren't Pandas users ourselves).

It sounds like what you need is a test suite, not benchmarks, am I interpreting that right?

[1st1]

It sounds like what you need is a test suite, not benchmarks, am I interpreting that right?

Sorry, I should have clarified my request. I wanted to ask for a small script that uses one type (say int32), fetches some data from the DB and performs some rudimentary calculation. We than could use that script to prototype the implementation and see how it compares to existing solutions. In any case, never mind, I think can jot a simple Pandas script myself.

[mangecoeur]

I was interested in attempting to implement this - I did some naive benchmarks using asyncpg to load large tables instead of psycopg and I saw about 3x speed improvement. I suspect it could be possible to load data into pandas tables even faster by generating numpy arrays directly in the cython layer. Any pointers on where to start poking around would be welcome - I am aiming to just produce some simple benchmark to start with, to see if its worth pursuing lower level integration (as opposed to just using the normal asyncpg interface).

(Edit: context, I actually wrote quite a bit of the pandas sqlalchemy based SQL interface but i was never too satisfied with performance and type support).

[elprans]

@mangecoeur Interface-wise, it would make most sense to integrate with Connection.copy_*. Basically, the algorithm for "copy_from_*_to_pandas" would look something like this:

1. Introspect the source relation, and to get the types of the attributes.
2. Initialize the dataframe accordingly.
3. Build a codec profile (see _ensure_rows_decoder), using decoder versions that produce pandas native format as output.
4. Initiate COPY OUT ... (FORMAT binary), and decode the data chunks into the target dataframe.

Step number 3 is the tricky part. For this whole thing to make sense performance-wise, the decoding pipeline must not dip into the interpreter loop, thus the decoders must be plugged in as C function pointers (maybe as a special argument to copy*()).

[wesm]

From a data representation point of view, it may also be worth looking at Apache Arrow as an intermediary to use -- then you don't have to deal with all the weird pandas stuff and just deal with strongly-typed nullable columns. The turbodbc folks have been using Arrow for data en route to/from pandas and that's been working very well cc @mathmagique @xhochy

[mangecoeur]

Thanks for the pointers, i will try to implement some really simple cases just to see what the performance is like, if it's promising i could follow @wesm suggestion and integrate with Arrow (although turbodbc looks a bit baroque wrt mixing C++ and python, compared to cython only).

(Edit: I notice that there is a WIP cython api for arrow)

[xhochy]

The main thing you will benefit from in Arrow is that you get a simple construction of the columnar buffers using the Builder classes. The transformation from the Arrow structures to a Pandas DataFrame is then taken care of from the Arrow side. As Arrow is simpler structured then Pandas, the implementation is much simpler, still very efficient.

API-wise, I have used Arrow with Cython, boost::python and pybind11. If you're fluent in C++ and Python, then pybind11 is the best option from my perspective as you get the best of both worlds. Cython is the more natural choice when you come from a Python-only perspective but limiting you in the use of the C++ as not all C++ features are supported in Cython. Neverthelesse, in Arrow, we want to support all three options and it should be simple to to use with Cython. Always happy to help you to get it working independent of the wrapper ;)

[deepers]

I would definitely be interested in being able to return the results of a query as a sequence of NumPy arrays representing the columns of the result. I don't use pandas very much, but I do use NumPy, and I would love an API that allows the user to pass in column dtypes and null handling instructions.

[jedmitten]

I generally use

res = await pool.fetch(...)
df = pd.DataFrame([dict(r) for r in res])

[foxx]

I generally use

res = await pool.fetch(...)
df = pd.DataFrame([dict(r) for r in res])

This didn't work for me, I had to use:

dfs = pd.DataFrame([dict(r.items()) for r in results ])

[RiccardoGiro]

Any news/updates on this enhancement?