# 2023-10-18 以Python來做Excel自動化與圖表專題 上課筆記
- [2023年10月18課程筆記](#2023-10-18)
- [2023年10月25課程筆記](#2023-10-25)
## 2023-10-18
## 安裝環境
### 步驟一：安裝Python
如果你打算使用Python開發，請先安裝Python；Python官網：https://python.org/downloads/
> 提示：安裝Python時請先將VSCode關閉，以避免VSCode無法辨認到Pyhon。
> **注意:**
> 安裝時請務必把 Add python.exe to PATH 選項打勾在進行安裝，否則未來在執行pip指令時會無法執行。
### 步驟二：下載與安裝Visual Studio Code
https://code.visualstudio.com/
點選「Download for Windows」按鈕後直接「下一步」安裝到底即可。
> 如果使用頻果Mac系列電腦的話，按鈕會是「Download for Mac」。
### 步驟三：安裝Python extension
https://marketplace.visualstudio.com/items?itemName=ms-python.python
點擊連結會打開Visual Studio Code進行安裝，或是直接從VSCode內的Market Place安裝也可以。
### 中文環境 extension(非必須)
在Extension搜尋「chinese」，選擇Chinese (Traditional) Language 中文(繁體)安裝。
> 此功能安裝後需要重新啟動Visual Studio Code
## 確認開發環境
1. 展開VSCode左邊的檔案總管
2. 開啟資料夾: 存放Python程式碼用
3. 建立新檔案: 檔名隨意，副檔名必須為`.py`
4. 檔案內容輸入:
> `print('Hello Python')`
5. Ctrl + F5 執行程式碼，此時VSCode右下方會跳出建議安裝Python Extension的對話盒，點擊安裝
6. 再次Ctrl + F5 執行Python程式
7. 下方終端機會出現`Hello Python`及代表開發環境安裝成功
## 好用快速鍵
| 功能 | MacOS | Windows |
| ---------------------- | ------------------ | ------------------- |
|執行程式| `Ctrl` - `F5`| `Ctrl` - `F5`|
|畫面放大|`CMD` + +| `Ctrl` + `+`|
|畫面縮小|`CMD` - +| `Ctrl` + `-`|
|快速複製一行程式碼|`CMD` + `C` + `V`| `Ctrl` + `C` + `V`|
|註解程式碼|`CMD` + `/`|`Ctrl` + `/`|
## 補充
1. 程式由: 輸入 計算 輸出 三個部分組成
2. 程式碼執行順序為由上往下，例如:
```
print('Hello Python')
print('今天天氣很好')
```
會在畫面上面先看到Hello Python
## 字串
單引號和雙引號的功用一樣，可以視情況做互換，例如字串本身包含有單引號時，就可以用雙引號來包圍字串。
例如：
```
"I'm a good student"
'Here is my "STUFF"!!'
```
字串裡定義了一些特殊的轉義文字，有特殊功用或意義，不會直接在畫面上輸出。
| 符號 | 說明 |
| ---------- | -------------------------------------------------- |
| `\\` | 反斜線`\` |
| `\'` | 單引號`'`' |
| `\"` | 雙引號`"` |
```
'Hi,\nGood Morning'
"1\t2\t3"
```
如果字串內想要顯示像`\t`這樣的字串的話，必須寫成`\\t`或是可以在字串前面加上`r`代表直接使用原始字串而不需要轉義，例如：
```
print('\\t')
print(r'\t')
```
輸出為：
```
\t
\t
```
## input()輸入
用來從終端視窗上接收使用者的輸入，輸入的資料為字串型態。
```
a = input()
print(a)
```
1. 執行後畫面會停住，程式碼會暫停在`input()`該行等待使用者輸入，並按下「Enter」結束輸入的動作。
2. 使用者完成輸入後`input()`函式會傳回使用者的資料，此時需要使用指派運算子「=」，將資料存到變數（也就是電腦的記憶體）中，以避免程式碼往下執行時，資料消失，上面範例中`a`即為該變數名稱，透過`print()`函式，就可以將`a`的內容顯示到標準輸出。
可以在等待使用者輸入時，顯示提示文字：
```
a = input('請輸入您的姓名：')
print('您的姓名為：', a)
```
```python=
print('輸入前')
a = input()
print('輸入後')
print('剛剛你輸入的資料是:', a)
```
1. 有些涵式執行完後會產生資料，必須透過`=`指派運算子將其存放下來，否則擋了下一行就會消失了。
## 變數命名規則
1. 可以使用英文字母大寫及小寫，大小寫代表不同的變數。
> 例如：`a`和`A`是不同的變數名稱。
2. 可以使用數字，但第一個字不能是數字。
> 例如：`1abc`為錯誤命名，`abc1`為合法名字。
3. 不可以使用保留字。
> 保留字：
>
> If、elif、else、and、or、not、import、from、as、assert、for、while、break、continue、try、except、finally、pass、def、return、lambda、del、global、nonlocal、in、is、with、yield、class、None、True、False
## 格式化字串
```python=
name = input('請輸入姓名:')
age = input('請輸入年齡:')
print('你的名字是:', name, ', 年齡:', age, '歲')
# f-string
print(f'你的名字是: {name}, 年齡: {age}歲')
```
## 數字與四則運算
#### 算數運算子
算術運算子有：
| 運算子 | 說明 |
| ------ | ---------------------- |
| ＋ | 加法運算 |
| — | 減法運算 |
| ＊ | 乘法運算 |
| ＊＊ | 指數運算 |
| ／ | 除法運算 |
| ／／ | 除法運算，但只保留整數 |
| ％ | 除法運算，但是只取餘數 |
#### 比較運算
| 比較運算子 | 說明 |
| ---------- | ------------------------------------------ |
| > | 大於 |
| >= | 大於或等於 |
| < | 小於 |
| <= | 小於或等於 |
| == | 等於 |
| != | 不等於 |
## 布林值與`in`判斷
`in`指令用來判斷前面的資料是否存在於後面的群集資料當中，運算結果為`True`或`False`
###### 上課範例:
```python=
# in
print('aa' in 'aaron')
print('我' in '天氣')
```
```python=
print(1 + 1)
print(1 - 1)
print(2 * 2)
print(2 / 2)
print(2 ** 3)
print(7 // 3)
print(7 % 3)
print('1' + '1' + '字串') # 串接
print('DE' * 5)
# 布林直
print(True)
print(False)
# 比較運算子
print('3 > 4 =', 3 > 4)
print('3 < 4 =', 3 < 4)
print('3 >= 4 = ', 3 >= 4)
print('3 <= 4 =', 3 <= 4)
print('3 == 4 =', 3 == 4)
print('3 != 4 =', 3 != 4)
```
## `type()`涵式
用來偵測資料型態:
```python=
print(type(1))
print(type('1'))
print(type(True))
```
輸出結果:
```
<class 'int'>
<class 'str'>
<class 'bool'>
```
> **備註:**
> `int`為Python的整數型態
> `str`為Python的字串型態
> `bool`為Python的布林型態
## if判斷式
格式：
```
if 條件式:
條件成立執行的程式碼
```
> **注意**
> 流程內的程式碼一定要縮排。
```python=
score = input('請輸入成績: ') # 只要是input涵式接收到的資料，一律都是字串
# 字串傳換成數字
score = int(score)
if score >= 60:
print('及格')
if score < 60:
print('不及格')
```
> **補充:**
> 所有的Python資料型態都有對應的涵式用來做資料轉型
## 群集資料list
透過一堆中括號可以用來建立群集資料，list內可以存放字串，數字，布林直，甚至list
透過索引的方式可以取出指定位置的資料，所以從0開始，例如:
```python=
a = [1, 2, 3, 4, 5]
print(a[3])
```
會輸出:
```
4
```
## `for in`迴圈
用來將群集資料一筆一筆取出來處理
```
for 暫存變數 in 群集資料:
處理迭代出來的變數
...
```
當全部資料都被取出來後，迴圈即結束執行
## 將不及格的分數顯示出來
```python=
# 把不及格的分數顯示到畫面上
a = [41, 72, 73, 54, 95, 86, 27, 18, 59]
for score in a:
if score < 60:
print(score)
```
## 字典
```python=
a = {'a': 33, 'b': 999}
print(a['b'])
a = {'aaron': '0987123456', 'andy': '0933127654'}
print(a['aaron'])
a = {'aaron': [44, 55, [1, 2, 3]], 'andy': [88, 77, 66]}
print(a['aaron'][2][1])
```
## 一個複雜的資料結構
```python=
data = {
'status': 'ok',
'result': {
'name': 'aaron',
'info': [
{
'ar': 3,
'bemp': 9,
'addr': '中壢'
}
]
}
}
# 取得中壢那筆資料的方式
print(data['result']['info'][0]['addr'])
```
## 模組的使用
#### 安裝requests模組
```
$ pip3 install requests
```
#### 抓取桃園市Ubike資訊
## 資料欄位說明
|索引|欄位名稱|說明|
|-|-|-|
|0|sareaen|行政區英文名|
|1|sarea|行政區中文名|
|2|lng|經度|
|3|sna|中文站名|
|4|snaen|英文站名|
|5|bemp|空位數量|
|6|ar|中文地址|
|7|act|全站禁用狀態(0:禁用、1:啟用)|
|8|sno|站編號|
|9|aren|英文地址|
|10|tot|場站總停車格|
|11|_id|資料編號|
|12|sbi|場站目前車輛數量|
|13|mday|微笑單車各場站來源資料更新時間|
|14|lat|緯度|
> **資料來源：**
>
> https://data.gov.tw/dataset/137993
```python=
import requests
import json
import openpyxl
import time
url = 'https://data.tycg.gov.tw/api/v1/rest/datastore/a1b4714b-3b75-4ff8-a8f2-cc377e4eaa0f?format=json'
# 呼叫request模組的get方法到網址的位置取回資料
response = requests.get(url)
# 判斷抓取網址資料是否成功
if response.status_code == 200:
# 透過json模組來將json字串轉換成Python資料結構
data = json.loads(response.text)
# 接收使用者輸入的尋關鍵字
# user_input = input('請輸入要搜尋的站台部分名稱: ')
# for row in data['result']['records']:
# if user_input in row['sna']:
# print(f"站名: {row['sna']}, 地址: {row['ar']}")
# print(f" - 可借: {row['sbi']}")
# print(f" - 可還: {row['bemp']}")
# print(f" - 總數: {row['tot']}")
# print()
# 建立新的活頁簿
wb = openpyxl.Workbook()
# 修改工作表名稱
wb.active.title = "桃園Ubike"
# 先取得目前的工作表
ws = wb.active
ws['A1'] = '站名'
# ws.cell(column=1, row=1, value='站名2')
ws['B1'] = '地址'
ws['C1'] = '可借'
ws['D1'] = '可還'
ws['E1'] = '總數'
# 從第二列開始寫入資料
row_index = 2
for row in data['result']['records']:
ws.cell(column=1, row=row_index, value=row['sna'])
ws.cell(column=2, row=row_index, value=row['ar'])
ws.cell(column=3, row=row_index, value=row['sbi'])
ws.cell(column=4, row=row_index, value=row['bemp'])
ws.cell(column=5, row=row_index, value=row['tot'])
row_index = row_index + 1
# 根據時間產生檔名
now = time.localtime() # 取得目前本地時間
time_str = time.strftime('%Y%m%d%H%M') # 格式化後的時間字串
# 存檔
wb.save('桃園Ubike-' + time_str + '.xlsx')
```
#### Excel介紹
Microsoft Excel副檔名為：xlsx或xls，以下為幾本名詞：
- 活頁簿（workbook）：Excel檔案本身又稱為活頁簿。
- 工作表（worksheet）：一個活頁簿由一個或數個工作表組成，一個工作表即為一頁表格文件，可以透過Excel應用程式下方的標籤來切換工作表。
- 欄位（column）：直的方向稱為欄位，名稱由英文字A、B、C...等等依序排列。
- 列（row）：橫的方向稱為列，名稱由１、２、３...等等依序排列。
- 儲存格（cell）：工作表內的每個存放資料的小格子稱為儲存格，可以用欄＋列來定位每個儲存格。
#### 安裝`openpyxl`模組
```bash=
$ pip3 install openpyxl
```
## 2023-10-25
## 函式
```python=
a = 1
b = 2
c = 3
d = 4
# 比較兩個變數大小的函式
# 函式必須要透過[呼叫]的手段，才會被執行
def compare(n1, n2):
if n1 > n2:
print(n1, '比', n2, '大')
else:
print(n1, '真的沒有比', n2, '大')
compare(6, 7)
compare(9, 4)
compare(10, 7)
compare(3, 8)
compare(78, 41)
if a > b:
print(a, '比', b, '大')
else:
print(a, '真的沒有比', b, '大')
if c > d:
print(c, '比', d, '大')
else:
print(c, '沒有比', d, '大')
```
#### 練習
寫一函式，名叫plus，需傳入兩個參數，加總後顯示結果到終端機上
```python=
def plus(num1, num2):
sum = num1 + num2 # 等號式把右邊的資料存到左邊的變數
print(f'{num1} + {num2} = {sum}')
plus(3, 4)
plus(13, 14)
plus(33, 54)
```
#### 參數預設值
```python=
# 一個參數有如果有參數預設值，那她右邊的所有參數都必須要有參數預設值
def show_me(name, phone = '尚未提供電話', address = '尚未提供地址'):
print(f'名字: {name}\n電話: {phone}\n地址: {address}')
show_me('Aaron', '台北市')
show_me('Andy', '0987654321')
show_me('Andy', '0987654321', '中壢區健行路229號')
```
#### 關鍵字引數
```python=
def test(a1, a2, a3, a4, a5):
print(a1)
print(a2)
print(a3)
print(a4)
print(a5)
test(a4=4, a3=3, a2=2, a1=1, a5=5)
```
#### 函式回傳值
使用`return`關鍵字可以把函式內的資料往外傳遞給呼叫方
```python=
def plus(n1, n2):
# print(f'{n1} + {n2} = {n1 + n2}')
return n1 + n2
def compare(a1, a2):
if a1 > a2:
print(f'{a1}比{a2}大')
else:
print(f'{a1}沒有比{a2}大')
a = 12
b = 33
c = 21
d = 25
sum1 = plus(a, b)
sum2 = plus(c, d)
c = compare(sum1, sum2)
print(c)
```
#### 打包與解包
```python=
a = 1, 2, 3, 4, 5,
first, *other, last = a
print(first)
print(other)
print(last)
```
#### 變數資料交換
```python=
a = 1
b = 2
a, b = b, a
print(a)
print(b)
```
## 模組
###### util.py
```python=
import time
def get_current_date():
return time.strftime('%Y%m%d')
```
###### test.py
```python=
import util
now_str = util.get_current_date()
print(now_str)
```
#### 例外
```python=
import random
pc = random.randint(0, 10) # 產生0~10之間的隨機整數
print('答案:', pc)
while True: # 無窮迴圈
try: # 裡面的程式碼如果發生例外會被捕捉到
user = int( input('猜一個數字: ') )
if user == pc: # 猜對了
print('恭喜，猜對了')
break # 迴圈會直接結束掉，跳到12行執行
except ValueError: # 捕捉到發生例外
print('輸入錯誤，請重新輸入')
except:
print('發生錯誤，請重新輸入')
print('遊戲結束')
```
#### 隨機決定出場順序
```python=
import random
users = []
# 輸入名單
while True:
user = input('請輸出場學生名字(quit=結束): ')
if user == 'quit':
break
else:
users.append(user)
print(users)
# 抽籤決定出場順序
random.shuffle(users)
print('出場順序為:')
for u in users:
print(f' - {u}')
```
# 收集Google Trend關鍵字熱度
> #### 並將結果儲存到Excel檔案後繪製成折線圖
#### !!! 注意 !!!
因為關鍵字熱度Google Trend有非常嚴格的存取次數限制，而該限制目前不明，因此建議盡可能完成程式功能後再進行執行程式。
如果遇到錯誤代碼：429，即表示因為超過次數被Google Trend封鎖了，此時可以嘗試切換`geo`參數，例如：
```python=
pytrend.build_payload(kw_list=[keyword], cat=0, timeframe='today 12-m', geo='TW')
```
變成：
```
pytrend.build_payload(kw_list=[keyword], cat=0, timeframe='today 12-m', geo='JP')
```
>geo code清單（ISO-3166 alpha 2欄位）：https://www.geonames.org/countries/
## 需安裝的模組
| 模組名稱 | 安裝指令 | 說明 |
| -------- | ----------------------- | ---------------- |
| openpyxl | `pip3 install openpyxl` | Excel模組 |
| pytrends | `pip3 install pytrends` | Google Trend模組 |
#### 官方文件
pytrends: https://pypi.org/project/pytrends/#installation
openpyxl: https://openpyxl.readthedocs.io/en/stable/tutorial.html
## 常見例外
1. `pytrends.exceptions.TooManyRequestsError: The request failed: Google returned a response with code 429`
##### 說明
因為太頻繁用程式存取，被Google Trend網站擋住了。
2. `pytrends.exceptions.ResponseError: The request failed: Google returned a response with code 400`
##### 說明
呼叫Google Trend時，傳遞了錯誤的參數格式。
## 程式碼說明
```
pytrend.build_payload(kw_list=[keyword], cat=0, timeframe='today 12-m')
```
##### 說明：
設定Google Trend參數
##### 參數：
###### kw_list
要搜尋關鍵字清單，資料類型為list，可以輸入多個關鍵字
###### cat
要搜尋的類別
> 所有類別代號：https://github.com/pat310/google-trends-api/wiki/Google-Trends-Categories
###### geo
國家代碼，如果沒有提供則表示：全球
> geo code清單（ISO-3166 alpha 2欄位）：https://www.geonames.org/countries/
###### timeframe
要取得資料的日期區間：
- Defaults to last 5yrs, `'today 5-y'`.
- Everything `'all'`
- Specific dates, 'YYYY-MM-DD YYYY-MM-DD' example `'2016-12-14 2017-01-25'`
- Specific datetimes, 'YYYY-MM-DDTHH YYYY-MM-DDTHH' example `'2017-02-06T10 2017-02-12T07'`
- Note Time component is based off UTC
- Current Time Minus Time Pattern:
- By Month: `'today #-m'` where # is the number of months from that date to pull data for
- For example: `'today 3-m'` would get data from today to 3months ago
- **NOTE** Google uses UTC date as *'today'*
- **Works for 1, 3, 12 months only!**
- Daily: `'now #-d'` where # is the number of days from that date to pull data for
- For example: `'now 7-d'` would get data from the last week
- **Works for 1, 7 days only!**
- Hourly: `'now #-H'` where # is the number of hours from that date to pull data for
- For example: `'now 1-H'` would get data from the last hour
- **Works for 1, 4 hours only!**
```python=
data = pytrend.interest_over_time()
```
##### 說明：
取得指定時間內被搜尋的次數，回傳為Pandas的DataFrame物件
#### 程式碼
```python=
from pytrends.request import TrendReq
import openpyxl
from openpyxl.chart import LineChart, Reference
def gt(keywords):
# 建立一個Google Trend物件
pytrend = TrendReq()
# 設定要傳給google trend參數
# kw_list=關鍵字, 為一個list
pytrend.build_payload(kw_list=keywords, cat=0, timeframe='today 12-m', geo='TW')
# 連線到google trend取得該時間區間內的熱度資料
data = pytrend.interest_over_time() # 這行有可能會被擋掉
print(data)
# # 將資料存到excel
data.to_excel('google-trend-explorer.xlsx',sheet_name='GoogleTrend', index=True)
# 載入Excel檔案
wb = openpyxl.load_workbook('google-trend-explorer.xlsx')
# 取得目前work sheet
ws = wb.active
# 建立折線圖物件
chart = LineChart()
# 準備資料來源
ref = Reference(ws, min_col=2, max_col=ws.max_column-1, min_row=1, max_row=ws.max_row)
# 將資料加入圖表
chart.add_data(ref, titles_from_data=True)
# 設定圖表標題
chart.title = 'Google Trend'
# 設定y軸標題
chart.y_axis.title = '資料比數'
# 設定x軸的資料格式
chart.x_axis.number_format = 'yyyy/mm/dd'
# 設定圖表大小
chart.width = 15 # 單位: 公分
chart.height = 10 # 單位: 公分
# 修改樣式
chart.style = 20
# 建立x軸的標籤資料
xlabel = Reference(ws, min_col=1, min_row=2, max_row=ws.max_row)
# 設定x軸標籤
chart.set_categories(xlabel)
# 將圖表放到work sheet的E2上
ws.add_chart(chart, 'E2')
# 存檔
wb.save('google-trend-explorer-chart.xlsx')
keywords = []
while True:
k = input('請輸入關鍵字(quit=離開): ')
if k == 'quit':
break;
keywords.append(k)
gt(keywords)
```
#### 排程
```python=
from apscheduler.schedulers.blocking import BlockingScheduler
import time
# 排程方法1號
def job1():
print(f'工作1啟動: 目前時間{time.strftime("%Y-%m-%d %H:%M:%S")}')
def job2():
print(f'工作2啟動: 目前時間{time.strftime("%Y-%m-%d %H:%M:%S")}')
def job3():
print(f'工作3啟動: 目前時間{time.strftime("%Y-%m-%d %H:%M:%S")}')
def job4():
print(f'工作4啟動: 目前時間{time.strftime("%Y-%m-%d %H:%M:%S")}')
# 建立排程器
scheduler = BlockingScheduler(timezone='Asia/Taipei')
# 每分鐘執行一次job1
scheduler.add_job(job1, 'interval', minutes=1)
# 每5秒執行一次job2
scheduler.add_job(job2, 'interval', seconds=5)
# 每1分1秒執行一次job3
scheduler.add_job(job3, 'interval', seconds=1, minutes=1)
#
scheduler.add_job(job4, 'cron', hour=16, minute=00, day_of_week='0-6')
scheduler.start()
```
Sign in with Wallet
Connect another wallet