So far we have covered database connections, data inserts, updates and deletes.
Now we can discuss data transformations as we know by now, to populate the core layer table, there
needs to be some transformations applied to the staging layer table.
In terms of differences between the raw and refined layers, it boils down to the video duration column.
The YouTube API gives the duration of a video in the ISO 8601 format, as per the documentation, which
I will link in the appendix of this section.
From the documentation, you can see that in its most generic format.
And again here we are talking about the duration.
The duration column itself is represented in this format where P indicates the start of the duration
specification.
D stands for days.
T implies that there is an hour value represented by H, M Mm represents minutes and S represents seconds.
So a transformation function needs to be defined to translate this format into a format which we understand
such as hours minutes and seconds.
Now another column in the table is the categorization of a video, which means if it's a normal YouTube
video or a YouTube shorts video.
Unfortunately, the YouTube API is of recording of this lecture does not have a parameter that specifies
the video type.
And to make matters a bit more complicated, I've seen discussions where they will increase the YouTube
shorts maximum time from 1 minute to 3 minutes to simplify this and avoid any future changes impacting
our logic.
We will create another column called Video Type and any video that is one minute or shorter.
We will give the value of shorts.
If the video is over one minute then we classify as normal.
So let's first start by converting the ISO 8601 format into a more recognizable format.
Let's go back to VSCode and create a data transformation script.
And we can define the first function as parse duration, which will take only one argument.
This is the duration string that is in the Iso8601 string format.
So what we will do in this function is break apart this string by splitting it in such a way that we
extract the values that we want.
So let's first remove the strings which are not needed, which are the p and the T strings.
We will do this by using the replace method for Python strings.
So we can write duration underscore string.
But replace can replace p with nothing.
We can concatenate to also replace the tea with nothing.
So here we have an example of method chaining where we have two replace methods.
Return one after the other, separated only by this full stop.
Side note I want to add to this is that the string will always be there and needs to be removed, but
the string may or may not be there.
However, the replace method will still work even if the T is not present.
So let's take for example the value that I have here.
So far we have removed the p and the t.
So what remains is this value.
Now we need to build some generic logic that will take into consideration the components and the respective
values.
So in this example what we have left is one here for one day, two for two hours, 30 for 30 minutes
and 45 for 45 seconds.
So now let's define the components to look for using a list and initialize their values to zero using
a values dictionary.
We can build this as follows.
A note on the components list is that it's important that the order of the components is kept like that,
since we will be splitting the duration string in each loop using the previous results at each split.
After having these two variables defined, we can now write a for loop, so we will loop through each
component in the components list, and if the component is in the duration string, we split and extract
the integer value of the component in question.
The function will look like this.
So for component in components, if the component is present in the duration string, In the value duration
string, and we split it at that particular component.
The values between which we just defined will also be overwritten to the specific value that we have
in the duration string.
And like that, we have the values dictionary populated with the correct values.
Now before we can continue, we need to import the Timedelta class from the DateTime module.
So let's do that at the top of the script.
And now we just need to define a Timedelta object based on the values of dictionary values as such.
So duration equals timedelta.
Base equals the value.
Here we are taking each respective value from the values dictionary.
So for this is ready.
And now we have to do also for hours minutes and seconds.
The time delta function we used here will give a time delta object, which in Python represents a duration
or the difference between two dates or times.
And finally we simply return time Delta object.
Now this function which we have just created will use it in the main transformation function that we
can define as transform underscore data.
In this function we'll use the pass duration function we just built to take the current duration column
on the stage layer table, which is the ISO 8601 format, to a more readable time format in the core
layer, and also take care of the second transformation, which will define a new column that we will
call Mediatype that will be used as a categorization column and will have values of either normal or
as shorts.
So let's continue building this transform data function by adding row as an argument again.
Here, row represents one row in the staging table, which we will loop through in the main script that
will be covered in a coming lecture.
We first define a variable duration time delta that will have the time delta duration using the pass
duration function we just built.
We can then update the duration field in the row directly as a time object by writing.
To use the DateTime here, we need to import it as well.
Same as we did for Delta.
So to understand what is happening here, let's first understand what DateTime dot mean is DateTime
Daytime min is the earliest possible daytime, which if I iterate it down, would be this value.
Next, we are adding the time delta variable, which we got from the previous line, and we use the
time method to extract the time component of the daytime.
We can now define the video type column based on the duration converted from time delta to seconds,
using the total seconds method, where we apply the logic of less than or equal to one minute that categorizes
as shorts, else it will be categorized as normal.
So this will look like.
And finally we just return the row.
So that's it for the transformation script.
So I will see you in the next lecture.