JSON to data frame after some calculations 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"}
             }
        }
    }
    

there are large no. of keys like `"b0:47:bf:af:c1:42"` so what I want to take
this key as index or first column of a data frame and then store `max_visited
city` in one column which I have to get from data stored in each city like in
this case max_visited city is `"Mumbai"` whose count is `5` .and one more
column as `% visit to max_visited city` like in this case it is `50%` so first
row of data frame will be something like this.

    
    
             mac_Address     max_visited city   % visit to max_visited city
    0  b0:47:bf:af:c1:42               Mumbai                            50
    

I have to do a lot of this kind of conversion from JSON to data frame applying
some calculations. I have put may problem a short and simple form, so any help
on this ? I am using python 2.7


Answer: 
    import json
    
    d = {
       "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"}
             }
        },
        "k0:k0:k0:k0:k0:k0": 
         {
         "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"}
             }
        }
    }
    
    table = []
    for mac, data in d.items():
        row = [ mac ]
        no_visits = data["No. of visits"]
        max = 0
        max_city = ""
        for city, city_data in data["cities"].items():
            if city_data["count"] > max:
                max = city_data["count"]
                max_city = city
        row += [ max_city, max * 100 / no_visits ]
        table.append(row)
    
    print "mac_address\t\tmax_vis city\t\t%visit to max_vis city"
    for row in table:
        print "{}\t\t{}\t\t{}%".format(row[0], row[1], row[2])
    

Output:

    
    
    $ python test.py
    mac_address             max_vis city        %visit to max_vis city
    b0:47:bf:af:c1:42       Mumbai              50%
    k0:k0:k0:k0:k0:k0       Kolkata             83%
    

