Move from Excel to Python with Pandas Transcripts
Chapter: Case study walk through: Sales commissions
Lecture: Cleaning up region
0:00 the regions to find here are official regions that the U. S. Census Bureau defines which states go into it.
0:08 Which region? And if you do a search on Wikipedia, you confined it. But it's not very easy to digest into a pandas data frame
0:15 And instead of typing at all, I did a Google search and found this repository that has each state, the state code, the region and the division.
0:26 So this is going to save us a lot of time, and one of the things that pandas can do is actually read in this file.
0:33 So let's go ahead and define what that you are. L Is this a little trick with reading? See SV files from Get Hub put past raw equals True.
0:44 So that gives us the URL. Call this states just past the URL, like we would have file name and then we only want to use two columns.
1:00 Now we have the state code and the region. So we got reaching here. We've got region here and we've got the state.
1:07 So now let's see what we would need to do to join this together before you do any joins like this one of the things I always like to dio is use
1:17 our value counts. So we look at the states and we can see how many regions there are and how many states are in each region.
1:26 Let's do the same thing for our sales rep, so that looks pretty good. But then you'll notice something. So if you pay close attention,
1:37 Northeast here is not spelled the same way as Northeast in the States file. So we're gonna need to clean that up.
1:45 There's a couple different ways we can do it. But what I'm gonna do is I'm gonna convert all of the regions to upper case
1:51 So remember we used our excess er's. So I've converted each one of those. So let's rerun our value counts and see what it looks like.
2:04 All right, that looks good. Now our value counts are showing that everything's capital, so we can probably do a joint on that before we do the joint.
2:15 I realize Ever got to do something? I forgot toe. Actually. Take a look at the info just to double check
2:27 and make sure everything's coming through is expected. So stay code and region or objects. Total sales manager. Everything else customers is an object.
2:36 And 10 years afloat. So this looks good. So now we should have our data where we can merge everything together and we'll walk
2:45 through that in just a second.