Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Need help converting dataframe to json format

I have a table which has some sample data as provided below

import pandas as pd

data = {
  "mode": ["single_table_list", "single_table_list", "single_table_list", "relational_table_list", "relational_table_list"],
  "type": ["type_a", "type_b", "type_c", "parent_table", "child_table"],
  "file_name": ["file_a", "file_b", "file_c", "file_d", "file_e"],
  "file_path": ["path_a", "path_b", "path_c", "path_d", "path_e"],
  "file_sample": ["sample_a", "sample_b", "sample_c", "sample_d", "sample_e"],
  "target_file_name": ["tgt_name_a", "tgt_name_b", "tgt_name_c", "tgt_name_d", "tgt_name_e"],
  "target_file_path": ["tgt_path_a", "tgt_path_b", "tgt_path_c", "tgt_path_d", "tgt_path_e"],
  "child_table": ["","","","child_d",""],
  "parent_key": ["","","","key_d",""],
  "parent_table": ["","","","","parent_e"],
  "child_key": ["","","","","key_e"]
}

I am facing difficulty with the output into the required formatting, how do I go about implementing this so that it provides the output as shown below.
Is it possible to have the JSON output structured as such?

{
  "single_table_list": {
    "1":{
    "type": "type_a",
    "file_name": "file_a",
    "file_path": "path_a",
    "file_sample": "sample_a",
    "target_file_name": "tgt_name_a",
    "target_file_path": "tgt_path_a"
    },
    "2":{
    "type": "type_b",
    "file_name": "file_b",
    "file_path": "path_b",
    "file_sample": "sample_b",
    "target_file_name": "tgt_name_b",
    "target_file_path": "tgt_path_b"
    },
    "3":{
    "type": "type_c",
    "file_name": "file_c",
    "file_path": "path_c",
    "file_sample": "sample_c",
    "target_file_name": "tgt_name_c",
    "target_file_path": "tgt_path_c"
    }
  },
  "relational_table_list": {
    "1":{
      "parent_table_list":{
        "type": "type_parent",
        "file_name": "file_d",
        "file_path": "path_d",
        "file_sample": "sample_d",
        "target_file_name": "tgt_name_d",
        "target_file_path": "tgt_path_d",
        "child_table": "child_d"
        "parent_key": "key_d"
        },
      "child_table_list":{
        "type": "type_child",
        "file_name": "file_e",
        "file_path": "path_e",
        "file_sample": "sample_e",
        "target_file_name": "tgt_name_e",
        "target_file_path": "tgt_path_e",
        "parent_table": "parent_e",
        "child_key": "key_e"
        }
    }  
  }
}
Tags:

2

Answers


  1. 0

    you can use "json" module
    json.dumps is uesd to convert python Object to JSON String.And you should give argument"indent". there is a example

    import json

data = {
    "name": "John Doe",
    "age": 30,
    "address": {
        "city": "Beijing",
        "country": "China",
    },
}

json_string = json.dumps(data, indent=2)

print(json_string)
    Login or Signup to reply.
  2. 0

    I don’t sure what you want
    but I think you want to organizes the data according to the desired structure, and then converts it to a JSON string with proper indentation. The output should match your specified JSON structure.

    import pandas as pd
import json

data = {
    "mode": ["single_table_list", "single_table_list", "single_table_list", "relational_table_list", "relational_table_list"],
    "type": ["type_a", "type_b", "type_c", "parent_table", "child_table"],
    "file_name": ["file_a", "file_b", "file_c", "file_d", "file_e"],
    "file_path": ["path_a", "path_b", "path_c", "path_d", "path_e"],
    "file_sample": ["sample_a", "sample_b", "sample_c", "sample_d", "sample_e"],
    "target_file_name": ["tgt_name_a", "tgt_name_b", "tgt_name_c", "tgt_name_d", "tgt_name_e"],
    "target_file_path": ["tgt_path_a", "tgt_path_b", "tgt_path_c", "tgt_path_d", "tgt_path_e"],
    "child_table": ["", "", "", "child_d", ""],
    "parent_key": ["", "", "", "key_d", ""],
    "parent_table": ["", "", "", "", "parent_e"],
    "child_key": ["", "", "", "", "key_e"]
}

df = pd.DataFrame(data)

# Initialize an empty dictionary to store the result
result_dict = {}

# Iterate through the DataFrame rows
for index, row in df.iterrows():
    mode = row["mode"]
    row_dict = {
        "type": row["type"],
        "file_name": row["file_name"],
        "file_path": row["file_path"],
        "file_sample": row["file_sample"],
        "target_file_name": row["target_file_name"],
        "target_file_path": row["target_file_path"]
    }

    if mode not in result_dict:
        result_dict[mode] = {}

    if mode == "relational_table_list":
        row_dict["parent_table_list"] = {
            "type": "type_parent",
            "file_name": row["file_name"],
            "file_path": row["file_path"],
            "file_sample": row["file_sample"],
            "target_file_name": row["target_file_name"],
            "target_file_path": row["target_file_path"],
            "child_table": row["child_table"],
            "parent_key": row["parent_key"]
        }

        row_dict["child_table_list"] = {
            "type": "type_child",
            "file_name": row["file_name"],
            "file_path": row["file_path"],
            "file_sample": row["file_sample"],
            "target_file_name": row["target_file_name"],
            "target_file_path": row["target_file_path"],
            "parent_table": row["parent_table"],
            "child_key": row["child_key"]
        }

    result_dict[mode][str(index + 1)] = row_dict

# Convert the result dictionary to JSON
result_json = json.dumps(result_dict, indent=2)
print(result_json)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search