Hi this is Jeff Heaton, welcome to Applications of Deep Neural Networks,
with Washington University. In this video we're going to look at how to use apply
and map functions together with Pandas. This allows you to write lambda
functions or other functions that will be executed across your entire data
frame. This allows you to do relatively complicated transformations to your data
frame that might be very useful for feature engineering, which allows you to
represent the data in ways that will allow your neural network to be more
predictive. For the latest on my course and projects click subscribe and
the bell next to it to be notified of every new video. Apply and map are two
functions that are provided by a Pandas data frame that you can use directly on
these. Now, we've seen map already for just general Python data structures,
lists in particular, but map can be used with data frames to do similar types of
transformations. For more advanced feature engineering that you'll probably
have to do with some of the projects in this class. Apply and map can be very,
very, useful and we'll look at a specific case in the later part about how to how
to do just that. So let's go ahead and load in the auto MPG dataset. We are
going to do a very simple mapping on it. This is one of the most common things
that you will do with maps. This is similar to a decode in SQL. What we're
going to do is, this origin value that you have here specifies the region that
the car was from. One is North America, two is Europe, and three is Asia. So what
we're going to do for this is we're going to take the origin. We're going to
create a column called "origin name" and we're going to fill it in all cases.
Where what this is what the map will do it's going to look up the origin value
and it's going to replace it with North America for 1, Europe for 2, and Asia
for 3. So let's run that and if I scroll that over a little bit
you can see North America. North America Asia for that, 3 Asia, Europe is 2
and so on. So this can be a very good way to summarize values. If you could
actually, I mean say you had the sea of numbers in here that were all the fifty
states of the United States of America you could put a bunch of values in here
that go to the same thing. You could do 1 to North America, 2 to North
America, or even state names Missouri to North America California to North
America. New York to North America and so on and so forth. Whereas if you saw
France, it would go to Europe. So you can also use this to de-summarize. Apply is
another thing that you can use with these data sets apply basically takes a
function, typically a lambda, and runs it against every single row in the dataset.
So here let's go ahead and run this. This is calculating something that I call an
efficiency, it's basically the displacement divided by the horsepower.
So how big is your engine, versus how much horsepower do you actually get and
this gives you a general indication of the efficiency of the car. So we could
add that in to the dataset. This shows how you're you're sort of engineering a
feature based on based on this ratio. Here I'll show you a more complex
example of feature engineering this was actually an assignment in previous
previous semester. You'll probably see an assignment about like this one in
this semester. This basically is using a dataset that we have here from the IRS,
which is a United States government taxing authority, and we're going to,
there's also more data documentation on here if you care to read about it, but
this is basically a dataset that gives you the estimated adjust gross income,
plus other things for ZIP codes in the United States. So, the the fields that
you care about are state, which is the state, example Missouri zip code is the
zip code of within that state, I stub are six different income
brackets so one is the lowest income six is the highest income. Each zip code is
going to have six rows for it, then they give you a count which is in one of the
number of people in each of those income brackets. So that gives you sort of a
distribution of wealth in that particular zip code. What we're going to
try to do is put those bands back together so that you have a estimate of
what the what the overall AGI is for that that particular zip code. So this is
a reduction you've got zip code you've got six rows for each zip code you want
to crunch that down to just one row per zip code and just give an overall
estimate of the adjusted income. And, this is kind of what the file looks like. See
you've got 63017, it's a zip code that I work in. AGI stub, so
that's your sixth value. Saying it for each and then the the count so the
second of the highest is the is the largest income, this is and this shows
this particular income if you do. Probably the most famous ZIP code in in
the United States of America, 90210, it probably be pretty high as
it's a very affluent zip code. So you see a lot of numbers down here. What we're
going to do to put it back together these are this is the bands for each of
these. 1 is 1 - 25k, t is 200,000 or more. Now, one of the issues with
putting this back together is 200,000 or more. That's the "or more" and that's a
pretty big category that could be up to, I don't know, probably many millions of
dollars. However, much those guys are making so what we're going to do is,
we're going to establish a median for each of these ranges, $12,500 now
this last one, since we don't know what the
ceiling is to $12,500. That's probably the biggest flaw in this estimate, that I'm
that I'm putting together. But you you can see about where those are at, so the
way that we can estimate 63017's average AGI is we're going
to basically sum all those in once so that's our total count we're going to
add those all up. We're going to total up the ages, but weighting them by the
medians, so we're going to take the count of people that, so the 4,710
times 12,500 because that's how many people were in band, one that's
the median for band one, band two there were two thousand seven hundred and
eighty and 37,500 was the median for that one. So we're essentially this is sort of
a weighted weighted sum you have here, and then we're going to divide those and
that gives us the average income for this zip code is around $88K,
which seems reasonably correct. This is good too because this gives us a
a value that we can check by hand. We usually calculated this by hand so
our automated process that's going to calculate for everything single zip code
should come up with the same value. So, we're gonna load that data set into RAM,
it was big and gigantic you'd probably want to stream it but this is this is
loading the entire data frame will be loaded in a second. That might have took
a while, that took probably twenty seconds for me. Now what we're going to
do so this is going to show you a good example of using multiple of these
techniques together. Now this dataset does have some junk data in it, so we
have zip codes that are 0 and 99999. There's no such as of codes they do have
a purpose in the data file, but we're simply not using them. So, we are going to
get all the rows that's what "loc" does for us where the data frame is not equal
to zero, zip code and the zip code is not equal to 99999 and we're also going to
only pull back these fields. So we're we're chopping this this thing down
horizontally and vertically we're getting rid of a bunch of columns and
all the rows that have these invalids. Because you run that this is actually
very quick and now the data frame has been modified if you were to display the
data frame you'll see that it has literally just these these four and the
invalid ones are also gone, but we're not going to go hunting for them what we're
now going to do is we're going to take all of these AGI stubs and replace those
with the medians. Those medians that I gave you, 1 through 6, so this is a
brilliant application of the map, we run that again it's very very quick and if
we look at this we now see that those should have transformed and they have
the AGI stubs are now those actual income medians. Next, we group it by zip
code and for each of those zip codes now we can basically perform a lambda so
this is a great use for apply we're going to do apply across it and we're
going to basically sum because we want to sum up all of the values in each of
those zip codes, but we're going to sum them weighted so we're going to
basically weight the n1 so how many of them we have say the 30m they're
multiplied by the AGI stub and then we're dividing the whole thing by in 1
which which which is the sum of the N ones actually you can see the sum there
for that entire zip code then we reset the index. So, that the so that the the
row numbers align like we've seen before we run that that does take just a few
seconds. Let's see what that actually did we print now pretty much have the data
set where we want it to be zip code and this is the average for each of those
the column headers are a little bit sloppy. Zip code that's good but 0 what's
a 0, 0 should be our average AGI our AGI estimate
we'll just rename those two columns and then we display the top and we can see
now we've got this nice output. You'd probably want to to CSV that and that
that would be a completely valid submission for that previous assignment
that I ran in last semester. We'll look up our zip code that we calculated by
hand and that matches our $88,689 that we had before. Thank you for watching this
video. In the next video we're going to continue with using pandas and talk
about feature engineering which is how you represent your data set in a way
that helps the neural network to be more predictive. This content change is often,
so subscribe to the channel to stay up to date on this course and other topics
and artificial intelligence.