はじめに
こんにちは、事業会社で働いているデータサイエンティストです。
会社が出してくれたインタビュー記事です：
業界で働くと、どうしても教科書が紹介するような、キレイなデータベース形式（tidyデータ）以外のデータと付き合うことになりますので、この記事では、データをキレイにする方法を話します。RとPythonのコードは3行だけです。
また、私は普段業務ではRとSQLしか使わず、Pythonのスキルはあまりないので、この記事でもRを中心に説明して、PythonでRの処理をできるだけ再現する流れで紹介します。
心構え
内容に入る前に、まずは以下のことを強調したいです：
- データベースの教科書はあくまでも教科書。現実が一番大事
- 手作業で帳票データを集めてくれた方への感謝とリスペクトを忘れない
- 帳票データの形式がよろしくなければ、RかPythonでそれをキレイにすればいい
- データの形式ではなく、データ分析などの価値提供にフォーカスしましょう
残念ながら、データベースの教科書で紹介されるデータ形式に一般人はなじみを感じません。これが現実です。
そこで、いわゆる神エクセルファイルをもらった際に、ファイル作成者に怒りを感じるデータサイエンティストもいると思いますが、プログラムによる自動収集が効かないデータを丁寧に手作業で集めていただいて、新しい分析を可能にしてくださった方へは感謝とリスペクトの気持ちで接してください。分析用のデータをキレイにするのもデータサイエンティストの仕事です。
3行だけです。3行でいわゆる神エクセルをキレイにしますので、一瞬で問題が解消されます。
「こんなエクセル嫌だ！絶対こんなのを処理したくない！これで分析したくない！」ではなく、データを粛々とキレイに整形し、計量政治学、計量経済学、統計学、機械学習でバリューを出すのが、一流のデータサイエンティストなのではないかと思います。
また、手入力帳票がこのような形式になりがちなのは、決して担当者からの嫌がらせではありません。人間にとって入力しやすいからです。
帳票入力は大体担当者の定型作業なので形式はあまり変わることなく、一回コードが完成したら、帳票が更新されても基本的にそのまま対応できます。
神エクセルくん、こんにちは
主役の神エクセルくんです：
ビジネスの現場にいる方にとっては、なじみのある形式かもしれません。
プログラムにとって厄介なポイントを説明します。
- 1行目に意味のない「⭐︎帳票です⭐︎」が入っている
- 地域のセルが結合されている
- 日付がカラム名になっている
- 0には「0」ではなく、NA（R視点。正確にいうとNA_real_）・NaN（Python視点）が入っている
では、帳票をRでいうとこんな形式に整形する方法を説明します
# A tibble: 30 × 4
地域 都道府県 日付 予算
<chr> <chr> <chr> <dbl>
1 関東 東京都 2023-01-01 10
2 関東 東京都 2023-01-02 0
3 関東 東京都 2023-01-03 61
4 関東 東京都 2023-01-04 0
5 関東 東京都 2023-01-05 20
6 関東 千葉県 2023-01-01 0
7 関東 千葉県 2023-01-02 32
8 関東 千葉県 2023-01-03 0
9 関東 千葉県 2023-01-04 67
10 関東 千葉県 2023-01-05 0
# ℹ 20 more rows
# ℹ Use `print(n = ...)` to see more rows
Rでの処理方法
Rで処理する際は、openxlsxパッケージを推奨します。
まず、そのまま読み込んでみましょう：
> openxlsx::read.xlsx("帳票.xlsx")
⭐︎帳票です⭐︎ X2 X3 X4 X5 X6 X7
1 地域 都道府県 44927 44928 44929 44930 44931
2 関東 東京都 10 NA 61 NA 20
3 <NA> 千葉県 NA 32 NA 67 NA
4 関西 大阪府 1 NA 23 NA 71
5 <NA> 京都府 NA 1 NA 91 44
6 東北 宮城県 20 9 76 NA NA
7 <NA> 福島県 NA NA NA NA NA
ここでは、1行目はいらないので、startRow引数に2を指定します
> openxlsx::read.xlsx("帳票.xlsx", startRow = 2)
地域 都道府県 44927.0 44928.0 44929.0 44930.0 44931.0
1 関東 東京都 10 NA 61 NA 20
2 <NA> 千葉県 NA 32 NA 67 NA
3 関西 大阪府 1 NA 23 NA 71
4 <NA> 京都府 NA 1 NA 91 44
5 東北 宮城県 20 9 76 NA NA
6 <NA> 福島県 NA NA NA NA NA
よくなっていますね。
次に、日付がなんか変な数字になっているので、detectDatesをTRUEにして、日付を検出させましょう
> openxlsx::read.xlsx("帳票.xlsx", startRow = 2, detectDates = TRUE)
地域 都道府県 2023-01-01 2023-01-02 2023-01-03 2023-01-04 2023-01-05
1 関東 東京都 10 NA 61 NA 20
2 <NA> 千葉県 NA 32 NA 67 NA
3 関西 大阪府 1 NA 23 NA 71
4 <NA> 京都府 NA 1 NA 91 44
5 東北 宮城県 20 9 76 NA NA
6 <NA> 福島県 NA NA NA NA NA
問題ないですね。
ではセル結合に関してなんですが、openxlsxのread.xlsx関数にはなんとfillMergedCellsという有難い引数が用意されていて、これをTRUEにするだけで結合されたセルに値を入れてくれます
> openxlsx::read.xlsx("帳票.xlsx", startRow = 2, detectDates = TRUE, fillMergedCells = TRUE)
地域 都道府県 2023-01-01 2023-01-02 2023-01-03 2023-01-04 2023-01-05
1 関東 東京都 10 NA 61 NA 20
2 関東 千葉県 NA 32 NA 67 NA
3 関西 大阪府 1 NA 23 NA 71
4 関西 京都府 NA 1 NA 91 44
5 東北 宮城県 20 9 76 NA NA
6 東北 福島県 NA NA NA NA NA
次に、横持ち形式からデータベースの縦持ち形式に変換しましょう。一つ目の引数に、「!c(地域, 都道府県)」を入れることで、地域と都道府県は変更の対象ではないことを指定します。次にカラム名を「日付」カラムに、値を「予算」カラムに入れることをそれぞれnames_to引数とvalues_to引数で指定します
> openxlsx::read.xlsx("帳票.xlsx", startRow = 2, detectDates = TRUE, fillMergedCells = TRUE) |>
tidyr::pivot_longer(!c(地域, 都道府県), names_to = "日付", values_to = "予算")
# A tibble: 30 × 4
地域 都道府県 日付 予算
<chr> <chr> <chr> <dbl>
1 関東 東京都 2023-01-01 10
2 関東 東京都 2023-01-02 NA
3 関東 東京都 2023-01-03 61
4 関東 東京都 2023-01-04 NA
5 関東 東京都 2023-01-05 20
6 関東 千葉県 2023-01-01 NA
7 関東 千葉県 2023-01-02 32
8 関東 千葉県 2023-01-03 NA
9 関東 千葉県 2023-01-04 67
10 関東 千葉県 2023-01-05 NA
# ℹ 20 more rows
# ℹ Use `print(n = ...)` to see more rows
最後に、予算カラムのNAを0に変換しましょう。tidyrのreplace_na関数に、NAの変換ルールをlist型で指定します
> openxlsx::read.xlsx("帳票.xlsx", startRow = 2, detectDates = TRUE, fillMergedCells = TRUE) |>
tidyr::pivot_longer(!c(地域, 都道府県), names_to = "日付", values_to = "予算") |>
tidyr::replace_na(list(予算 = 0))
# A tibble: 30 × 4
地域 都道府県 日付 予算
<chr> <chr> <chr> <dbl>
1 関東 東京都 2023-01-01 10
2 関東 東京都 2023-01-02 0
3 関東 東京都 2023-01-03 61
4 関東 東京都 2023-01-04 0
5 関東 東京都 2023-01-05 20
6 関東 千葉県 2023-01-01 0
7 関東 千葉県 2023-01-02 32
8 関東 千葉県 2023-01-03 0
9 関東 千葉県 2023-01-04 67
10 関東 千葉県 2023-01-05 0
# ℹ 20 more rows
# ℹ Use `print(n = ...)` to see more rows
これでできました！
Pythonでの処理方法
Pythonの場合、pandasのread_excel関数でheaderを1に指定することで、0行目（⭐︎帳票です⭐︎）を読み込まないようにします。
次に、チェーンメソッドのmeltで、Rのtidyrのpivot_longerと同じ処理をした上で、最後に予算のNaNに0を入れる処理をfillnaで実現します
import pandas as pd
df = pd.read_excel("帳票.xlsx", header = 1).melt(id_vars = ['地域', '都道府県'], var_name = '日付', value_name = '予算')
df['予算'].fillna(0, inplace = True)
結果はこんな感じです
残念ながら私のPython（pandas）力不足で、セル結合されたセルに自動で値を入れるR側のfillMergedCells引数のような処理を実現する方法がわかりません。
また、pandasの場合、日付を問題なく読み込むことができ、Rのように引数で指定して変換させる必要はないようです。
結論
3行で終わりました。
個人的にはRのopenxlsxパッケージはセル結合まで対応してくれるのでpandasより便利だなと思いますが、pandasでもセル結合対応用関数（Rの関数型プログラミングの考えで申し訳ないです💦）を入れれば解消できると思います。
さて、エクセルの整形が終わったので、次はデータサイエンスでバリューを出しましょう！