Python
Quadratic uses pyodide under the hood to provide a Python scripting environment directly in the browser. No more headaches around setting up or sharing a development environment. If it works for you, it’ll work when you share it.
Cells
Referencing individual cells
To reference an individual cell, use the global function cell
(or c
for short) which returns the cell value.
cell(2, 3) # Returns the value of the cell
c(2, 3) # Returns the value of the cell
The resulting value can be used directly in a Pythonic way.
c(0, 0) + c(0, 1) # Adds cell 0, 0 and cell 0, 1
c(0, 0) == c(0, 1) # Is cell 0, 0 equal to cell 0, 1 ?
When a cell depends on other cells and the other cells update, this dependent cell will also update — just like in Excel.
Referencing a range of cells
To reference a range of cells, use the global function cells
which returns a Pandas DataFrame.
cells((0, 0), (2, 2)) # Returns a DataFrame with the cell values
If the first row of cells is a header, you can set first_row_header
as an argument.
cells((2, 2), (7, 52), first_row_header=True)
As an example, this code references a table of expenses, filters it based on a user-specified column, and returns the resulting DataFrame to the spreadsheet.
# Pull the full expenses table in as a DataFrame
expenses_table = cells((2, 2), (7, 52), first_row_header=True)
# Take user input at a cell (Category = "Gas")
category = cell(10, 0)
# Filter the full expenses table to the "Gas" category, return the resulting DataFrame
expenses_table[expenses_table["Category"] == category]
Alternatively, slicing syntax works for selecting a range of cells (returns a Pandas DataFrame).
# Given a table like this:
#
# [ 0 ][ 1 ]
# [0][ 100 ][ 600 ]
# [1][ 200 ][ 700 ]
# [2][ 300 ][ 800 ]
# [3][ 400 ][ 900 ]
# [4][ 500 ][ 0 ]
# cells[row, col]
cells[0, 0] # -> [100]
cells[0, 1] # -> [200]
cells[1, 0] # -> [600]
cells[1, 1] # -> [700]
# cells[row_min:row_max, col]
cells[0:5, 0] # -> [100, 200, 300, 400, 500]
cells[0:5, 1] # -> [600, 700, 800, 900, 0]
# cells[row, col_min:col_max]
cells[0, 0:2] # -> [100, 600]
cells[1, 0:2] # -> [200, 700]
# cells[row_min:row_max, col_min:col_max]
cells[0:3, 0:2] # -> [[100, 200, 300], [600, 700, 800]]
Returning a Python list of values
You can return multiple values to the spreadsheet by returning a list of values in Python. For example:
# Loop over a list of values
result = []
for x in range(0, 5):
result.append(x)
# Return it to the spreadsheet
result
By default arrays are expanded vertically. To have an array expand horizontally, wrap it in a second array, e.g. [result]
# result: [0, 1, 2, 3, 4]
# Return it to the spreadsheet **vertically**
result
# [0][ ][ ][ ][ ]
# [1][ ][ ][ ][ ]
# [2][ ][ ][ ][ ]
# [3][ ][ ][ ][ ]
# [4][ ][ ][ ][ ]
# Return it to the spreadsheet horizontally
[result]
# [0][1][2][3][4]
# [ ][ ][ ][ ][ ]
# [ ][ ][ ][ ][ ]
# [ ][ ][ ][ ][ ]
# [ ][ ][ ][ ][ ]
Returning a Pandas DataFrame
The following code creates a DataFrame of 15 rows, by 4 columns filled with random numbers between 0 and 100. It is the last expression, so it is returned to the the spreadsheet.
# pandas and numpy are preloaded by default!
import pandas as pd
import numpy as np
# using numpy's randint, return df to Grid
pd.DataFrame(np.random.randint(
0,
100,
size=(15, 4),
))
Fetching data
You can make an external HTTP request directly from a Quadratic Cell using js.fetch
. See pyodide’s docs for detailed information on using the fetch API in Python
Example: making an API call to OpenAPI with a prompt
import json
import pyodide
api_key = c(0,3)
model = c(0, 5)
max_tokens = c(0, 7)
temperature = c(0, 9)
top_p = c(0, 11)
prompt = c(0, 13)
# Make API Request
response = await pyodide.http.pyfetch(
'https://api.openai.com/v1/completions',
method= "POST",
headers = {
"Content-Type": "application/json",
"Authorization": "Bearer {}".format(api_key),
},
body = json.dumps({
"model": str(model),
"prompt": str(prompt),
"max_tokens": int(max_tokens),
"temperature": float(temperature),
"top_p": float(top_p)
})
)
# debug
# print(await response.string())
if (response.status == 401):
raise Exception("Check your OpenAI API key")
else:
response_json = await response.json()
result = response_json["choices"][0]["text"].strip()
Packages
The following libraries are included by default:
- Pandas (https://pandas.pydata.org/)
- NumPy (https://numpy.org/)
- SciPy (https://scipy.org/)
You can import them like any other native Python package.
import pandas as pd
Additionally, you can use Micropip to install additional Python packages (and their dependencies).
import micropip
# `await` is necessary to wait until the package is available
await micropip.install("faker")
# import installed package
from faker import Faker
# use the package!
fake = Faker()
fake.name()
This only works for packages that are either pure Python or for packages with C extensions that are built in Pyodide. If a pure Python package is not found in the Pyodide repository it will be loaded from PyPI. Learn more about how packages work in Pyodide.