Feature suggestion: flexible hierarchical data (json) importer (will implement if interest exists)

[tkluck]

Hi there,

I recently wrote a very flexible module for flattening hierarchical (json) data into CSV: https://github.com/tkluck/Text-CSV-Flatten.

It encodes the exact semantics of the flattening by a pattern string. For example, the pattern .<index>.* flattens in the same way as orient="records". The pattern .*.<index> flattens in the same way as orient="columns".

The module is quite new and has already been very useful for me: I maintain an internal reporting tool for my employer, in which we expose hierarchical data. This module allows my users to download it as CSV with a minimum of effort on my side (no boilerplate) and on theirs.

I realized that the same thing might be very useful for Pandas. Not only does the pattern flatten the hierarchical data into rows and columns, it also encodes which columns are supposed to be an index. In CSV, this information in lost in the output format, but in a DataFrame, that remains meaningful.

This module fulfils a very similar function to what io.json.read_json, io.json.json_normalize and io.json.nested_to_record do. In fact, I think it can reproduce any of their semantics by just tweaking the pattern string.

I wouldn't mind at all to spend some time on adding this to Pandas. If I do that, would you be interested in merging it into your next version?

[jreback]

you would have to show some examples of use.

[tkluck]

Thanks @jreback for your comment. How does this look: https://github.com/tkluck/Text-CSV-Flatten/blob/master/t/Text-CSV-Flatten.t

[jreback]

what I actually want to know is how would this improve the semantics of existing pd.read_json and/or normalized_json in a nice usecase. Further performance consideration are pretty important here.

Ideally show a read_json operation and then doing something new/better with it. You can show pseudo (python) code inline here.

[tkluck]

I'll give some thought to come up with a pretty use-case. But I think what this shines in is mostly in non-pretty use cases: the case where you don't control the process that generates the json data for you, and it is almost-but-not-quite one of the formats that the existing functions support.

Maybe the data is in the right format except that it is wrapped in an {'data': <data>, 'error': null}. Maybe you need to join (think sql join here) columns from two paths into your data structure.

Of course, the solution to all of that is really quite simple: the boiler-plate loops that do the transformation are easy to write for everyone. But I think the patterns are much more concise.

My original use case is one where I want the hierarchical data to be exportable/downloadable as csv; the user can pass the pattern in the url (and we have default patterns for the most common reports). Basically, the patterns allow our users to do the exact data transformation that they want, on our servers, without actually executing any of their code. Of course, that's not really a concern for Pandas, but it gives some idea of where this idea is coming from.

[jreback]

@tkluck I understand the general use case. Looking for a specific example of improved syntax.

[tkluck]

Thanks for the reply! I'm not sure if I understand what you are looking for in a specific example. For example, would it be helpful if I translated one/a few of the unit test cases that I linked above to Python, and translate the expected output from CSV to a dataframe? Or are you (additionally) looking for a real-world data source that pandas currently cannot parse and that it would be able to with such an addition? Or something else entirely? Looking forward to your reply (but will likely read it only tomorrow; am in CET).

[jreback]

you can certainly transform one of those tests. I am looking for a sample input, then the transforming operations that a user would do to make this tabular.

[tkluck]

Here's an example:
from_json_suggestion.txt
(my apologies for uploading this as a text file; github doesn't allow attaching .py files). The function load_dataframe is what would currently be needed to flatten the data in a certain way, the function load_dataframe_new is what I'm proposing.

I've now realized that this may be more generally useful than just with json data. There's quite some methods that take hierarchical data of some form and transform it into a dataframe: DataFrame.from_dict, .from_records, .from_items. The orient parameter gives each of those a few different options. Here's how they map to patterns:

• from_dict(..., orient="columns") is the same as can be gotten from the pattern .*.<index_name>
• from_dict(..., orient="index") is the same as .<index_name>.*
• from_records(...) is the same as .<index_name>.*
• from_items(..., orient="columns") is the same as .*.1.<index_name>
• from_items(..., columns=..., orient="index") is (almost) the same as .<index>.0 .<index>.1.* (which also needs a column_name=... argument).

Obviously, I'm not suggesting replacing those or anything; I'm just trying to show how versatile this pattern system is.

[rhshadrach]

Closing as stale. Comment here if there is still interest in pursuing this and we can reopen.