Ames Housing - Part 1 - Laying the foundation - Building a Data Pipeline

Basement Features

1. For one property (Id = 2121), all the basement features are missing (NA). We set TotalBsmtSF (the total basement area) to 0.

2. Now 79 properties have no basement (TotalBsmtSF = 0). For these properties, we change all the missing basement values to None.

3. The quality of basements is different in different neighborhoods. So we fill in each remaining missing value with the most common value in the same Neighborhood.

# 1. Change TotalBsmtSF to 0 when all basement features are NA
#####################################################################################################################
dfClean %>% select(Id, Neighborhood, contains("Bsmt")) %>% dplyr::filter(is.na(TotalBsmtSF))

# A tibble: 1 × 13
     Id Neighborhood BsmtQual BsmtCond BsmtExposure BsmtFinType1 BsmtFinSF1 BsmtFinType2 BsmtFinSF2 BsmtUnfSF TotalBsmtSF BsmtFullBath BsmtHalfBath
  <dbl> <chr>        <chr>    <chr>    <chr>        <chr>             <dbl> <chr>             <dbl>     <dbl>       <dbl>        <dbl>        <dbl>
1  2121 BrkSide      <NA>     <NA>     <NA>         <NA>                 NA <NA>                 NA        NA          NA           NA           NA

dfClean <- dfClean %>% mutate(TotalBsmtSF = if_else(Id == 2121, 0, TotalBsmtSF))

# 2. Change NA values to None or 0 if there is no basement
#####################################################################################################################
dfClean %>% select(Id, Neighborhood, contains("Bsmt")) %>% dplyr::filter(TotalBsmtSF == 0)

# A tibble: 79 × 13
      Id Neighborhood BsmtQual BsmtCond BsmtExposure BsmtFinType1 BsmtFinSF1 BsmtFinType2 BsmtFinSF2 BsmtUnfSF TotalBsmtSF BsmtFullBath BsmtHalfBath
   <dbl> <chr>        <chr>    <chr>    <chr>        <chr>             <dbl> <chr>             <dbl>     <dbl>       <dbl>        <dbl>        <dbl>
 1    18 Sawyer       <NA>     <NA>     <NA>         <NA>                  0 <NA>                  0         0           0            0            0
 2    40 Edwards      <NA>     <NA>     <NA>         <NA>                  0 <NA>                  0         0           0            0            0
 3    91 NAmes        <NA>     <NA>     <NA>         <NA>                  0 <NA>                  0         0           0            0            0
 4   103 SawyerW      <NA>     <NA>     <NA>         <NA>                  0 <NA>                  0         0           0            0            0
 5   157 NAmes        <NA>     <NA>     <NA>         <NA>                  0 <NA>                  0         0           0            0            0
 6   183 Edwards      <NA>     <NA>     <NA>         <NA>                  0 <NA>                  0         0           0            0            0
 7   260 OldTown      <NA>     <NA>     <NA>         <NA>                  0 <NA>                  0         0           0            0            0
 8   343 NAmes        <NA>     <NA>     <NA>         <NA>                  0 <NA>                  0         0           0            0            0
 9   363 Edwards      <NA>     <NA>     <NA>         <NA>                  0 <NA>                  0         0           0            0            0
10   372 ClearCr      <NA>     <NA>     <NA>         <NA>                  0 <NA>                  0         0           0            0            0
# ℹ 69 more rows

vFeaturesChr_bsmt <- dfClean %>% select(contains("Bsmt"))  %>% select_if(is.character) %>% colnames()
vFeaturesNum_bsmt <- dfClean %>% select(contains("Bsmt"))  %>% select_if(is.numeric) %>% colnames()

dfClean <- dfClean %>% 
  mutate_at(all_of(vFeaturesChr_bsmt), ~ case_when(TotalBsmtSF == 0 ~ "None", TRUE ~ .)) %>% 
  mutate_at(all_of(vFeaturesNum_bsmt), ~ case_when(TotalBsmtSF == 0 ~ 0, TRUE ~ .))


# 3. Change NA values in remaining features to the most common value in the neighborhood where TotalBsmtSF > 0
#####################################################################################################################
dfClean %>% select(Id, Neighborhood, contains("Bsmt")) %>% dplyr::filter_all(any_vars(is.na(.)))

# A tibble: 9 × 13
     Id Neighborhood BsmtQual BsmtCond BsmtExposure BsmtFinType1 BsmtFinSF1 BsmtFinType2 BsmtFinSF2 BsmtUnfSF TotalBsmtSF BsmtFullBath BsmtHalfBath
  <dbl> <chr>        <chr>    <chr>    <chr>        <chr>             <dbl> <chr>             <dbl>     <dbl>       <dbl>        <dbl>        <dbl>
1   333 NridgHt      Gd       TA       No           GLQ                1124 <NA>                479      1603        3206            1            0
2   949 CollgCr      Gd       TA       <NA>         Unf                   0 Unf                   0       936         936            0            0
3  1488 Somerst      Gd       TA       <NA>         Unf                   0 Unf                   0      1595        1595            0            0
4  2041 Veenker      Gd       <NA>     Mn           GLQ                1044 Rec                 382         0        1426            1            0
5  2186 Edwards      TA       <NA>     No           BLQ                1033 Unf                   0        94        1127            0            1
6  2218 IDOTRR       <NA>     Fa       No           Unf                   0 Unf                   0       173         173            0            0
7  2219 IDOTRR       <NA>     TA       No           Unf                   0 Unf                   0       356         356            0            0
8  2349 Somerst      Gd       TA       <NA>         Unf                   0 Unf                   0       725         725            0            0
9  2525 CollgCr      TA       <NA>     Av           ALQ                 755 Unf                   0       240         995            0            0

# determine most common values by Neighborhood
dfNeighborhood_mcv <- most_common_value_by_grouping(dfClean %>% dplyr::filter(TotalBsmtSF > 0), "Neighborhood", c("BsmtQual","BsmtCond","BsmtExposure"))
bsmt_fintype2_mcv <- dfClean %>% dplyr::filter(BsmtFinSF2 > 0) %>% pull(BsmtFinType2) %>% find_most_common_value()

dfClean <- dfClean %>% 
  mutate(BsmtQual = if_else(is.na(BsmtQual),
                            map_chr(Neighborhood, ~ with(dfNeighborhood_mcv, BsmtQual[Neighborhood == .x])),
                            BsmtQual)) %>%
  mutate(BsmtCond = if_else(is.na(BsmtCond),
                            map_chr(Neighborhood, ~ with(dfNeighborhood_mcv, BsmtCond[Neighborhood == .x])),
                            BsmtCond)) %>%
  mutate(BsmtExposure = if_else(is.na(BsmtExposure),
                                map_chr(Neighborhood, ~ with(dfNeighborhood_mcv, BsmtExposure[Neighborhood == .x])),
                                BsmtExposure)) %>%
  mutate(BsmtFinType2 = if_else(is.na(BsmtFinType2), bsmt_fintype2_mcv, BsmtFinType2))

# cleanup
rm(vFeaturesChr_bsmt, vFeaturesNum_bsmt)
rm(dfNeighborhood_mcv, bsmt_fintype2_mcv)

Below are histograms of the numerical basement features, and their correlations with SalePrice. A histogram shows how many properties have values in each range. A correlation is a number from -1 to 1. It tells how closely two features move together in a straight line: near 1, they go up together; near -1, one goes down when the other goes up; near 0, they have no straight-line relationship.

We can check that: TotalBsmtSF = BsmtFinSF1 + BsmtFinSF2 + BsmtUnfSF

We also create new features:

• BsmtBath = BsmtFullBath + 0.5 * BsmtHalfBath
• HasBsmt = TotalBsmtSF > 0

# New features: BsmtBath, HasBsmt
dfClean <- dfClean %>% 
  mutate(BsmtBath = BsmtFullBath + 0.5*BsmtHalfBath) %>% 
  mutate(HasBsmt = (TotalBsmtSF > 0),
         HasBsmt = as.character(HasBsmt))

dfBsmt <- dfClean %>% select(SalePrice, contains("Bsmt"))

vFeaturesNum <- dfBsmt %>% select(-SalePrice) %>% select_if(is.numeric) %>% colnames()

p1 <- eda_plot_histograms(dfBsmt, vFeaturesNum, nCols = 2, plotTitle = "Basement Features")
p2 <- eda_plot_correlation_matrix(dfBsmt[c("SalePrice",vFeaturesNum)], outlineVar = "SalePrice", plotTitle = "Correlation with SalePrice")

cowplot::plot_grid(plotlist = list(p1, p2), ncol = 2)

• Most properties have a basement.
• The column plots show that one category is much more common than the others in BsmtFinType2 and in BsmtCond.

vFeaturesChr <- dfBsmt %>% select(-SalePrice) %>% select_if(is.character) %>% colnames()

cowplot::plot_grid(plotlist = map(vFeaturesChr, ~ eda_plot_column(dfBsmt, .x)))

# cleanup
rm(dfBsmt, vFeaturesNum, vFeaturesChr)

Bathroom Features

• We create a new feature for the total number of bathrooms: TotalBath = FullBath + 0.5 * HalfBath + BsmtBath

# New features: HasFireplace
dfClean <- dfClean %>% 
  mutate(TotalBath = FullBath + 0.5*HalfBath + BsmtBath)

dfBath <- dfClean %>% select(SalePrice, contains("Bath"))

vFeaturesNum <- dfBath %>% select(-SalePrice) %>% select_if(is.numeric) %>% colnames()

p1 <- eda_plot_histograms(dfBath, vFeaturesNum, plotTitle = "Bathroom Features")
p2 <- eda_plot_correlation_matrix(dfBath[c("SalePrice",vFeaturesNum)], outlineVar = "SalePrice", plotTitle = "Correlation with SalePrice")

cowplot::plot_grid(plotlist = list(p1, p2), ncol = 2)

# cleanup
rm(dfBath, vFeaturesNum)

Fireplace Features

1. 1420 properties have no fireplace. For these properties, we change FireplaceQu (the fireplace quality) to None.

# 1. Change FireplaceQu to None if there is no fireplace
###############################################################################################################
dfClean %>% select(Id, Neighborhood, contains("Fire")) %>% dplyr::filter(Fireplaces == 0)

# A tibble: 1,420 × 4
      Id Neighborhood Fireplaces FireplaceQu
   <dbl> <chr>             <dbl> <chr>      
 1     1 CollgCr               0 <NA>       
 2     6 Mitchel               0 <NA>       
 3    11 Sawyer                0 <NA>       
 4    13 Sawyer                0 <NA>       
 5    16 BrkSide               0 <NA>       
 6    18 Sawyer                0 <NA>       
 7    19 SawyerW               0 <NA>       
 8    20 NAmes                 0 <NA>       
 9    27 NAmes                 0 <NA>       
10    30 BrkSide               0 <NA>       
# ℹ 1,410 more rows

dfClean <- dfClean %>% mutate_at(vars("FireplaceQu"), ~ case_when(Fireplaces == 0 ~ "None", TRUE ~ .))

• We create a new feature: HasFireplace = Fireplaces > 0
• Many properties have a fireplace.

# New features: HasFireplace
dfClean <- dfClean %>% 
  mutate(HasFireplace = (Fireplaces > 0),
         HasFireplace = as.character(HasFireplace))

dfFireplace <- dfClean %>% select(SalePrice, contains("Fireplace"))

vFeaturesNum <- dfFireplace %>% select(-SalePrice) %>% select_if(is.numeric) %>% colnames()
vFeaturesChr <- dfFireplace %>% select(-SalePrice) %>% select_if(is.character) %>% colnames()

p1 <- eda_plot_histograms(dfFireplace, vFeaturesNum, plotTitle = "Fireplace Features")
p2 <- cowplot::plot_grid(plotlist = map(vFeaturesChr, ~ eda_plot_column(dfFireplace, .x)))
p3 <- eda_plot_correlation_matrix(dfFireplace[c("SalePrice",vFeaturesNum)], outlineVar = "SalePrice", plotTitle = "Correlation with SalePrice")

grid.arrange(arrangeGrob(p1, p2, nrow = 2), p3, ncol = 2)

# cleanup
rm(dfFireplace, vFeaturesNum, vFeaturesChr)

Garage Features

1. If GarageYrBlt is missing, we set it to YearBuilt.

2. 157 properties have no garage. For these properties, GarageType, GarageFinish, GarageQual and GarageCond are None.

3. Garages are different in different neighborhoods. So we fill in each remaining missing value with the most common value (or the median value, for numbers) of the same Neighborhood and GarageType.

# 1. Change GarageYrBlt to YearBuilt if it is NA
############################################################################################################
dfClean <- dfClean %>% mutate(GarageYrBlt = if_else(is.na(GarageYrBlt), YearBuilt, GarageYrBlt))

# 2. Change NA values to None if there is no garage
############################################################################################################
dfClean %>% select(Id, Neighborhood, contains("Garage")) %>% dplyr::filter(GarageArea == 0)

# A tibble: 157 × 9
      Id Neighborhood GarageType GarageYrBlt GarageFinish GarageCars GarageArea GarageQual GarageCond
   <dbl> <chr>        <chr>            <dbl> <chr>             <dbl>      <dbl> <chr>      <chr>     
 1    40 Edwards      <NA>              1955 <NA>                  0          0 <NA>       <NA>      
 2    49 OldTown      <NA>              1920 <NA>                  0          0 <NA>       <NA>      
 3    79 Sawyer       <NA>              1968 <NA>                  0          0 <NA>       <NA>      
 4    89 IDOTRR       <NA>              1915 <NA>                  0          0 <NA>       <NA>      
 5    90 CollgCr      <NA>              1994 <NA>                  0          0 <NA>       <NA>      
 6   100 NAmes        <NA>              1959 <NA>                  0          0 <NA>       <NA>      
 7   109 IDOTRR       <NA>              1919 <NA>                  0          0 <NA>       <NA>      
 8   126 IDOTRR       <NA>              1935 <NA>                  0          0 <NA>       <NA>      
 9   128 OldTown      <NA>              1930 <NA>                  0          0 <NA>       <NA>      
10   141 NAmes        <NA>              1971 <NA>                  0          0 <NA>       <NA>      
# ℹ 147 more rows

vFeaturesChr_garage <- dfClean %>% select(contains("Garage")) %>% select_if(is.character) %>% colnames()

dfClean <- dfClean %>% mutate_at(all_of(vFeaturesChr_garage), ~ case_when(GarageArea == 0 ~ "None", TRUE ~ .))

# 3. Change NA values in remaining features to the most common or median value in the Neighborhood for that GarageType
######################################################################################################################
dfClean %>% select(Id, Neighborhood, contains("Garage")) %>% dplyr::filter_all(any_vars(is.na(.)))

# A tibble: 2 × 9
     Id Neighborhood GarageType GarageYrBlt GarageFinish GarageCars GarageArea GarageQual GarageCond
  <dbl> <chr>        <chr>            <dbl> <chr>             <dbl>      <dbl> <chr>      <chr>     
1  2127 OldTown      Detchd            1910 <NA>                  1        360 <NA>       <NA>      
2  2577 IDOTRR       Detchd            1923 <NA>                 NA         NA <NA>       <NA>      

dfNeighborhood_mcv <- most_common_value_by_grouping(dfClean, c("Neighborhood","GarageType"), c("GarageQual","GarageFinish","GarageCond"))
dfNeighborhood_med <- median_by_grouping(dfClean, c("Neighborhood","GarageType"), c("GarageCars","GarageArea")) %>% 
  ungroup() %>% 
  mutate_if(is.numeric, as.integer)

dfClean <- dfClean %>% 
  mutate(GarageFinish = if_else(is.na(GarageFinish),
                                map2_chr(Neighborhood, GarageType, ~ with(dfNeighborhood_mcv, GarageFinish[Neighborhood==.x & GarageType==.y])),
                                GarageFinish)) %>% 
  mutate(GarageQual = if_else(is.na(GarageQual),
                              map2_chr(Neighborhood, GarageType, ~ with(dfNeighborhood_mcv, GarageQual[Neighborhood==.x & GarageType==.y])),
                              GarageQual)) %>%
  mutate(GarageCond = if_else(is.na(GarageCond),
                              map2_chr(Neighborhood, GarageType, ~ with(dfNeighborhood_mcv, GarageCond[Neighborhood==.x & GarageType==.y])),
                              GarageCond)) %>%
  mutate(GarageCars = if_else(is.na(GarageCars),
                              map2_dbl(Neighborhood, GarageType, ~ with(dfNeighborhood_med, GarageCars[Neighborhood==.x & GarageType==.y])),
                              GarageCars)) %>%
  mutate(GarageArea = if_else(is.na(GarageArea),
                              map2_dbl(Neighborhood, GarageType, ~ with(dfNeighborhood_med, GarageArea[Neighborhood==.x & GarageType==.y])),
                              GarageArea))

# cleanup
rm(vFeaturesChr_garage, dfNeighborhood_mcv, dfNeighborhood_med)

• GarageArea and GarageCars have almost the same correlation with SalePrice.
• We create a new feature: HasGarage = GarageArea > 0
• Most properties have a garage.
• The column plots show that one category is much more common than the others in GarageQual and in GarageCond.

# New features: HasGarage
dfClean <- dfClean %>% 
  mutate(HasGarage = (GarageArea > 0),
         HasGarage = as.character(HasGarage))

dfGarage <- dfClean %>% select(SalePrice, contains("Garage"))

vFeaturesNum <- dfGarage %>% select(-SalePrice) %>% select_if(is.numeric) %>% colnames()
vFeaturesChr <- dfGarage %>% select(-SalePrice) %>% select_if(is.character) %>% colnames()

p1 <- eda_plot_histograms(dfGarage, vFeaturesNum, nCols = 2, plotTitle = "Garage Features")
p2 <- cowplot::plot_grid(plotlist = map(vFeaturesChr, ~ eda_plot_column(dfGarage, .x)))
p3 <- eda_plot_correlation_matrix(dfGarage[c("SalePrice",vFeaturesNum)], outlineVar = "SalePrice", plotTitle = "Correlation with SalePrice")

grid.arrange(arrangeGrob(p1, p2, nrow = 2), p3, ncol = 2)

# cleanup
rm(dfGarage, vFeaturesNum, vFeaturesChr)

Masonry Features

1. For one property (Id = 2611), MasVnrArea (the area of the masonry veneer) is 198, but MasVnrType (the type of masonry) is missing. We set MasVnrType to the most common type in the same neighborhood, among the properties that have masonry.

2. We fill in the missing values of MasVnrType with the most common values for the same Neighborhood and YearRemodAdd.

3. We fill in the missing values of MasVnrArea with the median values for the same Neighborhood and MasVnrType. The median is the middle value: half the values are below it and half are above it.

# 1. Change MasVnrType in Id 2611 as the most common value in the Neighborhood
###############################################################################################################
dfClean %>% select(Id, Neighborhood, contains("Mas")) %>% dplyr::filter(Id == 2611)

# A tibble: 1 × 4
     Id Neighborhood MasVnrType MasVnrArea
  <dbl> <chr>        <chr>           <dbl>
1  2611 Mitchel      <NA>              198

masvnrtype_mcv <- dfClean %>% dplyr::filter(MasVnrArea > 0, Neighborhood == "Mitchel") %>%
  most_common_value_by_grouping(c("Neighborhood"), "MasVnrType") %>% pull(MasVnrType)

dfClean <- dfClean %>% mutate(MasVnrType = if_else(Id == 2611, masvnrtype_mcv, MasVnrType))


# 2. Change remaining NA values in MasVnrType to most common values by Neighborhood and YearRemodAdd
###############################################################################################################
dfClean %>% select(Id, Neighborhood, contains("Mas")) %>% dplyr::filter_all(any_vars(is.na(.)))

# A tibble: 23 × 4
      Id Neighborhood MasVnrType MasVnrArea
   <dbl> <chr>        <chr>           <dbl>
 1   235 Gilbert      <NA>               NA
 2   530 Crawfor      <NA>               NA
 3   651 Somerst      <NA>               NA
 4   937 SawyerW      <NA>               NA
 5   974 Somerst      <NA>               NA
 6   978 Somerst      <NA>               NA
 7  1244 NridgHt      <NA>               NA
 8  1279 CollgCr      <NA>               NA
 9  1692 Gilbert      <NA>               NA
10  1707 Somerst      <NA>               NA
# ℹ 13 more rows

dfNeighborhood_mcv <- most_common_value_by_grouping(dfClean, c("Neighborhood","YearRemodAdd"), "MasVnrType") %>% 
  mutate(MasVnrType = if_else(is.na(MasVnrType), "None", MasVnrType))

dfClean <- dfClean %>% 
  mutate(MasVnrType = if_else(is.na(MasVnrType), 
                              map2_chr(Neighborhood, YearRemodAdd, ~ with(dfNeighborhood_mcv, MasVnrType[Neighborhood == .x & YearRemodAdd == .y])),
                              MasVnrType))

# 3. Change NA values in MasVnrArea to median values by Neighborhood and MasVnrType
###############################################################################################################
dfClean %>% select(Id, Neighborhood, contains("Mas")) %>% dplyr::filter_all(any_vars(is.na(.)))

# A tibble: 23 × 4
      Id Neighborhood MasVnrType MasVnrArea
   <dbl> <chr>        <chr>           <dbl>
 1   235 Gilbert      None               NA
 2   530 Crawfor      None               NA
 3   651 Somerst      None               NA
 4   937 SawyerW      None               NA
 5   974 Somerst      Stone              NA
 6   978 Somerst      None               NA
 7  1244 NridgHt      Stone              NA
 8  1279 CollgCr      BrkFace            NA
 9  1692 Gilbert      None               NA
10  1707 Somerst      Stone              NA
# ℹ 13 more rows

dfNeighborhood_med <- median_by_grouping(dfClean, c("Neighborhood","MasVnrType"), "MasVnrArea")

dfClean <- dfClean %>% 
  mutate(MasVnrArea = if_else(is.na(MasVnrArea),
                              map2_dbl(Neighborhood, MasVnrType, ~ with(dfNeighborhood_med, MasVnrArea[Neighborhood == .x & MasVnrType == .y])),
                              MasVnrArea))

# cleanup
rm(masvnrtype_mcv, dfNeighborhood_mcv, dfNeighborhood_med)

• We create a new feature: HasMasVnr = MasVnrArea > 0
• Many properties have masonry.

# New features: HasMasVnr
dfClean <- dfClean %>% 
  mutate(HasMasVnr = (MasVnrArea > 0),
         HasMasVnr = as.character(HasMasVnr))

dfMasVnr <- dfClean %>% select(SalePrice, contains("MasVnr"))

vFeaturesNum <- dfMasVnr %>% select(-SalePrice) %>% select_if(is.numeric) %>% colnames()
vFeaturesChr <- dfMasVnr %>% select(-SalePrice) %>% select_if(is.character) %>% colnames()

p1 <- eda_plot_histograms(dfMasVnr, vFeaturesNum, plotTitle = "Masonry Features")
p2 <- cowplot::plot_grid(plotlist = map(vFeaturesChr, ~ eda_plot_column(dfMasVnr, .x)))
p3 <- eda_plot_correlation_matrix(dfMasVnr[c("SalePrice",vFeaturesNum)], outlineVar = "SalePrice", plotTitle = "Correlation with SalePrice")

grid.arrange(arrangeGrob(p1, p2, nrow = 2), p3, ncol = 2)

# cleanup
rm(dfMasVnr, vFeaturesNum, vFeaturesChr)

Pool Features

1. If the property has no pool, we change PoolQC (the pool quality) to None.

2. We fill in the remaining missing values of PoolQC with the most common value in the same neighborhood, among the properties that have a pool.

# 1. Change NA values in PoolQC to None if there is no pool
###############################################################################################################
dfClean %>% select(Id, Neighborhood, contains("Pool")) %>% dplyr::filter(PoolArea == 0)

# A tibble: 2,906 × 4
      Id Neighborhood PoolArea PoolQC
   <dbl> <chr>           <dbl> <chr> 
 1     1 CollgCr             0 <NA>  
 2     2 Veenker             0 <NA>  
 3     3 CollgCr             0 <NA>  
 4     4 Crawfor             0 <NA>  
 5     5 NoRidge             0 <NA>  
 6     6 Mitchel             0 <NA>  
 7     7 Somerst             0 <NA>  
 8     8 NWAmes              0 <NA>  
 9     9 OldTown             0 <NA>  
10    10 BrkSide             0 <NA>  
# ℹ 2,896 more rows

dfClean <- dfClean %>% mutate(PoolQC = if_else(PoolArea == 0, "None", PoolQC))


# 2. Change NA values in remaining PoolQC to the most common value in the Neighborhood where PoolArea > 0
###############################################################################################################
(dfMissing <- dfClean %>% select(Id, Neighborhood, contains("Pool")) %>% dplyr::filter_all(any_vars(is.na(.))))

# A tibble: 3 × 4
     Id Neighborhood PoolArea PoolQC
  <dbl> <chr>           <dbl> <chr> 
1  2421 NAmes             368 <NA>  
2  2504 SawyerW           444 <NA>  
3  2600 Mitchel           561 <NA>  

dfNeighborhood_mcv <- most_common_value_by_grouping(dfClean %>% dplyr::filter(PoolArea > 0), "Neighborhood", "PoolQC") %>% 
  mutate(PoolQC = if_else(is.na(PoolQC), "None", PoolQC))

dfMissing <- dfMissing %>% mutate(PoolQC = map_chr(Neighborhood, ~ with(dfNeighborhood_mcv, PoolQC[Neighborhood == .x])))

dfClean$PoolQC[dfClean$Id %in% dfMissing$Id] <- dfMissing$PoolQC

# cleanup
rm(dfNeighborhood_mcv, dfMissing)

• We create a new feature: HasPool = PoolArea > 0
• Most properties do not have a pool.

# New features: HasGarage
dfClean <- dfClean %>% 
  mutate(HasPool = (PoolArea > 0),
         HasPool = as.character(HasPool))

dfPool <- dfClean %>% select(SalePrice, contains("Pool"))

vFeaturesNum <- dfPool %>% select(-SalePrice) %>% select_if(is.numeric) %>% colnames()
vFeaturesChr <- dfPool %>% select(-SalePrice) %>% select_if(is.character) %>% colnames()

p1 <- eda_plot_histograms(dfPool, vFeaturesNum, plotTitle = "Pool Features")
p2 <- cowplot::plot_grid(plotlist = map(vFeaturesChr, ~ eda_plot_column(dfPool, .x)))
p3 <- eda_plot_correlation_matrix(dfPool[c("SalePrice",vFeaturesNum)], outlineVar = "SalePrice", plotTitle = "Correlation with SalePrice")

grid.arrange(arrangeGrob(p1, p2, nrow = 2), p3, ncol = 2)

# cleanup
rm(dfPool, vFeaturesNum, vFeaturesChr)

Porch Features

• We create new features for:
  • The total porch area: PorchSF = OpenPorchSF + EnclosedPorch + 3SsnPorch + ScreenPorch
  • Whether the property has a porch: HasPorch = PorchSF > 0

# New features: PorchSF, HasPorch
dfClean <- dfClean %>% 
  mutate(PorchSF = OpenPorchSF + EnclosedPorch + `3SsnPorch` + ScreenPorch) %>% 
  mutate(HasPorch = PorchSF > 0,
         HasPorch = as.character(HasPorch))

dfPorch <- dfClean %>% select(SalePrice, contains("Porch"))

vFeaturesNum <- dfPorch %>% select(-SalePrice) %>% select_if(is.numeric) %>% colnames()
vFeaturesChr <- dfPorch %>% select(-SalePrice) %>% select_if(is.character) %>% colnames()

p1 <- eda_plot_histograms(dfPorch, vFeaturesNum, plotTitle = "Porch Features")
p2 <- cowplot::plot_grid(plotlist = map(vFeaturesChr, ~ eda_plot_column(dfPorch, .x)))
p3 <- eda_plot_correlation_matrix(dfPorch[c("SalePrice",vFeaturesNum)], outlineVar = "SalePrice", plotTitle = "Correlation with SalePrice")

grid.arrange(arrangeGrob(p1, p2, nrow = 2), p3, ncol = 2)

# cleanup
rm(dfPorch, vFeaturesNum, vFeaturesChr)

Built Area Features

• We create a new feature for the total built area in square feet: TotalSF = GrLivArea + TotalBsmtSF

dfClean <- dfClean %>% 
  mutate(TotalSF = GrLivArea + TotalBsmtSF)

vFeaturesNum <- c("TotalSF","GrLivArea","TotalBsmtSF")

p1 <- eda_plot_histograms(dfClean, vFeaturesNum, plotTitle = "Built Area Features")
p2 <- eda_plot_correlation_matrix(dfClean[c("SalePrice",vFeaturesNum)], outlineVar = "SalePrice", plotTitle = "Correlation with SalePrice")

cowplot::plot_grid(plotlist = list(p1, p2), ncol = 2)

# cleanup
rm(vFeaturesNum)

Construction Year Features

• We create new features for:
  • The period when the property was built (Vintage): 1945 or earlier, 1946-1999, 2000 or later
  • The age of the property when it was sold, counted from its last remodel (or from its construction, if it was never remodeled): PropertyAge = YrSold - YearRemodAdd
  • Whether the property was new or newly remodeled when it was sold: IsNew = YearRemodAdd == YrSold
  • Whether the property was ever remodeled: IsRemod = YearRemodAdd > YearBuilt

# New feature: Vintage
dfClean <- dfClean %>% 
  mutate(Vintage = cut(YearBuilt, breaks = c(1800, 1945, 1999, 2100), 
                       labels = c("1945 or ealier","1946-1999","2000 or later"),
                       ordered_result = T)) %>% 
  mutate(PropertyAge = YrSold - YearRemodAdd) %>% 
  mutate(IsNew = (YearBuilt == YrSold),
         IsNew = as.character(IsNew)) %>% 
  mutate(IsRemod = (YearRemodAdd > YearBuilt),
         IsRemod = as.character(IsRemod))

vFeaturesNum <- dfClean %>% select(contains("Year"),contains("Yr"),"PropertyAge") %>% colnames() %>% sort()
vFeaturesChr <- c("IsNew","IsRemod","Vintage")

p1 <- eda_plot_histograms(dfClean, vFeaturesNum, nCols = 2, plotTitle = "Construction Year Features")
p2 <- cowplot::plot_grid(plotlist = map(vFeaturesChr, ~ eda_plot_column(dfClean, .x)))
p3 <- eda_plot_correlation_matrix(dfClean[c("SalePrice",vFeaturesNum)], outlineVar = "SalePrice", plotTitle = "Correlation with SalePrice")

grid.arrange(arrangeGrob(p1, p2, nrow = 2), p3, ncol = 2)

# cleanup
rm(vFeaturesNum, vFeaturesChr)

Neighborhood Features

Type of Neighborhood: The dataset has 25 neighborhoods. In real estate, location is very important. Some neighborhoods clearly have higher prices than others.

We can put the neighborhoods into fewer groups, by their median SalePrice:

• Type1: StoneBr, NridgHt, NoRidge
• Type2: Veenker, Timber, Somerst
• Type3: Crawfor, CollgCr, ClearCr, Blmngtn, Gilbert, NWAmes, SawyerW
• Type4: Mitchel, NPkVill, NAmes, SWISU, Sawyer, Blueste, BrkSide, Edwards, OldTown
• Type5: IDOTRR, BrDale, MeadowV

dfClean <- dfClean %>% 
  # lNeighborhoodTypes is defined with the neighborhood map at the start of this post
  mutate(NeighborhoodType = fct_collapse(Neighborhood, !!!lNeighborhoodTypes)) %>% 
  mutate(NeighborhoodType = factor(NeighborhoodType, levels = c("Type5","Type4","Type3","Type2","Type1"), ordered = TRUE))

p1 <- eda_plot_box(dfClean, "Neighborhood", "SalePrice", yLabelsScale = scales::label_dollar(accuracy = 1))
p2 <- eda_plot_box(dfClean, "NeighborhoodType", "SalePrice", yLabelsScale = scales::label_dollar(accuracy = 1))

cowplot::plot_grid(plotlist = list(p1, p2), ncol = 2)

Other Missing Features

1. In MiscFeature, Alley and Fence, we change the missing values to None.

2. In Utilities, Functional and SaleType, we fill in the missing values with the most common value of each feature.

3. In LotFrontage, we fill in the missing values with the median value of the same Neighborhood.

4. In MSZoning, KitchenQual, Exterior1st, Exterior2nd and Electrical, we fill in the missing values with the most common value of the same Neighborhood.

# 1. & 2. change NA values to None or most common value of that feature
####################################################################################################################
dfClean <- dfClean %>% 
  mutate_at(c("MiscFeature", "Alley", "Fence"), change_na_to, value = "None") %>% 
  mutate_at(c("Utilities"), change_na_to_most_common_value) %>% 
  mutate_at(c("Functional"), change_na_to_most_common_value) %>% 
  mutate_at(c("SaleType"), change_na_to_most_common_value)

# 3. In LotFrontage, change NA values to the median value by neighborhood
####################################################################################################################
dfNeighborhood_med <- median_by_grouping(dfClean, "Neighborhood", "LotFrontage")

dfClean <- dfClean %>% 
  mutate(LotFrontage = if_else(is.na(LotFrontage), 
                               map_dbl(Neighborhood, ~ with(dfNeighborhood_med, LotFrontage[Neighborhood == .x])), 
                               LotFrontage))

# 4. In MSZoning,KitchenQual,Exterior1st,Exterior2nd,Electrical change NA values to the median value by neighborhood
dfNeighborhood_mcv <- most_common_value_by_grouping(dfClean, "Neighborhood", c("MSZoning","KitchenQual","Exterior1st","Exterior2nd","Electrical"))

dfClean <- dfClean %>% 
  mutate(MSZoning = if_else(is.na(MSZoning),
                            map_chr(Neighborhood, ~ with(dfNeighborhood_mcv, MSZoning[Neighborhood == .x])),
                            MSZoning)) %>%
  mutate(KitchenQual = if_else(is.na(KitchenQual),
                              map_chr(Neighborhood, ~ with(dfNeighborhood_mcv, KitchenQual[Neighborhood == .x])),
                              KitchenQual)) %>%
  mutate(Exterior1st = if_else(is.na(Exterior1st),
                              map_chr(Neighborhood, ~ with(dfNeighborhood_mcv, Exterior1st[Neighborhood == .x])),
                              Exterior1st)) %>%
  mutate(Exterior2nd = if_else(is.na(Exterior2nd),
                              map_chr(Neighborhood, ~ with(dfNeighborhood_mcv, Exterior2nd[Neighborhood == .x])),
                              Exterior2nd)) %>%
  mutate(Electrical = if_else(is.na(Electrical),
                              map_chr(Neighborhood, ~ with(dfNeighborhood_mcv, Electrical[Neighborhood == .x])),
                              Electrical))

# cleanup
rm(dfNeighborhood_med, dfNeighborhood_mcv)