In this sample query we will be accessing the data from the Event API endpoint to calculate and visualize the number of events in India, Indonesia, Bangladesh and Nepal.
¶ API endpoint
In order to calculate and visualize the number of events in respective countries, we can build an API query in PowerBI or Microsoft Excel and later use bar chart (horizontal or vertical) to visualize the results. In this example we will be accessing the data from Event API endpoint. We will be filtering the data for our respective countries by using their ids. The ids for India, Indonesia, Bangladesh and Nepal are 84, 85, 27 and 123 respectively. So our URL for this specific query would look like this:
https://goadmin.ifrc.org/api/v2/event/?countries__in=123%2C84%2C85%2C27
- Here
https://goadmin.ifrc.org/api/is the base URL that tells PowerBI (or any other service/tool referencing the API) to connect with the GO server. - The
v2/event/refers to the latest version of the API. There are multiple data tables within the database and event tells GO to search for the table that stores events data ( as opposed to , say appeals, or the deployments. See https://goadmin.ifrc.org/api-docs/swagger-ui/ to see a full list of the available API endpoints. - The
countries__inparameter contains key-value pairs separated by ampersand ‘&’. 123%2C84%2C85%2C27 is the parameter value, where %2C represents a URL-encoded comma (,). URL encoding is used to represent characters in a URL that may have special meanings. In this case, %2C is the URL-encoded representation of a comma.
Since the data is coming from a paginated API, we will be utilizing the method used on the Pagination page along with the authorization token.
¶ Using PowerBI/ Excel
The steps needed to obtain data in PowerBI/ Excel are similar to the steps shown on the Projects per Sector page. However, there are some small modifications needed to obtain and transform the data for the respective countries.
- After opening the Power query editor, we have to define a parameters table which will contain the Authorization token along with the country id of the four respective countries.
- Under the Home ribbon, from New Source, select Blank Query. Click on Advance editor, this will open the advance query editing dialog box. In the advance editor window, paste the following code:
let
// Get the parameter values
ParametersTable = Parameters,
Token = Table.SelectRows(ParametersTable, each [Column1] = "Token"){0}[Column2],
country1 = Table.SelectRows(ParametersTable, each [Column1] = "country1_id"){0}[Column2],
country2 = Table.SelectRows(ParametersTable, each [Column1] = "country2_id"){0}[Column2],
country3 = Table.SelectRows(ParametersTable, each [Column1] = "country3_id"){0}[Column2],
country4 = Table.SelectRows(ParametersTable, each [Column1] = "country4_id"){0}[Column2],
BaseUrl = "https://goadmin.ifrc.org/api/v2/event/",
PageSize = 50, // Number of items per page
// Construct the query based on parameter values
countryFilter =
if country1 <> null or country2 <> null or country3 <> null or country4 <> null then
"?countries__in=" & Text.Combine(
List.Select({country1, country2, country3, country4}, each _ <> null),
"%2C"
)
else
"",
GetPage = (page) =>
let
Url = BaseUrl & countryFilter & "&limit=" & Text.From(PageSize) & "&offset=" & Text.From(page * PageSize),
Headers = if Token <> null then [#"Accept-Language"="en", #"Authorization"="Bearer " & Token] else [#"Accept-Language"="en"],
Response = Json.Document(Web.Contents(Url, [Headers=Headers])),
Results = Response[results]
in
Results,
CombineAllPages = List.Generate(
() => [Page = 0],
each List.Count(GetPage([Page])) > 0,
each [Page = [Page] + 1],
each GetPage([Page])
),
#"Converted to Table" = Table.FromList(CombineAllPages, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1")
in
#"Expanded Column1"
- In the code above the Token and country ids derive their values from the Parameters table that was constructed in the previous steps.
- Then we define a countryFilter function which is used to filter out the data for the respective countries. If the condition is true (meaning at least one of the country1, country2, country3 or country4 variable is not null), the THEN block is executed. In the THEN block, List.Select(…) is used to create a list containing non-null country variables. The each _ <> null ensures that only non-null values are introduced in the list.
- The Text.Combine(….,"%2c") is the used to combine the variables in the list into a single string separated by “%2C”, which represents a URL-encoded comma in query strings to separate multiple values of the countries__In parameter.
- After the code has been pasted in the advance editor window, click on done. This creates a new query with a series of steps visible under the APPLIED STEPS on the right side of the query editor window. If the APPLIED STEPS are not visible click on Query Settings under the View ribbon.
- Now we can expand the data by clicking on the icon next to the column name. We can then choose the necessary features/ columns.
- We also need to expand the countries column in order to get the data related to countries ISO3 code, name, region, society name etc.
- Next we filter out the data for the ISO3 codes of the respective countries. After completing these steps we can click on close and apply on the top left of the power query editor window.
¶ Visualization in PowerBI
The steps to visualize the events in countries are similar to what has been shown in Projects per Sector page. We can build the chart by selecting the bar chart from the visualization section. After this selection, a blank place holder will appear in the preview pane. From the data tab select the table which has the data of the events in countries and find the countries.iso3 field. To show the data on the chart you need to drag this field in the X-axis and Y-axis. Power BI will automatically display the frequency/ count of the events and construct the bars for the respective countries. The data labels to show the number of events can be shown by enabling the Data labels from the format option under the visualizations option.
The Bar chart can be customized, for example by changing the names, sizes and colors of the axis, titles and bar colors. The following chart can be obtained after applying the above steps with user's own customizations.
¶ Using Python
In this section we will show how the data can be received from the Event API endpoint and filter the data using the countries__in parameter. To authenticate, we will also add the authentication token when building the API request.
We start the process by first importing necessary libraries that will be used in data processing and visualization.
import requests
from collections import defaultdict
import matplotlib.pyplot as plt requests: Allows you to send HTTP requests to the API endpoint.defaultdict: A dictionary with default values for handling the event counts.matplotlib.pyplot: Provides a MATLAB-like interface for creating static, interactive, and animated visualizations in Python.
def get_data_from_response_results(response_results, response_result_key):
data = {"labels": [], "datasets": [{"data": []}]}
for response_result in response_results:
countries_data = response_result.get(response_result_key, [])
if countries_data:
iso3_codes = [country["iso3"] for country in countries_data]
data["labels"].extend(iso3_codes)
data["datasets"][0]["data"].append(len(iso3_codes))
return dataWe define a helper function named get_data_from_response_results. this function processes the API response, extracts ISO3 codes of countries, and counts the number of countries in each response result. The resulting data structure is suitable for creating charts or visualizations
response_results: This parameter represents a list of dictionaries. Each dictionary in this list contains information about countries retrieved from the API response.response_result_key: This parameter specifies the key in each dictionary where the list of countries' ISO3 code data can be found.
def call_go_api():
go_api_url = "https://goadmin.ifrc.org/api/v2/event/"
event_counts = defaultdict(int)
offset = 0
auth_token = "your authorization token"
headers = {"Authorization": f"Bearer {auth_token}"}
while True:
params = {
"countries__in": "123,84,85,27",
"format": "json",
"limit": 50, # Each page contains 50 entries
"offset": offset,
}
response = requests.get(go_api_url, params=params, headers = headers)
go_api_response = response.json()
parsed_go_api_response = get_data_from_response_results(
go_api_response["results"], "countries"
)
for country_iso3 in parsed_go_api_response["labels"]:
event_counts[country_iso3] += 1
if go_api_response["next"] is not None:
offset += 50
else:
break
return event_counts
The call_go_api function is used to call the API endpoint and receive data from each and every page as the API is paginated. The function enters into a while loop and continues to fetch data until there is no valid URL response. params is a dictionary that contains the query parameters like the country ids , format of the response, offset and limit. The API authorization is done by specifying a headers dictionary which contains the authorization token of the user. These params helps us to filter the data for the countries we need. event_counts is a default dictionary initialized with integer values. This will be used to store the count of events for each country.
The extracted country codes are used to update the event_counts dictionary, incrementing the count for each country code encountered.
def chart():
# Fetch event counts from the Go API
event_counts = call_go_api()
# Sort the event counts dictionary by values in descending order
sorted_event_counts = dict(
sorted(event_counts.items(), key=lambda item: item[1], reverse=True)
)
# Select the top 4 countries and their event counts
top_countries = list(sorted_event_counts.keys())[:4]
top_event_counts = list(sorted_event_counts.values())[:4]
# Define custom colors (hexadecimal codes)
custom_colors = ["#1f77b4", "#ff7f0e", "#2ca02c", "#d62728"]
# Create a bar chart with custom colors and larger axis labels and tick labels
plt.figure(figsize=(9, 7))
bars = plt.bar(top_countries, top_event_counts, color=custom_colors, zorder=2)
# Display data labels (counts) on the bars
for bar, label in zip(bars, top_event_counts):
plt.text(
bar.get_x() + bar.get_width() / 2 - 0.15,
bar.get_height() + 0.1,
str(label),
ha="center",
fontsize=10,
)
plt.xlabel("Countries", fontsize=12) # Set the font size for the X-axis label
plt.ylabel(
"Number of Events", fontsize=12
) # Set the font size for the Y-axis label
plt.title(
"Number of Events by Country", fontsize=12
) # Set the font size for the chart title
# Set the font size for X-axis tick labels
plt.xticks(rotation=0, fontsize=12) # Rotation set to 0 for horizontal labels
# Set the font size for Y-axis tick labels
plt.yticks(fontsize=12)
plt.grid(True, axis="y", linestyle="dotted", alpha=0.5, color="black", zorder=1)
plt.tight_layout()
plt.savefig("C:/Users/arun.gandhi/Downloads/ac.png")
# Call the chart function
chart()We define another function named chart to plot the respective number of events in countries queried. We extract the country ISO3 code and the corresponding event counts and plot them on a bar chart using the matplotlib library. The figure below shows the number of events in India, Indonesia, Nepal and Bangladesh.
