Built-in Python Code Window

The code window on the right side of all Row Zero workbooks is a Python development environment. The window enables writing custom Python functions that are referenced in the spreadsheet, importing data through Python packages and API connections, and importing popular Python libraries. Code written in the code window CANNOT reference or modify data in the spreadsheet. Think of the code window as a place to create helper functions that perform complex computations or pull data through python libraries, which can all be used and referenced in the spreadsheet. The code window can be executed by clicking the 'RUN' button or hitting shift+enter. spreadsheet medianif function

Custom functions

Custom Python functions can be defined in the code window. Once defined, they can be used in the spreadsheet by calling the function after an '=.'

# Define a custom function
def foo(x):
    return "Hello " + str(x)

# Type '=foo(x)' in a spreadsheet cell and pass in an argument, like A1.

Import Python Packages

Popular Python packages, like numpy, scipy, pandas, and others can be imported into the workbook with the normal python syntax of 'import numpy as np.' If an attempt to import a module returns a 'modulenotfound' error, the module is not yet supported. See supported Python packages below. Requests for additional Python module/package support can be sent to support@rowzero.io.

# Example import Python packages
import pandas as pd
import scipy as sp

Supported Python Packages

Importing Data

Many python packages are available that make it easy to import data. To do so, best practice is to create a function that executes a command to the python package and retrieves data. The newly created function can then be used in the spreadsheet and reference other cells in the spreadsheet as inputs to the functions.

Below is an example using the YFinance python package. YFinance calls the Yahoo Finance API to retrieve stock information. In the example below a function called 'STOCK()' is created, that expects a stock ticker variable and retrieves the appropriate stock data. The function is defined in the code window and then used in the spreadsheet to reference cells that contain various stock tickers. The code window must be executed to retrieve the stock information by pressing 'Ctrl + Enter' or clicking the 'run' button.

import yfinance

def STOCK(ticker):
    return yfinance.Ticker(ticker).history(period="max")

spreadsheet medianif function

Python to Spreadsheet Data Mapping

  • Spreadsheet types are converted (marshalled) to/from Python as follows: python to spreadsheet type mapping Marshalling is bidirectional. For example, you can define a Python function that returns a pandas DataFrame and this will be represented in the spreadsheet as a Data Table, which offers sorting and filtering capabilities.

  • Spreadsheet strings support Python's string index/slicing syntax (e.g. ="my_string"[0:3] returns "my_")

  • Range references (e.g. A1:A100) are converted to Python iterables. So if you want to sum the values in a range, define your python function like this:

def foo(range_ref):
     sum = 0
     for x in range_ref:
          sum += x
     return sum

# For this example, type '=foo(A1:A100)' in a spreadsheet cell and it will sum A1:A100 .

Important Notes

  • Functions defined in Python are available as formulas in the spreadsheet. Note that these Python functions do not autocomplete as you start to type them.
  • Spreadsheet data cannot be referenced in the code window and you cannot modify the spreadsheet from Python.

Python Examples

With big data power and a native Python spreadsheet window, Row Zero makes it easy to do big data analysis in a spreadsheet. Here are a few python spreadsheet examples to get your started:

Create custom spreadsheet functions with Python

YFinance data in a spreadsheet

Sports stats in a spreadsheet

pybaseball in a spreadsheet

nfl-data-py data in a spreadsheet