MakeMyTrip Hotel Analysis

1. Introduction

We are using dataset from MakeMyTrip which offers flight tickets for travel, rail and bus tickets, cab service and hotel booking. We are specifically targeting the hotel booking data scrappped from the website for analysis. This was made available to us from Kaggle

We did an analysis which reflects the best rated hotels in cities across various states in India. Also, narrowed down our analysis to look at which hotels are best in terms of customer experience (Food/Hospitality/Facilities/Value_for_Money). We have further explored the data further by making use of Geospatial Analysis.

We built a regression model to check how the hotel_star_rating is impacted by the customer service parameters like “Hospitality”, “Cleanliness”, “Value_For_Money”, “Food”.

2. Packages Required

As for now, we are using the following libraries:

dplyr - Data Manipulation

tidyr - For Manipulation, Cleaning & Processing data

ggplot2 - To plot the graphs

leaflet - For Geo-Spatial analysis

library(dplyr) ## To manipulate data
library(tidyr) ## To clean/tidy data
library(ggplot2)  ## To plot the graphs
library(leaflet)  ## For Geo- Spatial analysis

3. Data Preparation

Original Datasource: Kaggle

This is a pre-crawled dataset, taken as subset of a bigger dataset (more than 615,000 hotels) that was created by extracting data from MakeMyTrip.com, a travel portal in India. The complete dataset is available on DataStock.

We have 52 variables in the original dataset which we reduced to 15 variables.

Data Cleaning:

1. There were numerous missing values in our dataset which was the major challenge. Initially, we replaced all missing values with “Unknown” except the ones where we need to perform quantitative analysis (Eg: Location Rating, Food, etc.)

2. We have worked on “traveller_rating” which was in the following format “Location:5.0/5 | Hospitality:5.0/5 | Facilities:5.0/5 | Cleanliness:5.0/5 | Value for Money:5.0/5 | Food:5.0/5” and was not useful. Hence, we segregated this column into the respective attributes on which they were rated.

First, we read our csv file into R.

D1 = read.csv("https://www.dropbox.com/s/pdvfz4lp4df0kil/makemytrip_com-travel_sample.csv?dl=1",stringsAsFactors = FALSE)

Initial review of the data resulted in the removal of few insignificant columns (Ex: country “India” as it is uniform throughout the dataset)

D2 <- D1[, -c(3,4, 5, 6, 8,9,10, 13, 16, 18, 20, 21,22, 25, 26,28, 29,30,31,  33:52)]
head(D2)

NA

We have cleaned and standardized all our columns by filling in “Unknown” or “0” at places where there were no values.

## Replacing all the whitespaces with NA/Unknown or 0's as applicable
D3  <-  D2  %>%
  mutate(
        area = sub("^$", "Unknown", area),
        city = sub("^$", "Unknown", city),
        property_name = sub("^$", "Unknown", property_name),
        room_types = sub("^$", "Unknown", room_types),
        hotel_star_rating = sub("star$", "", hotel_star_rating),
        mmt_location_rating = sub("^Location:", "", mmt_location_rating),
        mmt_tripadvisor_count   =   sub("^$",   "0",    mmt_tripadvisor_count),
        mmt_review_count = sub("^$", "0", mmt_review_count),
        mmt_location_rating = sub("..", "", mmt_location_rating)
        )

We have segregated our “traveller_rating” column into “Location, Hospitality, Facilities, Cleanliness, Value_for_Money, Food”. Thus we have spread out our data into different columns for all the above mentioned attributes for the analysis based on specific customer satisfaction factors.

## Separate the values and spread into columns using functions of "tidyr" 
D4 <- separate(
  D3, traveller_rating,
  into = c("Location", "d1", "Hospitality", "d2",
           "Facilities", "d3", "Cleanliness", "d4",
           "d5", "d6", "Value_for_Money", "d7",
           "Food"),
  sep = " | "
)
## Removing the unnecessary columns
D5 <- D4[, -c(14,16,18,20,21,22, 24)]
## Editing the newly generated columns
D5 <- D5 %>%
  mutate(
        Location = sub("^Location:", "",Location),
        Hospitality = sub("^Hospitality:", "",Hospitality),
        Facilities = sub("^Facilities:", "",Facilities),
        Cleanliness = sub("^Cleanliness:", "",Cleanliness),
        Value_for_Money = sub("^Money:", "",Value_for_Money),
        Food = sub("^Food:", "", Food)
  )
## Removing"/5" from each of the values of the newly generated columns
D6 <- D5 %>%
  mutate(
       Location = sub("/5$", "", Location),
       Hospitality = sub("/5$", "", Hospitality),
       Facilities = sub("/5$", "", Facilities),
       Cleanliness = sub("/5$", "", Cleanliness),
       Value_for_Money = sub("/5$", "", Value_for_Money),
       Food = sub("/5$", "", Food)
  )

For final analysis we carried out data conversion for required columns into numeric using the as.numeric function so that we can further run our analysis seamlessly.

## Conversion using the as.numeric function
D7 <- D6
D7$mmt_review_count = as.numeric(D7$mmt_review_count)
D7$hotel_star_rating =  as.numeric(D7$hotel_star_rating)
D7$mmt_location_rating = as.numeric(D7$mmt_location_rating)
D7$mmt_review_score = as.numeric(D7$mmt_review_score)
D7$mmt_tripadvisor_count = as.numeric(D7$mmt_tripadvisor_count)
D7$Location = as.numeric(D7$Location)
D7$Facilities = as.numeric(D7$Facilities)
D7$Cleanliness = as.numeric(D7$Cleanliness)
D7$Value_for_Money = as.numeric(D7$Value_for_Money)
D7$Food = as.numeric(D7$Food)
D7$Hospitality = as.numeric(D7$Hospitality)

Since we had a lot of missing values we did not go ahead with omitting those rows or observations. The best way to resolve this is to ahead with the mean imputation method where we replace all the missing values with the average rating of the entire column so that our analysis is not impacted.

##Mean Imputation Function 
D7$mmt_location_rating <- ifelse(is.na(D7$mmt_location_rating),
                           mean(D7$mmt_location_rating, na.rm=TRUE), 
                           D7$mmt_location_rating)
D7$mmt_review_score <- ifelse(is.na(D7$mmt_review_score), 
                       mean(D7$mmt_review_score, na.rm=TRUE), 
                       D7$mmt_review_score)
D7$Location <- ifelse(is.na(D7$Location), 
               mean(D7$Location, na.rm=TRUE), 
               D7$Location)
D7$Facilities <- ifelse(is.na(D7$Facilities), 
                mean(D7$Facilities, na.rm=TRUE), 
                D7$Facilities)
D7$Cleanliness <- ifelse(is.na(D7$Cleanliness), 
                  mean(D7$Cleanliness, na.rm=TRUE), 
                  D7$Cleanliness)
D7$Value_for_Money <- ifelse(is.na(D7$Value_for_Money), 
                    mean(D7$Value_for_Money, na.rm=TRUE),
                    D7$Value_for_Money)
D7$Food <- ifelse(is.na(D7$Food), mean(D7$Food, na.rm=TRUE),
                  D7$Food)
D7$Hospitality <- ifelse(is.na(D7$Hospitality), 
                  mean(D7$Hospitality, na.rm=TRUE), 
                  D7$Hospitality)

Here we have the summary information for our cleaned dataset

## Preview of the structure of our cleaned dataset
str(D7)

'data.frame':   20063 obs. of  18 variables:
 $ area                 : chr  "Hardasji Ki Magri" "Near Nai Gaon" "Near Bagore Ki Haveli" "Dabok" ...
 $ city                 : chr  "Udaipur" "Udaipur" "Udaipur" "Udaipur" ...
 $ hotel_star_rating    : num  1 3 2 1 2 1 3 3 4 2 ...
 $ latitude             : chr  "24.581788" "24.544342" "24.579942" "24.622607" ...
 $ longitude            : num  73.7 73.6 73.7 73.9 73.7 ...
 $ mmt_location_rating  : num  4.35 4.35 4.35 4.35 4.35 ...
 $ mmt_review_count     : num  0 0 0 0 0 0 0 0 0 0 ...
 $ mmt_review_score     : num  4.50 4.50 1.05e+14 1.05e+14 3.70 ...
 $ mmt_tripadvisor_count: num  6 34 16 3 139 79 92 176 344 12 ...
 $ property_name        : chr  "Zion Home Stay" "Araliayas Resorts" "Shri Udai Palace" "SNP House Airport Hotel And Restaurant" ...
 $ property_type        : chr  "Hotel" "Hotel" "Hotel" "Hotel" ...
 $ room_types           : chr  "Unknown" "Unknown" "Unknown" "`standard" ...
 $ Location             : num  4.14 4.14 4.14 4.14 4.14 ...
 $ Hospitality          : num  3.87 3.87 3.87 3.87 3.87 ...
 $ Facilities           : num  3.73 3.73 3.73 3.73 3.73 ...
 $ Cleanliness          : num  3.8 3.8 3.8 3.8 3.8 ...
 $ Value_for_Money      : num  3.9 3.9 3.9 3.9 3.9 ...
 $ Food                 : num  3.08 3.08 3.08 3.08 3.08 ...

## Preview of the summary of our dataset
summary(D7)

     area               city           hotel_star_rating   latitude        
 Length:20063       Length:20063       Min.   : 0.000    Length:20063      
 Class :character   Class :character   1st Qu.: 1.000    Class :character  
 Mode  :character   Mode  :character   Median : 1.000    Mode  :character  
                                       Mean   : 1.844                      
                                       3rd Qu.: 3.000                      
                                       Max.   :77.173                      
                                       NA's   :39                          
   longitude      mmt_location_rating mmt_review_count  mmt_review_score   
 Min.   :  0.00   Min.   :0.000       Min.   :   0.00   Min.   :0.000e+00  
 1st Qu.: 72.56   1st Qu.:4.354       1st Qu.:   0.00   1st Qu.:4.000e+00  
 Median : 75.85   Median :4.354       Median :   0.00   Median :1.053e+14  
 Mean   : 59.34   Mean   :4.354       Mean   :  10.15   Mean   :1.053e+14  
 3rd Qu.: 77.62   3rd Qu.:4.354       3rd Qu.:   3.00   3rd Qu.:1.053e+14  
 Max.   :130.00   Max.   :9.000       Max.   :1335.00   Max.   :2.016e+17  
 NA's   :42                           NA's   :9                            
 mmt_tripadvisor_count property_name      property_type       room_types       
 Min.   :0.000e+00     Length:20063       Length:20063       Length:20063      
 1st Qu.:0.000e+00     Class :character   Class :character   Class :character  
 Median :2.000e+00     Mode  :character   Mode  :character   Mode  :character  
 Mean   :1.005e+13                                                             
 3rd Qu.:3.000e+01                                                             
 Max.   :2.015e+17                                                             
 NA's   :8                                                                     
    Location      Hospitality      Facilities     Cleanliness    Value_for_Money
 Min.   :0.000   Min.   :0.000   Min.   :1.000   Min.   :0.000   Min.   :0.000  
 1st Qu.:4.138   1st Qu.:3.869   1st Qu.:3.728   1st Qu.:3.805   1st Qu.:3.903  
 Median :4.138   Median :3.869   Median :3.728   Median :3.805   Median :3.903  
 Mean   :4.138   Mean   :3.869   Mean   :3.728   Mean   :3.805   Mean   :3.903  
 3rd Qu.:4.138   3rd Qu.:3.869   3rd Qu.:3.728   3rd Qu.:3.805   3rd Qu.:3.903  
 Max.   :5.000   Max.   :5.000   Max.   :5.000   Max.   :5.000   Max.   :5.000  
                                                                                
      Food      
 Min.   :0.000  
 1st Qu.:3.079  
 Median :3.079  
 Mean   :3.079  
 3rd Qu.:3.079  
 Max.   :5.000  
                

## Preview of the first 6 rows
head(D7)

## Preview of the last 6 rows
tail(D7)

4. Data Exploration:

Here we have filtered our data to find the top 5 cities having most number of hotels in our dataset.

#Created a dataframe to store top 5 cities based on the frequency
top5 <- as.data.frame(table(D8$city))
top5 %>% arrange(desc(Freq))%>%head(n=5)

Here, we have incorporated geospatial analysis which assisted us in visualizing the geopoints which attract more customers. Initially, we filtered on hotels having star rating >=3 and later went on to find which out of these had the most number of reviews

D8 <- D7
#Converted latitude from character variable to numeric
D8$latitude <- as.numeric(D8$latitude)
#Created the dataframe to filter out the hotels with rating more than or equal to 3
D9 = D8 %>%
  filter(hotel_star_rating >= 3 ) %>% 
  filter(!is.na(latitude)) %>%
  filter(!is.na(longitude))
#Created the dataframe to filter out the hotels with rating more than or equal to 3 and review count greater than 100
D10 = D8 %>%
filter(hotel_star_rating >= 3 & mmt_review_count>100) %>% 
  filter(!is.na(latitude)) %>%
  filter(!is.na(longitude))
#Calculated the median of latitude and longitude for geo-spatial analysis
longitude_center = median(D9$longitude)
latitude_center = median(D9$latitude)
#
devtools::install_github("rstudio/leaflet")
#Created the colorpallete for different ratings
colorpal <- colorFactor(topo.colors(6,alpha = 1), 
                       D9$hotel_star_rating)
#Geo-spatial analysis only for hotel_star_rating
leaflet(D9) %>% addTiles() %>%
  addCircles(lng = ~longitude, lat = ~latitude, 
             color = ~colorpal(hotel_star_rating))  %>%
  setView(lng=longitude_center, lat=latitude_center,zoom = 5) %>%
  addLegend("bottomright", pal = colorpal, values = ~hotel_star_rating,
            title = "MMT Hotels start rating across India",
            opacity = 1)

#Geo-spatial analysis only for hotel_star_rating and review counts
leaflet(D10) %>% addTiles() %>%
  addCircles(lng = ~longitude, lat = ~latitude, 
             color = ~colorpal(hotel_star_rating))  %>%
  setView(lng=longitude_center, lat=latitude_center,zoom = 5) %>%
  
  addLegend("bottomright", pal = colorpal, values = ~hotel_star_rating,
            title = "MMT Hotels start rating with atleast 100 reviews on MMT",
            opacity = 1)

5. Analysis/Findings:

Here we summarised the best 10 Cities having the maximum number of hotels

# Created the dataframe to find the cities with the maximum hotel frequency
D8 %>% group_by(city) %>%
  summarise(count = n()) %>%
  arrange(desc(count)) %>%
  head(n = 10) %>%
  ggplot(aes(x = city,y = count,fill=count)) +
  geom_bar(stat = "identity") + coord_flip()

Here we have plotted the top 10 cities with hotels based on the average ratings of Cleanliness,Food,Value For Money respectively

#Plotted the top cities with hotels with best Cleaniliness ratings
D8 %>% 
  group_by(city) %>%
  summarise(avg=mean(Cleanliness)) %>%
  arrange(desc(avg)) %>%
  select(city,avg) %>%
  head(n=10) %>%
  ggplot(aes(x= city,y= avg)) +
  geom_bar(stat = "identity",fill ="#FF3399", colour="black") + coord_flip()

#Plotted the top cities with hotels with best Food ratings
D8 %>%
  group_by(city) %>%
  summarise(avg=mean(Food)) %>%
  arrange(desc(avg)) %>%
  select(city,avg) %>%
  head(n=10) %>%
  ggplot(aes(x=city,y=avg)) +
  geom_bar(stat = "identity",fill="#33CCFF", colour="black") + coord_flip()

##Plotted the top cities with hotels with best Value_for_Money ratings
D8 %>%
  group_by(city) %>%
  summarise(avg=mean(Value_for_Money)) %>%
  arrange(desc(avg)) %>% 
  select(city,avg) %>% head(n=10) %>%
  ggplot(aes(x=city,y=avg)) + 
  geom_bar(stat = "identity",fill="#6600CC", colour="black") + coord_flip()

We performed a multiple regression analysis to understand which of the 5 factors are best correlated or significantly impact the values of hotel star rating.

We found out that the hotel star rating is significantly dependent on the “Hospitality”, “Facilities”, “Cleanliness”, “Value for Money” factors. “Food” and “Location” donot contribute much to the variation in the hotel_star_rating.

## Building our Regression Model 
mmtmodel <- lm(hotel_star_rating ~ Location +
            Hospitality + Facilities + Cleanliness +
            Value_for_Money + Food + mmt_tripadvisor_count,
            data = D8)
summary(mmtmodel)

Call:
lm(formula = hotel_star_rating ~ Location + Hospitality + Facilities + 
    Cleanliness + Value_for_Money + Food + mmt_tripadvisor_count, 
    data = D8)

Residuals:
     Min       1Q   Median       3Q      Max 
-10.3375  -0.6717  -0.6717   0.8441   3.5918 

Coefficients:
                        Estimate Std. Error t value Pr(>|t|)    
(Intercept)            8.347e-01  6.770e-02  12.329  < 2e-16 ***
Location              -2.560e-03  1.701e-02  -0.151   0.8803    
Hospitality            1.098e-01  2.411e-02   4.555 5.27e-06 ***
Facilities             2.024e-01  2.709e-02   7.471 8.26e-14 ***
Cleanliness            8.817e-02  1.994e-02   4.423 9.81e-06 ***
Value_for_Money       -1.950e-01  3.519e-02  -5.540 3.06e-08 ***
Food                   3.043e-02  1.216e-02   2.502   0.0124 *  
mmt_tripadvisor_count  3.050e-03  4.626e-05  65.926  < 2e-16 ***
---
Signif. codes:  0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1

Residual standard error: 0.9723 on 20008 degrees of freedom
  (47 observations deleted due to missingness)
Multiple R-squared:  0.2069,    Adjusted R-squared:  0.2066 
F-statistic: 745.5 on 7 and 20008 DF,  p-value: < 2.2e-16

6. Summary:

The problem statement of our analysis was centered around understanding how the “Traveller_Rating” which was segregated into “Cleanliness”, “Value_For_Money”, “Food” & “Facilities” varies with the “Hotel Star Rating”. Hence, we looked at the top 10 hotels in the cities based on these 4 factors.

We also ran a regression analysis to understand which of these factors impacts our hotel_star_rating the most. Our results show that the hotel star rating is significantly dependent on the “Hospitality”, “Facilities”, “Cleanliness”, “Value for Money” factors. We looked at the p-value for the regression model we built to arrive at this analysis. “Food” and “Location” donot contribute much to the variation in the hotel_star_rating.

We found out the top 10 cities based on the frequency of hotels in a particular city which gave us the insight of cities attracting maximum travellers.These cities were not present when we drill down to find the top cities on customer satisfaction parameter though they have the customer base but they are lacking behind on the customer satisfaction scale

7. Limitations:

We had 52 columns which had irrelevant information on which analysis was not possible. Hence our first step was removing these and standardizing our dataset so that we could run our analysis seamlessly.

Our dataset had a lot of missing values which were replaced using the Mean Imputation Method inorder to ensure our results are not impacted due to the missing data.

Inorder to build upon this analysis in future we would suggest usage of Shiny package to enable interactive binding between input and output variables.