API response:
[{'results': [{'location': {'address': '150 Greenwood Ave',
'country': 'CA',
'cross_street': 'Dundas St E',
'formatted_address': '150 Greenwood Ave (Dundas St E), Toronto ON M4L 2R1',
'locality': 'Toronto',
'postcode': 'M4L 2R1',
'region': 'ON'},
'name': 'Leslieville Farmers Market',
'rating': 9.1},
{'location': {'address': '1001 Eastern Ave',
'country': 'CA',
'cross_street': 'Woodfield Rd',
'formatted_address': '1001 Eastern Ave (Woodfield Rd), Toronto ON M4L 1A8',
'locality': 'Toronto',
'postcode': 'M4L 1A8',
'region': 'ON'},
'name': 'Rorschach Brewing',
'rating': 8.7},
{'location': {'address': '1483 Queen St E',
'country': 'CA',
'cross_street': '',
'formatted_address': '1483 Queen St E, Toronto ON M4L 1E2',
'locality': 'Toronto',
'postcode': 'M4L 1E2',
'region': 'ON'},
'name': 'Chick-N-Joy',
'rating': 7.5},
{'location': {'address': '1592 Queen St E',
'country': 'CA',
'cross_street': '',
'formatted_address': '1592 Queen St E, Toronto ON M4L 1G1',
'locality': 'Toronto',
'postcode': 'M4L 1G1',
'region': 'ON'},
'name': 'Velotique',
'rating': 7.8},
{'location': {'address': '6 Coxwell Ave',
'country': 'CA',
'cross_street': 'Queen',
'formatted_address': '6 Coxwell Ave (Queen), Toronto ON M4L 3A7',
'locality': 'Toronto',
'postcode': 'M4L 3A7',
'region': 'ON'},
'name': 'O Sushi',
'rating': 7.5},
{'location': {'address': 'Queen St',
'country': 'CA',
'cross_street': 'at Kingston Rd',
'formatted_address': 'Queen St (at Kingston Rd), Toronto ON M4L 1G7',
'locality': 'Toronto',
'postcode': 'M4L 1G7',
'region': 'ON'},
'name': 'Woodbine Park',
'rating': 8.5},
{'location': {'address': '1374 Queen St E',
'country': 'CA',
'cross_street': 'at Greenwood Ave',
'formatted_address': '1374 Queen St E (at Greenwood Ave), Toronto ON M4L 1C9',
'locality': 'Toronto',
'postcode': 'M4L 1C9',
'region': 'ON'},
'name': 'The Sidekick',
'rating': 8.8},
{'location': {'address': '1636 Queen St E',
'country': 'CA',
'cross_street': 'btwn Coxwell Ave. & Kingston Rd.',
'formatted_address': '1636 Queen St E (btwn Coxwell Ave. & Kingston Rd.), Toronto ON M4L 1G3',
'locality': 'Toronto',
'postcode': 'M4L 1G3',
'region': 'ON'},
'name': "The Burger's Priest",
'rating': 7.4},
{'location': {'address': '1646 Queen St E',
'country': 'CA',
'cross_street': 'at Eastern Ave,',
'formatted_address': '1646 Queen St E (at Eastern Ave,), Toronto ON M4L 1G3',
'locality': 'Toronto',
'postcode': 'M4L 1G3',
'region': 'ON'},
'name': 'Casa Di Giorgios',
'rating': 7.4},
{'location': {'address': '73 Coxwell Ave',
'country': 'CA',
'cross_street': 'Dundas St & Coxwell St',
'formatted_address': '73 Coxwell Ave (Dundas St & Coxwell St), Toronto ON M4L 3B1',
'locality': 'Toronto',
'postcode': 'M4L 3B1',
'region': 'ON'},
'name': 'British Style Fish & Chips',
'rating': 7.6}],
'context': {'geo_bounds': {'circle': {'center': {'latitude': 43.665269,
'longitude': -79.319796},
'radius': 1000}}}},
{'results': [{'location': {'address': '31 Powerhouse St',
'country': 'CA',
'cross_street': 'Lansdowne Ave',
'formatted_address': '31 Powerhouse St (Lansdowne Ave), Toronto ON M6H 0C7',
'locality': 'Toronto',
'postcode': 'M6H 0C7',
'region': 'ON'},
'name': "Balzac's Coffee Roasters",
'rating': 8.5},
{'location': {'address': '1200 Lansdowne Ave',
'country': 'CA',
'cross_street': '',
'formatted_address': '1200 Lansdowne Ave, Toronto ON M6H 3Z8',
'locality': 'Toronto',
'postcode': 'M6H 3Z8',
'region': 'ON'},
'name': 'Earlscourt Park Off-Leash Area',
'rating': 6.2},
{'location': {'address': '330 Geary Ave',
'country': 'CA',
'cross_street': 'at Lightbourn Ave.',
'formatted_address': '330 Geary Ave (at Lightbourn Ave.), Toronto ON M6H 2C7',
'locality': 'Toronto',
'postcode': 'M6H 2C7',
'region': 'ON'},
'name': 'Rehearsal Factory',
'rating': 6.7},
{'location': {'address': '229 Geary Ave',
'country': 'CA',
'cross_street': 'at Dufferin St',
'formatted_address': '229 Geary Ave (at Dufferin St), Toronto ON M6H 2C1',
'locality': 'Toronto',
'postcode': 'M6H 2C1',
'region': 'ON'},
'name': 'The Greater Good Bar',
'rating': 9.0},
{'location': {'address': '1311 Saint Clair Ave W',
'country': 'CA',
'cross_street': '',
'formatted_address': '1311 Saint Clair Ave W, Toronto ON M6E 1C2',
'locality': 'Toronto',
'postcode': 'M6E 1C2',
'region': 'ON'},
'name': 'Tre Mari Bakery',
'rating': 9.0},
{'location': {'address': '1200 Lansdowne Ave',
'country': 'CA',
'cross_street': '',
'formatted_address': '1200 Lansdowne Ave, Toronto ON M6H 3Z8',
'locality': 'Toronto',
'postcode': 'M6H 3Z8',
'region': 'ON'},
'name': 'Earlscourt Park',
'rating': 7.5},
{'location': {'address': '217 Geary Ave',
'country': 'CA',
'cross_street': '',
'formatted_address': '217 Geary Ave, Toronto ON M6H 2C1',
'locality': 'Toronto',
'postcode': 'M6H 2C1',
'region': 'ON'},
'name': 'Parallel',
'rating': 8.6},
{'location': {'address': '1359 Davenport Rd',
'country': 'CA',
'cross_street': '',
'formatted_address': '1359 Davenport Rd, Toronto ON M6H 2H5',
'locality': 'Toronto',
'postcode': 'M6H 2H5',
'region': 'ON'},
'name': 'The Sovereign',
'rating': 7.3},
{'location': {'address': '229 Geary Ave',
'country': 'CA',
'cross_street': '',
'formatted_address': '229 Geary Ave, Toronto ON M6H 2C1',
'locality': 'Toronto',
'postcode': 'M6H 2C1',
'region': 'ON'},
'name': 'North Of Brooklyn Pizzeria',
'rating': 7.6},
{'location': {'address': '1256 Saint Clair Ave W',
'country': 'CA',
'cross_street': 'at Boon Ave.',
'formatted_address': '1256 Saint Clair Ave W (at Boon Ave.), Toronto ON M6E 1B9',
'locality': 'Toronto',
'postcode': 'M6E 1B9',
'region': 'ON'},
'name': 'Rio 40 Restaurant',
'rating': 8.4}],
'context': {'geo_bounds': {'circle': {'center': {'latitude': 43.67142,
'longitude': -79.445947},
'radius': 1000}}}},
....
I am trying to create a dataframe with an id for each of the ‘results’ in the above API response. I am grouping the results by the ‘latitude’ and ‘longitude’ in each result.
I have tried:
df = pd.json_normalize(info, 'results', [['context', 'geo_bounds', 'circle', 'center', 'latitude'],
['context', 'geo_bounds', 'circle', 'center','longitude']])
df['id'] = df.groupby(['context.geo_bounds.circle.center.latitude',
'context.geo_bounds.circle.center.longitude']).ngroup()+1
But I am getting:
https://i.stack.imgur.com/rA1On.png
Expected result:
ID name rating
0 1 Leslieville Farmers Market 9.1
1 1 Rorschach Brewing 8.7
2 1 Chick-N-Joy 7.6
3 1 Velotique 7.4
4 1 O Sushi 7.5
5 1 Woodbine Park 8.5
6 1 The Sidekick 8.8
7 1 The Burger's Priest 7.6
8 1 Casa Di Giorgios 7.4
9 1 British Style Fish & Chips 7.6
10 2 Balzac's Coffee Roasters 8.4
11 2 Rehearsal Factory 6.7
12 2 Earlscourt Park Off-Leash Area 6.2
13 2 The Greater Good Bar 8.9
14 2 Tre Mari Bakery 9.0
15 2 Earlscourt Park 7.5
16 2 The Sovereign 7.3
17 2 Parallel 8.2
18 2 Rio 40 Restaurant 8.4
19 2 North Of Brooklyn Pizzeria 7.3
Any help would be greatly appreciated! Also, if any more info is needed let me know.
>Solution :
If data contains your list of results from the question, you can do:
all_data = []
for id_, d in enumerate(data, 1):
for r in d["results"]:
all_data.append({"ID": id_, "name": r["name"], "rating": r["rating"]})
df = pd.DataFrame(all_data)
print(df)
Prints:
ID name rating
0 1 Leslieville Farmers Market 9.1
1 1 Rorschach Brewing 8.7
2 1 Chick-N-Joy 7.5
3 1 Velotique 7.8
4 1 O Sushi 7.5
5 1 Woodbine Park 8.5
6 1 The Sidekick 8.8
7 1 The Burger's Priest 7.4
8 1 Casa Di Giorgios 7.4
9 1 British Style Fish & Chips 7.6
10 2 Balzac's Coffee Roasters 8.5
11 2 Earlscourt Park Off-Leash Area 6.2
12 2 Rehearsal Factory 6.7
13 2 The Greater Good Bar 9.0
14 2 Tre Mari Bakery 9.0
15 2 Earlscourt Park 7.5
16 2 Parallel 8.6
17 2 The Sovereign 7.3
18 2 North Of Brooklyn Pizzeria 7.6
19 2 Rio 40 Restaurant 8.4