Python
SQL
pandas

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

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