Python API Call: JSON to Pandas DF

Question

I'm working on pulling data from a public API and converting the response JSON file to a Pandas Dataframe. I've written the code to pull the data and gotten a successful JSON response. The issue I'm having is parsing through the file and converting the data to a dataframe. Whenever I run through my for loop, I get a dataframe that retruns 1 row when it should be returning approximately 2500 rows & 6 columns. I've copied and pasted my code below:

Things to note: I've commented out my api key with "api_key". I'm new(ish) to python so I understand that my code formatting might not be best practices. I'm open to changes. Here is the link to the API that I am requesting from: https://developer.va.gov/explore/facilities/docs/facilities?version=current

facilities_data = pd.DataFrame(columns=['geometry_type', 'geometry_coordinates', 'id', 'facility_name', 'facility_type','facility_classification'])


# function that will make the api call and sort through the json data
def get_facilities_data(facilities_data):
    # Make API Call
    res = requests.get('https://sandboxapi.va.gov/services/va_facilities/v0/facilities/all',headers={'apikey': 'api_key'})
    data = json.loads(res.content.decode('utf-8'))
    time.sleep(1)

    for facility in data['features']:
        geometry_type = data['features'][0]['geometry']['type']
        geometry_coordinates = data['features'][0]['geometry']['coordinates']
        facility_id = data['features'][0]['properties']['id']
        facility_name = data['features'][0]['properties']['name']
        facility_type = data['features'][0]['properties']['facility_type']
        facility_classification = data['features'][0]['properties']['classification']

    # Save data into pandas dataframe
    facilities_data = facilities_data.append(
        {'geometry_type': geometry_type, 'geometry_coordinates': geometry_coordinates,
         'facility_id': facility_id, 'facility_name': facility_name, 'facility_type': facility_type,
         'facility_classification': facility_classification}, ignore_index=True)
    return facilities_data


facilities_data = get_facilities_data(facilities_data)
print(facilities_data)```

Answer 1

As mentioned, you should

1. loop over facility instead of data['features'][0]
2. append within the loop

This will get you the result you are after.

    facilities_data = pd.DataFrame(columns=['geometry_type', 'geometry_coordinates', 'id', 'facility_name', 'facility_type','facility_classification'])

    def get_facilities_data(facilities_data):
        # Make API Call
        res = requests.get("https://sandbox-api.va.gov/services/va_facilities/v0/facilities/all",
                     headers={"apikey": "REDACTED"})
        data = json.loads(res.content.decode('utf-8'))
        time.sleep(1)

        for facility in data['features']:
            geometry_type = facility['geometry']['type']
            geometry_coordinates = facility['geometry']['coordinates']
            facility_id = facility['properties']['id']
            facility_name = facility['properties']['name']
            facility_type = facility['properties']['facility_type']
            facility_classification = facility['properties']['classification']

            # Save data into pandas dataframe
            facilities_data = facilities_data.append(
            {'geometry_type': geometry_type, 'geometry_coordinates': geometry_coordinates,
             'facility_id': facility_id, 'facility_name': facility_name, 'facility_type': facility_type,
             'facility_classification': facility_classification}, ignore_index=True)
        return facilities_data


    facilities_data = get_facilities_data(facilities_data)
    print(facilities_data.head())

There are some more things we can improve upon;

• json() can be called directly on requests output
• time.sleep() is not needed
• appending to a DataFrame on each iteration is discouraged; we can collect the data in another way and create the DataFrame afterwards.

Implementing these improvements results in;

def get_facilities_data():
    data = requests.get("https://sandbox-api.va.gov/services/va_facilities/v0/facilities/all",
                     headers={"apikey": "REDACTED"}).json()

    facilities_data = []
    for facility in data["features"]:
        facility_data = (facility["geometry"]["type"],
                         facility["geometry"]["coordinates"],
                         facility["properties"]["id"],
                         facility["properties"]["name"],
                         facility["properties"]["facility_type"],
                         facility["properties"]["classification"])
        facilities_data.append(facility_data)

    facilities_df = pd.DataFrame(data=facilities_data,
                                 columns=["geometry_type", "geometry_coords", "id", "name", "type", "classification"])
    return facilities_df