CODEWITHSUNDEEP
pythonjsonexcelkey-valuexls
Anoosha
Anoosha

Reputation: 1

convert a excel file to nested json file using python

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,

Upvotes: 0

Views: 1747

Answers (1)

Lidor Eliyahu Shelef
Lidor Eliyahu Shelef

Reputation: 1332

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

Upvotes: 2

Related Questions