Data Wrangling

Before beginning the exercises, create a sample data frame named employees for use in the exercises.

Warning Dataset for question 1:20

Important Dataset for question 21:30

Questions: 1:10

Tip 1. Filter and Select:

Select only the Name and Department columns of employees who are older than 30.

Solution:

employees |> 
  filter(Age > 30) |> 
  select(Name, Department)

Tip 2. Mutate and Arrange:

Create a new column called Bonus that is 10% of Salary, then arrange the data frame by Bonus in descending order.

Solution:

employees |>
  mutate(Bonus = Salary * 0.10) |>
  arrange(desc(Bonus))

Tip 3. Filter, Select, and Arrange:

Filter for employees who work in the “IT” department, select only their Name and Salary, and arrange the result in ascending order by Salary.

Solution:

employees |>
  filter(Department == "IT") |>
  select(Name, Salary) |>
  arrange(Salary)

Tip 4. Group By and Summarize:

Find the average Salary by Department.

Solution:

employees |>
  group_by(Department) |>
  summarise(avg_salary = mean(Salary))

Tip 5. Filter, Mutate, and Select:

For employees with more than 10 years of experience, create a new column called Seniority with values “Senior” if Years_Experience is greater than 10, otherwise “Junior”. Select only the Name, Years_Experience, and Seniority columns.

Solution:

employees |>
  filter(Years_Experience > 10) |>
  mutate(Seniority = ifelse(Years_Experience > 10, "Senior", "Junior")) |>
  select(Name, Years_Experience, Seniority)

Tip 6. Filter and Summarize:

Calculate the average Salary for employees over the age of 30.

Solution:

employees |>
  filter(Age > 30) |>
  summarise(avg_salary = mean(Salary))

Tip 7. Group By, Mutate, and Summarize:

Calculate the total Salary for each department and then create a new column Total_Budget that is 1.1 times this total (representing a 10% budget increase).

Solution:

employees |>
  group_by(Department) |>
  summarise(Total_Salary = sum(Salary)) |>
  mutate(Total_Budget = Total_Salary * 1.10)

Tip 8. Filter, Arrange, and Select:

Filter the employees who have a Salary above 60,000, arrange them by Age, and select only the Name, Age, and Salary columns.

Solution:

employees |>
  filter(Salary > 60000) |>
  arrange(Age) |>
  select(Name, Age, Salary)

Tip 9. Mutate and Filter:

Add a new column Age_Group that categorizes employees as “Young” if Age is less than 30, and “Experienced” if 30 or older. Then filter for employees in the “Experienced” age group.

Solution:

employees |>
  mutate(Age_Group = ifelse(Age < 30, "Young", "Experienced")) |>
  filter(Age_Group == "Experienced")

Tip 10. Group By, Summarize, and Arrange:

Find the average Years_Experience by Department and arrange the departments in descending order of experience.

Solution:

employees |>
  group_by(Department) |>
  summarise(avg_experience = mean(Years_Experience)) |>
  arrange(desc(avg_experience))

Questions: 11:20

Note 11. Filter, Group By, and Summarize:

Calculate the average Salary for employees in the “Finance” and “Marketing” departments only.

Solution:

employees |>
  filter(Department %in% c("Finance", "Marketing")) |>
  group_by(Department) |>
  summarise(avg_salary = mean(Salary))

Note 12. Filter, Mutate, and Summarize:

Add a new column Adjusted_Salary that is 95% of the current Salary for employees in “HR” and “Finance”. Then calculate the average Adjusted_Salary for each department.

Solution:

employees |>
  mutate(Adjusted_Salary = ifelse(Department %in% c("HR", "Finance"), Salary * 0.95, Salary)) |>
  group_by(Department) |>
  summarise(avg_adjusted_salary = mean(Adjusted_Salary))

Note 13. Select and Group By:

Select the Department and Salary columns, then calculate the sum of Salary for each department.

Solution:

employees |>
  select(Department, Salary) |>
  group_by(Department) |>
  summarise(total_salary = sum(Salary))

Note 14. Filter and Group By:

Filter for employees over the age of 40, then group by Department and calculate the count of employees in each department.

Solution:

employees |>
  filter(Age > 40) |>
  group_by(Department) |>
  summarise(employee_count = n())

Note 15. Mutate, Filter, and Arrange:

Create a column Experience_Ratio that is Years_Experience divided by Age. Filter for employees with a Experience_Ratio greater than 0.3, then arrange by Experience_Ratio in descending order.

Solution:

employees |>
  mutate(Experience_Ratio = Years_Experience / Age) |>
  filter(Experience_Ratio > 0.3) |>
  arrange(desc(Experience_Ratio))

Note 16. Filter, Summarize, and Arrange:

Filter for employees with a Salary greater than 60,000, then calculate the minimum and maximum Age in this group, and arrange by minimum Age.

Solution:

employees |>
  filter(Salary > 60000) |>
  summarise(min_age = min(Age), max_age = max(Age)) |>
  arrange(min_age)

Note 17. Group By and Mutate:

For each department, calculate the average Salary, and then add a column Above_Avg_Salary that marks TRUE if an employee’s Salary is above their department’s average, and FALSE otherwise.

Solution:

employees |>
  group_by(Department) |>
  mutate(avg_salary = mean(Salary)) |>
  mutate(Above_Avg_Salary = Salary > avg_salary) |>
  select(Name, Department, Salary, Above_Avg_Salary)

Note 18. Filter, Mutate, and Select:

Filter for employees in the “IT” department, add a column called Tenure_Level that labels them as “Experienced” if they have 10 or more years of experience, otherwise “New”. Select only the Name, Tenure_Level, and Salary.

Solution:

employees |>
  filter(Department == "IT") |>
  mutate(Tenure_Level = ifelse(Years_Experience >= 10, "Experienced", "New")) |>
  select(Name, Tenure_Level, Salary)

Note 19. Group By, Summarize, and Arrange:

Group by Department and calculate the total number of employees in each department. Arrange in descending order by this count.

Solution:

employees |>
  group_by(Department) |>
  summarise(employee_count = n()) |>
  arrange(desc(employee_count))

Note 20. Mixed Operations:

Filter for employees in “HR” or “Marketing”, group by Department, calculate the average Salary and Years_Experience in each department, and arrange by average Salary in ascending order.

Solution:

employees |>
  filter(Department %in% c("HR", "Marketing")) |>
  group_by(Department) |>
  summarise(avg_salary = mean(Salary), avg_experience = mean(Years_Experience)) |>
  arrange(avg_salary)

Questions: 21:30

Tip 21. Left Join:

Perform a left join on employees2 and salaries based on EmployeeID. Show all columns, and observe which employees have missing salary information.

Solution:

employees2 |>
  left_join(salaries, by = "EmployeeID")

Tip 22. Right Join:

Perform a right join on employees2 and salaries based on EmployeeID. Check which employees in the salaries data frame do not have corresponding information in employees.

Solution:

employees2 |>
  right_join(salaries, by = "EmployeeID")

Tip 23. Inner Join:

Use an inner join to combine employees2 and salaries by EmployeeID. Display only the records where both employee information and salary information are available.

Solution:

employees2 |>
  inner_join(salaries, by = "EmployeeID")

Tip 24. Full Join:

Use a full join to combine employees2 and salaries based on EmployeeID. Identify any employees or salary records without a match in the other table.

Solution:

employees2 |>
  full_join(salaries, by = "EmployeeID")

Tip 25. Join with Filtering:

Perform a left join on employees2 and salaries based on EmployeeID, then filter to show only employees with salaries above 60,000.

Solution:

employees2 |>
  left_join(salaries, by = "EmployeeID") |>
  filter(Salary > 60000)

Tip 26. Multiple Joins:

First, perform a left join between employees2 and salaries based on EmployeeID. Then, join the resulting data with departments to match each employee with their department manager based on the Department column.

Solution:

employees2 |>
  left_join(salaries, by = "EmployeeID") |>
  left_join(departments, by = c("Department" = "DepartmentName"))

Tip 27. Summarize After Join:

Perform an inner join between employees2 and salaries based on EmployeeID. Then, calculate the average salary for each department.

Solution:

employees2 |>
  inner_join(salaries, by = "EmployeeID") |>
  group_by(Department) |>
  summarise(avg_salary = mean(Salary, na.rm = TRUE))

Tip 28. Self-Join:

Use left_join to join the employees2 data frame to itself on the Department column to compare employees within the same department.

Solution:

employees2 |>
  left_join(employees, by = "Department", suffix = c("_1", "_2")) |>
  filter(EmployeeID_1 != EmployeeID_2)

Tip 29. Join with Different Key Columns:

Use full_join to combine employees2 and departments by matching the Department column in employees2 with the DepartmentName column in departments.

Solution:

employees2 |>
  full_join(departments, by = c("Department" = "DepartmentName"))

Tip 30. Identify Missing Data with Outer Join:

Perform a full join on employees2 and salaries based on EmployeeID. Filter the result to show only the records where either Name or Salary is NA to identify employees without salary data or vice versa.

Solution:

employees2 |>
  full_join(salaries, by = "EmployeeID") |>
  filter(is.na(Name) | is.na(Salary))