AirBnB Data Cleaning

#check the format of digits- dd/dd/dddd--returns false
all(grepl(pattern = "\\d{1,2}/\\d{1,2}/\\d{4}", data2$last_review))

#find row indices where this pattern is present in last-review column
correct_date_format_row_index <- grepl(pattern = "\\d{1,2}/\\d{1,2}/\\d{4}",
                                        data2$last_review)

#filter out rows where this pattern isn't present
data_incorrect_dates <- data2[!correct_date_format_row_index,
                              c('id', 'name', 'last_review')]

##check whether all last_reviews values are NA in data_incorrect_dates-- all are 0
sum(!is.na(data_incorrect_dates$last_review))

#So, 'last_review' column is in format- mm/dd/yyyy

#conver this column to date
data2$last_review <- lubridate::mdy(data2$last_review)

#check range of dates
summary(data2$last_review) ##- max value is "2058" which seems odd

(invalid_year_rows <- data2 %>% 
                      filter(year(last_review) > 2022) %>% 
                      select(id, name, last_review))

#Column- Host-identity-verified (unique values check)
#2 values- "unconfirmed", "verified"
unique(data3$host_identity_verified)

#find total percentage of verified/unconfirmed
#Proportion of confirmed identity/ unconfirmed identity is almost same at ~50%
verification_data <- data3 %>% group_by(host_identity_verified) %>% 
  summarise(total = n()) %>% 
  mutate(percent_share = round((total/nrow(data3)*100),2)) %>% 
  arrange(-percent_share) %>% 
  rename(`Verification Status` = host_identity_verified,
         `Total data points` = total,
         `% in dataset` = percent_share
         )

formattable(verification_data,
            align = c('l', 'c', 'c'),
            list(
              `Verification Status` = formatter("span",
                                                   style = ~ style(color = "black", font.weight = "bold")
                                                   ),#formatter
              `Total data points` = color_tile('#FFD1DC', '#FFB6C1'),
              `% in dataset` = color_tile('#FFD1DC', '#FFB6C1')
                 )#list
            ) #formattable

data3$host_identity_verified <- factor(data3$host_identity_verified,
                                    exclude = NA)

#Column- Cancellation policy
#3 categories: strict, moderate, flexible
unique(data3$cancellation_policy)

#check for proportion for each
#roughly same for all 3, ~33%
cancel_policy <- data3 %>% group_by(cancellation_policy) %>% 
  summarise(total = n()) %>% 
  mutate(percent_share = round((total/nrow(data3))*100,2)) %>% 
  arrange(-percent_share) %>% 
  rename(`Cancellation Policy` = cancellation_policy,
         `Total data points` = total,
         `% in dataset` = percent_share
         )

formattable(cancel_policy,
            align = c('l', 'c', 'c'),
            list(
              `Cancellation Policy` = formatter("span",
                                                   style = ~ style(color = "black", font.weight = "bold")
                                                   ),#formatter
              `Total data points` = color_tile('#FFD1DC', '#FFB6C1'),
              `% in dataset` = color_tile('#FFD1DC', '#FFB6C1')
                 )#list
            ) #formattable

data3$cancellation_policy <- factor(data3$cancellation_policy,
                                    exclude = NA)

#Column- Room type
#4 cats- "Private room", "Entire home/apt", "Shared room", "Hotel room"
unique(data3$room_type)

#Most listings are either Entire home/apt OR private room
type_of_room <- data3 %>% group_by(room_type) %>% 
  summarize(total = n()) %>% 
  mutate(percent_share = round((total/nrow(data3))*100,2)) %>% 
  arrange(-percent_share) %>% 
  rename(`Type of Room` = room_type,
         `Total Rows` = total,
         `% in dataset` = percent_share
         )

formattable(type_of_room,
            align = c('l', 'c', 'c'),
            list(
              `Type of Room` = formatter("span",
                                                   style = ~ style(color = "black", font.weight = "bold")
                                                   ),#formatter
              `Total Rows` = color_tile('#FFD1DC', '#FFB6C1'),
              `% in dataset` = color_tile('#FFD1DC', '#FFB6C1')
                 )#list
            ) #formattable

#convert to factor
data3$room_type <- factor(data3$room_type, exclude = NA)

#check for presence of HTML tags.
any(grepl(pattern = "<.*?>", data3$house_rules))

#some rows contain this value in house-rules column
data3$house_rules <- gsub(pattern = "#NAME?", replacement = "", data3$house_rules)
#some rows contain '?' in house-rules column
data3$house_rules <- gsub(pattern = "^\\?$", replacement = "", data3$house_rules)

#replace empty string with NA in house-rules column
data3$house_rules <- gsub(pattern = "^$", replacement = NA, data3$house_rules)

#remove bulletin symbols like "•" from house_rules
data3$house_rules <- stringr::str_remove_all(data3$house_rules, 
                                         pattern = "[•-]")
#remove numeric bulletin symbols like 1. abc, 2. xyz, ...
data3$house_rules <- stringr::str_remove_all(data3$house_rules,
                                             pattern = "\\d{1}\\.(?=\\s)")

data3$house_rules <- trimws(data3$house_rules)