如何导入谁的空气质量.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'temp.xlsx'，原因'无效参数'

Answer 1

使用OpenXLSX :: Read.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