網路爬蟲開發 - AQIStudy Crawler

因為近期有需要去中國網站抓空品資料分析的需求，他是一個城市一個城市個別放的https://www.aqistudy.cn/historydata/

備註 AQI 是計算空氣品質指標的方式，詳細請看我國環保署的網站。

為了比較方便處理，因此通通抓到資料庫再用Query或丟到Excel處理。處理方式是利用python去爬爬爬，放到MS SQL，再用SQL的方式進行分析比對，任務很簡單，但處理起來需要一些技巧。

1. 抓資料

#import需要的工具
import pandas as pd
import sqlite3
import urllib.parse
import sqlalchemy, pyodbc
from datetime import datetime
from selenium import webdriver

#取得所有城市，程式清單請自己整理成TXT
def get_city_set():
    str_file = r'city.txt'
    fp = open(str_file,'rb')
    city_set = list()
    for line in fp.readlines():
        city_set.append(str(line.strip(),encoding='utf-8'))
    return city_set
city_set = get_city_set()

#開始爬資料
base_url = 'https://www.aqistudy.cn/historydata/monthdata.php?city='
#SQLServer的帳密，請自己改
engine = sqlalchemy.create_engine("mssql+pyodbc://[帳號]:[密碼]@[主機]:1433/[資料庫名]?driver=SQL+Server+Native+Client+11.0")
conn = engine.connect()

for city in city_set:
    weburl = ('%s%s' % (base_url,urllib.parse.quote(city.encode('utf-8'))) )
    print(weburl)
    driver = webdriver.PhantomJS()
    driver.get(weburl)
    dfs = pd.read_html(driver.page_source,header=0)[0]
    driver.quit()
    dfs['City']=city
    dfs['InsertDatetime']=datetime.now()
    print(city,"\t",len(dfs))
    dfs.to_sql("[表格名]",engine,if_exists='append')

conn.close()

#判斷結果
engine = sqlalchemy.create_engine("mssql+pyodbc://[帳號]:[密碼]@[主機]:1433/[資料庫名]?driver=SQL+Server+Native+Client+11.0")

conn = engine.connect()
rs = conn.execute('SELECT [City],count(*) FROM tbAQI with (nolock) group by [City]')

for row in rs:
    print(row)
    
conn.close()

2. 分析
 用Pivot就可以方便資料分析人員在Excel做逐時的比對

select * from 
(
 select city,'PM25' as [Object],[月份],[pm2.5] from [表格名]
 group by city,[月份],[pm2.5]
)as S
PIVOT
(
 sum([pm2.5])
 for mon in ([2014-01],[2014-02],[2014-03],[2014-04],[2014-05],[2014-06],[2014-07],[2014-08],[2014-09],[2014-10],[2014-11],[2014-12],
  [2015-01],[2015-02],[2015-03],[2015-04],[2015-05],[2015-06],[2015-07],[2015-08],[2015-09],[2015-10],[2015-11],[2015-12],
  [2016-01],[2016-02],[2016-03],[2016-04],[2016-05],[2016-06],[2016-07],[2016-08],[2016-09],[2016-10],[2016-11],[2016-12],
  [2017-01],[2017-02],[2017-03],[2017-04],[2017-05],[2017-06],[2017-07],[2017-08],[2017-09],[2017-10])
)AS P

貼到Excel就長這樣，可以多城市一起比對分析，方便!


#Python
#Crawler
#AQI
#AQIStudy
#Sky is limit