I recently tried out Mage.ai and I was struck by how simple yet effective it is for creating data pipelines. It also makes embedding testing and data quality checks into your pipelines almost automatic. So what is this mystical mage that I speak of? In this article I want to share a bit of what I’ve learned about Mage so far and highlight parts of the first pipelines I’ve built with it.

What is Mage?

Mage was founded on Dec 1, 2020 and now has 3.7k stars on Github (06/04/2023) and has raised a total of $11.8M in funding over 3 rounds according to Crunchbase. It is still early days for Mage but I think it has massive potential to be adopted by data engineers, analytical engineers, data scientists and data analysts given its light-weight platform and democratised approach to building pipelines.

Mage.ai is an “open-source data pipeline tool for transforming and integrating data” and is dubbed as “the modern replacement for Airflow”.

Replacement for Airflow? Really?

I haven’t come across many people who are absolutely smitten with writing and managing Airflow DAGs and this could be due the difficulty in debugging them and the development experience. Airflow is the norm for many data professionals but I like to think of it as a childhood friend, let’s call them “Andile” (I am South African so its fitting), whom I know so well and whose qualities and quirks I’ve accepted as part of the package deal. We all need and have an “Andile” and, as data professionals, we have probably needed and have had Airflow. As you grow though, you need more friends who can ultimately cater to your developing needs and wants. Enter MAGE! (a.k.a your new friend “Mandisa”, if we’re sticking to the analogy LOL).

I digress, so what makes Mage the modern replacement of Airflow?

• Mage makes the development experience easier because it can be run locally with a few commands. If you’ve tried to run Airflow locally you know how finicky that can be.
• You can also easily launch your dev environment in the cloud by using Mage maintained Terraform scripts.
• Mage pipelines are developed in a modular way with each step being a file that can be re-used. This also makes it easier to avoid spaghetti code which can happen in Airflow DAG’s.
• Testing and data quality are also easily integrated in Mage.

My favourite aspect of Mage is the web-based IDE that is super intuitive. The UI definitely catches the eye or as I’ve heard in drag slang “she’s serving face”. YES Mage.

There are many more features that make Mage.ai a great alternative. I have listed a few more below but it is definitely worth it to read their docs and get a comprehensive view (https://docs.mage.ai/introduction/overview).

To round off, with Mage.ai one can:

• develop in Python, SQL and R in the same pipeline
• use the native Spark integration to scale and transform
• collaborate easily in the cloud and use git version control
• integrate pipelines with great expectations, dbt, Databricks, Airflow etc.
• have built in observability in the UI

Now let’s take a quick gander at those pipelines shall we!

Mage pipelines

Setting up

In my terminal I ran the following command for installation using docker (https://docs.mage.ai/getting-started/setup):

docker run -it -p 6789:6789 -v %cd%:/home/src mageai/mageai /app/run_app.sh mage start [project_name]

I then navigated to http://localhost:6789 and opened the web-based IDE. (see below image — it is wondrous at first sight am I right?)

Alternatively, if you just want to have a look at the web-based IDE without setup you can use the live demo tool: https://demo.mage.ai/pipelines.

The folder structure of a Mage pipeline is important as the code is split into different folders (think ETL): data_loaders, transformers and data_exporters. I won’t get into the other folders in this post. Each code block in Mage can be mapped to a file and each file maps to one of the “ETL” folders. In the UI you can connect the code blocks directly in the workflow tree/DAG and you can modify their dependencies on each other if needed. This is currently not possible in Airflow unless you change the code.

Code blocks

As shown below, when I selected a data loader block, I could choose the language I want to use (Python in my case) and the ingestion location.

Once selected my data loader block was created and template code was added for ingesting from GCS in my case (see below). A test function for the code is also included at the bottom of each block and can be executed in the pipeline. This is awesome!

Charts in Mage

A really cool and useful feature in Mage is the ability create visualisation/graphs on any output data of a block. Below is an example using the titanic dataset. The data is loaded into a pandas dataframe and I could visualise a bar chart showing the average of some columns easily by clicking on the graph icon. The code for this graph is then saved in my project folder under “charts”. You can read more here.

Executing code and developing w/o the UI

When you execute the code in Mage you have a few options. You can choose which blocks to execute so you don’t need to re-run the entire pipeline if you changed just one block of code or you can execute all upstream blocks to get the data ready for your current block. There is also the ability to execute the block and its tests or set the block as dynamic.

So where does Mage handle the pipeline dependencies/workflow? That happens in the pipelines folder. If you develop without the UI, which most data engineers will probably opt for, then you need to use the yaml file that is in the pipelines folder: demo_project/pipelines/your_pipeline/metadata.yaml. This yaml file tracks the changes made and keeps the pipelines up to date in the UI. You can read more on developing without the UI here.

Final thoughts

It is also worth noting that Mage is not just competing in orchestration but it has an offering that can be appealing to those who don’t have the deeper infrastructure knowledge (or don’t need it) and those who work in smaller teams and need the ability to build pipelines. The web-based IDE is quite useful and will allow data scientists/analysts who enjoy working with notebooks to delve more easily into data engineering. This could also democratise the DE field and allow for more focus to shift to business value creation instead of building and maintaining heavy-weight pipelines.

Since it is still early days for Mage, I am excited to see how it expands its offering and now that generative AI and low-code software are here to stay, I think Mage is a great fit for the shifting landscape of data engineering.

I will be exploring Mage further so stay tuned for more posts on projects if you’re interested in how Mage works with its integrations (Spark, dbt etc.) and how to use Mage in the cloud.

Thank you for reading!

The Monte Carlo Simulation is something that my masters class was recently tasked to explore as an introduction to data analysis. I must admit, my understanding in the beginning was crude to say the least… BUT… I believe I’ve wrapped my head around the mechanics of running a couple thousand simulations using our favourite reptilian language… No, not Parseltongue 🐍…Python! #Gryffindorforever

The Investor scenario:

Firstly, the Monte Carlo Simulation needs to be applied to a scenario which contains a dependent variable and multiple parameter variables. The simple case used in our class was that of a $100,000 investment which has been left to grow over a 30 year period, with $10,000 additional payments made in advance each year and with a variable interest rate to which we assigned random probabilities based on normal distribution. The standard deviation and mean interest rate were also provided.

Additionally, the dependent outcome or variable would be the future value that the investor would receive. Given the law of large numbers (the larger the sample size, the closer the sample mean to the population mean), the more simulations that we do, the closer the average of the future value outcomes will be to what we could actually receive at the end of 30 years.

In layman's terms: if we run 10,000 simulations it will be more accurate than running, let’s say, 10 simulations in predicting the average future value.

Coding skills are cool but only if you know what to do with them!

I am a novice but from what I have seen thus far — the thinking behind the code is always more important than the actual writing of the code itself! This sounds simplistically logical but we can fall prey to thinking that complexity and skills accumulation equates to knowledge.

That’s like stockpiling your home shed with various different tools and materials (hacksaw’s, drills, bolts, nails, wood, sheet metal, jackhammers etc. #zerodiyknowledge) only to realize that you don’t know why you need them or how to use them so you end up going IKEA and buying a “plug-and-play” coffee table which is akin to just resorting to open-ware software that can give you a pre-defined process for getting to an outcome… There is, of course, nothing wrong with that but if you’re trying to learn how to think then you need to actually…think… Sounds peculiar I know.

I digress.

I have included a simple diagram showing how I thought about the base calculations and how to structure the code to get the ending values at the end of 30 years:

My thought process is of course neither correct nor incorrect, it is simply personal. Thus, your approach to this problem may be vastly different or similar! Don’t feel restricted — understand the “why” first and then plot your course with your own personal preferences!

The actual code for the programme:

First I started with the variables and imported numpy which will do all the heavy lifting.

As shown above, a rand_return array of 30 possible interest rates was created given the mean and standard deviation. This is the first structure to be created as shown in my thought-process diagram (Thinking process Part 2).

The second structure is the list of ending values that will contain the first year ending value at return_y[0]. All subsequent values are to be added per iteration of the remaining 29 returns in the rand_return array (in the diagram I called the list EV_list but in the code it is return_y — apologies for the inconsistency).

Ok! So now we add the remaining 29 ending values for the remaining 29 years of investment to return_y. The if statement was created to account for the $10,000 annual_inv that is only added in advance thus the final ending value at return_y[29] will not have it added.

It is important to note that the rand_return_30 is added inside the for loop so that when we do 1000 simulations next there is a new array of 30 returns created every time otherwise we will get the exact same values at the end of year 30, 1000 times.

In the above for loop for 1000 simulations, I embedded the previous for loop for the 30 year return period. The outer for loop will run this 1000 times and populate a new simulation list called “output” with the ending value of each full run at year 30 which is the value at return_y[29] #line11.

The last step at #line12 is to calculate the mean/average value of all 1000 simulations of the final return at the end of year 30. This will give us a more accurate prediction of a possible future value figure given the variability in returns and this accuracy increases as the number of simulations increase.

Final thoughts…

So far in my journey of learning how to become more technical and how to think about data/programming, I have realized that I only learn when I am faced with problems that don’t come with a set of instructions. It can be frustrating when you don’t know which way is up but once you learn how to structure your thinking and break apart a problem into simpler tasks it becomes easier and you will never forget it. I encourage you to make mistakes, fail and feel silly 😊… I do it all the time! Happy coding!

The Monte Carlo Simulation using Python! was originally published in The Startup on Medium, where people are continuing the conversation by highlighting and responding to this story.

I come from a business background, and more specifically a financial accounting and auditing background, which means I have undoubtedly encountered extraneous manual processes and data analysis methods (insert flashback to a cluttered office desk and little, frazzled me buried behind stacks of files and paperwork fighting the urge to run to my car, race to the nearest beach and create a bonfire whilst using the whole lot as kindling - just kidding, it’s illegal in Cape Town to create beach fires without getting a permit first).

In one of my first gigs, I worked as a financial accountant which was quite eye-opening for a recently graduated millennial as I was surprised to see that actual printed paper and excel spreadsheets were used for all analysis and data storage! How retro and anti-earth/life… This is probably when my interest in using technology for enterprise automation and process improvement first began. Good old,first-hand experience!

SQL was the natural evolution from excel for me and once I had up-skilled, and subsequently transitioned into a business analyst role, I started to become more advanced in data manipulation with structured data. This inevitably led me to seek more robust methods and tools over time as the data requirements became more complex and the data itself become larger. Data wrangling skills became mandatory for me.

Fast-forward to present day, all hail the holy library in python for data analysis — pandas! I am a believer which is why I want to share some of the basics which have added so much value to my analysis.

Now that I have your attention (one can only hope)…

Data Wrangling basics in Pandas:

There are a two important basics in data wrangling using pandas which I would like to touch on as they have formed the foundation of most of my data pre-processing.

1. Dealing with missing data
2. Data Binning

1. Missing data

There are few options for dealing with missing values in pandas, however, this will largely depend on the context of your data/business requirements.

Firstly, why is this important? Null values make our data mucky and difficult to work with further down the line when you are, for example, building statistical models or even just viewing basic descriptive statistics.

Initially, we would need to check if there is missing data and if so, in which columns.

The below code will give us an output showing which columns in the whole dataframe have null values (NaN) in them. Missing_data (Line #7&9) will give us a Boolean output with all True values being the null values.

We can also use the .info() method and check if the lengths of certain columns are less than the length of the dataframe using the len() function.

It is important to note that your data may have null values showing up differently as “?” or “0”. It is advisable to scan your data and if the null values are not NaN then you can use the .replace() method as shown in (Line #5).

Now that we have spotted the missing data, made sure they are all NaN and we know which columns they are in we can decide how to deal with them.

As shown above, we can do the following to deal with missing data:

Drop the data:

1. Drop the whole row (line #2)
2. Drop the whole column (the same as line #2 but with axis = 1)

Replace the data:

1. Replace NaN with the mean (numerical data) (line #8)
2. Replace NaN with the frequency (categorical data) (line #14)
3. Replace with another function (here we can use a function with np.vectorize() or the .apply() method)

2. Data Binning

This is probably what I use the most when pre-processing my data because an important aspect of data preparation is ensuring the data is meaningful and easy to understand.

Data binning allows you to split continuous numerical data into bins for ease of grouping and visualization.

Categorical data can also be binned in a way to add meaning to data which has arbitrary values that mean something else based on a business requirement specification or index. I would refer to this as data mapping or flagging. I have not included the code for this.

The simplest way to create an evenly spaced sequence for continuous, numerical data is to use np.linspace():

In (line #5) we use pd.cut() to create a new column that has our bins.

For completeness, this new binned column can be visualized using matplotlib:

Final thoughts…

Data wrangling is an extremely broad topic and there are a multitude of ways to clean and work with data to get it from “raw” to “information”.

Pandas in combination with numpy, which it is built upon, allows us to seamlessly transform our data. The benefits of this far surpass the excel spreadsheets I used back in the day (approx. 2 years ago 😂) and the manual methods of data analysis because results can be obtained in milliseconds, visualizations can be done immediately and we can work with large data.

Furthermore, models such as linear regression will be based on the wrangled data thus this is an extremely vital step and worth the effort. Not every data analysis gig will focus on model building but all will require data wrangling and data manipulation in some form or the other.

You better wrangle!

Python and pandas: serving data cleaning realness. You better wrangle! was originally published in The Startup on Medium, where people are continuing the conversation by highlighting and responding to this story.

In my current gig, a main focus is data processing and analysis. Source files are acquired from various systems in csv format, pre-processed and finally loaded through an off-the-shelf ETL tool.

I decided to play around with an alternative method for my data analysis which makes use of Jupyter Notebook alongside MySQL workbench to create a simple way to get data from raw CSV files into a secure database for storage and added data control whilst harnessing the power and ease of python to analyse and pre-process this data.

Now to the point of this story - I have included 7 steps to using the pymysql module which I hope will assist any beginner along their journey with data analysis.

Before we get to the code: it is extremely important to “get a feel” for the data in your csv file and understand, for example, what the relevant field names would be, which fields should be dropped or transformed, whether calculated fields are required and, most importantly, what the values mean in your business context. This understanding will form the foundation of your data analysis going forward. If you are a business analyst like I am, you would usually get this information from a workshop with your business stakeholders or from the user stories and requirement specifications.

In this example, I have used generic column names “field1, field2, field3, field4, field5” with generic data types for simplicity.

I will only touch on the step which involves creating a MySQL database and table in MySQL workbench (which I refer to as “myDB” and “table1”, respectively, in this example). If you are new to creating databases and tables, the below query should suffice but it would be extremely helpful to read further on this topic and to also focus on choosing relevant datatypes in MySQL. There are many MySQL courses available online for free, Google is your friend!

Before I start with the pymysql module in Jupyter Notebook, it is important to mention the basics of reading the csv file into a pandas dataframe which I have named “df_new_obs” in the example below. Don’t forget to import pandas!

Now to the main-course, bon appétit!

The above steps can be followed to create a connection to your MySQL database (“myDB” in this example) and insert data from your csv file, which was read into a pandas dataframe (“df_new_obs”), into the table you have created in MySQL (“table1”).

I will dissect the above into further detail:

Steps 1 & 2: Connecting to the DB and .execute()

Firstly, as shown above, we need to import the pymysql module in Jupyter Notebook. If you have not inferred this already, you will need to install pymysql on Anaconda Prompt by running the command: pip install pymysql.

Secondly, importing the json module will allow us to use a json file to pass credentials in a secure and private manner when we access the database connection. Alternatively, you can just enter your username and password directly in the connection.

Once we have run the code to extract the user and password into variables from the json file using the json.load() method (line #4-7), we can now create the connection (“conn”) between Jupyter and MySQL! (line #9) Aaahh success!

Finally, the cursor connection needs to be created so the we can execute queries in MySQL (line #11). Below is the code you can run to execute MySQL queries directly from Jupyter either by writing the select statement in directly (line #3) or by assigning it to a variable like “query” (line #5) and then passing it as an argument in cursor.execute().

The output to the above executed query will be the number of records retrieved in MySQL, however in this example, the output will be 0 as our table is empty until we insert our csv data from df_new_obs! Lets get to it…

Step 3: Insert Query

OK! So this is less of a “baby-step” and more of a “fully-grown adult leap”… One seemingly being made by an Olympic, gold-medal-worthy, long-jump athlete if you have not had enough exposure to loops in coding. I suggest a Python programming course for beginners to immediate level if the above code is unfamiliar.

This, however, will be invaluable in getting any data from pandas dataframes into a SQL database so it is worth the effort.

The MySQL statement to insert data is always “INSERT INTO tablename VALUES (value1,value2…),(value1,value2…),(…and so on)”

My example csv has 40 rows and most csvs will have thousands or hundreds of thousands of rows. It would be highly inefficient to manually enter each value into this query and the human error would be atrocious. Therefore, we should extract the rows in “df_new_obs” and use the above for loop in python to add the SQL syntax modifications that we need to get this insert_query to execute in MySQL. These would be the additional comma separators, apostrophes and brackets.

Once the above for loop is complete and you print the “insert_query” your output will look something like this:

Perfect SQL syntax format for the INSERT statement! This is now ready to execute in MySQL-table1.

Steps 4,5,6,7: execute query, commit and close connections

Once the data is inserted into table1 in myDB, you can run a simple select * statement (line #3) to check that the records retrieved are no longer 0 but are now equal the rows in your csv/dataframe. In my case this would be 40.

Furthermore, it is important to commit your changes in the database otherwise the insertion of data will not be permanent (line #5).

Finally, closing the cursor and database connections (line #7&9). This is important as we don’t want to leave connections open and if you want to play around with your data in MySQL workbench afterwards you would need to close the connection in Jupyter.

Final thoughts…

In addition to pymysql, there is cx_oracle for Oracle Databases, sqlite3 for SQLite and SQLalchemy for PostgreSQL among many other modules. There are slight differences in each but overall they work the same.

Working with data in relational databases is a vital skill for any analyst and integrating this with Jupyter Notebook will give one access to the benefits of databases as well as the analysis power of python. Most companies still operate with relational databases and require SQL as a skill for most data analyst candidates so I believe it will be a valuable skill set to obtain and to merge with your existing knowledge.

Go forth and analyse!

Python & MySQL: baby-steps with pymysql was originally published in The Startup on Medium, where people are continuing the conversation by highlighting and responding to this story.