Using MindsDB for Time Series Forecasting - Honey Production in the USA
Tags: mindsdb, hacktoberfest, ai
Published: 2022-10-25 04:45:42

## The Pre-Requisites

The very first thing to do is to create your **30-day free** to use [Mindsdb account](https://mindsdb.com/). 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:
1. Loading Data
2. Pre-Processing Data
3. Fitting Data
4. Fine Tuning the Model
5. Predicting the Results

With MindsDB the entire pipeline is shrunk to:
1. Connect to Data
2. Describe the Predictor
3. 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](https://www.kaggle.com/datasets/jessicali9530/honey-production) 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.
![MindsDB Editor](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/i6b1mkk9mayh5acb2qsb.png)

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`.
![Upload File](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/acdxzec7w4dhl17nqtx8.png)
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;  
```
![Uploaded Files](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/32itd3uwqz9d30ehe121.png)

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;
```

![Data](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/34j55jc0uhaq3rzxo8vx.png)

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
```

![Create Predictor](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gb7bp6te6a9zzr8soqwv.png)

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;
```
![Check Predictor Status](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/e7kqwfzgptcobi9arxge.png)

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


![Fin Train](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0uhffthlpqr9jt74kfst.png)


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.

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

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](https://www.youtube.com/playlist?list=PLIp0C2zDy-fVtKJ3umVsL_8XpoJQhIuZA)

## Next Steps

Check out the following:

* [MindsDB Website](https://mindsdb.com/)
* [MindsDB GitHub](https://github.com/mindsdb/mindsdb)

Interested in being a part of MindsDB? 
Join the [MindsDB Slack](https://mindsdb.com/joincommunity?_ga=2.263433930.790547227.1666614251-1540291534.1666362762&_gl=1*u355dy*_ga*MTU0MDI5MTUzNC4xNjY2MzYyNzYy*_ga_7LGFPGV6XV*MTY2NjY3MDMzMS41LjEuMTY2NjY3MzA3Ni4wLjAuMA..)