 ### Taha Ashtiani

Searching for EV in the tails.

# Super Bowl

I was browsing around david robinson’s tidytuesday posts, and found the one on superbowl ads and decided to do it with pandas. Checkout the R notebook here. I enjoyed the analysis and learned couple of new cool pandas tricks. hope you enjoy! Also here’s the jupyter notebook:

ads = pd.read_csv("https://raw.githubusercontent.com/rfordatascience/tidytuesday/master/data/2021/2021-03-02/youtube.csv") ## Count of ads over the years

Let’s start by looking into ads total count of each brand.

ads.brand.value_counts().plot(kind="bar",figsize=(10,5)) I want to see the histogram of ads over the years, for each brand. the BudLight one looks interesting. I wonder if it has something to do with pushing hard on like a brand awareness type budget(compared to Doritos for example). I googled “how much did bud light spend on super bowl commercials?” and here’s the answer: “Between 2016 and 2020, Bud Light has spent \$61.4 million on Super Bowl airtime”

g = sns.FacetGrid(ads,col="brand", height=4, col_wrap=3, aspect=1)
g.map_dataframe(sns.histplot, "year",binwidth=1)
g.set_axis_labels("year", "Count")

for ax in g.axes.flatten():
ax.tick_params(labelbottom=True) ## Distribution of view, like, dislike, comment

Now let’s look into the histogram of [“view_count”,”like_count”,”dislike_count”,”comment_count”]. this helps us get a sense of the order and distribution of numbers. It’s interesting how like_count has a normal dist look to it, but disklike is heavily skewed to the left. an oversimplified explanation for this might be that there might be a group of dislikers, and disliking behaviour is assigned to them not the whole population. while that’s not the case with liking a post, which is the easiest and most common way of engaging with a post.

metric_counts = ads[["view_count","like_count","dislike_count","comment_count"]]
metric_counts = metric_counts[((metric_counts>0).all(axis=1))]

fig , axes = plt.subplots(2,2,figsize=(15,10))

for i,ax in enumerate(axes.reshape(-1)):
col = metric_counts.columns[i]
ax.set_xscale('log')
sns.histplot(data=metric_counts, x=col,binwidth=0.2,ax=ax) Now let’s see the effect of category of ads on viewership. we have 6 columns for 6 categories: funny show_product_quickly patriotic celebrity danger animals let’s boxplot them and look at the differences in their median.


import numpy as np

view_count_categories = {}
for category in categories:
view_count_categories = pd.DataFrame(view_count_categories).T.reset_index()



## “funny” category

median of the viewship is almost always higher for the funny ads. this reminds me of jonah berger’s book “contagious”.

sns.set_style("darkgrid")
fig , ax = plt.subplots(figsize=(15,20))
ax.set_xscale('log')
_ = sns.boxplot(ax=ax, x="view_count", y="brand", hue="funny", data=ads, palette="Set3",order=order) ## what sells more?

“patriotic” and “celebrity” have been on a rise, while “funny”, “show product”, “use_sex” have been all trending down.

category_year = pd.DataFrame()
for category in categories:

category_year.fillna(0,inplace=True)
category_year.index = pd.to_datetime(category_year.index,format='%Y')
category_year = category_year.resample("2Y").sum()
# category_year = category_year.rolling(3).sum()

category_year = category_year.apply(lambda row: row/row.sum(), axis=1).mul(100).round()
category_year = category_year.reset_index().rename({"index":"year"})
category_year = pd.melt(category_year, id_vars=['year'], value_vars=categories)


sns.set_style("darkgrid")
fig , axes = plt.subplots(3,3,figsize=(15,10))

for i,ax in enumerate(axes.reshape(-1)):
if i < len(categories) :
cat = categories[i]
sns.lineplot(data=category_year[category_year.variable == cat], x="year",y="value" ,ax=ax)
ax.set_ylabel("% of total views")
ax.set_xlabel(cat) ## % of brands ads have this quality

I love this one:

• Doritos uses a lot of “funny”+”show_product_quickly”
• NFL is all “patriotic” and “celebrity”
• Budweiser has the highest % of “danger”
category_brand = pd.DataFrame()
for category in categories:

category_brand = category_brand.apply(lambda row: row/row.sum(), axis=1).mul(100).round()
category_brand.fillna(0,inplace=True)
category_brand fig , axes = plt.subplots(3,3,figsize=(15,10))

for i,ax in enumerate(axes.reshape(-1)):
if i < len(categories) :
cat = categories[i]
category_brand[cat].sort_values().plot(kind="barh",ax=ax)
ax.set_ylabel("% of total views")
ax.set_xlabel(cat) ## Fingerprint of ads for different brands

very nice way of seeing what each brand is about. in other words brand personality.

category_brand = category_brand.T

fig , axes = plt.subplots(5,2,figsize=(15,25))

for i,ax in enumerate(axes.reshape(-1)):
if i < len(category_brand.columns) :
brand = category_brand.columns[i]
category_brand[brand].sort_values().plot(kind="barh",ax=ax)
ax.set_xlabel(brand) Checkout the like/dislike on this one from budwiser: https://www.youtube.com/watch?v=7ZmlRtpzwos

like_dislike = ads[["brand","year","title","description","view_count","like_count","dislike_count"]].copy()
like_dislike_total = like_dislike["dislike_count"] + like_dislike["like_count"]
like_dislike["dislike_ratio"] = round(like_dislike["dislike_count"] / (like_dislike_total),3).mul(100)
like_dislike.sort_values("dislike_ratio",ascending=False) like_dislike_filtered = like_dislike[like_dislike_total > 1000]

sns.set_style("darkgrid")
sns.set(font_scale=1.3)
fig , ax = plt.subplots(figsize=(20,30))
ax.set_xscale('log')
sns.scatterplot(data=like_dislike_filtered,x="view_count",y="dislike_ratio",hue="brand",s=200,ax=ax)

# let's annotate it with titles
a = pd.concat({'x': like_dislike_filtered["view_count"],
'y': like_dislike_filtered["dislike_ratio"],
'val': like_dislike_filtered["title"] + " ("+like_dislike_filtered["brand"]+")" }, axis=1)
for i, point in a.iterrows():
ax.text(point['x']+.1, point['y']+0.1, str(point['val']))

_ = plt.legend(bbox_to_anchor=(1.05, 1), loc=2,fontsize=15) ## What brands tend to produce polarizing ads in terms of Youtube likes?

like_dislike_filtered = like_dislike[like_dislike_total > 500]

sns.set_style("darkgrid")
fig , ax = plt.subplots(figsize=(10,10))
ax.set_xscale('log')
order = like_dislike_filtered.groupby(by=["brand"]).dislike_ratio.mean().sort_values(ascending=False).index.values
sns.boxplot(ax=ax, x="dislike_ratio", y="brand", data=like_dislike_filtered, palette="Set3",order=order)

<AxesSubplot:xlabel='dislike_ratio', ylabel='brand'> 