Python introduces nested JSON to transform into Dataframe in seconds!

Python tutorialThe column introduces how to nest JSON

Recommended (free):Python Tutorial

Calling API and the document database will return a nested JSON object when we use When Python tries to convert the keys in the nested structure into columns, the data loaded into pandas will often get the following results:

df = pd.DataFrame.from_records（results [“ issues”]，columns = [“ key”，“ fields”]）

Explanation: The results here are a large dictionary , issues is a key in results, and the value of issues is a list of nested JSON object dictionaries. You will see the JSON nested structure later.

The problem is that the API returns a nested JSON structure, and the keys we care about are indeed at different levels in the object.

The nested JSON structure looks like this.

What we want is the following.

The following takes the data returned by an API as an example. The API usually contains metadata about the fields. Let's say these are the fields we want.

• key: JSON key, at the first level.
• summary: The second-level "field" object.
• status name: Third level position.
• statusCategory name: Located at the 4th nesting level.

As above, the fields we choose to extract are at 4 different nesting levels in the JSON structure in the issues list, one after another.

{
  "expand": "schema,names",
  "issues": [
    {
      "fields": {
        "issuetype": {
          "avatarId": 10300,
          "description": "",
          "id": "10005",
          "name": "New Feature",
          "subtask": False
        },
        "status": {
          "description": "A resolution has been taken, and it is awaiting verification by reporter. From here issues are either reopened, or are closed.",
          "id": "5",
          "name": "Resolved",
          "statusCategory": {
            "colorName": "green",
            "id": 3,
            "key": "done",
            "name": "Done",
          }
        },
        "summary": "Recovered data collection Defraglar $MFT problem"
      },
      "id": "11861",
      "key": "CAE-160",
    },
    {
      "fields": { 
... more issues],
  "maxResults": 5,
  "startAt": 0,
  "total": 160
}

A not so good solution

One option is to code directly and write a function to find a specific field, but the problem is that each embedded field must be Call this function to set the field, and then call .apply to the new column in DataFrame.

In order to get the several fields we want, first we extract the objects in the fields key to the column:

df = (
    df["fields"]
    .apply(pd.Series)
    .merge(df, left_index=True, right_index = True)
)

As can be seen from the above table, only summary is available, issuetype, status, etc. Still buried in nested objects.

The following is a method to extract the name in issuetype.

# 提取issue type的name到一个新列叫"issue_type"
df_issue_type = (
    df["issuetype"]
    .apply(pd.Series)
    .rename(columns={"name": "issue_type_name"})["issue_type_name"]
)
df = df.assign(issue_type_name = df_issue_type)

Like the above, if there are too many nesting levels, you need to implement recursion yourself, because each level of nesting needs to call a method like the above to parse and add to a new column.

For friends with weak programming foundation, it is actually quite troublesome to learn one. Especially for data analysts, when they are anxious to use data, they hope to quickly get structured data for analysis.

Now Brother Dong shares a pandas built-in solution.

Built-in solution

pandas has an awesome built-in function called .json_normalize. The documentation of

pandas mentions: normalizing semi-structured JSON data into a flat table.

All the code in the previous solution can be completed using this built-in function in only 3 lines. The steps are very simple, just understand the following usage.

Determine the fields we want and use the . symbol to connect nested objects.

Put the nested list you want to process (here results["issues"]) as a parameter into .json_normalize.

Filter the FIELDS list we defined.

FIELDS = ["key", "fields.summary", "fields.issuetype.name", "fields.status.name", "fields.status.statusCategory.name"]
df = pd.json_normalize(results["issues"])
df[FIELDS]

Yes, it’s that simple.

Other operations

Record path

In addition to passing results["issues"]## like above #In addition to the list, we also use the record_path parameter to specify the path of the list in the JSON object.

# 使用路径而不是直接用results["issues"]
pd.json_normalize(results, record_path="issues")[FIELDS]

Custom delimiter

### 用 "-" 替换默认的 "."
FIELDS = ["key", "fields-summary", "fields-issuetype-name", "fields-status-name", "fields-status-statusCategory-name"]
pd.json_normalize(results["issues"], sep = "-")[FIELDS]

Control recursion

max_level parameter to control the depth. In this case, since the statusCategory.name field is at level 4 of the JSON object, it will not be included in the resulting DataFrame.

# 只深入到嵌套第二级
pd.json_normalize(results, record_path="issues", max_level = 2)

pandas official document description of .json_normalize. If you don’t understand, you can learn it by yourself. This time Brother Dong will introduce it here.

The above is the detailed content of Python introduces nested JSON to transform into Dataframe in seconds!. For more information, please follow other related articles on the PHP Chinese website!