---
# System prepended metadata

title: Interacting with Google Big Query
tags: [Arizona Voter Project]

---

# Voter Project Documentation
The combined data is stored <a href="[your_url_here](https://www.dropbox.com/scl/fo/t1ivhinoc2byyo6v2vcm4/h?rlkey=c7j0qs6l19ceguhlyppgmppwv&dl=0)">here</a>. Every time I receive data, I drop it to this folder. 


The only dataset that is relevant to the analysis presented thus far is **AVPv2**

The data are then "cleaned" with the following python code:

```{python}
# Geo Preprocessing
import pandas as pd
import numpy as np
import os
import glob
import re
import datetime
from datetime import date

path = r"/Users/Chris/Dropbox/batch_upload/"
os.chdir(path)
csv_files = glob.glob(os.path.join(path, "*.csv"))

regInfo = ['Registrant ID', 'Registration Date',
           'Effective Date of Change',
           'Year of Birth',
           'LastName',
           'FirstName',
           'MiddleName', 'Suffix', 'Status', 'Status Reason', 'County',
           'HouseNumber', 'StreetPrefix', 'StreetName', 'StreetType',
           'StreetSuffix', 'UnitType', 'UnitNumber', 'Residence Address',
           'Residence City', 'Residence State', 'Residence Zip',
           'MailingAddress',
           'MailingAddress2', 'MailingCity', 'MailingState', 'MailingZip',
           'MailingCountry', 'Party', 'PEVL', 'Phone', 'Occupation',
           'PrecinctPart', 'Congressional', 'Board of Supervisors', 'Legislative',
           'Municipal', 'School', 'Fire', 'Precinct']


electionList = ['03/22/2016-2016 PRES PREF', '08/30/2016-2016 PRIMARY ELECTION',
                '11/08/2016-2016 GENERAL ELECTION', '08/28/2018-2018 PRIMARY ELECTION',
                '11/06/2018-2018 GENERAL ELECTION', '03/17/2020-2020 Presidential Preference', '08/04/2020-PRIMARY 2020',
                '11/03/2020-GENERAL 2020', '08/02/2022-2022 PRIMARY ELECTION',
                '08/02/2022-PRIMARY 2022', '11/08/2022-GENERAL 2022']


# counties = ['Apache', 'Cochise', 'Coconino', 'Gila', 'Graham', 'Greenlee', 'La Paz',
#             'Maricopa', 'Mohave', 'Navajo', 'Pima', 'Pinal', 'Santa Cruz', 'Yavapai', 'Yuma']


def check_header(df, electionList, regInfo):
    if "Registrant County" in df.columns:
        df.rename(columns={"Registrant County": "County"}, inplace=True)
    combined = regInfo + electionList
    columnList = regInfo + electionList
    columns_present = list(set(df.columns).intersection(columnList))
    df = df[columns_present]
    for election in combined:
        if election not in df.columns:
            df[election] = np.nan
        else:
            df = df
    valid_columns = []
    for column in df.columns:
        column = re.sub(r'[^a-zA-Z0-9_]', '_', column)
        column = re.sub(r'^\d+', '_', column)
        valid_columns.append(column)
    df.columns = valid_columns
    sorted_columns = sorted(df.columns)
    return df[sorted_columns]


for f in csv_files:
    path_name = os.path.split(f)[-1]
    match = re.search(r'(\d{4}-\d{2}-\d{2})', path_name)
    date = datetime.datetime.strptime(match.group(), '%Y-%m-%d').date()
    df = pd.read_csv(f, low_memory=False)
    df = check_header(df, electionList, regInfo)
    df["build_date"] = date
    df = df.astype(str)
    df.to_csv("/Users/Chris/Dropbox/batch_upload/fix_headers/c" + path_name)



```
The cleaned data are then uploaded to Google Cloud Storage, they're then turned into a table in Big Query called **combined_0724** Big query is a data warehouse. It's relatively easy to move large datasets around here. It's also easy to query them using standard SQL queries.

## Geocoding

```sql
CREATE OR REPLACE TABLE `az-voter-file.Data_AVP_001.masterRegistrants`AS(
SELECT
  registrant_id, 
  (Residence_Address || ', ' || Residence_City || ', ' || County|| ', ' ||  Residence_State || ', ' || Residence_Zip) AS addressInVote,
FROM
  `az-voter-file.Data_AVP_001.combined_0724`
  WHERE status = "Active"
);
CREATE OR REPLACE TABLE `az-voter-file.Data_AVP_001.masterAddresses`AS(
SELECT
  distinct(addressInVote),
FROM
  `az-voter-file.Data_AVP_001.masterRegistrants`
);


```

## Download, Save, Use ArcGiS
```
os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = XXX
#import tensorflow as tf
import os
import pandas as pd
from datetime import datetime
import pandas_gbq
from google.cloud import bigquery
from sklearn.preprocessing import MinMaxScaler
import pandas_gbq


query = """
SELECT
 *
FROM
  `az-voter-file.Data_AVP_001.masterAddresses`
"""
df = pandas_gbq.read_gbq(query, project_id="az-voter-file")
df.head()
df.to_csv('to_geocode.csv') ## For later load, not to sync.
```

After geocoding, I upload back to GCP, BQ and create a table called *geoCoded0724*


# Pre Machine Learning
---
#### <span style="color: red;">External Data</span>. Files uploaded to GCP and then manually created in BQ. 
#### <span style="color: grey;">Views</span> : Big Query views save storage by not actually saving a table. 
#### <span style="color: black;">Tables</span> : Queries that create tables.
#### <span style="color: purple;">Functions</span> : Table functions.

-----

* <span style="color: red;">combined_0724</span>: This is the "rawest form of the data. It shouldn't be altered. It's built from the CSV files." From here, 

* <span style="color: grey;">masterRegistrants</span> : All grey entries indicate a "view" that is created in BQ. This view creates a set of master addresses from the <span style="color: red;">combined_0724</span> data

* <span style="color: grey;">masterAddresses</span> :This view creates a set of master addresses from the <span style="color: grey;">masterRegistrants</span> data.

* From here, I download the address file and geocode in ArcGIS. I then upload the file back to GCP and Big Query, to a file called <span style="color: red;">geocoded_0724</span>

* <span style="color: purple;">fullData</span> :This is a table. It's the full dataset, with addresses. It creates a set of master addresses from the <span style="color: red;">combined_0724</span> data

* <span style="color: grey;">fullDataGeo</span> :This is a view. It's the full dataset, with addresses. It creates a set of master addresses from the <span style="color: red;">combined_0724</span> data

* <span style="color: pink;">comp</span> :This is a function. It processes movement to different addresses. 

* <span style="color: grey;">changeAddress</span> :This view generates a series of comparisons, then a table from the <span style="color: pink;">comp</span> function.

* <span style="color: grey;">oldVote</span> :This view pulls in observations from older elections. They aren't in the most recent build of the data.

* <span style="color: grey;">voteDataActionable</span> :This view merges older observations onto the newer data table.

* <span style="color: grey;">voteDataActionablePr</span> :This view geocodes voters into precincts. This isn't altogether necessary, except to make comparisons over time.

* <span style="color: black;">goldAddressError</span> :This corrects incorrectly geocoded stuff. It's more or less a utility, in that it creates fields that geocode people into districts, for comparisons to the state.

* <span style="color: grey;">dataForMLraw</span> :This just cleans and adds fields form the <span style="color: black;">goldAddressError</span> table.

* <span style="color: black;">dataForML</span> : Grabs some data from the census and creates a penultimate file for analysis, ML, etc.

------


```sql=
``This is costly
  CREATE OR REPLACE TABLE `az-voter-file.AVPv2.pcd` AS (
  WITH precinct as (
  SELECT * except(geo), SAFE.ST_GEOGFROMGEOJSON(geo) as geo FROM `az-voter-file.shapes.geoPrecincts` where counter IS NOT NULL and precinctName != "id"
  ),
  cds as (
  SELECT counter, id as CD, SAFE.ST_GEOGFROMGEOJSON(geo) as geo FROM `az-voter-file.shapes.geoCD` where counter IS NOT NULL and id != "id"
  )
  SELECT
  cds.CD,
  precinct.precinctName,
  FROM cds JOIN precinct ON
  ST_INTERSECTS(cds.geo, precinct.geo )
  );
  CREATE OR REPLACE TABLE `az-voter-file.AVPv2.pld` AS (
  WITH precinct as (
  SELECT * except(geo), SAFE.ST_GEOGFROMGEOJSON(geo) as geo FROM `az-voter-file.shapes.geoPrecincts` where counter IS NOT NULL and precinctName != "id"
  ),
  lds as (
  SELECT counter, LD, SAFE.ST_GEOGFROMGEOJSON(geo) as geo FROM `az-voter-file.shapes.geoLDs` where counter IS NOT NULL and LD != "id"
  )
  SELECT
  lds.LD,
  precinct.precinctName,
  FROM lds JOIN precinct ON
  ST_INTERSECTS(lds.geo, precinct.geo )
  );
  CREATE OR REPLACE TABLE `az-voter-file.AVPv2.bld` AS (
  WITH blocks as (
  SELECT *  FROM `bigquery-public-data.geo_census_blockgroups.blockgroups_04`
  ),
  lds as (
  SELECT counter, LD, SAFE.ST_GEOGFROMGEOJSON(geo) as geo FROM `az-voter-file.shapes.geoLDs` where counter IS NOT NULL and LD != "id"
  )
  SELECT
  lds.LD,
  blocks.geo_id,
  FROM lds JOIN blocks ON
  ST_INTERSECTS(lds.geo, blocks.blockgroup_geom )
  );
  CREATE OR REPLACE TABLE `az-voter-file.AVPv2.bcd` AS (
  WITH blocks as (
  SELECT *  FROM `bigquery-public-data.geo_census_blockgroups.blockgroups_04`
  ),
  cds as (
  SELECT counter, id as CD, SAFE.ST_GEOGFROMGEOJSON(geo) as geo FROM `az-voter-file.shapes.geoCD` where counter IS NOT NULL and id != "id"
  )
  SELECT
  cds.CD,
  blocks.geo_id,
  FROM cds JOIN blocks ON
  ST_INTERSECTS(cds.geo, blocks.blockgroup_geom )
  );
  CREATE OR REPLACE VIEW
    `az-voter-file.AVPv2.masterRegistrants` AS(
    SELECT
      registrant_id,
      (Residence_Address || ', ' || Residence_City || ', ' || County|| ', ' || Residence_State || ', ' || Residence_Zip) AS addressInVote,
    FROM
      `az-voter-file.AVPv2.combined_1104` );
  CREATE OR REPLACE VIEW
    `az-voter-file.AVPv2.masterAddresses`AS(
    SELECT
      DISTINCT(addressInVote),
    FROM
      `az-voter-file.AVPv2.masterRegistrants` );
   CREATE OR REPLACE VIEW
    `az-voter-file.AVPv2.masterAddresses`AS(
    SELECT
      DISTINCT(addressInVote),
    FROM
      `az-voter-file.AVPv2.masterRegistrants` );
  ----------
  -- --   (3)
  -- ----------
  CREATE OR REPLACE VIEW
    `az-voter-file.AVPv2.fullData`AS(
    SELECT
      *,
      (Residence_Address || ', ' || Residence_City || ', ' || County|| ', ' || Residence_State || ', ' || Residence_Zip) AS addressInVote,
      Registrant_ID AS registrantID
    FROM
      `az-voter-file.AVPv2.combined_1104` );
    CREATE OR REPLACE VIEW
    `az-voter-file.AVPv2.fullDataGeo`AS(
    SELECT
      SAFE_CAST(Score1 AS FLOAT64) AS scores,
      a.Match_type1 AS matchType,
      SAFE_CAST(X1 AS float64) AS longitude,
      SAFE_CAST(Y1 AS float64) AS latitude,
      IN_Address1,
      b.*,
    FROM
      `az-voter-file.AVPv2.geocoded_1104` AS a
    RIGHT JOIN
      `az-voter-file.AVPv2.fullData` AS b
    ON
      a.In_Address1 = b.addressInVote
    WHERE
      b.Status = "Active" AND
      BUILD_DATE = "2023-07-03");


CREATE OR REPLACE VIEW
  `az-voter-file.AVPv2.oldVote`AS(
  SELECT
    Registrant_ID AS registrantID,
    __08_2016_2016_GENERAL_ELECTION AS general_2016,
    __06_2018_2018_GENERAL_ELECTION AS general_2018,
    __28_2018_2018_PRIMARY_ELECTION AS primary_2018,
    __30_2016_2016_PRIMARY_ELECTION AS priamry_2016,
  FROM
    `az-voter-file.AVPv2.fullData`
  WHERE
    build_date = '2022-09-30'
    AND Status = "Active" );
  -- Combine the two, this is called voteDataActionable.,
CREATE OR REPLACE VIEW
  `az-voter-file.AVPv2.voteDataActionable`AS(
  SELECT
    a.* EXCEPT(registrantID),
    b.*
  FROM
    `az-voter-file.AVPv2.oldVote` AS a
  RIGHT JOIN
    `az-voter-file.AVPv2.fullDataGeo` AS b
  ON
    a. registrantID = b.registrantID );
CREATE OR REPLACE VIEW
  `az-voter-file.AVPv2.voteDataActionablePr`AS(
  SELECT
    *,
    precinct AS GeoPrecinct
  FROM
    `az-voter-file.AVPv2.voteDataActionable` );
CREATE OR REPLACE TABLE
  `az-voter-file.AVPv2.goldAddressError`AS(
  SELECT
    *,
    bg.geo_id AS census_block,
    ST_GEOGPOINT(SAFE_CAST(longitude AS float64), SAFE_CAST(latitude AS float64) ) AS location_in_address_file,
  FROM
    `az-voter-file.AVPv2.voteDataActionablePr`
  CROSS JOIN
    `bigquery-public-data.geo_census_blockgroups.blockgroups_04` AS bg
  WHERE
    ST_CONTAINS(bg.blockgroup_geom, ST_GEOGPOINT(SAFE_CAST(longitude AS float64), SAFE_CAST(latitude AS float64) )) );

CREATE OR REPLACE VIEW
  `az-voter-file.AVPv2.dataForMLraw` AS (
  SELECT
    *,
    2022 - CAST(year_of_birth AS int) AS respondent_age,
    CAST(PEVL AS string) AS early_list,
    CAST(REGEXP_EXTRACT(congressional, r"([0-9]+)") AS string) AS CD,
    CAST(REGEXP_EXTRACT(legislative, r"([0-9]+)") AS string) AS LD,
    CASE
      WHEN party = "REP" THEN "Republican (REP)"
      WHEN party = "DEM" THEN "Democrat (DEM)"
    ELSE
    "Independent"
  END
    AS party_identification,
    CASE
      WHEN `priamry_2016` IS NOT NULL and `priamry_2016` != "nan" THEN 1
    ELSE
    0
  END
    AS PRIMARY_e2016,
    CASE
      WHEN `general_2016` IS NOT NULL and `general_2016` != "nan" THEN 1
    ELSE
    0
  END
    AS GENERAL_e2016,
    CASE
      WHEN `primary_2018` IS NOT NULL AND  `primary_2018` != "nan" THEN 1
    ELSE
    0
  END
    AS PRIMARY_e2018,
    CASE
      WHEN `general_2018` IS NOT NULL and `general_2018` != "nan" THEN 1
    ELSE
    0
  END
    AS GENERAL_e2018,
    CASE
      WHEN `__04_2020_PRIMARY_2020` IS NOT NULL and `__04_2020_PRIMARY_2020` != "nan" THEN 1
    ELSE
    0 END
    AS PRIMARY_e2020,
    CASE
      WHEN `__03_2020_GENERAL_2020` IS NOT NULL  and `__03_2020_GENERAL_2020` != "nan" THEN 1
    ELSE
    0
  END
    AS GENERAL_e2020,
    CASE
      WHEN `__02_2022_PRIMARY_2022` IS NOT NULL AND `__02_2022_PRIMARY_2022` != "nan" THEN 1
    ELSE
    0
  END
    AS PRIMARY_e2022,
    CASE
      WHEN `__08_2022_GENERAL_2022` IS NOT NULL AND `__08_2022_GENERAL_2022` != "nan" THEN 1
    ELSE
    0
  END
    AS GENERAL_e2022,
    COALESCE(SAFE_CAST(REGEXP_CONTAINS(`__08_2022_GENERAL_2022`, "E") AS int64), 0) AS early_voter,
    COALESCE(CAST(REGEXP_CONTAINS(`__08_2022_GENERAL_2022`, "A") AS int64), 0) AS absentee_voters,
    COALESCE(CAST(REGEXP_CONTAINS(`__08_2022_GENERAL_2022`, "PV") AS int64), 0) AS provisional_voters,
    COALESCE(CAST(REGEXP_CONTAINS(`__08_2022_GENERAL_2022`, "P") AS int64), 0) AS polling_voters,
    COALESCE(CAST(REGEXP_CONTAINS(`priamry_2016`, "REP") AS int64), 0) AS Republican_Primary_2016,
    COALESCE(CAST(REGEXP_CONTAINS(`priamry_2016`, "REP") AS int64), 0) AS Republican_Primary_2018,
    COALESCE(CAST(REGEXP_CONTAINS(`__04_2020_PRIMARY_2020`, "REP") AS int64), 0) AS Republican_Primary_2020,
    COALESCE(CAST(REGEXP_CONTAINS(`__02_2022_PRIMARY_2022`, "REP") AS int64), 0) AS Republican_Primary_2022,
    COALESCE(CAST(REGEXP_CONTAINS(`primary_2018`, "DEM") AS int64), 0) AS Democratic_Primary_2016,
    COALESCE(CAST(REGEXP_CONTAINS(`primary_2018`, "DEM") AS int64), 0) AS Democratic_Primary_2018,
    COALESCE(CAST(REGEXP_CONTAINS(`__04_2020_PRIMARY_2020`, "DEM") AS int64), 0) AS Democratic_Primary_2020,
    COALESCE(CAST(REGEXP_CONTAINS(`__02_2022_PRIMARY_2022`, "DEM") AS int64), 0) AS Democratic_Primary_2022,
  FROM
    `az-voter-file.AVPv2.goldAddressError` );

-----

  CREATE OR REPLACE TABLE
  `az-voter-file.AVPv2.dataForML`AS(
  SELECT
    a.*,
    b.bachelors_degree,
    b.median_age,
    b.median_income,
    b.white_pop,
    b.black_pop,
    b.asian_pop,
    b.amerindian_pop,
    b.hispanic_pop,
    b.housing_units,
    b.armed_forces,
    b.employed_pop,
    b.pop_in_labor_force,
    b.total_pop,
    b.mobile_homes,
  FROM
    `az-voter-file.AVPv2.dataForMLraw` AS a
  LEFT JOIN
    `bigquery-public-data.census_bureau_acs.blockgroup_2018_5yr` AS b
  ON
    a.census_block = b.geo_id );



-- CREATE OR REPLACE TABLE
--   FUNCTION `az-voter-file.AVPv2.comp`(d1 STRING,
--     d2 STRING) AS(
--   WITH
--     fullGeo AS(
--     SELECT
--       SAFE_CAST(Score AS FLOAT64) AS scores,
--       a.Match_type AS matchType,
--       SAFE_CAST(a.X AS float64) AS longitude,
--       SAFE_CAST(a.Y AS float64) AS latitude,
--       ST_GEOGPOINT(SAFE_CAST(X AS float64), SAFE_CAST(Y AS float64)) AS geoPoint,
--       b.*,
--     FROM
--       `az-voter-file.AVPv2.geoCoded_0724` AS a
--     RIGHT JOIN
--       `az-voter-file.AVPv2.fullData` AS b
--     ON
--       a.USER_addressInVote = b.addressInVote),
--     tableA AS (
--     SELECT
--       longitude,
--       latitude,
--       Precinct,
--       Registrant_ID AS id,
--       (Residence_Address || ', ' || Residence_City || ', ' || County|| ', ' || Residence_State || ', ' || Residence_Zip) AS address,
--     FROM
--       fullGeo
--     WHERE
--       build_date = d1
--       AND Status = "Active"),
--     tableB AS (
--     SELECT
--       longitude,
--       latitude,
--       Precinct,
--       Registrant_ID AS id,
--       (Residence_Address || ', ' || Residence_City || ', ' || County|| ', ' || Residence_State || ', ' || Residence_Zip) AS address,
--     FROM
--       fullGeo
--     WHERE
--       build_date = d2
--       AND Status = "Active")
--   SELECT
--     a.*,
--     CASE
--       WHEN a.address = b.address THEN 'no change'
--     ELSE
--     b.address
--   END
--     AS new_address,
--     CASE
--       WHEN a.longitude = b.longitude THEN NULL
--     ELSE
--     b.longitude
--   END
--     AS new_longitude,
--     CASE
--       WHEN a.latitude = b.latitude THEN NULL
--     ELSE
--     b.latitude
--   END
--     AS new_latitude,
--   FROM
--     tableA AS a
--   JOIN
--     tableB AS b
--   ON
--     a.id = b.id );
--   ----------
-- --   (6)
-- ----------

-- CREATE OR REPLACE VIEW
--   `az-voter-file.AVPv2.changeAddress`AS (
--   SELECT
--     a.id,
--     a.longitude AS longitudeRecent,
--     a.latitude AS latitudeRecent,
--     a.address AS address_recent,
--     b.new_address AS c1122,
--     b.new_latitude AS latc1122,
--     b.new_longitude AS lonc1122,
--     c.new_address AS c0922,
--     c.new_latitude AS latc0922,
--     c.new_longitude AS lonc0922,
--     d.new_address AS c0522,
--     d.new_latitude AS latc0522,
--     d.new_longitude AS lonc0522,
--     e.new_address AS c0421,
--     e.new_latitude AS latc0421,
--     e.new_longitude AS lonc0421,
--     f.*
--   FROM
--     `az-voter-file.Data_AVP_001.comp`('2023-03-31',
--       '2022-11-04') AS a
--   LEFT JOIN
--     `az-voter-file.Data_AVP_001.comp`('2023-03-31',
--       '2022-10-28') AS b
--   ON
--     a.id = b.id
--   LEFT JOIN
--     `az-voter-file.Data_AVP_001.comp`('2023-03-31',
--       '2022-09-30') AS c
--   ON
--     a.id = c.id
--   LEFT JOIN
--     `az-voter-file.Data_AVP_001.comp`('2023-03-31',
--       '2022-05-03') AS d
--   ON
--     a.id = d.id
--   LEFT JOIN
--     `az-voter-file.Data_AVP_001.comp`('2023-03-31',
--       '2021-04-28') AS e
--   ON
--     a.id = e.id
--   RIGHT JOIN
--     `az-voter-file.AVPv2.fullDataGeo` AS f
--   ON
--     a.id = f.Registrant_ID );
----------
--   (7)
----------

  ```
 
 
 # Download Data, Analysis
 Use VSCode or Rstudio or something or whatever to pull in to local machine. This file is called *download.py*
  
  
  ## Data
  
  The data can be pulled from a BQ table. I do this here, and save the files to my machine. 
 ```{python}
# Formulate the SQL query to pull the data from BigQuery

# import tensorflow as tf
import os
import pandas as pd
from datetime import datetime
import pandas_gbq
from google.cloud import bigquery
from sklearn.preprocessing import MinMaxScaler
import pandas_gbq
os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = "/Users/Chris/Dropbox/Keys/az-voter-file-30395362c45b.json"

query = """
SELECT
  Registrant_ID,
  Year_of_Birth,
  Effective_Date_of_Change,
  census_block,
  CD,
  LD,
  party_identification,
  GENERAL_e2016,
  GENERAL_e2016,
  GENERAL_e2018,
  GENERAL_e2020,
  PRIMARY_e2016,
  PRIMARY_e2018,
  PRIMARY_e2020,
  PRIMARY_e2022,
  GENERAL_e2022,
  early_voter,
  absentee_voters,
  provisional_voters,
  polling_voters,
  Republican_Primary_2022,
  Democratic_Primary_2022,
  Republican_Primary_2018,
  Democratic_Primary_2018,
  SAFE_DIVIDE(bachelors_degree, total_pop) as bachelors_degree,
  median_age,
  median_income,
  County,
  SAFE_DIVIDE(white_pop,total_pop) as white_pop,
  SAFE_DIVIDE(hispanic_pop,total_pop) as hispanic_pop,
  SAFE_DIVIDE(employed_pop,total_pop) as employed_pop,
  SAFE_DIVIDE(black_pop, total_pop) as black_pop,
  SAFE_DIVIDE(asian_pop, total_pop) as asian_pop,
  SAFE_DIVIDE(mobile_homes, total_pop) as mobile_homes,
  SAFE_DIVIDE(amerindian_pop, total_pop) as amerindian_pop,
FROM
  `az-voter-file.AVPv2.dataForML`
"""


df = pandas_gbq.read_gbq(query, project_id="az-voter-file")
df.head()


# For later load, not to sync.
df.to_pickle('/Users/Chris/Dropbox/masterData/voterFile/voterActionable.pkl')
df.to_csv('/Users/Chris/Dropbox/masterData/voterFile/voterActionable.csv')  #

 ```
 
 ## Building a Voter Engagement Score
Process the data and run a latent variable model. This file is called **latent.r**
 
 ```{r}
library(lavaanPlot)
library(lavaan)
library(semTools)
library(dplyr)
library(brms)
library(ggplot2)
library(tidybayes)
library(cowplot)

df <- read.csv("/Users/Chris/Dropbox/github_repos/the_az_voter_project/the_az_voter_project/wrangling/voter_file.csv")
head(df)
# # Fit Mimic Model
df$Republican <- ifelse(df$party_identification == "Republican (REP)", 1, 0)
df$Democratic <- ifelse(df$party_identification == "Democrat (DEM)", 1, 0)
df$power <- rowSums(cbind(
  df$GENERAL_e2016, df$GENERAL_e2018, df$PRIMARY_e2016,
  df$PRIMARY_e2018, df$GENERAL_e2020, df$PRIMARY_e2020
), na.rm = TRUE)
df$rep_leaner <- ifelse((df$Republican_Primary_2022 == 1), 1, 0)
df$dem_leaner <- ifelse((df$Democratic_Primary_2022 == 1), 1, 0)

# factor_vars <- c("GENERAL_e2016", "GENERAL_e2018", "GENERAL_e2020")
# paste0(paste0(factor_vars, collapse = " + "), " + Rep:", paste0(factor_vars, collapse = " + Republican*"), " + ", "Democratic:", paste0(factor_vars, collapse = " + Democratic*"))

# Misc Functions
zeroOne <- function(x) {
  min.x <- min(x, na.rm = T)
  max.x <- max(x - min.x, na.rm = T)
  return((x - min.x) / max.x)
}
sem_model <- function(data, factor_vars, name = "general") {
  model <- paste0("
    f0 =~ ", paste0(factor_vars, collapse = " + "), "\n", "f0 ~ Republican + Democratic", "\n")

  fit <- sem(model, data = data)

  ids <- lavInspect(fit, "case.idx")
  factor.scores <- lavPredict(fit)
  for (fs in colnames(factor.scores)) {
    data[, fs] <- factor.scores[, fs]
  }
  # df$primary_voter = zeroOne(df$primary_voter)
  data[[name]] <- zeroOne(data$f0)
  data <- data %>% select(-f0)
  return(data)
}


factor_vars <- c("GENERAL_e2016", "GENERAL_e2018", "GENERAL_e2020", "GENERAL_e2022")
df <- sem_model(data = df, factor_vars = factor_vars, name = "generalV")
factor_vars <- c("GENERAL_e2016", "GENERAL_e2018", "GENERAL_e2020")
df <- sem_model(data = df, factor_vars = factor_vars, name = "generalLag")
factor_vars <- c("PRIMARY_e2016", "PRIMARY_e2018", "PRIMARY_e2020", "PRIMARY_e2022")
df <- sem_model(data = df, factor_vars = factor_vars, name = "primaryV")
factor_vars <- c("PRIMARY_e2016", "PRIMARY_e2018", "PRIMARY_e2020")
df <- sem_model(data = df, factor_vars = factor_vars, name = "primaryLag")


df %>%
  group_by(census_block) %>%
  mutate(voterCount = n()) %>%
  mutate(blockDem = mean(Democratic)) %>%
  mutate(blockRep = mean(Republican)) %>%
  mutate(blockOther = 1 - blockDem - blockRep) %>%
  mutate(blockDemleaner = mean(dem_leaner)) %>%
  mutate(blockRepleaner = mean(rep_leaner)) %>%
  ungroup() %>%
  write.csv("voter_file_latent_.csv", row.names = FALSE)

```
 
 And then process the data again in python, run some simple ML models using tensorflow, construct the data, and output a file called **eng_pred_localscores.csv**. The python file that runs everything below is called **ml_upload.py**

```{python}
from tensorflow.keras.metrics import Accuracy, Precision, Recall
from tf.keras.optimizers.legacy import Adam
from tensorflow.keras.regularizers import l1_l2
import numpy as np
import tensorflow as tf
import pandas as pd
from sklearn.model_selection import train_test_split
from sklearn.preprocessing import MinMaxScaler
import keras
from keras import layers

dataForML = pd.read_csv(
    '/Users/Chris/Dropbox/github_repos/the_az_voter_project/the_az_voter_project/local_files/voter_file_latent_.csv')


dataForML["Lean_Democrat"] = np.where((dataForML["party_identification"] == 'Independent') & (
    dataForML['Democratic_Primary_2022'] == 1), 1, 0)
dataForML["Lean_Republican"] = np.where((dataForML["party_identification"] == 'Independent') & (
    dataForML['Republican_Primary_2022'] == 1), 1, 0)
dataForML["Defect_to_Democrat"] = np.where((dataForML["party_identification"] == 'Republican (REP)') & (
    dataForML['Democratic_Primary_2022'] == 1), 1, 0)
dataForML["Defect_to_Republican"] = np.where((dataForML["party_identification"] == 'Democrat (DEM)') & (
    dataForML['Republican_Primary_2022'] == 1), 1, 0)
dataForML["Republican"] = np.where(
    (dataForML["party_identification"] == 'Democrat (DEM)'), 1, 0)
dataForML["Democratic"] = np.where(
    (dataForML["party_identification"] == 'Republican (REP)'), 1, 0)
dataForML["age"] = 2022 - dataForML["Year_of_Birth"]
dataForML["voting2022"] = np.where(
    (dataForML["PRIMARY_e2022"] == 1) | (dataForML["GENERAL_e2022"] == 1), 1, 0)
dataForML["primaryVoter"] = np.where((dataForML["PRIMARY_e2022"] == 1) |
                                     (dataForML["PRIMARY_e2020"] == 1) |
                                     (dataForML["PRIMARY_e2018"] == 1) |
                                     (dataForML["PRIMARY_e2016"] == 1), 1, 0)

IDS = ['Registrant_ID']
FEATURES = ["Republican", "Democratic",
            "dem_leaner", "rep_leaner",
            "hispanic_pop", "white_pop",
            "asian_pop", "bachelors_degree",
            "age", 'generalLag', 'primaryLag',
            'median_age', 'median_income',
            'Year_of_Birth']


LABELS = ["GENERAL_e2022"]
dat = dataForML[LABELS + FEATURES + IDS]
dat = dat.dropna(how='any')
registrant_ids = dat[IDS]
dat = dat[LABELS + FEATURES + IDS]
scaler = MinMaxScaler()
dat_array = scaler.fit_transform(dat)
dat = pd.DataFrame(dat_array, columns=dat.columns)


train, test = train_test_split(dat, test_size=0.10)
labels_train = pd.DataFrame(
    {"Voter": train["GENERAL_e2022"],  "notVoter": 1-train["GENERAL_e2022"]})
labels_test = pd.DataFrame(
    {"Voter": test["GENERAL_e2022"],   "notVoter": 1-test["GENERAL_e2022"]})


features_test = test[FEATURES]
features_train = train[FEATURES]
labels_train_array = np.array(labels_train, np.float64)
labels_test_array = np.array(labels_test,   np.float64)
features_train_array = np.array(features_train, np.float64)
features_test_array = np.array(features_test,   np.float64)

features_full = dat[FEATURES]
labels_full = pd.DataFrame(
    {"notVoter": dat["GENERAL_e2022"],  "notVoter": 1-dat["GENERAL_e2022"]})
labels_test = dat[LABELS]

labels_full_array = np.array(labels_full, np.float64)
features_full_array = np.array(features_full, np.float64)


modelNN = tf.keras.Sequential()
# Define the first layer
modelNN.add(keras.layers.Dense(15, activation='softmax',
                               input_shape=(features_train.shape[1],)))
modelNN.add(keras.layers.Dropout(0.25))
modelNN.add(keras.layers.Dense(12, activation='relu'))
modelNN.add(keras.layers.Dropout(0.25))
modelNN.add(keras.layers.Dense(8, activation='relu'))
modelNN.add(keras.layers.Dense(2, activation='softmax'))


# Finish the model compilation
modelNN.compile(optimizer=keras.optimizers.Adam(learning_rate=0.001),
                loss='categorical_crossentropy',
                metrics=['accuracy'])

callback = tf.keras.callbacks.EarlyStopping(monitor='loss', patience=3)
print("TensorFlow version:", tf.__version__)
print("Num GPUs Available: ", len(
    tf.config.experimental.list_physical_devices('GPU')))
tf.config.list_physical_devices('GPU')

modelNN.fit(features_train_array,
            labels_train_array,
            epochs=20,
            batch_size=256,
            validation_split=0.10,
            callbacks=[callback],
            verbose=1)


# Evaluate the model on the test data using `evaluate`
print("Evaluate on test data")
results = modelNN.evaluate(
    features_test_array, labels_test_array, batch_size=128)
print("test loss, test acc:", results)
predictions = modelNN.predict(features_full_array)
predictions = pd.DataFrame(predictions)
predictions.head()


min_max_scalar = MinMaxScaler(feature_range=(-1, 1))

upload_data = pd.DataFrame({"Engaged_Voter_Prediction":  predictions.iloc[:, 0],
                            "Not_Engaged_Voter_Prediction":  predictions.iloc[:, 1],
                            # This will change it's a simulation
                            # Take a binomial draw from the prediction
                            "Registrant_ID": registrant_ids.iloc[:, 0],
                            "Lean_Democrat": dataForML["Lean_Democrat"].astype(int),
                            "Lean_Republican": dataForML["Lean_Republican"].astype(int),
                            "Defect_to_Republican": dataForML["Defect_to_Republican"].astype(int),
                            "primaryVoterScore": dataForML["primaryV"].astype(float),
                            "generalVoterScore": dataForML["generalV"].astype(float),
                            "primaryVoterLScore": dataForML["primaryLag"].astype(float),
                            "generalVoterLScore": dataForML["generalLag"].astype(float)
                            })
upload_data["primaryVoterScore"] = min_max_scalar.fit_transform(
    upload_data[["primaryVoterScore"]])
upload_data["generalVoterScore"] = min_max_scalar.fit_transform(
    upload_data[["generalVoterScore"]])
upload_data["primaryVoterLScore"] = min_max_scalar.fit_transform(
    upload_data[["primaryVoterLScore"]])
upload_data["generalVoterLScore"] = min_max_scalar.fit_transform(
    upload_data[["generalVoterLScore"]])

upload_data.to_csv('eng_pred_localscores.csv')

```

## Writing Data using the Census API

```{python}
import gspread
import re
import pandas as pd
import numpy as np
from google.colab import auth
from oauth2client.client import GoogleCredentials
from census import Census
from us import states

auth.authenticate_user()


c = Census(CENSUS API)
VARS = (["P1_" + str(i + 1).zfill(3) + "N"for i in range(45)] +
        ["P2_" + str(i + 1).zfill(3) + "N"for i in range(3)])

ld = pd.DataFrame.from_dict(c.pl.get((VARS), geo={'for': 'state legislative district (lower chamber):*',
                            'in': 'state:{}'.format(states.AZ.fips)}))
ld = ld.rename(columns={'state legislative district (lower chamber)': 'LD'})

cd = pd.DataFrame.from_dict(c.pl.get(VARS, geo={'for': 'congressional district:*',
                            'in': 'state:{}'.format(states.AZ.fips)}))
cd = cd.rename(columns={'congressional district': 'CD'})

county = pd.DataFrame.from_dict(c.pl.get(VARS, geo={'for': 'county:*',
                                                    'in': 'state:{}'.format(states.AZ.fips)}))
county_tract = pd.DataFrame.from_dict(c.pl.get(VARS, geo={'for': 'tract:*',
                                                          'in': 'state:{}. county:*'.format(states.AZ.fips)}))
county_block = pd.DataFrame.from_dict(c.pl.get(VARS, geo={'for': 'block:*',
                                                          'in': 'state:{}. county:*'.format(states.AZ.fips)}))

project_id = 'az-voter-file'

project_table = 'az-voter-file.Data_AVP_001.ld'
ld.to_gbq(project_table, project_id, chunksize=None, if_exists='replace')

project_table = 'az-voter-file.Data_AVP_001.cd'
cd.to_gbq(project_table, project_id, chunksize=None, if_exists='replace')

project_table = 'az-voter-file.Data_AVP_001.county'
county.to_gbq(project_table, project_id, chunksize=None, if_exists='replace')

project_table = 'az-voter-file.Data_AVP_001.county_tract'
county_tract.to_gbq(project_table, project_id,
                    chunksize=None, if_exists='replace')

project_table = 'az-voter-file.Data_AVP_001.county_block'
county_block.to_gbq(project_table, project_id,
                    chunksize=None, if_exists='replace')

```
* <span style="color: red;">writeCensus.py</span> : This writes census data for analysis: <span style="color: grey;">ld, cd, county, county_block, county_tract</span>

## Data for Visualization and Further Analysis

**eng_pred_localscores.csv** is uploaded to a Google Bucket. I then create a BQ table called <span style="color: red;">fullDataPostML</span>. 

---
#### <span style="color: red;">External Data</span>. Files uploaded to GCP and then manually created in BQ. 
#### <span style="color: grey;">Views</span> : Big Query views save storage by not actually saving a table. 
#### <span style="color: black;">Tables</span> : Queries that create tables.
#### <span style="color: purple;">Functions</span> : Table functions.

-----
 

* <span style="color: black;">data_merged</span> :This is a table. It's the full dataset, with addresses. It creates a set of master addresses from the <span style="color: red;">fullDataPostML</span> data

* <span style="color: grey;">datReduced</span> : I grab some stuff from <span style="color: black;">data_merged</span>, which is rather large -- but we'd like flexibility to download whole thing.

* <span style="color: grey;">centroids</span> : This creates a table of centroids for each census block.

* <span style="color: black;">starTable</span> : This creates a table of counts from the voter data. The counts are by block, county, LD, CD, and party identification.

* <span style="color: black;">starTableCent</span> : The star table with centroids.

### Census Data

* <span style="color: black;">block_aggregates, county_aggregates, ldAggregates, cdAggregates, </span> : These are taken from the census.

### Census Data

* <span style="color: black;">starAggregates</span> : The star table with the summary census information.


```{sql}
  
  CREATE OR REPLACE TABLE
  `az-voter-file.AVPv2.goldAddressError`AS(
    SELECT
      a.*,
      bg.geo_id AS census_block,
      ST_GEOGPOINT(SAFE_CAST(longitude AS float64), SAFE_CAST(latitude AS float64) ) AS location_in_address_file,
    FROM
      `az-voter-file.AVPv2.voteDataActionablePr` as a CROSS JOIN
       `bigquery-public-data.geo_census_blockgroups.blockgroups_04` AS bg
  WHERE
    ST_CONTAINS(bg.blockgroup_geom, ST_GEOGPOINT(SAFE_CAST(a.longitude AS float64), SAFE_CAST(a.latitude AS float64) ))
        );
CREATE OR REPLACE VIEW
  `az-voter-file.AVPv2.dataForMLraw` AS (
  SELECT
    *,
    2022 - CAST(year_of_birth AS int) AS respondent_age,
    CAST(PEVL AS string) AS early_list,
    CAST(REGEXP_EXTRACT(congressional, r"([0-9]+)") AS string) AS CD,
    CAST(REGEXP_EXTRACT(legislative, r"([0-9]+)") AS string) AS LD,
    CASE
      WHEN party = "REP" THEN "Republican (REP)"
      WHEN party = "DEM" THEN "Democrat (DEM)"
    ELSE
    "Independent"
  END
    AS party_identification,
    CASE
      WHEN `priamry_2016` IS NOT NULL THEN 1
    ELSE
    0
  END
    AS PRIMARY_e2016,
    CASE
      WHEN `general_2016` IS NOT NULL THEN 1
    ELSE
    0
  END
    AS GENERAL_e2016,
    CASE
      WHEN `primary_2018` IS NOT NULL THEN 1
    ELSE
    0
  END
    AS PRIMARY_e2018,
    CASE
      WHEN `general_2018` IS NOT NULL THEN 1
    ELSE
    0
  END
    AS GENERAL_e2018,
    CASE
      WHEN `__04_2020_PRIMARY_2020` IS NOT NULL THEN 1
    ELSE
    0
  END
    AS PRIMARY_e2020,
    CASE
      WHEN `__03_2020_GENERAL_2020` IS NOT NULL THEN 1
    ELSE
    0
  END
    AS GENERAL_e2020,
    CASE
      WHEN `__02_2022_PRIMARY_2022` IS NOT NULL THEN 1
    ELSE
    0
  END
    AS PRIMARY_e2022,
    CASE
      WHEN `__08_2022_GENERAL_2022` IS NOT NULL THEN 1
    ELSE
    0
  END
    AS GENERAL_e2022,
    COALESCE(SAFE_CAST(REGEXP_CONTAINS(`__08_2022_GENERAL_2022`, "E") AS int64), 0) AS early_voter,
    COALESCE(CAST(REGEXP_CONTAINS(`__08_2022_GENERAL_2022`, "A") AS int64), 0) AS absentee_voters,
    COALESCE(CAST(REGEXP_CONTAINS(`__08_2022_GENERAL_2022`, "PV") AS int64), 0) AS provisional_voters,
    COALESCE(CAST(REGEXP_CONTAINS(`__08_2022_GENERAL_2022`, "P") AS int64), 0) AS polling_voters,
    COALESCE(CAST(REGEXP_CONTAINS(`priamry_2016`, "REP") AS int64), 0) AS Republican_Primary_2016,
    COALESCE(CAST(REGEXP_CONTAINS(`priamry_2016`, "REP") AS int64), 0) AS Republican_Primary_2018,
    COALESCE(CAST(REGEXP_CONTAINS(`__04_2020_PRIMARY_2020`, "REP") AS int64), 0) AS Republican_Primary_2020,
    COALESCE(CAST(REGEXP_CONTAINS(`__02_2022_PRIMARY_2022`, "REP") AS int64), 0) AS Republican_Primary_2022,
    COALESCE(CAST(REGEXP_CONTAINS(`primary_2018`, "DEM") AS int64), 0) AS Democratic_Primary_2016,
    COALESCE(CAST(REGEXP_CONTAINS(`primary_2018`, "DEM") AS int64), 0) AS Democratic_Primary_2018,
    COALESCE(CAST(REGEXP_CONTAINS(`__04_2020_PRIMARY_2020`, "DEM") AS int64), 0) AS Democratic_Primary_2020,
    COALESCE(CAST(REGEXP_CONTAINS(`__02_2022_PRIMARY_2022`, "DEM") AS int64), 0) AS Democratic_Primary_2022,
  FROM
    `az-voter-file.AVPv2.goldAddressError` );
----------
--   (11)
----------

  CREATE OR REPLACE TABLE
  `az-voter-file.AVPv2.dataForML`AS(
  SELECT
    a.*,
    b.bachelors_degree,
    b.median_age,
    b.median_income,
    b.white_pop,
    b.black_pop,
    b.asian_pop,
    b.amerindian_pop,
    b.hispanic_pop,
    b.housing_units,
    b.armed_forces,
    b.employed_pop,
    b.pop_in_labor_force,
    b.total_pop,
    b.mobile_homes,
  FROM
    `az-voter-file.AVPv2.dataForMLraw` AS a
  LEFT JOIN
    `bigquery-public-data.census_bureau_acs.blockgroup_2018_5yr` AS b
  ON
    a.census_block = b.geo_id 
    WHERE build_date = "2023-03-31");

--  -- - Post ML Upload --
--       Construct the census blocks from the neural networks
--       This creates an aggregated score.
   CREATE OR REPLACE TABLE
     `az-voter-file.AVPv2.data_merged` AS(
      with tableA as (
    SELECT
       *
     FROM  `az-voter-file.AVPv2.fullDataPostML`
  ),
  tableB as(
    SELECT
  *
  FROM  `az-voter-file.AVPv2.dataForML`
  WHERE build_date = "2023-03-31"
  )
  SELECT
  tableA.*,
  tableB.* except(Registrant_ID),
  FROM tableA  LEFT JOIN tableB ON
   CAST(tableA.Registrant_ID AS int) = CAST(tableB.registrant_id AS int)
   );
  -- This generates a table of approximately 3.56 million voters

  CREATE OR REPLACE VIEW
    `az-voter-file.AVPv2.datReduced`AS(
    SELECT
      census_block,
      County,
      Engaged_Voter_Prediction,
      Not_Engaged_Voter_Prediction,
      Registrant_ID,
      Lean_Democrat,
      Lean_Republican,
      Defect_to_Republican,
      primaryVoterScore,
      generalVoterScore,
      primaryVoterLScore,
      generalVoterLScore,
      CD,
      LD,
      longitude,
      latitude,
      Precinct,
      registrantID,
      location_in_address_file,
      GeoPrecinct,
      respondent_age,
      party_identification,
      PRIMARY_e2016,
      GENERAL_e2016,
      PRIMARY_e2018,
      GENERAL_e2018,
      PRIMARY_e2020,
      GENERAL_e2020,
      PRIMARY_e2022,
      GENERAL_e2022,
      early_voter,
      absentee_voters,
      provisional_voters,
      polling_voters,
        CASE
          WHEN early_list = "Yes" THEN 1
      ELSE  0 end as early_list,     
       Republican_Primary_2016,
      Republican_Primary_2018,
      Republican_Primary_2020,
      Republican_Primary_2022,
      Democratic_Primary_2016,
      Democratic_Primary_2018,
      Democratic_Primary_2020,
      Democratic_Primary_2022,
      bachelors_degree,
      median_age,
      median_income,
      white_pop,
      black_pop,
      asian_pop,
      amerindian_pop,
      hispanic_pop,
      housing_units,
      armed_forces,
      employed_pop,
      pop_in_labor_force,
      total_pop,
      mobile_homes,
      CASE
        WHEN party_identification = "Republican (REP)" THEN 1
      ELSE
      0
    END
      AS Republican,
      CASE
        WHEN party_identification = "Democrat (DEM)" THEN 1
      ELSE
      0
    END
      AS Democrat,
      CASE
        WHEN party_identification <> "Republican (REP)" AND party_identification <> "Democrat (DEM)" THEN 1
      ELSE
      0
    END
      AS Independent
    FROM
      `az-voter-file.AVPv2.data_merged` );


CREATE OR REPLACE VIEW
  `az-voter-file.AVPv2.starTable`AS(
  SELECT
    census_block,
    County,
    party_identification AS pid,
    CAST(CD AS int64) AS CD,
    CAST(LD AS int64) AS LD,
    AVG(Engaged_Voter_Prediction) AS voterPrediction,
    SUM(Lean_Democrat) AS democraticLeaners,
    SUM(Lean_Republican) AS republicanLeaners,
    AVG(primaryVoterScore) AS primaryVoterScore,
    AVG(generalVoterScore) AS generalVoterScore,
    AVG(primaryVoterLScore) AS primaryVoterLScore,
    AVG(generalVoterLScore) AS generalVoterLScore,
    SUM(GENERAL_e2016) AS general2016,
    SUM(PRIMARY_e2016) AS primary2016,
    SUM(PRIMARY_e2018) AS primary2018,
    SUM(GENERAL_e2018) AS general2018,
    SUM(PRIMARY_e2020) AS primary2020,
    SUM(GENERAL_e2020) AS general2020,
    SUM(PRIMARY_e2022) AS primary2022,
    SUM(GENERAL_e2022) AS general2022,
    SUM(early_list) AS early_list,
    SUM(early_voter) AS early_voter,
    SUM(absentee_voters) AS absentee_voters,
    SUM(provisional_voters) AS provisional_voters,
    SUM(polling_voters) AS polling_voters,
    SUM(Democratic_Primary_2016) AS Democratic_Primary_2016,
    SUM(Democratic_Primary_2018) AS Democratic_Primary_2018,
    SUM(Democratic_Primary_2020) AS Democratic_Primary_2020,
    SUM(Democratic_Primary_2022) AS Democratic_Primary_2022,
    SUM(Republican_Primary_2016) AS Republican_Primary_2016,
    SUM(Republican_Primary_2018) AS Republican_Primary_2018,
    SUM(Republican_Primary_2020) AS Republican_Primary_2020,
    SUM(Republican_Primary_2022) AS Republican_Primary_2022,
    SUM(Republican) AS countRepublicans,
    SUM(Democrat) AS countDemocrats,
    SUM(Independent) AS countIndependents,
  FROM
    `az-voter-file.AVPv2.datReduced`
  GROUP BY
    census_block,
    County,
    CD,
    LD,
    pid );
CREATE OR REPLACE VIEW
  `az-voter-file.AVPv2.starTableCent2` AS(
  SELECT
    star.*,
    centroids.* EXCEPT(census_block)
  FROM
    `az-voter-file.AVPv2.starTable` AS star
  LEFT JOIN
    `az-voter-file.AVPv2.centroids` AS centroids
  ON
    star.census_block = centroids.census_block );
CREATE OR REPLACE TABLE
  `az-voter-file.AVPv2.blockAggregates`AS(
 SELECT
    geo_id AS census_block_,
    total_pop,
    households,
    male_pop,
    female_pop,
    median_age,
    white_pop,
    pop_16_over,
    black_pop,
    asian_pop,
    hispanic_pop,
    amerindian_pop,
    other_race_pop,
    two_or_more_races_pop,
    not_hispanic_pop,
    median_income,
    income_per_capita,
    income_less_10000,
    income_10000_14999,
    income_15000_19999,
    income_20000_24999,
    income_25000_29999,
    income_30000_34999,
    income_35000_39999,
    income_40000_44999,
    income_45000_49999,
    income_50000_59999,
    income_60000_74999,
    income_75000_99999,
    income_100000_124999,
    income_125000_149999,
    income_150000_199999,
    income_200000_or_more,
    housing_units,
    occupied_housing_units,
    housing_units_renter_occupied,
    vacant_housing_units,
    vacant_housing_units_for_rent,
    vacant_housing_units_for_sale,
    mobile_homes,
    housing_built_2005_or_later,
    housing_built_2000_to_2004,
    housing_built_1939_or_earlier,
    median_year_structure_built,
    married_households,
    nonfamily_households,
    family_households,
    median_rent,
    percent_income_spent_on_rent,
    million_dollar_housing_units,
    aggregate_travel_time_to_work,
    commuters_by_public_transportation,
    associates_degree,
    bachelors_degree,
    high_school_diploma,
    less_one_year_college,
    masters_degree,
    one_year_more_college,
    employed_pop,
    unemployed_pop,
    centroids.centroid_longitude as centroid_longitude,
    centroids.centroid_latitude as centroid_latitude,
  FROM
    `bigquery-public-data.census_bureau_acs.blockgroup_2018_5yr` as a JOIN 
    `az-voter-file.AVPv2.centroids` as centroids
  ON
    a.geo_id = centroids.census_block);
CREATE OR REPLACE VIEW `az-voter-file.AVPv2.starTableCent`AS (
SELECT
blocks.*,
bld.LD as adjLD,
bcd.CD as adjCD,
CASE
WHEN cast(bld.LD as int) = blocks.LD THEN 1
ELSE 0 
END AS matchLD,
CASE
WHEN cast(bcd.CD as int) = blocks.CD THEN 1
ELSE 0 
END AS matchCD
FROM `az-voter-file.AVPv2.starTableCent2` as blocks 
  LEFT JOIN `az-voter-file.AVPv2.bld` as bld ON blocks.census_block = bld.geo_id 
  LEFT JOIN `az-voter-file.AVPv2.bcd` as bcd ON blocks.census_block = bcd.geo_id 
); 

CREATE OR REPLACE TABLE
  `az-voter-file.AVPv2.countyAggregates`AS(
  WITH
    util AS (
    SELECT
      county_fips_code,
      area_name,
      -- REGEXP_EXTRACT(county_fips_code, "\\d{3}$") AS county_fips,
      REGEXP_REPLACE(area_name, " County", "") AS county_name,
    FROM
      `bigquery-public-data.census_utility.fips_codes_all`
    WHERE
      state_fips_code = "04" )
  SELECT
    * 
  FROM
    `az-voter-file.censusData.county` AS t1
  JOIN
    util AS t2
  ON
    t1.county_fips = t2.county_fips_code );
CREATE OR REPLACE TABLE
  `az-voter-file.AVPv2.ldAggregates`AS(
  SELECT
total as legP1_001N,
population_one_race as legP1_002N,
white as legP1_003N,
black as legP1_004N,
native as legP1_005N,
asian as legP1_006N,
pacific as legP1_007N,
population_hispanic_total as legP2_001N,
hispanic as legP2_002N,
not_hispanic as legP2_003N,
ld as legislative_district
  FROM
    `az-voter-file.censusData.legislative_district04`);
CREATE OR REPLACE TABLE
  `az-voter-file.AVPv2.cdAggregates`AS(
  SELECT
total as congP1_001N,
population_one_race as congP1_002N,
white as congP1_003N,
black as congP1_004N,
native as congP1_005N,
asian as congP1_006N,
pacific as congP1_007N,
population_hispanic_total as congP2_001N,
hispanic as congP2_002N,
not_hispanic as congP2_003N,
cd as congressional_district
FROM `az-voter-file.censusData.congressional_district04`);


CREATE OR REPLACE TABLE
  `az-voter-file.AVPv2.starAggregates`AS(
SELECT
* except(centroid_longitude, centroid_latitude)
     FROM
    `az-voter-file.AVPv2.starTableCent` AS star
  JOIN
    `az-voter-file.AVPv2.cdAggregates` AS cd
  ON
    star.CD = cast(cd.congressional_district as INT)
  JOIN
    `az-voter-file.AVPv2.ldAggregates` AS ld
  ON
    star.LD  = cast(ld.legislative_district as INT)
  JOIN
    `az-voter-file.AVPv2.countyAggregates` AS county
  ON
    cast(star.County as string) = county.county_name
  JOIN
    `az-voter-file.AVPv2.blockAggregates` AS block
  ON
    block.census_block_ = star.census_block  
  );
```


## Public Opinion  

```{R}
library(haven)
library(tidyverse)
path <- file.path("/Users/Chris/recodes_yougov/survey-2022-yougov/survey_recodes/data/AZ2022.sav")
df <- read_sav(path)
alot_4 <- list(`1` = 4, `2` = 3, `3` = 2, `4` = 1)
alot_2 <- list(`1` = 1, `2` = 1, `3` = 0, `4` = 0)

agree_5 <- list(`1` = 5, `2` = 4, `3` = 3, `4` = 2, `5` = 1)
agree_2 <- list(`1` = 1, `2` = 1, `3` = 0, `4` = 0, `5` = 0)
agree_5c <- list(`1` = 1, `2` = 2, `3` = 3, `4` = 4, `5` = 5)
agree_2c <- list(`1` = 0, `2` = 0, `3` = 0, `4` = 1, `5` = 1)

agre_4 <- list(`1` = 4, `2` = 3, `3` = 2, `4` = 1)
agre_2 <- list(`1` = 1, `2` = 1, `3` = 0, `4` = 0)

agre_4c <- list(`1` = 1, `2` = 2, `3` = 3, `4` = 4)
agre_2c <- list(`1` = 0, `2` = 0, `3` = 1, `4` = 1)


df <- df %>%
  mutate(age = 2022 - birthyr) %>%
  # exclude inegligibles
  mutate(vote_2020 = car::recode(as.numeric(vote_2020), "1=1; 2=0; else = NA")) %>%
  mutate(vote_trump = car::recode(as.numeric(presvote20post), "1=0; 2=1; else = NA")) %>%
  mutate(vote_2022 = car::recode(as.numeric(vote_2022), "1=1; 2=0; else = NA")) %>%
  # Only 2 people report not voting.
  mutate(vote_2022_type = car::recode(as.numeric(df$vote), "1=1; 2=2; 3=3; 4=4; else = NA")) %>%
  # Always code republican as 1, democrat as 0.
  mutate(vote_senate = car::recode(as.numeric(general_senate), "1=0; 2=1; else = NA")) %>%
  mutate(vote_gov = car::recode(as.numeric(general_gov), "1=0; 2=1; else = NA")) %>%
  mutate(vote_sos = car::recode(as.numeric(SoS), "1=0; 2=1; else = NA")) %>%
  mutate(primary_type = car::recode(as.numeric(primary), "1=1; 2=2; 3=3; 4=4; else = NA")) %>%
  # 1= Rep ; 0 = Dem
  mutate(primary_type = car::recode(as.numeric(primary_rep), "1=1; 2=0; else = NA")) %>%
  mutate(primary_republican_gov = car::recode(as.numeric(primary_dem), "1= 1; 2=2; 3:4 = 3; else = NA")) %>%
  mutate(primary_democratic_gov = car::recode(as.numeric(primary_dem_gov), "1= 1; 2=2; 3 = 3; 4 = 4; else = NA")) %>%
  mutate(primary_republican_senate = car::recode(as.numeric(primary_rep_senate), "1=1; 2=2; 3 = 3; 4:5 = 4; else = NA")) %>%
  # Rank order questions, 1 counts
  mutate(senate_alignment = ifelse(rank_order_senate_1 == 1, 1, 0)) %>%
  mutate(senate_ofparty = ifelse(rank_order_senate_2 == 1, 1, 0)) %>%
  mutate(senate_likedthem = ifelse(rank_order_senate_3 == 1, 1, 0)) %>%
  mutate(senate_dislikedother = ifelse(rank_order_senate_4 == 1, 1, 0)) %>%
  mutate(senate_thepast = ifelse(rank_order_senate_5 == 1, 1, 0)) %>%
  mutate(governor_alignment = ifelse(rank_order_governor_1 == 1, 1, 0)) %>%
  mutate(governor_ofparty = ifelse(rank_order_governor_2 == 1, 1, 0)) %>%
  mutate(governor_likedthem = ifelse(rank_order_governor_3 == 1, 1, 0)) %>%
  mutate(governor_dislikedother = ifelse(rank_order_governor_4 == 1, 1, 0)) %>%
  mutate(governor_thepast = ifelse(rank_order_governor_5 == 1, 1, 0)) %>%
  mutate(sos_alignment = ifelse(rank_order_SoS_1 == 1, 1, 0)) %>%
  mutate(sos_ofparty = ifelse(rank_order_SoS_2 == 1, 1, 0)) %>%
  mutate(sos_likedthem = ifelse(rank_order_SoS_3 == 1, 1, 0)) %>%
  mutate(sos_dislikedother = ifelse(rank_order_SoS_4 == 1, 1, 0)) %>%
  mutate(sos_thepast = ifelse(rank_order_SoS_5 == 1, 1, 0)) %>%
  mutate(Biden_FT = Biden / 100) %>%
  # Biden, Trump, Lake, Masters, Hobbs, Kelly, Finchem, Fontes, MAGARep, EstabRep, ProgDem, EstabDem
  mutate(Trump_FT = Trump / 100) %>%
  mutate(Lake_FT = Lake / 100) %>%
  mutate(Masters_FT = Masters / 100) %>%
  mutate(Hobbs_FT = Hobbs / 100) %>%
  mutate(Kelly_FT = Kelly / 100) %>%
  mutate(Finchem_FT = Finchem / 100) %>%
  mutate(Fontes_FT = Fontes / 100) %>%
  mutate(MAGARep_FT = MAGARep / 100) %>%
  mutate(EstabRep_FT = EstabRep / 100) %>%
  mutate(ProgDem_FT = ProgDem / 100) %>%
  mutate(EstabDem_FT = EstabDem / 100) %>%
  mutate(biden_economy_4 = recode(as.numeric(biden_economy), !!!alot_4)) %>%
  mutate(biden_economy_2 = recode(as.numeric(biden_economy), !!!alot_2)) %>%
  mutate(personal_economy_4 = recode(as.numeric(personal_economy), !!!alot_4)) %>%
  mutate(personal_economy_2 = recode(as.numeric(personal_economy), !!!alot_2)) %>%
  # Causes of Price increase.
  mutate(cause_covid_4 = recode(as.numeric(causes1), !!!alot_4)) %>%
  mutate(cause_covid_2 = recode(as.numeric(causes1), !!!alot_2)) %>%
  mutate(cause_russia4 = recode(as.numeric(causes2), !!!alot_4)) %>%
  mutate(cause_russia2 = recode(as.numeric(causes2), !!!alot_2)) %>%
  mutate(cause_spending4 = recode(as.numeric(causes3), !!!alot_4)) %>%
  mutate(cause_spending2 = recode(as.numeric(causes3), !!!alot_2)) %>%
  mutate(cause_supply4 = recode(as.numeric(causes4), !!!alot_4)) %>%
  mutate(cause_supply2 = recode(as.numeric(causes4), !!!alot_2)) %>%
  mutate(cause_dems4 = recode(as.numeric(causes5), !!!alot_4)) %>%
  mutate(cause_dems2 = recode(as.numeric(causes5), !!!alot_2)) %>%
  mutate(cause_interest4 = recode(as.numeric(causes6), !!!alot_4)) %>%
  mutate(cause_interest2 = recode(as.numeric(causes6), !!!alot_2)) %>%
  ## Immigration hurt by califrno
  mutate(cali_5 = recode(as.numeric(cali), !!!agree_5)) %>%
  mutate(cali_2 = recode(as.numeric(cali), !!!agree_2)) %>%
  mutate(intern_5 = recode(as.numeric(Q49), !!!agree_5)) %>%
  mutate(intern_2 = recode(as.numeric(Q49), !!!agree_2)) %>%
  mutate(immig_5 = recode(as.numeric(immiga), !!!agree_5)) %>%
  mutate(immig_5 = recode(as.numeric(immiga), !!!agree_2)) %>%
  mutate(separate_parents_5 = recode(as.numeric(immig1), `1` = 1, `2` = 2, `3` = 3, `4` = 4, `5` = 5)) %>%
  mutate(separate_parents_2 = recode(as.numeric(immig1), `1` = 0, `2` = 0, `3` = 1, `4` = 1)) %>%
  mutate(legal_status_4 = recode(as.numeric(immig2), `1` = 1, `2` = 2, `3` = 3, `4` = 4)) %>%
  mutate(legal_status_2 = recode(as.numeric(immig2), `1` = 0, `2` = 0, `3` = 1, `4` = 1)) %>%
  mutate(citizen_4 = recode(as.numeric(immig3), `1` = 1, `2` = 2, `3` = 3, `4` = 4)) %>%
  mutate(citizen_2 = recode(as.numeric(immig3), `1` = 0, `2` = 0, `3` = 1, `4` = 1)) %>%
  mutate(smart_4 = recode(as.numeric(immig3), `1` = 4, `2` = 3, `3` = 2, `4` = 1)) %>%
  mutate(smart_2 = recode(as.numeric(immig3), `1` = 1, `2` = 1, `3` = 0, `4` = 0)) %>%
  ### Conservation is scored higher
  mutate(water_supply_4 = recode(as.numeric(water1), !!!agre_4c)) %>%
  mutate(water_supply_2 = recode(as.numeric(water1), !!!agre_2c)) %>%
  mutate(limit_water_4 = recode(as.numeric(water2), !!!agre_4)) %>%
  mutate(limit_water_2 = recode(as.numeric(water2), !!!agre_2)) %>%
  mutate(tax_water_4 = recode(as.numeric(water3), !!!agre_4)) %>%
  mutate(tax_water_2 = recode(as.numeric(water3), !!!agre_2)) %>%
  mutate(reduce_water_4 = recode(as.numeric(water5), !!!agre_4)) %>%
  mutate(reduce_water_2 = recode(as.numeric(water5), !!!agre_2)) %>%
  # All these items are scored in the conservative direction
  mutate(background_guns_4 = recode(as.numeric(gun3), !!!agre_4c)) %>%
  mutate(background_guns_2 = recode(as.numeric(gun3), !!!agre_2c)) %>%
  mutate(registry_guns_4 = recode(as.numeric(gun2), !!!agre_4c)) %>%
  mutate(registry_guns_2 = recode(as.numeric(gun2), !!!agre_2c)) %>%
  mutate(age_guns_4 = recode(as.numeric(gun4), !!!agre_4c)) %>%
  mutate(age_guns_2 = recode(as.numeric(gun4), !!!agre_2c)) %>%
  mutate(assault_6 = recode(as.numeric(gun5), `1` = 1, `2` = 2, `3` = 3, `4` = 4, `5` = 5, `6` = 6)) %>%
  mutate(assault_2 = recode(as.numeric(gun5), `1` = 0, `2` = 0, `3` = 0, `4` = 1, `5` = 1, `6` = 1)) %>%
  mutate(abortion_4 = recode(as.numeric(abortion2), `1` = 1, `2` = 2, `3` = 3, `4` = 4)) %>%
  mutate(abortion_2 = recode(as.numeric(abortion2), `1` = 0, `2` = 0, `3` = 0, `4` = 1)) %>%
  mutate(abortion_jail_4 = recode(as.numeric(abortion1), `1` = 4, `2` = 3, `3` = 2, `4` = 1)) %>%
  mutate(abortion_jail_2 = recode(as.numeric(abortion1), `1` = 0, `2` = 0, `3` = 0, `4` = 1)) %>%
  mutate(violent_4 = recode(as.numeric(crime2), `1` = 4, `2` = 3, `3` = 2, `4` = 1)) %>%
  mutate(violent_2 = recode(as.numeric(crime2), `1` = 0, `2` = 0, `3` = 0, `4` = 1)) %>%
  mutate(violent_4 = recode(as.numeric(wall), `1` = 4, `2` = 3, `3` = 2, `4` = 1)) %>%
  mutate(violent_2 = recode(as.numeric(wall), `1` = 1, `2` = 1, `3` = 0, `4` = 0)) %>%
  mutate(border_increase_4 = recode(as.numeric(wall), `1` = 4, `2` = 3, `3` = 2, `4` = 1)) %>%
  mutate(border_increase_2 = recode(as.numeric(wall), `1` = 1, `2` = 1, `3` = 0, `4` = 0)) %>%
  ## Contestation Questions, High is contest
  mutate(attend_march_4 = recode(as.numeric(contestation1), !!!agre_4)) %>%
  mutate(attend_march_2 = recode(as.numeric(contestation1), !!!agre_2)) %>%
  mutate(criticize_4 = recode(as.numeric(contestation2), !!!agre_4)) %>%
  mutate(criticize_2 = recode(as.numeric(contestation2), !!!agre_2)) %>%
  mutate(burn_4 = recode(as.numeric(contestation3), !!!agre_4)) %>%
  mutate(burn_2 = recode(as.numeric(contestation3), !!!agre_2)) %>%
  mutate(recount_4 = recode(as.numeric(contestation4), !!!agre_4)) %>%
  mutate(recount_2 = recode(as.numeric(contestation4), !!!agre_2)) %>%
  mutate(court_4 = recode(as.numeric(contestation5), !!!agre_4)) %>%
  mutate(court_2 = recode(as.numeric(contestation5), !!!agre_2)) %>%
  mutate(certify_4 = recode(as.numeric(contestation6), !!!agre_4)) %>%
  mutate(certify_2 = recode(as.numeric(contestation6), !!!agre_2)) %>%
  mutate(concede_4 = recode(as.numeric(contestation7), !!!agre_4)) %>%
  mutate(concede_2 = recode(as.numeric(contestation7), !!!agre_2)) %>%
  mutate(legislator_4 = recode(as.numeric(contestation8), !!!agre_4)) %>%
  mutate(legislator_2 = recode(as.numeric(contestation8), !!!agre_2)) %>%
  mutate(violent_4 = recode(as.numeric(contestation9), !!!agre_4)) %>%
  mutate(violent_2 = recode(as.numeric(contestation9), !!!agre_2)) %>%
  mutate(new_election_4 = recode(as.numeric(contestation10), !!!agre_4)) %>%
  mutate(new_election_2 = recode(as.numeric(contestation10), !!!agre_2)) %>%
  mutate(stolen_2022_4 = recode(as.numeric(steal_2020), !!!agre_4)) %>%
  mutate(stolen_2022_2 = recode(as.numeric(steal_2020), !!!agre_2)) %>%
  mutate(free_fair_4 = recode(as.numeric(free_and_fair), !!!agre_4c)) %>%
  mutate(free_fair_2 = recode(as.numeric(free_and_fair), !!!agre_2c)) %>%
  # Not confident
  mutate(vote_confidence_4 = recode(as.numeric(vote_confidence), !!!agre_4c)) %>%
  mutate(vote_confidence_2 = recode(as.numeric(vote_confidence), !!!agre_2c)) %>%
  # Not COncerned about violence
  mutate(vote_confidence_4 = recode(as.numeric(vote_confidence), !!!agre_4c)) %>%
  mutate(vote_confidence_2 = recode(as.numeric(vote_confidence), !!!agre_2c)) %>%
  # Authoritarianism
  mutate(auth_1 = recode(as.numeric(auth1), `1` = 0, `2` = 1)) %>%
  mutate(auth_2 = recode(as.numeric(auth2), `1` = 0, `2` = 1)) %>%
  mutate(auth_3 = recode(as.numeric(auth3), `1` = 1, `2` = 0)) %>%
  mutate(auth_4 = recode(as.numeric(auth4), `1` = 0, `2` = 1)) %>%
  mutate(authoritarianism = rowMeans((cbind(auth_1, auth_2, auth_3, auth_4)))) %>%
  mutate(rr1 = recode(as.numeric(rr1), !!!agre_4c)) %>%
  mutate(rr2 = recode(as.numeric(rr2), !!!agre_4)) %>%
  mutate(rr3 = recode(as.numeric(rr3), !!!agre_4)) %>%
  mutate(rr4 = recode(as.numeric(rr4), !!!agre_4)) %>%
  mutate(racial_resentment = rowMeans((cbind(rr1, rr2, rr3, rr4)))) %>%
  # Media consumption, coded to almost never, rarely, occasionally, very often
  mutate(media_major = recode(as.numeric(news1), !!!agre_4)) %>%
  # Media type, always, often
  # create variables prefaced with n_ and then is fox, cnn, msnbc, local , nyt, wp, wsj, latimes, usatoday,
  # localnewspaper, npr, telmundo, facebook, twitter, gab, parler, truth, OAN, breitbart, infowars, reddit,
  # alex_jones, american_thinker, last_refuge, epoch_times, newsmax,
  mutate(n_fox = recode(as.numeric(news2_1), `1` = 0, `2` = 0, `3` = 0, `4` = 1, `5` = 1)) %>%
  mutate(n_cnn = recode(as.numeric(news2_2), `1` = 0, `2` = 0, `3` = 0, `4` = 1, `5` = 1)) %>%
  mutate(n_msnbc = recode(as.numeric(news2_3), `1` = 0, `2` = 0, `3` = 0, `4` = 1, `5` = 1)) %>%
  mutate(n_local = recode(as.numeric(news2_4), `1` = 0, `2` = 0, `3` = 0, `4` = 1, `5` = 1)) %>%
  mutate(n_nyt = recode(as.numeric(news2_5), `1` = 0, `2` = 0, `3` = 0, `4` = 1, `5` = 1)) %>%
  mutate(n_wp = recode(as.numeric(news2_6), `1` = 0, `2` = 0, `3` = 0, `4` = 1, `5` = 1)) %>%
  mutate(n_wsj = recode(as.numeric(news2_7), `1` = 0, `2` = 0, `3` = 0, `4` = 1, `5` = 1)) %>%
  mutate(n_latimes = recode(as.numeric(news2_8), `1` = 0, `2` = 0, `3` = 0, `4` = 1, `5` = 1)) %>%
  mutate(n_usatoday = recode(as.numeric(news2_9), `1` = 0, `2` = 0, `3` = 0, `4` = 1, `5` = 1)) %>%
  mutate(n_localnewspaper = recode(as.numeric(news2_10), `1` = 0, `2` = 0, `3` = 0, `4` = 1, `5` = 1)) %>%
  mutate(n_npr = recode(as.numeric(news2_11), `1` = 0, `2` = 0, `3` = 0, `4` = 1, `5` = 1)) %>%
  mutate(n_telmundo = recode(as.numeric(news2_12), `1` = 0, `2` = 0, `3` = 0, `4` = 1, `5` = 1)) %>%
  mutate(n_facebook = recode(as.numeric(news2_13), `1` = 0, `2` = 0, `3` = 0, `4` = 1, `5` = 1)) %>%
  mutate(n_twitter = recode(as.numeric(news2_14), `1` = 0, `2` = 0, `3` = 0, `4` = 1, `5` = 1)) %>%
  mutate(n_gab = recode(as.numeric(news2_15), `1` = 0, `2` = 0, `3` = 0, `4` = 1, `5` = 1)) %>%
  mutate(n_parler = recode(as.numeric(news2_16), `1` = 0, `2` = 0, `3` = 0, `4` = 1, `5` = 1)) %>%
  mutate(n_truth = recode(as.numeric(news2_17), `1` = 0, `2` = 0, `3` = 0, `4` = 1, `5` = 1)) %>%
  mutate(n_oan = recode(as.numeric(news2_18), `1` = 0, `2` = 0, `3` = 0, `4` = 1, `5` = 1)) %>%
  mutate(n_breitbart = recode(as.numeric(news2_19), `1` = 0, `2` = 0, `3` = 0, `4` = 1, `5` = 1)) %>%
  mutate(n_infowars = recode(as.numeric(news2_20), `1` = 0, `2` = 0, `3` = 0, `4` = 1, `5` = 1)) %>%
  mutate(n_reddit = recode(as.numeric(news2_21), `1` = 0, `2` = 0, `3` = 0, `4` = 1, `5` = 1)) %>%
  mutate(n_alex_jones = recode(as.numeric(news2_22), `1` = 0, `2` = 0, `3` = 0, `4` = 1, `5` = 1)) %>%
  mutate(n_american_thinker = recode(as.numeric(news2_23), `1` = 0, `2` = 0, `3` = 0, `4` = 1, `5` = 1)) %>%
  mutate(n_last_refuge = recode(as.numeric(news2_24), `1` = 0, `2` = 0, `3` = 0, `4` = 1, `5` = 1)) %>%
  mutate(n_epoch_times = recode(as.numeric(news2_25), `1` = 0, `2` = 0, `3` = 0, `4` = 1, `5` = 1)) %>%
  mutate(n_newsmax = recode(as.numeric(news2_26), `1` = 0, `2` = 0, `3` = 0, `4` = 1, `5` = 1)) %>%
  mutate(n_daily_wire = recode(as.numeric(news2_27), `1` = 0, `2` = 0, `3` = 0, `4` = 1, `5` = 1)) %>%
  # continue with variable names: n_ + censored_news, redstate, townhall, steven_crowder, washington_examiner, drudge,
  mutate(n_censored_news = recode(as.numeric(news2_28), `1` = 0, `2` = 0, `3` = 0, `4` = 1, `5` = 1)) %>%
  mutate(n_redstate = recode(as.numeric(news2_29), `1` = 0, `2` = 0, `3` = 0, `4` = 1, `5` = 1)) %>%
  mutate(n_townhall = recode(as.numeric(news2_30), `1` = 0, `2` = 0, `3` = 0, `4` = 1, `5` = 1)) %>%
  mutate(n_steven_crowder = recode(as.numeric(news2_31), `1` = 0, `2` = 0, `3` = 0, `4` = 1, `5` = 1)) %>%
  mutate(n_washington_examiner = recode(as.numeric(news2_32), `1` = 0, `2` = 0, `3` = 0, `4` = 1, `5` = 1)) %>%
  mutate(n_drudge = recode(as.numeric(news2_33), `1` = 0, `2` = 0, `3` = 0, `4` = 1, `5` = 1)) %>%
  mutate(media_trust_4 = recode(as.numeric(news3), !!!agre_4)) %>%
  mutate(media_trust_2 = recode(as.numeric(news3), !!!agre_2)) %>%
  # Demographics
  mutate(az_10years = recode(as.numeric(az_res), `1` = 0, `2` = 0, `3` = 0, `4` = 1)) %>%
  mutate(interest_in_politics = recode(as.numeric(interest), `1` = 1, `2` = 1, `3` = 0, `4` = 0)) %>%
  mutate(age = 2022 - as.numeric(birthyr)) %>%
  # 1 = white, 2 = black, 3 = hispanic, 4 = asian, 5 = native, 6 = two_or_more, 7=other, 8 = middle eastern,
  # mutate(racial_group = recode(as.numeric(race), `1` = "White", `2` = "Black", `3` = "Hispanic", `4` = "Asian", `5` = "Native", `6` = "Other", `7` = "Other", `8` = "Middle Eastern")) %>%
  mutate(black = recode(as.numeric(race), `1` = 0, `2` = 1, `3` = 0, `4` = 0, `5` = 0, `6` = 0, `7` = 0, `8` = 0)) %>%
  mutate(white = recode(as.numeric(race), `1` = 1, `2` = 0, `3` = 0, `4` = 0, `5` = 0, `6` = 0, `7` = 0, `8` = 0)) %>%
  mutate(hispanic = recode(as.numeric(race), `1` = 0, `2` = 1, `3` = 1, `4` = 0, `5` = 0, `6` = 0, `7` = 0, `8` = 0)) %>%
  mutate(asian = recode(as.numeric(race), `1` = 0, `2` = 0, `3` = 0, `4` = 1, `5` = 0, `6` = 0, `7` = 0, `8` = 0)) %>%
  mutate(american_indian = recode(as.numeric(race), `1` = 0, `2` = 0, `3` = 0, `4` = 0, `5` = 1, `6` = 0, `7` = 0, `8` = 0)) %>%
  mutate(other = recode(as.numeric(race), `1` = 0, `2` = 1, `3` = 0, `4` = 0, `5` = 1, `6` = 1, `7` = 1, `8` = 1)) %>%
  mutate(zipcode = as.numeric(inputzip)) %>%
  mutate(married = recode(as.numeric(marstat), `1` = 1, `2` = 0, `3` = 0, `4` = 0, `5` = 0)) %>%
  mutate(female = recode(as.numeric(gender), `1` = 0, `2` = 1)) %>%
  mutate(college = recode(as.numeric(educ), `1` = 0, `2` = 0, `3` = 0, `4` = 0, `5` = 1, `6` = 1)) %>%
  # Faminc is greater than 70k
  mutate(faminc = ifelse(as.numeric(faminc_new) > 8, 1, 0)) %>%
  mutate(kids_in_home = ifelse(as.numeric(child18) == 1, 1, 0)) %>%
  mutate(pid3 = recode(as.numeric(pid3), `1` = 1, `2` = 3, `3` = 2)) %>%
  mutate(vote_in_2016 = recode(as.numeric(presvote16post), `1` = 0, `2` = 1)) %>%
  mutate(fips_county = county_AZ) %>%
  mutate(congressional_district = cd) %>%
  mutate(legislative_district = LD_upper) %>%
  mutate(ideology = recode(as.numeric(ideo5), `1` = 1, `2` = 2, `3` = 3, `4` = 4, `5` = 5)) %>%
  # 1 = Protestant, 2 = Catholic, 3 = Mormon, 4 = Other, 5 = Jewish, 6 = Other, 7 = Other, 8 = Other, 9 = Atheist, 10 = Agnostic, 11 = Nothing, 12 = Else
  mutate(religion = recode(as.numeric(religpew), `1` = 1, `2` = 2, `3` = 3, `4` = 4, `5` = 5, `6` = 4, `7` = 4, `8` = 4, `9` = 5, `10` = 6, `11` = 7, `12` = 4)) %>%
  mutate(county_weight = weight) %>%
  mutate(county = county_AZ) %>%
  mutate(LD = legislative_district) %>%
  mutate(CD = congressional_district) %>%
  mutate(Party_Selector = pid3) %>%
  select(age:Party_Selector) %>%
  select(!ends_with("_4"))


df <- df %>%
  mutate(rid = seq(1:nrow(df))) %>%
  mutate_if(is.numeric, as.character) %>%
  pivot_longer(cols = -c(rid, county_weight, county, CD, LD, Party_Selector))

df <- df %>%
  mutate(county_name = recode(
    county,
    `1` = "Apache",
    `3` = "Cochise",
    `5` = "Coconino",
    `7` = "Gila",
    `9` = "Graham",
    `11` = "Greenlee",
    `12` = "La Paz",
    `13` = "Maricopa",
    `15` = "Mohave",
    `17` = "Navajo",
    `19` = "Pima",
    `21` = "Pinal",
    `23` = "Santa Cruz",
    `25` = "Yavapai",
    `27` = "Yuma"
  ))

df <- df %>% na.omit()
labels <- read.csv("/Users/Chris/Dropbox/github_repos/the_az_voter_project/the_az_voter_project/ML/data/labels.csv")
dat <- merge(df, labels, by.x = "name", by.y = "name", all.x = TRUE)
write.csv(dat, "/Users/Chris/Dropbox/github_repos/the_az_voter_project/the_az_voter_project/ML/data/bq_upload_.csv", row.names = FALSE)
```

Upload to GCP then create a table called **PublicOpinion_Fall2022**. Then, here are the SQL queries to create a file called **az-voter-file.AVPv2.public_opinion_actionable**.

```{sql}
  -- Four Tables
  -- (3) Aggregates from voter file, CD, LD, County
  -- (1) Public Opinion data, 
CREATE OR REPLACE TABLE `az-voter-file.AVPv2.poLD` as(
WITH
  aggregates AS (
  SELECT
    LD,
    SUM(Republican) as RepublicanLD,
    SUM(Democrat)   as DemocratLD,
    SUM(Independent)   as IndependentLD,
    AVG(respondent_age) AS registrant_ageLD,
    COUNT(*) as number_votersLD
  FROM
    `az-voter-file.AVPv2.datReduced`
  GROUP BY
    LD )
SELECT
  aggregates.*,
  SAFE_DIVIDE(aggregates.RepublicanLD, aggregates.number_votersLD) AS republican_percentageLD,
  SAFE_DIVIDE(aggregates.DemocratLD, aggregates.number_votersLD)    AS democrat_percentageLD,
  SAFE_DIVIDE(aggregates.IndependentLD, aggregates.number_votersLD) AS independent_percentageLD,
FROM
  aggregates
  WHERE LD IS NOT NULL
);

CREATE OR REPLACE TABLE `az-voter-file.AVPv2.poCD` as(
WITH
  aggregates AS (
  SELECT
    CD,
    SUM(Republican) as RepublicanCD,
    SUM(Democrat)   as DemocratCD,
    SUM(Independent)   as IndependentCD,
    AVG(respondent_age) AS registrant_ageCD,
    COUNT(*) AS number_votersCD
  FROM
      `az-voter-file.AVPv2.datReduced`  GROUP BY
    CD )
SELECT
  aggregates.*,
  SAFE_DIVIDE(aggregates.RepublicanCD, aggregates.number_votersCD) AS republican_percentageCD,
  SAFE_DIVIDE(aggregates.DemocratCD, aggregates.number_votersCD) AS democrat_percentageCD,
  SAFE_DIVIDE(aggregates.IndependentCD, aggregates.number_votersCD) AS independent_percentageCD,
FROM
  aggregates
  WHERE CD IS NOT NULL
);

CREATE OR REPLACE TABLE `az-voter-file.AVPv2.poCounty` as(
WITH
  aggregates AS (
  SELECT
    County,
    SUM(Republican) as RepublicanCounty,
    SUM(Democrat)   as DemocratCounty,
    SUM(Independent)   as IndependentCounty,
    AVG(respondent_age) AS registrant_ageCounty,
    COUNT(*) AS number_votersCounty
  FROM
      `az-voter-file.AVPv2.datReduced`  GROUP BY
    County )
SELECT
  aggregates.*,
  SAFE_DIVIDE(aggregates.RepublicanCounty, aggregates.number_votersCounty) AS republican_percentageCounty,
  SAFE_DIVIDE(aggregates.DemocratCounty, aggregates.number_votersCounty) AS democrat_percentageCounty,
  SAFE_DIVIDE(aggregates.IndependentCounty, aggregates.number_votersCounty) AS independent_percentageCounty,
FROM
  aggregates
  WHERE County IS NOT NULL
);


CREATE OR REPLACE TABLE `az-voter-file.AVPv2.public_opinion_actionable` as(
with county as (SELECT 
t1.*,
t2.* except(County)
  FROM `az-voter-file.AVPv2.PublicOpinion_Fall2022` as t1 JOIN  `az-voter-file.AVPv2.poCounty` as t2 ON
  t1.county_name = t2.County
)
SELECT
t1.*,
t2.* except(LD)
  FROM county as t1 
  JOIN  `az-voter-file.AVPv2.poLD` as t2 ON
  cast(t1.LD as string) = t2.LD
  JOIN  `az-voter-file.AVPv2.poCD` as t3 ON
  cast(t1.CD as string) = t3.CD
);

SELECt
* except(county)
FROM `az-voter-file.AVPv2.public_opinion_actionable`
```

## Precincts  

```{sql}
CREATE OR REPLACE TABLE `az-voter-file.AVPv2.precinctGold` as(
with rawVoter as (
  SELECT
a.*,
precinct_shapes.geo,
precinct_shapes.precinct as mergePrecinct,
FROM   `az-voter-file.AVPv2.datReduced` as a
  CROSS JOIN
     `az-voter-file.geo_voter.precinct_vote_returns`  AS precinct_shapes
    WHERE
      ST_CONTAINS(SAFE.ST_GEOGFROMGEOJSON(precinct_shapes.geo), SAFE.ST_GEOGPOINT(a.longitude, a.latitude)) 
      AND precinct_shapes.ballot_candidate = "Masters_Blake"),
voter as (
SELECT
    SUM(Lean_Democrat) as leanDemocrat,
    AVG(Engaged_Voter_Prediction) AS voterPrediction,
    SUM(Lean_Democrat) AS democraticLeaners,
    SUM(Lean_Republican) AS republicanLeaners,
    AVG(primaryVoterScore) AS primaryVoterScore,
    AVG(generalVoterScore) AS generalVoterScore,
    AVG(primaryVoterLScore) AS primaryVoterLScore,
    AVG(generalVoterLScore) AS generalVoterLScore,
    SUM(GENERAL_e2016) AS general2016,
    SUM(PRIMARY_e2016) AS primary2016,
    SUM(PRIMARY_e2018) AS primary2018,
    SUM(GENERAL_e2018) AS general2018,
    SUM(PRIMARY_e2020) AS primary2020,
    SUM(GENERAL_e2020) AS general2020,
    SUM(PRIMARY_e2022) AS primary2022,
    SUM(GENERAL_e2022) AS general2022,
    SUM(early_voter) AS early_voter,
    SUM(absentee_voters) AS absentee_voters,
    SUM(provisional_voters) AS provisional_voters,
    SUM(polling_voters) AS polling_voters,
    SUM(Democratic_Primary_2016) AS Democratic_Primary_2016,
    SUM(Democratic_Primary_2018) AS Democratic_Primary_2018,
    SUM(Democratic_Primary_2020) AS Democratic_Primary_2020,
    SUM(Democratic_Primary_2022) AS Democratic_Primary_2022,
    SUM(Republican_Primary_2016) AS Republican_Primary_2016,
    SUM(Republican_Primary_2018) AS Republican_Primary_2018,
    SUM(Republican_Primary_2020) AS Republican_Primary_2020,
    SUM(Republican_Primary_2022) AS Republican_Primary_2022,
    SUM(Republican) AS countRepublicans,
    SUM(Democrat) AS countDemocrats,
    SUM(Independent) AS countIndependents,
    mergePrecinct,
    CD,
    LD, 
    County
FROM    rawVoter as t1
GROUP BY mergePrecinct, CD, LD, County
ORDER BY mergePrecinct, CD, LD, County
)
SELECT
*
-- t2.*
FROM voter as t1  JOIN  `az-voter-file.AVPv2.precincts_actionable`  as t2 ON
t1.mergePrecinct = t2.precinct
 );
CREATE OR REPLACE TABLE `az-voter-file.AVPv2.precinctWide` as(
SELECT
*
FROM 
(
  SELECT
  mergePrecinct,
  precinctVotes,
  ballot_candidate
FROM `az-voter-file.AVPv2.precinctGold`
)
PIVOT(SUM(cast(precinctVotes as NUMERIC)) as votes
  FOR ballot_candidate IN   ("Hobbs_Katie", "Lake_Kari", 
    "Quezada_Martin", "Hamadeh_Abraham_Abe", "Yee_Kimberly", "Mayes_Kris", 
    "Masters_Blake", "Kelly_Mark"," Fontes_Adrian" ,"Finchem_Mark")
)
);
CREATE OR REPLACE TABLE `az-voter-file.AVPv2.cdWide` as(
SELECT
*
FROM 
(
  SELECT
  CD,
  precinctVotes,
  ballot_candidate
FROM `az-voter-file.AVPv2.precinctGold`
)
PIVOT(SUM(cast(precinctVotes as NUMERIC)) as votesC
  FOR ballot_candidate IN   ("Hobbs_Katie", "Lake_Kari", 
    "Quezada_Martin", "Hamadeh_Abraham_Abe", "Yee_Kimberly", "Mayes_Kris", 
    "Masters_Blake", "Kelly_Mark"," Fontes_Adrian" ,"Finchem_Mark")
)
);


CREATE OR REPLACE TABLE `az-voter-file.AVPv2.countyWide` as(
SELECT
*
FROM 
(
  SELECT
  County,
  precinctVotes,
  ballot_candidate
FROM `az-voter-file.AVPv2.precinctGold`
)
PIVOT(SUM(cast(precinctVotes as NUMERIC)) as votesCounty
  FOR ballot_candidate IN   ("Hobbs_Katie", "Lake_Kari", 
    "Quezada_Martin", "Hamadeh_Abraham_Abe", "Yee_Kimberly", "Mayes_Kris", 
    "Masters_Blake", "Kelly_Mark"," Fontes_Adrian" ,"Finchem_Mark")
)
);


CREATE OR REPLACE TABLE `az-voter-file.AVPv2.ldWide` as(
SELECT
*
FROM 
(
  SELECT
  LD,
  CD,
  precinctVotes,
  ballot_candidate
FROM `az-voter-file.AVPv2.precinctGold`
)
PIVOT(SUM(cast(precinctVotes as NUMERIC)) as votesLD
  FOR ballot_candidate IN   ("Hobbs_Katie", "Lake_Kari", 
    "Quezada_Martin", "Hamadeh_Abraham_Abe", "Yee_Kimberly", "Mayes_Kris", 
    "Masters_Blake", "Kelly_Mark"," Fontes_Adrian" ,"Finchem_Mark")
)
);
```