PUMS1_rquery.Rmd

---
title: "PUMS1_rquery"
author: "Win-Vector LLC"
date: "4/24/2018"
output: github_document
---

```{r}
library("DBI")
library("rquery")

db <- dbConnect(RSQLite::SQLite(), ":memory:") 	
dbWriteTable(db, "dpus", readRDS("ss16pus.RDS"))
dbWriteTable(db, "dhus", readRDS("ss16hus.RDS"))

dbGetQuery(db, "SELECT * FROM dpus LIMIT 5") 	

dpus <- dbi_table(db, "dpus") 
dhus <- dbi_table(db, "dhus")

# cdata::qlook(db, dpus$table_name)

# view(rsummary(db, dpus$table_name)) 	




target_emp_levs <- c(
  "Employee of a private for-profit company or busine",
  "Employee of a private not-for-profit, tax-exempt, ",
  "Federal government employee",                    
  "Local government employee (city, county, etc.)",   
  "Self-employed in own incorporated business, profes",
  "Self-employed in own not incorporated business, pr",
  "State government employee")

scllevs <- c(
  "Associate's degree",
  "Bachelor's degree",
  "Doctorate degree",
  "Master's degree",
  "Professional degree beyond a bachelor's degree")

optree <- dpus %.>%
  select_columns(., qc(AGEP, COW, ESR,  PERNP, 
                       PINCP, SCHL, SEX, WKHP)) %.>%
  sql_expr_set(., qc(AGEP, PERNP, PINCP, WKHP),
            "CAST(. AS DECIMAL)") %.>%
  count_null_cols(., NULL, "n_nulls") %.>%
  select_rows_nse(., n_nulls==0) %.>%
  sql_node(., "COW" := "SUBSTR(COW, 1, 50)") %.>%
  set_indicator(., "COW_SEL", "COW", target_emp_levs) %.>%
  select_rows_se(., "(PINCP>1000) & 
           (ESR==\"Civilian employed, at work\") & 
           (PINCP<=250000) & 
           (PERNP>1000) & (PERNP<=250000) & 
           (WKHP>=30) & 
           (AGEP>=18) & (AGEP<=65) & 
           (NOT (is.na(COW))) &
           (COW_SEL==1)") %.>%
  set_indicator(., "SCHL_SEL", "SCHL", scllevs) %.>%
  extend_se(., "SCHL" := "ifelse(is.na(SCHL) | (SCHL_SEL==0), \"No Advanced Degree\", SCHL)") %.>%
  drop_columns(., qc(COW_SEL, SCHL_SEL))

cat(format(optree))

optree %.>%
  op_diagram(.) %.>% 
  DiagrammeR::grViz(.)

d <- materialize(db, optree)
dL <- execute(db, optree)

cdata::qlook(db, d$table_name)

stree <- d %.>%
  project_nse(., 
             mean_income = AVG(PINCP), 
             groupby = qc(SCHL, SEX)) %.>%
  orderby(., qc(SCHL, SEX))
execute(db, stree)

# bring data from database to R
dpus <- execute(db, optree)

dpus$SCHL <- relevel(factor(dpus$SCHL), 
                     "No Advanced Degree")
dpus$COW <- relevel(factor(dpus$COW), 
                    target_emp_levs[[1]])
dpus$SEX <- relevel(factor(dpus$SEX), 
                    "Male")

set.seed(2019)
is_train <- runif(nrow(dpus))>=0.2
dpus_train <- dpus[is_train, , drop = FALSE]
dpus_test <- dpus[!is_train, , drop = FALSE]

model <- lm(PINCP ~ AGEP + COW + SCHL + SEX, 
            data = dpus_train)
summary(model)

dpus_test$predicted_income <- predict(model,
                                      newdata = dpus_test)
WVPlots::ScatterHist(dpus_test, "predicted_income", "PINCP",
                     "PINCP as function of predicted income on held-out data",
                     smoothmethod = "identity",
                     contour = TRUE)

DBI::dbDisconnect(db)
```