CODEWITHSUNDEEP
pythonjsonpandas
Bharath_Raja
Bharath_Raja

Reputation: 642

Convert a multi index dataframe to json

Here is the multi index data frame 

accounting                                         sales
PhNumber    age     firstName      lastName         PhNumber       age  firstName   lastName
0   <PH_Number> 29  <first_Name>    <last_Name>     <PH_Number> 29  <first_Name>    <last_Name>
1   <PH_Number> 38  <first_Name>    <last_Name>     <PH_Number> 48  <first_Name>    <last_Name>

How do I convert this to a proper json? I have used pandas.to_json(). But couldn't get the desired output like this

{ "accounting": [{"firstName": <first_name>,
   "lastName": <last_name>,
   "age": 29,
   "PhNumber": <PH_Number>},
  {"firstName": <first_name>,
   "lastName": "<last_name>",
   "age": 38,
   "PhNumber": <PH_Number>}],
 "sales": [{"firstName": "<first_name>",
   "lastName": "<last_name>",
   "age": 29,
   "PhNumber": <PH_Number>},
  {"firstName": "<first_name>",
   "lastName": "<last_name>",
   "age": 48,
   "PhNumber": <PH_Number>}]}

Upvotes: 0

Views: 154

Answers (1)

Serge Ballesta
Serge Ballesta

Reputation: 148975

What you ask is beyond the possibilies of to_json, so you should first compute the Python data structure and then convert it to JSON:

data_struct = {k: df[k].to_dict(orient='records') for k in df.columns.levels[0]}

You can then easily build a JSON file (or string):

print(json.dumps(data_struct, indent=2)

gives:

{
  "accounting": [
    {
      "PhNumber": "<PH_Number>",
      "age": 29,
      "firstName": "<first_Name>",
      "lastName": "<last_Name>"
    },
    {
      "PhNumber": "<PH_Number>",
      "age": 38,
      "firstName": "<first_Name>",
      "lastName": "<last_Name>"
    }
  ],
  "sales": [
    {
      "PhNumber": "<PH_Number>",
      "age": 29,
      "firstName": "<first_Name>",
      "lastName": "<last_Name>"
    },
    {
      "PhNumber": "<PH_Number>",
      "age": 48,
      "firstName": "<first_Name>",
      "lastName": "<last_Name>"
    }
  ]
}

Upvotes: 1

Related Questions