用 TSV 而不用 CSV

最近常常需要提供資料給其他部門 (非技術類的部門),有時候需要提供一些表格類的資料,傳統大多數人比較熟的是產生 CSV 格式的資料讓使用者可以用 Excel 打開,但這個格式其實有很多問題,最常見的就是 encoding 與資料有逗號 comma 的問題。

如果是在 Python 下,其中一個解法是用 openpyxl 直接產生 .xlsx,但用起來還是沒那麼有下面提到的方法順手。

如果是 shell script 時就比較麻煩了,像我這次手上有一堆影片檔,要用 FFmpeg 確認每個影片的 resolution 與 framerate 再提供給同事,這時候如果還是想產生 .xlsx 就累了...

下面要提到的解法好像記得是在 K 社的時候同事教的,用 TSV 檔格式 (當然檔名要取 .tsv),然後 encoding 用 UTF-16 (LE) 就可以解決上面提到的兩個問題,產生出來的檔案可以讓 Excel 直接打開。

StackOverflow 上的「Is it possible to force Excel recognize UTF-8 CSV files automatically?」這邊翻一翻,會發現裡面提到比較好的解法其實都是產生 TSV。

這邊另外推薦,就算是寫程式,也還是可以先產生出 UTF-8 的版本 (通常副檔名我都會先取 .txt),然後用 iconv 或是 piconv 轉成 UTF-16 (LE):

iconv -f utf8 -t utf16le a.txt > a.tsv

包到 Makefile 裡面用起來其實還蠻順手的...

在 Terminal 看資料的 VisiData

在「VisiData」這篇看到的專案,專案的頁面在「A Swiss Army Chainsaw for Data」這邊,從 screenshot 可以看出來是 terminal 的檢視工具:

會注意到是因為支援 .xls

explore new datasets effortlessly, no matter the format: vd foo.json bar.csv baz.xls

SUPPORTED SOURCES 這邊可以看到完整的支援清單,居然連 pcap 也支援,不知道看起來如何 :o

微軟在考慮讓 Excel 支援 Python...

在「Excel team considering Python as scripting language: asking for feedback」這邊看到微軟正在考慮要不要讓 Excel 支援 Python,出自 UserVoice 上的:「How can we improve Excel for Windows (Desktop Application)?」。

比較感覺到有可能性應該是因為微軟做了一個問卷收集資訊:「Python and Excel」。

不過本來的功能就已經可以用到很出神入化了... XD (想到最近提到的「LINE 將內部的座位表由 Excel 改成 Web 界面...」)

LINE 將內部的座位表由 Excel 改成 Web 界面...

LINE 將內部的座位表由 Excel 管理,改用 Web 界面了:「Excel管理の座席表をLeafletでWeb化した話」,這邊不確定是全球的 LINE,還是只有日本的 LINE...

如果跟日本人有過業務合作的話,就會知道他們對 Excel 的用法只能用

出神入化

來形容啊... 所以看到 LINE 特地寫了一篇來說明他們開發內部系統的事情,覺得還蠻有趣的...

起因是今年四月換辦公室,所以就順便換系統,把本來用 Excel 管理的座位表改用 Web 管理 (然後用了 Leaflet 這個 JavaScript Library):

人員の増加に対応するために、今年の4月、LINEはJR新宿ミライナタワーに移転しました。移転に伴い、IT支援室ではいくつかの新しい社内システムを導入しましたが、今日はその1つである「座席表」についてお話させていただきます。

這是 Excel 版本的樣子:

這是新版本的樣子,UI 上有更多互動的界面可以操作:

然後文末提到了總務業務量減少,而且因此變更座位變自由了而大受好評 (大概是不會讓總務煩死,所以就可以更自由換來換去 XDDD):

今回開発した座席表は総務の業務軽減に始まったプロジェクトでした。そして実際に導入後には、座席表の管理にかけていた総務の業務を大幅に削減することに成功しました。また、利用者からもかなり好評で、「これを待っていたんですよ!」といった声もあり、社内コミュニケーションの円滑化に一役買うことができているようです。誰の席でも自由に変更できるという点についても、これまでのところトラブルの報告を受けることなく運用できています。

翻了一下英文版的 blog,好像沒有提到這件事情?XDDD

對於按讚數排名的方法

前幾天看到一篇 2009 年的老文章,在討論使用者透過「喜歡」以及「不喜歡」投票後,要怎麼排名的方法:「How Not To Sort By Average Rating」。

基本的概念是當使用者投票數愈多時就會愈準確,透過統計方法可以算一個信賴區間,再用區間的下限來排... 但沒想到公式「看起來」這麼複雜 XDDD

Score = Lower bound of Wilson score confidence interval for a Bernoulli parameter

但實際的運算其實沒那麼複雜,像是 Ruby 的程式碼可以看出大多都是系統內的運算就可以算出來。其中的 z 在大多數的情況下是常數。

require 'statistics2'

def ci_lower_bound(pos, n, confidence)
    if n == 0
        return 0
    end
    z = Statistics2.pnormaldist(1-(1-confidence)/2)
    phat = 1.0*pos/n
    (phat + z*z/(2*n) - z * Math.sqrt((phat*(1-phat)+z*z/(4*n))/n))/(1+z*z/n)
end

The z-score in this function never changes, so if you don't have a statistics package handy or if performance is an issue you can always hard-code a value here for z. (Use 1.96 for a confidence level of 0.95.)

作者後來在 2012 年與 2016 年也分別給了 SQL 以及 Excel 的範例程式碼出來,裡面 hard-code 了 95% 信賴區間的部份:

SELECT widget_id, ((positive + 1.9208) / (positive + negative) - 
                   1.96 * SQRT((positive * negative) / (positive + negative) + 0.9604) / 
                          (positive + negative)) / (1 + 3.8416 / (positive + negative)) 
       AS ci_lower_bound FROM widgets WHERE positive + negative > 0 
       ORDER BY ci_lower_bound DESC;
=IFERROR((([@[Up Votes]] + 1.9208) / ([@[Up Votes]] + [@[Down Votes]]) - 1.96 * 
    SQRT(([@[Up Votes]] *  [@[Down Votes]]) / ([@[Up Votes]] +  [@[Down Votes]]) + 0.9604) / 
    ([@[Up Votes]] +  [@[Down Votes]])) / (1 + 3.8416 / ([@[Up Votes]] +  [@[Down Votes]])),0)

而更多的說明在維基百科的「Binomial proportion confidence interval」可以翻到,裡面也有其他的方法可以用。

創業的點子

在「Startup idea generator: find spreadsheet tasks and build something better」這邊看到很有趣的想法 XDDD 原討論區出於 Hacker News 上的 id=14631031 這則。

找到還在用 spreadsheet (或者說,Excel?) 的用途,然後設計新的產品 XDDD

1) Pick an industry
2) Ask someone in that industry what they use spreadsheets for
3) Build something better

算是一種很有趣但是也還蠻實際的想法 XDDD