How to Create a Nested JSON using Python

Question

I already read Create nested JSON from csv, but it didn't help in my case.

I would like to create a json from an excel spreadsheet using python. the code below generates a dic then a json, however i would like to revise the code further so that i can get the following json. I have had no luck so far.

Desired outcome:

{"items": { "AMS Upgrade": [ {"Total": "30667"}, {"% Complete to end": "100%"}, {"value claimed": "25799"} ], "BMS works": [ {"Total": "35722"}, {"% Complete to end": "10%"}, {"value claimed": "3572"} ] }}

Current outcome:

{"line items": {"AMS Upgrade": "30667", "BMS Modification": "35722"}}

Current code:

book = xlrd.open_workbook("Example - supporting doc.xls")
first_sheet = book.sheet_by_index(-1)
nested_dict = {}


nested_dict["line items"] = {}
for i in range(21,175):
    Line_items = first_sheet.row_slice(rowx=i, start_colx=2, end_colx=8)

    if str(Line_items[0].value) and str(Line_items[1].value):
        if not Line_items[5].value ==0 : 
            print str(Line_items[0].value)
            print str(Line_items[5].value)
            nested_dict["line items"].update({str(Line_items[0].value) : str(Line_items[1].value)})

print  nested_dict

print json.dumps(nested_dict)

Excel print out:

Where's the data?

Stephen Rauch
– Stephen Rauch ♦

2019-02-12 02:13:31 +00:00
Commented Feb 12, 2019 at 2:13 — Stephen Rauch
– Stephen Rauch ♦, Commented Feb 12, 2019 at 2:13
sorry, i did revise the code to original, ignore original

Ossama
– Ossama

2019-02-12 02:15:58 +00:00
Commented Feb 12, 2019 at 2:15 — Ossama
– Ossama, Commented Feb 12, 2019 at 2:15
Possible duplicate of Create nested JSON from CSV

Andreas
– Andreas

2019-02-12 02:27:51 +00:00
Commented Feb 12, 2019 at 2:27 — Andreas
– Andreas, Commented Feb 12, 2019 at 2:27

keineahnung2345 · Accepted Answer · 2019-02-12 02:28:32Z

1

book = xlrd.open_workbook("Example - supporting doc.xls")
first_sheet = book.sheet_by_index(-1)
nested_dict = {}

nested_dict["line items"] = {}
col_names = {1: "Total", 2: "% Complete to end", 5: "value claimed"}

for i in range(21,175):
    Line_items = first_sheet.row_slice(rowx=i, start_colx=2, end_colx=8)

    if str(Line_items[0].value) and str(Line_items[1].value):
        if not Line_items[5].value ==0 :
            inner_list = []
            for j in [1, 2, 5]:
                inner_list.append({col_names[j]: Line_items[j].value})
            nested_dict["line items"].update({str(Line_items[0].value) : inner_list})

print(nested_dict)

print(json.dumps(nested_dict))

answered Feb 12, 2019 at 2:28

keineahnung2345

2,7014 gold badges16 silver badges30 bronze badges

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

Collectives™ on Stack Overflow

How to Create a Nested JSON using Python

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related