Excel to JSON format with python

Question

I have an excel sheet which is in the below format

I want to convert this excel sheet into JSON format using Python. each JSON object is a diagonal value and column headings in the below format.

{
"Records": [
    {
        "RecordId": "F1",
        "Assets": [
            {
                "AssetId": "A1",
                "Support": "S11"
            },
            {
                "AssetId": "A2",
                "Support": "S12"
            },
            {
                "AssetId": "A3",
                "Support": "S13"
            }
        ]
    },
    {
        "RecordId": "F2",
        "Assets": [
            {
                "AssetId": "A1",
                "Support": "S21"
            },
            {
                "AssetId": "A2",
                "Support": "S22"
            },
            {
                "AssetId": "A3",
                "Support": "S23"
            }
        ]
    }
]

}

I have written some code it seems not working as I expected.

import json
import pandas as pd
  
df = pd.read_excel (r'test.xlsx', sheet_name='Sheet2')
#initialize data
data=[0 for i in range(len(df))]
datac=[0 for c in range(len(df.columns))]
newset=dict()


for i in range(len(df)):
    # data[i] = r'{"'+str(df.columns.values[0])+'": "' +str(df.loc[i][0])+'", '+str(df.columns.values[1])+'": "' +str(df.loc[i][1])+'", '+str(df.columns.values[2])+'": "' +str(df.loc[i][2])+'"}' 
    #data[i] = {str(df.columns.values[1]) : str(df.loc[i][0]), str(df.columns.values[1]):  str(df.loc[i][1]), str(df.columns.values[2]): str(df.loc[i][2])}
    for c in range(1,len(df.columns)):
        #data[i] = {str('RecordId') : str(df.loc[i][0]),str('Assets'):[{"AssetId": str(df.columns.values[c]),"Support": str(df.loc[i][c])}]}
        datac[c] = {"AssetId": str(df.columns.values[c]),"Support": str(df.loc[i][c])}
    data[i]={str('RecordId') : str(df.loc[i][0]),str('Assets'):datac[c]}
    print(data[i])
output_lines = [json.dumps(line)+",\n" for line in data]
output_lines[-1] =   output_lines[-1][:-2] # remove ",\n" from last line
with open(r'Savedwork.json', 'w') as json_file:
        json_file.writelines(output_lines)

What is the output you're getting specifically? Is it an error or a bug? — Liam Crowley
– Liam Crowley, Commented Mar 3, 2021 at 19:32

joao · Accepted Answer · 2021-03-03 21:45:10Z

1

What you need is the iterrows() method, it will iterate over the dataframe's rows as (index, series) pairs. The columns() method will give you the list of column names, so you'll be able to iterate over the columns in the series, and access them by name.

import json
import pandas as pd

df = pd.read_excel('test.xlsx')

recs = []
for i, row in df.iterrows():
    rec = {
        'RecordId': row[0],
        'Assets': [{'AssetId': c, 'Support': row[c]} for c in df.columns[1:]]
    }
    recs.append(rec)

out = {'Records': recs}

(yes, it could all be done in a single list comprehension, but abusing those hinders readability)

Also, you don't need to do json.dumps on lines, and then assemble them with newlines (don't work at the text level): build a dictionary with the entire data, and then json.dump that:

print(json.dumps(out, indent=4))

answered Mar 3, 2021 at 21:45

joao

2,3032 gold badges13 silver badges16 bronze badges

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

Comments

RJ Adriaansen · Accepted Answer · 2021-03-03 22:26:36Z

0

You can create the dicts directly in pandas. First set the first column with F1, F2 as index:

df.set_index(0, inplace = True)
df.index.name = None

Then create the dicts in pandas with dict keys as column names, export it to a dict and save it to json:

import json
df = df.apply(lambda x: [{"AssetId": x.name, "Support": i} for i in x], axis =1).reset_index().rename(columns={'index': 'RecordId', 0: 'Assets'})
json_data = {"Records": df.to_dict('records')}
with open('r'Savedwork.json', 'w') as fp:
    json.dump(json_data, fp)

edited Mar 3, 2021 at 22:26

answered Mar 3, 2021 at 22:05

RJ Adriaansen

9,7092 gold badges16 silver badges29 bronze badges

Comments

Glauco · Accepted Answer · 2021-03-04 15:19:10Z

0

another solution is to take a snapshot of the entire workbook in json format and reorganize it out of the box. Using the collect function of XLtoy is possible to do that via command line, this approach allows you more degrees of freedom.

[i'm the main developer of XLtoy]

answered Mar 4, 2021 at 15:19

Glauco

1,4712 gold badges11 silver badges21 bronze badges

Collectives™ on Stack Overflow

Excel to JSON format with python

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related