explode_json_to_rows

explode_json_to_rows

Explodes into multiple rows and expands into columns based on a column that has an array of JSON objects in it.

Definition

explode_json_to_rows(df, column_name, drop=True, **kwargs):
"""
           :param df : the dataframe
           :param column_name: the column that has the JSON in it.
           :param drop: drop the source column in the result. Default is True
           df - a new data frame with the JSON line expanded into columns and rows
"""

Example

Input

Objective

The Line column is actually a serialized JSON object provided by Quickbooks with several useful elements in it. We'll need to start by flattening the JSON and then exploding into unique columns so we can work with the data.

Again, we'll use the gluestick package to accomplish this. The explode_json_to_rows function handles the flattening and exploding in one step. To avoid exploding too many levels of this object, we'll specify max_level=1

Here is a snippet from one to give you an idea.

[{
    "Id": "1",
    "LineNum": "1",
    "Amount": 275.0,
    "DetailType": "SalesItemLineDetail",
    "SalesItemLineDetail": {
        "ItemRef": {
            "value": "5",
            "name": "Rock Fountain"
        },
        "ItemAccountRef": {
            "value": "79",
            "name": "Sales of Product Income"
        },
        "TaxCodeRef": {
            "value": "TAX",
            "name": None
        }
    },
    "SubTotalLineDetail": None,
    "DiscountLineDetail": None
}]

# Let's explode the Line column now
invoices = invoices.pipe(gs.explode_json_to_rows, "Line", max_level=1)
invoices.head()

Output