convert a excel file to nested json file using python

Question

My data with multiple pages looks like this I want to convert it to a JSON file like below.

{
"Name" : "A",
  {
  "Project" : "P1",
  {
  [
  "T1" : "P1.com",
  ]
  },
  "Project" : "P2",
  {
  [
   "T2" : "P2.com",
   "T3" : "P2.com",   
   ]
  }
  },
 "Name" : "B",
  {
  "Project" : "Q1",
  {
  [
  "T1" : "Q1.com",
  ]
  },
  "Project" : "Q2",
  {
  [
   "T2" : "Q2.com" 
   ]
  }
  },
 "Name" : "C",
  {
  "Project" : "R1",
  {
  [
  "T1" : "R1.com",
  ]
  },
  "Project" : "R2",
  {
  [
   "T2" : "R2.com" 
   ]
  }
  }
  }

This is the first time i'm working on json files and bit confused how to make the excel's heading as key and remaining datas as values. I am able to read the excel file using pandas. Can anyone help me with the idea to do this?

import json
import pandas as pd

df = pd.read_excel (r'D:\example.xlsx', sheet_name='Sheet1')

Names = df["Name"]
d = {}

for index, name in enumerate(Names):

I have tried reading the file.Since im working with JSON files for the first time i don't have much idea about how to convert this,

Please edit your JSON file to be easier to understand. @Anoosha — Lidor Eliyahu Shelef
– Lidor Eliyahu Shelef, Commented Nov 30, 2022 at 8:40

Lidor Eliyahu Shelef · Accepted Answer · 2022-11-26 16:56:26Z

2

You have many options, I'll provide you with them using the pandas library, choose which one is more suitable to you

Example Code:

import pandas as pd

data = [
    ["A", "P1", "T1", "P1.com"],
    ["A", "P2", "T2", "P2.com"],
    ["A", "P2", "T3", "P2.com"],
    ["B", "Q1", "T1", "Q1.com"],
    ["B", "Q2", "T2", "Q2.com"],
    ["C", "R1", "T1", "R1.com"],
    ["C", "R2", "T2", "R2.com"],
]
cols = ['name', 'project', 'type', 'link']
# Create the pandas DataFrame
df = pd.DataFrame(data, columns=cols)

In order to convert it to JSON apply one of the following:

parsed_split = json.loads(df.to_json(orient="split"))
parsed_records = json.loads(df.to_json(orient="records"))
parsed_index = json.loads(df.to_json(orient="index"))
parsed_columns = json.loads(df.to_json(orient="columns"))
parsed_values = json.loads(df.to_json(orient="values"))
parsed_table = json.loads(df.to_json(orient="table"))

If none of the following meets your requirement, for sure you'll be able to loop over one of them in order to achieve your solution, if you're struggling with that, comment and I'll update it for you.

Keep us updated, @Anoosha

Update 1: Multi Sheet Excel

import pandas as pd

sheets = ["A", "B"]
xls = pd.ExcelFile('path_to_file.xls')
for sheet in sheets:
    df = pd.read_excel(xls, sheet) # Or use without loop if you want

edited Nov 26, 2022 at 16:56

answered Nov 23, 2022 at 13:47

Lidor Eliyahu Shelef

1,3621 gold badge15 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Anoosha Over a year ago

hi @Lidor Eliyahu Shelef, i have managed to convert to a json using pandas. Like you said i used for loop. I tried for a simple excel sheet like the one i have attached in the question. I have a mutli paged excel. and the "Name" field is the sheet name. and there are 5 "Type" from which i should only consider data of 3(T1,T2,T3). I am not able to attach the picture in this comment. So i'll edit the main question and attach the picture there. can you please guide me on how to read the next pages and how to only consider the required column.

Lidor Eliyahu Shelef Over a year ago

I added ot my answer a multi page sheet read code line, but in order for that I'll need to to edit the json you want, because the json example you brought is not a valid json file mate, can you try to focus better on what is the result you are hoping to get? @Anoosha

Lidor Eliyahu Shelef Over a year ago

@Anoosha if this answer was enough, make sure to accept it or comment further problems

Collectives™ on Stack Overflow

convert a excel file to nested json file using python

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related