cca3	country	continent	pop_2023	pop_2022	pop_2000	area__km__
IND	India	Asia	1428627663	1417173173	1059633675	3287590.
CHN	China	Asia	1425671352	1425887337	1264099069	9706961.
USA	United States	North America	339996563	338289857	282398554	9372610.
IDN	Indonesia	Asia	277534122	275501339	214072421	1904569.
PAK	Pakistan	Asia	240485658	235824862	154369924	881912.

Column Name	Type	Computed With
cca3	String
country	String
continent	String
pop_2023	Int
pop_2022	Int
pop_2000	Int
area__km__	Float

country	yoy_change
India	11454490
China	-215985
United States	1706706
Indonesia	2032783
Pakistan	4660796

Column Name	Type	Computed With
cca3	String
country	String
continent	String
pop_2023	Int
pop_2022	Int
pop_2000	Int
area__km__	Float
yoy_change	Int	pop_2023 - pop_2022

country	yoy_change
India	11454490
China	-215985
United States	1706706
Indonesia	2032783
Pakistan	4660796

Column Name	Type	Computed With
cca3	String
country	String
continent	String
pop_2023	Int
pop_2022	Int
pop_2000	Int
area__km__	Float
yoy_change	Int	pop_2023 - pop_2022
yoy_percent_change	Float	(100 * yoy_change) / pop_2022

country	yoy_change	yoy_percent_change
India	11454490	0.808
China	-215985	-0.015
United States	1706706	0.505
Indonesia	2032783	0.738
Pakistan	4660796	1.976

cca3	country	continent	pop_2023	pop_2022	pop_2000	area__km__	yoy_change	yoy_percent_change
FLK	Falkland Islands	South America	3791	3780	3080.0	12173.	11	0.291
NIU	Niue	Oceania	1935	1934	2074.0	261.	1	0.052
TKL	Tokelau	Oceania	1893	1871	1666.0	12.	22	1.176
VAT	Vatican City	Europe	518	510	651.0	0.44	8	1.569
None	California	None	39110000	39030000	NaN	NaN	80000	0.205

cca3	country	continent	pop_2023	pop_2022	pop_2000	area__km__	yoy_change	yoy_percent_change
FLK	Falkland Islands	South America	3791	3780	3080.0	12173.	11	0.291
NIU	Niue	Oceania	1935	1934	2074.0	261.	1	0.052
TKL	Tokelau	Oceania	1893	1871	1666.0	12.	22	1.176
VAT	Vatican City	Europe	518	510	651.0	0.44	8	1.569
None	California	None	39110000	39030000	NaN	NaN	80000	0.205

source	metadata
	{"bits": 8, "mode": "RGB", "width": 640, "format": "JPEG", "height": 426, "palette": null}
	{"bits": 8, "mode": "RGB", "width": 640, "format": "JPEG", "height": 483, "palette": null}
	{"bits": 8, "mode": "RGB", "width": 621, "format": "JPEG", "height": 640, "palette": null}

Column Name	Type	Computed With
source	Image
metadata	Json	source.get_metadata()
rotated	Image	source.rotate(10)
rotated_transparent	Image['RGBA']	source.convert('RGBA').rotate(10)

source	detections
	{"boxes": [[243.329, 198.938, 252.491, 212.817], [411.603, 219.568, 442.399, 305.185], [339.933, 202.026, 347.892, 217.15], [512.94, 200.648, 530.061, 221.701], [494.712, 175.403, 518.906, 285.738], [342.964, 178.523, 367.205, 226.858], ..., [445.77, 170.045, 508.718, 291.668], [359.977, 212.132, 372.942, 229.529], [363.898, 220.541, 417.606, 315.758], [448.891, 121.4, 462.657, 142.467], [421.982, 158.211, 465.436, 300.659], [383.675, 172.826, 403.362, 211.302]], "labels": [86, 62, 86, 78, 82, 64, ..., 82, 86, 62, 85, 1, 1], "scores": [0.979, 0.987, 0.908, 0.915, 0.884, 0.926, ..., 0.987, 0.99, 0.997, 0.972, 0.999, 0.882], "label_text": ["vase", "chair", "vase", "microwave", "refrigerator", "potted plant", ..., "refrigerator", "vase", "chair", "clock", "person", "person"]}
	{"boxes": [[0.677, 280.959, 401.834, 477.803], [488.731, 51.887, 497.612, 82.074], [472.22, 251.556, 486.093, 287.805], [490.944, 249.437, 514.341, 288.037], [513.687, 48.824, 522.899, 80.729], [479.89, 251.301, 497.199, 288.15], ..., [341.577, 219.091, 429.855, 352.103], [468.799, 251.786, 481.076, 288.294], [461.138, 295.626, 470.704, 337.119], [503.576, 247.766, 535.297, 288.59], [440.375, 250.286, 449.473, 287.611], [499.56, 51.345, 508.956, 81.398]], "labels": [65, 84, 84, 84, 84, 84, ..., 64, 84, 84, 84, 84, 84], "scores": [0.995, 0.808, 0.819, 0.847, 0.841, 0.822, ..., 0.999, 0.81, 0.831, 0.844, 0.818, 0.81], "label_text": ["bed", "book", "book", "book", "book", "book", ..., "potted plant", "book", "book", "book", "book", "book"]}
	{"boxes": [[270.334, 353.472, 307.723, 404.887], [155.908, 101.902, 434.426, 551.656], [380.517, 160.025, 430.297, 190.625], [154.665, 128.519, 425.751, 607.173], [289.045, 98.536, 437.856, 549.117]], "labels": [40, 1, 37, 1, 1], "scores": [0.821, 0.897, 0.972, 0.98, 0.909], "label_text": ["baseball glove", "person", "sports ball", "person", "person"]}

Column Name	Type	Computed With
source	Image
metadata	Json	source.get_metadata()
rotated	Image	source.rotate(10)
rotated_transparent	Image['RGBA']	source.convert('RGBA').rotate(10)
detections	Json	detr_for_object_detection(source, model_id='facebook/detr-resnet-50', threshold=0.8)
image_with_bb	Image	bboxes_draw(source, detections.boxes, detections.label_text, fill=True)

source	image_with_bb	detections_labeltext	metadata
		["train"]	{"bits": 8, "mode": "RGB", "width": 640, "format": "JPEG", "height": 427, "palette": null}
		["person", "person", "tennis racket", "person", "person", "person", "person", "person", "person", "person", "person", "person", "person"]	{"bits": 8, "mode": "RGB", "width": 640, "format": "JPEG", "height": 427, "palette": null}

```python theme={null} pop_t ```

Now let’s suppose we want to add a new column for the year-over-year population change from 2022 to 2023. You can `select()` such a quantity into a Pixeltable `Query`, giving it the name `yoy_change` (year-over-year change): ```python theme={null} pop_t.select( pop_t.country, yoy_change=(pop_t.pop_2023 - pop_t.pop_2022) ).head(5) ```

A **computed column** is a way of turning such a selection into a new, permanent column of the table. Here’s how it works: ```python theme={null} pop_t.add_computed_column(yoy_change=(pop_t.pop_2023 - pop_t.pop_2022)) ```

  Added 234 column values with 0 errors.
  234 rows updated, 468 values computed.

As soon as the column is added, Pixeltable will (by default) automatically compute its value for all rows in the table, storing the results in the new column. If we now inspect the schema of `pop_t`, we see the new column and its definition. ```python theme={null} pop_t ```

The new column can be queried in the usual manner. ```python theme={null} pop_t.select(pop_t.country, pop_t.yoy_change).head(5) ```

The output is identical to the previous example, but now we’re retrieving the computed output from the database, instead of computing it on-the-fly. Computed columns can be “chained” with other computed columns. Here’s an example that expresses population change as a percentage: ```python theme={null} pop_t.add_computed_column( yoy_percent_change=(100 * pop_t.yoy_change / pop_t.pop_2022) ) ```

  Added 234 column values with 0 errors.
  234 rows updated, 468 values computed.

```python theme={null} pop_t ```

```python theme={null} pop_t.select( pop_t.country, pop_t.yoy_change, pop_t.yoy_percent_change ).head(5) ```

Although computed columns appear superficially similar to Queries, there is a key difference. Because computed columns are a permanent part of the table, they will be automatically updated any time new data is added to the table. These updates will propagate through any other computed columns that are “downstream” of the new data, ensuring that the state of the entire data is kept up-to-date. In traditional data workflows, it is commonplace to recompute entire pipelines when the input dataset is changed or enlarged. In Pixeltable, by contrast, all updates are applied incrementally. When new data appear in a table or existing data are altered, Pixeltable will recompute only those rows that are dependent on the changed data. Let’s see how this works in practice. For purposes of illustration, we’ll add an entry for California to the table, as if it were a country. ```python theme={null} pop_t.insert(country='California', pop_2023=39110000, pop_2022=39030000) ```

  Inserting rows into \`population\`: 1 rows \[00:00, 228.35 rows/s]
  Inserted 1 row with 0 errors.
  1 row inserted, 5 values computed.

Observe that the computed columns `yoy_growth` and `yoy_percent_growth` have been automatically updated in response to the new data. ```python theme={null} pop_t.tail(5) ```

Remember that all tables in Pixeltable are persistent. This includes computed columns: when you create a computed column, its definition is stored in the database. You can think of computed columns as setting up a persistent compute workflow: if you close your notebook or restart your Python instance, computed columns (along with the relationships between them, and any data contained in them) will be preserved. ### Recomputing Columns From time to time you might need to recompute the data in an existing computed column. Perhaps the *code* for one of your UDFs has changed, and you want to recompute a column that uses that UDF in order to pick up the new logic. Or perhaps you want to re-run a nondeterministic computation such as model inference. The command to do this is `recompute_columns()`. It won’t do much in the current example, because all our computations are simple and deterministic, but for demonstration purposes here’s what it looks like: ```python theme={null} pop_t.recompute_columns(pop_t.yoy_change, pop_t.yoy_percent_change) ```

  Inserting rows into \`population\`: 235 rows \[00:00, 8795.92 rows/s]
  235 rows updated, 940 values computed.

```python theme={null} pop_t.tail(5) ```

As expected, it looks the same. If you modify the data that a computed column depends on, Pixeltable will recompute automatically; so recompute\_columns() is primarily useful when the input data remains the same, but your UDF business logic changes. ### A More Complex Example: Image Processing Pixeltable supports media data such as images alongside traditional structured data. Let’s explore an example that uses computed columns for image processing operations. In this example, we’ll create the table directly by providing a schema, rather than importing it from a CSV. ```python theme={null} t = pxt.create_table('fundamentals/image_ops', {'source': pxt.Image}) ```

  Created table 'image\_ops'.

```python theme={null} url_prefix = 'https://github.com/pixeltable/pixeltable/raw/release/docs/resources/images' images = ['000000000139.jpg', '000000000632.jpg', '000000000872.jpg'] t.insert({'source': f'{url_prefix}/{image}'} for image in images) ```

  Inserting rows into \`image\_ops\`: 3 rows \[00:00, 1133.39 rows/s]
  Inserted 3 rows with 0 errors.
  3 rows inserted, 6 values computed.

```python theme={null} t.collect() ```

What are some things we might want to do with these images? A fairly basic one is to extract metadata. Pixeltable provides the built-in UDF `get_metadata()`, which returns a dictionary with various metadata about the image. Let’s go ahead and make this a computed column. “UDF” is standard terminology in databases, meaning “User-Defined Function”. Technically speaking, the get\_metadata() function isn’t user-defined, it’s built in to the Pixeltable library. But we’ll consistently refer to Pixeltable functions as “UDFs” in order to clearly distinguish them from ordinary Python functions. Later in this guide, we’ll see how to turn (almost) any Python function into a Pixeltable UDF. ```python theme={null} t.add_computed_column(metadata=t.source.get_metadata()) t.collect() ```

  Added 3 column values with 0 errors.

Image operations, of course, can also return new images. ```python theme={null} t.add_computed_column(rotated=t.source.rotate(10)) ```

  Added 3 column values with 0 errors.
  3 rows updated, 3 values computed.

```python theme={null} t.collect() ```

Or, perhaps we want to rotate our images and fill them in with a transparent background rather than black. We can do this by chaining image operations, adding a transparency layer before doing the rotation. ```python theme={null} t.add_computed_column( rotated_transparent=t.source.convert('RGBA').rotate(10) ) t.collect() ```

  Added 3 column values with 0 errors.

In addition to get\_metadata(), convert(), and rotate(), Pixeltable has a sizable library of other common image operations that can be used as UDFs in computed columns. For the most part, the image UDFs are analogs of the operations provided by the Pillow library (in fact, Pixeltable is just using Pillow under the covers). You can read more about the provided image (and other) UDFs in the Pixeltable SDK Documentation. Let’s have a look at our table schema. ```python theme={null} t ```

### Image Detection In addition to simple operations like `rotate()` and `convert()`, the Pixeltable API includes UDFs for various off-the-shelf image models. Let’s look at one example: object detection using the ResNet-50 model. Model inference is a UDF too, and it can be inserted into a computed column like any other. This one may take a little more time to compute, since it involves first downloading the ResNet-50 model (if it isn’t already cached), then running inference on the images in our table. ```python theme={null} from pixeltable.functions.huggingface import detr_for_object_detection t.add_computed_column( detections=detr_for_object_detection( t.source, model_id='facebook/detr-resnet-50', threshold=0.8 ) ) ```

  Added 3 column values with 0 errors.
  3 rows updated, 3 values computed.

```python theme={null} t.select(t.source, t.detections).collect() ```

It’s great that the DETR model gave us so much information about the images, but it’s not exactly in human-readable form. Those are JSON structures that encode bounding boxes, confidence scores, and categories for each detected object. Let’s do something more useful with them: we’ll use Pixeltable’s `bboxes_draw()` API to superimpose bounding boxes on the images, using different colors to distinguish different object categories. ```python theme={null} from pixeltable.functions.vision import bboxes_draw t.add_computed_column( image_with_bb=bboxes_draw( t.source, t.detections.boxes, labels=t.detections.label_text, fill=True, ) ) t.select(t.source, t.image_with_bb).collect() ```

  Added 3 column values with 0 errors.

It can be a little hard to see what’s going on, so let’s zoom in on just one image. If you select a single image in a notebook, Pixeltable will enlarge its display: ```python theme={null} t.select(t.image_with_bb).head(1) ```

Let’s check in on our schema. We now have five computed columns, all derived from the single source column. ```python theme={null} t ```

And as always, when we add new data to the table, its computed columns are updated automatically. Let’s try this on a few more images. ```python theme={null} more_images = ['000000000108.jpg', '000000000885.jpg'] t.insert({'source': f'{url_prefix}/{image}'} for image in more_images) ```

  Inserting rows into \`image\_ops\`: 2 rows \[00:00, 944.77 rows/s]
  Inserted 2 rows with 0 errors.
  2 rows inserted, 14 values computed.

```python theme={null} t.select( t.source, t.image_with_bb, t.detections.label_text, t.metadata ).tail(2) ```

It bears repeating that Pixeltable is persistent! Anything you put into a table, including computed columns, will be saved in persistent storage. This includes inference outputs such as t.detections, as well as generated images such as t.image\_with\_bb. (Later we’ll see how to tune this behavior in cases where it might be undesirable to store everything, but the default behavior is that computed column output is always persisted.) ### Expressions Let’s have a closer look at that call to `bboxes_draw()` in the last example. ```python theme={null} bboxes_draw(t.source, t.detections.boxes, labels=t.detections.label_text, fill=True) ``` There are a couple of things going on. `bboxes_draw()` is, of course, a UDF, and its first argument is a column reference of the sort we’ve used many times now: `t.source`, the source image. The other two arguments are more than simple column references, though: they’re compound expressions that include the column reference `t.detections` along with a suffix (`.boxes` or `.label_text`) that tells Pixeltable to look inside the dictionary stored in `t.detections`. These are all examples of Pixeltable expressions. In fact, we’ve seen other types of Pixeltable expressions as well, without explicitly calling them out: * Calls to a UDF are expressions, such as `t.source.rotate(10)`, or the `bboxes_draw()` example above; * Arithmetic operations are expressions, such as year-over-year calculation in our first example: `100 * pop_t.yoy_change / pop_t.pop_2022`. ## Next Steps Learn more about working with Pixeltable: * [Queries and Expressions](/tutorials/queries-and-expressions) * [Tables and Data Operations](/tutorials/tables-and-data-operations) Built with [Mintlify](https://mintlify.com).