よしたく blog

ITエンジニアとして自分が知らなかったことをまとめています

GROUP BYで範囲を集計する

SQL の GROUP BY を使うとカラムのデータごとに集計できる。 例えば次のようなデータがあり、GROUP BY を使って SQL を実行する。

実行環境はsqlfiddleで、MySQL5.6を使った。

sqlfiddle.com

データはテストデータ生成サイトで作った。 https://tm-webtools.com/Tools/TestData

野尻 成美, 48
杉江 絢子, 40
古畑 詩音, 47
沢井 金之助, 44
大前 伊都子, 22
大隅 一行, 34
今津 陽菜子, 2
東 遥華, 29
河村 一平, 35
堀川 乃愛, 57

age で GROUP BY すると次のようになり、当然だがデータごとに GROUP BY されるのでデータとしてはバラバラになる。 今回は 10 代、20 代、30 代...と、一定の範囲で GROUP BY したいときにどうするかをメモする。

SELECT
  age,
  count(*) as 人数
FROM
  Table1
GROUP BY
  age
ORDER BY
  age;
age 人数
2 1
22 1
29 1
34 1
35 1
40 1
44 1
47 1
48 1
57 1

1, CASE で分ける

まずは CASE 文を使って分ける方法がある。 考え方含めとてもシンプルに実行できる。

SELECT
  CASE
    WHEN age < 10 THEN '10代以下'
    WHEN age BETWEEN 10
    AND 19 THEN '10代'
    WHEN age BETWEEN 20
    AND 29 THEN '20代'
    WHEN age BETWEEN 30
    AND 39 THEN '30代'
    WHEN age BETWEEN 40
    AND 49 THEN '40代'
    WHEN age >= 50 THEN '50代以上'
    ELSE NULL
  END AS 年代,
  count(*) as 人数
FROM
  Table1
GROUP BY
  CASE
    WHEN age < 10 THEN '10代以下'
    WHEN age BETWEEN 10
    AND 19 THEN '10代'
    WHEN age BETWEEN 20
    AND 29 THEN '20代'
    WHEN age BETWEEN 30
    AND 39 THEN '30代'
    WHEN age BETWEEN 40
    AND 49 THEN '40代'
    WHEN age >= 50 THEN '50代以上'
    ELSE NULL
  END
ORDER BY
  age;
年代 人数
10 代以下 1
20 代 2
30 代 2
40 代 4
50 代以上 1

2, 計算をしてグループを作り出す

仮に年代を CASE 文で書くと、最大でも 10 代から 100 代までとなるが、もっと範囲が多いものになると辛くなる。 そういった場合は計算を使ってグループを作り出したほうが便利になる。

今回の年代のケースだと 10 の位があれば分類できるので、年齢を 10 で割り FLOOR で小数点になった 1 の位を切り落とし、GROUP BY している。 SELECT 文では# 1, CASE で分けると表記を合わせるために工夫しているが、基本的には次のようになる。

SELECT
  CONCAT(
    CASE
      FLOOR(age / 10) * 10
      WHEN 0 THEN 10
      ELSE FLOOR(age / 10) * 10
    END,
    '代'
  ) AS 年代,
  COUNT(*) AS 人数
FROM
  Table1
GROUP BY
  FLOOR(age / 10)
ORDER BY
  age;
年代 人数
10 代 1
20 代 2
30 代 2
40 代 4
50 代 1

Pythonのtranslateを使った文字の置換

Pythontranslateメソッドを使うとmaketransメソッドから生成される変換表を元に文字を置換できる。

適当な文字列を作成した。日本語の文章にカンマ、ピリオドなどが入っており、これを句読点に置き換えてみる。

greeting = '''
***
おはようございます,本日は下記を対応します.

 - 書類提出
 - ドキュメント読み込み
 - サンプル作成

 今日も、よろしくお願いいたします.
***
***
おはようございます、本日は下記を対応します。

 ・ 書類提出
 ・ ドキュメント読み込み
 ・ サンプル作成

 今日も、よろしくお願いいたします。
***

maketrans の第 3 引数にも指定する

文頭と文末に*が入っていて不要に見えるので、この置換作業の中で削除してみる。 maketransメソッドの 3 つ目の引数に指定して、削除されることも確認する。

greeting = '''
***
おはようございます,本日は下記を対応します.

 - 書類提出
 - ドキュメント読み込み
 - サンプル作成

 今日も、よろしくお願いいたします.
***
'''

print(greeting.translate(str.maketrans('.,-','。、・','*')))
おはようございます、本日は下記を対応します。

 ・ 書類提出
 ・ ドキュメント読み込み
 ・ サンプル作成

 今日も、よろしくお願いいたします。

maketrans の指定文字を片方の引数だけ増やしてみる

第 2 引数に#を追加すると、第 1 引数との数が一致していないのでエラーになる。

greeting = '''
***
おはようございます,本日は下記を対応します.

 - 書類提出
 - ドキュメント読み込み
 - サンプル作成

 今日も、よろしくお願いいたします.
***
'''

print(greeting.translate(str.maketrans('.,-', '。、・#', '*')))
      1 greeting = '''
      2 ***
      3 おはようございます,本日は下記を対応します.
   (...)
     10 ***
     11 '''
---> 13 print(greeting.translate(str.maketrans('.,-', '。、・#', '*')))

ValueError: the first two maketrans arguments must have equal length

SQLをlintする SQLFluff に入門した

プロジェクトの中で SQL を使い ETL 処理を書いていたが、個人によって書き方にばらつきがあり、他人の SQL を確認したりメンテナンスするタイミングがとてもつらかった。 SQL もフォーマッタや linter を使って、少しでも作業しやすくしたいと思って探していたところ、SQL Fluff に出会うことができた。 今回は SQL Fluff に入門する!

docs.sqlfluff.com

インストールと確認

SQL Fluff は pip でインストールできる。

pip install sqlfluff

sqlfluff --versionで無事にインストールされていることを確認する。 2022/08/17 時点でのバージョンは1.2.1となっていることがわかる。

sqlfluff, version 1.2.1

チュートリアル

チュートリアルに載っている SQL を lint してみる。

SELECT a+b  AS foo,
c AS bar from my_table

無事に lint が実行された!

$ sqlfluff lint test.sql --dialect ansi
== [test.sql] FAIL
L:   1 | P:   1 | L034 | Select wildcards then simple targets before calculations
                       | and aggregates.
L:   1 | P:   1 | L036 | Select targets should be on a new line unless there is
                       | only one select target.
L:   1 | P:   9 | L006 | Missing whitespace before +
L:   1 | P:   9 | L006 | Missing whitespace after +
L:   1 | P:  11 | L039 | Unnecessary whitespace found.
L:   2 | P:   1 | L003 | Expected 1 indentations, found 0 [compared to line 01]
L:   2 | P:  10 | L010 | Keywords must be consistently upper case.
All Finished 📜 🎉!

DeepL で翻訳した結果も載せておく。

計算と集計の前にワイルドカードを選択し、次に単純なターゲットを選択します。
選択対象は、選択対象が1つでない限り、改行されるべきです。
の前に空白がない
の後に空白がない
不要な空白が見つかりました。
1つのインデントが予想されますが、0が見つかりました[01行目と比較して]。
キーワードは一貫して大文字でなければなりません。

指摘事項を修正して、再度 lint してみる。

SELECT
    c AS bar,
    a + b AS foo
FROM my_table

指摘されることがなく、無事に lint が通った!

$ sqlfluff lint test.sql --dialect ansi
All Finished 📜 🎉!

Pandasでインデックス指向のJSONを読み込む

Pandas で JSON の key となる部分がインデックス指向*1となっているJSONを読み込ませたい場合、read_JSON()関数にorient='index'オプションを設定すればうまくいく。

pandas.pydata.org

サンプルデータ

{
  "0": {
    "name": "Nieves Finch",
    "gender": "male",
    "company": "XYQAG"
  },
  "1": {
    "name": "Frank Francis",
    "gender": "male",
    "company": "QUIZKA"
  },
  "2":{
    "name": "Erna Nieves",
    "gender": "female",
    "company": "ISOPLEX"
  }
}

まずはorient='index'オプション無しで読み込む

s = '''{
  "0": {
    "name": "Nieves Finch",
    "gender": "male",
    "company": "XYQAG"
  },
  "1": {
    "name": "Frank Francis",
    "gender": "male",
    "company": "QUIZKA"
  },
  "2":{
    "name": "Erna Nieves",
    "gender": "female",
    "company": "ISOPLEX"
  }
}
'''

df = pd.read_json(s)
df

0,1,2 をインデックスとしたいが、name, gender, companyがインデックスになっている。 これを読み込みの段階から正しくなるようにする。

 0   1   2
name    Nieves Finch    Frank Francis   Erna Nieves
gender  male    male    female
company XYQAG   QUIZKA  ISOPLEX

サンプルコード

s = '''{
  "0": {
    "name": "Nieves Finch",
    "gender": "male",
    "company": "XYQAG"
  },
  "1": {
    "name": "Frank Francis",
    "gender": "male",
    "company": "QUIZKA"
  },
  "2":{
    "name": "Erna Nieves",
    "gender": "female",
    "company": "ISOPLEX"
  }
}
'''

df = pd.read_json(s, orient='index')
df

無事に読み込む形が変わった!

 name    gender  company
0   Nieves Finch    male    XYQAG
1   Frank Francis   male    QUIZKA
2   Erna Nieves female  ISOPLEX

*1:インデックス指向のJSONという表現には若干違和感があったけど、Pandasではこのように表現するのが適切そうなので、このまま表記してみる

chunksizeを指定して、pandasで巨大ファイルを扱う

pandas でサイズの大きなファイルを扱うにはどうすればいいか調べたところ、ファイルを読み込む際に chunksize 引数を指定して、分割して読み込む方法があることを知った。 Google clab にあるサンプルファイルを使って動きを確認する。

まずは何も指定せずファイルを読み込む。

import pandas as pd

df = pd.read_csv('sample_data/mnist_test.csv')
df.shape

結果的に、9999 行と 785 列を保持していることがわかった。

(9999, 785)

今読み込んだファイルを分割して読み込む。 pd.read_csv関数のオプションへ chunksize 引数に読み込む行数を指定して実行する。

import pandas as pd

for df in pd.read_csv('sample_data/mnist_test.csv', chunksize=512):
  print(df.shape)

ループの中でdf.shapeを実行していて、その結果が出力されている。 最後を除くループで(512, 785)が出力されており、無事に分割しながら読み込めていることがわかる。

(512, 785)
(512, 785)
(512, 785)
(512, 785)
(512, 785)
(512, 785)
(512, 785)
(512, 785)
(512, 785)
(512, 785)
(512, 785)
(512, 785)
(512, 785)
(512, 785)
(512, 785)
(512, 785)
(512, 785)
(512, 785)
(512, 785)
(271, 785)

pipenvでpipenv install throws --system is intended to be used for pre-existing Pipfile installationが発生したときの解決方法

pipenvを触り始めて試行錯誤していたら環境を壊してしまったようで、同名のフォルダを再作成してもうまくいかず、pipenv install throws --system is intended to be used for pre-existing Pipfile installationが発生した。

まずはpipenv --venvで仮想環境の一覧を確認し、壊してしまった仮想環境のパスを確認する。

その後、rm -rf {{パス}}を実行すれば、同名のフォルダで再作成することができた。

Pythonの%timeで出力される値の見方

Pythonの%timeで出力される値の見方を調べたので、メモしておく。

yoshitaku-jp.hatenablog.com

CPU times: user 14 µs, sys: 0 ns, total: 14 µs
Wall time: 19.1 µs

と表示されているとき、 Wall time: 19.1 µsは全体の実行時間を表している。 CPU times: user 14 µsはユーザスペースでのCPU実行時間を表している。 sys: 0 nsカーネルスペースでのCPU実行時間を表している。 total: 14 µsはユーザスペースとカーネルスペースでのCPU実行時間の合計を表している。

Jupyter Notebookでコードの速度を計測するtimeとtimeit

Jupyte Notebook でコードの速度を計測する方法にマジックコマンドとして用意されている、timetimeitを使う方法がある。 この 2 つの存在は知っていたが、

  • time と timeit で何が違うのか
  • %の数が 1 つのときと 2 つのときで何が違うのか

がわからなかったので、確認してみた!

%time と %%time

%time

%time は 1 行毎の処理時間を 1 回計測する。 例で上げたコードは、ループの中で%timeを使っているので、出力結果には 10 回分の結果が表示されている。

dist = []
for i in range(10):
    %time dist.append(i ** 2)
CPU times: user 7 µs, sys: 1e+03 ns, total: 8 µs
Wall time: 13.4 µs
CPU times: user 7 µs, sys: 0 ns, total: 7 µs
Wall time: 10.5 µs
CPU times: user 5 µs, sys: 0 ns, total: 5 µs
Wall time: 8.58 µs
CPU times: user 6 µs, sys: 1 µs, total: 7 µs
Wall time: 11.2 µs
CPU times: user 6 µs, sys: 0 ns, total: 6 µs
Wall time: 9.78 µs
CPU times: user 5 µs, sys: 0 ns, total: 5 µs
Wall time: 9.3 µs
CPU times: user 5 µs, sys: 0 ns, total: 5 µs
Wall time: 9.3 µs
CPU times: user 4 µs, sys: 0 ns, total: 4 µs
Wall time: 6.68 µs
CPU times: user 3 µs, sys: 0 ns, total: 3 µs
Wall time: 5.72 µs
CPU times: user 4 µs, sys: 0 ns, total: 4 µs
Wall time: 5.72 µs

結果の見方についてはこちら

yoshitaku-jp.hatenablog.com

%%time

次に%が 2 つになった%%timeの動きを確認する。%%time は セルの処理時間を 1 回計測する。

%%time
dist = []
for i in range(10):
    dist.append(i ** 2)
CPU times: user 14 µs, sys: 0 ns, total: 14 µs
Wall time: 19.1 µs

%timeit と %%timeit

%timeit

%timeit は 1 行毎の処理時間を 複数回計測し平均処理時間を表示する。

%timeit [x ** 2 for x in range(10000)]
%timeit [x ** 2 for x in range(10000)]
%timeit [x ** 2 for x in range(10000)]
100 loops, best of 5: 2.77 ms per loop
100 loops, best of 5: 2.74 ms per loop
100 loops, best of 5: 2.74 ms per loop

%%timeit

次に%が 2 つになった%%timeitの動きを確認する。%%timeit は セルの処理時間を 複数回計測し平均処理時間を表示する。

%%timeit
[x ** 2 for x in range(10000)]
[x ** 2 for x in range(10000)]
[x ** 2 for x in range(10000)]
100 loops, best of 5: 8.39 ms per loop

実行回数の変更方法

timeitはデフォルトで 100 回ループしてくれる。 この回数を変更するためには-n オプションで回数を指定する。

%%timeit -n 1000
[x ** 2 for x in range(10000)]
[x ** 2 for x in range(10000)]
[x ** 2 for x in range(10000)]
1000 loops, best of 5: 8.34 ms per loop

さらにrで繰り返す回数も変更できる。

%%timeit -n 100 -r 10
[x ** 2 for x in range(10000)]
[x ** 2 for x in range(10000)]
[x ** 2 for x in range(10000)]
100 loops, best of 10: 8.25 ms per loop

まとめ

まとめると次のようになる。

Pandasで欠損値を埋めるための方法

Pandasで欠損値を埋めるための方法をいくつか確認したのでまとめておく。fillna関数を使うと引数に渡した値で、NaNを埋められる。

まずは適当にNaNを含んだデータを生成する。

import numpy as np
import pandas as pd

df = pd.DataFrame(data=[1,2,4,np.nan,5,8,7,np.nan], columns=['col_nan'])
df
index col_nan
0 1.0
1 2.0
2 4.0
3 NaN
4 5.0
5 8.0
6 7.0
7 NaN

ゼロで埋める

fillna関数を使うと引数に渡したもので、NaNを埋められる。 fillna(0)とするとNaNをゼロ埋め、fillna(5)とするとNaNを5で埋めてくれる。

df['col_fill_0'] = df.col_nan.fillna(0)
df
index col_nan col_fill_0 col_fill_mean
0 1.0 1.0 1.0
1 2.0 2.0 2.0
2 4.0 4.0 4.0
3 NaN 0.0 4.5
4 5.0 5.0 5.0
5 8.0 8.0 8.0
6 7.0 7.0 7.0
7 NaN 0.0 4.5

平均値で埋める

mean()関数を使って列の平均値を求め、その平均値を使ってNaNを埋めることができる。

df['col_fill_mean'] = df.col_nan.fillna(df.col_nan.mean())
df
index col_nan col_fill_0 col_fill_mean
0 1.0 1.0 1.0
1 2.0 2.0 2.0
2 4.0 4.0 4.0
3 NaN 0.0 4.5
4 5.0 5.0 5.0
5 8.0 8.0 8.0
6 7.0 7.0 7.0
7 NaN 0.0 4.5

前後の値を元に補間で埋める

補間という言葉を知らなかったが、調べたら次のように出てきた。

補間(ほかん)とは、ある既知の数値データ列を基にして、そのデータ列の各区間の範囲内を埋める数値を求めること、またはそのような関数を与えること。

interpolate関数を使うと、前後の値を元に補間してくれる。

df['col_fill_interpolate'] = df.col_nan.fillna(df.col_nan.interpolate())
df
index col_nan col_fill_0 col_fill_mean col_fill_interpolate
0 1.0 1.0 1.0 1.0
1 2.0 2.0 2.0 2.0
2 4.0 4.0 4.0 4.0
3 NaN 0.0 4.5 4.5
4 5.0 5.0 5.0 5.0
5 8.0 8.0 8.0 8.0
6 7.0 7.0 7.0 7.0
7 NaN 0.0 4.5 7.0

まとめ

今後使えそうな、NaNを埋める方法を3つ確認しておいた。なかなかゼロ埋めを多用することはなさそうなので、平均値か補間を使うことになりそう。ただ、補間は並び方でも偏りが出そうなので気をつけたい。

PythonのCounterクラスを使って要素の出現回数を集計する

Pythonでは、よく使われる組み込みコンテナの dictlistsettuple に代わる特殊なコンテナがあり、collectionsモジュールにまとまっている。今回はその中にある辞書型のサブクラスであるCounterを使ってリストなどの要素の出現回数を数え、簡単に集計をしてみる。

まずは、Counterを使わなかった場合の実装になる。 printと空行を除けば、7行での実装になる。

programming_languages = ['Python', 'Ruby', 'Go','Python','JavaScript', 'Go','Python','TypeScript','Python','TypeScript']

count = {}
for pl in programming_languages:
  if pl in count:
    count[pl] += 1
  else:
    count[pl] = 1    
print(count)

Counterを使うと下記のようになる。 printと空行を除けば、3行での実装になり、シンプルになった。

from collections import Counter

programming_languages = ['Python', 'Ruby', 'Go','Python','JavaScript', 'Go','Python','TypeScript','Python','TypeScript']

count = Counter(programming_languages)
for k, v in count.items():
    print(k + ':' + str(v))

返ってくるのが辞書型なので、items()などのメソッドを使うこともできる!