what's going on everybody welcome to
part three of our how to make a friends
you tour series in the last tutorial
basically we just kind of created our
tables or is set the scene for what
we're expecting to be doing and now in
this tutorial what we're gonna do is
start actually iterating through that
file start cleaning up the data at least
a little bit so with that let's go ahead
and get started so the code that we're
going to be writing here at least for a
little bit will be in here so I'm just
trying to make some space here so now
we're going to do is I'm gonna go ahead
and start a counter for row counter and
then I also just out of curiosity paired
rows so both of these things are gonna
be these counters row counter is just
gonna tell us kind of how many rows have
we gone through as we're iterating
through these files with like 50 to 80
plus million rows and then paired rows
tells us how many parent and child pairs
have we come up with basically because a
lot of the times we're probably not like
a lot of comments will go without a
reply because they don't need a reply or
they're never seen or whatever
so anyways we've got that now we're
gonna do is with open and now we want to
open one of those files now wherever
you've stored your files is probably
different from where I've stored the
files so mine are in J :
chat data slash reddit data and then one
of the files for example like let's say
we're into 2015
so we'll say that's but well it already
even knows okay so 2015
look at idle see people dis idle for you
know being not special or whatever and
look at this high-quality anyway okay
then finally our C and it'll be
underscore and then again some sort of
variables so them note will do is format
and then format is this will be time
frame dot split and we want to split by
was it a dash or underscore
that will be let's see it should be well
for the time frame we're going to need
split oh you know what no no it'll be
time frame - zero there we go
pay attention everybody no so split zero
and then finally RC underscore and then
it'll be the full time frame so so so we
want to open this file right we want to
open that file and then we're gonna
buffer we'll just say and then as F and
then we're ready to actually start
iterating through F so for example what
we could say is for Rho in F and then
what we can say here is first of all
let's go ahead row counter plus equals 1
and then what we're gonna do is row
equals json dot load string row and then
we can say parent ID parents ID equals
row parent ID and then we're gonna say
body equals so the body's probably gonna
have some issues so we're going to do it
format underscore data so we're going to
create a new function for this and then
it'll be ro body we need something to
kind of sanitize and clean up that that
data so that'll be body and then created
UTC that'll be row created UTC and then
score row
whatever the score was and then finally
subreddits
will equal Rho subreddit okay so now we
want to go ahead and do is we're gonna
come up here and we need to go ahead and
create that format data function so I'm
just going to define format data it's
gonna take in data and basically what we
want to do is data equals data down a
place a place and well at this stage we
really just are trying to replace a few
key things the first thing is new lines
so we want to get rid of any new lines
so I'm just replace a newline character
and we're going to replace that straight
up with I'm just going to put spaces
around it newline character okay that
way first of all it doesn't get appended
so I can you oftentimes at the very end
of something you get a newline character
so be touching and when we go to
actually tokenize this information that
would be like tokenized together and we
definitely don't want that
we also simultaneously don't really want
to token I like if we if we tokenize
this correctly we're going to tokenize
most likely a backslash and and as
separate entities not as one single
entity that means basically when we
tokenize we tokenize entire words in
theory you can actually tokenize chunks
of words and stuff like that and like
syllables basically but we're not going
to do that either so basically long
story short we want to make sure that
newline characters stay together but
also we can't we're just going to make
up a word basically hopefully no one on
reddit actually uses newline care but
anyway that's what we're doing there and
then we're going to do the exact same
thing with return I'm pretty sure reddit
will combine its like slash are slash n
or something like that so we're gonna
make sure that gets replaced and then
finally I'm just gonna replace one more
thing and that's going to be the double
quote we're just going to say all
quotes are actually single quotes just
to kind of normalize the data there so
because there's really no reason for the
AI to think to think that if there's two
you know there's no way these mean mean
the same thing so anyway we're gonna
we're gonna do that so so that's how
we're going to format data so then we
just return data there and now what I
want to go ahead and do is probably
right here I'd still like to do it like
we'd like to do something like this like
parent parent data equals find parent
parent ID so we might there's gonna be
times where we're gonna maybe you want
to find information from that actual
parents comment so if well let's just
write out so define finds underscore
parents we're gonna find the parent by
the parent ID and then what we're gonna
do is we're just going to say SQL equals
select comment from parent reply this is
the name of the table so if you named it
something different obviously change
that where comments ID so actually let's
see a parent ID oh in this case never
one will talk will probably talk about
this more in the next the next one but
anyways parent ID is gonna get passed
into here and then basically we're
looking for anywhere where the comment
ID is the parent so this is how we're
gonna actually find the initial parent
comment that belongs to that so like for
example every comment the string has a
parent ID but it doesn't have the parent
text right the parent body is not there
right so when we insert this comment
into our database we actually we're
gonna want the parent body so this is
how we're gonna do that so anyways
select the comment from parent reply
where the comment ID is equal to that
new comments parent ID okay so we're
comment ID equals
and then let's do mmm
this should be I think we're gonna need
some single quotes around that and then
we'll say limits one we're hoping that
doesn't get violated but anyways format
paranoid II so then why won't you let me
do this thank you sir
now we're gonna go ahead and execute
that so see that execute we're gonna
execute that SQL the results will be C
dot fetch one if result does not does
not equal none then we're going to
return results even let's fetch one we
still need to say the zero with and then
Oh actually I'm sorry
so you actually wouldn't I'm pretty sure
zeroth is just because we're only
selecting one comment there so normally
you might say comments score or
something like that and so it's always
gonna return a list so actually this
fetch ones will normally would be a list
of lists in this case it's just a list
but it only happens to have one element
anyway anyways return result 0 and then
else let's just return false you can
make that a new line if you want I'm not
gonna do it yeah and then just in case
we need something wrong let's just try
accept exception as a print find parent
fabulous and if we hit an exception
let's let's uh let's just return false
I'm just gonna come at this out actually
for now if we're having issue I'll let
that that print out anyway I think this
is a good spot to stop we've done a lot
of coding here so obviously we still
have a little bit of ways to go we still
need to actually insert this data into
the database and all of that
but then we also we have a few more
constraints on the data that we're gonna
put in like is is the is the string
maybe too long is it empty also there's
all kinds of things like sometimes
sometimes comments get deleted so they
might have been deleted before we
actually get to them so to have like
deleted or removed in like brackets and
stuff so we don't really want those
either so anyways we still have quite a
few things to build out also we might
want to add in some logic about like
under what constraints what do we want
to insert something so I'm gonna use
score for example I'm gonna only really
want comments that have scores that at
least one person up voted all right
because there's a lot of comments that
are pretty much useless so anyways
that's it for now questions comments
concerns whatever feel free to leave
them below otherwise I will see you in
the next tutorial