querying pandas

pandasによるクエリ

ここでは Google Colab で Python を使って、データベースのクエリ(query、問合せ)の練習をしてみましょう。

まず、データを操作するための pandas(パンダズ)というライブラリを pd という省略名でインポートする決まり文句です:

import pandas as pd

この pd で定義されている read_csv() という関数でデータを読みます。データは Web で公開されている CSV ファイルを URL で指定します。このように Web で機械可読なデータをもらってくる仕組みを一般に Web API といいます。ここでは2022年1月1日現在の住民基本台帳に基づく都道府県別・男女別人口データを扱います(元データはe-Statの住民基本台帳に基づく人口、人口動態及び世帯数調査 / 調査の結果)。読み込んだデータを df という名前の変数(データフレーム)に格納します。2行目は単に df とだけ打ち込んで、データの中身を表示しています:

df = pd.read_csv("https://okumuralab.org/~okumura/stat/data/pop2022.csv")
df

Google Colab での表示は次のようになります:

Google Colab Interactive Table

右の鉛筆マークをクリックすると、interactive table(マウスで操作できるテーブル)になります。ここでは説明しませんが、興味があったら試してみてください。

ここでクエリを投げてみましょう。SQL なら select * from df where 都道府県='東京'; と打つべきところを、Python では次のようにします:

df.query("都道府県 == '東京'")

東京の行だけが表示されました。男女の人口だけを表示させたいなら、SQL では select 男, 女 from df where 都道府県='東京'; としますが、Python では次のように最後に [[ ]] で囲んで指定します:

df.query("都道府県 == '東京'")[["男", "女"]]

男女の人口はわかりましたが、合計の人口もほしいですね。「計」という欄を追加しましょう:

df["計"] = df["男"] + df["女"]

これで再度 df 全体を表示してみてもいいのですが、スペースを食いますので、頭の数件だけ表示してみましょう:

df.head()

以下でも、ときどき df.head() や、データの末尾を見る df.tail() をしてみるとわかりやすいと思います。

df["計"] = df["男"] + df["女"] と同じことをする別の方法として df = df.assign(計 = df["男"] + df["女"]) があります。df["計"] = ... は既存のデータに列を直接追加し、df.assign() は列を追加した新しいデータを作成します。

全国の合計も知りたいですね:

df[["男", "女", "計"]].sum()
男     61420626
女     64507276
計    125927902
dtype: int64

この時点での日本の人口は 125927902 人でした。男より女のほうが多いようですね。

都道府県別に男女比が知りたくなりました。「男女比」という欄を追加しましょう。

df["男女比"] = df["男"] / df["女"]

男が少ない都道府県、例えば男女比が 90% 未満のデータを抽出しましょう:

df.query("男女比 < 0.9")

andor も使えます。

df.query("男女比 < 0.9 and 計 >= 1000000")

抽出されたものは、元の順番(都道府県コード順)に並んでいます。これを男女比の昇順(小さい順)に並べ替えてみます:

df.query("男女比 < 0.9").sort_values("男女比")

これは次のように分けて書いてもかまいません:

df1 = df.query("男女比 < 0.9")
df1.sort_values("男女比")

降順(大きい順)にするには ascending=False というオプションを付けます:

df.query("男女比 < 0.9").sort_values("男女比", ascending=False)

データ全体を人口の合計でソートし、大きい順に並び替えて、頭の3行を表示しましょう:

df.sort_values("計", ascending=False).head(3)

もう一つデータを読み込んでみます。こちらは各都道府県の面積です:

df2 = pd.read_csv("https://okumuralab.org/~okumura/stat/data/area.csv")

dfdf2 をマージ(結合、join)したものを df に代入します:

df = pd.merge(df, df2)

人口密度を求めてみましょう:

df["人口密度"] = df["計"] / df["面積_km2"]

問題:人口密度の大きい順に並べて、上位10件(.head(10))、下位10件(.tail(10))を表示してください。

ここでは行の追加・変更(SQL の insert や update に相当すること)は行いませんでしたが、簡単な方法として、行をインデックス(表示した際に一番左に現れるもので、デフォルトでは 0 から始まる番号)で指定して追加・変更できます。追加なら例えば

df.loc[df.index.max() + 1] = [0, "全国", 61420626, 64507276]

のようにすればいいのですが、インデックスは番号でなくてもよく、例えば

df.loc["total"] = [0, "全国", 61420626, 64507276]

でも行が追加できます。既存のインデックスを指定すれば行を上書きできます。行の削除は

df.drop(インデックス, inplace=True)

でできます。なお、同じインデックスが複数あるようなデータフレームを作ることは可能です。その場合、上書きすると両方が変わります。インデックスはクエリで df.query("index == 3") または略して df.query("3") のように指定することもできます。


おまけ:水平棒グラフ(horizontal bar plot)を描いてみましょう(Google Colaboratory ならあらかじめ !pip install japanize-matplotlib しておきます。詳しくはプロット参照):

import matplotlib.pyplot as plt
import japanize_matplotlib       # Google Colabの場合

df3 = df.sort_values("人口密度")
plt.figure(figsize=(8, 10))
plt.barh(df3["都道府県"], df3["人口密度"])
plt.xlabel("人口密度(人/km2)")
都道府県の人口密度

男女比については、棒グラフではなく、「Cleveland(クリーブランド)のドットプロット」と呼ばれるグラフ(散布図の一種)が妥当です。次のように描きます:

df4 = df.sort_values("男女比")
plt.figure(figsize=(8, 10))
plt.scatter(df4["男女比"], df4["都道府県"])
plt.grid()
plt.xlabel("男女比")
都道府県の男女比