MysticCode MysticCode - 6 months ago 18
JSON Question

Python - Convert JSON object data into a List

I have data in a JSON object with a

key:value
as shown below in Python. There are two records having same ID 13 for the Hari and 16 for the Liz.

from collections import defaultdict
from itertools import *
from operator import itemgetter

data = [
{
"fname": "Abc",
"lname": "xyz",
"id": 15,
"club": "-",
"date": "-"
},
{
"fname": "Hari",
"lname": "Lee",
"id": 13,
"club": "Manutd",
"date": "2016-03-20T22:00:00.000Z"
},
{
"fname": "David",
"lname": "James",
"id": 14,
"club": "Barca",
"date": "-"
},
{
"fname": "Hari",
"lname": "Lee",
"id": 13,
"club": "Chelsea",
"date": "2012-03-20T22:00:00.000Z"
},
{
"fname": "Liz",
"lname": "Kiz",
"id": 16,
"club": "-",
"date": "-"
},
{
"fname": "Liz",
"lname": "Kiz",
"id": 16,
"club": "Falkon",
"date": "2014-03-20T22:00:00.000Z"
}
]

newdata = []
#for item, value in enumerate(data):
#for i,v in value.iteritems():
#print value['id']
#print value[i]
#print i,v
#newdata.append()


I want to reformat JSON data into list without a key and merge duplicated ID as a list of list. The record with same ID's will be mapped into list of list as shown below. How can I achieve this?

newdata = [[["Hari", "Lee", "Manutd", "2016-03-20T22:00:00.000Z"], ["Hari", "Lee", "Chelsea", "2012-03-20T22:00:00.000Z"]],
["David", "James", "Barca", "-"], ["Abc", "xyz", "-" "-"], [["Liz", "Kiz", "-", "-"], ["Liz", "Kiz", "Falkon", "2014-03-20T22:00:00.000Z"]]]


Iterate over the new list data and write each list data as a row in excel (xlwt) file

for i1, v1 in enumerate(newdata):
for i2,v2 in enumerate(v1):
if(type(v2) is str):
print v2
else:
for i3,v3 in enumerate(v2):
print v3

Answer

To check the same ID you need to store data in a dict

ret_dict = {}

But dict isn't ordered. If you want to hold your order, you can you OrderedDict

from collections import OrderedDict
ret_dict = OrderedDict()
for element in data:
    # To remove 'id' from the dict 'element' use element.pop('id')
    # element.pop('id') return the value of id
    ret_dict.setdefault(element.pop('id'), []).append(element.values())

For me ret_dict.values() is already a good result:

>>> print ret_dict.values()
[[['xyz', '-', '-', 'Abc']], [['Lee', 'Manutd', '2016-03-20T22:00:00.000Z', 'Hari'], ['Lee', 'Chelsea', '2012-03-20T22:00:00.000Z', 'Hari']], [['James', 'Barca', '-', 'David']], [['Kiz', '-', '-', 'Liz'], ['Kiz', 'Falkon', '2014-03-20T22:00:00.000Z', 'Liz']]]

But for exactly what you want, you need to build a new list from the values of the last dict:

ret_list = [e[0] if len(e) == 1 else e for e in ret_dict.itervalues()]

itervalues() to get the iterator of values instead of a list like values()
Output:

>>> print ret_list
[['xyz', '-', '-', 'Abc'], [['Lee', 'Manutd', '2016-03-20T22:00:00.000Z', 'Hari'], ['Lee', 'Chelsea', '2012-03-20T22:00:00.000Z', 'Hari']], ['James', 'Barca', '-', 'David'], [['Kiz', '-', '-', 'Liz'], ['Kiz', 'Falkon', '2014-03-20T22:00:00.000Z', 'Liz']]]