藥品交互作用網頁文字轉存成格式化的資料表 - Challenge

 

在經過前面 4 篇「列轉欄」的修練後，我來分享一個在工作上實際的案例，步驟雖然簡單，卻是滿滿的干貨。

這是一個醫藥界位居翹楚的 MicromeXXX「藥品交互作用查詢結果」的網頁，我希望將它轉換成 excel 資料表，以便能匯入到 HIS 使用。以下是一些特性與限制：

1. 網頁文字可以複制。
2. 網頁原始碼檢視只能看到一些程式碼以及版權宣告，完完全全找不到任何網頁的內文，也就是說想要依靠解析網頁原始碼中的 HTML 標簽來擷取資料這招，在這裡無用武之地。

由於這網頁內容是付費資料，醫院雖有購買，但不代表著可以將它大勒勒的曝露在大眾面前，所以一切敏感內容都被遮蔽！

【網頁文字轉存成格式化的資料表 】關鍵的部份完成後，接下來若想要全流程自動化，那麼我會把院內每個藥品的交互作用網址 URL 存成 EXCEL 檔，用 VBA 寫迴圈，每季按一鍵就去抓取院內千百個藥品"最新"的交互作用資料，並且再讓它比對一下和上次抓取的檔案是否有更新，有更新的再丟給 HIS 去滙入，這樣就達到了徹底懶人化的目標囉~~~~

檔案下載：藥品交互作用網頁文字轉存成格式化的資料表 

Thst’s begin~~

這是一個藥品交互作用網頁，內容呈現 CLOPIDOGREL 這藥品與下面哪些藥品有交互作用，以及一些特性的敘述。 

網頁原始碼：

Ctrl+A 全選內容後，Ctrl+C 複製。

選擇性貼上→文字 ，貼到 excel 。 (如果只是單純地貼上，或貼上HTML，都會有換行及空白行)

內容看起來就像這樣。

在進入 Power Query 之前，我們先看一下整體資料長成什麼樣子。

前面 5 列是要移除的，藥名之下的就是一些特性，似乎都是以 數字) 的方式來列表

再往下看一看，8) 裡面的列表是以 英文) 的方式來呈現。

到這裡我可以歸納出一個規則，藥名就是文字內沒有 “)” 符號，特性就是有 “)” 符號。

拉到最尾段，最後有 4 列也是我不要的。

資料格式分析完後，就可正式進入 Power Query 了

step 1 - 移除頂端 5 列。

step 2 - 移除底端 4 列。

目前看起來像這樣。

step 3 - 為了要將資料分組，我需要加入一行輔助資料行，用來判斷從第n列到第n列是同一組資料。

是藥名的就直接帶出藥名，不是藥名的就代入 null 。

step 4 - 以 向下填滿 的方式，把 null 填入 藥名。

這裡就是一組一組的資料了，接下就是依照學名來分組。

step 5 - 以 [學名] 來分組

關鍵來了，做錯了就沒法得到想要的結果，就是 作業 這裡要選取 所有資料列。

它以 [學名] 為分組依據，把 [欄1] 的資料集結成一個 Table。

用滑鼠隨便一個 Table ，會在下面看見 Table 的內容。

Step 6 - 由於 Table 的內容有 2 個欄位，我只需要 [欄1] 這一欄而已，所以使用 Table.Column 這個方法，把 [欄1] 取出來，並且傳回成清單(list)。

清單出來囉~ 一樣，用滑鼠隨便一個 List ，會在下面看見 List 的內容。

Step 7 - 把清單展開，以 "擷取值” 的方式。 

如果使用 "展開至新資料列”，你會發現好不容易聚合起來的欄1又被展開成一開始的樣子了。

因為裡面文字的內容有 , . : ; 空白….什麼都有，所以我用 5個# 來分隔

Step 8 - 最後一步了，把 [清單] 內的內容，以 5個# 來分割成 欄。

登登登~~~資料很完美地分割好囉~ 接下來就是一些瑣碎的工作了。

像是把前面二個資料行 [學名]、[Data] 移除。

把每一個資料行裡重覆的文字移除。例如 [清單.2] 裡的 1) Interaction Effect: …..

再譬如把欄位名稱改一改…

贅字取代完了，欄位名稱也改好了，就可以很高興地按下 "關閉並載入” 看看成果，資料一共有234筆。

接到最下面看看，嗯~ 原來 CLOPIDOGREL 這藥品與 食物、香菸 也有交互作用呀!