layout

By default, the <title> tag for this page is determined by the title field (in the Content group). You can override the tag value here.

metaTitle

media

bottomSections

By default, the description tag for this post is taken from the Excerpt field. You can override the tag value here.

metaDescription

By default, the "og:image" meta tag set for social sharing this post points to the Featured Image field (in the Content group). You can override that image here.

socialImage

markdown_content

If enabled, the title suffix defined in the site configuration is appended to the title tag of this page.

addTitleSuffix

To add or override any meta tag for this page, add entries to this list. Entries defined here take precedence over any other defaults.

metaTags

title

colors

date

author

excerpt

featuredImage

Creating A Cincinnati City Employee Dashboard Using SQL And Tableau

## Motivation

Covid-19 is a terrible and deadly virus that has wreaked havoc over the world over the last 3 years. Thanks to the brilliant and diligent work done by researchers throughout the world to create a vaccine and the companies and governments that facilitated the administration of the vaccine, the number of infections, hospitalizations, and deaths reduced dramatically leading us as a world to be able to enjoy day to day life again. With the initial vaccine coming out a few years ago I wanted to look and see how not only the United States did when it came to vaccinations but how countries around the world did and how they compare. Additionally, I wanted to look at how each state in the United States compared when it comes to vaccinations. To use the interactive dashboard of both the worldwide trends and United States trends click [here](https://public.tableau.com/app/profile/kevahn.mcdonald/viz/U_SandWorldCovid-19VaccineTrends/Dashboard1)

## Code

SQL queries for worldwide dashboard

**–-Removing unneeded row**

```
DELETE
```

```
FROM VaccineData..WorldVaccine
```

```
WHERE Country IN ('World', 'iran')
```

\*\*--Getting total amount of vaccination in the world \*\*

```
Select SUM(Doses_Administered) AS Total_Vaccines
```

```
FROM VaccineData..WorldVaccine
```

**--country with the least amout of vaccines given per capita**

```
Select Country, Doses_per_1000
```

```
FROM VaccineData..WorldVaccine
```

```
WHERE Doses_per_1000 != 0
```

```
ORDER BY Doses_per_1000 DESC
```

**--country with the most amount of vaccines given per capita**

```
Select TOP 1 Country, Doses_per_1000
```

```
FROM VaccineData..WorldVaccine
```

```
WHERE Doses_per_1000 != 0
```

```
ORDER BY Doses_per_1000 DESC
```

**-- AVG amout of vaccines given per capita**

```
Select AVG(Doses_per_1000) AS Avg_Vac_Per_Capita
```

```
FROM VaccineData..WorldVaccine
```

```
WHERE Doses_per_1000 != 0
```

**--Percent of world vaccinated by country**

```
Select Country, AVG(Fully_Vaccinated_Population) AS Percent_World_Vaccinated_By_Country
```

```
FROM VaccineData..WorldVaccine
```

```
WHERE Fully_Vaccinated_Population != 0
```

```
Group by Country
```

**--Trend of fully vaccinated per month by continent per 100**

**--Asia**

```
SELECT continent, date, people_fully_vaccinated_per_hundred
```

```
FROM VaccineData..DailyVaccine
```

```
Where continent = 'Asia'
```

**--NA**

```
SELECT continent, date, people_fully_vaccinated_per_hundred
```

```
FROM VaccineData..DailyVaccine
```

```
Where continent = 'North America'
```

**--SA**

```
SELECT continent, date, people_fully_vaccinated_per_hundred
```

```
FROM VaccineData..DailyVaccine
```

```
Where continent = 'South America'
```

**--Europe**

```
SELECT continent, date, people_fully_vaccinated_per_hundred
```

```
FROM VaccineData..DailyVaccine
```

```
Where continent = 'Europe'
```

**--Africa**

```
SELECT continent, date, people_fully_vaccinated_per_hundred
```

```
FROM VaccineData..DailyVaccine
```

```
Where continent = 'Africa'
```

**--Oceania**

```
SELECT continent, date, people_fully_vaccinated_per_hundred
```

```
FROM VaccineData..DailyVaccine
```

```
Where continent = 'Oceania'
```



Recent Covid-19 Vaccine Trends Dashboard Using Tableau and SQL Queries

## Motivation

While attending Kent State University my favorite place to get a bite to eat and watch a sports game with friends is a bar called Ray's Place. Since my friends and I love it so much I thought it would be a great idea to go through their yelp reviews, data scrape them, and turn them into a usable dataset for future analysis.

## Code used

```
library(rvest)
library(tidyverse)
```

```
url <- "https://www.yelp.com/biz/rays-place-kent"
page <- read_html(url)
```

```
#Getting Reviews From All Pages
pageNums <- page %>%
html_elements(xpath = "//div[@class=' border-color--default__09f24__NPAKY text-align--center__09f24__fYBGO']") %>%
html_text() %>%
str_extract('of.*') %>%
str_remove('of ') %>%
as.numeric()
pageSequence <- seq(from = 0, to = (pageNums * 10)-10, by = 10)
```

```
#store items into vectors
usernames_all = c()
locations_all = c()
dates_all = c()
reviewtext_all = c()
ratings_all = c()
```

```
#For loop to retrieve values from all pages
for(i in pageSequence) {
if (i == 0) {
page <- read_html(url)
} else {
page <- read_html(paste0(url, '?start=', i))
}
#Customer Names
usernames <- page %>%
html_elements(xpath = "//div[starts-with(@class, ' user-passport')]") %>%
html_elements(xpath = ".//a[starts-with(@href, '/user_details')]") %>%
html_text()
```

```
#Customer Locations
locations <- page %>%
html_elements(xpath = "//div[starts-with(@class, ' user-passport')]") %>%
html_elements(xpath = ".//span[@class= ' css-qgunke']") %>%
html_text() %>%
.[.!="Location"]
```

```
#Date Review Was Left
dates <- page %>%
html_elements(xpath = "//div[starts-with(@class, ' review')]") %>%
html_elements(xpath = ".//span[@class= ' css-chan6m']") %>%
html_text()
```

```
#Customer Review Text
reviewtext <- page %>%
html_elements(xpath = "//div[starts-with(@class, ' review')]") %>%
html_elements(xpath = "(.//p[starts-with(@class, 'comment')])[1]") %>%
html_text()
```

```
#Customer Rating
ratings <- page %>%
html_elements(xpath = "//div[starts-with(@class, ' review')]") %>%
html_elements(xpath = "(.//div[contains(@aria-label, 'star rating')])[1]") %>%
html_attr("aria-label") %>%
str_remove_all(" star rating") %>%
as.numeric()
```

```
usernames_all = append(usernames_all, usernames)
locations_all = append(locations_all, locations)
dates_all = append(dates_all, dates)
reviewtext_all = append(reviewtext_all, reviewtext)
ratings_all = append(ratings_all, ratings)
```

```
#add N/As to all missing data
length(usernames_all) <- length(dates_all)
length(locations_all) <- length(dates_all)
length(ratings_all) <- length(dates_all)
length(reviewtext_all) <- length(dates_all)
```

```
#removing all rows that contains N/A
na.omit(df)
```

```
#removing duplicate reviews
dupe <- df [!duplicated(df[c('Review')]),]
```

```
}
```

```
#Creating data frame
df <- data.frame('Date' = dates_all,
'Username' = usernames_all,
'Located' = locations_all,
'Rating' = ratings_all,
'Review' = reviewtext_all
)
```

```
#Save data frame as a CSV
write.csv(df, "yelp-review.csv")
```

```
view(df)
```

***

## Dataset

Click [here](https://github.com/kevahn/Yelp-Review) for the github repository of the project including a CSV of the created dataset!


Web Scraping Data From Yelp Reviews In R

## Motivation

Covid-19 is a very serious disease that has wreaked havoc amongst the world over the last three years. The disease has killed millions of people throughout the world so it is vitally important that we as a society continue to build our knowledge of this disease and its effects so that we are better protected and prepared for however this disease continues to advance. That is why when I found a data set on how covid-19 is affecting each state in the United States  I was very eager to use my data analytic skills in excel to help build my own and others' understanding of this terrible disease.

## Cleaning

**Before**

![](https://preview--portfolio-website-72c09.stackbit.dev/images/before.png)

**After**

![](/images/after2.png)

**Changes**

One of the first changes was fixing the width of all the cells so that all text could be easily readable.

The next change that was done was adding the columns "Infection Percent By State" and "Death Percent By State". This was done so that a person viewing the data could get an easier understanding of how likely it is for someone to get infected or die in each state from covid-19. These columns were done by diving the number infected or died by the total population of the state and then multiplying by 100 with the code "=(C2/G2)\*100".

Another change was adding a new bracket column for the population. This was done so that visually things would not look too busy when reading the charts and graphs on the dashboard. The way that these brackets were created was by writing a long "if and" statement as seen here: "=IF(AND(G2>35000000,G2<40000000),"35,000,000-40,000,000",IF(AND(G2>30000000,G2<35000000),"30,000,000-35,000,000",IF(AND(G2>25000000,G2<30000000),"25,000,000-30,000,000",IF(AND(G2>20000000,G2<25000000),"20,000,000-25,000,000",IF(AND(G2>15000000,G2<20000000),"15,000,000-20,000,000",IF(AND(G2>10000000,G2<15000000),"10,000,000-15,000,000",IF(AND(G2>5000000,G2<10000000),"5,000,000-10,000,000",IF(AND(G2>0,G2<5000000),"0-5,000,000","Invalid"))))))))"

## Analyzing

After the cleaning step comes analyzing. One of the questions that I came up with that I was most intrigued by and wanted to find the answer to was  "Does poverty have an effect on your likelihood of dying of covid-19". In order to find out this answer a Pivot table was created.

![](/images/Death%20v%20income.png)

Another question that I had was "What is the comparison between infection percent and death percent". A pivot table was also used to find a conclusion to that question.

![](/images/dvi.png)

The next question that I had was " How does state population correlate to the number of deaths that occurred in that state due to covid-19".  A pivot plot was used to answer the questions.

![](/images/death%20v%20pop.png)

Lastly, after reading articles that ICU beds were extremely limited during the peak of covid-19, I wanted to see if there was a correlation between the number of ICU beds in the state and the number of deaths each state had. To find an answer to this a pivot table was created.

![](/images/icu%20v%20death.png)

## Visualizing

The charts and graphs from these pivot tables are located in the Covid-19 dashboard at the top of the page. 

## Findings 

As you can see from the chart the findings for the first question "Does poverty have an effect on your likelihood of dying of covid-19" were that there is a slight negative correlation between income and deaths (the lower the state's income the more deaths the state will have).

Findings from the second question "What is the comparison between infection percent and death percent" can also be seen in the graph listed in the dashboard.  What can be stated from the data in the graph is that your chances of dying from covid -19 after being infected are fairly low. This does not take into account the long-term effects that this disease causes which can increase the death percentage in the future. 

In the third question "How does state population correlate to the number of deaths that occurred in that state due to covid-19" we can see what is the strongest predictor of death from covid-19. What the graph shows is a strong positive correlation between state population and the number of deaths (the higher the population, the more deaths a state will have). 

The last question which had a look at the correlation between the number of ICU beds and deaths from covid-19 had some interesting findings. Looking at the graph that was created from the analysis that was done in the pivot table the correlation did not seem to support what I thought it would. The finding was that the more ICU beds a state had the more deaths a state had as well. This is likely contributed more to the population of the states as states with higher populations will have more ICU beds but also higher deaths as stated in an earlier analysis. 













Cleaning, Analyzing, and Visualizing United States Covid-19 Data In Excel

## Motivation

As a psychology major, substance use disorder and how that continues to affect the United States has always been extremely fascinating to me. After finding the dataset on Connecticut overdoses from the years 2012 - 2018 while scrolling through Kaggle.com, I couldn't help but throw it into SQL server and Tableau and try to discover some findings that would help myself and hopefully, others better understand this epidemic that is affecting a lot of people.

## Queries in SQL

**The average age of overdoses**

```
SELECT ROUND(AVG(Age)) AS Average_Age_OD
```

```
FROM drug_Death.DrugDeathCT;
```

**The number of overdoses from 2012-2018**

```
SELECT COUNT(DISTINCT ID) AS Number_Of_Overdoses
```

```
FROM  drug_Death.DrugDeathCT;
```

**The number of overdoses in each Connecticut city**

```
SELECT COUNT(DISTINCT ID) AS OverdoseByCity, DeathCity
```

```
FROM drug_Death.DrugDeathCT
```

```
GROUP BY DeathCity
```

```
ORDER BY OverdoseByCity DESC;
```

**The top 5 drugs causing the most overdoses**

```
--Heroin
```

```
SELECT COUNT(Heroin) AS ODs_By_Heroin FROM drug_Death.DrugDeathCT
```

```
WHERE Heroin = 1;
```

```
--Cocaine
```

```
SELECT COUNT(Cocaine) AS ODs_By_Cocaine, FROM drug_Death.DrugDeathCT
```

```
WHERE Cocaine = 1;
```

```
--Fentanyl
```

```
SELECT COUNT(*) AS ODs_By_Fentanyl FROM drug_Death.DrugDeathCT
```

```
WHERE Fentanyl_Analogue = 1;
```

```
--Oxycodone
```

```
SELECT COUNT(*) AS ODs_By_Oxycodone FROM drug_Death.DrugDeathCT
```

```
WHERE Oxycodone = 1;
```

```
--Oxymorphone
```

```
SELECT COUNT(*) AS ODs_By_Oxymorphone FROM drug_Death.DrugDeathCT
```

```
WHERE Oxymorphone = 1;
```

```
--Alcohol
```

```
SELECT COUNT(*) AS ODs_By_Alcohol FROM drug_Death.DrugDeathCT
```

```
WHERE Ethanol = 1;
```

```
--Hydrocodone
```

```
SELECT COUNT(*) AS ODs_By_Hydrocodone FROM drug_Death.DrugDeathCT
```

```
WHERE Hydrocodone = 1;
```

```
--Benzodiazepine 
```

```
SELECT COUNT(*) AS ODs_By_Benzodiazepines FROM drug_Death.DrugDeathCT
```

```
WHERE Benzodiazepine = 1;
```

```
--Methadone
```

```
SELECT COUNT(*) AS ODs_By_Methadone FROM drug_Death.DrugDeathCT
```

```
WHERE Methadone= 1;
```

T**he number of overdoses by each race**

```
SELECT COUNT(DISTINCT ID) AS OverdoseByRace, Count(Race), Race
```

```
FROM drug_Death.DrugDeathCT
```

```
WHERE Race is not null
```

```
GROUP BY Race 
```

```
ORDER BY OverdoseByRace DESC;
```

## Findings

After writing these queries, running them, and analyzing the results, we can state the findings which can be seen in the Tableau dashboard at the top of the page or [here](https://public.tableau.com/app/profile/kevahn.mcdonald/viz/ConnecticutOverdoseDeaths2012-2018/Dashboard1?publish=yes) (this link will take you to the tableau dashboard where the interactive map can be used). The main findings were that the average age of overdoses in the state of Connecticut was 42 years old, 5,103 overdoses had occurred, the city of Hartford had the most overdoses by about 200, heroin caused the most overdoses with fentanyl being a close second, and white people were far more likely to overdose in the state of Connecticut than any other race.

## Actions

Now that the data has been cleaned, explored, and analyzed the most important part is to take action on these findings. What a stakeholder could do is use these statistics to create programs that try to lessen drug use in the communities that are most affected, like middle-aged white individuals.


Projects

Creating A Cincinnati City Employee Dashboard Using SQL And Tableau

Recent Covid-19 Vaccine Trends Dashboard Using Tableau and SQL Queries

Web Scraping Data From Yelp Reviews In R

Cleaning, Analyzing, and Visualizing United States Covid-19 Data In Excel

Interactive Dashboard In Tableau Using SQL Queries