SQLでやるこの操作ってpandas.DataFrameではどうやるの！
pandasを触り始めた時に感じた思いをタイトルにしてみました。テーブル操作においてSQLを使うことに慣れすぎて、pandasのDataFrameを操作しようとした時どうやるのか戸惑うことが多かったです。
SQLの基本的な構文がpandasではどう実現するのかを1つ１つ並べて確認して行きます。備忘録になれば幸いです。
pandasとは
という説明は全部ふっとしばします。その代わりに以下のサイトに綺麗にまとまっているので読んでみてください。
事前準備
titanicのコードを使います。kaggleのコンペティションからデータをダウンロードしてきます。
import pandas
df = pd.read_csv("local/path/to/train.csv")
df1 = df[['PassengerId', 'Survived', 'Pclass', 'Name', 'Sex', 'Age','Fare', 'Embarked']]
df2 = df[['PassengerId', 'SibSp', 'Parch']]
print(df.columns)
print(df1)
print(df2)
Index(['PassengerId', 'Survived', 'Pclass', 'Name', 'Sex', 'Age', 'SibSp',
'Parch', 'Ticket', 'Fare', 'Cabin', 'Embarked'],
dtype='object')
PassengerId Survived Pclass Sex Age Fare Embarked
0 1 0 3 male 22.0 7.2500 S
1 2 1 1 female 38.0 71.2833 C
2 3 1 3 female 26.0 7.9250 S
3 4 1 1 female 35.0 53.1000 S
4 5 0 3 male 35.0 8.0500 S
...
PassengerId SibSp Parch
0 1 1 0
1 2 1 0
2 3 0 0
3 4 1 0
4 5 0 0
...
後で、joinするためにdf1とdf2に分割しておきます。
SELECT
カラムを指定して取得する場合。
SELECT PassengerId, Sex, Age FROM df1 ;
df1[['PassengerId', 'Sex', 'Age']] # もしくは df1.loc[:, ['PassengerId', 'Sex', 'Age']]
PassengerId Sex Age 0 1 male 22.0 1 2 female 38.0 2 3 female 26.0 3 4 female 35.0 4 5 male 35.0
locはlabel-locationの略です。loc[row,colum]のように使います。:は「全て」です。
WHERE
条件1つ
SELECT * FROM df1 WHERE Age > 40 ;
df1[df1.Age > 40]
PassengerId Survived Pclass Sex Age Fare Embarked 6 7 0 1 male 54.0 51.8625 S 11 12 1 1 female 58.0 26.5500 S 15 16 1 2 female 55.0 16.0000 S 33 34 0 2 male 66.0 10.5000 S 35 36 0 1 male 42.0 52.0000 S ...
AND条件
SELECT * FROM df1 WHERE Age > 40 AND Sex = 'male' ;
df1[(df1.Sex == 'male') & (df1.Age > 40)]
PassengerId Survived Pclass Sex Age Fare Embarked 6 7 0 1 male 54.0 51.8625 S 33 34 0 2 male 66.0 10.5000 S 35 36 0 1 male 42.0 52.0000 S 54 55 0 1 male 65.0 61.9792 C 62 63 0 1 male 45.0 83.4750 S ...
OR条件
SELECT * FROM df1 WHERE Age > 40 OR Sex = 'male' ;
df1[(df1.Sex == 'male') | (df1.Age > 40)]
PassengerId Survived Pclass Sex Age Fare Embarked 0 1 0 3 male 22.0 7.2500 S 4 5 0 3 male 35.0 8.0500 S 5 6 0 3 male NaN 8.4583 Q 6 7 0 1 male 54.0 51.8625 S 7 8 0 3 male 2.0 21.0750 S ...
NULLのレコードを抽出
SELECT * FROM df1 WHERE Age IS NULL ;
df1[df1.Age.isnull()]
PassengerId Survived Pclass Sex Age Fare Embarked 5 6 0 3 male NaN 8.4583 Q 17 18 1 2 male NaN 13.0000 S 19 20 1 3 female NaN 7.2250 C 26 27 0 3 male NaN 7.2250 C 28 29 1 3 female NaN 7.8792 Q ...
NULLでないレコードを抽出
SELECT * FROM df1 WHERE Age IS NOT NULL ;
df1[df1.Age.notnull()]
PassengerId Survived Pclass Sex Age Fare Embarked 0 1 0 3 male 22.0 7.2500 S 1 2 1 1 female 38.0 71.2833 C 2 3 1 3 female 26.0 7.9250 S 3 4 1 1 female 35.0 53.1000 S 4 5 0 3 male 35.0 8.0500 S ...
LIMIT
上から3レコードを取得する場合。
SELECT * FROM passengers LIMIT 3 ;
df1.head(3) # もしくは df1[0:3] # もしくは df1.iloc[0:3,:]
PassengerId Survived Pclass Sex Age Fare Embarked 0 1 0 3 male 22.0 7.2500 S 1 2 1 1 female 38.0 71.2833 C 2 3 1 3 female 26.0 7.9250 S
ilocはinteger-locationの略です。locが範囲をラベルで指定するものである一方で、ilocは範囲を数字（インデックス）で指定します。
ORDER BY
SELECT * FROM df1 ORDER BY Age DESC ;
df1.sort_values(by="Age", ascending=False)
PassengerId Survived Pclass Sex Age Fare Embarked 630 631 1 1 male 80.0 30.0000 S 851 852 0 3 male 74.0 7.7750 S 493 494 0 1 male 71.0 49.5042 C 96 97 0 1 male 71.0 34.6542 C 116 117 0 3 male 70.5 7.7500 Q ...
GROUP BY
数を数える
SELECT Survived, COUNT(Survived) FROM passengers GROUP BY Survived ;
df1.groupby("Survived").size()
Survived 0 549 1 342 dtype: int64
平均をとる
SELECT Survived, MEAN(Age) FROM passengers GROUP BY Survived ;
df1[['Survived', 'Age']].groupby("Survived").mean()
Age Survived 0 30.626179 1 28.343690
JOIN
内部結合
SELECT * FROM df1 INNER JOIN df2 ;
pd.merge(df1, df2, on="PassengerId", how="inner")
PassengerId Survived Pclass Sex Age Fare Embarked SibSp Parch 0 1 0 3 male 22.0 7.2500 S 1 0 1 2 1 1 female 38.0 71.2833 C 1 0 2 3 1 3 female 26.0 7.9250 S 0 0 3 4 1 1 female 35.0 53.1000 S 1 0 4 5 0 3 male 35.0 8.0500 S 0 0 ...
外部結合
SELECT * FROM df1 LEFT OUTER JOIN df2 ;
pd.merge(df1, df2, on="PassengerId", how="outer")
PassengerId Survived Pclass Sex Age Fare Embarked SibSp Parch 0 1 0 3 male 22.0 7.2500 S 1 0 1 2 1 1 female 38.0 71.2833 C 1 0 2 3 1 3 female 26.0 7.9250 S 0 0 3 4 1 1 female 35.0 53.1000 S 1 0 4 5 0 3 male 35.0 8.0500 S 0 0 ...
UPDATE
値を変更する場合（条件なし）
UPDATE df1 SET Survived = 1 ;
d = df1.copy() d.loc[:, 'Survived'] = 1
PassengerId Survived Pclass Sex Age Fare Embarked 0 1 1 3 male 22.0 7.2500 S 1 2 1 1 female 38.0 71.2833 C 2 3 1 3 female 26.0 7.9250 S 3 4 1 1 female 35.0 53.1000 S 4 5 1 3 male 35.0 8.0500 S ...
値を変更する場合（条件あり）
UPDATE df1 SET Sex = (CASE WHEN Sex = 'male' THEN 0 ELSE 1) ;
df1.replace({'male': 0, 'female': 1})
PassengerId Survived Pclass Sex Age Fare Embarked 0 1 0 3 0 22.0 7.2500 S 1 2 1 1 1 38.0 71.2833 C 2 3 1 3 1 26.0 7.9250 S 3 4 1 1 1 35.0 53.1000 S 4 5 0 3 0 35.0 8.0500 S ...
欠損値
欠損値の扱いも更新の1つですが、以下の記事がよくまとまっているので参考にしてください。 pandasで欠損値NaNを除外（削除）・置換（穴埋め）する
最後に
SQLでこうやる処理pandasでどうやるねん！というイライラから、簡単な操作について、SQLの書き方とpandasの書き方を比較してみました。間違っているよ、こう書いたほうがいいよなどあれば、コメントください。
Qiitaにも同じ内容で投稿しています。
