Using MindsDB for Time Series Forecasting - Honey Production in the USA

NarayanAdithya — Tue, 25 Oct 2022 04:45:42 +0000

The Pre-Requisites

The very first thing to do is to create your 30-day free to use Mindsdb account. Once this is done you are all set to explore and understand the MindsDB way of building machine learning models.

About MindsDB

MindsDB in simple words is basically machine learning but in the database. The usual machine learning pipeline involves:

Loading Data
Pre-Processing Data
Fitting Data
Fine Tuning the Model
Predicting the Results

With MindsDB the entire pipeline is shrunk to:

Connect to Data
Describe the Predictor
Predict The Data

The entire ETL Pipeline has been encapsulated by MindsDB thus allowing a very SQL Like, Low Code Paradigm for Machine Learning.

About the Dataset

For this tutorial we will be using the Honey Production in the USA Dataset from Kaggle.
The dataset consists of honey production values for different states from the year 1998 to 2012. There are several other columns in the dataset like colony count, yield per colony etc. Since the aim of the tutorial is to just forecast the total production of honey statewise we will be only needing the state, year and totalprod columns. We can ignore the rest of the columns.

Using MindsDB For TimeSeries

Uploading the Data

After you have logged in on the MindsDB website, you'll see the MindsDB Editor.

Click on the Add Data button on the navbar. Then Select Files and proceed to upload the honeyproduction.csv which you will get after downloading and extracting the zip file from Kaggle. Name the table as honey_prod.

Click on save and continue.
We have uploaded the data into the MindsDB database.

Playing Around with the Data

We have uploaded the data.
How do we see it ?
How do we know where the data is ?
What is the piece of code you see after uploading the dataset?

MindsDB stores files uploaded in a files database. Each file uploaded is a tabel. Inroder to see the list of files that you have uploaded you can simply write and execute the following query

SHOW TABLES FROM files;

Now that we have found our table. We can try to query the first 10 rows of our table.

SELECT * FROM files.honey_prod LIMIT 10;

Hurrayyy🥳🥳🥳!!!, Our Table exists and now we can move on to the main part of the tutorial....

Creating the PREDICTOR

In MindsDB creating the model is as simple as running the PREDICTOR command.

Since we are working on a TimeSeries application we will be using the following command:

CREATE PREDICTOR mindsdb.<name-of-model>
FROM files
    (<DATA-ON-WHICH-MODEL-HAS-TO-BE-TRAINED>)
PREDICT <COLUMN-TO-BE-FORECASTED>
ORDER BY <TIME-COLUMN>
GROUP BY <INDEPENDENT_VARIABLE1>
GROUP BY <INDEPENDENT_VARIABLE1>
...

WINDOW <Use Previous x years of data for prediction>
HORIZON <Predict for the next k years>

I believe the not so official syntax that I have given is pretty self-explanatory. But if you feel confused don't worry the actual query will be more clear and even if that doesn't help check out the youtube tutorial link at the end of this article.

Create Honey Predictor Command:

CREATE PREDICTOR mindsdb.honey_prod_predictor
FROM files
  ( SELECT state, totalprod, year FROM honey_prod)
PREDICT totalprod
ORDER BY year
GROUP BY state

WINDOW 12
HORIZON 2

In simple words the predictor will be trained using the columns state, totalprod and year based on the past 12(WINDOW) years values and the model will be built for the forecasting of totalprod for the next 4(HORIZON) years.

Once you execute the query you can check the status of the model using the command:

SELECT * FROM predictors;

Wait for the model to finish training.......

And just like that you have created a machine learning model.

Time To Forecast

Forecasting is basically done using the following command:

SELECT m.year AS Year, m.totalprod AS Forecast
FROM mindsdb.honey_prod_predictor AS m 
JOIN files.honey_prod AS t
WHERE t.year > LATEST 
AND t.state = 'AL' ;

The query can seem intimidating at first, but it is quite straightforward. So initially you have the columns you want in the output called off the predictor which is m. The join is required to fill in the dependent variable state and the year from which the forecast starts. LATEST is a keyword that represents the latest year in the dataset. Hence the predictions will be made 2 years from that year which is 2 years from 2012 i.e., 2013 and 2014.

We can see the production value that has been forecasted for the years 2013, 2014. To predict for a different state it is as easy as replacing the state value and running the command again.

Conclusion

We have seen how easy it is to create a machine learning model from scratch using MindsDB. If you are much used to code-along tutorials I would recommend you to checkout my channel here

Next Steps

Check out the following:

Interested in being a part of MindsDB?
Join the MindsDB Slack

DEV Community: NarayanAdithya