Reference cells

Reference cells from Python.

In Quadratic, reference individual cells from Python for single values or reference a range of cells for multiple values.

Referencing individual cells

To reference an individual cell, use the global function cell (or c for short) which returns the cell value.

# NOTE: cell is (x,y), so cell(2,3) means column 2, row 3 
cell(2, 3) # Returns the value of the cell

c(2, 3) # Returns the value of the cell

You can reference cells and use them directly in a Pythonic fashion.

cell(0, 0) + cell(0, 1) # Adds cell 0, 0 and cell 0, 1

cell(0, 0) == cell(0, 1) # Is cell 0, 0 equal to cell 0, 1 ?

Any time cells dependent on other cells update the dependent cell will also update. This means your code will execute in one cell if it is dependent on another. This is the behavior you want in almost all situations, including user inputs in the sheet that cause calculation in a Python cell.

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 should set first_row_header as an argument. This makes the first row of your DataFrame the column names, otherwise will default to integer column names as 0, 1, 2, 3, etc.

Use first_row_header when you have column names that you want as the header of the DataFrame. This should be used commonly. You can tell when a column name should be a header when the column name describes the data below.

# first_row_header=True will be used any time the first row is the intended header for that data.
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  ]

# table[row, col]
table[0, 0] # -> [100]
table[0, 1] # -> [200]
table[1, 0] # -> [600]
table[1, 1] # -> [700]

# table[row_min:row_max, col]
table[0:5, 0] # -> [100, 200, 300, 400, 500]
table[0:5, 1] # -> [600, 700, 800, 900, 0]

# table[row, col_min:col_max]
table[0, 0:2] # -> [100, 600]
table[1, 0:2] # -> [200, 700]

# table[row_min:row_max, col_min:col_max]
table[0:3, 0:2] # -> [[100, 200, 300], [600, 700, 800]]

Referencing another sheet

To reference another sheet's cells or range of cells use the following:

# Use the sheet name as an argument for referencing range of cells 
df = cells((0,0), (3,50), 'Sheet Name Here')

# For individual cell reference (alternatively can use just c instead of cell)
x = cell(0,0, 'Sheet Name Here')

Relative references

Reference cells relative to the cell you're currently in with relative cell references in Python.

Get position of current cell

Keyword pos() returns a tuple of the (x, y) coordinates of the current cell.

# if the current position is cell (1,1) this would return tuple (1,1)
(x, y) = pos() 

Reference values in relative cells

Reference the values of cells relative the current position.

# data is the cell one cell to the left of the current cell, use either rel_cell or rc
data = rel_cell(-1, 0)
data = rc(-1, 0)

# above for one cell to the left is equivalent to the following 
(x, y) = pos()
data = cell(x - 1, y)

# one cell left
data = rel_cell(-1, 0)
# one cell up 
data = rel_cell(0, -1)
# one cell right 
data = rel_cell(1, 0)
# one cell down
data = rel_cell(0, 1)
# five cells left, five cells down
data = rel_cell(-5, 5)