# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

import pandas as pd
import numpy as np

activity_scopes_df = pd.read_excel(r'd:/temp/activity_scope.xlsx')
activity_scopes_df.head()

test_df = pd.read_excel(r'D:\GoogleDrive\Wikipedia Expertise (Alex)\appendix\output_final_v2.xlsx')

print('experts > 80%: {}'.format(len(test_df[test_df.expert > 0.8])))
print('unique experts > 80%: {}'.format(len(test_df[test_df.expert > 0.8]['user'].unique())))
print('% experts > 80% of total population: {}'.format(np.round(len(test_df[test_df.expert > 0.8])/len(test_df), 2)))
print('experts > 80% page edits average: {}'.format(np.round(np.average(test_df[test_df.expert > 0.8]['page_edits']), 2)))
print('experts > 80% page edits total: {}'.format(np.round(np.sum(test_df[test_df.expert > 0.8]['page_edits']), 2)))
print('page edits total: {}'.format(np.round(np.sum(test_df['page_edits']), 2)))
test_df.head()

# +
# feature importance by weight
feature_names = ['page_edit_dist',
                 'ns0_edit_dist',
                 'summary_similarity',
                 'categories_overlap',
                 'links_overlap',
                 'title_similarity',
                 'mean_edit_size',
                 'avg_persistence',
                 'page_edits_ratio',
                 'content_token_vs_token'
                ]

experts_df = test_df[test_df.evaluation == 'expert']
print(len(experts_df))
non_experts_df = test_df[test_df.evaluation == 'not_expert']
print(len(non_experts_df))

feature_rows = []
for f_name in feature_names:
    feature_rows.append([f_name, np.average(experts_df[f_name]), np.average(non_experts_df[f_name])])

feat_df = pd.DataFrame(feature_rows, columns=['Feature', 'Expert', 'Non-Expert'])
feat_df.head(20)
feat_df.to_excel(r'd:/temp/feature_importance_by_weight.xlsx', index=False)

# +
# feature importance by gain
feature_names = ['title_similarity', 
                 'summary_similarity',
                 'page_edit_dist',
                 'avg_persistence',
                 'page_edits_ratio',
                 'ns0_edit_dist',
                 'links_overlap',
                 'mean_edit_size',
                 'edit_type_b',
                 'edit_type_h'
                ]

experts_df = test_df[test_df.evaluation == 'expert']
print(len(experts_df))
non_experts_df = test_df[test_df.evaluation == 'not_expert']
print(len(non_experts_df))

feature_rows = []
for f_name in feature_names:
    feature_rows.append([f_name, np.average(experts_df[f_name]), np.average(non_experts_df[f_name])])

feat_df = pd.DataFrame(feature_rows, columns=['Feature', 'Expert', 'Non-Expert'])
feat_df.head(20)
feat_df.to_excel(r'd:/temp/feature_importance_by_gain.xlsx', index=False)
# -

df = test_df.merge(activity_scopes_df, left_on='page_id', right_on='article_id')
non_experts_avg = np.average(df[(df.evaluation == 'not_expert')]['avg_persistence'])
experts_avg = np.average(df[(df.evaluation == 'expert')]['avg_persistence'])
print(non_experts_avg)
print(experts_avg)

# +

df[(df.evaluation == 'not_expert') & (df.avg_persistence > experts_avg)][['page', 'user', 'avg_persistence', 'activity_scope']]

# +
# experts_avg = np.average(experts_df[f_name])
# non_experts_avg = np.average(non_experts_df[f_name])
# test_df[(test_df.avg_persistence > experts_avg) & (test_df.evaluation == 'not_expert')][['user','persistence_exists', 'avg_persistence']]


df[ (df.evaluation == 'expert')][['page', 'user', 'avg_persistence', 'activity_scope']]
# print(np.average(res[res.avg_persistence > 10000000]['avg_persistence']))
# res[res.avg_persistence < 1000000]['avg_persistence']

# +
test_df = test_df[['expert', 
                   'category', 
                   'page', 
                   'page_id', 
                   'user', 
                   'registered', 
                   'evaluation', 
                   'is_wikipedian', 
                   'is_admin', 
                   'has_user_page']]

test_df['article'] = test_df['page']
test_df['article_id'] = test_df['page_id']
test_df['is_wikipedian'] = test_df.apply(lambda row: False if pd.isnull(row['is_wikipedian']) or row['is_wikipedian'] == 0 else True, axis=1)
test_df['is_admin'] = test_df.apply(lambda row: False if pd.isnull(row['is_admin']) or row['is_admin'] == 0 else True, axis=1)
test_df['has_user_page'] = test_df.apply(lambda row: True if pd.isnull(row['has_user_page']) or row['has_user_page'] > 0 else False, axis=1)

test_df.drop(['page', 'page_id'], axis=1)
# -

df = test_df.merge(activity_scopes_df, how='left', on='article_id')
df[df.registered].head(100)

# +
rows = []
grouped = df.groupby('article_id')
for article_id, group in grouped:
    category = group['category'].iloc[0]
    article = group['article'].iloc[0]    
    maturity = group['activity_scope'].iloc[0]
    total_registered = len(group[group.registered])
    total_unregistered = len(group[~group.registered])
    total_unique = len(group['user'].unique())
    
    sorted_group = group.sort_values(by='expert', ascending=False)
    
    top_10 = sorted_group.head(10)
    top_10_registered = len(top_10[sorted_group.registered==True])
    top_10_unregistered = len(top_10[sorted_group.registered==False])
    
    top5_registered = sorted_group[sorted_group.registered].head(5)
    top5_80_registered = sorted_group[(sorted_group.registered) & (sorted_group.expert > 0.8)].head(5)
    
    top5_expert = len(top5_registered[top5_registered['evaluation']=='expert'])
    top5_no_page = len(top5_registered[top5_registered['evaluation']=='no_page'])
    top5_not_expert = len(top5_registered[top5_registered['evaluation']=='not_expert'])
    top5_practitioner = len(top5_registered[top5_registered['evaluation']=='practitioner'])
    top5_scholar = len(top5_registered[top5_registered['evaluation']=='scholar'])
    top5_unknown = len(top5_registered[top5_registered['evaluation']=='unknown'])
    top5_wikipedian = len(top5_registered[top5_registered['is_wikipedian']])
    top5_administrator = len(top5_registered[top5_registered['is_admin']])
    top5_has_user_page = len(top5_registered[top5_registered['has_user_page']])
    top5_nan = len(top5_registered[top5_registered['evaluation'].isnull()])
    
    top5_80_expert = len(top5_80_registered[top5_80_registered['evaluation']=='expert'])
    top5_80_no_page = len(top5_80_registered[top5_80_registered['evaluation']=='no_page'])
    top5_80_not_expert = len(top5_80_registered[top5_80_registered['evaluation']=='not_expert'])
    top5_80_practitioner = len(top5_80_registered[top5_80_registered['evaluation']=='practitioner'])
    top5_80_scholar = len(top5_80_registered[top5_80_registered['evaluation']=='scholar'])
    top5_80_unknown = len(top5_80_registered[top5_80_registered['evaluation']=='unknown'])
    top5_80_wikipedian = len(top5_80_registered[top5_80_registered['is_wikipedian']])
    top5_80_administrator = len(top5_80_registered[top5_80_registered['is_admin']])
    top5_80_has_user_page = len(top5_80_registered[top5_80_registered['has_user_page']])
    top5_80_nan = len(top5_80_registered[top5_80_registered['evaluation'].isnull()])
        
    rows.append([category, 
                 article, 
                 article_id, 
                 maturity, 
                 total_unique, 
                 total_registered, 
                 total_unregistered, 
                 top_10_registered, 
                 top_10_unregistered,
                 top5_expert,
                 top5_not_expert,
                 top5_practitioner,
                 top5_scholar,
                 top5_unknown,
                 top5_wikipedian,
                 top5_administrator,
                 top5_has_user_page,
                 top5_nan,
                 top5_80_expert,
                 top5_80_not_expert,
                 top5_80_practitioner,
                 top5_80_scholar,
                 top5_80_unknown,
                 top5_80_wikipedian,
                 top5_80_administrator,
                 top5_80_has_user_page,
                 top5_80_nan])

new_df = pd.DataFrame(rows, columns=['category', 
                                     'article',
                                     'article_id', 
                                     'maturity', 
                                     '#distinct_users',
                                     '#registered_users',
                                     '#unregistered_users',
                                     '#top10_registered_users',
                                     '#top10_unregistered_users',
                                     'top5_expert',
                                     'top5_not_expert',
                                     'top5_practitioner',
                                     'top5_scholar',
                                     'top5_unknown',
                                     'top5_wikipedian',
                                     'top5_administrator',
                                     'top5_has_user_page',
                                     'top5_nan',
                                     'top5_80_expert',
                                     'top5_80_not_expert',
                                     'top5_80_practitioner',
                                     'top5_80_scholar',
                                     'top5_80_unknown',
                                     'top5_80_wikipedian',
                                     'top5_80_administrator',
                                     'top5_80_has_user_page',
                                     'top5_80_nan'])
new_df = new_df.sort_values(by='category', ascending=True)
new_df.head()
# -

# ### Add activity scope for each article

new_df = new_df.sort_values(by='category', ascending=True)
new_df.to_excel(r'd:/temp/test_analysis_v2.xlsx', index=False)


def average_by_column(column):
    avg_rows = []
    avg_cols = [col for col in new_df.columns if col not in ['category', 'article', 'article_id', 'maturity']]
    groups_by_column = new_df.groupby(column)
    for group_column, group in groups_by_column:
        avg_row = [group_column]
        for col in avg_cols:
            avg_row.append(np.round(np.average(group[col]), 2))
        avg_rows.append(avg_row)
    return pd.DataFrame(avg_rows, columns=[column] + avg_cols) 


by_category_df = average_by_column('category')
by_category_df.to_excel(r'd:/temp/avg_by_category_v2.xlsx', index=False)

by_maturity_df = average_by_column('maturity')
by_maturity_df.to_excel(r'd:/temp/avg_by_maturity_v2.xlsx', index=False)

StarcoderdataJupyterScriptsDedupFiltered