Click here to Skip to main content
15,849,829 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have xlsx file with information about sports grounds (cell 5 - area, where sport ground is situated, cell 6 - district in the area, 7 - address in the district). I need json dictionary, where areas (AdmArea) will be keys of big dictionary, values will be small dictionary with districts as keys, addresses (in list format) will be values for small dictionary. Of course each area has more than one district, each district in the meantime has more than one address. Necessary for addresses to be in the order of appearance in the excel file.

This is file xlsx: https://stepik.org/media/attachments/lesson/268674/data-25290-2019-09-30.xlsx
My code prints out for each admArea not corresponding districts. I need in case for the similar area correspond to it all the districts, which are included in this area. It prints out randomly!

What I have tried:

import json

from collections import defaultdict

import openpyxl

wb = openpyxl.load_workbook('data-25290-2019-09-30.xlsx')

fout = open('outa12.json', 'w', encoding='utf-8')

d1 = {}


res = defaultdict(list)

sheet = wb['Sheet0']

for i in range(2, sheet.max_row+1):

    admArea = sheet.cell(row=i,column=5).value

    distr = sheet.cell(row=i,column=6).value

    addr = sheet.cell(row=i,column=7).value

    res[distr].append(addr)

    d1[admArea]=res

json.dump(d1, fout, ensure_ascii=False)

fout.close()
Posted
Updated 20-Aug-20 0:16am
v2
Comments
Richard MacCutchan 20-Aug-20 5:04am    
What happens when you run the code?

1 solution

When I run the code it puts all the districts in the first area, not paying attention on what actual are for each district is.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900