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
- What does cls() function do inside a class method?
- Load Registered Component in Azure ML for Pipeline using Python sdk v2
- Can I add message to the tqdm progressbar?
- How can I preserve the previous value to find the row that is greater than it?
- What's a correct and good way to implement __hash__()?
- Python Harvesters Image Acquisition GigeCam
- Why do I get a recursion error when the depth of the expected recursion should be way less than 999?
- How to (intermittently) skip certain cells when running IPython notebook?
- How to give jupyter cell standard input in python?
- Connect EtherCAT Device with pysoem
- Regex to substitute the next two words after a matching point
- What is the time complexity of heapq.nlargest?
- How to check if a given number is a power of two?
- How to move Jupyter notebook cells up/down using keyboard shortcut?
- cannot override sys.excepthook
- Getting Python's unittest results in a tearDown() method
- Is redis in Python asynchronous?
- How to tell Click to always show option defaults
- flask sqlalchemy with circular imports with db models
- .join with a query
- Python's argparse to show program's version with prog and version string formatting
- classifiers in scikit-learn that handle nan/null
- How does improve detection of line in images by open cv?
- Performance Advantages to Iterators?
- Problem with selecting a specific web element with Playwright in Python
- Thread long running python task, doesn't work inside Docker
- How to add a column with JSON representation of rows in Polars DataFrame?
- Pandas insert empty row at 0th position
- What should HTTP 201 response body be when responding to a POST request with large data?
- Can Python's unittest test in parallel, like nose can?