Preview
Video Player
00:00
00:00
Cleaning A CSV Part 3
10:50Removing formatting errors using Pandas including replacing dashes with commas and correcting spelling mistakes.
Hello again,
let's tackle formatting errors.
0:00
The first error we'll tackle
are the dashes instead of commas.
0:03
Luckily, this is only in one column,
the Weaknesses column and
0:08
we're going to use replace
combined with apply.
0:11
Just to remember I'm gonna scroll up and
what we've worked with so far.
0:14
And you can see that our
Weaknesses are separated, and
0:19
types are separated by comma
space before the next word.
0:22
And there are some that
have dashes instead.
0:26
So that's what we're gonna find,
and we're going to fix.
0:28
So I know from reviewing the data, and
0:32
just to save us a little bit of
time that 104 is an example.
0:35
So here we have a space dash space
in between our words.
0:41
And we wanna make it like it is here for
type we wanna have word space word.
0:46
Right now it's word space dash space word.
0:51
So we're gonna fix that formatting here.
0:55
So it looks like this.
0:57
We need to change our
Weaknesses column to equal
0:58
what we're going to
change it to Weaknesses.
1:04
And we're going to use apply to apply
a function to every row in this column.
1:10
And we're gonna use a lambda function do
you remember how to spell it in my head?
1:18
Always do lambda to
remember how to spell it.
1:22
Then we're gonna have x,
which will be the row or the item.
1:26
So it'll be item in the column.
1:33
And we need to check for
that item that cell if it has the dashes
1:36
we can use replace to then
replace it with commas.
1:42
So we'll need to replace
1:47
space dash with a comma.
1:52
So just remember we have a space dash
and an extra space here
1:57
in between the words and
we just want a space comma,
2:02
so we need to replace this
space dash with a single comma.
2:07
So that's what this is going to do for us.
2:12
So let's go ahead and run it.
2:15
And I'm just gonna copy this and paste it
down here so we can see if it fixed it and
2:17
perfect we now have ghost,
space fire and then, space flying etc.
2:24
Awesome, next,
let's tackle spelling errors.
2:29
I'm going to show you a way that
doesn't take installing anything else.
2:33
There are some spell checker Python
packages you can download and use and
2:38
you can check the teacher's notes for
a few examples.
2:43
I'm going to use what's available to
us right now with pandas, just so
2:46
that you don't have to worry
about installing anything else.
2:50
So, what we're going to do, is we're
going to find all of the unique words in
2:53
the type column and
then we'll do the Weaknesses column.
2:58
So that we can find any spelling errors
and then go through and fix them.
3:01
So I'm gonna call this
variable unique_types.
3:06
We're gonna turn it into a list just to
make it a little bit easier to look at.
3:11
And we're gonna have data.
3:16
Oops, there we go type and
we're going to check for
3:17
string.split on the comma, space,
3:23
because it's the same for
both Weaknesses and types.
3:28
If there's more than one in here,
there's gonna be this comma,
3:34
space in between the words.
3:37
So we wanna break out each individual
words we can find any spelling mistakes
3:38
because it could be the first word.
3:42
It could be the second word,
or the third or the fourth,
3:44
etc. So we're gonna split them
up into individual words.
3:47
Then we're going to do expand = true
3:50
to expand those out. We're going
3:56
to do .stack to stack them all.
4:00
And then we're going to do unique
to just get the unique words.
4:05
Otherwise if there's let's say
50 Pokemon with type grass,
4:08
it's gonna list out grass 50 times.
4:12
And we only want the unique word so
that way it'll only show grass one time.
4:14
Awesome then I'm gonna do unique_types so
4:20
that it actually lists them out as output.
4:24
And here we go.
4:30
Let's see what we got.
4:31
So if we scroll through,
you can see, I see this word and
4:34
we already had poison up here.
4:37
So we have Posion.
4:39
So I'm just gonna copy that and
I'm gonna put that as a should equal and
4:42
I'm gonna put the correct
spelling next to it.
4:47
Just to make it a little easier
on us moving forward and
4:51
see if there any others we got Posion.
4:55
Definitely spelling mistake and
4:58
we have Fie and
5:01
this one I think we can just type in,
supposed to be Fire.
5:07
And that's it.
5:11
So that's it for the type column.
5:12
So now we have found
our spelling mistakes.
5:13
We need to go through and find which
rows have them, and then fix them.
5:16
So let's do data where data
5:22
type.string.contains.
5:27
So we're gonna check to
see if it contains and
5:33
we're gonna do the incorrect
spelling Posion.
5:36
And we're gonna make sure it
knows that regex is false.
5:39
This is just a string, it isn't a regex
pattern that it's using to check.
5:42
So it's gonna check every row.
5:47
It's gonna spit out the ones
that contain Posion for us.
5:49
So I'm gonna hit Enter, and
we can see we've got four. Arbok,
5:55
Nidorina, Nidoqueen and Nidoran.
6:00
So Posion, so
we need to go through and fix this.
6:03
To save a little bit of time,
I'm gonna have y'all pause me and
6:06
try doing it on your own.
6:09
We did do this, if I scroll up,
6:10
we did do this exact same thing up here
when we were doing our missing data.
6:13
So we have plenty of examples before
of doing this exact same type
6:18
of fix that we're gonna do here
to fix the spelling errors.
6:23
So go ahead and give it a try on your own.
6:27
Scroll back up in your notebook
file if you need a example.
6:29
And see me back here in a bit.
6:34
Awesome I'll show you
real quick what I did.
6:38
I found first one Arbok by
doing data dot loc 24 and
6:42
I change the type to Poison and
I did that exact same thing with each one.
6:46
And then I called our string
contains one more time just to
6:53
make sure we got all the Poisons and
it's empty.
6:58
Now the other error,
that I had was this Fire 1.
7:02
So let's do that.
7:07
Now because we know this one's empty,
we can actually just update this one.
7:08
So we need Fie.
7:12
Let's run that and it looks like we
have only 1, can do that one together,
7:13
Data.loc 78.
7:20
And then we need to find type =
make sure it's a string Fire and
7:30
then I'm gonna run this one one more
time just to make sure it's empty.
7:36
And perfect, now we're gonna do the same
thing on the Weaknesses column.
7:43
So I'm gonna scroll back up to
what we did our unique_types and
7:48
we're gonna do this exact same thing
except on the Weaknesses column.
7:52
So let me scroll down, paste and
7:57
then instead of types I'm just
gonna shorten it to weak.
8:00
This needs to be changed to
Weaknesses scroll, make sure yep.
8:07
Everything else is the same.
8:13
Perfect, now let's go through.
8:17
All right here's one
right off the bat Ie just
8:19
like we did before Ie should equal Ice.
8:24
See what else we find.
8:30
We have a Gras instead of
Grass should equal Grass.
8:35
We have an Ic should equal Ice.
8:42
We have another Poison should equal Poison.
8:50
And we have a flying
missing the I should equal
8:59
Flying All right, so
9:03
same thing I want you to try and
do this one on your own.
9:08
Pause me, and let's see how you do.
9:12
All right, let's see how you did.
9:16
So I copied and pasted our same
string contains that we used earlier,
9:18
I just scrolled up and grabbed it.
9:24
Changed it to Weaknesses and I went for
our first spelling mistake which was Ie.
9:27
So Ie, and it found these two rows.
9:33
Fix those two rows.
9:37
Don't forget to make sure you do the whole
thing and not just change out Ie to Ice.
9:38
Then I went and I did Gras.
9:45
Now Gras is going to pull up
every single instance because
9:47
Grass right here still contains Gras.
9:52
So this could be a really great one
to test out your regex skills or
9:55
regex whichever way you say it.
9:59
So I highly encourage you
to try doing it that way.
10:01
But this is also totally fine way you just
pick out which ones have the mistake and
10:03
then scroll down and fix it at the bottom.
10:09
Same thing for Ic,
it's going to do Ice as well as Ic
10:12
because Ice contains Ic
another great one to try and
10:16
use regex instead of just trying
to do the string contains.
10:21
Then we did the Poison one or
the Posion on one and change it to Poison.
10:31
We got four there for
10:36
fixes, then we did flying only
one with flying and fix that one.
10:37
And that's all of our formatting issues.
10:42
Spelling mistakes can be tough,
but you tackled them.
10:45
Awesome job.
10:48