<jupyter_start><jupyter_text>**Analysing the mortality rates for 2015 across US**  **Problem:** The goal of this project is to look at deaths in the US and the correlations between the descriptive statistics and mortality. Examples of questions we will answer and visualize include:Grouping the deaths by age and analyzing similarities within those groups* Grouping the number of deaths by age and analyzing similarities within those groups* Additional potential groupings by gender, education, time of year, and manner of death* Using various types of data columns to visualize most frequent causes of death across the US  **Data sources:**This dataset was found on Kaggle and is created by the CDC* Kaggle https://www.kaggle.com/cdc/mortality?select=2015_data.csv* CDC https://www.cdc.gov/nchs/nvss/mortality_public_use_data.html  **Why this data set?**The reason for analyzing any data set is to gain a deeper understanding of what the data represents. A story can be told from a data set and this one provides interesting insights into the US population. Mortalities (deaths) can tell us so much about a population if there is proper recordings of the facts of both the people and the situations around their deaths. From this data many relationships between features are looked into to try and understand who is dying and what groups of people can be made.An example of a relationship that provides an interesting story is looking at education levels and seeing if there is a correlation between education and the age at which someone dies.  **Exploratory Phase of our Project on mortality counts:**---  Overview of the tables with all data present:<jupyter_code>from google.colab import auth
auth.authenticate_user()
#authenticate and connect to gcloud
%%bigquery --project=ba775-team9-b2
select * from `ba775-team9-b2.death_data_us.Sorted_2015_US_Mortality` limit 5;
%%bigquery --project=ba775-team9-b2
select * FROM `ba775-team9-b2.death_data_us.Mortailty_with_string_categories` limit 5;<jupyter_output><empty_output><jupyter_text>The first table is the raw data given by the CDC which was found on Kaggle. From this data we used the legends given by the CDC to convert this categorical data back into strings so that it is easier to read.Below is the documentaion we used for this conversion:https://www.cdc.gov/nchs/data/dvs/Multiple_Cause_Record_Layout_2015.pdf  Relative death counts between men and woman:<jupyter_code>%%bigquery --project=ba775-team9-b2
select 
sex,
count(*) as deaths_by_gender,
round((count(*)/total_death_count)*100,2) as percent_of_total
from( select count(*) as total_death_count
from `ba775-team9-b2.death_data_us.Sorted_2015_US_Mortality`),
 `ba775-team9-b2.death_data_us.Sorted_2015_US_Mortality`
group by sex,total_death_count 
order by deaths_by_gender;<jupyter_output><empty_output><jupyter_text>We observed that males have a slightly higher death counts, but the difference is not substantial enough to draw any conclusions.  Analyzing how age and gender impact % of deaths:<jupyter_code>%%bigquery --project=ba775-team9-b2
select 
    sex,
    age_code, 
    count(*) as death_count, 
    ((count(*) / total_death_count)*100) as percent_of_total,
    round((count(case when sex = 'M' then sex else NULL end)/male_deaths)*100,2) as Male_Perc,
    round((count(case when sex = 'F' then sex else NULL end)/female_deaths)*100,2) as Female_Perc

from( select count(*) as total_death_count from `ba775-team9-b2.death_data_us.Sorted_2015_US_Mortality`),
    ( select count(*) as male_deaths from `ba775-team9-b2.death_data_us.Sorted_2015_US_Mortality` where sex = 'M'),
    ( select count(*) as female_deaths from `ba775-team9-b2.death_data_us.Sorted_2015_US_Mortality` where sex = 'F'),
    `ba775-team9-b2.death_data_us.Mortailty_with_string_categories`
group by sex,age_code,total_death_count, male_deaths, female_deaths 
order by percent_of_total desc limit 10;<jupyter_output><empty_output><jupyter_text>The age group with the most deaths for females is '85years and over', while the group with the highest deaths for males is '75-84years'.  Analyzing which manner of death is the most common among the total number of deaths reported:<jupyter_code>%%bigquery --project=ba775-team9-b2
select manner_of_death, count(sex) as number_of_deaths, round((count(sex)/total_death_count)*100,2) as percent_of_total
FROM ( select count(*) as total_death_count
from `ba775-team9-b2.death_data_us.Sorted_2015_US_Mortality`),
 `ba775-team9-b2.death_data_us.Mortailty_with_string_categories` 
 where manner_of_death not in ('Unknown','Could not determine', 'Pending investigation')
GROUP BY manner_of_death,total_death_count
ORDER BY number_of_deaths DESC;<jupyter_output><empty_output><jupyter_text>From this query we can see that natural causes are the most common manner of death, followed by accidents.  **Digging Deeper into the Data:**  Analyzing how the day of the week is related to number of deaths in a given age group:<jupyter_code>%%bigquery --project=ba775-team9-b2
WITH weekday_age as (
    select
      a.age_consolidated as age_group, 
        a.day_of_week_of_death,  b.total_deaths_by_age,
        round((count(a.day_of_week_of_death)/total_deaths_by_age)*100,2) as percent_of_total,
    FROM `ba775-team9-b2.death_data_us.Mortailty_with_string_categories` as a
    INNER JOIN (
        SELECT count(*) as total_deaths_by_age, age_consolidated
        FROM `ba775-team9-b2.death_data_us.Mortailty_with_string_categories`
        GROUP BY age_consolidated
        
    ) as b
    on a.age_consolidated = b.age_consolidated
    WHERE a.age_consolidated <> 'Age not stated' and a.day_of_week_of_death <>'Unknown'
    GROUP BY age_group, day_of_week_of_death, total_deaths_by_age
    ORDER BY b.total_deaths_by_age DESC, percent_of_total DESC) 

    SELECT  * FROM weekday_age 
    order by percent_of_total desc
    limit 10;<jupyter_output><empty_output><jupyter_text>The above results show the % of deaths between 15-34 year olds tend to be higher on the weekends, while % of deaths tend to be more even across the week for higher age groups.  Initial analysis of death counts by education:<jupyter_code>%%bigquery --project=ba775-team9-b2
select education_level,
     COUNT(*) as deaths_counts,
from `ba775-team9-b2.death_data_us.Mortailty_with_string_categories`
where education_level not like  '%unknown%'
GROUP BY education_level
ORDER BY education_level DESC;<jupyter_output><empty_output><jupyter_text>Based on the above results, we can see that death counts are highest for the individuals with high school diploma and lowest for the ones that have doctorate or professional degrees; however, this analysis is impacted by the fact that there are fewer people with higher levels of education.  Analyzing % deaths by education level for each age group:<jupyter_code>%%bigquery --project=ba775-team9-b2
WITH C as (
    select
        a.age_code, 
        a.education_level, 
        count(a.sex) as deaths_by_age_and_education,
        b.total_deaths_by_education
    FROM `ba775-team9-b2.death_data_us.Mortailty_with_string_categories` as a
    INNER JOIN (
        SELECT count(*) as total_deaths_by_education, education_level
        FROM `ba775-team9-b2.death_data_us.Mortailty_with_string_categories`
        GROUP BY education_level 
        
    ) as b
    on a.education_level = b.education_level
    WHERE a.education_level <> 'unknown or none'
    GROUP BY age_code, education_level, total_deaths_by_education
    ORDER BY b.total_deaths_by_education DESC, deaths_by_age_and_education DESC 
)

SELECT *, (round((deaths_by_age_and_education  / total_deaths_by_education),3)*100) as percent_of_total
FROM C 
order by percent_of_total desc
limit 10;<jupyter_output><empty_output><jupyter_text>The analysis between age groups and education levels indicated a higher % of deaths for people with education levels of bachelors and above were in the 65+ year age group, compared to deaths amongst people with fewer years of education.  Investigating if the education level and age are related to the injury at work and results in higher or lower death counts:<jupyter_code>%%bigquery --project=ba775-team9-b2
 select age_consolidated as age_during_death,education_level,injury_at_work ,((count(injury_at_work)/total_death_count)*100) as percent_of_total
 FROM( select count(*) as total_death_count
from `ba775-team9-b2.death_data_us.Mortailty_with_string_categories` where injury_at_work in ('Y','N') and education_level<>'unknown or none' and age_consolidated <> 'Age not stated'),
  `ba775-team9-b2.death_data_us.Mortailty_with_string_categories`
 where injury_at_work in ('Y','N') and education_level<>'unknown or none' and age_consolidated <> 'Age not stated'
 group by age_consolidated,education_level,injury_at_work,total_death_count
 order by percent_of_total desc
 limit 10;<jupyter_output><empty_output><jupyter_text>Results from above query show that % deaths due to injury at work is lower for people with doctorate or masters degree, compared to people with education levels of high school diplomas or below. An important external variable that we did not have visibility into would have been the type of occupation people in these age groups had.  Analyzing death counts by specific age groups and activity codes along with manner of death:<jupyter_code>%%bigquery --project=ba775-team9-b2
WITH activity_code_age as (
    select
      a.age_consolidated as age_group, 
        a.activity_code,  a.manner_of_death, b.total_deaths_by_age,
        (round((count(a.activity_code)/total_deaths_by_age),2)*100) as percent_of_total,
    FROM `ba775-team9-b2.death_data_us.Mortailty_with_string_categories` as a
    INNER JOIN (
        SELECT count(*) as total_deaths_by_age, age_consolidated
        FROM `ba775-team9-b2.death_data_us.Mortailty_with_string_categories`
        where age_consolidated <> 'Age not stated'  and activity_code<>'N/A' and activity_code<>'Unspecified Activities'
        GROUP BY age_consolidated
        
    ) as b
    on a.age_consolidated = b.age_consolidated
    WHERE a.age_consolidated <> 'Age not stated'  and a.activity_code<>'N/A' and a.activity_code not in ('Unspecified Activities','Other-Specified Activities')
    and manner_of_death <>'Could not determine'
    GROUP BY age_group, a.activity_code, manner_of_death, total_deaths_by_age
    )

    SELECT  * FROM activity_code_age order by percent_of_total desc
    limit 10;<jupyter_output><empty_output><jupyter_text>The data indicates that people at 65+ years, and 0-14 years had their highest % of deaths when facing accidents while engaged in vital activities.  Analyzing how place of injury is realted to % of deaths for a given age group:<jupyter_code>%%bigquery --project=ba775-team9-b2
select age_consolidated,
        place_of_injury,
        count(place_of_injury) as death_by_place_of_injury,
        (round((count(place_of_injury)/total_death_count),2)*100) as percent_of_total
 FROM ( select count(*) as total_death_count
from `ba775-team9-b2.death_data_us.Mortailty_with_string_categories`
where place_of_injury not in ('Other or Not Recorded','Other Specified Places')
),
 `ba775-team9-b2.death_data_us.Mortailty_with_string_categories`
 where place_of_injury not in ('Other or Not Recorded','Other Specified Places')
 group by age_consolidated,place_of_injury,total_death_count
 order by percent_of_total desc
 limit 10;<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup