Welcome everybody you're watching Mr. Fugu Data Science. Today, we will use
Python and Postgres featuring the extract function of Postgres and
Seaborn for plotting. As always the code will be linked in the description below.
The outcome and purpose today, will be to create a table with the extract function.
Where we're taking a date column and extracting the month and year and we're
going to get the quantities of items that were sold by month and year. Then
we're going to use a bar chart from Seaborn and create a nested bar chart.
Now, I will not go over the creation of the init and config files today. If you
like to see those you should look at the description link above: little pop-up. If
you are not worried about hiding any of your credentials or do not have any
credentials. I suggest using this generic form, and trying whatever
database you're logging in to and then your username, okay. So we need to do some
imports. We need to import psy cop G two (psycopg2), pandas, calendar, matplotlib, Seaborn.
Here, I am calling in my config file with the specific function that I'm using for
my parameters. Where my config file stores my parameters as key value pairs.
But, for you you could just do above if you uninterested in this. Now we're
making our connection, and then we're creating a cursor. This cursor is
allowing us to interface and send data or run queries with our database. I'm
using auto commit equals true here: because, I want every every operation
that I do to happen now. We have two data sets that are used
which are our user information, such as credit card ,email, first, and last name,
primary phone number. The second will be what they purchased on that
credit card and the data was purchased. In our last video which will be also
described above; will dictate how we did a join between the credit cards to
create the file we will use today. Now, we will look real quick at what a basic
query looks like of our data. That was joined. I printed out two entries right
here. It's the credit card, email, first name, last name, phone what they
purchased as a CPU, and what date. Let's get into running a query on these
data. It's kind of big so bear with me and let's try to pay attention. We
need to select, and then say extract or year from the purchase date column, and
then alias as year. Ok, well that's perfect. Now you just can copy or type it
again and just say do this for a month
alias is month. Then we'll hit a comma and say count everything from what
table? We're doing the staging fake CPU purchases and we need to group by or a
year and the month.
Then you do the simple cursor dot execute as always, and instead of
printing out a query. We're going to create a data frame. So we're going to
say sales by month and year and then I append a data frame. And then here you're
going to call cursor dot fetch all. So we can take all of our entries and print
them out or basically here store them into the data frame. Create your column labels
which would be year, month, and the quantity of items sold. Let's see
what that looks like. Sales by month, year
Okay, so you have by year; which is the first thing and then month and
quantity. But, let's do some ordering. But how do we do that? Well we're gonna redo
this. We're going to override what we already did and say alright; take this
and then sort values. And let's do it by the the month first followed by
the year and let me see what that looks like.
Okay, so we first sorted by the year and then by the month. And here you notice
that didn't really matter. Okay, I did this because later when you plot you'll
notice that there's a mistake and it will just go by the order of entry rows
for the month. But, then now let's convert this digit into an actual name or an
abbreviation of the month. That's where this calendar import comes
into play. What I need to do is convert everything into an int. Because,
if you notice here: when I took everything from that query, I see this
this double precision junk. And I'm just like why am I getting floats? You know.
And that's because of the wavelet it works inside of the extract and I'm
gonna go over that, I sent a link in the bottom of the citations. Where you can
kind of get an idea of some of these answer on Stack Overflow, why this
occurred. But, anyway let's take our our sales data and let's call it something
else. That's pretty much similar, it's called a month year capitals and then
this take that data. Then let's do as type. And then change it to int. Okay, so
then now, I have to do a conversion of this int to the actual month name. We
can look at what this looks like real quick, if you want. To make sure it
actually worked. It worked. Okay, so we need
to create a new list, and we're gonna call in our calendar now, all right. We
need to iterate through our
sales. Take only the month, and then we say that's append that data. Let's
call calendar. So calendar dot month abbreviation. Yeah,
month abbreviation. We need to do that for all of our entries reiterating
through. We can look at that real quick. We see let it all prints out.
Okay, perfect.
Now, create a new column. Let's create a new...
new column. What we call it a month and abbreviation, I left it out the
B but who cares. Month abbreviation and let's take it from all of our list. And
then let's see what that looks like.
Here's your new you new column corresponding to the month, okay. Cool, so
we know that that works. Now, we got to get our information for our plotting.
The first thing I noticed when I did this; was I needed to change the plotting.
Because, it was just too small .I figured that this worked for our example.
Then I say alright let's take the figure and take the axis. Then
we'll call PLT. We're going to call our subplot, and then we got to say how big
of us of our plot do we want? So we say the figure size, equals the fig
dimension.Then you need to call the Seaborn. So I just alias this
and you'll see why, in a second. I'm saying SNS, which is my alias for Seaborn.
Call the bar plot, and then my X will equal the year, my y will equal the
quantity. Then everything that I'm coloring, will be the hue. Which equals
the month abbreviations. Then the data that we're using will be sales
data. So then I need to do the legend. Because, that was extremely small when I
did this before. I said all right legend.
We need your location to change because, it went to a goofy area. And I want it to
the upper-right ,was in like the center before. Also, want to increase your
size to 15. And then I want to set my labels my x label as the year. The
font size as 24. Then we'll just do the same thing here.
Do this for Y. Could it typed this out faster, I guess. And then the quantity
we're going to do this a 22. Oh you need to change these, so
they're bigger. This is your tickers. So we say
tick parameters. There we are. Alright, cool. We've got our
plot, perfect. If you notice here's our sales data.
This is as of two days ago. One thing I would say to probably change, it maybe be
the color palettes. These don't blend in or consider putting like demarcation
values right here. To separate each of these a little. Other than that we're doing pretty
good. We have it by a year and by month if you notice here, and here's our
quantities of CPUs that were sold. You can do a heading or title up at the top
if you want. Would be other one other suggestion, I would say for this. But,
other than that I would like to say: thank you for watching. Please, subscribe
and turn on that notification bell. Leave a like. If you didn't like this
video throw me a comment and tell me why. So I
can make this channel better for all of us. See in the next video. Bye