read_excel（来自 readxl 包）将 1899-12-30 之前的日期读取为 NA，如何解决此问题？

我有一个包含 130 场足球比赛的数据集，这些比赛的历史可以追溯到 1893 年。

当我从 Excel 导入数据集时，1900 年的日期与 R (RStudio) 中应有的日期相同。但是，1900 年之前的日期以 NA 形式出现。

我该如何解决这个问题，以便所有日期都以正确的格式来自 Excel？

或者，如何用正确的 (18XX-MM-DD) 日期替换 NA？

这是 Excel 中显示的数据：

    Home_Team, Away_Team, SUFC, SWFC, H,  A,  Score, Season, Date

Sheffield United, Sheffield Wednesday, 1, 1, 1, 1, 1–1, 1893/94, 1893-10-16

Sheffield United, Sheffield Wednesday, 1, 0, 1, 0, 1–0, 1894/95, 1895-01-12

Sheffield United, Sheffield Wednesday, 1, 1, 1, 1, 1–1, 1895/96, 1895-12-26

Sheffield United, Sheffield Wednesday, 2, 0, 2, 0, 2–0, 1896/97, 1896-12-26

Sheffield United, Sheffield Wednesday, 1, 1, 1, 1, 1–1, 1897/98, 1897-12-27

Sheffield United, Sheffield Wednesday, 2, 1, 2, 1, 2–1, 1898/99, 1898-12-26

Sheffield United, Sheffield Wednesday, 1, 0, 1, 0, 1–0, 1900/01, 1900-12-15

Sheffield United, Sheffield Wednesday, 3, 0, 3, 0, 3–0, 1901/02, 1902-03-01

Sheffield United, Sheffield Wednesday, 2, 3, 2, 3, 2–3, 1902/03, 1902-09-01

Sheffield United, Sheffield Wednesday, 1, 1, 1, 1, 1–1, 1903/04, 1903-12-12

Sheffield United, Sheffield Wednesday, 4, 2, 4, 2, 4–2, 1904/05, 1905-04-08

Sheffield United, Sheffield Wednesday, 0, 2, 0, 2, 0–2, 1905/06, 1905-10-21

这是我应用到它的 R 代码：

library(tidyverse)
library(readxl)
library(magrittr)
library(dplyr)
library(ggplot2)
library(tidyr)

Sheff_derby_R <- read_excel("sheffield_derby/Sheff_derby_R.xlsx", 
                        col_types = c("text", "text", "text", 
                                      "text", "text", "text", "text", 
                                       "text", 
                                      "date", "text", "numeric", "numeric", 
                                      "numeric", "numeric", "numeric", 
                                      "numeric", "numeric", "numeric", 
                                      "numeric", "numeric", "numeric", 
                                      "numeric", "numeric", "numeric", 
                                      "numeric", "numeric", "numeric", 
                                      "numeric", "numeric", "numeric", 
                                      "numeric", "numeric", "numeric", 
                                      "numeric", "numeric", "numeric", 
                                      "numeric", "numeric", "numeric", 
                                      "numeric", "numeric", "numeric", 
                                      "numeric", "numeric", "numeric", 
                                      "numeric", "numeric", "numeric", 
                                      "numeric", "numeric", "numeric", 
                                      "numeric", "text"))
 View(Sheff_derby_R)

在 R 中，18xx 日期（最后一栏，不是 18xx/xx 季节栏）被 NA 替换。这是头部，前 12 行：

    Home_Team Away_Team SUFC  SWFC  H     A     Score Season Date                
   <chr>     <chr>     <chr> <chr> <chr> <chr> <chr> <chr>  <dttm>              
 1 Sheffiel~ Sheffiel~ 1     1     1     1     1 – 1 1893/~ NA                  
 2 Sheffiel~ Sheffiel~ 1     0     1     0     1 – 0 1894/~ NA                  
 3 Sheffiel~ Sheffiel~ 1     1     1     1     1 – 1 1895/~ NA                  
 4 Sheffiel~ Sheffiel~ 2     0     2     0     2 – 0 1896/~ NA                  
 5 Sheffiel~ Sheffiel~ 1     1     1     1     1 – 1 1897/~ NA                  
 6 Sheffiel~ Sheffiel~ 2     1     2     1     2 – 1 1898/~ NA                  
 7 Sheffiel~ Sheffiel~ 1     0     1     0     1 – 0 1900/~ 1900-12-15 00:00:00 
 8 Sheffiel~ Sheffiel~ 3     0     3     0     3 – 0 1901/~ 1902-03-01 00:00:00 
 9 Sheffiel~ Sheffiel~ 2     3     2     3     2 – 3 1902/~ 1902-09-01 00:00:00 
10 Sheffiel~ Sheffiel~ 1     1     1     1     1 – 1 1903/~ 1903-12-12 00:00:00 
11 Sheffiel~ Sheffiel~ 4     2     4     2     4 – 2 1904/~ 1905-04-08 00:00:00 
12 Sheffiel~ Sheffiel~ 0     2     0     2     0 – 2 1905/~ 1905-10-21 00:00:00 

无论存在什么错误，这里都有一个可能的（临时）解决方法：

首先，读入为"text"看看 R 有点窒息的原因之一。 （我过于简单化了read_excel这里的争论，因为"text"是它的默认值Date在这种情况下。在你的情况下，只需更新"date" to "text"在你的向量中col_types.)

library(readxl)
x <- read_excel("Sheff_derby_SO.xlsx")
x$Date
#  [1] "1893-10-16" "1895-01-12" "1895-12-26" "1896-12-26" "1897-12-27"
#  [6] "1898-12-26" "350"        "791"        "975"        "1442"      
# [11] "1925"      

对于 1900 年及之后的日期，它们将作为整数传递。它们都恰好基于相同的日期来源，因此作为黑客我们可以这样做：

wrong <- !grepl("-", x$Date)
as.Date("1900-01-01") + as.integer(x$Date[wrong]) - 2L
# [1] "1900-12-15" "1902-03-01" "1902-09-01" "1903-12-12" "1905-04-08"

这与 excel 告诉我的相符thinks他们应该是。

注意：我原以为这是一个简单的偏移，但是+ 2L需要使其对齐。这表明可能还有其他事情正在发生，所以please使用您的所有数据（如果这不是全部数据）验证此技巧是否适用于其他值。

在数据集中替换它们就像

sav <- as.Date("1900-01-01") + as.integer(x$Date[wrong]) - 2L
x$Date <- as.Date(x$Date) # 'wrong' ones will be NA
x$Date[wrong] <- sav
x$Date
#  [1] "1893-10-16" "1895-01-12" "1895-12-26" "1896-12-26" "1897-12-27"
#  [6] "1898-12-26" "1900-12-15" "1902-03-01" "1902-09-01" "1903-12-12"
# [11] "1905-04-08"