TowardsDataScience 博客中文翻译 2021（六百一十）

原文：TowardsDataScience Blog

协议：CC BY-NC-SA 4.0

Spotify 案例研究:制作热门歌曲有秘诀吗？

原文：https://towardsdatascience.com/spotify-case-study-is-there-a-secret-to-producing-hit-songs-aab8c2dc64c1?source=collection_archive---------15-----------------------

照片由 Yomex Owo 在 Unsplash 上拍摄

我可以用我的车库之歌让自己成为明星吗？

几个月后，我参加了一个数据科学项目，并为
的职业转型做好了准备，我发现要想在这个领域取得真正的成功
，应该将获得的知识应用到项目中。这些
知识可以回答任何关于分析数据的问题。

然而，人们可能会遇到几个新问题:这个问题是否符合我目前的技能组合，或者它是否过于雄心勃勃？一个人应该从哪里开始？这个问题有意义吗？

在这里，我提出了一个对初学者更友好的方法:回答一个已经回答过的问题，但加入自己的个人风格。

我将带你了解我在 Spotify 音乐数据集上的第一个探索性数据分析(EDA)项目。这将有助于提供更多的背景。

你可以在这里找到这个数据集(tracks . CSV)。

第 1 部分:数据操作和清理

首先，我们必须导入这个项目所需的库:

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import plotly.express as px
import datetime
import calendar

接下来，让我们阅读我们的数据框架，看看第一个样本:

df = pd.read_csv('../Spotify Tracks.csv')df.sample(10)

现在，我们已经看到了数据框架中不同类型变量的列，让我们看看它的数据类型，看看是否需要一些争论:

df.info()

有趣的是，似乎发布日期列被设置为一个字符串。我们最好把它定为一个日期，以确保更顺利的分析。让我们也为音轨的发布年份和月份创建一个新的列。

为此，我们将使用PD . to _ datetime()**函数将该列设置为日期，并使用。 dt 法提取其年月。我们还将使用一个 lambda 函数来为日历库中的每个月指定相应的名称:

*df['release_date'] = pd.to_datetime(df['release_date'])
df['release_year'] = df['release_date'].dt.year
df['release_month'] = df['release_date'].dt.month
df['release_month'] = df['release_month'].apply(lambda x : calendar.month_name[x])*

让我们再看一看:

*df.info()*

这个看起来没错！但是看起来 id 和 id_artists 对我们的分析没什么用。所以我们要用。下降方法:

***columns_to_drop = ['id','id_artists']
df.drop(columns = columns_to_drop, inplace = True)df.sample(5)***

好了，现在我们有了正确的列，在开始有趣的东西之前，让我们做最后一件事:检查重复项。

***df.duplicated().value_counts()***

消除重复:

***df.drop_duplicates(inplace = True)

df.duplicated().value_counts()***

非常有效。现在让我们进入正题。

第二部分:数据探索和分析

好了，现在我们应该阐明驱动我们分析的主要问题:“有人能根据音轨属性数据发布一首热门歌曲吗？”****

我们将为我们的图表设置样式和调色板(如果我们正在处理 Spotify 数据，它必须是绿色的，对吗？)

***sns.set_palette('BuGn_r')
sns.set_style('darkgrid')***

让我们来看看流行歌曲的分布情况:

***plt.figure(figsize = (14,8))sns.histplot(df['popularity'], kde = True)
plt.title('Distribution of Tracks by Popularity')
plt.show()***

有趣的是，超过 45，000 首歌曲在流行的墓地里。并且大多数歌曲分布在大约 1 到 40 点的流行度之间。

看起来音乐市场现在竞争很激烈，是吧？但是一直都是这样吗？让我们来看看:

***plt.figure(figsize = (20, 8))
sns.countplot(data = df, x = 'release_year', color = '#1DB954')
plt.title('Distribution of Tracks Produced Over Time')
plt.xticks(rotation = 90)
plt.show()***

看起来它的竞争一年比一年激烈，2020 年生产了近 14 万首歌曲，这一年许多人在家里有很多空闲时间。该数据集包含截至 2021 年 4 月的歌曲数据，因此我们必须等待，看看今年会产生多少首歌曲。

现在，让我们看看数据集中的一些总体指标。我们将使用。 describe() 方法，但是添加了一些附加参数来沿着几个百分点分布数据:

**df.describe([.01,.1,.2,.3,.4,.5,.6,.7,.8,.9,.99])**

这张表中有几个发现，但真正引起我注意的是，与之前的(19 点)相比，受欢迎程度变量中的 90%和 99%之间有一个很大的飞跃。因此，似乎有几首热门歌曲的人气接近 100 分。

这意味着 Spotify 中有一组精选的歌曲非常受欢迎。通过在我们的歌曲中加入正确的和弦和节奏，有可能达到这个目标吗？让我们绘制一个相关图表来找出答案:

**#First we make a list with the track attributes we want to comparetrack_attributes = ["popularity","acousticness",
                    "danceability", 
                    "energy", 
                    "duration_ms", 
                    "instrumentalness", 
                    "valence", 
                    "tempo", 
                    "liveness", 
                    "loudness", 
                    "speechiness"] #Then we plot
plt.figure(figsize = (10,8), dpi = 80)sns.heatmap(df[track_attributes].corr(),vmin=-1, vmax=1, annot=True, cmap = 'BuGn_r' )
plt.xticks(rotation = 45)
plt.show()**

我们可以在这张热图中看到，流行度和赛道属性之间没有明显的相关性。尽管如此，深入研究显示出正相关的三个属性仍然是值得的:可跳舞性、能量和响度。****

让我们试一试:

**corr_vars = ['danceability', 'energy', 'loudness']

list(enumerate(corr_vars))**

因此，在列出了我们想要检查的变量并将其转换成一个生成器对象之后，我们从每个图表中随机抽取 500 个出现次数的样本来绘制这三个属性:

**plt.figure(figsize = (20, 15))

for i **in** enumerate(corr_vars):
    plt.subplot(1,3,i[0]+1)
    sns.regplot(data = df.sample(500), y = 'popularity', x = i[1])
plt.show()**

看起来这证实了我们最初在相关性热图中看到的情况，但揭示了一些对我们的分析非常有趣的事情:大多数高受欢迎度异常值都在三个属性的最高范围内，特别是响度。这可能是解决我们主要问题的一块巨大的垫脚石。

最好将我们的数据分成子集，以获得最流行的歌曲，这样我们就可以看到这些属性的存在程度:

**popular_songs = df[df['popularity'] >= 80]

print(popular_songs.shape)

popular_songs.sample(10)**

这里我们有 954 首歌曲，让我们通过 mean 绘制它们的属性来看看它们有什么共同点:

**#First we list the attributes we want to see reflected in the plot
labels = [ "valence", "danceability", "energy", "acousticness","instrumentalness", "liveness","speechiness"]#Then we plot those attributes by meanfig = px.line_polar(popular_songs, theta = labels, r = popular_songs[labels].mean(), line_close = True)

fig.show()**

****

这是个好消息，因为我们又发现了一个可能有助于我们创作热门歌曲的属性:化合价。

另一方面，响度是一个非常强的竞争者，因为它的值范围从-60 到 0，平均值为-6 表明最流行的歌曲往往很大声。

现在，让我们来看看随着时间的推移，这些是如何成为最受欢迎的歌曲的一部分的:

**#First we make a list of our attributes of interest
audio_attributes = ["danceability","energy","valence"]#Now we plot our charts
plt.figure(figsize = (20, 8))
sns.set_palette('Accent')
plt.subplot(2,1,1)
for attribute in audio_attributes:
    x = popular_songs.groupby("release_year")[attribute].mean()
    sns.lineplot(x=x.index,y=x,label=attribute)
    plt.title('Audio Attributes Over Time for Popular Songs')plt.subplot(2,1,2)
sns.lineplot(x = popular_songs.groupby('release_year')['loudness'].mean().index, y = popular_songs.groupby('release_year')['loudness'].mean())plt.ylabel('Range')
plt.xlabel('Year')**

看起来垃圾音乐真的让价在 90 年代下降了，是吗？

似乎在过去的 50 年里，流行歌曲总是充满活力，声音洪亮，也很欢快。这看起来像是人们音乐品味的一个明确指标。

所以，任何创作出具有这些特质的歌曲的人肯定会成为明星，对吗？

让我们来看看目前最受欢迎的 25 位歌手的歌曲，并与随机样本进行比较:

**popular_songs['artists'].value_counts().nlargest(25)popular_songs['artists'].value_counts().sample(30)**

看起来贾斯汀比伯和比莉·艾莉丝现在很受欢迎，有 11 首热门歌曲！但这并不令人惊讶，因为前 25 名名单中的所有艺术家都非常知名，样本名单中的许多艺术家也是如此。

第三部分:结论

制作一首热门歌曲不一定取决于你的歌曲有多快乐、有活力或响亮，但更有可能的是，它与你目前作为一名艺术家的受欢迎程度有关。

尽管如此，为了增加推出流行歌曲的几率，在其中添加流行属性可能是好的，就像我们在分析中看到的那样。这对新音乐人来说也是一个很好的建议，让他们真正投入时间和精力来营销他们的内容。

感谢阅读！

在 LinkedIn 上找到我

Spotify 流派分类算法

原文：https://towardsdatascience.com/spotify-genre-classification-algorithm-88051db23d42?source=collection_archive---------10-----------------------

监督机器学习— SVM、随机森林、逻辑回归

本文假设对机器学习算法和数据科学技术有基本的了解。

文章大纲:

• 监督机器学习
• 分类—多类
• 数据集-初步分析和特征选择
• 算法选择— SVM、逻辑回归、随机森林
• 模型性能—准确性得分
• 改进—超参数调整和集成学习
• 结论—更多数据！

亚历山大·沙托夫在 Unsplash 上拍摄的照片

什么是有监督的机器学习？

正如所有技术都有术语一样，监督学习是一个总括术语，用来描述机器学习的一个领域(在实践中使用最频繁)，其中使用的数据是标记为的**。监督学习算法的目标是利用数据集来产生一个模型，该模型以特征向量(x)作为输入，以变量(Y)作为输出。使用一种算法来学习从输入到输出的映射函数，然后使用新的未知输入数据来预测该数据的输出变量。**

什么是分类？

分类算法将已标记示例的数据集作为输入来产生模型，该模型可以获取未标记的新数据并自动将标签分配给未标记的示例。

如果分类问题有一组两个标签(例如“垃圾邮件”或“非垃圾邮件”)，那么它就是一个二元分类问题。当集合中的标签数量为三个或更多时，多类分类是一个问题。我们正在寻找的问题是一个多类，因为在这个集合中有许多类型。

给我看看数据

被检查的数据集是歌曲信息的集合。它可以在我的 python 代码旁边的 Kaggle 和 Github 上获得。

数据已经被分成标记的训练数据和未标记的测试数据。

按作者分类的图像(标记为训练数据)

作者提供的图片(未标记的测试数据)

• Id —任意唯一的轨道标识符
• 标题—音轨标题
• 艺术家——歌手或乐队
• 年份—发布(或重新发布)的年份
• bpm —每分钟节拍数(节奏)
• nrgy —能量:数值越高，能量越大
• dnce — danceability:值越高，越容易随着这首歌跳舞
• dB —响度(dB):值越高，歌曲的音量越大
• live-liveness:该值越高，歌曲越有可能是现场录制的
• val-valence:值越高，歌曲的情绪越积极
• dur —持续时间:歌曲的长度
• acous-acoustic:值越高，歌曲的声音越大
• spch-speechiness:值越高，歌曲包含的口语单词越多
• 流行度:值越高，歌曲越流行
• 热门流派—曲目的流派(以及此问题的目标变量)

在 top_genre 列的训练集中识别出 15 个空值。missingo 库为缺失值提供了很好的可视化效果，使得识别具有空值的列变得很容易。15 个空值被删除。

import missingno as msno
msno.bar(class_train, color="dodgerblue", sort="ascending", figsize=(10,5), fontsize=12)
class_train["top genre"].isnull().value_counts()
# dropping NULL values
class_train = class_train.dropna(axis=0)

作者图片

在创建任何模型之前，最好检查多重共线性，多重共线性是数据集中独立要素之间的相关性。检查这一点最简单的方法是使用关联热图。显然不存在多重共线性。

# Plot linear correlation matrix
fig, ax = plt.subplots(figsize=(15,10))
sns.heatmap(class_train.corr(), annot=True, cmap='YlGnBu', vmin=-1, vmax=1, center=0, ax=ax)
plt.title('LINEAR CORRELATION MATRIX - CLASS_TRAIN')
plt.show()

作者图片

pop(流行度)列可用于创建另一个列，表示一首歌曲是否受欢迎(1)或不受欢迎(2)。直方图可用于显示数据集中每个要素的“相似”分布。这是特征工程的一个例子。

conditions = [
    (class_train['pop'] >= 55),
    (class_train['pop'] < 55) ]
values = [1, 2]
class_train['like'] = np.select(conditions, values)# for all features
pos_bpm = class_train[class_train['like'] == 1]['bpm']
neg_bpm = class_train[class_train['like'] == 2]['bpm']
pos_nrgy = class_train[class_train['like'] == 1]['nrgy']
neg_nrgy = class_train[class_train['like'] == 2]['nrgy']
pos_db = class_train[class_train['like'] == 1]['dB']
neg_db = class_train[class_train['like'] == 2]['dB']
pos_live = class_train[class_train['like'] == 1]['live']
neg_live = class_train[class_train['like'] == 2]['live']
pos_dur = class_train[class_train['like'] == 1]['dur']
neg_dur = class_train[class_train['like'] == 2]['dur']
pos_acous = class_train[class_train['like'] == 1]['acous']
neg_acous = class_train[class_train['like'] == 2]['acous']
pos_spch = class_train[class_train['like'] == 1]['spch']
neg_spch = class_train[class_train['like'] == 2]['spch']
pos_val = class_train[class_train['like'] == 1]['val']
neg_val = class_train[class_train['like'] == 2]['val']
pos_dnce = class_train[class_train['like'] == 1]['dnce']
neg_dnce = class_train[class_train['like'] == 2]['dnce']fig2 = plt.figure(figsize=(20,20))
#dnce
ax3 = fig2.add_subplot(331)
ax3.set_xlabel('Danceability')
ax3.set_ylabel('Count')
ax3.set_title('Song Danceability Like Distribution')pos_dnce.hist(alpha=0.5, bins=30)
ax4 = fig2.add_subplot(331)neg_dnce.hist(alpha=0.5, bins=30)
plt.legend(['Like', 'Dislike'])#duration
ax5 = fig2.add_subplot(332)
ax5.set_xlabel('Duration')
ax5.set_ylabel('Count')
ax5.set_title('Song Duration Like Distribution')pos_dur.hist(alpha=0.5, bins=30)
ax6 = fig2.add_subplot(332)neg_dur.hist(alpha=0.5, bins=30)
plt.legend(['Like', 'Dislike'])# loudness (dB)
ax7 = fig2.add_subplot(333)
ax7.set_xlabel('Loudness -dB')
ax7.set_ylabel('Count')
ax7.set_title('Song Loudness Like Distribution')
plt.legend(['Like', 'Dislike'])pos_db.hist(alpha=0.5, bins=30)
ax8 = fig2.add_subplot(333)neg_db.hist(alpha=0.5, bins=30)
plt.legend(['Like', 'Dislike'])# energy
ax9 = fig2.add_subplot(334)
ax9.set_xlabel('Energy')
ax9.set_ylabel('Count')
ax9.set_title('Song Energy Like Distribution')pos_nrgy.hist(alpha=0.5, bins=30)
ax9 = fig2.add_subplot(334)neg_nrgy.hist(alpha=0.5, bins=30)
plt.legend(['Like', 'Dislike'])# live
ax10 = fig2.add_subplot(335)
ax10.set_xlabel('Liveness')
ax10.set_ylabel('Count')
ax10.set_title('Liveness - Like Distribution')pos_live.hist(alpha=0.5, bins=30)
ax11 = fig2.add_subplot(335)neg_live.hist(alpha=0.5, bins=30)
plt.legend(['Like', 'Dislike'])# val
ax12 = fig2.add_subplot(336)
ax12.set_xlabel('Valence')
ax12.set_ylabel('Count')
ax12.set_title('Valence (Mood?) - Like Distribution')pos_val.hist(alpha=0.5, bins=30)
ax13 = fig2.add_subplot(336)neg_val.hist(alpha=0.5, bins=30)
plt.legend(['Like', 'Dislike'])# acous
ax14 = fig2.add_subplot(337)
ax14.set_xlabel('Acousticness')
ax14.set_ylabel('Count')
ax14.set_title('Acousticness - Like Distribution')pos_acous.hist(alpha=0.5, bins=30)
ax15 = fig2.add_subplot(337)neg_acous.hist(alpha=0.5, bins=30)
plt.legend(['Like', 'Dislike'])# speech
ax16 = fig2.add_subplot(338)
ax16.set_xlabel('Speech')
ax16.set_ylabel('Count')
ax16.set_title('Speech - Like Distribution')pos_spch.hist(alpha=0.5, bins=30)
ax17 = fig2.add_subplot(338)neg_spch.hist(alpha=0.5, bins=30)
plt.legend(['Like', 'Dislike'])# bpm
ax18 = fig2.add_subplot(339)
ax18.set_xlabel('Beats Per Minute')
ax18.set_ylabel('Count')
ax18.set_title('Song BPM - Like Distribution')pos_bpm.hist(alpha=0.5, bins=30)
ax19 = fig2.add_subplot(339)neg_bpm.hist(alpha=0.5, bins=30)
plt.legend(['Like', 'Dislike'])

作者图片

然后调查歌曲在训练集中的流派分布。在训练集中，有 86 个独特的流派名称，这实在是太多了。经过进一步调查，这些流派名称中有许多只是地理上的差异，例如，“英国摇滚”和“专辑摇滚”——它们可以归入“摇滚”类别。因此，流派被进一步概括，将流派的数量从 86 个减少到 20 个。

class_train = class_train.replace({'top genre': {"album rock": "rock", "glam rock": "rock", "dance rock":"rock", "art rock":"rock",
                                  "soft rock":"rock", "country rock": "rock", "classic rock":"rock", "blues rock":"rock", "celtic rock":"rock",
                                  "australian rock":"rock", "german alternative rock":"rock", "alternative rock":"rock", "dance pop":"pop",
                                "brill building pop": "pop", "europop": "pop", "barbadian pop": "pop", "classic uk pop":"pop", 
                                "new wave pop":"pop", "canadian pop":"pop", "art pop":"pop", "belgian pop": "pop", "britpop": "pop", 
                                "italian pop":"pop", "classic danish pop": "pop", "bow pop": "pop", "baroque pop": "pop", "bubblegum pop": "pop",
                                "afropop":"pop", "hip pop":"pop", "atl hip hop": "hip hop", "east coast hip hop": "hip hop", "detroit hip hop":"hip hop", 
                                "bronx hip hop": "hip hop", "bubblegum dance": "dance", "eurodance":"dance", "belgian dance":"dance", "german dance": "dance",
                                "classic soul": "soul", "british soul": "soul", "chicago soul": "soul", "british folk": "folk", "american folk revival":"folk",
                                "drone folk":"folk","canadian folk":"folk", "deep adult standards":"adult standards", "glam metal": "metal", "alternative metal": "metal",
                                "acoustic blues":"blues", "british blues":"blues", "louisiana blues":"blues", "g funk":"funk", "brit funk":"funk",
                                "afrobeat":"dance", "british invasion":"rock", "doo-wop":"blues", "boy band":"pop", "merseybeat":"rock-and-roll", "blue":"blues",
                                                "bebop":"jazz", "avant-garde jazz":"jazz", "boogaloo": "latin", "big room": "trance", "bubble trance":"trance", "glam punk":"rock",
                                                "australian talent show":"pop", "mellow gold":"rock", "hi-nrg": "dance", "neo mellow": "pop", "yodeling":"folk", "classic girl group":"pop",
                                                "british dance band":"jazz", "deep house":"dance", "uk garage": "dance", "chicago rap":"hip hop"}})

下面的饼状图显示了前 10 个流派，只是为了保持可读性。很明显，训练数据集中的大多数歌曲属于摇滚和流行音乐类别——这种情况的影响将在结论中讨论。

# Find percent of each genre
df_genre = class_train['top genre'].value_counts()[:10].sort_values(ascending=False) / len(class_train)
sizes = df_genre.values.tolist()
labels = df_genre.index.values.tolist()# Pie chart for genre
fig1, ax1 = plt.subplots(figsize=(10,10))
ax1.pie(sizes, labels=labels, autopct='%1.1f%%', shadow=False, textprops={'fontsize': 14})
ax1.axis('equal')
plt.title("Most Popular Genres\n" + "(limited to top 10 genres)", bbox={'facecolor':'0.8', 'pad':5})
plt.show()

作者图片

然后观察特征的分布。这是因为我们希望每个特征的直方图显示正态分布。

import matplotlib.pyplot as plt
%matplotlib inline
class_train.hist(bins=20, figsize=(15,15))
plt.show()

作者图片

上面的直方图显示一些变量是偏斜的(acous，live，spch)，这种偏斜可能是异常值的原因。因此，可以通过移除潜在的异常值来纠正这种偏差。可以使用箱线图和 z 值来识别异常值。z 值大于 3 的数据点将被移除。移除异常值以减少噪声可以提高模型的性能并防止过度拟合。

import seaborn as sns
# acoustic
sns.boxplot(x=class_train['acous']) # no visible outliers
# dur
sns.boxplot(x=class_train['dur']) # OUTLIERS !!
# live
sns.boxplot(x=class_train['live']) #OUTLIERS !!
# spch
sns.boxplot(x=class_train['spch']) #OUTLIERS !!class_train = class_train[np.abs(class_train.dur-class_train.dur.mean()) <= (3*class_train.dur.std())]
# keep only the ones that are within +3 to -3 standard deviations in the column 'live'.class_train = class_train[np.abs(class_train.live-class_train.live.mean()) <= (3*class_train.live.std())]
# keep only the ones that are within +3 to -3 standard deviations in the column 'live'.class_train= df[np.abs(class_train.spch-class_train.spch.mean()) <= (3*class_train.spch.std())]
# keep only the ones that are within +3 to -3 standard deviations in the column 'spch'.

将在模型创建过程中使用的特征是第 3 列到第 13 列。被删除的功能有标题、艺术家和 Id。为模型训练选择的特征是:“年份”、“bpm”、“nrgy”、“dnce”、“dB”、“spch”、“pop”、“live”、“acous”。

然后使用 train_test_split 函数对训练集进行分离，以进一步将数据划分为 75% / 25%的分割。通过进一步分割训练数据集，我们创建了一个验证集。

y = class_train.values[:,14]
X = class_train.values[:,3:13]from sklearn.model_selection import train_test_split
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.25, random_state=42)

算法选择+模型性能+改进

这三个部分紧密交织在一起。因为所选择的算法在很大程度上取决于它与其他算法相比的表现，反过来，改进在很大程度上是一个迭代过程。

支持向量机(SVM)算法是分析的好选择，因为训练集具有少量实例和大量特征。这是合适的，因为 SVM 算法可以处理高偏差/低方差。 [OneVersusRest](https://machinelearningmastery.com/one-vs-rest-and-one-vs-one-for-multi-class-classification/#:~:text=One%2Dvs%2Drest%20(OvR%20for%20short%2C%20also%20referred,into%20multiple%20binary%20classification%20problems.) 是一种启发式方法，涉及将训练数据拆分为多个二元分类问题。如前所述，这个问题被认为是多重标签。然而，SVM 算法要求正标签的数值为+1，负标签的数值为-1。因此，使用 OneVersusRest 技术，它对每个二元分类问题训练二元分类器，并根据最有把握的模型进行预测。

from sklearn import datasets
from sklearn.preprocessing import StandardScaler
from sklearn.svm import LinearSVC
from sklearn.multiclass import OneVsRestClassifier

std_scaler = StandardScaler()
X_scaled_train = std_scaler.fit_transform(X_train)
X_scaled_train **=** std_scaler.fit_transform(X_train)X_scaled_train **=** std_scaler.fit_transform(X_train)svm_clf **=** OneVsRestClassifier(LinearSVC(C**=**1, loss **=** "hinge", random_state **=** 1))svm_clf.fit(X_scaled_train, y_train)

在构建我们的 SVM 之前进行缩放，因为它们对要素比例很敏感。这是为了防止模型中最宽的可能街道过于靠近决策边界。

超参数不是由学习算法优化的，必须由我们这些数据分析师来完成。网格搜索是最简单的超参数调整方法，可以在 scikit-learn Python 机器学习库中找到。在使用 C=1 的超参数的模型的第一次运行中，精确度非常低，为 26%。使用网格搜索将 C(算法的超参数之一)减少到 0.01。这将防止过度拟合。

SVCpipe = Pipeline([('scale', StandardScaler()),
                   ('SVC',LinearSVC())])# Gridsearch to determine the value of C
param_grid = {'SVC__C':np.arange(0.01,100,10)}
linearSVC = GridSearchCV(SVCpipe,param_grid,cv=5,return_train_score=True)
linearSVC.fit(X_train,y_train)
print(linearSVC.best_params_)svm_clf = OneVsRestClassifier(LinearSVC(C=0.01, loss = "hinge", random_state=1))

preds = svm_clf.predict(X_scaled_train)
from sklearn.metrics import classification_report
print(classification_report(y_train,preds))

作者图片

最终达到的模型精度为 46%。其中准确度是正确分类的例子的数量除以分类的例子的总数。

逻辑回归算法是一种分类算法，而不是回归算法，可用于二元和多类问题。获得的准确度分数为 50%。

from sklearn.linear_model import LogisticRegression
ovr_clf = OneVsRestClassifier(LogisticRegression(max_iter=1000, random_state=1))
ovr_clf.fit(X_train, y_train)
y_test_pred = ovr_clf.predict(X_test)from sklearn.metrics import accuracy_score
confusion_matrix(y_test, y_test_pred)
print(accuracy_score(y_test, y_test_pred))

随机森林算法使用一种改进的树学习算法来检查“学习”过程中每个分裂处的随机特征子集。这样就避免了树的相关性。随机森林减少了最终模型的方差，从而减少了过度拟合。Random Forest 取得了 46%的准确率。

from sklearn.ensemble import RandomForestClassifier
rnd_clf = RandomForestClassifier(n_estimators=25, max_leaf_nodes=16, n_jobs=-1, random_state=1)
rnd_clf.fit(X_train, y_train)
ypred = rnd_clf.predict(X_test)
print(accuracy_score(y_test, ypred))

选择最好的 3 个模型用于集成学习:SVM、逻辑回归和随机森林。使用硬投票分类器，因为它实现了高精度。

from sklearn.ensemble import RandomForestClassifier
from sklearn.ensemble import VotingClassifier
from sklearn.linear_model import LogisticRegression
from sklearn.svm import SVClog_clf = OneVsRestClassifier(LogisticRegression(max_iter=1000, penalty = "l2", C=1, random_state=1))
rnd_clf = RandomForestClassifier(random_state=1)
svm_clf = OneVsRestClassifier(LinearSVC(C=0.01, loss = "hinge", random_state = 1))
voting_clf = VotingClassifier(estimators=[('lr', log_clf), ('rf', rnd_clf), ('svc', svm_clf)],voting='hard')
voting_clf.fit(X_train, y_train)from sklearn.metrics import accuracy_score
for clf in (log_clf, rnd_clf, svm_clf, voting_clf):
    clf.fit(X_train, y_train)
    ypred = clf.predict(X_test)
    print(clf.__class__.__name__, accuracy_score(y_test, ypred))

逻辑回归和投票分类器得分相同。选择投票分类器是因为它更稳健，因为它减少了预测和模型性能的传播。

结论

当提交给 Kaggle 竞赛时…一个可接受的第 17 名(50 支队伍中)。

当选定的模型应用于测试数据时(一旦提交给 Kaggle 竞赛)，准确性得分明显较低。这被称为过度拟合，即模型可以很好地预测训练数据的标签，但在应用于新数据时会经常出错。这意味着模型有很高的方差。

尝试过的过度拟合的可能解决方案:

• 添加更多数据
• 移除异常值
• 降维(通过主成分分析)

通过识别 15 个空值并在训练集中手动添加流派标签，试图增加总体样本大小。然而，使用 z 分数移除异常值会产生很大的反作用。总体而言，可用于训练模型的数据量非常有限，这可能是导致过度拟合的原因。

# reload the data
class_train = pd.read_csv("CS98XClassificationTrain.csv")
class_test = pd.read_csv("CS98XClassificationTest.csv")# update the genres for the missing values
class_train.loc[class_train['title'] == 'Unchained Melody', 'top genre'] = 'pop'
class_train.loc[class_train['title'] == 'Someone Elses Roses', 'top genre'] = 'adult standards'
class_train.loc[class_train['title'] == 'Drinks On The House', 'top genre'] = 'pop'
class_train.loc[class_train['title'] == 'Pachuko Hop', 'top genre'] = 'blues'
class_train.loc[class_train['title'] == 'Little Things Means A Lot', 'top genre'] = 'blues'
class_train.loc[class_train['title'] == 'The Lady Is A Tramp', 'top genre'] = 'pop'
class_train.loc[class_train['title'] == 'If I Give My Heart To You', 'top genre'] = 'pop'
class_train.loc[class_train['title'] == 'Happy Days And Lonely Nights','top genre'] = 'rock'
class_train.loc[class_train['title'] == 'Stairway Of Love','top genre'] = 'rock'
class_train.loc[class_train['title'] == 'You', 'top genre'] = 'pop'
class_train.loc[class_train['title'] == 'No Other Love' , 'top genre'] = 'adult standards'  
class_train.loc[class_train['title'] == "Hot Diggity" , 'top genre'] = 'folk'
class_train.loc[class_train['title'] == "Ain't That Just the Way" , 'top genre'] = 'r&b'
class_train.loc[class_train['title'] == "I Promised Myself" , 'top genre'] = 'pop'# dropping NULL values  (I've Waited So Long Anthony Newley = ? (Dance/ Electronic))
class_train = class_train.dropna(axis=0)# check again for null values
class_train.info()

PCA(主成分分析)是一种降维方法。降维移除数据集中冗余和高度相关的特征。它还有助于减少数据中的总体噪声。

from sklearn.decomposition import PCApca = PCA()
X_train = pca.fit_transform(X_train)
X_test = pca.transform(X_test)explained_variance = pca.explained_variance_ratio_
pca = PCA(n_components=0.95)
X_reduced = pca.fit_transform(X_train)
print(pca.explained_variance_ratio_)pca = PCA(n_components=6)
X_train = pca.fit_transform(X_train)
X_test = pca.transform(X_test)

使用主成分分析来降低维数没有显示出对 Kaggle 分数的改善。当特征之间有很强的相关性时，PCA 是最有效的。正如之前在关联热图中所强调的，训练数据集中的要素之间没有强关联。

虽然该模型在测试数据上表现不佳，但希望有一些有用的经验教训和可视化，可以为您未来的项目重铸！

安德烈·布尔科夫的百页机器学习书是一个很好的免费资源来学习更多关于 ML 的知识。

Spotiscience:数据科学家和音乐爱好者的工具

原文：https://towardsdatascience.com/spotiscience-a-tool-for-data-scientists-and-music-lovers-a3e32bd82ed1?source=collection_archive---------25-----------------------

Spotiscience 致力于简化下载和预测 Spotify 音乐数据的工作

马修·费雷罗在 Unsplash 上的照片

谁不喜欢和音乐一起工作？我们中的许多人整天都在电脑的后台进程中运行 Spotify，而播放列表中的随机音乐和我们喜欢的艺术家正在用他们的旋律营造一种神奇的氛围。它们提供了充分发挥生产力所必需能量。

为了整合我作为数据科学家的工作和我对音乐的热情，我决定创建一个名为“ Spotiscience 的工具，它允许使用官方 Spotify API 下载歌曲、艺术家、专辑和播放列表的数据。此外，这些数据被建模以生成新的数据，例如知道一首歌的情绪或歌词的主题，并找到类似的歌曲，这一切听起来很有趣，对吗？如果你想了解更多，请继续阅读这篇文章！

在本文中，您将学习:

• 从 Spotify API 下载歌曲、专辑、播放列表和艺术家的数据
• 从 API Genius 下载歌曲歌词
• 预测歌曲的音乐情绪
• 查找歌词的最相关主题
• 从 Spotify 的专辑、艺术家目录和播放列表中搜索相似的歌曲

索引

1.1 SpotiscienceDownloader

• 初始设置
• 1.1.2 歌曲特征的提取
• 1.1.3 影集提取
• 1.1.4 提取播放列表
• 1.1.5 播放列表和艺术家信息的提取

1.2 SpotisciencePredicter

• 初始设置
• 1.2.2 歌曲情绪的预测
• 1.2.3 歌词主题预测
• 1.2.4 相似歌曲预测

1.斑点科学

Spotiscience 是我在 GitHub 上用 Python 编程创建的一个项目，在这个项目中，你可以与 Spotify API 和 Genius API 进行交互，以提取歌曲、专辑、艺术家和播放列表的数据和特征。您还可以分析这些数据来生成新的信息，如情绪预测、主题建模和数学距离，以找到相似的歌曲。要下载 Spotiscience，您可以访问 Github 资源库。

https://github.com/cristobalvch/spotiscience

为了理解 Spotiscience 的应用和配置，我将详细介绍该工具的两个主要类别:

1.1 SpotiscienceDownloader

这个类从 Spotify API 和 Genius API 中提取数据。

初始设置

要使用它，必须进行如下设置:

import spotiscience

#create a dictionary with authorization keys
CREDENTIALS = {}
CREDENTIALS['client_id'] = "your_spotify_client_id"
CREDENTIALS['client_secret'] = "your_spotify_client_secret"
CREDENTIALS['redirect_url'] = "your_redirect_url"
CREDENTIALS['user_id'] = "your_spotify_user_id"
CREDENTIALS['genius_access_token'] = "your_genius_access_token"

"""You also can set your credentials id on credentials.py"""# returns 'downloader class'
sd = spotiscience.SpotiScienceDownloader(credentials=CREDENTIALS)

要获得 Spotify API 和 Genius API 的授权凭证，您可以观看以下教程:

认证 Spotify API 教程

认证天才 API 教程

要获取您的 Spotify 帐户的“ user_id ，您需要打开桌面 Spotify 应用程序，转到“ profile ”，然后复制指向 profile 的链接，如下所示:

作者照片

您将获得这个结果，您的 user_id 是粗体部分，链接的所有其他部分都可以删除。

" https://open . Spotify . com/USER/{USER _ ID}？si=9f52cafadbf148b2 "

1.1.2 歌曲特征的提取

要提取歌曲的特征，您应该在 Spotify 上搜索歌曲，然后复制歌曲的链接，如下所示:

在这种情况下，我复制了威肯的歌曲“眩目的灯光”的链接:

song_copy_link = "https://open.spotify.com/track/0VjIjW4GlUZAMYd2vXMi3b?si=369f90167c9d48fb"song = sd.get_song_features(song_id=song_copy_link)

结果将是具有以下歌曲特征的字典。要获得关于这些功能的更多信息，您可以在 Web API Spotify 上阅读关于音频功能的官方文档

{'id': '0VjIjW4GlUZAMYd2vXMi3b',
 'name': 'Blinding Lights',
 'artist': 'The Weeknd',
 'album': 'After Hours',
 'release_date': '2020-03-20',
 'popularity': 94,
 'length': 200040,
 'acousticness': 0.00146,
 'danceability': 0.514,
 'energy': 0.73,
 'instrumentalness': 9.54e-05,
 'liveness': 0.0897,
 'valence': 0.334,
 'loudness': -5.934,
 'speechiness': 0.0598,
 'tempo': 171.005,
 'key': 1,
 'time_signature': 4}

您还可以提取歌曲的音乐类型和歌词，如下所示:

# Returns song lyric
sd.get_song_lyrics(songname=song['name'],artistname=song['artist'])#Returns song Genre
sd.get_song_music_genre(song_id=song['id'])

1.1.3 影集提取

要从专辑中提取歌曲的特征，您必须在 Spotify 上搜索专辑并复制专辑的链接。相册提取方法有一个 id 参数，接收相册链接的字符串或字符串列表，需要在“False”中指定参数 is_artist :

#Returns songs features of album or albumsalbums =[
‘https://open.spotify.com/album/4yP0hdKOZPNshxUOjY0cZj?si=p5ItRNgXRlarmq4cihAVmA&dl_branch=1',
‘https://open.spotify.com/album/6Yf9kML4wT3opmXhTUIfk7?si=clKN-hzuTB236hINPATp-Q&dl_branch=1'
]sd.get_albums_song_features(id=albums,is_artist=False)

结果将是一个字典，其中的关键字是专辑的名称，内容对应于专辑歌曲的所有特征的列表。

也可以下载艺术家的唱片目录，在这种情况下，参数 id 只接收一个字符串，需要在“True”中指定 is_artist ，如下所示:

#Returns songs features of artist's discographyartist = 'https://open.spotify.com/artist/4fvA5FEHcdz97gscK90xZa?si=HNLNN7-dS5OR2W9TIUqQug&dl_branch=1'sd.get_albums_song_features(id=artist,is_artist=True)

1.1.4 提取播放列表

也可以从播放列表中提取歌曲特征。在这种情况下，playlist_id 参数只接收一个字符串，要提取的歌曲总数必须指定如下:

#Return song features of playlistplaylist = ‘https://open.spotify.com/playlist/37i9dQZF1DXcfZ6moR6J0G?si=da1435f1a0804933'sd.get_playlist_song_features(playlist_id=playlist,n_songs=50)

结果将是一个字典，其中的关键字是播放列表的名称，内容对应于一个包含播放列表歌曲所有特性的列表。

1.1.5 播放列表和艺术家信息的提取

最后，可以如下提取播放列表和艺术家的主要信息:

playlist = ‘https://open.spotify.com/playlist/37i9dQZF1DXcfZ6moR6J0G?si=da1435f1a0804933'artist = 'metallica'#Returns playlist information
sd.get_playlist_information(playlist_id=playlist)#Returns song information
sd.get_artist_information(artist=artist)

结果将是包含播放列表和艺术家信息的 2 个字典。

为了更好地理解所有的 SpotiscienceDownloader 方法，您可以通过点击这里来查看 GitHub repo 中模块 downloader.py 的源代码。

1.2 SpotiSciencePredicter

本课程使用监督学习的分类技术对歌曲数据建模，使用自然语言处理对主题建模，使用数学距离对歌曲相似性建模。

初始设置

要设置这个类，您只需要如下调用它:

import spotiscience
# returns 'predicter class'
sp = spotiscience.SpotiSciencePredicter()

1.2.2 歌曲情绪的预测

为了进行歌曲情绪预测，我使用了一种机器学习方法，从 Spotify 创建的情绪播放列表中标记一组歌曲，然后我用随机森林分类器算法训练一个模型，根据歌曲的特征标记歌曲。

关于这个话题的更多信息，你可以点击这里阅读我的文章《音乐情绪预测》

要预测情绪，只需传递用 SpotiscienceDownloader 提取的歌曲数据，如下所示:

#returns the tag of mood 
sp.predict_song_mood(song=song)

结果将是一个带有相应情绪类别的字符串，这些类别是；“悲伤、平静、活力和快乐”

1.2.3 歌词主题预测

歌词的主题预测使用潜在狄利克雷分配模型(LDA)、非负矩阵分解模型(NMF)或潜在语义索引模型(LSI)中的任何算法。为此，我将我的代码基于下面这篇文章，你可以在这里阅读。

要预测歌词的主题，您必须配置以下参数:

歌词=歌曲的歌词

model =要使用的模型[选项有“lsi”、“lda”或“NMF”]

lang =歌词语言[选项为“英语”或“西班牙语”]

n_grams =要分组的单词的子集数

n_topics =返回主题的数量

top_n =每个返回主题的字数

关于参数 n_grams 的更多信息，你可以点击这里阅读关于 sklearn 矢量化的官方文档

lyric = song_lyrics
model = 'lda' (available type 'lda', 'lsi', 'nmf')
lang = 'english' (available type 'english','spanish')
n_grams = (1,1)
n_topics = 1
top_n = 5#predict the topics of the song lyric
sp.predict_topic_lyric(lyric,model,lang,n_grams,n_topics,top_n)

1.2.4 相似歌曲预测

为了预测歌曲的相似性，我使用曼哈顿距离(l1)和欧几里德距离(l2)来计算歌曲特征之间的距离，并按升序对结果进行排序。

要预测歌曲相似性，您必须配置以下参数:

• object =要比较的参考歌曲
• target =要在专辑、播放列表或艺术家中评估的歌曲组
• 距离=使用距离[选项为“l1”和“L2”]
• n_features =用于计算距离的歌曲特征的数量
• top_n =在元组结果中返回的歌曲数

关于参数 n_features 的更多信息，可以点击这里阅读方法的源代码。

例 1 :通过亚声调预测“ Nu Metal Generation ”播放列表中哪些歌曲与歌曲**Change(In the House of Flies)】**最相似。

playlist_link = "[https://open.spotify.com/playlist/37i9dQZF1DXcfZ6moR6J0G?si=452e104160384c8e](https://open.spotify.com/playlist/37i9dQZF1DXcfZ6moR6J0G?si=452e104160384c8e)"song_link = "[https://open.spotify.com/track/51c94ac31swyDQj9B3Lzs3?si=5aca903582464acd](https://open.spotify.com/track/51c94ac31swyDQj9B3Lzs3?si=5aca903582464acd)"target = sd.get_playlist_song_features(playlist_link,n_songs=70)
object  = sd.get_song_features(song_link)
distance = 'l2'
n_features = 6
top_n = 10#returns most similar songs from playlistsp.predict_similar_songs(object,target,distance,n_features,top_n)

例 2 :预测“杜阿·利帕唱片公司的哪些歌曲与“威肯的歌曲“眩目的灯光”最相似

artist_link = "https://open.spotify.com/artist/6M2wZ9GZgrQXHCFfjv46we?si=VJ3J-isZRbSM5x2pNUnrhw&dl_branch=1"song_link = "https://open.spotify.com/track/0VjIjW4GlUZAMYd2vXMi3b?si=369f90167c9d48fb"target = sd.get_albums_song_features(id=artist_link,is_artist=True)
object  = sd.get_song_features(song_link)
distance = 'l2'
n_features = 6
top_n = 10#returns most similar songs from playlistsp.predict_similar_songs(object,target,distance,n_features,top_n)

这两个例子的结果都是一个字典，其中的关键字是引用歌曲的名称(对象),内容是一个元组列表。每个元组都是歌曲名称及其与参考歌曲(对象)的距离的一对值。

注意:也可以预测专辑中的类似歌曲，而不必下载艺术家的整个唱片目录。为此，您可以在目标参数上使用相册功能。

2.结论

将数据科学和音乐这两个不同的领域结合起来，可以产生很好的选择，有助于理解音乐在不断变化的文化中是如何发展的，在这种文化中，乐器的声音、歌词和声乐技巧可以有许多不同的解释。在技术的帮助下，我们试图获得这些解释和意义的近似值，以研究是什么无形的能量使音乐伴随我们一生。我希望 Spotiscience 可以成为帮助像我一样的数据科学家、开发人员和音乐爱好者的技术之一。

3。参考文献

• https://www . ka ggle . com/the brownviking 20/topic-modeling-with-spacy-and-sci kit-learn
• https://towards data science . com/predicting-the-music-mood of-a song-with-deep-learning-c3ac2b 45229 e

我的其他文章

https://medium.com/datos-y-ciencia/data-science-para-todos-4cb84264bb5f https://cristobal-veas-ch.medium.com/artificial-intelligence-extracting-qualifying-adjectives-from-comments-on-donald-trump-facebook-84bf60be5c8e https://medium.datadriveninvestor.com/automate-the-process-of-adding-your-users-saved-tracks-by-genre-to-playlists-on-spotify-d6402da60285 https://medium.datadriveninvestor.com/automating-playlists-on-spotify-from-the-music-folders-of-your-computer-c79060eadab2 https://medium.com/datos-y-ciencia/una-forma-interactiva-para-buscar-y-analizar-ofertas-de-trabajo-en-la-web-ef9327b0a8d3 https://medium.com/datos-y-ciencia/una-forma-genial-para-buscar-y-analizar-arriendos-de-departamentos-en-la-web-e20727390a8c https://medium.com/datos-y-ciencia/cómo-hacer-reflexionar-a-tu-computador-con-las-noticias-del-país-dc14d2fc0a67

发现天才的机器学习工程师

原文：https://towardsdatascience.com/spotting-talented-machine-learning-engineers-5298a76427a0?source=collection_archive---------14-----------------------

了解关键技能领域，以识别有才华的机器学习工程师。这种理解将有助于招聘、分配和提升工程师。

图 1:与机器学习工程师相关的学科。作者图。

机器学习工程师(MLE)是目前最热门的角色之一。虽然许多人会将这样的角色与 Python 、 R 、随机森林、卷积神经网络、 PyTorch 、 scikit-learn 、偏差-方差权衡等联系起来。在这些工程师前进的道路上，会有更多的事情发生。MLE 需要处理的事情不仅来自机器学习领域，还来自其他技术和软学科。如图 1 所示，除了拥有 ML 技能，MLE 还需要了解编程、(大)数据管理、云解决方案和系统工程。此外，此人需要有相当多的项目管理技能，以及成为一个坚实的团队成员，而不牺牲个人的好奇心和野心。

在这篇文章中，我们分享了如何根据这些学科的能力来发现有才华的 mle。我们将学科分为四类，其中只有一类是纯技术性的。其余的更软，组织和项目管理技能，这可能与其他专业人士有关。随着工程师成熟度的发展，我们也分享了期望的演变。你应该考虑到，除了在早期阶段获得的技能之外，处于某一成熟水平的工程师还会获得新的技能。在文章中，我们只展示了三个级别:初级、高级和领导。如果您不同意这些级别，而更喜欢更细粒度的级别，那么就按照您认为合适的方式进行调整。

可扩展的机器学习系统

如图 2 所示，支持基于 ML 的系统需要的不仅仅是 ML 代码。因此，MLEs 需要了解来自数据科学、计算机科学、软件工程、云工程和系统工程的概念。此外，MLEs 需要精通编程语言、数据科学库、大数据工程库、云和系统管理平台。在这种设置中，MLEs 感知笔记本或脚本之外的机器学习代码。相反，他们将它们视为可以模块化的应用程序，以便在生产基础设施中轻松工作。他们也知道 ML 代码需要使用一组协同工作的系统来托管、管理、执行、调试、记录和监控。我们在这些维度上捕捉 mle 的能力。

图 2:只有一小部分真实世界的机器学习系统由机器学习代码组成，如中间的小黑框所示。所需的周边基础设施庞大而复杂。MLE 需要深入研究所有这些系统。最初发表在论文机器学习系统中隐藏的技术债务。

核心技术学科的基础知识

• 初级工程师拥有这些学科的功能知识。
• 高级工程师在某些领域拥有广泛而深入的知识。这些工程师也可以指导低年级学生提高他们的知识。
• 领导级别的工程师可以重新定义某些领域的概念和知识，以更好地适应问题领域。这些工程师还可以指导高年级学生如何成为这些学科的专家或多面手。

核心学科的库和框架知识

• 初级工程师熟悉知名的库和框架。
• 高级工程师与许多备选方案合作过，了解其中一些方案的设计原则和底层实现。这些工程师还可以帮助团队采用和解密这些库。
• 领先水平的工程师自举或贡献了一些众所周知的被许多人使用。工程师还可以识别出团队应该关注的库和框架。

端到端机器学习工作流的实现

• 初级工程师知道如何实现典型的端到端 ML 流程。
• 高级工程师知道如何为大规模或复杂的部署改进/重新设计/重新实施工作流中的组件。工程师也知道如何测试和验证流程。
• 领导级别的工程师可以提出参考架构，他们的概念证明，并使其他团队能够采用这些架构。

系统设计与实现

• 初级工程师知道如何与操作和开发系统互动。
• 高级工程师知道如何使用常见的虚拟化、DevOps 和云技术来实现其中的一些系统。他们还可以指导初级工程师更熟练地使用和掌握构建模块。
• 首席工程师可以设计参考体系结构及其概念验证。他们还可以帮助团队制定采用这些架构的策略。他们还知道容错、可测试性、可调试性等的实现方法。

最佳实践

像任何其他工程一样，机器学习工程是一项团队活动。这基本上意味着某些实践需要微调，以便团队可以有效地相互作用，从而更容易交付。MLE 还需要能够原型很多。此外，MLEs 需要开发易于共享的东西，并且必须愿意建立在其他人的工作之上，这来自于内部源文化(想象一下，只在组织的范围内采用开源文化)。

工程最佳实践

• 初级工程师擅长执行坚持原则的任务
• 高级工程师帮助团队采用某些最佳实践，同时继续价值交付。
• 一流的工程师可以将这些原则具体化为可行的实践。他们还可以找出团队的(长期)策略，通过采用关键的最佳实践，在不牺牲当前价值交付太多的情况下，爬上技术成熟度的阶梯。

原型制作

• 初级工程师偶尔可以通过查看已知的例子来开始一个新的组件。
• 高级工程师可以在没有已知示例的情况下实现/验证新组件。
• 首席工程师可以提出设计原则和流程来实施/验证新组件，并推动核心组件的开发，从而实现快速原型开发。

内部来源

• 初级工程师知道如何对内在源泉做出贡献。
• 高级工程师可以推动一些关键的最佳实践和部落知识文档。工程师可以帮助团队在不同的环境中翻译这些内容。
• 领导级别的工程师可以确定内部源文化的框架，这将使高级工程师能够起草最佳实践和部落知识文档的内容。

项目管理

像任何工程师一样，MLEs 需要在日常工作中保持高效。这意味着掌握项目管理技能，包括设计思维、计划、理解交付和敏捷原则。对于 MLE 来说尤其如此，因为 MLE 很可能在跨职能团队中处于核心地位，需要发挥很大的作用。

设计思维

• 初级工程师知道如何进行简单的需求分析。
• 高级工程师知道如何执行全面的需求分析，验证需求，并帮助团队充实细节。
• 领导级工程师与利益相关者建立牢固的关系。他们知道如何参与描绘涉众旅程的对话，并将它们转化为一组系统需求。工程师还知道如何定义度量，以便通过分析理解需求及其解决方案。

通信

• 只要有需要，初级工程师就能与团队很好地沟通。
• 只要有需要，高级工程师也会与利益相关者进行良好的沟通。
• 领导级别的工程师努力建立便于沟通的环境。

策划

• 初级工程师知道如何针对工程问题实施计划。
• 高级工程师知道如何充实此类计划的细节，并细化实施细节。
• 领导级别的工程师知道如何为工程问题定义里程碑，并帮助团队区分活动的优先级，以确保达到里程碑。他们知道如何做出正确的取舍，不会过度设计事情。

交付心态

• 初级工程师知道如何在合理的时间内按计划交付。
• 高级工程师知道如何将交付分解成可管理的集合，并能够支持扩展目标，例如降低成本、提高效率等。，用于专注的工作。
• 领导级别的工程师知道如何通过复杂的调度来推动并行交付议程。他们还充分预测即将到来的情况和跑道上的进展，以使其易于交付。

敏捷原则

• 初级工程师通常能熟练掌握敏捷方法的仪式(看板、Scrum 等)。).
• 高级工程师知道如何坚持原则，并帮助团队坚持原则。
• 领导级别的工程师知道如何为团队翻译敏捷仪式的原则，并帮助他们选择正确的变体

文化契合度

MLEs 的存在是为了支持团队，而不是相反。因此，很多重点应该放在 MLEs 如何融入团队文化上。这并不意味着 MLEs 应该同意团队其他成员决定的所有事情。更确切地说，MLEs 和其他专业人士一样，应该做一些让团队变得更好的事情，即使这意味着不同意团队的其他成员，但无论如何都要遵守工作计划。此外，无论工程师开的是什么，他们都应该尽可能拥有全部所有权。争取多元化是建立一个健康团队的方法，这个团队知道如何处理赞美的意见。机器学习旨在解决复杂的全球问题，这不可能由一个狭隘的背景团队来完成。最重要的是，工程师需要有一种成长的心态，这种心态显然来自于主动和被动的学习。

团队第一

• 只要团队需要，初级工程师随时准备介入。
• 高级工程师知道如何介入，并可以随着情况的变化调整参与程度。
• 一流的工程师积极致力于创造一个培养健康团队的环境。

所有权

• 初级工程师知道如何掌控他们觉得舒服的领域。
• 当被要求时，高级工程师知道如何掌控所有领域，即使他们对其中一些领域感到不舒服。
• 领导级别的工程师知道如何掌控一切，无论舒适程度如何，并建立一个鼓励他人这样做的环境。

包容性

• 初级工程师知道如何在多样化的工作环境中工作。
• 高级工程师知道如何支持团队保持包容性。
• 领导级别的工程师知道如何努力创造一个更包容的工作环境。

学习心态

• 初级工程师有很大的动力提升自己的技能。
• 高级工程师知道如何在满足他们自己的学习目标的同时促进一群人的学习活动。
• 领导级别的工程师知道如何通过流程和策略实现团队学习。

评论

我确信我没有充实许多其他重要的特征。然而，至少上面提到的那些会让你开始。如果您不同意以上某些观点，请分享您的观点。如果你觉得其他一些观点需要提及，请分享你的观点。如果您同意这些观点并认为它们很有帮助，请分享您的观点。

电子表格到 Python:是时候进行转换了

原文：https://towardsdatascience.com/spreadsheets-to-python-its-time-to-make-the-switch-ef49cf9463d5?source=collection_archive---------5-----------------------

电子表格到 Python

使用代码更可靠，更可复制，也更有趣

照片由米卡·鲍梅斯特在 Unsplash 上拍摄

大多数人对数据分析的入门是使用电子表格，比如 Microsoft Excel。

电子表格非常强大和受欢迎，但也有明显的局限性，特别是在可重复性、可共享性和处理大型数据集方面。

当到了认真对待数据分析的时候，有经验的从业者会告诉你使用代码:它更可靠，更容易复制，而且更令人愉快。

但是，如果您是编码新手，从基于电子表格的数据分析过渡到基于代码的数据分析可能会令人望而生畏。

为了让您更容易开始基于代码的分析，我创建了一系列博客文章，使用日常示例展示代码如何取代电子表格来可视化、分析和建模数据。

我们将使用流行的编程语言 Python，它已经成为基于代码的数据分析的事实上的标准。代码都是通过在线协作工作空间运行的。

让我们开始吧。

你所在的地方天气怎么样？

为了向您展示为什么 Python 中的分析更优越，我将稍微聊一下天气。

具体来说，牛津的天气，但你也可以把它应用到你的城镇。这篇文章将向你展示如何从服务器自动下载天气数据，生成包含温度信息的数据帧，绘制不同的平均值，并拟合趋势线。

所有这些活动都可以在电子表格中完成，但使用代码可以更容易地更改分析，随着新数据的出现更新分析，分析更大的数据集，并以可重复的方式分享我们的分析。也更容易产生漂亮的图形！

下载数据

牛津的拉德克利夫气象站有可下载的天气数据。对于这个例子，我将探索从 1815 年开始的每日温度测量！

我们需要做的第一件事是导入 Pandas，这是一个用于数据分析的强大的 Python 包。然后，我们可以使用 Pandas 函数 read_csv 直接从牛津大学网站读取天气数据。这给了我一个数据框架，我们可以将它可视化如下:

作者图片

数据帧由数据列组成，标题指示每列的内容。

这看起来非常像你在电子表格中看到的。在没有数据的地方，我们看到 NaN。然而，在某些单元格中，csv 文件包含文本解释，稍后我们可以很容易地用 NaN 替换它。

通过查找包含我们需要的年、月和日的行，我们可以找到特定日期的数据。在 Excel 中，我们可以通过在列上放置过滤器来实现这一点。

作者图片

如果我们只想知道当天的温度，我们可以使用:

这给出了 4.7℃的输出

这比在电子表格中滚动大量数据要容易得多。这里唯一棘手的是记住如何写学位标志！

我们现在将做一些数据处理，以便我们可以比较不同年份的温度。

我们想要的是一个数据帧，其中列代表年份，行代表一年中的每一天。

我们要做的第一件事是删除 2 月 29 日的所有数据。我们通过查找所有行的索引(最左边的一列)来实现，其中 month 是 2，day 是 29。

然后我们使用。拖放以删除这些行。同样，这可以在电子表格中完成，但是使用 Python 和 Pandas 要简单得多。

完成后，保存新的数据帧是很有用的，这样我们就不必再次下载和操作它。

绘制温度图

现在让我们制作一些漂亮的图表来显示每天的温度。

我们将使用三个最常见的 Python 库中的函数:Pandas、Numpy 和 Matplotlib。首先我们需要导入这些库。

现在读入我们之前保存的数据。

利用这些数据回答一个有趣的问题可能是，“今天异常热吗？”，所以我们来看看 Tmax。为此，我们创建了一个包含每天最高温度的新数据帧。

作者图片

虽然这段代码最初看起来很复杂，但实际上比在 Excel 中执行同样的任务要简单得多。

现在我们有了每天的最大温度，很容易画出每年每天的最高温度，并突出显示我们想要的任何一年。然而，这并没有太大的意义，正如我们从下图中看到的。

作者图片

在这个图中，很难看出 2020 年是否是一个异常值。为此，我们需要计算 Tmax 的均值和标准差，并重新绘制。

作者图片

这里灰色的线代表标准差。看起来 2020 年有些热天！

当然，如果我们选取任何一年，都可能有异常值:我们预计大约有三分之一的日子在标准偏差之外，大约六分之一高于标准偏差，六分之一低于标准偏差。

如果我们想知道温度总体上是否在上升，也许最好取两个时间段的平均值，如下所示:

我们现在可以将最近 20 年的平均值与 1990 年以前的平均值和标准误差进行对比。

作者图片

所以，看起来过去 20 年比长期平均气温要高；尤其是冬天的气温明显更高。

计算趋势

让我们通过仔细观察 Tmax 这些年来的变化来进一步探讨这一点。我们感兴趣的是每年最热和最冷的一天，以及全年 Tmax 的平均值。

这显示了使用 Pandas 数据框架的一些威力；上面我们计算了行的平均值，现在我们找到了列的平均值，我们甚至可以在绘制图表的同时进行计算。

作者图片

从这张图表中可以看出，在过去的几年中，气温有所上升；让我们通过拟合一些趋势线来量化这一点。在这里，我们将从 1980 年开始重新定义“最近”的年份。

作者图片

该代码告诉我们，1980 年之前的梯度为每世纪 0.44 摄氏度，但自 1980 年以来，它已相当于每世纪 4.9 摄氏度。

在代码中这样做的一个巨大优势是，很容易改变用于比较的年份范围。

显然，近年来的梯度比过去 165 年要大。为了确定这是否有统计学意义，我们需要测量梯度计算中的误差。这就是所谓的标准误差，可以用不同的函数很容易地计算出来，相当于 Excel 中的 LINEST(我个人觉得用起来很痛苦)。

作者图片

该函数在那里输出相当多的信息；让我们只看我们想要的价值观

作者图片

+/-值代表 95%的置信区间。这意味着在过去的 40 年里，平均气温以每世纪 4 到 6 摄氏度的速度上升，这在统计学和这个词的日常意义上都是很重要的。

摘要

我已经展示了一个如何使用 Python 来执行简单的可视化和数据分析的例子。

有些步骤会比使用电子表格花费更长的时间。然而，对于大型数据集或重复操作，使用代码减少了错误的范围，同时使重复或修改计算变得容易。这绝对值得额外的时间投入。

最后，在这里的几乎所有例子中，我都使用了默认的 matplotlib 格式的图形，它们已经非常好地呈现了。这绝对是电子表格无法比拟的优势。

在这个博客系列中，我将进一步探索 Python 的功能，并希望能启发您迈出使用 Python 的第一步。

这篇文章的文本、代码和图像是使用 灯丝 创建的，这是一个用于数据、分析和报告的一体化工作空间。如需了解更多信息，请访问我们的网站http://www.filament.so*。Filament 目前正在运行一个封闭的测试程序；前 100 人到* 报名 使用推荐代码 TDSFILAMENT 可以跳过等候名单，提前报名。

参考

[1] Stephen Burt 和 Tim Burt，1767 年以来的牛津天气与气候 (2019)牛津大学出版社。

在生存森林中被抛弃:Python 中的客户流失预测

原文：https://towardsdatascience.com/spurned-in-a-survival-forest-customer-churn-prediction-in-python-2083551f3cc5?source=collection_archive---------6-----------------------

具有 PySurvival 的条件生存森林模型

年轻的爱被拒绝的股票照片——由伊斯托克/范德韦尔登(istockphoto.com)在标准许可下拍摄

一家公司的客户流失率 比率是在给定的时间段内停止与该公司做生意的客户的百分比。他们中的大多数人肯定已经开始喜欢竞争对手的产品了。留住客户的努力旨在最大限度地降低流失率。

一般来说，试图吸引新客户——通过把他们从竞争对手那里引走——比留住现有客户的成本更高。贝恩公司(Bain & Company)的一篇论文曾指出，在金融服务行业，客户保持率每增加 5%，利润就会增加 25%以上(bain.com)。

我们的目标是构建一个预测每个客户流失风险的模型。为了向销售和营销团队提供早期预警信号，该模型应识别那些与客户可能很快会拒绝我们的业务的风险增加相关的客户资料。

1.属国

除了我们的核心包如 pandas 和 numpy，我们将安装并导入 PySurvival 库。

和往常一样，在安装一个复杂的包之前，您应该创建一个新的虚拟环境，尤其是具有非 Python 依赖性的包。PySurvival 需要 C++可再发行版本。

要在 **MacOS 或 Linux 上 pip-install PySurvival，**您应该检查 gcc 是否已经安装在您的机器上:

• 安装—py survival(square . github . io)

在 Windows 机器上，安装需要一些额外的步骤。PySurvival 开发人员似乎忘记了一个事实，即 Python 用户社区存在于操作系统划分的 Windows 端；或者他们只是没那么喜欢 Windows 用户。但是我们可以在 Windows 机器上运行 PySurvival，即使 pip 安装无法运行。请遵循以下步骤:

• 使用不高于 3.8 的 Python 版本创建新的虚拟环境。例如在 conda 中:$ conda create-n your chosen name python = 3.8。原因是当前的 PySurvival 版本使用了一种 3.8 以后已经不推荐使用的方法 tp_print。
• 你将需要微软的 C++编译器。如果您的计算机上还没有安装它，那么一种方法是从以下网址下载免费的 Visual Studio 构建工具:下载 Visual Studio 工具—免费安装 Windows、Mac、Linux(microsoft.com)。在构建工具选择窗口的左上角，选择“用 C++进行桌面开发”选项。

截图:作者

• 从这个 GitHub 资源库下载分叉的 PySurvival 包，在 Windows 下工作:【github.com】bacalfa/py Survival:生存分析建模开源包【对其创建者 B. A. Calfa 的赞赏】。将下载的软件包保存在要用作其安装目录的文件夹中。
• 激活您的新虚拟环境，例如:$ conda activate yourchosenname
• 在命令提示符下，导航到保存并解压缩 PySurvival 包的目录。
• 在命令提示符下，首先重新打包下载的文件，然后安装软件包:

作者图片

作者图片

2.数据争论

PySurvival 附带了一个内置的数据集来分析客户流失。我们从数据集模块加载它。

原始数据代表 SaaS 提供商(软件即服务)的客户数据库，该提供商为中小型企业提供一系列服务:数据存储、工资和费用核算、在线营销和客户响应跟踪。它的商业模式是基于每月订阅。

这家 SaaS 公司要求我们实施一个预测客户流失风险的模型，以便销售和营销团队能够采取行动，增加留住这些高风险客户的机会。

作者图片

源数据由 13 列组成，其中 5 列是分类变量。Pandas 的 info()函数显示 dataframe 不包含空值。

当我们试图确定流失风险背后的模式时，有两列将作为我们的目标变量:

• “搅动的”，标识过去没有续订的客户
• “months_active”，它添加了一个时间维度，我们希望沿着这个维度来跟踪客户流失风险

其他列表示影响客户流失风险的特征。我们想确定哪种特征值组合——哪种客户特征——将流失概率提高了多少。

作者图片

作者图片

当我们查看分类变量时，我们看到每一列中唯一值的数量很少。它们的低计数将使它们更容易转换成数值。

作者图片

为了评估分类值对流失风险的影响，我们需要导出数值。

下面的列表 catcols 标识分类列的索引。然后， *ne-hot 编码，*通过使用熊猫的 get_dummies() 函数，为每个唯一分类值创建一个新列，并用二进制值 1 和 0 填充它:值 1 出现在找到匹配分类值的行中。

info()函数确认没有留下分类列。它还显示 dataframe 现在包含 26 列，而原来只有 13 列。例如，前一列“公司规模”中的四个唯一值被拆分为四列。

作者图片

作者图片

这个扩展的数据框架将使我们能够运行数据科学模型，量化特征变量对流失风险的影响。

作者图片

我们通过分离目标变量来完成我们的数据争论工作:

• “事件”——在我们的例子中是“搅动”= 1 或 0
• 时间维度:“months_active”

然后，第 6 行中的 numpy 函数 setdiff1d() 从所有列名的列表中删除“churned”和“months_active ”,留下一个特性列的列表。

作者图片

3.识别流失风险

3.1 特征的相关性

在 PySurvival 的效用函数中，我们找到了*相关矩阵。*它显示了特征对齐的紧密程度。如果任何一对显示出惊人的高相关性，接近 1.0，我们应该删除其中一个来处理它们的多重共线性。

在我们的例子中，大中型客户公司之间的最高相关性不超过 0.52。这不是一个令人担忧的水平，所以我们继续我们的分析。

作者图片

3.2 训练与测试数据集

在第 3 行，Scikit 的 train_test_split 方法保留了 35%的数据集用于测试。

第 9 行到第 11 行对特性列 X、事件列 E ("churned ")和时间列 T ("months_active ")应用相同的分割。

作者图片

3.3 条件生存森林模型—概念

我们从生存分析之外的分类问题中知道决策树和随机森林。你可以在 Carolina Bento 的文章中找到关于决策树的优秀初级读本:现实生活中解释的决策树分类器:挑选度假目的地| Carolina Bento |迈向数据科学；在饶彤彤关于随机森林的文章中:了解随机森林。算法如何工作|饶彤彤|走向数据科学

生存问题增加了一个困难:模型必须处理被审查的数据。当数据集达到观察期的末尾时，许多(希望是大多数)客户将不会有动摇。他们的流失事件仍未被观察到，并将在未知的未来日期发生。因此，数据集是右删截的。

为了处理审查问题，生存模型使用一个修改的配置:

• 传统的回归模型对两个数据组 X 和 y 进行操作:回归变量 X 和目标向量 y。
• 而生存模型对 X、E 和 T 这三个变量起作用:
• —特征数组 X(由代表客户属性或概况的列组成)
• —二进制事件指示符(1 或 0)的向量 E，其表示是否已经为客户记录了流失事件；
• —时间向量 T = min(t，c)，其中 T 表示事件时间，c 表示审查时间

生存模型预测感兴趣的事件在时间 t 发生的概率。

我们将实现由赖特/丹科夫斯基/齐格勒在 2017 年(【arxiv.org】)设计的条件生存森林模型。CSF 改进了旧的随机生存森林方法使用的训练算法。

3.4 拟合模型

PySurvival 的 CSF 模型采用以下参数:

• 每次分割的最大要素数
• — int =绝对数字
• —浮点=分数
• —“sqrt”=特征数量的平方根
• —“log2”=特征数量的二进制对数
• —“全部”
• 最小节点大小=每个节点的最小样本数，默认为 10
• alpha =允许拆分的显著性水平，默认为 0.05
• 样本大小百分比= %每个树构建中使用的原始样本，默认值为 0.63
• num_threads =并行线程中可用内核的数量

拟合生存森林后，我们检查它的准确性。

3.5 准确性指标

一致性指数衡量模型的辨别能力。如果有四个客户在 2 年、3 年、4 年和 5 年后流失，而模型准确预测他们将按此顺序流失，则和谐指数达到其最佳值 1.0。如果模型预测了不同的等级顺序，则指数将低于 1。该指数并不评估预测的存活时间，而是评估排名顺序。如果模型为四个客户分配了 3、4、6 和 7 的流失周期，则指数仍将等于 1.0，因为它报告了客户的正确排序顺序( C-index) 。虽然这看起来像是一个弱点，但与 MAE 等其他准确性指标相比，我们需要一个像 concordance index 这样的指标来处理右删截数据:在观察数据集的结束日期，所有客户的流失事件将只记录一小部分。

就我们的目的而言，我们认为 0.87 的一致性指数是令人满意的。

Brier 评分衡量实际流失状态和估计概率之间的平均差异:观察到的生存状态(0 或 1)和给定时间 t 的生存概率之间的平均平方距离。它可以采用 0 到 1 之间的值，0 是最佳值:生存函数将完美地预测生存状态( Brier 评分)。通常，最大值 0.25 被视为接受阈值。

在我们的例子中，Bier 分数不超过 0.14:低到可以接受。

作者图片

PySurvival 的compare _ to _ actual方法沿时间轴绘制了预测的和实际的风险客户数量。它还计算三个精度指标，RMSE、平均误差和中位数绝对误差。在内部，它计算 Kaplan-Meier 估计量，以确定源数据的实际生存函数。

作者图片

3.6 模型结果:特征重要性

拟合模型报告了数据帧中每个特征的重要性。重要性衡量特性对流失风险的影响。积极的重要性分数会增加风险，消极的分数会减轻风险。

列“pct_importance”在从 0 到 1 的范围内校准相对重要性。相对重要性合计为 1.0。

• 不出所料,“csat _ score”——通过市场调查衡量的客户满意度得分——与客户流失风险密切相关。
• 没有预订工资单或产品会计服务的客户更容易拒绝。要么其他顾客已经学会欣赏整个产品系列；或者，将这些更复杂的任务转移给竞争对手会花费他们更多的精力，因此，只要竞争对手不能展示出明显更好的竞争优势，他们就会继续留在 SaaS 公司。**
• “minutes_customer_support”将那些在取消订阅之前经常向支持热线投诉的客户视为高流失风险。

作者图片

3.7 流失预测

让我们从源数据中随机抽取一个客户:index 1989。

请记住，我们创建了 dataframe X 来包含所有的特性列(不包括目标变量 churned 和months _ active】)。我们将随机选择的索引行 1989 的特征输入三个函数:生存、危害和风险评分函数。

如果我们将时间变量 t 设置为 None，那么 PySurvival 将计算数据帧中所有时间段的函数值，并显示它们的趋势——在我们的示例中是 12 个月。

作者图片

生存函数是累积分布函数的补码:

• svf(t > T) = 1 — cdf(t ≤ T)

变量 T 表示直到感兴趣的事件发生的时间。

累积分布函数沿着时间轴追踪事件的增长概率。流失风险会随着时间的推移而增加。最终，许多客户将会破产或尝试竞争对手的产品。

相反，生存函数 svf(t)报告事件在时间 t 之前没有发生的概率。

危险函数 hf(t)代表时间 t 的瞬时故障率:事件在下一个时刻 t内发生的概率，假设事件在时间 t 之前没有发生。通常，危险函数不能单独解释。*

风险分值汇总了一段时间内的累积危险值。PySurvival 将其报告为我们选择的整个时间范围的标量。

下面的笔记本单元格计算每个客户在四个时间点的风险分数和生存概率:订阅期的 1、3、6 和 12 个月。它将生存概率作为四个新列插入数据帧:svf1、svf3、svf6 和 svf12。

作者图片

PySurvival 的方法 create_risk_groups 绘制了数据集中客户的得分。这有助于想象，例如，客户数量是否在接近标尺的高端时开始上升。

该方法采用可选数量的分组标准。在第 2 行到第 5 行，我选择用四分位数来划分风险分值。然后，图表通过许多不同的颜色代码来区分四个风险分值组。

作者图片

结论

我们将生存分析技术——条件生存森林——应用于我们的客户数据库。

该模型确定了客户档案中的哪些特征决定了流失风险:每个特征的重要性权重。然后我们将生存概率输入客户数据库。这些指标将为营销团队提供他们所寻求的预先信号:什么时候接触客户是明智的？

Jupyter 笔记本可在 GitHub 下载:h3ik0th/py survival _ CSF:py survival 的有条件生存森林(github.com)的客户流失

从 A 到 Z 的 SQL:第 1 部分

原文：https://towardsdatascience.com/sql-a-to-z-part-1-79ba695a563d?source=collection_archive---------35-----------------------

学习最重要和最常用的 SQL 语句

为什么要学习 SQL？

作为数据科学家，我们利用数据提供见解和建议，为产品战略、增长和营销、运营以及许多其他业务领域提供信息。在许多情况下，数据以关系数据库格式存储，使用允许访问彼此相关的数据点的结构(来源: AWS )。SQL(结构化查询语言)是一种用于访问关系数据库的编程语言。作为数据科学家，我们使用 SQL 对数据库执行查询，以检索数据、插入、更新和删除数据库中的记录，并创建新的数据库和表。语法可能会因关系数据库管理系统的不同而略有不同(例如，在查询末尾使用分号)，但一般逻辑应该适用。

数据作为称为表的对象存储在数据库中，表是列和行形式的数据条目的集合。列(也称为字段)包含列名及其属性。行也称为记录，包含每列的数据。

照片由奥斯丁·迪斯特尔在 Unsplash 上拍摄

例子

让我们用一个简单的例子来完成最常用的 SQL 查询。假设有一家公司通过类似于脸书、Reddit、Pinterest 等的应用程序向用户提供内容。

当用户登录该应用程序时，该公司会向用户提供内容。这记录在内容插入表中。该表存储了向谁提供了什么内容以及时间戳。

我们还有一个名为 user_content_activity 的表，当用户对内容进行操作时，它会存储所有数据。它包括用户 id、内容 id、操作类型和用户执行操作的时间戳。

最后，我们有两个表给出了用户和内容的属性，分别命名为用户和内容。 users 表存储用户居住的国家和用户加入(或注册)应用程序的日期。内容表存储内容类型。

表名:用户 _ 内容 _ 活动

+--------+-----------+--------+---------------------+
| userid | contentid | action |      timestamp      |
+--------+-----------+--------+---------------------+
|1       | 5         | view   | 2021-01-05 10:30:20 |
|1       | 5         | click  | 2021-01-05 10:30:55 |
|2       | 21        | view   | 2021-01-06 03:12:25 |
|3       | 100       | view   | 2021–02–04 06:25:12 |
+--------+-----------+--------+---------------------+

表名:用户

+--------+---------------+------------+
| userid |    country    |  join_date |
+--------+---------------+------------+
|1       | United States | 2018-05-06 |
|2       | Italy         | 2019-12-31 |
|3       | Japan         | 2020-03-05 |
|4       | United States | 2021–01-26 |
+--------+---------------+------------+

表格名称:内容

+-----------+-----------+
| contentid |   type    |
+-----------+-----------+
|1          | video     |
|2          | photo     |
|3          | text      |
|4          | photo     |
+-----------+-----------+

基本形式

***SELECT {column1, column2, …} 
FROM {tablename}***

这是从数据库中检索信息的最基本形式。编写查询时，要问的第一个问题是“哪个(哪些)表有我需要的信息？”然后，“哪一列有适当的信息？”举个例子，

• 问:给我用户来自的国家列表 >从用户中选择国家
• 问:给我所有的用户、内容和用户采取的行动

  从用户内容活动中选择用户标识、内容标识、行动

• 问:给我 content_insertion 表中的所有数据

  从 content_insertion 中选择 userid、contentid、时间戳
  从 content_insertion 中选择*

符号 ***** 是从表中检索所有列而不是列出所有列的简单方法。当您想要查看表中的一些数据时，这特别有用。为此，我们建议使用SELECT * FROM content _ insertion LIMIT 100，这将只给出表格的前 100 行。“LIMIT”总是出现在查询的最后。****

添加条件

****SELECT {column1, column2, …} 
FROM {tablename} 
WHERE {conditional statements}****

当我们想要给我们正在检索的数据添加一些条件时，我们添加“WHERE”子句。在许多情况下，我们将需要使用众所周知的数学符号=，！=，>， =，<= or logics such as AND, OR, NOT, as well as many others. We’ll go through the most common ones in the following examples.

• ****问:给我美国的用户列表

  从国家=‘美国’的用户中选择 userid****

请注意，我们在问题中使用了“美国”而不是“我们”。这是因为当我们查看该表时，US 被存储为 United States。一个很好的澄清问题应该是“美国是存储为‘US’，‘美国’，还是两者都是？”

我们使用符号“=”来表示与右边的符号完全匹配的国家。如果数据存储为“美国”,则不会检索到该行，因为字符串不完全匹配。如果我们想同时检索两者，那么使用子句就可以解决问题(阅读更多关于通配符)。**

• ****问:给我 2018 年加入的用户的唯一国家列表

  从 join_date 在‘2018–01–01’和‘2018–12–31’之间的用户中选择不同的 userid或
  从 join _ date>= ’ 2018–01–01 '和 join _ date<= ’ 2018–12–31 '的用户中选择不同的 userid****

DISTINCT 是检索列的唯一值的有用语法。

****日期函数有很多，但要知道最重要的是 CURRENT_DATE、DATE_ADD、DATE_SUB、DATE_DIFF、DATE_FORMAT。例如，

SELECT userid FROM users，其中 join _ DATE BETWEEN CURRENT_DATE AND DATE _ SUB(’ day '，-6，CURRENT _ DATE)给出一周前到今天之间加入的用户。****

• ****问:给我照片和视频形式的内容列表

  从输入的内容中选择 contentid 照片’，‘视频’)****

****当我们希望用逻辑语句“或”匹配多个字符串时，使用中的或中的【非 。以上查询同

从内容中选择内容 id WHERE(type = ’ photo '或 type = ‘video)
从内容中选择内容 WHERE type！= ‘text’
从输入不在(’ text ')的内容中选择 contentid********

聚合函数

通常我们希望找到一个变量的集合，比如 sum、average、counts、min、max。使用它的一般语法是

**SELECT column1, SUM(column2), COUNT(column3) 
FROM {tablename} WHERE {conditional statements} 
GROUP BY column1**

请注意，我们可以有一个或多个聚合函数，如果我们有任何条件语句，它们总是在 FROM 之后和 GROUP BY 之前。

• **问:每个国家有多少用户？

  选择国家，COUNT(userid)从用户组按国家**

**重命名聚合结果通常是一个好习惯。别名用于为表或表中的列提供临时名称。例如，使用COUNT(userid)作为 number_users

如果我们认为有重复的行，我们可以通过
选择国家，将(不同的用户标识)作为国家用户组中的 number_users 进行计数

• **问:2020–01–03

  点击次数最多的 contentid 是什么？SELECT contentid，COUNT()AS view FROM user _ content _ activity WHERE action = ’ click ’ AND CAST(timestamp AS DATE)= ’ 2020–01–03 ’ GROUP BY contentid
  ORDER BY COUNT()desc 限制 1**

ORDER BY 用于按升序(默认)或降序(通过指定 DESC)对结果集进行排序

• **问:超过 100 个视图的内容有哪些

  选择 contentid，COUNT()作为来自 user_content_activity 的视图，其中 action =‘view’GROUP BY contentid HAVING COUNT()>100**

****当您根据聚合结果提取信息时，使用是一个很好的方法。它总是在 GROUP BY 语句之后。

到目前为止，我们已经学习了使用 SQL 检索数据的基本格式。在第 2 部分中，我们将讨论更多利用关系数据库的有趣内容。

SQL 作为数据分析工具

原文：https://towardsdatascience.com/sql-as-a-data-analysis-tool-a18bf698a9cd?source=collection_archive---------29-----------------------

如何使用 SQL 来执行高效的数据过滤和转换

艾萨克·史密斯在 Unsplash 上拍摄的照片

SQL 是一种用于管理关系数据库中的数据的语言。关系数据库的核心组件是表，它以带有标签的行和列的表格形式存储数据。

我们使用 SQL 的 select 语句从关系数据库中查询数据。就数据转换和过滤操作而言，select 语句具有高度的通用性和灵活性。

从这个意义上说，SQL 可以被认为是一种数据分析工具。使用 SQL 进行数据转换和过滤的好处是，我们只检索我们需要的数据。这比检索所有数据然后应用这些操作更加实用和高效。

在本文中，我们将通过 7 个例子来演示 SQL 如何被用作数据分析工具。例子的复杂性稳步增加，所以如果你到达结尾会更好。

我之前将 Kaggle 上的医疗费用数据集存储在一个名为 insurance 的 SQL 表中。让我们首先通过显示前 5 行来看看这个表。

mysql> select * from insurance
    -> limit 5;

保险表(图片由作者提供)

“*”表示我们想要显示所有列，limit 关键字指定要显示的行数。

该数据集包含一些个人信息和保险费用。

示例 1

我们可以计算吸烟者和不吸烟者的平均收费金额。

mysql> select smoker, avg(charges)
    -> from insurance
    -> group by smoker;+--------+-----------------+
| smoker | avg(charges)    |
+--------+-----------------+
| no     |  8434.268297857 |
| yes    | 32050.231831532 |
+--------+-----------------+

它类似于大熊猫的功能群。我们选择“吸烟者”和“费用”列，并对费用应用平均值函数。group by 语句允许根据 smoker 列中的不同类别来分隔行。因此，我们在结果中得到两个平均值。

如果我们想查看总体平均值，我们只需选择“费用”列。

mysql> select avg(charges) from insurance;+-----------------+
| avg(charges)    |
+-----------------+
| 13270.422265142 |
+-----------------+

示例 2

我们可能还希望看到吸烟者和非吸烟者的数量以及平均费用值。

mysql> select smoker, avg(charges), count(charges)
    -> from insurance
    -> group by smoker;+--------+-----------------+----------------+
| smoker | avg(charges)    | count(charges) |
+--------+-----------------+----------------+
| no     |  8434.268297857 |           1064 |
| yes    | 32050.231831532 |            274 |
+--------+-----------------+----------------+

除了前面的示例，我们还选择了“费用”列的计数。

示例 3

考虑一个案例，我们需要根据性别进一步区分吸烟者和不吸烟者。为了完成这个任务，我们需要将性别列到 group by 语句中。

mysql> select smoker, sex, avg(charges), count(charges)
    -> from insurance
    -> group by smoker, sex;+--------+--------+-----------------+----------------+
| smoker | sex    | avg(charges)    | count(charges) |
+--------+--------+-----------------+----------------+
| no     | female |  8762.297299542 |            547 |
| no     | male   |  8087.204731276 |            517 |
| yes    | female | 30678.996276260 |            115 |
| yes    | male   | 33042.005975283 |            159 |
+--------+--------+-----------------+----------------+

实例 4

select 语句还允许过滤。例如，我们可以针对居住在东南部地区的人运行前面语句中的查询。

mysql> select smoker, sex, avg(charges), count(charges)
    -> from insurance
    -> where region = 'southeast'
    -> group by smoker, sex;+--------+--------+-----------------+----------------+
| smoker | sex    | avg(charges)    | count(charges) |
+--------+--------+-----------------+----------------+
| no     | female |  8440.205551942 |            139 |
| no     | male   |  7609.003586716 |            134 |
| yes    | female | 33034.820716388 |             36 |
| yes    | male   | 36029.839366545 |             55 |
+--------+--------+-----------------+----------------+

我们使用 where 语句来指定过滤条件。请务必注意，在选择查询中，where 语句必须写在 group by 语句之前。

实例 5

我们希望根据吸烟者和儿童列找到不同类别的平均 bmi 值，但只显示平均 bmi 值最高的 3 个类别。

这个示例包括对聚合值进行排序，这可以通过 order by 语句来完成。

mysql> select smoker, children, avg(bmi) 
    -> from insurance
    -> group by smoker, children
    -> order by avg(bmi) desc
    -> limit 3;+--------+----------+------------+
| smoker | children | avg(bmi)   |
+--------+----------+------------+
| no     |        4 | 31.6754545 |
| yes    |        2 | 31.3041818 |
| no     |        2 | 30.8811622 |
+--------+----------+------------+

order by 语句根据给定的列按升序对行进行排序。我们可以通过在列名后使用 desc 关键字将其改为降序。

实例 6

让我们详细说明前面的例子。考虑这样一种情况，我们需要平均 bmi 值高于整体平均值的组。

一种方法是单独计算整体平均值，并将其作为过滤的条件。

mysql> select avg(bmi) from insurance;+------------+
| avg(bmi)   |
+------------+
| 30.6633969 |
+------------+ mysql> select smoker, children, avg(bmi)
    -> from insurance
    -> group by smoker, children
    -> having avg(bmi) > 30.6633969;+--------+----------+------------+
| smoker | children | avg(bmi)   |
+--------+----------+------------+
| no     |        2 | 30.8811622 |
| no     |        3 | 30.7384322 |
| no     |        4 | 31.6754545 |
| yes    |        1 | 30.8743443 |
| yes    |        2 | 31.3041818 |
+--------+----------+------------+

值得注意的是，当我们基于聚合值进行过滤时，我们使用 having 语句而不是 where 语句。

第二种选择是将这两个查询组合成一个嵌套查询。

mysql> select smoker, children, avg(bmi)
    -> from insurance
    -> group by smoker, children
    -> having avg(bmi) > (
    -> select avg(bmi) from insurance
    -> );+--------+----------+------------+
| smoker | children | avg(bmi)   |
+--------+----------+------------+
| no     |        2 | 30.8811622 |
| no     |        3 | 30.7384322 |
| no     |        4 | 31.6754545 |
| yes    |        1 | 30.8743443 |
| yes    |        2 | 31.3041818 |
+--------+----------+------------+

我更倾向于第二种选择，因为第一种更容易出错。

例 7

这个例子比上一个稍微复杂一点。我们不是只显示 bmi 值高于平均值的行，而是希望创建一个新列来指示一行是否高于平均值。

我们将在嵌套查询中使用 case-when 语句。

mysql> select smoke, children, avg(bmi),
    -> (case when avg(bmi) > (
    -> select avg(bmi) from insurance) then "more than avg"
    -> else "less than avg" end) as compared_to_avg
    -> from insurance
    -> group by smoker, children;

在显示结果之前，让我们试着理解查询中的每一步是做什么的。

我们选择了与上一个示例相似的三列。第四列是用 case-when 语句创建的。新列根据行的平均 bmi 值与总体平均 bmi 值的比较，取值“高于平均值”或“低于平均值”。

总体平均 bmi 值是通过使用 case-when 语句中的嵌套 select 语句来计算的。下面是这个查询的结果。

+--------+----------+------------+-----------------+
| smoker | children | avg(bmi)   | compared_to_avg |
+--------+----------+------------+-----------------+
| no     |        0 | 30.5519499 | less than avg   |
| no     |        1 | 30.5648859 | less than avg   |
| no     |        2 | 30.8811622 | more than avg   |
| no     |        3 | 30.7384322 | more than avg   |
| no     |        4 | 31.6754545 | more than avg   |
| no     |        5 | 30.2700000 | less than avg   |
| yes    |        0 | 30.5436957 | less than avg   |
| yes    |        1 | 30.8743443 | more than avg   |
| yes    |        2 | 31.3041818 | more than avg   |
| yes    |        3 | 30.5206410 | less than avg   |
| yes    |        4 | 29.3066667 | less than avg   |
| yes    |        5 | 18.3000000 | less than avg   |
+--------+----------+------------+-----------------+

结论

我们已经介绍了一些查询示例来展示 SQL 的数据分析能力。

我认为 SQL 是数据科学家或分析师的必备技能。我们至少应该知道如何查询关系数据库。在检索数据时执行数据转换和操作操作的复杂查询有可能节省内存。它们也减轻了以后需要完成的任务。

感谢您的阅读。如果您有任何反馈，请告诉我。

SQL 挑战:案例研究

原文：https://towardsdatascience.com/sql-challenge-case-study-7bd75eb864ac?source=collection_archive---------1-----------------------

顶级技术公司提出的 SQL 挑战的逐步演练

艾米丽·莫特在 Unsplash 上的照片

在的上一篇文章中，我谈到了如何使用框架来解决 SQL 挑战。今天我就来做一个这个挑战的演练，2021 年 2 月微软问的。在你继续阅读这篇文章之前，我强烈建议你尝试自己解决这个问题。你练习解决 SQL 问题越多，你就能越好地解决它们。好吧，让我们开始吧！

阅读提示

这个问题要求我们:

根据至少有 50%的事件发生在以下列表中的用户数，选择最受欢迎的 client_id:“收到视频通话”、“发送视频通话”、“收到语音通话”、“发送语音通话”。

在我开始研究这个问题之前，我想想象一下数据集中的信息是如何出现的。让我们运行一个简单的 SELECT 语句来检查存储在表 fact_events 中的数据。

SELECT *
FROM fact_events
LIMIT 10

该查询返回以下输出:

作者截图( StrataScratch 网站)

出于好奇，我想知道所提供的列表中的所有值是否都出现在数据集中:

SELECT 
    event_type
FROM fact_events
WHERE event_type IN ('video call received',
                    'video call sent', 'voice call received',
                    'voice call sent')
GROUP BY event_type

作者图片

看起来只包括了 4 个值中的 2 个。尽管其他两个值没有出现在当前数据集中，但它们可能会在数据追加到表中时出现。你不能假设数据会保持不变。因此，让我们在过滤器中保留所有 4 个值。

太好了！我现在对我正在做的事情有了更好的了解。下一步是分析提示，以便我们可以提出一个策略来应对这一挑战。

笔和纸

对于这一步，我喜欢把我的想法写在一张纸上📝然而，请随意使用你的电脑、iPad 或其他最适合你的设备。这里，我们需要分析提示，以准确理解要求我们做什么。

作者图片

在这种情况下，提示似乎分为三个部分:

1. 从上面的列表中找到发生了 50%事件的用户
2. 计算步骤(1)中找到的用户数量
3. 根据步骤(2)选择用户数量最多的客户端 id

我还会做如下假设:

• 我不需要使用 customer_id 和 event_id 列
• 时机并不重要
• 我用给定列表中事件的数量除以所有事件的数量来计算 50%。
• 我在统计用户总数，即使他们在列中出现多次。提示没有指定通过识别用户在数据集中出现的不同次数来选择最流行的 client_id。

在面试的这一点上，你应该花时间去理解挑战，并向面试官传达最初的想法。此外，如果你有任何问题，请随时向面试官寻求澄清。没有面试官在场的情况下，让我们进入下一步吧！

第一步

首先，让我们在下面的列表中获取具有 50%或更多事件的用户:“收到视频呼叫”、“发送视频呼叫”、“收到语音呼叫”和“发送语音呼叫”为了得到百分比，我们将在一个 CASE 语句中使用一键编码，将列表中的值返回为 1，其他值返回为 0。CASE 语句总和将是分子。分母是 event_type 列中所有元素的计数。

CAST(SUM(
            CASE
                WHEN event_type IN ('video call received',
                    'video call sent', 'voice call received',
                    'voice call sent')
                    THEN 1
                ELSE 0
            END) AS FLOAT)  / COUNT(event_type)

接下来，我们将在 GROUP BY/HAVING 子句中包装条件。记住，我们需要用列表中至少 50%的事件对 user_id 进行过滤。为此，我们需要执行聚合，根据条件进行过滤，最后按 user_id 进行分组。

SELECT  
    user_id
FROM fact_events
GROUP BY user_id
HAVING CAST(SUM(
            CASE
                WHEN event_type IN ('video call received',
                    'video call sent', 'voice call received',
                    'voice call sent')
                    THEN 1
                ELSE 0
            END) AS FLOAT)  / COUNT(event_type) >= 0.5

完成第一步后，我们得到以下输出:

作者图片

第二步

现在我们知道只有 2 个用户拥有列表中至少 50%的事件，让我们通过 client_id 和 user_id 找出用户的数量。

SELECT 
    client_id, 
    user_id, 
    COUNT(user_id)
FROM fact_events
WHERE user_id in 
        (SELECT  
            user_id
        FROM fact_events
        GROUP BY user_id
        HAVING CAST(SUM(
            CASE
                WHEN event_type IN ('video call received',
                    'video call sent', 'voice call received',
                    'voice call sent')
                    THEN 1
                ELSE 0
            END) AS FLOAT)  / COUNT(event_type) >= 0.5)
GROUP BY 
    client_id, 
    user_id
ORDER BY COUNT (user_id) DESC

输出已经指向了我们的答案。请注意，client_id“桌面”有 7 + 4 = 11 个用户，而“移动”只有 3 个。

作者图片

第三步

在最后一步，我们将清理上面的输出，只返回拥有最多用户的 client_id。这里不需要对 SQL 脚本进行太多的修改。我没有按 client_id 和 user_id 分组，而是只按 client_id 分组。我还确保限制返回最多用户数的 client_id 的行数。如果不限制选择，在最终输出中还会返回“mobile”。

--final querySELECT 
    client_id
FROM fact_events
WHERE user_id in 
        (SELECT  
            user_id
        FROM fact_events
        GROUP BY user_id
        HAVING CAST(SUM(
            CASE
                WHEN event_type IN ('video call received',
                    'video call sent', 'voice call received',
                    'voice call sent')
                    THEN 1
                ELSE 0
            END) AS FLOAT)  / COUNT(event_type) >= 0.5)
GROUP BY 
    client_id
ORDER BY COUNT(user_id) DESC
LIMIT 1

最终输出:

作者图片

结论

我希望你喜欢这个演练！有大量的资源供你练习面试问题。一些最受欢迎的网站是 Leetcode、StrataScratch 和 InterviewQuery，但我相信你可以找到许多其他网站，为你赢得 DA 面试做好准备。别忘了在下面的评论中分享反馈或提出问题！

本帖最后编辑于 2021 年 10 月 30 日。这里表达的观点仅属于我自己，并不代表我的雇主的观点。

SQL —删除到另一个表中

原文：https://towardsdatascience.com/sql-delete-into-another-table-b5b946a42299?source=collection_archive---------15-----------------------

在一条语句中删除一个表中的记录，并将它们插入到另一个表中

把这个虎尾兰从一个锅里删除到另一个锅里！(图片由像素上的 Cottonbro 拍摄)

“我为什么需要这样的查询？”一个删除成语句是有利的，主要有两个原因:

• 语句是原子；要么两者都发生，要么什么都不发生，即如果删除或插入失败，它将回滚更改。
• 它更便宜:你的数据库只需要查找一次记录。或者，执行单独的插入和删除需要两次查找
• 吹牛的权利:给你的老板和同事留下深刻印象

深信不疑？“给我看一些代码！”。好的，但是首先我们必须设置一些表来演示查询。让我们编码:

设置:创建一些包含数据的表

作为一个例子，我们假设我们有一个有很多流程的公司。每个进程都将其错误记录到一个名为 ErrorMessages 的表中，以便进行跟踪。一旦一个错误被解决，过期或者不再相关，我们可以从错误消息中删除它。不过，我们希望保存错误信息，这样我们以后就可以分析哪个进程失败得最多。为此，我们将创建 ErrorMessageBackup。我们的目标是将 ErrorMessages 删除到 ErrorMessageBackup 表中。让我们首先创建我们的表，并将一些数据插入到我们的错误消息中。

用一些记录创建我们的表

通过执行这些查询，我们看到 ErrorMessages 表的内容如下所示:

我们的错误消息表

执行我们的查询

这是魔法开始的地方。我们想从数据库中删除 404 错误。让我们检查一下我们的查询，然后再深入一点。

我们的删除查询

我们的目标是 Id 为 1 的错误消息。我们将它保存在@targetId 变量中。然后我们从 ErrorMessages 表中删除，过滤这个 Id。使用输出，我们可以访问已经删除的列。我们将这些列以及一个额外的列输出到 ErrorMessageBackup 表中。

就是这样！一个简单、安全的查询，允许您一次执行两个查询。

结论

通过这个查询，我们可以执行原子操作，从一个表中删除记录，并允许我们将它们插入到另一个表中。另请查看:

• 更新到另一个标签页 le
• 使用交易撤销查询
• 在一条语句中插入、删除和更新
• 更新选择一批记录
• 版本控制你的数据库

这篇 文章向您展示了如何更新到表格中。编码快乐！

—迈克

页（page 的缩写）学生:比如我正在做的事情？跟我来！

SQL 数字营销分析

原文：https://towardsdatascience.com/sql-digital-marketing-analysis-be52c14e39aa?source=collection_archive---------8-----------------------

MySQL Workbench 中的一种方法，修改了应用于营销运营和常见分析请求的一些主要 SQL 查询

图片作者:gon alo GUI mares Gomes

介绍

SQL marketing analytics 设置的主要目标是帮助 mediumcompany *【虚构】*的营销团队根据他们的表现和收入，计算出不同付费流量细分市场的合理报价。

在 MySQL Workbench 环境中，我们将使用 SQL 数据库 mediumcompany ，具体来说，两个表格“网站 _ 会话”和“订单”，帮助我们了解流量来自哪里，以及流量和转化率方面的表现如何。我们还将调整出价，以优化营销预算。

连同以下选定的 5 个常见任务，我们将编写简单的 SQL 查询，但功能强大到足以回答几个运营营销问题。我们将处理关于连接表、计算转换率、设备性能和数量趋势的例子。

From: CEO
主题:网站流量细分
日期:2012 年 4 月 12 日

我们已经上线快一个月了，我们开始创造销售。
能不能帮我了解一下我们 网站会话 的大头是从哪里来的，通过**？
我想看看* 按来源、 和所指领域细分。*

—思考 我们要统计按 utm_source、utm_campaign、utm_referer 分组的 website_session_id(会话数)。
这是我们的预期结果表:

图片作者。

要解决这个问题，我们只需要 website_sessions 的表。

图片作者。

*SELECT * FROM mediumcompany.website_sessions;*

图片作者。

*USE mediumcompany; -- set global use of the db.SELECT utm_source, utm_campaign, http_referer,
COUNT(DISTINCT website_session_id) AS nb_sessionsFROM website_sessionsWHERE created_at < ‘2012–04–12’GROUP BY utm_source, utm_campaign, http_refererORDER BY nb_sessions DESC;*

—提示 根据 SELECT 语句中的每个位置更快地分组和排序:

*SELECT 
(...)
GROUP BY 1, 2, 3
ORDER BY 4 DESC;*

—输出

图片作者。

—了解 从从网站 _ 会话表中选择来源、活动和推荐人开始，直到 2012 年 4 月 12 日。

*SELECT utm_source, utm_campaign, http_refererFROM website_sessionsWHERE created_at < “2012–04–12”*

图片作者。

然后计算使用 GROUP BY 子句的会话数，合计每个组合的值。按降序排列会话(最高在顶部)。

*SELECT (...)
COUNT(DISTINCT website_session_id) AS nb_sessions
(...)
GROUP BY 1, 2, 3ORDER BY 4 DESC;*

图片作者。

我们可以得出结论， gsearch 和 nonbrand 在截至 2012 年 4 月 12 日的这段时间内，比任何其他活动或来源带来的流量(会话)都多。

发件人:市场总监
主题:g 搜索转换
日期:2012 年 4 月 14 日

看起来g search non brand是我们的主要流量来源，但我们需要了解这些会议是否在推动销售。
能否请您 计算一下从会话到订单的转化率(CVR) ？基于我们为点击支付的费用，我们需要至少 4%的 CVR 来让这些数字起作用。

—思考 我们想统计截止到 2012 年 4 月 14 日的 gsearch 和 nonbrand 的会话和订单总数。
关于转化率，我们简单的按时段划分订单。
这是我们的预期结果表:

图片作者。

为了解决这个问题，我们需要 website_sessions(我们已经从前面的作业中知道了)和 orders 表(见下文)。

图片作者。

*SELECT * FROM mediumcompany.orders;*

图片作者。

我们将执行“website_sessions”左连接“orders ”,因为我们希望看到 website_sessions 表中的所有 website_session_id，并查看 orders 表中何时有匹配的订单。我们将在 website_session_id 上同时加入。

*USE mediumcompany;SELECT
COUNT(DISTINCT ws.website_session_id) AS sessions,
COUNT(DISTINCT o.order_id) AS orders,
(COUNT(DISTINCT o.order_id) / COUNT(DISTINCT ws.website_session_id)) *100 AS session_to_order_conv_rateFROM website_sessions ws
LEFT JOIN orders o 
ON o.website_session_id = ws.website_session_idWHERE ws.created_at < ‘2012–04–14’ 
AND ws.utm_source = ‘gsearch’ 
AND ws.utm_campaign = ‘nonbrand’;*

—提示
按 2 位小数对‘session _ to _ order _ conv _ rate’进行舍入:

*SELECT
(...)
ROUND((COUNT(DISTINCT o.order_id) / COUNT(DISTINCT ws.website_session_id)) * 100 **,2**) AS session_to_order_conv_rate*

—输出

图片作者。

—了解 首先，通过在 website_session_id 上使用左连接来连接两个表(website_sessions 和 order_id)，选择 website_session_id 和 order _ id。

*SELECT 
ws.website_session_id AS sessions,
o.order_id AS orders

FROM website_sessions ws
LEFT JOIN orders o 
ON o.website_session_id = ws.website_session_idWHERE ws.created_at < “2012–04–14”
AND ws.utm_source = ‘gsearch’ 
AND ws.utm_campaign = ‘nonbrand’;*

图片作者。

统计会话和订单的总数。

*SELECT 
COUNT(DISTINCT ws.website_session_id) AS sessions,
COUNT(DISTINCT o.order_id) AS orders
(...)*

图片作者。

通过将订单除以会话来计算转化率(比率为100)。*

*SELECT (...)
(COUNT(DISTINCT o.order_id) / COUNT(DISTINCT ws.website_session_id)) *100 AS session_to_order_conv_rate 
(...)-- Round **2** decimals:
ROUND((COUNT(DISTINCT o.order_id) / COUNT(DISTINCT ws.website_session_id)) *100 **,2**) AS session_to_order_conv_rate*

图片作者。

2.9%的转换率意味着 gsearch 非品牌出价没有像预期的那样推动销售，投资没有以最佳方式发挥作用。

发件人:市场总监
主题:g 搜索量趋势
日期:2012 年 5 月 10 日

基于您的转化率分析，我们于 2012 年 4 月 15 日投标g search non brand。
能不能调出 gsearch 非品牌趋势交易量，按周细分， 看看竞价变化有没有导致交易量下降？

—思考
我们想按时间顺序按周统计 gsearch 非品牌会话，直到 2012 年 5 月 10 日。要做到这一点，按年和周将它们分组，找出每周的第一天或最小的一天。最后，看看交易量是否从 4 月 15 日开始下降。
这是我们的预期结果表:

图片作者。

*USE mediumcompany;SELECT 
MIN(DATE(created_at)) as week_started_at,
COUNT(DISTINCT website_session_id) AS sessionsFROM website_sessionsWHERE created_at < ‘2012–05–10’ 
AND utm_source = ‘gsearch’ 
AND utm_campaign = ‘nonbrand’GROUP BY YEAR(created_at), 
WEEK(created_at);*

—提示
我们实际上可以按 SELECT 语句中没有包含的列(年、周)进行分组。

—输出

图片作者。

—了解
先按年、周、日选择分组，统计场次。

*SELECT 
YEAR(created_at) AS yr,
WEEK(created_at) AS wk,
DATE(created_at) AS dt,
COUNT(DISTINCT website_session_id) AS sessions
(...)
GROUP BY 1, 2, 3*

图片作者。

找到一周中的第一天或最小的一天。因为我们继续按年和周对它们进行分组，但没有在 SELECT 子句中添加，所以日期的粒度将变得更粗。

*SELECT 
MIN(DATE(created_at)) as week_started_at,
COUNT(DISTINCT website_session_id) AS sessions
(...)
GROUP BY YEAR(created_at), WEEK(created_at);*

图片作者。

在 4 月 15 日之后，gsearch nonbrand 的流量明显下降。

**发自:市场总监
主题:g 搜索设备级性能 日期:2012 年 5 月 11 日

前几天，我试图在手机上使用我们的网站，但体验并不好。
您能否按设备类型从会话到订单获取 转换率？ 如果台式机的性能优于移动设备，我们或许可以提高台式机的价格，以获得更大的销量？

—思考 我们想统计截至 2012 年 5 月 11 日期间 gsearch 和 nonbrand 的会话和订单总数。
关于转化率，我们简单的按时段划分订单。然后，我们按设备类型分组。这是我们的预期结果表:

图片作者。

*USE mediumcompany;SELECT 
ws.device_type,
COUNT(DISTINCT ws.website_session_id) AS sessions,
COUNT(DISTINCT o.order_id) AS orders,
ROUND((COUNT(DISTINCT o.order_id) / COUNT(DISTINCT ws.website_session_id)) * 100, 2) AS session_to_order_conv_rtFROM website_sessions ws
LEFT JOIN orders o 
ON o.website_session_id = ws.website_session_idWHERE
ws.created_at < ‘2012–05–11’
AND ws.utm_source = ‘gsearch’
AND utm_campaign = ‘nonbrand’GROUP BY 1;*

—输出

图片作者。

—理解
从统计会话数、订单数，以及转化率开始。

*SELECT
COUNT(DISTINCT ws.website_session_id) AS sessions,
COUNT(DISTINCT o.order_id) AS orders,
(COUNT(DISTINCT o.order_id) / COUNT(DISTINCT ws.website_session_id)) *100 AS session_to_order_conv_rate
(...)*

图片作者。

呼叫和分组依据按设备细分。

*SELECT 
**ws.device_type,**
COUNT(DISTINCT ws.website_session_id) AS sessions,
COUNT(DISTINCT o.order_id) AS orders,
ROUND((COUNT(DISTINCT o.order_id) / COUNT(DISTINCT ws.website_session_id)) * 100, 2) AS session_to_order_conv_rt
(...)
**GROUP BY 1**;*

图片作者。

台式机的表现要好得多，因此我们应该提高这类设备的报价，以增加销量。

来自:市场总监
主题:g 搜索设备级趋势
日期:2012 年 6 月 9 日

在您对转化率进行设备层面的分析后，我们意识到台式机表现不错，因此我们在 2012 年 5 月 19 日对我们的 gsearch 非品牌台式机活动进行了竞价。
你能调出 桌面和移动的每周趋势 这样我们就能对销量产生影响了？
你可以用2012–04–15*直到投标变更为基线。*

—思考
我们希望选择并过滤设备，以统计设备类型为“桌面”或“移动”的次数，然后按年和周对时间序列进行转换和分组，以查看周开始日期。
最后，检查桌面会话的数量是否从 4 月 15 日到 6 月 9 日有所下降。这是我们的预期结果表:

图片作者。

*USE mediumcompany;SELECT 
MIN(DATE(created_at)) AS week_start_date,
COUNT(DISTINCT CASE WHEN device_type = ‘desktop’ THEN website_session_id ELSE NULL END) AS desktop_sessions,
COUNT(DISTINCT CASE WHEN device_type = ‘mobile’ THEN website_session_id ELSE NULL END) AS mobile_sessionsFROM website_sessionsWHERE created_at BETWEEN ‘2012–04–15’ AND ‘2012–06–09’ 
AND utm_source = ‘gsearch’ AND utm_campaign = ‘nonbrand’GROUP BY YEAR(created_at), WEEK(created_at)*

—输出

图片作者。

—了解 我们将从选择 device_type 开始，并转换年、周和日期。

*USE mediumcompany;SELECT
**device_type**,
**YEAR(created_at)** AS yr,
**WEEK(created_at)** AS wk,
**DATE(created_at)** AS dt

FROM website_sessionsWHERE created_at BETWEEN ‘2012–04–15’ AND ‘2012–06–09’ AND utm_source = ‘gsearch’ AND utm_campaign = ‘nonbrand’;*

图片作者。

接下来，根据设备类型(桌面或移动)过滤网站会话 id。

*USE mediumcompany;SELECT
device_type,
YEAR(created_at) AS yr,
WEEK(created_at) AS wk,
DATE(created_at) AS dt,**CASE WHEN device_type = ‘desktop’ THEN website_session_id ELSE NULL END AS desktop_session_id,
CASE WHEN device_type = ‘mobile’ THEN website_session_id ELSE NULL END AS mobile_session_id**FROM website_sessionsWHERE created_at BETWEEN ‘2012–04–15’ AND ‘2012–06–09’ AND utm_source = ‘gsearch’ AND utm_campaign = ‘nonbrand’*

图片作者。

接下来，我们将删除“device_type”列，因为我们希望汇总并计算 device _ type 为“desktop”或“mobile”的次数。

不要忘记按年、周和日期分组，这样计数才有意义。

*USE mediumcompany;SELECT
YEAR(created_at) AS yr,
WEEK(created_at) AS wk,
DATE(created_at) AS dt,**COUNT(**CASE WHEN device_type = ‘desktop’ THEN website_session_id ELSE NULL END**)** AS desktop_sessions,
**COUNT(**CASE WHEN device_type = ‘mobile’ THEN website_session_id ELSE NULL END**)** AS mobile_sessionsFROM website_sessionsWHERE created_at BETWEEN ‘2012–04–15’ AND ‘2012–06–09’ AND utm_source = ‘gsearch’ AND utm_campaign = ‘nonbrand’**GROUP BY 1,2,3***

图片作者。

最后，从 SELECT 语句中删除“yr”和“wk ”(但仍按它们分组),以获得更大的粒度。

对于日期列“dt ”,将日期转换并设置为第一(最小)天，按年“yr”和周“wk”分组，以便最小天等于每个分组周的第一天。

不要忘记按年和周分组，这样计数才有意义。

*USE mediumcompany;SELECT
**MIN(**DATE(created_at)**)** AS week_start_date,
COUNT(CASE WHEN device_type = ‘desktop’ THEN website_session_id ELSE NULL END) AS desktop_session_id,
COUNT(CASE WHEN device_type = ‘mobile’ THEN website_session_id ELSE NULL END) AS mobile_session_idFROM website_sessionsWHERE created_at BETWEEN ‘2012–04–15’ AND ‘2012–06–09’ AND utm_source = ‘gsearch’ AND utm_campaign = ‘nonbrand’**GROUP BY YEAR(created_at), WEEK(created_at)***

图片作者。

回答问题“了解桌面和移动设备的每周趋势，这样我们就可以看到对销量的影响？你可以将 2012 年 4 月 15 日之前的出价作为基准。”，gsearch 非品牌桌面的流量从 4 月 15 日至今有所增加。

结论

流量来源分析旨在了解客户来自哪里，以及哪些渠道带来了最高质量的流量。

竞价优化分析是关于了解付费流量各个细分市场的价值，以便我们优化营销预算。

从营销人员的角度来看，目标是产生流量，为网站带来更多的流量，为企业赚更多的钱。

在分析方面，任务是分析流量来源和竞价优化，以了解付费流量各部分的价值，改善营销预算。

*</15-business-questions-about-mobile-marketing-campaigns-roas-return-on-ad-spend-ff636a8095b6>

感谢阅读。*