Mulitlevel Pandas dataframe to nested json-CodePudding

I am trying to turn a data frame into some nested json and have been struggling a bit. Here is an example I created. The use case is to split a document for each guest at a hotel chain, with the guest at the top, the hotel details under the visit data, and the daily charges under the 'measurements' info.

The dataframe:

Here is an example of how I am trying to get the JSON to look

I have tried creating a multilevel index and using to_json:

Is there a way to do this using to_json() or will I need to build some nested loops to create nested dictionaries? This is the best I have been able to get:

CodePudding user response：

I would recommend a programming approach. pandas.DataFrame.groupby can be useful.

def hotel_data_to_json(df):
    return [
        person_data_to_json(person_df)
        for person_id, person_df
        in df.groupby('person_id')
    ]

def person_data_to_json(df):
    row = df.iloc[0]
    return {
        'person_id': row['person_id'],
        'personal_name': row['personal_name'],
        'family_name': row['family_name'],
        'visits': [
            visit_data_to_json(visit_df)
            for visit_id, visit_df
            in df.groupby('visit_id')
        ]
    }

def visit_data_to_json(df):
    row = df.iloc[0]
    # and so on