TL; DR
除了先前電子報所提到 Johns Hopkins University 的 Covid19 GitHub Repository: https://github.com/CSSEGISandData/COVID-19,另外一個我也很喜歡用來作為示範資料的來源是 imdb.com,我們可以寫作 Python 將需要的資料打包成一個 SQLite 資料庫檔案,文末也提供了 imdb.db 與完整程式碼的 Google Colab 給約維安計畫學員。
緣起
imdb.com 的電影、演員與導演資料是我在課程、工作坊或者寫作中相當頻繁使用的示範資料,該如何擷取(Extract)、轉換(Transform)以及載入(Load)這個網站的資料呢?其 ETL(Extract-Transform-Load) 細節分別是:
Extract:透過 requests 模組擷取 imdb.com 的 html 檔案。
Transform:透過 numpy 與 pandas 模組清理、轉換資料。
Load:透過 pandas 與 sqlite3 模組載入資料為 SQLite 資料庫檔案。
其中針對擷取與轉換的環節,我寫作一個類別 Imdb
來封裝整個過程。
Imdb
類別
Imdb
類別中我規劃了九個方法:
__init__()
: 標記下載到本機的 html 檔案相對路徑。_create_id_as_primary_key()
: 將 pandas 資料框的列索引改為資料表的主鍵欄位,用途為輔助型函數(Helper function),故命名前面加了一個底線。_download_full_credits_html_files()
: 根據下載到本機最高評價的 250 部電影清單下載這些電影的導演、編劇與演員 html 檔案,用途為輔助型函數。_get_directors_writers_actors()
: 擷取下載到本機的 html 檔案中的導演、編劇與演員資料,用途為輔助型函數。_drop_duplicates()
: 刪除重複的資料,要注意的是導演、編劇與演員都可能有同名同姓的狀況,因此在判斷是否為重複資料時,除了姓名還要考慮在 imdb.com 上的檔案頁面(Profile page)連結是否也相同,用途為輔助型函數。_get_movies_entities_table()
: 建立橋接資料表的前置作業,由於電影對應到不論導演、編劇或者演員都是「多對多」的關係,所以都會需要橋接資料表(Bridging table)來建立實體之間的關聯,用途為輔助型函數。get_movies_table()
: 建立電影實體的資料表。get_entity_tables()
: 建立導演、編劇與演員實體的資料表。get_bridging_tables()
: 建立橋接資料表。
class Imdb:
def __init__(self):
self._html_file = "IMDb-Top-250-Movies.html"
def _create_id_as_primary_key(self, df):
# ...ignored
return df
def _download_full_credits_html_files(self, folder_name="imdb_credits"):
# ...ignored
def _get_directors_writers_actors(self):
self._download_full_credits_html_files()
# ...ignored
return directors, writers, actors
def _drop_duplicates(self, list_of_dict):
# ...ignored
return df
def _get_movies_entities_table(self, bridging_df, entities_df, entity_name):
# ...ignored
return movies_entities_df
def get_movies_table(self):
# ...ignored
return movies_df[["id", "title", "release_year", "runtime", "rating", "link"]]
def get_entity_tables(self):
# ...ignored
return directors_df, writers_df, actors_df
def get_bridging_tables(self):
# ...ignored
return movies_directors_df, movies_writers_df, movies_actors_df
實例化
實例化 Imdb
類別之後可以依序使用物件的 get_movies_table()
方法、 get_entity_tables()
方法與 get_bridging_tables()
方法獲得 movies
、directors、writers、actors、movies_directors、movies_writers
與 movies_actors
資料框。
imdb = Imdb()
movies = imdb.get_movies_table()
directors, writers, actors = imdb.get_entity_tables()
movies_directors, movies_writers, movies_actors = imdb.get_bridging_tables()
如果我們在應用時希望將資料以 csv 純文字檔案的方式載入,那就可以使用資料框的 to_csv()
方法將資料框輸出為 csv 純文字檔案。
actors.to_csv("actors.csv", index=False)
directors.to_csv("directors.csv", index=False)
movies.to_csv("movies.csv", index=False)
writers.to_csv("writers.csv", index=False)
movies_actors.to_csv("movies_actors.csv", index=False)
movies_directors.to_csv("movies_directors.csv", index=False)
movies_writers.to_csv("movies_writers.csv", index=False)
若是在應用時希望將資料以關聯式資料庫的資料表方式載入,則使用 Python 標準模組 sqlite3 搭配 pandas 模組。
con = sqlite3.connect('imdb.db')
actors.to_sql('actors', con, if_exists='replace', index=False)
directors.to_sql('directors', con, if_exists='replace', index=False)
movies.to_sql('movies', con, if_exists='replace', index=False)
writers.to_sql('writers', con, if_exists='replace', index=False)
movies_actors.to_sql('movies_actors', con, if_exists='replace', index=False)
movies_directors.to_sql('movies_directors', con, if_exists='replace', index=False)
movies_writers.to_sql('movies_writers', con, if_exists='replace', index=False)
最後我們定義欄位資料類型,建立主鍵以及外鍵約束,完成 imdb.db 的 ETL 工作。