DEV Community: Theophilus1320

Data Analysis With Microsoft Excel: People Analytics.

Theophilus1320 — Fri, 09 Aug 2024 12:08:09 +0000

Introduction

In this project, data cleaning, exploration and visualization was done using only Microsoft excel.
Analysis was done to to extract meaningful insights about the distribution and characteristics of the fellows across different host companies

Data structure:

This dataset originates from the HR department of the company. The dataset contains records of employees ranging from Company Name, Company Location, Fellow Name, Fellow Educational Background, Fellow Age, Fellow Gender, Fellow ID, Fellow Start Day etc.

Data cleaning and preparation:

The data cleaning and preparation phase was done to ensure the dataset is free from errors, outliers and duplicates.
Here is a picture of the dataset prior to cleaning:

Some of the steps taken to clean the data include:
1.Removal of duplicates
2.And the removal of Blank rows and columns

Here is a picture showing how the blank rows were removed from the dataset.

Analysis was done using pivot table and charts.
Slicers were also added for easy access to various niche of the dashboard.
The dashboard provides answers to the following :
1.Count of Fellows by Age
2.Count of Fellows by Gender
3.Top Ten companies with the most Fellows
4.Count of Fellows by Company Location

Here is a picture of the dashboard:

1)Count Of Fellows By Age
In this dataset, the age distribution of fellows reveals significant insights:

•Fellows Over the Age of 33: 3,861
•Fellows at the Age of 22: 2,024

This data shows that there is a higher number of fellows over the age of 33 while fellows over the age of 22 are the least among the fellow age groups.

The substantial number of fellows over the age of 33 suggests a more experienced workforce. This can be advantageous for roles requiring significant expertise and leadership. However, it also highlights the potential need for succession planning.

2)Count Of Fellows By Gender
In this dataset, the count of fellows by gender is as follows:
•Males:739
•Females:767

This indicates a slight majority of female fellows, who constitute approximately 50.93% of the total, while males make up about 49.07%
The near-equal gender distribution is indicative of a balanced gender representation, which is a positive sign for diversity and inclusion efforts.

3)Top Ten Companies With The Most Fellows
In this dataset, I analyzed the top ten companies with the most fellows. Notably:

•Health LTD has the highest number of fellows at 68.
•Build Holdings has the least among the top ten, with 38 fellows.

Companies with a higher number of fellows, like Health LTD, might have more robust talent acquisition and retention strategies. Conversely, Build Holdings, with fewer fellows, might benefit from insights into the strategies employed by higher-ranking companies to enhance their own talent pool.

In this dataset, the count of fellows by company location reveals the following:

•Gombe: Has the highest number of fellows at 319.
•Bauchi: Has the least number of fellows among the locations analyzed, with 133.

The significant number of fellows in Gombe suggests a strong talent pool in this location. This could be due to various factors such as the presence of major educational institutions, a favourable job market, or specific industry clusters.

Dashboard:

Exploratory Data Analysis Using SQL.

Theophilus1320 — Fri, 09 Aug 2024 11:04:38 +0000

INTRODUCTION
In this project, I carried out an exploratory analysis on Internet Usage around the world. The dataset contains records of Internet Users of ALL the countries in the world as of 2018.
The dataset contains columns like Country, Internet Users, Population and Percentage.
Using SQL, I was able to run some queries through the dataset and get answers to some questions and also to get insights.

DATA PREPARATION AND CLEANING.
The dataset was first loaded into Microsoft Excel in order to “clean” the dataset, rename the columns, remove outliers and to check for consistency in the dataset.
Here is a picture of the dataset in Microsoft Excel:

A database was then created in MYSQL, and the dataset was imported into the SQL workbench in order to begin analysis.
Picture of the dataset after being imported into the SQL workbench:,

ANALYSIS:
Analysis was done to get answers to some very important questions and to get an understanding of the dataset.

1)Total sum of Internet Users in the entire world:

The query above shows the total number of internet users in the entire world is 3,663,346,115. as of 2018.

2)Country with the highest number of Internet Users:

China has the highest number of internet users in the world.
with 765,367,947 users.

3)Country with the Lowest number of internet users:

Ascension has the lowest number of internet users in the world, with just 361 users. It is a volcanic island located in the Atlantic Ocean, with a total population of only 806 people.

4)Top Five Countries by number of Internet Users:

China, India, United States, Brazil and Japan have the highest number of internet users in the world.

5)Bottom five countries by number of internet users:

Ascension, Niue, Wallis and Futuna, Montserrat and Falkland Islands have the lowest number of internet users in the world.

6) Difference In Internet Users Between the Country with Highest and Lowest Users:

There is a significant difference of 765,367,586 between the countries with the highest and lowest number of internet users.

7)Country with the Highest Percentage of Internet Users:

Andorra has the highest percentage of Internet users, at 99%

8) Country with the Lowest Percentage of Internet Users:

Eritrea has the lowest percentage of internet users in the world, at 1%

9) Country With the Highest Ratio of Internet Users to population:

The Falkland Islands, with a total population of 2,910 and 2,881 internet users, have the highest ratio of internet users to population, at 99%.

10)Correlation Between Internet Users and Population:

Here, I wanted to determine if there is any correlation or relationship between a country's population and the number of internet users.

The query revealed a correlation coefficient of 0.9. This indicates a strong positive association, suggesting that countries with larger populations tend to have more internet users.

Data Analysis With Python: Analysis of the global development and Prosperity Index for the year 2023

Theophilus1320 — Thu, 08 Aug 2024 20:47:44 +0000

Introduction
The dataset for this project contains records of the global development and prosperity index for the year 2023
Data cleaning, analysis and visualization was done using python. The analysis provides answers to some important questions and to get an understanding of the dataset.

Data Structure:
Columns in the dataset include; Country Code, Country, Average Score, Safety Security, Personnel Freedom, Governance, Social Capital, Investment Environment, Enterprise Conditions, Market Access Infrastructure, Economic Quality, Living Conditions, Health, Education, Natural Environment.

•The necessary python libraries needed to carry out this analysis was imported into the python IDLE (Jupyter Notebook), And the dataset was loaded in to begin analysis.

Total numbers of column and rows present in the dataset shows 167 rows and 14 columns.

10 random samples of the dataset to see what the dataset looks like.

Data Cleaning
Data cleaning was done using the python pandas library in order to “clean “ the dataset and prepare it for analysis.

•Checking for missing values in the dataset

The image above shows the dataset had no missing values

The image above shows there were no duplicates in the dataset.

Data Analysis and Exploration

1)Top Ten Countries By Average Score Of The Global ProsperityIndex:
•The visualization shows the top ten countries ranked by their average scores on the Global Prosperity Index. These countries demonstrate strong performances across various metrics such as governance, education, health, and economic quality. The high scores indicate a robust and balanced approach to fostering prosperity and well-being for their citizens, reflecting effective policies and a favorable socio-economic environment.

2)Bottom Ten Countries by Average score:
•This list and visualization highlights areas where these countries may need to focus their efforts to improve their overall scores, contributing to better quality of life and development outcomes for their citizens. It serves as a valuable tool for policymakers, researchers, and stakeholders interested in international development and comparative analysis.

3)Areas Scored Highest by the top Ten countries:
This list and Visualization titled "Areas Scored Highest by the Top Ten Countries" illustrates the top-performing metrics for the ten countries with the highest average scores. These metrics encompass various dimensions of national success, including safety, personel freedom, governance, social capital, economic quality, and more.

4)Areas of Improvement for the Bottom Ten Countries:
This list and visualization highlights areas where these countries may need to focus their efforts to improve their overall scores, contributing to better quality of life and development outcomes for their citizens. It serves as a valuable tool for policymakers, researchers, and stakeholders interested in international development and comparative analysis.

5)Relationship Between Governance and Living Conditions:
The correlation of 0.71 between governance and living conditions underscores the importance of strong governance as a key driver for improving living conditions. This relationship suggests that efforts to enhance governance structures can have a significant positive impact on the quality of life of a country’s population. Policymakers and development organizations can use this insight to prioritize governance reforms as a strategy to elevate living conditions.

Data Analysis With Power BI: Sales Analysis.

Theophilus1320 — Thu, 08 Aug 2024 13:58:59 +0000

Introduction

The dataset for this project originates from a Burmese retail store's sales records for the year 2019. The analysis aimed to answer key questions that reflect important key performance indicators (KPIs) and to gain a thorough understanding of the metrics and trends within the data. This project offers an in-depth analysis of the sales records and provides insights to support the growth of the retail store.

Data structure

The dataset contains sales records for the year 2019. The data includes the following details: Invoice ID, Branch, City, Product line, Unit Price, Quantity, Tax, Total, Date, Time, Payment Method, Cost of Goods Sold, Gross Margin, Gross Income, Rating, Customer Type, and Gender.

Data Cleaning and Preparation

The dataset was loaded into Power BI's Power Query Editor for cleaning in preparation for analysis. Initially, the dataset contained an empty row and column, which were removed. Additionally, misspellings, duplicates, and outliers were corrected or removed to ensure that only unique and accurate values were used for the analysis.

Here is a picture of the dataset after cleaning:

Analysis and insights
The analysis revealed that the total cost of goods sold by the retail store is 30,759K, with a total gross income of 1,538K

Total Gross Income By Product Line

The Food and Beverages line generated the most profit for the retail store, while the Health and Beauty line was the least profitable.

What Payment Method Brought in the most Gross Income

In reference to the data above, more gross income was generated through cash payments compared to e-wallet and credit card payments.

Total Gross Income By City

The retail store also generated the highest gross income from sales to the city of Naypyitaw, while it earned the least gross income from sales to both Yangon and Mandalay.

Dashboard 2:Sales Volume & Distribution Analysis

The city with the highest amount of goods sold
The retail store sold goods to three cities which include: Naypitaw, Yangon and Mandalay

The retail store generated the most sales from the city of Naypyitaw, while Yangon and Mandalay recorded the least and similar amounts of sales.

The Payment method with the most unit of goods sold

The retail store made sales through payment methods like Cash, E-wallet and Credit Cards channels.

Analysis of the dataset showed that the retail store sold more units of goods through cash transactions compared to the e-wallet and credit card channels.
Goods Sold By Product Line

This analysis reveals that food and beverages were the best-selling product line, while health and beauty products were the least sold by the retail store.

Strategic Recommendations

1)Enhance Payment Channel Incentives:

Cash transactions generated more sales and profits compared to e-wallet and credit card channels. To boost sales through e-wallet and credit card transactions, consider offering discounts on purchases made via these channels. This could encourage increased usage and enhance profitability.

2)Boost Sales in Low-Performing Cities:

Cities like Yangon and Mandalay experienced lower sales. To increase awareness and sales in these areas, consider implementing targeted publicity campaigns. Utilize promotional emails, flyers, and other marketing materials to highlight the retail store's offerings. Additionally, opening new branches in these cities could significantly boost sales.

3)Stimulate Interest in Least Sold Product Lines:

Product lines such as Home and Lifestyle, and Health and Beauty products experienced lower sales. To stimulate consumer interest, consider creating combo or special packages featuring these products. This approach could attract more buyers and boost sales for these categories.

Health Care Analytics With Microsoft Excel

Theophilus1320 — Thu, 08 Aug 2024 13:17:44 +0000

Introduction

In this project, I carried out data analysis on this healthcare dataset of a hospital using Microsoft Excel, the aim of this exploratory data analysis was to gain insights into the most relevant factors that affect patient care.
This report provides a comprehensive analysis of the patient dataset, focusing on the following key variables: Gender, Medical Condition, Insurance Provider, Billing Amount, and Medication.
The objective of this analysis is to uncover trends and insights that can inform healthcare management decisions, improve patient care, and optimize billing processes.
Data cleaning, analysis, exploration and visualization was done using just Microsoft Excel.

Data Structure And Preparation

The dataset for this project was gotten from kaggle.com
The dataset contains Fourteen columns and over Ten thousand and one rows. Records in the dataset include; Gender, Medical Condition, Insurance Provider, Billing Amount, Medication, Admission type, Test results and so many more.
Here is what the dataset looks like:

Data Cleaning And Preparation

The dataset had no outliers, errors, duplicates, missing rows and columns, so the data cleaning process was smoothly done

Analysis And Insights:

An overview of the dashboard shows the most important metrics captured and analysed from the dataset, which include; Count of Patients By Gender, Number of patients per medical condition and the Total billing amount by insurance provider.

1)Count of patients By Gender:

Male Patients: The analysis shows that there are 4,925 male patients, accounting for 49% of the total patient population.

Female Patients: There are 5,075 female patients, representing 51% of the total patient population.
•The patient population is relatively evenly split, with a slight majority of female patients.
•The difference in percentage between male and female patients is just
2%.

2)Number of Patients per Medical Condition

Asthma: The analysis indicates that asthma is the most prevalent condition, with the highest number of patients diagnosed. This suggests a significant public health concern, requiring focused medical attention, patient education, and resource allocation. High asthma prevalence could be linked to environmental factors, genetic predisposition, or other socio-economic conditions.

Diabetes: On the other end of the spectrum, diabetes has the lowest patient count. While it is less prevalent compared to asthma, diabetes remains a critical condition requiring continuous management and monitoring. The lower count may be attributed to effective preventive measures, early detection, and management strategies, or possibly underreporting.

3) Total Billing Amount by Insurance Provider

Cigna: The analysis indicates that Cigna accounts for the highest total billing amount among the insurance providers. This implies that a significant portion of the healthcare provider’s revenue comes from patients insured by Cigna, the high billing amount could be due to the volume of services provided or the higher reimbursement rates negotiated with Cigna.

Medicare: On the other hand, Medicare has the lowest total billing amount. This could be attributed to lower reimbursement rates, fewer Medicare-covered patients, or a combination of both. Understanding this disparity is crucial for financial planning and resource allocation.

Overview of the Dashboard: