Build machine learning models in Google Sheets

Key Takeaways

if you're new here my name is and I'm a former Professor but currently working in Tech as a senior developer Advocate and in this particular video I'm going to talk about a no code tool that will allow you to build machine learning models and so if this sounds like fun then this feels for you because today we're going to talk about simple ml for Sheets you've heard right we're going to use this particular add-on to build machine learning models right inside Google Sheets and no coding required so without further Ado we're starting right now so you've heard right we're going to do machine learning inside Google Sheets and the tool that we're going to be using today is called Simple ml for sheets so let's begin by showing you how you could get started so the first thing is I'm going to show you how you could import a CSV file so for that I'm going to go to the data Professor GitHub and we're going to the data repo and for this we're going to use a regression data set and we're going to use the Delani solubility with descriptors right here I'm going to click on the Raw and then I'm going to export this out as you can see here this is a CSV file and we have a total of about five columns here let's export it out so I'm going to say save page as and then I'm going to download it to the download folder let's just call it delaney.csv I'll save it to the desktop save and then I'm going to open up a blank Google Sheets here click on file open go to upload and then I'm going to essentially open up the file go to the desktop click on the data set delaney.csv click on open so the file size is about 56 kilobytes and this is the data go ahead and click on open with and then click on the Google Sheets okay and we have it here so in order to get the simple ml for Sheets installed we're going to go to the extensions and then click on add-ons and then click on get add-ons wait for it to load and in search apps bar here you want to type in simple ml hit on enter let me try again with this ml so looking from here simple ml here doesn't have empty space but apparently this one has so you need to search for ml or just simple and then space ml let me see yeah and then it'll detect that okay and right here click on it and then click on install and then it's asking for permission and go ahead and click on continue so give it permission choose your account click on allow and it's installed click on done close this let's say extension okay we only see the help we want to see also the start so let's refresh the page again click on refresh button go to extensions again right here simple ml for shoes now we have start okay so click on start and it's loading up and it's here on the right panel there you have it this is the simple ml panel and so let's click on here so these are all of the capabilities that this tool can do so it also helps you with performing data pre-processing so apparently you could do predict missing values so let's say that you have a couple of missing values let me just delete some at random and then you could select predict missing values here and then click on predict oh okay um apparently I have to click on the do the selection of the particular column that I want to analyze so here I'll need to click on the mo weight column the column that we deleted data at random and then click on predict when it's doing its pre-processing and there you go you have the predicted values which is corresponding to the same missing value here to the left okay so this is the first capability is attempting to predict the missing value let me copy this first and then I'm going to hit on command Z or Ctrl Z in order to undo it because I want the values back and then I'll just paste the ones that I just copied so it predicts it to be 105.9 and then the original value is 98. it predicts it to be 248 and the original value is here 273 and the original value is here so I'm guessing that it's using all of the inputs here in order for it to predict the mo weight so the mole weight apparently becomes the Y and columns a c d e probably becomes the X variables let's have a look at other so yeah you could play around with the other ones so let me go ahead and click on the train model so let me just delete this column here okay and let's see let's give it a name let's call it model one and so the label that we want to use here is the last column which is log s so we're gonna predict the log s value click on train and it's training the model here with 1144 samples which are corresponding to the number of rows here 1144 not including the first row which is the header because we have one one four five and the first row is to Heather so we minus one and we get 1144. and four features were used which corresponds to columns a b c d alright and it saved the model already and you could go ahead and hit on the close button right now we have already trained the model let's make predictions using the model the trained model so click on make predictions with a model and then this is the model that was selected model one and then click on predict so apparently it's going to use all of the data samples here for the prediction and yeah we have the prediction now so let's do a relative correlation between the log s and the predicted log s so we want columns e here so it's E2 until I think it's e 1145 right here so it's E2 until e 11 45 comma and then column F so it's F2 until F 1145 closing parenthesis hit on enter and we don't want the suggestion here all right and so the correlation value is 0.97 and let me show you for making the scatter plot click on insert chart all right and we have the scatter plot between the log s and the predicted value so you can see that is roughly approaching one as we can see from the value so the scatter plot is showing a very good correlation between the actual value and the predicted value let's close this one so let's see all of the other features here we have already trained the model we made predictions using the model let's see evaluator model let's have a look showing model evaluation all right this is pretty cool so number of predictions is 1144 the task here is regression the rmse value is 0.42 397 and the confidence interval at the 95 is here depicted here and then we have some data visualizations here the residual histogram of the false positive rate versus the true positive rate we have the recall versus Precision ground shoot versus prediction okay comes in handy these are the model evaluation and you can click here in order to find out how to interpret that here is in the very well documented website for the simple ml it's here on the left evaluate the model and all of the other features you could also read about it in this webpage here let's have a look at the other ones understand a model let's have a look at that for model 1 use the data in the current sheet to better explain the model let's go ahead and click on understand all right here we have the metadata quality ornaments e values here data sets so that's the metadata on the data set and it's giving us like the relative statistical parameters of each of the columns let's have a look at the variable importance here variable importance mean increase in rmse mean Min dab and then let's have a look at the predictions okay oh we need to activate it in order to have the prediction analysis plot model okay we need the decision Tree in order to visualize this let's click here again use the data in the current sheet to better explain the model this sheet should not be the one used to train the model oh we actually used it to train the model so let's just skip it for now manage models it's already saved into your Google Drive you could either remove or you could rename it let's have a look at export a model okay so you could export the model to Google collab you just use data as example let's check it out click on exports okay this is super cool you could easily build the model and then you save it out export it into Google codelab let's copy and let's click on this link to open up a Google collab notebook all right let's copy paste here let me separate this into here so I think we've used random forest model using all right how about this one building an ml model using simple ml four sheets and this is the first one for installing the prerequisite libraries oh okay and it's including some code for mounting data from my Google Drive all right I like to run it separately so let me do that I'm going to copy the code here paste it copy paste it here all right cool so it's doing the installation let's give it some time in the meantime I'm going to just format The Notebook here copy out the code and I'll add it here make it a bit smaller make this Bode S2 heading here paste it the last one here okay give it some more time all right and so it's installed let's do the transferring of the model from Google Drive to collab and click on connect and it's asking for permission gift permission and it's mounting your Google drive into the collab all right cool so it's mounted already and we're going to load up the model and the model is load up let's make the predictions using the example data here all right pretty cool and here are the predictions minus 2.22577 minus 2.109 minus 2.16 so these are the predicted log s values and that's pretty cool for using Google Sheets to build your machine learning model and for now let's have a look here and train and model I think they have three machine learning algorithms so if you click on Advanced options here learning algorithm has gradient boosted trees random forest and decision tree so the one that we just used right now is the gradient boosted tree and so I'll provide you the link to this particular example data set and also the link to this particular documentation website for the simple amount for sheets and so there you have it you can now build machine learning models using the simple and math for Sheets add-on right inside Google Sheets and so if you're finding value in the video please give it a thumbs up subscribe if you haven't already and make sure to hit all notifications to be notified of the next video and so the best way to learn data science is to dive in head first and get your hands dirty with real world data sets and projects and please enjoy the journey thanks for watching until the end of the video please help us out by putting in the comments section simple ML and let me know what you are building and I'll try to respond to all of the comments in this video and as always the best way to learn data science is to find a learning body so that you'll be accountable in your data science Learning Journey and as always please enjoy the journey