Mike's Website – Page 3 – Scientist, Techie, Avid Squirrel Whisperer

Recommender System in Python – Simple Example

August 9, 2020October 23, 2022 by Mike Comment Closed

It's actually very easy to build a simple recommendation system in Python. I'll show you how to do it utilizing a movie dataset with various user ratings. In this case we're just comparing two movies against all others to recommend what a user might like if they were to enjoy Star Wars or Liar Liar. ...

Principal Component Analysis in Python – Simple Example

August 5, 2020October 27, 2022 by Mike Comment Closed

The greatest variance is shown on an orthogonal line perpendicular to the axis. Likewise, the second greatest variation on the second axis, and so on. This allows us to reduce the number of variables used in an analysis. Taking this a step further - we can expand to higher level of dimensions - shown as "components". If we utilize a dataset with a large number of variables, this helps us reduce the amount of variation to a small number of components - but these can be tough to interpret. A much more detailed walk-through on the theory can be found here. I'm going to show how this analysis can be done utilizing Scikit learn in Python. The dataset were going to be utilizing can be loaded directly within sklearn as shown below. ...

Refresh all views in SQL Server database

August 3, 2020August 11, 2020 by Mike Comment Closed

One problem I've had recently is a SQL Server instance in which the views were constantly cached and needed to be refreshed. Normally to do this you would run the build in procedure: exec sp_refreshview MyView However - I had the need to run this on all my views daily, and we constantly had new views. You can use the stored proc below to simply loop all the views and run as needed. create procedure usp_refreshview as declare @sqlcmd nvarchar(max) = '' select @sqlcmd = @sqlcmd + 'exec sp_refreshview ' + '''' + name + '''; ' from sys.objects as so where so.type = 'V' print @sqlcmd if len(@sqlcmd) > 0 exec(@sqlcmd) To run, simply exec: exec usp_refreshview ...

K Means Clustering in Python – Simple Example

August 2, 2020August 11, 2020 by Mike Comment Closed

Just a little example on how to use a K Means Clustering model in Python. Or - how to take data and predict likely assignments among groupings of a given number of clusters. In this example we’re utilizing a fairly generic dataset of universities in which we're going to predict clusters of Public or Private universities (2 clusters). In the case of this data we know if they are Public or Private so we can actually evaluate the accuracy of the model, which would not be a common ability in most real world applications. The dataset can be found on my github here. ...

Rows to Comma Separated Lists in SQL Server

July 29, 2020August 11, 2020 by Mike Comment Closed

A useful bit of syntax in the case that you end up with multiple levels of granularity in a dataset, and you want to collapse it back down. One solution would be to turn your rows into columns (pivoting), or put your row values into a single column separated by commas (or pipes, etc.). Below I'm using the AdventureWorks2017 database to show how to do the latter. SELECT [CountryRegionCode] ,StateProvinceCode FROM [AdventureWorks2017].[Person].[StateProvince] Here it produces a gigantic list of countries with associated state/provinces. So let's collapse that down... SELECT outerQ.[CountryRegionCode], STUFF(( SELECT DISTINCT ',' + subQ.StateProvinceCode FROM [AdventureWorks2017].[Person].[StateProvince] subQ WHERE outerQ.CountryRegionCode = subQ.CountryRegionCode FOR XML PATH('') ), 1, 1, '' ) as LastNameList FROM [AdventureWorks2017].[Person].[StateProvince] outerQ GROUP BY outerQ.[CountryRegionCode]; ...

Grid Searching in Python – Machine Learning

July 29, 2020August 11, 2020 by Mike Comment Closed

So what happens if your ML model is obviously flawed? I.E. it predicts the flip of a coin to always be heads, etc. One potential problem is that the model parameters need to be tweaked from its defaults. A way to fix this problem is to utilize a grid search, in which we test multiple combinations of parameters to see which produce the most accurate results. In this example we're utilizing a breast cancer dataset already present in the scikit learn library. We're going to predict if values fall in the 'target' field or not (present as a binary 0 or 1). ...

Support Vector Machines in Python – Simple Example

July 28, 2020August 11, 2020 by Mike Comment Closed

Just a little example on how to use the Support Vector Machines model in Python. Support Vector Machines simply separate or classify data based on groupings, by dividing up the surface into "hyperplanes" (if the data point is in hyperplane 'A', it's most likely related to that cluster instead of the cluster in hyperplane 'B'). Again - a very simplistic description, but it's not terribly complex to understand at its highest level. A good description in detail can be located here. Interesting to note this can be calculated linearly and non-linearly (particularly in the third dimension). In this example we’re utilizing a cancer dataset that is provided within Scikit learn, and we're going to predict values based on the "target" field therein. The dataset can be imported as shown in the code. ...

K-Nearest Neighbor in Python – Simple Example

July 28, 2020August 11, 2020 by Mike Comment Closed

Just a little example on how to use the K-Nearest Neighbor model in Python. In this example we're utilizing an anonymous dataset with unknown context. All we know is that there is a TARGET CLASS of 0/1 and we want to predict if a row belongs to that class or not. K-Nearest Neighbor functions by comparing a point to a certain number of points around it. It's pretty simple to implement. The dataset can be found on my github here. ...

Decision Trees and Random Forests in Python – Simple Example

July 27, 2020August 11, 2020 by Mike Comment Closed

Just a little example on how to use Decision Trees and Random Forests in Python. Basically - tress are a type of "flow-chart", a decision tree, with nodes/edges to determine a likely outcome. Given that trees can be very difficult to predict, we can utilize Random Forests to take specific features/variables to build multiple decision trees and then average the results. This is a very very brief and vague explanation - as these posts are meant to be quick shots of how-to code and not to teach the theory behind the method. In this example we're utilizing a small healthcare dataset that predicts if spinal surgery for an individual was successful to help a particular condition (Kyphosis). Both methods are shown to see differences in accuracy. The dataset can be found on my github here. ...

Converting all blanks to NULL in a SQL table

July 24, 2020August 11, 2020 by Mike Comment Closed

The bane of my existence, blank values that should be NULL in a database. This happens frequently when importing Excel/Flat files particularly within SQL Server. Running hundreds of UPDATE statements isn't fun, so I use a Stored Procedure with a CURSOR to take care of it. CREATE PROCEDURE [dbo].[usp_getRidOfTableBlanks](@tableName nvarchar(50)) AS DECLARE @colName varchar(255) DECLARE @sql varchar(4000) DECLARE Table_Cursor CURSOR FOR select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=@tableName OPEN Table_Cursor; FETCH NEXT FROM Table_Cursor INTO @colName WHILE @@FETCH_STATUS = 0 BEGIN set @sql = 'UPDATE ' + @tableName + ' SET [' + @colName + '] = NULL WHERE [' + @colName + '] = '''';'; ...