How to import WHO air quality .xlsx database?

user_19233880 0
Viewed 34

I have been trying to use download.file in order to import but it keeps getting errors:

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")

Error in 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", : cannot open destfile 'temp.xlsx', reason 'Invalid argument'

xlsx r curl url download
1 Answers

Using openxlsx::read.xlsx, cut off the string after ".xlsx". Note, that sheet 1 is a readme, use sheet 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

Also works with libcurl actually.

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
user_65740380
Related