よしたく blog

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

【Google Colab】ログインしたGCPからログアウトする

Google Colaboratory

Google ColabからGCPへログインした後、ユーザを切り替えたいなどの理由でログアウトしたいときに実行する方法です。

  1. gcloudのrevokeコマンドを使ってログアウトする
! gcloud auth revoke
  1. Google Colabのランタイムを終了させる
from google.colab import runtime
runtime.unassign()

yoshitaku-jp.hatenablog.com

【Google Colab】GCPへログインする

Google Colaboratory

Google ColabからGCPへアクセスしたい場合は、次のコードを実行するとGoogleアカウントへのログインフォームが出てきます。 ログインが完了すると、Google Clund SDKのリクエスト確認の画面へ移動し、「許可」をクリックします。 次に認証用コードが発行され、認証コードをGoogle Colabの「Enter Valification Code」の欄に入力をすることで、GCPとの連携が完了します。

from google.colab import auth 
auth.authenticate_user()

yoshitaku-jp.hatenablog.com

【Google Colab】%%writefile でセルの内容をファイルに書き出す

Google Colaboratory

セルの内容をファイルに書き出す

マジックコマンドの%%writefileを使うことでセルの内容を書き出す事ができる。

%%bash
ls

sample_data
%%writefile sample.txt
test1
test2
%%bash
ls

sample_data sample.txt
%%bash
cat sample.txt

test1 test2

セルの内容をファイルに追記

-aオプションを使うと、追記することができる

%%writefile -a sample.txt
test3

Appending to sample.txt
%%bash
cat sample.txt

test1 test2 test3

応用

ファイルに書き出した内容を呼び出すこともできるので、Pythonプログラムを書き出して別のノートブックから呼び出すと言ったこともできる。

%%writefile hello.py
print('Hello world')
%run hello.py

Hello world

%%writefile pi.py
import math
print(math.pi)
%run pi.py

3.141592653589793

【VS Code】Edit CSVを使って、ExcelライクにCSVを編集する

VS Code

VS Code拡張機能のEdit CSVがとても便利だったので紹介する。

概要

Edit CSVはVS codeの拡張機能で、CSVファイルをExcel操作のように編集することができる。

紹介ページにも、次のように書いてある。

This extensions allows you to edit csv files with an excel like table ui

この拡張機能を使用すると、Excel のようなテーブル UI を使用して csv ファイルを編集できます。

CSVの拡張機能としてはRainbow CSVが有名でとても見やすくなって重宝している。一方で、一部のデータが長かったり、項目が多すぎたりすると、対応している部分がわかりづらくなってしまう面もあり、そういったときにEdit CSVが便利だった。

使い方

Edit csvをインストールした後、CSVファイルをVS Codeで開くと右上に「Edit csv」が現れる。

クリックすると、「Edit csv」でCSVファイルを開ける。

別タブが展開され、Edit csv画面でCSVファイルが開ける。

編集

ここからは紹介ページにもあったように、ExcelのようなUIで操作ができる。12行目の値を左から順に2,い,11，bに置き換えてみた。一つ不満としては、今現在編集しているセルかどうか、どこにカーソルが合っているのかが分かりづらかったので、この部分は今後改善されると嬉しい。

この状態は、まだ編集状態なのでCSVファイル本体へ変更を適用してみる。

CSVファイルへ反映と保存

CSVファイルへの反映と保存は次の２つのボタンからできる。

  • Apply changes to file
  • Apply changes to file and save

Apply changes to fileがファイルへの反映のみで、Apply changes to file and saveがsaveとあるように反映と保存となっている。

ファイルへの反映 / Apply changes to file

まずはファイルへの反映をしてみる。 2,い,11，bとなっている状態でApply changes to fileをクリックしてみる。

12行目の赤枠の値が上書きされたことがわかる。 また、赤矢印が指すようにファイルが編集中で未保存となっていることもわかる。

ファイルへの保存 / Apply changes to file and save

ファイルへの反映と同じことをして保存してみる。 Apply changes to file and saveをクリックすると、CSVファイルに変更が反映され、またファイルも保存されている。その証拠として、未保存時のマークが消えていることがわかる。

その他の便利機能

ExcelのようにCSVを表示してデータを編集する以外に、ヘッダー有無での読み込み、行追加、列追加などができる。

Has header

Has headerにチェックを入れると、column1-4となっていた部分が、1行目の値になる。

ボタンをクリックするだけで切り替わり、ファイルの再読み込みが発生しないので気持ちがいい。

Add row / Add column

Add rowを押すと行が、Add columnを押すと列が追加される。 CSVデータへ追記したいときに便利！

Add row / Add columnのとなりにある矢印をクリックすると、現在カーソルが合っている部分の上下、左右に1行追加することができる。下の画像ではid列の1行目にカーソルが合っている状態で下と右を押して行列を追加した。

まとめ

VS Code拡張機能のEdit CSVがとても便利だったので紹介した。 VS Code上でCSVを編集するときに、とても便利だったので機会があれば試してほしい。

GROUP BYで範囲を集計する

SQL

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を使った文字の置換

Python

Python のtranslateメソッドを使うと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

プロジェクトの中で 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

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

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)