TL; DR
Johns Hopkins University 的 Covid19 GitHub Repository: https://github.com/CSSEGISandData/COVID-19 在 2023-03-10 進行了公開封存,我們可以寫作 Python 將需要的資料打包成一個 SQLite 資料庫檔案,文末也提供了 covid19.db 與完整程式碼的 Google Colab 給約維安計畫學員。
緣起
如我在 End of an Era 一文中所提及,Johns Hopkins University 的 Covid19 GitHub Repository 是我在課程、工作坊或者寫作中相當頻繁使用的示範資料,那麼具體而言,這個儲存庫的資料是怎麼被我擷取(Extract)、轉換(Transform)以及載入(Load)的呢?其 ETL(Extract-Transform-Load) 細節分別是:
Extract:透過 pandas 模組擷取儲存庫的 csv 檔案。
Transform:透過 numpy 與 pandas 模組轉換資料。
Load:透過 pandas 與 sqlite3 模組載入資料為 SQLite 資料庫檔案。
其中針對擷取與轉換的環節,我寫作一個類別 Covid19
來封裝資料清理的過程。
擷取(Extract)與轉換(Transform)
Covid19
類別中我規劃了四個方法:
get_lookup_table()
: 擷取與轉換儲存庫中的 UID_ISO_FIPS_LookUp_Table.csv 將人口數、經度與緯度聚合到國家區域的層級,最後輸出countries_regions
資料框。tidy_time_series()
: 將原本儲存庫所提供的寬格式外型轉置(Transpose)為長格式外型的時間序列資料,並且將確診數、死亡數聚合到國家區域與獨一日期的層級。add_country_region_date_id()
: 將轉置後的時間序列資料加上與國家區域、日期的連接鍵(Join key)。get_time_series()
: 擷取儲存庫中的 time_series_covid19_confirmed_global.csv 與 time_series_covid19_deaths_global.csv 檔案,並呼叫tidy_time_series()
與add_country_region_date_id()
作為幫助函數(Helper function),最後輸出accumulative_cases
與dates
資料框。
class Covid19:
def get_lookup_table(self):
# Ignoring...
self._lookup_table = groupby_agg_reset_index
return groupby_agg_reset_index
def tidy_time_series(self, data_url, value_name):
# Ignoring...
self._dates_table = dates_table
return time_series
def add_country_region_date_id(self, df, value_name):
# Ignoring...
return time_series_lookup_table_dates_table
def get_time_series(self):
# Ignoring...
return time_series, self._dates_table
載入(Load)
實例化 Covid19
類別之後可以依序使用物件的 get_lookup_table()
方法與 get_time_series()
方法獲得 countries_regions
、accumulative_cases
與 dates
資料框。
covid19 = Covid19()
countries_regions = covid19.get_lookup_table()
accumulative_cases, dates = covid19.get_time_series()
如果我們在應用時希望將資料以 csv 純文字檔案的方式載入,那就可以使用資料框的 to_csv()
方法將資料框輸出為 csv 純文字檔案。
countries_regions.to_csv('countries_regions.csv', index=False)
accumulative_cases.to_csv('accumulative_cases.csv', index=False)
dates.to_csv('dates.csv', index=False)
若是在應用時希望將資料以關聯式資料庫的資料表方式載入,則使用 Python 標準模組 sqlite3 搭配 pandas 模組與 SQL 的交易控制語言(TCL, Transaction Control Language)。
con = sqlite3.connect('covid19.db')
countries_regions.to_sql('countries_regions', con, if_exists='replace', index=False)
accumulative_cases.to_sql('accumulative_cases', con, if_exists='replace', index=False)
dates.to_sql('dates', con, if_exists='replace', index=False)
cur = con.cursor()
SQL 的交易控制語言能夠讓資料庫執行「整合性」的操作,例如在範例中,我們需要將 pandas 模組匯入的資料表外鍵取消、複製一份資料表、正式建立資料表、定義欄位資料類型與約束、最後再匯入資料,就可以使用交易控制語言來達成這些「連串」的操作,完成 covid19.db 的 ETL 工作。
約維安計畫學員專區
約維安計畫學員可以點選下列連結下載 covid19.db SQLite 資料庫檔案: