# Analyzing Employee Details – Data Exploration & Manipulation

### Introduction

Organizations have a vast amount of employee data. The HR personnel in these organizations must understand these data and use basic statistical techniques to formulate effective strategies. They can use statistical methods such as mean, median, correlation, etc.; mathematical techniques such as minimum, maximum, etc.; and string operations such as concatenating data, modifying/updating records, etc. These basic techniques help the HR professional to have a better understanding of the data. This chapter discusses different functions in KNIME that help to analyze and manipulate data according to the requirement.

### **Employee Data**

To understand the usage of KNIME, the IBMdata.csv for HR Analytics is considered. This dataset can be downloaded from the “[Data](https://hub.knime.com/vijayv2k/spaces/Hands-on%20HR%20Analytics%20with%20KNIME/Data~gLvY8lmS5x6DT7Ag/)” folder from the KNIME Community Hub.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1738125196524/c395237f-723f-4182-984b-bfb83f8cb9e5.png align="center")

You are ready after downloading the CSV file from the Data folder.

> *You can download the* [02 Analyzing Employee Details - Data Exploration & Manipulation](https://hub.knime.com/s/fe-9EOmBKCzvK-Lj) *workflow from the KNIME Community Hub*

### **Reading the CSV-based dataset**

The first step in our analysis will be to load the data for our exploratory analyses. We will do this first step using the **CSV Reader** node before we persist our analysis in a KNIME table.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1738125212344/6b4f6d56-d485-4ccf-9b64-477df8dfff56.png align="center")

The KNIME table is created by loading the IBMdata CSV dataset.

The above table shows that the employee dataset has 1470 observations and 35 columns. Most columns are integers, and some, such as ***Gender***, ***JobRole***, ***MaritalStatus***, etc., are string columns.

### **Data Exploration and Descriptive Summary Statistics**

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1738125227721/b71a1212-a84b-4370-afbf-de6bc6b75096.png align="center")

The **Statistics** node primarily determines the descriptive summary statistics of the columns in the dataset. This node calculates statistical moments such as minimum, maximum, mean, standard deviation, variance, median, overall sum, number of missing values, and row count across all numeric columns. It counts all nominal values together with their occurrences. The node provides the following three output tables

1. Statistics Table: All statistic moments for all numeric columns,
    
2. Nominal Histogram Table: Nominal values for all selected categorical columns and
    
3. Occurrences Table: The most frequent/infrequent values from the categorical columns (Top/bottom)
    

The **Data Explorer** node offers various options for displaying the properties of the input data in an interactive view.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1738125241418/3183ea2e-c193-492d-8d89-558e6dbef3c7.png align="center")

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1738125254335/1681abd7-a56c-43b7-8789-c26d618be744.png align="center")

### **Data Manipulation of String Columns**

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1738125267168/5d7232df-3884-41a2-bbc9-a611d959309c.png align="center")

**Joining of Strings**

Objective: To display the employee details using concatenation of ***Department*** and ***EducationField*** string columns

A new ***CombinedString*** column can be created by concatenation (combining) of different strings. The strings can be combined by using the **Join** function of the **String Manipulation** node.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1738125282720/1ff3fbdc-1695-4f60-8fd0-b73be247eaab.png align="center")

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1738125296709/00c5a533-7257-41dd-8bce-27c6dbe294e6.png align="center")

### **Case Conversion of Strings**

Objective: To display the employee details in capitalized, uppercase, and lowercase format.

In the **String Manipulation** node, the **capitalize (str)** function capitalizes all whitespace-separated words in a string so that each word comprises a title-case character and then a series of lowercase characters.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1738125310808/4109df1d-bee5-444e-8f7b-98bb86370045.png align="center")

We apply the capitalize function on the ***CombinedString*** column to create the ***CapCombinedString*** column.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1738125328053/c4ae26b0-6fdd-4e1c-abcd-3533992275d6.png align="center")

The **uppercase(str)** and **lowercase(str)** functions convert all the characters in a string into uppercase and lowercase, respectively.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1738125341236/2c18c7e3-72fe-45ed-9598-658ef59a88a5.png align="center")

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1738125354840/43806262-5e14-4aca-abdb-258d13644323.png align="center")

### **Search and Replace of String**

Objective: To search for a particular string and replace it with another

The **replace(str, search, replace)** function of the String Manipulation node replaces all occurrences of a String within another String. In our ***CombinedString*** column, we search for the word “Employee” and replace it with “Staff” in the appended ***RepCombinedString*** column.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1738125369649/ea90f024-76f4-4838-b34d-ab2d3ee1e0cd.png align="center")

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1738125383863/9df65d9d-3505-4665-b743-9a6b4ec69253.png align="center")

### **Search and Match of String**

Objective: To determine and indicate whether the employee belongs to the Sales department

We use the **LIKE** function of the **Rule Engine** node to search for “Sales” in the ***CombinedString*** column and create a new column, ***Sales***, with “Yes” or “No,” to indicate whether the employee belongs to the Sales department.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1738125397837/65cd47db-ae1a-4eb8-a15a-cc3ecebe7b8b.png align="center")

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1738125412151/aca5b51c-fffc-43ed-94eb-e723560a6715.png align="center")

The **Like** function helps locate the string “Sales” in the given string column. If the department has a “Sales” word, the corresponding **Sales** column is populated with a “Yes” value and a “No” value otherwise. The above results show that the first employee belongs to the sales department, and the subsequent four do not.

### **Aggregated Statistics using Group by Operation**

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1738125428948/f5671960-46bd-4104-8349-fd03400fe7e9.png align="center")

The **GroupBy** node helps group observations based on a categorical column. After grouping observations, we can apply multiple aggregation functions, such as mean, median, minimum, maximum, count, sum, covariance, variance, standard deviation, etc.

We will use the **GroupBy** node to get the aggregated statistics for multiple columns across each ***Gender*** (male and female).

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1738125445517/e394b479-8183-42ea-b978-1904ee60fe33.png align="center")

* Mean of ***DistanceFromHome***
    
* Median of ***DistanceFromHome***
    
* Minimum ***Age***
    
* Maximum ***Age***
    
* Count of ***EmployeeCount***
    
* Sum of ***DailyRate***
    
* Covariance of ***HourlyRate***
    
* Variance of ***HourlyRate***
    
* Standard deviation of ***HourlyRate***
    

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1738125466138/b32f4e4b-34ce-4778-a0b1-1b372d0a8f2f.png align="center")

### **Group by with Sorting**

Objective: To determine the employee count and percent for different marital status

We will use the **GroupBy** node to get the distinct employee count and percentage across each marital status (group by ***MaritalStatus***, aggregate count, and percent by ***EmployeeNumber***). Subsequently, we use the **Sorter** node to order the resultant table in descending order of count.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1738125478552/807d847f-186d-42db-bb68-77213f237a7e.png align="center")

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1738125490711/0ee9fe8e-373a-4872-a09d-515995cdea53.png align="center")

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1738125502391/64e040ff-80b4-4d33-9512-6962b8d20241.png align="center")

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1738125513739/9f252c3e-facc-490a-aadf-99b1386f7ac0.png align="center")

We can observe three distinct values of ***Marital Status***: Single, Married, and Divorced. The number of observations of married employees is maximum (673, 45.782%) and displayed at the top; followed by the number of observations of single employees (470, 31.973%), and observations having divorced employees is minimum (327, 22.245%) and displayed at the bottom.

### **Data Filtering (subset) using AND and OR operation**

Objective: To filter the data of female employees belonging to the sales department

We will use the **Row Filter** node and add the criterion1 filter column as ***Gender*** (value = “Female”) and the criterion2 filter column as ***Department*** (value = “Sales”). By selecting the “All criteria” option, the “AND” filter condition is applied (189 rows in the resultant filtered table). On the other hand, by choosing the “Any criteria” option, the “OR” filter condition is applied (845 rows in the resultant filtered table).

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1738125530388/3aef6756-ad4d-43f7-abd0-fd0a0d61872e.png align="center")

### **Determine Correlation Using Spearman Correlation Coefficient**

Objective: To determine the correlation between hourly rate and job satisfaction

We will use the **Rank Correlation** node to determine the Spearman correlation coefficient of the ***HourlyRate*** and ***JobSatisfaction*** columns. The node calculates a correlation coefficient for each pair of selected columns, i.e., a measure of the correlation of the two variables. All measures are based on the rank of the cells. By executing the node, we get three outputs for statistical inference. Correlation measure contains correlation variables, p-values, and degrees of freedom. The correlation matrix displays the correlation variables in a matrix representation. The Rank table contains the fractional ranks of the columns where the rank corresponds to the position of the value in a sorted table.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1738125550029/d05c4469-aac3-4d48-be2f-35f50b9986ec.png align="center")

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1738125561097/e8751972-5829-40de-970e-7578c734373a.png align="center")

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1738125571679/39383296-29e9-46da-ab72-13fce95564c8.png align="center")

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1738125583258/5e449e92-383f-43d3-8708-e1d253b6b003.png align="center")

The correlation between the two groups lies between -1 and +1. The value -1 denotes a perfect negative correlation, and +1 denotes a perfect positive correlation. The value 0 denotes no correlation. The value between 0 and 1 denoted the extent of correlation. A lesser value denotes a lower correlation, and a higher value denotes a higher correlation. From the output, we can see that the Spearman correlation is -0.06; this means a low correlation between the two groups. We know the null hypothesis is rejected if the p-value is <0.05. Our data shows that the p-value is less than 0.05, which means the null hypothesis is rejected. This means there exists a negative correlation between hourly rate and job satisfaction.

### **Determine Correlation Using Pearson Correlation Coefficient**

Objective: To determine the correlation between monthly rate and job satisfaction

We will use the **Linear Correlation** node to determine the Spearman correlation coefficient of the ***MonthlyRate*** and ***JobSatisfaction*** columns. The node calculates a correlation coefficient for each pair of selected columns, i.e., a measure of the correlation of the two variables. By executing the node, we get two outputs for statistical inference. The correlation measure contains correlation variables, p-values, and degrees of freedom. The correlation matrix displays the correlation variables in a matrix representation.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1738125597231/1596f871-8976-4a7d-82f4-ea21d8c747a7.png align="center")

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1738125609894/e69ac5e9-88ca-4139-b8d4-9fe79d7a84cd.png align="center")

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1738125621319/2237d2bd-0392-4aab-9f80-d52abc039e16.png align="center")

The correlation coefficient is 0.001, which is very low. Also, the p-value (0.98) is more than 0.05, which means the null hypothesis failed to be rejected. This means no significant correlation exists between monthly rate and job satisfaction.

### **Summary**

In conclusion, this article demonstrated the effective use of KNIME for data exploration and manipulation, specifically in HR analytics using the IBMdata.csv dataset. By leveraging various KNIME nodes, we performed a comprehensive exploratory data analysis, including reading and summarizing data, manipulating string columns, and conducting statistical analyses such as correlation. These techniques provide HR professionals with valuable insights into employee data, enabling them to make informed decisions and develop strategic initiatives. The hands-on approach with KNIME showcases its versatility and power in handling complex data tasks, making it an essential tool for data-driven decision-making in organizations.