【1-13】Rで読み込みやすいExcelファイルの作り方
Rを含めデータを分析するときは事前にExcelファイルやcsvファイルで保存したものを読み込むことがほとんどだと思います。しかし、そもそもExcelではRに読み込みやすい形とそうでない形があります。Excelの見栄え的には良くても実際にRで読み込もうとすると大変な手間になることもあります。
Rで読み込みやすいExcelのポイントを紹介します。
上や左に空白はないか？
RでなにもせずにExcelファイルを読み込むとA1を始めとしてデータを読み込みます。よくExcelでかけ線を設定している場合B2から表をつくることがありますが、Rで読み込むときには一手間必要です。実際には一番上や左に何もデータがなければRが空気を読んで読み込んでくれたりもしますが、もし何かデータが入るとそうはいかないので慣れないうちは埋めておいた方が無難だと思います。
一番下に合計の行は作らない
加えてもし一番下に合計や平均の列があったらそれも1データとして読み込んでしまうので注意が必要です。
Excelは集計のために使い分析をRで行う場合はそもそも無いほうが安全です。
もしExcelでも集計を行いたい場合は集計を別のタブで行うかピボットテーブルを使う方法があります。
セル結合は基本しない
セル結合はエラーの元です。見栄えは良いのですが、データ収集することが目的のExcelでは使わない方が無難です。
4つの型を意識する
数値の場合の注意点
10,000などカンマ含む場合、Rで読み込むと数字ではなく文字として認識されます。どうしても , はつけないとダメ」と上司に言われてもRで , を取る方法はあります（parse_number関数）
また-100を▲100にしていたらマイナスに直す必要があります。
文字の置換（ここでは▲ → − ）にするにはstr_replaceなどの関数がありますが、手間を考えると−の方が手間が省けます。
日付の注意点
和暦だと古いExcelではいつまでたっても令和になりません。
計算や列の順番を考えると西暦のほうが後々対処しやすいです。
加えて2001年を01年としないよう注意しましょう。
1つのセルには1データ
上記のカテゴリーのように性別と左右を1つのセルに入れられると、分析に一手間が増えます。素直に2列にして入力したほうが分析しやすいです。
スペースで体裁を整えない、セル内で改行しない
スペースで見た目を整えるとスペースを文字とみなされることがあります。
報告書ではいいかもしれませんが、データベースではエラーの元なのでスペースは使わないようにします。加えてセル内で改行すると、Rで読み込むとExcelでは見えないよう設定されていますが、Rでは改行記号が入り込みます。なのでセル内改行もしないようにします。
同じデータを省略しない
Rで埋める方法もありますが、基本は1つ1つセルを埋めるようにします。
タイトル行が2行に渡っていないか？
タイトル行が2行に渡っているとRではうまく読み込んでくれません。
そしてそういう場合はセル結合されていることも多いです。
ただ上司にセル結合＋列名を2行にしないと減給だ！！！と脅される場合もあるかもしれません。
自分ならもう一列Rで列名読み込むための行を作るという回避策を用意します。
①集計に使うシートを全て選択し一括処理の状態にする
② 実際のデータ入力する列の1つ上の行に挿入する（ここでは3行目）
③ A,B,C列は実質1行なのでA3は =A1 を使う。（B3, C3も同様）
④ D列以降は複数行にまたがるため & でつなげる
⑤ D3は$D$1&"_"&D2とする。それをコピーしてE3以降を選択し貼り付け。
⑥3行目から表の右端まで選択しCtrl + Tを押す
完成
必要なら3行目を非表示にする
テーブル形式にすると無駄なセル結合をできなくしたり、書式設定や入力規則を使うことで数値の列に数値を入れ込ませないなどルールを作ることができます。
フォルダやタブの名前はルールをつける
正気の沙汰とは思えないフォルダの作り方をする他課の課長。 pic.twitter.com/7DLVLFlsXa
— 56日後に退職する影人事課長 (@ShadowSR1976) August 1, 2022
上記はフォルダ名ですが、タブの名前も一定のルールをつけた方がいいです。
Rはフォルダの名前やタブの名前を読み込んで分析に使うことができます。
例えば2022_01であれば2022_02とつけると後でデータを取り込むと手間が減ります。ここで上記のようなフォルダ管理や2022年1月, 2022 02, ３, 令和4年4月みたいにタブ毎にルールが変わると、Rでデータを読み込んだときに痛い目に遭います。
まとめ
データ分析で一番大変なのはデータ入力されたものを分析できる形にすることで、全体の8割の業務量と言われます。上記の方法であればそのままRに読み込むこともスムーズですし、Excelのピボットテーブルで分析することも容易になります。
この記事が気に入ったらサポートをしてみませんか？