The problem of weighting the type 1,2 errors on binary classification came up in a forum I visit.

My solution:

```# normal log-loss
ll &amp;amp;lt;- function(y) function(p) -(y * log(p) + (1-y)*log(1-p))

plot(ll(0),0,1,col=1,main="log loss",ylab="loss",xlab="p")
legend("topleft",legend = c("y=0","y=1"), lty=1, col=1:2, bty="n")

# cost weighted log-loss
cwll &amp;amp;lt;- function(y,cost) function(p) -(cost * y * log(p) + (1-cost)*(1-y)*log(1-p))

plot(cwll(0,0.1),0,1,col=1,main="cost weighted log loss\n(cost=0.1)",ylab="loss",xlab="p")
legend("topleft",legend = c("y=0","y=1"), lty=1, col=1:2, bty="n")
```

Here we can see the different loss behaviours

We will try to classify the Virginica species from the iris dataset

```# let's take the iris data set with a species that is hard to differentiate by sepal length and petal length
d <- transform(iris, y = Species == "virginica")
plot(Sepal.Length~Petal.Length,data=d,col=0,pch=21,bg=rgb(0,y,0), main="is it Virginica?")
legend("topleft",legend=c("virginica","others"),pch=21,col=0,pt.bg=c("green","black"),bty="n")
``` And now the loss functions:

```# normal loss function
Loss <- function(par, data) {
xb <- cbind(1,d\$Sepal.Length, d\$Petal.Length) %*% par
p <- 1 / (1 + exp(-xb))
sum(-(d\$y * log(p) + (1-d\$y)*log(1-p)))
}
# cost weighted version, cost refers to error on the virginica species
cwLoss <- function(par, data, cost = 0.01) {
xb <- cbind(1,d\$Sepal.Length, d\$Petal.Length) %*% par
p <- 1 / (1 + exp(-xb))
sum(-(cost * d\$y * log(p) + (1-cost)*(1-d\$y)*log(1-p)))
}

beta <- optim(par=c(0,0,0),fn=Loss,data=d)\$par
cw.beta <- optim(par=c(0,0,0),fn=cwLoss,data=d)\$par

xRange <- range(d\$Petal.Length)
yRange <- range(d\$Sepal.Length)
X <- seq(xRange,xRange,by = 0.05)
Y <- seq(yRange,yRange,by = 0.05)
z <- outer(X,Y, FUN = function(x,y) -(beta + x * beta + y * beta))
cwz <- outer(X,Y, FUN = function(x,y) -(cw.beta + x * cw.beta + y * cw.beta))

contour(X,Y,z, add=TRUE,levels = 0,labels = "normal",labcex = 1)
contour(X,Y,cwz, add=TRUE,levels = 0,col=4, labels = "cost-weighted",labcex = 1)
``` Statisticians, gather!

In a first of its kind public statement, the American Statistical Association (ASA), has addressed the worrying misuse and misunderstanding of the famous p-value. Some niches in science mistreat it while others ignore it completely.

Here is the link to the full statement, and a link to an abbreviated message, it is separated in two parts. The first, explains the reasons and process that led the ASA to release the statement, including a reference to the ‘reproducibility crisis’ reported in a paper last year . The second is the statement itself regarding the meaning of the p-value and recommended use.

It doesn’t address hard questions, like multiple comparisons or potential comparisons, but an interesting read altogether, especially for us who need to infer x>>0 times a day. They provide a long list of related papers if you are interested in reading them. I suggest to read Yoav Benjamini’s and Tal Gallili’s joint post on the matter.

In the 177 years since it’s foundation, the ASA has not publicly addressed such a fundamental scientific subject. This is tantamount to the Archmaesters of Hightower commenting to Westeros on how to ‘count coppers’.

 Peng, R. (2015), The reproducibility crisis in science: A statistical counterattack. Significance, 12: 30–32. doi: 10.1111/j.1740-9713.2015.00827.x

2 Years, are a long time. For a blogger wannabe for sure. 2 years since my last post, a lot has happened and my skills have broadened beyond R, so this R dedicated blog, might not speak about R so much, maybe some other things.

I’ve received some good comments from readers and friends on my contribution to the use of the Vertica R package, I even had an interviewee that shook my hand and said my posts were great help to his team. After using the package extensively to solve a few ML use cases, I must conclude that it is not the tool you would want to use. I’d dedicate this post to explain why in a few points I believe are important.

### 1 – Lack of community

Product communities are important, I never paid attention to it in the past, but today I suggest to anyone considering a new technology, look into the community of that product, who are the users, and how many are there. How well do they manage Braistorming and tickets. HP’s Vertica is not an open source product, so you would expect a smaller community compared to, say, MySQL, but still there is not much buzz around the search term ‘R and Vertica’.

As of writing this post, on incognito mode, my post from June 2014 is still ranked #1 on google .

As flattering as that may be, it is not a good sign for the product, you’d want to see stackoverflow questions and linkedin groups and a multitude of HP owned documents on the subject. Without a big community of developers, bugs don’t get attention, packages and solutions are not created and you find yourself stuck.

The number of tags in Stackoverflow for Vertica alone, without, filtering for R related issues is 439, vs. ~100K for Spark and ~400K for MySQL, get the picture?

### 2 -Lack of non-SQL API

Vertica’s great, Vertica’s cool, but, it only has a SQL API. Now, I can say that a LOT can be done with a SQL, and packages like sqlalchemy or frameworks like Apache Spark allow connections via JDBC and then a layer of their own sophistication and programmability, but then Vertica doesn’t really smoothly comply with sqlalchemy and there is no simple connection to Spark.

Also, SQL means that Vertica is stateless, you cannot cache your data, you cannot loop over it, and basically you cannot develop. So you’ll need some wrapping application to call Vertica for you with a JDBC connection – aha! brilliant! But, if you need to write an application that calls it, why not use open source framework like Apache Spark, and have your data in Hadoop instead of paying for Vertica’s license.

### 3 – Vertica is parallel, but, it doesn’t apply so much to R

Vertica segments or copies it’s data to many nodes and uses novel algorithms to read records quickly from zip, it can do some real magic with most complex queries I’ve ever seen. But when you call R through Vertica, the data gets unzipped then piped into an R process which is:

1. Limited by local resources.
2. Processes cannot communicate between or within nodes. You cannot broadcast any data. As you can do with spark.

So what is parallel here? Let’s see an example, assume you have a table with some grouping column ‘G’ and then features columns {x1, x2, ….xp} and an output column ‘y’. You’d like to run some, very general, R model that has the formula:

`y~x1+x2+...+xp`

for every group in ‘G’.

Vertica will split/shuffle the data by group to all nodes and then pipe it to R for analysis, the R instances (1 per node) will work in a queue until finished then each will return results back to Vertica. Why is this bad? You cannot run a huge regression that is truly parallel with something like BFGS or SGD this way, you are limited by the size of data that the R instance can handle, which will not be in the 100s of GB, sadly it would be much smaller.

## To Conclude…

Vertica is still my first choice DBMS for analytics, used and maintained correctly it’s amazing. If your team has need for scalar functions that are dependent on R and are not readily available in Vertica then I recommend to use the R package, otherwise, don’t.

I’m guessing you can do this in C++, but hey… it works for me.

You’ll notice 3 functions that need to be created, the main function which will be called from Vertica, the params functions which is optional and holds the config of parameters optionally pased to the function. And finally, and mandatory, is the factory function which tells Vertica what to expect when running ‘group_concat’.

```group_concat <- function(x,y) {

options(useFancyQuotes=FALSE)

# initialize parameters
sep <- ifelse(is.null(y[['delimiter']]),',',as.character(y[['delimiter']]))
if(is.null(y[['quote']])) y[['quote']] <- 0
doQuote <- ifelse(y[['quote']]==1 ,TRUE,FALSE)

if(doQuote) ret <- paste0(sQuote(x[,1]),collapse=sep)
else ret <- paste0(x[,1],collapse=sep)

ret

}

group_concat_params <- function() {
params <- data.frame(datatype=c("varchar", "int"), length=c(100,NA), scale=rep(NA,2),name=c("delimiter","quote"))
params
}

group_concat_factory <- function() {
list(
name=group_concat
,udxtype=c("transform")
,intype=c("any")
,outtype=c("varchar(65000)")
,parametertypecallback=group_concat_params
# ,volatility=c("stable")
# ,strict=c("called_on_null_input")
)
}
```

After distributiong the file into the same directory on all nodes.

In Vertica run :

```create or replace library r_func as '.../group_concat.r' language 'R';
create or replace transform function group_concat as name 'group_concat_factory' library r_func;

select group_concat(id using parameters delimiter='|', quote=1) over() from sometable;
```

I’ve been spending the last few months working my way through the integration of R and Vertica, and will try to keep here things that I find handy. I’m quite sad to see there is not much about this Vertica feature on the web, that’s a little disappointing. But, it didn’t stop us from creating a scalable statistical model learning machine out of this feature (I will write about it in later posts…).

For those of you who don’t know HP Vertica, it is a powerful columnar DBMS.  I’ve worked with two installations of it and me and my colleagues are very impressed with it. For those of you who are familiar with PostgreSQL, you will find many things similar  as both products were invented by the same guy.

That’s all the intro I’m going to give since we pay them and not the other way around 🙂

I suggest people reading this post to read through the chapter called “Developing a User Defined Function in R” in “HP Vertica 6.1.x (or higher) Programmer’s Guide” I hope you are familiar in vsql and R.

### Short example – normal inverse function in Vertica

Vertica and R communicate via User Defined Functions (UDFs) that are written in the R language. The columns selected in Vertica are passed to R as a data.frame, the functions must treat their main argument as a data.frame. Here is an example that creates an inverse normal CDF function, x is the data.frame passed from Vertica, in this case it is a 3-column data.frame with the percentile, mean and standard deviation.

```# normal inverse
norm_inv <- function(x) { # 1 - percentile, 2 - mean, 3 - sd
apply(x,1,function(i) qnorm(p=i[1L],mean=i[2L],sd=i[3L]))
}

norm_invFactory <- function() { # this is the factory function that 'links' between R and Vertica
list(
name=norm_inv
,udxtype=c("scalar")
,intype=c("float","float","float")
,outtype=c("float")
)
}
```

You create the file above and place somewhere on your machine, then load this function to the Vertica database :

```
MyDB=> CREATE LIBRARY r_func AS '/home/dbadmin/norm_inverse.R' LANGUAGE 'R';

MyDB=> CREATE FUNCTION norm_inv AS NAME 'norm_invFactory' LIBRARY r_func;

MyDB=> select norm_inv(.25,0,1);
norm_inv
--------------------
-0.674489750196082
(1 row)

```

### More about User Defined Functions

Imagine being able to implicitly parallelize an R function across an infinite amount of segments and data. This is basically the promise behind the R language package for Vertica.

What is parallelized exactly? Vertica allows you to partition the data sent into R, it implicitly works out how to divide the load between nodes and the nodes’ cores, so you don’t have to work out elaborate code. Each R instance is run independetly from other instances, so you cannot parallelize ,say, a single lm() function, but rather perform multiple ones at once.

The user defined function are loaded into Vertica using CREATE LIBRARY syntax and by writing R code that has two main parts, as you might have noticed above:

1. A main function – the main function is what does the work
2. A factory function – tells the Vertica engine about the input and output to expect.

### source() example

Using R’s source() function is possible through an R UDFs in Vertica. Which is a very useful ability for large projects with a lot of code you can then change the sourced code without changing any part of the main functions. Here is how.
Create some R file called ‘foo.R’ with a variable called ‘bar’.

```## Start of R code
bar <- "we've done it!"
## End of R code
```

Create the main and factory functions for in an R file

```# Main function
sourceTest <- function(x)
{
source('foo.R')
return(bar)
}
# Factory function
sourceTestFactory <- function()
{
list(
name=sourceTest
,udxtype=c("scalar")
,intype=c("char")
,outtype=c("char")
)
}
```

In vertica run :

```MyDB=> CREATE LIBRARY RsourceTest AS 'sourceTest.r' LANGUAGE 'R';
MyDB=> CREATE FUNCTION sourceTest AS NAME 'sourceTestFactory' LIBRARY RsourceTest;
MyDB=> SELECT sourceTest ("");
sourceTest
--------------------
we've done it!
(1 row)
```

### Pre-requisites for the R language pack

The vertica programmer guide instructs you to install a version of libgfortran. Aside from that you may notice that packages that rely on gfortran such as “xts” require installing other parts :

```yum install gcc-gfortran
yum install gcc-c++
```

### Installing the ‘forecast’ package for Vertica

The ‘forecast’ package, by Rob J Hyndman, is great for many time series analysis. Connecting it with Vertica is very powerful for creating forecasts out of your data. Trouble is, the R version used in Vertica is 3.0.0 so you will have to get the older version of forecast 4.8. In short :

```wget https://stat.ethz.ch/CRAN/contrib/main/Archive/forecast/forecast_4.8.tar.gz
```

And then in R:

```install.packages("tseries")
install.packages("fracdiff")
install.packages("/root/forecast/forecast_4.8.tar.gz")
```

Voila. Next time I’ll show some more elaborate examples of transform functions that I use.

### Motivation

How many times do users visit your site? A very relevant question in web analysis.
One major issue that biases your estimation is the users who save no cookies, these users change their ID on every page visit so they look like 1 time visitors and thus inflate the proportion of 1 in the distribution. This has an enormous effect on the bounce rate you calculate to your site.
Another minor issue, which is a by product of the solution I’m about to offer, is that most distribution functions have a support of x>=0, while visits start at 1. Read on to see the suggested solution.

I’ve stumbled upon this issue a few times and would like to suggest the following solution to estimate the latent proportion of 1 time visitors via maximum likelihood estimation.

### Assumptions

1. There exists a constant proportion (P) of users who don’t save cookies.
2. The number of visits to the site by users who do save cookies (X) follows some known distribution function (f) who’s parameters are unknown. The distribution is truncated at 0, as we will never see the users who visit 0 times. The CDF for f is denoted as F.

We define Y as the distribution of visits to your site :

`Pr(y=1) = P + (1-P) * f(1) / (1-F(0))`

For Y=y other than 1 :

`Pr(Y=y) = (1-P) * f(y) / (1-F(0))`

You might ask yourself why f(y) is  weighted with 1/(1-F(0)), since we are looking at the truncated version of the distribution this weighting factor is essential to keep the integral of the truncated distribution at 1. I’ll leave you to figure out the math.

### Example

Here is an example when assuming the distribution f is Poisson.

first we construct the likelihood function:

```Ind <- function(l) {
if(is.logical(l)) as.numeric(l)
else stop('"l" must be logical')
}
fy <- function(x, lambda, log = FALSE) {
if(!log) { Ind(x>=1) * dpois(x, lambda) / ppois(0, lambda, lower.tail = FALSE)}
else { Ind(x>=1) * ( dpois(x, lambda, log=TRUE) - ppois(0, lambda, lower.tail = FALSE, log=TRUE))}
}
Lik <- function(par,x) {
p <- par
lambda <- par
n1 <- sum(x==1)
n0 <- sum(x!=1)
x0 <- x[x!=1]
n1 * log(p + (1-p) * fy(1,lambda)) + n0 * log(1-p) + sum(fy(x0,lambda,log=TRUE))
}```

Let’s create some data :

```x <- c(rpois(5000,5),rep(1,600)) # a sample of 5000 values from a Poisson distribution with lambda = 5, artificially inflated with 600 1s.
par <- c(mean(x==1),mean(x)) # starting values for the estimation process
parOptim <- optim(par,fn=Lik,x=x,method="Nelder-Mead",control=list(fnscale=-1)) # using optim() to find the MLE
parOptim\$par
 0.1092563```

This means the estimated P is 10.9% of inflated one time visitors. Be sure to remove these guys from the distribution of visitors you get. But be sure to test a few distributions and find a way to judge between them prior to estimating the final numbers.

Good luck.

The coalesce function is a recursive null filler very common in every database software, however R seems to be missing this simple function. Here is my suggestion :

``````coalesce <- function(x,...) {

fillerList <- list(...)
y <- try(y <- unlist(..1))
if(class(y)=="try-error" | length(y)==0L) {
x <- x
}
else if(length(y)==1L) {
x[is.na(x)] <- y
}
else {
x[is.na(x)] <- y[is.na(x)]
}
# recursion
if(length(fillerList)-1L<=0L) {return(x)}
else {return(coalesce(x,fillerList[-1]))}
}``````