如何导入世界卫生组织空气质量.xlsx数据库？

Question

为了导入，我一直试图使用download.file，但是它不断地收到错误：

utils::download.file(url = "https://cdn.who.int/media/docs/default-source/air-pollution-documents/air-quality-and-health/who_aap_2021_v5.xlsx?sfvrsn=d912652d_3", destfile = "temp.xlsx", mode = "libcurl")

utils：：download.file中的错误(url= "https://cdn.who.int/media/docs/default-source/air-pollution-documents/air-quality-and-health/who_aap_2021_v5.xlsx?sfvrsn=d912652d_3"，：无法打开destfile‘tem.xlsx’)，原因是‘无效参数’

Answer 1

使用openxlsx::read.xlsx，在".xlsx"之后切断字符串。注意，第1页是自述文件，使用第2页。

d <- openxlsx::read.xlsx('https://cdn.who.int/media/docs/default-source/air-pollution-documents/air-quality-and-health/who_aap_2021_v5.xlsx', sheet=2)

head(d)
# WHO.Region ISO3 WHO.Country.Name City.or.Locality Measurement.Year PM2.5.(μg/m3)
# 1 Eastern Mediterranean Region  AFG      Afghanistan            Kabul             2019        119.77
# 2              European Region  ALB          Albania           Durres             2015            NA
# 3              European Region  ALB          Albania           Durres             2016         14.32
# 4              European Region  ALB          Albania          Elbasan             2015            NA
# 5              European Region  ALB          Albania          Elbasan             2016            NA
# 6              European Region  ALB          Albania          Elbasan             2017            NA
# PM10.(μg/m3) NO2.(μg/m3) PM25.temporal.coverage.(%) PM10.temporal.coverage.(%)
# 1           NA          NA                         18                         NA
# 2        17.65       26.63                         NA                         NA
# 3        24.56       24.78                         NA                         NA
# 4           NA       23.96                         NA                         NA
# 5           NA       26.26                         NA                         NA
# 6           NA       24.70                         NA                         NA
# NO2.temporal.coverage.(%)                                                               Reference
# 1                        NA U.S. Department of State, United States Environmental Protection Agency
# 2                  83.96119                        European Environment Agency (downloaded in 2021)
# 3                  87.93260                        European Environment Agency (downloaded in 2021)
# 4                  97.85388                        European Environment Agency (downloaded in 2021)
# 5                  96.04964                        European Environment Agency (downloaded in 2021)
# 6                  89.29224                        European Environment Agency (downloaded in 2021)
# Number.and.type.of.monitoring.stations Version.of.the.database Status
# 1                                   <NA>                    2022     NA
# 2                                   <NA>                    2022     NA
# 3                                   <NA>                    2022     NA
# 4                                   <NA>                    2022     NA
# 5                                   <NA>                    2022     NA
# 6                                   <NA>                    2022     NA
# 6                                   <NA>                    2022     NA

实际上，它也适用于libcurl。

temp <- tempfile(fileext='.xlsx')
utils::download.file(url="https://cdn.who.int/media/docs/default-source/air-pollution-documents/air-quality-and-health/who_aap_2021_v5.xlsx", 
                     destfile=temp, method='libcurl')
d <- openxlsx::read.xlsx(temp, 2)
head(d)
## output see above