3.1 Load Employment Datasets

file_path <- "../data/cpi/SAINC/"

EMP_DATA_FILE <- paste0(file_path,"EMP9818.csv")
EMP_DATA <- read_delim(EMP_DATA_FILE, delim = ",", col_names = TRUE, trim_ws= TRUE) 
#,skip = 6,col_types = list(col_character(),col_character(),col_character(),col_character(),col_character(),col_character(),col_character(),col_character(),col_character(),col_character(),col_character(),col_character(),col_character(),col_character(),col_character(),col_character()))
# names(SAP_INV_DAT) = c("DROP","I_BILLDATE","I_ORG","I_DC","I_DOCCA","I_BILLTYPE","I_ORDER_REA","I_DIV","I_MATYPE","I_BILL_QTY","BLANK")
# SAP_INV_DAT<- SAP_INV_DAT[-which(is.na(SAP_INV_DAT$I_ORG)),] %>% .[-str_which(trimws(.$I_ORG),'SOrg.|-----'),c(-1,-11)]
# 3
# SAP_INV_DAT$I_BILLDATE <- date(parse_datetime(SAP_INV_DAT$I_BILLDATE, "%m/%d/%Y"))
# 
# head(SAP_INV_DAT)
summary(EMP_DATA)

##    GeoFIPS            GeoName              Region       TableName        
##  Length:7084        Length:7084        Min.   :1.000   Length:7084       
##  Class :character   Class :character   1st Qu.:3.000   Class :character  
##  Mode  :character   Mode  :character   Median :5.000   Mode  :character  
##                                        Mean   :4.475                     
##                                        3rd Qu.:6.000                     
##                                        Max.   :8.000                     
##                                        NA's   :122                       
##     LineCode      IndustryClassification Description            Unit          
##  Min.   :  10.0   Length:7084            Length:7084        Length:7084       
##  1st Qu.: 517.0   Class :character       Class :character   Class :character  
##  Median : 712.5   Mode  :character       Mode  :character   Mode  :character  
##  Mean   : 872.1                                                               
##  3rd Qu.:1103.0                                                               
##  Max.   :2012.0                                                               
##  NA's   :4                                                                    
##      1998               1999               2000               2001          
##  Length:7084        Length:7084        Length:7084        Length:7084       
##  Class :character   Class :character   Class :character   Class :character  
##  Mode  :character   Mode  :character   Mode  :character   Mode  :character  
##                                                                             
##                                                                             
##                                                                             
##                                                                             
##      2002               2003               2004               2005          
##  Length:7084        Length:7084        Length:7084        Length:7084       
##  Class :character   Class :character   Class :character   Class :character  
##  Mode  :character   Mode  :character   Mode  :character   Mode  :character  
##                                                                             
##                                                                             
##                                                                             
##                                                                             
##      2006               2007               2008               2009          
##  Length:7084        Length:7084        Length:7084        Length:7084       
##  Class :character   Class :character   Class :character   Class :character  
##  Mode  :character   Mode  :character   Mode  :character   Mode  :character  
##                                                                             
##                                                                             
##                                                                             
##                                                                             
##      2010               2011               2012               2013          
##  Length:7084        Length:7084        Length:7084        Length:7084       
##  Class :character   Class :character   Class :character   Class :character  
##  Mode  :character   Mode  :character   Mode  :character   Mode  :character  
##                                                                             
##                                                                             
##                                                                             
##                                                                             
##      2014               2015               2016               2017          
##  Length:7084        Length:7084        Length:7084        Length:7084       
##  Class :character   Class :character   Class :character   Class :character  
##  Mode  :character   Mode  :character   Mode  :character   Mode  :character  
##                                                                             
##                                                                             
##                                                                             
##                                                                             
##      2018          
##  Length:7084       
##  Class :character  
##  Mode  :character  
##                    
##                    
##                    
## 

DT::datatable(EMP_DATA)

# Reading the Category and detail Desc for ref.
EMP_DEF_FILE <- paste0(file_path,"SAEMP25N__definition.xml")
IND_DESC <- XML::xmlToDataFrame(EMP_DEF_FILE)
names(IND_DESC) <- c("LineCode","Description")
glimpse(IND_DESC)

## Observations: 118
## Variables: 3
## $ LineCode    <fct> 10, 20, 40, 50, 60, 70, 80, 90, 100, 101, 102, 103, 200...
## $ Description <fct> "Total employment (number of jobs)", "Wage and salary e...
## $ NA          <fct> "A count of jobs, both full-time and part-time. It incl...

head(IND_DESC,5)

3.2 Show Relevant Employment Data

# MN-MAIN  | NN - RANGE  | NL - LAST |
EMAP_CAT_LAST <- EMP_DATA[,c(2,5,6,7)] %>% 
                  filter(GeoName=="United States") %>% 
                  mutate(ISM = ifelse(IndustryClassification=="...","M","N")) %>% 
                  separate(IndustryClassification, c("From", "To"))  %>% 
                  mutate(ISML = ifelse(is.na(To)==T,"L","N")) %>% 
                  filter(ISML %in% c("L","N")) %>%  
                  select(.,LineCode,Description)
#DT::datatable(EMAP_CAT_LAST)

EMP_DAT_CAT <- EMP_DATA[,c(2,5,9:29)] %>% 
                left_join(EMAP_CAT_LAST,.,by="LineCode")
glimpse(EMP_DAT_CAT)

## Observations: 7,080
## Variables: 24
## $ LineCode    <dbl> 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10,...
## $ Description <chr> "Total employment (number of jobs)", "Total employment ...
## $ GeoName     <chr> "United States", "Alabama", "Alaska", "Arizona", "Arkan...
## $ `1998`      <chr> "158481200", "2361892", "382166", "2616288", "1445536",...
## $ `1999`      <chr> "161531300", "2378217", "381307", "2695892", "1460374",...
## $ `2000`      <chr> "165370800", "2392225", "389734", "2801510", "1482449",...
## $ `2001`      <chr> "165522200", "2376053", "394565", "2829002", "1482587",...
## $ `2002`      <chr> "165095100", "2364828", "402187", "2847095", "1478929",...
## $ `2003`      <chr> "165921500", "2371430", "405621", "2917121", "1482035",...
## $ `2004`      <chr> "168839700", "2425649", "413864", "3041476", "1505095",...
## $ `2005`      <chr> "172338400", "2486833", "421419", "3219820", "1537680",...
## $ `2006`      <chr> "175868600", "2545556", "431320", "3375218", "1567682",...
## $ `2007`      <chr> "179543700", "2604078", "439825", "3465075", "1582858",...
## $ `2008`      <chr> "179213900", "2582591", "443538", "3402808", "1579283",...
## $ `2009`      <chr> "173636700", "2479507", "442447", "3228493", "1542944",...
## $ `2010`      <chr> "172901700", "2460298", "443904", "3181571", "1541272",...
## $ `2011`      <chr> "176091700", "2497933", "450364", "3239045", "1561948",...
## $ `2012`      <chr> "178979700", "2503678", "459222", "3295537", "1565142",...
## $ `2013`      <chr> "182325100", "2523338", "461110", "3371219", "1569249",...
## $ `2014`      <chr> "186233800", "2551872", "461327", "3448173", "1587414",...
## $ `2015`      <chr> "190315800", "2586885", "461767", "3548174", "1610779",...
## $ `2016`      <chr> "193371900", "2619154", "457371", "3646604", "1629237",...
## $ `2017`      <chr> "196825300", "2653968", "456799", "3751283", "1644432",...
## $ `2018`      <chr> "200746000", "2691517", "459178", "3859137", "1663188",...

head(EMP_DAT_CAT,130)

#DT::datatable(EMP_DAT_CAT)

temp_name <- names(EMP_DAT_CAT [,c(4:24)])
EMP_DAT_CAT_SAN <-  data.frame(lapply(EMP_DAT_CAT [,c(4:24)],function(x) {
                      gsub("(D)|(T)",NA,x)
                    })) 
names(EMP_DAT_CAT_SAN) = temp_name
#DT::datatable(EMP_DAT_CAT_SAN)

EMP_DAT_CAT_SAN <- cbind(EMP_DAT_CAT[,c(1:3)],EMP_DAT_CAT_SAN)
#DT::datatable(EMP_DAT_CAT_SAN)

EMP_DAT_CAT_SAN_AK <- EMP_DAT_CAT_SAN %>% 
                        filter(LineCode %in% c(705,1100,800,1600,1500)) %>% 
                        filter(GeoName=="Alaska") %>% 
                        reshape2::melt(
                          id.vars = c("LineCode","Description","GeoName"), 
                          variable.name = "Year", 
                          value.name = "Number of jobs")%>% 
                        rename(No_Jobs = "Number of jobs")

EMP_DAT_CAT_SAN_AK %>%
  #sample_frac(0.33) %>% 
  DT::datatable()

EMP_DAT_CAT_SAN_AZ <- EMP_DAT_CAT_SAN %>% 
                        filter(LineCode %in% c(705,1100,800,1600,1500)) %>% 
                        filter(GeoName=="Arizona") %>% 
                        reshape2::melt(
                          id.vars = c("LineCode","Description","GeoName"), 
                          variable.name = "Year", 
                          value.name = "Number of jobs")%>% 
                        rename(No_Jobs = "Number of jobs")

EMP_DAT_CAT_SAN_AZ %>%
  #sample_frac(0.33) %>% 
  DT::datatable()

EMP_DAT_CAT_SAN_CA <- EMP_DAT_CAT_SAN %>% 
                        filter(LineCode %in% c(705,1100,800,1600,1500)) %>% 
                        filter(GeoName=="California") %>% 
                        reshape2::melt(
                          id.vars = c("LineCode","Description","GeoName"), 
                          variable.name = "Year", 
                          value.name = "Number of jobs")%>% 
                        rename(No_Jobs = "Number of jobs")

EMP_DAT_CAT_SAN_CA %>%
  #sample_frac(0.33) %>% 
  DT::datatable()

EMP_DAT_CAT_SAN_CO <- EMP_DAT_CAT_SAN %>% 
                        filter(LineCode %in% c(705,1100,800,1600,1500)) %>% 
                        filter(GeoName=="Colorado") %>% 
                        reshape2::melt(
                          id.vars = c("LineCode","Description","GeoName"), 
                          variable.name = "Year", 
                          value.name = "Number of jobs")%>% 
                        rename(No_Jobs = "Number of jobs")

EMP_DAT_CAT_SAN_CO %>%
  #sample_frac(0.33) %>% 
  DT::datatable()

EMP_DAT_CAT_SAN_FL <- EMP_DAT_CAT_SAN %>% 
                        filter(LineCode %in% c(705,1100,800,1600,1500)) %>% 
                        filter(GeoName=="Florida") %>% 
                        reshape2::melt(
                          id.vars = c("LineCode","Description","GeoName"), 
                          variable.name = "Year", 
                          value.name = "Number of jobs")%>% 
                        rename(No_Jobs = "Number of jobs")

EMP_DAT_CAT_SAN_FL %>%
  #sample_frac(0.33) %>% 
  DT::datatable()

EMP_DAT_CAT_SAN_GA <- EMP_DAT_CAT_SAN %>% 
                        filter(LineCode %in% c(705,1100,800,1600,1500)) %>% 
                        filter(GeoName=="Georgia") %>% 
                        reshape2::melt(
                          id.vars = c("LineCode","Description","GeoName"), 
                          variable.name = "Year", 
                          value.name = "Number of jobs")%>% 
                        rename(No_Jobs = "Number of jobs")

EMP_DAT_CAT_SAN_GA %>%
  #sample_frac(0.33) %>% 
  DT::datatable()

EMP_DAT_CAT_SAN_HI <- EMP_DAT_CAT_SAN %>% 
                        filter(LineCode %in% c(705,1100,800,1600,1500)) %>% 
                        filter(GeoName=="Hawaii") %>% 
                        reshape2::melt(
                          id.vars = c("LineCode","Description","GeoName"), 
                          variable.name = "Year", 
                          value.name = "Number of jobs")%>% 
                        rename(No_Jobs = "Number of jobs")

EMP_DAT_CAT_SAN_HI %>%
  #sample_frac(0.33) %>% 
  DT::datatable()

EMP_DAT_CAT_SAN_IL <- EMP_DAT_CAT_SAN %>% 
                        filter(LineCode %in% c(705,1100,800,1600,1500)) %>% 
                        filter(GeoName=="Illinois") %>% 
                        reshape2::melt(
                          id.vars = c("LineCode","Description","GeoName"), 
                          variable.name = "Year", 
                          value.name = "Number of jobs")%>% 
                        rename(No_Jobs = "Number of jobs")

EMP_DAT_CAT_SAN_IL %>%
  #sample_frac(0.33) %>% 
  DT::datatable()

EMP_DAT_CAT_SAN_KS <- EMP_DAT_CAT_SAN %>% 
                        filter(LineCode %in% c(705,1100,800,1600,1500)) %>% 
                        filter(GeoName=="Kansas") %>% 
                        reshape2::melt(
                          id.vars = c("LineCode","Description","GeoName"), 
                          variable.name = "Year", 
                          value.name = "Number of jobs")%>% 
                        rename(No_Jobs = "Number of jobs")

EMP_DAT_CAT_SAN_KS %>%
  #sample_frac(0.33) %>% 
  DT::datatable()

EMP_DAT_CAT_SAN_MA <- EMP_DAT_CAT_SAN %>% 
                        filter(LineCode %in% c(705,1100,800,1600,1500)) %>% 
                        filter(GeoName=="Massachusetts") %>% 
                        reshape2::melt(
                          id.vars = c("LineCode","Description","GeoName"), 
                          variable.name = "Year", 
                          value.name = "Number of jobs")%>% 
                        rename(No_Jobs = "Number of jobs")

EMP_DAT_CAT_SAN_MA %>%
  #sample_frac(0.33) %>% 
  DT::datatable()

EMP_DAT_CAT_SAN_MI <- EMP_DAT_CAT_SAN %>% 
                        filter(LineCode %in% c(705,1100,800,1600,1500)) %>% 
                        filter(GeoName=="Michigan") %>% 
                        reshape2::melt(
                          id.vars = c("LineCode","Description","GeoName"), 
                          variable.name = "Year", 
                          value.name = "Number of jobs") %>% 
                        rename(No_Jobs = "Number of jobs")

EMP_DAT_CAT_SAN_MI %>%
  #sample_frac(0.33) %>% 
  DT::datatable()

EMP_DAT_CAT_SAN_MN <- EMP_DAT_CAT_SAN %>% 
                        filter(LineCode %in% c(705,1100,800,1600,1500)) %>% 
                        filter(GeoName=="Michigan") %>% 
                        reshape2::melt(
                          id.vars = c("LineCode","Description","GeoName"), 
                          variable.name = "Year", 
                          value.name = "Number of jobs") %>% 
                        rename(No_Jobs = "Number of jobs")

EMP_DAT_CAT_SAN_MN %>%
  #sample_frac(0.33) %>% 
  DT::datatable()

EMP_DAT_CAT_SAN_MO <- EMP_DAT_CAT_SAN %>% 
                        filter(LineCode %in% c(705,1100,800,1600,1500)) %>% 
                        filter(GeoName=="Missouri") %>% 
                        reshape2::melt(
                          id.vars = c("LineCode","Description","GeoName"), 
                          variable.name = "Year", 
                          value.name = "Number of jobs") %>% 
                        rename(No_Jobs = "Number of jobs")

EMP_DAT_CAT_SAN_MO %>%
  #sample_frac(0.33) %>% 
  DT::datatable()

EMP_DAT_CAT_SAN_NY <- EMP_DAT_CAT_SAN %>% 
                        filter(LineCode %in% c(705,1100,800,1600,1500)) %>% 
                        filter(GeoName=="New York") %>% 
                        reshape2::melt(
                          id.vars = c("LineCode","Description","GeoName"), 
                          variable.name = "Year", 
                          value.name = "Number of jobs") %>% 
                        rename(No_Jobs = "Number of jobs")

EMP_DAT_CAT_SAN_NY %>%
  #sample_frac(0.33) %>% 
  DT::datatable()

EMP_DAT_CAT_SAN_OH <- EMP_DAT_CAT_SAN %>% 
                        filter(LineCode %in% c(705,1100,800,1600,1500)) %>% 
                        filter(GeoName=="Ohio") %>% 
                        reshape2::melt(
                          id.vars = c("LineCode","Description","GeoName"), 
                          variable.name = "Year", 
                          value.name = "Number of jobs")%>% 
                        rename(No_Jobs = "Number of jobs")

EMP_DAT_CAT_SAN_OH %>%
  #sample_frac(0.33) %>% 
  DT::datatable()

EMP_DAT_CAT_SAN_OR <- EMP_DAT_CAT_SAN %>% 
                        filter(LineCode %in% c(705,1100,800,1600,1500)) %>% 
                        filter(GeoName=="Oregon") %>% 
                        reshape2::melt(
                          id.vars = c("LineCode","Description","GeoName"), 
                          variable.name = "Year", 
                          value.name = "Number of jobs")%>% 
                        rename(No_Jobs = "Number of jobs")

EMP_DAT_CAT_SAN_OR %>%
  #sample_frac(0.33) %>% 
  DT::datatable()

EMP_DAT_CAT_SAN_PA <- EMP_DAT_CAT_SAN %>% 
                        filter(LineCode %in% c(705,1100,800,1600,1500)) %>% 
                        filter(GeoName=="Pennsylvania") %>% 
                        reshape2::melt(
                          id.vars = c("LineCode","Description","GeoName"), 
                          variable.name = "Year", 
                          value.name = "Number of jobs")%>% 
                        rename(No_Jobs = "Number of jobs")

EMP_DAT_CAT_SAN_PA %>%
  #sample_frac(0.33) %>% 
  DT::datatable()

EMP_DAT_CAT_SAN_TX <- EMP_DAT_CAT_SAN %>% 
                        filter(LineCode %in% c(705,1100,800,1600,1500)) %>% 
                        filter(GeoName=="Texas") %>% 
                        reshape2::melt(
                          id.vars = c("LineCode","Description","GeoName"), 
                          variable.name = "Year", 
                          value.name = "Number of jobs")%>% 
                        rename(No_Jobs = "Number of jobs")

EMP_DAT_CAT_SAN_TX %>%
  #sample_frac(0.33) %>% 
  DT::datatable()

EMP_DAT_CAT_SAN_WA <- EMP_DAT_CAT_SAN %>% 
                        filter(LineCode %in% c(705,1100,800,1600,1500)) %>% 
                        filter(GeoName=="Washington") %>% 
                        reshape2::melt(
                          id.vars = c("LineCode","Description","GeoName"), 
                          variable.name = "Year", 
                          value.name = "Number of jobs")%>% 
                        rename(No_Jobs = "Number of jobs")

EMP_DAT_CAT_SAN_WA %>%
  #sample_frac(0.33) %>% 
  DT::datatable()

EMP_DAT_CAT_SAN_WI <- EMP_DAT_CAT_SAN %>% 
                        filter(LineCode %in% c(705,1100,800,1600,1500)) %>% 
                        filter(GeoName=="Wisconsin") %>% 
                        reshape2::melt(
                          id.vars = c("LineCode","Description","GeoName"), 
                          variable.name = "Year", 
                          value.name = "Number of jobs") %>% 
                        rename(No_Jobs = "Number of jobs")

EMP_DAT_CAT_SAN_WI %>%
  #sample_frac(0.33) %>% 
  DT::datatable()

3.3 Load CPI Data

file_path <- "../data/cpi/archive/"

### Read files named *.csv
filenames <- list.files(path=file_path,
                        pattern="*.csv",
                        full.names = TRUE)
###print(filenames)

###Create list of data frame names without the ".csv" part 
names <- gsub(pattern='\\.', 
              replacement='_', # remove the last 4 characters from filename (_clean)
              x=(sub(pattern = "(.*)\\..*$", replacement = "\\1", # remove extenson from filename (.csv)
                    basename(filenames) # get filename without the directory file path
                   )
                 )
              )
###print(names)

###Load all files
for(i in filenames){
  ## Create list of data frame names without the ".csv" part 
  name <- gsub(pattern='\\.', 
               replacement='_', # remove the last 4 characters from filename (_clean)
               x=(sub(pattern = "(.*)\\..*$", replacement = "\\1", # remove extenson from filename (.csv)
                      basename(i) # get filename without the directory file path
                     )
                 )
              )
   print(name)
   assign(x=name, value=data.frame(read.csv(i)))
   #DT::datatable(name)
}

## [1] "cu_area"
## [1] "cu_base"
## [1] "cu_data_0_Current"
## [1] "cu_data_1_AllItems"
## [1] "cu_data_10_OtherWest"
## [1] "cu_data_11_USFoodBeverage"
## [1] "cu_data_12_USHousing"
## [1] "cu_data_13_USApparel"
## [1] "cu_data_14_USTransportation"
## [1] "cu_data_15_USMedical"
## [1] "cu_data_16_USRecreation"
## [1] "cu_data_17_USEducationAndCommunication"
## [1] "cu_data_18_USOtherGoodsAndServices"
## [1] "cu_data_19_PopulationSize"
## [1] "cu_data_2_Summaries"
## [1] "cu_data_20_USCommoditiesServicesSpecial"
## [1] "cu_data_3_AsizeNorthEast"
## [1] "cu_data_4_AsizeNorthCentral"
## [1] "cu_data_5_AsizeSouth"
## [1] "cu_data_6_AsizeWest"
## [1] "cu_data_7_OtherNorthEast"
## [1] "cu_data_8_OtherNorthCentral"
## [1] "cu_data_9_OtherSouth"
## [1] "cu_footnote"
## [1] "cu_item"
## [1] "cu_period"
## [1] "cu_periodicity"
## [1] "cu_series"

3.4 Show CPI Reference Data

3.5 Show CPI Data from Select Industry

FoodItemCode <- cu_reference_data %>%
                    filter(grepl('SAF|SEF|SS0|SS1|SS20', item_code))
                    #dplyr::filter(item_code %like% 'SAH' | item_code %like% 'SEH')
#DT::datatable((FoodItemCode))

cu_data_USFoodBeverage = cu_data_0_Current %>% 
                              inner_join(FoodItemCode, by="series_id") %>% 
                              inner_join(cu_period, by="period") %>% 
                               filter(year %in% (1998:2018)) %>% 
                               select(c("series_id",
                                        "series_title",
                                        "year",
                                        "period",
                                        "period_abbr",
                                        "period_name",
                                        "value",
                                        "area_code",
                                        "area_name",
                                        "area_display_level",
                                        "area_selectable",
                                        "area_sort_sequence",
                                        "item_code",
                                        "item_name",
                                        "item_display_level",
                                        "item_selectable",
                                        "item_sort_sequence",
                                        "seasonal",
                                        "periodicity_code",
                                        "periodicity_name",
                                        "base_code",
                                        "base_name",
                                        "base_period"))
DT::datatable(head(cu_data_USFoodBeverage))

HousingItemCode <- cu_reference_data %>%
                    #filter(grepl('SAH|SEH', item_code, fixed = TRUE))
                    filter(grepl('SAH|SEH', item_code))
                    #dplyr::filter(item_code %like% 'SAH' | item_code %like% 'SEH')
#DT::datatable((HousingItemCode))

cu_data_USHousing = cu_data_0_Current %>% 
                              inner_join(HousingItemCode, by="series_id") %>% 
                              inner_join(cu_period, by="period") %>% 
                               #filter(item_code %like% c('SAH','SEH')) %>%
                               filter(year %in% (1998:2018)) %>% 
                               select(c("series_id",
                                        "series_title",
                                        "year",
                                        "period",
                                        "period_abbr",
                                        "period_name",
                                        "value",
                                        "area_code",
                                        "area_name",
                                        "area_display_level",
                                        "area_selectable",
                                        "area_sort_sequence",
                                        "item_code",
                                        "item_name",
                                        "item_display_level",
                                        "item_selectable",
                                        "item_sort_sequence",
                                        "seasonal",
                                        "periodicity_code",
                                        "periodicity_name",
                                        "base_code",
                                        "base_name",
                                        "base_period"))
DT::datatable(head(cu_data_USHousing))

TransportItemCode <- cu_reference_data %>%
                    filter(grepl('SAT|SET|SS4|SS52|SS53|SAS24|SAS4', item_code))

cu_data_USTransportation = cu_data_0_Current %>% 
                              inner_join(TransportItemCode, by="series_id") %>% 
                              inner_join(cu_period, by="period") %>% 
                               filter(year %in% (1998:2018)) %>% 
                               select(c("series_id",
                                        "series_title",
                                        "year",
                                        "period",
                                        "period_abbr",
                                        "period_name",
                                        "value",
                                        "area_code",
                                        "area_name",
                                        "area_display_level",
                                        "area_selectable",
                                        "area_sort_sequence",
                                        "item_code",
                                        "item_name",
                                        "item_display_level",
                                        "item_selectable",
                                        "item_sort_sequence",
                                        "seasonal",
                                        "periodicity_code",
                                        "periodicity_name",
                                        "base_code",
                                        "base_name",
                                        "base_period"))
DT::datatable(head(cu_data_USTransportation))

MedicalItemCode <- cu_reference_data %>%
                    filter(grepl('SAM|SEM|SS57', item_code))

cu_data_USMedical = cu_data_0_Current %>% 
                              inner_join(MedicalItemCode, by="series_id") %>% 
                              inner_join(cu_period, by="period") %>% 
                               filter(year %in% (1998:2018)) %>% 
                               select(c("series_id",
                                        "series_title",
                                        "year",
                                        "period",
                                        "period_abbr",
                                        "period_name",
                                        "value",
                                        "area_code",
                                        "area_name",
                                        "area_display_level",
                                        "area_selectable",
                                        "area_sort_sequence",
                                        "item_code",
                                        "item_name",
                                        "item_display_level",
                                        "item_selectable",
                                        "item_sort_sequence",
                                        "seasonal",
                                        "periodicity_code",
                                        "periodicity_name",
                                        "base_code",
                                        "base_name",
                                        "base_period"))
DT::datatable(head(cu_data_USMedical))

EducationItemCode <- cu_reference_data %>%
                    filter(grepl('SAE|SEE|SSE|SS27', item_code))

cu_data_USEducationAndCommunication = cu_data_0_Current %>% 
                                        inner_join(EducationItemCode, by="series_id") %>% 
                                        inner_join(cu_period, by="period") %>% 
                                         filter(year %in% (1998:2018)) %>% 
                                         select(c("series_id",
                                                  "series_title",
                                                  "year",
                                                  "period",
                                                  "period_abbr",
                                                  "period_name",
                                                  "value",
                                                  "area_code",
                                                  "area_name",
                                                  "area_display_level",
                                                  "area_selectable",
                                                  "area_sort_sequence",
                                                  "item_code",
                                                  "item_name",
                                                  "item_display_level",
                                                  "item_selectable",
                                                  "item_sort_sequence",
                                                  "seasonal",
                                                  "periodicity_code",
                                                  "periodicity_name",
                                                  "base_code",
                                                  "base_name",
                                                  "base_period"))
DT::datatable(head(cu_data_USEducationAndCommunication))

4.1 Food & Beverages

cu_emp_data_USFoodBeverage_AK = cu_data_USFoodBeverage %>% 
                                  filter(area_name=="Anchorage", 
                                         period %in% c("S01","S02")) %>% 
                                  mutate(parent_item_code = 'SAF', 
                                         emp_line_code = 705,
                                         industry = 'Food & Beverages',
                                         state = 'AK') %>% 
                                  select(c("industry",
                                            "state",
                                            "parent_item_code",
                                            "emp_line_code",
                                            "series_id",
                                            "series_title",
                                            "year",
                                            "period",
                                            "period_abbr",
                                            "period_name",
                                            "value",
                                            "area_code",
                                            "area_name",
                                            "area_display_level",
                                            "area_selectable",
                                            "area_sort_sequence",
                                            "item_code",
                                            "item_name",
                                            "item_display_level",
                                            "item_selectable",
                                            "item_sort_sequence",
                                            "seasonal",
                                            "periodicity_code",
                                            "periodicity_name",
                                            "base_code",
                                            "base_name",
                                            "base_period")) %>% 
                                  group_by(industry, state, parent_item_code, emp_line_code, year, area_code, area_name) %>% 
                                  summarise(cpi_value = round(sum(value),2))

cpi_emp_data_USFoodBeverage_AK = merge(EMP_DAT_CAT_SAN_AK, cu_emp_data_USFoodBeverage_AK, 
                                       by.x=c("LineCode","Year"), by.y=c("emp_line_code","year"))

cpi_emp_data_USFoodBeverage_AK %>%
  ###sample_frac(0.33) %>% 
  DT::datatable()

cu_emp_data_USFoodBeverage_AZ = cu_data_USFoodBeverage %>% 
                                  filter(area_name=="Phoenix-Mesa", 
                                         period %in% c("S01","S02")) %>% 
                                  mutate(parent_item_code = 'SAF', 
                                         emp_line_code = 705,
                                         industry = 'Food & Beverages',
                                         state = 'AZ') %>% 
                                  select(c("industry",
                                            "state",
                                            "parent_item_code",
                                            "emp_line_code",
                                            "series_id",
                                            "series_title",
                                            "year",
                                            "period",
                                            "period_abbr",
                                            "period_name",
                                            "value",
                                            "area_code",
                                            "area_name",
                                            "area_display_level",
                                            "area_selectable",
                                            "area_sort_sequence",
                                            "item_code",
                                            "item_name",
                                            "item_display_level",
                                            "item_selectable",
                                            "item_sort_sequence",
                                            "seasonal",
                                            "periodicity_code",
                                            "periodicity_name",
                                            "base_code",
                                            "base_name",
                                            "base_period")) %>% 
                                  group_by(industry, state, parent_item_code, emp_line_code, year, area_code, area_name) %>% 
                                  summarise(cpi_value = round(sum(value),2))

cpi_emp_data_USFoodBeverage_AZ = merge(EMP_DAT_CAT_SAN_AZ, cu_emp_data_USFoodBeverage_AZ, 
                                       by.x=c("LineCode","Year"), by.y=c("emp_line_code","year"))

cpi_emp_data_USFoodBeverage_AZ %>%
  ###sample_frac(0.33) %>% 
  DT::datatable()

cu_emp_data_USFoodBeverage_CA = cu_data_USFoodBeverage %>% 
                                   filter( (area_name %in% c("Los Angeles-Riverside-Orange County", "San Francisco-Oakland-San Jose")
                                            & period %in% c("M01","M02","M03","M04","M05","M06","M07","M08","M09","M10","M11","M12")
                                            )
                                           |
                                            (area_name %in% c("San Diego")
                                             & period %in% c("S01","S02")
                                             )
                                         ) %>% 
                                  mutate(parent_item_code = 'SAF', 
                                         emp_line_code = 705,
                                         area_code = 'A421;A422;A424',
                                         area_name = 'Los Angeles-Riverside-Orange County;San Francisco-Oakland-San Jose;San Diego',
                                         industry = 'Food & Beverages',
                                         state = 'CA') %>% 
                                  select(c("industry",
                                            "state",
                                            "parent_item_code",
                                            "emp_line_code",
                                            "series_id",
                                            "series_title",
                                            "year",
                                            "period",
                                            "period_abbr",
                                            "period_name",
                                            "value",
                                            "area_code",
                                            "area_name",
                                            "area_display_level",
                                            "area_selectable",
                                            "area_sort_sequence",
                                            "item_code",
                                            "item_name",
                                            "item_display_level",
                                            "item_selectable",
                                            "item_sort_sequence",
                                            "seasonal",
                                            "periodicity_code",
                                            "periodicity_name",
                                            "base_code",
                                            "base_name",
                                            "base_period")) %>% 
                                  group_by(industry, state, parent_item_code, emp_line_code, year, area_code, area_name) %>% 
                                  summarise(cpi_value = round(sum(value/3),2))

cpi_emp_data_USFoodBeverage_CA = merge(EMP_DAT_CAT_SAN_CA, cu_emp_data_USFoodBeverage_CA, 
                                       by.x=c("LineCode","Year"), by.y=c("emp_line_code","year"))

cpi_emp_data_USFoodBeverage_CA %>%
  ###sample_frac(0.33) %>% 
  DT::datatable()

cu_emp_data_USFoodBeverage_CO = cu_data_USFoodBeverage %>% 
                                  filter(area_name %in% c("Denver-Boulder-Greeley"), 
                                         period %in% c("S01","S02")) %>% 
                                  mutate(parent_item_code = 'SAF', 
                                         emp_line_code = 705,
                                         industry = 'Food & Beverages',
                                         state = 'CO') %>% 
                                  select(c("industry",
                                            "state",
                                            "parent_item_code",
                                            "emp_line_code",
                                            "series_id",
                                            "series_title",
                                            "year",
                                            "period",
                                            "period_abbr",
                                            "period_name",
                                            "value",
                                            "area_code",
                                            "area_name",
                                            "area_display_level",
                                            "area_selectable",
                                            "area_sort_sequence",
                                            "item_code",
                                            "item_name",
                                            "item_display_level",
                                            "item_selectable",
                                            "item_sort_sequence",
                                            "seasonal",
                                            "periodicity_code",
                                            "periodicity_name",
                                            "base_code",
                                            "base_name",
                                            "base_period")) %>% 
                                  group_by(industry, state, parent_item_code, emp_line_code, year, area_code, area_name) %>% 
                                  summarise(cpi_value = round(sum(value),2))

cpi_emp_data_USFoodBeverage_CO = merge(EMP_DAT_CAT_SAN_CO, cu_emp_data_USFoodBeverage_CO, 
                                       by.x=c("LineCode","Year"), by.y=c("emp_line_code","year"))

cpi_emp_data_USFoodBeverage_CO %>%
  ###sample_frac(0.33) %>% 
  DT::datatable()

cu_emp_data_USFoodBeverage_FL = cu_data_USFoodBeverage %>% 
                                  filter( (area_name %in% c("Miami-Fort Lauderdale")
                                          & period %in% c("M01","M02","M03","M04","M05","M06","M07","M08","M09","M10","M11","M12")
                                          )
                                         |
                                          (area_name %in% c("Tampa-St. Petersburg-Clearwater")
                                           & period %in% c("S01","S02")
                                           )
                                       ) %>% 
                                  mutate(parent_item_code = 'SAF', 
                                         emp_line_code = 705,
                                         area_code = 'A320;A321',
                                         area_name = 'Miami-Fort Lauderdale;Tampa-St. Petersburg-Clearwater',
                                         industry = 'Food & Beverages',
                                         state = 'FL') %>% 
                                  select(c("industry",
                                            "state",
                                            "parent_item_code",
                                            "emp_line_code",
                                            "series_id",
                                            "series_title",
                                            "year",
                                            "period",
                                            "period_abbr",
                                            "period_name",
                                            "value",
                                            "area_code",
                                            "area_name",
                                            "area_display_level",
                                            "area_selectable",
                                            "area_sort_sequence",
                                            "item_code",
                                            "item_name",
                                            "item_display_level",
                                            "item_selectable",
                                            "item_sort_sequence",
                                            "seasonal",
                                            "periodicity_code",
                                            "periodicity_name",
                                            "base_code",
                                            "base_name",
                                            "base_period")) %>% 
                                  group_by(industry, state, parent_item_code, emp_line_code, year, area_code, area_name) %>% 
                                  summarise(cpi_value = round(sum(value)/2,2))

cpi_emp_data_USFoodBeverage_FL = merge(EMP_DAT_CAT_SAN_FL, cu_emp_data_USFoodBeverage_FL, 
                                       by.x=c("LineCode","Year"), by.y=c("emp_line_code","year"))

cpi_emp_data_USFoodBeverage_FL %>%
  ###sample_frac(0.33) %>% 
  DT::datatable()

cu_emp_data_USFoodBeverage_GA = cu_data_USFoodBeverage %>% 
                                  filter(area_name=="Atlanta", 
                                         period %in% c("M01","M02","M03","M04","M05","M06","M07","M08","M09","M10","M11","M12")) %>% 
                                  mutate(industry = 'Food & Beverages',
                                         state = 'GA',
                                         parent_item_code = 'SAF', 
                                         emp_line_code = 705,
                                         industry = 'Food & Beverages',
                                         state = 'GA') %>% 
                                   select(c("industry",
                                            "state",
                                            "parent_item_code",
                                            "emp_line_code",
                                            "series_id",
                                            "series_title",
                                            "year",
                                            "period",
                                            "period_abbr",
                                            "period_name",
                                            "value",
                                            "area_code",
                                            "area_name",
                                            "area_display_level",
                                            "area_selectable",
                                            "area_sort_sequence",
                                            "item_code",
                                            "item_name",
                                            "item_display_level",
                                            "item_selectable",
                                            "item_sort_sequence",
                                            "seasonal",
                                            "periodicity_code",
                                            "periodicity_name",
                                            "base_code",
                                            "base_name",
                                            "base_period")) %>% 
                                  group_by(industry, state, parent_item_code, emp_line_code, year, area_code, area_name) %>% 
                                  summarise(cpi_value = round(sum(value),2))

cpi_emp_data_USFoodBeverage_GA = merge(EMP_DAT_CAT_SAN_GA, cu_emp_data_USFoodBeverage_GA, 
                                       by.x=c("LineCode","Year"), by.y=c("emp_line_code","year"))

cpi_emp_data_USFoodBeverage_GA %>%
  ###sample_frac(0.33) %>% 
  DT::datatable()

cu_emp_data_USFoodBeverage_HI = cu_data_USFoodBeverage %>% 
                                  filter(area_name=="Honolulu", 
                                         period %in% c("S01","S02")) %>% 
                                  mutate(industry = 'Food & Beverages',
                                         state = 'HI',
                                         parent_item_code = 'SAF', 
                                         emp_line_code = 705,
                                         industry = 'Food & Beverages',
                                         state = 'HI') %>% 
                                   select(c("industry",
                                            "state",
                                            "parent_item_code",
                                            "emp_line_code",
                                            "series_id",
                                            "series_title",
                                            "year",
                                            "period",
                                            "period_abbr",
                                            "period_name",
                                            "value",
                                            "area_code",
                                            "area_name",
                                            "area_display_level",
                                            "area_selectable",
                                            "area_sort_sequence",
                                            "item_code",
                                            "item_name",
                                            "item_display_level",
                                            "item_selectable",
                                            "item_sort_sequence",
                                            "seasonal",
                                            "periodicity_code",
                                            "periodicity_name",
                                            "base_code",
                                            "base_name",
                                            "base_period")) %>% 
                                  group_by(industry, state, parent_item_code, emp_line_code, year, area_code, area_name) %>% 
                                  summarise(cpi_value = round(sum(value),2))

cpi_emp_data_USFoodBeverage_HI = merge(EMP_DAT_CAT_SAN_HI, cu_emp_data_USFoodBeverage_HI, 
                                       by.x=c("LineCode","Year"), by.y=c("emp_line_code","year"))

cpi_emp_data_USFoodBeverage_HI %>%
  ###sample_frac(0.33) %>% 
  DT::datatable()

cu_emp_data_USFoodBeverage_IL = cu_data_USFoodBeverage %>% 
                                  filter(area_name=="Chicago-Gary-Kenosha", 
                                         period %in% c("M01","M02","M03","M04","M05","M06","M07","M08","M09","M10","M11","M12")) %>% 
                                  mutate(industry = 'Food & Beverages',
                                         state = 'IL',
                                         parent_item_code = 'SAF', 
                                         emp_line_code = 705,
                                         industry = 'Food & Beverages',
                                         state = 'IL') %>% 
                                   select(c("industry",
                                            "state",
                                            "parent_item_code",
                                            "emp_line_code",
                                            "series_id",
                                            "series_title",
                                            "year",
                                            "period",
                                            "period_abbr",
                                            "period_name",
                                            "value",
                                            "area_code",
                                            "area_name",
                                            "area_display_level",
                                            "area_selectable",
                                            "area_sort_sequence",
                                            "item_code",
                                            "item_name",
                                            "item_display_level",
                                            "item_selectable",
                                            "item_sort_sequence",
                                            "seasonal",
                                            "periodicity_code",
                                            "periodicity_name",
                                            "base_code",
                                            "base_name",
                                            "base_period")) %>% 
                                  group_by(industry, state, parent_item_code, emp_line_code, year, area_code, area_name) %>% 
                                  summarise(cpi_value = round(sum(value),2))

cpi_emp_data_USFoodBeverage_IL = merge(EMP_DAT_CAT_SAN_IL, cu_emp_data_USFoodBeverage_IL, 
                                       by.x=c("LineCode","Year"), by.y=c("emp_line_code","year"))

cpi_emp_data_USFoodBeverage_IL %>%
  ###sample_frac(0.33) %>% 
  DT::datatable()

cu_emp_data_USFoodBeverage_KS = cu_data_USFoodBeverage %>% 
                                  filter(area_name=="Kansas City", 
                                         period %in% c("S01","S02")) %>% 
                                  mutate(industry = 'Food & Beverages',
                                         state = 'KS',
                                         parent_item_code = 'SAF', 
                                         emp_line_code = 705,
                                         industry = 'Food & Beverages',
                                         state = 'KS') %>% 
                                   select(c("industry",
                                            "state",
                                            "parent_item_code",
                                            "emp_line_code",
                                            "series_id",
                                            "series_title",
                                            "year",
                                            "period",
                                            "period_abbr",
                                            "period_name",
                                            "value",
                                            "area_code",
                                            "area_name",
                                            "area_display_level",
                                            "area_selectable",
                                            "area_sort_sequence",
                                            "item_code",
                                            "item_name",
                                            "item_display_level",
                                            "item_selectable",
                                            "item_sort_sequence",
                                            "seasonal",
                                            "periodicity_code",
                                            "periodicity_name",
                                            "base_code",
                                            "base_name",
                                            "base_period")) %>% 
                                  group_by(industry, state, parent_item_code, emp_line_code, year, area_code, area_name) %>% 
                                  summarise(cpi_value = round(sum(value),2))

cpi_emp_data_USFoodBeverage_KS = merge(EMP_DAT_CAT_SAN_KS, cu_emp_data_USFoodBeverage_KS, 
                                       by.x=c("LineCode","Year"), by.y=c("emp_line_code","year"))

cpi_emp_data_USFoodBeverage_KS %>%
  ###sample_frac(0.33) %>% 
  DT::datatable()

cu_emp_data_USFoodBeverage_MA = cu_data_USFoodBeverage %>% 
                                  filter(area_name=="Boston-Brockton-Nashua", 
                                         period %in% c("M01","M02","M03","M04","M05","M06","M07","M08","M09","M10","M11","M12")) %>% 
                                  mutate(parent_item_code = 'SAF', 
                                         emp_line_code = 705,
                                         industry = 'Food & Beverages',
                                         state = 'MA') %>% 
                                   select(c("industry",
                                            "state",
                                            "parent_item_code",
                                            "emp_line_code",
                                            "series_id",
                                            "series_title",
                                            "year",
                                            "period",
                                            "period_abbr",
                                            "period_name",
                                            "value",
                                            "area_code",
                                            "area_name",
                                            "area_display_level",
                                            "area_selectable",
                                            "area_sort_sequence",
                                            "item_code",
                                            "item_name",
                                            "item_display_level",
                                            "item_selectable",
                                            "item_sort_sequence",
                                            "seasonal",
                                            "periodicity_code",
                                            "periodicity_name",
                                            "base_code",
                                            "base_name",
                                            "base_period")) %>% 
                                  group_by(industry, state, parent_item_code, emp_line_code, year, area_code, area_name) %>% 
                                  summarise(cpi_value = round(sum(value),2))

cpi_emp_data_USFoodBeverage_MA = merge(EMP_DAT_CAT_SAN_MA, cu_emp_data_USFoodBeverage_MA, 
                                       by.x=c("LineCode","Year"), by.y=c("emp_line_code","year"))

cpi_emp_data_USFoodBeverage_MA %>%
  ###sample_frac(0.33) %>% 
  DT::datatable()

cu_emp_data_USFoodBeverage_MI = cu_data_USFoodBeverage %>% 
                                  filter(area_name=="Detroit-Ann Arbor-Flint", 
                                         period %in% c("M01","M02","M03","M04","M05","M06","M07","M08","M09","M10","M11","M12")) %>% 
                                  mutate(parent_item_code = 'SAF', 
                                         emp_line_code = 705,
                                         industry = 'Food & Beverages',
                                         state = 'MI') %>% 
                                   select(c("industry",
                                            "state",
                                            "parent_item_code",
                                            "emp_line_code",
                                            "series_id",
                                            "series_title",
                                            "year",
                                            "period",
                                            "period_abbr",
                                            "period_name",
                                            "value",
                                            "area_code",
                                            "area_name",
                                            "area_display_level",
                                            "area_selectable",
                                            "area_sort_sequence",
                                            "item_code",
                                            "item_name",
                                            "item_display_level",
                                            "item_selectable",
                                            "item_sort_sequence",
                                            "seasonal",
                                            "periodicity_code",
                                            "periodicity_name",
                                            "base_code",
                                            "base_name",
                                            "base_period")) %>% 
                                  group_by(industry, state, parent_item_code, emp_line_code, year, area_code, area_name) %>% 
                                  summarise(cpi_value = round(sum(value),2))

cpi_emp_data_USFoodBeverage_MI = merge(EMP_DAT_CAT_SAN_MI, cu_emp_data_USFoodBeverage_MI, 
                                       by.x=c("LineCode","Year"), by.y=c("emp_line_code","year"))

cpi_emp_data_USFoodBeverage_MI %>%
  ###sample_frac(0.33) %>% 
  DT::datatable()

cu_emp_data_USFoodBeverage_MN = cu_data_USFoodBeverage %>% 
                                  filter(area_name=="Minneapolis-St. Paul", 
                                         period %in% c("S01","S02")) %>% 
                                  mutate(parent_item_code = 'SAF', 
                                         emp_line_code = 705,
                                         industry = 'Food & Beverages',
                                         state = 'MN') %>% 
                                  select(c("industry",
                                            "state",
                                            "parent_item_code",
                                            "emp_line_code",
                                            "series_id",
                                            "series_title",
                                            "year",
                                            "period",
                                            "period_abbr",
                                            "period_name",
                                            "value",
                                            "area_code",
                                            "area_name",
                                            "area_display_level",
                                            "area_selectable",
                                            "area_sort_sequence",
                                            "item_code",
                                            "item_name",
                                            "item_display_level",
                                            "item_selectable",
                                            "item_sort_sequence",
                                            "seasonal",
                                            "periodicity_code",
                                            "periodicity_name",
                                            "base_code",
                                            "base_name",
                                            "base_period")) %>% 
                                  group_by(industry, state, parent_item_code, emp_line_code, year, area_code, area_name) %>% 
                                  summarise(cpi_value = round(sum(value),2))

cpi_emp_data_USFoodBeverage_MN = merge(EMP_DAT_CAT_SAN_MN, cu_emp_data_USFoodBeverage_MN, 
                                       by.x=c("LineCode","Year"), by.y=c("emp_line_code","year"))

cpi_emp_data_USFoodBeverage_MN %>%
  ###sample_frac(0.33) %>% 
  DT::datatable()

cu_emp_data_USFoodBeverage_MO = cu_data_USFoodBeverage %>% 
                                  filter(area_name=="St. Louis", 
                                         period %in% c("S01","S02")) %>% 
                                  mutate(parent_item_code = 'SAF', 
                                         emp_line_code = 705,
                                         industry = 'Food & Beverages',
                                         state = 'MO') %>% 
                                  select(c("industry",
                                            "state",
                                            "parent_item_code",
                                            "emp_line_code",
                                            "series_id",
                                            "series_title",
                                            "year",
                                            "period",
                                            "period_abbr",
                                            "period_name",
                                            "value",
                                            "area_code",
                                            "area_name",
                                            "area_display_level",
                                            "area_selectable",
                                            "area_sort_sequence",
                                            "item_code",
                                            "item_name",
                                            "item_display_level",
                                            "item_selectable",
                                            "item_sort_sequence",
                                            "seasonal",
                                            "periodicity_code",
                                            "periodicity_name",
                                            "base_code",
                                            "base_name",
                                            "base_period")) %>% 
                                  group_by(industry, state, parent_item_code, emp_line_code, year, area_code, area_name) %>% 
                                  summarise(cpi_value = round(sum(value),2))

cpi_emp_data_USFoodBeverage_MO = merge(EMP_DAT_CAT_SAN_MO, cu_emp_data_USFoodBeverage_MO, 
                                       by.x=c("LineCode","Year"), by.y=c("emp_line_code","year"))

cpi_emp_data_USFoodBeverage_MO %>%
  ###sample_frac(0.33) %>% 
  DT::datatable()

cu_emp_data_USFoodBeverage_NY = cu_data_USFoodBeverage %>% 
                                  filter(area_name=="New York-Northern New Jersey-Long Island", 
                                         period %in% c("M01","M02","M03","M04","M05","M06","M07","M08","M09","M10","M11","M12")) %>% 
                                  mutate(parent_item_code = 'SAF', 
                                         emp_line_code = 705,
                                         industry = 'Food & Beverages',
                                         state = 'NY') %>% 
                                  select(c("industry",
                                            "state",
                                            "parent_item_code",
                                            "emp_line_code",
                                            "series_id",
                                            "series_title",
                                            "year",
                                            "period",
                                            "period_abbr",
                                            "period_name",
                                            "value",
                                            "area_code",
                                            "area_name",
                                            "area_display_level",
                                            "area_selectable",
                                            "area_sort_sequence",
                                            "item_code",
                                            "item_name",
                                            "item_display_level",
                                            "item_selectable",
                                            "item_sort_sequence",
                                            "seasonal",
                                            "periodicity_code",
                                            "periodicity_name",
                                            "base_code",
                                            "base_name",
                                            "base_period")) %>% 
                                  group_by(industry, state, parent_item_code, emp_line_code, year, area_code, area_name) %>% 
                                  summarise(cpi_value = round(sum(value),2))

cpi_emp_data_USFoodBeverage_NY = merge(EMP_DAT_CAT_SAN_NY, cu_emp_data_USFoodBeverage_NY, 
                                       by.x=c("LineCode","Year"), by.y=c("emp_line_code","year"))

cpi_emp_data_USFoodBeverage_NY %>%
  ###sample_frac(0.33) %>% 
  DT::datatable()

cu_emp_data_USFoodBeverage_OH = cu_data_USFoodBeverage %>% 
                                  filter( (area_name %in% c("Cleveland-Akron")
                                          & period %in% c("M01","M02","M03","M04","M05","M06","M07","M08","M09","M10","M11","M12")
                                          )
                                         |
                                          (area_name %in% c("Cincinnati-Hamilton")
                                           & period %in% c("S01","S02")
                                           )
                                       ) %>% 
                                  mutate(parent_item_code = 'SAF', 
                                         emp_line_code = 705,
                                         area_code = 'A210;A213', 
                                         area_name = 'Cleveland-Akron;Cincinnati-Hamilton',
                                         industry = 'Food & Beverages',
                                         state = 'OH') %>% 
                                  select(c("industry",
                                            "state",
                                            "parent_item_code",
                                            "emp_line_code",
                                            "series_id",
                                            "series_title",
                                            "year",
                                            "period",
                                            "period_abbr",
                                            "period_name",
                                            "value",
                                            "area_code",
                                            "area_name",
                                            "area_display_level",
                                            "area_selectable",
                                            "area_sort_sequence",
                                            "item_code",
                                            "item_name",
                                            "item_display_level",
                                            "item_selectable",
                                            "item_sort_sequence",
                                            "seasonal",
                                            "periodicity_code",
                                            "periodicity_name",
                                            "base_code",
                                            "base_name",
                                            "base_period")) %>% 
                                  group_by(industry, state, parent_item_code, emp_line_code, year, area_code, area_name) %>% 
                                  summarise(cpi_value = round(sum(value)/2,2))

cpi_emp_data_USFoodBeverage_OH = merge(EMP_DAT_CAT_SAN_OH, cu_emp_data_USFoodBeverage_OH, 
                                       by.x=c("LineCode","Year"), by.y=c("emp_line_code","year"))

cpi_emp_data_USFoodBeverage_OH %>%
  ###sample_frac(0.33) %>% 
  DT::datatable()

cu_emp_data_USFoodBeverage_OR = cu_data_USFoodBeverage %>% 
                                  filter(area_name=="Portland-Salem", 
                                         period %in% c("S01","S02")) %>% 
                                  mutate(parent_item_code = 'SAF', 
                                         emp_line_code = 705,
                                         industry = 'Food & Beverages',
                                         state = 'OR') %>% 
                                  select(c("industry",
                                            "state",
                                            "parent_item_code",
                                            "emp_line_code",
                                            "series_id",
                                            "series_title",
                                            "year",
                                            "period",
                                            "period_abbr",
                                            "period_name",
                                            "value",
                                            "area_code",
                                            "area_name",
                                            "area_display_level",
                                            "area_selectable",
                                            "area_sort_sequence",
                                            "item_code",
                                            "item_name",
                                            "item_display_level",
                                            "item_selectable",
                                            "item_sort_sequence",
                                            "seasonal",
                                            "periodicity_code",
                                            "periodicity_name",
                                            "base_code",
                                            "base_name",
                                            "base_period")) %>% 
                                  group_by(industry, state, parent_item_code, emp_line_code, year, area_code, area_name) %>% 
                                  summarise(cpi_value = round(sum(value),2))

cpi_emp_data_USFoodBeverage_OR = merge(EMP_DAT_CAT_SAN_OR, cu_emp_data_USFoodBeverage_OR, 
                                       by.x=c("LineCode","Year"), by.y=c("emp_line_code","year"))

cpi_emp_data_USFoodBeverage_OR %>%
  ###sample_frac(0.33) %>% 
  DT::datatable()

cu_emp_data_USFoodBeverage_PA = cu_data_USFoodBeverage %>% 
                                  filter( (area_name %in% c("Philadelphia-Wilmington-Atlantic City")
                                          & period %in% c("M01","M02","M03","M04","M05","M06","M07","M08","M09","M10","M11","M12")
                                          )
                                         |
                                          (area_name %in% c("Pittsburgh")
                                           & period %in% c("S01","S02")
                                           )
                                       ) %>% 
                                  mutate(parent_item_code = 'SAF', 
                                         emp_line_code = 705,
                                         area_code = 'A102;A104', 
                                         area_name = 'Philadelphia-Wilmington-Atlantic City;Pittsburgh',
                                         industry = 'Food & Beverages',
                                         state = 'PA') %>% 
                                  select(c("industry",
                                            "state",
                                            "parent_item_code",
                                            "emp_line_code",
                                            "series_id",
                                            "series_title",
                                            "year",
                                            "period",
                                            "period_abbr",
                                            "period_name",
                                            "value",
                                            "area_code",
                                            "area_name",
                                            "area_display_level",
                                            "area_selectable",
                                            "area_sort_sequence",
                                            "item_code",
                                            "item_name",
                                            "item_display_level",
                                            "item_selectable",
                                            "item_sort_sequence",
                                            "seasonal",
                                            "periodicity_code",
                                            "periodicity_name",
                                            "base_code",
                                            "base_name",
                                            "base_period")) %>% 
                                  group_by(industry, state, parent_item_code, emp_line_code, year, area_code, area_name) %>% 
                                  summarise(cpi_value = round(sum(value)/2,2))

cpi_emp_data_USFoodBeverage_PA = merge(EMP_DAT_CAT_SAN_PA, cu_emp_data_USFoodBeverage_PA, 
                                       by.x=c("LineCode","Year"), by.y=c("emp_line_code","year"))

cpi_emp_data_USFoodBeverage_PA %>%
  ###sample_frac(0.33) %>% 
  DT::datatable()

cu_emp_data_USFoodBeverage_TX = cu_data_USFoodBeverage %>% 
                                  filter(area_name %in% c("Dallas-Fort Worth","Houston-Galveston-Brazoria"), 
                                         period %in% c("M01","M02","M03","M04","M05","M06","M07","M08","M09","M10","M11","M12")) %>% 
                                  mutate(parent_item_code = 'SAF', 
                                         emp_line_code = 705,
                                         area_code = 'A316;A318', 
                                         area_name = 'Dallas-Fort Worth;Houston-Galveston-Brazoria',
                                         industry = 'Food & Beverages',
                                         state = 'TX') %>% 
                                  select(c("industry",
                                            "state",
                                            "parent_item_code",
                                            "emp_line_code",
                                            "series_id",
                                            "series_title",
                                            "year",
                                            "period",
                                            "period_abbr",
                                            "period_name",
                                            "value",
                                            "area_code",
                                            "area_name",
                                            "area_display_level",
                                            "area_selectable",
                                            "area_sort_sequence",
                                            "item_code",
                                            "item_name",
                                            "item_display_level",
                                            "item_selectable",
                                            "item_sort_sequence",
                                            "seasonal",
                                            "periodicity_code",
                                            "periodicity_name",
                                            "base_code",
                                            "base_name",
                                            "base_period")) %>% 
                                  group_by(industry, state, parent_item_code, emp_line_code, year, area_code, area_name) %>% 
                                  summarise(cpi_value = round(sum(value)/2,2))

cpi_emp_data_USFoodBeverage_TX = merge(EMP_DAT_CAT_SAN_TX, cu_emp_data_USFoodBeverage_TX, 
                                       by.x=c("LineCode","Year"), by.y=c("emp_line_code","year"))

cpi_emp_data_USFoodBeverage_TX %>%
  ###sample_frac(0.33) %>% 
  DT::datatable()

cu_emp_data_USFoodBeverage_WA = cu_data_USFoodBeverage %>% 
                                  filter(area_name=="Seattle-Tacoma-Bremerton", 
                                         period %in% c("M01","M02","M03","M04","M05","M06","M07","M08","M09","M10","M11","M12")) %>% 
                                  mutate(parent_item_code = 'SAF', 
                                         emp_line_code = 705,
                                         industry = 'Food & Beverages',
                                         state = 'WA') %>% 
                                  select(c("industry",
                                            "state",
                                            "parent_item_code",
                                            "emp_line_code",
                                            "series_id",
                                            "series_title",
                                            "year",
                                            "period",
                                            "period_abbr",
                                            "period_name",
                                            "value",
                                            "area_code",
                                            "area_name",
                                            "area_display_level",
                                            "area_selectable",
                                            "area_sort_sequence",
                                            "item_code",
                                            "item_name",
                                            "item_display_level",
                                            "item_selectable",
                                            "item_sort_sequence",
                                            "seasonal",
                                            "periodicity_code",
                                            "periodicity_name",
                                            "base_code",
                                            "base_name",
                                            "base_period")) %>% 
                                  group_by(industry, state, parent_item_code, emp_line_code, year, area_code, area_name) %>% 
                                  summarise(cpi_value = round(sum(value),2))

cpi_emp_data_USFoodBeverage_WA = merge(EMP_DAT_CAT_SAN_WA, cu_emp_data_USFoodBeverage_WA, 
                                       by.x=c("LineCode","Year"), by.y=c("emp_line_code","year"))

cpi_emp_data_USFoodBeverage_WA %>%
  ###sample_frac(0.33) %>% 
  DT::datatable()

cu_emp_data_USFoodBeverage_WI = cu_data_USFoodBeverage %>% 
                                  filter(area_name=="Milwaukee-Racine", 
                                         period %in% c("S01","S02")) %>% 
                                  mutate(parent_item_code = 'SAF', 
                                         emp_line_code = 705,
                                         industry = 'Food & Beverages',
                                         state = 'WI') %>% 
                                  select(c("industry",
                                            "state",
                                            "parent_item_code",
                                            "emp_line_code",
                                            "series_id",
                                            "series_title",
                                            "year",
                                            "period",
                                            "period_abbr",
                                            "period_name",
                                            "value",
                                            "area_code",
                                            "area_name",
                                            "area_display_level",
                                            "area_selectable",
                                            "area_sort_sequence",
                                            "item_code",
                                            "item_name",
                                            "item_display_level",
                                            "item_selectable",
                                            "item_sort_sequence",
                                            "seasonal",
                                            "periodicity_code",
                                            "periodicity_name",
                                            "base_code",
                                            "base_name",
                                            "base_period")) %>% 
                                  group_by(industry, state, parent_item_code, emp_line_code, year, area_code, area_name) %>% 
                                  summarise(cpi_value = round(sum(value),2))

cpi_emp_data_USFoodBeverage_WI = merge(EMP_DAT_CAT_SAN_WI, cu_emp_data_USFoodBeverage_WI, 
                                       by.x=c("LineCode","Year"), by.y=c("emp_line_code","year"))

cpi_emp_data_USFoodBeverage_WI %>%
  ###sample_frac(0.33) %>% 
  DT::datatable()

4.2 Housing

cu_emp_data_USHousing_AK = cu_data_USHousing  %>% 
                            filter(area_name=="Anchorage", 
                                   period %in% c("S01","S02")) %>% 
                            mutate(parent_item_code = 'SAH', 
                                   emp_line_code = 1100,
                                   industry = 'Housing',
                                   state = 'AK') %>% 
                            select(c("industry",
                                     "state",
                                     "parent_item_code",
                                     "emp_line_code",
                                     "series_id",
                                     "series_title",
                                     "year",
                                     "period",
                                     "period_abbr",
                                     "period_name",
                                     "value",
                                     "area_code",
                                     "area_name",
                                     "area_display_level",
                                     "area_selectable",
                                     "area_sort_sequence",
                                     "item_code",
                                     "item_name",
                                     "item_display_level",
                                     "item_selectable",
                                     "item_sort_sequence",
                                     "seasonal",
                                     "periodicity_code",
                                     "periodicity_name",
                                     "base_code",
                                     "base_name",
                                     "base_period")) %>% 
                            group_by(industry, state, parent_item_code, emp_line_code, year, area_code, area_name) %>% 
                            summarise(cpi_value = round(sum(value),2))

cpi_emp_data_USHousing_AK = merge(EMP_DAT_CAT_SAN_AK, cu_emp_data_USHousing_AK, 
                                  by.x=c("LineCode","Year"), by.y=c("emp_line_code","year"))

cpi_emp_data_USHousing_AK %>%
#  sample_frac(0.33) %>% 
  DT::datatable()

cu_emp_data_USHousing_AZ = cu_data_USHousing  %>% 
                            filter(area_name=="Phoenix-Mesa", 
                                   period %in% c("S01","S02")) %>% 
                            mutate(parent_item_code = 'SAH', 
                                   emp_line_code = 1100,
                                   industry = 'Housing',
                                   state = 'AZ') %>% 
                            select(c("industry",
                                     "state",
                                     "parent_item_code",
                                     "emp_line_code",
                                     "series_id",
                                     "series_title",
                                     "year",
                                     "period",
                                     "period_abbr",
                                     "period_name",
                                     "value",
                                     "area_code",
                                     "area_name",
                                     "area_display_level",
                                     "area_selectable",
                                     "area_sort_sequence",
                                     "item_code",
                                     "item_name",
                                     "item_display_level",
                                     "item_selectable",
                                     "item_sort_sequence",
                                     "seasonal",
                                     "periodicity_code",
                                     "periodicity_name",
                                     "base_code",
                                     "base_name",
                                     "base_period")) %>% 
                            group_by(industry, state, parent_item_code, emp_line_code, year, area_code, area_name) %>% 
                            summarise(cpi_value = round(sum(value),2))

cpi_emp_data_USHousing_AZ = merge(EMP_DAT_CAT_SAN_AZ, cu_emp_data_USHousing_AZ, 
                                  by.x=c("LineCode","Year"), by.y=c("emp_line_code","year"))

cpi_emp_data_USHousing_AZ %>%
#  sample_frac(0.33) %>% 
  DT::datatable()

cu_emp_data_USHousing_CA = cu_data_USHousing  %>% 
                            filter( (area_name %in% c("Los Angeles-Riverside-Orange County", "San Francisco-Oakland-San Jose")
                                      & period %in% c("M01","M02","M03","M04","M05","M06","M07","M08","M09","M10","M11","M12")
                                      )
                                     |
                                      (area_name %in% c("San Diego")
                                       & period %in% c("S01","S02")
                                       )
                                   ) %>% 
                            mutate(parent_item_code = 'SAH', 
                                   emp_line_code = 1100,
                                   area_code = 'A421;A422;A424',
                                   area_name = 'Los Angeles-Riverside-Orange County;San Francisco-Oakland-San Jose;San Diego',
                                   industry = 'Housing',
                                   state = 'CA') %>% 
                            select(c("industry",
                                     "state",
                                     "parent_item_code",
                                     "emp_line_code",
                                     "series_id",
                                     "series_title",
                                     "year",
                                     "period",
                                     "period_abbr",
                                     "period_name",
                                     "value",
                                     "area_code",
                                     "area_name",
                                     "area_display_level",
                                     "area_selectable",
                                     "area_sort_sequence",
                                     "item_code",
                                     "item_name",
                                     "item_display_level",
                                     "item_selectable",
                                     "item_sort_sequence",
                                     "seasonal",
                                     "periodicity_code",
                                     "periodicity_name",
                                     "base_code",
                                     "base_name",
                                     "base_period")) %>% 
                            group_by(industry, state, parent_item_code, emp_line_code, year, area_code, area_name) %>% 
                            summarise(cpi_value = round(sum(value/3),2))

cpi_emp_data_USHousing_CA = merge(EMP_DAT_CAT_SAN_CA, cu_emp_data_USHousing_CA, 
                                  by.x=c("LineCode","Year"), by.y=c("emp_line_code","year"))

cpi_emp_data_USHousing_CA %>%
#  sample_frac(0.33) %>% 
  DT::datatable()

cu_emp_data_USHousing_CO = cu_data_USHousing  %>% 
                            filter(area_name=="Denver-Boulder-Greeley", 
                                   period %in% c("S01","S02")) %>% 
                            mutate(parent_item_code = 'SAH', 
                                   emp_line_code = 1100,
                                   industry = 'Housing',
                                   state = 'CO') %>% 
                            select(c("industry",
                                     "state",
                                     "parent_item_code",
                                     "emp_line_code",
                                     "series_id",
                                     "series_title",
                                     "year",
                                     "period",
                                     "period_abbr",
                                     "period_name",
                                     "value",
                                     "area_code",
                                     "area_name",
                                     "area_display_level",
                                     "area_selectable",
                                     "area_sort_sequence",
                                     "item_code",
                                     "item_name",
                                     "item_display_level",
                                     "item_selectable",
                                     "item_sort_sequence",
                                     "seasonal",
                                     "periodicity_code",
                                     "periodicity_name",
                                     "base_code",
                                     "base_name",
                                     "base_period")) %>% 
                            group_by(industry, state, parent_item_code, emp_line_code, year, area_code, area_name) %>% 
                            summarise(cpi_value = round(sum(value),2))

cpi_emp_data_USHousing_CO = merge(EMP_DAT_CAT_SAN_CO, cu_emp_data_USHousing_CO, 
                                  by.x=c("LineCode","Year"), by.y=c("emp_line_code","year"))

cpi_emp_data_USHousing_CO %>%
#  sample_frac(0.33) %>% 
  DT::datatable()

cu_emp_data_USHousing_FL = cu_data_USHousing  %>% 
                            filter( (area_name %in% c("Miami-Fort Lauderdale")
                                    & period %in% c("M01","M02","M03","M04","M05","M06","M07","M08","M09","M10","M11","M12")
                                    )
                                   |
                                    (area_name %in% c("Tampa-St. Petersburg-Clearwater")
                                     & period %in% c("S01","S02")
                                     )
                                 ) %>% 
                            mutate(parent_item_code = 'SAH', 
                                   emp_line_code = 1100,
                                   area_code = 'A320;A321',
                                   area_name = 'Miami-Fort Lauderdale;Tampa-St. Petersburg-Clearwater',
                                   industry = 'Housing',
                                   state = 'FL') %>% 
                            select(c("industry",
                                     "state",
                                     "parent_item_code",
                                     "emp_line_code",
                                     "series_id",
                                     "series_title",
                                     "year",
                                     "period",
                                     "period_abbr",
                                     "period_name",
                                     "value",
                                     "area_code",
                                     "area_name",
                                     "area_display_level",
                                     "area_selectable",
                                     "area_sort_sequence",
                                     "item_code",
                                     "item_name",
                                     "item_display_level",
                                     "item_selectable",
                                     "item_sort_sequence",
                                     "seasonal",
                                     "periodicity_code",
                                     "periodicity_name",
                                     "base_code",
                                     "base_name",
                                     "base_period")) %>% 
                            group_by(industry, state, parent_item_code, emp_line_code, year, area_code, area_name) %>% 
                            summarise(cpi_value = round(sum(value)/2,2))

cpi_emp_data_USHousing_FL = merge(EMP_DAT_CAT_SAN_FL, cu_emp_data_USHousing_FL, 
                                  by.x=c("LineCode","Year"), by.y=c("emp_line_code","year"))

cpi_emp_data_USHousing_FL %>%
#  sample_frac(0.33) %>% 
  DT::datatable()

cu_emp_data_USHousing_GA = cu_data_USHousing  %>% 
                            filter(area_name=="Atlanta", 
                                   period %in% c("M01","M02","M03","M04","M05","M06","M07","M08","M09","M10","M11","M12")) %>% 
                            mutate(parent_item_code = 'SAH', 
                                   emp_line_code = 1100,
                                   industry = 'Housing',
                                   state = 'GA') %>% 
                            select(c("industry",
                                     "state",
                                     "parent_item_code",
                                     "emp_line_code",
                                     "series_id",
                                     "series_title",
                                     "year",
                                     "period",
                                     "period_abbr",
                                     "period_name",
                                     "value",
                                     "area_code",
                                     "area_name",
                                     "area_display_level",
                                     "area_selectable",
                                     "area_sort_sequence",
                                     "item_code",
                                     "item_name",
                                     "item_display_level",
                                     "item_selectable",
                                     "item_sort_sequence",
                                     "seasonal",
                                     "periodicity_code",
                                     "periodicity_name",
                                     "base_code",
                                     "base_name",
                                     "base_period")) %>% 
                            group_by(industry, state, parent_item_code, emp_line_code, year, area_code, area_name) %>% 
                            summarise(cpi_value = round(sum(value),2))

cpi_emp_data_USHousing_GA = merge(EMP_DAT_CAT_SAN_GA, cu_emp_data_USHousing_GA, 
                                  by.x=c("LineCode","Year"), by.y=c("emp_line_code","year"))

cpi_emp_data_USHousing_GA %>%
#  sample_frac(0.33) %>% 
  DT::datatable()

cu_emp_data_USHousing_HI = cu_data_USHousing  %>% 
                            filter(area_name=="Honolulu", 
                                   period %in% c("S01","S02")) %>% 
                            mutate(parent_item_code = 'SAH', 
                                   emp_line_code = 1100,
                                   industry = 'Housing',
                                   state = 'HI') %>% 
                            select(c("industry",
                                     "state",
                                     "parent_item_code",
                                     "emp_line_code",
                                     "series_id",
                                     "series_title",
                                     "year",
                                     "period",
                                     "period_abbr",
                                     "period_name",
                                     "value",
                                     "area_code",
                                     "area_name",
                                     "area_display_level",
                                     "area_selectable",
                                     "area_sort_sequence",
                                     "item_code",
                                     "item_name",
                                     "item_display_level",
                                     "item_selectable",
                                     "item_sort_sequence",
                                     "seasonal",
                                     "periodicity_code",
                                     "periodicity_name",
                                     "base_code",
                                     "base_name",
                                     "base_period")) %>% 
                            group_by(industry, state, parent_item_code, emp_line_code, year, area_code, area_name) %>% 
                            summarise(cpi_value = round(sum(value),2))

cpi_emp_data_USHousing_HI = merge(EMP_DAT_CAT_SAN_HI, cu_emp_data_USHousing_HI, 
                                  by.x=c("LineCode","Year"), by.y=c("emp_line_code","year"))

cpi_emp_data_USHousing_HI %>%
#  sample_frac(0.33) %>% 
  DT::datatable()

cu_emp_data_USHousing_IL = cu_data_USHousing  %>% 
                            filter(area_name=="Chicago-Gary-Kenosha", 
                                   period %in% c("M01","M02","M03","M04","M05","M06","M07","M08","M09","M10","M11","M12")) %>% 
                            mutate(parent_item_code = 'SAH', 
                                   emp_line_code = 1100,
                                   industry = 'Housing',
                                   state = 'IL') %>% 
                            select(c("industry",
                                     "state",
                                     "parent_item_code",
                                     "emp_line_code",
                                     "series_id",
                                     "series_title",
                                     "year",
                                     "period",
                                     "period_abbr",
                                     "period_name",
                                     "value",
                                     "area_code",
                                     "area_name",
                                     "area_display_level",
                                     "area_selectable",
                                     "area_sort_sequence",
                                     "item_code",
                                     "item_name",
                                     "item_display_level",
                                     "item_selectable",
                                     "item_sort_sequence",
                                     "seasonal",
                                     "periodicity_code",
                                     "periodicity_name",
                                     "base_code",
                                     "base_name",
                                     "base_period")) %>% 
                            group_by(industry, state, parent_item_code, emp_line_code, year, area_code, area_name) %>% 
                            summarise(cpi_value = round(sum(value),2))

cpi_emp_data_USHousing_IL = merge(EMP_DAT_CAT_SAN_IL, cu_emp_data_USHousing_IL, 
                                  by.x=c("LineCode","Year"), by.y=c("emp_line_code","year"))

cpi_emp_data_USHousing_IL %>%
#  sample_frac(0.33) %>% 
  DT::datatable()

cu_emp_data_USHousing_KS = cu_data_USHousing  %>% 
                            filter(area_name=="Kansas City", 
                                   period %in% c("S01","S02")) %>% 
                            mutate(parent_item_code = 'SAH', 
                                   emp_line_code = 1100,
                                   industry = 'Housing',
                                   state = 'KS') %>% 
                            select(c("industry",
                                     "state",
                                     "parent_item_code",
                                     "emp_line_code",
                                     "series_id",
                                     "series_title",
                                     "year",
                                     "period",
                                     "period_abbr",
                                     "period_name",
                                     "value",
                                     "area_code",
                                     "area_name",
                                     "area_display_level",
                                     "area_selectable",
                                     "area_sort_sequence",
                                     "item_code",
                                     "item_name",
                                     "item_display_level",
                                     "item_selectable",
                                     "item_sort_sequence",
                                     "seasonal",
                                     "periodicity_code",
                                     "periodicity_name",
                                     "base_code",
                                     "base_name",
                                     "base_period")) %>% 
                            group_by(industry, state, parent_item_code, emp_line_code, year, area_code, area_name) %>% 
                            summarise(cpi_value = round(sum(value),2))

cpi_emp_data_USHousing_KS = merge(EMP_DAT_CAT_SAN_KS, cu_emp_data_USHousing_KS, 
                                  by.x=c("LineCode","Year"), by.y=c("emp_line_code","year"))

cpi_emp_data_USHousing_KS %>%
#  sample_frac(0.33) %>% 
  DT::datatable()

cu_emp_data_USHousing_MA = cu_data_USHousing  %>% 
                            filter(area_name=="Boston-Brockton-Nashua", 
                                   period %in% c("M01","M02","M03","M04","M05","M06","M07","M08","M09","M10","M11","M12")) %>% 
                            mutate(parent_item_code = 'SAH', 
                                   emp_line_code = 1100,
                                   industry = 'Housing',
                                   state = 'MA') %>% 
                            select(c("industry",
                                     "state",
                                     "parent_item_code",
                                     "emp_line_code",
                                     "series_id",
                                     "series_title",
                                     "year",
                                     "period",
                                     "period_abbr",
                                     "period_name",
                                     "value",
                                     "area_code",
                                     "area_name",
                                     "area_display_level",
                                     "area_selectable",
                                     "area_sort_sequence",
                                     "item_code",
                                     "item_name",
                                     "item_display_level",
                                     "item_selectable",
                                     "item_sort_sequence",
                                     "seasonal",
                                     "periodicity_code",
                                     "periodicity_name",
                                     "base_code",
                                     "base_name",
                                     "base_period")) %>% 
                            group_by(industry, state, parent_item_code, emp_line_code, year, area_code, area_name) %>% 
                            summarise(cpi_value = round(sum(value),2))

cpi_emp_data_USHousing_MA = merge(EMP_DAT_CAT_SAN_MA, cu_emp_data_USHousing_MA, 
                                  by.x=c("LineCode","Year"), by.y=c("emp_line_code","year"))

cpi_emp_data_USHousing_MA %>%
#  sample_frac(0.33) %>% 
  DT::datatable()

cu_emp_data_USHousing_MI = cu_data_USHousing  %>% 
                            filter(area_name=="Detroit-Ann Arbor-Flint", 
                                   period %in% c("M01","M02","M03","M04","M05","M06","M07","M08","M09","M10","M11","M12")) %>% 
                            mutate(parent_item_code = 'SAH', 
                                   emp_line_code = 1100,
                                   industry = 'Housing',
                                   state = 'MI') %>% 
                            select(c("industry",
                                     "state",
                                     "parent_item_code",
                                     "emp_line_code",
                                     "series_id",
                                     "series_title",
                                     "year",
                                     "period",
                                     "period_abbr",
                                     "period_name",
                                     "value",
                                     "area_code",
                                     "area_name",
                                     "area_display_level",
                                     "area_selectable",
                                     "area_sort_sequence",
                                     "item_code",
                                     "item_name",
                                     "item_display_level",
                                     "item_selectable",
                                     "item_sort_sequence",
                                     "seasonal",
                                     "periodicity_code",
                                     "periodicity_name",
                                     "base_code",
                                     "base_name",
                                     "base_period")) %>% 
                            group_by(industry, state, parent_item_code, emp_line_code, year, area_code, area_name) %>% 
                            summarise(cpi_value = round(sum(value),2))

cpi_emp_data_USHousing_MI = merge(EMP_DAT_CAT_SAN_MI, cu_emp_data_USHousing_MI, 
                                  by.x=c("LineCode","Year"), by.y=c("emp_line_code","year"))

cpi_emp_data_USHousing_MI %>%
#  sample_frac(0.33) %>% 
  DT::datatable()

cu_emp_data_USHousing_MN = cu_data_USHousing  %>% 
                            filter(area_name=="Minneapolis-St. Paul", 
                                   period %in% c("S01","S02")) %>% 
                            mutate(parent_item_code = 'SAH', 
                                   emp_line_code = 1100,
                                   industry = 'Housing',
                                   state = 'MN') %>% 
                            select(c("industry",
                                     "state",
                                     "parent_item_code",
                                     "emp_line_code",
                                     "series_id",
                                     "series_title",
                                     "year",
                                     "period",
                                     "period_abbr",
                                     "period_name",
                                     "value",
                                     "area_code",
                                     "area_name",
                                     "area_display_level",
                                     "area_selectable",
                                     "area_sort_sequence",
                                     "item_code",
                                     "item_name",
                                     "item_display_level",
                                     "item_selectable",
                                     "item_sort_sequence",
                                     "seasonal",
                                     "periodicity_code",
                                     "periodicity_name",
                                     "base_code",
                                     "base_name",
                                     "base_period")) %>% 
                            group_by(industry, state, parent_item_code, emp_line_code, year, area_code, area_name) %>% 
                            summarise(cpi_value = round(sum(value),2))

cpi_emp_data_USHousing_MN = merge(EMP_DAT_CAT_SAN_MN, cu_emp_data_USHousing_MN, 
                                  by.x=c("LineCode","Year"), by.y=c("emp_line_code","year"))

cpi_emp_data_USHousing_MN %>%
#  sample_frac(0.33) %>% 
  DT::datatable()

cu_emp_data_USHousing_MO = cu_data_USHousing  %>% 
                            filter(area_name=="St. Louis", 
                                   period %in% c("S01","S02")) %>% 
                            mutate(parent_item_code = 'SAH', 
                                   emp_line_code = 1100,
                                   industry = 'Housing',
                                   state = 'MO') %>% 
                            select(c("industry",
                                     "state",
                                     "parent_item_code",
                                     "emp_line_code",
                                     "series_id",
                                     "series_title",
                                     "year",
                                     "period",
                                     "period_abbr",
                                     "period_name",
                                     "value",
                                     "area_code",
                                     "area_name",
                                     "area_display_level",
                                     "area_selectable",
                                     "area_sort_sequence",
                                     "item_code",
                                     "item_name",
                                     "item_display_level",
                                     "item_selectable",
                                     "item_sort_sequence",
                                     "seasonal",
                                     "periodicity_code",
                                     "periodicity_name",
                                     "base_code",
                                     "base_name",
                                     "base_period")) %>% 
                            group_by(industry, state, parent_item_code, emp_line_code, year, area_code, area_name) %>% 
                            summarise(cpi_value = round(sum(value),2))

cpi_emp_data_USHousing_MO = merge(EMP_DAT_CAT_SAN_MO, cu_emp_data_USHousing_MO, 
                                  by.x=c("LineCode","Year"), by.y=c("emp_line_code","year"))

cpi_emp_data_USHousing_MO %>%
#  sample_frac(0.33) %>% 
  DT::datatable()

cu_emp_data_USHousing_NY = cu_data_USHousing  %>% 
                            filter(area_name=="New York-Northern New Jersey-Long Island", 
                                   period %in% c("M01","M02","M03","M04","M05","M06","M07","M08","M09","M10","M11","M12")) %>% 
                            mutate(parent_item_code = 'SAH', 
                                   emp_line_code = 1100,
                                   industry = 'Housing',
                                   state = 'NY') %>% 
                            select(c("industry",
                                     "state",
                                     "parent_item_code",
                                     "emp_line_code",
                                     "series_id",
                                     "series_title",
                                     "year",
                                     "period",
                                     "period_abbr",
                                     "period_name",
                                     "value",
                                     "area_code",
                                     "area_name",
                                     "area_display_level",
                                     "area_selectable",
                                     "area_sort_sequence",
                                     "item_code",
                                     "item_name",
                                     "item_display_level",
                                     "item_selectable",
                                     "item_sort_sequence",
                                     "seasonal",
                                     "periodicity_code",
                                     "periodicity_name",
                                     "base_code",
                                     "base_name",
                                     "base_period")) %>% 
                            group_by(industry, state, parent_item_code, emp_line_code, year, area_code, area_name) %>% 
                            summarise(cpi_value = round(sum(value),2))

cpi_emp_data_USHousing_NY = merge(EMP_DAT_CAT_SAN_NY, cu_emp_data_USHousing_NY, 
                                  by.x=c("LineCode","Year"), by.y=c("emp_line_code","year"))

cpi_emp_data_USHousing_NY %>%
#  sample_frac(0.33) %>% 
  DT::datatable()

cu_emp_data_USHousing_OH = cu_data_USHousing  %>% 
                                  filter( (area_name %in% c("Cleveland-Akron")
                                          & period %in% c("M01","M02","M03","M04","M05","M06","M07","M08","M09","M10","M11","M12")
                                          )
                                         |
                                          (area_name %in% c("Cincinnati-Hamilton")
                                           & period %in% c("S01","S02")
                                           )
                                       ) %>% 
                            mutate(parent_item_code = 'SAH', 
                                   emp_line_code = 1100,
                                   area_code = 'A210;A213', 
                                   area_name = 'Cleveland-Akron;Cincinnati-Hamilton',
                                   industry = 'Housing',
                                   state = 'OH') %>% 
                            select(c("industry",
                                     "state",
                                     "parent_item_code",
                                     "emp_line_code",
                                     "series_id",
                                     "series_title",
                                     "year",
                                     "period",
                                     "period_abbr",
                                     "period_name",
                                     "value",
                                     "area_code",
                                     "area_name",
                                     "area_display_level",
                                     "area_selectable",
                                     "area_sort_sequence",
                                     "item_code",
                                     "item_name",
                                     "item_display_level",
                                     "item_selectable",
                                     "item_sort_sequence",
                                     "seasonal",
                                     "periodicity_code",
                                     "periodicity_name",
                                     "base_code",
                                     "base_name",
                                     "base_period")) %>% 
                            group_by(industry, state, parent_item_code, emp_line_code, year, area_code, area_name) %>% 
                            summarise(cpi_value = round(sum(value)/2,2))

cpi_emp_data_USHousing_OH = merge(EMP_DAT_CAT_SAN_OH, cu_emp_data_USHousing_OH, 
                                  by.x=c("LineCode","Year"), by.y=c("emp_line_code","year"))

cpi_emp_data_USHousing_OH %>%
#  sample_frac(0.33) %>% 
  DT::datatable()

cu_emp_data_USHousing_OR = cu_data_USHousing  %>% 
                            filter(area_name=="Portland-Salem", 
                                   period %in% c("S01","S02")) %>% 
                            mutate(parent_item_code = 'SAH', 
                                   emp_line_code = 1100,
                                   industry = 'Housing',
                                   state = 'OR') %>% 
                            select(c("industry",
                                     "state",
                                     "parent_item_code",
                                     "emp_line_code",
                                     "series_id",
                                     "series_title",
                                     "year",
                                     "period",
                                     "period_abbr",
                                     "period_name",
                                     "value",
                                     "area_code",
                                     "area_name",
                                     "area_display_level",
                                     "area_selectable",
                                     "area_sort_sequence",
                                     "item_code",
                                     "item_name",
                                     "item_display_level",
                                     "item_selectable",
                                     "item_sort_sequence",
                                     "seasonal",
                                     "periodicity_code",
                                     "periodicity_name",
                                     "base_code",
                                     "base_name",
                                     "base_period")) %>% 
                            group_by(industry, state, parent_item_code, emp_line_code, year, area_code, area_name) %>% 
                            summarise(cpi_value = round(sum(value),2))

cpi_emp_data_USHousing_OR = merge(EMP_DAT_CAT_SAN_OR, cu_emp_data_USHousing_OR, 
                                  by.x=c("LineCode","Year"), by.y=c("emp_line_code","year"))

cpi_emp_data_USHousing_OR %>%
#  sample_frac(0.33) %>% 
  DT::datatable()

cu_emp_data_USHousing_PA = cu_data_USHousing  %>% 
                            filter( (area_name %in% c("Philadelphia-Wilmington-Atlantic City")
                                    & period %in% c("M01","M02","M03","M04","M05","M06","M07","M08","M09","M10","M11","M12")
                                    )
                                   |
                                    (area_name %in% c("Pittsburgh")
                                     & period %in% c("S01","S02")
                                     )
                                 ) %>% 
                            mutate(parent_item_code = 'SAH', 
                                   emp_line_code = 1100,
                                   area_code = 'A102;A104', 
                                   area_name = 'Philadelphia-Wilmington-Atlantic City;Pittsburgh',
                                   industry = 'Housing',
                                   state = 'PA') %>% 
                            select(c("industry",
                                     "state",
                                     "parent_item_code",
                                     "emp_line_code",
                                     "series_id",
                                     "series_title",
                                     "year",
                                     "period",
                                     "period_abbr",
                                     "period_name",
                                     "value",
                                     "area_code",
                                     "area_name",
                                     "area_display_level",
                                     "area_selectable",
                                     "area_sort_sequence",
                                     "item_code",
                                     "item_name",
                                     "item_display_level",
                                     "item_selectable",
                                     "item_sort_sequence",
                                     "seasonal",
                                     "periodicity_code",
                                     "periodicity_name",
                                     "base_code",
                                     "base_name",
                                     "base_period")) %>% 
                            group_by(industry, state, parent_item_code, emp_line_code, year, area_code, area_name) %>% 
                            summarise(cpi_value = round(sum(value)/2,2))

cpi_emp_data_USHousing_PA = merge(EMP_DAT_CAT_SAN_PA, cu_emp_data_USHousing_PA, 
                                  by.x=c("LineCode","Year"), by.y=c("emp_line_code","year"))

cpi_emp_data_USHousing_PA %>%
#  sample_frac(0.33) %>% 
  DT::datatable()