Welcome everybody you're watching mr fugu data science. I have a question for you;
why do you suck at parsing json? Well we're going to get into that. If you'd like to support the
channel. Let's get into this. Here's some of the imports we're going to do today. If you'd like to
follow along there's a few things i think you should think about, when you're looking at parsing
json files. For instance, there's a few tools or techniques that you should be aware of. First,
json normalize and pandas explode. Those are two things that you should get in your tool belt.
Then the next things you should start looking at do you have a list of dictionaries. Do you
have nested objects, are your dictionary values a list? If you're doing web scraping or using an api
you could do some pre-processing ahead of time. Are you using a data frame to parse,
or are you iterating beforehand? I decided to choose the New York Times api once again. Because,
there's a few examples in this that will go over a few techniques. I keep getting people
coming up with similar circumstances and i decided to try to go through this as slowly as i can, to
reinforce some techniques. When you're looking at your data: every situation is different. You need
to understand that you may have values such as this, which are lists. Your values may be further
dictionaries. Depending on how that's set up would dictate how you're going to parse these data all
right. When you're calling this request if you're using an api or if you're doing any web scraping,
you can convert this into text. The good thing about using this json, right here we could start
parsing by using our keys to extract what we may need. You're going to see that all of the values
are different. These are just strings for these. Some of them are empty, some of them are dates.
Depending on what you need to do for processing will dictate how you handle these. When you call
this request for the api that you see highlighted right here. Zoom in a little bit you're going to
notice that we can iterate through this variable that we just created called json text and find
the outermost keys of interest. For us right now is this results section because the rest are just
junk. Since we know what that outermost key is, we could then iterate inside of that and append
it to a list. Throw it into a data frame, and see what we get. We notice that we have these news
articles and we could scroll over and we could see that we have these columns here. Which are lists,
some of them are empty, some of them are strings, some of them are further dictionaries. We'll take
these specific columns and practice a few skills. Instead of iterating like i did
above, i directly called that outermost key and threw it into a data frame. Which will give us
the same exact thing. Now when you're iterating instead of doing that data frame,
there's situations where you can actually parse your data directly. It just depends on how these
formats are inside of it. First thing, we're going to look at; is what do we do with the columns that
are still the list. Let's investigate that and only pull those columns so we could do
some formatting and get a little bit of practice. Understand everyone's data looks different and you
have to adapt and learn different techniques along the way. i've made around 13 videos at this point
doing various json parsing, creating functions, nested parsing, etc. I made those because,
you have various techniques and situations that come up. I can't cover everything in one video.
So we have these columns that are just our list of strings, and dictionaries , an empty list. What do
we do with this? The first thing you need to be aware of is the pandas explode, which flattens
out your list. But notice something, these lists are in varying lengths. So when you try to explode
these you're going to have an issue if you do it all at once. Because they're not the same length,
you're going to get a value error. Let's look at this from what i was just mentioning.
This is the function you need to be aware of to flatten out these lists. But, when you do this,
you're going to create more rows. You need to be aware of that and it's going to be indexed with
where it expanded from, and you'll see that in a second. Because you need to understand
this is a little bit different. You're working with more than one column of varying sizes.
If we were to use this apply function to use the explode on each column you get the value
error. That i'm talking about, if they were all the same length of list we'd be fine.
We adapt this a little bit and a few easy lines of code, super easy. What i decided to do zoom
in just a little bit. I took those columns of interest, and instead i iterated one by one
for each one of those column names. And i did the explode on it each individual time,
and then i updated the data frame. So it went through the first column, did the explode,
then it went through the second column and updated that. Let me show you what's really going on here
so you can get an idea. Let's comment all this out real quick. We're going to say that we want to do
this on one particular column. We updated this column and now we have more rows that you're
going to see right here. But all of these other columns are the same now. The next thing we do
is go through this one; remove this list. This one remove, the list. This one, remove the list.
And go one by one, by one by one, doing an update. You don't need to do any kind of recursive steps,
or anything like that. You don't need to make this more complicated. Doing this line of code is great
because, it just updates each time you go through iterating each column. You'll notice that we got
everything done. Now the only thing that we need to do is deal with this. Which will use the json
normalize, which will flatten out this and give us a dot notation on this media. We use
the json normalize but, we need to work inner to outer. First thing we do is we convert our
data frame into a json object, and then we use json.load s which is converting this to
a string and then we're using the pandas.json normalize function so we can flatten out that
dictionary. And if you notice this, you get this dot notation here for each key that it extracted
in that nested dictionary of dictionaries. That's what you pull out here. And when you scroll over,
it looks like we're pretty good. You only have this here. Really if you wanted to expand that,
you could expand and get more urls. But i left this because, it i just felt it wasn't
necessary. The next thing that i wanted to do is show you what if our values are instead of list
or just strings. There are more dictionaries and inside of that dictionary you have values that are
now a list of more dictionaries. i've done this type of stock data before. But i set this up with
two examples so you can see what this is looking like when you're parsing it so it's a little
more realistic. You have quite a bit of key value pairs here. We have two entries when we throw this
into a data frame. We notice that we have this outermost key which is our chart, then the next
key is the result that we want to deal with. And then we have to figure out what do we do with this
list of further dictionaries. We'll be able to use pandas explode once again. So instead
i decide what about iterating through the first two keys and throwing this out as a data frame
to get us closer and avoiding having to do any json normalized yet. And you notice that we have
this metadata. And we have to say, well how do we deal with this now? Because this is a lot closer
to what we need but, not completely there. At this step we're going to use the json normalize.
Since, we're dealing with a list i had to go inside of it and use an apply function so we
can go over these as columns. We end up with our two examples that we have which is our timestamp
information, and all these other columns. Each one of these dot notations deals with each individual
nested dictionary. Since, everything fell within this metadata which is this you get meta currency
meta symbol, meta currency, meta symbol, etc. You have all of these columns. Now we have these last
three columns here which are lists that we need to further investigate. This column is pretty
useless, as well as these two. If you've watched the other video i've done before. We're going to
use the same things we did with the pandas explode to get rid of the first part which is the list.
I dropped those useless columns and then i provide the same thing that i did in the first example.
Doing an update going over each individual column and doing an explode if it was a list. Remember
we're creating more rows. We could scroll over and see that we're pretty much there. And you
would only need now to do a json normalize at that point. Pardon the made up variable name but, we're
pretty much there. And you'll notice that we have these lists of values that we need to take care
of. Well when you have those list of values you just do your pandas explode. We are able to use
this formatting to go over all columns using the pandas explode because, they're the same
length. We do not get a value error and don't have to do any weird formatting or
iteration trickery that we did above. That's why this one will work. As you see here. We scroll
over and now it you'll see that we flattened out our nested portions. Now we have our open
adjusted close volume, and close. But that's the end of this video. I would like to say please like
share and subscribe and if you subscribe turn on that notification bell. Feel free to help
support this channel so i could continue making content for you. On patreon or feel free to buy
me a coffee. Don't forget to hit me up on twitter and instagram. i'll see you in the next video. Bye