セルと列の型
デフォルトでは readxl::read_excel() が列の型を推測しますが、 col_types 引数で明示的に指定することも可能です。この col_types 引数は思ったよりも柔軟で、実際の型と "skip" や "guess" を混ぜることができ、必要な長さに合わせて一つの型をリサイクルすることができます。
以下は、このような場合の例です。
read_excel("yo.xlsx")
read_excel("yo.xlsx", col_types = "numeric")
read_excel("yo.xlsx", col_types = c("date", "skip", "guess", "numeric"))
型を推測
tidyverse の他のパッケージを使っている場合、フラットファイルからデータを読み込むreadr を知っているのではないでしょうか。readxl と同様に、readr も列の型の推測を提供しますが、readr と readxl はその仕組みが大きく異なります。
- readr はデータに基づいて列の型を推測します。
- readxl は Excel のセルタイプに基づいて列の種類を推測します。
Excelスプレッドシートの各セルは、それぞれ独自の型を持っています。どう考えても、それらは
empty < boolean < numeric < text
ただし、datetimeはnumericの中でも特殊なものです。特定の型のセルは、常に上位の型のセルとして表現することができ、場合によっては下位の型のセルとして表現することもできる。推測するとき、 read_excel() は任意の列で見たことのあるセル型の「最大値」を記録しています。一旦 guess_max 行を訪れるか、データを使い果たすと、これがその列の推測される型となります。最後の手段である “text”列の型に向かう強い流れがあります。
以下は、readxlに同梱されている deaths.xlsx を使った列の推測の例です。
read_excel(readxl_example("deaths.xlsx"), range = cell_rows(5:15))
#> # A tibble: 10 × 6
#> Name Profession Age `Has kids` `Date of birth` `Date of death`
#> <chr> <chr> <dbl> <lgl> <dttm> <dttm>
#> 1 David Bow… musician 69 TRUE 1947-01-08 00:00:00 2016-01-10 00:00:00
#> 2 Carrie Fi… actor 60 TRUE 1956-10-21 00:00:00 2016-12-27 00:00:00
#> 3 Chuck Ber… musician 90 TRUE 1926-10-18 00:00:00 2017-03-18 00:00:00
#> 4 Bill Paxt… actor 61 TRUE 1955-05-17 00:00:00 2017-02-25 00:00:00
#> # … with 6 more rows
Excel の型, R の型, col_types
ここでは、Excel のセル/列の型を R の型に変換する方法と、 col_types で明示的に型を強制する方法について説明します。
| anything |
non-existent |
"skip" |
| empty |
logical, but all NA |
指定できません |
| boolean |
logical |
"logical" |
| numeric |
numeric |
"numeric" |
| datetime |
POSIXct |
"date" |
| text |
character |
"text" |
| anything |
list |
"list" |
最初の2行の奇妙なケースについて、いくつか説明します。
- もし、ある列がデータ矩形内に存在するが、関連する変数を出力したくない場合は、列の種類を
"skip" に指定してください。内部的には、これらのセルはその位置を知るために訪問されるかもしれませんが、それらは読み込まれず、そのデータは決して読み込まれません。
- ある列を含むが、
NAで埋め尽くされることを要求することはできません。そのような列は、すべてのセルが空の場合、自然に発生する可能性がありますし、列をスキップすることもできます（前のポイントを参照）。
スキップして推測する例。
read_excel(
readxl_example("deaths.xlsx"),
range = cell_rows(5:15),
col_types = c("guess", "skip", "guess", "skip", "skip", "skip")
)
#> # A tibble: 10 × 2
#> Name Age
#> <chr> <dbl>
#> 1 David Bowie 69
#> 2 Carrie Fisher 60
#> 3 Chuck Berry 90
#> 4 Bill Paxton 61
#> # … with 6 more rows
最後の行にある "list" 列型の詳細です。
- これは出力にリスト列を作成し、その各要素は長さ1のアトミックベクターとなります。これらのベクトルの型は、上で説明したロジックを使って決定されます。これは、本当にばらばらの型のデータが列に並べられる場合に便利です。
ここでは、Excel に同梱されている clippy.xlsx というシートを使って "list" 型のカラムのデモを行います。このシートの2列目には、1つの型だけでは格納するのが難しい Clippy の情報が格納されています。
(clippy <-
read_excel(readxl_example("clippy.xlsx"), col_types = c("text", "list")))
#> # A tibble: 4 × 2
#> name value
#> <chr> <list>
#> 1 Name <chr [1]>
#> 2 Species <chr [1]>
#> 3 Approx date of death <dttm [1]>
#> 4 Weight in grams <dbl [1]>
tibble::deframe(clippy)
#> $Name
#> [1] "Clippy"
#>
#> $Species
#> [1] "paperclip"
#>
#> $`Approx date of death`
#> [1] "2007-01-01 UTC"
#>
#> $`Weight in grams`
#> [1] 0.9
sapply(clippy$value, class)
#> [[1]]
#> [1] "character"
#>
#> [[2]]
#> [1] "character"
#>
#> [[3]]
#> [1] "POSIXct" "POSIXt"
#>
#> [[4]]
#> [1] "numeric"
最後に、Excel にはタイムゾーンの概念がないため、すべての日付はUTCタイムゾーンを持つものとしてインポートされます。
列の推測がうまくいかないとき
ある列が、例えば数値や日時としてインポートされると期待することはよくあることです。そして、代わりに文字としてインポートされたときに悲しくなります。主な原因は2つあります。
互換性のないタイプの欠落または不良データを埋め込んだことによる汚染。 例：数値列に「??」と入力されたデータがない場合。
- 修正:
read_excel() の na 引数を使用して、欠損データに対して可能なすべての形式を記述するようにしました。これにより、そのようなセルが型の推測に影響を与えることを防ぎ、適切な型の NA としてインポートされるようになるはずです。
データ矩形の先頭または末尾の非データ行による汚染。 例：データテーブルが始まる前に、数行の説明文を含むシートがあります。
- 修正：ターゲット矩形を指定。
skip と n_max を使用して、それぞれスキップする最小行数と読み込む最大データ行数を指定します。また、より強力な range 引数を使用すると、セルの矩形をさまざまな方法で指定することができます。詳しくは read_excel() のヘルプや vignette("sheet-geometry") のサンプルを参照してください。
deaths.xlsx シートはこれを完全に実証しています。上で行ったように range を指定しない場合、どのようにインポートされるかは以下の通りです。
deaths <- read_excel(readxl_example("deaths.xlsx"))
#> New names:
#> * `` -> ...2
#> * `` -> ...3
#> * `` -> ...4
#> * `` -> ...5
#> * `` -> ...6
print(deaths, n = Inf)
#> # A tibble: 18 × 6
#> `Lots of people` ...2 ...3 ...4 ...5 ...6
#> <chr> <chr> <chr> <chr> <chr> <chr>
#> 1 simply cannot resist writing <NA> <NA> <NA> <NA> some…
#> 2 at the top <NA> of thei…
#> 3 or merging <NA> <NA> <NA> cells
#> 4 Name Profession Age Has … Date… Date…
#> 5 David Bowie musician 69 TRUE 17175 42379
#> 6 Carrie Fisher actor 60 TRUE 20749 42731
#> 7 Chuck Berry musician 90 TRUE 9788 42812
#> 8 Bill Paxton actor 61 TRUE 20226 42791
#> 9 Prince musician 57 TRUE 21343 42481
#> 10 Alan Rickman actor 69 FALSE 16854 42383
#> 11 Florence Henderson actor 82 TRUE 12464 42698
#> 12 Harper Lee author 89 FALSE 9615 42419
#> 13 Zsa Zsa Gábor actor 99 TRUE 6247 42722
#> 14 George Michael musician 53 FALSE 23187 42729
#> 15 Some <NA> <NA> <NA> <NA> <NA>
#> 16 <NA> also like to write stuff <NA> <NA> <NA> <NA>
#> 17 <NA> <NA> at t… bott… <NA> <NA>
#> 18 <NA> <NA> <NA> <NA> <NA> too!
メインのデータ矩形の上下にデータ以外の行があるため、すべての列が文字としてインポートされてしまいます。
もし、列の型付けの問題が na やデータ矩形を指定しても解決しない場合は、 "list" 列型を要求し、インポート後にデータ欠損や強制を処理するようにしてください。
列名を覗き見する
時には、列の数や順番に完全な確信が持てない場合でも、 col_types によって 何らかの 情報を提供する必要があります。例えば、“foofy” という名前のカラムはテキストであるべきだとわかっていても、それがどこに表示されるのかがわからないということがあります。あるいは、“foofy” の先頭にあるたくさんの空のセルが、論理的であると推測される原因にならないようにしたいと思うかもしれません。
ここでは、列名を取得するための効率的なトリックを紹介します。そうすれば、Excelファイルを読み込む際に必要な col_types ベクトルをプログラムで作成することができます。例えば、“Petal”を名前に含む列は強制的にテキストにしたいが、それ以外は推測できるようにしたいとしましょう。
(nms <- names(read_excel(readxl_example("datasets.xlsx"), n_max = 0)))
#> [1] "Sepal.Length" "Sepal.Width" "Petal.Length" "Petal.Width" "Species"
(ct <- ifelse(grepl("^Petal", nms), "text", "guess"))
#> [1] "guess" "guess" "text" "text" "guess"
read_excel(readxl_example("datasets.xlsx"), col_types = ct)
#> # A tibble: 150 × 5
#> Sepal.Length Sepal.Width Petal.Length Petal.Width Species
#> <dbl> <dbl> <chr> <chr> <chr>
#> 1 5.1 3.5 1.4 0.2 setosa
#> 2 4.9 3 1.4 0.2 setosa
#> 3 4.7 3.2 1.3 0.2 setosa
#> 4 4.6 3.1 1.5 0.2 setosa
#> # … with 146 more rows
丸い穴に四角い釘
col_types によって、カラムに特定の型を強制することができます。では、他の型のセルはどうなるのでしょうか？要求された型に強制するか、適切な型の NA に強制することになります。
それぞれの列の型について、組み込みのサンプルである type-me.xlsx のシートのスクリーンショットを以下に示します。最初の列には特定の型を強制し、2番目の列は最初の列の内容を説明しています。セルタイプと列タイプの不一致がどのように解決されるかがわかると思います。
論理カラム
数値のセルは、ゼロの場合は FALSE に、それ以外の場合は TRUE に強制されます。日付のセルは NA になります。Rと同様に、文字列 “T”, “TRUE”, “True”, “true” は TRUE として、“F”, “FALSE”, “False”, “false” は FALSE として扱われます。それ以外の文字列は NA としてインポートします。
df <- read_excel(readxl_example("type-me.xlsx"), sheet = "logical_coercion",
col_types = c("logical", "text"))
#> Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i = sheet, :
#> Expecting logical in A5 / R5C1: got a date
#> Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i = sheet, :
#> Expecting logical in A8 / R8C1: got 'cabbage'
print(df, n = Inf)
#> # A tibble: 10 × 2
#> `maybe boolean?` description
#> <lgl> <chr>
#> 1 NA "empty"
#> 2 FALSE "0 (numeric)"
#> 3 TRUE "1 (numeric)"
#> 4 NA "datetime"
#> 5 TRUE "boolean true"
#> 6 FALSE "boolean false"
#> 7 NA "\"cabbage\""
#> 8 TRUE "the string \"true\""
#> 9 FALSE "the letter \"F\""
#> 10 FALSE "\"False\" preceded by single quote"
数値列
論理値セルは、FALSE なら0、TRUE なら1に強制変換されます。これは、日付の原点からの日数で、端数がある場合もあります。テキストについては、「テキストとしての数値」現象を処理するために、数値変換が試みられます。失敗した場合、テキストセルは NA としてインポートされます。
df <- read_excel(readxl_example("type-me.xlsx"), sheet = "numeric_coercion",
col_types = c("numeric", "text"))
#> Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i = sheet, :
#> Coercing boolean to numeric in A3 / R3C1
#> Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i = sheet, :
#> Coercing boolean to numeric in A4 / R4C1
#> Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i = sheet, :
#> Expecting numeric in A5 / R5C1: got a date
#> Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i = sheet, :
#> Coercing text to numeric in A6 / R6C1: '123456'
#> Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i = sheet, :
#> Expecting numeric in A8 / R8C1: got 'cabbage'
print(df, n = Inf)
#> # A tibble: 7 × 2
#> `maybe numeric?` explanation
#> <dbl> <chr>
#> 1 NA "empty"
#> 2 1 "boolean true"
#> 3 0 "boolean false"
#> 4 40534 "datetime"
#> 5 123456 "the string \"123456\""
#> 6 123456 "the number 123456"
#> 7 NA "\"cabbage\""
日付列
数値のセルは連続した日付として解釈されます（これが賢明かどうかは疑問ですが、https://github.com/tidyverse/readxl/issues/266）。ブール値やテキストセルは NA になります。
df <- read_excel(readxl_example("type-me.xlsx"), sheet = "date_coercion",
col_types = c("date", "text"))
#> Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i = sheet, :
#> Expecting date in A5 / R5C1: got boolean
#> Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i = sheet, :
#> Expecting date in A6 / R6C1: got 'cabbage'
#> Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i = sheet, :
#> Coercing numeric to date A7 / R7C1
#> Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i = sheet, :
#> Coercing numeric to date A8 / R8C1
print(df, n = Inf)
#> # A tibble: 7 × 2
#> `maybe a datetime?` explanation
#> <dttm> <chr>
#> 1 NA "empty"
#> 2 2016-05-23 00:00:00 "date only format"
#> 3 2016-04-28 11:30:00 "date and time format"
#> 4 NA "boolean true"
#> 5 NA "\"cabbage\""
#> 6 1904-01-05 07:12:00 "4.3 (numeric)"
#> 7 2012-01-02 00:00:00 "another numeric"
テキストまたは文字列
論理型セルは "TRUE" または "FALSE" のいずれかになります。数値のセルは、Rの as.character() と同様に文字に変換されます。日付のセルは数値と同様に扱われ、基となるシリアル値を用います。
df <- read_excel(readxl_example("type-me.xlsx"), sheet = "text_coercion",
col_types = c("text", "text"))
print(df, n = Inf)
#> # A tibble: 6 × 2
#> text explanation
#> <chr> <chr>
#> 1 <NA> "empty"
#> 2 cabbage "\"cabbage\""
#> 3 TRUE "boolean true"
#> 4 1.3 "numeric"
#> 5 41175 "datetime"
#> 6 36436153 "another numeric"
