Evergreen Health Center

We created dummy hospital data using the Python FAKER package.

The dataset includes the following administration and operational data. See Annex-1 for details on data.

We performed exploratory data analyses on the given data in Python. These analyses include understanding the data and its type and basic statistics including minimum, maximum, outliers, and quartile range. Further to understand the data, we created a correlation matrix, box plots, histogram, and bins to see the data distribution and identify skewness and kurtosis.

What questions were asked and what do the end users expect from us?

a. HR and Administration Analysis:

    1. Employee turnover analysis based on department and designation.
    2. Salary distribution and comparison across departments and designations.
    3. Gender diversity analysis across departments.
    4. Experience vs. salary analysis.

b. Operations Analysis:

    1. Patient demographics (age, gender) across different diseases and departments.
    2. Disease frequency analysis across departments.
    3. Revenue analysis based on patient billing across departments.
    4. Patient load analysis (entry and discharge dates) for operational efficiency.

After a basic understanding of the data and the demand from the end-user, we imported, analyzed, and transformed the data in Power Query as per our requirements.

After the data transformation, we created some new columns and measures for detailed analysis. These DAX are given in the Annex-2

Once the data is cleaned, transformed, and measures were created, we designed a roadmap listing the type of analysis and representation on different types of graphs. See Annex-3 for details.

Annex-1

1. Administration Dataset Variables Explanation

• Employee ID: A unique identifier for each employee.
• Name: The employee’s name.
• Gender: The employee’s gender (categorical: Male, Female).
• Age: The employee’s age.
• Salary: The annual salary of the employee.
• Department: The department where the employee works (e.g., HR, Finance, IT).
• Designation: The job role/designation of the employee (e.g., Nurse, Doctor, Technician).
• Years of Experience: The total years of work experience.
• Date of Joining: The date the employee joined the organization.
• Last Appraisal Date: The last date of performance appraisal.

2. Operations Dataset Variables Explanation

• Patient ID: A unique identifier for each patient.
• Patient Name: The patient’s name.
• Gender: The patient’s gender.
• Age: The patient’s age.
• Disease: The type of disease the patient was diagnosed with (e.g., Heart Disease, Stroke).
• Department: The hospital department where the patient was treated.
• Doctor Visited: The name of the doctor the patient visited.
• Entry Date: The date the patient was admitted.
• Discharge Date: The date the patient was discharged.
• Total Bill: The total amount billed for the patient’s treatment.

Annex-2

Key DAX Commands

1. Total Employee Count by Department:

Total Employees = COUNT(administration_data[Employee ID])

2. Average Salary by Department:

Average Salary = AVERAGE(administration_data[Salary])

3. Total Revenue by Department:

Total Revenue = SUM(operations_data[Total Bill])

4. Patient Count by Disease Type:

Total Patients = COUNT(operations_data[Patient ID])

5. Average Length of Stay for Patients:

Period of Stay = AVERAGEX(operations_data, DATEDIFF(operations_data[Entry Date], operations_data[Discharge Date],DAY))

6. Employee Experience Grouping (e.g., for Seniority Analysis):

Added a new column in Administration Data, with the following conditions:

Years of Experience < 5, "Junior",

Years of Experience >= 5 & < 15, "Mid-Level",

Years of Experience >= 15, "Senior"

Annex-3

Graphs for Each Indicator in Power BI:

1. Employee Count by Department (Bar Chart)

   • Visualize the distribution of employees across different departments.

2. Average Salary by Department (Column Chart)

   • Compare salary distribution across departments.

3. Gender Distribution (Pie Chart or Donut Chart)

   • Show the gender composition of the workforce.

4. Experience vs. Salary Analysis (Scatter Plot)

   • Analyze how experience correlates with salary across departments.

5. Patient Count by Disease (Stacked Bar Chart)

   • Visualize the frequency of different diseases across departments.

6. Total Revenue by Department (Treemap)

   • Show how revenue is distributed across different departments.

7. Average Length of Stay by Department (Line Chart)

   • Track the average stay duration of patients over time.

8. Revenue Over Time (Line Chart)

   • Visualize monthly or quarterly revenue trends.