In [1]:
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
%matplotlib inline
In [10]:
df = pd.read_csv("analytics.csv",thousands=",",encoding="ISO-8859-1")
In [12]:
df
Out[12]:
Page Title
Editorial_Type
Pageviews
Unique Pageviews
Avg. Time on Page
Entrances
Bounce Rate
% Exit
Page Value
0
The Secret Italian Sauce You Need to Make Righ...
unset
149295
128667
0:02:58
127987
50.30%
73.96%
<$0.01
1
Your Drunk Aunt Was Right: The Hot Toddy Is Th...
unset
94867
75941
0:02:56
75090
35.02%
60.57%
$0.00
2
Woman Finds why Drinking Your own Alcohol on a...
travel
94799
83600
0:02:02
82374
47.96%
57.81%
$0.00
3
This Cult Black Raspberry Chocolate Chip Ice C...
product_recommendation
38396
36183
0:00:23
1396
40.01%
65.07%
$0.00
4
3 Things Your Female Bartender Doesn't Want to...
opinion_editorial
25461
24644
0:02:57
24270
52.74%
61.10%
$0.00
5
Johnny Depp Spends $30,000 a Month on Wine | V...
humor
21751
18653
0:01:41
17836
41.72%
50.24%
$0.00
6
The 50 Best Wines of 2016 | VinePair
unset
19232
16543
0:04:31
14603
46.07%
68.32%
$0.00
7
The Super Bowl Drinking Game for People Who Do...
humor
18109
13752
0:02:20
13445
57.98%
62.97%
$0.00
8
11 Exceptional Tequilas Under $50 | VinePair
unset
17054
13139
0:02:57
12867
29.73%
57.28%
$0.00
9
The Drink To Convince A Friend To Break Up Wit...
unset
16917
14024
0:00:29
1498
50.00%
48.49%
$0.00
10
Tom And Jerry, America's Forgotten Christmas C...
unset
16815
16100
0:02:45
16066
58.69%
73.68%
$0.00
11
Wine 101 | Learn About Wine | Our Wine Guide
unset
16781
10314
0:01:16
8237
33.87%
33.88%
$0.00
12
The 10 Best Bottles Of Champagne For New Year'...
product_recommendation
15522
14501
0:02:06
14194
30.14%
54.39%
$0.00
13
7 Japanese Whiskies You Should Buy Right Now |...
product_recommendation
14739
13071
0:01:18
4153
36.07%
62.36%
$0.00
14
We Aged Two Buck Chuck; Here's What it Tasted ...
til
13003
12594
0:05:11
12118
55.18%
64.92%
$0.00
15
The History of the Shotski | VinePair
history
12765
11709
0:01:56
5991
47.19%
72.00%
$0.00
16
The People Calling Expensive Wine a Rip-Off Ar...
opinion_editorial
11607
10756
0:07:19
9973
42.00%
75.95%
$0.00
17
Here's Your Drink Pairing for Your January Hor...
humor
11471
10450
0:02:51
10314
55.45%
79.52%
$0.00
18
Your All-American BeerÛªs Stance on Immigrant...
til
9803
9054
0:00:35
102
0.00%
65.62%
$0.00
19
The 10 Best Whiskies To Use In An Old Fashione...
product_recommendation
9599
6910
0:02:43
6876
35.65%
58.51%
$0.00
20
The Kind of Drunk You'll Be at Your Wedding Ba...
unset
9191
8952
0:01:56
8510
18.40%
38.15%
$0.00
21
MAP: This Is How Your Corona Is Made | VinePair
visualization
7897
7046
0:02:43
4289
36.72%
66.38%
$0.00
22
The 7 Best Liquors to Drink On the Rocks | Vin...
product_recommendation
7591
6059
0:02:11
4834
37.32%
49.32%
$0.00
23
How To Saber A Bottle Of Champagne [Infographi...
visualization
7250
6093
0:00:28
1838
40.01%
45.08%
$0.00
24
How the Church Saved the Wine Industry During ...
history
7114
6399
0:00:59
238
62.50%
48.81%
$0.00
25
Your Super Bowl Drinking Playbook [Flowchart] ...
visualization
7080
5889
0:00:34
1021
22.56%
36.54%
$0.00
26
The Cocktail Industry Has a Sexual Assault Pro...
long_form
6672
6195
0:08:46
5685
51.47%
85.72%
$0.00
27
Your Inauguration Day Drinking Guide [Infograp...
visualization
6570
5685
0:00:51
4970
29.94%
50.26%
$0.00
28
Can You Buy Alcohol on New Year's Day & New Ye...
til
6535
5923
0:03:33
5855
22.29%
82.82%
$0.00
29
Cocktail Ingredients That Will Cure Your Cold ...
health
6467
5719
0:01:11
3268
43.31%
58.95%
$0.00
...
...
...
...
...
...
...
...
...
...
574
Champagne Ice Cube Mimosas - Recipe - Video
unset
34
34
0:00:00
34
100.00%
100.00%
$0.00
575
Cheat Sheet: Everything You Need To Know About...
visualization
34
34
0:00:09
0
0.00%
0.00%
$0.00
576
Chivas Regal Scotch Whisky Brand Profile | Vin...
brand_profile
34
34
0:00:00
34
100.00%
100.00%
$0.00
577
Cuba Offers to Pay $270 Million Debt in Rum | ...
til
34
34
0:16:42
0
0.00%
0.00%
$0.00
578
El DiabloÛªs Sangria [Recipe] | VinePair
recipe_single
34
34
0:00:06
0
0.00%
0.00%
$0.00
579
Es el DÌ_a Nacional del cafÌ© irlandÌ©s. He aq...
til
34
34
0:00:00
0
0.00%
100.00%
$0.00
580
Fernet-Branca Brand Profile | VinePair
brand_profile
34
34
0:00:42
0
0.00%
0.00%
$0.00
581
French 75 Cocktail Recipe
recipe_single
34
34
0:01:42
0
0.00%
0.00%
$0.00
582
Guide To Gluten-Free Beer | VinePair
unset
34
34
0:00:34
0
0.00%
0.00%
$0.00
583
Guide To Hard Cider | Hard Cider Basics | Vine...
unset
34
34
0:00:00
34
100.00%
100.00%
$0.00
584
Havana Club Rum Brand Profile | VinePair
brand_profile
34
34
0:00:00
34
100.00%
100.00%
$0.00
585
Hennessy Cognac Brand Profile | VinePair
brand_profile
34
34
0:00:00
34
100.00%
100.00%
$0.00
586
How To Build The Perfect Book Club Spread Feat...
unset
34
34
0:00:00
34
100.00%
100.00%
$0.00
587
If You See a Beer Commercial During the Superb...
business
34
34
0:01:21
34
0.00%
0.00%
$0.00
588
Is Canadian Rye One Big Lie? | VinePair
til
34
34
0:00:00
34
100.00%
100.00%
$0.00
589
Off NapaÛªs Beaten Path [WITH MAP!] | VinePair
unset
34
34
0:00:00
0
0.00%
100.00%
$0.00
590
Our Ethics & Our Samples Policy | VinePair
unset
34
34
0:00:00
0
0.00%
100.00%
$0.00
591
Pomegranate & Ruby Red Grapefruit Margarita | ...
recipe_single
34
34
0:00:02
0
0.00%
0.00%
$0.00
592
Sauza Tequila Brand Profile | VinePair
brand_profile
34
34
0:01:43
0
0.00%
0.00%
$0.00
593
Strawberry Pineapple Margaritas [RECIPE] | Vin...
recipe_single
34
34
0:00:00
34
100.00%
100.00%
$0.00
594
Study Finds Wines Aged in French Oak Protect Y...
unset
34
34
0:00:00
0
0.00%
100.00%
$0.00
595
Svedka Vodka Brand Profile | VinePair
brand_profile
34
34
0:00:11
0
0.00%
0.00%
$0.00
596
Tanqueray Gin Brand Profile | VinePair
brand_profile
34
34
0:00:43
0
0.00%
0.00%
$0.00
597
The 8 Irish Whiskies To Help You Get Into The ...
unset
34
34
0:00:00
34
100.00%
100.00%
$0.00
598
The 9 Mistakes YouÛªre Making At A Winery Tas...
til
34
34
0:00:00
0
0.00%
100.00%
$0.00
599
The InsiderÛªs Guide To The Ultimate Sherry A...
travel
34
34
0:00:03
0
0.00%
0.00%
$0.00
600
The One-Two Punch That Knocked Irish Whiskey F...
history
34
34
0:00:00
0
0.00%
100.00%
$0.00
601
The Perfect Sangria Recipe [Illustrated Infogr...
unset
34
34
0:00:00
0
0.00%
100.00%
$0.00
602
VinePair's Guide to Pairing Wine With Digiorno...
unset
34
34
0:00:00
34
0.00%
0.00%
$0.00
603
Where to Drink at AtlantaÛªs Hartsfield-Jacks...
unset
34
34
0:00:00
0
0.00%
100.00%
$0.00
604 rows × 9 columns
In [26]:
df["count"] = 1
In [29]:
df_pivoted = pd.pivot_table(df,index="Editorial_Type",values=["Pageviews","count"],aggfunc=np.sum)
In [30]:
df_pivoted
Out[30]:
Pageviews
count
Editorial_Type
advice_etiquette
34955
26
brand_profile
5207
18
business
33088
25
fashion
1293
3
food
28048
23
health
27742
12
history
49013
28
humor
91291
45
long_form
18586
8
opinion_editorial
68893
18
product_recommendation
167850
50
recipe_roundup
39213
23
recipe_single
28182
44
science
12628
7
til
131865
73
travel
128804
37
unset
444271
148
visualization
44148
16
In [34]:
df_pivoted_articles = pd.pivot_table(df,index="Editorial_Type",columns="Page Title",values="Pageviews")
In [35]:
df_pivoted_articles.head(5)
Out[35]:
Page Title
(not set)
10 Batch Cocktail Recipes For Your Big Super Bowl Party! | VinePair
10 Best Bottles to Gift Whisk(e)y Lovers | VinePair
10 Cheesy Yet Awesome Romantic Getaways | VinePair
10 Dishes to Help You Appreciate German Riesling | VinePair
10 Great New IPAs You Need To Try - Summer 2016
10 Great New IPAs You Need To Try | VinePair
10 Heavenly Appetizer and Cocktail Pairings for the Perfect Wedding Reception | VinePair
10 Imperial Stouts That Will Help You Survive The Winter | VinePair
10 Stunning Pieces of Drinking Art From the Met's Collection | VinePair
...
Your All-American Beer's Stance on Immigrants May Surprise You | VinePair
Your All-American BeerÛªs Stance on Immigrants May Surprise You | VinePair
Your Drinking Guide to Life as a Single Parent | VinePair
Your Drunk Aunt Was Right: The Hot Toddy Is The Cure To The Common Cold | VinePair
Your Inauguration Day Drinking Guide [Infographic] | VinePair
Your Super Bowl Drinking Playbook [Flowchart] | VinePair
Your Travel Guide To The Gastronomical Capital Of France: Lyon | VinePair
Your Two Favorite Things in One: Whiskey Flavored Coffee | VinePair
YouÛªre Probably Drinking White Ale Without Knowing It | VinePair
YouÛªre Probably Making Your Bloody Mary Wrong | VinePair
Editorial_Type
advice_etiquette
102.0
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
...
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
brand_profile
34.0
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
...
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
business
102.0
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
...
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
fashion
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
...
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
food
NaN
NaN
NaN
NaN
NaN
NaN
NaN
1702.0
NaN
NaN
...
NaN
NaN
NaN
NaN
NaN
NaN
NaN
1736.0
NaN
NaN
5 rows × 586 columns
In [37]:
df_pivoted_articles["total"] = df_pivoted_articles.sum(axis=1)
In [39]:
df_pivoted_articles["median"] = df_pivoted_articles.median(axis=1)
In [41]:
df_pivoted_articles["average"] = df_pivoted_articles.mean(axis=1)
In [49]:
df_pivoted_articles_T = df_pivoted_articles.iloc[:,:-3].T
In [51]:
df_pivoted_articles_T.boxplot("advice_etiquette")
Out[51]:
<matplotlib.axes._subplots.AxesSubplot at 0x10ccd6ba8>
In [74]:
fig, ax = plt.subplots(figsize=(15,5))
df_pivoted_articles_T.boxplot(list(df_pivoted_articles_T.columns),ax=ax,showfliers=False)
Out[74]:
<matplotlib.axes._subplots.AxesSubplot at 0x11006d8d0>
In [71]:
df_pivoted_articles_T.rename(columns={"advice_etiquette":"advice","brand_profile":"brand","long_form":"long","opinion_editorial":"opinion","product_recommendation":"product","recipe_roundup":"recipe_r","recipe_single":"recipe_s"},inplace=True)
In [75]:
df_pivoted_articles_T.columns
Out[75]:
Index(['advice', 'brand', 'business', 'fashion', 'food', 'health', 'history',
'humor', 'long', 'opinion', 'product', 'recipe_r', 'recipe_s',
'science', 'til', 'travel', 'unset', 'visualization'],
dtype='object', name='Editorial_Type')
In [80]:
long = df_pivoted_articles_T["long"]
In [81]:
long
Out[81]:
Page Title
(not set) 102.0
10 Batch Cocktail Recipes For Your Big Super Bowl Party! | VinePair NaN
10 Best Bottles to Gift Whisk(e)y Lovers | VinePair NaN
10 Cheesy Yet Awesome Romantic Getaways | VinePair NaN
10 Dishes to Help You Appreciate German Riesling | VinePair NaN
10 Great New IPAs You Need To Try - Summer 2016 NaN
10 Great New IPAs You Need To Try | VinePair NaN
10 Heavenly Appetizer and Cocktail Pairings for the Perfect Wedding Reception | VinePair NaN
10 Imperial Stouts That Will Help You Survive The Winter | VinePair NaN
10 Stunning Pieces of Drinking Art From the Met's Collection | VinePair NaN
10 Stunning Pieces of Drinking Art From the MetÛªs Collection | VinePair NaN
10 Ways to Drink With Your BFF on Valentine's Day | VinePair NaN
10 Ways to Drink With Your BFF on ValentineÛªs Day | VinePair NaN
10 White Sangria Recipes For A Refreshing Summer Season | VinePair NaN
10 Wine Pairings That Break the Rules | VinePair NaN
10 Wine and Salad Pairings for Your New Year's 'Diet' | VinePair NaN
10 Wine and Salad Pairings for Your New YearÛªs Û÷DietÛª | VinePair NaN
11 Exceptional Tequilas Under $50 | VinePair NaN
11 Mouthwatering Beer-Based Crock-Pot Recipes | VinePair NaN
11 Things You DidnÛªt Know About Captain Morgan | VinePair NaN
11 Things You DidnÛªt Know About Prosecco | VinePair NaN
11 Wine Themed Coffee Mugs That Reveal What YouÛªre Really Drinking | VinePair NaN
12 Actually Sexy Movies (That Aren't '50 Shades Darker') With Drink Pairings | VinePair NaN
12 Actually Sexy Movies (That ArenÛªt Ûª50 Shades DarkerÛª) With Drink Pairings | VinePair NaN
12 Bourbon-Based Recipes You Need to Try | VinePair NaN
12 Great High Octane IPAs You Should Try | VinePair NaN
12 Ideal Whites And Ros̩s For Cold Weather Drinking | VinePair NaN
12 Of The Most Impressive Wineries In The World | VinePair NaN
12 Soup Recipes That Are Better With Bourbon | VinePair NaN
13 Things You DidnÛªt Know About Jose Cuervo Tequila NaN
...
Why YouÛªre Wrong About Sweet Cocktails | VinePair NaN
Will Beer Before Liquor Actually Make You Sicker? | VinePair NaN
Wine 101 | Learn About Wine | Our Wine Guide NaN
Wine Grapes are Better for Wrinkles Than Botox | VinePair NaN
Wine Is Not About Flavor, It's About Structure | VinePair NaN
Wine Is Not About Flavor, ItÛªs About Structure | VinePair NaN
Wine Pairings For All of Your Favorite Toasts | VinePair NaN
Wine Pairings For Your Favorite Cauliflower Dishes | VinePair NaN
Wine, Beer & Spirit Travel Guides | VinePair Travel Guides NaN
Woman Finds why Drinking Your own Alcohol on a Plane is a bad Idea | VinePair NaN
Work At VinePair | VinePair NaN
Yes, There's An Irish Whiskey Boom, But Not Because Of Craft Distilleries | VinePair NaN
Yes, You're Too Old for the Club! Hit Up These Cocktail Bars Instead | VinePair NaN
Yes, YouÛªre Too Old for the Club! Hit Up These Cocktail Bars Instead | VinePair NaN
You Can Now Come Home to Wine In Your Mailbox | VinePair NaN
You Might Be Surprised to Learn Who's Making Your Wine | VinePair NaN
You Might Be Surprised to Learn WhoÛªs Making Your Wine | VinePair NaN
You Should Be Drinking Beer Punch at Your Super Bowl Party | VinePair NaN
You're Probably Drinking White Ale Without Knowing It | VinePair NaN
You're Probably Making Your Bloody Mary Wrong | VinePair NaN
Your All-American Beer's Stance on Immigrants May Surprise You | VinePair NaN
Your All-American BeerÛªs Stance on Immigrants May Surprise You | VinePair NaN
Your Drinking Guide to Life as a Single Parent | VinePair NaN
Your Drunk Aunt Was Right: The Hot Toddy Is The Cure To The Common Cold | VinePair NaN
Your Inauguration Day Drinking Guide [Infographic] | VinePair NaN
Your Super Bowl Drinking Playbook [Flowchart] | VinePair NaN
Your Travel Guide To The Gastronomical Capital Of France: Lyon | VinePair NaN
Your Two Favorite Things in One: Whiskey Flavored Coffee | VinePair NaN
YouÛªre Probably Drinking White Ale Without Knowing It | VinePair NaN
YouÛªre Probably Making Your Bloody Mary Wrong | VinePair NaN
Name: long, dtype: float64
In [82]:
df[df.Editorial_Type == "long_form"]
Out[82]:
Page Title
Editorial_Type
Pageviews
Unique Pageviews
Avg. Time on Page
Entrances
Bounce Rate
% Exit
Page Value
count
26
The Cocktail Industry Has a Sexual Assault Pro...
long_form
6672
6195
0:08:46
5685
51.47%
85.72%
$0.00
1
40
Being A Somm Is About Service, Not Stardom | V...
long_form
4902
4255
0:09:43
4119
41.32%
75.68%
$0.00
1
108
Maybe Black People Just Don't Like Craft Beer ...
long_form
2485
2417
0:08:47
2213
69.23%
83.54%
$0.00
1
168
All Celebrity Wine Might As Well Be Called ÛÏ...
long_form
1668
1532
0:07:15
1055
43.80%
65.29%
$0.00
1
169
I Went to Bartending School and Here's What Ha...
long_form
1668
1566
0:10:04
1259
62.19%
77.52%
$0.00
1
319
Maybe Black People Just DonÛªt Like Craft Bee...
long_form
681
545
0:00:20
0
0.00%
49.93%
$0.00
1
394
I Went to Bartending School and HereÛªs What ...
long_form
408
340
0:04:43
0
0.00%
50.00%
$0.00
1
518
(not set)
long_form
102
102
0:00:00
102
33.33%
100.00%
$0.00
1
In [83]:
df[df.Editorial_Type == "opinion_editorial"]
Out[83]:
Page Title
Editorial_Type
Pageviews
Unique Pageviews
Avg. Time on Page
Entrances
Bounce Rate
% Exit
Page Value
count
4
3 Things Your Female Bartender Doesn't Want to...
opinion_editorial
25461
24644
0:02:57
24270
52.74%
61.10%
$0.00
1
16
The People Calling Expensive Wine a Rip-Off Ar...
opinion_editorial
11607
10756
0:07:19
9973
42.00%
75.95%
$0.00
1
33
Stop Putting TVs in Every Bar | VinePair
opinion_editorial
5957
5650
0:05:07
4970
47.95%
79.42%
$0.00
1
41
Budweiser Tastes Like Nothing ÛÓ And ThatÛªs...
opinion_editorial
4697
4255
0:03:18
3812
33.04%
65.96%
$0.00
1
42
Don't Become a Certified Sommelier (ducks) | V...
opinion_editorial
4697
4561
0:07:00
3949
60.50%
74.64%
$0.00
1
49
You're Probably Making Your Bloody Mary Wrong ...
opinion_editorial
4357
4119
0:02:36
3778
49.55%
67.18%
$0.00
1
113
This Year, the Wine Industry Should Drink Like...
opinion_editorial
2417
2281
0:03:46
817
42.26%
67.60%
$0.00
1
123
3 Things Your Female Bartender DoesnÛªt Want ...
opinion_editorial
2247
2042
0:01:16
0
0.00%
68.18%
$0.00
1
150
Stop Telling Moms They Can't Drink Responsibly...
opinion_editorial
1838
1668
0:02:04
1396
65.83%
66.65%
$0.00
1
165
The 10 Things We Learned About Drinking In 201...
opinion_editorial
1702
1600
0:01:46
919
55.60%
59.99%
$0.00
1
218
The Super Bowl Beer Battle: Boston vs. Atlanta...
opinion_editorial
1293
1191
0:02:16
817
54.22%
73.70%
$0.00
1
276
DonÛªt Become a Certified Sommelier (ducks) |...
opinion_editorial
851
851
0:01:32
0
0.00%
68.04%
$0.00
1
379
YouÛªre Probably Making Your Bloody Mary Wron...
opinion_editorial
477
443
0:00:27
0
0.00%
57.02%
$0.00
1
389
2017 Welcomes You With an Extra Hangover Recov...
opinion_editorial
408
408
0:00:55
170
20.00%
66.67%
$0.00
1
405
Stop Telling Moms They CanÛªt Drink Responsib...
opinion_editorial
374
306
0:01:03
34
0.00%
45.45%
$0.00
1
439
(not set)
opinion_editorial
272
272
0:09:07
272
50.00%
75.00%
$0.00
1
500
Why I Hate Female Sommeliers and So Should You...
opinion_editorial
170
170
0:04:14
136
50.00%
60.00%
$0.00
1
540
Craft Beer Has Killed Day Drinking | VinePair
opinion_editorial
68
68
0:00:39
0
0.00%
50.00%
$0.00
1
In [ ]:
#Dump data from Nov 1 To January 31st
#Join that with publish date
Content source: facemelters/data-science
Similar notebooks: