EMPLOYEE SURVEY

1. We use below libraries for Data import and wrangling.

library(readr)
library(dplyr)
library(tidyr)

1. Import reuired datasets of Employee survey and employee profile data.(readr is best suited to import data in tibble and where datatypes are preserved while importing without converting strings to factors by default)

head(survey)

head(employee)

1. Glimpse of dataset to understand which columns can be used for joining

glimpse(survey)

Observations: 165
Variables: 8
$ EMPID    <int> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 2...
$ BRANCH   <chr> "HO", "NORTH", "NORTH", "SOUTH", "SOUTH", "SOUTH", "SOUTH", "SOUTH", "EAST",...
$ FUNCTION <chr> "SALES", "SALES", "FINANCE", "FINANCE", "FINANCE", "FINANCE", "FINANCE", "SA...
$ EES      <dbl> 46.60, 41.73, 52.64, 41.56, 42.68, 40.07, 33.30, 62.44, 55.25, 52.18, 34.30,...
$ RR       <dbl> 44.73, 33.80, 57.90, 54.40, 53.19, 40.59, 63.28, 55.11, 57.60, 62.47, 46.66,...
$ PP       <dbl> 58.38, 35.10, 55.87, 61.95, 53.47, 44.83, 41.07, 62.99, 60.81, 47.18, 42.14,...
$ CG       <dbl> 54.51, 51.79, 54.53, 48.69, 48.79, 50.20, 41.34, 58.27, 54.63, 55.14, 47.18,...
$ LT       <dbl> 45.00, 52.14, 53.70, 47.86, 48.60, 44.40, 39.11, 53.22, 54.30, 49.72, 47.17,...

glimpse(employee)

Observations: 165
Variables: 6
$ EMPID    <int> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 2...
$ BRANCH   <chr> "HO", "NORTH", "NORTH", "SOUTH", "SOUTH", "SOUTH", "SOUTH", "SOUTH", "EAST",...
$ FUNCTION <chr> "SALES", "SALES", "FINANCE", "FINANCE", "FINANCE", "FINANCE", "FINANCE", "SA...
$ GENDER   <chr> "M", "M", "M", "M", "F", "F", "M", "M", "M", "M", "M", "M", "F", "M", "M", "...
$ EDUQUAL  <chr> "GRAD", "PG", "PRO", "PRO", "GRAD", "PG", "PRO", "GRAD", "GRAD", "GRAD", "GR...
$ EXP      <int> 2, 3, 2, 5, 8, 3, 4, 5, 6, 1, 0, 1, 2, 3, 6, 4, 3, 11, 14, 2, 6, 3, 4, 5, 2,...

1. We will use dplyr Inner_Join method to join survey and employee dataset and mention keys for joining.

mergedata <-
  survey %>% inner_join(employee, by = c("EMPID", "BRANCH", "FUNCTION"))
head(mergedata)

1. We have analyse some insights which are as follows:

#.What is the count of employees in each Function Branch wise, having experience >2 years and EES more than 40. Output Format: Branches in rows and Functions in columns.

ans1 <-
  mergedata %>%
  filter(EXP > 2 & EES > 40) %>%
  group_by(BRANCH, FUNCTION) %>%
  summarize(count = n()) %>%
  spread(FUNCTION, count)
ans1

1. In above insight we are using filter method of dplyr for data filtering, group_by method of dplyr clubs our datset by BRANCH and than by FUNCTION. Summarize function is used to carry out statistics on our dataset. Than we use tidyr packag’s spread method to convert long dataset to wide.(As per requirement)

#.Recode M as ‘Male’ and F as ‘Female’ and derive a new column EXP_CATEGORY (using EXP column) with 3 categories: <3, >=3 & <=5 and >5. Calculate average EES of FEMALES in each Function Branch wise, having experience between 3 to 5 years Output Format: Branches in rows and Functions in columns

mergedata$GENDER <-
  ifelse(mergedata$GENDER == "M", "Male", "Female")
ans2 <-
  mergedata %>% mutate(EXP_CATEGORY = ifelse(
    EXP < 3,
    "RED BAND",
    ifelse(EXP >= 3 & EXP <= 5, "YELLOW BAND", "BLACK BAND")
  ))
ans2

1. We will break above example into 2 parts. part a and part b. part a uses mutate function of tidyr to add a column EXP_CATEGORY which depends on Exp of employee. It also transforms existing GENDER column from M/F to Male/Female

ans22 <- ans2 %>%
  filter(EXP_CATEGORY == "YELLOW BAND" & GENDER == "Female") %>%
  group_by(FUNCTION, BRANCH) %>%
  summarize(avgees = mean(EES, na.rm = TRUE)) %>%
  spread(BRANCH, avgees)
ans22

1. In above part b is just some more data manipulation to get result as per required. It uses already known methods of filter, group_by, summarize, spread.

#.Calculate average EES of employees in each Branch Gender wise, having EDUQUAL as PG or PRO Output Format: Branches in rows and Gender in columns

ans3 <- mergedata %>%
  filter(EDUQUAL %in% c("PG", "PRO")) %>%
  group_by(BRANCH, GENDER) %>%
  summarize(avgees = mean(EES, na.rm = TRUE)) %>%
  spread(BRANCH, avgees)
ans3