How to aggregate nested json array in Pandas?-CodePudding

I have simple json what i read with pandas:

json_list = [
    { 
        'class': 'Year 1', 
        'student count': 20, 
        'room': 'Yellow',
        'info': {
            'teachers': { 
                'math': 'Rick Scott', 
                'physics': 'Elon Mask' 
            }
        },
        'students': [
            { 
                'name': 'Tom', 
                'sex': 'M', 
                'grades': { 'math': 66, 'physics': 77 } 
            },
            { 
                'name': 'James', 
                'sex': 'M', 
                'grades': { 'math': 80, 'physics': 78 } 
            },
        ]
    },
    { 
        'class': 'Year 2', 
        'student count': 25, 
        'room': 'Blue',
        'info': {
            'teachers': { 
                'math': 'Alan Turing', 
                'physics': 'Albert Einstein' 
            }
        },
        'students': [
            { 'name': 'Tony', 'sex': 'M' },
            { 'name': 'Jacqueline', 'sex': 'F' },
        ]
    },
]

pd.json_normalize(json_list)

There a list of classes and every class has own students. How can i aggregate students into one string? I mean get some thing like this:

class  <some other class fields>    students
Year 1        ...                   Tom sex - M; Jamessex - M
Year 2        ...                   Tony sex - M; Jacqueline sex - F

CodePudding user response：

If I understand correctly, you want to apply a function to the students column that 'flattens' the list of dictionaries into a string, right?

df['students'] = df['students'].apply(lambda ds: '; '.join(f"{d['name']} sex - {d['sex']}" for d in ds))

Output:

    class  student count    room                          students  info.teachers.math info.teachers.physics
0  Year 1             20  Yellow        Tom sex - M; James sex - M          Rick Scott             Elon Mask 
1  Year 2             25    Blue  Tony sex - M; Jacqueline sex - F         Alan Turing       Albert Einstein