<jupyter_start><jupyter_text>Overview In this part of the assessment, you will act as a Data analyst to answer a research question on the US elections. First, will be required to write a Data Report on the process that will undertake while working on the given research problem. Using the CRISP-DM Methodology,  you will document the various stages of the data science lifecycle for the given research problem while also providing your recommendation.You have been provided with a detailed description of what you will be expected to do below.Do remember that complex tasks that may seem hard at first can often be broken down into a sequence of simple tasks, and there are workarounds to do what first seems “impossible” with a succession of known operations. **Problem Description**You have been asked to help a candidate to become US president. The winner of the election will be the candidate winning the most grand electors. Grand electors are attributed at the state level: in each of the 51 states, there is a given number of grand electors to win (roughly, but not exactly, proportional to the size of the state) and the presidential candidate receiving the most local votes wins ALL the Grand Electors in that state.Because the number of grand electors is not exactly proportional to the population, some states can be prioritized to increase the return on investment of the campaign. We assume here there are only 2 candidates, and no history (no trend of certain states to vote for a particular candidate or party). Hence, each vote is equally "expensive" to get, but some states grant more grand elector per capita.You are provided with 2 tables: one giving the number of Grand Electors per state, the other the population per state.- Grand Electors by State- Population by StateYou are asked to identify the states that should be prioritized to win the election, with a smart but simple algorithm (brute force computation of all possibilities to find the absolute optimum is not accepted, as it would be to computationally expensive). It is ok not to find the overall optimum, we just want a strategy that makes sense.(This is of course a very simplistic view of reality, but it is a nice starting point to play with data and analyze possibilities).First take a few minutes to think about what you need to do. Steps will be suggested hereafter, but a big part of the data scientist's job is to identify the flow of operations when being asked a practical question, so it is important you start exercising on that in addition to programming.Here is what we are suggesting to do: we will rank states by decreasing number of grand electors per capita. The first states in the list will be the most valuable (you get a large number of grand electors by convincing a small number of people to vote for you). We will target all the states at the top of the list until the cumulative sum (also called running total) of grand electors won is larger than half the total number of Grand Electors in the country. **Instructions**To do that, we need (you are allowed to create as many intermediary tables as you want, to keep queries short):1. To join the 2 tables:- You notice States are not capitalized the same way in both tables (one is in uppercase letters, the other not), so you will first need to convert all to uppercase, for instance.- Now you can join the tables on the state key.2. Your boss wants you to change the name of the "District of Columbia" state to its short version "DC". Please do that.3. To compute the ratio between the number of grand electors and the population. Please create a new column with that ratio.4. To order the states by decreasing ratio of Grand Electors per capita. That will make our priority list.5. To compute the running total of Grand Electors in that sorted list.- Hint: you can get inspiration from here to compute a running total from here:  https://stackoverflow.com/questions/21382766/cumulative-summing-values-in-sqlite6. Independently, to compute the half of the total of Grand Electors overall (in the whole country):- This is the threshold we need to reach for winning the presidential election.7. To filter our sorted list of states in order to keep only the (top) ones enabling us to reach the computed threshold. (the other states can be ignored). That is our target list.- Hint: You can do that in 2 steps: - Select all the states for which the running total is below or equal to the threshold. - Add the first state for which the running total is larger than the threshold.Can you draw some conclusions from the result? Is it in line with your expectations? How many states do you end up with in the target list? Is it a small or a large number? Do you think it would be a good recommendation to target those states?  Connecting to database and importing data from CSV files<jupyter_code># Loading an sql extension to allow me to work with sql on Colaboratory;
# 
%load_ext sql

# Connecting to the in memory sqlite database;
# 
%sql sqlite://
# Importing the python csv library to allow me to read csv file(s) that  will have uploaded to this environment;
# 
import csv

# Importing the pandas library to use for data manipulation in this notebook;
# 
import pandas as pd
# Uploading the Grand Electors csv file;
# 
with open('GrandElectors_by_state.csv','r') as f:
    GrandElectors_by_state = pd.read_csv(f, index_col=0, encoding='utf-8')

%sql PERSIST GrandElectors_by_state;
# Previewing the Grand_Electors table;
# 
%%sql
SELECT * FROM GrandElectors_by_state
LIMIT 10;
# Uploading the Population csv file;
# 
with open('Population_by_state.csv','r') as f:
    Population_by_state = pd.read_csv(f, index_col=0, encoding='utf-8')

%sql PERSIST Population_by_state;
# Previewing the Population table;
# 
%%sql
SELECT * FROM Population_by_state
LIMIT 10;<jupyter_output>* sqlite://
Done.<jupyter_text>Data Preparation<jupyter_code># Instruction 1:
# Notice States are not capitalized the same way in both tables
# Converting States in GrandElectors_by_state table to UPPERCASE;
# 
%%sql
UPDATE GrandElectors_by_state SET State = upper(State);

SELECT * FROM GrandElectors_by_state;
# Previewing the Grand_Electors table to confirm the update has taken effect;
# 
%%sql
SELECT * FROM GrandElectors_by_state LIMIT 10;
# Counting the number of States in the Grand_Electors table;
# 
%%sql
SELECT COUNT(State) FROM GrandElectors_by_state;
# Counting the number of States in the Population table;
# 
%%sql
SELECT COUNT(State) FROM Population_by_state;
# Notice that the number of States are not the same
# This will be corrected after using the INNER JOIN function
# The records from table one and table two would both be returned,...
# ... but only if the values in column one of table one match the values in column one of table two.
# Any records that do not have matching values would not be returned by an INNER JOIN.

# Joining both tables;
# 
%%sql
CREATE TABLE if not exists new_table AS SELECT STATE, POPULATION, GRAND_ELECTORS FROM
(SELECT
GrandElectors_by_state.State AS STATE,
Population_by_state.Population AS POPULATION,
GrandElectors_by_state.GrandElectors AS GRAND_ELECTORS
FROM GrandElectors_by_state
INNER JOIN Population_by_state ON GrandElectors_by_state.State = Population_by_state.State);

SELECT * FROM new_table;
# Previewing the new joint table to confirm the update has taken effect;
# 
%%sql
SELECT * FROM new_table;<jupyter_output>* sqlite://
Done.<jupyter_text>Modelling<jupyter_code># Instruction 2:
# Changing the name of the "District of Columbia" State to its short version "DC".
# 
%%sql
UPDATE new_table
SET STATE = "DC"
WHERE STATE = "DISTRICT OF COLUMBIA";

SELECT * FROM new_table;
# Instruction 3:
# To compute the ratio between the number of grand electors and the population.
# Create a new column with that ratio.
# 
# Instruction 4:
# To order the states by decreasing ratio of Grand Electors per capita. That will make our priority list.
# 
%%sql
SELECT STATE, POPULATION, GRAND_ELECTORS,
(POPULATION/GRAND_ELECTORS) AS "POPULATION FOR EVERY 1 GRAND ELECTOR"
FROM new_table
ORDER BY "POPULATION FOR EVERY 1 GRAND ELECTOR" DESC;
# Instruction 5:
# To compute the running total of Grand Electors in that sorted list.
# 
%%sql

%%sql
SELECT STATE, POPULATION, GRAND_ELECTORS, "POPULATION FOR EVERY 1 GRAND ELECTOR",
  SUM(GRAND_ELECTORS) 
  OVER (PARTITION BY STATE ORDER BY "POPULATION FOR EVERY 1 GRAND ELECTOR" DESC)
  AS "POPULATION FOR EVERY 1 GRAND ELECTOR"
FROM new_table;
%%sql
SELECT
    STATE,
    POPULATION,
    GRAND_ELECTORS,
    "POPULATION FOR EVERY 1 GRAND ELECTOR"
SUM (GRAND_ELECTORS) OVER (ORDER BY "POPULATION FOR EVERY 1 GRAND ELECTOR") AS RUNNING_TOTAL
FROM new_table;
%%sql
SELECT country, registration_date,registred_users,
  SUM(registred_users) 
  OVER (PARTITION BY country ORDER BY registration_date)
  AS total_users
FROM registration;

SELECT registration_date,registred_users,
  SUM(registred_users) OVER (ORDER BY registration_date)
  AS total_users
FROM registration;
SELECT
    t.Date,
    Sum(r.KeyColumn1),
    Sum(r.KeyColumn2),
    Sum(r.KeyColumn3)
FROM (SELECT DISTINCT Date FROM MyTable) as t
Left Join MyTable as r On (r.Date < t.Date)
Group By t.Date;
%%sql 
SELECT SUM(GRAND_ELECTORS)
FROM new_table;
# Instruction 6:
# Compute half of the total of Grand Electors overall (in the whole country):
# - This is the threshold we need to reach for winning the presidential election.
# 
%%sql
SELECT SUM(GRAND_ELECTORS)
# Instruction 7:
# To filter our sorted list of states in order to keep only the (top) ones enabling us to reach the computed threshold.
# (the other states can be ignored). That is our target list.
# Hint: You can do that in 2 steps:
# - Select all the states for which the running total is below or equal to the threshold.
# - Add the first state for which the running total is larger than the threshold.
# 
%%sql
SELECT
FROM new_table;<jupyter_output><empty_output><jupyter_text>Evaluation<jupyter_code># Test against success criteria
#<jupyter_output><empty_output><jupyter_text>Deployment<jupyter_code># Conclusion:
# Can you draw some conclusions from the result?
# Is it in line with your expectations?
# How many states do you end up with in the target list?
# Is it a small or a large number?
# Do you think it would be a good recommendation to target those states?
#<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.0512301013024602, 'fraction_numerical': 0.00472744814278823, 'mean_word_length': 4.705008255365988, 'pattern_counts': {'":': 0, '<': 20, '<?xml version=': 0, '>': 19, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '7803', 'license': 'MIT', 'n_tokens_mistral': 2944, 'n_tokens_neox': 2758, 'n_words': 1531, 'path': 'Moringa_Data_Science_Prep_W2_Independent_Project_2021_05_Lucy_Kinyua_SQL_Notebook.ipynb', 'repo_name': 'LucyKinyua/Week2_MS', 'size': '10365'}