API question: I’m trying to explode the visitor_home_cbgs variable according to the Pyspark directions on the SafeGraph website. However, I’m getting an error that I can’t parse. My code is in the screenshot below. When I run this, I get the following error on the final command (the explode command):
Traceback (most recent call last):
File “”, line 1, in
File “C:\Users\edgel\AppData\Local\Programs\Python\Python39\lib\site-packages\pyspark\sql\dataframe.py”, line 1685, in select
jdf = self._jdf.select(self._jcols(*cols))
File “C:\Users\edgel\AppData\Local\Programs\Python\Python39\lib\site-packages\py4j\java_gateway.py”, line 1321, in call
return_value = get_return_value(
File “C:\Users\edgel\AppData\Local\Programs\Python\Python39\lib\site-packages\pyspark\sql\utils.py”, line 117, in deco
raise converted from None
pyspark.sql.utils.AnalysisException: cannot resolve ‘safegraph_place_id’ given input columns: [city, date_range_end, date_range_start, key, location_name, parsed_visitor_home_cbgs, placekey, poi_cbg, raw_visit_counts,
raw_visitor_counts, region, street_address, value, visitor_home_cbgs];
'Project ['safegraph_place_id, key#122, value#123]
± Generate explode(parsed_visitor_home_cbgs#109), false, [key#122, value#123]
± Project [location_name#86, street_address#87, city#88, region#89, date_range_start#90, date_range_end#91, raw_visit_counts#92L, raw_visitor_counts#93L, poi_cbg#94, visitor_home_cbgs#95, placekey#96, parser(visitor_home_cbgs#95) AS parsed_visitor_home_cbgs#109]
± LogicalRDD [location_name#86, street_address#87, city#88, region#89, date_range_start#90, date_range_end#91, raw_visit_counts#92L, raw_visitor_counts#93L, poi_cbg#94, visitor_home_cbgs#95, placekey#96], false
hi danny. can you try running the request directly on the api?
query lookup{
lookup(placekey:"222-225@5vg-7gv-3qz") {
weekly_patterns(start_date: "2021-07-19" end_date: "2021-08-01") {
location_name
street_address
city
region
date_range_start
date_range_end
raw_visit_counts
raw_visitor_counts
poi_cbg
visitor_home_cbgs
}
}
}
import os
import requests
import json
import pandas as pd
headers = {
'Content-Type': 'application/json',
'apikey': ""
}
query = """
query($placekey: Placekey!){
lookup(placekey: $placekey) {
weekly_patterns(start_date: "2021-07-19" end_date: "2021-08-01") {
location_name
street_address
city
region
date_range_start
date_range_end
raw_visit_counts
raw_visitor_counts
poi_cbg
visitor_home_cbgs
}
}
}
"""
req = requests.post(
'https://api.safegraph.com/v2/graphql',
json={
'query': query,
'variables': { "placekey": "222-225@5vg-7gv-3qz"}
},
headers=headers
)
r_json = req.json()
df = pd.json_normalize(r_json['data']['lookup'], record_path =['weekly_patterns'])
print(str(df.shape[0]), 'rows,', str(df.shape[1]), 'columns')
df.head()
Hi Victor, I get the following error at the pd.json_normalize() step:
Traceback (most recent call last):
File “”, line 1, in
KeyError: ‘data’
are you changing the query? its scoped specifically to the request
if data is erroring it might be a failing call. did you add in your api key to this line 'apikey': ""
i can update the response parsing to pull in the error object from this request
Oh, of course. I forgot to add in my API key for your query. I just added it, and it ran fine.
Is there a way to still explode an already-pulled data frame, though? I wrote my code to use the sgql_client() to pull all relevant observations, so it would be very costly to re-write it using a direct JSON request
I’m also interested in exploding it long (with a CBG column) rather than wide (with each CBG getting its own column)
never mind, I figured it out: “safegraph_place_id” must have been the old name for “placekey”. Once I changed to “placekey”, the example code worked. You may want to change the example on the FAQ page to reflect this
yup we use placekey as the canonical ref. thanks for the callout, will update that page!