-: All right, so when we ended the last video
I talked about data cleaning.
And your data is an absolute mess,
and it is, you need to clean it up.
And cleaning, data cleaning comes by many different terms.
Data hygiene, data scrubbing, all kinds of things.
Listen, like I said before,
I'm gonna say it's slightly differently,
your data's a dumpster fire.
It always is a mess, okay, to varying degrees,
but we gotta clean it up.
And what does that mean?
What are these data cleaning things we're gonna do?
Well, as it says here
it's steps to fix incorrect, inconsistent
and/or incomplete data.
So what are those steps?
Well, we gotta start off by confirming the authenticity
and/or the quality of the data source.
Now I got this data that we're looking at,
the IMDB dataset from Kaggle.
I have not confirmed the authenticity
or the quality of that dataset.
Now we're just using it as an example dataset
so we can get away with it, doesn't really matter
if it's accurate or not, but boy,
if I was gonna publish something based on that dataset
I would do a much more careful job validating it.
I also always, always, always
will start by making a backup copy of that dataset
before I do anything 'cause in data cleaning,
I'm gonna be messing with it a lot.
Deleting stuff, changing stuff potentially,
merging, tweaking.
I gotta have the original pure version
in case I need to revert back to it.
So always seriously, trust me, make a backup copy.
It can be as simple as, take your spreadsheet
if you're working in Excel, duplicate the worksheet,
call the original one, pure, original, you know,
untouched data, call the other one working data
and never look at the pure original one ever again.
Only do your analysis using the working data.
Okay, we also, often in data cleaning,
can just throw out extraneous data.
Maybe we have a column that we're not gonna use.
In the IMDB dataset there's that descriptive column,
the overview column.
I'm not gonna use that, I'm not doing any text analysis.
I could throw that out if I wanted to.
So always look to do that as part of your process.
Why have it in your face to deal with it?
We also want to check for things
like standardized syntax and format consistencies.
Things like capitalized cities or zips,
zip codes with unallowed values,
like in the U.S. it's either five digit
or nine digit number, end of discussion.
If you see letters in there,
if you see something other number
like a seven digit zip code, that's bad.
You gotta figure out what you're gonna do about it.
Brings up an important point.
Data cleaning is two parts.
You gotta notice the issue like a seven digit zip code.
Okay, I see that, now we're gonna do about it?
What are you gonna do about it is gonna vary.
You may delete the data, it's just bad, right?
You may change it, you may go research
and look it up and replace it with the right value, right?
Or if it's a numeric thing
maybe you can calculate some fair replacement value
like an average of some kind
versus whatever number you see in there.
Or maybe it's missing a number
et cetera, et cetera, et cetera.
So it depends in a huge way what you do.
But first you gotta notice it
and then you can figure out what to do about it.
You can also look for outliers in your dataset.
Maybe I have in this top 1000 movies
one movie that has a meta score of three
and all the other ones are like seven or above.
Well, that's weird, that's an outlier.
Is that A, a mistake, maybe I just delete it,
B, does that mean like every other movie
aside from the top 1000 has meta scores below three?
Like you're gonna look into it
before you decide what to do about it.
You can also check for completeness
like missing fields, missing values,
and again, figure out what to do about it
after you first notice the problem.
So when we are data cleaning
one of the very first techniques we can use
to do it is to start with sorting and filtering.
Okay, sort every column you have,
you're looking for weird things, right?
Sort from largest to smallest
and smallest to largest and every numeric field
and you may notice, oh there's a bunch of zeros
in that field, but most of the numbers
are supposed to be like a thousand or above.
That's weird, right?
Or a bunch of blanks or a bunch of really high numbers
that don't seem right, et cetera, et cetera.
Sorting, as simple as it is
is a great way to start and definitely filtering.
You can filter, use the filter button.
In fact, one of the first things I do
in any dataset is I enable filtering in Excel
in order to be able to see what things I can turn on
and off, filter the data down
and get a sense of that segmentation, get a sense of,
by the way, how many unique values are in a column.
If I have a column a thousand rows deep
I don't know what the unique values are,
but by enabling filtering
and popping open that little window,
I can see how many options there are in there.
And we'll look at that a little bit later on.
So we start off, we make a backup copy of our data,
then we can actually start to look into it.
And by the way, as we are doing our data cleaning
you want to document every single decision,
every single thing you do.
I deleted this column, you're gonna write that down.
Add a notes tab to your spreadsheet that says,
"Step one deleted column."
You know, because I'm not using those descriptors.
I imputed, I replaced the value for, you know,
the year released, you know, for "Shawshank Redemption"
because it was wrong, things like that.
Write it down, because you need to remember what you did,
you may need to repeat what you did,
and you're gonna need to explain
and defend what you did to other people down the the road.
This is a great resource from Quartz,
a good journalistic publication
about what to do with bad data.
Definitely check it out for more information about this.
Now thinking about the IMDB data, let's ask ourselves
what do we notice, 'cause remember step one, notice it.
Then step two, we'll figure out what to do about it.
So what do we notice
in the IMDB dataset that maybe needs some tweaking?
And we gotta do this with our data literacy hats on okay.
So let's look at the dataset.
Series Title, I probably want the title
even though I may not analyze the title
I'm probably gonna want those titles to look at
'cause I know some of these movies
so I'm gonna hold onto that one.
Released year, now, (claps hands)
Released Year, that seems like a column
I'm gonna want to keep.
But outta curiosity, I said maybe we should sort
let me sort, smallest to, well,
so the oldest movie in this dataset is 1920.
That's interesting, I didn't know that until I sorted,
what's the most recent one?
Oh, PG, what?
That's, that doesn't make any sense.
I already have a data cleaning task, right?
So I need to fix that or do something about that,
we'll see about that.
Runtime, that's good to have.
It's kind of a weird one, data literacy hat.
It's numbers and text.
140 space M-I-N.
Maybe I'm gonna want to do something about that
so that I have numbers in there
so I can do math with those numbers.
So right now I'm just identifying issues,
I'm not gonna fix them and we'll fix those later.
Genre, like I mentioned before, we have three genres
or sometimes it's not three
sometimes it's one or two, separated by columns.
So what if I want to figure out which genres are the best,
have the highest scores, can I do that
if they're all in one column?
There's probably a way to do that.
But I think I'm gonna want to do something else with this,
we'll talk about that.
IMDB Rating, numeric, it's all good.
Let me just sort that one outta curiosity.
I didn't sort the Runtime
but I probably would under normal circumstances.
Sorting, whoops, decreasing.
Yeah, so at least those are all numbers.
Overview, like I said, maybe I'll delete that.
We'll figure it out.
Meta score, so if I sort the Meta score
from smallest to largest, okay, there's a 28,
and how about the largest is a hundred,
but by the way, I didn't enable filtering yet.
Let me actually enable filtering, which brings me this.
And if I pop that open, look what happens.
I can see how many different meta scores there are.
And by the way, what do I notice?
Ooh, blanks, interesting.
So let me filter.
Let me unselect all and let me just select blanks.
I wanna know how many blanks I have.
And so now by doing that, what I can see is
Let's see how many rows I have that are blank.
So if I shift, select down, good to know Excel shortcuts,
I select all the rows that are now not filtered out
and I can see the count right down here, 157 rows.
I have 157 values or rows of data in this dataset
that don't have that meta score.
So how are they the top 1000, maybe the top 1000
is based on the IMDB rating I guess, I don't know.
I'll look into that maybe later on.
I also by the way, data cleaning, may notice that some
of these movie titles
have these weird characters showing up.
This is probably just a character translation issue.
I'm not gonna worry about that today in a normal dataset.
I would definitely be concerned about that.
I have my different actors, directors in different columns.
I'm fine with that for now,
although you'll see later I do something about that.
Number of Votes.
I noticed that if I sort, I have 25,000 votes on the low end
and I have 300,000 something votes on the high end, okay?
I'm not sure what to make about that.
I also noticed by the way, under Gross,
that we have a lot of movies
where I don't have the gross revenues.
Okay, so a quick scan through this data
I learned a couple things.
I certainly noticed a couple of issues.
Long story short,
we're gonna work on cleaning it up together,
which is what we're gonna do next.