pandas操作⑥：横持ち変換

縦持ち（レコード形式）から横持ち（表形式）への変換をするとき、スマートではないのはわかっていたけどSQLで横持ち変換したい値に応じたcase文をシコシコ書いていた。値が増えたりしたときにまた書き換える必要あるのでこれはクソなコードである。

pythonに横持ち変換用にpivot_table関数というのがあることを知ったのでどうやるのか試してみる。

import pandas as pd
from preprocess.load_data.data_loader import load_hotel_reserve
customer_tb, hotel_tb, reserve_tb = load_hotel_reserve()

# pivot_table関数で、横持ち変換と集約処理を同時実行
# aggfuncに予約数をカウントする関数を指定
pd.pivot_table(reserve_tb, index='customer_id', columns='people_num',
               values='reserve_id',
               aggfunc=lambda x: len(x), fill_value=0)

https://github.com/ghmagazine/awesomebook/blob/master/preprocess/007_spread/01/python_awesome.py
『前処理大全』の上記githubから引用。

SQLだとpeople_numの人数毎にcase文書いていたけどこれなら一発でできるよ、ということらしい。でもredshiftのテーブルからデータを引っ張って来ている場合はこの関数では直接テーブルみれないのでredshiftのテーブルをDataFrameにする必要があるので、結局SQLは書くことになる。

仮に直接redshiftのテーブル見れたとしても、そのまま使うことってないから（他のテーブルと結合したり、何らかの抽出条件加えたりする）、やはりpivot_table関数の前にread_sql_query関数が必要になる。

まずは下記の記事で書いたredshiftに接続する方法でデータ持ってくるわけです。
exotic-manifold.hatenablog.com

いつも上記の方法でredshiftにあるテーブルにアクセスしていたけど、『前処理大全』にもっと簡単な接続方法書いてるかもしれないから次はこれを調べる。

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

pandas操作⑤：複数条件で抽出

複数条件でpandasから抽出したいときに下記エラー。

ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

各条件は丸括弧で括って、かつアンド条件のときはandではダメで&を使わないといけない。
下記が参考になる。
qiita.com

pandas操作④：結合(concat)、いらない行除外、インデックス振り直し、カウントして確認

①いくつか作ったDataFrameを統合して、
②ある条件に合致するいらない行以外を抽出して、
③インデックスが飛び飛びになっているから振り直しして、
④最後にカウント

import pandas as pd

test = pd.concat([hoge1, hoge2, hoge3, hoge4]) #①
test = test[test.カラム名 != 除外した行の条件] #②
test.reset_index(inplace = True, drop = True) #③
test.count() #④

pandas操作③：作業ディレクトリ確認・変更、エンコード失敗したとき

何かファイルを読み取ってDataFrameに入れたいとき、どこが作業ディレクトリだろう、という場合。

import os
os.getcwd()

で、これを変えたい場合は下記。

os.chdir('変更したいフォルダへのパス')

これをread_csvやread_tableとかで読み取って

import pandas as pd
hoge = pd.read_table('hogehoge.tsv')

下記エラー文がでちゃった。

UnicodeDecodeError: 'utf-8' codec can't decode byte 0x83 in position 0: invalid start byte

とりあえず下記で動いた。

hoge = pd.read_table('hogehoge.tsv', encoding="shift-jis")

sklearn.metrics.f1_scoreでエラーが出るとき

from sklearn.metrics import f1_score
f1_score(検証データ, 予測データ)

上記モデル検証でF-score出すときに下記エラーが出た。

ValueError: pos_label=1 is not a valid label: array([ 0., 21.])

多分ラベルがだめってことなんだろうけど確認する（jupyterならshift+tabでhelpでるけど）。
sklearn.metrics.f1_score — scikit-learn 0.20.2 documentation

"pos_label : str or int, 1 by default"

正解ラベルはデフォルトでは１だぞと。
私のデータは21になっているのでエラーが出たわけです。

１に置換してもいいけど、文字列か整数でもいいと書いてあるので指定すればいいだろう。
（ちなみにstr or int というのはこのままに入力するという意味ではない。自分がラベルに設定している文字列や整数を入れるということ）

from sklearn.metrics import f1_score
f1_score(検証データ, 予測データ, pos_label = 21)

これでおｋ

Redshift既存テーブルへの操作：置換、カラム追加、データ型変換、値追加

テーブルコピー

置換したいが既存のものをダイレクトに置換して失敗すると怖いからまずはテーブルコピーしたい。

SELECT * INTO <コピー先テーブル名>
FROM <コピー元テーブル名>

置換

コピーしたテーブルにやりたい置換をかける。

UPDATE <テーブル名>
SET <カラム名>=REPLACE(<カラム名>,"置換対象","置換後の文字");

カラム追加

またはカラム追加したい場合もある。

ALTER TABLE <テーブル名>
ADD <カラム名> <データ型> <NULL or NOT NULL>;

データ更新（値追加したり）

UPDATE <テーブル名>
    SET <カラム名>=値, [カラム名=値, ...]
    [WHERE 条件式]
    [ORDER BY ...]
    [LIMIT 値]

データ型変換

値変換したらデータ型も変換したいときもある。
redshiftだとalter columnがサポートされてなかったので、まずカラム追加してから変換する方法しかわからなかった。
追加方法はすでに記載の通り。

update <テーブル名>
set <追加したカラム名> = cast(<変更元のカラム名> as <変更後のデータ型>)

これで追加したカラムに元のカラムの値が入っているので、カラム名を変更して元のカラムを削除（めんどくさい）。

ALTER TABLE <テーブル名>
DROP COLUMN <カラム名>

EC2にsshで接続しているときのタイムアウトを防止する

EC2にssh接続してPython環境を利用している場合、ちょっと処理に時間がかかっていると接続がきれてしまう。これを回避するためにには~/.ssh/configに"ServerAliveInterval 60"と書き込む。でも操作に慣れていないと毎回忘れるので備忘として下記のステップでやる。

１．ユーザー名のフォルダに移動
２．command+shift+.（ドット）で隠しフォルダを表示
３．.sshのフォルダのconfigを開いて"ServerAliveInterval 60"(60秒毎にkeep aliveのパケットが送信される)