Hello everyone and welcome to the solutions lecture for the e-commerce purchases.
Pandas exercise.
Let's go ahead and jump to Jupiter notebook and start walking through these solutions.
All right.
Here I am at the notebook for the e-commerce purchase exercise.
Let's go ahead and just walk through the instructions.
So first thing you had to do was import pandas good import pay.
This is the end of what we want to do is read in the e-commerce purchases CSP file and send it to a
data friend called e-comm.
So when we say e-com is equal to CD read underscore CXVII.
And then when we start typing e-com in I'm going to use tab autocomplete to find the actual file here
and it's the first one there.
E-commerce purchases.
And I'm going to go ahead and check the head of that data frame and you can do that just by calling
the method e-com head.
And there we can see we have the head of that data frame.
Now we want to know how many rows and columns there are there are a couple of ways to do this.
For instance you could just say e-com columns and this returns a list of the columns indexed so you
could actually just checked the length of that if you wanted to and still return how many columns you
have.
And then you could do the same thing for index and that would tell you how many rows you had a nice
way to do this however to get both pieces of information at once plus a couple other nice pieces of
information it's called the info method off of that and hopefully by looking at the output that was
a nice hints to use the info method.
OK.
What does the average purchase price.
Let's go ahead and check out the columns one more time.
Notice here we have address slots.
AM or PM and continuing on we notice that we have finally at the end a purchase price.
We're going to go out and do is call that column purchase price and I'm going to go and do have autocomplete
here and then I want the average purchase price.
So average in this case we're talking about mean and the reason I didn't actually post what is the mean
purchase price because I don't want to actually give away the method right off the bat.
So hopefully you're able to figure that out and the average perched price is $50 at about 35 cents.
Now we want to know it's the highest and lowest purchase prices what we can do that in a very similar
manner by just passing in the column and then calling max and min off of it.
So the max purchase price is about 100 bucks and the minimum purchase price is $0.
Okay.
Now we want to know how many people have English or and as their language of choice on the Web site
.
Let's go ahead and check out the English column or the language column one more time ago and it's a
e-comm.
Go ahead.
I'm going to call the first three columns here.
Rose excuse me.
So notice if I go ahead and scroll to the right.
I have here at the language column it looks like it has just two length strings.
So we're looking for how many of these strings are equal to E and that means I'm going to do this.
I'm going to say e-com language equals equals the string e m.
This will return a series of boolean values.
Falses in Trews.
And that means what I can do now is go ahead and say e-com
of where this is true.
So I can say e-comm and now I have basically a subset of the data frame and I can check that by looking
at this index here.
And notice that it kind of bounces around.
And if I scroll all the way to the right on the state of frame let's go and just call the head of this
because for all that scroll to the right notice here all the languages are E-M.
So what I want is not the head of this since I want to know how many people have English.
I can just say count.
And this will count the rows.
And so I have one thousand nine hundred eight people that have English as their language of choice in
the web site.
And if you wanted this just to be specific for the language column you could just call language here
and this would actually give you the direct number.
All right.
Let's go ahead and keep on going.
Now we want to know how many people have the job title of lawyer.
We're going to go ahead and do is say e-com where job is equal to lawyer and then pass that in to the
e-com data frame and this returns the subset of the data frame where this is true in others a variety
of methods actually get the rose out of it.
You could just get info.
And this actually returns how many rows you have so 30 entries.
You can also just do counts that does the same thing 30.
You can also call index off of this that returns the index and then you can call the link for that.
And this also returns 30.
So quite a few methods to get the sexual answer 30 go ahead and feel free to use whatever method you
prefer.
OK.
Coming up next how many people made the purchase during the am and how many people made the purchase
during the p.m..
And there's a hint here that use value counts.
Well value counts makes this really easy.
You actually just pass in the AM or PM column and then say value.
Well let me just show you the unique values first.
So if we take a look at this column and just put unique notice we only have PM and am has values and
that means I can just say value underscore counts
and this will automatically counts.
How many people purchased during the p.m. and how many people purchased during the AM.
Coming up next we want to say what are the five most common job titles.
Well that counts actually also makes that very easy which you can end up doing is called The Job column
and you noticed you get a bunch of jobs back as a series and then you can call value counts on this
column
and then you actually have the counts and notice value counts actually automatically already sorts from
the most popular accounts all the way if you go to the bottom to the least popular ones.
So if we just check out the head of this for the top five do you get the five most common job titles
interior and special designer lawyer purchasing manager etc..
There's a couple of other ways to do this but this is probably the fastest and easiest way to do this
.
OK.
Next up someone made a purchase that came from Lotts quote 90 space W.T..
What was the purchase price for this transaction.
Let's go ahead and try to find out this lot first.
So we're going to kind of break this down step by step.
We're going to say e-com of lot equal to and then 90.
Space capital W T.
I just run this like a series of boolean values and I want the row where this is true.
So I'm going to go in and say e-com of this is this conditional selection.
And now I can see I get the Roback where this happened to be true.
So if I go ahead and just go over to the purchase price I should see it $75 and 10 cents.
Or I could just call purchase price off of this data frame and I'll return this $75 and 10 cents.
OK.
Coming up next what does e-mail the person with the following credit card number.
Here's credit card number itself.
Well we're going to want to do is a very similar process will say we want e-com credit card is equal
to and then I'm just going to copy and paste this number and then I'm going to pass this into the e-com
data frame and just grab the email column off of it.
And I just ran that and it gets to me the exact same answer.
So Bob Dylan at Williams Asgar said that.
OK now I want to know how many will have American Express as their credit card provider and made a purchase
above $95.
Again we're just doing conditional selection.
Except now we have to remember how to do multiple conditions when it go ahead and start sitting at my
data frame.
I'll say e-comm and I'm going to pass and the first condition in parentheses and then use ampersand
and pass the second condition in parentheses.
OK first condition was American Express as their credit card provider meaning.
E-com CC provider is equal to American Express and I want e-com of the purchase price
to be greater than $95.
Okay.
If I just run this I get back the actual rows where this is true.
Now I remember.
I just want the count so I can go ahead and just say that counts off of this run this and I get 39.
Again theres lots of different ways you can get the count.
You could also say info then that returns third 9 here.
You can also use for instance stock index and then get the length of this
and that also returns Thirty-Nine So lots of ways to do this.
But the basic idea of what I want to get down is that actually did the multiple condition selections
.
You need to pass an imprint CS ease and and then pass it and print CS again.
OK.
Now for the last two hard ones.
How many people have a credit card that expires in 2025.
So let's go ahead and take a look at the expiration date.
So we have CC Expwy expiration date.
We take a look at this.
We have what looks to be a string.
Well let's call in and confirm that it's a string by just asking for the very first object.
So if I say I EHLO see the bracket zero error turns back the string 0 to slash 20.
And that means if I actually just want to grab the year so this credit card expires in 2020 what I'm
going to go ahead and do is grab the values of that string.
Are the elements the string that are index 3 and beyond.
So if you look at 0 1 2 3 and beyond or essentially the last two.
And now I get a string that is the year we're going to go ahead and take that information and begin
to use apply on it.
So I want to say the Apply lambda will go ahead and call this XP Colan XP and then say three colon and
then I want this to be equal to 25 because I'm looking for the expiration date of 20 2025.
Let's go ahead and just run this.
I noticed now I have a series of false and true so a bunch of boolean values.
Now the reason why this is hard is because you need to figure out how many of these are actually true
.
There's a couple of different ways you can do this.
One very quick way is you can actually just take the sum of the series and it'll return back all the
values that are true as a sum.
So that's the way it's done and the solutions.
Kind of a neat trick but that makes it hard.
The other we can do this is by passing this into e-com getting the rows back where it happens to be
true.
And then just saying Got count and this will also return back that same number 1033.
OK.
Now for the hardest one which is what are the top five most popular email providers and hosts gmail
dot com yahoo dot com et cetera.
Let's go ahead and see how we can do this.
Let's take a look at the e-mail column
and notice here we basically have strings.
But what's important to know is that if you take one of these strings Let's go ahead and take one we'll
call this example underscore.
E-mail is equal to the very first instance of this.
If I take a look at the example e-mail is just a string here.
If I go ahead and say splits on at that I have a list that it splits it out at and I know that the second
item in this list item one or two out of index One is the domain name Yahoo dot com.
So what I want to do is use the sort of logic in lambda expression in order to grab the email provider
or the host Go ahead and do that.
I'm going to go in and say e-com e-mail or apply Lamda we take in the email and then we say email that
splits at.
And then I'm going to grab off of that.
The value at index 1.
So basically I'm just using this logic right here but I'm putting it into a lambda expression that I'm
going to say value underscore counts in order to actually count how many there are.
Let's go and just show this for the hash tag that out.
So this returns a series of all these values as far as the email providers.
And what I want to do is figure out the top five most popular.
So remember we can just value accounts along with AD 5.
And this returns the top five most popular.
So Hotmail Yahoo Gmail Smith and Wiliams dot com.
OK.
Hopefully that was challenging for you if it was.
And you breezed right through it even better.
But I hope you were able to use your pay and the skills start getting familiar.
And more importantly see what's actually available for you and the type of questions you can answer
quickly and easily of pandas.
This is your first exercise as far as a formal exercise of panderers.
It might have been challenging and might have been really tough but just go through solutions slowly
.
Make sure you can break it down step by step and understand the solutions.
Feel free to post any questions you have to Q&A forums and I'm happy to help you out as far as explaining
the logic of any of this what might be the most confusing parts of this are the ability to use apply
and then lambda expressions go ahead and review land expressions in Python.
If you thought these last two were just extremely hard.
OK.
Hope you enjoy that exercise.
Coming up next is another pair of those exercise to sharpen your skill set even more.
Thanks everyone and I'll see you at the next lecture