A Fine Slice Of SQL Server
The main steps that we will review are the following:
splitting paths depending on purchases counts
replacing some channels/touch points
a unique channel/touchpoint case
consequent duplicated channels in the path and higher order Markov chains
paths that haven’t led to a conversion
customer journey duration
attributing revenue and costs comparisons
There’s a lot there, and I like the practical explanations of issues when dealing with a real business problem.
Comments closedHossein Falaki and Xiangrui Meng show how to use sparklyr on a Databricks Spark cluster:
We collaborated with our friends at RStudio to enable sparklyr to seamlessly work in Databricks clusters. Starting with sparklyr version 0.6, there is a new connection method in sparklyr:
databricks. When callingspark_connect(method = "databricks")in a Databricks R Notebook, sparklyr will connect to the spark cluster of that notebook. As this cluster is fully managed, you do not need to specify any other information such as version, SPARK_HOME, etc.
I’d lean toward sparklyr over SparkR because of the former’s tidyverse-centric view.
Comments closedSteph Locke shows how to combine R models and SQL Server temporal tables for versioning:
If we’re storing our R model objects in SQL Server then we can utilise another SQL Server capability, temporal tables, to take the pain out of versioning and make it super simple.
Temporal tables will track changes automatically so you would overwrite the previous model with the new one and it would keep a copy of the old one automagically in a history table. You get to always use the latest version via the main table but you can then write temporal queries to extract any version of the model that’s ever been implemented. Super neat!
I do exactly this. In my case, it’s to give me the ability to review those models after the fact once I know whether they generated good outcomes or not.
Comments closedIt looks like there is a header/title at [1], numeric grouping at [2] “1.\tINFECTIOUS AND PARASITIC DISEASES”, subgrouping by ICD-9 code ranges, at [3] “Intestinal infectious diseases (001-009)” and then 3-digit ICD-9 codes followed by a specific diagnosis, at [10] “007\tOther protozoal intestinal diseases”. At the end we want to produce three separate data frames that we’ll categorize as:
Groups: the title which contains the general diagnosis grouping
Subgroups: the range of ICD-9 codes that contain a certain diagnosis subgroup
Classification: the specific 3-digit ICD-9 code that corresponds with a diagnosis
It’s a beefy article full of insight.
Comments closedIlya Kashnitsky shows how to embed subplots within a map using ggplot2:
So, with this map I want to show the location of more and less urbanized NUTS-2 regions of Europe. But I also want to show – with subplots – how I defined the three subregions of Europe (Eastern, Southern, and Western) and what is the relative frequency of the three categories of regions (Predominantly Rural, Intermediate, and Predominantly Rural) within each of the subregions. The logic of actions is simple: first prepare all the components, then assemble them in a composite plot. Let’s go!
This is very useful information, well worth the read.
Comments closedSPARKR & PYSPARK
Most data scientists use R & Python and with SparkR & PySpark respectively they can continue to leverage their familiarity with the R & Python languages. However, they need to use the Spark API to leverage Machine learning with Spark and to take advantage of distributed computations. Both SparkR & PySpark are evolving rapidly and SparkR now supports a number of machine learning algorithms such as LDA, ALS, RF, GMM GBT etc. Another key improvement in SparkR is the ability to deploy a package interactively. This will help Data Scientists deploy their favorite R package in their own environment without stepping on other users.
PySpark now also supports deploying VirtualEnv and this will allow PySpark users to deploy their libraries in their own individual deployments.
There are several large changes, so check it out.
Comments closedIlya Kashnitsky shows how to create custom subplots using ggplot2:
Actually,
ggplot2is a very powerful and flexible tool that allows to draw figures with quite a complex layout. Today I want to show the code that aligns six square plots (actually, maps) just as in the figure above. And it’s all about the handy functionggplot2::annotation_custom(). Since I used the layout more than once, I wrapped the code that produced it into a function that takes a list of 6 square plots as an input and yields the arranged figure with arrows as an output. Here is the commented code of the function.
This is the difference between “I’m just going to throw some stuff on there” (which is how I tend to operate) versus well thought out visual layout.
Comments closedDavid Smith announces Microsoft R Open 3.4.0:
R 3.4.0 (upon which MRO 3.4.0 is based) is a major update to the R language, with many fixes and improvements. Most notably, R 3.4.0 introduces a just-in-time (JIT) compiler to improve performance of the scripts and functions that you write. There have been a few minor tweaks to the language itself, but in general functions and packages written for R 3.3.x should work the same in R 3.4.0. As usual, MRO points to a fixed CRAN snapshot from May 1 2017, but you can use the built-in checkpoint package to access packages from an earlier date (for compatibility) or a later date (to access new and updated packages).
The version of Microsoft R Server shipping with SQL Server 2017 will still be based on 3.3.3, but I’m going to guess that a new version of Microsoft R Server supporting 3.4.0 will ship in the next several months.
Comments closedMala Mahadevan explains Fisher’s Exact Test and provides examples in T-SQL and R:
The decision rule in two sample tests of hypothesis depends on three factors :
1 Whether the test is upper, lower or two tailed (meaning the comparison is greater, lesser or both sides of gender and speaker count)
2 The level of significance or degree of accuracy needed,
3 The form of test statistic.
Our test here is to just find out if gender and speaker count are related so it is a two tailed test. The level of significance we can use is the most commonly used 95% which is also the default in R for Fischer’s Test. The form of the test statistic is P value. So our decision rule would be that gender and speaker category are related if P value is less than 0.05.
Click through for the R code followed by a code sample which should explain why you don’t want to do it in T-SQL.
1 CommentSomehow I have become the R DBA at my job which I don’t mind, I plan on taking Microsoft’s Professional Program on Data Science to be familiar with it. But recently I’ve had to upload files to our R servers which the first time wasn’t too bad. Copy these files to six different servers but come the second time around it became apparent that the Predictive Analytics Manger was going to be asking me to do this more frequently than I wanted to to it manually. So I wrote a quick PowerShell function to take care of this added to our module we use in house. It unzips the file provided to the correct location. It does assume you have administrative rights to your server i.e. you can use the admin shares (c$) for example on the server. You will need to get the function Get-CMSHost from my Running SQL Scripts Against Multiple Servers Using PowerShell post to run the code below.
Click through for the script. This is particularly useful for deploying in-house packages and you don’t want to set up a miniCRAN.
Comments closed