TowardsDataScience 博客中文翻译 2016~2018（二百六十二）

原文：TowardsDataScience Blog

协议：CC BY-NC-SA 4.0

Spotify 数据项目第 1 部分——从数据检索到第一视角

原文：https://towardsdatascience.com/spotify-data-project-part-1-from-data-retrieval-to-first-insights-f5f819f8e1c3?source=collection_archive---------13-----------------------

到目前为止，2018 年对我们这些数据书呆子来说是很棒的一年。有很多很好的选择来获得有趣的数据，例如最大的数据科学社区 Kaggle，拥有超过 10，000 个来自各种行业的已发布数据集。或者，谷歌(碰巧也拥有 Kaggle)新推出的数据集搜索工具，让寻找数据集来练习数据肌肉变得像安装 Pandas(或任何其他数据科学库)一样简单。

如果你愿意尝试一些好的数据，你可以选择 API。Twitter、Slack 和谷歌等科技公司(又来了！)提供 API，以便其他人可以在这些 API 的基础上构建应用程序。没有什么可以阻止你使用它们来提取数据和进行数据分析。

在这一系列文章中，我将描述我如何利用 Spotify Web API 自动检索数据(主要是本文的主题)，以及如何使用 Python、SQL 和 Bash 等数据科学工具从数据中获得洞察力(将在后续文章中讨论)。

**注意:**如果你只对编码感兴趣，请随意跳到本文末尾，在那里你会找到所有用于这个项目的笔记本、工具和参考资料。

资料检索

为了访问 Spotify 的平台，你需要在 developer.spotify.com创建一个账户。之后，您可以直接进入 Spotify Web API 控制台，通过一个易于使用的界面开始探索不同的 API 端点，而完全不需要任何 Jupyter 笔记本！

本质上，API 基于请求/响应工作。你问 API 关于 2010 年至 2012 年黄金岁月的歌曲，API 的回答是我们这一代的一些最佳歌曲，如 this gem 。然而，由于与 API 的通信是以机器可读的格式(如 JSON)完成的，所以您将希望使用编程语言来完成这一任务。也就是说，如果你想利用 Spotify 目录中的数百万行音乐数据。

因为我使用的是 Python，所以有一个非常好的库叫做 Spotipy ，这使得访问 Spotify API 的过程变得更加容易。

一旦你安装了 Spotipy，下面的代码足以让它启动并运行(使用你自己的 cid 和来自你的 Spotify 开发者帐户的密码):

import spotipy from spotipy.oauth2 import SpotifyClientCredentials cid ="xx" secret = "xx" 
client_credentials_manager = SpotifyClientCredentials(client_id=cid, client_secret=secret) 
sp = spotipy.Spotify(client_credentials_manager=client_credentials_manager)

现在您可以开始实际的数据检索了。通过观察 Spotify API 的不同端点，你可能会问自己:我应该获取什么数据？

这是你需要开始挖掘并找出你感兴趣的东西的地方。在这个例子中，我只使用了 50 多个可用端点中的 2 个。您可能会发现其他一些端点对您的分析也很有用。

我知道我想处理跟踪数据。这就是为什么我使用了搜索端点(而不是获得几个轨道端点，因为它需要提供轨道 id，而我没有)。

搜索端点很容易使用，因为只需要两个参数:q 和 type。第一个可以是任何关键词，如“roadhouse blues”或“2018”。类型可以是专辑、艺术家、播放列表或曲目。

这个端点也有一些限制:

• 限制:每个查询最多可以返回 50 个结果
• offset:这是要返回的第一个结果的索引，所以如果您想获得索引为 50–100 的结果，您需要将 offset 设置为 50 等。

此外，最大偏移量被限制为 10.000，这意味着您无法获得比单次搜索更多的结果。**趣闻:**我第一次做数据检索的时候(2018 年 4 月底)最大偏移量是 100.000。希望我不是他们决定砍掉它的原因:)

为了解决这些限制，我使用了一个嵌套的 for 循环，在这个循环中，我在外部循环中将偏移量增加了 50，直到达到最大偏移量。在将返回的结果追加到列表中时，内部 for 循环执行实际的查询。

for i in range(0,10000,50): 
  track_results = sp.search(q='year:2018', type='track',       limit=50,offset=i) 
    for i, t in enumerate(track_results['tracks']['items']):   artist_name.append(t['artists'][0]['name']) track_name.append(t['name']) track_id.append(t['id']) popularity.append(t['popularity'])

在我将数据加载到 pandas 数据框架中之后，我得到了以下结果:

artist_name	popularity	track_id	track_name 0	Drake 100	2G7V7zsVDxg1yRsu7Ew9RJ	In My Feelings 1	XXXTENTACION	97	3ee8Jmje8o58CHK66QrVC2	SAD! 2	
Tyga 96	5IaHrVsrferBYDm0bDyABy	Taste (feat. Offset) 3	
Cardi B 97	58q2HKrzhC3ozto2nDdN4z	I Like It 4	
XXXTENTACION	95	0JP9xo3adEtGSdUEISiszL	Moonlight

这已经足够了，正如我将在本文的第二部分展示的那样，届时我将更深入地挖掘 Spotify 提供的“流行”功能。由于我也想做一些更高级的分析(提示:机器学习)，我还包括了由 get 几个音频特征端点提供的数据。

此端点仅要求提供轨道 id。这不成问题，因为我在之前的查询中有 10.000 个音轨 id。

这里的限制是每个查询最多可以提交 100 个曲目 id。

同样，我使用了嵌套的 for 循环。这一次，外部循环以 100 为一批提取跟踪 id，内部 for 循环执行查询并将结果追加到 rows 列表中。

此外，当音轨 ID 没有返回任何音频特征(即没有返回任何音频特征)时，我必须执行检查，因为这会导致问题:

for i in range(0,len(df_tracks['track_id']),batchsize): 
 batch = df_tracks['track_id'][i:i+batchsize] 
 feature_results = sp.audio_features(batch) 
  for i, t in enumerate(feature_results): 
   if t == None: 
    None_counter = None_counter + 1 
   else: rows.append(t)

作为回报，我为我的歌曲获得了一系列的音频特征，比如舞蹈性、音量、声音等等。然而，这些特性将是本系列的下一篇文章的一部分。

在使用流行特性进行数据分析之前，我想向您展示我如何在 Linux 终端(命令行/ Bash)上设置 cron 作业，以便在我睡觉时自动进行数据检索！

这非常简单:您只需要您的 Python 文件(如果您使用的是 Jupyter 笔记本，您可以将其下载为. py 文件),然后创建一个 Bash 脚本(。sh 文件)，它将在 Python 中运行该文件:

#!/usr/bin/env bash python3 /home/user/SpotifyDataScript.py

最后，您打开 crontab 编辑器，在所需的时间和日期设置 cron 作业，如下所示:

30 00 * * 4 /home/user/Spotify.sh

瞧！你不用再担心了，数据现在会被自动检索。正如您从 Spotify 开发者仪表盘上截取的截图中所看到的，现在每周都会从 API 中自动提取数据。

如果你想了解更多关于 Bash 脚本和 cron 作业的信息，我推荐 data36 的这个系列文章以及这个快速参考指南。

**注意:**尽管这对我来说已经很好了，我还是建议在设置 API 调用的自动化时要小心。通过使用更长的时间间隔来触发数据，例如每周一次甚至每月一次，尽量减少对提供方的影响。

数据探索:流行特征

正如所承诺的，我现在将使用检索到的数据从中获得一些见解。在本文中，我将重点介绍流行功能，根据 Spotify 官方文档的说法它的意思是:

“流行曲目。该值介于 0(最不受欢迎)和 100(最受欢迎)之间。曲目的受欢迎程度是一个介于 0 和 100 之间的值，100 是最受欢迎的。受欢迎程度主要基于播放的总次数。诸如单曲和专辑中的重复曲目的流行度评级是不同的。注意:该值不会实时更新，因此可能会落后于实际受欢迎程度。”

尽管 Spotify 对这一功能并不太精确，但这对于数据分析来说应该足够了。理想情况下，应该有另一个功能，如每个轨道的流数量，然而，Spotify 目前没有通过他们的 API 分享这些信息。

关于人气特性，需要了解的一件重要事情也在 2013 年一位名叫 Sascha 的用户发布的SO 帖子中进行了解释:

“Spotify 的受欢迎程度的确是基于流媒体的数量，但它不是基于总播放量，而是基于一个很短的时间段。这就是为什么你会经常在艺人简介上看到一个更受欢迎的数字排在第一位，而播放次数却比第二位少。

我们已经使用 API 数作为我们流流行度的百分比，让人们了解他们的曲目当前的流行度。所以请记住，这个数字可以增加，但也很容易减少。”

所以基本上，它代表了某首歌在某个时刻的受欢迎程度。这也解释了为什么它会随着时间的推移而变化，我可以从我每周的数据检索中注意到这一点。

由于我在 2018 年 8 月开始定期收集数据，我决定将以下 3 个数据检索纳入我的分析:

• 2018 年 8 月 7 日
• 2018 年 8 月 30 日
• 2018 年 9 月 20 日

由于 Spotify 提到的流行度计算的所谓延迟，这应该很好地涵盖了 7 月、8 月和 9 月，这样我就可以将我的分析作为“2018 年夏季最受欢迎的歌曲”。

为此，我将 3 个 CSV 文件加载到 Pandas 数据帧中，并将它们合并成一个数据帧。通过使用外部连接作为合并方法，保留了各自的流行性:

# loop over dataframes and merge into one dataframe 
# outer join in order to keep the popularity column from each file
for df_, files in zip(df_from_each_file, all_files): 
# all_files are here to provide the column suffix (0920,0830 etc) 
df = df.merge(df_, how='outer', on=merge_columns, suffixes=('',str(files)[-8:-4])

这已经是该分析的整个数据准备阶段中最棘手的部分。在那之后，我计算了一个总体受欢迎分数以及一个平均受欢迎分数。

按总体受欢迎程度分数排序的新数据框架如下所示:

artist_name	track_name	popularity	popularity_mean 3	Drake In My Feelings	300.0 100.000000 4	
XXXTENTACION	SAD! 288.0 96.000000 12	
Cardi B I Like It	288.0 96.000000 6	
Tyga Taste (feat. Offset)	287.0	95.666667 10	
Post Malone	Better Now	287.0 95.666667

当谈到 2018 年夏季热播时，似乎没有什么能比得上德雷克的《在我的感觉中》。

按艺术家对数据帧进行分组揭示了一些更有趣的见解，例如前 100 名中每个艺术家的曲目数量:

track_name artist_name 
Drake 5 
XXXTENTACION	5 
Travis Scott	5 
Post Malone	5 
Juice WRLD	3

每一个好的数据分析也应该有一些可视化。在这个例子中，我可视化了前 10 首歌曲在测量期间的个人受欢迎程度得分。这是可视化的一部分(查看文章末尾的笔记本文件以获得完整视图):

今天就到这里吧！检索并分析了大量数据，以了解 2018 年夏天最受欢迎的歌曲和艺术家。

在下一篇文章中，我将深入挖掘音频特征数据，并尝试不同的聚类算法。如果你对此感兴趣，请务必在 Twitter ( @tgel0 )上关注我，因为我会首先在那里发布新闻。如果你想和我打招呼或什么的，这也是联系我的最好方式(我很乐意)。

正如承诺的那样，以下是链接:

• 数据检索笔记本放在 github 上或者作为 nbviewer 渲染
• 在 github 上的数据探索笔记本或作为 nbviewer 渲染
• 非常感谢 Spotify 的 Web API，也感谢 Spotify 的创造者让我的生活变得更简单
• 最后但同样重要的是，如果没有 Python 库 Pandas 、 Numpy 、 Matplotlib 以及构建和维护它们的出色人员，这是不可能的
• 如果你喜欢听音乐，而不仅仅是阅读，我已经根据这一分析创建了一个 Spotify 播放列表

原载于 2018 年 9 月 27 日tgel 0 . github . io。

Spotify 重新包装

原文：https://towardsdatascience.com/spotify-rewrapped-e2a7cc94fb4e?source=collection_archive---------6-----------------------

Spotify 每年 12 月都会以酷炫的年终特价带给我们惊喜。然而，今年的一些报道让人感到可疑。这个不起眼的中等账户决定进行调查。

Photoshop skills: level 9000

每年我都期待 Spotify 的总结。2016 年是歌曲、艺术家和流派的前五名。花在听音乐上的时间，不同歌曲的数量，以及关于一周中你最喜欢哪一天听音乐的非常酷的见解。此外，你的热门歌曲播放列表。2017 年还带来了分钟数、热门歌曲播放列表和一个很酷的另一个播放列表，名为“The ones that away ”,可能是过去一年很酷的推荐。
今年带来了 Spotify Wrapped ，它告诉你许多统计数据(我们将分析)和两个播放列表:Top 100 和 Tastebreakers。我需要承认，我爱上了前卫的❤.它们应该让你在新的一年里听到 Spotify 惊人的推荐算法为你量身定制的新内容。

尽管这一次并不是一切都那么美好。 Spotify Wrapped 在 12 月和前几天被泄露，并不是每个用户都对提供的见解感到高兴。只要在 Reddit 的 r/spotify 中快速浏览一下，就足以意识到前 5 名艺术家或前 5 名歌曲中的大多数都很奇怪。尤其是我的，看起来很奇怪；就好像我最后的几个月根本不算在内。

Charly and his famous bi-color moustache

像每一个音乐和数据极客一样，我用 Last.fm 记录(并不断咨询和控制)我的听力故事。所以，我知道我的数据不准确。10 月份和 11 月份的大部分时间，我都在不停地听 Charly García 的音乐，尤其是他的一个乐队项目:Serú Girán

有点跑题:这两个名字都是阿根廷摇滚的精华部分，是阿根廷摇滚的精华。自 1967 年以来，Charly 一直在从事不同的优秀项目，他仍然非常活跃。在 1978 年至 1982 年期间，以及在 1992 年持续一年的短暂复出期间，Serú Girán 是他的主要乐队。信不信由你，他们被认为是“阿根廷披头士”。

Judge by yourself 💥💥💥💥

我也一直想用 Python 的熊猫库研究一点时间序列操作和可视化，所以这看起来是一个很好的借口。

技术

和我的大多数数据项目一样，我的主要盟友是Python 3熊猫 和matplotlib。我还与 Seaborn 、 Spotipy 合作，为 Spotify Web API 做 Python 包装，并与 Pylast 合作，为 Last.fm API 做 Python 包装。就环境而言，我只使用了 Jupyter 笔记本电脑和版本化我的作品。可以在我的公众 Github 账号查看数据库创建、数据分析、数据可视化的完整代码。

** [## alexing/spotify_rewrapped

Spotify 重新包装。通过在 GitHub 上创建一个帐户，为 alexing/spotify_rewrapped 开发做出贡献。

github.com](https://github.com/alexing/spotify_rewrapped)

这些数据是从 Last.fm 的 API 中删除的，还用 Spotify 的 API 丰富了一点。此外，根据 GDPR 法律，我联系了 Spotify 客户支持，试图访问我的全部收听历史数据并获得更多见解。到写这篇文章的时候，我还没有收到回信。

数据分析

数据集由我从 1 月 1 日到 12 月中旬的收听历史组成。Spotify Wrapped 于 12 月 1 日发布，所以这一年还没有结束并不重要。

df.head()

Most of this features I won’t be using, like the listener_count, mbid, playcount and url.

让我们通过逐一浏览 Spotify 的见解(至少是有趣的……)来开始分析，并决定它们是真是假。

I got the email in Spanish, sorry. You probably get the main idea, though.

1.今年你在 Spotify 上听了 8486 首不同的歌曲。

.

嗯，这个很容易检查。只需要询问数据帧中的唯一值。

.

unique_songs = df[df.date < '2018-12-01'].drop_duplicates(subset=['artist', 'title', 'album']).shape[0]
unique_songs

6538

这意味着 Spotify 的数字和我的数字之间有 1948 首歌曲的差异。

假的！

考虑到 Wrapped 发行日期之后的歌曲没有计算在内，所以在数量上有相当大的差异。

2.你开始了 2018 年，听了《浪费的生命》Pearl Jam 的 Brendan O’ Brien Mix，你发现的第一个艺术家是 Faces。

哦，这原来是辣的，而且超级容易检查。让我们开始吃吧。

df.loc[df['timestamp'].idxmin()]

事实上，我听的第一首歌是浪费的生命(开始 2019 年的精彩歌曲，对吗？！)。Spotify 的第一个目标。1–1 是这场比赛的部分比分。

现在，这是灰色部分，它开始变得不那么科学严谨。我们如何衡量被发现的艺术家？我用了手头的东西，决定用Spotify API 的功能来获取用户的顶级艺术家。这可以让你每次请求获得多达 50 位艺术家，其中一个有趣的参数让你在long_term(根据几年的数据计算，包括所有可用的新数据)medium_term(大约过去 6 个月)和short_term(大约过去 4 周)之间进行选择。有一个偏移量参数来平衡限制，以便在列表中进一步前进，但是如果偏移量超过 50，它会返回一个异常。基本上每个学期只能接触到前 100 个。在每个术语收到的 100 位艺术家中，我创建了一个集合，这样就不会出现重复，然后如果一个新的艺术家不在这个集合中，我会认为这是一个“发现”。

ranges = ['short_term', 'medium_term', 'long_term']
top_artists = set()
for a_range in ranges:
    results = sp.current_user_top_artists(time_range=a_range, limit=50)
    top_artists =top_artists.union({item['name'] for item in results['items']})
    results = sp.current_user_top_artists(time_range=a_range, limit=50, offset=49)
    top_artists =top_artists.union({item['name'] for item in results['items']})

基本上，第一个不在片场的艺术家将是我的第一个发现

reverse_df = df.iloc[::-1]
for i, row in reverse_df.iterrows():
    if row['artist'] not in top_artists:
        print("Song n# %d of the year" % (len(df) - i))
        print(row['artist'])
        print("Played on %s" % str(row['date']))
        break

年度歌曲第 74 首
惠特尼
播放时间 2018–01–01 16:04:00

df[df[‘artist’] == ‘Faces’]

太神奇了。不仅我第一个发现的艺术家不是面孔，而且今年我显然从未听过他们。所以这句话是:

对/错

Apparently Spotify calls me an overachiever 💪🏻

3.听了 58776 分钟。

.

.

这也是一件容易检查的事情。

.

df[df.date < '2018-12-01'].duration.astype('int').sum() / 1000 / 60

26860 . 868686868626

如果我们从 58776 分钟开始，有 281 分 13 秒的差距。如果我们计算今年(4:05)听的歌曲的平均持续时间，有 69 首歌曲的误差。

又一个热门，Spotify！这种说法是:

假

4。你和你最喜欢的艺术家荷西·德克勒一起度过了 41 个小时，这是他们的荣幸。

毫无疑问，今年我最喜欢的艺术家是荷西·德克勒。

Being retwitted by your favorite artist is better than Radiohead’s In Rainbows. Word.

如果你之前浏览过这个博客，你可能知道我对他的音乐和歌词做了深入的分析，他的专辑是我那漫长时光的配乐。关于快乐是他的那部分我真的怀疑，但我知道他喜欢它。

现在让我们检查小时数。

.

top_listened = df[df.date < '2018-12-01'].groupby(by=['artist']).agg('sum')['duration'].sort_values(ascending=False)[:1]
top_listened = top_listened / 1000 / 60 / 60 # ms / s / m -> h
top_listened

那是 42 小时 40 分钟。这意味着，如果我今年从 Drexler 那里听到的歌曲的平均值是 3:41， Spotify 的数字相差近 22 首歌曲。

对不起，斯波提…但这是另一个

错误的

Any excuse that I can get to embed a Drexler song, I’ll use.

.

5.排行榜

有趣的部分来了。看着顶级艺术家的部分，我意识到一切似乎并不准确。如上所述，查理·加西亚和塞鲁吉兰都不见了。此外，在热门歌曲中，有 3 首属于马丁·奥利弗的第二张专辑，这张专辑在 4 月份发行时我非常喜欢。CirrusfromBonobo，我在一月和二月每天都在重复收听。事实上，我知道今年我最着迷的曲目是安德森的《色彩》。帕克和肯德里克·拉马尔——我的天啊多么有感染力的节拍。当然色彩也无处可寻。

我们不会进入流派，因为我真的不太相信它们。一个和另一个之间的界限变得如此模糊和武断，我有点看不到这一点。我只想说:“墨西哥人？那是什么意思？”

顶级艺人

Spotify 榜单如下:荷西·德克勒、电台司令、北极猴子、吸血鬼周末、古斯塔沃·塞拉蒂。

df[df.date < '2018-12-01'].groupby(by=['artist']).agg('count')['track'].sort_values(ascending=False)[:10]

I knew it.

真的很有趣。北极猴和电台司令倒相差 20 首(可以接受，也许？).塞鲁吉兰和查理·加西亚怎么样了？！

首先， scrobble 是 Last.fm 发明的一个术语，用来描述你听的歌曲的日志。一个 scrobble 每复制一首歌。

从图表中我们可以清楚地看到，在这一年的下半年有一个很大的增长。穿红色衣服的查理·加西亚在九月中旬大幅增加，而塞鲁吉兰则从十一月开始飙升。这与 Spotify 标记为顶级的其他艺术家相比如何？Spotify 的顶级艺术家是否会在某个时间范围内与现实相符？

**首先，让我告诉你。**那些艺人那个顺序在整个 2018 年都没有发生过。但是如果我们不顾顺序，只关心他们五个是顶级艺术家，会发生什么？

The possible ranges are between 2018-06-18 and 2018-06-19
The possible ranges are between 2018-08-07 and 2018-08-08
The possible ranges are between 2018-08-16 and 2018-11-16

这就是可能性域。如果 Spotify 顶级艺术家是在 6 月 16 日、8 月 7 日或 8 月 16 日和 11 月 16 日之间的 3 个月内计算的，那么不管顺序如何，他们都是可以的。这可能没问题，我们可以假设 Spotify 是在 11 月中旬计算出这个列表的。
让我们继续追踪报道。

df[df.date < '2018-12-01'].groupby(by=['title', 'artist']).agg('count')['track'].sort_values(ascending=False)[:20]

我就知道！ Tints 是我的 2018 顶级曲目。另一方面，Spotify 表示，我最喜欢的 5 首歌曲依次是: Estocolmo (实际上是 17 首) Los días eternos (18 首) Cirrus (不在前 20 首)Black trecle(5 首)和 Shangai Safari (8 首)。这里发生了 WTF？

The possible ranges are between 2018-05-14 and 2018-05-21

Estocolmo and Los días eternos have exactly the same values everyday. Every time I listened to Martin Oliver’s album I did sequentially, so that probably explains it.

所有灰色线条都是我实际排名前 20 的歌曲，而不是 Spotify 的。Spotify 热门曲目出现的唯一时刻是在 5 月的一周，这是无视顺序的。回家 Spotify，你醉了！

大错误

结论

我并没有从中吸取太多。有些东西真的很容易看到，比如他们是如何计算出顶级艺术家的，然后塞鲁吉兰和查理加西亚可能会在我的前五名中占据重要地位。其他的东西是可以理解的，一点点的误差应该被原谅，比如我花在听荷西·德克勒和全球范围内我所有的音乐上的时间。但是有些东西基本上无法解释。
比如说脸怎么了？其实并不是 2018 年我没有发现他们……也是我这辈子都没有听过他们的事实。

artist = pylast.Artist(“Faces”, network, username=’alexing10')
print(“Faces has %d scrobbles.” % artist.get_userplaycount())

面孔没有 0 个阴囊。

最大的谜团是顶部的轨道。那是怎么回事？怎么会差这么远？
我猜他们认为 Spotify Wrapped 是他们需要在 12 月前快速发布的产品，为了减少资源或计算时间，他们没有使用所有用户的收听历史，而是随机抽取一些样本并从中进行计算。总而言之，我不得不说这是非常草率的。

我仍然希望 Spotify 会把我在 GDPR 的数据反馈给我，或许那时我们会对这个谜有更多的了解。

有些见解我决定不去核实，一方面是因为很难做到，另一方面是因为我真的不相信它们。例如，Spotify 告诉我我最喜欢的子类别是“现代摇滚”。那是什么？比如，有没有一年可以作为一个枢纽？1992 年以前发行的摇滚专辑都不是现代摇滚？格里塔·范·弗利特是现代摇滚吗尽管他们所有的歌听起来都无耻地像 1973 年以前的齐柏林飞艇？

Why that song out of that album? It was all released in 1951.

Spotify 还告诉我我听过的最老的歌是的 T4 的录音，当然还有塞隆尼斯·蒙克的**。我很确定他们没有得到我的剧本或者坦哥·雷恩哈特 30 年代和 40 年代的录音…或者任何古典作曲家的任何作品。肖邦的钢琴前奏曲出版于 1839 年，马尔塔·阿格里齐在 1977 年录制了它们的最终版本，Spotify 将它们收录在目录中的“翻唱”日期:2002 年。就这样…**

明年的想法:与其花时间检查你听得最多的黄道星座(说真的，这他妈的是什么？);他们可以检查每个人的最佳专辑。那可能是个更好的主意。

再次，你可以在我的公众 github 账号查看数据库创建、数据分析、数据可视化的完整代码。

** [## alexing/spotify_rewrapped

Spotify 重新包装。通过在 GitHub 上创建一个帐户，为 alexing/spotify_rewrapped 开发做出贡献。

github.com](https://github.com/alexing/spotify_rewrapped)

附赠曲目

这是我 2018 年的顶级专辑:

You can check the full list here

而且，虽然远不准确，但这是我的 Spotify 2018 年前 100 名播放列表。我非常喜欢它。

非常感谢，新年快乐！****

Spotify 的“这是”播放列表:50 位主流艺术家的终极歌曲分析

原文：https://towardsdatascience.com/spotifys-this-is-playlists-the-ultimate-song-analysis-for-50-mainstream-artists-c569e41f8118?source=collection_archive---------3-----------------------

Source: http://www.shekclifestyle.com/home/2017/5/15/summer-playlist

每个艺术家都有自己独特的音乐风格。从献身于木吉他的艾德·希兰，到精通说唱艺术的德雷克。从能在流行歌曲中唱一些疯狂高音的阿黛尔，到在 DJ 台上创造 EDM 魔术的 Kygo。音乐是关于创造力、独创性、灵感和感情的，它是跨越差异连接人们的完美门户。

Spotify 是目前最大的音乐流媒体服务。拥有超过 3500 万首歌曲和 1.7 亿月活跃用户，是音乐人接触观众的理想平台。在该应用程序上，可以通过各种参数浏览或搜索音乐，如艺术家、专辑、流派、播放列表或唱片公司。用户可以创建、编辑和共享播放列表，在社交媒体上共享曲目，并与其他用户一起制作播放列表。

此外，Spotify 推出了各种为用户量身定制的有趣播放列表，其中我最欣赏以下三个:

• 每周发现:每周生成的播放列表(每周一更新)，为用户带来两个小时的定制音乐推荐，将用户的个人品味与类似听众喜欢的歌曲混合在一起。
• 发布雷达:一个个性化的播放列表，允许用户了解他们最常听的艺术家发布的最新音乐。
• 每日混音:一系列播放列表，具有“近乎无止境的回放”功能，将用户喜爱的曲目与新的推荐歌曲混合在一起。

我最近发现了‘这是”播放列表系列。Spotify 最好的原创功能之一，“这是”兑现了流媒体革命的一个主要承诺——将伟大艺术家的作品典藏起来，供后代发现和欣赏。

每张专辑都献给一位不同的传奇艺术家，记录了经典专辑的巅峰时刻。“这是:坎耶·韦斯特”。“这是:魔力红”。“这是:埃尔顿·约翰”。Spotify 提供了一条捷径，为我们提供了最伟大艺术家的最佳歌曲精选列表。

方法

这个项目的目的是分析 Spotify 上不同艺术家制作的音乐。重点将放在从广泛的流派中理清 50 位不同艺术家的音乐品味。在整个过程中，我还识别出不同的艺术家群体，他们有着相似的音乐风格。

在研究中，我将访问 Spotify Web API ，它提供来自 Spotify 音乐目录的数据。这可以通过对 API 端点的标准 HTTPS 请求来访问。

Spotify API 提供了每首歌曲的曲目信息，包括音频统计数据，如舞曲、乐器或节奏。每个特征测量一首歌曲的一个方面。关于每个功能如何计算的详细信息可以在 Spotify API 网站上找到。本文中的代码片段可能有点难以理解，尤其是对于数据初学者来说，所以请耐心等待。

下面是我的方法的简要总结:

• 从 Spotify API 获取数据。
• 处理数据以提取每个艺术家的音频特征。
• 使用 D3.js 可视化数据。
• 应用 k-means 聚类将艺术家分成不同的组。
• 分析所有艺术家的每个特征。

现在让我们从 Spotify 上 50 位不同艺术家的“这是”播放列表中检索音频特征信息。

Source: https://blog.prototypr.io/have-you-heard-about-the-spotify-web-api-8e8d1dac9eaf

获取数据

第一步是在 API 网站中注册我的应用程序，并为将来的请求获取密钥(客户端 ID 和客户端秘密)。

Spotify Web API 使用不同的 URIs(统一资源标识符)来访问播放列表、艺术家或曲目信息。因此，获取数据的过程必须分为两个关键步骤:

• 获取多个音乐家的“这是”播放列表系列。
• 获取每位艺术家的播放列表曲目的音频功能。

Web API 凭据

首先，我为Client ID和Client Secret凭证创建了两个变量。

spotifyKey <- "YOUR CLIEND ID"
spotifySecret <- "YOUR CLIENT SECRET"

之后，我请求一个访问令牌，以便授权我的应用程序检索和管理 Spotify 数据。

library(Rspotify)
library(httr)
library(jsonlite)

spotifyEndpoint <- oauth_endpoint(NULL, "https://accounts.spotify.com/authorize",
"https://accounts.spotify.com/api/token")spotifyToken <- spotifyOAuth("Spotify Analysis", spotifyKey, spotifySecret)

这是播放列表系列

第一步是拉艺术家们的“这是”系列是为每一个人拿到 URIs。以下是我选择的 50 位音乐家，以他们的受欢迎程度、现代感和多样性作为主要标准:

• Pop :泰勒斯威夫特、爱莉安娜·格兰德、肖恩·蒙德兹、魔力红、阿黛尔、贾斯汀比伯、艾德·希兰、贾斯汀汀布莱克、查理·普斯、约翰·梅耶、洛德、第五和声、拉娜·德尔·雷、詹姆斯亚瑟、扎拉拉尔森、Pentatonix。
• 嘻哈/说唱:肯德里克·拉马尔、波斯特·马龙、德雷克、坎耶·韦斯特、阿姆、未来、50 美分、李尔·韦恩、维兹·卡利法、史努比·道格、麦克摩尔、Jay-Z
• 布鲁诺·马斯，碧昂斯，安立奎·伊格莱希亚斯，斯蒂维·旺德，约翰·传奇，艾丽西亚·凯斯，亚瑟，蕾哈娜。
• EDM / House : Kygo，The Chainsmokers，Avicii，Marshmello，加尔文·哈里斯，马丁·盖瑞斯。
• 摇滚:酷玩乐队，埃尔顿·约翰，一个共和国，剧本，杰森·玛耶兹。
• 爵士乐:弗兰克·辛纳屈、迈克尔·布伯、诺拉·琼斯。

Source: http://www.thedrum.com/news/2017/11/29/spotify-wraps-up-2017-making-humorous-goals-2018-using-its-data-and-artists

我基本上是转到每个音乐家的个人播放列表，复制 URIs，将每个 URI 存储在一个. csv 文件中，并导入。csv 文件转换成 r。

library(readr)playlistURI <- read.csv("this-is-playlist-URI.csv", header = T, sep = ";")

对于每个播放列表 URI，我应用了“RSpotify”包中的getPlaylistSongs,并将播放列表信息存储在一个空的 data.frame 中

# Empty dataframe
PlaylistSongs <- data.frame(PlaylistID = character(),
                            Musician = character(),
                            tracks = character(),
                            id = character(),
                            popularity = integer(),
                            artist = character(),
                            artistId = character(),
                            album = character(),
                            albumId = character(),
                            stringsAsFactors=FALSE)# Getting each playlist
for (i in 1:nrow(playlistURI)) {
  i <- cbind(PlaylistID = as.factor(playlistURI[i,2]),
             Musician = as.factor(playlistURI[i,1]),
             getPlaylistSongs("spotify",
                              playlistid = as.factor(playlistURI[i,2]),
                              token=spotifyToken))
  PlaylistSongs <- rbind(PlaylistSongs, i)
}

音频功能

首先，我编写了一个公式(getFeatures)，它提取任何特定 ID 的音频特征并存储为一个向量。

getFeatures <- function (vector_id, token) 
{
  link <- httr::GET(paste0("https://api.spotify.com/v1/audio-features/?ids=", 
  vector_id), httr::config(token = token))
  list <- httr::content(link)
  return(list)
}

接下来，我在另一个公式(get_features)中包含了getFeatures。后一个公式提取音轨 ID 向量的音频特征，并在 data.frame 中返回它们

get_features <- function (x) 
{
  getFeatures2 <- getFeatures(vector_id = x, token = spotifyToken)
  features_output <- do.call(rbind, lapply(getFeatures2$audio_features, data.frame, stringsAsFactors=FALSE))
}

使用上面创建的公式，我能够提取每个音轨的音频特征。为此，我需要一个包含每个曲目 ID 的向量。Spotify API 的速率限制是 100 首曲目，所以我决定为每位音乐家创建一个带有曲目 id 的向量。

接下来，我将get_features公式应用于每个向量，获得每个音乐家的音频特征。

之后我把每个音乐人的音频特征 data.frame 合并成一个新的，all_features。它包含每个音乐家的“这是”播放列表中所有曲目的音频功能。

library(gdata)all_features <- combine(TaylorSwift,ArianaGrande,KendrickLamar,ShawnMendes,Maroon5,
PostMalone,Kygo,TheChainsmokers,Adele,Drake,JustinBieber,Coldplay,
KanyeWest,BrunoMars,EdSheeran,Eminem,Beyonce,Avicii,Marshmello,
CalvinHarris,JustinTimberlake,FrankSinatra,CharliePuth,MichaelBuble,
MartinGarrix,EnriqueIglesias,JohnMayer,Future,EltonJohn,FiftyCent,
Lorde,LilWayne,WizKhalifa,FifthHarmony,LanaDelRay,NorahJones,
JamesArthur,OneRepublic,TheScript,StevieWonder,JasonMraz,JohnLegend,
Pentatonix,AliciaKeys,Usher,SnoopDogg,Macklemore,ZaraLarsson,JayZ,
Rihanna)

最后，我使用aggregate函数计算了每个音乐家的音频特征的平均值。所得的数据帧包含每个音乐家的音频特征，表示为他们各自播放列表中音轨的平均值。

mean_features <- aggregate(all_features[, c(1:11,17)], list(all_features$source), mean)names(mean_features) <- c("Musician", "danceability", "energy", "key", "loudness", "mode", "speechiness", "acousticness", "instrumentalness", "liveness", "valence", "tempo", "duration_ms")

下图是mean_features data.frame 的一个子集，供你参考。

音频功能描述

从 Spotify Web API 指南中可以找到每个功能的描述:

• 可跳舞性:描述一个曲目是否适合跳舞。这是基于音乐元素的组合，包括速度、节奏稳定性、节拍强度和整体规律性。值 0.0 最不适合跳舞，1.0 最适合跳舞。
• 能量:从 0.0 到 1.0 的度量，代表强度和活动性的感知度量。通常，高能轨道感觉起来很快，很响，很嘈杂。例如，死亡金属具有高能量，而巴赫前奏曲在音阶上得分较低。对该属性有贡献的感知特征包括动态范围、感知响度、音色、开始速率和一般熵。
• 调:音轨所在的调。整数使用标准音高分类符号映射到音高。例如，0 = C，1 = C♯/D♭，2 = D，等等。
• 响度:音轨的整体响度，单位为分贝(dB)。响度值是整个轨道的平均值，可用于比较轨道的相对响度。响度是声音的质量，是与体力(振幅)相关的主要心理因素。值的典型范围在-60 和 0 db 之间。
• 调式:表示一个音轨的调式(大调或小调)，其旋律内容来源的音阶类型。大调用 1 表示，小调用 0 表示。
• 语速:语速检测音轨中是否存在口语单词。越是类似语音的录音(例如脱口秀、有声读物、诗歌)，属性值就越接近 1.0。高于 0.66 的值描述可能完全由口语单词组成的轨道。介于 0.33 和 0.66 之间的值描述可能包含音乐和语音的轨道，可以是分段的，也可以是分层的，包括说唱音乐。低于 0.33 的值很可能代表器乐和其他非语音类曲目。
• Acousticness :从 0.0 到 1.0 的音轨是否声学的置信度度量。1.0 表示音轨是声学的高置信度。
• 乐器性:预测音轨是否不包含人声。“Ooh”和“aah”在这种情况下被视为乐器。Rap 或口语词轨道明显是“有声的”。乐器度值越接近 1.0，轨道不包含人声内容的可能性就越大。高于 0.5 的值旨在表示乐器轨道，但随着该值接近 1.0，置信度会更高。
• 活跃度:检测录像中是否有观众。较高的活跃度值表示音轨被现场执行的概率增加。高于 0.8 的值表示该轨迹很有可能是实时的。
• 效价:从 0.0 到 1.0 的一个量度，描述一首曲目所传达的音乐积极性。高价的音轨听起来更积极(例如快乐、愉快、欣快)，而低价的音轨听起来更消极(例如悲伤、沮丧、生气)。
• 速度:轨道的整体估计速度，单位为每分钟节拍数(BPM)。在音乐术语中，速度是给定作品的速度或节奏，直接来源于平均节拍持续时间。
• Duration_ms :以毫秒为单位的音轨持续时间。

数据可视化

雷达图

雷达图有助于以更直观的方式比较这些音乐家的音乐氛围。第一个可视化是来自 chart.js JavaScript 库的 radar 图表的 R 实现，并评估了 10 位选定音乐家的音频特性。

为了绘图，我将调、响度、速度和持续时间毫秒值标准化为从 0 到 1。这有助于使图表更加清晰易读。

mean_features_norm <- cbind(mean_features[1], apply(mean_features[-1],2, function(x){(x-min(x))/diff(range(x))}))

好的，让我们以十个音乐家为一批来绘制这些交互式雷达图。当您将鼠标悬停在每条径向线上时，每个图表都会显示数据集标签，显示所选特性的值。下面的代码详细介绍了第一批十位音乐人的雷达图制作过程。其他四个批次的代码已被省略，但雷达图显示。

第一批:泰勒·斯威夫特、爱莉安娜·格兰德、肯德里克·拉马尔、肖恩·蒙德兹、魔力红、波斯特·马龙、基戈、链烟者、阿黛尔、德雷克

第二批:贾斯汀比伯、酷玩乐队、坎耶·韦斯特、布鲁诺·马斯、艾德·希兰、阿姆、碧昂斯、阿维西、马什梅洛、加尔文·哈里斯

第三批:贾斯汀·汀布莱克、弗兰克·辛纳屈、查理·普斯、迈克尔·布伯、马丁·盖瑞斯、安立奎·伊格莱希亚斯、约翰·梅耶、未来、埃尔顿·约翰、50 美分

第四批:洛德，李尔·韦恩，维兹·卡利法，第五和声，拉娜·德尔·雷，诺拉·琼斯，詹姆士·亚瑟，一个共和国，剧本，斯蒂维·旺德

第五批:杰森·玛耶兹、约翰·传奇、宾得尼克斯、艾丽西亚·凯斯、亚瑟、史努比·道格、麦克摩尔、莎拉·拉尔森、Jay-Z、蕾哈娜

聚类分析

另一种找出这些音乐家音乐曲目差异的方法是将他们分组。聚类算法的一般思想是根据数据的相似性将给定的数据集分成多个组。

在这种情况下，音乐家将根据他们的音乐偏好被分组到不同的集群中。与在查看数据之前定义群体不同，聚类让我能够找到并分析已经有机形成的音乐家群体。

在对数据进行聚类之前，重新调整数据集的数值变量非常重要。由于我有混合的数字数据，其中每个音频特征都与另一个不同，并且具有不同的测量值，因此运行 scale 函数(也称为 z 标准化)是一个给予它们同等权重的好做法。之后，我将音乐家作为行名，以便能够在情节中作为标签显示它们。

scaled.features <- scale(mean_features[-1])
rownames(scaled.features) <- mean_features$Musician

我应用了 K 均值聚类方法，这是无监督统计学习方法中最流行的技术之一。它用于未标记的数据。该算法在数据中寻找组，组的数量由变量 K 表示。该算法反复工作，根据所提供的变量将每个数据点分配给 K 个组中的一个。数据点基于相似性进行聚类。

在这种情况下，我选择了K = 6——可以根据我在选择艺术家时使用的六种不同流派(流行音乐、嘻哈音乐、节奏布鲁斯、EDM、摇滚和爵士乐)来形成聚类。

在我对每个音乐家应用 K-Means 算法后，我可以绘制出数据的二维视图。在第一个图中，x 轴和 y 轴分别对应于第一和第二主成分。特征向量(用红色箭头表示)表示每个变量对主成分的方向影响。

让我们来看看对我的数据集应用 K-Means 算法所得到的聚类。

在上图中可以看到，x 轴是 PC1 (30.24%) ，y 轴是 PC2 (16.54%) 。这是前两个主要部分。PCA 图显示，PC1 根据响度/能量对声学/醇厚度来区分艺术家，而 PC2 似乎根据化合价对音调、速度和乐器性来区分艺术家。

因为我的数据是多元的，所以检查所有的二元散点图是很繁琐的。相反，单个“汇总”散点图更方便。从数据中得出的前两个主要成分的散点图已显示在图表中。同样，百分比是由总体可变性的每个分量解释的方差:第一分量捕获了关于多元数据的 30.24%的信息，第二分量捕获了关于多元数据的 16.54%的信息。

如果你有兴趣了解这个算法背后的数学，我建议你温习一下主成分分析。

让我们看看哪些艺术家属于哪些集群:

k_means$cluster

我还绘制了另一张雷达图，包含每个星团的特征。比较每个聚类创建的歌曲的属性是有用的。

集群 1 包含四个艺术家:酷玩乐队、阿维西、马什梅洛和马丁·盖瑞斯。他们的音乐大多是现场演奏和器乐演奏，通常声音洪亮，充满活力，节奏很快。这并不令人惊讶，因为四位艺术家中有三位表演 EDM / House 音乐，而 Coldplay 以他们的现场音乐会而闻名。

集群 2 包含两位艺术家:弗兰克·辛纳特拉和诺拉·琼斯(有爵士迷吗？).他们的音乐在声学和大调音阶调式上得分很高。然而，他们在其余所有属性上得分较低。典型的爵士乐。

集群 3 包含十位艺术家:波斯特马龙、凯戈、链烟者、阿黛尔、洛德、拉娜·德尔·雷、詹姆士亚瑟、一个共和国、约翰·传奇和艾丽西亚·凯斯。该集群在几乎所有属性上得分平均。这表明这一组艺术家在风格和创作方面是平衡的和多才多艺的，因此在这一组中呈现了流派的多样性(EDM，Pop，R & B)。

集群 4 包含 15 位艺术家:爱莉安娜·格兰德、魔力红、德雷克、贾斯汀比伯、布鲁诺·马斯、加尔文·哈里斯、查理·普斯、安立奎·伊格莱希亚斯、未来、维兹·卡利法、第五和声、亚瑟、麦克莫尔、扎拉·拉尔森和蕾哈娜。他们的音乐适合跳舞，声音洪亮，节奏快，充满活力。这个团体中有许多流行和嘻哈流派的年轻主流艺术家。

集群 5 包含 10 位艺术家:泰勒·斯威夫特、肖恩·蒙德兹、艾德·希兰、迈克尔·布雷、约翰·梅耶、埃尔顿·约翰、脚本、斯蒂维·旺德、杰森·玛耶兹和潘塔托尼克斯。这是我最喜欢的组合！泰勒·斯威夫特？艾德·希兰？约翰·梅耶？杰森·玛耶兹？埃尔顿·约翰？我想我听了很多创作型歌手的歌。他们的音乐大多是大调音阶，同时在所有其他属性上达到完美的平衡(平均分)。

集群 6 包含九位艺术家:肯德里克·拉马尔、坎耶·韦斯特、阿姆、碧昂斯、贾斯汀·汀布莱克、50 美分、李尔·韦恩、史努比·道格和 Jay-Z。你已经看到了这里的趋势:其中七位是说唱歌手，甚至碧昂斯和 JT 也经常与说唱歌手合作。他们的歌曲有大量的口语单词和类似演讲的部分，持续时间长，并且经常现场表演。对说唱音乐有更好的描述吗？

按特征分析

下面的图表显示了每个音乐家的每个特性的值。下面的代码详细说明了制作 danceability 分叉条形图的过程。其他特征的代码已被省略，但随后会显示每个特征的绘图。

可跳舞性

如果你想让你的迷恋者印象深刻，试着多听听未来，德雷克，维兹·卡利法，史努比杜古和阿姆。另一方面，不要试图跟着弗兰克·辛纳屈或拉娜·德尔·雷的曲子跳舞。

能量

如果你听很多马克斯梅洛、加尔文·哈里斯、安立奎·伊格莱希亚斯、马丁·盖瑞斯、阿姆、Jay-Z 的音乐，你就是一个相当精力充沛的人。如果你是弗兰克·辛纳特拉和诺拉·琼斯的粉丝，情况正好相反。

响度

响度排名和能量排名差不多。

语音

所有说唱乐迷:你最喜欢肯德里克·拉马尔的哪首歌？还是 50 美分？还是 Jay-Z？嗯，我很惊讶阿姆没有排名更高，因为我个人认为他是所有说唱歌手的山羊。

声音

声音与响度和能量正好相反。辛纳特拉先生和琼斯夫人在他们的职业生涯中发布了一些强有力的原声歌曲。

仪表化

EDM 为了胜利！马丁·盖瑞斯、Avicii 和 Marshmello 制作的歌曲几乎没有人声。

活跃度

那么表演现场录音最多的 5 位艺术家是谁呢？杰森·玛耶兹、酷玩乐队、马丁·盖瑞斯、坎耶·韦斯特和肯德里克·拉马尔，按此顺序。

化合价

配价是描述音轨所传达的音乐积极性的特征。布鲁诺·马斯、斯蒂维·旺德和安立奎·伊格莱希亚斯的音乐非常积极，而拉娜·德尔·雷、酷玩乐队和马丁·盖瑞斯的音乐听起来相当消极。

节奏

未来、马希梅洛和维兹·卡利法是速度之王。他们制作每分钟节拍数最高的曲目。还有史努比狗狗，lol？他往往需要一些时间来说出他的神奇的话。

持续时间

最后但并非最不重要的是，贾斯汀·汀布莱克的歌曲，其次是埃尔顿·约翰和阿姆的歌曲，有时长得令人难以忍受。相比之下，弗兰克·辛纳屈、莎拉·拉尔森和五弦琴喜欢较短的音乐。

结论

哇，我在 Spotify 数据上做这个分析和可视化项目时获得了很多乐趣。谁能想到詹姆斯·亚瑟和波斯特·马龙在一个集群里？还是说肯德里克·拉马尔是游戏里最快的说唱歌手？或者说马希梅洛会在制作充满活力的歌曲方面击败马丁·盖瑞斯？

无论如何，你可以在 my GitHub repository 这里查看完整的 R Markdown、用于处理和可视化数据的单独 R 代码以及原始数据集。从我自己的角度来看，R 在数据可视化方面比 Python 好得多，有像 ggplot 和 plot.ly 这样的库。我强烈建议您尝试一下 R！

— —

如果你喜欢这首曲子，我希望你能按下鼓掌按钮👏这样别人可能会偶然发现它。你可以在 GitHub 上找到我自己的代码，在【https://jameskle.com/】上找到更多我的写作和项目。也可以在 推特 ， 上关注我直接发邮件给我 或者 在 LinkedIn 上找我。 注册我的简讯 就在你的收件箱里接收我关于数据科学、机器学习和人工智能的最新想法吧！

使用电子商务监控解决方案通过双样本假设检验发现转换率下降

原文：https://towardsdatascience.com/spotting-conversion-rate-drop-with-two-sample-hypothesis-testing-using-e-commerce-monitoring-24542ada6122?source=collection_archive---------4-----------------------

简介

假设您有一个在线商店，您的客户必须完成一系列常见的步骤才能买东西:访问->添加到购物车->结帐->购买。假设一些客户在某个步骤卡住了，没有通过漏斗。假设你想知道这是不是因为一些技术问题。如果是，那么某个技术问题会在多大程度上影响您的收入变化？

前一段时间，我被要求帮助一家名为 Kuoll 的初创公司解决这样的问题，这是一个防止网上商店损失的电子商务监控解决方案。它估计 JavaScript 和 HTTP 错误等转换阻塞造成的损失，保留收入并控制变化。该团队改变了根据整个漏斗中每一步的转化率下降来衡量收入表现损失的问题。

转换率下降问题可以表述如下。假设有一些具体的电子商务错误，这是一个在你的网站购买漏斗的某个步骤的阻碍。让我们将用户分为两类:A 类用户和 B 类用户。A 类用户是在该步骤中没有遇到该特定拦截器的在线商店购物者，B 类用户是在同一步骤中遇到该特定拦截器的购物者。 N —类型为 A ， M 的用户总数——其中成功通过漏斗步骤的用户数(姑且称之为转化的用户)。 P —类型 B ， Q 的用户总数——其中转化的用户数。

*问题:*有没有因为这个特定的错误/拦截器导致转化率下降？

这个问题类似于经典的 A/B 测试问题。现在很流行。毫无疑问，使用双样本假设检验的模型种类繁多。我们测试了其中一些。这里需要注意的是，我们在某一点上卡住了。小观察数就是这种情况。它包括小样本量(其中 N 和 P 不够大)和极端频率(其中比例 M/N 和 Q/P 接近零或一)。我们习惯于谈论从海量数据中获取价值。但如果我们在没有足够数据的情况下不得不下结论呢？统计学的挑战性和艰巨性在于从有限的数据中得出一般性的结论。

双样本假设检验解决方案

为了更深入地研究数学，让我们记住下面的定义。

• 一个 假设 是关于一个概率模型的陈述。
• 一个 零假设 H₀ 是一个关于概率模型的特定陈述，可能会被拒绝。
• 一个 替代假设 H₁ 是一组与原假设相矛盾的假设。
• 统计 是可以从数据样本中计算出来的数值。
• p 值 是假设零假设为真，观察到至少与实际观察到的一样极端的检验统计的概率。
• 一个I 型错误 是当零假设为真时拒绝该假设。第一类错误的概率称为 测试的显著性 水平，表示为 α 。
• 一个 第二类错误 当其为假时不拒绝零假设。第二类错误的概率称为 β ，但是 β 的值通常取决于哪个特定的替代假设为真。
• 对于一个特定的备选假设，假设检验的 功效 为1-β，即拒绝一个特定的真备选假设的概率。

现在让我们考虑 2x2 列联表，其中一个二元变量由两行表示，另一个由两列表示:

其思路是使用 样本比例 p̂₁=a/m 和 p̂₂=c/n 以便比较相应的未知 人口比例 p₁ 和 p₂ 。比较这些比例的传统策略是统计假设检验。它通常由以下步骤组成。

1.陈述了 null 和 的替代假设 。
在零情况下(在零假设下)，比例 p₁ 和 p₂ 之差为零，双侧检验比例为 1。在大多数文献中，在单侧检验的情况下，比例 p₁ 和 p₂ 的差也是零，但是一些假设“它大于(小于)零”并且比率“大于(小于)一”。我们将使用第二个变量来表示单侧检验的零假设。
在我们的案例中 H₀ : 错误/阻断器不会导致转换下降和 H₁ : 错误导致转换下降。数学上h₀:p₁≤p₂对h₁:p₁>p₂。

2.定义 置信区间 和对应的 显著性水平α(α*)*。
如果原假设为真， α 为拒绝原假设的概率。

3.选择一个 测试统计 来运行您的测试/实验。
这里你应该决定比较两个比例 p₁ 和 p₂ 时要考虑哪个*。比较这些量的常用方法是差值和比值。另一个常见的衡量标准是优势比。*

4.然后应计算出 p 值 。

5.比较 p 值和 alpha。
如果 p 值小于α，则应拒绝零假设，否则不能拒绝。

6.力量 分析。对于大样本，各种检验的功效大致相同。然而，对于小样本来说，功率的差异可能相当大。功效分析应在用于分析数据的测试统计数据上进行。因此，您必须比较各种测试的功效，以确定使用哪种测试。本文不涉及功耗分析。我们进行了实验，但我们对测试统计数据的选择主要是由于后面将讨论的比较研究。

下面是一些测试和相应的测试统计数据的列表(这个例子应用于我们的数字),我们发现它们可以解决我们的问题。

两比例 Z-检验

无效假设和替代假设分别是:
h₀:p₁≤p₂对h₁*:p₁>p₂。*

这里 p 值是使用 z 分布得到的。让我们考虑一些(还有很多)比例的差异、比率和优势比的 z 检验。

1)对于足够大的样本，比例差近似正态分布。两个比例之差的 z 值由以下公式给出

在给定数据的情况下， p 值为

我们可以使用 Python 代码作为计算该值的示例:

2)对于足够大的样本，比例比的对数近似呈正态分布。用于检验比率是否等于 1:

在哪里

在带有数字 M、N、P 和 Q 的情况下， p 值为:

计算该值的 Python 代码:

3)对于足够大的样本，比例比值比的对数近似呈正态分布。检验比值比是否等于 1 的检验统计量:

在哪里

在有数字 M、N、P 和 Q 的情况下， p 值为:

计算该值的 Python 代码:

还有一个测试统计的修正版本，其中 z 乘以

两个比例之差的方差不等的双样本 t 检验

无效假设和替代假设分别是:
h₀:p₁≤p₂对h₁:p₁>p₂。

这里我们考虑 t 分布

有自由度的

在我们的例子中

****

p 值为

这种概率是使用带自由度的 1 尾 t 分布得到的

计算该值的 Python 代码:

费雪精确测验(中 P 版)

无效假设和替代假设分别是:
h₀:p₁≤p₂对h₁:p₁>p₂。

该测试使用超几何分布。Fisher 精确检验的 p 值是通过求和超几何概率来计算的。让我们考虑 2x2 表提供的给定数字:

以下是其他表格，在“有误差的转换”的比例上有更大的差异，但边际总数相同:

其中 x 介于 0 和 Q 之间。p 值是每个表的概率之和，其中只包括观察表概率的一半:

计算该值的 Python 代码:

二项式检验

无效假设和替代假设分别是:
h₀:p₁≤p₂对h₁:p₁>p₂。

在这种情况下，假设:
(1)某总体概率 p0=M/N (类似地，我们可以使用 Q/P )。
②下 H₀ ，x∾binomial(p,p₀)。所以在从总体中随机抽取的 P 个样本中，如果有 X 个正面结果，那么 p₂=X/P 就是样本比例。
我们观察到 Q 的正计数，因此 p 的值为

计算该值的 Python 代码(我们使用了 Fisher 精确测试中观察值的一半概率):

应该注意的是，有一些测试我们没有使用，因为它们是双面的。如果我们想知道是否有因特定阻断剂引起的转换率变化，我们可以使用它们。这些测试是:

卡方独立性检验

无效假设和替代假设是
H₀ : 数据来自特定的离散分布和
H₁ : 数据来自不同的分布。

该测试的测试统计数据为

其中
Oij —第 i 行和第 j 列对应的观察计数；

—对应于行 i 和列 j. 的预期计数

考虑到我们的 2x2 表，在提供测试统计数据的情况下，计算出 p 值

而且对应的是 1 个自由度的 Chi 平方分布。

计算该值的 Python 代码:

【似然比检验】

无效假设和替代假设是h₀:数据来自特定的离散分布和
H₁ : 数据来自不同的分布。

这个测试是似然比测试的一个例子。在 rxc 表的一般情况下， G 测试的测试统计为

其中
Oij-对应于行 i 和列 j 的观察计数；

—对应于行 i 和列 j. 的预期计数

假设零假设为真并且 n 足够大，该测试统计具有一个近似的卡方分布，具有 (r-1)(c-1) 个自由度。
在我们的案例测试统计中:

p-值是在考虑 LR 具有一个自由度的近似卡方分布的情况下得到的。**

计算该值的 Python 代码:

讨论

自从 1900 年以来，一直有很多关于解决 2 x2 T1 桌子问题的争论。在这个问题上仍然没有达成共识。下面是一个简短的回顾，它引导我们找到了自己的解决方案。

测试假设的传统方法(用于测量差异)是对大样本使用皮尔逊卡方检验(或z-单侧检验情况下的比例差异检验)，对中间样本大小使用耶茨卡方检验(具有连续性校正)，对小样本使用费希尔精确检验。一些科学家已经开始质疑这个解决方案。例如，一些人认为费希尔精确检验和耶茨检验不应该使用[3]。

E.Pearson 推荐了他的卡方检验的一个版本，其中将表达式(ad BC)2(n1)/mnrs(参见原始的 2x2 表)与具有一个自由度的卡方分布进行比较，即与原始分布相差因子*(n1)/N*。对于大样本量，与原始版本的差异很小，但在小样本量的分析中，这种差异变得至关重要[2]。**

当卡方检验在小样本情况下无效时的标准通常基于零假设下的最小期望细胞数，其等于(mm和 NN中的较小者)乘以(rr和 s ) /N 中的较小者)。大多数建议是，如果最小预期数小于 5，则不应使用卡方检验。这条规则通常被认为是科克伦的。他指出，数字 5 似乎是随意选择的，当有新的证据时，这些建议可能需要修改。[2]但这反而成了一种普遍的做法。

当基线比例很小(接近 0)或很大(接近 1)时，比率度量通常优于差异，因为它将差异表示为百分比而不是数量。

在极端情况下， 2x2 表格的某些单元格为零，需要特殊的方法。在这里，我们可以使用 Fisher 精确检验、二进制检验，或者将零改为一个小的正数，如 0.01。

I.Campbell (2007)在论文中提出的比较研究和论证数据提供了一组令人信服的证据，表明在分析 2×2 表时，就 I 型误差和功效而言(研究中考虑了双边检验)[2]:

(1)当所有预期数字至少为 1 时，通过’n1’卡方检验进行分析(K. Pearson 卡方检验，但用n1代替n1)

(2)否则，通过 Fisher–Irwin 检验进行分析，根据 Irwin 规则进行双边检验(根据观察结果尽可能或尽可能少地从任一尾部获取表格)。

我们的解决方案

我们对上述测试统计数据进行了实验，在我们为客户提供的报告框架内对结果进行了比较，并决定采用 I.Campbell 的研究并对其进行修改，以进行单侧测试。

我们选择测试统计的规则是:

**(1)在所有预期数*至少为 1 的情况下，我们通过’N-1 ’ z 检验来分析比例差异
)这里我们找到最小预期数，即
min(M+Q，N+P-(M+Q))min(N，P)/(N+P) ，并将其与 1 进行比较

(2)否则，我们通过 Fisher–Irwin 检验(单侧检验的中间 P 版本)进行分析

应该提到的是，我们决定报告 p 值，而不是在假设检验中做出是/否的决定，因为我们希望向我们的客户展示证据的力度，而不是做出正式的决定。假设检验由以下步骤组成:

1.陈述无效假设和替代假设(h₀:p₁≤p₂对h₁:p₁>p₂)；

2.计算检验统计量(根据每种类型的观察值的样本大小，使用上述规则选择正确的检验)；

3.计算一个p-值；

4.解释并报告结果。

进一步研究

我们决定将问题扩展到这样一种情况，即不是单独考虑每个阻碍因素，而是考虑一组影响转化率下降的阻碍因素。

结论

乔治·博克斯:“所有的模型都是错误的，但有些是有用的”。

指称

1. https://OCW . MIT . edu/courses/mathematics/18-05-introduction-to-probability and-statistics-spring-2014/readings/MIT 18 _ 05s 14 _ reading 19 . pdf
2. https://onlinelibrary.wiley.com/doi/pdf/10.1002/sim.2832或http://www.iancampbell.co.uk/twobytwo/twobytwo.htm
3. https://NCSs-wpengine . net DNA-SSL . com/WP-content/themes/NCSs/pdf/Procedures/ncss/Two _ proportions . pdf，NCSS 文档。
   https://www.ncss.com/software/ncss/ncss-documentation/NCSS 文档主页提供了大量的统计理论资源
4. Bret Hanlon 和 Bret Larget 的课堂笔记可在http://www.stat.wisc.edu/~st571-1/获得
5. 维基百科资源:
   https://en.wikipedia.org/wiki/Binomial_test https://en.wikipedia.org/wiki/Fisher%27s_exact_test https://en.wikipedia.org/wiki/Z-test https://en.wikipedia.org/wiki/Student%27s_t-test https://en.wikipedia.org/wiki/Likelihood-ratio_test https://en.wikipedia.org/wiki/Chi-squared_test

冲刺可视化轨道(第 1 部分):Matplotlib

原文：https://towardsdatascience.com/sprinting-into-the-visualization-track-part-1-matplotlib-6c069ac91e40?source=collection_archive---------6-----------------------

“A race on the running track at Anglo-Chinese School in Singapore” by Goh Rhy Yan on Unsplash

我们已经用 DataFrame 走了很久，

[## 开始走数据科学之路(下) :熊猫数据框架

在第 1 部分中，我们开始走这条路，一路上，我们遇到了系列。

towardsdatascience.com](/beginning-to-walk-the-data-science-road-part-2-pandas-dataframe-c3e898499d90)

并获得了相当大的势头。现在，我们准备开始冲刺。在这篇文章中，我们将开始研究可视化。到目前为止，我们看到的都是数字，文本，数组。无聊的东西。可视化是我们可以更好地交流我们的发现的方法。在这篇文章中，我们将看看可能是最流行的可视化库— Matplotlib 。特别是，我们将使用 Matplotlib 库中的 pyplot 模块。

一如既往，我们将寻求解决问题。首先，我们来看一个在图像处理中使用的著名图像——Lenna 图像。之后，我们将使用温度变化数据集在 pyplot 上再玩一些。

这篇文章的完整代码可以在下面的资源库中找到。

** [## bajracharya-kshitij/matplotlib

边解题边探索 matplotlib。为 bajracharya-kshitij/matplotlib 开发做出贡献，创建一个…

github.com](https://github.com/bajracharya-kshitij/matplotlib)

重要的事情先来。使用 anaconda 和 Python 3 安装 matplotlib。之后，导入 pyplot 模块。

import matplotlib.pyplot as plt

问题 1

Lenna 图像是图像处理中广泛使用的测试图像。

a)找出其广泛使用背后的原因。调查一下它有什么特别之处。

b)为图像的 RGB 值创建堆叠直方图。

c)表示饼图中(50，100)处像素的 RGB 值。

d)表示饼图中(100，100)处像素的 RGB 值。这个饼状图有什么不寻常的地方？

e)生成该图像的负片。

解决方案 1

快速的谷歌搜索应该会显示 Lenna 图片的结果。原图可以在这里找到，是 512x512 的 tif 图像。在这篇文章中，我将使用在这个链接中找到的图像，这是一个 220x220 的 png 图像。

注意:

关于这一形象的使用一直存在一些争议。但是用于测试的图像的裁剪版本对于工作是安全的。

让我们使用imread来读取图像lenna.png。

img = plt.imread('lenna.png')

如果你现在打印img，你会看到它是一个 3D 数组，每个条目的值在 0 和 1 之间。其他图像没有必要具有介于 0 和 1 之间的条目。例如，原始 tif 具有 0 到 255 之间的条目；只有我们选择的图像的值在 0 和 1 之间。然而，所有的 2D 图像都会形成一个 3D 阵列。如果你检查img.shape，你会得到(220,220,3)，表明这是一个三维数组。前两个维度代表图像的维度。但是最后那个3代表什么？

lenna 图像有 3 个颜色通道—红色( R )、绿色( G )和蓝色( B )。3代表这三个颜色通道。因此，前两个维度给出一个像素，最后一个给出该像素的 3 个颜色通道的值。为了更清楚起见，img[0][0][0]给出了第一个像素的 R 通道的像素值，img[219][219][2]给出了最后一个像素的 B 通道的像素值，所有剩余像素也是如此。

让我们现在显示图像。为此，我们使用imshow。

plt.imshow(img)

然而，单独这样做，我们会在结果中看到类似这样的内容。

<matplotlib.image.AxesImage at 0x119434fd0>

为了显示图像，我们需要使用plt.show(),之后我们将看到下图

如果你使用的不是 Jupyter 笔记本，每次你需要显示一个图形时，你都必须使用plt.show()。幸运的是，Jupyter 笔记本有一个魔法函数，你可以用它来显示图像，而不需要每次都调用 show 函数。你需要做的就是添加下面一行。

%matplotlib inline

然后你可以简单地使用plt.imshow(img)，它将返回图像。

那么，这张图片有什么特别之处呢？

为了理解这一点，让我们生成此图像的灰度版本。为此，首先我们将原始图像复制到一个新的 NumPy 数组中，并找到 3 个颜色通道的平均值。

import numpy as np
img_gray = np.copy(img)
img_gray[:] = img.mean(axis=-1,keepdims=1)

这将为每个像素在 3 个条目的每一个中放置平均值。这里，axis=-1将取最里面的轴，即每个像素的所有 3 个值，并求其平均值。当我们这样做的时候，其中一个维度被删除，得到的数组将是 2D。但是，我们希望通过用平均值替换三个值中的每一个来保留一个像素的所有三个值。这可以通过保留原始数组的尺寸的keepdims=1来实现。现在，plt.imshow(img_gray)将显示如下图像。

灰度图像的形状仍然保持(220,220,3)。让我们把它弄平，这样我们就可以在一个维度上得到所有的像素值。我们reshape它来获取像素值。

pixels = img_gray.reshape(220*220*3)

所以，pixels.shape现在返回(145200，)。现在我们有了平面阵列中的所有像素，让我们为这些像素值绘制一个直方图。

plt.hist(pixels,bins=255)

hist函数将所有像素值作为第一个参数。它还需要一个我们设置为 255 的bin参数。

什么是垃圾箱？

为了更好地解释直方图和条柱，请查看此链接。

当您运行上面包含hist函数的代码块时，它返回两个数组。第一个数组的前 5 个条目如下所示:

6.,     0.,     0.,     9.,     6.

第二个数组如下所示:

0.12679739,  0.12989876,  0.13300014,  0.13610151,  0.13920288

那么，这些数字是什么？如果仔细观察，您会发现两个数组都有 256 个条目，比我们在bins中设置的多一个。现在，如果你生成一个线性间隔的数组，比如

np.linspace(pixels.min(),pixels.max(),256)

它将生成与上面第二个数组完全相同的数组。因此，bins正在做的是，从像素阵列中获取最小和最大值，生成 256 个线性间隔的数字，然后创建块(其中 255 个)。因此，第一个块将具有范围0.12679739-0.12989876，第二个块将具有范围0.12989876-0.13300014，以此类推，直到上限等于pixels.max()的最后一个块。那么第一个数组呢？嗯，它是值落在相应块内的频率。因此，有 6 个像素的值落在第一个范围内，9 个像素的值落在第四个范围内，依此类推。为什么是 256？你可以选择任何你喜欢的数字。我选择 256 是因为这是一个很好的数字，因为在灰度图像中有 256 种不同的亮度。如果没有为bins提供值，缺省值是 10。

使用这些值，hist函数将生成一个如下所示的直方图

从图中可以看出，lenna 图像几乎覆盖了整个面元范围。几乎每个箱都有强度值。正如在这个链接中提到的，Lenna 图像包含了细节、平坦区域、阴影和纹理的良好混合，可以很好地测试各种图像处理算法。这就是该图像被视为标准测试图像的原因。

现在，让我们分别看看每个颜色通道。我们在单独的数组中提取对应于每种颜色的像素。

img_r = img[:,:,0]
img_g = img[:,:,1]
img_b = img[:,:,2]

如果你检查这些数组的shape，你会得到(220，220)。所以我们现在有三个 2D 阵列。像前面一样，让我们为这三个元素创建一个平面一维数组。

img_r_pixels = img_r.reshape(220*220)
img_g_pixels = img_g.reshape(220*220)
img_b_pixels = img_b.reshape(220*220)

现在，我想看看这三个像素的亮度是如何分布的。如果所有的颜色都可以并排比较就好了。我们可以使用subplot来做到这一点。

plt.subplot(1,3,1)
plt.hist(img_r_pixels,bins=100)plt.subplot(1,3,2)
plt.hist(img_g_pixels,bins=100)plt.subplot(1,3,3)
plt.hist(img_b_pixels,bins=100)

如果你用过 MATLAB，你可能对支线剧情很熟悉。subplot函数有三个参数——第一个是行数，第二个是列数，第三个是索引号。然后对于plt.subplot(1,3,2)上面的第二个支线剧情表示我们要取剧情，把它分成 1×3 的格子，这样一共 3 个支线剧情都是可能的，然后我们要用第 2 个支线剧情。之后，我们用plt.hist(img_g_pixels,bins=100)绘制绿色像素的直方图。这里我们用bins=100。同样，您可以使用任何您喜欢的数字。或者直接跳过。

然后我们有这样的东西:

但这看起来不太好。这些地块非常拥挤，纵轴上的数字相互重叠。让我们在结尾加上plt.tight_layout()让它更漂亮一点，就像

plt.subplot(1,3,1)
plt.hist(img_r_pixels,bins=100)plt.subplot(1,3,2)
plt.hist(img_g_pixels,bins=100)plt.subplot(1,3,3)
plt.hist(img_b_pixels,bins=100)plt.tight_layout()

很好。我们还可以使用hist函数中的histtype参数来生成不同类型的直方图。现在，直方图在宽度方向上看起来仍然有点拥挤；这个数字比我们想要的要小得多。所以，让我们用plt.figure来设置支线剧情的大小。

plt.figure(figsize=(15,5))
plt.subplot(1,3,1)
plt.hist(img_r_pixels,bins=100,histtype='barstacked')plt.subplot(1,3,2)
plt.hist(img_g_pixels,bins=100,histtype='step')plt.subplot(1,3,3)
plt.hist(img_b_pixels,bins=100,histtype='stepfilled')plt.tight_layout()

figsize将元组作为参数—元组的第一个条目是宽度，第二个条目是高度，以英寸为单位。我们可以将这些值更改为最适合该图的值。对于上面的例子，我们现在有一个类似于

好多了。现在，只有 3 种颜色，但还是让我们通过给这些图添加颜色来使它更清晰。

plt.figure(figsize=(15,5))
plt.subplot(1,3,1)
plt.hist(img_r_pixels,bins=100,histtype='barstacked',color='r')plt.subplot(1,3,2)
plt.hist(img_g_pixels,bins=100,histtype='step',color='g')plt.subplot(1,3,3)
plt.hist(img_b_pixels,bins=100,histtype='stepfilled',color='b')plt.tight_layout()

接下来，让我们标记坐标轴，并给每个支线剧情一个标题。使用xlabel和ylabel作为轴，使用title作为子剧情标题。

plt.figure(figsize=(15,5))
plt.subplot(1,3,1)
plt.hist(img_r_pixels,bins=100,histtype='barstacked',color='r')
plt.title('Histogram for Red Pixels')
plt.xlabel('Pixel Intensity')
plt.ylabel('Frequency')plt.subplot(1,3,2)
plt.hist(img_g_pixels,bins=100,histtype='step',color='g')
plt.title('Histogram for Green Pixels')
plt.xlabel('Pixel Intensity')
plt.ylabel('Frequency')plt.subplot(1,3,3)
plt.hist(img_b_pixels,bins=100,histtype='stepfilled',color='b')
plt.title('Histogram for Blue Pixels')
plt.xlabel('Pixel Intensity')
plt.ylabel('Frequency')plt.tight_layout()

最后，让我们把这三个支线剧情叠加成一个

colors = ['red','green','blue']
plt.hist([img_r_pixels,img_g_pixels,img_b_pixels],bins=100,stacked=True,color=colors,normed=1)
plt.title('Stacked Histogram')
plt.xlabel('Pixel Intensity')
plt.ylabel('Frequency')

这里，hist接受一个像素值数组，这个数组的每个元素都用color参数赋予适当的颜色。stacked=True创建堆积直方图。由于 3 个颜色通道具有不同的频率范围，如单独的子图所示，我们使用normed=1标准化直方图。

使用normed=1，强度值将以这样一种方式绘制，即强度值和相应箱宽度的乘积总和等于 1。有关normed的更多信息，请参见此链接。

在我们的例子中，如果我们设定

x = plt.hist([img_r_pixels,img_g_pixels,img_b_pixels],bins=100,stacked=True,color=colors,normed=1)

打印出 x，我们将看到它返回一个元组—第一个元素是每种颜色的像素强度数组，第二个元素是 bin 大小数组。我们将把它设置为

r = x[0][0]
g = x[0][1]
b = x[0][2]
bins = x[1]

如果我们检查每一个的尺寸(像r.size，我们会看到r、g和b的尺寸是 100，而bins的尺寸是 101 (100 个间隔)。然后，如果我们找到总和，

def getSum(frequency_array, bins_array):
    _sum = 0
    for i in range(frequency_array.size):
        _sum += frequency_array[i] * (bins_array[i+1]-bins_array[i])
    return _sum

print("red px: ", getSum(r,bins))
print("green px: ", getSum(g,bins))
print("blue px: ", getSum(b,bins))

我们会回来的

red px:  0.333333333333
green px:  0.666666666667
blue px:  1.0

显示总和为 1，因此强度已经被归一化。

只有直方图？pyplot 能做的就这些吗？

我们已经看到了很多使用直方图的东西。但这只是 pyplot 可以生成的各种图之一。查看官方 matplotlib 网站中的图库页面，查看可以使用 pyplot 绘制的所有各种类型的绘图。现在，让我们转移到一些其他类型的情节。

接下来，让我们看看饼状图。让我们取(50，100)处的一个像素，并找出该像素处的主色。

intensities = img[50][100]

intensities给出一个类似于

array([0.8745098 , 0.7058824 , 0.64705884], dtype=float32)

因此，在(50，100)处，3 种颜色有 3 个强度值。要绘制一个代表该像素亮度的饼图，我们需要做的就是

plt.pie(intensities)

饼状图将被绘制成

但目前看起来不太好看。第一，它不是一个规则的圆。让我们解决这个问题。

plt.pie(intensities)
plt.axis('equal')

好多了。现在，我们不能真正区分哪个部分代表哪个颜色。请注意，这个饼图中的蓝色和绿色不一定表示蓝色和绿色部分。这只是 pyplot 分配的 3 种默认颜色。所以，让我们给馅饼添加一些更多的信息。

colors = ['red', 'green', 'blue']
explode = (0, 0.1, 0)
plt.pie(intensities,labels=colors,colors=colors,autopct='%1.2f%%',explode=explode)
plt.axis('equal')

好多了。plt.pie这里以intensities数组为数据。它使用colors数组将labels和colors应用于截面。autopct=’%1.2f%%’格式化区段的百分比，对于explode参数，它只是从圆形饼图中分离出一个区段。我们将explode=(0,0.1,0)定义为我们想要将第二个元素(绿色元素)分解 0.1。如上图所示，绿色部分已被分离。因此，从这个饼图中我们可以看到，在 pixel (50，100)处，像素值几乎相似，但红色比其他两个略占优势。

让我们再次做同样的事情；这次是像素(100，100)。

intensities2 = img[100][100]
print(intensities2)
colors = ['red', 'green', 'blue']
plt.pie(intensities2,autopct='%1.2f%%',labels=colors,colors=colors)
plt.axis('equal')

太奇怪了。它看起来不像普通的馅饼。如果你把各部分加起来，它甚至不是 100%。那么，为什么会这样呢？如果你看看文件，你会发现

每个楔形的分数面积由x/sum(x)给出。如果sum(x) < 1，那么 x 的值直接给出分数面积，数组不会被归一化。生成的饼图将有一个大小为1 - sum(x)的空楔形区

如果你打印出intensities2，你会得到

array([0.34509805, 0.07843138, 0.24705882], dtype=float32)

的确，如果你把它们都加起来，总和在 1 以下。这就是为什么数组没有被规范化，并且有一个空的楔形，一个空的部分覆盖了大约 33%的饼图。

现在，要生成图像的负片，我们只需要从 255 减去原始亮度值。

plt.imshow(255-img)

就这么简单。但是让我们来硬的，看看会发生什么。首先，我们为 3 个颜色通道中的每一个通道生成一个反向像素阵列。

img_r_pixels_inv = 255 - img_r_pixels
img_g_pixels_inv = 255 - img_g_pixels
img_b_pixels_inv = 255 - img_b_pixels

然后我们把这些组合成一个数组。

pixels_inv = np.append(img_r_pixels_inv,(np.append(img_g_pixels_inv,img_b_pixels_inv)))

我们一个接一个地为每种颜色添加像素，现在如果我们检查pixel_inv的shape，我们将得到(145200，)即 2202203。接下来我们reshape将这些像素还原到原始的三维空间并绘制图像。

img_inv = pixels_inv.reshape(220,220,3)
plt.imshow(img_inv)

这很有趣，但这不是我们想要的形象。为什么会这样？如果你检查255-img和img_inv，你会发现它们是两个不同的数组。原因是在后一种方法中，我们首先堆叠所有的 R 像素，然后是 G 像素，最后是 B 像素。我们应该做的是取第一个 R 像素、第一个 G 像素和第一个 B 像素，它们将形成数组的第一个分量，并对所有像素重复同样的操作。

让我们试试别的东西。让我们首先用全零初始化一个(220，220，3)数组img_inv。然后，我们为 3 个通道中的每一个找到逆像素。我们使用两个循环，找到每个颜色通道的i*j像素的倒数。

img_inv = np.zeros((220,220,3))
for i in range(220):
    for j in range(220):
        img_inv[i][j][0] = 255 - img_r_pixels[i*j]
        img_inv[i][j][1] = 255 - img_g_pixels[i*j]
        img_inv[i][j][2] = 255 - img_b_pixels[i*j]

现在，如果我们检查img_inv，我们将得到一个看起来像这样的图像

那确实是一件很好的艺术品，但不是我们想要的。然而，我们可以看到，这可以用来如果你想加密你的图像。当然，这是一种非常幼稚的加密方法，但如果你愿意，你也可以这样做。

上面的方法行不通，因为我们不想要第i*j个像素；我们想要第[i][j]个像素。让我们最终做到这一点。

img_inv = np.zeros((220,220,3))
for i in range(220):
    for j in range(220):
        img_inv[i][j][0] = 255 - img_r[i][j]
        img_inv[i][j][1] = 255 - img_g[i][j]
        img_inv[i][j][2] = 255 - img_b[i][j]

最后，我们在img_inv中有了我们需要的负像。

让我们使用savefig将该图像保存为一个新文件。

plt.axis('off')
plt.savefig('lenna_negative.png',dpi=300)

plt.axis('off')从图像中删除轴和标签。savefig的第一个参数是文件名，而dpi定义了我们希望图像拥有的每英寸点数。

图像将保存在记事本所在的文件夹中。您可以提供一个不同的路径来将其保存在您想要的位置。

所以，pyplot 只对图像起作用？

不，绝对不行。这只是一个应用。事实上，大多数时候，我们会处理文本数据。就像我们接下来要看的一样。

现在，让我们离开图像。在本节中，我们将使用温度变化数据集，通过 pyplot 探索更多的曲线图。

问题 2

给定来自Kaggle上此链接的 globallandtemperaturesbycount . CSV 数据集，使用 1960 年至 2009 年期间的数据

a)显示尼泊尔的年气温

b)比较美国、英国和中国的年气温

c)从(b)中找出温度的平均值和范围

解决方案 2

数据集在一个 csv 文件GlobalLandTemperaturesByCountry.csv中，所以让我们首先使用 pandas 来读取它。

import pandas as pd
df = pd.read_csv('GlobalLandTemperaturesByCountry.csv')

如果我们检查这个数据帧的info，我们会看到它有 4 列— dt即日期、AverageTemperature、AverageTemperatureUncertainty和Country。您可以使用head或tail查看更多信息，它列出了从 1743 年到 2013 年所有国家的月平均气温。一些月份的数据丢失了。很难看到所有这些数据，所以让我们只看一个特定的国家。在这里，我们看看尼泊尔。

df_nepal = df[df['Country']=='Nepal']

现在，我们只有df_nepal中尼泊尔的条目。如果我们检查info，我们看到它仍然有 2613 个条目。我们不想要 18 世纪的数据。此外，很多 20 世纪以前的数据都丢失了。所以，让我们来看看从 1960 年初到 2009 年底的 50 年间的数据。

df_nepal = df_nepal[(df_nepal['dt'] >= '1960-01-01') & (df_nepal['dt'] < '2010-01-01')]

因此，我们过滤掉数据，只剩下 600 个条目。现在我们有 50 年的月平均气温。让我们把它改成年平均温度。

start_year = 1960
year = np.zeros(50)
avg_temp = np.zeros(50)
for i in range(0,df_nepal.shape[0],12):
    new_df_index = int(((i+12)/12)) - 1
    year[new_df_index] = start_year + new_df_index
    avg_temp[new_df_index] = np.mean(
                      [df_nepal.iloc[i:i+12]['AverageTemperature']])
df_nepal_annual = pd.DataFrame([],columns=['year','avg_temp'])
df_nepal_annual['year'] = year
df_nepal_annual['avg_temp'] = avg_temp

我们从start_year = 1960开始，设置year和avg_temp保存 50 年及其各自的年平均温度，并暂时将其设置为全零。接下来，我们遍历 600 个条目，其中 600 由df_nepal.shape[0]给出，我们采取 12 的步骤，以便每 12 个条目被分组在一起，并且结果由new_df_index给出的单个索引来表示。使用此new_df_index，条目被追加到year和avg_temp中。对于avg_temp中的每个条目，取连续 12 行的平均值，并设置为当年的年平均温度。最后，创建一个新的数据帧df_nepal_annual，并将year和avg_temp的更新数组设置到该数据帧的列中。

如果我们现在检查df_nepal_annual，这个数据帧中正好有 50 个条目，只有两列——year 和 avg_temp。我们现在可以绘制 avg_temp 对应的年份

plt.plot(df_nepal_annual['year'],df_nepal_annual['avg_temp'])

以获得以下情节。

我们可以看到，在过去的半个世纪里，尼泊尔的气温一直在稳步上升。

现在，我们想将同样的概念扩展到绘制其他国家的温度。这里，我们来看看三个国家——美国、英国和中国。因为我们将对所有国家做同样的事情，所以让我们把上面的代码放到函数中。

def getDataForCountry(df,countryName):
    df_country = df[df['Country']==countryName]
    return df_country[(df_country['dt'] >= '1960-01-01') &   (df_country['dt'] < '2010-01-01')]

getDataForCountry提取 1960 年至 2009 年(含)50 年间的数据。

让我们定义另一个函数来计算年平均温度。

def getAvgTempByYear(df):
    start_year = 1960
    year = np.zeros(50)
    avg_temp = np.zeros(50)
    for i in range(0,df.shape[0],12):
        new_df_index = int(((i+12)/12)) - 1
        year[new_df_index] = start_year + new_df_index
        avg_temp[new_df_index] = np.mean([df.iloc[i:i+12]['AverageTemperature']])
    df_annual = pd.DataFrame([],columns=['year','avg_temp'])
    df_annual['year'] = year
    df_annual['avg_temp'] = avg_temp
    return df_annual

现在，使用这个函数getAvgTempByYear，我们可以对所有三个国家进行计算。为了更好地进行比较，让我们在一个图中绘制所有国家的图表。

df_usa = getDataForCountry(df,'United States')
df_usa = getAvgTempByYear(df_usa)
plt.plot(df_usa['year'],df_usa['avg_temp'],'r--')df_uk = getDataForCountry(df,'United Kingdom')
df_uk = getAvgTempByYear(df_uk)
plt.plot(df_uk['year'],df_uk['avg_temp'])df_china = getDataForCountry(df,'China')
df_china = getAvgTempByYear(df_china)
plt.plot(df_china['year'],df_china['avg_temp'],'#0be3df')

我们可以使用红色虚线r--，使用类似#0be3df的六进制代码，或者简单地使用默认颜色，而不需要明确定义。最终的图看起来会像这样

让我们也为这些线条添加一个图例。

plt.legend(['USA','UK','China'],loc=(0.2,0.25))

第一个参数是标签，第二个参数是loc，它是图例的位置。loc当给定一个元组时，将图例放置在绘图左下方的该百分比处。所以在这种情况下，20%来自左边，25%来自底部。

可能很难手动确定图例的最佳放置位置。因此，我们可以使用loc='best'，pyplot 将图例放在可能的最佳位置。这一次，让我们也加入一个网格，这样就可以很容易地可视化这些值。

plt.legend(['USA','UK','China'],loc='best')
plt.grid()

所以，左上角似乎是这个剧情传说的最佳位置。正如我们所看到的，美国和英国的气温变化非常相似，在 20 世纪 70 年代末到 80 年代初有所不同，在 80 年代末，美国似乎每年都比英国热。这些年中国似乎相对更冷。

最后给三国画个boxplot。

plt.boxplot([df_usa['avg_temp'],df_uk['avg_temp'],df_china['avg_temp']],labels=['USA','UK','China'])

使用单个箱线图可以传达许多信息，如中位数、四分位数和极差。盒状图由盒状图和须状图组成。根据文件，方框从数据的下四分位数延伸到上四分位数，中间有一条线。触须从框中延伸出来，以显示数据的范围。

美国和英国的平均气温非常接近 9 度，而中国的平均气温在 7 度左右。美国的范围超过 1.5，最高和最低温度分别略高于 10 度和低于 8.5 度。中国也有 1.5 左右的类似区间。英国的范围约为 2。

可视化是一种重要的技术，通过这种技术，大量的信息可以以一种更紧凑的方式传达。此外，它比纯文本、数字和表格更具视觉吸引力。和往常一样，这并不是 pyplot 所能做的详尽列表。但现在如果我们需要任何情节，至少我们知道去哪里找。**

SPRITE 案例研究#2:极化 Porterhouse 的案例(以及一些更新)

原文：https://towardsdatascience.com/sprite-case-study-2-the-case-of-the-polarizing-porterhouse-and-some-updates-7dfe4d1564fc?source=collection_archive---------0-----------------------

在我之前的文章中，我通过一个例子简单介绍了 SPRITE——cart horse Child。

REGULAR HUMAN CHILD DEMANDS BREAKFAST WILL HAVE 12lb OF OATS PLS

这是我能找到的最简单的案例研究，也是最有趣的，因为它是好马和孩子的交叉。

案例研究探讨了基本问题:给定样本的常规汇总统计数据(均值、标准差和 n)，符合该描述的假设样本的属性是什么？

这种情况下的答案是，如果你有 45 个孩子，他们平均吃了 19.4 个胡萝卜(SD=19.9)，那么大多数符合描述的样本至少有一个孩子吃了至少 60 个胡萝卜，因此是一匹马。

许多人有异议和观点:

你怎么能确定‘胡萝卜’是完整的胡萝卜呢？

几个原因。我希望你觉得它们很有说服力:

a)在这项研究中，以及同一作者的许多其他类似研究中，它们一直被称为“胡萝卜”。有时它们被明确称为“小胡萝卜”(只是不在本文中)。但是在感兴趣的论文中也没有使用其他语言——切片、切块、缩略等。在美国，以我的经验(作为一个肮脏的偷工作的移民)，当人们提到“胡萝卜”时，他们总是指小胡萝卜。

b)这里是作者对这项研究的文字描述——这种特殊的超级太空胡萝卜在照片和菜肴中都有展示。他们就在那里。看看他们橙色的荣耀。

现在，我想，我希望，我是合理的。我也花了比平时更多的时间去思考胡萝卜。我不想仓促行事，但也非常希望有一些当前的和合适的东西来最终介绍雪碧——我已经断断续续玩了很久了——然后马童出现了。你认为我有多长时间有机会向人们介绍法医元科学技术？？

如果我没理解错 SPRITE 的话，你列举的很多问题，难道不能用适当的统计推导来解决吗？没有你的循环重的方法，一些样品是不可能的。

是的，你绝对可以找到适当的统计关系来解决精灵问题。(我们可以在某个时候详细讨论它们。)是的，我的方法也有点丑。它也是 a)多用途 b)直观 c)简单。

(目前正在做一些优化。)

问题是:我不是统计学家。我甚至不确定如何开发更神秘的工具，即使我开发了，我也不确定其他人是否会使用它们。根据我的经验，人们会被他们理解的事物所吸引。

你知道你使用的洗牌法背后的所有假设吗？它赋予你的解决方案什么样的属性？会生成一类答案还是所有答案？

非常好的问题，还不是 100%确定。但是——我有不止一种方法，而且它们在相同种类的解决方案上相当集中。我会尽我所能解释它们。

你见过类似的方法吗，XYZ？

我现在有了！我已经收到了四封关于这个问题的电子邮件，都是来自致力于解决这个问题的人(而且都是以不同的方式)和有额外分析建议的人。我感谢你们所有人给我发来这些东西，我学到了很多东西(即使是在几天内)，我想和所有相关的人谈谈。请和谢谢你。

你的马和胡萝卜的笑话是糟糕的和/或不恰当的。

“摆在委员会面前的建议是:不要再开马的玩笑了。所有赞成的人？”

一些零散的答案…

“所有反对的人？”

* 嘶声！*

(是的，我去了。但是我现在在浪费时间。)

案例研究# 2——两极分化住宅的案例

今天，我们要做的事情比胡萝卜的例子稍微复杂一点，那就是 a) 在没有 SD 的情况下工作，而不是在一系列 SD 上采样以建立一个合理的 SD，b) 在上面的基础上引入一个简单的约束。

为了让 SPRITE 提供有用的信息，约束非常有用。它们允许我们将大量的可能数字减少到更小的数字。或多或少，任何约束都可以用来以某种方式照亮子画面。

下面的例子来自 Wansink 等人(2003 年)的论文:“探索不同年龄和性别的舒适食品偏好”。它提出了一项关于不同性别和年龄的人对舒适食品偏好的调查。该论文目前有 330 篇引文。下面是表 2 中的结果:

以防不明显，这是一个方法表。它涉及到一项针对美国人的大规模调查，调查者对所提供的食物是否符合“舒适食品”的标准进行评级。标度从 1 到 5——所以 1 是在犯下重罪后站在小巷里被吃掉，5 是在家看《网飞》时在羽绒被下被吃掉。

忽略其他错误，让我们来谈谈肉(‘牛排或牛肉’)。

对于男性来说，牛排的平均值为 3.2，我想这意味着它“有点”让人舒服。

而对于女性来说，平均值为 2.8，这“略低于令人欣慰的水平”。

总之。

根据表格，当你用单因素方差分析比较这两个值时，你得到的 F 值是 17.8。在这种情况下，ANOVA 返回与 t 值完全相等的 F 值，t = F。换句话说，对同一组进行常规的旧 t 检验将返回 t 值 4.22。这是一个很大的区别。

因此，为了方便起见，让我们从假设这些组之间的相同的 SDs 开始，然后反算SDs。

(我们还不需要 SPRITE，但我们可以用它来精确地再现上面的内容——只需运行它而不受规模限制，并迭代地改变 SD，直到我们得到 SD=1.47 的解，这给出了 4.2179 的 t…可爱！)

所以，现在我们的假设数据集是:

男性:平均值= 3.2，标准差= 1.47，n = 401

女性:平均值= 2.8，标准差= 1.47，n = 602

唯一的问题是…？

恶作剧问题。百分之百没问题。

以下是针对每种情况的 SPRITE 解决方案示例:

好吧，那就这样吧。

对吗？当然不是。

事实上，这份报纸继续把它的脚完全放进去:

另一种检验男性和女性对安慰性食物评价不同的一般趋势的方法是构建一个百分比接受度的替代测量，将对食物评价为 4 =同意或 5 =非常同意的人编码为接受该食物为安慰性食物的人[93]。在这样做的过程中，发现女性对糖果和巧克力的接受百分比更高[69%对 58%；v2 = 4.8p 在这一过程中，发现女性对糖果和巧克力的接受率更高[69%对 58%…] ，但对牛排或牛肉等与膳食相关的食物的接受率较低[52%对 77%] 。

这就是我们的限制。

让我们从男人开始:我们必须采用上面那些相当平坦的分布，并以某种方式将 77%的个体值塞进 4 或 5 个容器中。

这可能吗？

嗯，平均值低于 4，所以在我们最受约束的可能情况下，将会有*no 5 .*显然，这是愚蠢的，因为许多美国男人对牛排有什么接近蒸汽般的浪漫，但我们现在让它过去。

如果我们只对从 1 到 4 的值运行 SPRITE，我们会得到最大可能的解决方案，这真的很奇怪:

这是数据能做到的最好的字面意思——除了 1 和 4 什么都没有。平均值仍然在它应该在的地方(3.2)，但即使在这种完全疯狂和极端的情况下，我们只有 1.33 的标准差。

但是你可能还记得以前，我们编造了那个 SD。所以违反它不是一个问题。问题是，这是获得大于 4 的多个值的绝对最佳情况，而不是 77% —它达到了 73.3%。

然而，女性数据可以存在。雪碧会把它弹出来。

…只是看起来很滑稽。

是的，看起来很傻，但事实上我们得到了我们想要的——足足 59.4%的值是 4 或 5(也就是所有的 4)，超过了 52%的阈值。

因此，与其最大化我们的分布，不如强制我们的样本的 52%都是 4s。使用 SPRITE 很容易做到这一点:我们需要从 313 个 4 开始，当我们在剩余的 n 个中分割剩余的和时，可以找到样本参数。

具体来说:我们现在需要找到一个平均值为 1.5 的 n=289 的样本，当我们将它添加到 n=313 个 4 的巨大堆栈中时，我们就得到我们的总体平均值。

所以，这里有两个解决方案:

现在，上面的第一种情况将给出最大 SD(都是关于 1 的)，它只比我们开始时的假设值差一点点:SD=1.39。

然而，我们现在面临的前景是，样本中只有极少量的 3，而没有任何 5。你认为这些分布，或者你从 1 到 3 打乱数值的类似分布，是现实的吗？他们不是。但它们是唯一能用的。

结论

如前所述,“男人”的案子是不可能的,“女人”的案子简直是不可能的。

即使牛排是有史以来最奇怪的两极分化的食物，是世界各地战争和骚乱的形式，将人们分成一种烹饪阿崔迪斯和哈康宁(这里显示了我的年龄)，也没有可能重建论文中给出的数字。

论文中还有其他不一致的地方，但它们将不得不等待。

下一次，我们会看一些更复杂的东西。

虚假统计显著性:为什么大型实验设计可能是危险的

原文：https://towardsdatascience.com/spurious-statistical-significance-why-a-large-design-of-experiment-could-be-dangerous-418eceb189c0?source=collection_archive---------4-----------------------

在半导体工艺或产品设计中，经常会涉及大量的变量。很多时候，在初始学习周期阶段，大量的 PCM()过程控制监视器 )将被插入到布局设计中。这些是 IC 设计参数的各种可能组合，它们与工艺变量混合在一起，进行详尽的 实验设计(DoE) 研究，希望发现统计上显著的关系(或确认一些现有的但未经验证的理论)，这可以导致良好的设计理解并简化开发过程，即在项目的后期阶段只需要一组较小的实验。

标准 IC 设计参数的例子可以是——有源单元间距、栅极长度、栅极-漏极重叠、晶体管宽度、宽长比、终端单元宽度和形状等。工艺变量包括(但不限于):离子注入能量/剂量/角度、各种薄膜(例如栅极电介质、场氧化物、氮化物掩模)参数、硅或氧化物蚀刻比率和配方、金属成分和沉积变量等。

从统计角度来说，这些工艺/设计变量中的每一个都与一个假设相关联，该假设有可能说明其与一个或多个输出参数的关系，即表示最终设备性能或可靠性的数量。

然而，关键问题是:在存在大量假设的情况下，即使遵循标准/严格的统计显著性检验方法，是否也有可能出现错误发现？

不幸的是，答案是肯定的，这是“假设-购物”的结果。一个简洁的演示就是 这里的 。

(非常)粗略地说，如果您使用任何统计建模技术(比如线性回归)测试至少 20 个假设(对应于调查 20 个 PCM 变量中的任何一个是否与特定器件输出参数相关)，并列出 P 值，您可能会发现至少一个 P 值小于 0.05(这通常是拒绝零假设的黄金标准边界值)，即使所有 20 个变量实际上都与输出参数不相关。现在，考虑到 P 值解释的普遍性和诱人的简单性，它们很难在假设检验场景中被忽略，您有义务报告这一发现，即这一特定的过程/设计参数将被标记为控制变量，它可以以统计显著的方式影响输出。

下面的图表说明了当你试图从一大组变量中发现具有统计显著性的关系时，存在大量假设的潜在危险。在本例中，所有的 x 变量都是完全随机生成的，输出 y 变量也是如此，即对于任何场景，在 x 和 y 之间都不应该有任何统计上的显著关系。

但是随着假设数量的增加，统计显著 P 值的数量和假设数量之间存在明确的正相关关系(由正斜率线性拟合线显示),即更大的实验组更有可能发现 y 和 x 之间的关系，即使它们基本上是由不相关的随机过程产生的。如图所示，一个明显的趋势是，随着 alpha 值阈值从 0.05 降低到 0.01，这种正相关性变得“弱”。

有趣的是，这种现象与产生变量和输出的潜在自然过程的性质无关。我用 R 编写了一小段代码来生成这个图，并使用了 rnorm 和 runif 函数来运行它，即从正态高斯和均匀分布中提取了 x 和 y 变量，但是上面的图的一般性质保持不变。基本 R 代码在 GitHub 里。

现在，在半导体设计领域，大多数时候输入/输出的功能关系是相互关联的，这使得这种现象更加复杂，难以确定和隔离。此外，如果一个人不止一次地进行类似的实验，这种伪性质很容易被发现，但是在半导体设计中，由于与大的/重复的实验周期相关联的大的成本和开销，学习周期信息通常被用于调整未来的实验。

一般来说，谨慎的做法是在设计 DoE 时从较小的变量子集开始，并在使用尽可能小的 P 值时得出因果关系的结论。**与社会科学或医学研究不同，半导体设计中的统计建模不必符合 0.05 作为 alpha 值。**在分析了具有最显著输入变量的第一组数据并看到最高 P 值仅为 0.0001 后，为什么不将其降至最小可能值？

有许多研究和可用的方法来避免这个陷阱。希望在另一篇文章中写下它们。这是一个很好的介绍性演示。

原载于 2017 年 7 月 4 日【https://www.linkedin.com。

使用 Apache Spark SQL 和数据框架扩展 SQL——概念、架构和示例

原文：https://towardsdatascience.com/sql-at-scale-with-apache-spark-sql-and-dataframes-concepts-architecture-and-examples-c567853a702f?source=collection_archive---------0-----------------------

使用您友好的 SQL，轻松地大规模辩论、聚合和过滤数据！

Source: Pixabay

注意

本文涵盖了与 Spark、SQL 和 DataFrames 相关的详细概念。除此之外，我们还将介绍一个使用 Spark SQL 和 DataFrames 大规模使用 SQL 的实践案例研究。如果第一眼看上去这篇文章有点让人不知所措或者很长，你可以在opensource.com的以下链接中找到同样的内容

• 用 Apache Spark SQL 和 DataFrames 扩展关系数据库
• 如何使用 Spark SQL:动手教程

我希望这有助于您开始使用 Spark 和 SQL 的旅程！

介绍

不管宣传以及通常被称为*‘NoSQL’*数据库的新数据库的出现，关系数据库都将存在。原因很简单，这些数据库强制执行基本的结构和约束，并提供了一个很好的声明性语言来查询数据，这是我们喜欢的 SQL！然而，规模一直是关系数据库的一个问题。21 世纪的大多数企业都承载着丰富的数据存储和存储库，并希望最大限度地利用其“大数据”来获得可操作的见解。关系数据库可能很受欢迎，但除非我们投资适当的大数据管理策略，否则它们不会很好地扩展。这包括考虑潜在的数据源、数据量、约束、模式、ETL(提取-转换-加载)、访问和查询模式等等！

Large Scale Data Science in Apache Spark: https://www.slideshare.net/databricks/largescale-data-science-in-apache-spark-20

本文将介绍在利用关系数据库的强大功能方面取得的一些优秀进展，但是是“大规模的”，使用 Apache Spark 的一些较新的组件——Spark SQL 和 DataFrames。最值得注意的是，我们将涵盖以下主题。

1.扩展关系数据库的动机和挑战

2.了解 Spark SQL 和数据框架

• 目标
• 架构和功能
• 表演

3.一个关于 Spark SQL 的真实案例研究，包含实际操作示例

因此，我们将着眼于人们努力工作的主要挑战和动机，并投入时间在 Apache Spark 中构建新的组件，以便我们可以大规模执行 SQL。我们还将了解 Spark SQL 和 DataFrames 的主要架构、接口、特性和性能基准。最后，也是最重要的一点，我们将通过利用 Spark 的 Databricks 云平台 ，使用 Spark SQL 和 DataFrames，基于【KDD 99 杯赛数据 进行入侵攻击分析的真实案例研究！

为大数据扩展关系数据库的动机和挑战

关系数据存储易于构建和查询。此外，用户和开发人员通常更喜欢用类似人类的可读语言(如 SQL)编写易于解释的声明性查询。然而，随着数据的数量和种类开始增加，关系方法无法很好地扩展以构建大数据应用程序和分析系统。以下是一些主要挑战。

• 处理不同类型和来源的数据，可以是结构化的、半结构化的和非结构化的。
• 构建进出各种数据源的 ETL 管道，这可能会导致开发大量特定的定制代码，随着时间的推移，这会增加技术债务。
• 能够执行基于传统商业智能的分析和高级分析(机器学习、统计建模等)。)后者在关系系统中执行肯定是有挑战性的。

大数据分析不是昨天才发明的东西！借助 Hadoop 和 Map-Reduce 范式，我们在这一领域取得了成功。这是强大的，但通常很慢，而且给用户提供了一个低级的、过程化的编程接口，要求人们为非常简单的数据转换编写大量代码。然而，一旦 Spark 发布，它就真正改变了大数据分析的方式，专注于内存计算、容错、高级抽象和易用性。

从那时起，Hive、Pig 和 Shark(演变为 Spark SQL)等几个框架和系统为大数据存储提供了丰富的关系接口和声明式查询机制。挑战依然存在，这些工具要么是基于关系的，要么是基于过程的，我们不可能两全其美。

然而在现实世界中，大多数数据和分析管道可能包含关系代码和过程代码的组合。因此，强迫用户选择任何一个都会使事情复杂化，并增加用户开发、构建和维护不同应用程序和系统的工作量。Apache Spark SQL 建立在前面提到的 SQL-on-Spark 的基础上，称为 Shark。Spark SQL 不是强迫用户在关系型或过程型 API 之间做出选择，而是试图让用户无缝混合这两种 API，并在大数据上执行大规模数据查询、检索和分析。

了解 Spark SQL 和数据框架

Spark SQL 本质上试图通过两个主要组件来弥合我们之前提到的两个模型之间的差距——关系模型和过程模型。

• Spark SQL 提供了一个 DataFrame API，可以在外部数据源和 Spark 内置的分布式集合上执行关系操作——大规模！
• 为了支持大数据中各种不同的数据源和算法，Spark SQL 引入了一种称为 Catalyst 的新型可扩展优化器，可以轻松添加数据源、优化规则和数据类型，以进行机器学习等高级分析。

本质上，Spark SQL 利用 Spark 的强大功能在大数据上执行大规模的分布式、健壮的内存计算。Spark SQL 提供了最先进的 SQL 性能，并且还保持了与所有现有结构和组件的兼容性，这些结构和组件由 Apache Hive (一个流行的大数据仓库框架)支持，包括数据格式、用户定义函数(UDF)和 metastore。除此之外，它还有助于从大数据源和企业数据仓库(如 JSON、Hive、Parquet 等)获取各种数据格式，并执行关系和过程操作的组合，以进行更复杂的高级分析。

目标

让我们来看看一些关于 Spark SQL 的有趣事实，它的用法、采用和目标，其中一些我将再次无耻地从 Spark 中关于 关系数据处理的优秀原创论文中复制。Spark SQL 于 2014 年 5 月首次发布，现在可能是 Spark 中开发最活跃的组件之一。Apache Spark 绝对是最活跃的大数据处理开源项目，有数百名贡献者。除了只是一个开源项目，Spark SQL 实际上已经开始被主流行业采用！它已经被部署在非常大规模的环境中。脸书提到了一个很好的案例研究，他们谈到了【Apache Spark @ Scale:一个 60 TB 以上的生产用例】**—**在这里，他们正在为实体排名做数据准备，他们的 Hive 工作过去需要几天时间并面临许多挑战，但他们能够使用 Spark 成功地扩展和提高性能。一定要看看他们在这次旅程中面临的有趣挑战！

[## Apache Spark @Scale:一个 60 TB 以上的生产用例——脸书代码

脸书经常使用数据驱动的决策分析。在过去几年中，用户和产品的增长…

code.fb.com](https://code.fb.com/core-data/apache-spark-scale-a-60-tb-production-use-case/)

另一个有趣的事实是，Databricks Cloud(运行 Spark 的托管服务)的 2/3 客户在其他编程语言中使用 Spark SQL。在本文中，我们还将展示一个在数据块上使用 Spark SQL 的实际案例研究。敬请关注！Spark SQL 的创建者定义的主要目标如下。

1. 使用一个程序员友好的 API 支持 Spark 程序(在本地 rdd 上)和外部数据源中的关系处理。
2. 使用已建立的 DBMS 技术提供高性能。
3. 轻松支持新的数据源，包括半结构化数据和服从查询联合的外部数据库。
4. 借助图形处理和机器学习等高级分析算法实现扩展。

架构和功能

我们现在来看看 Spark SQL 和 DataFrames 的关键特性和架构。这里需要记住的一些关键概念是围绕着 Spark 生态系统的，这个生态系统一直在不断发展。

RDDs 或“弹性分布式数据集”也许是 Spark 所有成功故事背后的最大贡献者。它基本上是一种数据结构，或者更准确地说，是一种分布式内存抽象，允许程序员在大型分布式集群上执行内存计算，同时保留容错等方面。您还可以并行处理大量计算和转换，并跟踪整个转换过程，这有助于高效地重新计算丢失的数据。Spark 爱好者确实读过关于 RDDs 的优秀论文， 【弹性分布式数据集:内存集群计算的容错抽象】 *。*此外，Spark 还使用了驱动程序和工人的概念，如下图所示。

您通常可以通过从文件、数据库读入数据、并行化现有集合甚至转换来创建 RDD。通常情况下,转换是根据我们处理数据的方式将数据转换成不同方面和维度的操作。它们也被延迟评估，这意味着即使你定义了一个转换，结果也不会被计算，直到你应用了一个动作，这通常需要一个结果被返回到驱动程序(然后它计算了所有应用的转换！).

向数据科学家同行和朋友 Favio Vázquez 和他关于使用 Apache Spark 进行深度学习的优秀 文章大声疾呼 从中我获得了一些优秀的想法和内容，包括上图。一定要去看看！

[## 使用 Apache Spark 进行深度学习—第 1 部分

第一部分全面讨论了如何使用 Apache Spark 进行分布式深度学习。这一部分:什么是火花…

towardsdatascience.com](/deep-learning-with-apache-spark-part-1-6d397c16abd)

现在我们已经了解了 Spark 工作的一般架构，让我们更深入地了解一下 Spark SQL。通常， Spark SQL 作为一个库运行在 Spark 之上，正如我们在覆盖 Spark 生态系统的图中所看到的。下图更详细地展示了 Spark SQL 的典型架构和接口。

该图清楚地向我们展示了各种 SQL 接口，可以通过 JDBC/ODBC 或命令行控制台访问，以及集成到 Spark 支持的编程语言中的 DataFrame API(我们将使用 Python！).DataFrame API 非常强大，允许用户 最后 混合程序和关系代码！像 UDF(用户定义函数)这样的高级函数也可以在 SQL 中公开，供 BI 工具使用。

Spark 数据框架非常有趣，可以帮助我们利用 Spark SQL 的强大功能，并根据需要组合其过程范例。Spark 数据帧基本上是具有相同模式的行(行类型)的分布式集合。它基本上是将 Spark 数据集 组织成命名列。这里要注意的一点是， 数据集 ，是 DataFrame API 的扩展，提供了一个*类型安全、面向对象的编程接口。*因此，它们只在 Java 和 Scala 中可用，因此我们将关注数据帧。

Source: PySpark-Pictures — Jeffrey Thompson

DataFrame 相当于关系数据库中的一个表(但是有更多的优化)，也可以用类似于 Spark (RDDs)中“本地”分布式集合的方式进行操作。Spark 数据帧有一些有趣的属性，下面会提到其中一些。

1. 与 rdd 不同，数据帧通常跟踪它们的模式，并支持各种关系操作，从而实现更优化的执行。
2. 数据帧可以从表中构造，就像您的大数据基础架构中现有的 Hive 表一样，甚至可以从现有的 rdd 中构造。
3. 数据帧可以通过直接的 SQL 查询进行操作，也可以使用数据帧 DSL(特定于域的语言),其中我们可以使用各种关系运算符和转换器，比如 where 和 groupBy
4. 此外，每个数据帧也可以被视为行对象的 RDD，允许用户调用过程化 Spark APIs，如 map
5. 最后，给定但要记住的一点是，与传统的数据帧 API(pandas)不同，Spark 数据帧是懒惰的，因为每个数据帧对象代表一个计算数据集的逻辑计划，但在用户调用特殊的“输出操作”如 save 之前不会执行。

这将使你对 Spark SQL、数据框架、基本特性、概念、架构和接口有足够的了解。让我们通过查看性能基准来结束这一部分。

表演

发布一个没有正确优化的新特性可能是致命的，而构建 Spark 的人做了大量的性能测试和基准测试！让我们来看看一些有趣的结果。展示一些结果的第一张图如下所示。

Performance of Shark, Impala and Spark SQL on Big Data benchmark queries

在这些实验中，他们使用 AMPLab 大数据基准测试比较了 Spark SQL 与 Shark 和 Impala 的性能，该基准测试使用了 Pavlo 等人开发的 web 分析工作负载。该基准测试包含四种类型的查询，使用不同的参数执行扫描、聚合、连接和基于 UDF 的 MapReduce 作业。使用柱状拼花格式压缩后，数据集的数据量为 110 GB。我们看到，在所有查询中，Spark SQL 都比 Shark 快得多，总体上与 Impala 相当。Catalyst optimizer 负责这一点，这减少了 CPU 开销(我们将简要介绍这一点)。这个特性使得 Spark SQL 在许多查询中与基于 C++和 LLVM 的 Impala 引擎竞争。与 Impala 最大的差距是在**query 3a**中，Impala 选择了一个更好的连接计划，因为查询的选择性使得其中一个表非常小。

下图显示了数据帧和常规 Spark APIs 以及 Spark + SQL 的更多性能基准。

Spark DataFrames vs RDDs and SQL

最后，下图显示了不同语言中数据帧与 rdd 的一个很好的基准测试结果，这从一个有趣的角度展示了数据帧可以优化到什么程度！

Comparing Spark DataFrames and RDDs

性能秘诀——催化剂优化器

为什么 Spark SQL 这么快，这么优化？原因是，基于 Scala 中的函数式编程结构，有了新的可扩展优化器 Catalyst。虽然我们不会在这里详细讨论 Catalyst，但它值得一提，因为它有助于优化数据帧操作和查询。

Catalyst 的可扩展设计有两个目的。

• 使向 Spark SQL 添加新的优化技术和功能变得容易，特别是解决围绕“大数据”、半结构化数据和高级分析的各种问题。
• 能够轻松扩展优化器—例如，通过添加特定于数据源的规则，可以将过滤或聚合推到外部存储系统中，或者支持新的数据类型。

Catalyst 支持基于规则和基于成本的优化。虽然在过去已经提出了可扩展的优化器，但是它们通常需要复杂的特定于领域的语言来指定规则。通常，这导致了很长的学习曲线和维护负担。相比之下，Catalyst 使用 Scala 编程语言的标准特性，例如模式匹配，让开发人员可以使用完整的编程语言，同时仍然可以轻松地指定规则。

Catalyst 的核心包含一个通用库，用于表示树并应用规则来操作它们。在这个框架之上，它有特定于关系查询处理的库(例如，表达式、逻辑查询计划)，以及处理查询执行的不同阶段的几组规则:分析、逻辑优化、物理规划，以及将部分查询编译成 Java 字节码的代码生成。有兴趣了解关于 Catalyst 的更多细节并深入了解吗？你可以从 Databricks 查看 的一篇优秀文章！

[## 深入了解 Spark SQL 的 Catalyst 优化器

Spark SQL 是 Spark 最新的、技术含量最高的组件之一。它支持 SQL 查询和…

databricks.com](https://databricks.com/blog/2015/04/13/deep-dive-into-spark-sqls-catalyst-optimizer.html)

Spark SQL 实践案例研究

我们现在将基于真实世界的数据集做一个简单的教程，看看如何使用 Spark SQL。我们将使用 Spark 数据框架，但重点将更多地放在使用 SQL 上。我将在另一篇文章中详细讨论 Spark 数据帧和常见操作。我喜欢使用云服务来满足我的机器学习、深度学习甚至大数据分析需求。与其痛苦地建立自己的 Spark 集群，不如使用云中最好的一个！我们将使用 Databricks 平台来满足我们的 Spark 需求！Databricks 是由 Apache Spark 的创始人创办的一家公司，旨在帮助客户使用 Spark 进行基于云的大数据处理。

最简单的方法(也是免费的)是去 试用数据块页面 和 注册 使用 社区版 的账户，在那里你可以获得一个基于云的集群，这是一个单节点集群，有 6 GB 和无限的笔记本，对于一个免费版本来说还不错！如果您有分析大数据的迫切需求，我绝对推荐您使用 Databricks 平台！

现在让我们开始我们的案例研究，如下面的快照所示，您可以在 Databricks 的主屏幕或自己的 Spark 集群中随意创建一个新笔记本。

你也可以导入我的笔记本，里面有整个教程，但是一定要运行每一个单元，玩它，探索它，而不只是通读它。不确定如何在数据块上使用 Spark？跟随 这篇简短却有用的教程 今天就开始吧！

[## Apache Spark 教程:Apache Spark 入门教程

Apache Spark 是一个强大的开源处理引擎，围绕速度、易用性和复杂的分析而构建…

databricks.com](https://databricks.com/spark/getting-started-with-apache-spark)

本教程将使您熟悉基本的 Spark 功能，以处理经常从数据库或平面文件中获得的结构化数据。我们将通过 Spark 利用 DataFrames 和 SQL 的概念来探索查询和聚合关系数据的典型方法。我们将处理来自 1999 年 KDD 杯的一个有趣的数据集，并尝试使用 dataframe 之类的高级抽象来查询数据，data frame 已经在 R 和 Python 之类的流行数据分析工具中获得成功。我们还将了解使用您已经学习过的 SQL 语言构建数据查询有多容易，并从我们的数据中检索有见地的信息。由于 Spark 在后端高效地分发这些数据结构，这使得我们的查询尽可能地可伸缩和高效，因此这也可以大规模发生，而无需我们做更多的工作。我们首先加载一些基本的依赖项。

**import pandas as pd
import matplotlib.pyplot as plt
plt.style.use('fivethirtyeight')**

资料检索

我们将使用来自 1999 年 KDD 杯的数据，这是用于第三届国际知识发现和数据挖掘工具竞赛的数据集，该竞赛与 KDD-99 第五届知识发现和数据挖掘国际会议同时举行。竞赛任务是建立一个网络入侵检测器，一个能够区分不良连接(称为入侵或攻击)和 良好、正常连接 的预测模型。该数据库包含一组要审计的标准数据，其中包括在军事网络环境中模拟的各种入侵。**

我们将使用包含近 50 万个网络交互的精简数据集**kddcup.data_10_percent.gz**,因为我们将从 web 本地下载这个 Gzip 文件，然后对其进行处理。如果您有一个良好、稳定的互联网连接，请随意下载并使用作为**kddcup.data.gz** 的完整数据集。

使用来自 web 的数据

在数据块中处理从网上检索的数据集可能有点棘手。幸运的是，我们有一些优秀的实用程序包，比如**dbutils**，可以帮助我们简化工作。让我们快速看一下本模块的一些基本功能。

****dbutils.help()** This module provides various utilities for users to interact with the rest of Databricks.**fs: DbfsUtils** -> Manipulates the Databricks filesystem (DBFS) from the console
**meta: MetaUtils** -> Methods to hook into the compiler (EXPERIMENTAL)
**notebook: NotebookUtils** -> Utilities for the control flow of a notebook (EXPERIMENTAL)
**preview: Preview** -> Utilities under preview category
**secrets: SecretUtils** -> Provides utilities for leveraging secrets within notebooks
**widgets: WidgetsUtils** -> Methods to create and get bound value of input widgets inside notebooks**

在数据块中检索和存储数据

我们现在将利用 python **urllib**库从他们的 web 存储库中提取 KDD 杯 99 数据，将其存储在一个临时位置，然后将其移动到 Databricks 文件系统，这样可以方便地访问这些数据进行分析

****注意:如果你跳过这一步直接下载数据，你可能会得到一个**InvalidInputException: Input path does not exist**错误

构建 KDD 数据集

现在，我们已经将数据存储在 Databricks 文件系统中，让我们将数据从磁盘加载到 Spark 的传统抽象数据结构中，即弹性分布式数据集(RDD)

您还可以使用下面的代码来验证我们的数据(RDD)的数据结构的类型。

****type(raw_rdd)****

在我们的数据上建立一个火花数据框架

Spark 数据帧是一种有趣的数据结构，表示分布式数据集合。通常，Spark 中所有 SQL 功能的入口点是**SQLContext**类。为了创建这个调用的一个基本实例，我们只需要一个**SparkContext**引用。在 Databricks 中，这个全局上下文对象作为**sc**可用于此目的。

分割 CSV 数据

RDD 中的每个条目都是逗号分隔的数据行，在解析和构建数据帧之前，我们首先需要对其进行拆分。

检查特征(列)的总数

我们可以使用下面的代码来检查数据集中潜在列的总数。

****len(csv_rdd.take(1)[0])****Out[57]: 42****

数据理解和解析

KDD 99 杯赛数据由从连接数据中捕获的不同属性组成。数据中属性的完整列表可在此处获得，有关每个属性\列描述的更多细节可在此处找到。我们将只使用数据集中的一些特定列，其细节如下所述。

我们将根据它们在每个数据点(行)中的位置提取下面的列，并构建一个新的 RDD，如下所示。

构建数据框架

现在我们的数据已经被干净利落地解析和格式化了，让我们构建我们的数据框架吧！

您现在还可以使用下面的代码来检查我们的 dataframe 的模式。

*****df.printSchema()*****

构建临时表

我们可以利用registerTempTable()函数构建一个临时表，在我们的数据帧上大规模运行 SQL 命令！需要记住的一点是，这个临时表的生命周期是与会话联系在一起的。它创建一个内存中的表，该表的作用域是创建它的集群。数据使用 Hive 高度优化的内存列格式存储。

您还可以查看saveAsTable(),它使用 Parquet 格式创建了一个永久的物理表，存储在 S3。所有集群都可以访问该表。包括文件位置的表元数据存储在 Hive metastore 中。

*****help(df.registerTempTable)*****

*****df.registerTempTable("connections")*****

大规模执行 SQL

让我们看几个例子，看看如何基于数据帧在表上运行 SQL 查询。在本教程中，我们将从一些简单的查询开始，然后查看聚合、过滤器、排序、子查询和透视。

基于协议类型的连接

让我们看看如何根据连接协议的类型获得连接总数。首先，我们将使用正常的 DataFrame DSL 语法来执行聚合，从而获得这些信息。

我们是否也可以使用 SQL 来执行相同的聚合？是的，我们可以利用我们之前为此构建的表！

你可以清楚地看到，你得到相同的结果，你不需要担心你的后台基础设施或代码是如何执行的。只写简单的 SQL！

基于好坏(攻击类型)签名的连接

我们现在将运行一个简单的聚合来检查基于良好(正常)或不良(入侵攻击)类型的连接总数。

我们有许多不同的攻击类型。我们可以用条形图的形式把它形象化。最简单的方法就是使用 Databricks 笔记本本身的优秀界面选项！

这给了我们以下好看的条形图！您可以根据需要点击**Plot Options**进行进一步定制。

另一种方法是自己写代码来做。您可以将聚合数据提取为 pandas DataFrame，然后将其绘制为常规条形图。

基于协议和攻击的连接

让我们根据下面的 SQL 查询来看看现在哪些协议最容易受到攻击。

嗯，看起来 ICMP 连接和 TCP 连接受到的攻击最多！

基于协议和攻击的连接统计

让我们看一下与这些协议和对我们的连接请求的攻击相关的一些统计测量。

看起来 TCP 请求中传输的平均数据量要高得多，这并不奇怪。有趣的是，攻击从源传输到目的地的平均数据负载要高得多。

根据 TCP 协议按服务和攻击类型过滤连接统计信息

让我们更仔细地看看 TCP 攻击，因为我们有更多相关的数据和统计数据。我们现在将根据服务、攻击类型汇总不同类型的 TCP 攻击，并观察不同的指标。

有许多攻击类型，前面的输出显示了其中的一个特定部分。

根据 TCP 协议按服务和攻击类型过滤连接统计信息

现在，我们将根据查询中的持续时间、文件创建和根用户访问施加一些限制，来过滤一些攻击类型。

有趣的是， 多跳攻击 能够获得对目的主机的根访问权限！

基于服务过滤 TCP 攻击类型的子查询

让我们尝试基于服务和攻击类型获取所有 TCP 攻击，使得这些攻击的总体平均持续时间大于零(**> 0**)。为此，我们可以使用所有聚合统计信息进行内部查询，然后提取相关查询，并在外部查询中应用平均持续时间过滤器，如下所示。

这真好！现在，查看这些数据的一种有趣方式是使用一个数据透视表，其中一个属性表示行，另一个属性表示列。让我们看看是否可以利用 Spark 数据帧来做到这一点！

从聚合数据构建数据透视表

在这里，我们将基于之前获得的 DataFrame 对象，基于类型和服务来聚合攻击。为此，我们可以利用 Spark 数据帧和数据帧 DSL 的强大功能。

我们得到了一个漂亮整洁的数据透视表，显示了基于服务和攻击类型的所有事件！

后续步骤

我鼓励你出去玩玩 Spark SQL 和 DataFrames，你甚至可以 导入我的笔记本 在你自己的账户里自己玩。

关于本文中使用的所有代码和笔记本，请随意参考我的 GitHub 资源库 。我们在这里没有涉及的内容包括以下内容。

• 连接
• 窗口功能
• Spark 数据帧的详细操作和转换

网上有很多文章/教程，所以我建议你去看看。您可以查看一些有用的资源，包括来自 Databricks 的 Spark SQL 完整指南。

**[## SQL 指南-数据块文档

查看 Azure 数据块文档 Azure 文档

docs.databricks.com](https://docs.databricks.com/spark/latest/spark-sql/index.html)**

考虑使用 JSON 数据，但不确定是否使用 Spark SQL。他们支持！查看这篇关于 Spark SQL 中的 JSON 支持的优秀指南。

** [## Spark SQL 中的 JSON 支持简介

在这篇博客文章中，我们介绍了 Spark SQL 的 JSON 支持，这是我们在 Databricks 上一直致力于开发的一项功能，旨在使它…

databricks.com](https://databricks.com/blog/2015/02/02/an-introduction-to-json-support-in-spark-sql.html)

对 SQL 中的窗口函数和秩等高级概念感兴趣？看看这篇关于 Spark SQL 中 窗口函数的精彩文章

[## Spark SQL 中的窗口函数介绍

在这篇博文中，我们介绍了 Apache Spark 1.4 中添加的新窗口函数特性。窗口功能…

databricks.com](https://databricks.com/blog/2015/07/15/introducing-window-functions-in-spark-sql.html)

我还将写一篇后续文章，以直观的方式介绍其中的一些概念，这对你来说应该很容易理解。敬请期待！

你可以通过 这个链接 直接进入我在 Databricks 上的笔记本，然后直接导入并摆弄它

[## 大规模使用 SQL-Spark SQL 教程-数据块

迪潘然(DJ)萨卡尔

databricks-prod-cloudfront.cloud.databricks.com](https://databricks-prod-cloudfront.cloud.databricks.com/public/4027ec902e239c93eaaa8714f173bcfc/3137082781873852/3704545280501166/1264763342038607/latest.html)

本教程的所有代码和资源都可以在 我的 GitHub上找到

[## 全民数据科学

我的博客和文章的代码和资源，与大家分享数据科学和人工智能的知识和学习…

github.com](https://github.com/dipanjanS/data_science_for_all/tree/master/tds_spark_sql_intro)

你也可以访问我的教程作为一个 Jupyter 笔记本 以防你想离线使用。

[## Jupyter 笔记本浏览器

Spark SQL 为 Spark 带来了对 SQL 的原生支持，并简化了查询存储在 RDDs

nbviewer.jupyter.org](http://nbviewer.jupyter.org/github/dipanjanS/data_science_for_all/blob/master/tds_spark_sql_intro/Working%20with%20SQL%20at%20Scale%20-%20Spark%20SQL%20Tutorial.ipynb)

有反馈给我吗？或者有兴趣和我一起从事研究、数据科学、人工智能甚至发表一篇关于TDS的文章？你可以在LinkedIn联系我。

[## Dipanjan Sarkar —数据科学家—英特尔公司| LinkedIn

查看 Dipanjan Sarkar 在世界最大的职业社区 LinkedIn 上的个人资料。Dipanjan 有 6 份工作列在…

www.linkedin.com](https://www.linkedin.com/in/dipanzan/)

感谢 Durba 编辑此文。**

面试的 SQL 摘要

原文：https://towardsdatascience.com/sql-cheat-sheet-for-interviews-6e5981fa797b?source=collection_archive---------0-----------------------

用例子和样本代码 DIY

SQL 或结构化查询语言是一种设计用于管理关系数据库管理系统(RDBMS) 中数据的语言。在本文中，我将带您了解每个程序员都应该知道的常用 SQL 命令。如果你需要为面试而温习 SQL 知识，这篇文章是完美的。你所要做的就是尝试给出的例子，并刷新你很久以前在数据库系统课上所学的内容。😉

**注意:**一些数据库系统要求在每条语句的末尾插入一个分号。分号是指示每个 SQL 语句结尾的标准方式。我将使用 MySQL 作为例子，它要求在每个语句的末尾有一个分号。

设置示例数据库

示例数据库将用于演示每个命令。您可以通过单击链接找到两个 SQL 脚本 DLL.sql 和 InsertStatements.sql 。

将文件保存到本地机器后，打开终端，使用以下命令登录到 MySQL 控制台。(我假设你已经安装了 MySQL。)

mysql -u root -p

然后会提示您输入密码。

现在执行以下命令。我们将把我们的数据库命名为“ 大学 ”。

**CREATE DATABASE** university;**USE** university;**SOURCE** <*path_of_DLL.sql_file>;***SOURCE** <*path_of_InsertStatements.sql_file>;*

Figure 1: Executed queries to setup the database

现在让我们开始刷新我们以前学过的 SQL 命令。

数据库相关命令

1.查看当前可用的数据库

**SHOW DATABASES**;

2.创建新的数据库

**CREATE DATABASE** <*database_name*>;

3.选择要使用的数据库

**USE** <*database_name*>;

4.从导入 SQL 命令。sql 文件

**SOURCE** <*path_of_.sql_file>;*

5.删除数据库

**DROP DATABASE** <*database_name*>;

与表格相关的命令

6.查看数据库中当前可用的表

**SHOW TABLES**;

Figure 2: Tables in university database

7.创建新表格

**CREATE TABLE** <*table_name1*> (
    <*col_name1*> <*col_type1*>, 
    <*col_name2*> <*col_type2*>, 
    <*col_name3*> <*col_type3*>
    **PRIMARY KEY** (<*col_name1*>),
   ** FOREIGN KEY** (<*col_name2*>) **REFERENCES** <*table_name2*>(<*col_name2*>)
);

完整性约束创建表

您可能需要为表中的某些列设置约束。创建表时可以施加以下约束。

• 不为空
• 主键 ( 列名 1，列名 2，… )
• 外键 ( col_namex1 ，…， col_namexn ) 引用 表名(col_namex1 ，…， col_namexn)

您可以包含多个主键，这将创建一个复合或串联 主键。

例子

创建表格讲师。

CREATE TABLE instructor (
    ID CHAR(5),
    name VARCHAR(20) NOT NULL,
    dept_name VARCHAR(20),
    salary NUMERIC(8,2), 
    PRIMARY KEY (ID),
    FOREIGN KEY (dept_name) REFERENCES department(dept_name))

8.描述表格的列

您可以使用下面给出的命令来查看表的列，包括类型和键等详细信息。图 3 显示了几个例子。

**DESCRIBE** <*table_name*>;

Figure 3: Details of table columns

9.插入表中

**INSERT INTO** <*table_name>* (<*col_name1>*, *<col_name2>*, *<col_name3>*, …)
    **VALUES** (<*value1>*, *<value2>*, *<value3>*, …);

如果要将值插入到表的所有列中，则不需要在开头指定列名。

**INSERT INTO** <*table_name>*
    **VALUES** (<*value1>*, *<value2>*, *<value3>*, …);

10.更新表格

**UPDATE** <*table_name>*
    **SET** <*col_name1>* = *<value1>*, *<col_name2>* = *<value2>*, ...
    **WHERE** <*condition>*;

11.删除表格的所有内容

**DELETE FROM** <*table_name*>;

12.删除表格

**DROP TABLE** <*table_name*>;

查询相关命令

13.挑选

SELECT 语句用于从特定的表中选择数据。

**SELECT** <*col_name1>*, *<col_name2>, …*
     **FROM** <*table_name>*;

您可以选择表格的所有内容，如下所示。也参考图 4。

**SELECT * FROM** <*table_name*>;

Figure 4: Select all from department and course tables

14.选择不同

表中的一列可能经常包含重复值。选择不同的允许您检索不同的值。

**SELECT DISTINCT** <*col_name1>*, *<col_name2>, …*
     **FROM** <*table_name>*;

Figure 5: SELECT DISTINCT example

15.在哪里

您可以在 SELECT 语句中使用 WHERE 关键字，以便为您的数据包含条件。

**SELECT** <*col_name1>*, *<col_name2>, …*
     **FROM** <*table_name>
*     **WHERE** <*condition*>;

您可以包含以下条件:

• 比较文本
• 数字的比较
• 逻辑运算包括与、或与非。

例子

浏览下面的示例语句。请注意条件是如何包含在 WHERE 子句中的。

SELECT * FROM course WHERE dept_name=’Comp. Sci.’;
SELECT * FROM course WHERE credits>3;
SELECT * FROM course WHERE dept_name='Comp. Sci.' AND credits>3;

Figure 6: Usage of WHERE in SELECT

16.分组依据

GROUP BY 语句通常与聚合函数一起使用，如 COUNT 、 MAX 、 MIN 、 SUM 和 AVG 对结果集进行分组。

**SELECT** <*col_name1>*, *<col_name2>, …*
     **FROM** <*table_name>*
     **GROUP BY** <*col_namex>*;

示例

列出每个系的课程数量。

SELECT COUNT(course_id), dept_name 
     FROM course 
     GROUP BY dept_name;

Figure 7: Number of courses for each department

17.拥有

具有子句的被引入 SQL，因为 WHERE 关键字不能用于比较聚合函数的值。

**SELECT** <*col_name1>,* <*col_name2>, ...*
    **FROM** <*table_name>*
    **GROUP BY** <*column_namex>* **HAVING** <*condition>*

示例

列出提供一门以上课程的每个系的课程数量。

SELECT COUNT(course_id), dept_name 
    FROM course 
    GROUP BY dept_name 
    HAVING COUNT(course_id)>1;

Figure 8: Departments offering more than one course

18。排序依据

ORDER BY 用于对结果集进行升序或降序排序。如果不指定 ASC 或 DESC，ORDER BY 将按升序排序。

**SELECT** <*col_name1>*,<*col_name2>, …*
**FROM** <*table_name>*
**ORDER BY** <*col_name1>,* <*col_name2>, …* **ASC**|**DESC**;

示例

按照学分的升序和降序列出课程。

SELECT * FROM course ORDER BY credits;
SELECT * FROM course ORDER BY credits DESC;

Figure 9: Courses sorted according to credits

19.在…之间

与之间的子句用于选择给定范围内的数据。这些值可以是数字、文本甚至日期。

**SELECT** <*col_name1>*,<*col_name2>, …*
    **FROM** <*table_name>*
    **WHERE** <*col_namex>* **BETWEEN** <*value1>* **AND** <*value2>;*

例子

列出薪水在 50 000 到 100 000 之间的教师。

SELECT * FROM instructor 
    WHERE salary BETWEEN 50000 AND 100000;

Figure 10: Instructors who get a salary in between 50 000 and 100 000

20.喜欢

在 WHERE 子句中使用 LIKE 运算符来搜索文本中的指定模式。

LIKE 使用了两个通配符运算符。

• % (零个、一个或多个字符)
• _ (单个字符)

**SELECT** <*col_name1>,* <*col_name2>, …*
    **FROM** <*table_name>*
    **WHERE** <*col_namex>* **LIKE** <*pattern>*;

例子

列出课程名称包含“to”的课程以及课程 id 以“CS-”开头的课程。

SELECT * FROM course WHERE title LIKE ‘%to%’;
SELECT * FROM course WHERE course_id LIKE 'CS-___';

Figure 11: Use of wildcard operators with LIKE

21.在…里

在子句中使用**，可以允许在一个 WHERE 子句中有多个值。**

**SELECT** <*col_name1>,* <*col_name2>, …*
    **FROM** <*table_name>*
    **WHERE** <*col_namen>* **IN** (<*value1>*, *<value2>*, …);

例子

列出计算机系的学生。Sci。、物理和电子。英语。

SELECT * FROM student 
    WHERE dept_name IN (‘Comp. Sci.’, ‘Physics’, ‘Elec. Eng.’);

Figure 12: Students in the departments of Comp. Sci., Physics, and Elec. Eng

22.加入

JOIN 用于根据两个或多个表中的公共属性合并它们的值。下面的图 13 描述了不同类型的 SQL 连接。注意左外接头和右外接头的区别。

Figure 13: Types of joins (Source: http://files.zeroturnaround.com/pdf/zt_sql_cheat_sheet.pdf)

**SELECT** <*col_name1>,* <*col_name2>, …*
    **FROM** <*table_name1>*
    **JOIN** <*table_name2>* **ON** <*table_name1.col_namex>* = *<table2.col_namex>*;

示例 1

选择所有包含相关部门详细信息的课程。

SELECT * FROM course 
    **JOIN** department 
    ON course.dept_name=department.dept_name;

Figure 14: All the courses with department details

示例 2

选择所有先修课程及其课程详细信息。

SELECT prereq.course_id, title, dept_name, credits, prereq_id 
    FROM prereq 
   ** LEFT OUTER JOIN** course 
    ON prereq.course_id=course.course_id;

Figure 15: All the prerequisite courses with their course details

示例 3

选择所有课程及其课程详细信息，无论它们是否有先决条件。

SELECT course.course_id, title, dept_name, credits, prereq_id 
    FROM prereq 
    **RIGHT OUTER JOIN** course 
    ON prereq.course_id=course.course_id;

Figure 16: All the courses with their course details, regardless of whether or not they have prerequisites

23.视图

视图是使用语句的结果集创建的虚拟 SQL 表。它包含行和列，非常类似于一般的 SQL 表。视图总是显示数据库中的最新数据。

创建视图

**CREATE VIEW** <*view_name*> **AS**
    **SELECT** <*col_name1>*, <*col_name2>*, …
    **FROM** <*table_name*>
    **WHERE** <*condition*>;

删除视图

**DROP VIEW** <*view_name*>;

例子

创建由 3 个学分的课程组成的视图。

Figure 17: A view consisting of courses with 3 credits

24.聚合函数

这些函数用于获得与正在考虑的数据相关的累积结果。以下是常用的聚合函数。

• COUNT(col_name) —返回行数
• SUM(col_name) —返回给定列中值的总和
• AVG(列名) —返回给定列的平均值
• MIN(col_name) —返回给定列的最小值
• MAX(col_name) —返回给定列的最大值

25.嵌套子查询

嵌套子查询是包含嵌套在另一个查询中的 SELECT-FROM-WHERE 表达式的 SQL 查询。

例子

查找 2009 年秋季和 2010 年春季提供的课程。

**SELECT DISTINCT** course_id 
    **FROM** section 
    **WHERE** semester = ‘Fall’ **AND** year= 2009 **AND** course_id **IN** (
        **SELECT** course_id 
            **FROM** section 
            **WHERE** semester = ‘Spring’ **AND** year= 2010
    );

Figure 18: Courses offered in Fall 2009 and in Spring 2010

希望这篇文章对你有用。

感谢大家的阅读！😃

祝你面试好运！