毎木調査データの正規化

R
作者

伊東宏樹

公開

2023年4月27日

先日の記事にて、毎木調査データを横長(wide)から縦長(long)の整然(tidy)データの形式に変形するということをしました。

以下が縦長にしたデータです。

## # A tibble: 4,388 × 12
##    ID    Individual_ID  Belt     X     Y Scientific_name Japanese_name First
##    <fct> <fct>         <int> <dbl> <dbl> <chr>           <chr>         <int>
##  1 1     1                27   3.9   2.5 Betula ermanii  ダケカンバ     1993
##  2 1     1                27   3.9   2.5 Betula ermanii  ダケカンバ     1993
##  3 1     1                27   3.9   2.5 Betula ermanii  ダケカンバ     1993
##  4 1     1                27   3.9   2.5 Betula ermanii  ダケカンバ     1993
##  5 1     1                27   3.9   2.5 Betula ermanii  ダケカンバ     1993
##  6 1     1                27   3.9   2.5 Betula ermanii  ダケカンバ     1993
##  7 1     1                27   3.9   2.5 Betula ermanii  ダケカンバ     1993
##  8 1     1                27   3.9   2.5 Betula ermanii  ダケカンバ     1993
##  9 1     1                27   3.9   2.5 Betula ermanii  ダケカンバ     1993
## 10 1     1                27   3.9   2.5 Betula ermanii  ダケカンバ     1993
## # ℹ 4,378 more rows
## # ℹ 4 more variables: Last <int>, Type <chr>, Year <int>, Value <dbl>

さて、データとして扱いやすい縦長の形式にできましたが、注意しないとデータに矛盾が生じることがあります。たとえば、 IDが1の樹幹のX座標が実は測定ミスで、4.0が正しい値だった場合を考えると、IDがであるレコードのX座標をすべて同時に4.0に修正しなくてはなりません。ここで誤って一部のレコードに修正漏れがあるとデータベースとして矛盾が生じることになります。

これは横長のデータ形式なら大丈夫かというとそうでもなく、たとえば同じ個体ID (Individual_ID) のレコードなのに、樹幹 (ID) により和名 (Japanese_name) が異なっているという事態は、横長のデータ形式でも発生しえます。

データベースの正規化

このような矛盾の発生の可能性を減らすため、データベースを正規化しようと思います。

まず、フィールド(列)の依存関係を整理してみます。矢印の左側のフィールドの値が決まると、右側のフィールドの値も決まるという関係を書き出してみると、以下のようになります。

  • Individual_ID → Belt, Japanese_name, Scientific_name
  • Japanese_name → Scientific_name (これは逆も成り立つ)
  • ID → Individual_ID, X, Y, First, Last
  • ID, Type, Year → Value

以上の関係を使って、データベースを正規化します。具体的にはデータフレーム（より正確にはtibble）を分割していきます。

個体データ

select()関数でIndividual_ID, Belt, Japanese_nameを抽出し、distinct()関数で一意なものだけ残して、indiv_dataというデータフレーム (正確にはtibble) としています。このデータでは和名と学名は1対1対応なので、どちらを持ってきてもいいのですが、和名 (Japanese_name) の方を残しています。

indiv_data <- data_long %>%
  select(Individual_ID, Belt, Japanese_name) %>%
  distinct()
indiv_data
## # A tibble: 511 × 3
##    Individual_ID  Belt Japanese_name
##    <fct>         <int> <chr>        
##  1 1                27 ダケカンバ   
##  2 4                27 バッコヤナギ 
##  3 7                27 エゾマツ     
##  4 9                27 トドマツ     
##  5 10               27 エゾマツ     
##  6 11               27 エゾマツ     
##  7 12               27 シラカンバ   
##  8 13               27 エゾマツ     
##  9 14               27 エゾマツ     
## 10 15               27 エゾマツ     
## # ℹ 501 more rows

種名データ

Japanese_name と Scientific_name を抽出して一意なものを残し、name_dataというデータフレームにしています。

name_data <- data_long %>%
  select(Japanese_name, Scientific_name) %>%
  distinct()
print(name_data)
## # A tibble: 14 × 2
##    Japanese_name  Scientific_name                      
##    <chr>          <chr>                                
##  1 ダケカンバ     Betula ermanii                       
##  2 バッコヤナギ   Salix caprea                         
##  3 エゾマツ       Picea jezoensis                      
##  4 トドマツ       Abies sachalinensis                  
##  5 シラカンバ     Betula platyphylla var. japonica     
##  6 エゾニワトコ   Sambucus racemosa subsp. kamtschatica
##  7 ドロノキ       Populus suaveolens                   
##  8 オガラバナ     Acer ukurunduense                    
##  9 アカエゾマツ   Picea glehnii                        
## 10 ツリバナ       Euonymus oxyphyllus var. oxyphyllus  
## 11 ヒロハツリバナ Euonymus macropterus                 
## 12 オノエヤナギ   Salix udensis                        
## 13 ケヤマハンノキ Alnus hirsuta var. hirsuta           
## 14 ヤナギ不明     Salix sp.

樹幹データ

ID, Individual_ID, X, Y, First, Last を抽出し、一意なものだけ残して、stem_dataとしています。

stem_data <- data_long %>%
  select(ID, Individual_ID, X, Y, First, Last) %>%
  distinct()
print(stem_data)
## # A tibble: 574 × 6
##    ID    Individual_ID     X     Y First  Last
##    <fct> <fct>         <dbl> <dbl> <int> <int>
##  1 1     1               3.9   2.5  1993  2017
##  2 2     1               3.9   2.5  1993  2009
##  3 3     1               3.9   2.5  1993  2002
##  4 4     4               5.9   0.3  1993  2017
##  5 5     4               5.9   0.3  1993  2017
##  6 6     4               5.9   0.3  1993  2017
##  7 7     7               7.2   4.2  1993  2017
##  8 8     7               7.4   4.2  1993  2017
##  9 9     9               7     4.2  1993  2017
## 10 10    10              7.1   4.2  1993  2017
## # ℹ 564 more rows

測定データ

ID, Type, Year, Value

この組み合わせはもともと一意なものだけなので、distinct()関数はつかっていません。抽出してmeas_dataとしています。

meas_data <- data_long %>%
  select(ID, Type, Year, Value)
print(meas_data)
## # A tibble: 4,388 × 4
##    ID    Type   Year Value
##    <fct> <chr> <int> <dbl>
##  1 1     D      1993  14.9
##  2 1     D      1998  16.4
##  3 1     D      2002  17.9
##  4 1     D      2009  18.3
##  5 1     D      2017  19.1
##  6 1     H      1993  11  
##  7 1     H      1998  11.4
##  8 1     H      2002  11.4
##  9 1     H      2009  10.8
## 10 1     H      2017  13.1
## # ℹ 4,378 more rows

もとの1つのデータフレームからなるデータベースを、4つのデータフレーム (indiv_data, name_data, stem_data, meas_data) に分解することで、データベースを正規化しました（今回はたぶん第2正規形まで）。

元のデータフレームを復元

以上のようにデータベースを分解しましたが、結合することで元に戻すということもやってみようと思います。

樹幹+個体データ

left_join()関数で、Individual_IDをキーとして、stem_dataにindiv_dataを結合します。

stem_data %>%
  left_join(indiv_data, by = "Individual_ID")
## # A tibble: 574 × 8
##    ID    Individual_ID     X     Y First  Last  Belt Japanese_name
##    <fct> <fct>         <dbl> <dbl> <int> <int> <int> <chr>        
##  1 1     1               3.9   2.5  1993  2017    27 ダケカンバ   
##  2 2     1               3.9   2.5  1993  2009    27 ダケカンバ   
##  3 3     1               3.9   2.5  1993  2002    27 ダケカンバ   
##  4 4     4               5.9   0.3  1993  2017    27 バッコヤナギ 
##  5 5     4               5.9   0.3  1993  2017    27 バッコヤナギ 
##  6 6     4               5.9   0.3  1993  2017    27 バッコヤナギ 
##  7 7     7               7.2   4.2  1993  2017    27 エゾマツ     
##  8 8     7               7.4   4.2  1993  2017    27 エゾマツ     
##  9 9     9               7     4.2  1993  2017    27 トドマツ     
## 10 10    10              7.1   4.2  1993  2017    27 エゾマツ     
## # ℹ 564 more rows

樹幹+個体+測定データ

さらにmeas_dataを結合します。

stem_data %>%
  left_join(indiv_data, by = "Individual_ID") %>%
  left_join(meas_data, by = "ID")
## # A tibble: 4,388 × 11
##    ID    Individual_ID     X     Y First  Last  Belt Japanese_name Type   Year
##    <fct> <fct>         <dbl> <dbl> <int> <int> <int> <chr>         <chr> <int>
##  1 1     1               3.9   2.5  1993  2017    27 ダケカンバ    D      1993
##  2 1     1               3.9   2.5  1993  2017    27 ダケカンバ    D      1998
##  3 1     1               3.9   2.5  1993  2017    27 ダケカンバ    D      2002
##  4 1     1               3.9   2.5  1993  2017    27 ダケカンバ    D      2009
##  5 1     1               3.9   2.5  1993  2017    27 ダケカンバ    D      2017
##  6 1     1               3.9   2.5  1993  2017    27 ダケカンバ    H      1993
##  7 1     1               3.9   2.5  1993  2017    27 ダケカンバ    H      1998
##  8 1     1               3.9   2.5  1993  2017    27 ダケカンバ    H      2002
##  9 1     1               3.9   2.5  1993  2017    27 ダケカンバ    H      2009
## 10 1     1               3.9   2.5  1993  2017    27 ダケカンバ    H      2017
## # ℹ 4,378 more rows
## # ℹ 1 more variable: Value <dbl>

樹幹+個体+測定+種名データ

さらにname_dataを結合します。

stem_data %>%
  left_join(indiv_data, by = "Individual_ID") %>%
  left_join(meas_data, by = "ID") %>%
  left_join(name_data, by = "Japanese_name")
## # A tibble: 4,388 × 12
##    ID    Individual_ID     X     Y First  Last  Belt Japanese_name Type   Year
##    <fct> <fct>         <dbl> <dbl> <int> <int> <int> <chr>         <chr> <int>
##  1 1     1               3.9   2.5  1993  2017    27 ダケカンバ    D      1993
##  2 1     1               3.9   2.5  1993  2017    27 ダケカンバ    D      1998
##  3 1     1               3.9   2.5  1993  2017    27 ダケカンバ    D      2002
##  4 1     1               3.9   2.5  1993  2017    27 ダケカンバ    D      2009
##  5 1     1               3.9   2.5  1993  2017    27 ダケカンバ    D      2017
##  6 1     1               3.9   2.5  1993  2017    27 ダケカンバ    H      1993
##  7 1     1               3.9   2.5  1993  2017    27 ダケカンバ    H      1998
##  8 1     1               3.9   2.5  1993  2017    27 ダケカンバ    H      2002
##  9 1     1               3.9   2.5  1993  2017    27 ダケカンバ    H      2009
## 10 1     1               3.9   2.5  1993  2017    27 ダケカンバ    H      2017
## # ℹ 4,378 more rows
## # ℹ 2 more variables: Value <dbl>, Scientific_name <chr>

relocate()関数で、列を並べ替えます。

combined_data <- stem_data %>%
  left_join(indiv_data, by = "Individual_ID") %>%
  left_join(meas_data, by = "ID") %>%
  left_join(name_data, by = "Japanese_name") %>%
  relocate(ID, Individual_ID, Belt, X, Y,
           Scientific_name, Japanese_name,
           First, Last, Type, Year, Value)
print(combined_data)
## # A tibble: 4,388 × 12
##    ID    Individual_ID  Belt     X     Y Scientific_name Japanese_name First
##    <fct> <fct>         <int> <dbl> <dbl> <chr>           <chr>         <int>
##  1 1     1                27   3.9   2.5 Betula ermanii  ダケカンバ     1993
##  2 1     1                27   3.9   2.5 Betula ermanii  ダケカンバ     1993
##  3 1     1                27   3.9   2.5 Betula ermanii  ダケカンバ     1993
##  4 1     1                27   3.9   2.5 Betula ermanii  ダケカンバ     1993
##  5 1     1                27   3.9   2.5 Betula ermanii  ダケカンバ     1993
##  6 1     1                27   3.9   2.5 Betula ermanii  ダケカンバ     1993
##  7 1     1                27   3.9   2.5 Betula ermanii  ダケカンバ     1993
##  8 1     1                27   3.9   2.5 Betula ermanii  ダケカンバ     1993
##  9 1     1                27   3.9   2.5 Betula ermanii  ダケカンバ     1993
## 10 1     1                27   3.9   2.5 Betula ermanii  ダケカンバ     1993
## # ℹ 4,378 more rows
## # ℹ 4 more variables: Last <int>, Type <chr>, Year <int>, Value <dbl>

もとのデータフレーム(data_long)と同一かどうか比較してみます。

identical(data_long, combined_data)
## [1] TRUE

無事、元に戻りました。

おわりに

以上のように、データベースを正規化のために分解すること、またそれを復元するということをやってみました。正規化したほうが安全にデータベースを操作できるのですが、実際のデータ入力などの際に扱いやすいかはまた別の話です。必要に応じて分割の段階を変えたり、データ構造を変形したり、データの整合性をチェックするのがよいのかもしれません。