🍖

特定の写真の撮影日を集計した話

2022/08/10に公開

jpeg

Exif

googlespreadsheets

tech

👺 < 鱗滝さん！ (CV:大塚芳忠さん)

概要

ホルモンセンター天狗家 が大好きで、定期的に友人と行っている。最近では、仕事終わりに高速を飛ばしたり、1人で行ったり、2日連続で食べに行ったりと、 ~~ちょっと~~ 通う頻度が増えている。

実際のところ、頻度はどんな感じなのだろう？

行く度に写真は撮っているので、その写真をもとに頻度を確認してみた。
焼肉集計を口実に、集計方法の備忘録です。

結論

予想以上に増えていた。

今年上半期で、前年度にリーチかかっていて、更に7月は5回というアタオカ。

少しでもお店の売上に貢献できていたら何より。

集計環境

Windows 10 Pro
Google Photos
https://photos.google.com
Stylus
https://add0n.com/stylus.html
exiftool
https://exiftool.org/
Google Spreadsheets
https://docs.google.com/spreadsheets/

すまない。

GAEやGASなどといったクラウドプラットフォーム上での集計じゃなく、ローカルでの作業なんだ。。。
(だれか、Google Photoの内容を集計するのに適したプラットフォームと手法があったら教えて下しあ。。。)

集計方法

STEP⓪ 手順のサマリー

手順をざっくりサマると。

Google Photos で一括ダウンロード用にアルバム作成する。
アルバムを一括ダウンロードする。
ZIPを解凍する (言わずもがな)。
exiftool で JPEG (🔊 じぇいぴーいーじー) 写真のメタ情報を収集する。
(主に写真など)
exiftool で PNG (🔊 ぴーえぬじー) 写真画像のメタ情報を収集する。
(主に写真やスクリーンショットなど)
収集した写真・画像の情報を Google Spreadsheets で集計する。

といった感じ。

※ PNG写真と言う馬鹿げたものが存在しているのかについては後述。私のやらかしによる、特異なファイルです。

STEP① Google Photosのアルバム整理

(1-1) アルバムを作成して、該当写真だけにまとめる。

該当写真の識別用にアルバムを作成して、天狗屋の焼肉写真のリストを作ります。

しかし、このアルバムでの選択UIでのハイライトが非常に弱く、繰り返し探していても、すでに追加済みなのかなどの視認性が悪い。なので、 Stylus の UserCSS で適当にハイライトを再定義する。

選択済み写真の強調用CSS

/** 選択写真全般の背景色を定義. */
div.rtIMgb.nV0gYe.GQzRlf.WjVZdb.R1sU4e {
    background-color:#00FF00 !important;
}

/** 今回選択した写真の背景色を再定義. */
div.rtIMgb.nV0gYe.GQzRlf.WjVZdb.R1sU4e.N4EBib {
    background-color:#FF0000 !important;
}

コレくらい露骨だと、繰り返し作業がしやすくて良い！👍

(1-2) Google Photosのアルバムをダウンロード。

アルバムのメニューの「すべてダウンロード」から、ZIPファイルでダウンロードする。

(1-3) ZIPファイルを解凍する。

ダウンロードしたZIPファイルを展開解凍する。
タイムスタンプを見ると、属性情報は使い物にならないことがわかる。
そのため、撮影日はExifないしはファイル名からの推察となる。

STEP② exiftool で情報収集

(2-0) exiftool のインストール。

コマンドでExif情報を出力するのに、exiftool を利用する。

https://exiftool.org/

から、 Windows Executable: exiftool-**.**.zip をダウンロード。
任意の場所に展開解凍し、exiftool(-k).exe を exiftool.exe にリネームする。

また、

https://exiftool.org/gui/

から ExifToolGUI をダウンロードする。

exiftool と同じフォルダに、同じフォルダに展開解凍する。

※ ExifToolGUI は必須ではないが、あるとちょっとした確認作業で便利になるかもしれない。

(2-1) exiftool で JPEG (🔊 じぇいぴーいーじー) 写真のメタ情報を収集する。

JPEG (🔊 じぇいぴーいーじー) 写真から撮影日、などの情報を収集する。

属性	内容	(例)	備考
DateTimeOriginal	撮影日時 (？)	2022:08:10 20:05:01
Model	撮影機種	SO-02K
ImageWidth	画像幅 (px)	720	多分Exif属性じゃない
ImageHeight	画像幅 (px)	1280	多分Exif属性じゃない

この辺の情報があれば、集計には色々と活用できそう (今回は全く使わなかったけど)。また、Exifの形式の日付書式では不便のため。DateFmt関数にてYY/MM/DD hh:mm:ss 書式で出力させる。

取れない属性があると何も取れないので、JPEG形式単独で走らせる。exiftool はワイルドカードにも対応しているので、FORで回すよりも高速に処理してくれる。

あとエラーが割りとうっせぇので、標準エラーは nul に捨てる。

sample command

@REM 出力
exiftool -p "${filename} ${dateTimeOriginal;DateFmt('%Y/%m/%d %H:%M:%S')} ${model} ${ImageWidth} ${ImageHeight}" "*.jpg" 2> nul

@REM クリップ ボードへ出力
exiftool -p "${filename} ${dateTimeOriginal;DateFmt('%Y/%m/%d %H:%M:%S')} ${model} ${ImageWidth} ${ImageHeight}" "*.jpg" 2> nul | CLIP

コレをSpreadsheetsの新しいシートに貼っておく。

(B列に貼り付け。A列はその後に番号付けをしただけ)

(2-2) exiftool で PNG (🔊 ぴーえぬじー) 写真画像のメタ情報を収集する。

PNG画像にはExif情報はないのだけど。exiftool ではいくつかの情報については出力が可能なので、その情報を収集する。

属性	内容	例	備考
~~DateTimeOriginal~~	n/a	n/a	撮影日はファイル名などから類推するしかない。
~~Model~~	n/a	n/a
ImageWidth	画像幅 (px)	720	PNGでも取れる
ImageHeight	画像幅 (px)	1280	PNGでも取れる

取れない属性があると何も取れないので、PNG形式も単独で走らせる。

sample command

@REM 出力
exiftool -p "${filename} ${ImageWidth} ${ImageHeight}" "*.png" 2> nul

@REM クリップ ボードへ出力
exiftool -p "${filename} ${ImageWidth} ${ImageHeight}" "*.png" 2> nul | CLIP

コレもSpreadsheetsの新しいシートに貼っておく。

(B列に貼り付け。A列はその後に番号付けをしただけ)

STEP③ Spreadsheetsで情報の整形

(3-1) JPEGのExif情報を整形。

(3-1-1) 出力テキストをSPLIT。

SPLIT関数でスペースで分割することで、それぞれの属性が取得出来る。配列関数のため、自動でセルに展開してくれるので、今回はそれをそのまま応用する。

=SPLIT(B2, " ")

(3-1-2) 日付情報を更にSPLIT。

更にSPLIT関数にて、日付をスラッシュで分割する。

=SPLIT(D2,"/")

そもそも、下手にスラッシュ区切りの書式にせず、スペース区切りの書式にしたほうが楽だったんじゃね？🤔

(3-1-3) 抽出用情報に整形する。

後にJPEG情報とPNG情報を結合する際に、列が合っている方が都合が良いため、抽出用として情報を整形する。

列	内容	式	備考
A列目	index	(n/a)	連番。特に用途なし
B列目	ファイル名	`=J2`	突合用
C列目	年月日	`=DATE(P2,Q2,R2)`	集計抽出用 (重み付けに利用)
D列目	年	`=P2`	集計抽出用 (突合用)
E列目	月	`=Q2`	集計抽出用 (突合用)
F列目	日	`=R2`	集計抽出用 (今回は利用なし)
G列目	解像度	`=N2 & "x" & O2`	集計抽出用 (今回は利用なし)
H列目	機種	`=M2`	集計抽出用 (今回は利用なし)

(3-2) PNGの情報を整形。

(3-2-1) 出力テキストをSPLIT。

こちらも同様にSPLIT関数でスペースで分割する。配列関数のため、隣のセルに展開される。

=SPLIT(B2, " ")

(3-2-2) ファイル名をSPLIT。

ファイル名から日時情報を抽出するため、更にファイル名をSPLITする。SPLIT関数に指定する区切り文字は、文字列の文字いずれかで分割してくれるので、今回は、アンダースコア ( _ )、ドット ( . )、ハイフン ( - ) のいずれかで区切ることにする。

=SPLIT(C2, "_.-")

今回はG列に日にちが来そうなのでコレを利用する。

(3-2-3) 年月日情報を抽出。

上記G列に日にちが集中している様だったが、正常に日にちが取れないものもある。情報の整合性のため、8文字未満は一様にエラーになる様に、ワンクッション入れ、先頭から年・月・日を抽出する。

列	式	備考
J列	`=IF(LEN(G2) >= 8, G2, NA())`	8文字未満はエラーにする。
K列	`=VALUE(MID(J2, 1, 4))`	J列から抽出して数値化。
L列	`=VALUE(MID(J2, 5, 2))`	J列から抽出して数値化。
M列	`=VALUE(MID(J2, 7, 2))`	J列から抽出して数値化。

(3-2-4) 抽出用情報に整形する。

こちらもJPEGシート同様に、後にJPEG情報とPNG情報を結合するため、抽出用に情報が同じ列に来る様に整形をする。

列	内容	式	備考
A列目	index	(n/a)	連番。特に用途なし
B列目	ファイル名	`=J2`	突合用
C列目	年月日	`=DATE(R2,S2,T2)`	集計抽出用 (重み付けに利用)
D列目	年	`=R2`	集計抽出用 (突合用)
E列目	月	`=S2`	集計抽出用 (突合用)
F列目	日	`=T2`	集計抽出用 (今回は利用なし)
G列目	解像度	`=K2 & "x" & L2`	集計抽出用 (今回は利用なし)
H列目	機種	"不明"	PNGでは機種は判定できないため、固定値 (今回は利用なし)

(3-3) 集計用に情報を1シートに結合する。

(3-3-1) 対象となる全ファイル名を収集する。

ファイル名を起点に、JPENシート、PNGシートの情報を結合するため。一度新しいシートに全ファイル名の一覧を作る。

sample command

@REM 出力
dir /b | sort

@REM クリップ ボードへ出力
dir /b | sort | CLIP

これをSpreadsheetsの新しいシートに貼る。

(B列に貼り付け。A列はその後に番号付けをしただけ)

(3-3-2) MATCH関数で行番号を収集する。

複数のシートから情報を集めるため、ファイル名を基にどのシートの何行目から情報を取ってくるかを調べる必要があり、まずシート毎に行番号を調査する。

列	1行目 (シート名)	式
C列	JPEG	`=MATCH($B2, INDIRECT(C$1& "!B:B"), 0)`
D列	PNG	`=MATCH($B2, INDIRECT(D$1& "!B:B"), 0)`

基本的にはいずれか一方のシートに存在しているはず。
なければそのままエラー。

(3-3-3) シート名と行番号を解決する。

シート毎に行番号を調査した結果から、参照すべきシートと行番号を解決する。

列	内⁠容	式	備考
E⁠列	シ⁠ー⁠ト⁠名	`=IF(NOT(ISERROR(C2)), C$1, IF(NOT(ISERROR(D2)), D$1, NA()))`	エラーの無い列のシート名 (1行目) を採択する。愚直に求める。
F⁠列	行⁠番⁠号	`=SUM(ARRAYFORMULA(IFERROR(C2:D2, 0)))`	ARRAYFORMULA関数を用いて、IFERROR関数でエラーを0に丸めることで算術演算を可能にし、SUM関数でindexを求める。

MAX関数ではなくSUM関数なのは、条件分岐より加算の方がCPUコストが小さいと思われるため。

(3-3-4) 各シートから情報を収集する。

INDIRECT関数で文字列から動的に参照範囲を生成できる。また各シートは整形作業により、参照範囲が固定化できているので、解決したシート名で範囲を生成する。それをINDEX関数を用いて、解決した行番号を参照する。列の順番を変更する必要がないため、列番号は順番に横着できる。

列	内容	式
G列	ファイル名	`=INDEX(INDIRECT($E2 & "!B:H"), $F2, 1)`
H列	年月日	`=INDEX(INDIRECT($E2 & "!B:H"), $F2, 2)`
I列	年	`=INDEX(INDIRECT($E2 & "!B:H"), $F2, 3)`
J列	月	`=INDEX(INDIRECT($E2 & "!B:H"), $F2, 4)`
K列	日	`=INDEX(INDIRECT($E2 & "!B:H"), $F2, 5)`
L列	解像度	`=INDEX(INDIRECT($E2 & "!B:H"), $F2, 6)`
M列	機種	`=INDEX(INDIRECT($E2 & "!B:H"), $F2, 7)`

(3-3-5) 日付ごとに重みを設定。

1日複数枚写真を取ることがあるが、それを 1回として勘定したい。そのため、写真1枚あたりの重みを設定する。

写真1枚の重み = \frac{1}{同日の撮影枚数}

この重みを集計することで、合算で1日を1回として勘定出来る様になる。

=1/COUNTIF(H:H, H2)

STEP④ 月別に訪店日数を集計

(4-1) 集計用Matrix表の雛形を作成。

月ごとの訪店状況を、複数年にわたって集計したいため、年x月のMatrix表が見やすいと思った。
今回年と月のフィールドは数値にしているため、こちらの表上のキャプションも数値にしておく。必要があれば、後で書式で調整を行う。

(4-2) SUMPRODUCTで範囲集計。

SUMPRODUCT関数は同じ要素数の配列を行ごとに積を求め、その和を計算してくれる。そのため、条件式を加えた配列を追加すると、行フィルターとしても機能させることが出来る。

ExcelではBOOL値の配列だとSUMPRODUCT関数が機能しないため、* 1 して数値配列化させる。また、Spreadsheetsではそのまま配列に条件式を加えられないため、ARRAYFORMULA関数を用いてフィルター配列を作成する。エラーのセルもあるため、IFERRORも行い、エラーをFALSEに丸める。

SUMPRODUCTで集計

=SUMPRODUCT(
  ARRAYFORMULA(IFERROR(mix!$I:$I = $B3, FALSE)) * 1,
  ARRAYFORMULA(IFERROR(mix!$J:$J = C$2, FALSE)) * 1,
  mix!$N:$N
)

👏😃👏👏😃👏👏😃👏

(SUMPRODUCTの動作イメージ)

なんでフィルターが先やねん分かりづらいやろがい！🤬 と作ってて思ったけど。まぁ、乗算は交換法則が成り立つので許してほしい。

あと、SUMPRODUCT自体もある程度配列操作を許容している様で、* 1 のいちにブレがあるのはご愛嬌。

(4-3) 月ごとの合計

サルワカ

=SUM(C3:N3)

(4-4) 総合計

サルワカ

=SUM(O3:O14)

(4-5) お好みで書式設定

単位をつけたり、条件付き書式で0回は薄くしたりするなどすると、少し見やすくなるかと。またカラースケールも利用してみると、頻度の強弱が少し視認しやすいかも。

あとがき

最近Excel (Spreadsheets) でSPIL関数や配列操作にハマっていて。特に SUMPRODUCT と ARRAYFORMULA はとても便利で色々と使える。

具体的な活用事例として参考になったら幸い。

だけど、こんなのローカルでやるこっちゃ無いんだよなぁ、、、ほんま。

_(┐「ε:)_

Q&A

Q. Google Photosが無制限じゃなくなったのに、なんで未だに使ってんの？

A. そもそも Google Photosが無制限だった と思っている方が情弱。使い道の無い機能がなくなって、他のサービスと同様になっただけ。

Google Photosで無制限だったのは、バックアップ時に「高画質」に指定していた場合。この場合、画質は再圧縮により劣化する。

バックアップする上で大事なのは情報の欠落がないこと。極論を言えばbit perfectであることが必要。メタデータの多少の違いは合っても、バイナリに変化があるものが、バックアップになり得るだろうか？バイナリにダメージがあるものが、バックアップになり得るだろうか？

すなわち、「高画質」の選択肢は元々無意味なものだった。バックアップ用途であれば「元の画質」一択なのは考えるまでもない。そして、Google Photos自体がよもや写真のバックアップのためのサービスだろう。だから、そのバックアップ足り得ない機能が無くなっただけの話。

なので、無制限でなくなったどうのこうので騒ぐ意味がわからない。だって、「元の画質」に対してのサービスは何も変わってないのだから。

Androidでの自動同期が非常に便利なので、Google Photosは大変便利です。

Q. PNG写真？馬鹿じゃないの？

A. _(:3」∠)_
純正のカメラアプリで選べる解像度がどれもデカすぎて。サードパーティのカメラアプリを使っているんだけど。保存形式になぜかPNGを知らん間に選んだまま、しばらく使ってしまっていたのが原因です。。。そんな間抜けは世界中探しても、私しかいないだろう。。。

Q. HEICは？

A. 🤔 知らない子ね。。。

Q. 写真の解像度小さくね？

A. 10MPとか、普段使いには超ミラクルウルトラハイパー全く要らない解像度。

5年くらい前までは VGA (640x480) で撮ってたんだけど。初めてXPERIAにしたときに、選択肢が無くて適当に使ってたら、1枚の写真の解像度がデカすぎて、容量喰いすぎて、Google Driveの残量5GBを3ヶ月で喰い潰されて発狂した。容量が10倍以上差があってあり得なかった。

以後は、OpenCamera

を使って、1280x720のHD程度で撮影している。1MPもありゃ十分じゃろ？

正直カメラ性能で競っている昨今のスマホ市場はピーキー過ぎてアタオカと言わざるを得ない。トレードオフが悪すぎるんよ。
(´・ω・`)

Q. UTC時間のファイル名大丈夫？

A. 天狗家は0時以降やっていないのと、日本は+9時間先行しているので日付を跨ぐことはないので、一応問題なし。標準時の西側だったら、ちょっと怖かったかも。

と言うかSpreadsheetsの関数でそこまで考えるは辛い。。。

Q. ピボットテーブルで良くね？

A. 存在しない値 (年・月) も含めた表の作り方がわかりません。
😭😭😭😭😭😭😭😭😭😭😭😭

Q. 小数の集計とか、誤差怖くないの？

A. 超怖い。。。😱 正直、なんで誤差が出ていないのか、よくわかんない(爆)。ただ、今回知りたい結果が得られてはいるし、論理的には一応間違えていないから、良いかな、と。

Q. GCP (GAE / GAS) 使わないの？

A. チャレンジしたい。。。やり方わかんないけどチャレンジしたい。。。

Q. 天狗屋美味しいの？

A. 美味しいよ。かなり並ぶけど。。。

謝辞 (参考)

ExifTool by Phil Harvey
https://exiftool.org/
ExifTool Command-Line Examples
https://exiftool.org/examples.html
exiftool Application Documentation
https://exiftool.org/exiftool_pod.html
exiftool Application Documentation#Advanced formatting feature
https://exiftool.org/exiftool_pod.html#Advanced-formatting-feature
Rename and sort
https://exiftool.org/forum/index.php?topic=9540.0
ExifToolのかんたんなメモ – DigitalBoo
https://www.digitalboo.net/post/7518/a-note-about-exiftool

👺 < 鱗滝さん！ (CV:大塚 芳忠さん)

概要

結論

集計環境

集計方法

STEP⓪ 手順のサマリー

STEP① Google Photosのアルバム整理

(1-1) アルバムを作成して、該当写真だけにまとめる。

(1-2) Google Photosのアルバムをダウンロード。

(1-3) ZIPファイルを解凍する。

STEP② exiftool で情報収集

(2-0) exiftool のインストール。

(2-1) exiftool で JPEG (🔊 じぇい ぴー いー じー) 写真のメタ情報を収集する。

(2-2) exiftool で PNG (🔊 ぴー えぬ じー) 写真 画像のメタ情報を収集する。

STEP③ Spreadsheetsで情報の整形

(3-1) JPEGのExif情報を整形。

(3-1-1) 出力テキストをSPLIT。

(3-1-2) 日付情報を更にSPLIT。

(3-1-3) 抽出用情報に整形する。

(3-2) PNGの情報を整形。

(3-2-1) 出力テキストをSPLIT。

(3-2-2) ファイル名をSPLIT。

(3-2-3) 年月日情報を抽出。

(3-2-4) 抽出用情報に整形する。

(3-3) 集計用に情報を1シートに結合する。

(3-3-1) 対象となる全ファイル名を収集する。

(3-3-2) MATCH関数で行番号を収集する。

(3-3-3) シート名と行番号を解決する。

(3-3-4) 各シートから情報を収集する。

(3-3-5) 日付ごとに重みを設定。

STEP④ 月別に訪店日数を集計

(4-1) 集計用Matrix表の雛形を作成。

(4-2) SUMPRODUCTで範囲集計。

(SUMPRODUCTの動作イメージ)

(4-3) 月ごとの合計

(4-4) 総合計

(4-5) お好みで書式設定

あとがき

Q&A

Q. Google Photosが無制限じゃなくなったのに、なんで未だに使ってんの？

Q. PNG写真？馬鹿じゃないの？

Q. HEICは？

Q. 写真の解像度小さくね？

Q. UTC時間のファイル名大丈夫？

Q. ピボット テーブルで良くね？

Q. 小数の集計とか、誤差怖くないの？

Q. GCP (GAE / GAS) 使わないの？

Q. 天狗屋美味しいの？

謝辞 (参考)

Discussion

👺 < 鱗滝さん！ (CV:大塚芳忠さん)

(2-1) exiftool で JPEG (🔊 じぇいぴーいーじー) 写真のメタ情報を収集する。

(2-2) exiftool で PNG (🔊 ぴーえぬじー) 写真画像のメタ情報を収集する。

Q. ピボットテーブルで良くね？