cover

你常用到網路上各種來源的資料嗎?搜集整理總是一番功夫,是不是覺得若有一個小精靈能幫我們從搜集、整理到計算都自動完成就好了呢? 這次我們要來與大家分享使用 Google Spreadsheet 的秘技 — 如何利用 Google Spreadsheet 將整個資料分析的過程給自動化!

在做資料視覺化的過程,我們有時候會需要讓資料自動更新的流程自動化,這時通常我們可以透過程式語言將資料截取、梳理與分析的過程做成一條龍的服務。這樣的方式相當強大與具彈性,然而它仍然有一些缺點:

  • 圖表多時管理不易
  • 協作時需要所有人都有相同的執行環境
  • 對付迷你專題時,就像殺雞用牛刀般
  • 我不會寫程式啊啊啊啊啊

是的,程式往往是大家最頭大的一個東西。為什麼我要為了畫一個按讚數走勢去學程式?事實上就算會寫程式,有時候能有現成的工具幫我們把繁瑣的編程過程取代掉也是一件很棒的事。

試算表工具 ( 像是 Microsoft Excel 或 Google Spreadsheet ) 其實可以幫我們不少忙,利用試算表的公式與函式功能,我們可以透過儲存格的串連運算很快的將繁複的運算過程自動化,但是資料的來源又該怎麼辦呢?

Google Spreadsheet 資料爬取

其實, Google Spreadsheet 已經內含了幾個資料爬取的工具,其中包括:

  • importDATA – 讀取網路上的 CSV / TSV 檔
  • importHTML — 讀取網頁表格
  • importXML — 讀取任意網頁內容
  • 使用 Magic JSON 匯入 json 檔

使用 importDATA 讀取 CSV 檔

CSV 檔是一種類似 Excel .xls 檔的檔案,通常我們可以直接匯入到 Google Spreadsheet 中,但若我們想串接動態 API 的資料 ( 例如政府開放平台的 Open Data API ) ,並讓資料自動且即時的更新,我們可以直接將 API 的網址串接到 importDATA 函式,這樣就不需要再重覆下載、匯入的動作了。

要使用 importDATA 很簡單,首先我們需要一個 CSV 的網址,這裡我們利用政府資料開放平臺中的「01年平均餘命-依年齡別分.CSV」做為範例 ( CSV 檔案網址在這裡 ) ,接著只要在 Google Spreadsheet 中任一儲存格鍵入以下內容:

  =importDATA("http://data.gov.tw/iisi/logaccess/8718?dataUrl=http://data.hpa.gov.tw/dataset/16f94378-b68d-4759-ab89-7700f49cccb7/resource/4d4fff31-7b5a-4e11-8a6c-a73e062132db/download/lifeexpectancybyage.csv&ndctype=CSV&ndcnid=14454")

完成後, Google Spreadsheet 便會自動將資料載入:

import csv in google spreadsheet

使用 importHTML 讀取網頁表格

若我們今天想要使用的資料是網頁中的一部份,就沒辦法直接使用 importDATA 來讀取。不過,如果資料在網頁中使用表格 ( table ) 或列表 ( list ) 的方式來排版的話,我們還可以使用 importHTML 來匯入資料 — importHTML 會自動分析表格的長相,並為我們將表格直接對應到試算表的儲存格中。

舉例來說,若我們想要匯入維基百科中「各國人均國民總收入列表」中的「高收入組」資料,我們同樣可以透過將網址提供給 importHTML 函式的方式來取得;不過這次稍微複雜一些,我們還需要告訴 Google Spreadsheet 我們要的是哪種格式的資料 ( 表格 / 列表 ),還有我們要的是第幾個表格。以這個例子來看,我們要的是表格「table」式資料,並且要前兩個表格,因此我們使用的指令如下:

=importHTML(“https://zh.wikipedia.org/wiki/各國人均國民總收入列表”, “table”, 1)

不過,由於有時候網頁會利用表格或列表來做內容排版,我們可能會誤判表格的順序而抓到錯誤的內容,例如下圖就是一個抓失敗的例子:

import html failed

這時候我們其實也只需要稍微微調一下最後一個數字,很快就能找到正確的表格了,將順序從「1」改為「2」,馬上就出來了正確的資料:

importhtml succeded

由於這個維基頁面將高收入組分成兩個表格,我們需要再下方再寫一次 importHTML ,並將表格順序改為 3 ,這樣就可以完整的將資料匯入 Google Spreadsheet 了。

使用 importXML 讀取任意網頁內容

importDATA 與 importHTML 相當的方便,但世界上並不是每天都有這麼好的事,有時候我們想要的資料既不是 CSV 格式、也不是用表格的方式排版,而是任意的網頁內容,這時候該怎麼辦呢?

其實就算是任意的網頁內容,也都可以說是「結構化的資料」,我們就可以用某種方式正確的處理他們。我們可以把網頁中的所有資料想成一間房子,而每間房子都可以用一個地址找到,在網頁中這個地址的概念就叫做「XPath」。

XPath 長得有點像目錄結構或網址的寫法,他使用斜線分格、並利用標籤名做找查。下面為一個 XPath 的範例:

  //li/table/tbody/tr[2]/td/div/span

雖然不會太複雜,但要學起來還要費一番功夫,身為懶人的我們當然不會想要自己來摸這個東西。幸好, Google Chrome 瀏覽器提供了「Copy XPath」的功能,可以讓我們直接複製任意網頁內容的 XPath 。要怎麼複製呢?這邊的步驟又比前面複雜了一些:

  • 首先開啟你的 Google Chrome 瀏覽器,連至你想爬取資料的網站 ( 此處以批踢踢棒球版為例 )
  • 找到你有興趣的內容後,在該內容上點擊右鍵 ( 如下圖 1 ) ,並點擊彈出選單的「Inspect」(如下圖 2 )
    importxml1
  • Inspect 會開啟開發者視窗,這時你會在一堆程式碼中,不用太害怕,看到一條與眾不同的的程式了嗎? ( 此處為灰底該行,如下圖 1 處) 對他點擊滑鼠右鍵,並在彈出的選單中選擇「Copy」→「Copy XPath」(下圖 2 / 3 處 ):
    importxml2

於是我們便取得了批踢梯棒球版最新貼文之一的 XPath :

  //*[@id="main-container"]/div[2]/div[1]/div[3]/a

這個 XPath 直接指定到該網頁中「人口資料集」的「觀看人數」。要在 Google Spreadsheet 中讀入這個資料,我們使用 importXML 函式,並將網址與 XPath 傳入其中即可;不過,這裡會稍微有點陷阱,因為 Google Spreadsheet 基於某些不明原因並不會正確的解讀網頁內文,使得我們的 XPath 對應到他所解讀的網頁時可能會有些出入。一般來說,我們可以嘗試先將 XPath 中中括號的部份 ( 比方說上例的 [@id=”masonry”] ) 逐一移除來測試效果。要注意的是移除中括號代表著放寬 XPath 的限制 ( 比方說,原本指定「湖山路一段 20 巷 50 號」中的住戶,移除 20 巷 50 號以後,就變成指定住在「湖山路一段」的所有住戶了 ),因此我們可能會因此而取得多筆資料。

經過些許嘗試後,最後的指令碼如下,可以取得批踢踢棒球版最新的若干篇貼文:

  =importXML("https://www.ptt.cc/bbs/Baseball/index.html","//*/div[2]/div/div/a")

執行結果如下:

importxml3

事實上如果你熟悉 XPath 的話,透過語法的組合你可以爬到不少東西,所以有興趣的朋友可以再進一步鑽研 XPath 的語法,以製作更精確的 XPath 指令。

進階分析

由於這次的主題著重在資料爬取,梳理的部份我們稍微簡單帶一下,有機會再跟大家詳細介紹。這裡我們利用三個不同的指令直接算出最新貼文的分類比例,包含:

取出貼文分類 ( 於儲存格 B1 執行 )

  =regexextract(A1,"\[[^\]]+\]")

條列分類種類 ( 於儲存格 C1 執行 )

  =unique(B:B)

計算各分類數量 ( 於儲存格 D1 執行 )

  =counta(filter(B:B,B:B=C1))

上列指令執行後的結果如下:

analysis

這裡我們可以看到各篇貼文分類的數量統計,棒球版最近的 23 篇貼文中 ( 包含置頂文 ) 共有 9 篇是新聞、 6 篇在閒聊。 ( 我們並沒有處理回文的狀況,基於教學簡化了分析過程 ) 快速用 Google Spreadsheet 的圖表功能做視覺化,我們得到如下的結果:

visualize

( 圖表本身有許多可改進的地方,但我們並沒有特別優化圖表,在此僅為快速示範 )

然後呢?

最棒的地方在於,若資料來源的網頁內容有變化 ( 比方說出現了新的貼文 ) ,我們現在建立的這個試算表完全可以自動更新。隨時跟上最新的資訊,是不是很棒呢?

結語

講了這麼多,好像聽起來爬資料都交給 Google Spreadsheet 就好了?很可惜,事實上 Google Spreadsheet 只能對付「靜態」網頁,也就是資料與結構都是在遠端電腦產生的網頁類型,若碰到使用 JavaScript 做內容動態載入或需要登入的個人化頁面,我們便還是得靠進一步的網頁分析與開發爬蟲程式才能順利取得我們想要的資料了。

不過,如果是在 Google Spreadsheet 可以處理的範圍內,使用這次介紹的這些指令也真的是相當方便;以前面的批踢踢文章分類分析為例,我們一共只寫了四個指令,就不只達到即時更新與視覺化,同時還方便與他人協作;若使用程式爬蟲,光是處理網頁結構與資料下載的細節就有得忙的了,更別提程式的輸出若要能夠供他人使用,還得要設法匯入,是不是麻煩多了呢?

無論如何,不同的技巧都有其發揮的空間,之後若有機會你也可以用 Google Spreadsheet 爬爬看資料,相信你會愛上這個手法喔!


Written by infographics.tw

7 Comments

yptsai

請問遇到ptt看板有分級(未滿18歲禁入),要怎麼自動讀取,感謝

Reply
HYP

首先謝謝您分享,這篇文章幫助很大!
在importXML實作上有個問題想請教,如果想讀取的文字是超連結,有辦法將連結網址一起抓取嗎?

Reply

發表迴響

你的電子郵件位址並不會被公開。 必要欄位標記為 *