下のようなデータのExcelファイルがあるとする.
A |
B |
C |
1.23 |
1.23 |
2018-01-01 |
これは,
A1:C1
がヘッダー,つまり col_names
A2
1.23 で「標準」
B2
が 1.23 で「数値」
C2
が 2018-01-01 で「日付」
となっている.
readxl::read_excel()
で読むと,
d <- readxl::read_excel("Book1.xlsx")
d
となるのが正しいが,最近のExcel (Office 365 Pro Plus version 1809 とか)で作ったファイルの場合,
d <- readxl::read_excel("~/Desktop/Book1.xlsx")
d
となって,「数値」にしたときに「日付」として認識されてしまう,ということがあった.
col_type
を指定すればいいのだが,
d <- readxl::read_excel("~/Desktop/Book1.xlsx", col_types = c("numeric", "numeric", "date"))
d
となって,警告が果てしなくでてしまう.
これ,原因は,
- 「数値」指定したときになぜか組み込み型の「数値」にならずに,カスタム書式,つまり「ユーザー定義」になる
- 「数値」書式の負の値の表示形式がデフォルトでは 赤色 になっている
readxl
で読んだときに カスタム書式で負の値の表示形式が赤色の数値の場合,日付に誤判定される
というもののようだ.
もう少し詳しい話
Excel ファイルの中身はXMLファイルで,Windows 的には拡張子を.zip に変えて展開すれば見える.
Book1
│ [Content_Types].xml
│
├─docProps
│ app.xml
│ core.xml
│
├─xl
│ │ sharedStrings.xml
│ │ styles.xml
│ │ workbook.xml
│ │
│ ├─theme
│ │ theme1.xml
│ │
│ ├─worksheets
│ │ sheet1.xml
│ │
│ └─_rels
│ workbook.xml.rels
│
└─_rels
.rels
データは,xl > worksheets > sheet1.xml
とかに入っていて,
<sheetData>
<row r="1" spans="1:3" x14acdyDescent="0.25">
<c r="A1" t="s">
<v>0</v>
</c>
<c r="B1" t="s">
<v>1</v>
</c>
<c r="C1" t="s">
<v>2</v>
</c>
</row>
<row r="2" spans="1:3" x14acdyDescent="0.25">
<c r="A2">
<v>1.23</v>
</c>
<c r="B2" s="2">
<v>1.23</v>
</c>
<c r="C2" s="1">
<v>43101</v>
</c>
</row>
</sheetData>
というようになっている.
Cell の情報は <c></c>
にあって,c
のアトリビュートのうち,
r
はセル番号
t
はセルタイプ(デフォルトn
で数値)
s
はStyle Index で書式などの情報
となっている.
B2
を見ると
となっている.
Style Index はxl > styles.xml
の cellXfs のインデックス(0始まり)を表していて,該当部分は,
<cellXfs count="3">
<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0">
<alignment vertical="center"/>
</xf>
<xf numFmtId="180" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1">
<alignment vertical="center"/>
</xf>
<xf numFmtId="181" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1">
<alignment vertical="center"/>
</xf>
</cellXfs>
となっている.
cellXfs
以下の xf
はさらに numFmtId
を参照しており,実際の書式はここに書かれている.
つまり, cellXfs
の 2番目の書式情報は numFmtId="181"
に書かれている.
これは,同じく styles.xml
内に書かれていて,
<numFmts count="2">
<numFmt numFmtId="180" formatCode="yyyy\-mm\-dd;@"/>
<numFmt numFmtId="181" formatCode="0.00_);[Red]\(0.00\)"/>
</numFmts>
となっていて,B2
の1.23 の書式は,"0.00_);[Red]\(0.00\)"
だと分かった.
この numFmtId
の値は,< 164
が組み込み164 <=
がカスタムとなっていて,組み込みの場合は styles.xml
には書かれずに省略される.
以前のバージョンのExcel だと,「数値」に指定すると組み込み になっていた.
readxl はセルのデータ型を判定するときに,
- 組み込みの中の日付書式かどうか
- 日付以外の組み込み書式かどうか
- カスタム書式は日付書式かどうか
という順で判定する.
この 3番目のカスタム書式は日付書式かどうか,の判定が微妙で,
https://github.com/tidyverse/readxl/blob/master/src/ColSpec.h#L144-L172
inline bool isDateFormat(std::string x) {
for (size_t i = 0; i < x.size(); ++i) {
switch (x[i]) {
case 'd':
case 'D':
case 'm':
case 'M':
case 'y':
case 'Y':
case 'h':
case 'H':
case 's':
case 'S':
return true;
default:
break;
}
}
return false;
}
"d" という文字が入っていたら日付と判定してしまうようで,[Red]
の d
を見て日付と判断してしまっているらしい.
以前のバージョンだと,組み込み型の数値なので,負の数の表記を赤文字にしていても,この処理は走らなくて正常に数値と判定される.
しかし,最近のバージョンだとなぜかカスタム書式になってしまっているので,誤判定が起こった,ということらしい.
カスタム書式,自分でも使うときは日付に対してしか使わないので,確かに問題にならなそうだし,数値が問答無用でカスタム書式になるというのがおかしい.
この辺の issue とかが関係してそう
github.com