Flattering

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:

_Name	_Price	_Currency	_Sku	_Images	_Description	_{AdditionalProperty}	_RatingValue	_ReviewCount
_Product	_154.95	_$	₉₂₀₄	_{https://m.site.com/i/9204_1.jpg https://m.site.com/i/9204_2.jpg https://m.site.com/i/9204_3.jpg}	_{Custom description on multiple lines.}	_{size: XL color:blue}	₅	₃

Quickstart

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.

CLI

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.

What you can do

1. Flatten data

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:

_name	_{offers0->price}	_{offers0->currency}	_sku	_images0	_images1	_images2	_description	_{additionalProperty0->name}	_{additionalProperty0->value}	_{additionalProperty1->name}	_{additionalProperty1->value}	_{aggregateRating->ratingValue}	_{aggregateRating->reviewCount}
_Product	_154.95	_$	₉₂₀₄	_{https://m.site.com/i/9204_1.jpg}	_{https://m.site.com/i/9204_2.jpg}	_{https://m.site.com/i/9204_3.jpg}	_{Custom description on multiple lines.}	_size	_XL	_color	_blue	_5.0	₃

2. Rename columns

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)

_Name	_Price	_Currency	_Sku	_Images[0]	_Images[1]	_Images[2]	_Description	_{AdditionalProperty[0]->name}	_{AdditionalProperty[0]->value}	_{AdditionalProperty[1]->name}	_{AdditionalProperty[1]->value}	_RatingValue	_ReviewCount
_Product	_154.95	_$	₉₂₀₄	_{https://m.site.com/i/9204_1.jpg}	_{https://m.site.com/i/9204_2.jpg}	_{https://m.site.com/i/9204_3.jpg}	_{Custom description on multiple lines.}	_size	_XL	_color	_blue	_5.0	₃

3. Format data

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)

_Name	_Price	_Currency	_Sku	_Images	_Description	_{AdditionalProperty[0]->name}	_{AdditionalProperty[0]->value}	_{AdditionalProperty[1]->name}	_{AdditionalProperty[1]->value}	_RatingValue	_ReviewCount
_Product	_154.95	_$	₉₂₀₄	_{https://m.site.com/i/9204_1.jpg https://m.site.com/i/9204_2.jpg https://m.site.com/i/9204_3.jpg}	_{Custom description on multiple lines.}	_size	_XL	_color	_blue	_5.0	₃

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
    }
}

_Name	_Price	_Currency	_Sku	_Images	_Description	_Size	_Color	_RatingValue	_ReviewCount
_Product	_154.95	_$	₉₂₀₄	_{https://m.site.com/i/9204_1.jpg https://m.site.com/i/9204_2.jpg https://m.site.com/i/9204_3.jpg}	_{Custom description on multiple lines.}	_XL	_blue	_5.0	₃

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
}

_Name	_Price	_Currency	_Sku	_Images	_Description	_{AdditionalProperty}	_RatingValue	_ReviewCount
_Product	_154.95	_$	₉₂₀₄	_{https://m.site.com/i/9204_1.jpg https://m.site.com/i/9204_2.jpg https://m.site.com/i/9204_3.jpg}	_{Custom description on multiple lines.}	_{size: XL color: blue}	_5.0	₃

4. Filter columns

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.

_Name	_Price	_Currency	_Sku	_Images	_Description	_{AdditionalProperty}
_Product	_154.95	_$	₉₂₀₄	_{https://m.site.com/i/9204_1.jpg https://m.site.com/i/9204_2.jpg https://m.site.com/i/9204_3.jpg}	_{Custom description on multiple lines.}	_{size: XL color: blue}

5. Order columns

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.

_Name	_Sku	_Price	_Currency	_Images	_Description	_{AdditionalProperty}
_Product	₉₂₀₄	_154.95	_$	_{https://m.site.com/i/9204_1.jpg https://m.site.com/i/9204_2.jpg https://m.site.com/i/9204_3.jpg}	_{Custom description on multiple lines.}	_{size: XL color: blue}

6. Process invalid data

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:

_a	_b
_{a_1}	_{{'c': 'c_1'}}
_{a_2}	_{some_value}

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:

_a	_b->c
_{a_1}	_{c_1}
_{a_1}	_{c_2}

7. Process complex data

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:

_{a->nested_a[0][0]->2x_nested_a->3x_nested_a}	_{a->second_nested_a}
_{parameter1: value1 parameter2: value2}	_{some_value}

8. Export data

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']]

Arguments

StatsCollector

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.

Exporter

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.

Requirements

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

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

README.md

README.md

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

Files

README.md

Latest commit

History

README.md

File metadata and controls

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