How to Parse API Responses (XML, JSON, or Other Formats) into Tabular Format in Domo Jupyter Workspace

While Domo offers a wide range of powerful native connectors to integrate data from various sources, there are times when you may need to access specific APIs or handle complex data that isn't covered by these connectors. In such cases, Domo’s Jupyter Workspace provides an excellent solution. By leveraging Python within Jupyter, you can directly fetch data from any API, process it as needed, and seamlessly integrate the results into Domo for further analysis and visualization. In this article, we will explore how to parse API responses, focusing on JSON and XML formats, and convert them into a DataFrame that can be used effortlessly within your Domo environment.

To create a Jupyter Workspace in Domo:

1. Log in to Domo: Open your Domo instance and log in with your credentials.
2. Navigate to Data Center: Click on the "Data" tab from the top navigation bar.
3. Select Jupyter Workspaces: Look for the "Jupyter Workspaces" option under the "Integrations" section.
4. Create a New Workspace: Click on the "Create" button to set up a new workspace. Give it a meaningful name and select the size of the instance based on your data processing needs.
5. Open Workspace: Once created, click on the workspace to launch Jupyter Notebook in a new tab.

Now that your workspace is ready, you can start processing API responses by writing Python scripts.

Making API Calls in Python

APIs allow applications to communicate with each other. The response formats are often JSON or XML, which are widely supported in Python. Let’s demonstrate how to make an API call and handle the response.

Here’s an example of making an API call to a public endpoint:

Example Code:

import requests

# Define the API endpoint and parameters

url = "https://api.example.com/data"

headers = {"Authorization": "Bearer YOUR_API_TOKEN"}

params = {"key1": "value1", "key2": "value2"}

# Make the API request

response = requests.get(url, headers=headers, params=params)

# Check the status of the response

if response.status_code == 200:

print("API call successful!")

api_data = response.text

else:

print(f"API call failed with status code {response.status_code}")

api_data = None

In this example, replace YOUR_API_TOKEN and the url with the actual API details. The response.text contains the raw data from the API.

Authentication Methods in API Calls

The sample API call above uses Header Authentication (via a Bearer token), but there are various authentication methods depending on the API, such as Basic Authentication, OAuth, and API Key authentication. Each requires a different setup. For more details on handling different types of authentication in Python, refer to this article.

?

Parsing API Responses into Tabular Format

API responses come in various formats, such as JSON, XML, or CSV. Parsing these into a tabular format requires different techniques depending on the format.

1. Parsing JSON Responses

JSON is a popular format for API responses, as it is lightweight and easy to use.

Example Code:

import pandas as pd

import json

# Example JSON response (usually from the API response)

json_data = '''{

"data": {

"users": [

{

"id": 1,

"name": "Alice",

"email": "[email protected]",

"details": {"age": 25, "city": "New York"}

},

{

"id": 2,

"name": "Bob",

"email": "[email protected]",

"details": {"age": 30, "city": "Los Angeles"}

}

]

}

}'''

# Parse the JSON string

parsed_data = json.loads(json_data)

# Extract nested data

if "data" in parsed_data and "users" in parsed_data["data"]:

users = parsed_data["data"]["users"]

# Normalize nested JSON into a flat table

df = pd.json_normalize(users)

print(df)

else:

print("Key 'data.users' not found in the response.")

Output:

id name email details.age details.city

0 1 Alice [email protected] 25 New York

1 2 Bob [email protected] 30 Los Angeles

2. Parsing XML Responses

XML is another common API response format. You can use libraries like xml.etree.ElementTree to parse XML data.

Example Code:

import pandas as pd

import xml.etree.ElementTree as ET

# Example XML response

xml_data = '''<response>

<users>

<user>

<id>1</id>

<name>Alice</name>

<email>[email protected]</email>

<details>

<age>25</age>

<city>New York</city>

</details>

</user>

<user>

<id>2</id>

<name>Bob</name>

<email>[email protected]</email>

<details>

<age>30</age>

<city>Los Angeles</city>

</details>

</user>

</users>

</response>'''

# Parse the XML string

root = ET.fromstring(xml_data)

# Extract data into a list of dictionaries

rows = []

for user in root.findall(".//user"):

rows.append({

"id": user.find("id").text,

"name": user.find("name").text,

"email": user.find("email").text,

"age": user.find("details/age").text,

"city": user.find("details/city").text

})

# Convert to DataFrame

df = pd.DataFrame(rows)

print(df)

Output:

id name email details.age details.city

0 1 Alice [email protected] 25 New York

1 2 Bob [email protected] 30 Los Angeles

?

3. Handling Other Formats (e.g., CSV)

If the API response is in CSV format, you can directly use Pandas to read it.

Example Code:

from io import StringIO

# Example CSV response

csv_data = """id,name,email,age,city

1,Alice,[email protected],25,New York

2,Bob,[email protected],30,Los Angeles

"""

# Convert CSV string to DataFrame

df = pd.read_csv(StringIO(csv_data))

print(df)

Output:

id name email details.age details.city

0 1 Alice [email protected] 25 New York

1 2 Bob [email protected] 30 Los Angeles

?

Steps to Save a DataFrame as a Dataset in Domo Using Jupyter Workspace

1. Creating the Output Dataset: When you're inside your Jupyter Workspace, go to the "Edit" section at the top. Look for the "Output Dataset" section. Click on "+ Create Output Dataset". Provide a name for your output dataset, and select the columns you want to include.
2. Using the Output Dataset Inside Jupyter: After creating the output dataset, it will be available for you to write data into directly from the Python code inside your Jupyter notebook.
3. Saving the DataFrame to the Output Dataset: Once you've created the output dataset, you can reference it in your code and save a DataFrame to that dataset using the following method:

Code:

????????? import domojupyter as domo

????????? domo.write_dataframe(df, output name')

?By following these steps, you can effectively fetch and parse API responses into tabular formats directly within Domo’s Jupyter Workspace, enabling seamless data integration and analysis.