ENH: When running json_normalize over a pandas dataframe, be able to insert/keep an index/Id series/column

[frbelotto]

Feature Type

• Adding new functionality to pandas

• Changing existing functionality in pandas

• Removing existing functionality in pandas

Problem Description

Currently I am running a pandas Json_normalize in a dataframe where one of its columns is a nested json. The output dataframe is a unnested json with all fields and values.

After facing my needed, I´ve questioned about it on stackoverflow, but I´ve realized that the solution os not really "user friendly".

Feature Description

So, I would like to suggest that when running a json_normalize over a dataframe, I could set anoter column/series to be kept with the resulting dataframe.

Alternative Solutions

Concatenating commands is a solution, but not really "easy" (and I don´t know about performance impact using this)

import pandas as pd
import json

data = {
            "id de transação": [1, 2, 3, 4, 5],
            "nome": ["Alice", "Bob", "Charlie", "David", "Eve"],
            "dados": [
                {"data": "2024-01-01", "local": "São Paulo", "valor": 100.50},
                {"data": "2024-01-02", "local": "Rio de Janeiro", "valor": 200.75},
                {"data": "2024-01-03", "local": "Belo Horizonte", "valor": 300.00},
                {"data": "2024-01-04", "local": "Curitiba", "valor": 400.25},
                {"data": "2024-01-05", "local": "Porto Alegre", "valor": 500.50}
            ]
        }
df = pd.DataFrame(data)

out = (df[['id de transação', 'nome']]
       .join(pd.json_normalize(data=df['dados'], record_path=None)
               .set_axis(df.index)
            )
      )

Additional Context

No response

[rhshadrach]

Thanks for the request. Since you know the order of rows in the two pieces you wish to combine is the same, you should use concat instead of join. This is performant.

data = {
    "id de transação": [1, 2, 3, 4, 5],
    "nome": ["Alice", "Bob", "Charlie", "David", "Eve"],
    "dados": [
        {"data": "2024-01-01", "local": "São Paulo", "valor": 100.50},
        {"data": "2024-01-02", "local": "Rio de Janeiro", "valor": 200.75},
        {"data": "2024-01-03", "local": "Belo Horizonte", "valor": 300.00},
        {"data": "2024-01-04", "local": "Curitiba", "valor": 400.25},
        {"data": "2024-01-05", "local": "Porto Alegre", "valor": 500.50}
    ]
}
df = pd.DataFrame(data)
print(
    pd.concat(
        [
            df[['id de transação', 'nome']],
            pd.json_normalize(data=df['dados'], record_path=None),
        ],
        axis=1,
    )
)

[frbelotto]

Thanks for the suggestion. Usually I use concatenation, but nowadays I got to use dask (instead of pandas) because of the size of the dataset.

As dask does not contain the json_normalize function, I use pandas on it (using dask delayed)

the issue is that dask work with many data frame partitions and its index are set on each partition, so the concat becomes a messy. I do not really fell safe that each partition/output are kept on the original order (because of the parallel running)

If I could just keep and id/column of the original dataframe, I could do a safer merge os just export the hole dataframe to a table (normalizing a table)

[rhshadrach]

I do not really fell safe that each partition/output are kept on the original order (because of the parallel running)

If I could just keep and id/column of the original dataframe

These two seem to be in conflict to me. If the original DataFrame has columns with row order that match, then my solution above will also be the same. On the other hand, if the original DataFrame has columns with row order that don't match, then keeping the id/column of the original DataFrame will not help you.

[frbelotto]

The original dataframe index is not “safe” but each row as an transaction id column.

If I keep this column on the output, I could merge it later or do any kind of data filtering

[rhshadrach]

Sorry @frbelotto - I am not following. It would be helpful if you proposed an example call to json_normalize, the desired output, and how this differs from my proposed solution.