Sorting JSON by value

Question

I am trying to parse the following student grade report JSON using Python

{
    "report":[
        {
            "enrollment": "rit2011001",
            "name": "Julia",
            "subject":[
                {
                    "code": "DSA",
                    "grade": "A"
                }
            ]
        },
        {
            "enrollment": "rit2011020",
            "name": "Samantha",
            "subject":[
                {
                    "code": "COM",
                    "grade": "B"
                },
                {
                    "code": "DSA",
                    "grade": "A"
                }
            ]
        }
    ]
}

Such that the report should be ordered ascending first by code, then by grade and then by enrollment. Here is how the output should be

COM B rit2011020 Samantha
DSA A rit2011001 Julia
DSA A rit2011020 Samantha

Here is my incomplete piece of code that I need help with:

import json

data='''{
    "report":[
        {
            "enrollment": "rit2011001",
            "name": "Julia",
            "subject":[
                {
                    "code": "DSA",
                    "grade": "A"
                }
            ]
        },
        {
            "enrollment": "rit2011020",
            "name": "Samantha",
            "subject":[
                {
                    "code": "COM",
                    "grade": "B"
                },
                {
                    "code": "DSA",
                    "grade": "A"
                }
            ]
        }
    ]
}'''

print data  #for debug
parsed_json = json.loads(data)
print parsed_json #for debug
for key,value in sorted(parsed_json.items()):
    print key,value

I don't know how to apply successive filtering to achieve the result.

Does this help you? https://stackoverflow.com/questions/4110665/sort-nested-dictionary-by-value-and-remainder-by-another-value-in-python — jmoney, Jan 15 '19 at 02:59
@jmoney I already looked at the example and tried following it but I got Errors — tulamba, Jan 15 '19 at 03:02

U13-Forward · Accepted Answer · 2019-01-15T03:15:32.613

Try using a nested loop, with a print:

import json
data='''{
    "report":[
        {
            "enrollment": "rit2011001",
            "name": "Julia",
            "subject":[
                {
                    "code": "DSA",
                    "grade": "A"
                }
            ]
        },
        {
            "enrollment": "rit2011020",
            "name": "Samantha",
            "subject":[
                {
                    "code": "COM",
                    "grade": "B"
                },
                {
                    "code": "DSA",
                    "grade": "A"
                }
            ]
        }
    ]
}'''
print data  #for debug
parsed_json = json.loads(data)
print parsed_json #for debug
for i in parsed_json['report']:
    for x in i['subject']:
        print x['code'],x['grade'],i['enrollment'],i['name']

Output:

DSA A rit2011001 Julia
COM B rit2011020 Samantha
DSA A rit2011020 Samantha

If care about the order of the frame:

import json
data='''{
    "report":[
        {
            "enrollment": "rit2011001",
            "name": "Julia",
            "subject":[
                {
                    "code": "DSA",
                    "grade": "A"
                }
            ]
        },
        {
            "enrollment": "rit2011020",
            "name": "Samantha",
            "subject":[
                {
                    "code": "COM",
                    "grade": "B"
                },
                {
                    "code": "DSA",
                    "grade": "A"
                }
            ]
        }
    ]
}'''
print data  #for debug
parsed_json = json.loads(data)
print parsed_json #for debug
l=[]
for i in parsed_json['report']:
    for x in i['subject']:
        l.append(' '.join([x['code'],x['grade'],i['enrollment'],i['name']]))
print('\n'.join(sorted(l)))

score 1 · Answer 2 · answered Jan 15 '19 at 03:41

If you are willing to use a very popular external library for data analysis then you can use pandas with json_normalize(), e.g.:

In []:
from pandas.io.json import json_normalize

df = json_normalize(parsed_json['report'], 'subject', ['enrollment', 'name'])
df.sort_values(['code', 'grade', 'enrollment']).reset_index(drop=True)

Out[]:
  code grade  enrollment      name
0  COM     B  rit2011020  Samantha
1  DSA     A  rit2011001     Julia
2  DSA     A  rit2011020  Samantha

Sorting JSON by value

2 Answers2