R Maps in Microsoft Power BI: Getting Started

Are you using maps in Power BI but desire more control over the final visual? The following tutorial introduces the potential for using R to move beyond the default maps in Power BI. Starting with basics, we will step through a series of simple maps that plot airfield locations in the Great Lakes region of the United States. Although there are many mapping packages for R, this tutorial explores the aptly named “maps” library.

The dataset is a modified version of the FAA’s Airport Facilities Data found here. It carries a broad definition of airport — we will plot much more than major commercial airports. I am also using Power BI to filter out heliports and other minor locations that the FAA includes. Until I saw and mapped this dataset, I had no idea that so many minor airfields existed!

Before getting started in Power BI, make sure that both the maps and mapproj packages are installed in your R environment:

install.packages("maps")
install.packages("mapproj")

After adding the R visual in Power BI Desktop, we will add four fields from the dataset: Latitude, Longitude, State, and Elevation. State corresponds to states in the United States and consists of full state names, i.e. Michigan. Elevation is simply a value that we will use to show size variation later on — similar to a “bubble map” in a standard Power BI map. Any measure would work for this purpose.Power BI R Visual Map Fields

After loading the maps package, we will simply call the map() function and click Run:

library(maps)
map()
World.PNG

 

That was easy! We have not added any data from the dataset yet, but we have a map of the world.

Since we will be plotting data from the United States, let’s specify map(“state”) to narrow down the geography to show the United States with borders for each state. You can read through the package documentation in more detail, but it is possible to use other geographies such as map(“italy”), etc. depending on your needs.

library(maps)
map("state")
US

Notice how the border is black. Since this is Power BI, we may want to have our map fit in with other charts. Let’s change the state outline to Power BI’s ubiquitous default green by adding a col (color) property. It turns out that I am breaking my own rule about using the Power BI default colors, but it is for the sake of branding. For this tutorial, everyone should know at a glance that this is a Power BI R map.

library(maps)
map("state", col="#01B8AA")US Green.PNG

Green state borders on the white background are probably too intense. Let’s change the background to make it darker by using the bg (background) property. You can match the R visual background with whatever background color you use for your own report. I used hex colors, but you could also use common color names, i.e.  “green” instead of “#01B8AA”. Common color names may not always match up well with your expectations though, while hex colors provide a greater level of control.

library(maps)
map("state", col="#01B8AA", bg="#374649")US Green Dark.PNG

In addition to coloring the borders, it is also possible to fill in the states using the fill property. Note how the state borders darken automatically as well when using fill.

library(maps)
map("state", col="#01B8AA", bg="#374649", fill=TRUE)Fill.PNG

The default projection for the maps package is rectangular, meaning that latitude and longitude are balanced based on the center point of the image. Depending on your map’s location, there could be significant distortion. To account for this, you can use the mapproj package to select from a list of approximately 40 alternative map projections. Common ones include Mercator and Albers, but I have also provided samples of some less common ones such as AzEqualArea and Guyou.

library(maps)
library(mapproj)
map("state", col="#01B8AA", bg="#374649", fill=TRUE, proj="mercator")Mercator
library(maps)
library(mapproj)
map("state", col="#01B8AA", bg="#374649", fill=TRUE, proj="azequalarea")AZEqualArea.PNG
library(maps)
library(mapproj)
map("state", col="#01B8AA", bg="#374649", fill=TRUE, proj="guyou")
Guyou
library(maps)
library(mapproj)
map("state", col="#01B8AA", bg="#374649", fill=TRUE, proj="albers", param=c(39,45))Albers

 

We will continue to use Albers, but since we will be adding points for the airfields, let’s remove the fill to make those dots stand out. Also, note that with a few projections, an additional param property exists. This parameter is included for Albers, which relies on latitude values to define a scale. In this case, we will use parameter values of 39 and 45 degrees, which is close enough (for this tutorial) when working with the Great Lakes.

library(maps)
library(mapproj)
map("state", col="#01B8AA", bg="#374649", proj="albers", param=c(39,45))Albers No Fill

We have come this far and have not even considered our dataset! Now, it is time to plot the airfield points that will appear as simple dots on the map. To do this, we’ll add a points() function after map(). Since we are using an alternative projection, we also need to use the mapproject() function to translate the longitude and latitude to the correct Albers position. Finally, if you are unfamiliar with R, note how we are referencing specific features named Latitude and Longitude in the “dataset” data frame using “$”.

library(maps)
library(mapproj)
map("state", col="#01B8AA", bg="#374649", proj="albers", param=c(39,45))
points(mapproject(dataset$Longitude, dataset$Latitude))Point.PNG

That looks…terrible. This dataset contains private airfields and not just large commercial ones, so there are a lot of points, and they all overlap. Let’s change the point color to Power BI’s default yellow to make the dots stand out more (again, this is for branding, not necessarily for beauty). In this case, bg acts as the fill for the points, but there is still a black border.

library(maps)
library(mapproj)
map("state", col="#01B8AA", bg="#374649", proj="albers", param=c(39,45))
points(mapproject(dataset$Longitude, dataset$Latitude), bg="#F2C80F", pch=21, cex=.5)

Yellow

Specify col to change the border color. We will remove the black border using col=NA.

library(maps)
library(mapproj)
map("state", col="#01B8AA", bg="#374649", proj="albers", param=c(39,45))
points(mapproject(dataset$Longitude, dataset$Latitude), col=NA, bg="#F2C80F", pch=21, cex=.5)

Point Yellow

That still looks bad. Are the Great Lakes covered in ragweed? Since there are so many airports being plotted in a small space, we can give more definition to them by reducing the dot size. Do this by changing the cex value from 0.5 to 0.1.

library(maps)
library(mapproj)
map("state", col="#01B8AA", bg="#374649", proj="albers", param=c(39,45))
points(mapproject(dataset$Longitude, dataset$Latitude), col=NA, bg="#F2C80F", pch=21, cex=.1)

CEX reduced

That looks better, but we are only plotting airfields in the Great Lakes region. Is there a way to “zoom in” and focus only on the relevant states with data? There is! To accomplish this, we need to add a reference to dataset$State to map(). Since our dataset only includes data for five out of fifty states, the resulting map  will only show the relevant five states.

library(maps)
library(mapproj)
map("state", dataset$State, col="#01B8AA", bg="#374649", proj="albers", param=c(39,45))
points(mapproject(dataset$Longitude, dataset$Latitude), col=NA, bg="#F2C80F", pch=21, cex=.1)

Great Lakes

At this stage, all of the points have the same radius. Recall that we have included Elevation as a measure. What if we wanted to increase the size of the points for higher elevations? To do this dynamically, we can get the maximum elevation value and then use that in a formula for cex.

library(maps)
library(mapproj)
elevationMax <- max(dataset$Elevation)
map("state", dataset$State, col="#01B8AA", bg="#374649", proj="albers", param=c(39,45))
points(mapproject(dataset$Longitude, dataset$Latitude), col=NA, bg="#F2C80F", pch=21, cex=dataset$Elevation / elevationMax * 0.5)

Size by Measure.PNG

This map still looks like a field of ragweed, but at least it has definition. We have encoded our measure value using the size of the points, and the map now has some character to it.

In a similar vein, the point color can also be based on the data. When plotting the points, simply replace the static bg color with a feature in your data frame. For example, we can substitute the state value instead of yellow so that points in each state have a separate color.

library(maps)
library(mapproj)
elevationMax <- max(dataset$Elevation)
map("state", dataset$State, col="#01B8AA", bg="#374649", proj="albers", param=c(39,45))
points(mapproject(dataset$Longitude, dataset$Latitude), col=NA, bg=dataset$State, pch=21, cex=dataset$Elevation / elevationMax * 0.8)

Colors by State

What if we wanted to include multiple maps in the same R visual, such as the original United States one as well as the more focused Great Lakes one? In R, this can be done using mfrow to specify a multi-panel plot. In this case, we will specify par (parameter), and then we will remove margins using mar = 0 and use mfrow with one row with two columns: par(mfrow=c(1,2), mar=c(0,0,0,0)). Then, to get the overview/inset effect with multiple maps, simply include code for the United States version first, followed by the more focused Great Lakes code.

library(maps)
library(mapproj)
par(mfrow=c(1,2), mar=c(0,0,0,0))
elevationMax <- max(dataset$Elevation)
map("state", col="#01B8AA", bg="#374649", proj="albers", param=c(39,45))
points(mapproject(dataset$Longitude, dataset$Latitude), col=NA, bg="#F2C80F", pch=21, cex=dataset$Elevation / elevationMax * 0.5)
map("state", dataset$State, col="#01B8AA", bg="#374649", proj="albers", param=c(39,45))
points(mapproject(dataset$Longitude, dataset$Latitude), col=NA, bg="#F2C80F", pch=21, cex=dataset$Elevation / elevationMax * 0.5)

Focused.PNG

It is also possible to show a version with counties. How? Simply change “state” to “county”.

library(maps)
library(mapproj)
par(mfrow=c(1,2), mar=c(0,0,0,0))
elevationMax <- max(dataset$Elevation)
map("state", col="#01B8AA", bg="#374649", proj="albers", param=c(39,45))
points(mapproject(dataset$Longitude, dataset$Latitude), col=NA, bg="#F2C80F", pch=21, cex=dataset$Elevation / elevationMax * 0.5)
map("county", dataset$State, col="#01B8AA", bg="#374649", proj="albers", param=c(39,45))
points(mapproject(dataset$Longitude, dataset$Latitude), col=NA, bg="#F2C80F", pch=21, cex=dataset$Elevation / elevationMax * 0.8)

County.PNG

Now, let’s allow for some interactivity by adding other Power BI visuals. I’ve added two bar charts that allow me to filter and drilldown by state/county/airfield as well as filter by chart name. Chart name is the term used in this FAA dataset for a geographic grid that covers multiple states, which provides a good example of showing how different combinations of states appear as the selection changes. I believe that chart name has its origin in paper charts used for navigation. While flying east over the Great Lakes, for example, you would have switched from your Chicago chart to your Detroit chart, which was a different piece of paper. The name of the chart is taken from the most significant city on the chart. In any case, selecting from either of these two bar charts will filter the R maps accordingly.

Michigan.PNG

Chicago.PNG

Kalamazoo.PNG

 

While this tutorial introduced the maps package and looked at basic samples where we plotted points, there are many more capabilities and packages for mapping in R. We have not even touched upon how to draw a line between two cities, let alone more advanced scenarios! The goal in this post was to explore a basic map in R with Power BI, but hopefully even with these small steps, it inspires you to go beyond the default Bing maps and take advantage of the richness of R.

 

If you want to review the sample report, the PBIX file for Power BI Desktop as well as the modified dataset are available on GitHub. Also, if you are interested in deploying Power BI in your organization or want to learn more about using R with Power BI, get in touch with my employer, BlueGranite.

 

Next tutorial: R Maps in Power BI: Small Multiples

 

 

 

 

Advertisements

13 thoughts on “R Maps in Microsoft Power BI: Getting Started

  1. This is very cool… I have two questions.

    1. What version of R are you running? I am running 3.2.3 and i get an error running the “maps”. # Type ‘?world’ or ‘news(package=”maps”)’. See README_v3. #

    2. How is this technically running in the browser? Do you need an R server running? or locally at the PC running it in the browser?

    Best regards
    Rasmus

    Liked by 1 person

  2. Hmm, when this is run:

    map(“state”, col=”#01B8AA”, bg=”#374649″, proj=”albers”, param=c(39,45))
    points(mapproject(dataset$Longitude, dataset$Latitude))

    The entire USA is displayed. The param is not limiting the plot to the Great Lakes. Any ideas?

    Like

      • David,

        When I run:

        map(“state”, dataset$State, col=”#01B8AA”, bg=”#374649″, proj=”albers”, param=c(39,45))

        an error is returned. Here is a snip:

        Error in grep(regexp, nam, ignore.case = TRUE, perl = TRUE) :
        invalid regular expression ‘(^AK)|(^AK)|(^AK)|(^AK)|(^AK)|(^AK)

        Since others are running your code successfully, I can only guess that my data is not formatted correctly. Here is how I modified your Excel data file:

        datasetRaw <- data.frame(read.csv("./data/FAAAirport.csv", stringsAsFactors = FALSE))
        dataset <- select(datasetRaw, Latitude, Longitude, State, Elevation)
        dataset <- unique(dataset)

        Here is what the data looks like:

        Latitude Longitude State Elevation
        51.87802453 -176.6459193 AK 19
        56.93869083 -154.1825556 AK 44
        60.91380972 -161.4933292 AK 23

        str(dataset)
        'data.frame': 19523 obs. of 4 variables:
        $ Latitude : chr "51.87802453" "56.93869083" "60.91380972" "60.90786472" …
        $ Longitude: chr "-176.6459193" "-154.1825556" "-161.4933292" "-161.4350772" …
        $ State : chr "AK" "AK" "AK" "AK" …
        $ Elevation: int 19 44 23 18 39 0 129 21 7 63 …

        What have I done wrong?

        Thanks for listening.

        Cliff

        Liked by 1 person

      • Cliff,
        It looks like perhaps you are trying to do this outside of Power BI Desktop. I had done some additional filtering and renaming in Power BI itself, and one of those was to rename “State” to “StateAbbreviation” and also “StateName” to “State” (I should have documented that, but of course in an R script, that would have been self-documenting!). The grep error occurs when trying to run on the two letter abbreviation instead of the full state name. I’ve modified the script to create a standalone R version and placed it on GitHub. In order to create less confusion compared to the original dataset, I did not rename anything in the standalone version. Hopefully it is clearer.

        In summary, I modified your script to:
        1) Add dplyr
        2) Add StateName and Type to your “select”
        3) Added a “filter” based on State and Type in R that takes the place of what was being done in Power BI
        4) Changed map(“county”, dataset$State[…] to map(“county”, dataset$StateName[…]

        Let me know if you run into anything else.

        Thanks,
        David

        Like

      • Much appreciated. I should have been able to figure that out but I guess the lack of time made me lazy – sorry. Thanks for the feedback and the post.

        Like

  3. This works quite well. Thank you! Would be nice if R visuals could update in web versions of PowerBI dashboards… but I suppose that’s down the pike somewhere.

    Like

    • Agreed. Technically the R visual is still a preview feature for Power BI Desktop, so it would not surprise me if we see R enabled in the PBI web version around the same time it goes lives in PBI Desktop. We will see though–hopefully soon.

      Like

  4. Are the R Mapping visuals limited to the number of points mapped, as PowerBI map visuals are? I think it is 10,000 points currently.

    Like

    • The R Visual in Power BI only hosts the image as plotted in R, so you do not encounter record limits in the same way that you do with a native PBI visual. Other than potentially running into resource limitations, I am not aware of specific limits within R as a whole or with the various visualization packages I have used. Once you reach tens of thousands of densely packed points though, you may want to consider better ways of representing the data. For the map example, I plotted a dataset with >50,000 records alongside a 10% sample. I *know* that all 50k points are there, but my eyes cannot differentiate well between 50k and 5k due to the density and overlap.

      Liked by 1 person

      • Fantastic, this really removes some limitations I was experiencing with Power BI mapping. Your blog is very good and I do appreciate your quick response to my question. I have downloaded R and am following your excellent example. I have been putting learning R off to a distant future, but with this and the new SQL Server 2016 tie-in with R, I guess I am going to have to get started.
        Thanks again, you have really opened my eyes.

        Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s