*本記事は旧TechblogからCOLORSに統合した記事です。
目次
自己紹介
こんにちはMS データマイニング推進部の入社３年目のS.Tです。
新卒で入社後、ポータルサイトの運用業務を担当してきました。
2018年７月からデータサイエンティストを目指して現在の部署へ異動し、データ分析の手法を勉強中です。
はじめに
現在の部署に来て、SQLを勉強し直しました。
そこで学んだ、SQLを用いたデータの前処理の方法について全4回にわたって書いていきます。
今回はDBへのデータの取り込み方を中心に進めていきたいと思います。
そもそもデータの前処理って？
データ分析を行う前に、間違った値の処理やデータの集約・加工をすることです。
ここがうまくいかないと、その後の分析に大きく影響を与えてしまうこともある重要なポイントであり、
分析の70%を占めるとまで言われています。
1. 実行環境
今回は下記の環境を用いて、実施します。
PostgreSQL 10.4
A5:SQL Mk-2（フリーのSQL開発環境。高機能で便利です）
https://a5m2.mmatsubara.com/
2. 前提
取り込みたいテーブルのCSVファイルがあること。
準備ができたら早速データを取り込んでいきます。
データのインポート
まずはテーブルの登録を行います。今回は、カラムがたくさんあるテーブルを登録したいときに便利な、エクセルを使ったクエリの作成方法も記載していきます。
必要な情報
- テーブル名
- カラム名
※テーブルデータの情報が一覧化されているファイルがある場合は、
それをエクセルで読み込んで必要なところを使用すると良いと思います。
3.【テーブル作成用クエリ作成方法】
エクセルを開き、カラム名を入れます。（取り込みたい各テーブルのCSVからコピペが一番楽だと思います）
下の画像①のようにファイル名も入れておくと色々なCSVのデータを1つのシートで管理できます。
＜画像①＞こんなかんじです
追加で以下の列を作成します（画像②）・データ型①列
・テーブル作成用クエリ列
＜画像②＞
それぞれ以下のように入力します ・「データ型①」列 → 一旦すべて「text」と入力（データ型が既に分かっている場合は、正しいデータ型を入力してください）
・テーブル作成用クエリ列 → =” , ” & カラム名セル番号& ” ” &データ型①セル番号
（先頭のセルは” , ” &を抜いてください）
＜画像③＞
A5に移動し、以下のクエリを実行します。 [code lang=”sql”]DROP TABLE IF EXISTSテーブル名; CREATE TABLE テーブル名( ○○ );[/code] テーブルごとに○○の中に上記クエリをコピペ&実行するだけでテーブルの作成ができました。 次にテーブルの中身のデータを登録していきます。
CSVファイルを以下のクエリでインポートを実行します。 [code lang=”sql”]copy テーブル名 FROM ‘読み込みたいCSVファイルのパス’ (format csv, delimiter ‘,’, header TRUE, encoding ‘sjis’);[/code] ※読み込みたいCSVファイルのパス → 「C:\Program Files～」等、CSVファイルを置いている場所を指定してください。 ここでSELECT ＊で中身を確認したり、SELECT count(＊)で件数を数えたりして、正しく登録されたか見てみます。
（確認の時はlimitで件数を制限すると、実行に時間がかからず良いです） 問題なければ、いったんtext型で入れていたデータ型を正しいものに直していきます。
カラムごとに集約し、中身を見て判断しますので、ちょっと大変かもしれません。
※既に正しいデータ型で登録されている場合はこの工程はスキップしてください
4.【集約用クエリ作成方法】
エクセルに以下の列を追加します ・テーブル名列（ここにテーブル名を入力）
・データ型②列
・集約用クエリ列（ここに以下を入力） [code lang=”sql”] =SELECT LOWER(カラム名のセル番号) , count(*) AS cnt FROM テーブル名セル番号 GROUP BY LOWER(カラム名のセル番号) ORDER BY cnt DESC; [/code]
＜画像④＞
集約用クエリ列をA5でコピペ&実行すると、カラムごとに集約された結果がでてきますので、
その中身をみて文字列であればtext、数値であればintegerやnumericのように
「データ型②」列に書き込んでいきます。
＜画像⑤＞
できたら次に進みます。
5.【データ型更新用クエリ作成方法】
エクセルに以下の列を追加します
・データ型更新用列（ここに以下を入力） [code lang=”sql”] =ALTER TABLE テーブル名セル番号 ALTER COLUMN カラム名セル番号 TYPE データ型②セル番号 USING CAST(カラム名セル番号 AS データ型②セル番号); [/code] こんなかんじです
＜画像⑥＞
データ型更新用クエリをA5でコピペ&実行し、正しいデータ型に上書きできたら同様にして必要なテーブルを登録していきます。 これでデータのインポートは完了です。
次回は、メインテーブルに関連するデータの調査をしていきます。
（第2回）
データ分析覚書 第２回：データの前処理をしてみた(2)＜データ利用範囲の確認＞
