學習完Power BI,馬上來進行實作囉~在思考要使用什麼主題,發現可以在Power BI使用ArcGIS的功能,是我擅長的空間分析,而且政府提供蠻多Youbike開放資料,實作上更加便利!
本篇實作將會介紹如何產出以下統計圖及地圖:
- 2022年臺北市Youbike2.0租借次數,按行政區統計
- 2022年Youbike1.0跟Youbike2.0各月份租借次數比較
- 2022年Youbike2.0租借量地圖(Bubble Map)
先來看看成品! ✨✨✨
接下來開始詳細步驟!實作檔也放在我的github。
🌎將以下csv匯入到power BI:
- 2022年各月Youbike2.0刷卡紀錄csv檔 (source: 臺北市公共自行車2.0租借紀錄)
- Youbike2.0 站點資訊的csv (source:YouBike2.0臺北市公共自行車即時資訊 )
- 2021年Youbike1.0刷卡紀錄各月統計csv檔 (source:臺北市公共自行車租借紀錄)
🌎前處理:
- csv 檔刪除不必要的欄位: 刷卡紀錄都是幾百萬筆資料,刪除不必要的欄位在計算時才會加速。
- json to csv: Youbike2.0 站點資訊json檔轉成csv檔
- 自建Youbike1.0 各月份租借次數csv檔
- 在Power BI新增日期表(Date),作為模型關聯及篩選月份用(參考Exercise 4),日期範圍為2022年。
Table = CALENDAR(DATE(2022,1,1),DATE(2022,12,31))
5. 新增行政區的表,並將其中欄位命名為sarea(模型關聯用)
行政區 = DISTINCT('Youbike2 0'[sarea])
6. 模型關聯(如左圖所示):
行政區| sarea → 2022月份_Modify| area
Date | Date → 2022月份_Modify| 日期
7. 匯入及前處理完的資料如右圖所示
🌎 2022年臺北市Youbike2.0租借次數
📑合併查詢
為了要統計某月份各行政區的刷卡紀錄,需藉由Youbike2.0刷卡紀錄csv檔來統計,但此資料只有站點名稱並沒有該站點的行政區資料,故需透過Youbike2.0 站點資訊的csv檔來進行站點名稱的匹配,以獲得行政區的欄位,在Power BI中可以使用合併查詢這個功能來完成。
- 將需要的csv匯入至power BI desktop,點擊轉換資料開啟power query編輯器,在power query中對各csv進行整理,例如變更型態、移除不必要欄位等。
- 合併查詢(常用→合併查詢):
- 合併查詢就是將兩張表根據相同或相似欄位進行連結,建立合併的資料表,再選擇所需要的欄位。
- 在Youbike2.0票證刷卡資料的table,點擊合併查詢後會出現合併的窗格,選取Youbike2.0票證刷卡資料的rent_station 欄位(即我們要比對的欄位),下拉選單選擇另一張table,Youbike2.0,選擇station的欄位。
- 因為這兩張表的這兩個欄位值並不是完全一樣,所以我們要進行模糊比對
- 在下方勾選使用模糊比對來執行合併
- 模糊比對的選項中,有相似性閾值,就是相似度達多少時進行匹配,default是0.8
- 最後比對的結果會在Youbike2.0票證刷卡資料裡新增一個欄位,在欄位title右側篩選我們要保留的值,選擇sarea(行政區),最後就是各station的行政區啦~
📑分組依據
接下來要計算各行政區的租借數,可以在power query計算。BUTTTTT!!這Part當作參考就好~因為資料量太大,如果直接在power query進行計算會跑非常久。想看快方法直接跳下一Part~
- 在power query中常用 → 分組依據 ,打開分組依據的窗格(左圖),上方那列表示是要以哪欄為依據,選擇Youbike2.0.sarea的欄位,接著下方那列是要輸入新增的資料行名稱,在新資料行名稱中輸入計數,作業選擇計算列數,欄則空白,因為是要計算本欄各行政區的個數。
- 最後會跑出來各行政區的租借次數(中圖)
💡 null值是因為Youbike2.0.csv沒有該站點的資料,並非模糊比對失敗。
可以在站點資料自行補充站點資訊,但在本實作基於時間考量就不補齊資料了。
- 選擇套用並關閉power query後,在視覺效果的窗格加入群組直條圖,並將新分組的資料移入視覺表格中,視覺化的直方圖就跑出來囉~可以改顏色、格式、標題等。
📑 視覺效果
要計算各行政區的刷卡數,可以在Report View直接拉圖加入值
- 在視覺效果的窗格加入堆疊直條圖
- 將行政區的sarea放入X軸
- Y軸放入所有月份的rent_station計數
- 即可以快速產生2022年所有月份各行政區的租借次數直條圖了!!
💡本來要使用python,但是資料量太大了QQ,cpu跟記憶體太爛跑不動,但程式也寫好了,可以參考看看。
Excel運算速度蠻快的,用VLOOKUP+樞紐分析很快就跑好了,但是因為我沒有Power Pivot增益集
以匯入超過1048575列的資料,所以只好用Power BI,順便熟悉操作也不錯。
(ps. 本來用Power BI也超級慢,但發現更快的方法後就咻咻咻覺得Power BI超好用)
🌎 2022 年臺北市Youbike2.0租借次數與2022年Youbike1.0租借次數
要計算2022年每個月份的租借次數,可以在原始資料上藉由計數來計算每個table的資料筆數。
- 新增一個matrix visual(矩陣),將值欄位移入欲統計的月份租借資料(area),並在下拉選單選擇計數
- 各月份租借次數的matrix如下所示,將matrix visual匯出成csv檔(2022TotalCount.csv),再轉置後匯入
- 新增一群組直條圖,將新匯入的csv拖拉進visual,再將Youbike1.0的各月份統計資料也匯入,下圖即為1.0和2.0租借次數比較圖。
💡 1.0的租借資料只到2022年9月
🌎 2022年Youbike2.0租借量地圖
現在要建立2022年租借量的Bubble Map,在地圖上不只是呈現該站點位置,圓點大小以該站點在該月份的租借量來呈現。
📑管理關聯性(模型化→ 管理關聯性)
Youbike2.0.csv有經緯度資料及各站點名稱,將2022年各月份租借資料進行關聯,方便接下來創建地圖使用。
💡在Exercise 3也有實作過,忘了可以去看看。
💡或是在Model View中直接將要連接的欄位拖拉過去對應欄位。
📑 附加查詢
現在要將202201_Modify~202212_Modify合併,在power query中使用附加查詢→將查詢附加為新查詢,彙整為1–12月的租借資料,目的為接下來使用ArcGIS要顯示該站點每月使用量,能夠根據月份來進行篩選。
📑 連結ArcGIS
接下來是要來製作地圖了,在視覺效果點選ArcGIS Maps for Power BI,將經緯度、站點名稱、加入欄位中,尺寸則加入附加查詢的1-12月租借資料如下所示:
💡 需有ArcGIS online帳號並登入。
- 結果像這樣~滑鼠移到bubble上可以看到詳細資訊
- 也可以在Power BI 匯入在ArcGIS online 製作好的地圖,如下圖是在ArcGIS online用Calculate Density所製作的熱點圖,再匯入ArcGIS Maps Power BI 的圖層即可。
🌎 結果分析
互動式地圖完成了!加入月份及行政區篩選,可以更了解該月份或某行政區的統計狀況。
- 2022年臺北市Youbike2.0租借次數,按行政區統計
在直條圖可以發現各行政區的租借狀況,不管哪個月份都以大安區為租借次數最高的行政區。依次是中正區、中山區、信義區為前幾名的租借量。
- 2022年Youbike 1.0 及 Youbike 2.0 租借次數比較
由於近年來Youbike2.0站點增設,可以觀察到Youbike2.0 租借次數有總體往上的趨勢,而在2022年,Youbike 2.0 租借次數已經遠大於Youbike1.0的租借次數。Youbike1.0的總租借數則可達五百萬次,而Youbike2.0總租借量可高達4千萬次。
看完覺得還不錯或是有需要交流的地方歡迎留言給我,不要忘記拍拍手支持一下!👏👏