Visualising Macrobond data as maps using Python

You already know how to visualise Macrobond’s wealth of macroeconomic and financial aggregate data using a variety of line, bar and other charts.

But did you know you can also use it to create geographic heat map like this one below in just a few steps?

Our World in Data, COVID-19, Vaccinations, People Fully Vaccinated Per Hundred

‍Step 1: Find the same variable for several countries. Use the search functionality in the Macrobond API for a specific concept. Find this under “Time Series information” in the application or by accessing the metadata in Python. In this example we use a time series of Covid vaccinations and access that region key.

import win32com.client

import pandas as pd

c = win32com.client.Dispatch("Macrobond.Connection")

d = c.Database

Getting the region key/concept

s = d.FetchOneSeries("owidvacci_ar_pfvph")

key = s.Metadata.GetFirstValue("RegionKey") #Getting the region key of a series

regionkey = d.FetchOneEntity(key)

regionkeydesc =regionkey.Metadata.GetFirstValue("Description") #Getting the full name of the region key

regionkeydesc

Step 2: After finding the region key, use it to perform your search.  

Searching for all series with the region key/concept

query = d.CreateSearchQuery()

query.SetEntityTypeFilter("TimeSeries")

query.AddAttributeValueFilter("RegionKey", key)

result = d.Search(query).Entities

Step 3: After receiving the search result, get the relevant metadata for constructing the map. In this example, we used the last value of the series, the region name and the ISO 3 code for the country.

Getting the last value and the region of the series via the metadata

val = pd.DataFrame(columns=['value'])

region = pd.DataFrame(columns=['region'])

 for s in result:

region=region.append({'region': s.Metadata.GetFirstValue("Region")},ignore_index=True)

val= val.append({'value':s.Metadata.GetFirstValue("LastValue")}, ignore_index=True)

Translating the region code to ISO 3 and accessing the full name of the country

li=region.values.tolist()

iso = pd.DataFrame(columns=['iso'])

country = pd.DataFrame(columns=['country'])

entities = d.FetchEntities(li)

for e in entities:

iso= iso.append({'iso':e.Metadata.GetFirstValue("IsoCountryCode3")}, ignore_index=True)

country=country.append({'country': e.Metadata.GetFirstValue("Description")},ignore_index=True)

Step 4: Merge this data into the same data frame.

Merging the ISO3, Country name, Region and Values into one

df = pd.merge(iso, country, left_index=True, right_index=True)

df=pd.merge(df, region, left_index=True, right_index=True)

df=pd.merge(df, val, left_index=True, right_index=True)

df['iso'] = df['iso'].str.upper() # Making sure that the data is inupper cases

Step 5: Once you have the data, use plotly to createthe map.

import plotly.graph_objects as go 

fig = go.Figure(data=go.Choropleth(

   locations = df['iso'],

   z = df['value'],

   text = df['country'],

   colorscale = 'viridis',

   autocolorscale=False,

   reversescale=True,

   marker_line_color='darkgray',

   marker_line_width=0.5,

   colorbar_title = '',

))

Adding a dynamic title and sourcing

fig.update_layout(

   title_text=regionkeydesc,

   geo=dict(

       showframe=False,

       showcoastlines=False,

      projection_type='equirectangular'

   ),

   annotations = [dict(

       x=0.55,

       y=0.00001,

       xref='paper',

       yref='paper',

       text='Source: <ahref="https://www.macrobond.com/">Macrobond</a>',

       showarrow = False

   )]

Here’s the full script:

import plotly.express as px

import win32com.client

import pandas as pd

c = win32com.client.Dispatch("Macrobond.Connection")

d = c.Database

Getting the region key

s = d.FetchOneSeries("owidvacci_ar_pfvph")

key = s.Metadata.GetFirstValue("RegionKey") #Getting theregion key of a series

regionkey = d.FetchOneEntity(key)

regionkeydesc = regionkey.Metadata.GetFirstValue("Description")#Getting the full name of the concept 

regionkeydesc

Searching for all series with the region key/concept

query = d.CreateSearchQuery()

query.SetEntityTypeFilter("TimeSeries")

query.AddAttributeValueFilter("RegionKey", key)

result = d.Search(query).Entities

Getting the last value and the region of the series via the metadata

val = pd.DataFrame(columns=['value'])

region = pd.DataFrame(columns=['region'])

for s in result:

   region= region.append({'region':s.Metadata.GetFirstValue("Region")}, ignore_index=True)

   val= val.append({'value':s.Metadata.GetFirstValue("LastValue")}, ignore_index=True)

Translating the region code to ISO 3 and accessing the full name of thecountry

li=region.values.tolist()

iso = pd.DataFrame(columns=['iso'])

country = pd.DataFrame(columns=['country'])

entities = d.FetchEntities(li) 

for e in entities:

   iso= iso.append({'iso':e.Metadata.GetFirstValue("IsoCountryCode3")}, ignore_index=True)

   country=country.append({'country': e.Metadata.GetFirstValue("Description")},ignore_index=True)

Merging the ISO3, Country name, Region and Values into one

df = pd.merge(iso, country, left_index=True, right_index=True)

df=pd.merge(df, region, left_index=True, right_index=True)

df=pd.merge(df, val, left_index=True, right_index=True)

df['iso'] = df['iso'].str.upper()

import plotly.graph_objects as go

fig = go.Figure(data=go.Choropleth(

   locations = df['iso'],

   z = df['value'],

   text = df['country'],

   colorscale = 'viridis',

   autocolorscale=False,

   reversescale=True,

   marker_line_color='darkgray',

   marker_line_width=0.5,

   colorbar_title = '',

))

Adding a dynamic title and sourcing

fig.update_layout(

   title_text=regionkeydesc,

   geo=dict(

       showframe=False,

       showcoastlines=False,

      projection_type='equirectangular'

   ),

   annotations = [dict(

       x=0.55,

       y=0.00001,

       xref='paper',

       yref='paper',

       text='Source: <ahref="https://www.macrobond.com/">Macrobond</a>',

       showarrow = False

   )]

)