<jupyter_start><jupyter_text>Data Wrangling  Missing valuesMissing values occur when no data value is stored for a feature in an observation. Dealing with missing data: - Check with data collection source - Drop the missing value - Replace the missing value (with an average or with the most frequent value) - Leave it as missing  How to drop value in Pythondf.dropna() methodUsage:df.dropna(subset=["column name"], axis=0, inplace=True)  Using replacedf["column name"].replace(missing_value, new_value)  Data FormattingData are usually collected from different sources. Data formatting ensures the data is consistent and easily understandable.For example, standardize measuring units and abbreviatures.  Data NormalizationUniform features values with different ranges.|   Age  |    Income    ||--------|--------------||   25   |     70000    ||   35   |     90000    ||   45   |    120000    |In this example, features are harder to compare and 'Income' may influence the result more.***|   Age  |    Income    ||--------|--------------||   2.5   |     7    ||   3.5   |     9    ||   4.5   |    12    |A normalized dataset have similar ranges.***  Normalizarion methods - Simple feature scaling -> $ x_{new} = x_{old} / x_{max} $ - Min-Max -> $ x_{new} = (x_{old} - x_{min}) / (x_{max} - x_{min})  $ - Z-score -> $ x_{new} = (x_{old} - \mu) / \sigma  $   BinningBinning is used to group features in similar ranges.For example,| value | category ||-------|----------||0 to 5000 | low || 5000 to 15000 | medium || 15000 to 30000 | high |  Turning Categorical to Numeric FeaturesWhenever a feature is presented as categories, it should be separated in different columns and set a value to represent its category.| Car | Fuel | gas | diesel ||-----|------|-----|--------|| A | gas | 1 | 0 || B | diesel | 0 | 1 || C | gas | 1 | 0 || D | gas | 1 | 0 | This technique is called one-hot encoding.Usage:pandas.get_dummies(df['Fuel']) ***  Practice - Dataset location: https://archive.ics.uci.edu/ml/machine-learning-databases/autos/imports-85.data - File format: csv<jupyter_code>import pandas as pd
import matplotlib.pylab as plt
filename = "https://archive.ics.uci.edu/ml/machine-learning-databases/autos/imports-85.data"
# dataset was provided without headers
headers = ["symboling","normalized-losses","make","fuel-type","aspiration", "num-of-doors","body-style",
         "drive-wheels","engine-location","wheel-base", "length","width","height","curb-weight","engine-type",
         "num-of-cylinders", "engine-size","fuel-system","bore","stroke","compression-ratio","horsepower",
         "peak-rpm","city-mpg","highway-mpg","price"]

df = pd.read_csv(filename, names = headers)
df.head()<jupyter_output><empty_output><jupyter_text>Convert "?" to NaN<jupyter_code>import numpy as np
df.replace("?", np.nan, inplace=True)
df.head()<jupyter_output><empty_output><jupyter_text>Evaluating for missing data<jupyter_code>missing_data = df.isnull()
missing_data.head()<jupyter_output><empty_output><jupyter_text>Count missing data in each column<jupyter_code>for column in missing_data.columns.values.tolist():
    # Check if there is any value set to 'True' in column
    if (True in missing_data[column].unique()):
        print(column)
        # Count how many 'True' values
        count = missing_data[column].value_counts()
        # value_counts() returns a list [False, True]
        print(count[1], "missing data")
        print("")<jupyter_output>normalized-losses
41 missing data

num-of-doors
2 missing data

bore
4 missing data

stroke
4 missing data

horsepower
2 missing data

peak-rpm
2 missing data

price
4 missing data<jupyter_text>Deal with missing data Replace by mean: - normalized-losses - bore - stroke - horsepower - peak-rpm Replace by frequency: - num-of-doors     - Reason: majority of cars in dataset have four doors      Drop the whole row: - price     - Reason: price is what we want to predict. Any row without price is not useful.<jupyter_code>mean_replaced = ["normalized-losses", "bore", "stroke", "horsepower", "peak-rpm"]

def replace_by_mean(replace_list):
    for column in replace_list:
        # convert to float and calculate average
        avg_value = df[column].astype("float").mean(axis=0)
        print("Average of", column, ":", avg_value)
        # replace missing values with average
        df[column].replace(np.nan, avg_value, inplace=True)

replace_by_mean(mean_replaced)
# replace the missing 'num-of-doors' by the most frequent
# find the most frequent
df['num-of-doors'].describe(include=['object'])
# replace the missing 'num-of-doors' by the most frequent
df["num-of-doors"].replace(np.nan, "four", inplace=True)
df.head()
# drop 'price' rows with missing data
df.dropna(subset=["price"], axis=0, inplace=True)

# reset index because rows were dropped
df.reset_index(drop=True, inplace=True)
# check if there is any missing data remaining
missing_data = df.isnull()

for column in missing_data.columns.values.tolist():
    # Check if there is any value set to 'True' in column
    if (True in missing_data[column].unique()):
        print(column)
        # Count how many 'True' values
        count = missing_data[column].value_counts()
        # value_counts() returns a list [False, True]
        print(count[1], "missing data")
        print("")
df.head()<jupyter_output><empty_output><jupyter_text>Correct data format<jupyter_code>df.dtypes<jupyter_output><empty_output><jupyter_text>Convert data types to proper format<jupyter_code>df[["bore", "stroke", "price", "peak-rpm"]] = df[["bore", "stroke", "price", "peak-rpm"]].astype("float")
df[["normalized-losses"]] = df[["normalized-losses"]].astype("int")
df.dtypes<jupyter_output><empty_output><jupyter_text>Data StandardizationStandardization is the process of transforming data into a common format which allows the researcher to make meaninful comparisons.For example, transform mpg to L/100km.The formula for unit conversion is L/100km = 235 / mpg<jupyter_code># convert city-mpg and highway-mpg to L/100km
df["city-L/100km"] = 235 / df["city-mpg"]
df["highway-L/100km"] = 235 / df["highway-mpg"]

df.head()<jupyter_output><empty_output><jupyter_text>Data NormalizationWe're going to normalize the columns 'length', 'width' and 'height' so their value ranges from 0 to 1 applying simple feature scaling.<jupyter_code>df['length'] = df['length'] / df['length'].max()
df['width'] = df['width'] / df['width'].max()
df['height'] = df['height'] / df['height'].max()<jupyter_output><empty_output><jupyter_text>BinningBinning is a process of transforming continuous numerical variables into discrete categorical 'bins', for grouped analysis.We're going to bin the column 'horsepower' in three categories.<jupyter_code># convert data to correct format
df['horsepower'] = df['horsepower'].astype(int, copy=True)
# plot horsepower histogram to see what its distibution looks like
%matplotlib inline
import matplotlib as plt
from matplotlib import pyplot
plt.pyplot.hist(df['horsepower'])

# set x and y labels and plot title
plt.pyplot.xlabel("horsepower")
plt.pyplot.ylabel("count")
plt.pyplot.title("horsepower distribution")
# create 3 bins from min to max horsepower
bins = np.linspace(min(df['horsepower']), max(df['horsepower']), 4)
bins
group_names = ['Low', 'Medium', 'High']

df['horsepower-binned'] = pd.cut(df['horsepower'], bins, labels=group_names, include_lowest=True)

df[['horsepower', 'horsepower-binned']].head(20)
df['horsepower-binned'].value_counts()
# plot the distribution of each bin
plt.pyplot.bar(group_names, df['horsepower-binned'].value_counts())

# set x and y labels and plot title
plt.pyplot.xlabel("horsepower")
plt.pyplot.ylabel("count")
plt.pyplot.title("horsepower distribution")
plt.pyplot.hist(df['horsepower'], bins = 3)

# set x and y labels and plot title
plt.pyplot.xlabel("horsepower")
plt.pyplot.ylabel("count")
plt.pyplot.title("horsepower distribution")<jupyter_output><empty_output><jupyter_text>Indicator VariableAn 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.<jupyter_code>df.columns
dummy = pd.get_dummies(df['fuel-type'])
dummy.head()
dummy.rename(columns={'diesel':'fuel-type-diesel', 'gas':'fuel-type-gas'}, inplace=True)
dummy.head()
# merge dummy to original dataframe
df = pd.concat([df, dummy], axis = 1)

# drop original column 'fuel-type'
df.drop("fuel-type", axis = 1, inplace=True)
df.head()
dummy = pd.get_dummies(df['aspiration'], prefix='aspiration', prefix_sep='-')
dummy.head()
df = pd.concat([df, dummy], axis = 1)
df.drop("aspiration", axis = 1, inplace=True)
df.head()
df.to_csv('clean_df.csv')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14267705707082282, 'fraction_numerical': 0.013293605317442126, 'mean_word_length': 4.5906470211402945, 'pattern_counts': {'":': 1, '<': 44, '<?xml version=': 0, '>': 47, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '294481', 'license': 'MIT', 'n_tokens_mistral': 2885, 'n_tokens_neox': 2712, 'n_words': 792, 'path': 'data-analysis/data-wrangling.ipynb', 'repo_name': 'PortoRAF/ibm-data-science', 'size': '8726'}