Yi's Blog

目之所及,尽是萌芽

YouTube 观看历史分析

来到美国之后,访问 YouTube 不再需要翻墙了,看的视频也越来越多。后来办了 YouTube Premium,看视频没有广告,更是把 YouTube 视频当成了日常生活的背景音,一刻也不闲着,生活在刷 YouTube 视频中度过。

视频看得多了没觉得有什么问题,毕竟视频作为一个传播的媒介,有许多文字和声音无法媲美的特性,好的视频能在短时间内传递大量的信息,并且让人记忆犹新。

我看 YouTube 主要是以下几类视频:

  • 科技测评博主。以美国的为主,美国的刷干净了,也会刷一些国内的博主。
  • 国内的综艺节目。每年都会追一些新的综艺,大部分追的综艺都会把视频上传到 YouTube 官方的频道上,少部分没有官方更新的,也会有网友把自己翻录的节目发到 YouTube 上,赚点广告费。
  • 国内的连续剧。偶尔看一些国内的剧集,新的旧的都有。
  • 都市传说。主要是老高,大连口音,质量上乘,无法拒绝。
  • 哔哩哔哩博主。这个主要是靠 YouTube 推荐,极其偶尔会翻出来主动去看。
  • 音乐。有一些国内的歌在 Spotify 上找不到,YouTube 是很好的补充。
  • 其他。

不使用其它视频网站主要是以下几个原因:1、视频内容太过单一化,和 YouTube 上的百花齐放无法媲美,举个简单的例子,单单就是在潜水捞东西的视频,在 YouTube 上一搜都是一大把,而且制作精良,节奏轻快,让人看了舒服。2、视频质量不行。3、和 YouTube 相比,播放器做得太渣,一个简单的例子就是在 YouTube 快进和快退时非常稳定,几乎没有遇到视频加载好了,但是快进快退卡住的情况。

和刷微信、微博、Twitter、论坛(主要是 V2EX)一样,视频刷多了要自我反省。首先是反省自己是在主动获取信息,还是被动接受信息。信息获取分为两种,一种是主动获取,比如说使用搜索引擎,一种是被动接受,比如说看电视。既然是刷,尽管是在一个列表中有所选择,列表是被钉死的,所以还是被动接受得比较多。被动接受的比较多就要相对警惕,因为信息的输入被人控制,稍不留神就会被人“精神控制”,通过在 Facebook 投放广告来操控选举就是一个具体的例子。其次是控制自己的观看时间。适度被动地接受 YouTube 的信息,不去考虑和选择看什么,可以缓解疲劳,但是如果长时间的观看,难免会信息过载,感到疲劳。另外,在 YouTube 上,我收看的大部分视频都被在 15 分钟以内,最终导致的结果就是,在点开每一个视频时,我的心态是这个视频很短,花不了一会儿我就看完它了,结果一个又一个地点下去,几个小时的时间过去了。

反省说白了就是少看。少看说起来简单,做起来难。首先,我需要对自己看了多久、多少个 YouTube 视频有个清醒的认识。连看了多久、看了多少都不知道,又何谈少看?有了具体的衡量方法之后,才好制定一个具体的目标。

数据收集

说干就干。相比其它网站,YouTube 还算厚道,可以在 Google Takeout 里下载完整的观看历史。数据格式选择 JSON 后,导出。稍等片刻,收到邮件告知数据准备完毕。下载后,每条观看历史如下:

[{
  "header": "YouTube",
  "title": "Watched Apple Watch 5 Review: Three Months Later",
  "titleUrl": "https://www.youtube.com/watch?v=gWC12xemfDE",
  "subtitles": [{
    "name": "Rene Ritchie",
    "url": "https://www.youtube.com/channel/UC3rK4_AbQfu1Lv9GI1tKp4A"
  }],
  "time": "2019-12-22T23:03:51.237Z",
  "products": ["YouTube"]
  },
  ...
]

我的观看数据量不算大,几年下来也只有 12780 条。存储到数据库方便查询,数据库选的 SQLite,零配置,简单直接。建了三个表 channelvideohistory,后续可以补充更多信息(比如说视频的长度和点击量):

CREATE TABLE "channel" (
  "channel_id" TEXT NOT NULL PRIMARY KEY,
  "name" TEXT NOT NULL
);

CREATE TABLE "video" (
  "video_id" TEXT PRIMARY KEY NOT NULL,
  "title" TEXT NOT NULL,
  "channel_id" INTEGER NOT NULL,
  "duration" INTEGER,
  "created_timestamp" INTEGER,
  "view_count" INTEGER
);

CREATE TABLE "history" (
  "timestamp" INTEGER NOT NULL PRIMARY KEY,
  "video_id" TEXT NOT NULL
);

JSON 文件简单处理一下,扔到数据库里。代码比较简单,值得一说的有以下两点:

转换时间戳

JSON 文件里的时间戳是字符串格式,直接存在数据库里不好查询,需要转成 Epoch。

2007-03-04T21:08:12Z -> 1173042492

比较麻烦的是有的时间戳有毫秒,有的没有,不能直接用 %Y-%m-%dT%H:%M:%S.%f%z 或者 %Y-%m-%dT%H:%M:%S%z来解析。还好有一个 dateutil.parser 覆盖了两种情况(代码来自 Stack Overflow):

import dateutil.parser
yourdate = dateutil.parser.parse(datestring)

获取视频信息

视频的长度和观看量是两个比较有趣的维度,可以用来分析我观看视频的时间长度分布和观看量分布。导出的数据里没有这部分信息,需要额外抓取。尝试了两种方式来获取信息:首先是 YouTube 自己提供 YouTube Data API,其次是通过 youtube-dl。

YouTube Data API 很方便。Google Cloud Console 里申请一个 API Key 后,就可以直接请求了。

https://www.googleapis.com/youtube/v3/videos?part=contentDetails,status&id={video_id,another_video_id}&key={api_key}

详细用法可以参考官方文档。这里需要额外注意的是这个 API 返回的 duration 用了一种特殊的格式,比如 P1W2DT6H21M32S,需要额外的代码才能将其转化为秒。我需要下载 12000+ 条视频的信息,超出了默认的单日 Quota,申请更多 Quota 又比较繁琐,最后就放弃了这个方法。

youtube-dl 是用 Python 实现的,API 可以在 Python 中直接调用:

import youtube_dl

ydl = youtube_dl.YoutubeDL({'outtmpl': '%(id)s%(ext)s'})

with ydl:
    result = ydl.extract_info(
        'http://www.youtube.com/watch?v=BaW_jenozKc',
        download=False # We just want to extract the info
    )

if 'entries' in result:
    # Can be a playlist or a list of videos
    video = result['entries'][0]
else:
    # Just a video
    video = result

print(video)
video_url = video['url']
print(video_url)

唯一的缺点是视频的信息要一条一条的拉取,而且有一些视频因为 copyright 的原因拿不到信息:

ERROR: This video contains content from Apple, who has blocked it on copyright grounds.

因为只是少量的视频有个问题,索性就不抓取这些视频的信息了。youtube-dl 走起。

代码

from datetime import datetime
import dateutil.parser
import json
import os
import requests
import sqlite3
import youtube_dl

json_file_path = '/Users/jswang/Downloads/Takeout-2/YouTube/history/watch-history.json'
sqlite_file = '/Users/jswang/Desktop/youtube.db'
video_detail_cache_folder = '/Users/jswang/Desktop/cache/%s'
video_url = 'http://www.youtube.com/watch?v=%s'

conn = sqlite3.connect(sqlite_file)

def insert_channel(name, channel_id):  
  c = conn.cursor()
  c.execute("INSERT OR IGNORE INTO channel (channel_id, name) VALUES (?, ?)", (channel_id, name))

def insert_video(title, video_id, channel_id):
  c = conn.cursor()
  c.execute("INSERT OR IGNORE INTO video (video_id, channel_id, title) VALUES (?, ?, ?)", (video_id, channel_id, title))

def insert_history(timestamp, video_id):
  c = conn.cursor()
  c.execute("INSERT OR IGNORE INTO history (timestamp, video_id) VALUES (?, ?)", (timestamp, video_id))
  
def update_video_info(video_id, duration):
  c = conn.cursor()
  c.execute("UPDATE video SET duration=? WHERE video_id=?", (duration, video_id))

def populate_database():
  ydl = youtube_dl.YoutubeDL({'outtmpl': '%(id)s%(ext)s'})
  
  with open(json_file_path) as json_file:
    data = json.load(json_file)
    for history in data:
      if "subtitles" not in history or "titleUrl" not in history or not history["title"].startswith("Watched "):
        continue
      
      channel_id = ""
      for subtitle in history["subtitles"]:
        channel_id = subtitle["url"].replace("https://www.youtube.com/channel/", "")
        insert_channel(subtitle["name"], channel_id)
      
      title = history["title"].replace("Watched ", "")
      video_id = history["titleUrl"].replace("https://www.youtube.com/watch?v=", "")
      insert_video(title, video_id, channel_id)

      duration = get_video_duration(ydl, video_id)
      update_video_info(video_id, duration)

      timestamp = int(dateutil.parser.parse(history["time"]).timestamp())
      insert_history(timestamp, video_id)

def get_video_duration(ydl, video_id):
  file_path = video_detail_cache_folder % video_id
  url = video_url % video_id
  
  if not os.path.exists(file_path):
    try:
      result = ydl.extract_info(url, download=False)
    except:
      return -1
      
    with open(file_path, 'w') as f:
      f.write(json.dumps(result))

  with open(file_path) as json_file:
    data = json.load(json_file)
    return data["duration"]

populate_database()

conn.commit()
conn.close()

数据分析

下面两个图分别是每年观看视频的数量和 2019 年观看视频的时间段。

graphs

基本上和我的直观体验一致:

  • 需不需要翻墙是一个重要指标,需要翻墙,又没有稳定的梯子(2011-2015)就会明显减少 YouTube 视频的观看量,选择观看一些其它的视频网站。
  • 2018 年和 2019 年明显看得更多了,每年 5000+。2017 年那会还看很多斗鱼的主播, YouTube 相对看得少。
  • 每天早起 7 点到 9 点醒了不起床,在床上刷视频。
  • 每天 7 点至 8 点到家后,刷视频++。

下面一个图是视频长度的分布:

video length

大部分观看的视频(82%)都在 20 分钟以内。因为看了很多歌曲的视频,最高点在 5-6 分钟之间。11 分钟有一个小的凸起,主要来自于一些自媒体的视频。

画图代码:

import matplotlib.pyplot as plt
import sqlite3

def plot_video_length():
  x = []
  y = []

  c = conn.cursor()
  c.execute("""
  SELECT 
    duration / 60, COUNT(1) 
    FROM video 
    WHERE duration > 0
    GROUP BY 1;
  """
  )

  rows = c.fetchall()

  for row in rows:
    x.append(row[0])
    y.append(row[1])

  plt.plot(x,y, marker='x')
  # plt.xticks(x)
  plt.xlim(0, 180)
  plt.xlabel('Length')
  plt.ylabel('Video Count')
  plt.title('YouTube Length Distribution')
  plt.legend()

def plot_yearly_count():
  x = []
  y = []

  c = conn.cursor()
  c.execute("""
  SELECT
    STRFTIME('%Y', DATE(timestamp, 'unixepoch')) AS year,
    COUNT(1)
  FROM
    history
  GROUP BY 1;
  """
  )
   
  rows = c.fetchall()

  for row in rows:
    x.append(row[0])
    y.append(row[1])

  plt.bar(x,y)
  plt.xticks(x)
  plt.xlabel('Year')
  plt.ylabel('Video')
  plt.title('Yearly Watched YouTube Videos')
  plt.legend()

def plot_watch_hour():
  x = []
  y = []

  c = conn.cursor()
  c.execute("""
  SELECT
    STRFTIME('%Y', DATETIME(timestamp, 'unixepoch', 'localtime')) AS year,
    STRFTIME('%H', DATETIME(timestamp, 'unixepoch', 'localtime')) AS hour,
    COUNT(1)
  FROM history GROUP BY 1, 2 HAVING year = '2019';
  """
  )
   
  rows = c.fetchall()

  for row in rows:
    x.append(int(row[1]))
    y.append(int(row[2]))

  plt.bar(x,y)
  plt.xlabel('Hour')
  plt.ylabel('Video')
  plt.title('Watch Hour (2019)')
  plt.legend()

sqlite_file = '/Users/jswang/Desktop/youtube.db'
conn = sqlite3.connect(sqlite_file)

plt.rcParams["figure.figsize"] = (12, 4)

plt.subplot(1, 2, 1)
plot_yearly_count()

plt.subplot(1, 2, 2)
plot_watch_hour()

plt.subplots_adjust(wspace=0.2)

# plt.show()
plt.savefig("/Users/jswang/Desktop/graphs.svg", format="svg")

plt.clf()

plot_video_length()
# plt.show()
plt.savefig("/Users/jswang/Desktop/video_length.svg", format="svg")

conn.commit()
conn.close()

以下是观看视频数量最多的 25 个频道:

排名 频道 观看视频数量
1 湖南卫视芒果TV官方频道 China HunanTV Official Channel 295
2 优酷 240
3 一条 220
4 Marques Brownlee 208
5 Dave Lee 204
6 SMG上海电视台官方频道 SMG Shanghai TV Official Channel 187
7 The Verge 185
8 芒果TV音乐频道 MGTV Music Channel 180
9 腾讯视频 154
10 CaseyNeistat 144
11 老高與小茉 Mr & Mrs Gao 125
12 中国东方卫视官方频道China DragonTV Official 119
13 Unbox Therapy 116
14 FirePanda 111
15 ZHONG.TV 109
16 浙江卫视音乐频道 ZJSTV Music Channel 105
17 中国浙江卫视官方频道 Zhejiang STV Official Channel 89
18 Linus Tech Tips 81
19 TESTV 80
20 钟文泽 80
21 花花与三猫 Cat Live 71
22 吃货请闭眼官方频道——Justeatit Official Channel 60
23 滾石唱片 ROCK RECORDS 58
24 灿星官方频道Canxing Media Official Channel 58
25 Nintendo 57

统计代码:

import sqlite3

sqlite_file = '/Users/jswang/Desktop/youtube.db'

conn = sqlite3.connect(sqlite_file)

c = conn.cursor()
c.execute("""
SELECT "|" || ROW_NUMBER() OVER (ORDER BY c DESC) || "|" || "[" || name || "](https://www.youtube.com/channel/" || channel_id || ")|" || c || "|"
FROM (
  SELECT 
    channel.name, 
    channel.channel_id, 
    COUNT(1) AS c FROM history
  LEFT JOIN video ON
    history.video_id = video.video_id
  LEFT JOIN channel ON
    video.channel_id = channel.channel_id
  GROUP BY 1
  ORDER BY c DESC
  LIMIT 25
)
""")

rows = c.fetchall()

for row in rows:
  print(row)

conn.commit()
conn.close()

以下是时长最多的 25 个频道。这里有一个问题,从 YouTube 上下载下来的数据并没有看了多久的信息,所以视频都是按照全部观看了来计算的。

排名 频道 观看时长(小时)
1 湖南卫视芒果TV官方频道 China HunanTV Official Channel 344.44
2 SMG上海电视台官方频道 SMG Shanghai TV Official Channel 195.36
3 优酷 170.29
4 腾讯视频 167.66
5 中国东方卫视官方频道China DragonTV Official 117.83
6 中国浙江卫视官方频道 Zhejiang STV Official Channel 94.14
7 commaai archive 78.80
8 浙江卫视【奔跑吧】官方频道 ZJSTV Keep Running Channel 38.41
9 浙江卫视音乐频道 ZJSTV Music Channel - 欢迎订阅 - 34.60
10 Marques Brownlee 33.57
11 捷成华视—偶像剧场 Idol & Romance 30.99
12 老高與小茉 Mr & Mrs Gao 30.57
13 欢乐传媒官方频道丨The Official Channel of Joy Entertainment 28.26
14 中国好声音官方频道SING!CHINA Official Channel 24.27
15 CNET 23.86
16 The Verge 23.18
17 Gamker攻壳官方频道 23.15
18 DocFirebird 22.41
19 再难看到的经典老节目回顾 22.20
20 CaseyNeistat 21.29
21 灿星官方频道Canxing Media Official Channel 21.07
22 芒果TV音乐频道 MGTV Music Channel 20.40
23 Dave Lee 20.17
24 Nintendo 19.31
25 TESTV 19.09

统计代码:

import sqlite3

sqlite_file = '/Users/jswang/Desktop/youtube.db'

conn = sqlite3.connect(sqlite_file)

c = conn.cursor()
c.execute("""
SELECT "|" || ROW_NUMBER() OVER (ORDER BY c DESC) || "|" || "[" || name || "](https://www.youtube.com/channel/" || channel_id || ")|" || printf("%.2f", c) || "|"
FROM (
  SELECT channel.name, channel.channel_id, SUM(video.duration) / 3600.0 AS c FROM history
  LEFT JOIN video on history.video_id = video.video_id
  LEFT JOIN channel on video.channel_id = channel.channel_id
  WHERE video.duration > 0
  GROUP BY 1, 2
  ORDER BY c DESC
  LIMIT 25
)
""")

rows = c.fetchall()

for row in rows:
  print(row)

conn.commit()
conn.close()

总结

最近半年因为工作的原因,做了一些和数据相关的工作、写了一些 SQL,对于 Google 内部用来处理数据和分析数据的工具,有了更多的了解,对于为什么要收集数据、收集什么数据、怎么处理收集到的数据、从数据中我们能学到什么,有了更多的认识。

上面也算是我对自己的一个检验,检验在离开 Google 内部工具之后,我还能不能做一些简单的工作。实践证明会麻烦很多。从有这个想法,到准备数据,到学习怎么画图,再到此刻下笔写作总结,大概花了一整天时间,如果在公司的话,估计最多一两个小时就搞定了。

发几句牢骚的话,一是 YouTube 导出 JSON 文件缺少文档描述。二是导出的信息比较有限,连看了多久这种最基本的信息都没有。最后抱怨一下其它网站,Google 上的数据至少还有得下载,其它网站我还没见有这功能,就算有,也藏得深之又深,生怕用户拿去给了别家,譬如说,最简单的,谁能告诉我怎么在淘宝一键导出购买记录。我不反对你把我的数据包装成产品到处销售使用,最少也应该允许我拿回自己的数据,另作他用吧。期待有一天,每个人都能轻松建立一个真正属于自己的数据中心,想查就查,说删就删。

- EOF -