YouTube视频榜单SQL/Python/Excel数据分析

本文的结构如下:

Part 1. 理解数据

1.1 数据集

https://www.kaggle.com/datasnaek/youtube-new

该数据集包含有关YouTube每日热门视频的数月(且在不断增加)的数据。包括美国,GB,DE,CA和FR地区(分别为美国,英国,德国,加拿大和法国)的数据,每天最多列出200个趋势视频。

现在包括同一时期来自RU,MX,KR,JP和IN地区(分别为俄罗斯,墨西哥,韩国,日本和印度)的数据。每个地区的数据都保存在单独的文件中。数据包括视频标题,频道标题,发布时间,标签,观看次数,喜欢和不喜欢,描述以及评论数。数据还包括category_id字段,该字段在区域之间有所不同。要检索特定视频的类别,请在关联的JSON中找到它。数据集中的五个区域中的每个区域都包含一个这样的文件。

1.2 数据含义

Part 2. 提出问题

2.1 流媒体常见指标

参考知乎文章短视频运营指标
https://zhuanlan.zhihu.com/p/53091630

2.2 提出问题

Part 3. 清洗数据

3.1 编码转换

打开一个数据集的时候发现有多处乱码,是非英文字符造成的。

解决方案:

用记事本打开保存为UTF-8格式

3.2 Json处理

代码参考知乎寐语
出处:https://zhuanlan.zhihu.com/p/66702457

可以看到一个id对应一个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']:
        csvWriter.writerow([i['id'], i['snippet']['title']])

Python里面显示csv情况

3.3 导入MySQL

此处两个坑。第一,创建数据库的时候要utf8mb4

字符集设置

第二,所有数据需要longtext不然会报错(1406 – Data too long for column…)

设置字段类型

导入后:

所有数据

3.4 选择子集

通过Navicat的筛选功能选择子集,隐藏与研究问题无关项

通过显示按钮来设置

3.5 列名重命名

因为字段名纯英文,很清晰没有重命名的必要。

3.6 加入主键

alter table youtube
add COLUMN id int

自动递增和主键设置

3.7 删除重复值

select min(id),video_id,trending_date,count(video_id) as 重复数
from youtube
GROUP BY video_id,trending_date
having count(video_id)>1
order by 重复数 desc

这里根据video_id和trendingdate 分组,因为在不同时间重复上trending排行榜这样的信息是有价值的。所以我们需要找出videoid 在相同trending_date上的唯一id,一共有五十条。

相同trending日期,相同video_id的重复值情况

select * from youtube a
INNER JOIN
(
select min(id) as 唯一id,video_id,trending_date,count(video_id) as 重复数
from youtube
GROUP BY video_id,trending_date
having count(video_id)>1
order by 重复数 desc)b
on a.trending_date=b.trending_date and a.video_id=b.video_id and a.id<>b.唯一id

通过多表查询我们可以找出我们不要的数据,这里要先创建暂时表,因为不能在本表内查询出内容再删除,必须借助临时表。

INSERT into temp(id)
(select id from youtube a
INNER JOIN
(
select min(id) as 唯一id,video_id,trending_date,count(video_id) as 重复数
from youtube
GROUP BY video_id,trending_date
having count(video_id)>1
order by 重复数 desc)b
on a.trending_date=b.trending_date and a.video_id=b.video_id and a.id<>b.唯一id
)

最后删除即可

DELETE from youtube
where id IN
(select id
from temp)

delete 删除影响行

影响50行,正好对应起来了。

3.8 缺失值处理

select count(id),count(video_id),count(title),count(channel_title),count(category_id),count(publish_time),count(tags),count(views),count(likes),count(dislikes),count(comment_count),count(description)
from youtube

用count()查看缺失值情况

我们发现video_id和description有不同程度的缺失情况,因为是属于重要信息,所以我们对缺失值一整行进行删除。

delete from 
youtube
where video_id is null 
or description is null

删除影响行

3.9 一致化处理

3.9.1 标签个数(tags_num)

tags用| 分隔,所以有多少个(|+1)个| 就表示多少个标签。

alter table youtube add COLUMN tags_num int
update youtube set tags_num=(
LENGTH(tags)-LENGTH(replace(tags,'|',''))+1
)

3.9.2 描述长度

alter table youtube add COLUMN description_len int
update youtube set description_len=(
LENGTH(description)
)

3.9.3 上榜日期转换

update youtube set trending_date= concat('20',left(trending_date,'2'),'/',RIGHT(trending_date,'2'),'/',MID(trending_date,4,2))

3.9.4 发布时间拆分日期和时间

拆分日期

alter table youtube add COLUMN publish_date date
update youtube set publish_date= concat(left(publish_time,4),'-',MID(publish_time,6,2),'-',MID(publish_time,9,2))

拆分时间

UPDATE youtube set publish_time= mid(publish_time,12,8)

3.9.5 上榜情况

执行以下语句前请添加索引加快搜索:

添加索引

我们可以通过判断上榜次数时间间隔+1是否相等来知道上榜的视频是否存在连续上榜现象。这里的SQL比较复杂,但是思路就是找出每个视频的trending日期的最大最小值相减就可以得出时间gap,然后通过count计算出上榜次数最后用if进行对比。

select e.video_id,e.上榜次数,(f.间隔+1) as 间隔,if(间隔=上榜次数,'不连续','连续') as 是否连续上榜
from 
(
select video_id,count(*) as 上榜次数
from youtube
GROUP BY video_id
ORDER BY video_id) e
INNER JOIN
(
SELECT c.video_id,DATEDIFF(最大日期,最小日期) as 间隔 FROM
(
select a.video_id,max(a.trending_date) as 最大日期
from youtube a 
inner JOIN youtube b
on a.video_id=b.video_id and a.trending_date=b.trending_date
GROUP BY a.video_id
ORDER BY a.video_id
)c
INNER JOIN
(
select a.video_id,min(a.trending_date) 最小日期
from youtube a 
inner JOIN youtube b
on a.video_id=b.video_id and a.trending_date=b.trending_date
GROUP BY a.video_id
ORDER BY a.video_id
)d
on c.video_id=d.video_id
)f
on e.video_id=f.video_id

上榜概览

select 是否连续上榜,COUNT(是否连续上榜)
FROM(
select e.video_id,e.上榜次数,(f.间隔+1) as 间隔,if(间隔=上榜次数,'不连续','连续') as 是否连续上榜
from 
(
select video_id,count(*) as 上榜次数
from youtube
GROUP BY video_id
ORDER BY video_id) e
INNER JOIN
(
SELECT c.video_id,DATEDIFF(最大日期,最小日期) as 间隔 FROM
(
select a.video_id,max(a.trending_date) as 最大日期
from youtube a 
inner JOIN youtube b
on a.video_id=b.video_id and a.trending_date=b.trending_date
GROUP BY a.video_id
ORDER BY a.video_id
)c
INNER JOIN
(
select a.video_id,min(a.trending_date) 最小日期
from youtube a 
inner JOIN youtube b
on a.video_id=b.video_id and a.trending_date=b.trending_date
GROUP BY a.video_id
ORDER BY a.video_id
)d
on c.video_id=d.video_id
)f
on e.video_id=f.video_id
)g
GROUP BY 是否连续上榜

是否连续的占比

我们发现连续上榜的有5995个视频,占到了总视频的97%。

Part 4. 数据分析和可视化

4.1 观看量和喜欢程度的相关系数?

因为浏览和喜欢/不喜欢/评论能普遍反映一个视频的情况,所以对这些指标进行相关系数分析。

得出结论:可以看到喜欢和(浏览量以及评论)相关度非常高,反映了当这个视频受到人们的喜爱的时候,人们的参与度会越高。但是不喜欢也一定造成评论,这反应了人们对一个视频不满的时候,有表达的欲望。

4.2 什么类别视频的上榜次数高?

SELECT 类型,count(类型) as 数量
from
(
select a.video_id,b.category as 类型
from youtube as a
inner JOIN youtube_category as b
on a.category_id=b.id
)c
GROUP BY 类型
ORDER BY 数量

得出结论:可以看到Howto&Style\Music\Entertainment 上榜次数最高,后期可以重点关注。进一步从视频指标进行构建模型。

4.3 评论、喜欢、观看数在不同类型下的比率是怎么样的?

指标构建

select b.category,round(sum(likes)/sum(dislikes),2) as 喜欢和不喜欢比率,round((sum(comment_count)/sum(views)),6) as 评论率
from youtube as a
inner JOIN youtube_category as b
on a.category_id=b.id
group by b.category

提出假设:热门视频可能喜欢比率会比较高,评论率也同理。

搜集证据:

得出结论:

  • 环境类是上榜次数高的视频,但是喜欢率和评论率都非常低,所以考虑YouTube瞄准的用户群体不精准,没有做好视频创作类型引导。
  • music的话因为比较通俗易懂,讨论的门槛比较低所以大家的参与度还是喜欢度都会比较高,因为对于这种大众通俗视频大家的忍耐度比较高。
  • 对于教程类(how to)视频大家的喜欢度还是参与度还是对得起占有率的。

4.4 视频的标签数越多越好吗,和trending有关系吗?

视频标签和描述对视频的浏览量的影响

select b.category,round(avg(a.tags_num),0) as 标签数,round(avg(a.description_len),0) as 描述长度,round(avg(a.views),0) as 平均值
from youtube as a
inner JOIN youtube_category as b
on a.category_id=b.id
group by b.category
ORDER BY 平均值

提出假设:标签越多浏览量越大

搜集证据:

得出结论:假设不成立。

  • 虽然在低浏览量下标签呈现小波动,但是低浏览量下标签数量整体趋向高,在高浏览下标签数量有下降趋势。
  • 而且我们还发现了在shows类视频标签量非常的大,是因为shows种类很多,可能有脱口秀、综艺、真人秀等shows。而且非盈利的视频tags数量比较少,体现这类视频很纯粹,不会放很多花里胡哨的标签。

提出假设:描述长度越长浏览量越大

搜集证据:

得出结论:假设不成立,越长的描述并不能为视频赢得观众的好感,我们还发现在教程\教育类类视频当中描述很长,有一个峰值,体现了虽然教程类视频没有很受大家喜欢,但是因为本身视频的特性,描述长度很长。

4.5 上榜和什么因素有关系?

提出假设:不同类别下登上榜单和视频数量有关系

SELECT c.类型,c.视频数,d.上榜次数
from
(
select b.category as 类型,count(DISTINCT video_id) as 视频数
from youtube a
inner JOIN youtube_category as b
on a.category_id=b.id
group by b.category
)c
INNER JOIN
(
select b.category as 类型,count(trending_date) as 上榜次数
from youtube as a
inner JOIN youtube_category as b
on a.category_id=b.id
group by b.category
ORDER BY b.category
)d
on c.`类型`=d.`类型`

搜集证据:

得出结论:假设成立,可以看出视频数量越多登上榜单的机会越大。

4.6 视频的发布日期与首次上榜的间隔和发布次数有关系吗?

select e.video_id,e.总上榜次数,d.间隔
from
(
SELECT DISTINCT a.video_id,TIMESTAMPDIFF(day,a.publish_date,b.最早发布时间) as 间隔
from youtube a
INNER JOIN 
(
SELECT video_id,min(trending_date) as 最早发布时间
from youtube
GROUP BY video_id
ORDER BY video_id desc
)b
on a.video_id=b.video_id
)d
INNER JOIN 
(SELECT video_id,count(*) 总上榜次数
from youtube
GROUP BY video_id
)e
on e.video_id=d.video_id

得出结论:

  • 大多数多次上榜的视频往往是在一开始发布0-2天就上了热门榜单。
  • 还存在有一个4215天后上榜的,考虑是因为热点新闻对过往视频的影响。

4.7 是否存在视频发布的黄金事件?

4.7.1 周

select DAYOFWEEK(publish_date) as 周几,count(trending_date) as 上榜次数
from youtube
GROUP BY DAYOFWEEK(publish_date)

搜集证据:

得出结论:

从图表中发现周日发布的视频反而并没有优势,工作日反而上trending的视频更多更加的均匀,因为我们分析的是美国国家的数据,和我们周末有更多的时间上网的观点相违背。因为数据集给的是榜单视频数据,所以缺乏总体视频数据的支撑,推测可能产生一些片面结论。

推测1:大部分美国人周末可能去户外或者线下实地进行活动,而不是选择网上冲浪,看视频。

推测2:可能是YouTube的推荐方式不同,在周末更加集中的推荐一些视频到trending上。

推测3:可能视频制作者在工作日的时候是制作/发布视频的时间。

4.7.2 小时

select hour(publish_time) as 小时,count(trending_date) as 上榜次数
from youtube
GROUP BY hour(publish_time)

搜集证据:

得出结论:在trending当中12-17小时这个时间段发布的比率比较高,在17小时达到了巅峰,但是也开始了下降,到早上七点达到了一个低谷期。

推测:这可能和外国人的工作时间有关系,在下午五六点就已经下班,可能在快下班前想要post视频,但这里还是缺乏一定的数据支撑,例如用户画像数据。

Part 5. 总结&建议

建议:

Final:

本文分析思路有借鉴知乎-寐语,特此说明!

两篇文章使用同一kaggle数据集。大家可以出门左转看该文,互相用不同技术来尝试数据分析。

本文旨在学习交流,不做商业用途,也谢绝商业转载。

https://juejin.im/post/5e68984c518825495e105ea0

「点点赞赏,手留余香」

    还没有人赞赏,快来当第一个赞赏的人吧!
Caffe
0 条回复 A 作者 M 管理员
    所有的伟大,都源于一个勇敢的开始!
欢迎您,新朋友,感谢参与互动!欢迎您 {{author}},您在本站有{{commentsCount}}条评论