pythondataframegrouping
How can I group API results by an id in a dataframe
I got information from an API returning places for each specific location in each of the rows in my dataframe table. I then transformed this information into another dataframe table. Now I'm wondering how I can create an id for the 2nd table that groups each result returned by the id in the first.
My API results:
[{'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.6},
{'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.4},
{'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.6},
{'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.4},
{'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': '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': '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': 8.9},
{'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': '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': '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.2},
{'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},
{'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.3}],
'context': {'geo_bounds': {'circle': {'center': {'latitude': 43.67142,
'longitude': -79.445947},
'radius': 1000}}}},
Like I want result#1 to be id 1, result#2 to be id 2.
My first table:
Second table:
I have tried to do something like this:
id = 0
for res in info:
id+=1
df = pd.DataFrame({
'id': id,
'name' : names,
'rating' : ratings,
'location' : locations
})
But this just returns the same id for each row.
I am expecting something like:
| id | place |
|---|---|
| 1 | place_1_in location_1 |
| 1 | place_2_in location_1 |
| 2 | place_1_in location_2 |
| 3 | place_2_in location_3 |
Solution
Here is one option using pd.json_normalize where data is your API result
# normalize the json data from the API
df = pd.json_normalize(data, 'results', [['context', 'geo_bounds', 'circle', 'center', 'latitude'],
['context', 'geo_bounds', 'circle', 'center', 'longitude']])
# groupby the geo_bounds and get the number of groups
df['ID'] = df.groupby(['context.geo_bounds.circle.center.latitude',
'context.geo_bounds.circle.center.longitude']).ngroup()+1
# you can then keep the columns that you need
print(df[['ID', 'name', 'rating', 'location.formatted_address']])
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