Happy New Year! I can’t believe I haven’t put up a post this year. I won’t promise to do better, but I’m willing and hoping to try. Anyway, I figured I talk about things I have learnt, without necessarily sharing back (or tutoring) and that’s not the purpose of this blog. I just might lose some of my readers here, but before you leave, I run a farm produce delivery business. Please follow on Instagram and hit us up if you are in Lagos.
As I mentioned in my last post, I learned to use Python for data analysis and I’m trying to be more proficient with it. I came about this database some weeks back and while studying it, I made some observations and cleaned a column. So that’s really all this post is about, simple and nothing major, just how I cleaned a column two weeks ago.
I volunteer and teach elementary school children digital literacy and try to break everything down to the barest minimum. However with this, I don’t know what the barest minimum is but I’d start with this. Cognitive Class (where I take my analytics courses) gives their students opportunities to use different types of labs and I use Jupyter notebook basically because my courses were taught with it and I like using it so it works. The downside to working online is when the internet breaks or when there’s no light and I can’t use my Wi-Fi at that moment. I decided to start working offline.
I had python installed on my computer earlier because I was using django for some web developments. Then I asked a friend (not google) how to get Jupyter notebook offline and that’s how I learnt about anaconda. I believe installing anaconda means installing everything you need to analyze data with Python so you don’t have to download python on its own.
After installation, python libraries are pre-installed and we would be working with pandas. Pandas is an open source Python library for data analysis. It gives Python the ability to work with spreadsheet-like data enabling fast file loading and manipulation among other functions.
So yeah, let’s go…
I got the database in Microsoft Excel, it contains some company data. I converted the file to csv, there are a lot of things I still don’t know (that’s why I’m going back to school), like why I decided to use csv, I just know I learnt working with csv files, I don’t know if it’s a better format, it’s ignorant but I’d figure it out one day and most likely share here.
First, I imported pandas and numpy. Cause my intentions with the file was more than I did. However for this exercise you only need pandas.
Something interesting happened when I wanted to read the file. I got the error below:
File “”, line 1
df = pd.read_csv(“C:\file path\filename.csv”)
^
SyntaxError: (unicode error) ‘unicodeescape’ codec can’t decode bytes in position 2-3: truncated \UXXXXXXXX escape
I checked google for a solution and that’s why I added ‘r’ (
pd.read_csv(r“C:\file path\filename.csv” )) before the file path. There were other solutions but I tried that first and it worked.
To get the first five rows of the database and column names, I ran “df.head()”. Please note that “df” can be named anything, so if I didn’t name my dataframe “df” but maybe “company” then to get the first five I would run company.head().
I proceeded to get the data types of all the columns and they were all objects. This didn’t look interesting as I just had few columns and no diverse data types. Anyway, I decided to play with it and work on the ‘STATE’ column. We have 37 (36 + FCT) states in Nigeria so I desired to just work with that and make analysis off that.
Cleaning the data
First thing I did when cleaning the data was check for the total number of states in the column.
No! This was too dirty for me. We have 36 states and FCT but now I had over 80 states. I forgot to run the analysis before cleaning up so I actually don’t know how many unique states were there earlier.
Before cleaning, I had to make sure I knew the exact format for the states then fix the errors. For some it was easy, it was spelling errors or omissions or too many spaces. Some had names of their villages or cities (I reached out to my friend google to figure out what states they were in). The one I can say I cheated with was Bendel. I replaced Bendel with Edo state, that’s because I didn’t want to go deep into it. Bendel was a state before it was split into Edo and Delta in 1991. What would have been ideal is having another column “LGA”, and it would be easier to clean up that way. I’d assign the states based on their LGAs but this is just a rough work so I replaced Bendel with Edo.
For places that I didn’t recognize like aguta, I used google.
Here’s how I replaced the states, and this is where I’d like my python gurus to pitch in.
This works but I’d assume it’s best to use this for small databases. If I was working with a very large database with a lot more errors, would I have to run this over and over? Is there another package that can do this faster? Or any other way around it? I tried to google for solutions in pandas but haven’t gotten yet. Anyway, I spent about 30 minutes cleaning one by one.
After cleaning, to get information about the column, I used .describe() . Now I have 37 states
With the column clean, one can proceed to do his/her analysis. I can see more companies are in Lagos, there just might be a story there. Do you use python? Would you clean the column like I did or another way? Are you new to data analysis? Did you learn something new? Please leave your thoughts and let me know.
Cool