Machine Learning: Getting Data Into the Right Shape

When you build a Machine Learning model, first start with the data — make sure input data is prepared well and it represents the true state of what you want the Machine Learning model to learn. Data preparation task takes time, but don't hurry — quality data is key for Machine Learning success. In this post, I will go through the essential steps required to bring data into the right shape to feed it into a Machine Learning algorithm.

Sample dataset and Python notebook for this post can be downloaded from my GitHub repo.

Each row from the dataset represents invoice, which was sent to the customer. The original dataset extracted from the ERP system comes with five columns:

customer — customer ID
invoice_date — date when invoice was created
payment_due_date — expected invoice payment date
payment_date — actual invoice payment date
grand_total — invoice total

invoice_risk_decision — 0/1 value column that describes the current invoice risk. The goal of the Machine Learning module will be to identify the risks for future invoices based on risks estimated for historical invoice data.

There are two types of features — categorical and continuous:

categorical — often text than number, something that represents distinct groups/types
continuous — numbers

Machine Learning typically works with numbers. This means we need to transform all categorical features into continuous. For example, grand_total is a continuous feature, but dates and customer ID are not.

The date can be converted to continuous feature by breaking it into multiple columns. Here is an example of breaking invoice_date into multiple continuous features (year, quarter, month, week, day of year, day of month, day of week):

Using this approach, all date columns can be transformed into continuous features. Customer ID column can be converted into matrix of 0/1. Each unique text value is moved into a separate column and assigned with 1, all other columns in that row are assigned with 0. This transformation can be done with a Python library called Pandas, we will see it later.

You may or may not have decision values for your data, this depends on how data was collected and what process was implemented in ERP app to collect this data. Decision column (invoice_risk_decision) value represents business rule we want to calculate with machine learning. See 0/1 assigned to this column:

Rule description:

0 — invoice was payed on time, payment_date less or equal payment_due_date
0 — invoice wasn't payed on time, but total is less than all invoices total average and payment delay is less or equal 10% for current customer average
1 — all other cases, indicates high invoice payment risk

I would recommend to save data in CSV format. Once data is prepared, we can load it in Python notebook:

I'm using Pandas library (imported through pd variable) to load data from file into data frame. Function head() prints first five rows from data frame (dataset size 5x24):

We can show the number of rows with 0/1, this helps to understand how the data set is constructed — we see that more than half rows represent invoices without payment risk:

Customer ID column is not a number, we need to convert it. Will be using Pandas get_dummies function for this task. It will turn every unique value into a column and place 0 or 1 depending on whether the row contains the value or not (this will increase dataset width):

Original customer column is gone, now we have multiple columns for each customer. If a customer with ID = 4 is located it given row, 1 is set:

Finally, we can check the correlation between decision column — invoice_risk_decision and other columns from the dataset. Correlation shows which columns will be used by a Machine Learning algorithm to predict a value based on the values in other columns in the dataset. Here is the correlation for our dataset (all columns with more than 10% correlation):

As you can see, all date columns have a high correlation as well as grand_total. Our rule tells that invoice payment risk is low if invoice amount is less than all total average — that's why correlation on grand_total value exist.

The customer with ID = 11 is the one with the largest number of invoices, and the correlation for this customer is higher than for others, as expected.