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

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:"[email protected]") {
    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": "[email protected]"}
    },
    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!