Predicting Employee Productivity using Machine Learning with MindsDB
Tags: machinelearning, sql, ai, mindsdb
Published: 2022-07-21 09:31:00

*Community Author:* [**Chandre Van Der Westhuizen**](https://github.com/chandrevdw31)

The garment industry is an essential exemplar of industrial globalization. It is of utmost importance for the decision makers in the garment industry to analyze,track and predict the productivity performance of employees as the companies rely on the production and delivery performance of their factory workers to satisfy the massive global demands for garment products. MindsDB's in-database machine learning capabilities provides the convenience of predicting productivity to assist employers with employee management.

This tutorial will explore how employers can make predictions on the productivity of employees producing garments using a machine learning regression predictive model. You will get to learn how to connect your database directly to MindsDB, create and train models and make predictions.

## Pre-requisites
1.  Access to MindsDB [Cloud](https://docs.mindsdb.com/setup/cloud/) or local deployment via [docker](https://docs.mindsdb.com/setup/self-hosted/docker/) or [pip installation.](https://docs.mindsdb.com/setup/self-hosted/pip/windows/)
2. Dataset: You can find the dataset on [Kaggle](https://www.kaggle.com/datasets/ishadss/productivity-prediction-of-garment-employees)

## Connecting your Database to MindsDB.

The first step will be to establish a database connection via MindsDB's GUI which has a SQL Editor that makes it easy to execute queries. MindsDB enables connections to your favorite databases, data warehouses and data lakes via the [CREATE DATABASE](https://docs.mindsdb.com/sql/create/databases/) syntax.

**First, we need to connect MindsDB to the database where the Employee producing garments data is stored:**

- Access MindsDB GUI on either cloud or the URL 127.0.0.1:47334/
- On the default page, select the button Add Data or alternatively select the plug icon on the left sidebar.
- The 'Select your data source' page will populate for you to choose your database type. MindsDB has a list of databases that can be chosen. For this tutorial we will be selecting the postgres database button.
![db](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/l6vudxowviosvc6e7qkk.png)
 

- Once you have selected the database type,the page will automatically navigate to the SQL Editor where the syntax to create a database connection will automatically populate for you to enter the required parameters.

**The required parameters are:**

- CREATE DATABASE display_name  --- display name for database. 
- WITH ENGINE = "postgres",     --- name of the mindsdb handler 
- PARAMETERS = {
    - "user": " ",              --- Your database user.
    - "password": " ",          --- Your password.
    - "host": " ",              --- host, it can be an ip or an url. 
    - "port": "5432",           --- common port is 5432.
    - "database": " "           --- The name of your database *optional.
} 

![db](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/at6n4gm5lz9hry3afh1j.png)

Select the `Run` button or Shift+Enter to execute the syntax. Once the Database connection is created the console will display a message 'Query successfully completed'.
​
> _Please note that some database connections require running a Ngrok tunnel to establish a connection._
> _Run the ngrok command in a terminal:_
> ```bash
> ngrok tcp [db-port]
> ```
> _for example,if your port number is 5433 you will see a similar output:_
> ```console
> Session Status                online
> Account                       myaccount (Plan: Free)
> Version                       2.3.40
> Region                        United States (us)
> Web Interface                 http://127.0.0.1:4040
> Forwarding                    tcp://6.tcp.ngrok.io:14789 -> localhost:5433
> ```
> _The forwarded address information will be required when connecting to MindsDB's GUI. Select and copy the 'Forwarding' information, in this case it is `6.tcp.ngrok.io:14789`, where 6.tcp.ngrok.io will be used for the host parameter and 14789 as the port number._

Once the database integration is successful we can query the table from the database to ensure the data pulls through on MindsDB.

You can run queries directly on your database. The below is an example of the syntax used:

```sql
SELECT * 
FROM example_db.datasource.table_name
LIMIT 10;
```
We will run the query to preview the data:
```sql
SELECT * 
FROM mindsdb_predictions.data.garments
LIMIT 10;
```
![select](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ffig70c3815k4imsgo8y.png)

> _MindsDB also has a functionality to [Upload a file and SELECT data from the file](https://docs.mindsdb.com/sql/api/select_files/) to create a machine learning model._


### Understanding the data

```sql
+----------+----------+------------+----------+------+-----------------------+-------+--------+-----------+-----------+-----------+----------+--------------------+---------------+---------------------+
| date     | quarter  | department | day      | team | targeted_productivity | smv   | wip    | over_time | incentive | idle_time | idle_men | no_of_style_change | no_of_workers | actual_productivity |
+----------+----------+------------+----------+------+-----------------------+-------+--------+-----------+-----------+-----------+----------+--------------------+---------------+---------------------+
| 1/1/2015 | Quarter1 | sweing     | Thursday |   12 |                   0.8 | 11.41 |  968.0 |      3660 |        50 |       0.0 |        0 |                  0 |          30.5 |         0.800570492 |
| 1/1/2015 | Quarter1 | sweing     | Thursday |    6 |                   0.8 |  25.9 | 1170.0 |      1920 |        50 |       0.0 |        0 |                  0 |          56.0 |         0.800381944 |
| 1/1/2015 | Quarter1 | sweing     | Thursday |    7 |                   0.8 |  25.9 |  984.0 |      6720 |        38 |       0.0 |        0 |                  0 |          56.0 |            0.800125 |
| 1/1/2015 | Quarter1 | sweing     | Thursday |    3 |                  0.75 | 28.08 |  795.0 |      6900 |        45 |       0.0 |        0 |                  0 |          57.5 |         0.753683478 |
| 1/1/2015 | Quarter1 | sweing     | Thursday |    2 |                  0.75 | 19.87 |  733.0 |      6000 |        34 |       0.0 |        0 |                  0 |          55.0 |         0.753097531 |
+----------+----------+------------+----------+------+-----------------------+-------+--------+-----------+-----------+-----------+----------+--------------------+---------------+---------------------+
```

Where:

| Column                | Description                                                                                  | Data Type           | Usage   |
| :-------------------- | :------------------------------------------------------------------------------------------- | ------------------- | ------- |
| `date`                | Date in MM-DD-YYYY                                                                           | `integer`           | Feature |
| `quarter`             | A portion of the month. A month was divided into four quarters                               | `character varying` | Feature |
| `department`          | Associated department with the instance                                                      | `integer`           | Feature |
| `day`                 | Day of the Week                                                                              | `character varying` | Feature |
| `team`                | Associated team number with the instance                                                     | `integer`           | Feature |
| `targeted_productivity` | Targeted productivity set by the Authority for each team for each day.                     | `integer`           | Feature |
| `smv`                 | Standard Minute Value, it is the allocated time for a task                                   | `integer`           | Feature |
| `wip`                 | Work in progress. Includes the number of unfinished items for products                       | `integer`           | Feature |
| `over_time`           | Represents the amount of overtime by each team in minutes                                    | `integer`           | Feature |
| `incentive`           | Represents the amount of financial incentive (in BDT) that enables or motivates a particular course of action. | `integer`   | Feature |
| `idle_time`           | The amount of time when the production was interrupted due to several reasons                | `integer`           | Feature |
| `idle_men`            | The number of workers who were idle due to production interruption                           | `integer`           | Feature |
| `no_of_style_change`  | Number of changes in the style of a particular product                                       | `integer`           | Feature |
| `no_of_workers`       | Number of workers in each team                                                               | `integer`           | Feature |
| `actual_productivity` | The actual % of productivity that was delivered by the workers. It ranges from 0-1           | `integer`           | Label   |


>_A **label** is the thing we're predicting—the y variable in simple linear regression._
>_A **feature** is an input variable—the x variable in simple linear regression._

## Creating a Regression Machine Learning Predictive model.

To make predictions, we will create a machine learning model using the [`CREATE PREDICTOR`](https://docs.mindsdb.com/sql/create/predictor/) statement and make a Regression prediction.
In the syntax we will specify which columns/ features to use for training and what column the model should learn to predict as our target/label. In this case we will select all the data to train with and use the column 'Target' as our target variable/label that we want to predict.

In the SQL Editor, the below syntax will be executed:

```sql
CREATE PREDICTOR garments_productivity
FROM mindsdb_predictions
(SELECT * FROM data.garments)
PREDICT actual_productivity;
```

Select the `Run` button or Shift+Enter to execute the syntax. If there are no issues with creating the query you will receive the message in the console **'Query successfully completed'.**

![create](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9ngdvdgh36eio6mfr7pm.png)
The model can take a while to train. The status of the model's training can be checked with the below syntax:

```sql
SELECT * FROM mindsdb.predictors WHERE name='garments_productivity'
```
As soon as the model is created the status should show training.Once the model is finished training,the status will show complete. Below you can see that the model has an accuracy of 84%.

![select](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/thfn5gp88iv33pzqcf94.png)

> _The Predictor Status Must be 'complete' Before Making a Prediction_
 
## Making a Regression Prediction

When the status of the predictor shows complete, we can go ahead and make a prediction. Predictions are made using the [`SELECT`](https://docs.mindsdb.com/sql/api/select/) statement by querying the predictor as if it is a table. We will provide the features with parameters to get a result.

The below syntax will be used to make a prediction:

```sql
SELECT actual_productivity, actual_productivity_explain FROM garments_productivity WHERE date='1/1/2015' AND quarter='Quarter1' AND department='sweing' AND day='Thursday' 
AND team=8 AND targeted_productivity=0.8 AND smv=26.16 AND wip=1108 
AND over_time=7080 AND incentive=98 AND idle_time=0 AND idle_men=0 
AND no_of_style_change=0 AND no_of_workers=59;
```

Run the above syntax and you will see the below results:
![prediction](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vfohyiu3055hylpqrf90.png)
 
The actual productivity has a value from 0-1.The model predicted that with these parameters provided the result for the productivity of the employees will be 0.93 and by using `Target_explain` we can see the confidence levels which shows that the confidence_lower_bound is 0.8556 and confidence_higher_bound is 1. This means that the garment company can expect to have the productivity of the employees to be between 0.8556 and 1.

## Making Batch Predictions.

The model can also make batch predictions using the [`JOIN`](https://docs.mindsdb.com/sql/api/join/) clause:

```sql
SELECT a.actual_productivity as productivity, b.actual_productivity as predicted_productivity, 
a.date, a.quarter, a.department, a.day,
a.team, a.targeted_productivity, a.smv, a.wip,
a.over_time, a.incentive, a.idle_time, a.idle_men,
a.no_of_style_change, a.no_of_workers
FROM mindsdb_predictions.data.garments as a
JOIN mindsdb.garments_productivity as b limit 5;
```

Results:
```sql
+--------------+------------------------+----------+----------+------------+----------+------+-----------------------+-------+--------+-----------+-----------+-----------+----------+--------------------+---------------+
| productivity | predicted_productivity | date     | quarter  | department | day      | team | targeted_productivity | smv   | wip    | over_time | incentive | idle_time | idle_men | no_of_style_change | no_of_workers |
+--------------+------------------------+----------+----------+------------+----------+------+-----------------------+-------+--------+-----------+-----------+-----------+----------+--------------------+---------------+
| 0.800570492  | 0.7820790218694679     | 1/1/2015 | Quarter1 | sweing     | Thursday | 12   | 0.8                   | 11.41 | 968.0  | 3660      | 50        | 0.0       | 0        | 0                  | 30.5          |
| 0.800381944  | 0.8169200021797305     | 1/1/2015 | Quarter1 | sweing     | Thursday | 6    | 0.8                   | 25.9  | 1170.0 | 1920      | 50        | 0.0       | 0        | 0                  | 56.0          |
| 0.800125     | 0.7619499385051534     | 1/1/2015 | Quarter1 | sweing     | Thursday | 7    | 0.8                   | 25.9  | 984.0  | 6720      | 38        | 0.0       | 0        | 0                  | 56.0          |
| 0.753683478  | 0.762949004815915      | 1/1/2015 | Quarter1 | sweing     | Thursday | 3    | 0.75                  | 28.08 | 795.0  | 6900      | 45        | 0.0       | 0        | 0                  | 57.5          |
| 0.753097531  | 0.752386052621824      | 1/1/2015 | Quarter1 | sweing     | Thursday | 2    | 0.75                  | 19.87 | 733.0  | 6000      | 34        | 0.0       | 0        | 0                  | 55.0          |
+--------------+------------------------+----------+----------+------------+----------+------+-----------------------+-------+--------+-----------+-----------+-----------+----------+--------------------+---------------+
```

_Want to try it out for yourself? Sign up for a [free MindsDB account](https://cloud.mindsdb.com/signup?utm_medium=community&utm_source=ext.%20blogs&utm_campaign=blog-crop-detection) and join our community!
Engage with MindsDB community on [Slack](https://join.slack.com/t/mindsdbcommunity/shared_invite/zt-o8mrmx3l-5ai~5H66s6wlxFfBMVI6wQ) or [Github](https://github.com/mindsdb/mindsdb/discussions) to ask questions, share and express ideas and thoughts!_

_Make sure to check out the official website of [MindsDB](https://mindsdb.com/). For more check out other [tutorials and MindsDB documentation](https://docs.mindsdb.com/)._