Welcome everybody, you're watching Mr Fugu Data Science. I decided to compress
a few of my nested json video examples into one video
showing you different approaches you can take to solving these
problems. I would like to say thank you to the
viewers who have gave me some feedback recently for future video topics: i
greatly appreciate it, as well as the new subscribers.
Now just import pandas, json, and date time. We have four examples that
i'd like to go through. Okay let's zoom in a little bit so we get a better idea.
We have this first example where we have the outer layer of our
json object as entities. Where the values are further
dictionaries and some are lists of dictionaries, okay.
The second example is entities but, it's nested urls that we have. Where
we will use recursion. The third example we have
will be candidates where each candidate has a first name, last name, their skills
which are list, the state, specialty, etc. But if you
notice this: inside of our candidate we have another
separate dictionary. Which will be human resources related. We'll figure out how
to combine both of these. The last example will be taking
the same information we had above but you don't see your key value pairs for
your first, your last name ,your skills etc. And so this is a little deceiving
but we're going to look at how to solve this where you have a
tuple which is two different dictionaries hidden inside of it.
You still have your list; you have your your list of skills. Now these won't
be shown in any particular order from first example to last.
One of the techniques you could utilize for solving these problems is called
json normalize: where you can take some kind of semi-structured data and
basically flatten it out. So let's look at our first example for
entities that has our hashtags or symbols etc from
a twitter example. Let me zoom out a little bit.
Now let's look at what this evaluates to if we just
use the regular data frame and didn't do any
processing, currently. Okay, this looks kind of like an issue right?
If you notice you have your outermost part which is your entities and then
inside of that is what's looking like you're having a
nested table in a sense. Where it has levels. We have to figure out how do
we approach this. Let's do a little bit of formatting here.
Instead of setting it up like this. What if we did this another way? Called
the entities. How's that gonna print out? Uh oh we got this: all arrays must be the
same length. Well let's handle this differently.
Instead let's do something. Let's create a dictionary and let's do a
list comprehension and take the key and your value.
Which you're going to iterate and create a new panda series.
We need to iterate between our key and our value:
for example one data and i'm going to call in the entities. So we can go inside
of it. But, i need to convert this to items and let's see what
this looks like, Okay, so we have our one row; it's looking
like we're getting some progress. But, we need to further expand on this. So
how are we going to deal with this? Let's try something else: let's
flatten this out with the json normalize. But, convert our data
into a json string. Let's take it in convert it to json
and there we go. Now, we just split it up and got within our nested data. Let's
take this apart figure out what's going on. So let's take this first part
and see what happened. So you took this converted it into json
and oriented it to be in the record format. Then
we converted it to a json string here, and then we use the normalize
so we can flatten it out. Let's look at our next example: which would be example
three for our data that we're using but it's in reality the second
example of parsing our data. So we have our candidates which have nested values.
Which also have nested key value pairs. If we print
this off first as a data frame: we'll see that we end up
with two columns of dictionaries. And you say: well
this is interesting. How am i supposed to actually deal with
this? Let's do that json normalize once again and then see
what happens here. And let's take in i don't know this is taking the human
resources related. Okay, so it looks like we could play with this. How should we
manipulate these data? We're going to do the same thing like we did for this:
except here this is going to be our df3 and this instead
is going to be a candidate.
Okay, but it looks a little odd right here if you notice. So we need to
obviously do a little bit of formatting. Let's
change this up and do a reset of the index
and then we're going to call the column level equals zero,
okay. But, that doesn't look like it did very much now did it?
So let's check this out all right. There we go. We just needed to
transform that. i'll just call this the
df3 candidate as their variable name and then
we need to put this all together. So how are we going to do that? Let's
concatenate and take: what we just created as well as
the json normalize that we did above. So let's just do that real quick
of the df3. For the human resources related. But we need to
do this along the columns. df3 candidate, and there we go. Now we got
to put together. But, we could also do this
a lot easier. So let's do this we'll do it for the hr and we could do
it for the candidate. And instead of going through the rigmarole
as one other way of doing this. We can just use json normalize because,
of the formatting that we have for these data.
If we realize the formatting. What does pandas explode do?
If you had a situation where your column is a list of values
such as these skills column. You can expand it.
If you notice all your rows are duplicated and all it's doing is just
expanding the column of your skills. If that's a situation that you needed to
do that you can. Using explode: if i had a list of
dictionaries it would remove a list but i would still have a column
of dictionaries be aware of that. Some circumstances it's
good to use explode get rid of the list of dictionaries and then figure out how
to deal with that, accordingly. We can create dummy variables and let me
explain what's going on with this. If we have the column of skills which is
a list: we can just expand that into multiple columns and just use dummy
variables of ones and zeros to represent if it was included or not. So let's take
that into consideration by doing json normalize. Which is so fun. Taking
our json load string, we're going to take our last
thing that we used convert that to json and then as always
we're going to orient it towards records at least in this example.
Here we go. This is looking somewhat promising: except for the column names
which are kind of a nuisance. Let's get the column names corrected.
We're going to change all of the columns, and then we're going to take the
first name, last name, skill, state, experience, specialty, relocation. So let's
look at what our df looks like now.
Okay, here's our data. But, it looks kind of funny: so let's
transform these data. That looks a little more appropriate.
Let's change all the column names for these data.
Let's look at that. Now, expand on these data: where we're
going to convert this into dummy variables for added columns.
Okay, so that's looking like it's promising. But, now we need to combine
these data. So we're just going to use the concat
again. Let's take our dummy variable; and we're going to do
both of these data frames along the columns.
Now, we expanded everything. So if you needed a situation like this
you're covered. Now let's do some recursion: so for instance
if we had to keep going further in this and make sure we get all of the keys
that would be a scenario where you would use this function.
What i'm doing with this function: is looking to see if we have a dictionary.
If we have a dictionary, we're going to iterate with inside of that.
We're going to do a recursive call to see if we need to keep
moving further inside to find more keys. And we're going to split each of these
keys by this dot notation. So you can understand that
you're going inside of it. Then if we're dealing with any list for
our values. We're going to iterate with inside of that and run a
recursive call to see what's inside of that list. And
then finally we're going to just append everything that we found for
formatting. And this is what we end up with so here's all of our keys,
here's our values. We can create our data frame from this example.
We're going to say it's from a dictionary. And we're going to
call in the function that we just found or created. But we're going to
orient this along the columns this time. Let's see what this looks like. Ah, we got
to do a transform. Okay, that's looking pretty
good. But what else do we need to do with this to make it look appropriate?
Well this is our column names: which is in the first row.
So let's deal with that. So the columns
will equal the data frames, first row. Okay, now we just need to drop that row.
So we're going to call on the index of that:
which is the first row.
All right, now let's change the column names which is becoming quite a
nuisance for us. So let's just calling those
columns. Rename it with our current names that we
just made, and then let's drop...
There we go but we need to drop that first row like we did before.
There we go. Now we got everything expanded out
with the proper names without all the dot notation if that's what you
wanted. Let's look at this example which is a little misleading it doesn't
have your key value pairs like you've seen for the first and last
name and the skills above. But this is a little tricky and not
straightforward, pay attention. You still have this
dictionary that you need to deal with. You can keep this or change this.
Depending on what you want to do with it. Let's take our df and i'm going to
say: df4 to list. Create a data frame
for this. I'm converting these values to a list
so we can manipulate this; and you're going to see it's nothing too special.
Now let's do some json normalize: let's take in what we did before.
Convert it like usual; orient so we have this as records.
Let's look at that. Now we get this situated
it's looking a little promising. We can combine these together
along the columns. Change the column names
and drop the column that had these mixed together.
This looks quite good. Now if we're going to do some simple
iterating: let's figure out how we can deal with this.
Let's create a dummy blank list and let's go through that
example number two and let's append to it. There we are. Now this looks all
right but, there's a problem here. We have this second row that's not needed and
this higher date and the salary healthcare are all part of this one
entry. Except when you look at evaluating this
it's two separate dictionaries. So you need to do some manipulation
further. We need to separate this into the first
and second components and do a concatenation of it
so we can get this aligned properly. Take this first one. Let's look at
this first. Okay, we have our first part which is the
candidates which we're going to put in. The second
part that we're going to place will be the first parameter of t
and we need to do this along the axis of one: which is your columns as usual.
And now we spread everything out properly.
So the salary and the higher date are taken into consideration
for this one entry because, it's for one person.
Now if we were to iterate through this differently: and instead of doing
like this; we did this. You end up only taking the keys and not going
inside of the nested portion here, okay. We can also parse the dates of
this. I combined all of that prior into one
data frame, which you see now. And we could purse by date. We need to
just call this in. I'm going to take the higher date fix it and change
this into date time formatting for that column.
And i need to format this where, we're taking in the year, day and
month. Do this notation. Let's see how that looks. Oh, that's not
going to show anything. From there we can take our hire date
and start parsing through it. Let's just look through this.
It looks the same right. Now let's parse through this
and take the year. And you're going to use something different here. You're
going to use dt.year and let's see what we end up getting
back. And there's your hire year. We can take this and cut and paste it a couple
more times and find different things that we're
looking at. We could look at the month. But, not just the month. We could actually
get a month name. we can cut and
paste the same thing again. We can get the day of the week.
Depends on what your circumstances are. But, this is how you could parse this
you know. possible considering the
circumstances that we have. I hope with these few examples it'll give you some
confidence of parsing json or nested json data. As always i would
like to say: i hope this brought utility to someone.
Please like, share and subscribe. And if you subscribe turn on that
notification bel., i'll see you in the next video.
Bye