<jupyter_start><jupyter_text>Notebook 2: Data CleaningThis Notebook aims to clean and prep the data for analysis Data cleaningThe final dataset contains several columns with many values (subdictionaries).To handle these, I will identify all columns containing desired data, remove unneccessary columns and use the apply function to convert the multidimentional columns into their own dataframes that can be appened back onto the original dataframe.  Text CleaningAny text data will undergo additional cleaning to prepare it for analysis, including converting the text to lowercase, removing symbols and punctuation, and generally tidying the data.This concludes the goals of this notebook. A cleaned dataset will be save and the next stage of EDA will occur in the next notebook in this series.<jupyter_code># standard library imports
import csv
import datetime as dt
import json
import os
import statistics
import time

import numpy as np
import pandas as pd
pd.set_option('display.max_rows', 100)
pd.set_option('display.max_columns', 100)
#import raw data

GameData = pd.read_csv('RawSteamGameData2.csv',  engine='python')
GameData.info()
GameData.isnull().sum()
#drop fields with a lot of mising data and fields that are unneccessary for the current analysis.
# Dropping 'drm_notice', 'alternate_appid','score_rank', 'ext_user_account_notice', 'demos', 'dlc', 'recommendations  high number of missing data
# Dropping 'legal_notice', 'header_image', 'website', 'pc_requirements', 'packages','mac_requirements', 'linux_requirements', 'screenshots', 'movies', due to irrelevance
#Dropping duplicated developer, publisher, reviews, price overview, languages
#Acheivements, dlc, and controller support are all covered in categories
#Only 250 games include metacritic scores. I will keep the field however, as I may decide to use it during exploratory analysis, or if I expand the number of games collected

GameData = GameData[['Unnamed: 0', 'type', 'name', 'steam_appid', 'required_age', 'is_free',
       'detailed_description', 'about_the_game', 'short_description',
       'supported_languages', 'developers', 'publishers', 'platforms',
       'categories', 'genres', 'release_date','content_descriptors', 'Review Score',
       'Review Score Description', 'Top Reviews by Upvotes', 'appid',
       'positive', 'negative', 'userscore', 'owners', 'average_forever', 'average_2weeks',
       'median_forever', 'median_2weeks', 'price', 'initialprice', 'discount',
       'ccu', 'genre', 'tags', 'metacritic'
       ]]
GameData.describe(include = 'all')
GameData.head()<jupyter_output><empty_output><jupyter_text>A major issue with this data are the collumns that contain multiple datapoints. There are 3 formats this takes in  the data frame right now:- a dictionary- a list stored as a string- a list of dictionaries stored as stringsI also need to convert the acheivements value into a binary (I don't care what the acheivements are, only whether or not it's a feature in the game).<jupyter_code>#Expand columns containing multiple datapoints
#https://stackoverflow.com/questions/38231591/split-explode-a-column-of-dictionaries-into-separate-columns-with-pandas

# I will start by handling dictionary lists stored as strings 
# create a function to convert the lists of dictionaries stored as strings to dictionaries for processing
# https://www.geeksforgeeks.org/python-convert-string-to-list-of-dictionaries/

def makelist(DictStrList):
    ''' 
    Takes list of dictionaries stored as a string and converts to a list of dictionaries
    '''
    try:    
        if len(DictStrList.split(', '))==2:
            x = eval(DictStrList)
        else:
            x = list(eval(DictStrList))
    except: 
        x = DictStrList
        # print(DictStrList)
    return x
# Handle categories
#desired output 1 column for each category type with a value indicating whether or not the game includes that value
#Start by finding each category name:
GameData2 = GameData
GameData2['categories'] = GameData2['categories'].apply(makelist)
GameData2 = GameData2.explode('categories')
categories = pd.concat([GameData2.drop(['categories'], axis=1), GameData2['categories'].apply(pd.Series)], axis=1)
uniquecats = list(categories['description'].unique()) #create list of unique categories
uniquecats #remove categories not in english
#remove categories not in english
remove = ['多人', '合作','部分支持控制器', '包含关卡编辑器', 'Steam 排行榜', '玩家对战', '线上玩家对战', '同屏/分屏玩家对战', '同屏/分屏',
       '统计数据', '远程同乐', '在线合作', 'Steam 创意工坊', '单人', 'Steam 成就', '完全支持控制器', 'Steam 集换式卡牌','Steam 云']
catlist = [cat for cat in uniquecats if cat not in remove]
#remove nan value
catlist = [cat for cat in catlist if pd.isnull(cat) == False]
catlist
# GameData['categories']
#Next iterate through list and create lists for each category to append
def valueoffered(dfCol, valuelist):
    ''' 
    Takes a data frame column in string  format and list of strings and iterates through the column looking for those strings
    returns a dataframe with a column for each value.
    '''
    valueDict = {}
    for value in valuelist: #start a list in a dictionary for each category or other variable
        valueDict[value] = []
    print(valueDict)
    for row in dfCol:
        try:
            for value in valuelist:
                if value in row: #if the game has that category tag, add a 1 to the category column, else add a 0
                    valueDict[value].append(1)
                else:
                    valueDict[value].append(0)
        except: #handle Nans
            for value in valuelist:
                valueDict[value].append(0)
    return pd.DataFrame(valueDict)

CategoryDF = valueoffered(GameData['categories'].astype(str), catlist)
CategoryDF
#add category df to GameData
GameData = pd.concat([GameData, CategoryDF], axis=1)
GameData.head().T
#handle genres
GameData2 = GameData
GameData2['genres'] = GameData2['genres'].apply(makelist)
GameData2 = GameData2.explode('genres')
genres =  GameData2['genres'].apply(pd.Series)
uniquegenres = list(genres['description'].unique()) #create list of unique genres
uniquegenres #remove genres not in english

#handle genres
#remove genres not in english
remove = ['抢先体验','策略','角色扮演','模拟', '免费开玩','休闲', '动作', '独立','冒险',]
genlist = [gen for gen in uniquegenres if gen not in remove]
#remove nan value
genlist = [gen for gen in genlist if pd.isnull(gen) == False]
genlist
GenreDF = valueoffered(GameData['genres'].astype(str),genlist)
GenreDF.head()
#add Genre df to GameData
GameData = pd.concat([GameData, GenreDF], axis = 1)<jupyter_output><empty_output><jupyter_text>Next I will handle fields that are stored as dictionaries by turning the column of dictionaries into a dataframe and appending to the original dataset.<jupyter_code># convert nans to empty dict
def handleNaN(col):
    newlist = []
    for item in col:
        if str(item) == 'nan':
            newlist.append({})
        else:
            newlist.append(item)
    return newlist

GameData['metacritic'] = handleNaN(GameData['metacritic'])
GameData['platforms'] = handleNaN(GameData['platforms'])
GameData['release_date'] = handleNaN(GameData['release_date'])
GameData['content_descriptors'] = handleNaN(GameData['content_descriptors'])

# https://stackoverflow.com/questions/38231591/split-explode-a-column-of-dictionaries-into-separate-columns-with-pandas

platforms = pd.json_normalize(GameData['platforms'].astype(str).apply(eval))
release = pd.json_normalize(GameData['release_date'].astype(str).apply(eval))
content = pd.json_normalize(GameData['content_descriptors'].astype(str).apply(eval))
metacritic = pd.json_normalize(GameData['metacritic'].astype(str).apply(eval))
#Rename columns
release = release.rename({'date':'Release Date'})
content = content.rename({'notes': 'Content Notes'})
content = content.drop(columns = 'ids')
metacritic = metacritic.rename({'score':'Metacritic Score'})
metacritic = metacritic.drop(columns = 'url')
#Add all new data frames back into the original

GameData = pd.concat([GameData, platforms, release, content, metacritic], axis = 1)
GameData.head()<jupyter_output><empty_output><jupyter_text>Text Data CleaningTarget fields:- detailed description- short description- about the game- top reviews by upvote- tags (note, I am treating tags as a text field because there are so many possible tags a game can have)Initial Cleaning goals:- Send to lowercase- remove punctuation and symbols- remove stopwords<jupyter_code>import string
import re
import nltk
from nltk.corpus import stopwords

nltk.download("stopwords")
stop_words = set(stopwords.words("english"))

# https://stackoverflow.com/questions/9662346/python-code-to-remove-html-tags-from-a-string
# https://stackoverflow.com/questions/33404752/removing-emojis-from-a-string-in-python

# preprocessing for tags 
GameData['tags'] = GameData['tags'].str.strip('[]')
textfields = ['detailed_description', 'about_the_game', 'short_description', 'Top Reviews by Upvotes', 'tags']

def CleanText(DF, ColList, stop_words):
    '''
    Input: Dataframe containing text data, list of columns to clean, stopwords to remove
    Output: Dataframe containing cleaned text data
    '''
    CleanedCol = {}
    for col in ColList:
        stringlist = []
        for line in DF[col]:
            #remove html tags and text in brackets
            cleanstring = re.sub(re.compile('<.*?>') , '', str(line))
            cleanstring = re.sub(re.compile('\[.*?]') , ' ', cleanstring)
            cleanstring = re.sub(re.compile('\n') , ' ', cleanstring)
            #Remove stop words and sent to lowercase
            cleanstring = " ".join([word.lower() for word in str(cleanstring).split() if word.lower() not in stop_words])
            #Remove emojis and icons
            icons = re.compile("["u"\U0001F600-\U0001F64F"  # emoticons
                        u"\U0001F300-\U0001F5FF"  # symbols & pictographs
                        u"\U0001F680-\U0001F6FF"  # transport & map symbols
                        u"\U0001F1E0-\U0001F1FF"  # flags (iOS)
                        u"\U00002500-\U00002BEF"  # chinese char
                        "]+", re.UNICODE)
            cleanstring = re.sub(icons, '', cleanstring)
            #Remove punctuation
            for punctuation in string.punctuation: 
                cleanstring = cleanstring.replace(punctuation, '')
            
            stringlist.append(cleanstring)
        CleanedCol.update({col:stringlist})
    return pd.DataFrame(CleanedCol)

CleanData = CleanText(GameData, textfields, stop_words=stop_words)
CleanData

Clean_GameData = pd.concat([GameData, CleanData], axis = 1)
from google.colab import files
Clean_GameData.to_csv('CleanSteamGameData.csv') 
files.download('CleanSteamGameData.csv')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10933432502789141, 'fraction_numerical': 0.012365191521011528, 'mean_word_length': 4.206679574056147, 'pattern_counts': {'":': 0, '<': 18, '<?xml version=': 0, '>': 18, 'https://': 5, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '16991', 'license': 'CC0-1.0', 'n_tokens_mistral': 3336, 'n_tokens_neox': 3214, 'n_words': 1007, 'path': 'Notebooks/2.Steam_Data_Cleaning.ipynb', 'repo_name': 'Jcc329/Jessica_DATA606', 'size': '10756'}