将带有逗号作为小数分隔符的xlsx数据导入R

Question

如何从一个扩展名为.xlsx 的文件中导入数据到R中，使得原本以逗号为小数点的数字能够以数字形式呈现？

目前我只知道一种处理Excel文件的包，那就是来自于tidyverse的readxl。

我正在寻找一种解决方案，不需要在任何其他软件中打开和编辑Excel文件（并且可以处理导入的数百列数据）-如果可能的话，我会将所有Excel文件导出为 .csv ，然后使用我所知道的工具导入它们，这些工具可以接受dec=参数。

到目前为止，我找到的最佳解决方案是将数字作为字符导入，然后进行转换：

library(dplyr)
library(stringr)

var1<- c("2,1", "3,2", "4,5")
var2<- c("1,2", "3,33", "5,55")
var3<- c("3,44", "2,2", "8,88")
df<- data.frame(cbind(var1, var2, var3))

df %>%
      mutate_at(vars(contains("var")),
                str_replace,
                pattern = ",",
                replacement = "\\.") %>%
      mutate_at(vars(contains("var")), funs(as.numeric))

- blazej

---

其实我有点惊讶它没能正常工作。（我猜你已经尝试过了？那么当你这样做时会发生什么？）我在我的 Mac 上开始探索它，看起来 Excel 使用整个系统的语言环境来决定如何呈现数字（逗号或点）。虽然我还没有勇气尝试切换我的语言环境来看看会发生什么。 - Aaron left Stack Overflow

看起来在readxl函数中没有dec=参数：Error in read_excel("df.xlsx", skip = 5, dec = ",") : unused argument (dec = ",")。 - blazej

如果没有使用参数，则所有值都将作为字符导入。 - blazej

糟糕。在Excel中它看起来像什么？也就是说，那些是真正的数字还是字符字符串？ - Aaron left Stack Overflow

在我的 Excel（波兰语区域设置）中，它们是数字。 - blazej

Answer 1

我强烈怀疑这些列被读取为字符的原因是其他的，很可能是可怕的“数字存储为文本”。对于普通数字（以数字形式存储），在将逗号作为小数点分隔符切换到单个文件或整个系统设置后，readxl :: read_excel可以正确读入数字。（这是在我的Windows系统上。）即使在该列中添加一个字符或设置col_types =“text”，我仍然会使用句点而不是逗号将数字读入，这进一步证明了readxl正在使用内部存储的数据类型。

我唯一让R读取逗号作为小数的方法是将数据存储为Excel文本而不是数值。（您可以在数字前面加上单引号，如'1,7。）然后我会得到一个带有小绿三角的单元格，提示“数字存储为文本”。在我的探索中，我惊讶地发现Excel会对存储为文本的数字进行计算，因此这不是检查这一问题的有效方法。

- Aaron left Stack Overflow

---

参考 https://dev59.com/QHE85IYBdhLWcg3wgDzd 处理 Excel 中以文本形式存储的数字。 - Aaron left Stack Overflow

Answer 2

将“,”替换为“.”并将该列转换为数字非常容易。例如：

> x <- c('1,00','2,00','3,00')
> df <- data.frame(x)
> df
     x
1 1,00
2 2,00
3 3,00
> df$x <- gsub(',','.',df$x)
> df$x <- as.numeric(df$x)
> df
  x
1 1
2 2
3 3
> class(df$x)
[1] "numeric"
> 

仅使用基础的 R 和 gsub 函数。

- Martin Boros

---

看起来很简单，只有一两列的时候 - 当有数百列需要逐个进行gsub时就会变得有点复杂。 - blazej

嗯，说得有道理。正如您在问题中所提到的，使用循环的缺点是显而易见的。您尝试过 xlsx 包中的 ReadColumns 函数吗？它可能适用于本地化数据，因为它使用内部存储的 Excel 数据类型。 - Martin Boros

从快速浏览readxl代码来看，它似乎也使用了内部存储的Excel数据类型。 - Aaron left Stack Overflow

Answer 3

我刚刚遇到了同样的问题，因为我收到了同事发来的Excel电子表格。在尝试使用readxl导入文件失败后，我将文件转换为csv文件，希望通过使用read_delim并调整区域设置和小数符号选项来解决问题。但无论我使用哪个选项，问题仍然存在。

以下是对我有效的解决方案：我发现在包含缺失值的单元格中使用的字符（在我的情况下是.）会引起问题。我返回Excel文件，在所有包含缺失值的单元格中用空白替换.，同时保持小数的默认选项（,）。之后，使用readxl正确地将所有列作为数字导入。

如果您遇到小数点设置为.的此类问题，请确保在替换所有缺失值实例.之前，在Excel中勾选“匹配整个单元格内容”框。

- GRowInG