How to Clean Data

Having gone through the procedures described in the previous post and identified unclean data, your next challenge is how to clean it and use accurate data for analysis.

You have five possible alternatives for handling such a situation:

● Data imputation

If you are unable to find the necessary values, you can impute them by filling in the gaps for the inaccurate values. The closest explanation for imputation is that it is a clever way of guessing the missing values, but through a data-driven scientific procedure. Some of the techniques you can use to impute missing data include stratification and statistical indicators like mode, mean and median. If you have studied the data and identified unique patterns, you can stratify the missing values based on the trend identified. For example, men are generally taller than women. You can use this presumption to fill in missing values based on the data you already have.

The most important thing, however, is to try and seek a second opinion on the data before imputing your new values. Some datasets are very critical, and imputing might introduce a personal bias which eventually affects the outcome.

● Data scaling

Data scaling is a process where you change the data range so that you have a reasonable range. Without this, some values that might appear larger than others might be given prominence by some algorithms. For example, the age of a sample population generally exists within a smaller range compared to the average population of a city. Some algorithms will give the population priority
over age, and might ignore the age variable altogether.

By scaling such entries, you maintain a proportional relationship between different variables, ensuring that they are within a similar range. A simple way of doing this is to use a baseline for the large values, or use percentage values for the variables.

● Correcting data


Correcting data is a far better alternative than removing data. This involves intuition and clarification. If you are concerned about the accuracy of some data, getting clarification can help allay your fears. With the new information, you can fix the problems you identified and use data you are confident about in your analysis.

● Data removal

One of the first things you could think about is to eliminate the missing entries from your dataset. Before you do this, it is advisable that you investigate to determine why the entries are missing. In some cases, the best option is to remove the data from your analysis altogether. If, for example, more than 80% of entries in a row is missing and you cannot replace them from any other source, that row will not be useful to your analysis. It makes sense to remove it.

Data removal comes with caveats. If you have to eliminate any data from your analysis, you must give a reason for this decision in a report accompanying your analysis. This is important so as to safeguard yourself from claims of data manipulation or doctoring data to suit a narrative. Some types of data are irreplaceable, so you must consult experts in the associated fields before you remove them. Most of the time, data removal is applied when you identify duplicates in the data, especially if removing the duplicates does not affect the outcome of your analysis.

● Flagging data

There are situations where you have columns missing some values, but you cannot afford to eliminate all of them. If you are working with numeric data, a reprieve would be to introduce a new column where you indicate all the missing values. The algorithm you are using should identify these values as such. In case the flagged values are necessary in your analysis, you can impute them or find a better way to correct them then use them in your analysis. In case this is not possible, make sure you highlight this in your report.

Cleaning erroneous data can be a difficult process. A lot of data scientists generally hope to avoid it, especially since it is time-consuming. However, it is a necessary process that will bring you closer to using appropriate data for your analysis. Remember that the main objective is to use clean data that will give you the closest reflection of the true picture of events.

Read More

Identify Inaccurate Data

More often, you need to make a judgement call to determine whether the data you are accessing is accurate or not.

As you go through data, you must make a logical decision based on what you see. The following are some factors you should think about:

● Study the range

First, check the range of data. This is usually one of the easiest problems to identify. Let’s say you are working on data for primary school kids. You know the definitive age bracket for the students. If you identify age entries that are either too young or too old for primary school kids whose data you have, you need to investigate further.

Essentially what you are doing here is an overview of a max-min approach. With these ranges in mind, you can skim through data and identify erroneous entries. Skimming through is easy if you are working with a few entries. If you have thousands or millions of data entries, a max-min function code can help you identify the wrong entries in an instant. You can also plot the data on a graph and
visually detect the values that don’t fall within the required distribution pattern.

● Investigate the categories

How many categories of data do you expect? This is another important factor that will help you determine whether your data is accurate or not. If you expect a dataset with nine categories, anything less is acceptable, but not more. If you have more than nine categories, you should investigate to determine the legitimacy of the additional categories. Say you are working with data on marital status, and your expected options are single, married, divorced, or widowed. If the data has six categories, you should investigate to determine why there are two more.

● Data consistency

Look at the data in question and ensure all entries are consistent. In some cases, inaccuracies appear as a result of inconsistency. This is common when working with percentages. Percentages can either be fed into data sets as basis points or decimal points. If you have data that has both sets of entries, they might be incompatible.

● Inaccuracies across multiple fields

This is perhaps one of the most difficult challenges you will overcome when cleaning inaccurate data. The following entries, for example, are valid individually. A 4-year old girl is a valid age entry. 5 children is also a valid entry. However, a datapoint that depicts Grace as a 4-year old girl with 5 children is absurd. You would need to check for inconsistencies and inaccuracies in several rows and columns.

● Data visualization

Plotting data in visual form is one of the easiest ways of identifying abnormal distributions or any other errors in the data. Say you are working with data whose visualization should result in a bimodal distribution, but when you plot the data you end up with a normal distribution. This would  immediately alert you that something is not right, and you need to check your data for accuracy.

● Number of errors in your data set

Having identified the unique errors in the data set, you must enumerate them. Enumeration will help you make a final decision on how and whether to use the data. How many errors are there? If you have more than half of the data as inaccurate, it is obvious that your presentation would be greatly flawed. You must then follow up with the individuals who prepared the data for clarification or find an alternative.

● Missing entries

A common data concern that data analysts deal with is working with datasets missing some entries. Missing entries is relative. If you are missing two or three entries, this should not be a big issue. However, if your data set is missing many entries, you have to find out the reason behind this. Missing entries usually happen when you are collating data from multiple sources, and in the process some of the data is either deleted, overwritten, or skipped. You must investigate the missing entries because the answer might help you determine whether you are missing only a few entries that might be insignificant going forward, or important entries whose absence affects the outcome.

Read More

Data Cleaning

Data cleaning is one of the most important procedures you should learn in data analysis. You will constantly be working with different sets of data and the accuracy or completeness of the same is never guaranteed. Because of this reason, you should learn how to handle such data and make sure the incompleteness or errors present do not affect the final outcome.



Why should you clean data, especially if you did not produce it in the first place?

Using unclean data is a sure way to get poor results. You might be using a very powerful computer capable of performing calculations at a very high speed, but what they lack is intuition. Without this, you must make a judgement call each time you go through a set of data. In data analysis, your final presentation should be a reflection of the reality in the data you use. For this reason, you must eliminate any erroneous entries.

Possible Causes of Unclean Data

One of the most expensive overheads in many organizations is data cleaning. Unclean data is present in different forms. Your company might suffer in the form of omissions and errors present in the master data you need for analytical purposes. Since this data is used in important decision-making processes, the effects are costly. By understanding the different ways dirty data finds its way into your organization, you can find ways of preventing it, thereby improving the quality of data you use.
In most instances, automation is applied in data collection. Because of this, you might experience some challenges with the quality of data collected or consistency of the same. Since some data is obtained from different sources, they must be collated into one file before processing. It is during this process that concerns as to the integrity of the data might arise. The following are some explanations as to why you have unclean data:

● Incomplete data

The problem of incomplete data is very common in most organizations. When using incomplete data, you end up with many important parts of the data blank. For example, if you are yet to categorize your customers according to the target industry, it is impossible to create a segment in your sales report according to industry classification. This is an important part of your data analysis that will be
missing, hence your efforts will be futile, or expensive in terms of time and resources invested before you get the complete and appropriate data.

● Errors at input

Most of the mistakes that lead to erroneous data happen at data entry points. The individual in charge might enter the wrong data, use the wrong formula, misread the data, or innocently mistype the wrong data. In the case of an open-ended report like questionnaires, the respondents might input data with typos or use words and phrases that computers cannot decipher appropriately. Human error at
input points is always the biggest challenge in data accuracy.

● Data inaccuracies

Inaccurate data is in most cases a matter of context. You could have the correct data, but for the wrong purpose. Using such data can have far-reaching effects, most of which are very costly in the long run. Think about the example of a data analyst preparing a delivery schedule for clients, but the addresses are inaccurate. The company could end up delivering products to their customers, but with the wrong address details. As a matter of context, the company does have the correct addresses for their clients, but they are not matched correctly.


● Duplicate data

In cases where you collect data from different sources, there is always a high chance of data duplication. You must have a lot of checks in place to ensure that duplicates are identified. For example, one report might list student scores under Results, while another will have them under Performance. The data under these tags will be similar, but your sensors will consider them as two independent entities.

● Problematic sensors

Unless you are using a machine that periodically checks for errors and corrects them or alerts you, it is possible to encounter errors as a result of problematic sensors. Machines can be faulty or breakdown too, which increases the likelihood of a problematic data entry.

● Incorrect data entries

An incorrect entry will always deliver the wrong result. Incorrect entry happens when your dataset includes entries that are not within the acceptable range. For example, data for the month of February should range from 1 to 28 or 29. If you have data for February ranging up to 31, there is definitely an error in your entries.

● Data mungling

If at your data entry point you use a machine with problematic sensors, it is possible to record erroneous values. You might be recording people’s ages, and the machine inputs a negative figure. In some cases, the machine could actually record correct data, but between the input point and the data collection point, the data might be mungled, hence the erroneous results. If you are accessing data
from a public internet connection, a network outage during data transmission might also affect the integrity of the data.

● Standardization concerns

For data obtained from different sources, one of the concerns is often how to standardize the data. You should have a system or method in place to identify similar data and represent them accordingly. Unfortunately, it is not easy to manage this level of standardization. As a result, you end up with erroneous entries. Apart from data obtained from multiple sources, you can also experience challenges dealing with data obtained from the same source. Everyone inputs data uniquely, and this might pose a challenge at data analysis.

Read More

Data Manipulation

By this point, you are aware of how to draw summaries from the data in your possession. Beyond this, you should learn how to slice, select, and extract data from your DataFrame. I mentioned earlier that DataFrames and Series share many similarities, especially in the methods used on them. However, their attributes are not similar. Therefore you must be keen to make sure you are using the right attributes, or you will end up with attribute errors.


To extract a column, you use square brackets as shown below:

position_col = squad_df['position']
type(position_col)

You will get the output below:

pandas.core.series.Series

The result is a Series. However, if you need to return the column as a Dataframe, you must use column names as shown below:

position_col = squad_df[['position']]
type(position_col)

You will get the output below:

pandas.core.frame.DataFrame

What you have now is a simple list. Onto this list, you can add a new column as follows:

subset = squad_df[['position', 'earnings']]
subset.head()

You should get the output below:




Next, we will look at how to call data from your DataFrame using rows. You can do this using any of the following means:

● Locating the name (.loc)
● Locating the numerical index (.iloc)

Since we will still be indexed using the Teams, we must use .loc and assign it the name of the team as shown below:

eve = squad_df.loc["Everton"]
eve

Another option is to use .iloc for the numerical index of Everton as shown below:

eve = squad_df.iloc[1]

The .iloc slice works in the same way that you slice lists in Python. Therefore, the item found in the index section at the end is omitted.

Read More

Describing Variables

There is so much more information you can get from your DataFrames. A summary of the continuous variables can be arrived at using the following syntax:

squad_df.describe()

This will return information about continuous numbers. This information is useful when you are uncertain about the kind of plot diagram to use for visual representation. .describe() is a useful attribute because it returns the number of rows, categories, and frequency of the top category about a specific column.


squad_df['position'].describe()

The syntax above will return an output in the following format:

count xx
unique xx
top xx
freq xx
Name: genre, dtype: object

What we can deduce from this output is that the selected column contains xx number of unique values, the top value in that column, and the fact that the top column shows up xx number of times (freq) . To determine the frequency of all the values in the position column, you use the syntax below:

squad_df['position'].value_counts().head(10)

You can also find out the relationship between different continuous variables using the .corr() syntax as shown below:

squad_df.corr()

The output is a correlation table that represents different relationships in your dataset. You will notice positive and negative values in the output table. Positive results show a positive correlation between the variables. This means that one variable rises as the other rises and vice versa. Negative results show an inverse correlation between the variables. This means that one variable will rise as the other falls. A perfect correlation is represented by 1.0. A perfect correlation is obvious for each column with itself.

Read More

Data Imputation

Imputation is a cleaning process that allows you to maintain valuable data in your DataFrames, even if they have null values. This is important in situations where eliminating rows that contain null values might eliminate a lot of data from your dataset. Instead of losing all values, you can use the median or mean of the column in place of the null value.

Using the example above, and assuming a new column for earnings from gate receipts earned by the clubs over the season. Some values are missing in that revenue column. To begin, you must extract the revenue column and use it as a variable. This is done as shown below:


earnings = squad_df[‘earnings_billions’]

Take note that when you are selecting columns to use from a DataFrame, you must enclose them with square brackets as shown above. To handle the missing values, we can use the mean as follows:

earnings_mean = earnings.mean()
earnings_mean

The output should deliver the mean of all the values in the specified cells. Once you have this, you replace it in the null values using the following syntax:

fillna() as shown below:

earnings.fillna(earnings_mean, inplace=True)

This will replace all the null values in the earnings column with the mean of that column. The syntax inplace=True changes the original squad_df.

Read More

Computation with Missing Values

One thing you can be certain about as a data analyst is that you will not always come across complete sets of data. Since data is collected by different people, they might not use the same conventions you prefer. Therefore, you can always expect to bump into some challenges with missing values in datasets.

In Python, you will encounter None or np.nan in NumPy whenever you come across such types of data. Since you must proceed with your work, you must learn how to handle such scenarios. You have two options: either replace the null values with non-null values or eliminate all the columns and rows that have null values.

First, you must determine the number of null values present in each column within your dataset. You can do this in the syntax below:

squad_df.isnull()

The result is a DataFrame that has True or False in each cell, in relation to the null status of the cell in question. From here, you can also determine the number of null returns in every column through an aggregate summation function as shown below:

squad_df.isnull() .sum()

The result will list all the columns, and the number of null values in each. To eliminate null values from your data, you have to be careful. It is only advisable to eliminate such data if you have deep knowledge of the explanation behind the null values. Besides, it is only advisable to eliminate null data if you are missing a small amount. This should not have a noteworthy effect on the data. The following syntax will help you eliminate null data from your work:

squad_df.dropna()

The syntax above eliminates all rows with at least one null value from your dataset. However, this syntax will also bring forth a new DataFrame without changing the original DataFrame you have been using.

The problem with this operation is that it will eliminate data from the rows with null values. However, some of the columns might still contain some useful information in the eliminated rows. To circumvent this challenge, we must learn how to perform imputation on such datasets.

Instead of eliminating rows, you can choose to eliminate columns that contain null values too. This is performed with the syntax below:

axis=1

For example, squad_df.dropna(axis=1)

What is the explanation behind the axis=1 attribute? Why does it have to be 1 in order to work for columns? To understand this, we take a closer look at the .shape output discussed earlier.

squad_df.shape

Output
(20,2)

In the example above, the syntax returns the DataFrame in the form of a tuple of 20 rows and 2 columns. In this tuple, rows are represented as index zero, while columns are represented as index one. From this explanation, therefore, axis=1 will work on columns.

Read More

Cleaning Data in a Column

We often come across datasets that have varying names for their columns, encounter typos, spaces, and a mixture of upper and lower-case words.

Cleaning up these columns will make it easier for you to choose the correct column for your computations.


In the example shown in the previous post, the syntax below will help us print the column names:


squad_df.columns

You will have the following output:

Index ([‘Position’, ‘Designation’])

Once you have this information, you can use a simple command .rename() to rename some or all the columns in your data. Since we do not need to use any parentheses, we will rename the content as follows:

Assuming the Designation Column was named Designation (Next Season), you would have it renamed as follows

squad_df.rename(columns={
‘Designation (Next Season)': 'Designation_next_season',
}, inplace=True)
squad_df.columns

Our output would look like this:

Index ([‘Position’, ‘Designation_next_season’])

You can also use the same process to change the column content from upper to lower case without having to enter all the connotations individually. A list comprehension will help you instead of manually changing the name of each item on the column list as shown below:

squad_df.columns = [col.lower() for col in squad_df]
squad_df.columns

You will have the following output:

Index ([‘position’, ‘designation_next_season’])

Over time, you will use a lot of dict and list attributes in Pandas. To make your work easier, it is advisable to do away with special characters and use lower case connotations instead. You should also use underscores instead of spaces.

Read More

Dealing with Duplicates

The example we used in the previous post does not have any duplicate rows, thus we need to learn how to identify duplicates to ensure that we perform accurate computations. In the example in our previous post, we can append the squad Dataframe to itself and double it as shown:

temp_df = squad_df.append(squad_df)
temp_df.shape

Our output will be as follows:

(40, 2)

The append() attribute copies the data without altering the initial DataFrame. The example above does not use the real data, hence display in temp . In order to do away with the duplicates, we can use the following attribute:

temp_df = temp_df.drop_duplicates()
temp_df.shape

Our output will be as follows:

(20, 2)

The drop_duplicates() attribute works in the same manner that the append() attribute does. However, instead of doubling the DataFrame, it results in a fresh copy without duplicates. In the same example, .shape helps to confirm whether the dataset we are using has 20 rows as was present in the original file.

In Pandas, the keyword inplace is used to alter the DataFrame objects as shown below:

temp_df.drop_duplicates(inplace=True)

The syntax above will change your data automatically. The drop_duplicates() argument is further complemented with the keep argument in the following ways:

● False – This argument will eliminate all duplicates
● Last – This argument will eliminate all duplicates other than the last one.
● First – This argument will eliminate all duplicates other than the first one.

In the examples we used above, the keep argument has not been defined. Any argument that is not defined will always default to first . What this means is that if you have two duplicate rows, Pandas will maintain the first one but do away with the second.

If you use last , Pandas will drop the first row but maintain the second one. Using keep , however, will eliminate all the duplicates. Assuming that both rows are similar, keep will eliminate both of them. Let’s look at an example using temp_df below:

temp_df = squad_df.append(squad_df) # generate a fresh copy
temp_df.drop_duplicates(inplace=True, keep=False)
temp_df.shape

We will have the output below:

(0, 2)

In the above example, we appended the squad list, generating new duplicate rows. As a result, keep=False eliminated all the rows, leaving us with zero rows. This might sound absurd, but it is actually a useful technique that will help you determine all the duplicates present in the dataset you are working on.

Read More

Extracting Information from Data

The .info() command will help you derive information from your data sets. The syntax is as follows:

squad_df.info()

You will have the following output:

<class ‘pandas.core.frame.DataFrame’>
Index: 20 entries, Manchester United to Swansea
Data Columns (total 2 columns):
Position 20 non-null int64
Designation 20 non-null object
dtypes: int64 (1), object (1)
memory usage: 35.7+ KB

The .info() command will deliver all the important information you need about the dataset, including how many non-null values are available, the number of columns and rows, memory used by the DataFrame, and the type of data available in every column.

The dataset you are using might contain missing values in some columns. You will need to learn how to address these, to help in cleaning the data for final presentation.

Why do you need to determine the datatype? 

Without this, you might struggle to interpret data correctly. If, for example, you are using a JSON file but the integers are stored as strings, most of your operations will not work. This is because it is impossible to perform mathematical computations with strings. This is why the .info() is useful. You know the kind of content present in every column.

The .shape attribute can also help you because it delivers the tuple of rows and columns in the dataset. In the example above, you can have it as follows:

squad_df.shape

Your output will be as follows:

(20, 2)

It is also important to remember that there are no parentheses used in the .shape attribute. It basically returns the tuple format for rows and columns. In the example above, we have 20 rows and 2 columns in the squad DataFrame. As you work with different sets of data, you will use the .shape attribute a lot to transform and clean data.

Read More

Obtaining Data from SQL Databases

Before you begin, check to ensure you have a connection with the Python library in question. Once the connection is established, you can then push a query to Pandas. You need SQLite to establish a connection with your database, from where you will then create a DataFrame using the SELECT query as follows:

import sqlite3
con = sqlite3.connect("database.db")

Using our car dealership example from the previous posts, the SQL database will have a table denoted as sales , and the index. We can read from the database using the command below:

df = pd.read_sql_query("SELECT * FROM sales", con)
df

You will have the following output:






Just as we did with the CSV files, you can also bypass the index as follows:
df = df.set_index('index')
df

You will have the output below:



Once you are done with your data, you need to save it in a file system that is relevant to your needs. In Pandas, you can convert files to and from any of the file formats discussed above in the same way that you read the data files, when storing them as shown below:

df.to_csv('new_sales.csv')
df.to_sql('new_sales', con)
df.to_json('new_sales.json')


In data analysis, there are lots of methods that you can employ when using DataFrames, all of which are important to your analysis. Some operations are useful in performing simple data transformations, while others are necessary for complex statistical approaches.

In the examples below, we will use an example of a dataset from the English Premier League below:

squad_df = pd.read_csv("EPL-Data.csv", index_col="Teams")

As we load this dataset from the CSV file, we will use teams as our index. To view the data, you must first open a new dataset by printing out rows as follows:

squad_df.head()

You will have the following Output:






.head() will by default print the first five rows of your DataFrame. However, if you need more rows displayed, you can input a specific number to be printed as follows:


squad_df.head(7)

This will output the top seven rows as shown below:



In case you need to display only the last rows, use the .tail() syntax. You can also input a specific number. Assuming we want to determine the last three teams, we will use the syntax below:

squad_df.tail(3)

Our output will be as follows:



Generally, whenever you access any dataset, you will often access the first five rows to determine whether you are looking at the correct data set. From the display, you can see the index, column names, and the preset values. You will notice from the example above that the index for our DataFrame is the Teams column.

Read More