这是Paul Leclercq的客座博客，他是一位数据工程师、体育和音乐爱好者，最初发表在他的个人博客上。在数据库里试试这个笔记本每当我开车或编码时，我都会听音乐，因为这种情况经常发生，为了找到新的歌曲，我会听收音机，或者收听Spotify的discover weekly播放列表，这让我喜欢上周一（因为他们每周一都会发布）。法国一家名为Mediamétrie的老派学院分析广播电台的歌曲。自从几年前我看过他们的研究（我再也找不到了）以来，我一直痴迷于创造自己的研究。Mediametrie的形象你可以在网上找到→老学校本文将通过有趣的SQL查询展示4家法国主要广播电台的2016年，然后我们将把每首歌曲连接到Spotify API来创建广播电台的音乐简介。我们将使用Databricks社区版本来可视化我们的数据。所有SQL查询和所有结果都可在此笔记本上使用。这是本文的"后台"，如果我们可以说的话，魔术就发生在这里。普罗提普：不要错过文章结尾的奖金广播电台简介我们都有一个最喜欢的电台；我的电台是Nova电台，因为它的多样性、幽默感，作为一个嘻哈爱好者，这是唯一一个我们能听到可收听的嘻哈歌曲的国家电台。2016年9月，nova电台拥有1.4%的观众（PDF可从Mediametrie下载）。2016年在Nova上播放最多的5首歌曲为了了解一个电台是如何成为第一的，我们还将分析排名第一的音乐电台NRJ，它有10.8%的听众和2个其他人：维珍（5%），我们会看到，听起来像NRJ，和Skyrock（6%），别介意它是一个说唱电台…哈哈2016年NRJ上播放最多的5首歌曲主要的问题是，在我们比较了这些无线电之后，我们是否应该根据NRJ的分析，向Radio Nova提供一些如何成为第一的建议？你说呢，诺娃？向最好的人学习，对吗？！正在获取收音机的歌曲数据"这个标题是什么？"Nova页面为了提取歌曲列表、艺术家、歌曲标题和时间戳，我们将解析每个收音机"这首歌是什么？"HTML页面，除了Skyrock，它有一个方便的restfulweb服务。提取的每首歌曲都将转换为该歌曲类，以便使用（Spark）SQL轻松查询：歌曲(时间戳：Int, 人形日期：长, 年份：国际, 月份：Int, 日期：内景, 小时：内景，分钟：内景，艺术家：弦乐，allArtists:字符串，标题：字符串)2016年，收集了30万次广播：Nova:95K播放5000首不同的歌曲NRJ:50K播放800首不同的歌曲维珍：6万首不同歌曲的BroacastSkyrock:100K广播1000首不同的歌曲每首歌都以parquet格式存储，以便只提取一次数据（欢迎使用radios-servers:p），并加快SparkSQL查询。顺便说一句，如果你对这个文件感兴趣，我可以用CSV或parquet导出给你。请记住，如果您必须一次又一次地使用同一个SQL表（或Dataset/Dataframe），那么加快查询速度的最佳方法（Spark doc说通常是10倍以上），就是在内存中缓存表（感谢6Go RAM服务器的Databricks！）和数据帧缓存（）方法。现在让我们深入分析吧！一天有多少首歌？有些日子没有被电台的历史记录系统记录，所以实际数字应该要高一点。白天播放的歌曲有趣的是，两个广播电台在夏季都会播放更多的歌曲（如果我们不考虑到8月份蓝色的Nova电台一周的错误），这当然是由于暑假。他们一年四季都干得很好，所以我想可以休息几天吧！我们可以看到，Skyrock和Nova每天播放相同数量的歌曲，而NRJ和Virgin则少了一点，这肯定是因为更多的脱口秀节目或未经跟踪的DJ夜间节目。一天有多少首不同的歌？真正的区别在于播放的不同歌曲的数量；你自己看看每天不同曲目的数量：每天不同的歌曲更主流的电台，如NRJ，Virgin和Skyrock一天能播放100/120首不同的歌曲，而Nova则是280首左右。如果你想发现更多的歌曲，它显然是在Nova。每个月有多少首不同的歌？如果我们看看每月不同的歌曲，收音机之间的差距就更大了。2016年各月不同歌曲每个广播电台播放的前10个节目看一看这一年"热门"是怎么玩的很有趣。我们可以注意到夏季的热门歌曲：诺瓦的凯特拉纳达，NRJ的恩里克·伊格莱西亚斯，天空摇滚的肯特·琼斯和德雷克，维珍的伊玛尼和昆斯。而且，大多数广播歌曲大多在夏季播出。夏天收音机倾向于播放更多的歌曲。所以艺术家们在这里耍聪明，在二月到六月之间发布他们的歌曲，是为了有更多的机会成为第一，还是为了让更多的人因为听了太多而讨厌他们的音乐？新星尼泊尔卢比天岩处女每日音乐百分比如果我们以每天平均播放的歌曲和歌曲的平均持续时间，3.30分钟，我们就可以猜出每天播放的音乐的百分比。另一个百分比可能是脱口秀、广告或未经追踪的歌曲。每天音乐的百分比为了了解更多这些百分比，我们应该看看我们分析过的广播电台的正常日数。我们的广播电台通常星期一是什么？让我们看看星期一所有电台的平均歌曲数量。星期一的平均歌曲数我们可以在每个广播电台的早晚节目中区分两个间隙。太神了。更严重的是，这里没有发现；众所周知，大多数收音机都有早晚节目，其间音乐较少，谈话较多。广告时间如果我们重新计算中午音乐的平均百分比，当所有广播电台没有节目时，我们可以按小时估计广播广告的百分比。我们估计电台主持人在一小时内讲5分钟。我们必须注意到，电台在黄金时间有更多的听众时可能会做更多的广告。60分钟，我们有7分钟的广告时间，天岩，15分钟，维珍。具体如下表：每周一中午音乐和广告的平均分钟数收音机洗脑？我们有时用收音机时会有一种恼人的感觉，就是我们一遍又一遍地听同一首歌。因为我们是相信科学而不是直觉的男人和女人，我们将用基本的统计数据来验证这种奇怪的感觉。同一首歌在同一天播出多少次？同一首歌一天平均播放次数下面这些饼图告诉我们很多广播电台的习惯；也就是说，更多的主流电台，如维珍，NRJ或Skyrock，将更多地播放相同的歌曲多次。同一首歌要播多少遍？下一次我们在同一天听同一首歌是什么时候？同一天播放的同一首歌曲之间的最小时差同样，最主流的电台，NRJ，Skyrock和Virgin都倾向于播放同一首歌2/3小时。新星的价值大约是7/8小时。虽然我们有不同的分布，我们4台收音机的平均时间是7到8小时。加入了多少首新歌？何时加入？"新歌"指2016年尚未播出的歌曲。按月发行新歌如果我们看一下2016年4月以后的平均水平，我们会发现Nova领先，但别忘了Nova每月播放2500首不同的歌曲，所以从统计上讲，这是正常的。每月平均新歌数新歌每周平均分配给所有收音机。电台间常见歌曲在下表中，我们可以看到NRJ与维珍有25%的共同歌曲，与Skyrock有12%的共同歌曲。维珍在NRJ拥有18%的份额，而Skyrock在NRJ拥有9%的普通歌曲。收音机之间广播的相同歌曲数Nova与其他电台有一些类似的歌曲；其中大部分是传奇艺术家，如鲍勃·马利、愚蠢的朋克、芦荟·布拉克、卡文斯基、碧昂丝……如果你对完整的列表感兴趣，可以在"后台"也就是博客文章的Databricks笔记本中查找"收音机之间类似的歌曲"单元格。当然，我们的4个电台是不同的，但它们之间有共同的歌曲吗？令人惊讶的是答案是肯定的。王子接吻C2C - 快乐间质我把这些歌归为每个人都喜欢的歌；你可以在你的聚会上演奏它们，而不会受到任何嘘声的压力。如果我们对上一张表使用可视化显示，它将如下所示：蓝色条是相似的歌曲，橙色和绿色条是不同歌曲的总和。相似的歌曲做1的秘诀是什么？我们分析了4个广播电台的艺术家的名字，标题名称和日期和时间的歌曲广播。除了字母和数字之外，这3个值没有任何意义，如果我们想做更深入的分析，我们必须了解更多关于播放的歌曲：这首歌现在有多流行？这首歌的体裁是什么？这位艺术家有多少追随者？希望通过将每首歌曲连接到Spotify API，我们可以获得许多可以使用的数据：https://api.spotify.com/v1/search？q=[ARTIST_TITLE]&type=曲目和限制=12016年，我们从收音机中收集了8000首不同的歌曲，因此要从Spotify API获取艺术家、曲目和曲目的音频功能，我们必须制作：歌曲数量*（艺术家+曲目+音频功能）=24K请求从HTTP状态狗太多了。另外，Spotify在时间上有一个限制，所以我们必须慢慢来，每2秒20个请求，为什么不知道呢。但是，有一件事我没有计划，那就是当我们要求一首歌的歌手时，我们可以看到粉丝数量的变化，因为大多数艺术家都有多首歌曲被播放，艺术家的信息被询问了2到10次。没问题，对吧？不…这会在以后扰乱艺术家和跟踪数据之间的SQL连接，只是因为艺术家的不同信息是假的，因为关注者。总计我不得不说这让我变得疯狂，是吗