ColaboratoryでSQL:PostgreSQL

この記事はPostgreSQL Advent Calendar 2022の六日目です。

以前の記事は @fujii_masao さんの記事になります。

はじめに

で使用されているPostgreSQL

SQL(PostgreSQL)を勉強するために、psql-basicsとか、オンラインでできるところもあります。が、少し画面が狭いです。

今回は使いなれているGoogle Colaboratoryでやってみようと思います。

コード

postgres_sql.ipynb

%%capture

# https://www.postgresql.org/download/linux/debian/

!sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
!wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
!sudo apt-get update
!sudo apt-get -y install postgresql-15
!sudo service postgresql start

# Setup a password `postgres` for username `postgres`
!sudo -u postgres psql -U postgres -c "ALTER USER postgres PASSWORD 'postgres';"

# Setup a database with name `tutorial` to be used
!sudo -u postgres psql -U postgres -c 'DROP DATABASE IF EXISTS tutorial;'
!sudo -u postgres psql -U postgres -c 'CREATE DATABASE tutorial;'

# set connection
%env DATABASE_URL=postgresql://postgres:postgres@localhost:5432/tutorial
#To load the sql extention to start using %%sql
%load_ext sql
%config SqlMagic.autopandas = True

!sudo apt-get -y install postgresql をそのままやると、colaboratoryはバージョン１２をダウンロードします。
せっかくなので、最新版をダウンロードしたいので、ホームページのやり方をそのまま踏襲しています。

パスワードの設定等については、google colaboratoryのチュートリアルから持ってきました。

データの入力

# https://github.com/simongeek/PandasDA

!wget -P /tmp/ https://raw.githubusercontent.com/simongeek/PandasDA/master/weather.csv

import pandas as pd
df = pd.read_csv('/tmp/weather.csv')

# Make variables some friendlier names for users
old_names = ['Max TemperatureF', 'Min TemperatureF', 'Mean TemperatureF', 'Max Dew PointF', 'MeanDew PointF',
             'Min DewpointF', 'Max Humidity',
             ' Mean Humidity', ' Min Humidity', ' Max Sea Level PressureIn', ' Mean Sea Level PressureIn',
             ' Min Sea Level PressureIn', ' Max VisibilityMiles', ' Mean VisibilityMiles',
             ' Min VisibilityMiles', ' Max Wind SpeedMPH', ' Mean Wind SpeedMPH', ' Max Gust SpeedMPH', 'PrecipitationIn',
             ' CloudCover', ' WindDirDegrees', ' Events']
new_names = ['max_Temp', 'min_Temp', 'mean_Temp', 'max_Dew', 'mean_Dew', 'min_Dew', 'max_Hum', 'mean_Hum', 'min_Hum', 'max_Press',
             'min_Press', 'mean_Press', 'max_Vis', 'mean_Vis',
             'min_Vis', 'max_Wind', 'mean_Wind', 'max_Gust', 'preIn', 'cloud', 'Wind_Dir', 'events']
df.rename(columns=dict(zip(old_names, new_names)), inplace=True)

df.columns = [c.lower() for c in df.columns] # PostgreSQL doesn't like capitals or spaces

df['prein']=df['prein'].mask(df['prein']=='T',0.01) # Digitize T of PrecipitationIn

from sqlalchemy.types import Integer,Text,Numeric,SmallInteger,Date

table_dict={'zip': Text(),
 'cloud': SmallInteger(),
 'wind_dir': SmallInteger(),
 'index': SmallInteger(),
 'max_temp': SmallInteger(),
 'mean_temp': SmallInteger(),
 'min_temp': SmallInteger(),
 'max_dew': SmallInteger(),
 'mean_dew': SmallInteger(),
 'min_dew': SmallInteger(),
 'max_hum': SmallInteger(),
 'mean_hum': SmallInteger(),
 'min_hum': SmallInteger(),
 'max_press': Numeric(3,1),
 'min_press': Numeric(3,1),
 'mean_press': Numeric(3,1),
 'max_vis': Numeric(3,1),
 'mean_vis': Numeric(3,1),
 'min_vis': Numeric(3,1),
 'max_wind': SmallInteger(),
 'mean_wind': SmallInteger(),
 'max_gust': Numeric(3,1),
 'pdt': Date(),
 'prein': Numeric(4,3),
 'events': Text()}

from sqlalchemy import create_engine

engine = create_engine('postgresql://postgres:postgres@localhost:5432/tutorial')

df.to_sql("weather", engine,dtype=table_dict, index=False)

読み込み方についてはCan I automatically create a table in PostgreSQL from a csv file with headers?
から。

データはPandas + Seaborn tutorial on Weather data for San Francisco Bay Area in Californiaから。

単純にto_sql()で読み込むとデータ型がすごくおかしくなってしまったので、修正してみました。

データ型の修正の仕方

# https://stackoverflow.com/questions/109325/postgresql-describe-table

%%sql
select column_name, data_type, character_maximum_length, column_default, is_nullable
from INFORMATION_SCHEMA.COLUMNS where table_name = 'weather';

このクエリーでpsql -c "\d weather"と同じ内容がでるので、data_typeの値をみて、
The Type Hierarchy - SQLAlchemy

pandas.DataFrame.to_sql
の二つをみて、修正しました。

テスト

%%sql
select * from weather limit 10;

うまくいきました。

psqlについて

!sudo -u postgres psql -U postgres -d tutorial
で一応psqlのプロンプトは出るけど、入力値は見えないし、簡易的にしか使えなかったです。

なんかいい方法があるのかな

まとめ

とりあえずは、準備よしです。

あとは、いろいろな本をみながらやっていきましょう。

おまけ

の演習で使用する郵便番号データ。

pdfをコピペするとずれるので。

zip.ipynb

!wget --quiet  http://www.post.japanpost.jp/zipcode/dl/kogaki/zip/ken_all.zip

!unzip ken_all.zip

%%sql
DROP TABLE IF EXISTS zip;

CREATE TABLE zip (
lgcode char(5), 
oldzip char(5),
newzip char(7),
prefkana text,
citykana text,
areakana text,
pref text,
city text,
area text,
largearea integer,
koaza integer,
choume integer,
smallarea integer,
change integer,
reason integer
);

-- https://www.postgresql.jp/document/9.3/html/sql-copy.html
COPY zip FROM '/content/KEN_ALL.CSV' (FORMAT csv, ENCODING 'sjis');

確認

%%sql

select * from zip
where newzip between '1000000' and '2000000' limit 5;

綺麗にできました。

おまけ　その２

せっかくAdvent Calenderに乗っけるのでもう少しデータが欲しくなった。
気象庁のデータがあったので、やってみたら・・・・orz

データと加工と取り込み

colaboratoryで引き続き

# データの入手
#　昭和26年（1951年）以降の梅雨入りと梅雨明け（確定値）
# https://www.data.jma.go.jp/cpd/baiu/index.html

!wget "https://www.data.jma.go.jp/cpd/baiu/tsuyu_iriake.csv"

# csvはsjisだし、ヘッダーは２重になっているので、そのまま取り込めないのでpandasにお任せ
import pandas as pd

df = pd.read_csv('tsuyu_iriake.csv', encoding='sjis',header=[0,1])

# https://stackoverflow.com/questions/45670981/reading-csv-with-sparsely-labeled-column-headers-using-pandas

df.columns = pd.MultiIndex.from_arrays(
    [df.columns.get_level_values(0).to_series().mask(lambda x: x.str.startswith('Unnamed')).ffill(), df.columns.get_level_values(1)]
)

# 上のやり方で列の名前を整える
# そのあと、データベースに取り込む時のデータ型を整えるために平均値の行を削除する。
df = df[1:]

# すごい汚くなってしまったけど、地方毎ループさせて、コラムを２つ作っている。
ddf=pd.DataFrame()
for i in df.columns.get_level_values(level=0).drop_duplicates()[1:]:
    _df = df[i]
    col_name = i + "_入り"
    ddf[col_name]= df.iloc[:,0].astype('str')+"-"+_df.iloc[:,0].astype('str')+"-"+_df.iloc[:,1].astype('str')
    col_name = i + "_開け"
    ddf[col_name]= df.iloc[:,0].astype('str')+"-"+_df.iloc[:,2].astype('str')+"-"+_df.iloc[:,3].astype('str')

# 取得できていない月には-9999が入っているので、全部削除して時間に変換
sql_data = ddf.replace("^.*9999.*$","",regex=True).apply(lambda x: pd.to_datetime(x,infer_datetime_format=True))

# 用意しているデータベースに入れ込む
from sqlalchemy import create_engine
from sqlalchemy.types import Date, DateTime

engine = create_engine('postgresql://postgres:postgres@localhost:5432/tutorial')


sql_data.to_sql("梅雨", engine, index=False, if_exists='replace', dtype={k: Date() for k in sql_data.columns})

確認

%%sql

select * from 梅雨 limit 5;

なんとかなった。

確認

%%sql
select extract(YEAR FROM 沖縄地方_入り) as 年, 沖縄地方_開け - 沖縄地方_入り as 期間
from 梅雨 ORDER BY 期間 desc
limit 5;

	年	期間
0	1962	60
1	1982	60
2	1975	59
3	2021	59
4	1995	58

沖縄は梅雨が長いですね。

でも、実際どこが一番ながかったんだろう

梅雨の期間を確認してみる

%config SqlMagic.autopandas = False

%%sql
SELECT concat('SELECT ''',
B.column_name,
''' AS 列名, extract(YEAR FROM ',
column_name,
') AS 年,',
column_name,
' AS 日付 FROM 梅雨 UNION ALL') FROM 
(select column_name
from INFORMATION_SCHEMA.COLUMNS
where table_name = '梅雨') AS B;

で抽出文を作成してコピペと少し修正

%%sql

CREATE TABLE 梅雨_t
AS
SELECT '沖縄地方_入り' AS 列名, extract(YEAR FROM 沖縄地方_入り) AS 年,沖縄地方_入り AS 日付 FROM 梅雨 UNION ALL
SELECT '沖縄地方_開け' AS 列名, extract(YEAR FROM 沖縄地方_開け) AS 年,沖縄地方_開け AS 日付 FROM 梅雨 UNION ALL
SELECT '奄美地方_入り' AS 列名, extract(YEAR FROM 奄美地方_入り) AS 年,奄美地方_入り AS 日付 FROM 梅雨 UNION ALL
SELECT '奄美地方_開け' AS 列名, extract(YEAR FROM 奄美地方_開け) AS 年,奄美地方_開け AS 日付 FROM 梅雨 UNION ALL
SELECT '九州南部_入り' AS 列名, extract(YEAR FROM 九州南部_入り) AS 年,九州南部_入り AS 日付 FROM 梅雨 UNION ALL
SELECT '九州南部_開け' AS 列名, extract(YEAR FROM 九州南部_開け) AS 年,九州南部_開け AS 日付 FROM 梅雨 UNION ALL
SELECT '九州北部地方（山口県を含む）_入り' AS 列名, extract(YEAR FROM 九州北部地方（山口県を含む）_入り) AS 年,九州北部地方（山口県を含む）_入り AS 日付 FROM 梅雨 UNION ALL
SELECT '九州北部地方（山口県を含む）_開け' AS 列名, extract(YEAR FROM 九州北部地方（山口県を含む）_開け) AS 年,九州北部地方（山口県を含む）_開け AS 日付 FROM 梅雨 UNION ALL
SELECT '四国地方_入り' AS 列名, extract(YEAR FROM 四国地方_入り) AS 年,四国地方_入り AS 日付 FROM 梅雨 UNION ALL
SELECT '四国地方_開け' AS 列名, extract(YEAR FROM 四国地方_開け) AS 年,四国地方_開け AS 日付 FROM 梅雨 UNION ALL
SELECT '中国地方_入り' AS 列名, extract(YEAR FROM 中国地方_入り) AS 年,中国地方_入り AS 日付 FROM 梅雨 UNION ALL
SELECT '中国地方_開け' AS 列名, extract(YEAR FROM 中国地方_開け) AS 年,中国地方_開け AS 日付 FROM 梅雨 UNION ALL
SELECT '近畿地方_入り' AS 列名, extract(YEAR FROM 近畿地方_入り) AS 年,近畿地方_入り AS 日付 FROM 梅雨 UNION ALL
SELECT '近畿地方_開け' AS 列名, extract(YEAR FROM 近畿地方_開け) AS 年,近畿地方_開け AS 日付 FROM 梅雨 UNION ALL
SELECT '東海地方_入り' AS 列名, extract(YEAR FROM 東海地方_入り) AS 年,東海地方_入り AS 日付 FROM 梅雨 UNION ALL
SELECT '東海地方_開け' AS 列名, extract(YEAR FROM 東海地方_開け) AS 年,東海地方_開け AS 日付 FROM 梅雨 UNION ALL
SELECT '関東甲信地方_入り' AS 列名, extract(YEAR FROM 関東甲信地方_入り) AS 年,関東甲信地方_入り AS 日付 FROM 梅雨 UNION ALL
SELECT '関東甲信地方_開け' AS 列名, extract(YEAR FROM 関東甲信地方_開け) AS 年,関東甲信地方_開け AS 日付 FROM 梅雨 UNION ALL
SELECT '北陸地方_入り' AS 列名, extract(YEAR FROM 北陸地方_入り) AS 年,北陸地方_入り AS 日付 FROM 梅雨 UNION ALL
SELECT '北陸地方_開け' AS 列名, extract(YEAR FROM 北陸地方_開け) AS 年,北陸地方_開け AS 日付 FROM 梅雨 UNION ALL
SELECT '東北南部_入り' AS 列名, extract(YEAR FROM 東北南部_入り) AS 年,東北南部_入り AS 日付 FROM 梅雨 UNION ALL
SELECT '東北南部_開け' AS 列名, extract(YEAR FROM 東北南部_開け) AS 年,東北南部_開け AS 日付 FROM 梅雨 UNION ALL
SELECT '東北北部_入り' AS 列名, extract(YEAR FROM 東北北部_入り) AS 年,東北北部_入り AS 日付 FROM 梅雨 UNION ALL
SELECT '東北北部_開け' AS 列名, extract(YEAR FROM 東北北部_開け) AS 年,東北北部_開け AS 日付 FROM 梅雨;

そして

%%sql

SELECT A.地方, A.年, B.日付 開け日付, A.日付 入り日付, (B.日付-A.日付) 日数
FROM
(select split_part("列名",'_',1)  地方, split_part("列名",'_',2)  入り開け,年, 日付
from 梅雨_t
WHERE split_part("列名",'_',2)='入り') A
JOIN
(select split_part("列名",'_',1)  地方, split_part("列名",'_',2)  入り開け,年, 日付
from 梅雨_t
WHERE split_part("列名",'_',2)='開け') B
ON A.地方=B.地方 AND A.年=B.年
ORDER BY 日数 desc;

地方	年	開け日付	入り日付	日数
九州南部	1954	1954-08-01	1954-05-13	80
九州北部地方（山口県を含む）	1954	1954-08-01	1954-05-13	80
関東甲信地方	1963	1963-07-24	1963-05-06	79
九州南部	1956	1956-07-13	1956-05-01	73
奄美地方	2010	2010-07-15	2010-05-06	70

昔は九州の梅雨が長かったみたいです。

内部結合を使わないでやってみた。

%%sql

SELECT 地方,日付 as 入り, 開け, 期間
FROM
(select split_part("列名",'_',1)  地方,年, 日付,split_part("列名",'_',2) as 入り開け,
lag(日付) OVER (PARTITION BY split_part("列名",'_',1), 年 ORDER BY split_part("列名",'_',2) desc) as 開け,
(lag(日付) OVER (PARTITION BY split_part("列名",'_',1), 年 )  - 日付) as 期間 
from 梅雨_t
order by 地方, 年,split_part("列名",'_',2)) as T
WHERE 期間 IS NOT NULL
ORDER BY 期間 desc
limit 10;

windows関数を使ってみた。
aliasが途中で使えないので、みやすくないですね。