Aliens in America

1. Scenerio

For this project, you play the role of a newly hired Data Analyst for a pharmaceutical company. It's the year 2022 and aliens are well-known to be living among us. Unfortunately, some of the aliens are a bit... too alien... and would like to fit into society a bit more.

So, it's up to you to find the best state(s) we should market our new prescription.

Main Objective:

To help aliens fit into society.

Business Task:

To find the best state(s) to market our prescription.

Goal:

To find

1. If these aliens are hostile

2. Their diet

3. Their age

Process:

Our process will be based on six steps which are,

• Ask

• Prepare

• Process

• Analyse

• Share

• Act

2. Ask

In the year 2022, aliens are well-known to be living among humans, but some of the aliens are too alien and would like to fit in the society a bit more. We are asked to find the best state(s) to market our prescription.

The following questions will guide us;

How many hostile aliens are there?

What is their most preferred food?

Which state has more aliens?

What’s their age?

Which type of alien has the most population?

3. Prepare

3.1 Dataset used

The source of the data for this case study is

https://iklosowicz.gumroad.com/l/aliens-in-america?layout=profile&recommended_by=library

3.2 Privacy of Data

This fictional dataset has been made available for public use by Ian Klosowicz.

3.3 Data Organization

There are three CSV files available to us. These files and their description are as follows;

4. Process

4.1 Tools used

Excel: To view the data and write descriptions of the files.

SQL BigQuery: For data cleaning, sorting and analysing.

Tableau: For visualisations

4.2 Data Cleaning and Sorting:

To check for duplicate rows in all the files, following query was used

To check for null values in columns of all files, following query was used;

To check inner joins for matching ids of all three tables;

To check weather data type is correct or not, schema was used

There were no nulls or duplicates found. The data is now clean and ready for analysis.

5. Analysis

In this phase, we are going to run queries in SQL BigQuery to analyse data to answer our questions.

5.1 First we will see which type of aliens has more population

5.2 Let's find out how many aliens are hostile

Nearly half of the aliens are hostile but there are more non-aggressive aliens than aggressive aliens.

5.3 Now let's analyse their diet

First thing we want to find is how many types of food can aliens eat.

Aliens can eat 1498 types of total different foods. Looks like they can eat a lot of different varieties of food.

5.4 Let's check out their most favorite foods

Top 7 favorite foods aliens

5.5 How many times do aliens prefer eating food?

5.6 States in U.S with highest alien population

5.7 New table was created containing inner join of details and location

5.8 To check for the states in the U.S which have the most aggressive aliens.

5.9 Now let's find out age of aliens

New table was created for arranging aliens according to age group

After creating the table, we can now proceed with analysis and find out age of aliens.

I have arranged different age of aliens into age groups to analyze and find out which age group has most aliens. We can see that aliens between age 50-150 are more than the other age groups.

With this we have completed analyzing the data and we move towards the next step, share phase, where we will share our findings.

6. Share

In this phase we will share our findings about aliens in america. The following visualisations will help stakeholders have a better understanding of our analysis.

6.1 Let's first see how many aliens in america are hostile

We can see that there are a total 50,000 aliens currently residing in the US from which, 24,947 are hostile and 25,053 are non aggressive.

6.2 Food and diet of aliens in america

Now we will check out what type of food they prefer and their diet.

1) Top 30 foods of aliens

2) Diet of aliens

Most of the aliens only need to eat once or have only eaten once in their lifetime, which is 6,490 aliens, followed by 6,300 aliens who eat weekly. There are only 6,007 aliens who prefer eating monthly, which are the least of all.

6.3 And lastly, let's check the age of aliens in america

We can see that most of the aliens are between age 50-150. Aliens in the age group 120-350 consists of 16,561 aliens in total which is less than the age group of 50-150 aliens which make up 16,725 aliens.

6.4 Best State to market our prescription?

To answer this question we need to check which states has more alien population.

Three states, Texas, California and Florida, have the most alien population than any other state.

Considering the fact that almost half the aliens are hostile, we can see that Texas, California and Florida are also likely to hold the most aggressive aliens in US

7. Act

Conclusion:- Mostly half the aliens are aggressive and these aliens reside in the states which also have the highest population in the US, Texas, California and Florida. Our business should focus more on these states.

Applying Insights:- Considering the fact that most of these aliens eat only once in their lifetime and age between 50-150, our business needs to find a way to make them eat more regularly like humans. And also we need to find a way to calm the half of the population of aliens , so they are socially acceptable and more functional as a member of society.