BQJ75A9p

1library("civis.r.client")
2library("dplyr")
3library("tidyr")
4
5#this stuff creates the underlying table that would get used
6query <- "drop table if exists eross.census_zip; create table eross.census_zip as
7select state_code, zip, tsmart_census_block_fips, count from
8(select *, row_number() over (partition by state_code, tsmart_census_block_fips order by count desc)
9from
10(select distinct state_code, zip, tsmart_census_block_fips, count(*)
11from ts.basic_commercial
12where tsmart_census_block_fips IS NOT NULL and zip IS NOT NULL and is_dupe=0
13group by 1, 2, 3
14order by 1, 2, 3)
15order by 1, 2, 3)
16where row_number=1"
17
18query_civis(query,"redshift-general")
19
20path <- "/Users/civisemployee/Desktop/geo_tables.csv"
21col <- read.csv(file=path, header=TRUE, sep=",")
22as.data.frame(col)
23
24using <- col %>% dplyr::filter(!is.na(Survey.Name)) %>% dplyr::filter(Survey.Name != '') %>% dplyr::arrange(Order)
25
26columns <- using %>% select(one_of(c("Name","Inverse"))) %>% 
27  mutate(value = ifelse(Inverse=="abs(.5-avg)",paste("abs(.5-",Name,")",sep=""),paste(Name))) %>%
28  mutate(dir = ifelse(Inverse=="X","asc","desc"))
29
30avg <- paste(", avg(",columns$Name,") as ",columns$Name,sep="",collapse="")
31final <- paste(", ",columns$value," as ",columns$Name,sep="",collapse="")
32
33q <- paste("select state_code, zip",final," from
34           (select state_code, zip",avg,
35           " from eross.census_zip a
36             join geo_tables.main b on a.state_code=b.geo_state_code AND a.tsmart_census_block_fips=b.geo_blocks_id
37             group by 1, 2)",sep="")
38
39df <- read_civis(sql=q,database="redshift-general")
40
41
42#now we're going to create dataframe with deciles
43decile <- df[1:2] 
44
45for (i in 3:ncol(df)) {
46
47  string <- as.character(columns$Name[i-2])
48  dir <- as.character(columns$dir[i-2])
49  
50if (dir=="asc") {  
51  
52  decile <- decile %>%
53    mutate(string = dplyr::ntile(df[[i]], 10))
54} else {
55  decile <- decile %>%
56    mutate(string = dplyr::ntile(dplyr::desc(df[[i]]), 10))
57}
58  
59  colnames(decile)[i] <- string
60}
61
62
63#this is the stuff we would do live
64import <- imports_create_run(id=5078765)
65
66gdoc <- read_civis(tablename="scratch.sciencefair_responses",database="redshift-general")
67max_row <- nrow(gdoc)
68gdoc_ <- gdoc[max_row,] %>% select(-timestamp)
69
70#now let's start kicking zip codes out
71fd <- decile
72for (i in 1:(ncol(df)-2)) 
73  {
74  j = i+2
75response <- as.character(gdoc_[1,i])
76if(response=="Very Important") {
77fd <- fd %>% dplyr::filter(fd[j]>=7)
78}
79if(response=="Somewhat Important") {
80fd <- fd %>% dplyr::filter(fd[j]>=5)
81}
82
83print(paste(i,colnames(gdoc_[i]),response,": ",nrow(fd)))
84
85}