Edited at

SQLでやるこの操作ってpandas.DataFrameではどうやるの!

More than 1 year has passed since last update.

pandasを触り始めた時に感じた思いをタイトルにしてみました。テーブル操作においてSQLを使うことに慣れすぎて、pandasのDataFrameを操作しようとした時どうやるのか戸惑うことが多かったです。

SQLの基本的な構文がpandasではどう実現するのかを1つ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().reset_index(name='counts')

Survived

0 549
1 342
dtype: int64


平均をとる

SELECT Survived, MEAN(Age)

FROM passengers
GROUP BY Survived
;

df1[['Survived', 'Age']].groupby("Survived").mean().reset_index(name='counts')

                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の書き方を比較してみました。間違っているよ、こう書いたほうがいいよなどあれば、コメントください。