R and Python Integration

R and Python are powerful languages that can be used for more advanced statistical data manipulation such as predictive analytics or to create more specific chart formats. With the R and Python integration, Sisense for Cloud Data Teams will automatically pull the results of a SQL query into R and Python to enable statistical analysis, all within the chart editor.

Note: R and Python Integration is an add-on feature. Site administrators can contact their Account Manager for additional information.

<div>
<UL>
<LI><a href="#PythonChart">Adding Python to a Chart</a></LI>
<LI><a href="#RChart">Adding R to a Chart</a></LI>
<LI><a href="#CodeView">Adding Code to a View</a></LI>
<UL>
<LI><a href="#CodeLimits">Code Limits</a></LI>
<LI><a href="#Materialization">Materialization</a></LI>
<LI><a href="#MoreViews">Looking for More?</a></LI>
</UL>
<LI><a href="#CodeTemplate">Code Templates</a></LI>
<LI><a href="#LimitPreview">Limit Preview</a></LI>
<LI><a href="#Modules">Custom Modules</a></LI>
<LI><a href="#Libraries">Available Libraries</a></LI>
</UL>
</div>
<HR>
<a name="PythonChart"></a>

Adding Python to a Chart

Once a SQL query has been successfully run for a new chart, select the desired language from the dropdown below the SQL editor and click the '+' icon to add a block of code. Sisense for Cloud Data Teams currently supports Python 2.7.15, Python 2.7.18, Python 3.7, Python 3.8, R 3.5, and R 3.6. Sites that had R and Python access before May 2019 will also include Python 2.7, Python 3.6, and R 3.4.

For Python, the pandas library has already been imported as pd, with the result set of the chart imported as a pandas dataframe variable called "df".

The data frame columns along with the data type are shown in the schema, The schema viewer also displays the list of libraries available for the chosen language, which link to the library's documentation.

If the code outputs another dataframe (named df2) to be used by Sisense for Cloud Data Teams' charting functionality, the dataframe can be passed to periscope.table with the following syntax:

periscope.table(df2)

Similarly, for code that generates a plot (named plt), the plot can be passed to periscope.image like this, which would replace the common plt.show():

periscope.image(plt)

Lastly, for code that generates text (named txt), the text can be passed to periscope.text like this:

periscope.text(txt)

Data and text can also be printed to the Standard Out. Switching between the Sisense for Cloud Data Teams Output and the Standard Out can be done with the tabs below the code block Run button.

To use the Sisense for Cloud Data Teams Output or the Standard Out directly in a chart, select "Python Image" or "Python Text" respectively in the chart type drop down.

Note: The Periscope.output() function used previously is deprecated. It will continue to function as normal to avoid disrupting charts that use them, however the above specified functions should be used going forward.

Adding R to a Chart

Select R 3.5 from the dropdown below the SQL editor and click the '+' icon to add a block of code.

For R, the result set of the chart is imported as a dataframe variable called "df".

If the code outputs another dataframe (named df2) to be used by Sisense for Cloud Data Teams' charting functionality, the dataframe can be passed to periscope.table with the following syntax:

periscope.table(df2)

Similarly, for code that generates a plot, the plot can be output by calling periscope.image() after plotting. For images, the Sisense for Cloud Data Teams chart will scale the size of the image according to the chart's bounds. To define an image's height and width, an optional png function call can override the defaults:

plot(df)
png(width=500, height=500, unit='px')
periscope.image()

Lastly, for code that generates text (named txt), the text can be passed to periscope.text like this:

periscope.text(txt)

Data and text can also be printed to the Standard Out. Switching between the Sisense for Cloud Data Teams Output and the Standard Out can be done with the tabs below the code block Run button.

To use the Sisense for Cloud Data Teams Output or the Standard Out directly in a chart, select "R Image" or "R Text" respectively in the chart type drop down.

Adding Code to a View

Note: R and Python Integration with Views is only available with the Cache or Warehouse Infrastructure as well as the R and Python Integration add-on feature. Site administrators can contact their Account Manager for additional information.

Adding code to a view is very similar to adding code to charts. Once a SQL query has been successfully run for a new view, select the desired language from the dropdown below the SQL editor and click the '+' icon to add a block of code. Sisense for Cloud Data Teams currently supports Python 2.7.15, Python 2.7.18, Python 3.7, Python 3.8, R 3.5, and R 3.6. Sites that had R and Python access before May 2019 will also include Python 2.7, Python 3.6, and R 3.4.

For Python, the pandas library has already been imported as pd, with the result set of the chart imported as a pandas dataframe variable called "df".

For R, the result set of the chart is imported as a dataframe variable called "df".

The code attached to a view should output another dataframe (df2) which will be saved as a table on The Cache or Managed Redshift Warehouse to be used across the site in charts. This final dataframe can be passed to periscope.materialize() for materialization into the Sisense Cloud Cache with the following syntax:

periscope.materialize(df2)

Data and text can also be printed to the Standard Out, which works the same in both charts and views. Switching between the Sisense for Cloud Data Teams Output and the Standard Out can be done with the tabs below the code block Run button. The above mentioned periscope.table(), periscope.image() and periscope.text() functions will all also be available to enhance the development experience.

Code Limits

The code environment for executing Python and R for views has a 500mb limit for both input data size and output data size. For the materialization process, the SQL code and the R or Python code block are each given 30 minutes to run. View previews are given the same time as charts. Views with code are kept fresh with the same refresh logic as views without code.

Materialization

Unlike normal view materialization, materialization from R or Python code does NOT require the input SQL be run on the Sisense Cloud Data Engine. The SQL attached to the view can be run on the Periscope Data Engine or directly on any database connected to the site. The data returned from R or Python code will be materialized and accessible on the Data Engine regardless of the database where the SQL is run.

Note: SQL queries can only be run against one database. For queries that use CSVs, cross database joins, or other functionality that requires the Sisense Cloud Data Engine to run, the view SQL should be run on the Sisense Cloud Data Engine and not against a connected database.

Looking for More?

For more information on views, including how to create a view, please see the documentation here.
For more information on materializing views on the Sisense Cloud Data Engine, see the documentation here.

Code Templates

Code templates are available for common analyses based on popular posts from our Community. These Include the following templates:

Regression Analysis
Transpose Table
Pivot Table
Melt Table
Ranged Calculations
Confidence Interval
Funnel Chart
Statistical Significance
Variable Y Axis Chart
Plot.ly Funnel Chart
Gantt Chart
Sankey Diagram
Stack Rank Chart
KPI Chart
Box Plot
Process to Goal
Predictive Forecast Plot

To select a code template, first select the language (Python 2.7.15, Python 2.7.18, Python 3.7, Python 3.8, R 3.5, or R 3.6). Then, click on New Analysis and select the desired template. The code for the template will be in the selected language.

Next, click the green plus to add the code for the selected template. In this case, code for regression analysis.

This will display the template code with a brief description and comments to aid in using the template.

To request a template, please post an example in our Community or reach out to Sisense for Cloud Data Teams support at support@periscopedata.com or over the in-app live chat.

Limit Preview

Most browsers can only handle visualizations of up to 5MB, so to keep dashboards running smoothly, visualization results are capped. Once a code block for R or Python is added to a chart, the “Limit Preview” setting for charts will only apply to the SQL Output preview. The “Limit Preview” setting will not affect the rows or size of the dataframe sent to the R or Python script, allowing the code to run on the full result set within a 500MB limit. If the R or Python code returns over 5MB of data, an error will be surfaced and a hard-coded limit or aggregation, outside of the “Limit Preview” setting, should be added to the SQL or code blocks.

Custom Modules

For more advanced analysis, users can save up to 1MB in files that contain reusable code, utils, and modules into their Sisense for Cloud Data Teams Git repository which can then be used in the chart and view editors. Using custom modules can enforce consistency, scalability and repeatability for an organization's analytics.

Custom modules are available for sites that have both the Git integration and the Python/R integration. They are specific to one space and all users that have at least SQL edit privileges will have access to all modules.

More information on Custom Modules

Available Libraries

The libraries available to import can be easily accessed from the schema browser and link directly to the library docs.

Python supported libraries include:

<div style="display: flex; flex-direction: column; flex-wrap:wrap; height:200px; ">
<div> <a href="https://www.crummy.com/software/BeautifulSoup/bs4/doc/">BeautifulSoup</a></div>
<div> <a href="https://pypi.org/project/beautifulsoup4/">BeautifulSoup4</a> </div>
<div> <a href="https://pypi.org/project/bootstrapped/>bootstrapped">bootstrapped</a> </div>
<div> <a href="https://github.com/santosjorge/cufflinks">cufflinks</a> </div>
<div> <a href="http://dfm.io/emcee/current/">emcee</a></div>
<div> <a href="https://pypi.org/project/Fuzzy/"> Fuzzy </a></div>
<div> <a href="https://pypi.org/project/fuzzywuzzy/">Fuzzywuzzy </a> </div>
<div> <a href="http://geopandas.org/"> GeoPandas</a></div>
<div> <a href="https://pypi.org/project/fuzzywuzzy/"> Graphviz </a> </div>
<div> <a href="https://pypi.org/project/holidays/"> holidays </a> </div>
<div> <a href="https://igraph.org/python/"> igraph </a> </div>
<div> <a href="http://jinja.pocoo.org/docs/2.10/"> Jinja </a> </div>
<div> <a href="https://lifelines.readthedocs.io/en/latest/"> lifelines </a> </div>
<div> <a href="https://pypi.org/project/Lifetimes/"> Lifetimes </a> </div>
<div> <a href="https://matplotlib.org/contents.html"> matplotlib </a> </div>
<div> <a href="https://pypi.org/project/matplotlib-venn/"> matplotlib-venn </a> </div>
<div> <a href="http://networkx.github.io/"> NetworkX </a> </div>
<div> <a href="https://www.nltk.org/"> nltk </a> </div>
<div> <a href="https://docs.scipy.org/doc/"> numpy </a> </div>
<div> <a href="https://pandas.pydata.org/pandas-docs/stable/"> pandas </a> </div>
<div> <a href="https://patsy.readthedocs.io/en/latest/"> patsy </a> </div>
<div> <a href="https://pypi.org/project/plotly/"> plot.ly </a> </div>
<div> <a href="https://pypi.org/project/PuLP/"> PuLP </a> </div>
<div> <a href="https://facebook.github.io/prophet/"> prophet </a> </div>
<div> <a href="https://pypi.org/project/pygeohash/"> pygeohash </a> </div>
<div> <a href="https://pypi.org/project/pygraphviz/"> PyGraphviz </a> </div>
<div> <a href="https://pypi.org/project/pymc/"> pymc* </a> </div>
<div> <a href="https://docs.pymc.io/"> pymc3** </a> </div>
<div> <a href="https://pyod.readthedocs.io/en/latest/"> PyOD </a> </div>
<div> <a href="https://pypi.org/project/pyshp/"> PyShp </a> </div>
<div> <a href="https://pypi.org/project/s2sphere/"> s2sphere </a> </div>
<div> <a href="https://scikit-image.org/"> scikit-image </a> </div>
<div> <a href="https://scikit-learn.org/stable/documentation.html">scikit-learn </a> </div>
<div> <a href="https://docs.scipy.org/doc/"> scipy </a> </div>
<div> <a href="https://seaborn.pydata.org/"> seaborn </a> </div>
<div> <a href="https://pypi.org/project/spacy/"> spaCy</a> </div>
<div> <a href="https://www.statsmodels.org/stable/index.html"> statsmodels </a> </div>
<div> <a href="https://www.sympy.org/en/index.html"> sympy </a> </div>
<div> <a href="https://pypi.org/project/Theano/"> Theano </a> </div>
<div> <a href="https://pypi.org/project/views/"> views </a> </div>
<div> <a href="https://pypi.org/project/windrose/"> windrose </a> </div>
<div> <a href="https://pypi.org/project/wordcloud/"> wordcloud </a> </div>
<div> <a href="https://pypi.org/project/xgboost/"> xgboost </a> </div>
<div>

Notes:
* For Python 2.7, 2.7.15 and 3.6 only
** Sisense for Cloud Data Teams only supports sampling using one core, which must be specified. Example: trace = pm.sample(niter, progressbar=False, cores=1)

R supported libraries include:

<div style="display: flex; flex-direction: column; flex-wrap:wrap; height:200px; ">
<div><a href="https://cran.r-project.org/web/packages/anomalize/index.html"> anomalize </a></div>
<div><a href="https://topepo.github.io/caret/index.html"> caret </a></div>
<div><a href="https://cran.r-project.org/web/packages/caTools/index.html"> caTools </a></div>
<div><a href="https://cran.r-project.org/web/packages/CausalImpact/index.html"> CasualImpact </a></div>
<div><a href="https://www.rdocumentation.org/packages/choroplethr/versions/3.6.3"> choroplethr </a></div>
<div><a href="https://www.rdocumentation.org/packages/choroplethrMaps/versions/1.0.1"> choroplethrMaps </a></div>
<div><a href="https://cran.r-project.org/web/packages/data.table/vignettes/datatable-intro.html"> data.table </a></div>
<div><a href="https://cran.r-project.org/web/packages/diagram/index.html"> diagram </a></div>
<div><a href="https://dplyr.tidyverse.org/"> dplyr </a></div>
<div><a href="https://cran.r-project.org/web/packages/dtw/index.html"> dtw </a></div>
<div><a href="https://cran.r-project.org/web/packages/e1071/index.html"> e1071 </a></div>
<div><a href="https://www.rdocumentation.org/packages/epitools/versions/0.09"> epitools </a></div>
<div><a href="https://forcats.tidyverse.org/"> forcats </a></div>
<div><a href="https://cran.r-project.org/web/packages/forecast/index.html"> forecast </a></div>
<div><a href="https://cran.r-project.org/web/packages/geosphere/index.html"> geosphere </a></div>
<div><a href="https://ggplot2.tidyverse.org/"> ggplot2 </a></div>
<div><a href="https://cran.r-project.org/web/packages/ggrepel/index.html"> ggrepel </a></div>
<div><a href="https://cran.r-project.org/web/packages/igraph/index.html"> igraph </a></div>
<div><a href="https://cran.r-project.org/web/packages/kernlab/index.html"> kernlab </a></div>
<div><a href="https://cran.r-project.org/web/packages/kknn/index.html"> kknn </a></div>
<div><a href="https://cran.r-project.org/web/packages/ltm/index.html"> ltm </a></div>
<div><a href="https://cran.r-project.org/web/packages/labeling/index.html"> labeling </a></div>
<div><a href="https://lubridate.tidyverse.org/"> lubridate </a></div>
<div><a href="https://cran.r-project.org/web/packages/magrittr/index.html"> magrittr </a></div>
<div><a href="https://cran.r-project.org/web/packages/mclust/index.html"> mclust </a></div>
<div><a href="https://cran.r-project.org/web/packages/pa/index.html"> pa </a></div>
<div><a href="https://cran.r-project.org/web/packages/party/index.html"> party </a></div>
<div><a href="https://cran.r-project.org/web/packages/plotly/index.html"> plot.ly </a></div>
<div><a href="https://cran.r-project.org/web/packages/plsdepot/index.html"> plsdepot </a></div>
<div><a href="https://cran.r-project.org/web/packages/plspm/index.html"> plspm </a></div>
<div><a href="https://cran.r-project.org/web/packages/prophet/index.html"> prophet </a></div>
<div><a href="https://cran.r-project.org/web/packages/purrr/index.html"> purrr </a></div>
<div><a href="https://www.rdocumentation.org/packages/randomForest/versions/4.6-12"> randomForest </a></div>
<div><a href="https://cran.r-project.org/web/packages/RColorBrewer/index.html"> RColorBrewer </a></div>
<div><a href="https://cran.r-project.org/web/packages/Rcpp/index.html"> Rcpp </a></div>
<div><a href="https://www.rdocumentation.org/packages/reshape2/versions/1.4.3"> reshape2 </a></div>
<div><a href="https://cran.r-project.org/web/packages/rjags/index.html"> rjags </a></div>
<div><a href="https://cran.r-project.org/web/packages/rpart/index.html"> rpart </a></div>
<div><a href="https://cran.r-project.org/web/packages/rpart.plot/index.html"> rpart.plot </a></div>
<div><a href="https://cran.r-project.org/web/packages/scales/index.html"> scales </a></div>
<div><a href="https://cran.r-project.org/web/packages/SnowballC/index.html"> SnowballC </a></div>
<div><a href="https://cran.r-project.org/web/packages/sp/index.html"> sp </a></div>
<div><a href="https://cran.r-project.org/web/packages/splines2/index.html"> splines2 </a></div>
<div><a href="https://cran.r-project.org/web/packages/survival/index.html"> survival </a></div>
<div><a href="https://stringr.tidyverse.org/"> stringr </a></div>
<div><a href="https://tibble.tidyverse.org/"> tibble </a></div>
<div><a href="https://tidyr.tidyverse.org/"> tidyr </a></div>
<div><a href="https://www.tidyverse.org/"> tidyverse </a></div>
<div><a href="https://www.rdocumentation.org/packages/treemapify/versions/2.5.3"> treemapify </a></div>
<div><a href="https://www.rdocumentation.org/packages/tseries/versions/0.10-47"> tseries </a></div>
<div><a href="https://www.rdocumentation.org/packages/vcd/versions/1.4-4"> vcd </a></div>
<div><a href="https://www.rdocumentation.org/packages/vcdExtra/versions/0.7-1"> vcdExtra </a></div>
<div><a href="https://cran.r-project.org/web/packages/wordcloud/index.html"> wordcloud </a></div>
<div><a href="https://cran.r-project.org/web/packages/xgboost/index.html"> xgboost </a></div>
<div><a href="https://cran.r-project.org/web/packages/zoo/index.html"> zoo </a></div>
</div>

More libraries will be added as they are requested and reviewed. Please reach out to Sisense for Cloud Data Teams support to request a library addition at support@periscopedata.com or over the in-app live chat.