Cinemas in Strasbourg

# Load packages
library(tidyverse)
library(readxl)
library(nominatim)
library(leaflet)

# Define functions

## Load sheets from Excel file
load_sheet <- function(sheet_name, i){
  path <- "/home/david/Documents/GitHub/chodera.github.io/assets/projects/6. Cinemas in Strasbourg/"
  sheet_name <- read_excel(str_c(path, "exploitation - données par commune.xlsx"), sheet = sheet_name, skip = i)
}

## Tidy sheets
clean_sheet <- function(sheet){
  # Get name of data frame
  df_name <- deparse(substitute(sheet))

  sheet <- sheet %>%
    # Rename columns
    rename(code = "...1",
           city = "...2") %>%
    # Filter for Strasbourg
    filter(city == "Strasbourg") %>%
    gather(c("1960":"2017"), key = "year", value = "col") %>%
    select(-c(code, city)) %>%
    mutate(year = as.numeric(year))

  # Set correct column name
  names(sheet)[names(sheet) == "col"] <- df_name

  # Assign changed data frame to original data frame
  assign(df_name, sheet, envir = .GlobalEnv)
}

## Set correct column names
set_col <- function(list, names){
  for (i in 1:length(list)){
    names(list[[i]])[[2]] <- sheet_names[[i]]
  }
  return(list)
}

# Load data
number_of_cinemas <- load_sheet("établissements", 6)
screens <- load_sheet("écrans", 6)
seats <- load_sheet("fauteuils", 6)
multiplex <- load_sheet("multiplexes", 6)
showing <- load_sheet("séances", 6)
entries <- load_sheet("entrées", 6)
revenue <- load_sheet("recettes", 6)
avg_rev_per_entry <- load_sheet("RME", 6)
frequentation_index <- load_sheet("indice de fréquentation", 8)
occupancy_rate <- load_sheet("taux d'occupation des fauteuils", 6)

# Assign sheet_names to a vector
sheet_names <- c("number_of_cinemas",
                 "screens",
                 "seats",
                 "multiplex",
                 "showing",
                 "entries",
                 "revenue",
                 "avg_rev_per_entry",
                 "frequentation_index",
                 "occupancy_rate")

# Assign data frames to a list
data <- list(number_of_cinemas,
             screens,
             seats,
             multiplex,
             showing,
             entries,
             revenue,
             avg_rev_per_entry,
             frequentation_index,
             occupancy_rate)
names(data) <- sheet_names

# Clean data
clean_data <- map(data, clean_sheet) %>%
  set_col(sheet_names)

# Join data frames
cinemas_stras <- left_join(clean_data$number_of_cinemas, clean_data$screens, by = "year") %>%
  left_join(., clean_data$seats, by = "year") %>%
  left_join(., clean_data$multiplex, by = "year") %>%
  left_join(., clean_data$showing, by = "year") %>%
  left_join(., clean_data$entries, by = "year") %>%
  left_join(., clean_data$revenue, by = "year") %>%
  left_join(., clean_data$avg_rev_per_entry, by = "year") %>%
  left_join(., clean_data$frequentation_index, by = "year") %>%
  left_join(., clean_data$occupancy_rate, by = "year")

# Remove separate data frames
rm(list = c(sheet_names, "clean_data", "data"))