pandas to_json exclude the groupby keys-CodePudding

How do we exclude the grouped by key from the to_json method ?

import pandas as pd

students_df = pd.DataFrame(
    [
        ["Jay", 16, "Soccer"],
        ["Jack", 19, "FootBall"],
        ["Dorsey", 19, "Dining"],
        ["Mark", 18, "Swimming"],
    ],
    columns=["Name", "Age", "Sport"],
)
students_df.groupby("Name").apply(lambda x: x.to_json(orient="records")).reset_index(
    name="students_json"
)

Current output:

    Name                                  students_json
0  Dorsey  [{"Name":"Dorsey","Age":19,"Sport":"Dining"}]
1    Jack  [{"Name":"Jack","Age":19,"Sport":"FootBall"}]
2     Jay     [{"Name":"Jay","Age":16,"Sport":"Soccer"}]
3    Mark  [{"Name":"Mark","Age":18,"Sport":"Swimming"}]

I want to exclude the grouped by key from the resulting json.

There could be multiple keys on which I can group on not just name. Expected output should be:

     Name                                  students_json
0  Dorsey                                   [{"Age":19,"Sport":"Dining"}]
1    Jack                                   [{"Age":19,"Sport":"FootBall"}]
2     Jay                                   [{"Age":16,"Sport":"Soccer"}]
3    Mark                                   [{"Age":18,"Sport":"Swimming"}]

CodePudding user response：

You could drop it:

out = students_df.groupby('Name').apply(lambda x: x.drop(columns='Name').to_json(orient="records"))

Output:

Name
Dorsey      [{"Age":19,"Sport":"Dining"}]
Jack      [{"Age":19,"Sport":"FootBall"}]
Jay         [{"Age":16,"Sport":"Soccer"}]
Mark      [{"Age":18,"Sport":"Swimming"}]
dtype: object

CodePudding user response：

Specify which columns you want in the json.

students_df.groupby("Name").apply(
lambda x: x[["Age", "Sport"]].to_json(orient="records")).reset_index(name="students_json")

     Name                    students_json
0  Dorsey    [{"Age":19,"Sport":"Dining"}]
1    Jack  [{"Age":19,"Sport":"FootBall"}]
2     Jay    [{"Age":16,"Sport":"Soccer"}]
3    Mark  [{"Age":18,"Sport":"Swimming"}]