national_data.Rmd

---
title: ""
author: ""
output: 
  html_document:
    css: style.css
    
---

```{r setup-1-general, include=FALSE}
# Loading Packages That we need
library(forcats)
library(DT)
library(readxl)
library(tidyverse)
library(highcharter)
library(glue)

# Setting Number formatting 
knitr::knit_hooks$set(inline = function(x) { if(!is.numeric(x)){ x }else{ prettyNum(round(x,2), big.mark=",", digits=6) } })
hcoptslang <- getOption("highcharter.lang")
hcoptslang$thousandsSep <- ","
options(highcharter.lang = hcoptslang)

# Read in data from excel file
pop_id_lookup <- read_csv("pop_id_lookup.csv") #Look-up table
national_cvap<- readxl::read_xlsx("national_dta.xlsx", sheet = "cvap")
national_edu<- readxl::read_xlsx("national_dta.xlsx", sheet = "edu")
national_lep<- readxl::read_xlsx("national_dta.xlsx", sheet = "lep")
national_nativity<- readxl::read_xlsx("national_dta.xlsx", sheet = "nativity")

## Using the lookup table to merge in Group Names and the proper population ID names
national_edu<-national_edu %>% left_join(pop_id_lookup) %>% select(-geoid, - geodisplaylabel)
national_lep<-national_lep %>% left_join(pop_id_lookup) %>% select(-geoid, - geodisplaylabel)
national_nativity<-national_nativity %>% left_join(pop_id_lookup) %>% select(-geoid, - geodisplaylabel)


## Specifying Column Group names, to be used later
nativity_columns <- c("Group", "Total Pop", "Foreign Born","Total Foreign Born")
cvap_columns <- c("Group", "CVAP", "Total")
education_columns <- c("Group", "Total","Less than HS", "HS or GED", "Some College or AA", "BA or higher")
lep_columns <- c("Group", "Total Pop", "Speak Other Language","LEP", "Total LEP")

## Renaming CVAP
national_cvap <-national_cvap %>% rename(Total = total_pop)


#### Creating reusable Functions
national_tbl_display <- function(data,group,column_names,orderby){
  data %>%
    filter(group_id ==  paste(group)) %>%
    select(column_names) %>%
    #arrange(desc(paste(orderby)))%>%
    datatable(extensions = 
                list("Buttons" = NULL, 'FixedColumns'= T, "Scroller"=T),
              rownames= FALSE, 
              options = list(
                dom = 'rtB',
                buttons = c('copy', 'csv', 'excel'),
                scrollX = TRUE,
                lengthMenu = c(25, 50, 100), pageLength = 50))
}


```

```{r setup-1-education, include=FALSE}

## This is the setupt for the education data
### Getting Rid of Estimates where the "CHECK" variable is flagging uncertainty
national_edu <- national_edu %>% 
  mutate(est_hsless = 
            case_when( check_hsless == 1 ~  NA_real_ ,TRUE ~ est_hsless),
         est_hsged  =
          case_when( check_hsged == 1 ~  NA_real_ ,TRUE ~ est_hsged),
         est_somecollegeaa  =
          case_when( check_somecollegeaa == 1 ~  NA_real_ ,TRUE ~ est_somecollegeaa),
         est_bahigher  =
          case_when( check_bahigher == 1 ~  NA_real_ ,TRUE ~ est_bahigher))
      
national_edu <- national_edu %>% select(-check_hsless,
                                  -check_hsged,
                                  -check_somecollegeaa,
                                  -check_bahigher)

# Creating Columns for percentages 
national_edu <- national_edu %>% mutate(
  pct_hsless   = est_hsless/est_totpop,
  pct_bahigher = est_bahigher/ est_totpop)

# Renaming columns
national_edu<- national_edu %>% rename(Total = est_totpop,
         `Less than HS` = est_hsless,
         `HS or GED` = est_hsged,
         `Some College or AA` = est_somecollegeaa,
         `BA or higher` = est_bahigher,
         Group = group_name)




```

```{r setup-1-lep, include=FALSE}

### Getting Rid of Estimates where the "CHECK" variable is flagging uncertainty

national_lep <- national_lep %>% 
  mutate(pct_lep = 
            case_when( checking_lep == 1 ~  NA_real_ ,TRUE ~ pct_lep),
         est_lep = 
            case_when( checking_lep == 1 ~  NA_real_ ,TRUE ~ est_lep),
         est_other_lang = 
            case_when( checking_other_lang == 1 ~  NA_real_ ,TRUE ~ est_other_lang),
         est_totalpop = 
            case_when( checking_totpop == 1 ~  NA_real_ ,TRUE ~ est_totalpop))

national_lep <- national_lep %>% 
  mutate(pct_other_lang = est_other_lang/est_totalpop)  

national_lep<- national_lep %>% rename(Group = group_name,
                                       `LEP` = pct_lep,
                                       `Total LEP` = est_lep,
                                       `Total Pop` = est_totalpop,
                                       `Total Speak Other Language` = est_other_lang,
                                       `Speak Other Language` = pct_other_lang)

```


```{r setup-1-nativity, include=FALSE}

### Getting Rid of Estimates where the "CHECK" variable is flagging uncertainty

national_nativity<- national_nativity %>% 
  mutate(pct_foreign = 
            case_when( checking_foreign == 1 ~  NA_real_ ,TRUE ~ pct_foreign),
         est_tot_pop = 
            case_when( checking_tot_pop == 1 ~  NA_real_ ,TRUE ~ est_tot_pop),
         est_foreign = 
            case_when( checking_foreign == 1 ~  NA_real_ ,TRUE ~ est_foreign))


national_nativity<- national_nativity %>% rename(Group = group_name,
                                       `Foreign Born` = pct_foreign,
                                       `Total Pop` = est_tot_pop,
                                       `Total Foreign Born` = est_foreign)


```

```{r setup-1-population, include=FALSE}

### Getting Rid of Estimates where the "CHECK" variable is flagging uncertainty

## Hey Sunny! Take a look at previous "setup blocks", to see what you need to do here to get everything in shape, but you can put all the code you use for cleaning, adding columns, etc. to the population data here.



```

<div class="jumbotron">
  <h1>National Data</h1>
  <p>Use the buttons to select the topic and then the tabs for population groups</p>
</div>



# Citizen Voting Age Population {.tabset .tabset-fade .tabset-pills}

> Citizen Voting Age Population (CVAP) data is complied using Table [B05003](https://factfinder.census.gov/bkmk/table/1.0/en/ACS/15_5YR/B05003) from the 2015 ACS 5-Year estimates.

## Major Racial Groups

```{r cvap major groups, echo=FALSE, message=FALSE, warning=FALSE}

national_tbl_display(national_cvap,"Major racial groups",cvap_columns,"CVAP") %>% # Use the function to display the national_cvap data, and then
  formatCurrency(columns = cvap_columns,currency = "", interval = 3, mark = ",", digits=0) # format the columns in "cvap_columns" with the thousand separator so they look nice

```


## Asian Detailed Origin
```{r cvap asian alone, echo=FALSE, message=FALSE, warning=FALSE}


national_tbl_display(national_cvap,"Asian Detail",cvap_columns,"CVAP")%>% 
  formatCurrency(columns = cvap_columns,currency = "", interval = 3, mark = ",", digits=0)


```

## Asian Detailed Origin Combo
```{r cvap asian combo, echo=FALSE, message=FALSE, warning=FALSE}

national_tbl_display(national_cvap,"Asian Detail Combo",cvap_columns,"CVAP")%>% 
  formatCurrency(columns = cvap_columns,currency = "", interval = 3, mark = ",", digits=0)


```

## NHPI Detailed Origin
```{r cvap nhpi alone, echo=FALSE, message=FALSE, warning=FALSE}


national_tbl_display(national_cvap,"NHPI Detail",cvap_columns,"CVAP")%>% 
  formatCurrency(columns = cvap_columns,currency = "", interval = 3, mark = ",", digits=0)

```

## NHPI Detailed Origin Combo
```{r cvap nhpi combo, echo=FALSE, message=FALSE, warning=FALSE}


national_tbl_display(national_cvap,"NHPI Detail Combo",cvap_columns,"CVAP")%>% 
  formatCurrency(columns = cvap_columns,currency = "", interval = 3, mark = ",", digits=0)

```



# Education {.tabset .tabset-fade .tabset-pills}

> Educational Attainment data is complied using Table [B15002](https://factfinder.census.gov/bkmk/table/1.0/en/ACS/15_5YR/B15002) from the 2015 ACS 5-Year estimates.

According to the latest national estimates, about **1 in 2** Asian Americans have a Bachelor's Degree or higher compared to about **1 in 3** Americans and **1 in 5** NHPIs.

```{r edu graph major groups, echo=FALSE, message=FALSE, warning=FALSE}


## Creating the graph
national_edu %>% # Take education data and then,
  filter(group_id ==  "Major racial groups") %>% # filter it so we only get rows belonging to "major racial groups" and then,
  select(Group, pct_hsless, pct_bahigher) %>% # Select the columns named: Group, pct_hsless, pct_bahigher and then, 
  arrange(desc(pct_bahigher)) %>% # Sort the columns according to: pct_bahigher and then,
  mutate(`BA or Higher` = round((pct_bahigher*100),2), 
         `Less than HS` = round((pct_hsless*100),2))  %>% # Create new columns, just taking pcts and multiplying by 100 for display and then,
   select(-pct_hsless, -pct_bahigher) %>% # Drop those old columns and then,
  gather(estimate_type,estimate, -Group) %>% # gather the columns (i.e pivot) and then,
hchart( "bar", hcaes(x = Group , y = estimate, group = estimate_type)) %>% # Create a bar chart with X = Group, Y = estimate, and Group = Estimate_type and then,
hc_add_theme(hc_theme_tufte2()) %>%  hc_colors(colors = c("#E26069","#008fd5")) %>% # add a theme and choose colors and then,
  hc_yAxis(title = list(text = ""),
         labels= list(style =list(color ="black", fontSize='18px'),
                      format= "{value}%"), max = 100, opposite=TRUE) %>% # Alter titling and then,
  hc_xAxis(title = list(text = ""),
           labels= list(style =list(color ="#E5790D", fontSize='20px')),
            minorTickLength= 0,tickLength=0) %>%
   hc_tooltip(pointFormat = "{series.name}: {point.y}%") %>% # create tool tip and then,
 hc_title(text = "Educational Attainment",
           margin = 20, align = "left",
           style = list(color = "#E5790D"))%>% # Create title and then,
  hc_subtitle(text = "(2011-2015 ACS)",
            margin = 20, align = "left",
           style = list(color = "#E5790D")) # Add subtitle
```


## Major Racial Groups
```{r edu major groups, echo=FALSE, message=FALSE, warning=FALSE}

national_tbl_display(national_edu,"Major racial groups",education_columns,"Total")%>% 
  formatCurrency(columns = education_columns,currency = "", interval = 3, mark = ",", digits=0)

```


## Asian Detailed Origin
```{r edu asian alone, echo=FALSE, message=FALSE, warning=FALSE}
national_tbl_display(national_edu,"Asian Detail",education_columns,"Total")%>% 
  formatCurrency(columns = education_columns,currency = "", interval = 3, mark = ",", digits=0)

```

## Asian Detailed Origin Combo
```{r edu asian combo, echo=FALSE, message=FALSE, warning=FALSE}

national_tbl_display(national_edu,"Asian Detail Combo",education_columns,"Total")%>% 
  formatCurrency(columns = education_columns,currency = "", interval = 3, mark = ",", digits=0)


```

## NHPI Detailed Origin
```{r edu nhpi alone, echo=FALSE, message=FALSE, warning=FALSE}

national_tbl_display(national_edu,"NHPI Detail",education_columns,"Total")%>% 
  formatCurrency(columns = education_columns,currency = "", interval = 3, mark = ",", digits=0)



```

## NHPI Detailed Origin Combo
```{r edu nhpi combo,echo=FALSE, message=FALSE, warning=FALSE}

national_tbl_display(national_edu,"NHPI Detail Combo",education_columns,"Total")%>% 
  formatCurrency(columns = education_columns,currency = "", interval = 3, mark = ",", digits=0)


```



# Limited English Proficiency {.tabset .tabset-fade .tabset-pills}

> Limited English Proficiency (LEP) refers to the proportion of individuals who a language other than english at home & speak english less than "very well".
Limited English Proficiency data is complied using Table [B16004](https://factfinder.census.gov/bkmk/table/1.0/en/ACS/15_5YR/B16004) from the 2015 ACS 5-Year estimates.


```{r lep_graph, echo=FALSE, message=FALSE, warning=FALSE}
national_lep %>%
  filter(group_id ==  "Major racial groups") %>%
  select(lep_columns) %>% 
  mutate(pct_other = round(`Speak Other Language`*100,2),
                                 pct_lep = round(LEP *100,2)) %>%
  arrange(desc(pct_other)) %>% hchart( "bar", hcaes(x = Group , y = pct_other)) %>%
  hc_add_theme(hc_theme_tufte2()) %>%  hc_colors(colors = c("#008fd5")) %>%
  hc_yAxis(title = list(text = ""),
         labels= list(style =list(color ="black", fontSize='15px'),
                      format= "{value}%"), max = 100, opposite=TRUE) %>%
  hc_xAxis(title = list(text = ""),
           labels= list(style =list(color ="#E5790D", fontSize='15px')),
            minorTickLength= 0,tickLength=0) %>%
  hc_tooltip(useHTML=T,pointFormat = "About <span style = 'color:#E26069'>{point.y:.0f}%</span> speak a language other than english at home <br> and of those, <span style = 'color:#E26069'>{point.pct_lep:.0f}%</span> are LEP.") %>%
   hc_title(text = "Speak a language other than english",
           margin = 20, align = "left",
           style = list(color = "#E5790D"))%>%
  hc_subtitle(text = "(2011-2015 ACS)",
            margin = 20, align = "left",
           style = list(color = "#E5790D"))

```



## Major racial groups
```{r lep major groups,echo=FALSE, message=FALSE, warning=FALSE}

national_tbl_display(national_lep,"Major racial groups",lep_columns,"Total Lep")%>% 
  formatPercentage(columns = c("LEP","Speak Other Language"), digits = 2) %>% 
  formatCurrency(columns = c("Total LEP", "Total Pop"),currency = "", interval = 3, mark = ",", digits=0)

```


## Asian Detailed Origin
```{r lep asian detail,echo=FALSE, message=FALSE, warning=FALSE}

national_tbl_display(national_lep,"Asian Detail",lep_columns,"Total Lep")%>% 
  formatPercentage(columns = c("LEP","Speak Other Language"), digits = 2) %>% 
  formatCurrency(columns = c("Total LEP", "Speak Other Language", "Total Pop"),currency = "", interval = 3, mark = ",", digits=0)
```


## Asian Detailed Origin Combo
```{r lep asn combo,echo=FALSE, message=FALSE, warning=FALSE}

national_tbl_display(national_lep,"Asian Detail Combo",lep_columns,"Total Lep")%>% 
  formatPercentage(columns =c("LEP","Speak Other Language"), digits = 2) %>% 
  formatCurrency(columns = c("Total LEP", "Speak Other Language", "Total Pop"),currency = "", interval = 3, mark = ",", digits=0)

```

## NHPI Detailed Origin
```{r lep nhpi detail,echo=FALSE, message=FALSE, warning=FALSE}

national_tbl_display(national_lep,"NHPI Detail",lep_columns,"Total Lep")%>% 
  formatPercentage(columns = c("LEP","Speak Other Language"), digits = 2) %>% 
  formatCurrency(columns = c("Total LEP", "Speak Other Language", "Total Pop"),currency = "", interval = 3, mark = ",", digits=0)

```


## NHPI Detailed Origin Combo
```{r lep nhpi combo,echo=FALSE, message=FALSE, warning=FALSE}

national_tbl_display(national_lep,"NHPI Detail Combo",lep_columns,"Total Lep")%>% 
  formatPercentage(columns = c("LEP","Speak Other Language"), digits = 2) %>% 
  formatCurrency(columns = c("Total LEP", "Speak Other Language", "Total Pop"),currency = "", interval = 3, mark = ",", digits=0)

```


# Nativity {.tabset .tabset-fade .tabset-pills}

> Limited English Proficiency data is complied using Table [B05012](https://factfinder.census.gov/bkmk/table/1.0/en/ACS/15_5YR/B05012) from the 2015 ACS 5-Year estimates.


```{r nativity graph, echo=FALSE, message=FALSE, warning=FALSE}
national_nativity %>%
  filter(group_id ==  "Major racial groups") %>%
  select(nativity_columns) %>% 
  mutate(pct_foreign = round(`Foreign Born`*100,2)) %>%
  arrange(desc(pct_foreign)) %>% hchart( "bar", hcaes(x = Group , y = pct_foreign)) %>%
  hc_add_theme(hc_theme_tufte2()) %>%  hc_colors(colors = c("#008fd5")) %>%
  hc_yAxis(title = list(text = ""),
         labels= list(style =list(color ="black", fontSize='15px'),
                      format= "{value}%"), max = 100, opposite=TRUE) %>%
  hc_xAxis(title = list(text = ""),
           labels= list(style =list(color ="#E5790D", fontSize='15px')),
            minorTickLength= 0,tickLength=0) %>%
  hc_tooltip(useHTML=T,pointFormat = "About <span style = 'color:#E26069'>{point.y:.0f}%</span> are foreign born.") %>%
   hc_title(text = "Percent Foreign Born",
           margin = 20, align = "left",
           style = list(color = "#E5790D"))%>%
  hc_subtitle(text = "(2011-2015 ACS)",
            margin = 20, align = "left",
           style = list(color = "#E5790D"))

```



## Major racial groups
```{r nativity major groups,echo=FALSE, message=FALSE, warning=FALSE}

national_tbl_display(national_nativity,"Major racial groups",nativity_columns,"Total Pop")%>% 
  formatPercentage(columns = c("Foreign Born"), digits = 2) %>% 
  formatCurrency(columns = c("Total Pop", "Total Foreign Born"),currency = "", interval = 3, mark = ",", digits=0)

```


## Asian Detailed Origin
```{r nativity asian detail,echo=FALSE, message=FALSE, warning=FALSE}

national_tbl_display(national_nativity,"Asian Detail",nativity_columns,"Total Pop")%>% 
  formatPercentage(columns = c("Foreign Born"), digits = 2) %>% 
  formatCurrency(columns = c("Total Pop", "Total Foreign Born"),currency = "", interval = 3, mark = ",", digits=0)

```


## Asian Detailed Origin Combo
```{r nativity asn combo,echo=FALSE, message=FALSE, warning=FALSE}

national_tbl_display(national_nativity,"Asian Detail Combo",nativity_columns,"Total Pop")%>% 
  formatPercentage(columns = c("Foreign Born"), digits = 2) %>% 
  formatCurrency(columns = c("Total Pop", "Total Foreign Born"),currency = "", interval = 3, mark = ",", digits=0)


```

## NHPI Detailed Origin
```{r nativity nhpi detail,echo=FALSE, message=FALSE, warning=FALSE}

national_tbl_display(national_nativity,"NHPI Detail",nativity_columns,"Total Pop")%>% 
  formatPercentage(columns = c("Foreign Born"), digits = 2) %>% 
  formatCurrency(columns = c("Total Pop", "Total Foreign Born"),currency = "", interval = 3, mark = ",", digits=0)

```


## NHPI Detailed Origin Combo
```{r nativity nhpi combo,echo=FALSE, message=FALSE, warning=FALSE}

national_tbl_display(national_nativity,"NHPI Detail Combo",nativity_columns,"Total Pop")%>% 
  formatPercentage(columns = c("Foreign Born"), digits = 2) %>% 
  formatCurrency(columns = c("Total Pop", "Total Foreign Born"),currency = "", interval = 3, mark = ",", digits=0)

```



## Population {.tabset .tabset-fade .tabset-pills}

> Populationdata is complied using Table [?????](https://factfinder.census.gov/bkmk/table/1.0/en/ACS/15_5YR/??????) from the 2015 ACS 5-Year estimates.


```{r population graph, echo=FALSE, message=FALSE, warning=FALSE}

### Hey Sunny! put the code for the graph here, take a look at lines 218-245 for commented code on how each part works
### 

```

## Major racial groups
```{r population major groups,echo=FALSE, message=FALSE, warning=FALSE}

### Hey Sunny! put the code for the table here, take a look at lines 175-179 to see how the code works

```

## Asian Detail...