<jupyter_start><jupyter_text>Data loading and preprocessingLoad data from excel file, drop unused columns, replace missing values indicators (like tbd) with NaNs,convert column types from object to numeric if necessary.<jupyter_code>columns_to_drop=["Other_Sales", "Critic_Count", "User_Count", "Developer", "Publisher", "Rating"]
numeric_columns = ["Global_Sales", "User_Score", "Critic_Score", "EU_Sales", "NA_Sales", "JP_Sales", "Year_of_Release"]

data = pd.read_excel("../data/games_sales_2016_modified.xlsx", index_col=0)
data = data.drop(columns=columns_to_drop)
data = data.replace({'tbd': np.NaN})
data["Critic_Score"] = pd.to_numeric(data["Critic_Score"])
data["User_Score"] = pd.to_numeric(data["User_Score"])<jupyter_output><empty_output><jupyter_text>Basic statistics before missing values handlingGet basic statistics about the dataset before handling missing values: * total number of rows * number of missing values in each column * values like mean, max, min<jupyter_code>print("Number of rows: {}".format(data.shape[0]))
print("\nNumber of NaNs in each column: \n{}".format(data.isna().sum()))

stats_to_compute = ["max", "min", "std", "mean", "median"]
columns_stats = data.agg({item: stats_to_compute for item in numeric_columns})
print("\nDetailed stats: \n{}".format(columns_stats))<jupyter_output>Number of rows: 16710

Number of NaNs in each column: 
Name                  2
Platform              0
Year_of_Release      74
Genre                 2
NA_Sales              0
EU_Sales              0
JP_Sales              0
Global_Sales          0
Critic_Score       8544
User_Score         9085
dtype: int64

Detailed stats: 
        Global_Sales  User_Score  Critic_Score   EU_Sales   NA_Sales  \
max        82.530000    9.700000     99.000000  28.960000  41.360000   
min         0.010000    0.000000     13.000000   0.000000   0.000000   
std         1.548316    1.501310     13.951209   0.503408   0.813713   
mean        0.533778    7.129456     69.022410   0.145100   0.263459   
median      0.170000    7.500000     71.000000   0.020000   0.080000   

         JP_Sales  Year_of_Release  
max     10.220000      2016.000000  
min      0.000000      1977.000000  
std      0.308895         5.912285  
mean     0.077609      2006.463092  
median   0.000000      2007.000000<jupyter_text>Handling missing valuesThere are no missing values in EU_Sales, JP_Sales, NA_Sales and Global_Sales columns.Some columns should be filled manually, while others can be generated (for example somecritic and user scores). Manual missing and strange values handling: * over 100 records with `Year_of_Release` have been filled (there are still 74 left, which will be discarded) * records with strange names like **Luminous Arc 2 (JP sales)** have been removed (at least 8) * **Brothers in Arms: Furious 4** record has been removed because this game has been cancelled * **Imagine: Makeup Artist** had `Year_of_Release` set to 2020 - changed to proper 2009 * missing `Critic_Score` and `User_Score` for important games (like **Super Mario**) filled manually, using GameSpot data * missing values in `Published` column for important games filled manually, but this column has been discarded Remove entries without release date or without name<jupyter_code>data = data.drop(data[data["Year_of_Release"].isna()].index)
data = data.drop(data[data["Name"].isna()].index)<jupyter_output><empty_output><jupyter_text>Remove games without Critic or User Score with global sales below 0.2m: * this is done because for small global sales there is a lot of missing data * filling it would probably have a negative impact on further analysis results<jupyter_code>data = data.drop(data[
    (data["Critic_Score"].isna() | data["User_Score"].isna()) &
    (data["Global_Sales"] < 0.2)
].index)<jupyter_output><empty_output><jupyter_text>Standardize data before proceeding further into missing values generation<jupyter_code>standardization = StandardScaler().fit(data[numeric_columns])
data[numeric_columns] = standardization.transform(data[numeric_columns])
print(data)<jupyter_output>Name Platform  Year_of_Release  \
5140                                  Wii Sports      Wii        -0.000599   
16561                          Super Mario Bros.      NES        -3.460187   
5899                              Mario Kart Wii      Wii         0.328886   
5100                           Wii Sports Resort      Wii         0.493628   
15741                   Pokemon Red/Pokemon Blue       GB        -1.648022   
...                                          ...      ...              ...   
7553   <NAME>' Tournament Paintball Max'd      PS2        -0.000599   
7569                                     Deus Ex       PC        -0.989053   
7601                   Monster Rancher Advance 2      GBA        -0.659568   
7605                               <NAME>      GBA        -0.494826   
7606                 Wade Hixton's Counter Punch      GBA        -0.330084   

              Genre   NA_Sales   EU_Sales   JP_Sales  Global_Sa[...]<jupyter_text>Generate missing User Score and Critic Score values (using KNN method)<jupyter_code>print("\nNumber of NaNs in each column: \n{}".format(data.isna().sum()))
data = data.reset_index(drop=True)
data[numeric_columns] = pd.DataFrame(KNNImputer(n_neighbors=10).fit_transform(data[numeric_columns]), columns=numeric_columns)<jupyter_output>Number of NaNs in each column: 
Name                  0
Platform              0
Year_of_Release       0
Genre                 0
NA_Sales              0
EU_Sales              0
JP_Sales              0
Global_Sales          0
Critic_Score       3105
User_Score         3125
dtype: int64<jupyter_text>Reverse standardization and display statistics after handling missing values<jupyter_code># reverse standardization
data[numeric_columns] = standardization.inverse_transform(data[numeric_columns])

# compute and display statistics
print("Number of rows: {}".format(data.shape[0]))
print("\nNumber of NaNs in each column: \n{}".format(data.isna().sum()))
columns_stats = data.agg({item: stats_to_compute for item in numeric_columns})
print("\nDetailed stats: \n{}".format(columns_stats))<jupyter_output>Number of rows: 10452

Number of NaNs in each column: 
Name               0
Platform           0
Year_of_Release    0
Genre              0
NA_Sales           0
EU_Sales           0
JP_Sales           0
Global_Sales       0
Critic_Score       0
User_Score         0
dtype: int64

Detailed stats: 
        Global_Sales  User_Score  Critic_Score   EU_Sales   NA_Sales  \
max        82.530000    9.600000     99.000000  28.960000  41.360000   
min         0.010000    0.000000     13.000000   0.000000   0.000000   
std         1.904295    1.302307     12.435271   0.622661   1.002670   
mean        0.810365    7.270369     70.916772   0.225307   0.402908   
median      0.360000    7.500000     72.400000   0.070000   0.170000   

         JP_Sales  Year_of_Release  
max     10.220000      2016.000000  
min      0.000000      1977.000000  
std      0.385861         6.070377  
mean     0.108312      2006.003636  
median   0.000000      2007.000000<jupyter_text>Save modified dataset to file<jupyter_code>data.to_excel("../data/games_sales_2016_preprocessed.xlsx")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08352827851933398, 'fraction_numerical': 0.10155497454245219, 'mean_word_length': 2.590909090909091, 'pattern_counts': {'":': 0, '<': 32, '<?xml version=': 0, '>': 31, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 6, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '128458', 'license': 'MIT', 'n_tokens_mistral': 2718, 'n_tokens_neox': 2174, 'n_words': 626, 'path': 'notebooks/data_preprocessor.ipynb', 'repo_name': 'arokasprz100/Data-Mining-Project', 'size': '7281'}