In this lesson I want to talk to you a little bit more about cleaning data. 1
We've already gathered all our data and we've started on this process. 2
In particular what we've done so far is we've extracted the relevant data, namely the e-mail bodies from 3
all our text files and we've also converted all the text files into a pandas dataframe. 4
In other words, we've taken over 5000 different files, we've extracted the actual pieces of data that 5
we need from it and we've converted all of that into a format that we can work with in our Python code. 6
Now it's time for the next steps, namely checking for empty emails, checking for missing values or no 7
values in our data that we've imported into our Python notebook. 8
So without further ado, let me show you how to do that 9
in Jupyter notebook. I'm going to add another markdown cell right here that reads "Data Cleaning: Checking 10
for Missing Values". 11
Of course it would help if I could spell a bit better. 12
But there we go. 13
The handy thing is that we've put all our data into a pandas dataframe. 14
The first thing I want to do is check if any message bodies are equal to null. By null I mean that the value 15
is missing. 16
The way I can access a particular column in my dataframe, namely the messages column, is by writing 17
"data.MESSAGE". 18
This will access my message column which looks like so. The alternative syntax that you'll see to accomplish 19
the exact same thing is with the square brackets and then the actual column name between single quotes. 20
So that reads "MESSAGE". Voila! 21
Great. 22
Now there are 5800 rows in this column. 23
So if I wanted to check if any of them has a missing value, then I can use the method "isnull". What we 24
get in this case is a value True or False based on whether or not any particular value was null or not. 25
Now this is a very, very handy method, but it doesn't help me check if any of these values is equal to 26
null. 27
I mean part of the reason is is that, yes, I can scroll through here, 28
but Jupyter will actually only display so many rows. It will hide the middle part of my dataframe. 29
So what I'm going to do is I'm going to chain some more methods. 30
The first one is this values attribute. 31
And what this will do is instead of giving me the index name and the value here, it will just give me 32
numpy array. 33
It will just give me the values without the index. 34
The nice thing about this is that if I wanted to check if any of the 5800 rows 35
has a missing value, I can use the "any" method and chain that onto here and get my result. 36
So the good news for us is that the result is False. 37
We have no missing values in our message column. Now, missing values are actually not the same thing as 38
empty emails, right? An empty email would be something like this, an empty string. If I check what this 39
is I will actually see that is of type string. 40
This would simply be an empty string, a string of length zero. So what would be an example of a missing 41
value? Say I had a variable called "my_var" and we don't want to put any value inside this variable, we 42
simply want it to be empty or contain nothing. 43
How would we accomplish this in Python? 44
Now you might think that given that we've used isnull as a method on our dataframe, you could simply 45
write something like "myvar = null" and create your empty variable. 46
However, even though this null keyword is commonly used in other programming languages like say Java, 47
in Python there is another keyword that has the same meaning 48
and this is the keyword None. 49
And you can see that Jupyter notebook is instantly picking up the fact that this is a keyword with the 50
syntax highlighting. 51
So in this case, if I was to check the type of my variable, 52
I would see that it is indeed a NoneType, 53
my_var is truly empty. 54
It truly contains nothing. 55
Now conceptually, 56
the way I like to remember this difference between zero or an empty string with a null value is with 57
this picture. 58
The Internet has truly blessed us with lots of terrible programming jokes and I'm going to do my 59
best to share them with you. 60
But in all seriousness, this is actually quite a helpful way about thinking about null values or None 61
values as they're known in Python. 62
Okay, so even though that we've established that there's no entry in our message column with the value 63
null or None, it doesn't mean that we don't have empty emails that could be in our dataset. 64
Okay, so let me show you how we would write a check for discovering if there are any strings of length 65
zero in this entire message column in our dataframe. 66
I'll add a little comment here that reads "check if there are empty emails (string length zero)". 67
The way I would go about doing this is grabbing our dataframe, then grabbing the message column, then 68
converting that to a string and then grabbing the length of these strings. 69
Let's take a look at what this looks like. 70
Here we can see how many characters each email body has in our messages column. 71
Now if we wanted to check if any of them are equal to zero, 72
in other words if any of them look like this, we can simply use the double equal sign and then zero for 73
the logical test. 74
If I press Shift+Enter now we again get an entire column of True or False values. 75
All right. 76
We can't look through 5800 different messages, so the any method is our best 77
friend again to see if there are any rows where the value is equal to True instead of False. 78
Let's take a look. 79
Huh. 80
So there are some empty emails somewhere in our dataframe. 81
What we don't know yet is how many of these rows have a string length of zero. 82
So how would we check that? 83
Well we could take our logical test here, 84
remember this returned 5800 entries of True or False. 85
Now the Boolean value of False is equated with the numerical value of 0 and the Boolean value of True 86
equates to the value 1. 87
So if we wanted to know how many rows have a string length of 0, all we would have to do is sum them up. 88
This sums up all the zeros and any ones, any true values in the 5800 entries. 89
Okay. 90
So we're going deeper down the rabbit hole. 91
We now know that among the 5800 entries there are 4 strings of length 92
zero. Now, we have to find out where in our enormous dataframe these strings are. 93
We have to find the rows that contain these empty strings. 94
But before we do that I want to pose a challenge to you. 95
Namely, I'd like you to pause the video and write the Python code that would check for the number of 96
entries with null or None values. 97
Did you solve it? 98
Here's the solution. 99
Now I know we don't have any null values in this dataframe, but that doesn't mean that your future projects 100
won't be having this issue. 101
So with "data.MESSAGE" we can pull up our message column and then with "isnull()" we can find out if 102
there are any null values. 103
And by summing up all the True and False values that we get out of this bit of code here, we can find 104
out the number of null values. 105
The answer of zero shouldn't be surprising, because we've already established that there are no null 106
values in the message column. 107
But I do think that this is quite an important piece of the data cleaning workflow, so it's good to have 108
this down and practice this once or twice. 109
Let me add some more empty cells here. 110
Now I'll add a markdown cell and what I'll do here is I'll just write "Locate empty emails". 111
Let's find out the row names of our empty emails. 112
As you can probably guess this condition is gonna be key. 113
This is what we're going to be using to get a pandas series of True or False values for where these 114
rows are empty. 115
Now if I wanted to pull out the index names for where these empty values are, then I could select based 116
on this criteria where these values are True from our dataframe and then access the index attribute 117
from the dataframe. 118
So "data[]" and then put the logical condition in here and then have ".index" afterwards. 119
And now we'll get the index names. 120
The row names for where this condition is True. 121
Check it out. 122
OK, so this is interesting right? 123
"cmds, cmds, cmds" and then ".DS_Store". 124
These are the index names. Now, 125
does anything strike you as odd with these index names? 126
Let's see how they compare to all the other file names. 127
So just scrolling through our dataset here, we can see that most of these emails actually have file 128
names that looks something like this. The "cmds" and ".DS_Store" as a file name does seem to be a little bit 129
odd. 130
Let's see if we can find out what these files are. 131
If I go to my spam_1 folder and I scroll all the way down then I indeed see one of these 132
files here. 133
This actually does not seem to be an email, right? 134
Taking a look at this file here in my Atom text editor I can see that it looks like this. 135
This is definitely not part of our dataset. 136
This "cmds" file is actually a system file that you'll see both on Windows and Mac when you unzip the 137
original email archive. 138
This is not an email file that we should include in our dataframe. 139
You'll also find this cmds file in the spam_2 folder and in the easy_ham_1 140
folder. Now what about this ".DS_ 141
Store" file? Now, because the file name is preceded by a full stop, by a dot, 142
this "DS_Store" file is actually a hidden file. 143
This is a Mac OS specific file that stores the attributes of a particular folder. And this system file 144
was created when I downloaded, unzipped and then rezipped the course materials. 145
In other words, this is not an email. 146
This is an artifact of me preparing the lesson resources for you. 147
Now obviously we don't want any of these four files in our dataframe. 148
We just want our email data. 149
But why are these files in our dataframe in the first place 150
if we don't want them there? 151
What, what happened? 152
Well let's take a look at our file reading function. 153
Looking at the e-mail body generator code and this walk function from the os library, we can see that 154
this function actually picks up all the files in a particular folder 155
and this includes the system files that don't belong in the email corpus. 156
That's how they got included in the dataframe. 157
Now we know the row names are index names for undesirables in our dataframe. 158
But what we don't know yet is where they are. Now, 159
let me show you how to get the location for this ".DS_Store" entry. 160
What we can do with our dataframe is get the index, 161
so "index.get_ 162
loc", get_location, loc, parentheses and then single quotes, and then ".DS_ 163
Store" in the single quotes will give us the row number where this entry lives. So we can search the entire 164
index, 165
so all the names for a particular name which is our argument in this "get_location" method here. 166
Let's take a look. 167
So here we have it, 4609 is where this entry is in our dataframe. If I say 168
"data[4609: 169
4611]" then I can pull up this section of the dataframe and take a look at which rows precede 170
and follow this ".DS_Store" entry and there we can see it just happily living between two 171
non-spam emails. In the next lesson I'll show you how to remove these system files from our dataframe. 172
I'll show you how to drop these entries and clean things up a little bit and much, much more. I'll see 173
you there.