<jupyter_start><jupyter_text>File built on the mechanics file<jupyter_code>import requests
from lxml import etree
from bs4 import BeautifulSoup
import pandas as pd
import numpy as np
import time
import sqlite3
import statsmodels.formula.api as smf
def add_dataframe_rank(dataframe, rank_column, is_ascending=False):
    """
    Adds a ranking column to the dataframe based on sort value
    
    Note that this function changes a dataframe "in place", so it does not return anything
    
    Attributes
    ----------
    dataframe: pandas DataFrame
        Dataframe that will have ranking column added to it
    
    rank_column: string
        Name of column by which dataframe will be sorted
        
    is_ascending: bool
        If true, sort dataframe in ascending order (smallest value first)
    """
    
    dataframe.sort_values(rank_column, ascending= is_ascending, inplace=True)
    dataframe.reset_index(inplace=True)
    dataframe['Rank'] = dataframe.index + 1 # so the top ranking game is #1 rather than #0
    dataframe.drop(['index'], axis=1, inplace=True)
con = sqlite3.connect('bgg.db')
cur = con.cursor()
query = '''
 SELECT t1.id, title, CAST(avg_rating AS FLOAT64) avg_rating, CAST(avg_bayes_rating AS FLOAT64),
        CAST(std as FLOAT64) std, category
 
 FROM TOP_GAMES_FULL t1
   LEFT JOIN BGG_CATEGORIES t2
     ON t1.id = t2.id
     
ORDER BY avg_rating DESC
'''

results = cur.execute(query)
result_li = [elem for elem in results.fetchall()]
df = pd.DataFrame(result_li, columns = ['id', 'Title','Score', 'BayesScore', 'StdDev', 'Category'])
pivot = pd.pivot_table(data=df, index='Category', values = ['id'], aggfunc='count')
pivot.columns = ['Games']
pivot.sort_values('Games', ascending=False,inplace=True)
pivot.head(5)
pivot.tail(5)
unpopular_cat_li = list(pivot[pivot['Games'] < 30].index)
# What are the top economic games?
df[df.Category == 'Economic'].head(10)
# What is a fighting game? I don't think of any board game as being a fighting game
df[df.Category == 'Fighting'].head(10)<jupyter_output><empty_output><jupyter_text>Fighting appears to mean "has combat." Different than the video game meaning of "fighting games," which usually entails a two-player game with combos etc<jupyter_code># My favorite: abstract strategy?
df[df.Category == 'Abstract Strategy'].head(10)
# Remove unpopular categories
df = df[~df['Category'].isin(unpopular_cat_li)]
cat_popularity = pd.pivot_table(df, index='Category',values='Score', aggfunc =['count','mean'])
cat_popularity.sort_values(('mean', 'Score'), ascending=False, inplace=True)
cat_popularity.head(10)
std_pivot = pd.pivot_table(df, index='Category', values =['Score'], aggfunc=['mean', 'std', 'count'])
std_pivot.sort_values(('mean', 'Score'), ascending=False).head(5)<jupyter_output><empty_output><jupyter_text>Given the lack of variability for scores, let's test to see if there is a difference between the first and last score<jupyter_code>from scipy.stats import ttest_ind
best_mechanic = df.loc[df['Category'] == 'Wargame', 'Score']
worst_mechanic = df.loc[df['Category'] == 'Party Game', 'Score']
ttest_ind(a=best_mechanic, b=worst_mechanic,equal_var=False)<jupyter_output><empty_output><jupyter_text>Pretty clearly significant. Side note: why is "wargame" one word and "party game" two words? Some questions can't be answered by data<jupyter_code># What are the most divisive categories?
std_pivot.sort_values(by=('std','Score'), ascending=False).head(5)
# The least?
std_pivot.sort_values(by=('std','Score'), ascending=True).head(5)<jupyter_output><empty_output><jupyter_text>Interesting: Party games are the lowest rated, but least divisive. People agree that they're the worst type of game in the top 1000 I guess. Similar for abstract strategy and deduction. Next question: is the difference in ratings driven by weight?<jupyter_code>query = '''
WITH inner_table AS( 
SELECT t1.id, title, CAST(avg_rating AS FLOAT64) avg_rating,
        CAST(std as FLOAT64) std, category, CAST(weight AS FLOAT64) weight,
        COUNT(t1.id) OVER (PARTITION BY category) NumOfGames
 
 FROM TOP_GAMES_FULL t1
   LEFT JOIN BGG_CATEGORIES t2
     ON t1.id = t2.id)
     
SELECT id, title, avg_rating, std, category, weight, NumOfGames

FROM inner_table

WHERE 1=1
    AND NumOfGames >= 30
     
ORDER BY avg_rating DESC
'''

results = cur.execute(query)
result_li = [elem for elem in results.fetchall()]
weight_df = pd.DataFrame(data = result_li, 
                         columns = ['id', 'title', 'score', 'std', 'category', 'weight', 'NumOfGames'])
weight_pivot = pd.pivot_table(data=weight_df, index='category',
                             values = ['weight', 'score'], aggfunc='mean')
weight_pivot.sort_values('weight',ascending=False).head(5)
weight_pivot.corr()
weight_pivot['weight_sq']= weight_pivot['weight'] ** 2
result = smf.ols(data=weight_pivot, formula='score~weight+weight_sq').fit()
result.summary()
del weight_pivot['weight_sq']<jupyter_output><empty_output><jupyter_text>Regression does not support a nortion of diminishing returns to weight. higher = better Each point of weight is worth 0.233 towards a score. I will put forth a simplistic adjustment to scores in order to "remove" the impact of weight on a game's score. From there, I will see if the same rankings remaining<jupyter_code>dedup_df = weight_df.drop_duplicates(subset='id', keep='first')
dedup_df.head()
result = smf.ols(data=dedup_df, formula='score~weight').fit()
params = result.params
beta_0, beta_1 = params['Intercept'], params['weight']
print(beta_1)
weight_df['score_adj'] = weight_df['score'] - beta_1 * weight_df['weight']<jupyter_output><empty_output><jupyter_text>Next, we will compare the rankings with the original score and with the weight adjustment<jupyter_code>new_pivot = pd.pivot_table(data=weight_df, index = 'category', values='score_adj', aggfunc=['count','mean'])
new_pivot.reset_index(inplace=True)
new_pivot.columns = ['category','count', 'score']
add_dataframe_rank(new_pivot, 'score')
# Need to make some fixes to the old pivot table before comparing
weight_pivot.reset_index(inplace=True)
weight_pivot.columns = ['category', 'score', 'weight']
add_dataframe_rank(weight_pivot,'score')
# Simplify adjusted dataframe
new_pivot = new_pivot[['category', 'Rank']]
new_pivot.rename(columns = {'Rank': 'Adj_Rank'}, inplace=True)

# Simplify original dataframe
weight_pivot = weight_pivot[['category','Rank']]
weight_pivot.rename(columns = {'Rank': 'Original_Rank'}, inplace=True)
merge_df = new_pivot.merge(weight_pivot, how='inner', on='category')
merge_df['Delta'] = merge_df['Adj_Rank'] - merge_df['Original_Rank']
merge_df.sort_values('Delta', ascending=True)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12389645368846326, 'fraction_numerical': 0.00942690408499177, 'mean_word_length': 5.664007976071785, 'pattern_counts': {'":': 0, '<': 30, '<?xml version=': 0, '>': 30, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 1}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '196386', 'license': 'MIT', 'n_tokens_mistral': 2268, 'n_tokens_neox': 2166, 'n_words': 606, 'path': 'BestCategories.ipynb', 'repo_name': 'MarshallKrakauer/BGGAnalysis', 'size': '6683'}