<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 The project I am angling towards deals with my website's traffic and taxonomy data. I will be trying to build models that can accurately predict which tags are best for specific channels of traffic, and will also be investigating the longitudinal nature of an article's lifecycle (days to 90% of traffic is my current threshold for an article being done, but I will refine that with some descriptive statistics).
I'll also want to investigate what kind of content performs best in each month.
Step1:  First, import the table of tag-article mappings from our SQL db
Step2:  We only care about the articles for this analysis. Place entries are outside scope.
Step3:  Extract the tag name from the tag's URL
Step4:  Create a tag_url column that just has the tag's name
Step5:  Get dummies for each tag
Step6:  Join the dummies back to the main dataframe
Step7:  De-dupe articles but maintain the tagging data using groupby and sum
Step8:  Using a csv generated by a script I wrote that queries Google Analytics for pageviews per article from publish date to n-days post-publication, import pageview data and join it to the tag/article DataFrame
Step9:  Set the pageviews index to the url column to make joining easy
Step10:  Reset index
Step11:  Articles published more recently have, on average, received much more traffic than older articles (this reflects growth and heavier distribution of the newer content). The drop in the mean as we move into 2016 is an artifact of the article's lifecycle not being complete.
Article lifecycle will be explored below.
Step12:  Let's import the time-series I created with a python script that asks GA for the daily time-series of Pageviewsof each article from publication date forward two years.
Step13:  It was easier to collect the data from GA by looping over the columns in my original dataframe, but having each row be an article record is easier to work with now, so we transpose.
Step14:  Let's determine how many days post-publication it takes for an article to collect 90% of total pageviews.
Step15:  Now let's look at the number of articles per tag (we will later join the two DataFrames above into one)
Step16:  Now I'm going to try this with the time series 30days pvs
Step17:  Let's check average performance when just looking at Simplereach Tag data
Step18:  Let's run some regression analysis on our tag_analysis DataFrame
Step19:  Now let's try it with KNN
Step20:  Let's check the roc_auc scores for both the knn and logistic regression models.
Step21:  Looks like they give similar scores, but the scores are easily manipulated by changing the threshold for the number of articles per tag and by changing the threshold for "success" (currently set at > 10,000 Pageviews).
Step22:  Now let's try RandomForest
Step23:  Let's try the Logistic Model but with more tags
<ASSISTANT_TASK:>
Python Code:
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
%matplotlib inline
Explanation: The project I am angling towards deals with my website's traffic and taxonomy data. I will be trying to build models that can accurately predict which tags are best for specific channels of traffic, and will also be investigating the longitudinal nature of an article's lifecycle (days to 90% of traffic is my current threshold for an article being done, but I will refine that with some descriptive statistics).
I'll also want to investigate what kind of content performs best in each month.
End of explanation
df = pd.read_csv('atlas-taggings.csv')
df.head(10)
articles = df[df.tagged_type == 'Article']
Explanation: First, import the table of tag-article mappings from our SQL db
End of explanation
articles.head()
Explanation: We only care about the articles for this analysis. Place entries are outside scope.
End of explanation
def get_tag(x):
    return x.split('/')[2]
#changing this function to get_tag_name() in module.
Explanation: Extract the tag name from the tag's URL
End of explanation
articles.tag_url = articles.tag_url.apply(get_tag)
articles.head()
Explanation: Create a tag_url column that just has the tag's name
End of explanation
test = pd.get_dummies(articles.tag_url)
test.head()
Explanation: Get dummies for each tag
End of explanation
articles = articles.join(test)
articles.drop(['tag_id','tag_url','tagged_type','tagged_id'],axis=1,inplace=True)
articles.head()
Explanation: Join the dummies back to the main dataframe
End of explanation
unique_articles = articles.groupby('tagged_url').sum() #made into func
unique_articles = unique_articles.reset_index()
unique_articles = unique_articles.set_index('tagged_url')
Explanation: De-dupe articles but maintain the tagging data using groupby and sum
End of explanation
#now we need the pageviews and have to map the URLs to Page Titles
pageviews = pd.read_csv('output_articles_performance.csv',header=None,names=['url','published','pageviews'])
pageviews.head()
#In the future I should import the module and run it here instead of grabbing.
pageviews.url = ['www.atlasobscura.com/articles/' + x for x in pageviews.url]
pageviews.head()
pageviews.describe()
Explanation: Using a csv generated by a script I wrote that queries Google Analytics for pageviews per article from publish date to n-days post-publication, import pageview data and join it to the tag/article DataFrame
End of explanation
pageviews.set_index('url',inplace=True)
article_set = unique_articles.join(pageviews)
article_set.head()
Explanation: Set the pageviews index to the url column to make joining easy
End of explanation
article_set.reset_index()
article_set['upper_quartile'] = [1 if x > 10000 else 0 for x in article_set.pageviews]
article_set.pageviews.plot(kind='hist', bins=100,title='Page View Distribution, All Content')
article_set['published'] = pd.to_datetime(article_set['published'])
article_set
article_set['year'] = pd.DatetimeIndex(article_set['published']).year
ax = article_set.boxplot(column='pageviews',by='year',figsize=(6,6),showfliers=False)
ax.set(title='PV distribution by year',ylabel='pageviews')
Explanation: Reset index
End of explanation
yearly = article_set.set_index('published').resample('M').mean().plot(y='pageviews')
yearly.set(title='Total Pageviews By Month of Article Publication')
Explanation: Articles published more recently have, on average, received much more traffic than older articles (this reflects growth and heavier distribution of the newer content). The drop in the mean as we move into 2016 is an artifact of the article's lifecycle not being complete.
Article lifecycle will be explored below.
End of explanation
time_series = pd.read_csv('time-series.csv')
type(time_series)
time_series = time_series.drop('Unnamed: 0',axis=1)
Explanation: Let's import the time-series I created with a python script that asks GA for the daily time-series of Pageviewsof each article from publication date forward two years.
End of explanation
time_series = time_series.T
time_series.columns
time_series['total'] = time_series.sum(axis=1)
time_series.head()
Explanation: It was easier to collect the data from GA by looping over the columns in my original dataframe, but having each row be an article record is easier to work with now, so we transpose.
End of explanation
time_series['days_to_90p']= [(time_series.iloc[x].expanding().sum() > time_series.iloc[x].total*.90).argmax() \
                                 for x in range(len(time_series))]
time_series.reset_index(inplace=True)
time_series.head(1)
time_series['index'] = ['www.atlasobscura.com/articles/' + x for x in time_series['index']]
time_series.set_index('index',inplace=True)
time_series = time_series.join(pageviews.published)
time_series.head(5)
time_series['published'] = pd.to_datetime(time_series.published)
time_series['year_pub'] = pd.DatetimeIndex(time_series['published']).year
time_series.boxplot(column='days_to_90p',by='year_pub')
time_series.year_pub.value_counts(dropna=False)
time_series[['days_to_90p','total','year_pub']].corr()
#I DON'T KNOW WHY THIS WON'T WORK
time_series['30-day-PVs'] = [time_series.fillna(value=0).iloc[x,0:31].sum() for x in range(len(time_series))]
time_series['7-day-PVs'] = [time_series.fillna(value=0).iloc[x,0:8].sum() for x in range(len(time_series))]
Explanation: Let's determine how many days post-publication it takes for an article to collect 90% of total pageviews.
End of explanation
total_tagged= pd.DataFrame(data=article_set.sum(),columns = ['num_tagged'])
total_tagged.sort_values('num_tagged',ascending=False,inplace=True)
total_tagged.drop('pageviews',axis=0,inplace=True)
total_tagged[total_tagged.num_tagged >= 10].count()
total_tagged[total_tagged.num_tagged <=5].index
#tag_analysis = article_set.drop(total_tagged[total_tagged.num_tagged < 5].index,axis=1)
#I'm resetting tag_analysis to contain all tags so I can manipulate later whenever I want. It makes it more clear.
tag_analysis = article_set
print tag_analysis.shape
tag_analysis.head()
tag_analysis.tail()
tag_analysis.to_csv('tag_analysis_ready.csv')
total_tagged.head(30)
print total_tagged.shape
from sklearn.preprocessing import PolynomialFeatures
poly = PolynomialFeatures(interaction_only=True)
poly_df = pd.DataFrame(poly.fit_transform(tag_analysis.fillna(0).drop(['published','pageviews','upper_quartile','year'],axis=1)))
poly.n_output_features_
total_tagged.ix['extra-mile']
regular_features = ['places-you-can-no-longer-go','100-wonders','extra-mile','video-wonders','news','features','columns',
                    'found','animals','fleeting-wonders','visual','other-capitals-of-the-world','video','art','list','objects-of-intrigue',
                    'maps','morbid-monday','female-explorers','naturecultures']
total_tagged[total_tagged.num_tagged >10].shape
interactions = pd.DataFrame()
for item in regular_features:
    for column in tag_analysis.drop(['published','pageviews','upper_quartile','year'],axis=1).drop(
         total_tagged[total_tagged.num_tagged < 10].index,axis=1).columns:
        interactions[(item + '_' + column)] = tag_analysis[item] + tag_analysis[column]
#Just sum the row and column and then turn any 2s into 1s and 1s into zeros.
def correct_values(x):
    if x == 2.0:
        return 1
    elif x == 1.0:
        return 0
    else:
        return 0
for item in interactions.columns:
    interactions[item] = interactions[item].apply(correct_values)
interactions.head(2)
tagged_total = pd.DataFrame(data =interactions.sum(),columns=['num_tagged'])
tagged_total = tagged_total.sort_values('num_tagged',ascending=False)
identity_tags = tagged_total[0:26].index
interactions = interactions.drop(identity_tags,axis=1)
tagged_total = pd.DataFrame(data =interactions.sum(),columns=['num_tagged'])
tagged_total = tagged_total.sort_values('num_tagged',ascending=False)
tagged_total.head(10)
#DO I WANT TO DROP THE EMPTY COLUMNS?
#for item in interactions.columns:
 #   if interactions[item].sum == 0:
#      interactions = interactions.drop(item,axis=1)
interactions.head(10)
interactions = interactions.join(pageviews)
#drop empty cols
def drop_zero_cols(df):
    for item in df.columns:
        if df[item].sum() == 0:
            df = df.drop(item,axis=1)
        else:
            continue
    return df
interactions = drop_zero_cols(interactions.fillna(0).drop(['published','pageviews'],axis=1))
interactions = interactions.join(pageviews)
interactions.head(1)
interaction_totals = pd.DataFrame(interactions.sum().sort_values(ascending=False),columns=['num_tagged'])
interaction_totals[interaction_totals.num_tagged < 4].shape
interactions_analysis = interactions.drop(interaction_totals[interaction_totals.num_tagged < 4].index,axis=1)
interactions_analysis.head()
#Check whether number of Aggregated stories published per day has an impact on average/total Day 0 - 1 traffic.
from sklearn import linear_model
from sklearn import metrics
from sklearn import cross_validation
interactions_analysis['upper_quartile'] = [1 if x > 10000 else 0 for x in interactions.pageviews]
interactions_analysis['twenty_thousand'] = [1 if x > 20000 else 0 for x in interactions.pageviews]
y = interactions_analysis.upper_quartile
X = interactions_analysis.drop(['pageviews','published','upper_quartile','twenty_thousand'],axis=1)
kf = cross_validation.KFold(len(interactions_analysis),n_folds=5)
scores = []
for train_index, test_index in kf:
    lr = linear_model.LogisticRegression().fit(X.iloc[train_index],y.iloc[train_index])
    scores.append(lr.score(X.iloc[test_index],y.iloc[test_index]))
print "average accuracy for LogisticRegression is", np.mean(scores)
print "average of the set is: ", np.mean(y)
interactions_lr_scores = lr.predict_proba(X)[:,1]
print metrics.roc_auc_score(y,interactions_lr_scores)
interactions_probabilities = pd.DataFrame(zip(X.columns,interactions_lr_scores),columns=['tags','probabilities'])
interactions_probabilities.sort_values('probabilities',ascending=False)
interaction_totals.head(2)
def split_tag(x):
    return x.split('_')[1]
interactions_probabilities = interactions_probabilities.reset_index()
interactions_probabilities['subtag'] = interactions_probabilities.tags.apply(split_tag)
interactions_probabilities = interactions_probabilities.sort_values(['tags','probabilities'],ascending=[1, 0])
interactions_probabilities = interactions_probabilities.set_index('tags').join(interaction_totals)
interactions_probabilities
interactions_probabilities['pageviews'] = [sum(interactions['pageviews'][interactions[item]==1]) for item in interactions_probabilities.tags]
interactions_probabilities['mean-PVs'] = interactions_probabilities['pageviews'] // interactions_probabilities['num_tagged']
regular_features
interactions_probabilities[interactions_probabilities.tags.str.contains('features')==True].sort_values('mean-PVs',
                                                                                                   ascending = False)
interactions_probabilities.sort_values('probabilities',ascending = False)
np.mean(interactions.pageviews)
#I took the dashes out. Have to add back for this
fix_regular_features = [x.replace(' ','-') for x in regular_features]
fig,axes=plt.subplots(figsize=(10,10))
for item, name in enumerate(fix_regular_features):
    interactions.plot(x=interactions['pageviews'][interactions.columns.str.contains(name)==True],kind='box',ax=item)
plt.show()
#doublecheck my work on pageviews vs num-published
pub_volume = tag_analysis[['published','pageviews']]
pub_volume['num_pubbed'] = 1
pub_volume['published'] = pd.to_datetime(pub_volume.published)
pub_volume = pub_volume.set_index('published')
pub_volume.head(10)
pub_volume = pub_volume.resample('M').sum().dropna()
pub_volume['year'] = pub_volume.index.year
pub_volume[pub_volume.index.year >=2015].corr()
pub_volume[pub_volume.index.year >=2015].plot(kind='scatter',x='num_pubbed',y='pageviews')
import seaborn as sns
ax = sns.regplot(x='num_pubbed',y='pageviews',data=pub_volume)
Explanation: Now let's look at the number of articles per tag (we will later join the two DataFrames above into one)
End of explanation
#doublecheck my work on pageviews vs num-published
pub_volume = time_series[['published','7-day-PVs']]
pub_volume['num_pubbed'] = 1
pub_volume['published'] = pd.to_datetime(pub_volume.published)
pub_volume = pub_volume.set_index('published')
pub_volume.head(10)
num_holder = pub_volume.resample('D').sum().dropna().drop('7-day-PVs',axis=1)
pub_volume = pub_volume.resample('D').sum().dropna().drop('num_pubbed',axis=1)
pub_volume = pub_volume.join(num_holder)
pub_volume['year'] = pub_volume.index.year
pub_volume[pub_volume.index.year >=2015].corr()
pub_volume[pub_volume.index >='2016-01-01'].plot(kind='scatter',x='num_pubbed',y='7-day-PVs',title='7-Day PVs')
import seaborn as sns
ax = sns.regplot(x='num_pubbed',y='7-day-PVs',data=pub_volume)
Explanation: Now I'm going to try this with the time series 30days pvs
End of explanation
simplereach = pd.read_csv('simplereach-tags.csv')
simplereach.head(1)
simplereach = simplereach.set_index('Tag')
total_tagged2 = total_tagged
total_tagged2.head(4)
total_tagged2.index = [x.replace('-',' ') for x in total_tagged.index]
simplereach = simplereach.join(total_tagged2)
simplereach['mean-PVs'] = simplereach['Page Views'] // simplereach['num_tagged']
simplereach['mean-shares'] = simplereach['Facebook Shares'] // simplereach['num_tagged']
simplereach = simplereach[['mean-PVs','mean-shares','num_tagged']]
simplereach[simplereach['num_tagged'] > 5].sort_values('mean-PVs',ascending=False)
#regular_features = [x.replace('-',' ') for x in regular_features]
simplereach.ix[regular_features].sort_values('mean-PVs',ascending=False)
Explanation: Let's check average performance when just looking at Simplereach Tag data
End of explanation
from sklearn import linear_model
from sklearn import metrics
tag_analysis.fillna(value=0,inplace=True)
y = tag_analysis.upper_quartile
X = tag_analysis.drop(['pageviews','published','upper_quartile'],axis=1)
from sklearn import cross_validation
kf = cross_validation.KFold(len(tag_analysis),n_folds=5)
scores = []
for train_index, test_index in kf:
    lr = linear_model.LogisticRegression().fit(X.iloc[train_index],y.iloc[train_index])
    scores.append(lr.score(X.iloc[test_index],y.iloc[test_index]))
print "average accuracy for LogisticRegression is", np.mean(scores)
print "average of the set is: ", np.mean(y)
lr_scores = lr.predict_proba(X)[:,1]
print metrics.roc_auc_score(y,lr_scores)
print metrics.roc_auc_score(y,lr_scores)
lr_scores
coefficients = pd.DataFrame(zip(X.columns,lr.coef_[0]),columns=['tags','coefficients'])
probabilities = pd.DataFrame(zip(X.columns,lr_scores),columns=['tags','probabilities'])
probabilities.sort_values('probabilities',ascending=False)
coefficients.sort_values('coefficients',ascending=False)
tag_analysis[tag_analysis['100-wonders'] ==1].describe()
tag_analysis.head()
Explanation: Let's run some regression analysis on our tag_analysis DataFrame
End of explanation
from sklearn.grid_search import GridSearchCV
from sklearn.neighbors import KNeighborsClassifier
params = {'n_neighbors': [x for x in range(2,200,1)],
          'weights': ['distance','uniform']}
gs = GridSearchCV(estimator=KNeighborsClassifier(),param_grid=params,n_jobs=8,cv=10)
gs.fit(X,y)
print gs.best_params_
print gs.best_score_
print type(gs.best_estimator_)
knn = gs.best_estimator_.fit(X,y)
knn_scores = knn.predict_proba(X)[:,1]
print np.mean(knn_scores)
print np.mean(lr_scores)
knn_probabilities = pd.DataFrame(zip(X.columns,knn_scores),columns=['tags','probabilities'])
knn_probabilities.sort_values('probabilities',ascending=False)
Explanation: Now let's try it with KNN
End of explanation
print 'knn', metrics.roc_auc_score(y,knn_scores)
print 'lr', metrics.roc_auc_score(y,lr_scores)
Explanation: Let's check the roc_auc scores for both the knn and logistic regression models.
End of explanation
probabilities = probabilities.set_index('tags')
probabilities = probabilities.join(total_tagged)
probabilities.to_csv('tag-probabilities-logisticregression.csv')
Explanation: Looks like they give similar scores, but the scores are easily manipulated by changing the threshold for the number of articles per tag and by changing the threshold for "success" (currently set at > 10,000 Pageviews).
End of explanation
from sklearn.ensemble import RandomForestClassifier
params = {'max_depth': np.arange(20,100,2),
          'min_samples_leaf': np.arange(90,200,2),
          'n_estimators': 20}
gs1 = GridSearchCV(RandomForestClassifier(),param_grid=params, cv=10, scoring='roc_auc',n_jobs=8,verbose=1)
gs1.fit(X,y)
print gs1.best_params_
print gs1.best_score_
rf = RandomForestClassifier(gs1.best_estimator_)
rf.fit(X,y)
probs = rf.predict_proba(X)[:,1]
print rf.score(X,y)
print metrics.roc_auc_score(y,probs)
probs = pd.DataFrame(zip(X.columns,probs),columns=['tags','probabilities'])
probs.sort_values('probabilities',ascending=False)
Explanation: Now let's try RandomForest
End of explanation
tag_analysis2 = article_set.drop(total_tagged[total_tagged.num_tagged < 15].index,axis=1)
tag_analysis2['ten_thousand'] = [1 if x > 10000 else 0 for x in tag_analysis2.pageviews]
tag_analysis2.fillna(value=0,inplace=True)
y2 = tag_analysis2.ten_thousand
X2 = tag_analysis2.drop(['pageviews','upper_quartile','ten_thousand'],axis=1)
kf2 = cross_validation.KFold(len(tag_analysis2),n_folds=5)
scores2 = []
for train_index, test_index in kf2:
    lr2 = linear_model.LogisticRegression().fit(X2.iloc[train_index],y2.iloc[train_index])
    scores2.append(lr2.score(X2.iloc[test_index],y2.iloc[test_index]))
print "average accuracy for LogisticRegression is", np.mean(scores2)
print "average of the set is: ", np.mean(y2)
print tag_analysis2.shape
print y2.shape
print X2.shape
lr_scores2 = lr2.predict_proba(X2)[:,1]
lr2_probs = pd.DataFrame(zip(X2.columns,lr_scores2),columns=['tags','probabilities'])
lr2_probs.sort_values('probabilities',ascending=False)
metrics.roc_auc_score(y2,lr2.predict_proba(X2)[:,1])
lr2_probs = lr2_probs.set_index('tags')
lr2_probs = lr2_probs.join(total_tagged)
plt.figure(figsize=(10,10))
plt.scatter(lr2_probs.num_tagged,lr2_probs.probabilities)
plt.show()
lr2_probs = lr2_probs.sort_values('probabilities',ascending=False)
lr2_probs = lr2_probs.reset_index()
lr2_probs.to_csv('min15tags_min10000pvs.csv')
lr2_probs.shape
lr2_probs
Explanation: Let's try the Logistic Model but with more tags
End of explanation
<END_TASK>