andrewbtran

dd_adjusted <- pc_adjust(dd, "State")

head(dd_adjusted)

andrewbtran

dd <- read.csv("https://docs.google.com/spreadsheets/d/1TWuWZpfDUMWmMpc7aPqUQ-g1a1J0rUO8_cle_zcPyI8/pub?gid=1983903926&single=true&output=csv", stringsAsFactors=F)

head(dd)

andrewbtran

test <- pc_adjust(sb, "Abbrev")

head(test, 3)

andrewbtran

pc_adjust <- function(any_df, state_type){
  # bring in the population lookup table
  pop <- read.csv("https://docs.google.com/spreadsheets/d/16oW_uvRJCNoOnCeAkJH4fDouFokjaGUdGFUCaFdKd6I/pub?output=csv", stringsAsFactors=F)
  # State type options are either "Abbrev" or "State"
colnames(any_df)[1] <- state_type
df_adjusted <- left_join(any_df, pop, by=state_type)
df_adjusted$per_capita <- df_adjusted[,2] / df_adjusted$Population * 1000000
return(df_adjusted)
}

andrewbtran

# Save the dataframe as a consistent name
any_df <- sb

# Rename the first column to "Abbrev"
colnames(any_df)[1] <- "Abbrev"

# Join by the similar name
df_adjusted <- left_join(any_df, pop, by="Abbrev")

# Do the calculations based on the values in the second column

andrewbtran

sb_adjusted$per_capita <- sb_adjusted$Starbucks/sb_adjusted$Population*100000

andrewbtran

library(dplyr)
sb_adjusted <- left_join(sb, pop, by=c("State_Abbreviation"="Abbrev"))

andrewbtran

pop <- read.csv("https://docs.google.com/spreadsheets/d/16oW_uvRJCNoOnCeAkJH4fDouFokjaGUdGFUCaFdKd6I/pub?output=csv", stringsAsFactors=F)

View(pop)

andrewbtran

sb <- read.csv("https://docs.google.com/spreadsheets/d/1gH6eUQVQsEmFagy0qzQDEuwb3cutMWaddCLc7ESbzjc/pub?gid=294374511&single=true&output=csv", stringsAsFactors=F)

View(sb)

andrewbtran

# set wd() to folder with Excel files
# install.packages("readxl")
library(readxl)

excel_list <- list.files()

for (i in 1:length(excel_list)) {
  e_file <- excel_list[i]
  if (i == 1) {
  e_file_combined <- read_excel(e_file, sheet=1)