Exercise 5 -inconsistent-data-entry.ipynb

{"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"name":"python","version":"3.7.9","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"**This notebook is an exercise in the [Data Cleaning](https://www.kaggle.com/learn/data-cleaning) course.  You can reference the tutorial at [this link](https://www.kaggle.com/alexisbcook/inconsistent-data-entry).**\n\n---\n","metadata":{}},{"cell_type":"markdown","source":"In this exercise, you'll apply what you learned in the **Inconsistent data entry** tutorial.\n\n# Setup\n\nThe questions below will give you feedback on your work. Run the following cell to set up the feedback system.","metadata":{}},{"cell_type":"code","source":"from learntools.core import binder\nbinder.bind(globals())\nfrom learntools.data_cleaning.ex5 import *\nprint(\"Setup Complete\")","metadata":{"execution":{"iopub.status.busy":"2021-06-18T06:51:01.533181Z","iopub.execute_input":"2021-06-18T06:51:01.533653Z","iopub.status.idle":"2021-06-18T06:51:01.644550Z","shell.execute_reply.started":"2021-06-18T06:51:01.533568Z","shell.execute_reply":"2021-06-18T06:51:01.643591Z"},"trusted":true},"execution_count":1,"outputs":[{"name":"stdout","text":"Setup Complete\n","output_type":"stream"}]},{"cell_type":"markdown","source":"# Get our environment set up\n\nThe first thing we'll need to do is load in the libraries and dataset we'll be using.  We use the same dataset from the tutorial.","metadata":{}},{"cell_type":"code","source":"# modules we'll use\nimport pandas as pd\nimport numpy as np\n\n# helpful modules\nimport fuzzywuzzy\nfrom fuzzywuzzy import process\nimport chardet\n\n# read in all our data\nprofessors = pd.read_csv(\"../input/pakistan-intellectual-capital/pakistan_intellectual_capital.csv\")\n\n# set seed for reproducibility\nnp.random.seed(0)","metadata":{"execution":{"iopub.status.busy":"2021-06-18T06:51:04.502144Z","iopub.execute_input":"2021-06-18T06:51:04.502501Z","iopub.status.idle":"2021-06-18T06:51:04.518278Z","shell.execute_reply.started":"2021-06-18T06:51:04.502469Z","shell.execute_reply":"2021-06-18T06:51:04.517399Z"},"trusted":true},"execution_count":2,"outputs":[]},{"cell_type":"markdown","source":"Next, we'll redo all of the work that we did in the tutorial.","metadata":{}},{"cell_type":"code","source":"# convert to lower case\nprofessors['Country'] = professors['Country'].str.lower()\n# remove trailing white spaces\nprofessors['Country'] = professors['Country'].str.strip()\n\n# get the top 10 closest matches to \"south korea\"\ncountries = professors['Country'].unique()\nmatches = fuzzywuzzy.process.extract(\"south korea\", countries, limit=10, scorer=fuzzywuzzy.fuzz.token_sort_ratio)\n\ndef replace_matches_in_column(df, column, string_to_match, min_ratio = 47):\n    # get a list of unique strings\n    strings = df[column].unique()\n    \n    # get the top 10 closest matches to our input string\n    matches = fuzzywuzzy.process.extract(string_to_match, strings, \n                                         limit=10, scorer=fuzzywuzzy.fuzz.token_sort_ratio)\n\n    # only get matches with a ratio > 90\n    close_matches = [matches[0] for matches in matches if matches[1] >= min_ratio]\n\n    # get the rows of all the close matches in our dataframe\n    rows_with_matches = df[column].isin(close_matches)\n\n    # replace all rows with close matches with the input matches \n    df.loc[rows_with_matches, column] = string_to_match\n    \n    # let us know the function's done\n    print(\"All done!\")\n    \nreplace_matches_in_column(df=professors, column='Country', string_to_match=\"south korea\")\ncountries = professors['Country'].unique()","metadata":{"execution":{"iopub.status.busy":"2021-06-18T06:51:14.647914Z","iopub.execute_input":"2021-06-18T06:51:14.648439Z","iopub.status.idle":"2021-06-18T06:51:14.663668Z","shell.execute_reply.started":"2021-06-18T06:51:14.648384Z","shell.execute_reply":"2021-06-18T06:51:14.662702Z"},"trusted":true},"execution_count":3,"outputs":[{"name":"stdout","text":"All done!\n","output_type":"stream"}]},{"cell_type":"markdown","source":"# 1) Examine another column\n\nWrite code below to take a look at all the unique values in the \"Graduated from\" column.","metadata":{}},{"cell_type":"code","source":"# TODO: Your code here\ndf = professors['Graduated from'].unique()","metadata":{"execution":{"iopub.status.busy":"2021-06-18T06:52:00.924026Z","iopub.execute_input":"2021-06-18T06:52:00.924378Z","iopub.status.idle":"2021-06-18T06:52:00.930273Z","shell.execute_reply.started":"2021-06-18T06:52:00.924348Z","shell.execute_reply":"2021-06-18T06:52:00.928884Z"},"trusted":true},"execution_count":4,"outputs":[]},{"cell_type":"markdown","source":"Do you notice any inconsistencies in the data?  Can any of the inconsistencies in the data be fixed by removing white spaces at the beginning and end of cells?\n\nOnce you have answered these questions, run the code cell below to get credit for your work.","metadata":{}},{"cell_type":"code","source":"# Check your answer (Run this code cell to receive credit!)\nq1.check()","metadata":{"execution":{"iopub.status.busy":"2021-06-18T06:52:07.633159Z","iopub.execute_input":"2021-06-18T06:52:07.633562Z","iopub.status.idle":"2021-06-18T06:52:07.644248Z","shell.execute_reply.started":"2021-06-18T06:52:07.633525Z","shell.execute_reply":"2021-06-18T06:52:07.643148Z"},"trusted":true},"execution_count":5,"outputs":[{"output_type":"display_data","data":{"text/plain":"<IPython.core.display.Javascript object>","application/javascript":"parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.3333333333333333, \"interactionType\": 1, \"questionType\": 4, \"questionId\": \"1_ExamineProvince\", \"learnToolsVersion\": \"0.3.4\", \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\"}}, \"*\")"},"metadata":{}},{"output_type":"display_data","data":{"text/plain":"Correct: \n\nThere are inconsistencies that can be fixed by removing white spaces at the beginning and end of cells.  For instance, \"University of Central Florida\" and \" University of Central Florida\" both appear in the column.","text/markdown":"<span style=\"color:#33cc33\">Correct:</span> \n\nThere are inconsistencies that can be fixed by removing white spaces at the beginning and end of cells.  For instance, \"University of Central Florida\" and \" University of Central Florida\" both appear in the column."},"metadata":{}}]},{"cell_type":"code","source":"# Line below will give you a hint\n#q1.hint()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 2) Do some text pre-processing\n\nConvert every entry in the \"Graduated from\" column in the `professors` DataFrame to remove white spaces at the beginning and end of cells.","metadata":{}},{"cell_type":"code","source":"# TODO: Your code here\nprofessors['Graduated from'] = professors['Graduated from'].str.strip()\n\n# Check your answer\nq2.check()","metadata":{"execution":{"iopub.status.busy":"2021-06-18T06:52:45.306243Z","iopub.execute_input":"2021-06-18T06:52:45.306794Z","iopub.status.idle":"2021-06-18T06:52:45.319769Z","shell.execute_reply.started":"2021-06-18T06:52:45.306753Z","shell.execute_reply":"2021-06-18T06:52:45.318485Z"},"trusted":true},"execution_count":6,"outputs":[{"output_type":"display_data","data":{"text/plain":"<IPython.core.display.Javascript object>","application/javascript":"parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.3333333333333333, \"interactionType\": 1, \"questionType\": 1, \"questionId\": \"2_TextProcessing\", \"learnToolsVersion\": \"0.3.4\", \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\"}}, \"*\")"},"metadata":{}},{"output_type":"display_data","data":{"text/plain":"Correct","text/markdown":"<span style=\"color:#33cc33\">Correct</span>"},"metadata":{}}]},{"cell_type":"code","source":"# Lines below will give you a hint or solution code\n#q2.hint()\n#q2.solution()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 3) Continue working with countries\n\nIn the tutorial, we focused on cleaning up inconsistencies in the \"Country\" column.  Run the code cell below to view the list of unique values that we ended with.","metadata":{}},{"cell_type":"code","source":"# get all the unique values in the 'City' column\ncountries = professors['Country'].unique()\n\n# sort them alphabetically and then take a closer look\ncountries.sort()\ncountries","metadata":{"execution":{"iopub.status.busy":"2021-06-18T06:52:53.028087Z","iopub.execute_input":"2021-06-18T06:52:53.028600Z","iopub.status.idle":"2021-06-18T06:52:53.037126Z","shell.execute_reply.started":"2021-06-18T06:52:53.028567Z","shell.execute_reply":"2021-06-18T06:52:53.036232Z"},"trusted":true},"execution_count":7,"outputs":[{"execution_count":7,"output_type":"execute_result","data":{"text/plain":"array(['australia', 'austria', 'canada', 'china', 'finland', 'france',\n       'germany', 'greece', 'hongkong', 'ireland', 'italy', 'japan',\n       'macau', 'malaysia', 'mauritius', 'netherland', 'new zealand',\n       'norway', 'pakistan', 'portugal', 'russian federation',\n       'saudi arabia', 'scotland', 'singapore', 'south korea', 'spain',\n       'sweden', 'thailand', 'turkey', 'uk', 'urbana', 'usa', 'usofa'],\n      dtype=object)"},"metadata":{}}]},{"cell_type":"markdown","source":"Take another look at the \"Country\" column and see if there's any more data cleaning we need to do.\n\nIt looks like 'usa' and 'usofa' should be the same country.  Correct the \"Country\" column in the dataframe so that 'usofa' appears instead as 'usa'.\n\n**Use the most recent version of the DataFrame (with the whitespaces at the beginning and end of cells removed) from question 2.**","metadata":{}},{"cell_type":"code","source":"# TODO: Your code here!\nmatches = fuzzywuzzy.process.extract(\"usa\", countries, limit=10, scorer=fuzzywuzzy.fuzz.token_sort_ratio)\nreplace_matches_in_column(df=professors, column='Country', string_to_match=\"usa\", min_ratio=70)\n\n# Check your answer\nq3.check()","metadata":{"execution":{"iopub.status.busy":"2021-06-18T06:53:39.942489Z","iopub.execute_input":"2021-06-18T06:53:39.943102Z","iopub.status.idle":"2021-06-18T06:53:39.956106Z","shell.execute_reply.started":"2021-06-18T06:53:39.943064Z","shell.execute_reply":"2021-06-18T06:53:39.954883Z"},"trusted":true},"execution_count":8,"outputs":[{"name":"stdout","text":"All done!\n","output_type":"stream"},{"output_type":"display_data","data":{"text/plain":"<IPython.core.display.Javascript object>","application/javascript":"parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.3333333333333333, \"interactionType\": 1, \"questionType\": 1, \"questionId\": \"3_WorkingWithCities\", \"learnToolsVersion\": \"0.3.4\", \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\"}}, \"*\")"},"metadata":{}},{"output_type":"display_data","data":{"text/plain":"Correct","text/markdown":"<span style=\"color:#33cc33\">Correct</span>"},"metadata":{}}]},{"cell_type":"code","source":"# Lines below will give you a hint or solution code\n#q3.hint()\n#q3.solution()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Congratulations!\n\nCongratulations for completing the **Data Cleaning** course on Kaggle Learn!\n\nTo practice your new skills, you're encouraged to download and investigate some of [Kaggle's Datasets](https://www.kaggle.com/datasets).","metadata":{}},{"cell_type":"markdown","source":"---\n\n\n\n\n*Have questions or comments? Visit the [Learn Discussion forum](https://www.kaggle.com/learn-forum/172650) to chat with other Learners.*","metadata":{}}]}