かもメモ

自分の落ちた落とし穴に何度も落ちる人のメモ帳

Google SpreadSheet 複数のカラムでグルーピングしたい。ついでに複数カラムで並び替えたい

例えば都道府県別の飲食店のリストみたいなものがあって、都道府県別のカテゴリー別で店舗数を出したいみたいな時、都道府県とカテゴリーと複数のカラムでグルーピングする必要があります。

シート名店舗リスト

A B C
1 店舗名 都道府県 カテゴリー
2 店舗 A 北海道 ラーメン
3 店舗 B 群馬 そば
4 店舗 C 群馬 そば
5 店舗 D 北海道 そば
6 店舗 E 香川 うどん
7 店舗 F 北海道 ラーメン
8 店舗 G 北海道 ラーメン
9 店舗 H 群馬 うどん
10 店舗 I 北海道 うどん
11 店舗 J 群馬 ラーメン
12 店舗 K 香川 うどん

このデータから都道府県別かつカテゴリー別の店舗数を出したい

QUERY は 複数カラムで GROUP BY できる

スプレッドシートでは GROUP BY A, B のように書かけば、A 列でグルーピングしたものの中で B 列の値でグルーピングすることができます。
今回の例では、都道府県とカテゴリーとでそれぞれ GROUP BY すれば良いので次のようなクエリを書けば OK

= QUERY('店舗リスト'!A:C, "SELECT B, C, count(A) WHERE A is not null GROUP BY B, C LABEL count(A) '店舗数'",1)

↓ 結果

A B C
1 都道府県 カテゴリー 店舗数
2 北海道 うどん 1
3 北海道 そば 1
4 北海道 ラーメン 3
5 群馬 うどん 1
6 群馬 そば 2
7 群馬 ラーメン 1
8 香川 うどん 2

₍ ᐢ. ̫ .ᐢ ₎ できた!

カテゴリーの順番がバラバラで見づらいのでいい感じにソートしたい…
カテゴリーの列 ORDER BY すると都道府県の並び順が崩れてしまいます。なので、都道府県で並び替えた上で、その中でカテゴリーで並び替えたい

ORDER BY も複数カラムで実行することができる

ORDER BY {カラム1} DESC|ASC, {カラム2} DESC|ASC

と指定することで、カラム1 で並び替えたものの中を、カラム2 で並び替えることができます。

今回は都道府県 (B列) で並び替えた後に、カテゴリー (C列) で並び替えるので ORDER BY B ASC, C DESC のように書けばOK!

先程の QUERY を次のように編集します

=QUERY('店舗リスト'!A:C, "SELECT B, C, count(A) WHERE A is not null GROUP BY B, C ORDER BY B ASC, C DESC LABEL count(A) '店舗数'",1)

↓ 結果

A B C
1 都道府県 カテゴリー 店舗数
2 北海道 ラーメン 3
3 北海道 そば 1
4 北海道 うどん 1
5 群馬 ラーメン 1
6 群馬 そば 2
7 群馬 うどん 1
8 香川 うどん 2

カテゴリーの並び順が揃って見やすくなりました!
ORDER BY で指定する順番を間違えると意図しない並び方になるので注意が必要です。
カンタンですね! ( ˙꒳​˙ᐢ ) ワーイ

スプレッドシートだとある程度のことは QUERY を使うと実現することができます。ちょっと難しそうかな〜ということも QUERY のネストや今回の複数カラムでの GROUP BY や ORDER BY を使うと意外とできちゃったりするので覚えておくと少し幸せになれます!

それではハッピースプレッドシートライフを! (おれは JavaScriptを書きたい…


知見が溜まってきた!

転生したらスプレッドシートだった件…タイトルすごすぎて興味出てきた…

GitHub remote branch が Repository not found になるにハマる

GitHub に新しくリポジトリを作って、README に出てくる通りローカルリポジトリの remote に登録して push しようとしたら Repository not found. なエラーになってしまったので解決方法のメモ。
と言っても状況はそれぞれのマシンの ssh の設定とかで変わるだろうからこれで解決することもあるよくらいのお気持ちで、どうぞよろしく

エラーになったパターン

GitHub に新しくリポジトリを作成した際に出てくる …or push an existing repository from the command line の方法に従って remote を追加しました。

$ git remote add origin https://github.com/KiKiKi-KiKi/something-my-project.git
$ git branch -M main
$ git push -u origin main
remote: Repository not found.
fatal: repository 'https://github.com/KiKiKi-KiKi/something-my-project.git/' not found

.git/config を見ても正しく remote は追加されていました。

[remote "origin"]
  url = https://github.com/KiKiKi-KiKi/something-my-project.git
  fetch = +refs/heads/*:refs/remotes/origin/*

😇 NANMO WAKARAN …

SSH じゃなくて HTTPS になっていた

よくよく見るとリポジトリのパスが SSHgit@github.com:〜 ではなく、HTTPShttps://github.com/〜 になっており、これを SSH のものにすると問題なく接続することができるようになりました。

追加していた HTTPS のリモートを削除する

# リモートリポジトリの確認
$ git remote -v
origin https://github.com/<USER NAME>/<REPO NAME>.git (fetch)
origin https://github.com/<USER NAME>/<REPO NAME>.git (push)
# origin で登録されているリモートリポジトリの削除
$ git remote rm origin
# 何も表示されないことを確認
$ git remote -v

SSH のリモートリポジトリを改めて登録する

$ git remote add origin git@github.com:<USER NAME>/<REPO NAME>.git
# fetch できれば OK
$ git fetch
From github.com:<USER NAME>/<REPO NAME>
 * [new branch]      main       -> origin/main

GitHubリポジトリ作った時に表示されるページのチェックボックスが普段は SSH だったのに何故か HTTPS になっており (気づかずクリックした可能性) それに気づいていなかった事が今回ハマった原因でした。
なぜか GitHub のリモートブランチへの接続が Repository not found になってしまった時は設定が HTTPS になっていないかチェックしてみると良いかもです!

おわり


映画大好きポンポさんの映画めちゃ良かった!
何かを選ぶということはその他の可能性を切り捨てること…

Google SpreadSheet UTF-8 な CSV をスプレッドシートで開いたら日本語が文字化けするときのTIPS

とある Database からダウンロードした CSVgoogle drive に上げてスプレッドシートで開いたら、日本語が入っていたカラムが � な文字化けになってしまいました。

CSV文字コードを確認する

まず疑うべきは CSV文字コードが特殊なものになっている可能性なので、文字コードが確認できるエディタで CSV を開き文字コードを確認します。
homebrew でファイルのエンコードを調べられる nkf をインストールして調べてみました。

$ brew install nkf
$ nkf -g './path/to/my.csv'
UTF-8

今回は UTF-8 になっており、エディタで CSV を開いても日本語は文字化けしていませんでした。
スプレッドシートに変換すると文字化けしてしまう…なぜ〜 🤔

CSV の先頭に日本語の文字を追加すると文字化けしなくなる

以前 Qiita で見た「UTF-8 のファイルなのに文字コードが Shift_JIS になっている」と指摘を受けた記事に 1バイト文字は ASCII と UTF-8 は互換性があるのでコンピューターからしたら見分けがつかないのようなことが書かれていたのを思い出しました。

今回はCSVがそれなりのサイズが合ったので、何らかの別のエンコードスプレッドシートに変換されている。もしかするとスプレッドシートに変換する際に先頭のいくらかだけを見てファイルのエンコードを決めて変換しているのかも?と思い CSV の先頭に日本語の文字を追加して見た所、google drive 上で CSV からスプレッドシートに変換しても日本語の文字化けは発生しなくなりました!

文字コード周りに詳しいわけではないので、先の仮説が正しいかは判断できませんが日本語が含まれる UTF-8CSVスプレッドシートに変換して文字化けが発生してしまう場合は、CSV ファイルの先頭に日本語の文字を追加したものを使うと上手く変換できるみたいです。

謎〜って言いながらハマりました。
バグ踏みの達人に俺はなる…


[参考]

これはチップス