YouTube熱門視頻榜單Excel數據分析
本文結構如下：
Part 1. 數據獲取
- 數據集簡介
數據集是YouTube平台上Trending List中每日的視頻信息統計，包括視頻的標題、瀏覽量、發布時間、點贊數、評論數等。
時間跨度為2017年11月14日至2018年6月14日。涵蓋了美國、英國、德國、加拿大、法國等國家和地區，每個地區一個文件，考慮到excel所能處理的數據量，本文僅選取了美國的數據進行分析。
「Trending List」的YouTube官方中文翻譯是「時下流行」，可以理解為熱門視頻榜單。類似微博的熱搜機制，榜單內容在同一地區是相同的，不根據用戶個人的喜好而做個性化推薦。但Trending List並不僅根據播放量等單一指標排序，YouTube綜合了多種因素衡量用戶對視頻的互動熱度（包括播放量、分享數、評論和點贊等）。
- 數據來源
通過Google數據搜索引擎，在Kaggle上找到的數據：
Trending YouTube Video Statistics- 數據內容
數據集為csv格式，文件大小59.85M。數據共計16個欄位，40949條。具體欄位如下：
更詳細的數據集及YouTube介紹參見我的前一篇文章：
YouTube熱門視頻的數據集介紹
Part 2. 提出問題
- 視頻/短視頻運營常見指標
有了數據集後，我們可以根據數據集欄位，結合行業常用指標提出感興趣的問題用以分析。
所以在提出具體的問題前，首先需要對視頻/短視頻運營常見指標有一定的了解。大致可以分為兩大類，一類是針對單個視頻的指標，另一類則是對視頻IP整體的多維度衡量。對具體的指標和含義繪製了思維導圖如下：
顯然本文的數據集是針對單個視頻進行的統計數據，不適用IP指標的相關內容，故根據視頻指標部分進行對照：
該數據集不含有播放完成性相關的欄位；除了上述內容外，數據集在固有屬性中還含有視頻類別、標籤、描述等信息，這些都可以進行分析；且該數據集本身是trending榜單中視頻的時間順序統計，視頻可以多次上榜，分析時需要特別注意這一情況。
- 提出問題
綜上，提出問題如下，並在問題1中對互動行為的關聯指標也進行分析。
Part 3. 清洗數據
接下來進入正式的數據清洗步驟，數據清洗是一個反覆的過程；若增加了新的函數，還要檢查下是否產生了錯誤值，並針對性地解決。
3.0 亂碼和數據錯位的解決（編碼轉換）
一開始直接使用Excel打開了csv文件，並另存為xls格式，通過篩選功能，發現存在非英文字元的亂碼，且注意到有許多錯位(值不對位)的情況，如在僅存放布爾值的列，出現數值，在數值列出現鏈接等。
- 解決方式：
將csv文件轉為UTF-8-BOM編碼（使用notepad++軟體等），再用Excel打開存為xls格式，發現錯位和亂碼問題都得到解決。原因是源數據為ASCII編碼，但存在非拉丁字母，需要對編碼標準進行轉換。
3.1 json文件的轉化與整合
數據中含有視頻的category_id(類別編號)但並無其對應的category_title（類別名稱）。兩者間的對應關係直接採用了原始json格式上傳，未整合進主體數據文件中。為了方便進行分析，首先將json文件中的信息提取出來。
該json文件結構如圖（僅截取部分），深度不為1，嵌套較多，業務無關欄位較多，提取所需欄位root-items-id和root-items-snippet-title即可。
這裡採用筆者較為熟悉的python進行處理。代碼如下：
import json
import csv
jsonFile = open("US_category_id.json", "r")
categoryId = json.load(jsonFile)
with open(US_category_id.csv, w, newline=) as f:
csvWriter = csv.writer(f)
csvWriter.writerow([id, category])
for i in categoryId[items]:
print(i[id])
csvWriter.writerow([i[id], i[snippet][title]])
得到的csv文件如圖。
使用函數vlookup()將category_title（類別名稱）整合進主文件中，如圖：
3.2 選擇子集
隱藏了與前文所提問題無關的6個欄位：
title（標題）、channel_title（頻道標題）、thumbnail_link（縮略圖鏈接）、comments_disabled（是否允許評論）、ratings_disabled（是否允許打分）、video_error_or_removed（視頻是否損壞或移除）
3.3列名重命名
欄位命名較規範，能清晰表達數據含義，不再進行重命名。
3.4刪除重複值
重複值是表格中多餘的重複數據，常確定一列唯一值（類似SQL中的Unique鍵），並據此刪除重複值。
這裡需要注意的是，本數據中視頻ID的確是唯一確定視頻的欄位，但Trending榜單存在同一視頻多次上榜的情況；而同一天的Trending榜單是不變的，若某天內同一視頻多次出現則確為重複值。
也就是說我們非但不該直接根據視頻ID去重，視頻的重複出現反倒是很有挖掘價值的一個方向，但同時也有視頻ID唯一的分析需求。
因此，我們在數據清洗的最後，會再將表格分為原版（src.xls）與ID唯一版（id_unique.xls），並根據需要增加一些列。詳見3.6.5 首次/最近上榜日期 & 發布-上榜時間間隔（first/last_trending_date & pulish_trending_gap）。
這裡僅去除真正的重複值，也就是對video_id（視頻ID）和trending_date（trending日期）兩列聯合進行去重，如圖：
刪去了259個重複值，餘40690條有效數據：
3.5缺失值處理
Excel中選中列可在右下角查看計數。現有40690條數據，各列計數均相符，本數據無缺失值。
3.6一致化處理
接下來對數據進行一致化處理，對不利於分析的數據類型進行轉換，將信息量大的數據拆分，或根據分析需求增加一些欄位。
3.6.1 標籤數(tags)計數
tags這一列，多個標籤以「|」符號分隔並列，且除了每行首個標籤，其餘皆帶有雙引號，例如：Robots|"Boston Dynamics"|"SpotMini"|"Legged Locomotion"|"Dynamic robot"
要統計tags的個數，可以等價為統計同一單元格中「|」出現的次數再加上1。
如上圖，新增列「tags_num」，輸入函數=LEN(H2)-LEN(SUBSTITUTE(H2,"|",""))+1
通過函數substitute()將「|」替換為空，利用字元長度在替換前後的變化，求得被替換字元在單元格中出現的個數。
（也可以對「|」符號進行分列，並對分出來的列使用counta()函數統計非空單元格個數，從而達到統計標籤個數的目的。）
3.6.2 視頻描述(description)的長度
如下圖，新增列「description_len」，可以直接使用函數len()統計description（描述）的字元長度。
3.6.3 上榜日期(trending_date)格式的轉換
trending_data的日期是字元串格式，且採用了歐美習慣的yy/dd/mm（年/日/月）格式，需將其轉換為符合我們習慣的日期格式。
使用left()函數取出兩位年份，並用"&"連接符在前面補上"20"，使年份完整；用right()、mid()函數分別取出月份和日期，再用"&"將"/"符號連在年月日中。
為了excel能正確識別日期，再用datevalue()函數，對前面得到的結果進行進一步處理。
3.6.4 發布時間(publish_time)拆分日期和時間
publish_time同時包含日期和時間，例如: 2017-11-13T17:13:01.000Z
將其分為publish_date和publish_time，便於處理。
該欄位位數固定，可以採用分列進行操作，直接對固定位置分列並設置相應的數據格式（如圖），或對分隔符"T"和"."進行分列皆可。需要注意的是分列會直接覆蓋右側的列，需要預留相應的空白列，或將該列移至最後。再將多餘的列刪去。
如用類似3.6.3中的方式，使用函數find()配合left()、mid()處理也可以。
同樣為使excel能正確識別時間，再使用timevalue()函數，進行進一步處理，此處不再贅述。
3.6.5 首次/最近上榜日期 & 發布-上榜時間間隔（last/first_trending_date & pulish_trending_gap）
前文3.4刪除重複值一段中已經說過，同一視頻可能多次登上trending榜單，根據分析的不同需求，我們最好同時保留原版（src.xls）與ID唯一版（id_unique.xls），並給id_unique.xls增加一些列。
首先對原版（src.xls）的視頻ID進行重複值的刪除，得到ID唯一版（id_unique.xls）。
刪去了34409條重複值，得到了6281條數據。
那麼，我們究竟需要哪些新列呢？
首先，我們很自然地產生出新的疑問：trending推薦對同一視頻來說是連續的么？
要回答這個問題，需要知道視頻的 首次/最近上榜日期（first/last_trending_date），並計算出差值（trending_gap），與視頻ID計次得到的上榜次數（trending_times）進行對比（對比需要新增一輔助列judgement），若一致則說明trending榜單推薦對同一視頻來說是連續，若存在不一致則反之。
此外，為了探究 發布日期（publish_date）和 首次上榜日期（first_trending_date）間的關係，我們再構造一列publish_trending_gap，用來指代兩者的間隔時間。
綜上，我們需要first_trending_date、last_trending_date、trending_gap、trending_times、publish_trending_gap、輔助列judgement六個新列。
- 首次/最近上榜日期（first/last_trending_date）
在Excel2016里，使用刪除重複值功能時，excel只保留第一個值。
利用這個特性，我們將原版（src.xls）中視頻id和trending_date兩列複製到新的工作表中，對trending_date進行升序排列（對日期來說就是從舊到新），再勾選視頻id刪除重複值，僅保留了每個視頻最舊的trending日期，即first_trending_date。再使用函數vlookup()整合到id_unique.xls中。
Last_trending_date僅在對trending_date排列時使用降序，其他為完全相同的操作。
- 首次-最近上榜時間間隔（trending_gap）
直接將last_trending_date減去first_trending_date再加1即可，單元格格式為常規。
- 發布-首次上榜時間間隔（pulish_trending_gap）
直接將first_trending_date減去publish_date再加1即可，單元格格式為常規。
- 上榜次數（trending_times）
在原版數據（src.xls）中採用函數countif()統計視頻id的出現次數，再使用函數vlookup()整合進id_unique.xls中，命名為trending_times。
- 輔助列（judgement）
構造輔助列judgement來判斷trending_gap和trending_times是否相等，輸入函數=IF(E2=F2,"equal","unequal")
篩選發現在6281條記錄中有534條不等，現在我們可以回答本節初提出的小問題了，trending推薦對同一視頻來說並非必須是連續的，但連續的情況確實佔了大多數。
Part 4. 分析與可視化
為了讓分析部分盡量簡潔，後文不再對具體的excel操作進行詳述。
主要用到了數據透視表、圖表、數據分析功能。
數據透視表功能：插入-數據透視表。插入數據透視表時會包含隱藏的列/行/單元格，若只想對可見單元格進行分析，可以按「Alt」+「;」鍵選中可見單元格，並複製粘貼到新表再插入數據透視表。
圖表功能：選中需要的數據，插入-圖表-所有圖表。選擇需要的箱型圖、柱狀圖、散點圖、組合圖等。
數據分析功能：首次使用需先載入工具庫，文件-選項-載入項-excel載入項-轉到分析工具庫。在數據-分析-數據分析中調用。本文只使用了其中的相關係數計算。
4.1 Views/likes/dislikes/comment_count的相關係數與指標構建
- 相關係數
通過excel的「數據分析」功能，對views、likes、dislikes、comment_count四個欄位計算相關係數，這裡需要使用的是視頻id唯一的數據文件id_unique.xls。
可以看出，點贊數likes與評論數comment_count有著最高的正相關性(r=0.81)，緊隨其後的是點贊數likes與瀏覽量views(r=0.78)，這是比較符合我們的直覺的，瀏覽量高的視頻受到用戶的喜愛，同時用戶樂於在喜歡的視頻下評論。
有趣的是，不喜歡數dislikes與評論數comment_count同樣具有較高的正相關性(r=0.68)。看來令許多人不喜的視頻同樣能引發廣泛的討論。
- 指標構建
接下來我們構建一些簡單的指標，比如likes/dislikes（喜歡/不喜歡）、views/comments（觀看數/評論數）。
通過數據透視表對各個類別category的視頻分別統計likes和dislikes之和，增加計算欄位likes/dislikes，對該欄位降序並繪製柱狀圖。
1.類別Pets & Animals（寵物與動物）擁有最高的likes/dislikes比值（56.43），可見寵物著實惹人喜愛，少有人對萌寵題材的視頻產生負面情緒。
2.類別New & Politics的likes/dislike比值最低，僅為4.05，倒是很符合美國人對政治不滿的風格。
3.最特別的當屬Nonprofits & Activism（非營利組織與激進主義），likes/dislike比值位居倒數第二，同時comments/views高得出奇，幾乎是其他類別的5-6倍。看來此類視頻的爭議性很大，許多人討厭該題材，並引發了評論區的諸多爭論。
4.2視頻的標籤數越多越好么？視頻描述越詳細越好么？
將tags_num與description_len同4.1中的4個欄位計算相關係數。
可以看到除了tags_num與description_len兩者之間有低度相關外，它們與其他4個欄位的相關係數絕對值都小於0.05，不存在線性相關。看來增加標籤數量和描述長度並不能使視頻獲得瀏覽量、點贊數、評論數方面的收益，當然也沒有產生負面作用。
對於標籤數tags_num繪製箱型圖，從上圖左側可以看出，總體來說trending榜單上的視頻標籤數集中在10-29個之間。再分類別繪製箱型圖，發現shows類別的上傳者最喜歡打標籤。
對視頻描述長度description_len繪製箱型，字元長度集中在388-1348之間。
同樣對其分類別繪製箱型圖，發現其中education教育類和how to & style教程類視頻的上傳者在寫描述時里最為「話癆」。(嗯，這看起來和教育類以及教程類的特性還是很相符的嘛…
4.3 什麼類別登上trending榜單的次數/視頻數量最多？
對類別和trending_times求和項、video_id計數項拉取數據透視表，並繪製柱狀/折線組合圖。
1.無論從視頻數量還是上榜次數來看，entertainment娛樂類都佔據了trending榜單的第一，甩開了其他類別一大截。娛樂類是大眾接受度最高，同時也是熱度最高的類別。
2.整體趨勢上看 trending次數與trending的視頻數量有很強的正相關關係。
3.此外注意到，其實有很多類別完全沒有登上過trending榜單。
4.4 Trending榜單上的視頻通常能上榜多少次？
對trending_times繪製箱型圖。
無論是總體來看還是分類別查看，絕大多數視頻的上榜次數都集中在3-8次。
但同時也存在不少離群點，說明仍有一定數量的視頻能反覆十餘次甚至二十餘次上榜。
類別shows，結合前文來看，雖然只有4支視頻登上trending榜單，但上榜的平均次數反倒是最多的。
4.5 視頻的發布日期與首次上榜（trending）日期之間是否存在某種關係？
首先讓我們來看看publish_trending_gap的柱形圖
為了探究trending_times和publish_trending_gap之間的關係，再繪製散點圖。
非常有趣，我們發現了居然有4215天（近12年）後才首次登上trending榜單的視頻，並且此類「大器晚成」的視頻並不算罕見，365天以上才被推薦的共有69個。
更有意思的是，上榜12次以上的視頻無一例外都是在2天內就首次上榜的。看來爆款視頻從出生起就閃耀著爆款的光芒。
4.6 是否存在視頻發布的黃金時間（每周/每天中是否存在周期性的高熱度）？
- 每周
對發布時間publish_date使用weekday()函數，參數return_type選擇2（周一作為每周的第一天），得到代表星期的對應數值，星期一為1，星期二為2，以此類推。
插入數據透視表，對trending_times的總和值和視頻個數，繪製柱形-折線組合圖。
發現周末發布的視頻反而在trending榜上居於劣勢，無論從視頻數量還是次數來說都是如此。而周一到周五發布的視頻，表現明顯優於周末，且較為平均。
- 每日
拉取數據透視表，對publish_time同時繪製trending_times和視頻數量的折線圖，發現trending榜單中14時-18時發布的視頻佔了絕大多數，特別是16時前後更是達到了頂峰，晚間發布的視頻也較多，凌晨1點後至上午12時前進入視頻發布的低谷期。
但這裡缺少YouTube總體視頻的發布時間進行對比，不能斷言究竟是16時本就是視頻發布高峰期，使得trending榜單呈現類似的分布，抑或這真的是一個黃金髮布時間。
但不論如何，可以肯定的是16時都是一個很值得注意的視頻發布時間。
Part 5. 總結
推薦閱讀：