Generating Row and Column Names when using R UDF

Moderator: NorbertKrupa

Post Reply
vioravis
Newbie
Newbie
Posts: 2
Joined: Wed Jul 03, 2013 2:20 pm

Generating Row and Column Names when using R UDF

Post by vioravis » Wed Jul 03, 2013 3:12 pm

Hi,

I am trying to use the R UDF to create a cross tab and have given the R function and the Vertica commands below:

Code: Select all

generateCrosstab <- function(x)
{
	var1 = x[,1]
	var2 = x[,2]
	crossTab <- as.data.frame.matrix(table(var1,var2))
	crossTab
}

crossTab_factory <- function()
{
	inlist <- rep("int", 2)
	outlist <- c("int", "int")
	list(name=generateCrosstab, udxtype=c("transform"), intype=inlist, outtype=outlist)
}

DROP LIBRARY crossTab CASCADE;

CREATE LIBRARY crossTab AS '/home/dbadmin/test/crossTab.r' LANGUAGE 'R';

CREATE TRANSFORM FUNCTION generateCrosstab AS NAME 'crossTab_factory' LIBRARY crossTab ;

SELECT generateCrosstab(column1, column2) OVER() FROM testdb.table;
This query generates the following output:

Code: Select all

 col0  |  col1
--------+--------
 466881 | 655735
  21047 |  28336
(2 rows)
Is there any way to generate the row names and the column names? I have given an example of what I am looking for:

Code: Select all

           setosa versicolor virginica
setosa         50          0         0
versicolor      0         50         0
virginica       0          0        50
Would appreciate any help on this? Thank you.

Ravi

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Generating Row and Column Names when using R UDF

Post by JimKnicely » Fri Jul 05, 2013 3:41 pm

Hi,

I am trying to re-create your example on my system.

Can you post a description of your testdb.table table and the data that it stores?
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

vioravis
Newbie
Newbie
Posts: 2
Joined: Wed Jul 03, 2013 2:20 pm

Re: Generating Row and Column Names when using R UDF

Post by vioravis » Mon Jul 08, 2013 1:36 pm

Hi Knicely,

Thank you for your reply. I started the same discussion in the Vertica community forum as well linking to this post. I have given a link to that post as well that contains more details. (However, I am unable to post to that thread anymore and the problem is still unresolved)

https://community.vertica.com/vertica/t ... sing_r_udf

To answer your question, the table contains varchar or integers. Column 1 and Column 2 could be both varchar or could be both be integers or a combination as well. I am trying to create a cross tab between the two columns. Please let me know if you need any other details.

Thank you.

Ravi

Post Reply

Return to “R Language Integration”