The Easiest Data Cleaning Method
The Easiest Data Cleaning Method using Python & Pandas
In this post, we are going to learn how to do simplify our data preprocessing work using the Python package Pyjanitor. More specifically, we are going to learn how to:
Add a column to a Pandas dataframe
Remove missing values
Remove an empty column
Cleaning up column names
Table of Contents
Clean Data in Python
That is, we are going to learn how clean Pandas dataframes using Pyjanitor. In all Python data manipulation examples, here we are also going to see how to carry out them using only Pandas functionality.
What is Pyjanitor?
What is Pyjanitor? Before we continue learning on how to use Pandas and Pyjanitor to clean our datasets, we will learn about this package. The python package Pyjanitor extends Pandas with a verb-based API. This easy to use API is providing us with convenient data cleaning techniques. Apparently, it started out as a port of the R package janitor. Furthermore, it is inspired by the ease-of-use and expressiveness of the r-package dplyr. Note, there are some different ways how to work with the methods and this post will not cover all of them (see the documentation).
How to install Pyjanitor
There are two easy methods to install Pyjanitor:
1. Installing Pyjanitor using Pip
2. Installing Pyjanitor using Conda:
Now that we know what Pyjanitor is and how to install the package we soon can continue the Python data cleaning tutorial by learning how to remove missing values from Pandas. Note, that this Pandas tutorial will walk through each step on how to do it using Pandas and Pyjanitor. In the end, we will have a complete data cleaning example using only Pyjanitor and a link to a Jupyter Notebook with all code.
Fake Data to Clean using Python
In the first Python data manipulation examples, we are going to work with a fake dataset. More specifically, we are going to create a dataframe, with an empty column, and missing values. In this part of the post we are, further, going to use the Python packages SciPy, and NumPy. That is, these packages also need to be installed.
In this example, we are going to create three columns; Subject, RT (response time), and Deg. To create the response time column, we will use SciPy norm to create data that is normally distributed.
Python Normal Distribution using Scipy
In the next code chunk, we create a variable, for response time, using a normal distribution.
Shuffling the List and Adding Missing Values
Furthermore, we are adding some missing values and shuffling the list of normally distributed data:
Dataframe from Dictionary
Finally, we are creating a dictionary of our two variables and use the dictionary to create a Pandas dataframe.
Data Cleaning in Python with Pandas and Pyjanitor
How to Add a Column to Pandas Dataframe
Now that we have created our dataframe from a dictionary we are ready to add a column to it. In the examples, below, we are going to use Pandas and Pyjanitors method.
1. Append a Column to Pandas Dataframe
It’s quite easy to add a column to a dataframe using Pandas. In the example below we will append an empty column to the Pandas dataframe:
2. Adding a Column to Pandas Dataframe using Pyjanitor
Now, we are going to use the method add_column to append a column to the dataframe. Adding an empty column is not as easy as using the method above. However, as you will see towards the end of this post, we can use all of the methods when creating our dataframe:
How to Remove Missing Values in Pandas Dataframe
It is quite common that our dataset is far from complete. This may be due to error in the measurement instruments, people forgetting, or refusing, to answer certain questions, amongst many other things. Despite the reason behind missing information, these rows are called missing values. In the framework of Pandas the missing values are coded by the symbol NA, much like in R statistical environment. Pandas have the function isna() to help us identify missings in our dataset. If we want to drop missing values, Pandas have the function dropna().
1 Dropping Missing Values using Pandas dropna method
In the code example below we are dropping all rows with missing values. Note, if we want to modify the dataframe we should add the inplace parameter and set it to true.
Dropping Missing Values from Pandas Dataframe using PyJanitor
The method to drop missing values from a Pandas Dataframe using Pyjanitor is the same as the one above. That is, we are going to use the dropna method. However, when using Pyjanitor we also use the parameter subset to select which column(s) we are going to use when removing missing data from the dataframe:
How to Remove an Empty Column from Pandas Dataframe
In the next Pandas data manipulation example, we are going to remove the empty column from the dataframe. First, we are going to use Pandas to remove the empty column and, then, we are going to use Pyjanitor. Remember, towards the end of the post we will have a complete example in which we carry out all data cleaning while actually creating the Pandas Dataframe.
1. Removing an Empty Column from Pandas Dataframe
When we want to remove an empty column (e.g., with missing values) we use the Pandas method dropna again. However, we use the axis method and set it to 1 (for column). Furthermore, we also have to use the parameter how and set it to ‘all’. If we don’t it will remove any column with missing valuesRemoved empty columns
2. Deleting an Empty Column from Pandas Dataframe using Pyjanitor
It’s a bit easier to remove an empty column using Pyjanitor:
How to Rename Columns in Pandas Dataframe
Now that we know how to remove missing values, add a column to a Pandas dataframe, and how to remove a column, we are going to continue this data cleaning tutorial learning how to rename columns.
For instance, in the post where we learned how to load data from a JSON file to a Pandas dataframe, we renamed columns to make it easier to work with the dataframe later. In the example below, we will read a JSON file, and rename columns using both Pandas dataframe method rename and Pyjanitor
Learn more about how to use iloc to slice Pandas dataframes by rows and columns
More about loading data to dataframes:
1 Renaming Columns in Pandas Dataframe
As can be seen in the image above, there are some whitespaces and special characters that we want to remove. In the first renaming columns example, we are going to use Pandas rename method together with regular expressions to rename the columns (i.e., we are going to replace whitespaces and \ with underscores).
2. How to Rename Columns using Pyjanitor and clean_names
The task to rename a column (or many columns) is way easier using Pyjanitor. In fact, when we have imported this Python package, we can just use the clean_names method and it will give us the same result as using Pandas rename method. In fact, using clean_names we also get all letters in the column names to lowercase:
How to Clean Data when Loading the Data from Disk
The cool thing with using Pyjanitor to clean our data is that we can do use all of the above methods when loading our data. For instance, in the final data cleaning example, we are going to add a column to the dataframe, remove empty columns, drop missing data, and clean the column names. This is what makes working with Pyjanitor our lives easier.
Aggregating Data using Pyjanitor
In the last example we are going to use Pandas methods agg, groupby, and reset_index together with the Pyjanitor method collapse_levels to calculate the mean and standard for each sector:
More about grouping and aggregating data using Python and Pandas:
Conclusion:
In this post, we have learned how to do some data cleaning methods. Specifically, we have learned how to append a column to a Pandas dataframe, remove empty columns, handling missing values, and renaming the columns (i.e., getting better column names). There are, of course, many more data cleaning methods available, both when it comes to Pandas and Pyjanitor.
In conclusion, the methods added by the Python package are both similar to the one of the R-package janitor and dplyr. These methods will make our lives easier when preprocessing our data.
What is your favorite data cleaning method and/or Package? It can be either using R, Python, or any other programming language. Leave a comment below!
Last updated