Convert dataframe into JSON file

Question

Dataframe:

Name    Location code   ID  Dept    Details Fbk
Kirsh   HD12    76  Admin   "Age:25; Location : ""SF""; From: ""London""; Marital stays: ""Single"";"   Good
John    HD12    87  Support "Age:35; Location : ""SF""; From: ""Chicago""; Marital stays: ""Single"";"  Good

Desired output:

{
“Kirsh”: {
    “Location code”:”HD12”,
    “ID”: “76”,
    “Dept”: “IT”,
     “Details”: {
         “Age”:”25”;,
         “Location”:”SF”;,
         “From”: "London";,
         “Marital stays”: "Single";,
         }
      “Fbk”: “good”
   },
“John”: {
    “Location code”:”HD12”,
    “ID”: “87”,
    “Dept”: “Support”,
     “Details”: {
         “Age”:”35”;,
         “Location”:”SF”;,
         “From”: "chicago";,
         “Marital stays”: "Single";,
         }
      “Fbk”: “good”
    }
}

Welcome to Stack Overflow! You are encouraged to make an attempt to write your code. If you encounter a specific technical problem during that attempt, such as an error or unexpected result, we can help with that. Please provide specific information about that attempt and what didn't work as expected. To learn more about this community and how we can help you, please start with the [tour](https://stackoverflow.com/tour) and read [How to Ask](https://stackoverflow.com/questions/how-to-ask) and its linked resources. — Dash, Nov 25 '22 at 05:16
Does this answer your question? [Convert Pandas DataFrame to JSON format](https://stackoverflow.com/questions/39257147/convert-pandas-dataframe-to-json-format) — Gonçalo Peres, Nov 25 '22 at 09:17

score 1 · Answer 1 · edited Nov 28 '22 at 12:26

import pandas as pd
import json

df = pd.DataFrame({'name':['a','b','c','d'],'age':[10,20,30,40],'address':['e','f','g','h']})

df_without_name = data1.loc[:, df.columns!='name']

dict_wihtout_name = df_without_name.to_dict(orient='records')

dict_index_by_name = dict(zip(df['name'], df_without_name))

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

Output:

{
  "a": {
    "age": 10,
    "address": "e"
  },
  "b": {
    "age": 20,
    "address": "f"
  },
  "c": {
    "age": 30,
    "address": "g"
  },
  "d": {
    "age": 40,
    "address": "h"
  }
}

Answering the comment posted by @Eswar:

If a field has multiple values then you can store it as a tuple in the dataframe. Check this answer - https://stackoverflow.com/a/74584666/1788146 on how to store tuple values in pandas dataframe.

What if address/age has muntiple values like here I’m thinking to get that as { "a": { "age": 10, "address": { "e", “x”, “w”. } }, "b": { "age": 20, "address": { "f", “z”, “y”. } }, "c": { "age": 30, "address": { "r", “s”, “t”. } }, "d": { "age": 40, "address": "h" } } — Eswar, Nov 27 '22 at 05:49

Convert dataframe into JSON file

1 Answers1

Linked