Enriching Excel Files with Python Pandas

import requests
import pandas as pd
import logging

# setup logging, so we can view what's happening as it executes
logging.getLogger().addHandler(logging.StreamHandler())
logging.getLogger().setLevel(logging.INFO)

# define the file/sheet names
existing_file = 'endpoint-report.xlsx'
new_file = 'endpoint-report-with-locations.xlsx'
sheet_name = 'Endpoint Data'


# reusable function to get the location for a given IP address
def make_api_call(ip_address: str):
    """Return the location for a given IP address."""
    url = f"https://mywebsite/api/?ip={ip_address}"
    resp = requests.get(url=url)
    if resp.status_code == 200:
        location = resp.json()['location']
        return location
    else:
        raise HTTPError(resp.status_code, resp.text)


if __name__ == '__main__':

    # open the Excel document and import as a Pandas data frame
    df = pd.read_excel(existing_file, sheet_name=sheet_name)

    # get the values of the column 'IPv4 Address'
    ip_addresses = df['IPv4 Address']

    # create an empty list to store the location values
    locations = []

    # get the total rows in the data frame and create a counter to track progress
    total_ips = len(ip_addresses)
    processed_ips = 0

    # loop over each IP address
    for ip in ip_addresses:
        # query API to get the location
        location = make_api_call(ip)

        # log to screen the IP and the discovered location
        logging.info(f"{processed_ips}/{total_ips}: {ip} --> {location}")

        # add the discovered location to the list of location values
        locations.append(location)

        # increment the number of processed IP addresses
        processed_ips += 1

    # create a new column in the dataframe called 'location' and assign the values from the now populated list
    df['location'] = locations

    # save the data frame to a new Excel document
    df.to_excel(new_file, sheet_name=sheet_name)