Quickly prepare data for ML using Amazon SageMaker Data Wrangler - AWS Virtual Workshop

Key Takeaways

hello everybody um again thank you for joining us today my name is vadim and i will be your guide in this data wrangler workshop so the goal of this session is for you to get familiar with data wranglers functionality interface and understand how it operates within overall stage maker ecosystem as we mentioned earlier today this will be a predominantly demonstration type of session and i'm going to use a sample data set the hotels data set which you have links to and guide you from start to finish on how to bring that data set into datawrangler perform few common data exploratory functions apply some transformations and we will then take a look at some of the options that you have available in datawrangler to export the workflow that we're going to create today so that you could later automate it and productionalize it and run it in in a repeatable uh manner so before we jump into the environment itself into the demo um let's uh let's take a step back a little bit and and maybe talk a little bit about what is data wrangler and in in fact i'll even take another step back and and kind of show you where data wrangler fits into the amazon sagemaker uh framework and for those of you who are not aware or are not familiar with amazon sage maker this is a aws offering which allows data scientists to easily prepare build train tune and deploy and subsequently manage machine learning models and as you can see datawrangler is right here in the prepare stage of amazon sage maker because it's it's it's a tool for data wrangling as the name implies we will predominantly spend our time here in in the data wrangler but you will notice as we go through the workshop that it integrates quite well with a lot of other features of sagemaker as a matter of fact by the end of this lab you will also see how datawrangler actually kicks off a sagemaker processing job to actually do the data transformations you will notice that there are features which allow you to not only export data out of data wrangler into a storage system but also it can export data into sagemaker feature store and because we are dealing with um with raw data um sometimes the preparation step of um uh data science life cycle also is a good spot for running some basic checks on quality of your data and sagemaker clarify allows you to analyze the data set that you have at hand and maybe look for bias or establish certain constraints that that apply to your data which later on could be used in production environments during monitoring model monitoring stages because if you trained your data if you trained your model on data with certain types of distributions with certain types of feature types you can monitor for that data quality during the model deployment phase and see if the inference requests that you're getting have the same distribution and and the feature types remain the same so those are some of the things that we'll touch on but again uh our main uh goal obviously today is to spend um as much time and go as deep as possible on on sagemaker data wrangler and understand how it works so let's go ahead and um hop into the uh the actual environment those of you who chose to follow along this webinar you probably have a link to the event engine you should have the link to event engine and you should have authenticated by now and hopefully you are at at the screen that shows this team dashboard um you already have the link to the uh webinar to this um lab guide so you can also choose to just um watch me walk in through the steps of the guide and then you'll have access to this environment after the webinar as well so you can then um step through the same steps uh go through the same steps at your own pace but you can you're also welcome to uh follow along as i'm going to go through through this lab so we are at the team dashboard let's go ahead and click on the aws console this brings up a pop-up in which we again choose open console and this launches aws console which you may have slightly different look but in general it should be somewhat similar to this on the left hand side you'll see the recently visited services yours might be a little different but what we're going to do here is at the top uh in the upper left corner in the search bar we will type sage maker and out of the two sagemaker offerings studio and canvas we will pick studio because datawrangler is part of sagemaker studio once in the control panel there is a sagemaker user pre-provisioned in this demo environment i'm going to click launch app choose studio and that brings me into the actual sagemaker studio as you saw on the previous slide data wrangler is one of the features of sagemaker studio there are a number of them and data wrangler is one of them but the way you use it the way you access it is by looking at logging into the studio ui first so what you see on the screen right now is the studio ui sagemaker studio ui let me make it a little bigger font and on the left hand side there is a vertical menu with different options and we are looking to go to the very last one this little triangle so i'm going to click on that by default it brings you to projects but we're going to change that from projects to data wrangler and that will initialize oh not quite so we the data wrangler and one one other thing we need to do is we're going to click new flow button right here that will actually begin the initialization process for the data wrangler while it is not that was quick it initializes quickly but i wanted to kind of give you an idea of what's happening now behind the scenes in the studio uh ui that we logged in on this architectural diagram we are here right now and we just connected to sagemaker studio and in order to render studio ui on our screens a virtual machine was started t3 medium virtual machine came up this virtual machine is provisioned at no charge no cost to you so you don't have to pay for it it's just there to render ui it's not very powerful there is no computations of any kind that we will run on it but just so you know that's that's what uh kind of drives the user interface of sagemaker studio and by selecting data wrangler in the studio ui right now we are here another virtual machine was provisioned which will actually power every interaction with data wrangler that we are going to perform now as we start running analysis various analysis as we start creating different transformations as we're going to bring in the data set into data wrangler all of that will be taking place on this virtual machine and this machine does have a cost associated with it so be mindful of of that and as you completed your session with datawrangler it is a good practice to shut it down just so that resources are not wasted let me show you where you can actually see that if i hop back to the studio ui one of the menu items here on the left hand side is this round circle if i come here you'll notice that there is a data wrangler virtual machine that's running m5 for excel 16 cpus 64 gigs of ram and to power it down you simply click this button here switch it off and it'll power down because it's a pre-provisioned lab environment there is another machine that's running to power some of your jupiter notebooks in your environment if you're starting data wrangler for the first time this the second virtual machine probably won't be around so back to data wrangler we are logged into its user interface um you can see that we are authoring a flow file i'll talk a little bit about that later it's currently called untitled flow let's go ahead and rename it by right clicking on on this tab and choosing rename data flow i'm going to call it hotels because we'll be exploring hotels data set and so i'm going to click rename the name will propagate in a few minutes to the left hand side so don't worry about that and as you can see here on this diagram the first step will be to import the data hopefully you have the data set downloaded if not it's a kaggle data set that we will be using if if you want to kind of quickly search for it you can start on google and search kaggle hotel booking demand and it'll take you to this kaggle website and in the upper right corner there is a download button to to download the data set it will ask you to create a kaggle account so it might take you a couple of minutes to to get through the authentication but once you do it's a csv file that you will download and that's what we will be using for this um for this workshop so in order to import the data set let's go ahead and click on that you see there are a couple of options listed here amazon s3 amazon athena and there is a couple of other sources that we can add redshift snowflake databricks there isn't an option to just drag and drop an excel file in here a csv file so what we need to do is we need to pre-stage our data in the storage layer with which data wrangler operates so in this case we will be using s3 and just to kind of put it into a context this again uh is uh it's another depiction depiction of of sage maker uh pipeline you you you saw in the previous slide uh how uh we talked that sagemaker has uh pre-processing functionality then model training functionality then model deployment and then model monitoring so this is an in-depth view into all of that kind of a little more in-depth you'll see how raw data comes into sage maker as potentially unlabeled data there is a tool for labeling that data if if you are building supervised machine learning algorithm you would want it labeled and you can see how ground truth brings in unlabeled data from this storage layer and then labeled data comes back into it this is where we are right now in the data wrangler and we are authoring a pre-processing job and you'll see how pre-processing again will take date from from the storage layer does something with it puts it back you'll have the option later on to put that ingested data into feature store any training jobs subsequent training jobs that you might run will also ingest data from that same storage layer the resulting models will go back to that storage layer those models can also be checked into model registry and finally inference is done by pulling in the models from that same storage layer so you can see how the entire pipelines entire sagemaker data science workflow is centered around the storage layer and so the storage layer is called s3 simple storage layer and we will be relying on it quite a bit and you'll see it referenced in data wrangler as well as in all the other features and functions of stage makers that we will be touching let's go back to here and obviously i'll i downloaded the the csv file the hotels data set from kaggle it's on my local drive right now so first thing i need to do is actually upload it to s3 storage bucket for that i'm going to go back to the prior tab that which we used to open aws console and i'm going to click it one more time open console it'll open another copy of aws console for me and this time instead of going to sage maker i'm going to go to our simple storage service s3 here at the top in the search bar i'm typing in s3 clicking on that and that brings me to my simple storage service dashboard there are two storage buckets that have been pre-created for me in in s3 buckets are kind of similar like folders you can think of them as folders so i will use the sagemaker us east1 bucket not the studio but us east one bucket i will go in there and i will upload my data set into this bucket so i'll click upload add files and one of them is hotel bookings open and click upload so this uh actually takes the file from my local laptop and ships it into the s3 storage layer so that it is now accessible uh to all sagemaker features you can see it's right here let's click on it and out of curiosity i want to see how many records are there one of the functions in s3 is you can actually run queries against flat files so i'm going to go and select query with s3 select it's a csv file comma delimited i'm going to do select count pick up the limit around the query and query results 120 000 rows roughly so that csv file has a 120 000 records so that's good to know let's make a mental note of that and i'm going to go back to the jupiter lab tab now i'm ready to bring that file into datawrangler so i'm going to select amazon s3 navigate to this sagemaker us east one bucket yours will be called something very similar but the suffix will be different the account number for each of you will will vary i'll go in there and here's my hotel bookings data set so i'll just click on it once uh to bring in details about it and i will also go ahead and collapse this uh um pane on the left hand side by clicking on the triangle so it toggles off to get more real estate here to examine my data so we can see that the size of the file is 16 megabytes it has a csv file comma separated you can inspect the data inside the file right here you can see all the columns we know now it's 120 000 records which brings up a point here we are now you can notice here in the upper right corner and as you remember from the diagram architectural diagram we are running data wrangler on a on a single virtual machine it is a large one 16 cpus 64 gigs of ram we are going to be crunching through this data set which is not too big of a data set we can totally ingest it completely into the memory of this virtual machine but some of you or many of you at some point or even right away are most likely to be dealing with data sets that are much larger and most likely will not fit into the memory of a single uh virtual machine so what do we do about that well for starters the idea of data wrangler is is creation of pre-processing jobs and recipes in fact let me come back to my slides here and this would be a good time to describe the data wrangler paradigm so again on this architectural diagram we are here in the data wrangler we brought in our data set from other sources we imported it into we uploaded it into s3 and we are about to bring it into a data wrangler but let's pretend that our data set is too big and will not fit into the memory of data wrangler so by default actually datawrangler assumes that and it offers you to sample your data and the idea is that everything that we are going to do in data wrangler for this point on we are going to be defining transformations one after another manipulating these fields and dropping columns adding new things feature engineering and so this list of transformations will be in the end exported as a recipe so this dot flow file which as you remember we renamed from whether it was undefined to hotels this will be our recipe file it will actually contain the exact steps that need to be applied to data the dissect artifact the other artifact that data wrangler will produce at the end of our session is a jupiter notebook with orchestration code so transformation recipes here orchestration code here this orchestration code will have all the logic necessary to start and run a processing job this processing job will take the transformation recipe and that then it will run against the entire full data set so even though we sampled the data to create our recipe the actual process in the job that we will run will do so against the entire full data set and these processing jobs are powered by spark clusters and they are highly scalable and can do massive parallel operations so there could be multiple instances of them running and you are actually in control of that and in the end the transform data set will be stored back into s3 at the end of the lab we will also explore some of the options of how these processing jobs can be scheduled there is number of options there but for now just so you know um this is why we are seeing the sampling uh choice uh on the right hand side in our studio ui right here it offers us to sample data our first 50 000 records we know we have roughly 120 or 130 but we'll pretend our data set is much bigger some of the other options here for sampling is random first cape by the way means that it's just going to take first so many records one two three four five sequentially you can shuffle them uh kind of get random or you can do stratified selection and you you'll notice that if you choose stratified it it asks you which column is of importance to you because what we are most likely be importing this data to build either you know a regression or classification model and if it is a classifier and in fact that's what we're building today a classifier one of the columns will be our target column so we will be predicting whether or not a hotel reservation is likely to be cancelled and so based on our target column data wrangler will attempt to select records from this data set in such a way that all classes are represented more or less evenly so the data set is as close to being balanced as possible and depending on your exploratory data analysis goals depending on your knowledge of data set this may or may not be a a valuable option for you so today we are going to go with just first k first 5 000 records um balanced data set is too easy right so let's do that so we dialed in our selections and go ahead and click import and that will ingest first 50 000 records from that s3 file into datawrangler and on the right hand side you can see our first two transformations as i mentioned uh the the idea of datawrangler is is dial in all the transformations and save them as a pipeline and these are our first two the first one is actually the choosing the data source where the data comes from this is convenient by the way because that means you can reuse this recipe later on even maybe outside of data wrangler and just swap out this step with something else and number two data wrangler step number two datawrangler automatically identified the types of columns in our data set and here we have a choice to change that if if we see that some of the features are not the type we want them to be this is our chance to change that so we have our two transformations before we proceed any further let's talk a little bit about the um well actually no we'll we'll take a closer look at the data set in a little bit um i'm going to go back to the home screen of data wrangler which i will do by clicking here on data flow a little breadcrumb in the upper left corner and you can see already that data wrangler is automatically building um a direct acyclic graph of your data flow you'll see how we started with hotel bookings.csv and our first transformation was applied which were the data types column types so the way you navigate datawrangler from this point on is by adding additional steps here by clicking on this plus sign and you can see there are these are the steps available to you transforms various types of analysis you can actually kick off a model training from here which will use autopilot you can sync your data from the memory of this instance directly either back into s3 or into sagemaker feature store or you can produce those two artifacts that i mentioned earlier the transformation recipe and orchestration code for variety of pipelines variety of jobs that you can then integrate with with your other maybe higher level orchestration layer which will rely on data wrangler to produce data which will go elsewhere again we'll review all that in a little bit but um for now we'll start with creating our first analysis right now it's um you can find it under analysis here but it's been also brought out uh to the main menu because this is usually the first step that we see data scientists begins begin data exploration in in their uh workflows so let's click on that and and um you'll notice that it it we ended up in analysis part of the data wrangler the analysis type there are multiple types but the one for data quality and insights was automatically selected for us the only thing that we need to tell it is what kind of problem are we solving is that regression or classification and what is our target column of interest what it is that we will be predicting we will be predicting uh the cancellation likelihood and it's going to be a classifier so i'm going to go ahead and click create and while the report is being generated let's take a look at the data set itself in the walkthrough guide that you guys have access to under getting started section you'll see that the data set is described here basically this data set is a snapshot of data it's a snapshot in time was taken from i don't know a reservation system of some sort which lists variety of hotels current status of their reservation how far ago how long ago the reservation was made what is the expected arrival time of a customer who are in the party who will be staying how many adults children what kind of meal they pre-selected if they did which country they're coming from uh which market segment this reservation was made uh through um a travel agent or tour operators uh the room type that was reserved the agent id whom it was booked through so number of different things here so you can examine this and we'll we'll come back to this uh description a few times as we are working on our analysis uh charts and as we're building transformations now looks like the data quality insights report has been built let's take a look and see what what we what we see here first of all a summary quick summary of the data set there are 32 features features are is another name for columns in the data set number of rows 50 000 makes sense that's what we selected when we were importing you can see that we have some missing values in some of the columns the number of valid rows is the opposite from from missing the two add up to 100 there is a quite a bit of uh duplicate rows that's good to know and the breakdown of different feature types 17 numeric 11 categorical no plain text one date time the couple of binary fields a two high priority warnings surfaced right away duplicate rows which you already see 30 percent close to 30 of duplicate rows the certain types of models and algorithms will are are more sensitive to to duplicate rows than others so depending on your the the model that you're building you may choose to uh eliminate duplicate rows but this this warrant is actually a closer inspection sometimes what may look like duplicate rows might not actually be duplicate rows if if our data set is as simple as a hotel name date when reservation was made and type of reservation you can imagine that on one date multiple reservations of the same type are made and unless there is unique id differentiating them that may look like a duplicate duplicate rows but in fact each represents a unique reservation so this this warrants investigation target leakage what is the target leakage um this as it explains here that uh oh it actually tells us that the reservation status column predicts predicts the target extremely well on its own which if you think about it makes sense it looks like the database had two columns that were indicating that a reservation was cancelled the flag is cancelled the column is cancelled zero no one yes and reservation status could be pending could be intros or booking or or cancelled and so if it's cancelled and the column is cancelled so these two columns are kind of redundant if we're going to try to build a model and when we keep the reservation status column in our training data the model will latch on to this particular column will build the perfect model but then in real life in real situation when we're trying to predict whether a hotel booking will be cancelled or not we're not going to have status ahead of time and so this is what's called target leakage when we are dealing in our training data set we are dealing with data that actually will not be available in real life so these are examples of duplicate rows a good a good snapshot to examine with for those if if you have the main knowledge of this data set you can kind of make a judgment call here whether or not these are indeed duplicates some anomalous samples are being bubbled up as well in order to identify anomalous rows data wrangler uses isolation forest algorithm again it does take someone who is familiar with data to actually uh look and confirm and validate if these are indeed uh anomalous symbols or not but it's it's good to know that there is a flag that that can bring your attention to it a quick analysis of a target column we can see that it is our target column is is cancelled or not one means cancelled 32 percent of records are um in that class and um 68 of non-cancelled so unbalanced data set but it's not too bad uh we can work with this for sure next um datawrangler attempts to build a quick model using xgboost algorithm splitting the data set quickly into training and validation but as you can see um we have a perfect model here the accuracy was perfect the f1 scores were perfect recall precision well now we know why right because we did have a target leakage the model just used that one column that that was predicting the target variable uh perfectly so not super helpful uh confusion matrix either let's see feature summary is an interesting statistics this part shows prediction power of different features and obviously you'll notice reservation status has a perfect prediction power but we're going to eliminate it from our training data set the the rest of the the features um sort of have pretty nice uh even more or less you know normally distributed spread for their predicting powers looks like interesting agent lead time yeah i would expect that how far in advance country i guess some of the countries cancel more than others average daily rate i could see that fluctuating and impacting folks canceling rebooking different hotels market segments things like that and it's just another way of representing that same data here target leakage we knew that let's see let's take a look at some of these now for each and every feature now we have this kind of breakdown i'm gonna scroll down a little bit to look at this one this one is a good one too to look into to dig into let me see if i can make it bigger yeah so lead time feature so you have two charts here a histogram on the bottom and um and and this um this chart at the top and then they are they aligned with one another so the way you read this histogram is uh you can see the number 25 here and the first bar shows that um 34 percent of records in our data set are bookings that were made within 25 days of the check-in time so relatively uh folks are booking their travels judged two three four weeks ahead no more than a month out and what you see uh at the top here it says the target label one is cancelled 16 so of in if we just were to focus on on these bookings that are four weeks out or less 16 of them end up being cancelled if we go months out slightly more than a month out it's we have 13 percent of records in our data set are more than one months out um a week or two 35 rate of cancellation for those and as you go further out unsurprisingly um cancellation chances grow so like if someone booked more than a year out there is a 74 chance that well no i i shouldn't be saying that 75 of records in our training data set that were booked with more with more than one year of lead time have cancellation label set to yes they ended up being cancelled so so you get this kind of charts for oh that's interesting for the countries different countries this prt prt uh portugal probably uh 45 cancellation rate that's interesting so you you have these um you have these kind of uh charts for each and every feature so you can examine your data set closely you'll notice there is another warning that pops up um occasionally rare classes for instance in this case it says italy china netherlands belgium uh very rare and it it kind of says if you want to consolidate those into one class maybe called other uh to just reduce uh variance for for that particular feature you can do so so this is our quick view into the data set i'm going to click here again in the upper left corner i'm going to click on the data flow come back here you'll see a little chart sign that means we we added an analysis let's take a quick look at a couple of more and go into transformations i'm going to click add analysis histogram just a simple histogram if we were to pick lead time and click preview and um it generates a quick histogram of of lead time you can see that again majority of folks at booking hotels uh within 100 days and and so on what else we have here um maybe a scatter plot let's try scatter plot on the x we're gonna put uh maybe lead time on the x and on y maybe average daily rate and click preview see what kind of uh chart we get here uh looks like an outlier here is messing up our scale but um it's okay we can still see um [Music] yeah not not not a clear pattern but you get the idea one specific analysis type of analysis i wanted to show is analyzing your input data for bias before we even go into building a model if our data is biased chances are the model that we're going to train will end up being biased and um the the data wrangler itself does not calculate bias there is a separate feature in sagemaker that does that it's a service called clarify but datawrangler interacts with clarify natively and there are other uh places in your data science workflow where you're going to call on clarify for data bias analysis for model explainability and even in production when your model is already deployed you'll still want to monitor for both for explainability to make sure that feature contributions don't drift or if they do that's a signal you need to retrain your model so let's take a look at that i'm going to again click on the plus go to add analysis and one of the reports here bias report so bias report obviously needs to know what your target variable is is cancelled in our case and it is um it depends on the the bias will depend on the type of outcome that you're trying to predict so we're going to say what are the is there a bias towards non-cancellations like if we look at all non-cancellations is there a bias and also when you're analyzing for bias you are analyzing an impact of specific feature so in this case we're going to say um let's look at um let's see what was that distribution previous so maybe no i think it was booking changes so what i dialed in here is this i want to see if my data set is biased towards keeping your reservation if you previously had booking cancellations how biased is my data set towards someone who who had uh booking changes and now are they more or less likely to to cancel that's what i'm trying to to to predict but i want to see if there is bias based on uh booking changes in in me attempting to predict if they will keep the reservation or not a little bit convoluted but um that's that's basically what it is um i'll go ahead and click check for bias and this will generate a report which will do that while we are waiting for it um we can take a look and see so we are following this workshop and we are in the exploratory data analysis stage at the moment right here we talked about importing data we looked at histograms scatter plots bias analysis we touched on target leakage i'm going to skip through feature correlation and um multiculinarity for the sake of time but as we're going to start dialing in transformations because we there is uh there are columns that um that happen to be um correlating and you can see here uh reservation status had direct correlation with uh is cancelled or not you can see correlation one means let's get a 100 correlation few more columns correlate with one another arrival date month arrival date week number 99 date week number date months reservation status and their arrival uh reservation status date so basically what this tells me is that a lot of these columns are redundant so i'm actually going to drop some of them during during our transformations so the bias report has finished a couple of things that's reports so far on the data going by the booking changes if someone had three booking changes this is the report and you can you can examine for different values of that column 16 booking changes how much bias is is in the data based on that and you can see the class imbalance variable difference in positive proportion and labels and jensen shannon divergence analysis so these are just three metrics that allow you to understand your data bias on the data level there is also uh and it's kind of outside the scope of data wrangler but there are ways to do further bias analysis on the model and um if we get a chance we'll look at explainability later as well but for now i'll go back to data flow and let's say we completed our exploratory data analysis and next we would like to actually start making transformations to our data how do we do that so we're gonna click this plus sign and go into add transform this time we're back to that same menu where we already have two transformations and i guess first thing first let's drop some of those redundant columns that we identified in in the multi-collinearity analysis so i'm going to click add step i'm going to select manage columns drop column and what you can do here is you can just start typing in the column names and they'll they'll appear so base and waiting list i'm going to drop hotel i'm going to drop and i'm just following the guide the data wrangler lab guide which as you follow you'll see the list of of uh columns that we're dropping in there and explanations why but uh mostly is because of that uh multicollinearity analysis i've got eight months and some of these are we just not not interested in maybe if we having the the um the main knowledge you might decide that you i know that these features don't carry predictive power so i want to get rid of them i will date a month all right so these are the columns that i'm going to drop i'm going to click preview and now i can kind of scroll to c and i will notice that you will notice that there is slightly lesser number of columns in our data set and to finalize this transformation you click add and now we have another transformation step that reads drop columns and we're dropping them now you remember that we in in the overall overview of our data set one of the warnings was that we had tons of duplicate data let's say we do know for sure that we we don't want any duplicates in our data set let's let's get rid of them i'm going to click add step and look for manage rows here and one of the options is drop duplicates there is no nothing else i need to say preview add all right we got rid of the redundant columns we dropped the duplicate records um i think there were missing values in some of the features we didn't i didn't drill deeper into which columns had them but if you follow the lab guide it explains that one of the columns called children if if there were no children uh on a certain reservation sometimes it said zero and other times it just didn't have a value so let's go ahead and um take care of that we're going to choose handle missing and you can see there are different ways to handle missing values we can run an imputation we can add just an add an indicator that something is using we can we can drop missing records that have missing values uh in this case we'll do fill missing um the column was i believe children and we're just gonna fill if there is a missing value in children we'll just fill it with zero preview and add all right we took care of missing values now if we are building certain types of models certain algorithms can be sensitive of to features that are not normalized that are not scaled and so it is helpful often to actually scale your numerical features and that's what we're going to do here and as you probably guessed there is an option for that so we're going to go in and look for let's see process numeric and we're going to choose scale values that's the only option the the options here standard scalar robust scalar min max scalar and we're going to do min max scalar and we'll use zero to one um boundaries and let's dial in the columns that we want to actually scale there's quite a few so we're going to do lead time we'll scale lead time stays in weekend nights stays in weeknights is a repeated guest again i'm going by the by the walkthrough guide previous installations it is not cancelled booking changes average daily rate aer um total of special requests and required car parking spaces i will use the zero to one scaling hit preview quite a few columns for it to transform and [Music] yes it's working its way yeah here you go so you can already see a lot of floating point values and one thing that stands out is we have quite a few categorical values so we should do something about that um let's add a step and look into encode categorical feature you can do an ordinal ordinal encoding similarity encoding or one hot encoding and we'll do one hot encoding in this example [Music] let's see some of the columns is going to be meal is full board or bed and breakfast type is repeated guest market segment was a categorical a assign the room type the one hotend code deposit type and [Music] customer type set network customer type so um let's see what are the options if um if there is an invalid entry let's keep it uh and do we want to one hot encode as a vector no we'll actually create columns separate columns so let's preview and see what that does to our data set all right now we should see a lot less categorical so there is still country distribution channel but the company is predominantly empty reservation status reservation status yeah oh i should have dropped it we knew this was a target leakage column so let's do that i'm going to add i'm going to go ahead and go to manage manage columns and i will drop columns reservation status i will drop country and um what was the other one distribution channel i'll drop distribution channel really interested in that a company yells drop company it's quite empty and we think we should be in good shape after that if you this actually makes our data set agent looks like a categorical value even though it's numeric but this this looks this makes our data set looks quite ready for consumption by a very simple um logistic regression algorithm so i'm going to click add and maybe one more one more step um we if you remember when we were trying to plot uh lead time against average daily rate there was an outlier so maybe we could um we could do something like handle handle outliers let's see handle missing handle outliers and the columns that we want to touch i'll go by the guide are going to be lead time time stays in weekend stays in big nights is repeated guests ah that one is one how to encode it we don't have to see that's enough uh fix method we're going to clip them preview and add and so this um this concludes our uh transformation recipe uh author in time we we have our transformations dialed in on the right hand side if i go back to this diagram you can see that we have seven steps and where do we go from here we have we've done data exploratory analysis we've dialed in all the transformation steps so now we have a couple of choices we can actually export our data from the memory this data set that we have um right now uh not export and sync it into either s3 storage or into sagemaker feature store and um it could be useful if your entire data set was uh ingested into this uh into this uh instance of data wrangler and it's it's uh and and it's a one time of effort and you just need it to transform data and take it elsewhere and and play with it experiment with it you have no intentions of of doing it again or in in a repeatable manner so you could you could just use this option but what we are going to do instead is we will export this recipe and orchestration code so we could reuse it elsewhere so we could deploy this um data transformation pipeline in in production or in in another environment where it will run on a schedule or in some sort of event driven manner and there are a number of options here um i'll i'll do the amazon s3 uh option right now it will generate an orchestration code which will initiate uh a data processing job and you can then reuse that notebook or or take that code put it elsewhere another option if you are practicing mlaps would be to instead generate sage maker pipeline step um this kind of falls outside the scope of this workshop but there are um best practices on how to build sagemaker pipelines which encapsulate not only data pre-processing but also model training bias analysis explainability analysis model deployments so that would be useful if if you were to integrate this process into a pipeline but for now let's go with the with this simpler option a a data wrangler job a processing job which we can run using this orchestration code on demand so i'll click that and what you see is that you are taken to [Music] a notebook a jupiter notebook that was just created for you i'll hop back here for a second and show you um one more time what's all the moving parts that are at play right now this is us this is ui we have our data wrangler instance this is where we cooked up our recipe and then indeed all the transformations and exploratory data analysis and now it generated a notebook for us a jupiter notebook and another instance you saw it already it was pre-warmed for us started is now powering this jupiter notebook so we can actually now step through this notebook right now which we're going to do in a minute um we're going to go through these steps and at some point in this notebook we will actually initiate data pre-processing job and that data pre-processing job will actually start the pre-processing uh cluster which will then ingest the the entire data set from s3 um all the 120 k rows it will use the recipe that we created and it will produce the results back to s3 so let's let's go back and step through it so i am going to go ahead and start executing this uh in fact i will i'm going to execute these steps real quick and then i'll come back and talk to them while the job is running so we could then observe the results so i stepped through this through this notebook real quick but let me explain what's happening the the first cell of the notebook is uh is a boilerplate code that you probably have in uh in every notebook that you write yourself it's just uh identifying which s3 bucket will be used for data storage and maintaining some basic variables um this the second cell right here it defines subfolders in the bucket and it begins to define our processing job parameters in this particular case we are spelling out the location of the output file in our processing job you can skip that it's it's it's at this point it's not relevant to us but um destination s3 output pass is important this is where our transformed data will be stored and we're going to see that later on in the output in in fact i think it's it's produced right here it's showing this is where our transform data will land in s3 next uh the the flow file that we created here in data wrangler let me expand this this hotel's that flow in fact let's see if we can take a peek inside this hotels.flow if i was to open it with an editor you can see that this file is actually human readable it spells out the steps uh recipe steps that processing job will take in order to transform our data you can see that the csv file is referenced here then sagemaker spark job will do the inference and casting of of the fields and these are the fields name uh on the data set and the next step visualization we built one of the visuals the pre-processing job won't really build the visualization but as you keep scrolling there will be transformation steps for dropping columns from for imputing values and things like that so everything that we've done as we crafted our recipe it's all been captured here in this flow file so going back here this flow file now needs to be uploaded to s3 because data processing job when it runs it will expect that that recipe to be in s3 and that's exactly what's happening here we are uploading the the flow file to s3 let's see what else loading flow files from current notebook data wrangler flow this is where it gets placed next we are defining the inputs for the for the for the processing job when it runs where will it take the inputs from and [Music] now these are definitions of the actual uh job itself the the rather middleware that will be used to run this job first of all the container for for data wrangler processing job is referenced here sagemaker datawrangler container the uri where to grab it from which includes the exact version the instance count how many parallel instances will be spun up to actually crunch throughout data the type of the instance that will be used in that cluster the size of the ebs volume that will be attached and then the output content type um and finally the creation of the job we define the processor those of you who have worked in amazon sage maker already the the concept of processors and estimators is familiar to you if you've never worked in siege maker before this this might look new to you but uh if if you go through the uh sagemaker sdk if you look at sagemaker's decay on on how to start um training jobs or processing jobs you'll notice the pattern is quite similar you define a processor whether it's a training estimator or a script processor and then you actually either call fit on training job or you call a run method to run a processing job and that's what's happening here we populate that all these parameters already the instance count instance types volume size all that and this line right here actually initiates the job run and you can see the output that the data wrangler flow processing job was started if i was to go back to my sage maker console i should be able to see that processing job and how it's running so here under process in processing jobs you can see that it is it's been running for five minutes and in fact let's go inside it and it's in progress right now and once it's finished we should be able to find the output right here our data set will be transformed and sent to this output right here now once the job finishes we can examine the the location of the data file we can take a look inside and see what transform data looks like and optionally here you can actually proceed with the training of a model again this entire notebook was automatically generated by datawrangler based on the the selections and the choices that we made the types of transformations we dialed the type of model that we selected that we were going to build and so in in this case it chose to run the xg boost to train xg boost model and you can see uh it defines it has some definitions for the job here's it kicks off the training there are ways to actually initiate training directly from data wrangler as i mentioned earlier there is a a menu item here that's a strain model and if you come here you can train the model from here this is um somewhat anything that you do here in datawrangler might actually be somewhat redundant if you're going to end up training a model using autopilot the reason i say that is because sagemaker autopilot has its own data preprocessing pipelines so you don't necessarily have to do all the transformations by hand if if you want to use autopilot you can just import data set into data wrangler skip exploratory data analysis skip transformations come come to this menu select this option train click export and train and this will kick off autopilot and autopilot will inspect and analyze your data it will come up with a dozen of potential candidate pipelines pipelines which will include different methods pre-processing methods different model types and then it will execute them all on your behalf allow the data to be pre-processed models to be trained hyper parameters to be optimized in the end it will then compare all the models find the best one and come back to you and say hey based on all these 10 different pipelines and all these pre-processing techniques we've tried and all these hyper parameters we've explored this was the best performing model it came out of this pipeline which used this kind of transformations you can still pre-process data for it if you'd like but really there is there is no need to do that let's see if our job finished here or not i'm going to click refresh and looks like the job has completed so let's go look at um at the output um that we produced it's going to be in the us1 bucket export flow output so i'm here export flow output and default csv file i'm going to use the same method just query s3 so with s3 select i'll grab top i don't know 10 lines from the file run see what comes up and this is our pre-processed data you can see that the values there are no categorical values anymore all the most of the numerical values have been normalized you can see how one hot encoding is here so that's uh that's the data set and that is the output of our job so um where do you go from here um the couple of take-up takeaways that i want to re-emphasize one more time is that datawrangler is about authoring recipes you can while you can bring in data uh and do interactive um transformations and then save it uh it's definitely available and possible the main power of datawrangler is the fact that you could just operate on a sample data come up with a recipe and then datawrangler will generate orchestration code for you to apply that recipe in any other environment in in production staging pre-staging in a repeatable manner and you can version control the code that is being produced by datawrangler you can customize it you've seen the recipe file that it can be manipul