前処理18本ノック

いろいろ前処理で泣きそうになりながらやってきた証をここに記す（お墓みたいだな・・・） 前処理X本ノックとあるがこのXの数は随時増える予定。

前処理大全[データ分析のためのSQL/R/Python実践テクニック]

bash

1. csvの連結

日毎にCSVが出力されるが、DBにロードする際、ETL等で１件ずつロードがめんどくさいよね・・・ってときに、 CSVを一気に連結する方法。 すべて同じ列構成で、ヘッダ行がある前提で、ヘッダ行は残す。

$ # カレントディレクトリ内の拡張子がcsvのものをすべて連結する。
$ awk 'NR==1 || FNR!=1' *.csv >> concat.csv  

2. テキストファイルの分割

行数で分割したい場合（リモートのDBにロード等で、分割しないとタイムアウトしたりする場合） ただしヘッダ行は考慮されない。

$ # 10000行毎に分割。分割あとはhoge-xx.csvになる。
$ split -l 10000 hoge.csv -d  --additional-suffix=.csv  hoge-

3. csvの不要列を削除

データ容量が大きく、不要列を削除して容量を削減したい場合に使用。 必要な列数目を指定して、残す処理。

$ # 4-6列目,8-19列目を残す
$ cat hoge.csv | cut -d "," -f 4-6,8-19 >> extract.csv  

4. csvの不要行を削除

データ容量が大きく、不要列を削除して容量を削減したい場合に使用。 必要な行の条件を指定して、残す処理

$ #2列目がfugaのレコードだけを残す
$ cat hoge.csv | awk -F, '$2 == fuga {print $0}' > done.csv

5. tsvをcsvに変換

水平タブをカンマに置換

$ sed -E 's/\t/,/g' hoge.tsv >> hoge.csv

6. csvをtsvに変換

カンマを水平タブに置換

$ sed -E 's/,/\t/g' hoge.csv >> hoge.tsv 

7. 固定長で改行を入れる

レガシーシステムでしか見ないけれども、

aaabbbccc

を

aaa
bbb
ccc

のように固定長で改行をいれたい場合。(csv化のための前処理とか)

$ # 3文字毎の折返し
$ fold -w 3 test.txt

バイト指定の場合はオプションが-b

$ # 3byte折返し
# fold -b 3 test.txt

8. 空白埋めされたcsvの空白除去

こんなのを

aaa   ,bbb    ,ccc    ,ddd  
eee   ,ff     ,g      ,h    

こうしたい場合

aaa,bbb,ccc,ddd
eee,ff,g,h

$ tr -d " " < hoge.csv >> hoge_del_space.csv

ただしこのままだとyyyy/mm/dd hh:mm:ssみたいな形の値が入っていた場合も、 yyyy/mm/ddhh:mm:ssとdとhの間も埋められてしまう。

そのときはsedでの置換をする必要がある。 ダブルクオーテーションでくくってあれば下記でできる。

sed -E 's/\s+"/"/g' hoge.csv >> hoge_del_space.csv

9. 文字コード変換

sjisをutf8に変換

$ iconv -f SJIS -t UTF-8

[改訂第3版]シェルスクリプト基本リファレンス ──#!/bin/shで、ここまでできる (WEB+DB PRESS plus)

SQL

1. 履歴型のマスタで最新値だけを取ってくる

履歴型のマスタ（という言葉がすでに崩壊しているが）等でよくあるパターン

登録日	商品コード	価格
2018/01/01	A	100
2018/10/01	A	120
2018/12/01	A	90
2018/11/01	B	120

みたいなのがあったとき、

登録日	商品コード	価格
2018/12/01	A	90
2018/11/01	B	120

こんな感じで最新のデータだけ取り出したい場合の処理。

-- データ作成
drop table if exists 商品マスタ;
create table 商品マスタ as
with t1 ( 
select to_date('20180101','YYYYMMDD') "登録日",'A' "商品コード",100 "価格" union all
select to_date('20181001','YYYYMMDD') "登録日",'A' "商品コード",120 "価格" union all
select to_date('20181201','YYYYMMDD') "登録日",'A' "商品コード",90 "価格" union all
select to_date('20181101','YYYYMMDD') "登録日",'B' "商品コード",120 "価格"
)
select * from t1
;

-- クエリ
select distinct
          first_value(登録日) over (partition by 商品コード order by 登録日 desc nulls last) "登録日"
         ,商品コード "商品コード"
         ,first_value(価格) over (partition by 商品コード order by 登録日 desc nulls last) "価格"
from
          商品マスタ
;

商品コード毎に新しい値で全部塗り替えてdistinctで重複を除外している。

2. 重複IDがあるレコード/単一IDのレコードだけを抽出する

ID	名前
1	hoge
1	fuga
2	chome
3	hogehoge

みたいなテーブルがあったとき、 IDがダブる1のレコードだけを取りたいとき。 with句を使うと楽（？）

-- データ作成
drop table if exists name_master;
create table name_master as 
with t1 (
select 1 "ID", 'hoge' "名前" union all
select 1 "ID", 'fuga' "名前" union all
select 2 "ID", 'chome' "名前" union all
select 3 "ID", 'hogehoge' "名前"
)
select * from t1
;
-- クエリ
with t1 as (
select 
    ID
from
    name_master
group by ID
having count(1) > 1
)
select
    a.*
from
     name_master a
     inner join t1 b on a.ID = b.ID
;

これで

ID	名前
1	hoge
1	fuga

が取り出せる。

逆にhaving count=1にすると単一レコードだけを取り出せる。

drop table if exists name_master;
create table name_master as 
with t1 (
select 1 "ID", 'hoge' "名前" union all
select 1 "ID", 'fuga' "名前" union all
select 2 "ID", 'chome' "名前" union all
select 3 "ID", 'hogehoge' "名前"
)
select * from t1;

with t1 as (
select distinct
    ID
from
    name_master
group by ID
having count(1) = 1
)
select
    a.*
from
     name_master a
     inner join t1 b on a.ID = b.ID
;

結果は下記になる。

ID	名前
2	chome
3	hogehoge

3. from-toで持っているテーブルの時間を展開

よくあるログデータの持ち方で、

user_id	from_date	to_date
A	2018/12/01	2018/12/03
B	2018/12/02	2018/12/05

のようなデータがあったとき、 （あるツールをAさんは12/1-3の間使いました、Bさんは2018/12/2-5の間使いました、のような意味）

user_id	from_date
A	2018/12/01
A	2018/12/02
A	2018/12/03
B	2018/12/02
B	2018/12/03
B	2018/12/04
B	2018/12/05

のように加工したい場合は下記のようなSQLで対応可能。 (postgresqlの場合)

-- データ作成
drop table if exists user_log;
create table user_log as
select 'A' "user_id",to_date('20181201','YYYYMMDD') "from_date" ,to_date('20181203','YYYYMMDD') "to_date" union all
select 'B' "user_id",to_date('20181202','YYYYMMDD') "from_date" ,to_date('20181205','YYYYMMDD') "to_date"
;
-- クエリ
with RECURSIVE rt(user_id,from_date,to_date) as(
select user_id,from_date,to_date
  from user_log
union all
select user_id,from_date+1,to_date
  from rt
 where from_date+1 <= to_date)
select user_id,from_date "valid_date" from rt
order by user_id,from_date;

試してはいないが、MSSQL(sqlserver)の場合は上記のSQLからrecuresive(~~)を除外すればできる（はず）。 簡易的な解説をすると、 再帰SQLを使い、条件を満たしている間（from_dateがto_dateを超えるまで)は、 インクリメントさせ続ける。

ただし、この処理は重いので、大きいテーブルに対してはあまりやるべきではない。

↓のようにpythonでもできるがこちらも重い。 kazuhitogo.hateblo.jp

毎回型の確認等が発生するため遅い。 ので、コンパイル言語でやったほうがよい。 あるいはjuliaで関数作るか。

julia,コンパイル言語 >> python > SQL である。

4. pivot（縦→横）

よくあるあれです。

今回はこんな例を用意

店	商品	値段
A	りんご	100
A	みかん	50
A	バナナ	200
B	りんご	110
B	みかん	40
B	バナナ	100

これを

店	りんご	みかん	バナナ
B	110	40	100
A	100	50	200

こうしたい。

postgresqlだとcrosstab使えだとか、 oracle,mssqlだとpivot使えだとか、 いろいろ書き方はありますが、 どれもこれも商品が増えるとめんどくさいので、最初から動的SQLで。 （今回の例はpostgresqlだが、oracleもMSSQLも似たような書き方はできる。

-- データ作成
drop table if exists 値段マスタ;
create table 値段マスタ as
with t1 as (
select 'A' "店",'りんご' "商品", 100 "値段" union all
select 'A' "店",'みかん' "商品", 50 "値段" union all
select 'A' "店",'バナナ' "商品", 200 "値段" union all
select 'B' "店",'りんご' "商品", 110 "値段" union all
select 'B' "店",'みかん' "商品", 40 "値段" union all
select 'B' "店",'バナナ' "商品", 100 "値段"
)
select * from t1
;

-- 横持ちデータ作成
drop table if exists 値段マスタ横持ち;
DO $$
DECLARE
  vSQL text;
  CR RECORD;
BEGIN
  vSQL := 'create table 値段マスタ横持ち as select 店 ';
  FOR CR IN (SELECT distinct "商品" from "値段マスタ") LOOP
    vSQL := vSQL || ',max(case when "商品" = ''';
    vSQL := vSQL || CR.商品;
    vSQL := vSQL || '''';
    vSQL := vSQL || 'then 値段 else null end) ';
    vSQL := vSQL || CR.商品;
  END LOOP;
  vSQL := vSQL || ' from 値段マスタ group by 店';
  RAISE NOTICE '%', vSQL;
  EXECUTE vSQL;
END$$;

-- selectする
select * from 値段マスタ横持ち;

解説すると、 発行したいSQLは下記の通り。

create table 値段マスタ横持ち as 
select 
          店 
         ,max(case when "商品" = 'りんご'then 値段 else null end) りんご
         ,max(case when "商品" = 'みかん'then 値段 else null end) みかん
         ,max(case when "商品" = 'バナナ'then 値段 else null end) バナナ 
from 
          値段マスタ 
group by 
          店

このSQLをvSQL変数で作り、最後にEXECUTE vSQLで実行している。 商品が増えたときにも対応できるよう、max(~~)のところを、

SELECT distinct "商品" from "値段マスタ"

でカーソルに収め、自動で発行している。

ただし、今回はりんご、みかん、バナナくらいなので、 手書きやキーマクロ等でSQLを生成したほうが速いのは言うまでも無い。

あくまで何が入るか予測できなかったり、 列の数が巨大（といっても列数の上限はoracleが1000,MSSQLが1024,postgresが1600しか無いが)のときに、 このような動的SQLを発行するとよい。

5. unpivot(横→縦)

pivotの逆。

これを

店	りんご	みかん	バナナ
B	110	40	100
A	100	50	200

こうしたい。

店	商品	値段
A	りんご	100
A	みかん	50
A	バナナ	200
B	りんご	110
B	みかん	40
B	バナナ	100

-- データ作成
drop table if exists 値段マスタ横持ち;
create table 値段マスタ横持ち as
with t1 as (
select 'A' "店",100 "りんご",50 "みかん", 200 "バナナ" union all
select 'B' "店",110 "りんご",40 "みかん", 100 "バナナ"
)
select * from t1

-- テーブル作成
drop table if exists 値段マスタ縦持ち;
DO $$
DECLARE
  vSQL text;
  CR RECORD;
BEGIN
  vSQL := 'create table 値段マスタ縦持ち as ';
  FOR CR IN (SELECT attname FROM pg_attribute WHERE attrelid = '値段マスタ横持ち'::regclass and attstattarget = -1 and attname != '店') LOOP
      vSQL := vSQL || 'select 店, ''';
      vSQL := vSQL || CR.attname;
      vSQL := vSQL || ''' 商品 ,';
      vSQL := vSQL || CR.attname;
      vSQL := vSQL || ' from 値段マスタ横持ち union all ';
  END LOOP;
  vSQL := left(vSQL,-11);
  RAISE NOTICE '%', vSQL;
  EXECUTE vSQL;
END $$;

-- クエリ

select * from 値段マスタ縦持ち order by 店,商品;

これもpivotと同様、発行したいSQLがあり、それをカーソルループで作る。

発行したいSQLは下記の通り。

create table 値段マスタ縦持ち as 
select 
          店
         , 'りんご' 商品 
         ,りんご 
from 
           値段マスタ横持ち 
union all 
select 
          店
         , 'みかん' 商品 
         ,みかん 
from 
           値段マスタ横持ち 
union all 
select 
          店
         , 'バナナ' 商品 
         ,バナナ 
from 
          値段マスタ横持ち

これを作成するために、動的SQLで、列リストが必要。 列リストは下記SQLで取れる。（カーソル）

SELECT 
          attname 
FROM 
          pg_attribute 
WHERE 
          attrelid = '値段マスタ横持ち'::regclass 
      and attstattarget = -1 
      and attname != '店'

これはpostgresにしかできないが、 pg_attributeテーブルのatterelidにテーブルを指定し、 attstattarget = -1の条件をつけることで、 該当テーブルの列リストを作ることができる。

ただし、今回のところで言う、 "店"カラムは動的SQLのパラメータには不要なため、 これもまたwhere句で除外する。

postgresqlにしかできない、と書いたが、 OracleはALL_TAB_COLUMNSから、MSSQLはsys.columnsから列一覧が取れるので、 似たようなSQLは記述できる。

ただし、今回はりんご、みかん、バナナくらいなので、 手書きやキーマクロ等でSQLを生成したほうが速いのは言うまでも無い。

あくまで何が入るか予測できなかったり、 列の数が巨大（といっても列数の上限はoracleが1000,MSSQLが1024,postgresが1600しか無いが)のときに、 このような動的SQLを発行するとよい。

6. 過去データと新データを新データ優先してマージ

下記のような２つのデータがあったとする。

過去データ

登録日	売上
2018/12/01	1000
2018/12/02	1001
2018/12/03	1002
2018/12/04	1003
2018/12/05	1004

新データ

登録日	売上
2018/12/04	1010
2018/12/05	1011
2018/12/06	2000

ここから新しいデータを優先して(重複する場合は新データを採用)、 縦にくっつけたい(union)場合。

求める結果は下記。

登録日	売上
2018/12/01	1000
2018/12/02	1001
2018/12/03	1002
2018/12/04	1010
2018/12/05	1011
2018/12/06	2000

sqlは下記で行ける。

with 過去データ as (
select to_date('20181201','YYYYMMDD') "登録日",1000 売上 union all
select to_date('20181202','YYYYMMDD') "登録日",1001 売上 union all
select to_date('20181203','YYYYMMDD') "登録日",1002 売上 union all
select to_date('20181204','YYYYMMDD') "登録日",1003 売上 union all
select to_date('20181205','YYYYMMDD') "登録日",1004 売上
),新データ as (
select to_date('20181204','YYYYMMDD') "登録日",1010 売上 union all
select to_date('20181205','YYYYMMDD') "登録日",1011 売上 union all
select to_date('20181206','YYYYMMDD') "登録日",2000 売上
)
select   
         *
from 
         新データ
union all
select
         *
from
         過去データ a
where
         not exists(select 1 from 新データ b where a.登録日 = b.登録日 )
order by 登録日

まずは新データを全部持ってきて、新データと過去データを比較して、無いものだけを過去データから持ってくる。 (not existsの部分)

ただし、not existsの処理は重いので注意。（まぁ仕方なしの場合が多いけれども）

7. 多数決でデータを作る

なんのこっちゃ、かと思いますが、例えば下記。

商品	原価
A	100
A	100
A	120
B	50
B	50
B	70

こんなデータがトランザクションで入ってきたとして、 でも原価って一律決まってて誰かの入力ミスだよね…。

みたいなときに、多数決で決めれば多分合ってんだろ、という場合、 下記のようなデータをどうやって抜き出すか、という問題。

商品	原価
A	100
B	50

実装はこれでいける。

with 原価データ as (
select 'A' "商品",100 "原価" union all
select 'A' "商品",100 "原価" union all
select 'A' "商品",120 "原価" union all
select 'B' "商品",50 "原価" union all
select 'B' "商品",50 "原価" union all
select 'B' "商品",70 "原価"
),t1 as (
select
          "商品"
         ,"原価"
         ,count(1) "件数"
from
         原価データ
group by 
          "商品"
         ,"原価"
)
select distinct
         "商品"
         ,first_value("原価") over (partition by "商品" order by "件数" desc) "原価" 
from
         t1

その原価の件数を取って多いものソートし、それをfirst_valueで塗り替える。

(本来こんなデータがあっちゃいけないんですがそれはさておき)

8. データが無い日を補完する

こんな情報があったとする。

日付	売上
2018/01/01	1000
2018/01/02	2000
2018/01/03	3000
2018/01/05	5000
2018/01/07	7000

4日や6日は売上がなくてトランザクションが存在しない場合NULLを入れたくなる。 まともなシステムならカレンダマスタ的なのが存在するが、 無い場合どうしよ、ってエントリ。

無いものは作ったれ…とカレンダマスタを作る。 ただし、不要なカレンダは作りたくないので、最小と最大部分のマスタを作る実装。

前述の再帰withでもできるけれども、 postgresqlにはgenerate_seriesがあるので、それを使ったもの。

generate_seriesはざっくりいうと、 日付に限らず連続するものを最小と最大とインターバルを指定すると、 その値の集合を返してくれる。

https://www.postgresql.jp/document/9.1/html/functions-srf.html

ただしそのままだと固定値を都度入れることになるので、 pl/pgsqlで可変で使えるようにする。

-- データ作成

drop table if exists 売上情報;
create table 売上情報 as 
with t1 as (
select to_date('20180101','YYYYMMDD') "日付",1000 "売上" union all
select to_date('20180102','YYYYMMDD') "日付",2000 "売上" union all
select to_date('20180103','YYYYMMDD') "日付",3000 "売上" union all
select to_date('20180105','YYYYMMDD') "日付",5000 "売上" union all
select to_date('20180107','YYYYMMDD') "日付",7000 "売上"
)
select * from t1
;

-- カレンダ作成

DO $$
DECLARE
  min_date date;
  max_date date;
BEGIN
  select into min_date min("日付") from 売上情報;
  select into max_date max("日付") from 売上情報;
  RAISE NOTICE '%', min_date;
  RAISE NOTICE '%', max_date;
  drop table if exists カレンダマスタ;
  create table カレンダマスタ as
  SELECT
            generate_series "日付"
  FROM 
            generate_series(min_date,max_date, '1 days')
  ;
END
$$
;

-- カレンダと売上の結合

select
         a.日付
         ,b.売上
from
         カレンダマスタ a
         left join 売上情報 b on a.日付 = b.日付  

結果は下記が返る。

日付	売上
2018/01/01	1000
2018/01/02	2000
2018/01/03	3000
2018/01/04	NULL
2018/01/05	5000
2018/01/06	NULL
2018/01/07	7000

oracleもsqlserverも似たような方法はある。

oracle doruby.jp

sqlserver 夏椰の庵 - Secluded Spot of Kaya -

スッキリわかる SQL 入門 ドリル215問付き! (スッキリシリーズ)

python

1. リストと辞書の組み合わせからcsvに変換

下記のような辞書のキーに予測がつかないようなリストがあったとする。

x = [{"hoge":1,"fuga":2},{"hoge":3,"fuga":4},{"hoge":5,"hogehoge":6}]

これをキー一覧から表形式に変換する。

fuga	hoge	hogehoge
2	1	NaN
4	3	NaN
NaN	5	6

# pandasを使う
import pandas as pd
# データ作成
x = [{"hoge":1,"fuga":2},{"hoge":3,"fuga":4},{"hoge":5,"hogehoge":6}]
# キー（カラム）一覧を作成
key_set = set([ii for i in x for ii in i.keys()])
# indexのみのdataframe作成
csv_df = pd.DataFrame(index=[i for i in range(len(x))], columns=[])
# 各カラムのデータを入れていく
for ii in key_set:
    csv_df[ii] = pd.DataFrame([i.get(ii) for i in x])
# csv出力
csv_df.to_csv("hoge.csv",index = False)

リスト内包表記を連発しているが、簡単に解説すると下記の通り。

1. キーをまずは重複ありでもいいのでとにかくとってきて、それをset関数で重複を排除する
2. 空のデータフレームを作成する
3. キー順にデータを入れていく。

もう少し速い処理もかけそうな気もするけれども一旦。

入門 Python 3