Welcome everybody, you're watching mr fugu data science. Today we're looking at python to
mysql. I have a few things that have come up lately. Which i'm pretty sure will help you out in the
future. What we're covering today is some problems you can have with date time formatting, for imports
into mysql. We need to investigate handling Nan values. Things we should consider for
schema, what can happen with dot notation. Here's my socials as always feel free to
hit me up. All the code will be in the link in description below per usual. Let's get into this.
I will not be going over everything on how to set this up and how to do it. If you'd like to
see those. I have two separate videos to create your user credentials as well as sending data as
data frames and as separate files bulk loading into mysql. You can feel free to follow along
and look at the code. But i'm not going any specifics. I do need to mention this you will
need to install the mysql connector and let's scroll down. The next thing that's highlighted right here
is my user credentials. The code will not work with this unless you set up your own user credentials
or the snippet that you'll see below if you don't have credentials that you want to set up. Just to
log in and do manipulations. The next thing i use a mac this is what i started in the terminal to get
myself going. You could try this to see if it works. Just put in your own user information for your
password, user, and database. Feel free to pause the video for these real quick. Our first example deals
with some date time formatting. What you see below is six columns of data that i parsed out. Which are
timestamp. These are a little unusual not because you see the scientific notation. But we have a few
too many zeros which causes issues when you're trying to use the date time formatting. We divide
by a thousand along all the rows to get the proper formatting and remove the extra zeros. Going along
a data frame we don't have to worry so much about using a loop. We have the apply. Which allows me
to throw in whatever i'm using. In this case i want to convert into a date time. If there's some error
just coerce it. If you wanted this utc formatting you could use this as true. We're not using in this
case. But i'm allowing you to see an option and we're going down to the seconds. Feel free to help
support me. I gotta do the shameless plug. Super thanks, for my channel and channel memberships.
If you want to expand your data because in this case we're having the my sequel format for the
year, month, and day. You can add this on here. You would just unblock this and remove this right
here and add it in if you need the time portion. We have Nan values in two columns at least from
what we see. This is a potential problem. If you notice here we have three columns. This particular
column there are Nan's. But you can't see it in the first five rows. When viewing your data always
investigate first. We have three of these columns out of seven thousand plus rows. This is the number
of rows that have Nan values for this date time. We need to see is this going to be an issue for us
throwing it into mysql. Well the first thing we should do is let's just play around and see if
this will go into the database. We create this table here this generic table that i created.
I take each one of my columns and i'm only using the date. Because that's what i parsed out.
If you wanted the date time you just add time onto this as one word and fix the formatting above
that you would have to unblock the code. You could send this directly into mysql and establish your
schema for your new table you created. That's not a problem. What's going to happen is, when you throw
these data into mysql into that new table you have an issue. We're taking that data frame that
we have from above and we're converting it into values. And each one of these little strings that
you see represents each one of the columns that we're going to use. We're executing many. Think
of this as like a bulk upload. We're sending it directly from python. Let's see what this error is.
Scroll down and you see. Oh man. We got a bunch of Nan values. How are we going to deal with this?
I set up two different versions because this is date time formatting. The first one we're going to
use a column that's consistent with our data. Where we can take that row value and place it instead of
the Nan value. This is the first option i'm doing. Why did i create a deep copy here? Because i did
not want to use any reference from the old data. I want this separate. Because any changes i do
to this will completely change the data. And the following examples will be overwritten for the
data and i don't want that. That's why i did this deep copy. We're using our apply function with the
lambda. Which is just your anonymous function allowing you to do whatever operations you
want. And we're filling all NA values with that specific column name. And we're using
in place equals true. We don't see any more Nan values. But how are we going to be sure? Absolutely
certain we didn't override all of the data. We need to investigate this before we move along. This is
extremely important to always self-check. From stack overflow in the citations will be the link
for this. Very helpful i just chucked it in as it was and put my data frame. I didn't do any other
changes and it works. Here's the original data with the Nan values. And we're making sure that we're
saying find everything that's not equal for your rows. Both data frames. This second one the updated
is the one where we overwrote any Nan value and replace it with that new column. And you're
stacking this creating a nested sort of table. Here's the id which represents the row where your
data are coming from. This is the column of where the data had discrepancies. Basically where this
one was overwritten and this one had a Nan. That's what's going on here and that's what these true
values are. And you notice here that we have 3170 rows out of 7300 or so rows that were changed. So
at row 3. There were two columns that had changed for Nan value. At row 14 these two columns. At 15
this one column and so on. You see this ellipsis. Because it goes on and on and on. Well that's fine.
Let's find another way from stack overflow from the same little page that did it differently with
numpy. Where we're using the np dot where. We're finding where the first data frame is not equal
to the second. The first one has the Nan values the second one has the overwritten values. We're
converting each of the data frames to values. And then we're finding the differences. Then we're
saying what two represent what you changed. And this is the original value. The index once again is
going to follow where the changes occurred. You have this id value once again which is your row.
This is the row where both of these columns were changed. This is the original value and this is
what the values were changed to. You say well these all look the same. Yes because at that particular
row this is the value. But it's not going to be the same because you see this is for March this
for January. So we're good to go. We're at that 3170 again so this is done correctly as well. Here's the
second example of how we can pluck these dates to make something cohesive for our import into mysql.
Choose a date range that is not included within your data. Something that's extremely far away so
you will never have any confusion. Something that's a thousand years from now. 100 years past. Whatever
it may be. Something you know that's very distinct from your particular data. That's what i did here.
I took the data frame column and i replaced any Nan value with this made up year of 1900 with the
month and day January 1st. And using the regex's equals true. Perfect. Here's an example here and
here of our changes. That works. Now we're good to go to start sending these data into mysql. So
we're sending it into this particular table. We're inserting many. Just think of a bulk upload. Here's
all of the columns that we're sending with the matching little strings here. From our data frame.
Converted to values. Remember this list. So it could read everything in. Here's the query that we're
taking. We're storing the output of that query as this df. We're throwing it into a data frame
and we're taking all of the column names from our query. And that's how we're re-outputting this for
us to read it here. So this is going full circle. Taking the data from python, sending it to mysql ,
sending it back to python and reading it in. That's what just happened. So we're good to
go and look at that. I use the 1901 just as the example to show you this column this column and
this column are the ones that we had to deal with. What are some other errors that we need to look at?
Dot notation and more Nan values. We also have something we're going to consider. Is changing
formatting of our data for data types. That's going to be very important. I intentionally created dot
notation to screw us up on our data. I took a subset of this full data set for mysql which is
like. So let's inspect this and notice that only the object id is the only one that's consistent
here. Everything else has a bunch of Nan these ones pretty much have almost the entirety of
Nan values. Really i would just delete these whole columns. But forsake argument let's deal with this.
Here's the data types but that doesn't really help us out. The floats are okay. The objects are
kind of an issue. Because you need to start investigating that further. These also should
be investigated. Also for a few reasons. It could be a float if you investigate the data and it turns
out it's float. So down cast it and return it as an integer so you can say memory. I intentionally did this
dot notation so we could express what will happen for an error. Intentionally creating an error and
then fixing it. I take these data and i change the name intentionally. I create this schema for
this first dot notation table where i create the schema for each one of our columns. And prior i
investigated to see what each one of these columns really looks like. And you're going to ask well
how did you actually determine this? Okay i'll get into that in a second. Because that's going to help
you out also. Since i could create any table on the fly like this and create my schema with my
variables and their data types and chuck it in to save it here into mysql. Hold on. What's going on
here? Oh no this dot notation. That's the first one. Underscore id dot o id. Let's replace that with an
underscore because mysql doesn't want dot notation for any naming conventions.
Fine i changed everything to an underscore. Now we're not going to have any problems, right? So
let's change all of the names for our little table and let's create this is a second example of this
table. Because i was lazy. Really you could just delete the table and see what happens. We could
send it in here that's fine because, now we have the proper naming convention underscore instead
of dot notation. But we try to import all of this into mysql by taking each one of our
columns placing it into this table taking a list of the values for our data frame and we get an
error. We scroll down and we see oh man this stupid Nan value again. Well this is interesting there's a
few ways to handle this depending on the data type that you have for each column. And that's
going to be extremely particular and you have to take time to look at that. I look at all the Nan
values and i say all right this is pretty big most of my data is at least three quarters Nan.
On an individual case-by-case basis just scroll over and let's investigate this one by one. I take
this particular column this column was a float. i know that because i denoted it here. And i filled
all the NA's with the 0.0. Then i converted it into a numeric column that's what you're seeing here.
I went and i repeated this for all these columns and i looked one by one to see what is going
on. What you would do is take each one of these columns just like this and you run it as a set.
Then you're going to return all the unique values and you can investigate from there. What it looks
like for the data types and that's how i did this for each one of these. I figured out 88 was Nan and
i was thinking how should i actually deal with this? Because this is a really unique situation
it's different than the others. You have a boolean situation and how do i want to handle Nan values?
I decided to place it as false. Instead of an empty string. Because i didn't want to have other issues
later cropping up when you're actually trying to query because you have to query differently.
I wanted to have it very simple and then create everything as false and in place equals true. This
column i filled it with unknown because that was relevant for that particular column. So let's do
this. So you can see exactly what i'm talking about so i'm not just spewing nonsense to you.
And just blank these out. And here's what you get. Unknown, true, false. This one you notice
is zero because i'm going to throw it as an integer and that's why i use this down cast equals signed.
Because this would output as a float value and with this i convert it into a integer. I think
you may need to consider if you have issues with if your data were truncating or rounding. In this
case it was not the situation. I think it was just someone lazy when they input data originally. We're
creating this table we have what appears to be the proper formatting. Convert our data frame into
values throw it into a list. Do our execute many and throw it in to mysql. Just like before coming
full circle. Taking my query. Throwing it in to a variable. Storing it as a data frame and coming
full circle back into python. When you're looking at this id value you have to be extremely careful
storing something like this because you don't want to confuse mysql with an id with this underscore
id. You have to be very careful with this throwing it into other databases as well like Mongodb. So be
careful with your naming convention for variables. i intentionally convert it back here so we can
keep everything else with the underscore except for this one. And we know what's going to happen.
it's going to throw us an error with this stupid id. i did this intentionally because
i want you to see if you have one column you can convert the name of that column here by renaming
that column. I took the position of it and i placed it in this new name and i changed this
variable name and i threw it into this new table i created. Yes i could have removed and deleted the
prior table with one or two lines of code. But i just did this because i wanted to illustrate
for you and it was lazy of me. Chuck it in to mysql and we'll send it off to that new table
with all of our variables. Just like all the other examples and everything looks good now. And here's
the changed one with this object id. That'll be the conclusion of this video so please like share
and subscribe and if you subscribe turn on that notification bell. Thanks for watching remember
i have a buy me a coffee. I have a Patreon which i haven't been using. As well as super thanks for
my channel and channel memberships. Feel free to help support me i got to do the shameless
plug. Thanks for everything for everyone who's helped or gave me suggestions over the last few
years. i'll see you in the next video bye. hey kid don't ever let them get inside your head