28-Data-Wrangling.jupyterlite.ipynb

{"cells":[{"cell_type":"markdown","id":"314004d5-a31b-4edd-a85b-c796c4006c95","metadata":{},"outputs":[],"source":["\u003cp style=\"text-align:center\"\u003e\n","    \u003ca href=\"https://skills.network\" target=\"_blank\"\u003e\n","    \u003cimg src=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/assets/logos/SN_web_lightmode.png\" width=\"300\" alt=\"Skills Network Logo\"\u003e\n","    \u003c/a\u003e\n","\u003c/p\u003e\n","\n","\n","# Data Wrangling\n","\n","\n","Estimated time needed: **30** minutes\n","    \n","\n","## Objectives\n","\n","After completing this lab you will be able to:\n","\n","* Handle missing values\n","* Correct data formatting\n","* Standardize and normalize data\n"]},{"cell_type":"markdown","id":"60375665-f1e5-4f0b-b4db-7503048179d7","metadata":{},"outputs":[],"source":["\u003ch2\u003eTable of Contents\u003c/h2\u003e\n","\n","\u003cdiv class=\"alert alert-block alert-info\" style=\"margin-top: 20px\"\u003e\n","\u003cul\u003e\n","    \u003cli\u003e\u003ca href=\"#Identify-and-handle-missing-values\"\u003eIdentify and handle missing values\u003c/a\u003e\n","        \u003cul\u003e\n","            \u003cli\u003e\u003ca href=\"#Identify-missing-values\"\u003eIdentify missing values\u003c/a\u003e\u003c/li\u003e\n","            \u003cli\u003e\u003ca href=\"#Deal-with-missing-data\"\u003eDeal with missing values\u003c/a\u003e\u003c/li\u003e\n","            \u003cli\u003e\u003ca href=\"#Correct-data-format\"\u003eCorrect data format\u003c/a\u003e\u003c/li\u003e\n","        \u003c/ul\u003e\n","    \u003c/li\u003e\n","    \u003cli\u003e\u003ca href=\"#Data-Standardization\"\u003eData standardization\u003c/a\u003e\u003c/li\u003e\n","    \u003cli\u003e\u003ca href=\"#Data-Normalization\"\u003eData normalization (centering/scaling)\u003c/a\u003e\u003c/li\u003e\n","    \u003cli\u003e\u003ca href=\"#Binning\"\u003eBinning\u003c/a\u003e\u003c/li\u003e\n","    \u003cli\u003e\u003ca href=\"#Indicator-Variable\"\u003eIndicator variable\u003c/a\u003e\u003c/li\u003e\n","\u003c/ul\u003e\n","    \n","\u003c/div\u003e\n"," \n","\u003chr\u003e\n"]},{"cell_type":"markdown","id":"5b12b7e9-bc09-49dd-88c7-b30b14c35597","metadata":{},"outputs":[],"source":["\u003ch2\u003eWhat is the purpose of data wrangling?\u003c/h2\u003e\n"]},{"cell_type":"markdown","id":"16a5933f-a9b1-4165-a007-eb777ee7ee39","metadata":{},"outputs":[],"source":["You use data wrangling to convert data from an initial format to a format that may be better for analysis.\n"]},{"cell_type":"markdown","id":"a5daed33-cdb3-4b87-b788-9e0a940403e9","metadata":{},"outputs":[],"source":["\u003ch3\u003eWhat is the fuel consumption (L/100k) rate for the diesel car?\u003c/h3\u003e\n"]},{"cell_type":"markdown","id":"4f7d3148-065b-44a0-aba5-dcc488096f97","metadata":{},"outputs":[],"source":["\u003ch3\u003eImport data\u003c/h3\u003e\n","\u003cp\u003e\n","You can find the \"Automobile Dataset\" from the following link: \u003ca href=\"https://archive.ics.uci.edu/ml/machine-learning-databases/autos/imports-85.data\"\u003ehttps://archive.ics.uci.edu/ml/machine-learning-databases/autos/imports-85.data\u003c/a\u003e. \n","You will be using this data set throughout this course.\n","\u003c/p\u003e\n"]},{"cell_type":"markdown","id":"6ab8990d-f1d9-4d1e-86d3-da06c4912f02","metadata":{},"outputs":[],"source":["\u003ch4\u003eImport pandas\u003c/h4\u003e \n"]},{"cell_type":"code","id":"77455db7-4dfb-4792-9d49-8362f2f61336","metadata":{},"outputs":[],"source":["#install specific version of libraries used in lab\n#! mamba install pandas==1.3.3\n#! mamba install numpy=1.21.2\n"]},{"cell_type":"code","id":"b7b96130-1592-4fcc-8ecb-9f8cdfd4f102","metadata":{},"outputs":[],"source":["import pandas as pd\nimport matplotlib.pylab as plt"]},{"cell_type":"markdown","id":"43670e1f-4a6a-4e11-8a32-325ac88de25c","metadata":{},"outputs":[],"source":["\u003ch2\u003eReading the dataset from the URL and adding the related headers\u003c/h2\u003e\n"]},{"cell_type":"markdown","id":"6cb38ba3-fd3c-4b66-ba7c-34135550cbc8","metadata":{},"outputs":[],"source":["First, assign the URL of the data set to \"filepath\". Then download the dataset to the interface by executing the cell below.\n"]},{"cell_type":"markdown","id":"99a7d83e-869a-4141-82bc-5827c27d0339","metadata":{},"outputs":[],"source":["This dataset was hosted on IBM Cloud object. Click \u003ca href=\"https://cocl.us/corsera_da0101en_notebook_bottom\"\u003eHERE\u003c/a\u003e for free storage.\n"]},{"cell_type":"code","id":"d01e2c5b-e3c7-4b48-ad3d-5e6347857878","metadata":{},"outputs":[],"source":["import piplite\nawait piplite.install('skillsnetwork')\nimport skillsnetwork\n\nfilepath = \"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DA0101EN-SkillsNetwork/labs/Data%20files/auto.csv\"\nawait skillsnetwork.download(filepath,'./usedcars.csv')\nfilename = './usedcars.csv'"]},{"cell_type":"markdown","id":"9583b989-cc5e-4fb8-b8b4-aa61b8ef82cc","metadata":{},"outputs":[],"source":["\u003eNote: In case you are using the lab offline on your local machines, you may use `filepath` directly in `pd.read_csv()` to access the dataset directly from the link.\n"]},{"cell_type":"markdown","id":"c92c7e95-2224-47f1-990a-f91113aeccff","metadata":{},"outputs":[],"source":["Then, create a Python list \u003cb\u003eheaders\u003c/b\u003e containing name of headers.\n"]},{"cell_type":"code","id":"47a3d793-3e98-482f-8976-d90297232529","metadata":{},"outputs":[],"source":["headers = [\"symboling\",\"normalized-losses\",\"make\",\"fuel-type\",\"aspiration\", \"num-of-doors\",\"body-style\",\n         \"drive-wheels\",\"engine-location\",\"wheel-base\", \"length\",\"width\",\"height\",\"curb-weight\",\"engine-type\",\n         \"num-of-cylinders\", \"engine-size\",\"fuel-system\",\"bore\",\"stroke\",\"compression-ratio\",\"horsepower\",\n         \"peak-rpm\",\"city-mpg\",\"highway-mpg\",\"price\"]"]},{"cell_type":"markdown","id":"30abe7cc-24b1-4429-8db0-7926954cae60","metadata":{},"outputs":[],"source":["Use the Pandas method \u003cb\u003eread_csv()\u003c/b\u003e to load the data from the web address. Set the parameter  \"names\" equal to the Python list \"headers\".\n"]},{"cell_type":"code","id":"5f296bb6-6a44-4267-b2bf-85011377ade7","metadata":{},"outputs":[],"source":["df = pd.read_csv(filename, names = headers)"]},{"cell_type":"markdown","id":"8caceba3-c2c5-4deb-9f65-b759b9e58cf8","metadata":{},"outputs":[],"source":[" Use the method \u003cb\u003ehead()\u003c/b\u003e to display the first five rows of the dataframe. \n"]},{"cell_type":"code","id":"75bc8b3d-7c27-46b4-9d35-77219de6099c","metadata":{},"outputs":[],"source":["# To see what the data set looks like, we'll use the head() method.\ndf.head()"]},{"cell_type":"markdown","id":"8b2fc483-bf06-4b38-ae09-e52b2c0cda7e","metadata":{},"outputs":[],"source":["As you can see, several question marks appeared in the data frame; those missing values may hinder further analysis. \n","\u003cdiv\u003eSo, how do we identify all those missing values and deal with them?\u003c/div\u003e \n","\n","\n","\u003cb\u003eHow to work with missing data?\u003c/b\u003e\n","\n","Steps for working with missing data:\n","\u003col\u003e\n","    \u003cli\u003eIdentify missing data\u003c/li\u003e\n","    \u003cli\u003eDeal with missing data\u003c/li\u003e\n","    \u003cli\u003eCorrect data format\u003c/li\u003e\n","\u003c/ol\u003e\n"]},{"cell_type":"markdown","id":"e99e6598-f536-41e8-bb4d-0cc879e783b0","metadata":{},"outputs":[],"source":["# Identify and handle missing values\n","\n","\n","### Identify missing values\n","\u003ch4\u003eConvert \"?\" to NaN\u003c/h4\u003e\n","In the car data set, missing data comes with the question mark \"?\".\n","We replace \"?\" with NaN (Not a Number), Python's default missing value marker for reasons of computational speed and convenience. Use the function: \n"," \u003cpre\u003e.replace(A, B, inplace = True) \u003c/pre\u003e\n","to replace A by B.\n"]},{"cell_type":"code","id":"6deb6598-692e-44e1-9319-0d5b8caa6b4e","metadata":{},"outputs":[],"source":["import numpy as np\n\n# replace \"?\" to NaN\ndf.replace(\"?\", np.nan, inplace = True)\ndf.head(5)"]},{"cell_type":"markdown","id":"7a0a4560-01d8-4434-bc80-93959ddca7af","metadata":{},"outputs":[],"source":["\u003ch4\u003eEvaluating for Missing Data\u003c/h4\u003e\n","\n","The missing values are converted by default. Use the following functions to identify these missing values. You can use two methods to detect missing data:\n","\u003col\u003e\n","    \u003cli\u003e\u003cb\u003e.isnull()\u003c/b\u003e\u003c/li\u003e\n","    \u003cli\u003e\u003cb\u003e.notnull()\u003c/b\u003e\u003c/li\u003e\n","\u003c/ol\u003e\n","The output is a boolean value indicating whether the value that is passed into the argument is in fact missing data.\n"]},{"cell_type":"code","id":"fed9ba86-451c-4cb2-8e83-0ef8394e1313","metadata":{},"outputs":[],"source":["missing_data = df.isnull()\nmissing_data.head(5)"]},{"cell_type":"markdown","id":"b375c068-d76d-4dee-a43c-7107da0838c8","metadata":{},"outputs":[],"source":["\"True\" means the value is a missing value while \"False\" means the value is not a missing value.\n"]},{"cell_type":"markdown","id":"4889bc38-234b-4235-9445-066d1e8c1360","metadata":{},"outputs":[],"source":["\u003ch4\u003eCount missing values in each column\u003c/h4\u003e\n","\u003cp\u003e\n","Using a for loop in Python, you can quickly figure out the number of missing values in each column. As mentioned above, \"True\" represents a missing value and \"False\" means the value is present in the data set.  In the body of the for loop the method \".value_counts()\" counts the number of \"True\" values. \n","\u003c/p\u003e\n"]},{"cell_type":"code","id":"be54566f-16cf-4aaa-b02e-77ea3332ea9f","metadata":{},"outputs":[],"source":["for column in missing_data.columns.values.tolist():\n    print(column)\n    print (missing_data[column].value_counts())\n    print(\"\")    "]},{"cell_type":"markdown","id":"9e111c1e-8b61-42ae-94f9-347fc15623bd","metadata":{},"outputs":[],"source":["Based on the summary above, each column has 205 rows of data and seven of the columns containing missing data:\n","\u003col\u003e\n","    \u003cli\u003e\"normalized-losses\": 41 missing data\u003c/li\u003e\n","    \u003cli\u003e\"num-of-doors\": 2 missing data\u003c/li\u003e\n","    \u003cli\u003e\"bore\": 4 missing data\u003c/li\u003e\n","    \u003cli\u003e\"stroke\" : 4 missing data\u003c/li\u003e\n","    \u003cli\u003e\"horsepower\": 2 missing data\u003c/li\u003e\n","    \u003cli\u003e\"peak-rpm\": 2 missing data\u003c/li\u003e\n","    \u003cli\u003e\"price\": 4 missing data\u003c/li\u003e\n","\u003c/ol\u003e\n"]},{"cell_type":"markdown","id":"21b7ccaf-cd50-49cb-aac6-7597547a9ece","metadata":{},"outputs":[],"source":["### Deal with missing data\n","\u003cb\u003eHow should you deal with missing data?\u003c/b\u003e\n","\n","\u003col\u003e\n","    \u003cli\u003eDrop data\u003cbr\u003e\n","        a. Drop the whole row\u003cbr\u003e\n","        b. Drop the whole column\n","    \u003c/li\u003e\n","    \u003cli\u003eReplace data\u003cbr\u003e\n","        a. Replace it by mean\u003cbr\u003e\n","        b. Replace it by frequency\u003cbr\u003e\n","        c. Replace it based on other functions\n","    \u003c/li\u003e\n","\u003c/ol\u003e\n"]},{"cell_type":"markdown","id":"65c1368c-d2ad-43d7-aef6-b5b48648c755","metadata":{},"outputs":[],"source":["You should only drop whole columns if most entries in the column are empty. In the data set, none of the columns are empty enough to drop entirely.\n","You have some freedom in choosing which method to replace data; however, some methods may seem more reasonable than others. Apply each method to different columns:\n","\n","\u003cb\u003eReplace by mean:\u003c/b\u003e\n","\u003cul\u003e\n","    \u003cli\u003e\"normalized-losses\": 41 missing data, replace them with mean\u003c/li\u003e\n","    \u003cli\u003e\"stroke\": 4 missing data, replace them with mean\u003c/li\u003e\n","    \u003cli\u003e\"bore\": 4 missing data, replace them with mean\u003c/li\u003e\n","    \u003cli\u003e\"horsepower\": 2 missing data, replace them with mean\u003c/li\u003e\n","    \u003cli\u003e\"peak-rpm\": 2 missing data, replace them with mean\u003c/li\u003e\n","\u003c/ul\u003e\n","\n","\u003cb\u003eReplace by frequency:\u003c/b\u003e\n","\u003cul\u003e\n","    \u003cli\u003e\"num-of-doors\": 2 missing data, replace them with \"four\". \n","        \u003cul\u003e\n","            \u003cli\u003eReason: 84% sedans are four doors. Since four doors is most frequent, it is most likely to occur\u003c/li\u003e\n","        \u003c/ul\u003e\n","    \u003c/li\u003e\n","\u003c/ul\u003e\n","\n","\u003cb\u003eDrop the whole row:\u003c/b\u003e\n","\u003cul\u003e\n","    \u003cli\u003e\"price\": 4 missing data, simply delete the whole row\n","        \u003cul\u003e\n","            \u003cli\u003eReason: You want to predict price. You cannot use any data entry without price data for prediction; therefore any row now without price data is not useful to you.\u003c/li\u003e\n","        \u003c/ul\u003e\n","    \u003c/li\u003e\n","\u003c/ul\u003e\n"]},{"cell_type":"markdown","id":"1f7659dc-3092-4bff-9dcb-d72ab7024cb3","metadata":{},"outputs":[],"source":["\u003ch4\u003eCalculate the mean value for the \"normalized-losses\" column \u003c/h4\u003e\n"]},{"cell_type":"code","id":"51f786cc-cb79-47f6-aca2-2ed8b3eda5b9","metadata":{},"outputs":[],"source":["avg_norm_loss = df[\"normalized-losses\"].astype(\"float\").mean(axis=0)\nprint(\"Average of normalized-losses:\", avg_norm_loss)"]},{"cell_type":"markdown","id":"bde3dd20-06dd-4db8-8855-b632f8a4e332","metadata":{},"outputs":[],"source":["\u003ch4\u003eReplace \"NaN\" with mean value in \"normalized-losses\" column\u003c/h4\u003e\n"]},{"cell_type":"code","id":"466848d5-c449-4315-bcee-0044b88b27a6","metadata":{},"outputs":[],"source":["df[\"normalized-losses\"].replace(np.nan, avg_norm_loss, inplace=True)"]},{"cell_type":"markdown","id":"87aa2dd4-c713-40a1-830a-5bb820ed487e","metadata":{},"outputs":[],"source":["\u003ch4\u003eCalculate the mean value for the \"bore\" column\u003c/h4\u003e\n"]},{"cell_type":"code","id":"ab01f96a-fd4f-49b6-8bf1-f6c56d0f8511","metadata":{},"outputs":[],"source":["avg_bore=df['bore'].astype('float').mean(axis=0)\nprint(\"Average of bore:\", avg_bore)"]},{"cell_type":"markdown","id":"01aea048-e744-4605-9845-5e1cce7467c4","metadata":{},"outputs":[],"source":["\u003ch4\u003eReplace \"NaN\" with the mean value in the \"bore\" column\u003c/h4\u003e\n"]},{"cell_type":"code","id":"15e6c66a-1c01-4774-832d-8daf29f0002a","metadata":{},"outputs":[],"source":["df[\"bore\"].replace(np.nan, avg_bore, inplace=True)"]},{"cell_type":"markdown","id":"d451138f-25d2-4b8f-bb06-5797f2c55cae","metadata":{},"outputs":[],"source":["\u003cdiv class=\"alert alert-danger alertdanger\" style=\"margin-top: 20px\"\u003e\n","\u003ch1\u003e Question  #1: \u003c/h1\u003e\n","\n","\u003cb\u003eBased on the example above, replace NaN in \"stroke\" column with the mean value.\u003c/b\u003e\n","\u003c/div\u003e\n"]},{"cell_type":"code","id":"5511258c-36ec-422e-9003-ea91056758ad","metadata":{},"outputs":[],"source":["# Write your code below and press Shift+Enter to execute \n"]},{"cell_type":"markdown","id":"5054ea4d-374e-4306-8c82-1fe41cbe662e","metadata":{},"outputs":[],"source":["\u003cdetails\u003e\u003csummary\u003eClick here for the solution\u003c/summary\u003e\n","\n","```python\n","#Calculate the mean vaule for \"stroke\" column\n","avg_stroke = df[\"stroke\"].astype(\"float\").mean(axis = 0)\n","print(\"Average of stroke:\", avg_stroke)\n","\n","# replace NaN by mean value in \"stroke\" column\n","df[\"stroke\"].replace(np.nan, avg_stroke, inplace = True)\n","```\n","\n","\u003c/details\u003e\n","\n"]},{"cell_type":"markdown","id":"72018ed6-e53e-42d3-8f16-620cc39b3bae","metadata":{},"outputs":[],"source":["\u003ch4\u003eCalculate the mean value for the \"horsepower\" column\u003c/h4\u003e\n"]},{"cell_type":"code","id":"f4ca4b91-7c81-4ff0-8df0-ea52f40b8d8c","metadata":{},"outputs":[],"source":["avg_horsepower = df['horsepower'].astype('float').mean(axis=0)\nprint(\"Average horsepower:\", avg_horsepower)"]},{"cell_type":"markdown","id":"022ea990-7899-4836-9ae6-651804f499ed","metadata":{},"outputs":[],"source":["\u003ch4\u003eReplace \"NaN\" with the mean value in the \"horsepower\" column\u003c/h4\u003e\n"]},{"cell_type":"code","id":"00235a53-7513-4139-ad79-2cf52046b773","metadata":{},"outputs":[],"source":["df['horsepower'].replace(np.nan, avg_horsepower, inplace=True)"]},{"cell_type":"markdown","id":"ecaddbfc-7e38-4f3f-9cdf-dbb3d7020147","metadata":{},"outputs":[],"source":["\u003ch4\u003eCalculate the mean value for \"peak-rpm\" column\u003c/h4\u003e\n"]},{"cell_type":"code","id":"2cf4793e-a12e-4a50-9d87-ddf39c3fdd1f","metadata":{},"outputs":[],"source":["avg_peakrpm=df['peak-rpm'].astype('float').mean(axis=0)\nprint(\"Average peak rpm:\", avg_peakrpm)"]},{"cell_type":"markdown","id":"e0fa31d4-2a3c-4fac-ab7c-55b7ac7b31c9","metadata":{},"outputs":[],"source":["\u003ch4\u003eReplace \"NaN\" with the mean value in the \"peak-rpm\" column\u003c/h4\u003e\n"]},{"cell_type":"code","id":"d67a9d6b-eb5c-4d61-883a-a61ceabf637d","metadata":{},"outputs":[],"source":["df['peak-rpm'].replace(np.nan, avg_peakrpm, inplace=True)"]},{"cell_type":"markdown","id":"c882c072-683c-44b7-8a4f-c4ff62931e58","metadata":{},"outputs":[],"source":["To see which values are present in a particular column, we can use the \".value_counts()\" method:\n"]},{"cell_type":"code","id":"94bfade5-11af-4ba5-9fbf-3a55b9819b12","metadata":{},"outputs":[],"source":["df['num-of-doors'].value_counts()"]},{"cell_type":"markdown","id":"eb6eef33-c52d-4d8e-9804-f51fcc00acbb","metadata":{},"outputs":[],"source":["You can see that four doors is the most common type. We can also use the \".idxmax()\" method to calculate the most common type automatically:\n"]},{"cell_type":"code","id":"33bc1b1c-8bc8-41d4-a867-0cfab97db348","metadata":{},"outputs":[],"source":["df['num-of-doors'].value_counts().idxmax()"]},{"cell_type":"markdown","id":"c0e77a0b-db9c-4e74-98cb-0ff846c5bce3","metadata":{},"outputs":[],"source":["The replacement procedure is very similar to what you have seen previously:\n"]},{"cell_type":"code","id":"e5e7400a-47a4-48b2-bad6-41e6a827ae88","metadata":{},"outputs":[],"source":["#replace the missing 'num-of-doors' values by the most frequent \ndf[\"num-of-doors\"].replace(np.nan, \"four\", inplace=True)"]},{"cell_type":"markdown","id":"af1bcf2b-651b-4199-b280-dc1aa046707c","metadata":{},"outputs":[],"source":["Finally, drop all rows that do not have price data:\n"]},{"cell_type":"code","id":"0ea7adc4-3fb5-439b-bae7-fac062684ac4","metadata":{},"outputs":[],"source":["# simply drop whole row with NaN in \"price\" column\ndf.dropna(subset=[\"price\"], axis=0, inplace=True)\n\n# reset index, because we droped two rows\ndf.reset_index(drop=True, inplace=True)"]},{"cell_type":"code","id":"93c3dd01-33dc-45c6-905c-7ceaac66d867","metadata":{},"outputs":[],"source":["df.head()"]},{"cell_type":"markdown","id":"719e84b4-adbd-4a20-b292-37e0b8d5b543","metadata":{},"outputs":[],"source":["\u003cb\u003eGood!\u003c/b\u003e Now, you have a data set with no missing values.\n"]},{"cell_type":"markdown","id":"ba0df207-efd9-40cf-bfcb-b98b43205dcf","metadata":{},"outputs":[],"source":["### Correct data format\n","\u003cb\u003eWe are almost there!\u003c/b\u003e\n","\u003cp\u003eThe last step in data cleaning is checking and making sure that all data is in the correct format (int, float, text or other).\u003c/p\u003e\n","\n","In Pandas, you use:\n","\u003cp\u003e\u003cb\u003e.dtype()\u003c/b\u003e to check the data type\u003c/p\u003e\n","\u003cp\u003e\u003cb\u003e.astype()\u003c/b\u003e to change the data type\u003c/p\u003e\n"]},{"cell_type":"markdown","id":"b1a50df1-1750-410f-a40a-2cb7c6349acf","metadata":{},"outputs":[],"source":["\u003ch4\u003eLet's list the data types for each column\u003c/h4\u003e\n"]},{"cell_type":"code","id":"d4029a0d-c4b8-46ca-b548-06a8f388a153","metadata":{},"outputs":[],"source":["df.dtypes"]},{"cell_type":"markdown","id":"8770ca5d-6cd8-4a00-b30b-49560084dadf","metadata":{},"outputs":[],"source":["\u003cp\u003eAs you can see above, some columns are not of the correct data type. Numerical variables should have type 'float' or 'int', and variables with strings such as categories should have type 'object'. For example, the numerical values 'bore' and 'stroke' describe the engines, so you should expect them to be of the type 'float' or 'int'; however, they are shown as type 'object'. You have to convert data types into a proper format for each column using the \"astype()\" method.\u003c/p\u003e \n"]},{"cell_type":"markdown","id":"2af92b31-692b-425f-83ff-180014e49347","metadata":{},"outputs":[],"source":["\u003ch4\u003eConvert data types to proper format\u003c/h4\u003e\n"]},{"cell_type":"code","id":"dca03661-cfe9-49bc-b323-20dec9b74c77","metadata":{},"outputs":[],"source":["df[[\"bore\", \"stroke\"]] = df[[\"bore\", \"stroke\"]].astype(\"float\")\ndf[[\"normalized-losses\"]] = df[[\"normalized-losses\"]].astype(\"int\")\ndf[[\"price\"]] = df[[\"price\"]].astype(\"float\")\ndf[[\"peak-rpm\"]] = df[[\"peak-rpm\"]].astype(\"float\")"]},{"cell_type":"markdown","id":"9e794764-d783-4804-9d6e-edfbcb2fb224","metadata":{},"outputs":[],"source":["\u003ch4\u003eLet us list the columns after the conversion\u003c/h4\u003e\n"]},{"cell_type":"code","id":"fec0eddb-7da9-4587-ad75-2068942ef7f4","metadata":{},"outputs":[],"source":["df.dtypes"]},{"cell_type":"markdown","id":"2212b579-739f-46c4-8eff-219753052017","metadata":{},"outputs":[],"source":["\u003cb\u003eWonderful!\u003c/b\u003e\n","\n","Now you finally obtained the cleansed data set with no missing values and with all data in its proper format.\n"]},{"cell_type":"markdown","id":"7f4c0be0-d6b6-4da8-908c-cd16b518cc78","metadata":{},"outputs":[],"source":["## Data Standardization\n","\u003cp\u003e\n","You usually collect data from different agencies in different formats.\n","(Data standardization is also a term for a particular type of data normalization where you subtract the mean and divide by the standard deviation.)\n","\u003c/p\u003e\n","    \n","\u003cb\u003eWhat is standardization?\u003c/b\u003e\n","\u003cp\u003eStandardization is the process of transforming data into a common format, allowing the researcher to make the meaningful comparison.\n","\u003c/p\u003e\n","\n","\u003cb\u003eExample\u003c/b\u003e\n","\u003cp\u003eTransform mpg to L/100km:\u003c/p\u003e\n","\u003cp\u003eIn your data set, the fuel consumption columns \"city-mpg\" and \"highway-mpg\" are represented by mpg (miles per gallon) unit. Assume you are developing an application in a country that accepts the fuel consumption with L/100km standard.\u003c/p\u003e\n","\u003cp\u003eYou will need to apply \u003cb\u003edata transformation\u003c/b\u003e to transform mpg into L/100km.\u003c/p\u003e\n"]},{"cell_type":"markdown","id":"3247c9fd-2539-4a77-b0dd-0d8fabbbb13d","metadata":{},"outputs":[],"source":["\u003cp\u003eUse this formula for unit conversion:\u003cp\u003e\n","L/100km = 235 / mpg\n","\u003cp\u003eYou can do many mathematical operations directly using Pandas.\u003c/p\u003e\n"]},{"cell_type":"code","id":"026acb55-e143-4032-8b5c-a410ec2237d2","metadata":{},"outputs":[],"source":["df.head()"]},{"cell_type":"code","id":"3a74f2d7-ce42-4c74-814a-67a30d6f5cc8","metadata":{},"outputs":[],"source":["# Convert mpg to L/100km by mathematical operation (235 divided by mpg)\ndf['city-L/100km'] = 235/df[\"city-mpg\"]\n\n# check your transformed data \ndf.head()"]},{"cell_type":"markdown","id":"b43dbd92-6215-41e4-b57e-be44855f7fc9","metadata":{},"outputs":[],"source":["\u003cdiv class=\"alert alert-danger alertdanger\" style=\"margin-top: 20px\"\u003e\n","\u003ch1\u003e Question  #2: \u003c/h1\u003e\n","\n","\u003cb\u003eAccording to the example above, transform mpg to L/100km in the column of \"highway-mpg\" and change the name of column to \"highway-L/100km\".\u003c/b\u003e\n","\u003c/div\u003e\n"]},{"cell_type":"code","id":"be8708c9-2be8-4433-8948-1c380338e43b","metadata":{},"outputs":[],"source":["# Write your code below and press Shift+Enter to execute \n"]},{"cell_type":"markdown","id":"126fd5a4-7ca0-40a0-9355-dddc474fe136","metadata":{},"outputs":[],"source":["\u003cdetails\u003e\u003csummary\u003eClick here for the solution\u003c/summary\u003e\n","\n","```python\n","# transform mpg to L/100km by mathematical operation (235 divided by mpg)\n","df[\"highway-mpg\"] = 235/df[\"highway-mpg\"]\n","\n","# rename column name from \"highway-mpg\" to \"highway-L/100km\"\n","df.rename(columns={'\"highway-mpg\"':'highway-L/100km'}, inplace=True)\n","\n","# check your transformed data \n","df.head()\n","\n","```\n","\u003c/details\u003e\n","\n","\n"]},{"cell_type":"markdown","id":"b7ea9faf-a9c8-463e-905a-9c3596786bbf","metadata":{},"outputs":[],"source":["## Data Normalization\n","\n","\u003cb\u003eWhy normalization?\u003c/b\u003e\n","\u003cp\u003eNormalization is the process of transforming values of several variables into a similar range. Typical normalizations include \n","\u003col\u003e\n","    \u003cli\u003escaling the variable so the variable average is 0\u003c/li\u003e\n","    \u003cli\u003escaling the variable so the variance is 1\u003c/li\u003e \n","    \u003cli\u003escaling the variable so the variable values range from 0 to 1\u003c/li\u003e\n","\u003c/ol\u003e\n","\u003c/p\u003e\n","\n","\u003cb\u003eExample\u003c/b\u003e\n","\u003cp\u003eTo demonstrate normalization, say you want to scale the columns \"length\", \"width\" and \"height\".\u003c/p\u003e\n","\u003cp\u003e\u003cb\u003eTarget:\u003c/b\u003e normalize those variables so their value ranges from 0 to 1\u003c/p\u003e\n","\u003cp\u003e\u003cb\u003eApproach:\u003c/b\u003e replace the original value by (original value)/(maximum value)\u003c/p\u003e\n"]},{"cell_type":"code","id":"9c932ed9-49b1-472b-83d6-9d41ab1b7f30","metadata":{},"outputs":[],"source":["# replace (original value) by (original value)/(maximum value)\ndf['length'] = df['length']/df['length'].max()\ndf['width'] = df['width']/df['width'].max()"]},{"cell_type":"markdown","id":"22a35e81-e7e2-4552-8a21-53aebe7320ef","metadata":{},"outputs":[],"source":["\u003cdiv class=\"alert alert-danger alertdanger\" style=\"margin-top: 20px\"\u003e\n","\u003ch1\u003e Question #3: \u003c/h1\u003e\n","\n","\u003cb\u003eAccording to the example above, normalize the column \"height\".\u003c/b\u003e\n","\u003c/div\u003e\n"]},{"cell_type":"code","id":"1238d09b-9b3e-44d2-abab-c27011287371","metadata":{},"outputs":[],"source":["# Write your code below and press Shift+Enter to execute \n"]},{"cell_type":"markdown","id":"0e6110d2-986e-4a35-9ebb-faf2fdf60fc0","metadata":{},"outputs":[],"source":["\u003cdetails\u003e\u003csummary\u003eClick here for the solution\u003c/summary\u003e\n","\n","```python\n","df['height'] = df['height']/df['height'].max() \n","\n","# show the scaled columns\n","df[[\"length\",\"width\",\"height\"]].head()\n","\n","\n","```\n","\u003c/details\u003e\n","\n","\n"]},{"cell_type":"markdown","id":"6bf79756-fb36-4a17-8db0-7bbbc71540c3","metadata":{},"outputs":[],"source":["Here you've normalized \"length\", \"width\" and \"height\" to fall in the range of [0,1].\n"]},{"cell_type":"markdown","id":"7983e3d0-f661-49aa-9e6c-98488590b0aa","metadata":{},"outputs":[],"source":["## Binning\n","\u003cb\u003eWhy binning?\u003c/b\u003e\n","\u003cp\u003e\n","    Binning is a process of transforming continuous numerical variables into discrete categorical 'bins' for grouped analysis.\n","\u003c/p\u003e\n","\n","\u003cb\u003eExample: \u003c/b\u003e\n","\u003cp\u003eIn your data set, \"horsepower\" is a real valued variable ranging from 48 to 288 and it has 59 unique values. What if you only care about the price difference between cars with high horsepower, medium horsepower, and little horsepower (3 types)? You can rearrange them into three ‘bins' to simplify analysis.\u003c/p\u003e\n","\n","\u003cp\u003eUse the Pandas method 'cut' to segment the 'horsepower' column into 3 bins.\u003c/p\u003e\n"]},{"cell_type":"markdown","id":"88ba39f5-7ecc-4c91-b27f-dd108e578516","metadata":{},"outputs":[],"source":["\u003ch3\u003eExample of Binning Data In Pandas\u003c/h3\u003e\n"]},{"cell_type":"markdown","id":"1c2e13fb-b353-4ac0-8576-83dd5756ea34","metadata":{},"outputs":[],"source":[" Convert data to correct format:\n"]},{"cell_type":"code","id":"7c317c2e-62ee-483e-a6e9-c48b1b972830","metadata":{},"outputs":[],"source":["df[\"horsepower\"]=df[\"horsepower\"].astype(int, copy=True)"]},{"cell_type":"markdown","id":"be5c6719-834a-4b6b-8c3b-615a712fa62c","metadata":{},"outputs":[],"source":["Plot the histogram of horsepower to see the distribution of horsepower.\n"]},{"cell_type":"code","id":"502468d7-a27f-4949-9454-9c8279e1d0cb","metadata":{},"outputs":[],"source":["%matplotlib inline\nimport matplotlib as plt\nfrom matplotlib import pyplot\nplt.pyplot.hist(df[\"horsepower\"])\n\n# set x/y labels and plot title\nplt.pyplot.xlabel(\"horsepower\")\nplt.pyplot.ylabel(\"count\")\nplt.pyplot.title(\"horsepower bins\")"]},{"cell_type":"markdown","id":"e2575b82-e96b-4bbf-8489-f5a72790c8ca","metadata":{},"outputs":[],"source":["\u003cp\u003eFind 3 bins of equal size bandwidth by using Numpy's \u003ccode\u003elinspace(start_value, end_value, numbers_generated\u003c/code\u003e function.\u003c/p\u003e\n","\u003cp\u003eSince you want to include the minimum value of horsepower, set start_value = min(df[\"horsepower\"]).\u003c/p\u003e\n","\u003cp\u003eSince you want to include the maximum value of horsepower, set end_value = max(df[\"horsepower\"]).\u003c/p\u003e\n","\u003cp\u003eSince you are building 3 bins of equal length, you need 4 dividers, so numbers_generated = 4.\u003c/p\u003e\n"]},{"cell_type":"markdown","id":"f508aee1-c51d-4a15-bff8-7193ebc928e3","metadata":{},"outputs":[],"source":["Build a bin array with a minimum value to a maximum value by using the bandwidth calculated above. The values will determine when one bin ends and another begins.\n"]},{"cell_type":"code","id":"f65baa1e-b4ac-46e6-9e38-99b40c432623","metadata":{},"outputs":[],"source":["bins = np.linspace(min(df[\"horsepower\"]), max(df[\"horsepower\"]), 4)\nbins"]},{"cell_type":"markdown","id":"e10ad72f-eca4-43fc-b7d6-0fe4922eb656","metadata":{},"outputs":[],"source":["Set group  names:\n"]},{"cell_type":"code","id":"e02c4d35-dc1a-489a-9d1e-73b0f2bf7866","metadata":{},"outputs":[],"source":["group_names = ['Low', 'Medium', 'High']"]},{"cell_type":"markdown","id":"18a3147c-76dd-408b-82b2-5880bfe5e8aa","metadata":{},"outputs":[],"source":["Apply the function \"cut\" to determine what each value of `df['horsepower']` belongs to. \n"]},{"cell_type":"code","id":"b09f9570-9237-46a2-b224-e4fc5749f262","metadata":{},"outputs":[],"source":["df['horsepower-binned'] = pd.cut(df['horsepower'], bins, labels=group_names, include_lowest=True )\ndf[['horsepower','horsepower-binned']].head(20)"]},{"cell_type":"markdown","id":"9df25bb0-8d6c-481f-b9f1-2a4dd7d37cad","metadata":{},"outputs":[],"source":["See the number of vehicles in each bin:\n"]},{"cell_type":"code","id":"53c2d5e8-ef0a-4a41-b489-65366eee0741","metadata":{},"outputs":[],"source":["df[\"horsepower-binned\"].value_counts()"]},{"cell_type":"markdown","id":"5282bc9c-2455-42c4-873e-d31934345b5f","metadata":{},"outputs":[],"source":["Plot the distribution of each bin:\n"]},{"cell_type":"code","id":"de38b23a-fcac-4ae7-9d20-43679347924f","metadata":{},"outputs":[],"source":["%matplotlib inline\nimport matplotlib as plt\nfrom matplotlib import pyplot\npyplot.bar(group_names, df[\"horsepower-binned\"].value_counts())\n\n# set x/y labels and plot title\nplt.pyplot.xlabel(\"horsepower\")\nplt.pyplot.ylabel(\"count\")\nplt.pyplot.title(\"horsepower bins\")"]},{"cell_type":"markdown","id":"d1cda560-38d1-4f8f-88b3-96ca6580dbe0","metadata":{},"outputs":[],"source":["\u003cp\u003e\n","    Look at the data frame above carefully. You will find that the last column provides the bins for \"horsepower\" based on 3 categories (\"Low\", \"Medium\" and \"High\"). \n","\u003c/p\u003e\n","\u003cp\u003e\n","    You successfully narrowed down the intervals from 59 to 3!\n","\u003c/p\u003e\n"]},{"cell_type":"markdown","id":"ba34755c-a095-4eb8-8283-d8d189040637","metadata":{},"outputs":[],"source":["\u003ch3\u003eBins Visualization\u003c/h3\u003e\n","Normally, you use a histogram to visualize the distribution of bins we created above. \n"]},{"cell_type":"code","id":"badbf81d-fb4b-4dcd-8cf5-388f6d95a963","metadata":{},"outputs":[],"source":["%matplotlib inline\nimport matplotlib as plt\nfrom matplotlib import pyplot\n\n\n# draw historgram of attribute \"horsepower\" with bins = 3\nplt.pyplot.hist(df[\"horsepower\"], bins = 3)\n\n# set x/y labels and plot title\nplt.pyplot.xlabel(\"horsepower\")\nplt.pyplot.ylabel(\"count\")\nplt.pyplot.title(\"horsepower bins\")"]},{"cell_type":"markdown","id":"d6fbc4a6-052e-442f-8292-564691503def","metadata":{},"outputs":[],"source":["The plot above shows the binning result for the attribute \"horsepower\". \n"]},{"cell_type":"markdown","id":"0e2932f0-e4fc-4aa3-adf3-1ec4bff6b669","metadata":{},"outputs":[],"source":["## Indicator Variable\n","\u003cb\u003eWhat is an indicator variable?\u003c/b\u003e\n","\u003cp\u003e\n","    An indicator variable (or dummy variable) is a numerical variable used to label categories. They are called 'dummies' because the numbers themselves don't have inherent meaning. \n","\u003c/p\u003e\n","\n","\u003cb\u003eWhy use indicator variables?\u003c/b\u003e\n","\u003cp\u003e\n","    You use indicator variables so you can use categorical variables for regression analysis in the later modules.\n","\u003c/p\u003e\n","\u003cb\u003eExample\u003c/b\u003e\n","\u003cp\u003e\n","    The column \"fuel-type\" has two unique values: \"gas\" or \"diesel\". Regression doesn't understand words, only numbers. To use this attribute in regression analysis, you can convert \"fuel-type\" to indicator variables.\n","\u003c/p\u003e\n","\n","\u003cp\u003e\n","    Use the Panda method 'get_dummies' to assign numerical values to different categories of fuel type. \n","\u003c/p\u003e\n"]},{"cell_type":"code","id":"d5535051-9911-40ec-85a9-63079ba8f661","metadata":{},"outputs":[],"source":["df.columns"]},{"cell_type":"markdown","id":"8cb655b8-1136-4921-8b10-0223cf7690e6","metadata":{},"outputs":[],"source":["Get the indicator variables and assign it to data frame \"dummy_variable_1\":\n"]},{"cell_type":"code","id":"9c407597-9333-4936-b3ce-1bcfe2316895","metadata":{},"outputs":[],"source":["dummy_variable_1 = pd.get_dummies(df[\"fuel-type\"])\ndummy_variable_1.head()"]},{"cell_type":"markdown","id":"d36cf5ba-95c5-4531-a8b5-428bd4977927","metadata":{},"outputs":[],"source":["Change the column names for clarity:\n"]},{"cell_type":"code","id":"10786bf3-c56f-441a-8a28-7421cc2d379b","metadata":{},"outputs":[],"source":["dummy_variable_1.rename(columns={'gas':'fuel-type-gas', 'diesel':'fuel-type-diesel'}, inplace=True)\ndummy_variable_1.head()"]},{"cell_type":"markdown","id":"a16facc0-edab-4fe2-b732-92060b990949","metadata":{},"outputs":[],"source":["In the data frame, column 'fuel-type' now has values for 'gas' and 'diesel' as 0s and 1s.\n"]},{"cell_type":"code","id":"69aac43b-aaf0-408a-921c-b1a934bfa4fa","metadata":{},"outputs":[],"source":["# merge data frame \"df\" and \"dummy_variable_1\" \ndf = pd.concat([df, dummy_variable_1], axis=1)\n\n# drop original column \"fuel-type\" from \"df\"\ndf.drop(\"fuel-type\", axis = 1, inplace=True)"]},{"cell_type":"code","id":"2c8db51d-ccc7-4b94-b997-3cad5b223e97","metadata":{},"outputs":[],"source":["df.head()"]},{"cell_type":"markdown","id":"41b8c88f-ca9e-4095-8e04-19116768b19c","metadata":{},"outputs":[],"source":["The last two columns are now the indicator variable representation of the fuel-type variable. They're all 0s and 1s now.\n"]},{"cell_type":"markdown","id":"8b41eac1-0154-45b9-ab73-214aab9285a8","metadata":{},"outputs":[],"source":["\u003cdiv class=\"alert alert-danger alertdanger\" style=\"margin-top: 20px\"\u003e\n","\u003ch1\u003e Question  #4: \u003c/h1\u003e\n","\n","\u003cb\u003eSimilar to before, create an indicator variable for the column \"aspiration\"\u003c/b\u003e\n","\u003c/div\u003e\n"]},{"cell_type":"code","id":"5bec0f7d-7c09-43cb-bedf-ab7ad8688d40","metadata":{},"outputs":[],"source":["# Write your code below and press Shift+Enter to execute \n"]},{"cell_type":"markdown","id":"8d0b113c-ed62-4ab1-9684-e7af55e523fa","metadata":{},"outputs":[],"source":["\u003cdetails\u003e\u003csummary\u003eClick here for the solution\u003c/summary\u003e\n","\n","```python\n","# get indicator variables of aspiration and assign it to data frame \"dummy_variable_2\"\n","dummy_variable_2 = pd.get_dummies(df['aspiration'])\n","\n","# change column names for clarity\n","dummy_variable_2.rename(columns={'std':'aspiration-std', 'turbo': 'aspiration-turbo'}, inplace=True)\n","\n","# show first 5 instances of data frame \"dummy_variable_1\"\n","dummy_variable_2.head()\n","\n","\n","```\n","\u003c/details\u003e\n","\n","\n"]},{"cell_type":"markdown","id":"23cdee60-cbd8-4bd4-b9e7-0fa3e3730d0b","metadata":{},"outputs":[],"source":[" \u003cdiv class=\"alert alert-danger alertdanger\" style=\"margin-top: 20px\"\u003e\n","\u003ch1\u003e Question  #5: \u003c/h1\u003e\n","\n","\u003cb\u003eMerge the new dataframe to the original dataframe, then drop the column 'aspiration'.\u003c/b\u003e\n","\u003c/div\u003e\n"]},{"cell_type":"code","id":"8e168145-b5ac-4068-9391-a9770ad4dc33","metadata":{},"outputs":[],"source":["# Write your code below and press Shift+Enter to execute \n"]},{"cell_type":"markdown","id":"e7991fa9-5d90-45b8-ad27-d3d060d832dc","metadata":{},"outputs":[],"source":["\u003cdetails\u003e\u003csummary\u003eClick here for the solution\u003c/summary\u003e\n","\n","```python\n","# merge the new dataframe to the original datafram\n","df = pd.concat([df, dummy_variable_2], axis=1)\n","\n","# drop original column \"aspiration\" from \"df\"\n","df.drop('aspiration', axis = 1, inplace=True)\n","\n","\n","```\n","\u003c/details\u003e\n","\n","\n"]},{"cell_type":"markdown","id":"c0848af0-e8c1-4bef-acaa-d6f17f08f478","metadata":{},"outputs":[],"source":["Save the new csv:\n"]},{"cell_type":"code","id":"58f53003-8023-4e33-9d72-f7e0852baad1","metadata":{},"outputs":[],"source":["df.to_csv('clean_df.csv')"]},{"cell_type":"markdown","id":"aab37b27-32d6-4908-a950-6cda363a6ee2","metadata":{},"outputs":[],"source":["### Thank you for completing this lab!\n","\n","\n","## Author\n","\n","\u003ca href=\"https://www.linkedin.com/in/joseph-s-50398b136/\" target=\"_blank\"\u003eJoseph Santarcangelo\u003c/a\u003e\n","\n","\n","### Other Contributors\n","\n","\u003ca href=\"https://www.linkedin.com/in/mahdi-noorian-58219234/\" target=\"_blank\"\u003eMahdi Noorian PhD\u003c/a\u003e\n","\n","Bahare Talayian\n","\n","Eric Xiao\n","\n","Steven Dong\n","\n","Parizad\n","\n","Hima Vasudevan\n","\n","\u003ca href=\"https://www.linkedin.com/in/fiorellawever/\" target=\"_blank\"\u003eFiorella Wenver\u003c/a\u003e\n","\n","\u003ca href=\" https://www.linkedin.com/in/yi-leng-yao-84451275/ \" target=\"_blank\" \u003eYi Yao\u003c/a\u003e.\n","\n","\u003ca href=\" https://www.coursera.org/instructor/~129186572/\" target=\"_blank\" \u003eAbhishek Gagneja \u003c/a\u003e\n","## Change Log\n","\n","\n","|  Date (YYYY-MM-DD) |  Version | Changed By  |  Change Description |\n","|---|---|---|---|\n","| 2023-09-28 | 2.3| Abhishek Gagneja| Instructional Update |\n","| 2020-10-30  | 2.2  | Lakshmi  |  Changed URL of csv  |\n","| 2020-09-09  | 2.1  | Lakshmi  |  Updated Indicator Variables section  |\n","| 2020-08-27  | 2.0  | Lavanya  |  Moved lab to course repo in GitLab  |\n","\n","\n","\n","\n","\u003chr\u003e\n","\n","## \u003ch3 align=\"center\"\u003e © IBM Corporation 2023. All rights reserved. \u003ch3/\u003e\n"]},{"cell_type":"code","id":"94e8fd8a-8a52-4394-96c9-4aa86fbd4701","metadata":{},"outputs":[],"source":[""]}],"metadata":{"language_info":{"codemirror_mode":{"name":"python","version":3},"file_extension":".py","mimetype":"text/x-python","name":"python","nbconvert_exporter":"python","pygments_lexer":"ipython3","version":"3.8"},"kernelspec":{"name":"python","display_name":"Pyolite","language":"python"}},"nbformat":4,"nbformat_minor":4}