前回の記事までSQLでデータサイエンス100本ノックの問題を解いていこうと考えていたのですが、やっぱり方針変えてPythonも同時進行で進めていきたいと思います。
細かいPandasの文法は現時点では一つもわかりませんが、解答を写経しながら調べていきます。
まずJupyter Notebookからpsycopg2を使って以下のようにDBに接続します。
import os
import pandas as pd
import numpy as np
from datetime import datetime, date
from dateutil.relativedelta import relativedelta
import math
import psycopg2
from sqlalchemy import create_engine
from sklearn import preprocessing
from sklearn.model_selection import train_test_split
from imblearn.under_sampling import RandomUnderSampler
pgconfig = {
'host': 'db',
'port': os.environ['PG_PORT'],
'database': os.environ['PG_DATABASE'],
'user': os.environ['PG_USER'],
'password': os.environ['PG_PASSWORD'],
}
# pd.read_sql用のコネクタ
conn = psycopg2.connect(**pgconfig)
df_customer = pd.read_sql(sql='select * from customer', con=conn)
df_category = pd.read_sql(sql='select * from category', con=conn)
df_product = pd.read_sql(sql='select * from product', con=conn)
df_receipt = pd.read_sql(sql='select * from receipt', con=conn)
df_store = pd.read_sql(sql='select * from store', con=conn)
df_geocode = pd.read_sql(sql='select * from geocode', con=conn)
dictionary型のpgconfigに接続情報を格納していて、その接続情報はどこで定義しているのか探したところdocker-compose.ymlで以下のように定義されていました。
notebook:
build:
context: .
dockerfile: dockerfiles/notebook/Dockerfile
image: dss-notebook
container_name: dss-notebook
ports:
- "8888:8888"
environment:
- PG_PORT=5432
- PG_USER=padawan
- PG_PASSWORD=padawan12345
- PG_DATABASE=dsdojo_db
- JUPYTER_ENABLE_LAB=yes
おそらくdocker-compose.ymlで定義されてJupyter Notebookコンテナの環境変数のになり、os.environ[変数名]で取得できるようになっているはず。
そしてその環境変数をJupeyter Notebookで以下のようにpsycopg2のconnect関数に渡しています。
pgconfig = {
'host': 'db',
'port': os.environ['PG_PORT'],
'database': os.environ['PG_DATABASE'],
'user': os.environ['PG_USER'],
'password': os.environ['PG_PASSWORD'],
}
# pd.read_sql用のコネクタ
conn = psycopg2.connect(**pgconfig)
psycopg2のドキュメントは以下だと思います。
psycopg2もまだよくわかっていませんが、どりあえずPythonからPostgresqlを操作できるライブラリと認識しています。
psycopg2に関してはこちらの記事がわかりやすかったです。
DBに接続した後にpandasのread_sql関数を使ってデータを取得しています。
df_customer = pd.read_sql(sql='select * from customer', con=conn) df_category = pd.read_sql(sql='select * from category', con=conn) df_product = pd.read_sql(sql='select * from product', con=conn) df_receipt = pd.read_sql(sql='select * from receipt', con=conn) df_store = pd.read_sql(sql='select * from store', con=conn) df_geocode = pd.read_sql(sql='select * from geocode', con=conn)
pandasの公式ドキュメントに関数の説明がありました。
pandas.read_sql(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, columns=None, chunksize=None)
と書かれているので第三引数以降はオプションのようです。
また、以下の記載があったので返す値はデータフレームという型になっているようです。
Returns DataFrame or Iterator[DataFrame]
上記のコードを実行しPostgresqlに接続した後、以下のコードでデータを取得しました。
df_receipt.head(10)
head関数は以下の説明がありました。
デフォルトは5件になっているようで
-をつけると最後から指定した行だけを除いた全ての行を取得するようです。
df_reciptは104681件あり、head(-1)で104680件取得しています。
SQLで先に解いていた問題をPythonでも書いてみます。
SQLは自分でゼロから書いてみてそれが合ってるか解答と比べる感じで進めてましたが、pandasの構文は全然わからないので写経していきます。
S-002: レシート明細のテーブル（receipt）から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上金額（amount）の順に列を指定し、10件表示させよ。
上記のデータは以下のコードで取得できました。
df_receipt[['sales_ymd', 'customer_id', 'product_cd', 'amount']].head(10)
'sales_ymd', 'customer_id', 'product_cd', 'amount'ってという書き方がよくわからなかったので['sales_ymd', 'customer_id', 'product_cd', 'amount']にしていみると以下のようにKeyErrorになりました。
これはこういう書き方なんだなぐらいの認識で進めます。
P-003: レシート明細のデータフレーム（df_receipt）から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上金額（amount）の順に列を指定し、10件表示させよ。ただし、sales_ymdはsales_dateに項目名を変更しながら抽出すること。
上記のデータは以下のコードで取得できました。(\をつけて改行できるようです)
df_receipt[['sales_ymd', 'customer_id', 'product_cd', 'amount']]\
.rename(columns={'sales_ymd':'sales_date'}).head(10)
別名をつける構文はpandasだとrename関数を使う必要があるみたいなのでSQLの方がasをつけるだけでシンプルに書けそうだなと感じました。
