One Click Data Cleaner Algorithm (Notebook).ipynb

{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"name":"python","version":"3.10.13","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"},"kaggle":{"accelerator":"none","dataSources":[{"sourceId":5407,"databundleVersionId":868283,"sourceType":"competition"},{"sourceId":2879186,"sourceType":"datasetVersion","datasetId":826163},{"sourceId":9167364,"sourceType":"datasetVersion","datasetId":5539338}],"dockerImageVersionId":30746,"isInternetEnabled":false,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"source":"<a href=\"https://www.kaggle.com/code/ayfait/one-click-data-cleaner-algorithm?scriptVersionId=193433223\" target=\"_blank\"><img align=\"left\" alt=\"Kaggle\" title=\"Open in Kaggle\" src=\"https://kaggle.com/static/images/open-in-kaggle.svg\"></a>","metadata":{},"cell_type":"markdown"},{"cell_type":"markdown","source":"                                         # LITERALLY AS IT SOUNDS!!!\n# This code takes in a chunk of your dataset and outputs a clean, ready-to-use version for data analysis and machine learning models. With customizable parameters, you can tailor the cleaning process to your specific needs. \nKindly adjust it to fit your taste before use. and btw, I'm using these data sources just to test. It works perfectly for ANY dataset.  ","metadata":{}},{"cell_type":"markdown","source":"# Importing Libraries ","metadata":{}},{"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load\n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\nimport re\n# Input data files are available in the read-only \"../input/\" directory\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\n\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))\n\n# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using \"Save & Run All\" \n# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","jupyter":{"source_hidden":true},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Defining Filepaths","metadata":{}},{"cell_type":"code","source":"\n#Assumes that your csv file as a single row of defined header (starting point, first row)\n\ncleanedFilepath = '/kaggle/working/cmc-gain-dataset-Clean.csv'\nfilepath = '/kaggle/input/house-prices-advanced-regression-techniques/train.csv'\nfilepath2 = '/kaggle/input/cmcgainers2/cmc-gain-dataset.csv'\ncsvFile = pd.read_csv(filepath)\nprint(csvFile.dtypes)#prints datatypes for each col\nprint(type(csvFile))#prints <class 'pandas.core.frame.DataFrame'>\nnoInputCols = len(csvFile.columns)\nprint('Total no of input cols: ', noInputCols)\n","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# FillingNull Fxn","metadata":{}},{"cell_type":"code","source":"#To fill empty cells\ndef fillNullCells(workingCol):\n    workingCol = pd.to_numeric(workingCol, errors='coerce')\n    if pd.isna(workingCol.iloc[0]):#Probably first cell in col is empty\n        colMedian = workingCol.median()\n        workingCol.iloc[0] = colMedian#interpolate doesnt fill the first cell id its empty so need to fill manually\n    workingCol.fillna(workingCol.interpolate(), inplace=True)#Using interpolate as it is best fit for time series data\n    #print(workingCol)\n    return workingCol","metadata":{"jupyter":{"source_hidden":true},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# TurnAlphabetsToLowercase Fxn","metadata":{}},{"cell_type":"code","source":"#To turn alphabets to lowercase\ndef turnAlphaLower(workingCol):\n    for idx, elmt in enumerate(workingCol):#itrs over each elmt in a single col \n        if pd.isna(elmt): #skips an empty cell\n            continue\n        try: #To avoid float * isalpha * str * strip errors when there are empty cells in the col\n            #elmt = re.sub('[^a-zA-Z0-9]+', '', str(elmt))#remove all whitespaces and special characters that negates alphabets or numbers from each elmt, no need for 'str'\n            elmt = re.sub('[^a-zA-Z0-9]+', '', elmt)#remove all other chars apart from alphas and nums\n            try: #To catch errors that will come from values other than numbers\n                floatelmt = float(elmt)\n            except: #Then value is str or unknown, i.e mix of chars\n                if not elmt.isalpha() and  not elmt.isalnum():#If value tossed here is not an alphabet and not alphanumeric\n                    csvFile.at[idx, col] = np.nan\n                else:\n                    csvFile.at[idx, col] = elmt.lower() #return the elmt in lowercase without spaces \n        except:\n            continue #skip the empty cell\n    return workingCol","metadata":{"jupyter":{"source_hidden":true},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Mapping Fxn","metadata":{}},{"cell_type":"code","source":"#To map each nonnumerical elemt to a num\ndef mapElmtToNum(workingCol):\n    nonNum = []\n    for idx, elmt in enumerate(workingCol):#itrs over each elmt in a single col \n        if pd.isna(elmt): #skips an empty cell\n            continue\n        try:\n            elmt = re.sub('[^a-zA-Z0-9]+', '', elmt)#remove all other chars apart from alphas and nums\n            try: #Try to get nums are included in the col\n                floatelmt = float(elmt)\n            except: #Then value is str or unknown, i.e mix of chars\n                nonNum.append(elmt)\n        except:\n            continue\n    nonNum = pd.unique(pd.Series(nonNum))\n    #If nums are included in the col, we need the max \n    startNum = pd.to_numeric(workingCol, errors='coerce').max()+1#Trying to get max num in the col, turning all num to float and nonnum to nan\n    if pd.isna(startNum):#All the values are probably alphabets\n        startNum = 1\n        mapped = {val: idx for idx, val in enumerate(nonNum, start=int(startNum))}\n        workingCol = workingCol.map(lambda elmt: mapped.get(elmt, elmt))\n    else: #Then there are nums\n        mapped = {val: idx for idx, val in enumerate(nonNum, start=int(startNum))}\n        workingCol = workingCol.map(lambda elmt: mapped.get(elmt, elmt))\n    workingCol = pd.to_numeric(workingCol, errors='coerce')\n    workingCol = fillNullCells(workingCol)\n    return workingCol","metadata":{"jupyter":{"source_hidden":true},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# IsCategorical Fxn","metadata":{}},{"cell_type":"code","source":"#To determine for categorical col\ndef isCategorical(workingCol):\n    #numValues = len(workingCol)\n    numValues = sum(workingCol.isna() == False)#Total num of vals in the col\n    numUniks = workingCol.nunique()#Total num of unique vals\n    numTwiceUniques = sum(workingCol.value_counts() >= 2)#Total num of vals that reapeat atb least twice\n    #print(workingCol.value_counts()) # To get a view of the freq of each val\n\n    #Using at least one value must repeat at least twice -- this works for some cols with a single value reapeats up to 80% of the time\n    if numTwiceUniques >= 1:\n        return True    \n    \n    #Using max 60% should be unique threshold\n    #isCat = (numUniks / numValues) #The ratio of unique vals to total vals\n    #print(isCat)\n    #if isCat <= 0.6:\n        #return True\n\n    #Using 60% of unique should repeat at least twice threshold\n    #isCat2 = (0.6 * numUniks)#preferred ratio\n    #print(isCat2)\n    #if isCat2 <= numTwiceUniques:\n        #return True\n\n    #OR\n    #if (numTwiceUniques / numUniks) >= 0.4:#you can adjust your threshold for a categorical col here\n        #return True\n    \n    else:\n        return None #This col is not cleaned","metadata":{"jupyter":{"source_hidden":true},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# MapCategorical","metadata":{}},{"cell_type":"code","source":"#To map Categorical vals to num\ndef mapCategorical(workingCol):\n    if isCategorical(workingCol) is True:\n        #Turn to categorical\n        workingCol = turnAlphaLower(workingCol)\n        workingCol = mapElmtToNum(workingCol)\n        return workingCol\n\n    else:\n        return None #This col is not cleaned","metadata":{"jupyter":{"source_hidden":true},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# CleaningMixed Fxn","metadata":{}},{"cell_type":"code","source":"#To clean a num col mixed with other chars\ndef cleanObjNumericalCol(workingCol):\n    numUniks = workingCol.nunique()\n    numValues = sum(workingCol.isna() == False)\n    if isCategorical(workingCol):#might be a categorical col\n        return True\n    elif numUniks >= 0.6 * numValues:#To check if the col is not categorical i.e. not repeated values\n        for idx, elmt in enumerate(workingCol):#itrs over each elmt in a single col \n            if pd.isna(elmt): #skips an empty cell\n                continue\n            elmt = re.sub('[^a-zA-Z0-9]+', '', elmt)#remove all other chars apart from alphas and nums\n            #try:\n                #floatelmt = float(elmt)\n            #except:\n                #csvFile.at[idx, col] = np.nan\n        workingCol = pd.to_numeric(workingCol, errors='coerce')\n        workingCol = fillNullCells(workingCol)\n        return workingCol\n    else:\n        return None","metadata":{"jupyter":{"source_hidden":true},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Count Values Fxn","metadata":{}},{"cell_type":"code","source":"#To count each elmt in each col\ndef countObjsCol(workingCol):\n    strCount = 0\n    alphanumericCount = 0\n    numCount = 0\n    emptyCount = 0\n    unknownCount = 0\n    #for elmt in csvFile[col]:#itrs over each elmt in a single col\n    \n    for idx, elmt in enumerate(workingCol):#itrs over each elmt in a single col \n        if pd.isna(elmt): #skips an empty cell\n            emptyCount += 1\n            continue\n        try: #To avoid float * isalpha * str * strip errors when there are empty cells in the col\n            elmt = re.sub('[^a-zA-Z0-9]+', '', elmt)#remove all other chars apart from alphas and nums ONLY from each elmt\n            if elmt.isalpha():#Checks if each elmt is an alphabet\n                strCount += 1\n                #print(elmt)#prints the alphabetical elmt\n            else:\n                try: #To catch errors that will come from values other than numbers\n                    floatnum = float(elmt)\n                    numCount += 1\n                except:\n                    if elmt.isalnum():#For alphanum chars\n                        alphanumericCount += 1 \n                    else:\n                        unknownCount += 1 #Then value is unknown, i.e mix of chars \n                    continue #skip the value\n                \n        except:\n            continue #skip\n    return strCount, alphanumericCount, numCount, emptyCount, unknownCount","metadata":{"jupyter":{"source_hidden":true},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Main Program","metadata":{}},{"cell_type":"code","source":"#Main Operation\nnewCols = []\ndeletedCols = []\nfor col in csvFile.columns:#itrs over all cols at once, but a single col with index def\n    #pass\n    #print(csvFile[col]) #Prints the current col\n    workingCol = csvFile[col]\n\n    if csvFile[col].dtype == int: #Its perfect\n        newCols.append(col)\n        continue\n\n    elif csvFile[col].dtype == float: #Might need fixing\n        strCount, alphanumericCount, numCount, emptyCount, unknownCount = countObjsCol(workingCol)\n        if emptyCount >= 0.4 * len(workingCol): #Probably almost all cells are empty\n            deletedCols.append(col)\n            csvFile.drop(col, axis=1, inplace=True)\n            #print(col)\n        else:\n            newCols.append(col)\n            csvFile[col] = fillNullCells(csvFile[col])\n            #print(csvFile[col])\n        \n\n    elif workingCol.dtype == object: #Checks for each cols first\n        strCount, alphanumericCount, numCount, emptyCount,  unknownCount = countObjsCol(workingCol)\n        values = strCount + alphanumericCount + numCount\n        \n        #print(col)\n        #print('String: ', strCount)\n        #print('Alphanumeric: ', alphanumericCount)\n        #print('Nums: ', numCount)\n        #print('EmptyCells: ', emptyCount)\n        \n        if numCount >= (0.8 * values):#Incase a numerical col is mixed with few other chars\n            #print(workingCol)\n            if cleanObjNumericalCol(csvFile[col]) is True:#means it's categorica. Can use if isCategorical(csvFile[col]) directly \n                #print(col)\n                newCols.append(col)\n                csvFile[col] = mapCategorical(csvFile[col])\n\n            elif cleanObjNumericalCol(csvFile[col]) is None:\n                deletedCols.append(col)\n                csvFile.drop(col, axis=1, inplace=True)\n            else:\n                newCols.append(col)\n                csvFile[col] = cleanObjNumericalCol(csvFile[col])#Num col with some empty cells\n                #print(csvFile[col])\n\n        #This works well for cols with more alpha chars than the others\n        elif (strCount + alphanumericCount + numCount) >= (0.6 * len(workingCol)): #This ratio means cols is likely mixed up with invalid data\n            #To determine for Categorical col and then map\n            if isCategorical(csvFile[col]) is None:\n                deletedCols.append(col)\n                csvFile.drop(col, axis=1, inplace=True)#Means its not categorical so delete col\n            else:\n                newCols.append(col)\n                csvFile[col] = mapCategorical(csvFile[col])\n                #print(csvFile[col])\n                #print(col)#col name\n\n        else:\n            deletedCols.append(col)\n            #print(workingCol)\n            csvFile.drop(col, axis=1, inplace=True)#Means its not categorical so delete col\n            \n    else:\n        deletedCols.append(col)\n        csvFile.drop(col, axis=1, inplace=True)#Del any other col\n\nprint(csvFile.dtypes)\nprint()\ncsvFile.to_csv(cleanedFilepath, index=False)\nnoOutputCols = len(csvFile.columns)\nremovedCols = noInputCols - noOutputCols\nprint(f'Total of {noOutputCols} output cols: {newCols}')\nprint()\nprint(f'Total of {removedCols} cols removed: {deletedCols}')\nprint()\nprint('Cleaned Data Exported Successfully')","metadata":{"trusted":true},"execution_count":null,"outputs":[]}]}