Flatten function retrieving the wrong value

user_3396351 0
Viewed 66

I currently have a Python script which uses a flatten function in order to flatten a JSON object, which I will then use json_normalize to turn the flattened JSON into a DataFrame.

import pandas as pd
from pandas import json_normalize
from collections.abc import MutableMapping as mm

def flatten(dictionary, p_key=None, parent_key=False, separator='.'):
    items = []
    if isinstance(dictionary, list): 
        for listval in dictionary:
            items.extend(flatten(listval).items())
            return dict(items)
    for key, value in dictionary.items():
        if parent_key:
            new_key = f"{str(p_key)}{separator}{key}"
        else:
            new_key = p_key if p_key else key
        if isinstance(value, mm):
            items.extend(flatten(
                dictionary=value,
                p_key=new_key,
                parent_key=True,
                separator=separator).items())
        elif isinstance(value, list):
            for k, v in enumerate(value):
                items.extend(flatten(
                    dictionary={str(k): v},
                    p_key=new_key,
                    parent_key=False,
                    separator=separator).items())
        else:
            items.append((new_key, value))
    return dict(items)
    

jfile =  [{ 
    "id" : 1,
    "labelId" : 169,  
    "indicators" : [
        62
    ], 
    "Wait" : 6, 
    "Levels" : [
        {
            "isActive" : "true", 
            "pressure" : 3, 
            "actions" : [
                {
                    "isActive" : "true", 
                    "description" : "Place"
                }
            ], 
            "users" : [
                5467, 
                5469, 
                5
            ]
        }, 
        {
            "isActive" : "true", 
            "pressure" : 2, 
            "actions" : [
                {
                    "isActive" : "true", 
                    "description" : "Test"
                }
            ], 
            "users" : [
                3253, 
                6903
            ]
        }
    ]
}]


flatdoc = [flatten(i) for i in jfile]

flatdoc = json_normalize(flatdoc)

print(flatdoc)

Current Output:

   id  labelId  indicators  Wait Levels.isActive  Levels.pressure Levels.actions.isActive Levels.actions.description  Levels.users
   1      169          62     6            true                2                    true                       Test          6903

The output I'm currently getting is retrieving the last value from the last key that is found in the JSON object for some reason. What I need it to do is retrieve it from the first key within the dictionary and then when that list is not nested and just contains numbers/integers it should simply explode it so the result can look like the below:

Expected Output:

   id  labelId  indicators  Wait Levels.isActive  Levels.pressure Levels.actions.isActive Levels.actions.description  Levels.users
   1      169          62     6            true                3                    true                       Place          5467
   1      169          62     6            true                3                    true                       Place          5469
   1      169          62     6            true                3                    true                       Place             5

How would I go about amending the function so that it locates the correct key to produce the appropriate value?

pandas flatten python json
2 Answers

Your flattening function is wrong: with the given jfile it only returns one row, when 5 would be expected.

Here is a correct version:

def flatten(data):
    if isinstance(data, (list, tuple)):
        newdata = []
        for elt in data:
            elt = flatten(elt)
            if isinstance(elt, list):
                newdata.extend(elt)
            else:
                newdata.append(elt)
        return newdata
    elif isinstance(data, mm):
        for k,v in data.items():
            v = flatten(v)
            if isinstance(v, list):
                newdata = [data.copy() for _ in v]
                for i, elt in enumerate(v):
                    newdata[i][k] = elt
                return flatten(newdata)
            data[k] = v
    return data

You can then directly do:

flatdoc = pd.json_normalize(flatten(jfile))

to obtain:

   id  labelId  indicators  Wait Levels.isActive  Levels.pressure Levels.actions.isActive Levels.actions.description  Levels.users
0   1      169          62     6            true                3                    true                      Place          5467
1   1      169          62     6            true                3                    true                      Place          5469
2   1      169          62     6            true                3                    true                      Place             5
3   1      169          62     6            true                2                    true                       Test          3253
4   1      169          62     6            true                2                    true                       Test          6903
user_35452730

You can use recursion with a generator:

from itertools import product
import pandas as pd
jfile = [{'id': 1, 'labelId': 169, 'indicators': [62], 'Wait': 6, 'Levels': [{'isActive': 'true', 'pressure': 3, 'actions': [{'isActive': 'true', 'description': 'Place'}], 'users': [5467, 5469, 5]}, {'isActive': 'true', 'pressure': 2, 'actions': [{'isActive': 'true', 'description': 'Test'}], 'users': [3253, 6903]}]}]
def get_vals(d, c = []):
    if not isinstance(d, (dict, list)):
       yield ('.'.join(c), d)
    elif isinstance(d, list):
       yield from [i for b in d for i in get_vals(b, c=c)]
    else:
       for i in product(*[get_vals(b if isinstance(b, list) else [b], c+[a]) for a, b in d.items()]):
           yield [j for k in i for j in ([k] if all(not isinstance(l, (tuple, list)) for l in k) else k)]
          
df = pd.DataFrame([dict(i) for i in get_vals(jfile)])

Output:

    id  labelId  indicators  Wait Levels.isActive  Levels.pressure Levels.actions.isActive Levels.actions.description  Levels.users
0   1      169          62     6            true                3                    true                      Place          5467
1   1      169          62     6            true                3                    true                      Place          5469
2   1      169          62     6            true                3                    true                      Place             5
3   1      169          62     6            true                2                    true                       Test          3253
4   1      169          62     6            true                2                    true                       Test          6903
user_73267380
Related