python - create json file from input matrix by grouping rows
Question: Given the matrix below, I'd like to create and json output that will iterate
through the matrix rows and append items (col 1) if the group (col2) repeats:

    
    
      matrix = [ 
            ['JAN', '1', 'abc'], 
            ['FEB', '7', 'xyz'], 
            ['FEB', '4', 'abc'], 
            ['MAR', '3', 'xyz'], 
            ['DEC','12', 'xyz']
          ]
    

how can be the a output/JSON file created:

    
    
    {
         "month": ["JAN","FEB","FEB","MAR","DEC"],
         "items": [
            { "group":"abc",
             "data":[1,4]
            },
            { "group":"xyz",
             "data":[7,3,12]
            }
          ]
        }
    

I've tried transposed matrix/column and construct a dictionary where I append
items in a for loop, but I am missing something and it's getting messy. it
should be a simple way.


Answer: This program:

    
    
    from itertools import groupby
    import json
    matrix = [
        ['JAN',  '1', 'abc'],
        ['FEB',  '7', 'xyz'],
        ['FEB',  '4', 'abc'],
        ['MAR',  '3', 'xyz'],
        ['DEC', '12', 'xyz']
    ]
    months = [x[0] for x in matrix]
    items = [[x[2], int(x[1])] for x in matrix]
    items = sorted(items, key=lambda x: x[0])
    items = groupby(items, lambda x: x[0])
    items = [{"group": k, "data": [x[1] for x in g]} for k, g in items]
    result = {
        "month": months,
        "items": items
    }
    print (json.dumps(result, sort_keys=True, indent=4, separators=(',', ': ')))
    

produces this output:

    
    
    {
        "items": [
            {
                "data": [
                    1,
                    4
                ],
                "group": "abc"
            },
            {
                "data": [
                    7,
                    3,
                    12
                ],
                "group": "xyz"
            }
        ],
        "month": [
            "JAN",
            "FEB",
            "FEB",
            "MAR",
            "DEC"
        ]
    }
    

