all right in this video i'm going to show you how to convert a json file to an excel csv or
google sheets so in this example i have this json data so right now i'm viewing that through
firefox and firefox has a built-in actually json parser if i just go to this tab
see it will make it a lot easier to read and basically we have this data that has all this
what we can say as rows in this data this array see and each line has these columns in it
and here there's also the second level for this location that has this two columns
inside of that location i was trying to find a file that has multiple levels of
indentation but i couldn't find any publicly available data sets that i could use so
i'll just go over this example with a single level and hopefully you'll be able to take that and take
it to the next level if you need to do this at all so to be able to do this all you need is a
google account so a gmail account or really any google account and what i'm going to do i'm just
going to take that file that's going to be a file saved in my system and i'm going to go to this
colab.research.google.com so once you're logged in with your google account simply go to
colab.research.google.com and that should basically bring you to this screen so now what
i'm going to do here i'm just going to click on this new notebook and that should get us started
so you want to probably name this notebook if you're going to be using this later on
otherwise it doesn't really matter
and what i'm gonna do here i'm gonna get started by first uploading my file here so
what i'll do i'll click on this little folder icon on the left and see it's still apparently
connecting so once it's connected it's not gonna do that anymore but now we're done here i'm just
gonna right click in this empty white space and click upload and that should basically
just prompt you to select one of the files so once you get there just select your json file basically
and open it and that should upload so this is just warning you that once this runtime
is over this file is going to be deleted so basically what that means is that once we run
whatever we're gonna run to do the conversion next time you reopen this google collab project
this file is no longer gonna be here so if you need to do this again you have to re-upload that
file but for now that is sitting here so i'll just go in here and i'm gonna just do import pandas as
pd so i'll just run this line and add another code block so now that pandas is imported as this pd
we'll do pd dot and then we should be able to do this read json method
and here all we have to do is just in quotations pass the name of this file so i'll just type
inspections.json that's the file name coming from here as simple as that so we're going to
save that in a data frame so i'll just do df equals to this and if i run this that should
hopefully read that file if i open another line here and just check what's inside of that df
you'll see that it basically red let me just zoom out a little bit that file and see these
are those column names so if i go back and look at that file see we have inspection id dba name
etc and those same things are gonna be here now in more like a spreadsheet looking format
so at this point if you don't have to deal with like a second level indentation or anything like
that you're pretty much ready to put this to an excel file or a csv file so all you have to do
just do df dot and then do two well let's start with excel i guess that's gonna be really quick
and do two excel i'm gonna zoom in so you can see what i'm typing and just provide the name
of your excel file you want as a result so i'll just call this results.xlsx so that's gonna be my
excel file so that's all i'm gonna do i'm gonna run this and that should now be here but this
is probably not refreshing so i'm just gonna click on this refresh icon right here and see
this results xlsx is showing up here so if i just go ahead and click in this and download that file
let's just go ahead and open that
so i'm gonna just zoom in so you can see what's going on so you can see we got ourselves an
excel file with that json data converted to a nice spreadsheet i'm just gonna close this
so just three lines and we're done as you can see now if you wanted this to be a csv file
i would do dot to csv where is it there it is and then just name the file right
whatever that file name is dot csv so if i run this
let's refresh now that should create see this csv file so if i download this and open that file
so as you can see my system opened that in excel
but it really doesn't matter so the same thing now we have our csv file so at this point if you're a
google sheets user you could just open any one of these files and import that in google sheets
so i should be able to just go to a spreadsheet go to file and import
and i'm just gonna basically drag that file
over here i'm just gonna use the excel file that i've created a second ago
and we'll just insert a new sheet i guess
and there we go we have our data in here
so this should cover how to basically take that data and put it in here so i want to
talk about how do we do this inner level things now right now in this data we don't really have
to do that because we already have these two columns but let's say you didn't have these
two columns available like this data has so if i go back to that json see they do have these
two columns here but they also have this location that repeats those two columns now let's assume
these two weren't available by default and you had to get these two as separate columns from this
location now by default the way that would show up would be like this see in this weird json notation
in this column instead of having two columns side by side so to do something like that i'm going to
go back and what i'll do i'll just comment this line so right now this data frame we have here
just to refresh your memory it's just that data right so it has this location column that has this
thing happening in here that we want to convert to two extra columns so what i'm gonna do on this
data frame there is a method called apply and what it allows you to do is apply a function
over each row of your data frame now i guess it's not actually correct to say over each row if you
apply it the right way it will be over each row and what that means is that the first argument
in this is going to be the function you want to apply so let's just call that function add columns
so that function doesn't exist we're going to create it in a second so i'm going to do a comma
and to make sure that it applies that function over each row of our data we're going to have to
use this parameter axis equals one to make sure that this function add columns is applied over
each row of this data df now this function at columns doesn't exist so we need to create it
so i'm going to add a space here and do a function and the way we define it by this def and then we
do add columns that same function name and that function is going to accept this parameter right
here and that parameter i'm going to call it r is going to be each row of this data set so every
time is going to be one of these rows and we want to take that row and do something with it and then
put it back so what we're going to do with that row we're going to go in here and hit tab to go
indent this otherwise python is going to give us errors in this so to be able to get to this now
location column inside of that row we can do that by doing r and do these brackets and in quotes
provide that column name which is called location so i'm just going to do that so i'll just save
this in a variable i'll just call it lock call or loc column well you know what let's just
do the whole thing i'll just call it location column so that's basically going to be this
whole column here with this json in it so at this point what i want to do i want to take
whatever is inside of this column this object we have with this parameters with this inner c
like this one and the second one so so far i got to this location which now has this other
object with two things inside of it so now inside of that i want to get to this and this basically
these two values and to do that i'm going to go back to this and basically we're going to create a
couple of other columns inside of that row so to create a column we just take that r
and encodes provide a new column name so you want to provide a column name that doesn't
already exist so i'm going to just call this one lat for the first column and see we have this
longer name version already i'm just basically creating a shorter version name for this extra
columns that i'm gonna do and to get to that value from here from inside of this location i'm just
gonna take that location column and inside of this i'm gonna go and say get this part
so that should now go get from this object that we've created the part that we need and store
it inside of this new column that we've just defined inside of our row right here
so i need to repeat the same thing for the second part so this one we're going to call
long i guess and we'll take the name from here you can copy it from here or you can copy it from
see here it's the same thing basically everything up to that colon and that should now create those
two columns so now all we have to do in this function just return that whole row which is
that r variable after we make all these changes so basically what this is gonna do it's gonna
take the row initially how it was then we're going to add two columns to that row and then return the
row back and we're going to do it over and over and over and over for every single row from this
data so let's try to run this and see if this works so i'm going to run this that will create
the function then i'm going to go back and rerun this or run this the first time apparently i got
an error so i have to take a look and see what the error says so it says float object is not
the error i get is c on this line where we're trying to get this value from here and apparently
it's trying to get that value what it thinks is from a float instead of being like an object
all right so let's try to do something here really quickly i want to be able to just see if
this thing fails completely or what's going on so let's just try to for example just print like this
and see if that ever happens so i'm going to run this function and rerun this
okay so that does print something so this means it doesn't actually give us the error on every single
row but it happens apparently on some point so what i'm gonna do to try to rectify this problem
we'll just do an if statement in this function so what we'll do here we'll just say if the type
of that location column to a dictionary which is a dict basically then we're going to know that we
should be able to do this so we'll do a tab to indent these two lines to get inside of this if
statement so if it's not however so we'll do else so in those cases i guess we'll just leave the
values blank so i'll just copy and paste this and what i'm gonna do i'm just gonna replace this with
double quotes or maybe we'll just do none we could have done also double quotes to do a blank string
that should be fine too so i'm just gonna do this and try to see if this will fix the problem
so in this case basically we'll first after we get that column for location then we'll check
is that actually an object by doing this type check and if it is then we'll go and
get the parts we need otherwise we'll just say there's nothing in there so i'm going to run this
and rerun this function right here oh no errors this time that's good news i want to go check
those columns on the right to see if they exist see we created this two extra columns now again
that was not necessary because we kind of already have them apparently they are
reversed did i do this right interesting let's go take a look in the data because this seems to be
the opposite of what they have here even though the values are the same so is it the same way
here see this is 41 for this and then here it's negative yes so it's reversed here too for some
reason i don't know what the reason is but i don't really care that's not what this is about so i'm
gonna get back so we were able to place those in their own respective columns now inside of this
now you want to remember though that you need to save this back to either the same data frame or a
new data frame so i'll just call it ndf for a new data frame and rerun this and what this is gonna
do it's gonna basically create this whole new data frame now with this new name so if i look inside
of that that should have those extra columns that i just created here on the right so at this point
i can just save this to again an excel file or a csv file so i'll just do two excel or two csv
the same thing and just name the file so i'll just call this new file dot xlsx run this line
no errors so at this point if i go back to this
apparently i called it new file so let's download that
and open that in excel and again you could take that and import it to google sheets
or you could create a csv file but now if i just go in to show you what happened in this file
see i have two extra columns here that i've created again using this data from
here and basically we have that data in here in these columns now in their own
respective columns so that's how you can go to an inner level if you have to from that json object
like this and get the data put it in an extra column and basically just export it again and that
should do it for this video thanks for watching please subscribe and i'll see in the next one