---
title: "How has tourism recovered in Australia and Victoria more specifically after COVID-19 restrictions?"
output: 
  flexdashboard::flex_dashboard:
    orientation: columns
    vertical_layout: fill
    source_code: embed
---

```{r setup, include=FALSE}
library(readxl)
library(dplyr)
library(tidyr)
library(ggplot2)
library(plotly)
library(flexdashboard)


#function for column concatenation
rename_col_vic <- function(x){
  colnames(x) <- paste0(colnames(x),x[1:2,],sep="")
  x[-c(1:2),]
}
###VICTORIAN DATA IMPORT####
#overnight vis to regions sheet 17 - 6:23
region_night_vis <- read_xlsx("Domestic_visitor_and_expenditure_estimates_year_ending_June_2022.xlsx",
                              sheet = 17, range = cell_rows(5:23))
region_night_vis <- rename_col_vic(region_night_vis)

#vis nights to regions sheet 17 - 25:42
region_vis_nights <- read_xlsx("Domestic_visitor_and_expenditure_estimates_year_ending_June_2022.xlsx",
                               sheet = 17, range = cell_rows(24:42))
region_vis_nights <- rename_col_vic(region_vis_nights)

#interstate vis vic regions sheet 17 - 44:61
region_inter_vis <- read_xlsx("Domestic_visitor_and_expenditure_estimates_year_ending_June_2022.xlsx",
                              sheet = 17, range = cell_rows(43:61))
region_inter_vis <- rename_col_vic(region_inter_vis)

#interstate vis nights regions sheet 17 - 63-80
region_inter_vis_nights <- read_xlsx("Domestic_visitor_and_expenditure_estimates_year_ending_June_2022.xlsx",
                                     sheet = 17, range = cell_rows(62:80))
region_inter_vis_nights <- rename_col_vic(region_inter_vis_nights)

#intrastate vis regions sheet 17-  82-99
region_intra_vis <- read_xlsx("Domestic_visitor_and_expenditure_estimates_year_ending_June_2022.xlsx",
                              sheet = 17, range = cell_rows(81:99))
region_intra_vis <- rename_col_vic(region_intra_vis)

#intrastate vis regions nights sheet 17 -  101-118
region_intra_vis_nights <- read_xlsx("Domestic_visitor_and_expenditure_estimates_year_ending_June_2022.xlsx",
                                     sheet = 17, range = cell_rows(100:118))
region_intra_vis_nights <- rename_col_vic(region_intra_vis_nights)

#domestic daytrip regions sheet 17 - 120-136
region_daytrip <- read_xlsx("Domestic_visitor_and_expenditure_estimates_year_ending_June_2022.xlsx",
                            sheet = 17, range = cell_rows(119:136))
region_daytrip <- rename_col_vic(region_daytrip)

#overnight spend in regions sheet 18 - skip =2
region_night_spend <- read_xlsx("Domestic_visitor_and_expenditure_estimates_year_ending_June_2022.xlsx",
                                sheet = 18, skip = 1)
region_night_spend <- rename_col_vic(region_night_spend)

#daytrip spend in regions sheet 19 skip2
region_day_spend <- read_xlsx("Domestic_visitor_and_expenditure_estimates_year_ending_June_2022.xlsx",
                              sheet = 19, skip = 1)
region_day_spend <- rename_col_vic(region_day_spend)

#total spend in regions sheet 20 skip2
region_tot_spend <- read_xlsx("Domestic_visitor_and_expenditure_estimates_year_ending_June_2022.xlsx",
                              sheet = 20, skip = 1)
region_tot_spend <- rename_col_vic(region_tot_spend)

####DOMESTIC DATA IMPORT####
#Domestic overnight visitors
dom_vis_night <- read_xlsx("Domestic_visitor_and_expenditure_estimates_year_ending_June_2022.xlsx",
                           sheet = 8, skip = 2)
dom_vis_night <- rename_col_vic(dom_vis_night)

#Domestic daytrip visitors
dom_vis_day <- read_xlsx("Domestic_visitor_and_expenditure_estimates_year_ending_June_2022.xlsx",
                         sheet = 9, skip = 2)
dom_vis_day <- rename_col_vic(dom_vis_day)

#Interstate visitors - sheet 10
dom_vis_inter <- read_xlsx("Domestic_visitor_and_expenditure_estimates_year_ending_June_2022.xlsx",
                           sheet = 10, skip = 2)
dom_vis_inter <- rename_col_vic(dom_vis_inter)

#Intrastate visitors - sheet 11
dom_vis_intra <- read_xlsx("Domestic_visitor_and_expenditure_estimates_year_ending_June_2022.xlsx",
                           sheet = 11, skip = 2)
dom_vis_intra <- rename_col_vic(dom_vis_intra)

####INTERNATIONAL DATA IMPORT####
international <- read_excel("IVS_TOURISM_RESULTS_YE_JUN_2022_2019.xlsx", sheet = 3,
                            skip = 4)

international_purpose <- read_excel("IVS_TOURISM_RESULTS_YE_JUN_2022_2019.xlsx", sheet = 2,
                                    skip = 2)
international_purpose <- rename_col_vic(international_purpose)
#VICTORIAN DATA WARANGLING 
#vic spending
vic_region_spend <- region_tot_spend %>%
  select('Area'= 1,"Yearly change,2021-22" = 8,
         "3-year change,2019-22" = 9)%>%
  mutate('Spend_type'= 'Total') %>% na.omit() %>%
  full_join(region_day_spend %>% select('Area'= 1,"Yearly change,2021-22" = 8,
                                        "3-year change,2019-22" = 9)%>%
              mutate('Spend_type'= 'Day') %>% na.omit()) %>%
  full_join(region_night_spend %>% select('Area'= 1,"Yearly change,2021-22" = 8,
                                          "3-year change,2019-22" = 9)%>%
              mutate('Spend_type'= 'Overnight') %>%na.omit())

#variable datatype converisons
vic_region_spend$`3-year change,2019-22` <-
  vic_region_spend$`3-year change,2019-22` %>% as.numeric()

vic_region_spend$`Yearly change,2021-22` <-
  vic_region_spend$`Yearly change,2021-22` %>% as.numeric()

vic_region_spend$Spend_type <- vic_region_spend$Spend_type %>%
  as.factor()
vic_region_spend <-  vic_region_spend %>%
  mutate(`3-year change,2019-22`=`3-year change,2019-22`*100) %>%
  mutate(`Yearly change,2021-22`=`Yearly change,2021-22`*100)

#vic visitors
vic_visitors <- region_night_vis %>%
  select('Area'= 1,"Yearly change,2021-22" = 13,
         "3-year change,2019-22" = 14)%>%
  mutate('Vis_type'= 'Total') %>% na.omit() %>% 
  full_join(region_inter_vis %>% 
              select('Area'= 1,"Yearly change,2021-22" = 13,
                     "3-year change,2019-22" = 14)%>%
              mutate('Vis_type'= 'Interstate overnight') %>% na.omit()) %>% 
  full_join(region_intra_vis %>% 
              select('Area'= 1,"Yearly change,2021-22" = 13,
                     "3-year change,2019-22" = 14)%>%
              mutate('Vis_type'= 'Intrastate overnight') %>% na.omit()) %>% 
  full_join(region_daytrip %>% 
              select('Area'= 1,"Yearly change,2021-22" = 13,
                     "3-year change,2019-22" = 14)%>%
              mutate('Vis_type'= 'Daytrip') %>% na.omit())

#variable datatype converisons
vic_visitors$`3-year change,2019-22` <-
  vic_visitors$`3-year change,2019-22` %>% as.numeric()

vic_visitors$`Yearly change,2021-22` <- 
  vic_visitors$`Yearly change,2021-22` %>% as.numeric()

vic_visitors$Vis_type <- vic_visitors$Vis_type %>% 
  as.factor()

##vic visitor night estimates

vic_vis_nights <- region_vis_nights %>%
  select('Area'= 1,"Yearly change,2021-22" = 13,
         "3-year change,2019-22" = 14)%>%
  mutate('Vis_type'= 'Total') %>% na.omit() %>% 
  full_join(region_inter_vis_nights %>%
              select('Area'= 1,"Yearly change,2021-22" = 13,
                     "3-year change,2019-22" = 14)%>%
              mutate('Vis_type'= 'Interstate') %>% na.omit()) %>% 
  full_join(region_intra_vis_nights %>%
              select('Area'= 1,"Yearly change,2021-22" = 13,
                     "3-year change,2019-22" = 14)%>%
              mutate('Vis_type'= 'Intrastate') %>% na.omit())

#variable datatype converisons
vic_vis_nights$`3-year change,2019-22` <-
  vic_vis_nights$`3-year change,2019-22` %>% as.numeric()

vic_vis_nights$`Yearly change,2021-22` <- 
  vic_vis_nights$`Yearly change,2021-22` %>% as.numeric()

vic_vis_nights$Vis_type <- vic_vis_nights$Vis_type %>% 
  as.factor()


####DOMESTIC DATA WRANGLING####

target_sydney <- ("Sydney")
#spending df construction
domestic_spending <- dom_vis_night %>% select('Area'= 1,
                                              "3-year change,2019-22" = 5)%>%
  mutate('Spend_type'= 'Overnight visitor') %>% na.omit() %>% 
  full_join(dom_vis_day %>% select('Area'= 1,
                                   "3-year change,2019-22" = 5)%>%
              mutate('Spend_type'= 'Daytrip visitor') %>% na.omit()) %>% 
  full_join(dom_vis_inter %>% select('Area'= 1,
                                     "3-year change,2019-22" = 5)%>%
              mutate('Spend_type'= 'Interstate visitor') %>% na.omit()) %>% 
  full_join(dom_vis_intra %>% select('Area'= 1,
                                     "3-year change,2019-22" = 5)%>%
              mutate('Spend_type'= 'Intrastate visitor') %>% na.omit()) %>% 
  filter(!Area %in% target_sydney)

#datatype conversions
domestic_spending$`3-year change,2019-22` <-
  domestic_spending$`3-year change,2019-22` %>% as.numeric()

domestic_spending$Spend_type <- domestic_spending$Spend_type %>% 
  as.factor()

domestic_spending$Area <- 
  factor(domestic_spending$Area,
         levels = c("Australia","New South Wales","Victoria",
                    "Melbourne","Regional Victoria","Queensland",
                    "South Australia","Western Australia","Tasmania",
                    "Northern Territory","ACT"))

#Visitor estimates df construction
domestic_visitors <- dom_vis_night %>% select('Area'= 1,
                                              "3-year change,2019-22" = 10)%>%
  mutate('Visit_type'= 'Overnight') %>% na.omit() %>% 
  full_join(dom_vis_day %>% select('Area'= 1,
                                   "3-year change,2019-22" = 10)%>%
              mutate('Visit_type'= 'Daytrip') %>% na.omit()) %>% 
  full_join(dom_vis_inter %>% select('Area'= 1,
                                     "3-year change,2019-22" = 10)%>%
              mutate('Visit_type'= 'Interstate Overnight') %>% na.omit()) %>% 
  full_join(dom_vis_intra %>% select('Area'= 1,
                                     "3-year change,2019-22" = 10)%>%
              mutate('Visit_type'= 'Intrastate Overnight') %>% na.omit()) %>% 
  filter(!Area %in% target_sydney)

#datatype conversions
domestic_visitors$`3-year change,2019-22` <-
  domestic_visitors$`3-year change,2019-22` %>% as.numeric()

domestic_visitors$Visit_type <- domestic_visitors$Visit_type %>% 
  as.factor()

domestic_visitors$Area <- 
  factor(domestic_visitors$Area,
         levels = c("Australia","New South Wales","Victoria",
                    "Melbourne","Regional Victoria","Queensland",
                    "South Australia","Western Australia","Tasmania",
                    "Northern Territory","ACT"))

####INTERNATIONAL DATA WRANGLING####
int_visitors <-
  international %>% select("State"=1,"3-year change,2019-22"=5) %>%
  mutate('Indicator'= 'Visitor estimates')

int_spend <- 
  international %>% select("State"=1,"3-year change,2019-22"=17) %>%
  mutate('Indicator'= 'Spend in Australia')

int_tourism <- int_visitors %>% full_join(int_spend) %>% na.omit()

#datatype conversions
int_tourism$Indicator <- int_tourism$Indicator %>% as.factor()

#international purpose of trip
int_purp_vis <- international_purpose %>% select("Purpose of visit"=1,"3-year change,2019-22"=5) %>%
  mutate('Indicator'= 'Visitor estimates')

int_purp_spend <- international_purpose %>% select("Purpose of visit"=1,"3-year change,2019-22"=17) %>%
  mutate('Indicator'= 'Spend in Australia')

int_tourism_spend <- int_purp_spend %>% full_join(int_purp_vis) %>% na.omit()

#datatype conversion
int_tourism_spend$Indicator <- int_tourism_spend$Indicator %>% as.factor()
int_tourism_spend$`3-year change,2019-22` <- int_tourism_spend$`3-year change,2019-22` %>% as.numeric()


####VICTORIA PLOTS####
##spending plot
vic_region_spend$Area <-
  factor(vic_region_spend$Area, levels = c("Melbourne","Total Victoria","Yarra Valley and Dandenong Ranges","Murray","Victoria's High Country",
                                       "Mornington Peninsula","Goldfields","Daylesford and the Macedon Ranges","Regional Victoria","Geelong and the Bellarine",
                                       "Phillip Island","Great Ocean Road","Grampians","Gippsland"))
                                       
                                       

p_spend <- plot_ly(data = vic_region_spend,
                   x= ~Area,
                   y= ~`3-year change,2019-22`,
                   color =  ~Spend_type,
                   type = "bar") %>% 
  layout(yaxis=list(zeroline=FALSE,showgrid=T, title='3-year % change, June 2019-June22'),
         xaxis= list(title='Area',showgrid=T,tickangle=-45, tickfont=list(size=10)),
         title= "Domestic tourism expenditure within Victoria pre/post COVID-19")


####visitor numbers plot ###
vic_visitors$Area <-
  factor(vic_visitors$Area, levels = c("Melbourne","Total Victoria","Yarra Valley and Dandenong Ranges",
                                       "Mornington Peninsula","Daylesford and the Macedon Ranges","Goldfields",
                                      "Murray","Grampians","Geelong and the Bellarine","Regional Victoria","Victoria's High Country",
                                      "Great Ocean Road","Phillip Island","Gippsland"))

target_vis <- c("Phillip Island","Daylesford and the Macedon Ranges",
                "Yarra Valley and Dandenong Ranges","Mornington Peninsula")

vic_visitors <- vic_visitors %>% 
  mutate(`3-year change,2019-22`=`3-year change,2019-22`*100) %>% 
  mutate(`Yearly change,2021-22`=`Yearly change,2021-22`*100)


p_vis <- plot_ly(data = vic_visitors,
                 x= ~Area,
                 y= ~`3-year change,2019-22`,
                 color =  ~Vis_type,
                 type = "bar") %>% 
  layout(yaxis=list(zeroline=FALSE,showgrid=T, title='3-year % change, June 2019-June22'),
         xaxis= list(title='Area',showgrid=T, tickangle=-45, tickfont=list(size=9)),
         title= "Domestic visitor estimates within Victoria pre/post COVID-19")

##vic vis nights plot
vic_vis_nights <- vic_vis_nights %>% 
  mutate(`3-year change,2019-22`=`3-year change,2019-22`*100) %>% 
  mutate(`Yearly change,2021-22`=`Yearly change,2021-22`*100)

#changing factor levels for better appearance on plot,ordered by total visitor nights
vic_vis_nights$Area <-
  factor(vic_vis_nights$Area, levels = c("Melbourne","Total Victoria","Yarra Valley and Dandenong Ranges",
                                       "Mornington Peninsula","Daylesford and the Macedon Ranges","Goldfields",
                                       "Murray","Grampians","Regional Victoria","Victoria's High Country",
                                       "Great Ocean Road","Gippsland","Geelong and the Bellarine","Phillip Island"),
         ordered = TRUE)

p_vis_nights <- plot_ly(data = vic_vis_nights,
                        x= ~Area,
                        y= ~`3-year change,2019-22`,
                        color =  ~Vis_type,
                        type = "bar") %>% 
  layout(yaxis=list(zeroline=FALSE,showgrid=T,title='3-year % change, June 2019-June22'),
         xaxis= list(title='Area',showgrid=T,tickangle=-45, tickfont=list(size=9)),
         title= "Domestic visitor night estimates within Victoria pre/post COVID-19")


#DOMESTIC AUSTRALIA PLOTS#####
domestic_spending <- domestic_spending %>% 
  mutate(`3-year change,2019-22`=`3-year change,2019-22`*100)

p_domest_spend <- plot_ly(data = domestic_spending,
        x=~reorder(Area,`3-year change,2019-22`),
        y= ~`3-year change,2019-22`,
        color =~Spend_type,
        type = "bar") %>% 
  layout(yaxis=list(zeroline=FALSE,showgrid=T, title='3-year % change, June 2019-June22'),
         xaxis= list(title='Area',showgrid=T,tickangle=-45, tickfont=list(size=10)),
         title= "Domestic visitor expenditure estimates within Australia pre/post COVID-19")

##domestic visitors
domestic_visitors <- domestic_visitors %>% 
  mutate(`3-year change,2019-22`=`3-year change,2019-22`*100)

p_domest_vis <- plot_ly(data = domestic_visitors,
        x=~reorder(Area,`3-year change,2019-22`),
        y= ~`3-year change,2019-22`,
        color =  ~Visit_type,
        type = "bar") %>% 
  layout(yaxis=list(zeroline=FALSE,showgrid=T,title='3-year % change, June 2019-June22'),
         xaxis= list(title='Area',showgrid=T,tickangle=-45, tickfont=list(size=10)),
         title= "Domestic visitors estimates within Australia pre/post COVID-19")


######INTERNATIONAL PLOTS###
p_intern <- plot_ly(data = int_tourism,
        x= ~State,
        y= ~`3-year change,2019-22`,
        color =  ~Indicator,
        type = "bar") %>% 
  layout(yaxis=list(zeroline=FALSE, title='3-year % change, June 2019-June22'),
         xaxis= list(title='State',showgrid=T,tickangle=30, tickfont=list(size=10)),
         title= "International travellers to Australia pre/post COVID-19")

p_int_spend <- plot_ly(data = int_tourism_spend,
        x= ~`Purpose of visit`,
        y= ~`3-year change,2019-22`,
        color =  ~Indicator,
        type = "bar") %>% 
  layout(yaxis=list(zeroline=FALSE, title='3-year % change, June 2019-June22'),
         xaxis= list(title='Purpose of visit',showgrid=T, tickfont=list(size=10)),
         title= "International travel to Australia, by reason, pre/post COVID-19")

```


International and domestic tourism within Australia {data-icon="fa-signal"}
=======================================================================

Row
-----------------------------------------------------------------------

### Spending has recovered slightly better across the nation.

    
```{r, echo=FALSE, message=FALSE, warning=FALSE}
p_intern
```
    
### Employment has recovered relatively well.

```{r, echo=FALSE, message=FALSE, warning=FALSE}
p_int_spend
```
   
Row
-----------------------------------------------------------------------  

### No 'Intrastate' data available for ACT.
    
```{r, echo=FALSE, message=FALSE, warning=FALSE}
p_domest_spend
```
    
### No 'Intrastate' data available for ACT. 
```{r, echo=FALSE, message=FALSE, warning=FALSE}
p_domest_vis
```

Domestic tourism within Victoria {data-icon="fa-signal"}
=====================================     

Row {data-height=500}
-------------------------------------

### Daylesford and the Macedon Ranges 'Total' change was 0%, data is not missing here. 

```{r, echo=FALSE, message=FALSE, warning=FALSE}
p_spend
```

Row {data-height=500}
-------------------------------------
   
### Interstate data missing for the four regions without 'Interstate' bars in this graph.

```{r, echo=FALSE, message=FALSE, warning=FALSE}
p_vis
```  
    
### Interstate data missing for the four regions without 'Interstate' bars on this graph. 

```{r, echo=FALSE, message=FALSE, warning=FALSE}
p_vis_nights
```

References
=======================================================================

Row
-----------------------------------------------------------------------

### References

* Victoria, B. (2021, March 31). Domestic and regional research. Retrieved from Business Victoria website: https://business.vic.gov.au/business-information/tourism-industry-resources/tourism-industry-research/domestic-and-regional-research 

* International Visitor Survey results March 2022 | Tourism Research Australia. (2022). Retrieved from www.tra.gov.au website: https://www.tra.gov.au/data-and-research/reports/international-visitor-survey-results/international-visitor-survey-results