How to list JSON non-list items together with list items with pandas.json_normalize with Python?

user_1013388 0
Viewed 114
[{
    "builtin_name": "custom_template",
    "fields": [{
            "id": 10012,
            "field_type": "OBJECT_SET",
            "tooltip_text": "",
            "name_plural": "",
            "name_singular": "reference",
            "backref_name": "reference",
            "backref_tooltip_text": "",
            "allow_multiple": False,
            "allowed_otypes": [
                "schema",
                "table",
                "attribute",
                "user",
                "groupprofile",
                "groupprofile"
            ],
            "options": None,
            "builtin_name": None
        }, {
            "id": 8,
            "field_type": "OBJECT_SET",
            "tooltip_text": None,
            "name_plural": "Stewards",
            "name_singular": "Steward",
            "backref_name": "Steward",
            "backref_tooltip_text": None,
            "allow_multiple": True,
            "allowed_otypes": [
                "user",
                "groupprofile",
                "groupprofile"
            ],
            "options": None,
            "builtin_name": "steward"
        }
    ],
    "id": 16,
    "title": "Custom template"
}]

Using this JSON object, I want to normalize it using pandas.json_normalize.

When I do this:

pd.json_normalize(data, "fields", errors='ignore', record_prefix='')

The I get the fields listed out in nice table form like this:

id field_type tooltip_text name_plural name_singular backref_name backref_tooltip_text allow_multiple allowed_otypes options builtin_name

(followed by data rows)

But I also was the Outer properties, id, title and builtin_name listed along with fields

So that I end up this:

id builtin_name title id field_type tooltip_text name_plural name_singular backref_name backref_tooltip_text allow_multiple allowed_otypes options builtin_name

I have tried this:

pd.json_normalize(data, ["id", "builtin_name", "title"], "fields", errors='ignore', record_prefix='')

But it throws an error saying that id is not a list.

Also tried without the square brackets to no avail.

How can I get these fields "id", "builtin_name", "title" to list along with the other ones in each row?

Thanks!

json-normalize pandas python
1 Answers

I'd use .json_normalize on whole data list and .explode() the fields column. Then concat back to obtain desired DataFrame:

df = pd.json_normalize(data, errors="ignore", record_prefix="")
df = pd.concat(
    [df, df.explode("fields")["fields"].apply(pd.Series)], axis=1
).drop(columns="fields")
print(df)

Prints:

      builtin_name  id            title     id  field_type tooltip_text name_plural name_singular backref_name backref_tooltip_text  allow_multiple                                     allowed_otypes options builtin_name
0  custom_template  16  Custom template  10012  OBJECT_SET                              reference    reference                                False  [schema, table, attribute, user, groupprofile,...    None         None
0  custom_template  16  Custom template      8  OBJECT_SET         None    Stewards       Steward      Steward                 None            True                 [user, groupprofile, groupprofile]    None      steward
user_100359850
Related