Welcome everybody, you're watching mr fugu data science.
Today we're going to parse some json data from a
car dealer file. I was contacted by a recent viewer who had interest in this,
who wanted a little help. So that's why we're making this video. We're basically
going to parse these data and then establish a primary key that we
can have and split these data into two tables
with the common key between them. I will use two separate examples of
separating some of our i shouldn't say nested data. But,
you'll see what i'm talking about. Here's your basic
imports. Feel free to go on my Github and download the code; it'll be in the link
in description below. And hit me up on some of the socials.
Let's align this a little better and zoom out some.
I have multiple files that i was given and i noticed that this could start
becoming time consuming if you're trying to parse all of these.
So basically what i would suggest is throw it into a file and then wherever
it's at for instance i decided to just look in my current
working directory. But if you have a directory of interest
throw that here where you would have these files. And then i used a regular
expression because all of them contain this
same specific generic name. And i'm basically walking through my directory
and looking for the regular expression match. And then appending it to a list.
So here i only have three files. But assuming you had many files.
Now we need to take care of these and put them into
one concise list of these json files. If you're doing the lazy way
and you didn't iterate like this you could call each individual file which is
kind of useless. But just depends on your skill set and
what you're actually looking to do. What did i do here? I iterated through my
list of json files and i use the with open. So we can look through each one of
these and do a read operation. and i'm calling the json dot load so i can open
these files and store them as individual list of lists. Let's look at the first
entry. So here's the first entry that we're
looking at. There's something that i want to mention
this is what we have to iterate inside of.
We have a first key which is your used vehicles.
And then that value is then a dictionary and inside of that dictionary you have
more dictionaries. So you could say all right i have some nesting here.
Okay fine. And inside this used value list
we have values which are our list of dictionaries. You say oh man this sucks.
But it's not that bad. So just scroll down and there's
something that you're gonna notice. If you pay particular attention
some of this stuff looks like it's repeating, right. So let me get to that
so we scroll down to this. And this add deduct
list. Which is a list of dictionaries and this is what repeats
for each individual entry. So what we're looking at is basically for each car we
have this particular information from a dealership and
apparently maybe this is like what's valid for each particular
model for a car for instance. Like we have 18 inch wheels we have some premium
package or maybe this is what this specific car had listed on it
all right. Let's do a little bit of digging inside of this and see what's
going on. So i'm iterating inside of f. That we just created which would be a
list of three entries which would be our files. And
then you have to go inside of each one of those
which will have the relevant data, all right. And this is what we're going to
need to parse now. So let's look at that. Now you're
iterating inside of it. You say okay, now i have my three my
three entries that are separated out just looks really
big. Let's enter that dictionary. We had to
iterate inside of it... So what did i just do here? For this
we had to iterate inside of this nested portion and append it. And i
threw it into a data frame. And this looks like it's pretty easy. These are
our three documents. Each document corresponds to a specific
vehicle. Here's the vehicle. Now we have a
specific made up vin number which is going to be very
useful later. As well as if we scroll over and do a
little bit of investigating we'll see that this could
be particularly useful as well. Now we have this portion that we're
going to need to open up and start digging around to make this
a little more useful and expand our data set further, all right.
Since, we're splitting this into keys we could use this later for
instance if we're making a database or loading this data
in to postgres or sql for instance. If we step back and think about
what's going on with these data. Since we're dealing with
cars you have a unique identifier which is your vin number.
Now since this is made of data you're only limited by digits
if this was real we would have letters with this as well. And each
vehicle vin corresponds to particular items that are very useful like... Are
particularly useful for example if you're looking at the
airbag, country of origin, the engine size, model year, trim level, etc. And this would
be a good point for us to start. Now if you're looking at the trim level versus
the model number of a vehicle: understand that you have varying trim levels for
any given model of a vehicle. So we're going to break this up. But i
decided i could actually which was not asked of me for this example but, you can
create a primary as well as a foreign key constraint.
Where we can use our vin number as our primary
key in this example and the model number as our foreign key
if we're interested. As it you know as a reflection you know of me speaking
out loud. I said i'm going to solve this column two different ways: this is what
each one of them looks like all right for each one of our rows that we need to
expand on. I need to mention that each one of these instances for these
cars has different or not the same length all
right. So we may see this 18 we may see this 18 inch wheel. But we
may have a bunch of other options that were with
this vehicle, all right. For instance this one will have two but
this last one will end up having six different options
and we'll see that shortly. Here's what one particular example looks like
for our very first entry expanded. Now what happens is this is repeating
you see this uoc, name, x clean clean, example blah blah. The
only thing was different is this name corresponds to whatever this
is. Maybe a trade-in value or something like this if it has these
options. That's what i was inferring but i don't know if that's what this means.
For instance this vehicle had 18 inch wheels with a primo package
and that's it. And if i had these it has whatever these amounts
are okay. There's a few things that are going to be going on with this code
you can pause this and read this and i'll try to explain it while we're going
through, all right. I'm iterating inside of this data frame
and converting it into tuples where the first entry is your key
the second will be your value. Then i'm iterating inside of i. And since
this is nested list of lists we need to go
inside of that all right. We can print that off and here
you go. You're going to have each one of these
entries. But, we're going to need to keep track of these since some of them have
varying... Well since they all have varying lengths.
Then i need to iterate inside of j to go even further to get key value
pairs of this. This first one right here will allow me
to call in my index values which is going to help me
keep all my ducks in a row. Here i'm creating my default dictionary
where i'm taking the keys that we're going to need for our columns
and a list of all the values. And then i'm doing the same thing
here but i'm actually keeping the index locations so
everything is aligned when we finish. So what's going on here?
Since i'm iterating inside of this... Let's do this. I have;
see this is what we're looking at right here. There's my index position and then
i have a list which is my values basically. Well key
value pairs, all right. So this looks kind of long and
interesting. Well what's going on here? This is the index for each observation.
Let's get a clearer picture of what's going on. I throw this in a data frame
real quick so we can see this.
Let's do something real quick since i didn't show this. This is the expanded
rows that we have from our data for that particular column.
But it's difficult to say which one of these
aligns with the other. Where did it come from? Because it's expanded. But i don't
have a representation or any kind of bearing
of which entry in my file these came from. That's why i'm doing this next step.
So here if you notice each one of these came from a particular
entry, all right.
So now i got our indexes set up so we know where to navigate to. But this is
looking kind of goofy. What's going on with this?
Something's wrong. There we go the indentation was incorrect
and i didn't pay attention. You always have to pay attention. I already
iterated and stored the information from this. I didn't need to go and reiterate
through anything else and i just threw it back one indentation were all right.
You pay attention: this one had only two items associated with it. I said the last
one right here it had six, all right. Now we have the correct
orientation of this. Now we should take our index, our vin and our
model number and find a way to deal with getting our table information sorted out.
That's why i'm using this intermediate table. What i did was since
i knew that i had a specific number of rows. Each row, i can treat it
as an individual index number and that's what
i'm using here. Where i'm taking the vin and the
model number. I had to transform it because the data was in a different
orientation. And i renamed everything so when we're doing our merge statement
next everything will work and it'll be
aligned. Pardon me. We're going to have a second example showing how to solve this
same problem. Let's look at that first before we do merge statements.
This looks like it's repeated. Now solving that second situation i found
this code right here on stack overflow. This particular cell
block is not mine. But what you're doing is you're using the itertools chaining
where you're basically iterating over many, let's say
dictionaries here and you're outputting one piece.
And what i'm doing right here is taking care of the indexes so we have these
lining up for what we want. Iterating through our data frame column
or series and converting that into our tuples. And you're outputting it here and
you just have to do next a json normalize to take care of this. And
expand it. That's what's going on. So we do our trusty old json normalize.
But we first need to convert this to a json format
and orient this called records. Load this or call it
as a json string and then use the json normalize
and kick this out. And now you have the same thing what we had before. So i'll
just take the second piece we'll throw it in to a merge statement.
And merge: just think of you doing a join operation. In this case we
want everything. So we're going to do an outer
join and base everything off of our index values for each one of
those data frames. I kept this so we can see
what's going on here. So you have your index values. It's
matching up with our vin number and our model number. This
is our... Pardon me. Since we had to put these
together it's off of index. But any for future
value i would suggest you use the your primary key. Which will now be your vin
number, all right. So we have all of our columns which are
expanded out it's pretty big. And see the final result. This is where
you will call both of your tables that you have and
inner join them based on the vin to make one large
data frame or table. If you were to do this or simulate doing
this as a database operation. We still have the
same 12 entries. But now we have 71 columns versus the original data
frame which was 54 columns and subtract that and you'll figure out
how much you expanded. But nevertheless that's pretty much the conclusion of
this video. I would like to say please like share
and subscribe. And if you subscribe turn on that notification bell. i hope this by
utility to someone. Let me know if there's any future
content you would like to see. Have a good one.
Bye