pandasqlクエリを使用してpandasでSQLを使う方法

Python SQL

SQL、または構造化查询言語は、関係データベースに保存されたデータにアクセス、抽出、操作、探索するためのプログラミング言語です。pandasは、データ操作と分析のためのPythonのオープンソースライブラリで特化して設計されています。

このチュートリアルでは、pandasのフレームワーク内でSQL機能をどのような時に、どのように（そして、どのような時には使えないか）使用するかを説明します。また、このアプローチの実装例をいくつか見て、それらと同等のpandasのコードを比較します。

なぜpandasでSQLを使用するのか？

前述の定義に基づいて、pandasはデータ分析のための包括的なパッケージですが、なぜSQLと組み合わせて使用する必要があるのでしょう？

答えは、特に複雑なプログラムでは、SQLのクエリはpandasの対応するコードよりもはるかに直感的で読みやすい場合があります。これは、特にデータ操作にSQLを使い始めてからpandasを学んだ人にとって特に当てはまります。

pandasについてより学習が必要な場合、私たちのpandasによるデータ操作コースとPandasチュートリアル：PythonのDataFramesをチェックしてください。

To see SQL’s readability, let’s assume we have a table (data frame) called penguins that contains various information about penguins (and we will use this table later in this tutorial). To extract all unique species of male penguins with flipper lengths greater than 210 mm, we would write the following code in pandas:

penguins[(penguins['sex'] == 'Male') & (penguins['flipper_length_mm'] > 210)]['species'].unique()

On the other hand, to obtain the same information using SQL, we would execute the following code:

SELECT DISTINCT species FROM penguins WHERE sex = 'Male' AND flipper_length_mm > 210

SQLのコードは、ほぼ自然な英語の文として書かれており、より直感的です。さらに、それを複数の行にわたって書くことで、読みやすさを向上させることができます。

SELECT DISTINCT species
  FROM penguins 
 WHERE sex = 'Male' 
   AND flipper_length_mm > 210

SQLをpandasで利用する利点を認識したので、どのように技術的に両方を結びつけることができるか見ていきましょう。

pandasqlの使い方

pandasql Pythonライブラリは、SQLサーバーに接続することなくpandasのデータフレームにSQLコマンドを実行することができます。内部では、SQLiteの構文を使用し、自動的にpandasデータフレームを検出し、それを通常のSQLテーブルとして扱います。

環境設定

まず、pandasqlをインストールする必要があります。

pip install pandasql

次に、必要なパッケージをimportします。

from pandasql import sqldf
import pandas as pd

私たちは上記で、pandasqlからsqldf関数を直接インポートしました。これはライブラリのほぼ唯一の有意義な関数です。名前が示す通り、SQL構文を使用してデータフレームをクエリするために適用されます。この関数以外にも、pandasqlにはload_births()とload_meat()という自己説明的な関数を使用してロードできる2つのシンプルな組み込みデータセットがあります。

pandasql構文

sqldf関数の構文は非常にシンプルです。

sqldf(query, env=None)

ここで、queryは文字列としてSQLクエリを受け取る必須パラメータであり、envはオプションの（そしてほとんど役に立たない）パラメータで、locals()またはglobals()のどちらかを指定し、Python環境内の対応する変数セットにsqldfがアクセスできるようにします。

sqldf関数はクエリの結果をpandasデータフレームとして返します。

pandasqlを使用できる場合

pandasqlライブラリは、SQLのサブセットの一つであるデータクエリ言語（DQL）を使用してデータを操作することを可能にします。言い換えれば、pandasqlを使用して、データベースに保存されているデータに対してクエリを実行し、必要な情報を取得することができます。特に、データをアクセス、抽出、フィルタリング、ソート、グループ化、結合、集計し、数学的または論理的操作を行うことができます。

pandasqlを使用できない場合

pandasqlはDQL以外のSQLサブセットを使用することを許可していません。つまり、pandasqlを使用してテーブルを変更（更新、切り捨て、挿入など）したり、テーブル内のデータを変更（更新、削除、または挿入）することはできません。

このライブラリはSQL構文に基づいているため、SQLiteの既知の仕様のひねりに注意する必要があります。

pandasqlの使用例

では、pandasのデータフレーム上でSQLクエリを実行するためのsqldf関数の使用方法をより詳細に見ていきます。pandasql。

import seaborn as sns
penguins = sns.load_dataset('penguins')
print(penguins.head())

練習用のデータを読み込むために、seabornライブラリの内蔵データセットの一つであるpenguinsをロードしましょう：出力：

   species island     bill_length_mm  bill_depth_mm  flipper_length_mm  \
0  Adelie  Torgersen            39.1           18.7              181.0   
1  Adelie  Torgersen            39.5           17.4              186.0   
2  Adelie  Torgersen            40.3           18.0              195.0   
3  Adelie  Torgersen             NaN            NaN                NaN   
4  Adelie  Torgersen            36.7           19.3              193.0   

   body_mass_g     sex  
0       3750.0    Male  
1       3800.0  Female  
2       3250.0  Female  
3          NaN     NaN  
4       3450.0  Female

SQLのスキルを更新する必要がある場合は、私たちのSQL基礎スキルトラックが良い参照点です。

pandasqlを使用したデータの抽出

print(sqldf('''SELECT species, island 
FROM penguins 
LIMIT 5'''))

出力：

  species  island
0  Adelie  Torgersen
1  Adelie  Torgersen
2  Adelie  Torgersen
3  Adelie  Torgersen
4  Adelie  Torgersen

上記では、penguinsデータフレームから最初の5匹のペンギンの種と地理情報を抽出しました。sqldf関数の実行はpandasデータフレームを返すことに注意してください：

print(type(sqldf('''SELECT species, island 
                      FROM penguins 
                     LIMIT 5''')))

出力：

<class 'pandas.core.frame.DataFrame'>

純粋なpandasでは、以下のようになります：

print(penguins[['species', 'island']].head())

出力：

   species island
0  Adelie  Torgersen
1  Adelie  Torgersen
2  Adelie  Torgersen
3  Adelie  Torgersen
4  Adelie  Torgersen

別の例は、列から一意の値を抽出することです：

print(sqldf('''SELECT DISTINCT species 
                 FROM penguins'''))

出力：

     species
0     Adelie
1  Chinstrap
2     Gentoo

pandasでは、以下のようになります：

print(penguins['species'].unique())

出力：

['Adelie' 'Chinstrap' 'Gentoo']

pandasqlを使用したデータの並べ替え

print(sqldf('''SELECT body_mass_g 
                 FROM penguins 
                ORDER BY body_mass_g DESC 
                LIMIT 5'''))

出力：

   body_mass_g
0       6300.0
1       6050.0
2       6000.0
3       6000.0
4       5950.0

上記では、ペンギンを体重の降順に並べ替え、体重の上位5つを表示しました。

pandasでのコードは以下のようになります。

print(penguins['body_mass_g'].sort_values(ascending=False, 
ignore_index=True).head())

出力:

0    6300.0
1    6050.0
2    6000.0
3    6000.0
4    5950.0
Name: body_mass_g, dtype: float64

pandasqlを使ったデータのフィルタリング

この章で紹介した同じ例を試してみましょう。つまり、雄性で且つペンギンの腕の長さが210mm以上のユニークな種を抽出します。

print(sqldf('''SELECT DISTINCT species
                 FROM penguins 
                WHERE sex = 'Male' 
                  AND flipper_length_mm > 210'''))

出力:

     species
0  Chinstrap
1     Gentoo

上記では、sex = 'Male'とflipper_length_mm > 210の2つの条件に基づいてデータをフィルタリングしました。

pandasでの同じコードはもう少し複雑です。

print(penguins[(penguins['sex'] == 'Male') & (penguins['flipper_length_mm'] > 210)]['species'].unique())

出力:

['Chinstrap' 'Gentoo']

データのグルーピングと集計をpandasqlで行う

次に、データフレーム内の各種の最長の嘴長を見つけるためにデータのグルーピングと集計を適用しましょう。

print(sqldf('''SELECT species, MAX(bill_length_mm)
                 FROM penguins 
                GROUP BY species'''))

出力:

     species  MAX(bill_length_mm)
0     Adelie                 46.0
1  Chinstrap                 58.0
2     Gentoo                 59.6

pandasでの同じコード:

print(penguins[['species', 'bill_length_mm']].groupby('species', as_index=False).max())

出力:

     species  bill_length_mm
0     Adelie            46.0
1  Chinstrap            58.0
2     Gentoo            59.6

pandasqlでの数学的演算の実行

pandasqlを使うと、データに対して数学的または論理的演算を簡単に行うことができます。これから、各ペンギンの嘴長と深さの比率を計算し、この測定の上位5つの値を表示することを考えてみましょう。

print(sqldf('''SELECT bill_length_mm / bill_depth_mm AS length_to_depth
                 FROM penguins
                ORDER BY length_to_depth DESC
                LIMIT 5'''))

出力:

   length_to_depth
0         3.612676
1         3.510490
2         3.505882
3         3.492424
4         3.458599

今回は、比率値を持つ列に別名length_to_depthを使用したことに注意してください。そうしないと、bill_length_mm / bill_depth_mmという名前の厄介な列が得られます。

pandasでは、まず比率値を持つ新しい列を作成する必要があります。

penguins['length_to_depth'] = penguins['bill_length_mm'] / penguins['bill_depth_mm']
print(penguins['length_to_depth'].sort_values(ascending=False, ignore_index=True).head())

出力:

0    3.612676
1    3.510490
2    3.505882
3    3.492424
4    3.458599
Name: length_to_depth, dtype: float64

総じて

まとめると、このチュートリアルでは、より良く効率的なコードを書くために、pandasのためのSQLの機能を組み合わせる理由とタイミングについて探りました。この目的のためにpandasqlライブラリを設定して使用する方法と、このパッケージの制限についても議論しました。最後に、pandasqlの実際の応用例を複数検討し、それぞれのケースでpandasのコードと比較しました。

これで、実際のプロジェクトでpandasのためのSQLを適用するために必要なすべてが揃いました。練習のための素晴らしい場所は、DataLab、DataCampのAI対応データノートブックで、優れたSQLサポートを提供しています。

Source:
https://www.datacamp.com/tutorial/how-to-use-sql-in-pandas-using-pandasql-queries