Voting Survey Study

ABOUT THIS DOCUMENT

ABOUT THE DATA

SUMMARY OF MY PROCESS

Step 1 - Import the .csv into an Azure SQL database

I have a personal Azure account, so I created a SQL database for this project and used the Azure Data Studio’s import wizard to import the nonvoters_data.csv.

*NOTE - I am using an Acer Chromebook, so needed to install Linux in order to install Azure Data Studio. It works well, but can be slow and clunky, so I mainly use the Azure Data Studio for any tools not in the online Azure.

Step 2 - Transform the data!

2.1 - Now that the raw data is imported in to a table, I could trudge through as it stands, developing SQL to pull the data and using the codebook as a guide to translate and name things in the SQL or translate everything in Excel as I visualize. That sounds brutal. I want a data table that is as simple as possible. This will enable me to easily play with the data and get to know it quickly.

I need to create a method to translate the raw data into something visually interpretable.
It doesn’t have to be perfect - just better than a table of Qs, 1s, and 2s.

2.1 - I make a copy of my raw table (nonvoter_data) into CLEAN_VOTER_DATA. This is just to ensure I don’t corrupt nonvoter_data.

2.2 - I rename all of the columns in CLEAN_VOTER_DATA into something that I can interpret at a glance. All columns must have a unique name. Plus, these are going to be a primary key in your other translation tables.

Here is the script to change column names: EXEC sp_rename 'dbo.CLEAN_VOTER_TABLE.Q1', 'Citizen', 'COLUMN';

2.3 - This was a little tedious but by no means hard nor time-consuming. I took the codebook that was provided (which looks like the survey but with question and answer designators) and formatted it into 2 ‘$’ delimited CSVs. One file for questions and one file for answers. They looked like this: 

Step 3 - The creation of VOTING_PIVOT_MONSTER

3.1 - I used a straightforward SELECT INTO method for creating VOTING_PIVOT_MONSTER. I unioned all 112 necessary SELECTs and used my translation tables to replace all of the numeric values (answers) with real answers.

There were a few errors with data types. For example, in a column where a majority of answers were numeric the data type was assumed to be int but occasionally an NA (not applicable) would be the answer and fail the insert.

The following script is an example of what was used to alter data types until everything passed:

ALTER TABLE [dbo].[VOTING_PIVOT_MONSTER]

ALTER COLUMN ANSWER_DESC nvarchar(255);

3.1 - Here is the top few lines of the SQL script that creates VOTING_PIVOT_MONSTER:

INSERT INTO VOTING_PIVOT_MONSTER (

   QUESTION,

   RESPID,

   ANSWER_DESC,

   GENDER,

   PPAGE,

   EDUC,

   RACE,

   INCOME_CAT,

   VOTER_CATEGORY

)

select 'voting' AS 'question', a.respid,
b.answer_desc, a.gender, 'age', a.educ, a.race,
a.income_cat, a.voter_category

from [dbo].[CLEAN_VOTER_TABLE] a, [dbo].[ANSWERS] b,
[dbo].[COLUMN_TRANS] d--

where d.column_name = 'voting'

and d.question_id = b.question_id

and a.voting = b.answer_id

and a.voting NOT IN ('-1        ', 'NA')

UNION

select 'politics' AS 'question', a.respid, b.answer_desc, a.gender, 'age', a.educ, a.race, a.income_cat, a.voter_category

from [dbo].[CLEAN_VOTER_TABLE] a, [dbo].[ANSWERS] b, [dbo].[COLUMN_TRANS] d--

where d.column_name = 'politics'

and d.question_id = b.question_id

and a.politics = b.answer_id

and a.politics NOT IN ('-1        ', 'NA')

UNION

-- The script goes on for another 800 lines of “Unions” pulling all the questions and answers.

EASE OF USE - FOR PASSING TO COLLEAGUES OR PASSING TO AI

Structuring the data into such a simple table, using intuitive labeling, enables this project to be shared with others. It also allows it to be passed to an LLM easily. Only a few hours after receiving the raw CSV, the data can be explored easily by colleagues or submitted to an LLM to reveal further exploration.

LLM AIs, like ChatGPT 4o, can use well-named column names (designed with AI in mind):

Here is the straightforward SQL I used to create an AI-friendly CSV:

SELECT a.respid as ID,

a.ppage as 'Age',

a.gender as 'Gender',

a.educ as 'Education Level',

a.race as 'Race',

a.income_cat as 'Income Category',

a.voter_category as 'Voting Frequency',

a.answer_desc as 'Political Party',

--

(select z.answer_desc

FROM [dbo].[VOTING_PIVOT_MONSTER] z

WHERE a.respid = z.respid
and z.question IN ('racism')) as 'Racism is a Problem' ,

--

(select y.answer_desc

FROM [dbo].[VOTING_PIVOT_MONSTER] y

WHERE a.respid = y.respid

and y.question IN ('whichpres')) as 'My Choice as President'

--

FROM [dbo].[VOTING_PIVOT_MONSTER] a

WHERE a.question IN ('yourparty')

GROUP BY a.respid, a.ppage, a.gender, a.educ, a.race, a.income_cat, a.voter_category, a.answer_desc

ORDER BY ID;

Steps for using AI

1. - Download the results as a csv (there is an export/download limit in Azure of 55K rows) 

2. - Open a new chat in ChatGpt 4o

3. - Upload the csv into the chat. The LLM will analyze the file and ask you what you need to know.

4. - I entered the following prompt: 

To see the resulting analysis, please click the following link:

ChatGPT 4o Voting Data Analysis

REVIEW WHAT WE HAVE:

A simple, 9-column table with all of the data in English terms (VOTER_PIVOT_MONSTER)

• • Easy to pass to colleagues without needing complex instructions
    
    

A simple file-building SQL that anyone could use as a template

• • Only uses one table

  • Produces very Excel friendly files

  • Produces very AI friendly files
    
    

After only about 8 hours of preparation, you can have a team meeting to do a very quick and creative analysis of the data. 

ANALYSIS OF THE DATA

There are many (possibly hundreds) of ways to analyze this dataset. It is truly a wealth of voter survey data. I chose to concentrate of barriers to voting. What prevents people from voting? There are conditions which make it a burden. There are also beliefs and attitudes that lessen one's motivation to vote. My analysis tries to find what these barriers are and imagine what could minimize them.