Data cleaning is key step in a analysis cycle. Without preparing the data you are given, you are going to hinder your ability to conduct analysis later on. On this page, you will see examples of data cleaning I have conducted in Python.
Summary of project:
The data set for this project is data regarding the FTSE 100 share index and the companies within it. After loading the data set, it was apparent there were a few errors within, which needed the be rectified before conducting a few pieces af analysis.
Here is an overview of the data set using the .head() function
Here we have another overview of each series in the data frame using the .info() function.
Immediately, I noticed 3 things:
There are 101 entries for a set that (should) consist of 100 entries.
The 'Strong Buy' series doesn't actually have any values in it.
The 'Mid-price' series is an object data type, when it should be a float.
These will be the 3 things to rectify.
First thing to do, was to find the additional entry in the data set. Given the data is relating to FTSE 100, there should only be 1 entry for each company.
With this in mind, I found the occurances of each company in the data to find a potential duplicate. As we can see to the right, 'Royal Shell Company' has 2 entries when it should only have 1.
I created a copy of the data set to modify the data without impacting the original. I then conducted a search for all entries which the company is the 'Royal Dutch Shell'. It came back with the 2 entries above.
From here, I decided that I would remove the first entry as it did not have a 'Our view' value (unlike every other entry). Using the .drop() function, I removed the entry with index 75 and also reset the index so that there was not a gap in the data frame indexing. This left the 1 entry seen below:
Our second task was to remove the 'Strong Buy' series due to no values being in the series.
To do this, I used the .drop() function again to remove the 'Strong Buy' series:
As you can see from both the data frame above and the summary information to the left, the 'Strong Buy' column has been successfully removed.
Finally, I needed to change the data type of the 'Mid-price' series. Looking at the current format, I will be unable to change the elements into floats whilst a comma (,) is used to separate the thousands in each value.
I started by again, creating a copy of the cleaned data set, then proceeding to remove the commas using the .str.replace() function and the .astype() function:
From the pictures above and to the left, the 'Mid-price' series has been successfully converted into the float data type.
Looking at the first few entries of the latest data set, I noticed that the format of the 'Change' series is inconsistent, with some values being a percentage and others being the decimal equivalent.
I went about standardising the format of the series, converting the values to floats and then creating a new series with the percentage change.
First I created a function to format and cast the values to a float.
Reformatting function code
'Change %' series creation
Next, I created a new column called 'Change %' using the .map() function, along with the user-made function shown above.
From the picture above and to the right, we can see that the 'Change %' series has been successfully added to the data frame and in the correct data type of a float.
Now that the data set is cleaned, I wanted to create some comparative metrics to the data frame.
This was going to be in the form of a 'Beat Market' value and a 'Buy Ratio'. The 'Beat Market' is going to be a boolean value of whether the 'Change %' is above or below the average for the FTSE 100 overall. The 'Buy Ratio' will represent the 'Buy' value divided by the 'Broker' value.
