getting back key:subkey pair sorted on subkey of subkey of key. python

Question

I have a JSON file

 {
"b0:47:bf:af:c1:42": 
 {
 "No. of visits": 10, "cities": 
    {
      "Mumbai": {"count": 5,"last_visited": "5/22/2016"},
      "Kolkata": {"count": 2,"last_visited": "5/22/2016"},
      "Amritsar":{"count": 3,"last_visited": "5/22/2016"}
     }
},
"c0:ee:fb:71:be:0d": 
 {
 "No. of visits": 24, "cities": 
 {
  "Mumbai": {"count": 2,"last_visited": "5/22/2016"},
  "Kolkata": {"count": 20,"last_visited": "5/22/2016"},
  "Amritsar":{"count": 2,"last_visited": "5/22/2016"}
 }
}
}

SO I was sorting on count to get max_visited city, 2nd max_visited city and so on for each mac by this, after parsing to dictionary.

for mac in dic_data:
    sorted_cities = sorted(cities, key=lambda x: cities[x]['count'], reverse=True)

this gives me output like this.

['Kolkata', 'Amritsar', 'Mumbai']
['Amritsar', 'Mumbai', 'Kolkata']

which is fine but lets say I want to sort on "count" for whole dictionary and return all the keys for that , so that it can be accessed later. Like for this example max value of "count" is for "c0:ee:fb:71:be:0d" key "cities" sub key and "Kolkata" further sub key. so I want to store it in some structure by which I can call it later. * am trying to store it in array of array*. which I want to access later as dic array[0][1]. I am not sure if it can be done like this. All I need is to call the "Kolkata" and its "count" later. So, My output may look like

[["c0:ee:fb:71:be:0d","cities","Kolkata"],
 ["b0:47:bf:af:c1:42","cities","Mumbai"],
 ["b0:47:bf:af:c1:42","cities","Amritsar"],
 ["c0:ee:fb:71:be:0d","cities","Mumbai"],
 ["c0:ee:fb:71:be:0d","cities","Mumbai"],
 ["c0:ee:fb:71:be:0d","cities","Amritsar"],
 ["b0:47:bf:af:c1:42","cities","Kolkata"]]

Answer 1

Here you go, That's whatever I understood from the question, specifics? Ask!

>>> with open('file.txt', 'r') as f:
...     json_ = json.load(f)
>>> formatted_ = [[key, 'cities', city, value['cities'][city]['count']] for key, value in json_.items() for city in value['cities']]
>>> formatted_
185: [[u'b0:47:bf:af:c1:42', 'cities', u'Kolkata', 2],
 [u'b0:47:bf:af:c1:42', 'cities', u'Amritsar', 3],
 [u'b0:47:bf:af:c1:42', 'cities', u'Mumbai', 5],
 [u'c0:ee:fb:71:be:0d', 'cities', u'Kolkata', 20],
 [u'c0:ee:fb:71:be:0d', 'cities', u'Amritsar', 2],
 [u'c0:ee:fb:71:be:0d', 'cities', u'Mumbai', 2]]
>>> sorted_ = sorted(formatted_, key=lambda x: x[3])
>>> sorted_
186: [[u'b0:47:bf:af:c1:42', 'cities', u'Kolkata', 2],
 [u'c0:ee:fb:71:be:0d', 'cities', u'Amritsar', 2],
 [u'c0:ee:fb:71:be:0d', 'cities', u'Mumbai', 2],
 [u'b0:47:bf:af:c1:42', 'cities', u'Amritsar', 3],
 [u'b0:47:bf:af:c1:42', 'cities', u'Mumbai', 5],
 [u'c0:ee:fb:71:be:0d', 'cities', u'Kolkata', 20]]
>>> sorted_ = sorted(formatted_, key=lambda x: x[3], reverse=True)
>>> sorted_
187: [[u'c0:ee:fb:71:be:0d', 'cities', u'Kolkata', 20],
 [u'b0:47:bf:af:c1:42', 'cities', u'Mumbai', 5],
 [u'b0:47:bf:af:c1:42', 'cities', u'Amritsar', 3],
 [u'b0:47:bf:af:c1:42', 'cities', u'Kolkata', 2],
 [u'c0:ee:fb:71:be:0d', 'cities', u'Amritsar', 2],
 [u'c0:ee:fb:71:be:0d', 'cities', u'Mumbai', 2]]
>>> minimize_sorted_ = [item[:3] for item in sorted_]
>>> minimize_sorted_
188: [[u'c0:ee:fb:71:be:0d', 'cities', u'Kolkata'],
 [u'b0:47:bf:af:c1:42', 'cities', u'Mumbai'],
 [u'b0:47:bf:af:c1:42', 'cities', u'Amritsar'],
 [u'b0:47:bf:af:c1:42', 'cities', u'Kolkata'],
 [u'c0:ee:fb:71:be:0d', 'cities', u'Amritsar'],
 [u'c0:ee:fb:71:be:0d', 'cities', u'Mumbai']]