csv 轉檔萬用瑞士刀 miller

跟 AI 對話時學到 miller/mlr 這個工具： 用 apt show miller 查看它的簡介： 「這是專用於處理 csv 之類的檔案、 具有 sed, awk, cut, join, sort, ... 多種功能的工具。」 這超帥! 跟 json 轉檔萬用瑞士刀 jq 有點類似， 又有點互補。 好可惜網路上的教學文很少。

【例一： 太陽系天然衛星】

它支援的格式不只 csv， 還有 json、 yaml、 markdown 表格、 ... 等等， 詳見 mlr help file-formats 。 不過我最常用的就是 csv 跟 json。 那我們就先拿太陽系天然衛星列表 satellites.csv 來練習吧：

1. mlr --csv cat satellites.csv 原封不動列印。
2. mlr --csv sort -nr radius then head -n 10 satellites.csv 抓出太陽系前十大衛星。 其中的 "then" 就相當於 shell 底下的 | 。
3. mlr --csv filter '$planet=="Neptune"' satellites.csv 抓出海王星所有的衛星。 這個例子直接用 shell 底下的 grep 也是可以做得到啦， 畢竟 Neptune 必然出現在 planet 欄位。 但有時一個名字可能會同時出現在資料表裡的好幾個欄位， 例如同一位員工的名字可能同時出現在 「姓名」、 「直屬長官」、 「代理人」 三個不同的欄位裡， 那麼用 mlr 限定有興趣的欄位就比 shell 底下其他常用指令要簡單多了。
4. mlr --csv put '$ratio = ($orbit_major/1e3)**3/$rev_cycle**2' then sort -nf ratio satellites.csv 計算 R^3/T^2 (小數點移九位)、 產生一個新的欄位 "ratio"， 以便驗證 克卜勒第三定律： 圍繞著同一顆行星的所有衛星， 它們的這個比值算出來應該相同， 且與行星質量成正比。 計算結果感覺有點像， 但不太精準。 AI 說可能的原因包含： 維基百科的資料有效數字位數不多、 來自不同來源甚至年代、 外圍衛星受到太陽引力干擾較大、 乘方會讓誤差變大。

【例二： FindMind 台股資產負債表】

接下來， 我們模仿上一篇 「FinMind： 從命令列下載台股財報」 但是改下載 「資產負債表」 (-d dataset=TaiwanStockBalanceSheet) 取得 fm-5483-bs.json (中美晶過去幾年的資產負債表)。

Miller 比較適合處理表格， 不適合處理很深的結構。 所以先用 json 取出我們有興趣的陣列， 交由 miller 轉成 csv： jq .data fm-5483-bs.json | mlr --j2c cat > 5483.csv 可以先用 visidata 或其他試算表軟體查看一下 5483.csv 的長相。 這裡的 --j2c 等同於 --ijson --ocsv 也等同於 -i json -o csv； 完整列表請見： mlr help format-conversion-keystroke-saver-flags。

抓回來的是瘦瘦高高的表格， 但我想把它轉成矮矮胖胖的表格， 像這樣：

date	stock_id	現金及約當現金	透過損益按公允價值...	應收帳款淨額	...
2021-03-31	...	...	...	...	...
2021-06-30	...	...	...	...	...
2021-09-30	...	...	...	...	...

我不知道該如何描述這個動作， 於是問 AI。 綜合幾家 AI 的解釋： 「你要做的動作， 用精確術語來說， 是要以 date 與 stock_id 兩欄作為識別主鍵 (group keys / key fields)， 以 origin_name 欄的各個值作為新欄位名稱 (以 origin_name 欄作為轉置欄位 / 新欄位名來源 pivot key / variable field) 並以 value 欄作為新欄位值， 進行 (不帶彙整的 / non-aggregating) 長格式轉寬格式 (long-to-wide) 的 reshape / pivot 動作。」 AI 建議的指令：

mlr --csv \
  filter '$type !=~ "_per$"' \
  then cut -x -f type \
  then reshape -s origin_name,value \
  then unsparsify \
  5483.csv

首先看到 5483.csv 裡面有好幾組成對的 "type" 名稱： [CashAndCashEquivalents, CashAndCashEquivalents_per]、 [Inventories, Inventories_per] 每一組的中文名稱都相同， 但分別代表金額 vs 百分比。 所以我們要先把英文名稱當中以 "_per" 結尾的列通通刪除 (過濾) 掉。

再來要把不需要的欄位 (只有一個， type) 通通刪掉。

再來就是重點： 用 reshape -s 完成 「長=>寬」 的轉換， 新欄位名稱取自原來的 origin_name 欄， 新欄位值取自原來的 value 欄。 詳見 mlr help reshape。

因為每季財報的細項可能略有出入， 例如 「應付公司債」、 「合約負債」 並沒有出現在每一季的財報當中， 所以最後要用 unsparsify (補齊稀疏矩陣/缺漏欄位) 把欠缺的資料 (用空字串， 或其他指定的值) 補滿， 讓每一列的資料可以對齊。

以上的 filter、 cut、 reshape、 unsparsify 是 mlr 裡面的四個 "動詞" (verbs)。 每個 verb 都可用 mlr help ... 查詢詳細的用法。

【例三： 電力能源來源分類】

以前曾經遇到 需要做 unpivoting/melting 的情況， 現在就拿當初的原始資料 electricity-mix.csv 改用 mlr 重做一遍。 注意： 這是未經整理、 未變成 "leaves" 的資料， 所以無法直接套用到 2023 年那篇文章的流程裡面。 這裡只是為了介紹 mlr， 拿相同的原始資料來測試。

mlr --csv \
    reshape -r "Electricity from" -o source,value \
    then cut -f iso3,continent,Entity,Year,source,value \
    electricity-mix.csv > em-long.csv

這裡做的事跟上一節正好相反： 我們要把寬格式轉長格式 (wide-to-long reshape)。 原本的試算表當中， 凡是名稱內包含 "Electricity from" 的欄位， 通通都拉下來變成新欄位 "source" 的值， 而它對應到的數字則變成新欄位 "value" 的值。 最後的輸出只包含六個指定的欄位， 其他 (例如許多 "% electricity" 的欄位) 通通刪掉。

然後， 每一列重複出現的 "Electricity from " 跟 "(TWh)" 有點礙眼 ==> 刪掉! 可以用 gsub() 這個函數： mlr --csv put '$source = gsub($source, "^Electricity from | \(TWh\)", "")' em-long.csv 也可以用 gsub 這個 verb： mlr --csv gsub -f source "^Electricity from (.*) \(TWh\)" "\1" em-long.csv 對! miller 有一些支援正規表示式 (regular expressions) 的函數/指令。 這個例子也可以用 sed 完成， 不需要 mlr； 但有時候如果你只對某欄位底下的某字串有興趣， 不想誤抓其他欄位底下的同一字串， 那就要用 mlr 比較方便了。

【例四： 台灣各村里現住人口統計】

再來看一個統計摘要的例子： 114現住人口數按性別及出生地分。

原始資料 (叫它 pop.csv 好了) 長這樣：

統計年,區域別代碼,區域別,村里名稱,性別,出生地,人口數
114,65000010001,新北市板橋區,留侯里,男,本國_新北市,373
114,65000010001,新北市板橋區,留侯里,男,本國_臺北市,196
114,65000010001,新北市板橋區,留侯里,男,本國_桃園市,25
...
114,66000260002,臺中市霧峰區,吉峰里,女,大陸地區,42
114,66000260002,臺中市霧峰區,吉峰里,女,港澳地區,4
114,66000260002,臺中市霧峰區,吉峰里,女,東南亞地區,65
...
114,64000160001,高雄市大社區,嘉誠里,男,本國_雲林縣,4
114,64000160001,高雄市大社區,嘉誠里,男,本國_嘉義縣,9
114,64000160001,高雄市大社區,嘉誠里,男,本國_屏東縣,9

想要的輸出長這樣：

區域別代碼,縣市,區域別,村里名稱,村里人口
65000010001,新北市,板橋區,留侯里,xxxx
66000260002,臺中市,霧峰區,吉峰里,yyyy
64000160001,高雄市,大社區,嘉誠里,zzzz
...

AI 給的指令長這樣：

mlr --csv \
    stats1 -a sum -f 人口數 -g 區域別代碼,區域別,村里名稱 \
    then put '$縣市=substr($區域別,0,2); $區域別=substr($區域別,3,6); $村里人口=$人口數_sum' \
    then cut -o -f 區域別代碼,縣市,區域別,村里名稱,村里人口 \
    pop.csv

這裡的 stats1 可以計算某欄位的總和、 最大、 最小、 ... 等等彙整數據 (aggregate data)。 此處我們選擇對 "人口數" 欄位加總 (-a sum)； 又用 -g 指定 group-by 欄位。 如果只用 -g 村里名稱 統計結果相同， 但輸出當中就不會有 "區域別代碼" 跟 "區域別" 欄位。 然後用 put 建立新欄位： 以 substr 從 「區域別」 欄位分別抓出縣市和鄉鎮兩欄。 最後， 用 cut 輸出時， -o 選項要求 mlr 按照 cut 命令列上的欄位順序 (而非按照原始資料的欄位順序) 輸出。

【心得】

我的工作生涯有超多處理試算表的場合 (學生成績、地圖資訊、資料視覺化、股票、...)， 而我又偏好 .csv 勝過 .odt。 真希望當初早一點知道有 mlr/miller 這個工具! 有它再搭配 visidata， 我可能就會... 更不熟悉 LibreOffice calc 吧! 😅