Flattering is the tool to flatten, format, and export any JSON-like data to CSV (or any other output), no matter how complex or mixed the data is.

So, items like this:

{
    "name": "Product",
    "offers": [{"price": "154.95", "currency": "$"}],
    "sku": 9204,
    "images": [
        "https://m.site.com/i/9204_1.jpg",
        "https://m.site.com/i/9204_2.jpg",
        "https://m.site.com/i/9204_3.jpg"
    ],
    "description": "Custom description\non multiple lines.",
    "additionalProperty": [
        {"name": "size", "value": "XL"}, {"name": "color", "value": "blue"}
    ],
    "aggregateRating": {"ratingValue": 5.0, "reviewCount": 3}
}

will look like this:

• Flattering
  • Contents
  • Quickstart
  • CLI
  • What you can do
    • 1. Flatten data
    • 2. Rename columns
    • 3. Format data
    • 4. Filter columns
    • 5. Order columns
    • 6. Process invalid data
    • 7. Process complex data
    • 8. Export data
  • Arguments
    • StatsCollector
    • Exporter
  • Requirements

Flattering consists of two elements:

• StatsCollector, to understand how many columns are required, what headers they'll have, and what data is mixed/invalid (to skip or stringify).
• Exporter, to format and beatify the data, fit it in columns, and export it (as .csv or flat data).

item_list = [{"some_field": "some_value", "another_field": [1, 2, 3]}]
sc = StatsCollector()
sc.process_items(item_list)
exporter = Exporter(sc.stats["stats"], sc.stats["invalid_properties"])
exporter.export_csv_full(item_list, "example.csv")

You could use both parts on the same side or separately. For example, collect stats during a running job, and then provide them (tiny JSON with numbers) to the backend when a user wants to export the data.

Also, stats and items could be processed one by one (use append=True to append rows, if needed):

item_list = [{"some_field": "some_value", "another_field": [1, 2, 3]}]
sc = StatsCollector()
[sc.process_object(x) for x in item_list]
exporter = Exporter(sc.stats["stats"], sc.stats["invalid_properties"])
exporter.export_csv_headers("example.csv")
for item in item_list:
    exporter.export_csv_row(item, "example.csv", append=True)

When you provide the filename, the file will be opened to write/append automatically. If you want to open the file manually or write to any other form of StringIO, TextIO, etc. - check the 8. Export data section.

Plus, you can use the tool through CLI:

flattering --path="example.json" --outpath="example.csv"

CLI supports all the same parameters, you can get a complete list using the flattering -h command.

Let's pick an initial item to explain what parameters and formatting options do.

{
    "name": "Product",
    "offers": [{"price": "154.95", "currency": "$"}],
    "sku": 9204,
    "images": [
        "https://m.site.com/i/9204_1.jpg",
        "https://m.site.com/i/9204_2.jpg",
        "https://m.site.com/i/9204_3.jpg"
    ],
    "description": "Custom description\non multiple lines.",
    "additionalProperty": [
        {"name": "size", "value": "XL"}, {"name": "color", "value": "blue"}
    ],
    "aggregateRating": {"ratingValue": 5.0, "reviewCount": 3}
}

If you don't provide any custom options:

item_list = [item]
sc = StatsCollector()
sc.process_items(item_list)
exporter = Exporter(sc.stats["stats"], sc.stats["invalid_properties"])
exporter.export_csv_full(item_list, "example.csv")

the export will look like this:

Let's make it a bit more readable with headers_renaming:

renaming = [
    (r"^offers\[0\]->", ""),
    (r"^aggregateRating->", ""),
    (r"^additionalProperty->(.*)->value", r"\1")
]
exporter = Exporter(
    sc.stats["stats"],
    sc.stats["invalid_properties"],
    headers_renaming=renaming)

Better, but images take too much place. Let's group them in a single cell, using the name of the field and field_options. Fields could be grouped (all data in a single cell), named (create columns based on an object property), or both.

options = {"images": {"named": False, "grouped": True}}
exporter = Exporter(
    sc.stats["stats"],
    sc.stats["invalid_properties"],
    headers_renaming=renaming,
    field_options=options)

Looks even better, but we still have a lot of additionalProperty columns. Let's make them named, by using name property as the name of the column to make it better:

options = {
    "images": {"named": False, "grouped": True},
    "additionalProperty": {
        "named": True, "name": "name", "grouped": False
    }
}

Now we have a column with a value for each additionalProperty. But if you don't need separate columns for that, you can go even further and format them as both named and grouped:

"additionalProperty": {
    "named": True, "name": "name", "grouped": True
}

Also, let's assume we don't really need ratingValue and reviewCount in this export, so we want to filter them with headers_filters:

filters = [r".*ratingValue.*", ".*reviewCount.*"]
exporter = Exporter(
    sc.stats["stats"],
    sc.stats["invalid_properties"],
    headers_renaming=renaming,
    headers_filters=filters,
    field_options=options
)

It's important to remember that filters are regular expressions and work with the initial headers, so we're replacing aggregateRating->ratingValue and aggregateRating->reviewCount here.

And, to add a final touch, let's reorder the headers with headers_order. For example, I want Name and Sku as the first two columns:

order = ["name", "sku"]
exporter = Exporter(
    sc.stats["stats"],
    sc.stats["invalid_properties"],
    headers_renaming=renaming,
    headers_filters=filters,
    headers_order=order,
    field_options=options
)

All headers present in the headers_order list will be ordered, and other headers will be provided in the natural order they appear in your data. Also, we're sorting initial headers, so using name and sku in lowercase.

If your input has mixed types or invalid data, it could be hard to flatten it properly. So, you can decide - either skip such columns or stringify them.

For example, here the property changed type from dict to list:

item_list = [
    {"a": "a_1", "b": {"c": "c_1"}},
    {"a": "a_2", "b": [1, 2, 3]}
]
sc = StatsCollector()
sc.process_items(item_list)
exporter = Exporter(sc.stats["stats"], sc.stats["invalid_properties"])
exporter.export_csv_full(item_list, "example.csv")

By default, invalid properties would be stringified, so you'll get:

But if you want to skip them, you could set stringify_invalid parameter to False. It works at all level of nesting, and will affect only the invalid property, so items like this:

item_list = [
    {"a": "a_1", "b": {"c": "c_1", "b": "b_1"}},
    {"a": "a_1", "b": {"c": "c_2", "b": [1, 2, 3]}},
]
sc = StatsCollector()
sc.process_items(item_list)
exporter = Exporter(
    sc.stats["stats"],
    sc.stats["invalid_properties"],
    stringify_invalid=False
)
exporter.export_csv_full(item_list, "example.csv")

Will export like this:

Following the nesting, you can export and format data with any amount of nested levels. So, let's create a bit unrealistic item with multiple levels, arrays of arrays, and so on:

{
    "a": {
        "nested_a": [[
            {
                "2x_nested_a": {
                    "3x_nested_a": [
                        {"name": "parameter1", "value": "value1"},
                        {"name": "parameter2", "value": "value2"},
                    ]
                }
            },
        ]],
        "second_nested_a": "some_value",
    }
}

If we try to flatten it as is, it will work. However, headers will be a bit questionable, so let's show it as a code:

[
    "a->nested_a[0][0]->2x_nested_a->3x_nested_a[0]->name",
    "a->nested_a[0][0]->2x_nested_a->3x_nested_a[0]->value",
    "a->nested_a[0][0]->2x_nested_a->3x_nested_a[1]->name",
    "a->nested_a[0][0]->2x_nested_a->3x_nested_a[1]->value",
    "a->second_nested_a",
]
["parameter1", "value1", "parameter2", "value2", "some_value"]

But the best part is that we can format data (grouped, named) on any level, so with a bit of field_options magic:

"a->nested_a[0][0]->2x_nested_a->3x_nested_a": {
    "named": True, "name": "name", "grouped": True
}

It will look like this:

By default, all the data is exported to .csv, either in one go:

exporter = Exporter(sc.stats["stats"], sc.stats["invalid_properties"])
exporter.export_csv_full(item_list, "example.csv")

or one-by-one:

exporter.export_csv_headers("example.csv")
[exporter.export_csv_row(x, "example.csv", append=True) for x in item_list]

Also, you could use any writable input, like TextIO, StringIO, and so on, so all of the examples below will work:

# StringIO
buffer = io.StringIO()
exporter.export_csv_full(item_list, buffer)

# File objects
with open("example.csv", "w") as f:
    exporter.export_csv_full(item_list, f)

# Path-like objects
filename = tmpdir.join("example")
exporter.export_csv_full(item_list, filename)

We plan to support other formats, but for now you could also get flattened items one by one trough export_item_as_row method and write them wherever you want:

# [{"property_1": "value", "property_2": {"nested_property": [1, 2, 3]}}]
flattened_items = [exporter.export_item_as_row(x) for x in item_list]
# [['value', '1', '2', '3']]

• named_columns_limit int(default=50)

  How many named columns could be created for a single field. For example, you have a set of objects like {"name": "color", "value": "blue"}. If you decide to create a separate column for each name ("color", "size", etc.), the limit defines how much data would be collected to make it work. If the limit is hit (too many columns) - no named columns would be created in export. It's required to control memory usage and data size during stats collection (no need to collect stats for 1000 columns if you don't plan to have 1000 columns anyway).

• cut_separator str(default="->")

  Separator to organize values from items to required columns. Used instead of default "." separator. If your properties' names include the separator - replace it with a custom one.

• stats Dict[str, Header]

  Item stats collected by StatsCollector (stats_collector.stats["stats"]).

• invalid_properties Dict[str, str]

  Invalid properties data provided by StatsCollector (stats_collector.stats["invalid_properties"])

• stringify_invalid bool(default=True)

  If True - columns with invalid data would be stringified. If False - columns with invalid data would be skipped

• field_options Dict[str, FieldOption]

  Field options to format data.

  • Options could be named (named=True, name="property_name"), so the exporter will try to create columns based on the values of the property provided in the "name" attribute.
  • Options could be grouped (grouped=True), so the exporter will try to fit all the data for this field into a single cell.
  • Options could be both named and grouped, so the exporter will try to get data collected for each named property and fit all this data in a single field.

• array_limits Dict[str, int]

  Limit for the array fields to export only first N elements ({"images": 1}).

• headers_renaming List[Tuple[str, str]]

  Set of RegExp rules to rename existing item columns ([".*_price", "regularPrice"]). The first value is the pattern to replace, while the second one is the replacement.

• headers_order List[str]

  List to sort columns headers. All headers that are present both in this list and actual data - would be sorted. All other headers would be appended in a natural order. Headers should be provided in the form before renaming ("offers[0]->price", not "Price").

• headers_filters List[str]

  List of RegExp statements to filter columns. Headers that match any of these statements would be skipped (["name.*", "_key"]).

• grouped_separator str

  Separator to divide values when grouping data in a single cell (if grouped=True).

• cut_separator str(default="->")

  Separator to organize values from items to required columns. Used instead of default "." separator. If your properties' names include the separator - replace it with a custom one.

• capitalize_headers bool(default=False)

  Capitalize fist letter of CSV headers when exporting.

• Python 3.7+
• Works on Linux, Windows, macOS, BSD