Exploring Dictionaries, Classifying Variables, and Imputing Data in the Ames Dataset

The real estate market is a complex ecosystem driven by numerous variables such as location, property features, market trends, and economic indicators. One dataset that offers a deep dive into this complexity is the Ames Housing dataset. Originating from Ames, Iowa, this dataset comprises various properties and their characteristics, ranging from the type of alley access to the property’s overall condition.

In this post, your aim is to take a closer look at this dataset using data science techniques. Specifically, you’ll focus on how to identify categorical and numerical variables, as understanding these variables is crucial for any data-driven decision-making process.

Let’s get started.

Overview

This post is divided into three parts; they are:

• The Importance of a Data Dictionary
• Identifying Categorical and Numerical Variables
• Missing Data Imputation

The Importance of a Data Dictionary

Before we delve into the code and dataset, let’s discuss the value of a data dictionary. A data dictionary is essentially a “map” that describes the nature of data we’re dealing with. It explains each variable, indicating whether it is categorical or numerical and, in the case of categorical, what the variable codes mean. This is particularly helpful when working with comprehensive datasets like the Ames Housing dataset.

Understanding the levels of measurement is fundamental in this context. Levels of measurement, namely nominal, ordinal, interval, and ratio, provide a framework for interpreting variables. Nominal variables represent categories without any inherent order (such as user ID), ordinal variables have a meaningful order but unequal intervals (such as ranking), interval variables have consistent intervals but no true zero point to make division meaningful (such as timestamp), and ratio variables have a meaningful order, consistent intervals, and a true zero point (such as length and weight). This distinction is crucial as it guides the appropriate statistical analyses and informs the interpretation of the dataset. In our case, the Ames Data Dictionary provides invaluable insights into the variables we will be analyzing, aiding in the accurate classification of variables and subsequent data analysis.

For example, the variable MSSubClass is explained as “Identifies the type of dwelling involved in the sale,” with codes like “20” for “1-STORY 1946 & NEWER ALL STYLES” and “30” for “1-STORY 1945 & OLDER.” Despite its numerical appearance, MSSubClass is a categorical variable. Without additional context or domain knowledge, one might categorize the numerical codes as labels without inherent order, making them “nominal,” and the average of 20 and 30 doesn’t make any sense.

However, it’s essential to note that the accurate classification of MSSubClass hinges on a nuanced understanding of the housing domain. With domain knowledge, one could discern the meaningful order among the categories, potentially reclassifying MSSubClass as an ordinal variable, in which you can conclude that 20 is better than 30. This emphasizes the critical role of domain expertise in refining the interpretation of variables, ensuring a more accurate representation of their nature and relationships.

Identifying Categorical and Numerical Variables

Identifying the nature of variables in a dataset is a crucial step in any data analysis task. While it’s tempting to consider any variable with numerical values as a numerical variable, that’s not always the case. As we saw with MSSubClass in the Ames data dictionary, some numerical variables are actually categorical in nature—they represent codes, not measurable quantities.

Few Basic Methods To Classify Data Types

• select_dtypes(): Find the columns in a DataFrame of certain data types

1 2 3 4 5 6 7

# Load the Ames dataset import pandas as pd Ames = pd.read_csv('Ames.csv')   # Using select_dtypes() numerical_features = Ames.select_dtypes(include=['int64', 'float64']).columns.tolist() categorical_features = Ames.select_dtypes(include=['object', 'category']).columns.tolist()

• describe(): Gather basic descriptive statistics for numerical columns (and ignore non-numerical columns). Hence you can shortlist all the numerical columns from its output

1 2	# Using describe() to automatically extract numerical features numerical_features = Ames.describe().columns.tolist()

• nunique(): Count the number of unique values in each column, in which categorical columns should only have a few unique values

1 2	# Consideration of data dictionary and domain knowledge could be useful in setting the threshold categorical_features = Ames.columns[Ames.nunique() <= threshold].tolist()

• value_counts(): Count the number of occurrences of each unique value

1 2 3

# Using value_counts() on each column or feature for column in Ames.columns:     print(Ames[column].value_counts())

The value_counts() method can be used to explore the unique values and their counts for each feature. If a feature has a limited set of unique values, it might be categorical.

• info(): This prints a concise summary of the DataFrame, including the the count of non-null values and the data type of each column

1 2	# Using info() on the Ames Dataset Ames.info()

In summary, this code combines all the above snippets to investigate a DataFrame:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

# Load the Ames dataset import pandas as pd Ames = pd.read_csv('Ames.csv')   # Using select_dtypes() numerical_features = Ames.select_dtypes(include=['int64', 'float64']).columns.tolist() categorical_features = Ames.select_dtypes(include=['object', 'category']).columns.tolist() print("Numerical features (int64 and float64):", numerical_features) print("Categorical features (object and category):", categorical_features)   # Using describe() to automatically extract numerical features numerical_features = Ames.describe().columns.tolist() print("Numerical features from describe():", numerical_features)   # Consideration of data dictionary and domain knowledge could be useful in setting the threshold threshold = 10 categorical_features = Ames.columns[Ames.nunique() <= threshold].tolist() print("Categorical features based on unique values:", categorical_features)   # Using value_counts() on each column or feature print("Value counts:") for column in Ames.columns:     print(Ames[column].value_counts())   # Using info() on the Ames Dataset print("info():") Ames.info()

Running the above code will print you the following:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60

Numerical features (int64 and float64): ['PID', 'GrLivArea', 'SalePrice', 'MSSubClass', 'LotFrontage', 'LotArea', 'OverallQual', 'OverallCond', 'YearBuilt', 'YearRemodAdd', 'MasVnrArea', 'BsmtFinSF1', 'BsmtFinSF2', 'BsmtUnfSF', 'TotalBsmtSF', '1stFlrSF', '2ndFlrSF', 'LowQualFinSF', 'BsmtFullBath', 'BsmtHalfBath', 'FullBath', 'HalfBath', 'BedroomAbvGr', 'KitchenAbvGr', 'TotRmsAbvGrd', 'Fireplaces', 'GarageYrBlt', 'GarageCars', 'GarageArea', 'WoodDeckSF', 'OpenPorchSF', 'EnclosedPorch', '3SsnPorch', 'ScreenPorch', 'PoolArea', 'MiscVal', 'MoSold', 'YrSold', 'GeoRefNo', 'Latitude', 'Longitude'] Categorical features (object and category): ['MSZoning', 'Street', 'Alley', 'LotShape', 'LandContour', 'Utilities', 'LotConfig', 'LandSlope', 'Neighborhood', 'Condition1', 'Condition2', 'BldgType', 'HouseStyle', 'RoofStyle', 'RoofMatl', 'Exterior1st', 'Exterior2nd', 'MasVnrType', 'ExterQual', 'ExterCond', 'Foundation', 'BsmtQual', 'BsmtCond', 'BsmtExposure', 'BsmtFinType1', 'BsmtFinType2', 'Heating', 'HeatingQC', 'CentralAir', 'Electrical', 'KitchenQual', 'Functional', 'FireplaceQu', 'GarageType', 'GarageFinish', 'GarageQual', 'GarageCond', 'PavedDrive', 'PoolQC', 'Fence', 'MiscFeature', 'SaleType', 'SaleCondition', 'Prop_Addr'] Numerical features from describe(): ['PID', 'GrLivArea', 'SalePrice', 'MSSubClass', 'LotFrontage', 'LotArea', 'OverallQual', 'OverallCond', 'YearBuilt', 'YearRemodAdd', 'MasVnrArea', 'BsmtFinSF1', 'BsmtFinSF2', 'BsmtUnfSF', 'TotalBsmtSF', '1stFlrSF', '2ndFlrSF', 'LowQualFinSF', 'BsmtFullBath', 'BsmtHalfBath', 'FullBath', 'HalfBath', 'BedroomAbvGr', 'KitchenAbvGr', 'TotRmsAbvGrd', 'Fireplaces', 'GarageYrBlt', 'GarageCars', 'GarageArea', 'WoodDeckSF', 'OpenPorchSF', 'EnclosedPorch', '3SsnPorch', 'ScreenPorch', 'PoolArea', 'MiscVal', 'MoSold', 'YrSold', 'GeoRefNo', 'Latitude', 'Longitude'] Categorical features based on unique values: ['MSZoning', 'Street', 'Alley', 'LotShape', 'LandContour', 'Utilities', 'LotConfig', 'LandSlope', 'Condition1', 'Condition2', 'BldgType', 'HouseStyle', 'OverallQual', 'OverallCond', 'RoofStyle', 'RoofMatl', 'MasVnrType', 'ExterQual', 'ExterCond', 'Foundation', 'BsmtQual', 'BsmtCond', 'BsmtExposure', 'BsmtFinType1', 'BsmtFinType2', 'Heating', 'HeatingQC', 'CentralAir', 'Electrical', 'BsmtFullBath', 'BsmtHalfBath', 'FullBath', 'HalfBath', 'BedroomAbvGr', 'KitchenAbvGr', 'KitchenQual', 'Functional', 'Fireplaces', 'FireplaceQu', 'GarageType', 'GarageFinish', 'GarageCars', 'GarageQual', 'GarageCond', 'PavedDrive', 'PoolArea', 'PoolQC', 'Fence', 'MiscFeature', 'YrSold', 'SaleType', 'SaleCondition'] Value counts: PID 909176150    1 923203100    1 909250220    1 534450150    1 528290190    1             .. 531452260    1 528488130    1 923229010    1 528382020    1 906223180    1 Name: count, Length: 2579, dtype: int64   ...   SaleCondition Normal     2413 Partial      82 Abnorml      61 Family       17 Alloca        4 AdjLand       2 Name: count, dtype: int64   ...   Longitude -93.638398    5 -93.636432    5 -93.621618    5 -93.609088    4 -93.639331    4              .. -93.661950    1 -93.687946    1 -93.643655    1 -93.605005    1 -93.682220    1 Name: count, Length: 2423, dtype: int64 info(): <class 'pandas.core.frame.DataFrame'> RangeIndex: 2579 entries, 0 to 2578 Data columns (total 85 columns): #   Column         Non-Null Count  Dtype ---  ------         --------------  ----- 0   PID            2579 non-null   int64 1   GrLivArea      2579 non-null   int64 2   SalePrice      2579 non-null   int64 ... 82  Prop_Addr      2559 non-null   object 83  Latitude       2482 non-null   float64 84  Longitude      2482 non-null   float64 dtypes: float64(14), int64(27), object(44) memory usage: 1.7+ MB

Converting Numerical Features to Categorical Features Upon Careful Consideration

Most of the basic methods above would classify MSSubClass as a numerical feature. However, as highlighted earlier, this feature is in fact a categorical variable. Similarly, features like MoSold (Month Sold) and YrSold (Year Sold) are numerical in nature, but they can often be treated as categorical variables, especially when there is no interest in performing mathematical operations on them. We can use the astype() method in pandas to convert MSSubClass, MoSold, and YrSold to categorical features.

1 2 3

Ames['MSSubClass'] = Ames['MSSubClass'].astype('object') Ames['YrSold'] = Ames['YrSold'].astype('object') Ames['MoSold'] = Ames['MoSold'].astype('object')

After performing this conversion, the count of columns with the “object” data type has increased to 47 (from the previous 44), while “int64” has dropped to 24 (from 27).

1 2 3 4 5 6 7

# Determine the data type for each feature after conversion data_types = Ames.dtypes   # Tally the total by data type type_counts = data_types.value_counts()   print(type_counts)

1 2 3 4

object     47 int64      24 float64    14 dtype: int64

A careful assessment of the data dictionary, the nature of the dataset, and domain expertise can contribute to properly reclassifying data types.

The following is the complete code to count the number of columns of each data type:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

# Load the Ames dataset import pandas as pd Ames = pd.read_csv('Ames.csv')   # Reassign data type Ames['MSSubClass'] = Ames['MSSubClass'].astype('object') Ames['YrSold'] = Ames['YrSold'].astype('object') Ames['MoSold'] = Ames['MoSold'].astype('object')   # Determine the data type for each feature after conversion data_types = Ames.dtypes   # Tally the total by data type type_counts = data_types.value_counts()   print(type_counts)

Missing Data Imputation

Dealing with missing data is a challenge that every data scientist faces. Ignoring missing values or handling them inadequately can lead to skewed analysis and incorrect conclusions. The choice of imputation technique often depends on the nature of the data—categorical or numerical. In addition, information in the data dictionary will be useful (such as the case for Pool Quality) where a missing value (“NA”) has a meaning, namely the absence of this feature for a particular property.

Data Imputation For Categorical Features

You can identify categorical data types and rank them in the order in which they are most affected by missing data.

1 2 3 4 5 6 7 8 9 10 11 12 13 14

# Calculating the percentage of missing values for each column missing_data = Ames.isnull().sum() missing_percentage = (missing_data / len(Ames)) * 100 data_type = Ames.dtypes   # Combining the counts and percentages into a DataFrame for better visualization missing_info = pd.DataFrame({'Missing Values': missing_data, 'Percentage': missing_percentage,                              'Data Type':data_type})   # Sorting the DataFrame by the percentage of missing values in descending order missing_info = missing_info.sort_values(by='Percentage', ascending=False)   # Display columns with missing values of 'object' data type print(missing_info[(missing_info['Missing Values'] > 0) & (missing_info['Data Type'] == 'object')])

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

Missing Values  Percentage Data Type PoolQC                  2570   99.651028    object MiscFeature             2482   96.238852    object Alley                   2411   93.485847    object Fence                   2054   79.643273    object FireplaceQu             1241   48.119426    object GarageCond               129    5.001939    object GarageQual               129    5.001939    object GarageFinish             129    5.001939    object GarageType               127    4.924389    object BsmtExposure              71    2.753005    object BsmtFinType2              70    2.714230    object BsmtFinType1              69    2.675456    object BsmtQual                  69    2.675456    object BsmtCond                  69    2.675456    object Prop_Addr                 20    0.775494    object MasVnrType                14    0.542846    object Electrical                 1    0.038775    object

The data dictionary indicates that missing values for the entire list of categorical features above indicate the absence of that feature for a given property, except for “Electrical”. With this insight, we can impute with the “mode” for the 1 missing data point for the electrical system and impute all others using "None" (with quotations to make it a Python string).

1 2 3 4

mode_value = Ames['Electrical'].mode()[0] Ames['Electrical'].fillna(mode_value, inplace=True) print(mode_value) print(Ames['Electrical'].isnull().sum())

This verifies the mode value is "SBrkr" and the missing value in “Electrical” column is fixed:

1 2

SBrkr 0

Afterward, you can replace all missing values with string "None":

1 2 3 4 5 6 7

missing_categorical = missing_info[(missing_info['Missing Values'] > 0)                            & (missing_info['Data Type'] == 'object')]   for item in missing_categorical.index.tolist():     Ames[item].fillna("None", inplace=True)   print(Ames[missing_categorical.index].isnull().sum())

This confirms that there are now no more missing values for categorical features:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

PoolQC          0 MiscFeature     0 Alley           0 Fence           0 FireplaceQu     0 GarageCond      0 GarageQual      0 GarageFinish    0 GarageType      0 BsmtExposure    0 BsmtFinType2    0 BsmtFinType1    0 BsmtQual        0 BsmtCond        0 Prop_Addr       0 MasVnrType      0 Electrical      0

The complete code is as follows:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

# Load the Ames dataset import pandas as pd Ames = pd.read_csv('Ames.csv')   # Calculating the percentage of missing values for each column missing_data = Ames.isnull().sum() missing_percentage = (missing_data / len(Ames)) * 100 data_type = Ames.dtypes   # Combining the counts and percentages into a DataFrame for better visualization missing_info = pd.DataFrame({'Missing Values': missing_data, 'Percentage': missing_percentage,                              'Data Type':data_type})   # Sorting the DataFrame by the percentage of missing values in descending order missing_info = missing_info.sort_values(by='Percentage', ascending=False)   # Display columns with missing values of 'object' data type print(missing_info[(missing_info['Missing Values'] > 0) & (missing_info['Data Type'] == 'object')])   mode_value = Ames['Electrical'].mode()[0] Ames['Electrical'].fillna(mode_value, inplace=True) print(mode_value) print(Ames['Electrical'].isnull().sum())   missing_categorical = missing_info[(missing_info['Missing Values'] > 0)                            & (missing_info['Data Type'] == 'object')]   for item in missing_categorical.index.tolist():     Ames[item].fillna("None", inplace=True)   print(Ames[missing_categorical.index].isnull().sum())

Data Imputation For Numerical Features

We can apply the same technique demonstrated above to identify numerical data types and rank them in the order in which they are most affected by missing data.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

# Import Numpy import numpy as np   # Calculating the percentage of missing values for each column missing_data = Ames.isnull().sum() missing_percentage = (missing_data / len(Ames)) * 100 data_type = Ames.dtypes   # Combining the counts and percentages into a DataFrame for better visualization missing_info = pd.DataFrame({'Missing Values': missing_data, 'Percentage': missing_percentage,                              'Data Type':data_type})   # Sorting the DataFrame by the percentage of missing values in descending order missing_info = missing_info.sort_values(by='Percentage', ascending=False)   # Display columns with missing values of numeric data type print(missing_info[(missing_info['Missing Values'] > 0)                    & (missing_info['Data Type'] == np.number)])

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

Missing Values  Percentage Data Type LotFrontage              462   17.913920   float64 GarageYrBlt              129    5.001939   float64 Longitude                 97    3.761148   float64 Latitude                  97    3.761148   float64 GeoRefNo                  20    0.775494   float64 MasVnrArea                14    0.542846   float64 BsmtFullBath               2    0.077549   float64 BsmtHalfBath               2    0.077549   float64 BsmtFinSF2                 1    0.038775   float64 GarageArea                 1    0.038775   float64 BsmtFinSF1                 1    0.038775   float64 BsmtUnfSF                  1    0.038775   float64 TotalBsmtSF                1    0.038775   float64 GarageCars                 1    0.038775   float64

The above illustrates that there are fewer instances of missing numerical data versus missing categorical data. However, the data dictionary is not as useful for a straightforward imputation. Whether or not to impute missing data in data science largely depends on the goal of the analysis. Often, a data scientist may generate multiple imputations to account for the uncertainty in the imputation process. Common multiple imputation methods include (but are not limited to) mean, median, and regression imputation. As a baseline, we will illustrate how to employ mean imputation here, but may refer to other techniques depending on the task at hand.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

# Initialize a DataFrame to store the concise information concise_info = pd.DataFrame(columns=['Feature', 'Missing Values After Imputation',                                      'Mean Value Used to Impute'])   # Identify and impute missing numerical values, and store the related concise information missing_numeric_df = missing_info[(missing_info['Missing Values'] > 0)                            & (missing_info['Data Type'] == np.number)]   for item in missing_numeric_df.index.tolist():     mean_value = Ames[item].mean(skipna=True)     Ames[item].fillna(mean_value, inplace=True)       # Append the concise information to the concise_info DataFrame     concise_info.loc[len(concise_info)] = pd.Series({         'Feature': item,         'Missing Values After Imputation': Ames[item].isnull().sum(),         # This should be 0 as we are imputing all missing values         'Mean Value Used to Impute': mean_value     })   # Display the concise_info DataFrame print(concise_info)

This prints:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

Feature Missing Values After Imputation  Mean Value Used to Impute 0    LotFrontage                               0               6.851063e+01 1    GarageYrBlt                               0               1.976997e+03 2      Longitude                               0              -9.364254e+01 3       Latitude                               0               4.203456e+01 4       GeoRefNo                               0               7.136762e+08 5     MasVnrArea                               0               9.934698e+01 6   BsmtFullBath                               0               4.353900e-01 7   BsmtHalfBath                               0               6.208770e-02 8     BsmtFinSF2                               0               5.325950e+01 9     GarageArea                               0               4.668646e+02 10    BsmtFinSF1                               0               4.442851e+02 11     BsmtUnfSF                               0               5.391947e+02 12   TotalBsmtSF                               0               1.036739e+03 13    GarageCars                               0               1.747867e+00

At times, we may also opt to leave the missing value without any imputation to retain the authenticity of the original dataset and remove the observations that do not have complete and accurate data if required. Alternatively, you may also try to build a machine learning model to guess the missing value based on some other data in the same rows, which is the principle behind imputation by regression. As a final step of the above baseline imputation, let us cross-check if there are any missing values.

1 2	missing_values_count = Ames.isnull().sum().sum() print(f'The DataFrame has a total of {missing_values_count} missing values.')

You should see:

1	The DataFrame has a total of 0 missing values.

Congratulations! We have successfully imputed every missing value in the Ames dataset using baseline operations. It’s important to note that numerous other techniques exist for imputing missing data. As a data scientist, exploring various options and determining the most appropriate method for the given context is crucial to producing reliable and meaningful results.

The complete code is as follows:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46

# Load the Ames dataset import pandas as pd import numpy as np Ames = pd.read_csv('Ames.csv')   # Calculating the percentage of missing values for each column missing_data = Ames.isnull().sum() missing_percentage = (missing_data / len(Ames)) * 100 data_type = Ames.dtypes   # Combining the counts and percentages into a DataFrame for better visualization missing_info = pd.DataFrame({'Missing Values': missing_data, 'Percentage': missing_percentage,                              'Data Type':data_type})   # Sorting the DataFrame by the percentage of missing values in descending order missing_info = missing_info.sort_values(by='Percentage', ascending=False)   # Display columns with missing values of numeric data type print(missing_info[(missing_info['Missing Values'] > 0)                    & (missing_info['Data Type'] == np.number)])   # Initialize a DataFrame to store the concise information concise_info = pd.DataFrame(columns=['Feature', 'Missing Values After Imputation',                                      'Mean Value Used to Impute'])   # Identify and impute missing numerical values, and store the related concise information missing_numeric_df = missing_info[(missing_info['Missing Values'] > 0)                            & (missing_info['Data Type'] == np.number)]   for item in missing_numeric_df.index.tolist():     mean_value = Ames[item].mean(skipna=True)     Ames[item].fillna(mean_value, inplace=True)       # Append the concise information to the concise_info DataFrame     concise_info.loc[len(concise_info)] = pd.Series({         'Feature': item,         'Missing Values After Imputation': Ames[item].isnull().sum(),         # This should be 0 as we are imputing all missing values         'Mean Value Used to Impute': mean_value     })   # Display the concise_info DataFrame print(concise_info)   missing_values_count = Ames.isnull().sum().sum() print(f'The DataFrame has a total of {missing_values_count} missing values.')

Further Reading

This section provides more resources on the topic if you want to go deeper.

Resources

• Ames Dataset
• Ames Data Dictionary

Summary

In this tutorial, we explored the Ames Housing dataset through the lens of data science techniques. We discussed the importance of a data dictionary in understanding the dataset’s variables and dove into Python code snippets that help identify and handle these variables effectively.

Understanding the nature of the variables you’re working with is crucial for any data-driven decision-making process. As we’ve seen, the Ames data dictionary serves as a valuable guide in this respect. Coupled with Python’s powerful data manipulation libraries, navigating complex datasets like the Ames Housing dataset becomes a much more manageable task.

Specifically, you learned: 

• The importance of a data dictionary when assessing data types and imputation strategies.
• Identification and reclassification methods for numerical and categorical features.
• How to impute missing categorical and numerical features using the pandas library.

Do you have any questions? Please ask your questions in the comments below, and I will do my best to answer.