Welcome everybody. This is Mr. Fugu and you're watching Mr. Fugu Data Science.
Today, we're exploring and cleaning some online sales data: using r studio. This
will be a multi-part series for a project that you can create. The code
will be provided with a link in the description below. The outcome today
we'll be cleaning the data, doing feature extraction, look for
duplicate rows and remove them. Look for NA values remove those. Also, show what rows
have any NA values. Do a technique similar to pythons value count. Look for unique
values from specific columns, and inspect our data. Remember, as always please
subscribe and turn on that notification bell. And throw me a like. If you don't
like the video throw me a comment tell me why before you dislike. You're gonna
need knitter, tidy verse and I don't think we're gonna need tidyr today.
This data set will be provided in the description below, as well as the Jupyter
notebook. We have five hundred and
forty-one thousand rows with eight columns. Which are your invoice number,
the stock number, a description of the item, the quantity sold, the date8 sold
the price per item. the unique customer ID, as well as the country that made the
purchase. If you're looking to find duplicate values: you just say duplicated
and then parentheses, and whatever you're calling. Here I wanted to find the
number of duplicated rows. It turns out that we have fifty to sixty eight (5268). Now
how does that affect the total data set? Well, it turns out live it's minuscule
and it's less than one percent. We retained
a little over 99% of our original data after we got rid of the duplicated rows.
Okay, that's fine. Now, what does it look like if we
actually print that out? So you say let's call our data frame use the square
brackets, then we say this take all the rows let our duplicated, and then find
all columns, okay. That's what's going on after this comma. So you print that out
and this is what it looks like. Okay, well now I say here's what our
duplicated rows look like. That's fine and dandy, we could you know use that we
could just do it for no reason who cares. But, you can also do a logical not
if you would like to get rid of duplicate rows and only show
everything else. and that's what's going on here.
So I say let's take our online retail data frame. Let's use our brackets, let's
called the logical not operator, for the duplicated rows and return all
the columns. so here we are: this is everything except the duplicated values.
This is what we want. Okay, cool so we've got two ways of doing this then huh? Yep,
we have another one we're removing duplicates. But, we're using the (dplyr)
method. So you're calling your data, then you're using the percent greater
sign percent, and you're calling distinct here.
So you're basically finding all the distinct or unique values in your data.
and that's what's going on here and you're getting rid of it. So now you have
two different ways to do it. now what is the proportion of data that
actually has NA values? Well, if you're trying to find an n/a value you say is
dot n/a. And then parenthesis and whatever your data is you're calling
here. It turns out in our case 25 percent of our data that we just made which does
not have duplicates. Was occupied by any values. Well then what about if we wanted
to look and find all all the places let this occurred with the columns that it
occurred. Well you can use an apply method which is awesome because there's
a family of apply functions. And they're really unique: because, it helps you avoid
using iteration in R. So you say let's use our apply function. Where we're
finding all of our NA values here and we're using two because we're looking at
the columns. Then if we used one its row wise. And then we use which; so saying
which places have our NA values. and then if you were looking at a new function
and you never used it before, or you just couldn't remember what are all of the
features. You just do a question mark and then the function name. and then it
prints out here. Apply function over array margins. and then it tells you how
you set it up, explains what everything is in detail, and gives you some examples.
and this is really useful because they'll help you you know on the fly
figure things out, and you may not have to go to stack overflow immediately. You
might be able to just figure it out initially. Then we could use something
with similar tool union. Where we're taking our data right here. and we're
using our (dplyr) type of notation. But, now we're using something new.
So we're saying that's filter all, and let's take any variable that has an n/a
value. and this right here is just a predicate, you know it's a that dot is
called a predicate. and so you're just saying let's take this verb in let's
find a predicate. Now, this is printing out all
the rows that have n/a values. and you say oh well this description would be
useless there's nothing here. and this column well let's just get rid of this
ID, customer ID column. Well, there's two things going on here: one if you're
looking at the behavior of customers, individual customers you need to take
this into account. Therefore, you cannot use any rows do not have a customer ID.
But, if you are looking at the macro version: and only the behavior of country
data. and if all of your entries have a country name, but it didn't have a
customer who cares. Because, then you could look directly at countrywide
behavior of purchasing. and do a comparison of those. Now, how do you get
rid of this n/a value? Well, we can do an NA dot omit. and that removes all of our
NA values. Well that's pretty simple. Well how much of our data is actually, our
subset data is actually taken care of now? Because it seems like it's
pretty big amount of our data. It looks like it's 25% of our original data. All
right but, we're not using our original data here. We're using the subset data
without the duplicates. Oh it's still 25% okay, so that's still pretty high. Now how
do we want to do with that? Well it depends on what you're doing for your
analysis. Now what if you're looking for unique values? Well you just run the
unique, and then your column name. For instance here we're just looking at the
unique country names. But, if you were looking now at finding like a value
count similar to the Python method. You would do what's called table, and then
your column name you looking for. Here we're finding this specific customer, made
two purchases over the year timespan that we have for the data. This customer
made a hundred and eighty two unique purchases. Okay so that's really
useful. then we could find how many; we could subset our data and
find out basically how many unique values for each one of these columns.
so I said let find the customers, the invoices, amount of transactions,
and the unique products. and we notice that we have a little over four thousand
unique customers; and of those customers they made twenty two thousand one
hundred and ninety transactions, and of those transactions there were unique
products in the amount of 3684. Okay cool. Well then let's do some sorting and how
do you do sorting? Lets sort our data and call it with order, which will do it in
ascending order here. If you notice that by the country. and then we're doing it
by description also. so we're ordering by two different columns here, and printing
it off. so first we have it by country then ordering by description of item.
Okay fine. well then sometimes when you're doing
analysis of your data there's things that you don't pay attention to, and can
affect you later on down the road. This happened to me when I did this project a
few years ago, and I didn't catch this. If you notice here there's a C. And that C
is talking about a cancellation of an a transaction. So then you say
here's my original invoice number. Which at this time. With a quantity of one of
these things purchased. But, then you no more than an hour later you said: oh no I
don't want this anymore. And you cancel that off and that will mess up your
analysis. So sometimes you have to further investigate your data to see if
there's anything going on. And so then I say well then how do I deal with these
canceled values? Well one way you can deal with it is using: grep. I could say
grep, lets find everything that starts with the C or has it seen it, in the
invoice column. And let's print that out as a data frame and here we go. We're
printing that out as a data frame. But, how much of our data was actually
occupied by this? It turns out that we have you know almost 16 and a half
percent of our data, our subset data occupied by canceled orders.
Well that causes a problem because now you have to track down this order that
was cancelled and the original purchase order. And basically get rid of those
rows, because you can't use that data.
Then you have another issue: if you were to look at, let me let me make sure I
verify this correctly. Okay let me just make sure so 50 for one for only one
three. Okay so if you're looking at this you'd say well if I made the table and I
look just at the invoice number for this specific invoice, for the specific
purchase or transaction. What comes out? I say oh well 28 equals true.
what does that 28 mean? Well that doesn't mean the quantity of 28 things purchased.
That means that we have 28 individual rows.
Where there were different items that were purchased, in different quantities.
so you got to pay attention to that and there are 27 unique items. Because one of
these was purchased: two times back to back, in the same order. They just increased
the quantities. I can't remember which one. But, you had to pay attention to that.
Now you also have this. so let's say we sort by the customer ID number, okay. We
sort by the customer ID, which is fine and what was this person's customer ID?
13859. So I'd have to go in here we have to extract it out. Okay, so if you're
looking in here you could have some you could have some issues too. If you're not
paying attention. okay here is where we have our hiccups
but we have to adjust for later. and you have to get a little creative on how you
set this up. Now, we're not going to do that in this
circumstance. But, I'd like to make you aware of that. We will be moving on to
further analysis in the coming videos. I think let the next video I will
do will be another database video. Since I'm getting a lot of replies to MongoDB
using R and Python. So I may do a few more videos of that. and I'll come back
to this in a few days. But, by the end of this project what we plan on doing is
doing some PCA, using a few statistical techniques, and diving further into this
data and figure out what's really going on. So we get some
clear insight and do some plotting. But, as always I like to say thank you for
watching this video. I hope it brought somebody some utility. Please
subscribe, and like the video. Again if you don't like the video: leave me a
comment before you throw a thumbs down. have a good one
see you in the next video, Ciao