read_excel 不会修剪空格

Question

我正在使用 readxl 包来加载 Excel 文件。默认情况下，它应该去除空白，但它没有这样做。

该文件可以直接从下面的链接下载，也可以通过附录B所在的网站下载

http://www2.nationalgrid.com/UK/Industry-information/Future-of-Energy/Electricity-Ten-Year-Statement/

http://www2.nationalgrid.com/WorkArea/DownloadAsset.aspx?id=8589937799

require(readxl);require(tidyverse)
test <- read_excel("ETYS 2016 Appendix B.xlsx", skip = 1, sheet = 22, trim_ws = TRUE)
print(test$`MVAr Generation`)
test$`MVAr Generation` %>% str_count(patter = "\\s")

test$`MVAr Generation` %>% table #all are numeric
test$`MVAr Generation` %>% class #however the class is characer

test$`MVAr Generation` %>% str_count(patter = "\\s") %>%
sum(na.rm = T) #It should be 0 however it is 2 

此问题导致分析出现问题，从数字列是字符的示例中可以看出。如有帮助，我们将不胜感激

Answer 1

library(readxl)

readxl::excel_sheets('ETYS 2016 Appendix B.xlsx')[22]
test <- read_excel("ETYS 2016 Appendix B.xlsx", skip = 1, sheet = 22, 
                   trim_ws = FALSE)
test$`MVAr Generation` <- as.numeric(gsub('^\\s', "", test$`MVAr Generation`))

该错误可能是由于字符编码造成的。当我强制对列进行数字解释时，出现此错误:

Expecting numeric in D9 / R9C4: got 'Â 225'

您可以通过用 gsub 替换前导空格来手动避免这种情况。