Table serialisation to JSON

@adonath

@adonath and I started to implement functions to serialise astropy.table.Table objects to JSON in gammapy/gammapy#441 because we and @kepta want to build web pages that display the catalog info.

This is the example we're currently interested in dumping to JSON:

>>> from astropy.table import Table
>>> table = Table.read('https://github.com/gammapy/gammapy-extra/blob/master/datasets/catalogs/fermi/gll_psch_v08.fit.gz?raw=true')

We're running into the issue that Numpy arrays need special attention to be JSON serialisable, see here or here.

Next I thought I'd use pandas.DataFrame.to_json, but the to_pandas call fails:

>>> table.to_pandas()
...
ValueError: Cannot convert a table with multi-dimensional columns to a pandas DataFrame

So this would be my first feature request: how about adding an option to astropy.table.Table.to_pandas to drop multi-dimensional columns? The same option would be useful to have here:

> table.write('/tmp/test.ecsv', format='ascii.ecsv')
...
ValueError: ECSV format does not support multidimensional column 'Unc_Flux50_171GeV'

Maybe there is already a convenience method to drop those columns?

Side comment: It would also be nice to have a convenience method to serialise Table objects to text without temp files that basically does this:

from io import StringIO
with StringIO() as fh:
    table.write(fh, format='ascii.ecsv')
    text = fh.getvalue()
return text

Finally, I guess my main feature request is: implement support for JSON and / or ECSV serialisation of multidimensional columns somehow.

@taldcroft - What do you think?
For the smaller feature requests I could probably make a PR if you comment if you'd like to have them and what the API should be.

@cdeil

@cdeil - I've been thinking about this..

First on the easier bits, there is no convenience method for dropping multi-dim columns, but it's pretty easy:

cols = tuple(name for name in table.colnames if len(table[name].shape) <= 1)
t2 = table[cols]

It might be worth putting in a way of controlling what happens when there are columns that cannot be converted, i.e. silently drop, issue warning and drop, or raise exception. With to_pandas() there is no way around the limitation in that back-end. With ecsv one could add support for multi-dim columns, though I have not yet thought of an elegant implementation.

On the bigger picture of serializing tables to JSON, can you give me a quick synopsis of why you need JSON? Do you have some javascript front-end that needs this, or is most everything in a Python framework? Even though the standard itself is well defined, using it to hold tabular data basically means using/defining a new standard, e.g. http://dataprotocols.org/json-table-schema/. So just making the table into a JSON format does not immediately make it universally readable. (Please understand that I'm pretty naive about a lot of web technology so my questions are a bit basic).

If I were going to make a JSON serialization I would probably start with the ECSV infrastructure but put the columns out as flat JSON arrays. Multi-dim columns would be flattened first and then reconstituted using the shape parameter.

@kepta

On the bigger picture of serializing tables to JSON, can you give me a quick synopsis of why you need JSON? Do you have some javascript front-end that needs this, or is most everything in a Python framework?

Yes, we want to read and display the data from some source catalogs that contain array-valued columns on a webpage. Here's a demo that @kepta has built recently (that doesn't display any data from the array-valued columns yet): page, code.

I managed to write a simple function that does what we need:

def table_to_python(table):
    """Convert Astropy Table to Python dict.

    Numpy arrays are converted to lists, so that
    the output is JSON serialisable.

    Can work with multi-dimensional array columns,
    by representing them as list of list.
    """
    total_data = {}
    for name in table.colnames:
        data = table[name].tolist()
        total_data[name] = data
    return total_data

Examples: http://nbviewer.jupyter.org/gist/cdeil/5c1a31ae00b3a0eefe84

I've looked a bit at the http://datatables.net/ docs and didn't see anything concerning multi-dim columns.
Probably some manual javascript is needed to display that info. But still, I think this function is a useful first step -- dump all the data to JSON.

@taldcroft - Do you think adding a Table.to_python (or some other name, e.g. Table.to_dict) method would be useful enough to warrant adding it? Or add a small "Table serialisation" page at http://astropy.readthedocs.org/en/latest/table/index.html#i-o-with-tables ?

Same question for the code snippet you gave: would a Table.drop_multidim_colums() be worth adding? Or adding that snippet to the docs somewhere?

FYI, are you planning on pre-processing the available tables with something derived from fits_dump.py to have them available to the web page as JSON, or will this be some on-the-fly call to a conversion script (assuming you are running some Python web app framework at the core)?

Anyway, after looking at this it seems that having a JSON serializer for Table would be a good thing. I would think of this as an io.ascii reader/writer that starts from the ecsv model. For inclusion in the core it should support the features of ECSV, namely serializing table and column metadata and allowing for round-trip.

My preference would be to follow the data structure defined in APE-6. An important feature is to maintain the ordering of everything. Since JSON key/value pairs are unordered, I think one needs to use lists where ordering is required.

Is gammapy able to work on this? I understand this is probably more than you need...

@taldcroft

@taldcroft - I run into a somewhat related question, so it's probably not worth open a new issue.
We can now save Tables into JSON, but it's an ugly hack to be done via pandas, e.g. exo_hosts.to_pandas().to_json('data/exoplanet_hosts.json').

Do we plan to a reader/writer directly to astropy?

@bsipocz

@bsipocz - would YAML be acceptable? With the table.serialize machinery it would be reasonably straightforward to output a YAML serialization of a table. Basically like ECSV but with the column table data in YAML instead of an ASCII table. I've thought about this before as a big missing piece of the claim that astropy can serialize most core objects to YAML.

I'm less excited about JSON because it is not a "complete" solution and only works for simple tables. But I understand that JSON is more widely used. In the worst case we could just encode the to_pandas() workaround into astropy.

In this case the JSON were preferred as we needed to do less JS hacking in a project as all the examples we followed used JSON. Presumably all other ascii format would have worked eventually but no one was very excited with the prospects and started to question why we don't use pandas instead.

@bsipocz

@bsipocz - makes sense and sounds reasonable. In that case there is no huge driver for making a pure astropy to_json() that is basically doing what to_pandas().to_json() is doing.

Maybe just a docs addition mentioning to_pandas().to_json()? Question is of course where to put it so that people find interested in JSON serialisation will find it...

@cdeil

@cdeil - I wouldn't have found it if it were in the docs and not in the list of supported formats in the docstring, but then I don't use the package as I should.

Actually what might be pretty easy and fit in with the unified I/O scheme, and help with discovery, is to add a json format for reading and writing Table. It's just a few lines of code (and of course tests, blah blah), but then it shows up in:

I'm happy to take a stab on that, if it's basically just wrapping to_pandas().to_json(). It wasn't clear to me above in #4604 (comment) whether you would be against it or not.

By "pure astropy" I meant without using pandas as a conduit. But I very much like the idea of adding a new format since it fits in nicely to unified I/O and is self-documenting. So please do go ahead and do this.

Of course now I start thinking... and actually looked at the JSON that pandas outputs. It's quite specific to pandas (no surprise, and no different from astropy in that regard).

In [3]: t = simple_table()

In [4]: t.to_pandas().to_json('junk.json')

In [5]: cat junk.json
{"a":{"0":1,"1":2,"2":3},"b":{"0":1.0,"1":2.0,"2":3.0},"c":{"0":"c","1":"d","2":"e"}}

So I wonder if it would make sense to call the format json.pandas. The point is that there are many ways you can serialize a table to JSON (e.g. see http://paulfitz.github.io/dataprotocols/json-table-schema/ for a richer specification).

somewhat related question: is there a reason the yaml loader/dumper didn't end up in the unified list but with a different API?

The pandas json format seems very specific, I think it would be useful to have more generic formats e.g. list of rows (as dict) or maybe list of columns. Both should be easy to do (maybe not will all Table features though, e.g. masked values would be annoying) and easy to read (loading a list of dict is already possible). The list of rows could be useful in many contexts like web apps, datatables (https://www.datatables.net/examples/ajax/objects.html).

somewhat related question: is there a reason the yaml loader/dumper didn't end up in the unified list but with a different API?

currently it is only to serialize the metadata for others formats (ecsv, fits, etc.), but it cannot dump the data itself.

It's kind of a silly thing with YAML, which is that it can serialize array-like data for any mixin column, but not a pure Column. There is no good reason for this except that it wasn't a requirement for all the ECSV, FITS etc serialization work. But it would be straightforward to do (serialize Column, MaskedColumn, and Table/QTable), and then yaml could be a format. Do you think there is a use for this?

The pandas json format seems very specific, I think it would be useful to have more generic formats

Agreed, but a json.pandas format is very easy to implement and could be useful given the ubiquity of pandas. Not to say that someone with time / energy could not work on a more general JSON format. In that case I would argue to use a JSON table schema that is already available and popular. I have no insight into "popular" schemas.

But it would be straightforward to do (serialize Column, MaskedColumn, and Table/QTable), and then yaml could be a format. Do you think there is a use for this?

no, not really, I really just wondered.

Currently I'm only interested in adding the pandas json, just to have some json support in astropy. Beyond this (hackday project) I don't have much more interest to go into other schemas etc.

Did someone say hack day? I added the "sprint" label. 😄

@pllim

@pllim - I'm at astrohackweek. And since this need for a json output came up here, it's quite appropriate that I do some minimal fix for it 😄

This PR would also be useful for Cosmology I/O #11559.

@nstarman

@nstarman - we'll have YAML as a core dependency for 5.0. I was under the impression you would prefer ECSV in that case.

Looking back at the conversation, the simple idea of t.to_pandas().to_json() seems like it covers things.

we'll have YAML as a core dependency for 5.0. I was under the impression you would prefer ECSV in that case

🎉 . Yes I will much prefer ECSV. In #11559 I'm implementing the Table read/write methods, so any file format can be used. Just like for tables, ECSV will be preferred.
I do still want a JSON format, and pandas.json might well be good enough, for future interoperability with CLASS and CAMB, which are pretty minimal in their python wrappings. Python has a built-in JSON library, so won't require adding dependencies on their part.

Navigation Menu

Repository navigation

Table serialisation to JSON #4604

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Table serialisation to JSON #4604

Description

Activity

taldcroft commented on Feb 15, 2016

cdeil commented on Feb 16, 2016

taldcroft commented on Feb 16, 2016

taldcroft commented on Feb 16, 2016

bsipocz commented on Aug 7, 2018

taldcroft commented on Aug 8, 2018

bsipocz commented on Aug 8, 2018

taldcroft commented on Aug 8, 2018

cdeil commented on Aug 8, 2018

bsipocz commented on Aug 8, 2018

taldcroft commented on Aug 8, 2018

bsipocz commented on Aug 8, 2018

taldcroft commented on Aug 8, 2018

bsipocz commented on Aug 8, 2018

saimn commented on Aug 8, 2018

saimn commented on Aug 8, 2018

taldcroft commented on Aug 8, 2018

taldcroft commented on Aug 8, 2018

bsipocz commented on Aug 8, 2018

pllim commented on Aug 8, 2018

bsipocz commented on Aug 8, 2018

nstarman commented on May 6, 2021

taldcroft commented on May 6, 2021

nstarman commented on May 6, 2021

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions