power bi 创建空表_如何使用R在Power BI中创建地理地图

power bi 创建空表

介绍 (Introduction)

This is the fifth article of a series dedicated to discovering geographic map tools in Power BI.

这是致力于在Power BI中发现地理地图工具的系列文章的第五篇。

In the ToC below the article you can find out references to the previous article and the project’s goal

在文章下面的目录中，您可以找到对上一篇文章的引用以及该项目的目标

Take a pill for a headache and immerse yourself in a world ruled by command lines with obscure syntax; but if you commit yourself to learn, an unbelievable power will raise from your scripts. In other words: welcome to R.

服用药可减轻头痛，让自己沉浸在语法晦涩难懂的命令行所统治的世界中； 但是如果您致力于学习，您的脚本将产生难以置信的力量。 换句话说：欢迎来到R。

什么是R？ (What is R?)

R is the most common open-source language for statistical computing and graphics. R provides a wide variety of statistical and graphical techniques, and it is highly extensible.

R是用于统计计算和图形的最常见的开源语言。 R提供了多种统计和图形技术，并且高度可扩展。

R is shipped with a huge number of packages for spatial data, analysis and plotting. Many kinds of maps (geographic, choropleths, projections, topological, animated) and many drawing options are available using R.

R随附了大量用于空间数据，分析和绘图的软件包。 使用R可以使用多种地图（地理，地形，投影，拓扑，动画）和许多绘图选项。

R for Power BI (R for Power BI)

R scripts are fully supported and integrated with Power BI, offering the way for performing statistical analysis and creating compelling visuals. The integration of R in Power BI, grants access to a rich array of data visualizations that are not present in the standard Power BI visuals set.

R脚本得到Power BI的完全支持并与Power BI集成，从而提供了执行统计分析和创建引人注目的视觉效果的方式。 R在Power BI中的集成允许访问标准Power BI可视化集中不存在的大量数据可视化。

Using R in Power BI you can:

在Power BI中使用R，您可以：

• Import data and create connectors writing scripts
  导入数据并创建编写脚本的连接器
• Cleanse, transform, model, shape, analyze data
  清理，变换，建模，塑造，分析数据
• Create charts, maps, and any kind of compelling visualization
  创建图表，地图和任何形式的引人注目的可视化

In the current article, we will focus on the way to exploit the power of R for creating maps and performing spatial analysis on data.

在当前文章中，我们将重点介绍利用R的功能来创建地图和对数据执行空间分析的方法。

先决条件 (Prerequisites)

To use R from Power BI some preliminary steps must be accomplished:

要使用Power BI中的R，必须完成一些初步步骤：

• CRAN Mirrors choose the mirror and the release that is best fit for you. In my case, I downloaded R for Windows from CRAN Milano, from CRAN Mirrors选择最适合您的镜像和发行版。 就我而言，我从CRAN Milano的this link. Install the R release on your local machine 此链接下载了WindowsR。 在本地计算机上安装R版本
• Power BI Desktop, go to Power BI Desktop ，转到“ File > Options and Settings > R scripting. Power BI should recognize the folder for your local R release. If it doesn’t, click on 文件”>“选项和设置”>“ R脚本” 。 Power BI应该识别出本地R版本的文件夹。 如果没有，请单击“ Other and browse to the folder where R is installed 其他”并浏览到安装R的文件夹。

You could also have more than one R version installed on your machine, like me. Then you got to choose which R you want to use in your Power BI report. Simply click on the dropdown list and select the appropriate version.

您也可以像我一样在计算机上安装多个R版本。 然后，您必须选择要在Power BI报表中使用的R。 只需单击下拉列表，然后选择适当的版本。

You could also switch between versions according to the Power BI report you’re creating and the features you need.

您还可以根据要创建的Power BI报告和所需的功能在版本之间进行切换。

Once done, close and restart Power BI Desktop.

完成后，关闭并重新启动Power BI Desktop。

• Install R Studio. My suggestion is also to install an R IDE. R scripts into Power BI are neither easy to write nor debug. Is it simpler to test scripts outside the environment and then copy and paste into the Power BI Desktop script window? From the link is it possible to download a free version for R Studio. Once installed, check the R version used by the IDE. Open R Studio > Tools > Global Options > General. Under R Sessions it is listed the version of R currently used. As in Power BI, you can switch between different versions, by clicking on Change …
• 安装R Studio 。 我的建议也是安装R IDE。 Power BI中的R脚本既不容易编写也不容易调试。 在环境外部测试脚本，然后将其复制并粘贴到Power BI Desktop脚本窗口中是否更简单？ 从链接可以下载R Studio的免费版本。 安装后，检查IDE使用的R版本。 打开R Studio>工具>全局选项>常规 。 在“ R会话”下 ，列出了当前使用的R版本。 与在Power BI中一样，您可以通过单击Change来在不同版本之间切换。

安装R包 (Install R packages)

Some packages are required to complete the demo. Open R Studio. In the Console Window enter the following command (once a time):

需要一些软件包才能完成演示。 打开R Studio 。 在控制台窗口中，输入以下命令（一次）：

• install.packages(“ggplot2”)
  install.packages（“ ggplot2”）
• install.packages(“ggmap”)
  install.packages（“ ggmap”）
• install.packages(“maps”)
  install.packages（“地图”）
• install.packages(“calibrate”)
  install.packages（“校准”）
• install.packages(“dplyr”)
  install.packages（“ dplyr”）
• 
  
  

There is a bunch of packages in R for spatial data. The above-mentioned are just the most common. Some additional packages will be required further on.

R中有一堆用于空间数据的包。 以上只是最常见的。 以后将需要一些其他软件包。

To close R Studio, either click on File > Quit session … or write q() in the Console window.

要关闭R Studio，请单击“ 文件”>“退出会话 ...”或在“控制台”窗口中输入q（） 。

Google Maps Key (Google Maps Key)

Unfortunately, before getting started, a preliminary step is required. Some of the most common packages rely on Google Maps API, therefore you need to get an API key from Google Developers Console. Google Maps API is not a free service, but there an allowance of 40,000 calls for months that allows testing without any fee. Register your API key at this address. If you want to know more about Google geo-location APIs, please visit the following link.

不幸的是，在开始之前，需要一个初步步骤。 一些最常见的软件包依赖于Google Maps API，因此您需要从Google Developers Console获取API密钥。 Google Maps API并非免费服务，但可以允许数月的40,000次通话，而无需任何费用即可进行测试。 在此地址注册您的API密钥。 如果您想进一步了解Google地理位置API，请访问以下链接 。

Once, you got the API key, continue to the next step.

一旦获得API密钥，请继续下一步。

第一R图 (First R map)

Now it is time for building our first map. Let’s do it in R Studio before. Afterwards, we will repeat the same procedure in Power BI.

现在该构建第一张地图了。 让我们之前在R Studio中进行操作。 之后，我们将在Power BI中重复相同的过程。

Open R Studio. Click on the green + icon on the top left and select R Script.

打开R Studio。 单击左上角的绿色+图标，然后选择R脚本 。

A new window appears where you can write your R script.

出现一个新窗口，您可以在其中编写R脚本。

Download the attached file Cities_R.csv and save it in a folder with a “simple” path; no spaces in the name. Mine is saved on C:\RMaps. Please pay attention that R is case-sensitive, when writing your script.

下载附件文件Cities_R.csv并将其保存在带有“简单”路径的文件夹中； 名称中没有空格。 我的保存在C：\ RMaps中。 在编写脚本时，请注意R区分大小写。

The file contains data for some European cities with population and geographic coordinates (Latitude and Longitude). Import the file in R Studio. In the blank empty window write the following code:

该文件包含具有人口和地理坐标（纬度和经度）的某些欧洲城市的数据。 在R Studio中导入文件。 在空白的空白窗口中，输入以下代码：

#call libraries
library(ggmap)
library(ggplot2)
 
#register your google key frr using with google
register_google(key = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx")
 
#import csv file. First change working directory
setwd("C:\\Rmaps")
#read csv
cities <- read.csv("Cities_R.csv")
 
#draw an empty simple map
m <- get_map(location = 'europe', maptype = "watercolor", source = "google", zoom = 4)
#display map
ggmap(m)

Calling the get_map function, we passed some parameters:

调用get_map函数，我们传递了一些参数：

• Location = the area definition. Could also be a set of coordinates
  位置=区域定义。 也可以是一组坐标
• Source = the mapping service called by the function. Could also be Open Street Map or Stamen
  Source =函数调用的映射服务。 也可以是开放式街道地图或雄蕊
• Maptype = type of rendering for the image
  Maptype =图片的渲染类型

If you’re are interested in knowing more about the get_map function and parameters, please check out the online documentation

如果您有兴趣了解有关get_map函数和参数的更多信息，请查看在线文档。

So, from the above script in the plot area, you got a new empty map.

因此，从上述绘图区中的脚本中，您得到了一个新的空图。

Now add the cities to the map. Add two lines to the script and execute only

现在，将城市添加到地图上。 在脚本中添加两行，仅执行

e <- get_map(location = 'europe', maptype = "toner-lite", source = "google", zoom = 4)
ggmap(e) + geom_point(aes(x = cities$Longitude, y = cities$Latitude), colour ="#0066FF" ,size = 4, data = cities)

geom_point adds a new layer to the map, in this case plotting latitude and longitude for the cities in our dataset

geom_point在地图上添加了一个新图层，在这种情况下，我们在数据集中绘制了城市的经度和纬度

But some other features can be plotted to our map, using few other parameters from geom_point

但是，可以使用geom_point的其他一些参数将一些其他特征绘制到我们的地图

Write and execute the following line:

编写并执行以下行：

ggmap(e) + geom_point(aes(x = cities$Longitude, y = cities$Latitude, colour = factor(Country), size = Population), data = cities)

Now the map is displaying a different size for every point, based on the population value, and it’s also using a country-based color coding with the related legend.

现在，地图根据人口值在每个点上显示不同的大小，并且还使用了带有相关图例的基于国家/地区的颜色编码。

As you can see, R allows full control of every aspect of visualization. It is even possible adding a layer over layer to the map using the geom_xxx set of functions from package ggplot2

如您所见，R允许完全控制可视化的各个方面。 甚至可以使用ggplot2软件包中的geom_xxx函数集在地图上添加一层

So far so good. Now we want to replicate the same map using R in Power BI.

到目前为止，一切都很好。 现在，我们想在Power BI中使用R复制相同的映射。

Open Power BI Desktop. Click Get Data > Text/CSV > Import the file Cities_R.csv from your local folder.

打开Power BI Desktop。 单击获取数据 > 文本/ CSV >从本地文件夹导入文件Cities_R.csv。

In the preview window click Edit and open the Query Editor for your dataset.

在预览窗口中，单击“编辑”，然后打开数据集的“查询编辑器”。

Check out the format for Latitude and Longitude is a decimal number and click Home > Close & Apply

查看“经度和纬度”的格式为十进制数字，然后单击“主页”>“关闭并应用”

Back to report view. Drag and drop an R visual to the canvas. At the bottom of the screen, the R script editor opens. This is the area where to write R scripts to be executed.

返回报告视图。 将R视觉对象拖放到画布上。 在屏幕底部，R脚本编辑器打开。 这是编写要执行的R脚本的区域。

But, first of all, we need some data. Keep the R visual selected and drag all the fields from the dataset Cities_R into the Values bucket.

但是，首先，我们需要一些数据。 保持R处于可视状态，并将所有字段从数据集Cities_R拖到“ 值”存储桶中。

Consequently, the following script is automatically generated into R script editor:

因此，以下脚本将自动生成到R脚本编辑器中：

By default, the data passed to R from Power BI is converted into a tabular format and passed to a standard variable called “dataset”. This is what happens on row 3:

默认情况下，从Power BI传递到R的数据将转换为表格格式，并传递给称为“数据集”的标准变量。 这是在第3行上发生的事情：

Data.frame()

= converts all the data into tabular format.

=将所有数据转换为表格格式。

Dataset <- the values you dropped into the bucket are passed to a default variable called “dataset”

数据集<-您放入存储桶中的值将传递到一个称为“数据集”的默认变量

On row 4 all the duplicate values are removed from the unique(dataset) function

在第4行上，所有重复值都从unique（dataset）函数中删除

All these rows are commented because of the initial # symbol. You got to write your code from the next one, row 7.

所有这些行都以开头的＃符号进行注释。 您必须从下一个第七行开始编写代码。

Let’s try to replicate the same maps as we built in R studio. Type in the following code:

让我们尝试复制与在R Studio中构建的地图相同的地图。 输入以下代码：

#call libraries
library(ggmap)
library(ggplot2)
 
#register your google key
register_google(key = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx")
 
#draw an empty simple map
m <- get_map(location = 'europe', maptype = "watercolor", source = "google", zoom = 4)
#display map
ggmap(m)

Then you have to execute the script. Click on the Play button on the first bar for the R editor

然后，您必须执行脚本。 单击R编辑器第一个栏上的“播放”按钮

You get the following output:

您将获得以下输出：

Same empty Europe’s map as in R Studio.

与R Studio中的欧洲地图相同。

Moving ahead on the example, add cities spots on the map. Delete or comment out the previous lines:

继续进行示例，在地图上添加城市景点。 删除或注释掉前几行：

#draw an empty simple map
#m <- get_map(location = 'europe', maptype = "watercolor", source = "google", zoom = 4)
#display map
#ggmap(m)

And write the following code:

并编写以下代码：

# Paste or type your script code here:
#call libraries
library(ggmap)
library(ggplot2)
 
#register your google key
register_google(key = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx")
 
e <- get_map(location = 'europe', maptype = "toner-lite", source = "google", zoom = 4)
ggmap(e) + geom_point(aes(x = dataset$Longitude, y = dataset$Latitude), colour ="#0066FF" ,size = 4, data = dataset) 

to get the following output:

得到以下输出：

Note the reference to the keyword dataset into the script:

注意脚本中对关键字数据集的引用：

ggmap(e) + geom_point(aes(x = dataset$Longitude, y = dataset$Latitude), colour ="#0066FF" ,size = 4, data = dataset) 

Data is coming from a dataset called “dataset” and the dollar sign before Latitude and Longitude means we are referencing a column from that dataset.

数据来自名为“数据集”的数据集，而“纬度和经度”之前的美元符号表示我们正在引用该数据集中的一列。

The last example is the full map with coordinates, different sized bubble points and legend. Again, either comment or delete the previous code and write down the following lines:

最后一个示例是具有坐标，不同大小的气泡点和图例的完整地图。 再次，注释或删除前面的代码并写下以下几行：

#call libraries
library(ggmap)
library(ggplot2)
 
#register your google key
register_google(key = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx")
 
e <- get_map(location = 'europe', maptype = "toner-lite", source = "google", zoom = 4)
ggmap(e) + geom_point(aes(x = dataset$Longitude, y = dataset$Latitude, colour = factor(Country), size = Population), data = dataset) 

And a fully-featured map is shown to your report.

功能齐全的地图会显示在您的报告中。

子图 (Subplot)

For the second example, we prepare a visual that you cannot get using any other tool. R allows you to combine different elements in the same plot offering endless possibilities, really. I want to show you some of these features.

对于第二个示例，我们准备了一个视觉效果，您无法使用任何其他工具获得视觉效果。 R允许您在同一图中组合不同的元素，确实提供了无限的可能性。 我想向您展示其中一些功能。

Download the sample file Cities_Italy_gender_R.csv. It is a simple list of some Italian cities, with the gender distribution population, males and females. Open R Studio. Some other packages are required to install:

下载示例文件Cities_Italy_gender_R.csv。 它是一些意大利城市的简单列表，其中包括性别分布的人口，男性和女性。 打开R Studio。 需要安装一些其他软件包：

install.packages("TeachingDemos")
install.packages("dplyr")

Once the packages are ready, open a new blank window in R Studio and type in the following code:

包准备好后，在R Studio中打开一个新的空白窗口，然后输入以下代码：

#call libraries
library(maps)
library(ggmap)
library(ggplot2)
library(dplyr)
library(TeachingDemos)
library(reshape2)
library(methods)
require(TeachingDemos)
 
#import csv file. First change working directory
setwd("C:\\Rmaps")
#read csv
sp <- read.csv("Cities_Italy_gender_R.csv")
 
tb <- sp
map('world', region = 'Italy')
points(tb$Longitude, tb$Latitude, col = "#0066FF", pch = 20, cex = 2)
for(i in 1:nrow(tb)){
  subplot(barplot(height = as.numeric(as.character(unlist(tb[i,6:7], use.names = FALSE ))), 
                  cex.names = 0.5, axes = FALSE, col=rainbow(8)),
          x = tb[i,'Longitude'], y = tb[i, 'Latitude'], size = c(0.3,0.3), vadj = 0
  )
}
legend("topright", legend=names(tb[, 6:7]), fill=rainbow(8), bty = "n", cex = 0.8)

The script uses the map function to sketch the border of a geographic area. In no filter is passed, then you get all the world’s outline. In this case, I filtered out only Italy. The next step is to draw points on the map, based on latitude and longitude. The last call is a cycle for drawing a histogram over the points, based on gender distribution. Try to execute step-by-step.

该脚本使用地图功能来绘制地理区域的边界。 在没有通过任何过滤器的情况下，您将获得世界的轮廓。 在这种情况下，我只过滤掉了意大利。 下一步是根据纬度和经度在地图上绘制点。 最后一次调用是一个基于性别分布在点上绘制直方图的循环。 尝试逐步执行。

Run the code until the line with map call. This is the output:

运行代码，直到带有map的行为止。 这是输出：

A simple outline of Italy.

意大利的简单轮廓。

Then add the next row to draw points on the map:

然后添加下一行以在地图上绘制点：

The last step is adding histograms (and a legend):

最后一步是添加直方图（和图例）：

There no other way to get such visualization and R also allows piling up layer over layer making your map richer and detailed.

没有其他方法可以得到这种可视化效果，R还可以一层又一层地堆积起来，使您的地图更加丰富和详细。

Let’s replicate it in Power BI. Open a new file or add a new empty page to the existing one. Import the data Get Data > Text/CSV > <your location> > Cities_Italy_gender_R.csv. In the preview window, click Load.

让我们在Power BI中复制它。 打开一个新文件或将一个新的空白页添加到现有的空白页。 导入数据获取数据>文本/ CSV> <您的位置>> Cities_Italy_gender_R.csv 。 在预览窗口中，点击加载 。

Drag an R visual on the canvas, add the following fields to the bucket: Latitude, Longitude, Males, Females.

将R视觉对象拖动到画布上，将以下字段添加到存储桶：“纬度”，“经度”，“男性”，“女性”。

Code for the R script editor:

R脚本编辑器的代码：

#call libraries
library(maps)
library(ggmap)
library(ggplot2)
library(dplyr)
library(TeachingDemos)
library(reshape2)
library(methods)
require(TeachingDemos)
 
tb <- dataset
map('world', region = 'Italy')
points(tb$Longitude, tb$Latitude, col = "#0066FF", pch = 20, cex = 2)
for(i in 1:nrow(tb)){
  subplot(barplot(height = as.numeric(as.character(unlist(tb[i,3:4], use.names = FALSE ))), 
                  cex.names = 0.5, axes = FALSE, col=rainbow(8)),
          x = tb[i,'Longitude'], y = tb[i, 'Latitude'], size = c(0.3,0.3), vadj = 0
  )
}
legend("topright", legend=names(tb[, 3:4]), fill=rainbow(8), bty = "n", cex = 0.8)

Run the code and you get the following output:

运行代码，您将获得以下输出：

Your second map using R in Power BI is now completed!

在Power BI中使用R的第二张地图现已完成！

路线 (Routes)

For my third example, I also want to show you a complex visualization easy to produce in R with few lines of code: displaying routes on a map. The goal is to display air routes from a starting point to various destinations.

对于我的第三个示例，我还想向您展示一个复杂的可视化效果，它只需几行代码即可在R中轻松生成：在地图上显示路线。 目的是显示从起点到各个目的地的航线。

Another package called “geosphere” is required; open R Studio and install it.

需要另一个称为“地球”的软件包； 打开R Studio并安装它。

install.packages("geosphere")

This time I execute my script directly in Power BI. You already know how to prepare it in R Studio. The dataset to be used is Cities_R. Drag and drop an R visual into the canvas. In the Values bucket, add following fields: City, Country, Latitude, and Longitude.

这次我直接在Power BI中执行脚本。 您已经知道如何在R Studio中进行准备。 要使用的数据集是Cities_R。 将R视觉对象拖放到画布中。 在“值”存储区中，添加以下字段：城市，国家/地区，纬度和经度。

Below is the code to execute:

下面是要执行的代码：

library(maps)
library(dplyr) #for filtering data
library(calibrate) #for drawning lines on a map
library(geosphere) #for drawning lines on a map
 
#draw european countries map
map('world', region = c('Austria', 'Belgium', 'Denmark','France','Germany','Netherlands','Hungary','Ireland','Italy','Poland','Portugal','Slovenia','Spain','Switzerland','UK'))
#filter origin
orig <- filter(dataset, City  == "COPENHAGEN")
dest <- filter(dataset, City != "COPENHAGEN") #exclude origin
#plot the origin
points(orig$Longitude, orig$Latitude, col = "#00cc00", pch = 19, cex = 3)
#plot the destinations
points(dest$Longitude, dest$Latitude, col = "#0066FF", pch = 19, cex = 3)
#plot the directions
for (i in 1:nrow(dest))
{
  a <- c(orig[1,]$Longitude, orig[1,]$Latitude)
  b <- c(dest[i,]$Longitude, dest[i,]$Latitude)
  #calls the function gcIntermediate to get intermediate points (way points) between the two locations with longitude/latitude coordinates.
  intermed <- gcIntermediate(a,b,n=100,addStartEnd=TRUE)
  lines(intermed, col="black", lwd=2)
}

The output map displays routes from Copenhagen to other European destinations with curves based on the earth’s sphere.

输出地图显示了从哥本哈根到其他欧洲目的地的路线，并带有基于地球范围的曲线。

形状图 (Shape map)

Moving on another example of the endless possibilities offered by spatial packages in R. Time to talk about shape maps. A shape map is a visual built to show comparisons of regions on a map by applying different colors to each region. It is based on shapefile, a storage format nowadays universally recognized as the standard for storing geospatial information. A shapefile format spatially describes vector features: points, lines, polygons. It is therefore commonly used to represent geometric locations for data and its attributes.

继续研究R.中的空间包提供的无尽可能性的另一个例子。现在来谈谈形状图。 形状图 是一种可视化对象，用于通过对每个区域应用不同的颜色来显示地图上区域的比较。 它基于shapefile ， shapefile是当今公认的存储地理空间信息的标准存储格式。 shapefile格式在空间上描述矢量特征：点，线，多边形。 因此，通常用于表示数据及其属性的几何位置。

This is how a shape map usually looks like:

形状图通常如下所示：

Using a shape map you can easily build a “choropleth”, a thematic map with different shades of color according to the measure you want to show.

使用形状贴图，您可以轻松地构建“色度”（choropleth），这是一个根据您要显示的度量具有不同色度的主题贴图。

If you want to know more about shape maps, please refer to my previous articles for this series How to create geographic maps in Power BI using built-in shape maps and How to create geographic maps in Power BI using custom shape maps

如果您想了解有关形状图的更多信息，请参阅本系列以前的文章。 如何使用内置形状 图在Power BI中创建地理图，以及如何使用自定义形状图在Power BI中创建地理图

Native shape map visual in Power BI has a big flaw; it is missing a legend. Using shape maps in R you can overcome this issue and furthermore have access to many other features.

Power BI中的本机形状贴图可视化有一个很大的缺陷。 它缺少一个传说。 在R中使用形状贴图可以解决此问题，并且可以使用许多其他功能。

For this example, we need a shapefile and some demo data. I prepared a MyEurope.shp and Europe_shape_demo_data.csv, which contains expenses amount for some European countries. Please download and copy to your R working directory.

对于此示例，我们需要一个shapefile和一些演示数据。 我准备了MyEurope.shp和Europe_shape_demo_data.csv，其中包含一些欧洲国家/地区的费用金额。 请下载并复制到R工作目录。

Some packages are missing. Open R studio and run the following commands:

一些软件包丢失。 打开R studio并运行以下命令：

install.packages("scales")
install.packages("maptools")
install.packages("plotly")
install.packages("sf")
install.packages("rgdal")
install.packages("rgeos")
install.packages("mapproj")

Open Power BI and import the file Europe_shape_demo_csv. As you can see, I includes a “Range” field for creating a legend. Add a new page to the report. Drag and drop an R visual into the canvas. Move all the fields from the dataset into the Values bucket: CountryCode, Range, TotalExpense

打开Power BI并导入文件Europe_shape_demo_csv。 如您所见，我包括一个用于创建图例的“范围”字段。 在报告中添加一个新页面。 将R视觉对象拖放到画布中。 将数据集中的所有字段移到“值”存储桶中：CountryCode，Range，TotalExpense

Below is the code to be executed into Power BI.

以下是要在Power BI中执行的代码。

library(ggplot2)
library(dplyr)
library(scales)
library(RColorBrewer)
library(maptools)
library(plotly)
library(sf)
library(units)
library(rgdal)
library(ggplot2)
library(rgeos)
#import shape file
setwd("C:/RMaps/EU_shape")
eu.shp <- readOGR(dsn = "C:/RMaps/Eu_shape", layer = "MyEurope") %>%
  spTransform(CRS("+init=epsg:4326"))
#import demo data
setwd("C:/RMaps")
eu.dt <- read.csv("Europe_shape_demo_data.csv",header = TRUE, sep = ';')
colnames(eu.dt)[1] <- "CountryCode"
 
#fortify shape file to get into dataframe 
eu.shp.f <- fortify(eu.shp, region = "GMI_CNTRY")
#rename the id column to match the CountryCode from the other dataset
colnames(eu.shp.f)[6] <- "CountryCode"
#merge with CountryCodes
merge.shp <-merge(eu.shp.f, eu.dt, by="CountryCode", all.x=TRUE)
fp <-merge.shp[order(merge.shp$order), ] 
#delete countries that not match
final.plot <- filter(fp, TotalExpense != "NA")
 
#basic plot
ggplot()  + 
  geom_polygon(data = final.plot, aes(x = long, y = lat, group = group, fill =TotalExpense ), color = "black", size = 0.25) + 
  coord_map() + 
  scale_fill_distiller(name="Expenses", palette = "Oranges", breaks = pretty_breaks(n = 5))  + 
  labs(title = "Travel Expenses 2011 - 2017") + 
  theme(axis.line=element_blank(),
        axis.text.x=element_blank(),
        axis.text.y=element_blank(),
        axis.ticks=element_blank(),
        axis.title.x=element_blank(),
        axis.title.y=element_blank(),
        panel.background=element_blank(),
        panel.border=element_blank(),
        panel.grid.major=element_blank(),
        panel.grid.minor=element_blank(),
        plot.background=element_blank(),
        plot.title = element_text(hjust = 0.5)
  )

It worth adding some explanation, as this time the code is quite complex to debug.

值得添加一些解释，因为这一次代码调试起来非常复杂。

This step imports the external shape file in R. The command init = epsg:4326 converts the curvature to standard projection WGS84. Keep in mind that at this stage the eu.shp variable is a spatial object for R

此步骤将外部形状文件导入R。命令init = epsg：4326将曲率转换为标准投影WGS84 。 请记住，在这个阶段eu.shp变量是R的空间对象

#import shape file
setwd("C:/RMaps/EU_shape")
eu.shp <- readOGR(dsn = "C:/RMaps/Eu_shape", layer = "MyEurope") %>%
  spTransform(CRS("+init=epsg:4326"))

This step imports the demo data from CSV. The command colnames changes name for column 1 of the dataset. This is important for matching data to the shapefile

此步骤从CSV导入演示数据。 命令colnames更改数据集第1列的名称。 这对于将数据与shapefile匹配很重要

#import demo data
setwd("C:/RMaps")
eu.dt <- read.csv("Europe_shape_demo_data.csv",header = TRUE, sep = ';')
colnames(eu.dt)[1] <- "CountryCode"

This step changes the type for the shape object from spatial to a data frame (tabular format). The technical term is “fortify”. Changing a spatial object to tabular format is necessary to make it usable from ggplot package. If you query the objects in R Studio, you can check the difference, before and after the fortifying process.

此步骤将形状对象的类型从空间更改为数据框（表格格式）。 技术术语是“强化”。 要使它在ggplot包中可用，必须将空间对象更改为表格格式。 如果在R Studio中查询对象，则可以在强化过程之前和之后检查差异。

#fortify shape file to get into dataframe 
eu.shp.f <- fortify(eu.shp, region = "GMI_CNTRY")

Now, to the fortified object, change the name of column 6 to match the dataset with values. Both the shape map and dataset must have a common key. In our example, the key is the country code: “AUT”, “ITA”, “DEU”, “DNK”, …

现在，对于强化对象，更改第6列的名称以使数据集与值匹配。 形状图和数据集都必须具有公共密钥。 在我们的示例中，键是国家/地区代码：“ AUT”，“ ITA”，“ DEU”，“ DNK”，…

Using the same column name, it is possible to merge the two datasets as it’s done in the next step

使用相同的列名，可以像下一步一样合并两个数据集

#rename the id column to match the CountryCode from the other dataset
colnames(eu.shp.f)[6] <- "CountryCode"

Merge the two datasets: the shape map now fortified and called eu.shp.f, the list of countries with some data to show called eu.dt. The common key to match both datasets is the CountryCode. Then the rows are sorted and the last step removes countries that are not matching the data.

合并两个数据集：现在加强了形状的地图并称为eu.shp.f ，有一些数据要显示的国家/地区列表称为eu.dt。 匹配两个数据集的公共密钥是CountryCode。 然后对行进行排序，最后一步将删除与数据不匹配的国家/地区。

#merge with CountryCodes
merge.shp <-merge(eu.shp.f, eu.dt, by="CountryCode", all.x=TRUE)
fp <-merge.shp[order(merge.shp$order), ] 
#delete countries that not match
final.plot <- filter(fp, TotalExpense != "NA")

Finally, the map can be plotted using ggplot. Pay attention to the way the plot is built. Starting from the basic ggplot command further instructions are added through the + sign.

最后，可以使用ggplot绘制地图。 注意地块的建造方式。 从基本ggplot命令开始，更多的说明通过+符号添加。

geom_polygon draws the polygons for the shape map from latitude and longitude values stored in the .shp file. Then the polygons are filled with coord_map and scale_fill_distiller functions. All the subsequent steps are “make-up”. Add a title, show/remove axis, show/remove background, grid, etc.

geom_polygon从.shp文件中存储的纬度和经度值绘制形状图的多边形。 然后，用coord_map和scale_fill_distiller函数填充多边形。 随后的所有步骤都是“化妆”。 添加标题，显示/删除轴，显示/删除背景，网格等。

Try executing the script without the last rows to check out how you can easily control layout and settings for your map plot.

尝试执行没有最后一行的脚本，以了解如何轻松控制地图的布局和设置。

#basic plot
ggplot()  + 
  geom_polygon(data = final.plot, aes(x = long, y = lat, group = group, fill =TotalExpense ), color = "black", size = 0.25) + 
  coord_map() + 
  scale_fill_distiller(name="Expenses", palette = "Oranges", breaks = pretty_breaks(n = 5))  + 
  labs(title = "Travel Expenses 2011 - 2017") + 
  theme(axis.line=element_blank(),
        axis.text.x=element_blank(),
        axis.text.y=element_blank(),
        axis.ticks=element_blank(),
        axis.title.x=element_blank(),
        axis.title.y=element_blank(),
        panel.background=element_blank(),
        panel.border=element_blank(),
        panel.grid.major=element_blank(),
        panel.grid.minor=element_blank(),
        plot.background=element_blank(),
        plot.title = element_text(hjust = 0.5)
  )

地理编码 (Geocoding)

Geocoding is the process to get coordinates (latitude and longitude) from an address. This is usually performed by calling APIs, for instance, Google Geocoding API or other geocoding services. R allows geocoding through ggmap package; the function geocode calls Google APIs as well. Remember that, in order to use Google APIs you need a key, for free calls tough.

地理编码是从地址获取坐标（纬度和经度）的过程。 这通常是通过调用API（例如Google Geocoding API或其他地理编码服务）来执行的。 R允许通过ggmap软件包进行地理编码； 功能地理编码也会调用Google API。 请记住，要使用Google API，您需要一个密钥，以便免费拨打电话。

I use two cases for showing you the geocoding function: passing directly a static address and from a dataset. The dataset to be used is called “HotelAddress.txt”. The dataset contains the addresses for some hotels in Paris. We want to geocode these locations, starting from addresses. Please download from the bottom of the article to your R folder.

我使用两种情况向您展示地理编码功能：直接传递静态地址并从数据集中传递。 要使用的数据集称为“ HotelAddress.txt”。 数据集包含巴黎一些酒店的地址。 我们想从地址开始对这些位置进行地理编码。 请从文章底部下载到您的R文件夹。

Open Power BI Desktop, add a new page to the report. Get data > Text/CSV > <your folder> > HotelAddress.txt. This time, in the preview window, click Edit.

打开Power BI Desktop，将新页面添加到报告中。 获取数据 > 文本/ CSV > <您的文件夹>> HotelAddress.txt。 这次，在预览窗口中，单击“ 编辑” 。

We need two further steps before working with this dataset:

在使用此数据集之前，我们还需要两个步骤：

1. Home > Use First Row as Headers 主页>使用第一行作为标题

1. Add Column > Custom Column. Into the dialog window type following data: 添加列>自定义列 。 在对话框窗口中输入以下数据：
   
   
   
   

   New column name = FullAddress
   Custom column formula = [Address] & “,” & [City] & “, ” & [Country]

   新列名称 = FullAddress
   自定义列公式= [地址]＆“，”＆[城市]＆“，”＆[国家/地区]

Click Ok. Close Query Editor Home > Close & Apply.

单击确定 。 关闭查询编辑器主页>关闭并应用 。

Now we can test the geocoding functions. Drag and drop an R visual into the canvas. Add field FullAddress from the dataset “HotelAddress” and type in the following R code:

现在，我们可以测试地址解析功能了。 将R视觉对象拖放到画布中。 从数据集“ HotelAddress”添加字段FullAddress，然后输入以下R代码：

#register your google key
register_google(key = "xxxxxxxxxxxxxxxxxxxxxxx")
 
wh <- geocode("Ghelamco Arena, Ottergemsesteenweg Zuid, Gent, Belgium", output = "latlon", source ="google")
qmap(location = c(lon = wh$lon, lat = wh$lat), zoom = 15, maptype = "satellite")

I choose satellite as a map type. This is the output.

我选择卫星作为地图类型。 这是输出。

A google static image centered on the location I passed to the script.

以我传递给脚本的位置为中心的google静态图片。

Now, let’s try to geocode addresses coming from a dataset. Add another R visual to the canvas. Fields to be passed are: Hotel, FullAddress, City

现在，让我们尝试对来自数据集的地址进行地址解析。 将另一个R视觉效果添加到画布。 要传递的字段是：酒店，FullAddress，城市

R code to copy and paste

R代码复制并粘贴

library(ggplot2)
library(ggmap)
library(dplyr)
 
#register your google key
register_google(key = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx")
 
addr <- filter(dataset, City  == "Paris")
gc <- geocode(as.character(addr$FullAddress), output = "latlon", source ="google")
m <- get_map(location = 'Paris', maptype = "roadmap", source = "google", zoom = 11)
ggmap(m) + geom_point(aes(x = lon, y = lat), colour ="#ff3300", size = 5 ,data = gc)  + geom_text(x = gc$lon, y = gc$lat, label = paste(addr$Hotel, gc$lat, gc$lon, sep = "-"), size = 5, hjust = 0, vjust = 0.5, angle = 10)

Output:

输出：

The map of Paris, with the geocoded hotels.

巴黎地图，以及经过地理编码的酒店。

Please pay attention to the following line:

请注意以下行：

+ geom_text(x = gc$lon, y = gc$lat, label = paste(addr$Hotel, gc$lat, gc$lon, sep = "-"), size = 5, hjust = 0, vjust = 0.5, angle = 10)

I’m using paste function to retrieve geocoded latitude and longitude along with the hotel name

我正在使用粘贴功能来检索经过地理编码的纬度和经度以及酒店名称

paste(addr$Hotel, gc$lat, gc$lon, sep = "-")

Mapdist和路由 (Mapdist and routing)

A couple of other nice features available from Google APIs. a) Distance between two points; b) routing.

Google API提供了其他一些不错的功能。 a）两点之间的距离； b）路由。

Add a new page to your Power BI report. Drag and drop the R visual into the canvas. Actually, for the next two demos, we are not using data from datasets, but they are necessary in order to enable the R script editor. Therefore, drop a field into the Values bucket, for instance, City from, Cities_R.

在Power BI报告中添加一个新页面。 将R视觉对象拖放到画布中。 实际上，对于接下来的两个演示，我们不使用数据集中的数据，但是启用R脚本编辑器是必需的。 因此，将一个字段（例如，Cities_R中的City）放入“值”存储桶中。

Copy and paste code into R script editor

复制代码并将其粘贴到R脚本编辑器中

library(ggmap)
library(ggplot2)
library(dplyr)
library(grid)
library(gridExtra)
 
#register your google key
register_google(key = "AIzaSyB6b6TiVNTm1nPigwKDE5cHavmHAXIt0Vg")
 
from <- "Copenhagen"
to <- "Amsterdam"
 
dca <- mapdist(from, to, mode = "driving")
dca.df <- data.frame(dca)
grid.table(dca.df)

Please note, I declared origin and destination directly into the script. Of course, you can pass the data as usual from Power BI datasets. It’s up to you to test it. I invoked the simplest function to get distance value between two points. There are more complex API to be used: distance matrix for instance, or from a single origin to many destinations.

请注意，我直接在脚本中声明了起点和终点。 当然，您可以像往常一样从Power BI数据集中传递数据。 测试取决于您。 我调用了最简单的函数来获取两点之间的距离值。 有更复杂的API可以使用：例如距离矩阵，或者从单个起点到许多目的地。

The Google API returns a table in this case with a list of values: distance in km, in miles, time, mode …

在这种情况下，Google API返回一个带有值列表的表：以公里为单位的距离，以英里为单位的时间，时间，模式…

So, it makes no sense to render a map. I used the grid.table() function to show data in tabular format

因此，渲染地图没有任何意义。 我使用grid.table（）函数以表格格式显示数据

The next topic is routing. It gives you the suggested route between two points. It works exactly like Google Maps on a web browser. We want to know the distance and routing from Copenhagen to Amsterdam and show it on a map. This is the R code:

下一个主题是路由。 它为您提供了两点之间的建议路线。 它的工作原理与网络浏览器上的Google Maps完全一样。 我们想知道哥本哈根到阿姆斯特丹的距离和路线，并将其显示在地图上。 这是R代码：

library(ggmap)
library(ggplot2)
library(dplyr)
library(grid)
library(gridExtra)
 
#register your google key
register_google(key = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx")
 
from <- "Copenhagen"
to <- "Amsterdam"
 
#routing
route_df <- route(from, to, structure = 'route', mode = 'driving')
m <- get_map(location = 'Europe', maptype = "roadmap", source = "google", zoom = 5)
ggmap(m) +
  geom_path(aes(x = lon, y = lat),  colour = 'blue', size = 1.5,data = route_df, lineend = 'round')

for this output:

对于此输出：

Function route() from ggmap() is invoked, and the returned list of points is plotted over the maps using geom_path() function.

调用ggmap（）中的route（）函数，并使用geom_path（）函数在地图上绘制返回的点列表。

互动性。 缺失的戒指 (Interactivity. The missing ring )

So far, I showed you the brightness of R, a language that allows building custom maps with full control on every detail; from data to the visual’s layout. Now it’s time to face the dark side of the relationship between Power BI and R: what is not supported or not allowed. You’ve probably concluded by yourself trying to zoom in or out the maps, panning or browsing. You simply cannot. Interactivity is not supported in standard R visual for Power BI. Therefore maps are rendered as static images on the canvas.

到目前为止，我向您展示了R的亮度，R是一种语言，它允许构建对每个细节都具有完全控制权的自定义地图。 从数据到视觉的布局。 现在是时候面对Power BI与R之间关系的阴暗面了：不支持或不允许的东西。 您可能已经得出结论，尝试放大或缩小地图，平移或浏览。 你根本不能。 Power BI的标准R visual不支持交互性。 因此，地图在画布上呈现为静态图像。

Nevertheless, a common Power BI pattern is guaranteed. Any R visual interacts with other visuals in the way we are used to. For a simple demonstration, open your Power BI report on the first example: the map built with ggmap. Add a slicer to the canvas and put the Country field in the bucket. The R map reacts to the country selected in the slicer, by filtering out only cities for that country. In the example below, for instance, I selected only Italy in the slicer.

但是，可以保证使用通用的Power BI模式。 任何R视觉都以我们习惯的方式与其他视觉交互。 为了简单演示，请在第一个示例上打开Power BI报告：使用ggmap构建的地图。 在画布上添加切片器，然后将“国家/地区”字段放入存储桶中。 R地图通过仅滤除该国家的城市，对切片器中选择的国家做出React。 例如，在下面的示例中，我在切片器中仅选择了意大利。

These a workaround, but it is quite complex and requires programming skills. If you paid attention, I mentioned “standard R visual” i.e. the one you find out the box in the visuals pane

这些解决方法很复杂，但是需要编程技能。 如果您注意的话，我提到了“标准R视觉”，即您在视觉窗格中找到的框

But, interactivity is supported for custom visuals built on R packages. Please refer to this article for details. For custom visual the rendering format is HTML, meaning that you can add interactivity, tooltips, selections, etc. Here it is reported the list for supported R packages in Power BI. Scrolling down the list, you can find out a leaflet, the most common package dynamic mapping in R.

但是，基于R包构建的自定义视觉效果支持交互性。 有关详细信息，请参阅本文 。 对于自定义视觉，呈现格式为HTML，这意味着您可以添加交互性，工具提示，选择等。 此处报告了Power BI中受支持的R包的列表。 向下滚动列表，您可以找到一个传单 ，这是R中最常见的软件包动态映射。

If you try to create a map with leaflet using Power BI R visual this is the message you get:

如果您尝试使用Power BI R visual用传单创建地图，则会收到以下消息：

Whereas the same code is R Studio is working fine. Unfortunately, the output from the package is not suitable for Power BI.

而相同的代码是R Studio可以正常工作。 不幸的是，该软件包的输出不适用于Power BI。

Of course, this is not a solution within everyone’s reach. Maybe it could be the topic for the next article …

当然，这不是每个人都能解决的解决方案。 也许这可能是下一篇文章的主题...

If you want to dig into details on how to build a custom map visual for Power BI, using leaflet, please refer to Microsoft’s GitHub repository.

如果您想使用传单深入了解如何为Power BI构建自定义地图视觉的细节，请参考Microsoft的GitHub存储库 。

结论 (Conclusion)

In this article, I showed you the integration between Power BI and R for mapping. I introduced you to some of the most common packages in R for geographic representations. But there are still more than this you can do with R spatial features. I hope that I intrigued you. All the R scripts are available in one single file at the end of the article, both for R Studio and Power BI. Feel free to download and reuse.

在本文中，我向您展示了Power BI和R之间用于映射的集成。 我向您介绍了R中最常用的地理表示软件包。 但是，使用R空间特征还可以做更多的事情。 我希望我对你感兴趣。 本文结尾处的所有R脚本都可以在一个文件中找到，适用于R Studio和Power BI。 随时下载和重复使用。

Take your time, read the official documentation, test the packages and get the most out of your data.

花些时间，阅读官方文档，测试软件包并充分利用数据。

资料下载 (Downloads)

EU_shape

demo_datasets

R_scripts

EU_shape

demo_datasets

R_脚本

目录 (Table of contents)

How to create geographic maps using Power BI – Filled and bubble maps

How to create geographic maps in Power BI using built-in shape maps

How to create geographic maps in Power BI using custom shape maps

How to create geographic maps in Power BI using ArcGIS

How to create geographic maps in Power BI using R

Customized images with Synoptic Panel

MapBox

Geocoding

如何使用Power BI创建地理地图-填充地图和气泡地图

如何使用内置形状图在Power BI中创建地理图

如何使用自定义形状图在Power BI中创建地理图

如何使用ArcGIS在Power BI中创建地理地图

如何使用R在Power BI中创建地理地图

使用Synoptic Panel定制图像

地图框

地理编码

翻译自: https://www.sqlshack.com/how-to-create-geographic-maps-in-power-bi-using-r/

power bi 创建空表