Convert CSV to well-structured JSON in Python
Question: I have a CSV file that is structured as below :

`Store, Region, District, MallName, Location`

    
    
    1234,90,910,MallA,GMT
    4567,87,902,MallB,EST
    2468,90,811,MallC,PST
    1357,87,902,MallD,CST
    

What I was able to accomplish with my iterative brow-beating was getting a
format like so:

    
    
    {
      "90": {
        "910": {
          "1234": {
            "name": "MallA",
            "location": "GMT"
          }
        },
        "811": {
          "2468": {
            "name": "MallB",
            "location": "PST"
          }
        }
      },
      "87": {
        "902": {
          "4567": {
            "name": "MallB",
            "location": "EST"
          },
          "1357": {
            "name": "MallD",
            "location": "CST"
          }
        }
      }
    }
    

The code below is stripped down to match the sample data set I provided but
you get the idea as to what is happening. Again, it's very iterative and non-
pythonic which I'm trying to also move towards. (If anyone feels the defined
procedures would be worthwhile to post I can).

    
    
    #************
    #   Main()
    #************
    dictHierarchy = {}
    
    with open(getFilePath(), 'r') as f:
        content = [line.strip('\n') for line in f.readlines()]
    
    for data in content:
        data = data.split(",")
    
        myRegion = data[1]
        myDistrict = data[2]
        myName = data[3]
        myLocation = data[4]
        myStore = data[0]
    
        if myRegion in dictHierarchy:
            #check for District
            if myDistrict in dictHierarchy[myRegion]:
                #checkforStore
                dictHierarchy[myRegion][myDistrict].update({myStore:addStoreDetails(data)})
            else:
                #add district
                dictHierarchy[myRegion].update({myDistrict:addStore(data)}) 
        else:
            #add region
            dictHierarchy.update({myRegion:addDistrict(data)})
    
    with open('hierarchy.json', 'w') as outfile:
        json.dump(dictHierarchy, outfile)
    

Obsessive compulsive me looked at the JSON output above and thought that to
someone blindly opening the file it looks like a hodge-podge. What I was
hoping to do for plain-text readability is group the data and throw it into
JSON format as so:

    
    
    {"Regions":[
        {"Region":"90", "Districts":[
            {"District":"910", "Stores":[
                {"Store":"1234", "name":"MallA", "location":"GMT"}]},
            {"District":"811", "Stores":[
                {"Store":"2468", "name":"MallC", "location":"PST"}]}]},
        {"Region":"87", "Districts":[
            {"District":"902", "Stores":[
                {"Store":"4567", "name":"MallB", "location":"EST"},
                {"Store":"1357", "name":"MallD", "location":"CST"}]}]}]}
    

Long story short I wasted quite some time today trying to sort out how to
actually populate the data structure in Python and essentially ended up no
where. Is there a clean, pythonic way to achieve this? Is it even worth the
effort?


Answer: I've added headers to your input like:

    
    
    Store,Region,District,name,location
    1234,90,910,MallA,GMT
    4567,87,902,MallB,EST
    2468,90,811,MallC,PST
    1357,87,902,MallD,CST
    

then used python [csv reader](https://docs.python.org/2/library/csv.html) and
[group by](https://docs.python.org/2/library/itertools.html#itertools.groupby)
like this:

    
    
    import csv
    from itertools import groupby, ifilter
    from operator import itemgetter
    
    data = []
    
    with open('in.csv') as csvfile:
        reader = csv.DictReader(csvfile)
    
        regions = []
    
        regions_dict = sorted(list(reader), key=itemgetter('Region'))
        for region_id, region_group in groupby(regions_dict, itemgetter('Region')):
    
            districts = []
            regions.append({'Region': region_id, 'Districts': districts})
    
            districts_dict = sorted(region_group, key=itemgetter('District'))
            for district_id, district_group in groupby(districts_dict, itemgetter('District')):
                districts.append({'District': district_id, 'Stores': list(district_group)})
    
    print regions
    

