Unleashing the Power of Power BI and Geopandas: Building Dynamic Geospatial Dashboards

https://www.freecodecamp.org/news/python-in-powerbi/

Geospatial data visualisation is a powerful tool for understanding patterns and trends in location-based information. In this blog post, we will explore how to leverage the strengths of two popular data visualisation tools, Power BI and Geopandas, to create dynamic and interactive dashboards for geospatial data.

Power BI is a business intelligence tool developed by Microsoft that allows users to easily create interactive data visualisations and dashboards. One of its strengths is its ability to connect to a wide variety of data sources, including Excel, SQL Server, and cloud-based data storage.

Geopandas is a Python library that makes working with geospatial data easy. It is built on top of other popular Python libraries such as Pandas, NumPy, and Shapely, and allows for easy manipulation and visualisation of geospatial data.

To start, we will need to install both Power BI and Geopandas on our computer. Once they are installed, we can use Power BI to connect to our geospatial data source and use Geopandas to manipulate and visualize the data.

One of the great features of Geopandas is that it can read a variety of file types including shapefiles, GeoJSON, and more, so it is easy to get started with the data that you have.

Once we have our data loaded into Power BI, we can use the built-in visualisation tools to create a variety of charts and maps. Power BI has a wide range of map visualisations, such as choropleth maps, heat maps, and bubble maps, that can be used to display geospatial data.

Additionally, with the use of Power Query, it is possible to process and clean the data with Python, using the power of the Geopandas library, allowing for more complex data manipulation before the visualisation.

With the combination of Power BI and Geopandas, we can create dynamic and interactive dashboards that allow us to easily explore and understand our geospatial data. Whether we are working with data on population density, crime rates, or natural disasters, these tools make it easy to uncover insights and make data-driven decisions.

#Gathering dataset

Lead Scoring Dataset

I wanted to create geospatial dashboard using Power BI and Python, and I was in need of a suitable dataset for my analysis. After some research, I came across the “Lead Scoring” dataset on Kaggle, which seemed like an excellent fit for my project.

Retrieving the dataset from Kaggle was a simple process. I started by creating a free account on the platform, which only took a few minutes. Once I was logged in, I searched for the “Lead Scoring” dataset and found it easily. I then clicked on the “Download” button, and the dataset was downloaded to my local machine in a CSV format.

Let’s start and create our dynamic dashboards for analysis

PYTHON (GEOPANDAS)

Creating a geospatial dashboard with Python and the library geopandas is a powerful way to visualize and analyze geographical data. In this blog post, we will go through the steps of setting up a basic geospatial dashboard using Python and geopandas.

First, we will need to install the necessary libraries. The two main libraries we will be using are geopandas and folium. Geopandas is a library for working with geospatial data in Python, and folium is a library for creating interactive maps. To install these libraries, you can use the following command:

pip install geopandas folium

Folium and geopandas are both powerful libraries in Python that are commonly used for creating interactive visualizations of geospatial data.

Folium is a library for creating interactive maps in Python. It allows you to create maps that can be zoomed, panned, and interacted with in a variety of ways. It also provides a simple and intuitive interface for adding markers, lines, and other features to the map. This makes it a great tool for creating visualizations that allow users to explore and understand geographical data in an interactive way.

Together, folium and geopandas provide a powerful set of tools for creating interactive visualizations of geospatial data in Python. Geopandas can be used to read and manipulate geospatial data, while folium can be used to create interactive maps and visualizations. This allows developers to easily create dynamic and interactive dashboards and visualizations that allow users to explore and understand geographical data in new and exciting ways.

Next, we will need to acquire some geospatial data. This can be in the form of shapefiles, GeoJSON, or other geospatial data formats. For this example, we will use the dataset given above.

Once we have our data, we can use geopandas to read in the shapefile and create a GeoDataFrame. The following code shows an example of how to do this:

import geopandas as gpd

# read in the shapefile
states = gpd.read_file("path/to/shapefile.shp")

you can use the following code to create a basic world map:

import folium

world_map = folium.Map(location=[0, 0], zoom_start=2)
world_map

You can customize your map by adding markers, polygons, or other features. You can also change the starting location and zoom level.

It’s also possible to plot data on the map using folium. For example, you can use the dataframe column of ‘City’ and ‘TotalVisits’ and plot it on the map.

# groupby city column and sum totalvisit
df_city_visit = df.groupby(['City'])['TotalVisits'].sum()

# create empty map
world_map = folium.Map(location=[0, 0], zoom_start=2)

# iterate through each city and add marker
for city, total_visit in df_city_visit.items():
    folium.Marker(location=[city_latitude_dict[city], city_longitude_dict[city]],
                  popup=city + ' - ' + str(total_visit),
                  icon=folium.Icon(color='red', icon='info-sign')
                 ).add_to(world_map)

world_map

Note: You need to have city latitude and longitude for this example.

As, You can see we don’t have latitude and longitude for our Cities in our dataset .

These coordinates can be obtained from various sources such as Google Maps, OpenStreetMap, or a separate dataset that contains the coordinates. Once you have obtained the coordinates, you can use a library like folium or plotly to create the map.

An alternative way to create a world map without coordinates is to use a choropleth map. A choropleth map is a type of map that is used to display data that varies across geographic regions. It is a good choice if you have data that can be divided into pre-defined regions such as states, provinces, or countries. You can use the ‘plotly’ library to create a choropleth map.

So, we can use the plotly library to create a choropleth map. A choropleth map is a map that uses different colors to represent different values in a dataset. To create a choropleth map, you need to have the latitude and longitude coordinates of each location in your dataset.

If you don’t have the latitude and longitude for each city, you can use a library like geopy to geocode the city names and get the corresponding coordinates.

Here is an example of how to create a choropleth map using the plotly library and the geopy library to geocode the cities:

import plotly.express as px
from geopy.geocoders import Nominatim

geolocator = Nominatim(user_agent="geoapiExercises")

# Creating a new column in the dataframe with latitude and longitude
df['coordinates'] = df['city'].apply(geolocator.geocode).apply(lambda x: (x.latitude, x.longitude))
df[['latitude', 'longitude']] = df['coordinates'].apply(pd.Series)

fig = px.scatter_mapbox(df, lat="latitude", lon="longitude", color="column_name", size='column_name',
                  color_continuous_scale=px.colors.sequential.Plasma, size_max=15, zoom=3)
fig.show()

You may also use folium library to create the map.

import folium

# Creating a new column in the dataframe with latitude and longitude
df['coordinates'] = df['city'].apply(geolocator.geocode).apply(lambda x: (x.latitude, x.longitude))
df[['latitude', 'longitude']] = df['coordinates'].apply(pd.Series)

m = folium.Map(location=[20, 0], tiles="Mapbox Bright", zoom_start=2)

for i in range(0,len(df)):
    folium.Circle(
        location=[df.iloc[i]['latitude'], df.iloc[i]['longitude']],
        color='crimson', fill='crimson',
        tooltip = df.iloc[i]['city'],
        radius=df.iloc[i]['column_name']*1000
    ).add_to(m)

m

Note : Please note that the above examples are just a sample and you need to replace the column name and value with your actual data.

In addition to creating the map, it’s also possible to add more interactive features to your geospatial dashboard using folium. For example, you can use folium’s Marker and Popup classes to add markers to the map that display information when clicked.

Here’s an example of how you might add markers to the map for the state capitals:

# add markers for state capitals
for idx, row in states.iterrows():
    folium.Marker([row['capital_lat'], row['capital_lon']], popup=row['capital_name']).add_to(m)

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —

#INSTALLING POWER BI

Step 1: Download Power BI Desktop

The first step in installing Power BI on your laptop is to download the Power BI Desktop application. You can download Power BI Desktop from the official Microsoft website. The download link can be found here: https://www.microsoft.com/en-us/download/details.aspx?id=58494

Step 2: Install Power BI Desktop

Once the download is complete, double-click on the installation file to start the installation process. Follow the on-screen instructions to complete the installation.

Step 3: Launch Power BI Desktop

After the installation is complete, you can launch Power BI Desktop by clicking on the Power BI Desktop icon on your desktop or by searching for it in the Start menu.

Step 4: Sign in with your Microsoft account

Upon launching Power BI Desktop, you will be prompted to sign in with your Microsoft account. If you don’t have a Microsoft account, you can create one for free. Once you have signed in, you’ll have access to the full range of features and capabilities in Power BI Desktop.

Step 5: Connect to your data

Now that Power BI Desktop is installed and you have signed in, you can start connecting to your data. You can connect to a wide variety of data sources, including Excel, CSV, and SQL Server.

POWER BI

Creating a world map in Power BI is a simple and straightforward process that can be accomplished with just a few clicks. In this blog, we’ll walk through the process of creating a world map in Power BI and show you how to perform some basic analysis on your data.

First, you’ll need to open Power BI and create a new report. You can do this by clicking on the “New Report” button in the top-right corner of the screen.

Once you’ve created a new report, you’ll be presented with a blank canvas. To add a world map to your report, you’ll need to add a new map visual to your report. You can do this by clicking on the “Map” button in the “Visualizations” pane on the right side of the screen.

Next, you’ll need to add your data to the map. You can do this by clicking on the “Edit Queries” button in the “Home” tab of the ribbon. From here, you can import your data from a variety of sources, including Excel, CSV, and SQL Server.

Once your data is loaded, you can start to analyze it. One of the most powerful features of Power BI is the ability to create custom calculations and perform advanced analysis on your data. You can do this by using the “Fields” pane on the right side of the screen.

For example, you can create a custom calculation to calculate the total population for each country on your map. You can do this by creating a new column in your data, and then using the “SUM” function to calculate the total population for each country.

Next, you can use the map visual to create a choropleth map that shows how population varies by country. This can be done by dragging the column you created in the previous step to the “Color” field in the “Visualizations” pane.

https://simplemaps.com/docs/powerbi-install

Finally, you can add additional visualizations to your report such as a Pie chart and a Bar chart, to showcase the analysis you have done on the data.

Original

There are several types of maps available in Power BI, including:

1. Basic Map: This is the simplest type of map in Power BI. It allows you to display your data on a map as points, or markers, that represent individual locations. You can customize the color and size of the markers to represent different data points.
2. Filled Map: A filled map is similar to a basic map, but instead of displaying markers, it fills in the areas of the map based on data values. For example, you can use a filled map to show the population density of a region or the sales volume of a product in different areas.
3. Bing Map: This type of map is built on top of the Bing Maps API and provides a more detailed and interactive map than the basic and filled maps. Bing Maps allow you to add additional layers of data, such as traffic and weather information, to your map.
4. Shape Map: A Shape Map is a map that displays data using custom polygon shapes. This type of map is useful for displaying data that is specific to a particular geographic region, such as a state or a country.

In the above map dashboard , I have used various Dax expressions and Slicers to ease the load of the analysis and to get powerful insights.

Here are few important Dax function and expressions that can be used for the analysis of the data:

• SUM: The SUM function is used to calculate the sum of a column of data. For example, to calculate the total sales for a given product, you would use the following DAX expression:

SUM(Sales[SalesAmount])

• AVERAGE: The AVERAGE function is used to calculate the average value of a column of data. For example, to calculate the average sales price of a product, you would use the following DAX expression:

AVERAGE(Sales[SalesPrice])

• COUNT: The COUNT function is used to count the number of items in a column of data. For example, to count the number of sales transactions, you would use the following DAX expression:

COUNT(Sales[TransactionID])

• MIN: The MIN function is used to calculate the minimum value in a column of data. For example, to find the lowest sales price for a product, you would use the following DAX expression:

MIN(Sales[SalesPrice])

• MAX: The MAX function is used to calculate the maximum value in a column of data. For example, to find the highest sales price for a product, you would use the following DAX expression:

MAX(Sales[SalesPrice])

• IF: The IF function is used to create conditional statements in DAX. For example, to create a new column that displays “High” or “Low” based on a sales threshold, you would use the following DAX expression:

IF([SalesAmount] > 1000, "High", "Low")

• CONCATENATE: The CONCATENATE function is used to join together two or more text strings. For example, to create a new column that displays the full name of a customer, you would use the following DAX expression:

CONCATENATE(Customers[FirstName], " ", Customers[LastName])

Conclusion

Power BI and Geopandas are two powerful tools that can be used together to build dynamic geospatial dashboards. By leveraging the data visualization capabilities of Power BI and the geospatial analysis capabilities of Geopandas, you can create interactive, informative dashboards that can help you better understand and analyze your data.

With Power BI, you can easily create stunning visualizations, perform advanced data analysis using DAX functions, and interact with your data using slicers. And with Geopandas, you can perform complex geospatial analysis, such as creating maps, calculating distances, and performing spatial joins.

Whether you’re looking to analyze sales data by region, track the spread of a disease, or explore the patterns in your data, Power BI and Geopandas provide you with the tools you need to build dynamic geospatial dashboards that can help you make informed decisions.

So, if you’re ready to unleash the power of Power BI and Geopandas, start building your own dynamic geospatial dashboards today!

‘’Data are just summaries of thousands of stories”

Thank you!