Data Cleaning - Filter

• Introduction

• Detecting what to filter

• How to code

• Magical Solution ✨🔮

Filtering through data is a very common transformation; it takes in a conditional and checks through all the data to keep only the data that meets the condition. By filtering you can improve your machine learning models by training on a specific subset of data to specialize the model, remove incorrect data and outliers, or prune biased features.

To start off with what filtering does, it takes in a pile of data and turns it into something smaller and (hopefully) easier to work with. When you create a filter, you start from what will stay, not what will go.

Understanding how to create the best filters are based around 2 things: Where the data comes from and how we apply each filter to our data. 

First, we need to assess where our features are coming from and whether they’ll be relevant to our use case. Filtering can be applied to all data, and can therefore be used for many different use cases. It’s best to do this to avoid bias and the situation below.

These bad cases occur when a lot of mathematical transformations were done to manipulate the data too much. We’ll want to throw away this as it can lead to data leakage. This could be training a model to predict a user’s location, but your data contains the lat/long coordinates. Or in the very bad case, when the samples have been cherry picked instead of being entirely random.

Next, we’ll take a look at three different possible data types. For more information on how to spot each one read our 

on data types. 

The conditional values do a different thing depending on the data types. For an input of numbers you can filter using the logical operators to set ranges for the data. Otherwise, when given 

like words, or “strings”, it’ll follow an ASCII Table to determine the order, usually leading to an alphabetized range from A-z.

Finally, we get to the datetimes, in another article called 

we go over how to format and reformat them to meet specifications. For filtering through datetimes, it’ll be in chronological order, so “<” means dates before, “>” is dates after, “==” is dates equal to, and so on.

To begin with, we will look at a simplified version of a 

conducted for the 

. This data contains information on all demographics related to companies from 2016. From this we’ll want to analyze companies. To begin with we’ll want to filter down this data to exclude firms, and show only companies that are at most two years old. Finally, in order to make a good investment we want to follow startups that are looking into a particular market niche.

We’ll begin by using the Python Pandas library to import our census data and begin filtering it to find the up and coming startups of 2016. We’ll break down our criteria into 3 conditional statements. 

First, we want to identify the startup companies so we’ll filter all companies that have been running for two or more years. Then we’ll exclude all firms to get the parent companies only. Finally, we’ll look at their success by filtering out those which have an annual spend of 1 million dollars or more.

According to the census, the 

YIBSZFI_TTL

represents the “Years in business from initial founding”. Here we’ll see if there’s an exact match for our value “Firms with less than 2 years in business.” Our conditionals that we’ll want to start off with is to filter out all the firms that aren’t startups. Next we’ll want to get the firms that don’t make over $1 million.

We covered many of the ways filter works in a more comprehensive guide on “

”, but here we’ll write a simple 

statement using == to check for an exact match.

Then, we’ll keep only the companies that didn’t have an annual spending of 1 million dollars. Here we’ll create a filter for annual pay, 

PAYANN

, to determine if a company was profitable. By our definition of profitable, we’ll be checking if the value was greater than or equal to a million dollars. 

1
profitable = data[(data['PAYANN']>=1000000)]

Finally, because we want companies that are specialized and have a niche in the market, we’ll filter out companies that are more generalist. This is represented by the North American Industry Classification System (NAICS). For a generalist company, it will have a value of "Total for all sectors".

1
niche = data[(data['NAICS2012_TTL'] != "Total for all sectors" )]

As a one liner, you can combine all the statements together with the use of the & (and).

1
2
3
top_startups = data[(data['PAYANN']>=1000000)
& (data['YIBSZFI_TTL] == 'Firms with less than 2 years in business')
& (data[' NAICS2012_TTL'] != "Total for all sectors" )]

Now that we’re done, we have a list of all companies that have an annual spending of 1 million dollars, so they’ve got to be making quite some bank or have investors who believe in them. That’s the end of filtering our dataset, we now have a good amount of data that can be used to answer your questions on what companies you should invest in. With this data, we can develop a ranking model to create a model to rank the top 100 startups and create predictive models for the generations to come.

But now with Mage, you can create a model to demonstrate your pitch by comparing yourself to the competition. Here, we’ll recreate our dataset and apply the same filter in less than a minute. You don’t need to know how to code Python, but some basic understanding of logic is required such as conditionals.

Want to learn more about machine learning (ML)? Visit 

! ✨🔮