標籤彙整: 政府開放資料平台

Python範例

Pandas+SQL-空氣品質資料擷取

發佈留言

Pandas+SQL 操作 – 前言

Pandas+SQL 的操作範例，本篇文章以政府開放資料平台中的 空氣品質指標(AQI) 為資料來源，展示了如何使用 pandas 搭配 SQL 語法 來做資料搜尋，對於已經有 SQL 語法操作基礎的人，可以用之前學過的 SQL 語法快速的搜尋並篩選出所需要的資料，提供大家參考。

Pandas 簡介

Pandas 是 python 的一個數據分析模組庫，於 2009 年底開源出來，提供高效能、簡易使用的資料格式 (Data Frame) 讓使用者可以快速操作及分析資料，主要特色描述如下：

  • 在異質數據的讀取、轉換和處理上，讓分析人員更容易處理，例如：從列欄試算表中找到想要的值。
  • Pandas 提供兩種主要的資料結構，Series 與 DataFrame。
    • Series： 用來處理時間序列相關的資料(如感測器資料等)，主要為建立索引的一維陣列。
    • DataFrame： 用來處理結構化(Table like)的資料，有列索引與欄標籤的二維資料集，例如：關聯式資料庫、 CSV  等等。
  • 透過載入至 Pandas 的資料結構物件後，透過結構化物件所提供的方法，快速地資料前處理。例如：資料補值，空值去除或取代等。
  • 更多的輸入來源及輸出整合性，例如：可以從資料庫讀取資料進入 Dataframe ，也可將處理完的資料存回資料庫。

本篇文章就是使用 DataFrame 搭配關連式資料庫，使用 SQL 語法進行資料查詢篩選，而未來有時間再整理更多其他 Pandas 相關的應用範例。

安裝 Pandas

Python 安裝方式一樣都是透過 pip install 即可完成安裝，語法如下：

pip install pandas

其他介紹可以參考 官方網站

JSON

JSON（JavaScript Object Notation）是一種由道格拉斯·克羅克福特構想設計、輕量級的資料交換語言，以文字為基礎，且易於讓人閱讀。儘管 JSON 是 Javascript 的一個子集，但 JSON 是獨立於語言的文字格式，並且採用了類似於 C語言 家族的一些習慣。此種格式與XML格式常被用於API的資料回傳格式。本篇文章的程式碼範例就是以 JSON 格式的內容為資料的輸入來源。

範例程式碼

此範例程式主要流程大致為：

  1. 透過 requests 呼叫政府公開資料的 API 得到空氣品質指標的資料( JSON 格式)
  2. 透過 json.loads 函數將資料讀入
  3. 使用 pandas DataFrame 產生出資料框
  4. 透過 sqlalchemy 模組中的 create_engine 函數來建立 sqlite 的連線 ，並設定將資料表儲存在 memory 中(提供一次性的操作使用，若需要永久儲存則可以寫入檔案中)
  5. 使用 SQL 語法作取出特定的欄位(縣市、區域、平均 PM2.5 值)並以 PM2.5 的值由大至小排序。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
import json
import requests
import pandas as pd
'''
資料庫的讀寫函數可以使用 SQLAlchemy，支援 PostgreSQL, MySQL, Oracle,
Microsoft SQL server 等資料庫...
'''
from sqlalchemy import create_engine
 
req = requests.get('http://opendata2.epa.gov.tw/AQI.json')
data = json.loads(req.content.decode('utf8'))
df = pd.DataFrame(data)
 
#透過sqlalchemy模組中的create_engine函數來建立sqlite的連線
#並設定將資料表儲存在memory中(提供一次性的操作使用，若需要永久儲存則可以寫入檔案中)
engine = create_engine('sqlite:///:memory:')
df.to_sql('db_table', engine, index=False)
print(pd.read_sql_query('SELECT `County` as `縣市`, `SiteName` as `區域`, \
    CAST(`PM2.5_AVG` AS int) as `PM2.5` FROM `db_table` \
    order by CAST(`PM2.5_AVG` AS int) ASC', engine))

點我觀看執行範例 (使用 Microsoft Azure Notebooks )

Pandas+SQL 執行效果截圖
Pandas+SQL 執行效果截圖

若有其他想法，例如：想知道哪個縣市的整體平均空氣品質較差就可以在 SQL 語法中使用 GROUP BY 語法，對於 SQL 語法熟悉的話就能輕易地藉由改變查詢的語法來獲得想查詢 / 篩選的資料。

PS. 對於Python語法上有疑問可以參考課程投影片