One table, three languages, one variable

Most of the work in a mixed-language analysis isn't analysis. It's moving the table around. You parse a CSV in one language, write a temporary file, read it back somewhere else, then do it again for the next language. The cells that carry a question end up outnumbered by the cells that carry a file.

I wanted to see how small that gets when the languages share memory instead of a folder, so I built a small analysis of the Palmer Penguins dataset inside one Verso notebook. C# parses the table once. Python groups it. SQL aggregates it. PowerShell draws the final table. Three languages do the analysis, a fourth renders it, and none of them exports anything for the next one.

Verso 1.2.2 ships today, and one of the things in it is Verso.DataFrame, a formatter that renders a Microsoft.Data.Analysis.DataFrame as a scrollable table with column names and types. That was the last piece I was missing, so today's the day to write the whole thing down. The formatter and the Palmer Penguins sample notebook it was built against both came from @eosfor, in #94 and #95.

The dataset

Palmer Penguins is body-measurement data for 344 penguins observed on three islands in the Palmer Archipelago, Antarctica, over three field seasons. The palmerpenguins project publishes it as a plain CSV. I like it for the same reason teachers like it: it's small enough to read, it has an obvious grouping, and it has holes in it, which is where real analysis spends its time.

One header row, 344 data rows, and missing values written as the literal text NA.

Eleven rows are missing something. Two of them are missing every measurement and their sex on top of that, and the other nine are missing only sex. That distinction comes back in a few minutes. A count and a mean over the same column can disagree about how many birds there are, and you want to know why before you find it in a chart.

Every cell below reads the file straight from this URL:

https://raw.githubusercontent.com/allisonhorst/palmerpenguins/main/inst/extdata/penguins.csv

Setting up

Install the command line tool and start the browser editor:

dotnet tool install -g Verso.Cli
verso serve

verso serve opens a notebook editor on port 5050. If you'd rather work in VS Code, everything below runs there too: both hosts run the same engine and the same kernels, and the notebook has no idea which one is in front of it.

Two cells want something extra. The SQL step writes to SQLite, so a C# cell pulls the provider in with #!nuget Microsoft.Data.Sqlite. The last cell uses the DataFrame PowerShell module, which the sample notebook installs from a regular PowerShell terminal before you start Verso:

Install-Module DataFrame -Scope CurrentUser

Nothing else gets installed, and no dataset file gets committed anywhere.

Cell 1: fetch and parse, in C

using System.Globalization;
using System.Net.Http;

record Penguin(
    string Species, string Island,
    double? BillLengthMm, double? BillDepthMm,
    double? FlipperLengthMm, double? BodyMassG,
    string Sex, int Year);

double? Number(string field) =>
    field is "NA" or "" ? null : double.Parse(field, CultureInfo.InvariantCulture);

var url = "https://raw.githubusercontent.com/allisonhorst/palmerpenguins/main/inst/extdata/penguins.csv";
var csv = await new HttpClient().GetStringAsync(url);

var penguins = csv
    .Split('\n', StringSplitOptions.RemoveEmptyEntries)
    .Skip(1)
    .Select(line => line.Trim().Split(','))
    .Select(f => new Penguin(
        f[0], f[1],
        Number(f[2]), Number(f[3]), Number(f[4]), Number(f[5]),
        f[6] == "NA" ? null : f[6],
        int.Parse(f[7], CultureInfo.InvariantCulture)))
    .ToList();

penguins.Count

344

Look at what isn't in that cell: a sharing step. When a cell finishes, the kernel walks its own state and publishes what it found into the notebook's variable store. The C# kernel reads Roslyn's script state variables, so csv and penguins are in the store the moment the cell returns, under exactly the names they have in the code. You can put something there by hand with Variables.Set("name", value), but for ordinary top-level variables you never have to.

One store per session, no per-kernel isolation. Every cell that runs after this one, in any language, can see both names.

Cell 2: group it, in Python

Python runs out of process, against the CPython you already have on your machine, so the rows reach it as data instead of as .NET objects. A record shows up as a mapping that answers both spellings, p.Species and p["Species"], so a list of records reads like a list of small objects on the Python side.

import pandas as pd

df = pd.DataFrame([
    {
        "species": p.Species,
        "island": p.Island,
        "bill_length_mm": p.BillLengthMm,
        "flipper_length_mm": p.FlipperLengthMm,
        "body_mass_g": p.BodyMassG,
        "sex": p.Sex,
    }
    for p in penguins
])

for (species, island), group in df.groupby(["species", "island"]):
    print(f"{species:<10} {island:<10} {len(group):>4}  {group['body_mass_g'].mean():>8.1f}")

minMass = int(df["body_mass_g"].median())
print(f"\nmedian body mass: {minMass} g")

Adelie     Biscoe       44    3709.7
Adelie     Dream        56    3688.4
Adelie     Torgersen    52    3706.4
Chinstrap  Dream        68    3733.1
Gentoo     Biscoe      124    5076.0

median body mass: 4050 g

Two things fall out of that. The shape of the data is the easy one: Adelie penguins are on all three islands, Chinstrap only on Dream, Gentoo only on Biscoe, and Gentoo outweigh the other two by well over a kilogram.

The second is the null handling, and it's the kind of thing I like catching early. Adelie on Torgersen counts 52 birds and averages 51 masses. One of the two rows with no measurements is a Torgersen Adelie, and pandas leaves it out of the mean without warning you. A C# double? with no value arrives in Python as None, pandas turns a numeric column containing None into float64 with NaN, and mean quietly ignores it while len does not. Which of the two you want is your call. Noticing you have a call to make is the part that gets easier here, because the C# cell that produced the nulls is right above this one.

The last line assigns minMass. Work you do in Python comes back when you assign a name, so minMass is now in the same store as penguins and csv, ready for the next language. The one thing that doesn't come back is modifying a shared collection in place, so assign your result to a name instead of appending to a list that came from another language.

Cell 3: put the same rows in a database, in C

The SQL kernel talks to a database over ADO.NET, so it needs a real one. This cell writes the rows that are already in memory into a SQLite file. It's the only cell in the notebook that writes a file, and it does that because a database is the point of the next two cells, not because the table had to be handed over.

#!nuget Microsoft.Data.Sqlite

using Microsoft.Data.Sqlite;
using System.IO;

var dbPath = Path.Combine(Path.GetTempPath(), "penguins.db");
File.Delete(dbPath);

var db = new SqliteConnection($"Data Source={dbPath}");
db.Open();

var ddl = db.CreateCommand();
ddl.CommandText = @"CREATE TABLE penguins (
    species TEXT, island TEXT,
    bill_length_mm REAL, bill_depth_mm REAL,
    flipper_length_mm REAL, body_mass_g REAL,
    sex TEXT, year INTEGER)";
ddl.ExecuteNonQuery();

var tx = db.BeginTransaction();
var insert = db.CreateCommand();
insert.Transaction = tx;
insert.CommandText =
    "INSERT INTO penguins VALUES (@species, @island, @billLength, @billDepth, @flipper, @mass, @sex, @year)";
foreach (var name in new[]
    { "@species", "@island", "@billLength", "@billDepth", "@flipper", "@mass", "@sex", "@year" })
    insert.Parameters.Add(new SqliteParameter(name, DBNull.Value));

foreach (var p in penguins)
{
    object[] values =
        { p.Species, p.Island, p.BillLengthMm, p.BillDepthMm, p.FlipperLengthMm, p.BodyMassG, p.Sex, p.Year };
    for (var i = 0; i < values.Length; i++)
        insert.Parameters[i].Value = values[i] ?? DBNull.Value;
    insert.ExecuteNonQuery();
}

tx.Commit();
db.Close();

$"{penguins.Count} rows written"

344 rows written

#!nuget resolves the package for the session before the cell body runs, which is why the using on the very next line works in the same cell. The path lands in dbPath, which goes into the store with everything else. That turns out to matter one cell later.

Cell 4: connect

#!sql-connect --name penguins --connection-string "Data Source=$var:dbPath" --provider Microsoft.Data.Sqlite

A $var: token in a connection string is replaced with a variable from the notebook's store, and $env: does the same for an environment variable. So the string doesn't have to repeat a path a cell already computed, and it doesn't have to carry a password in the notebook file either. When it connects, the output names the connection, the provider, and the database, with the connection string redacted.

You can leave --provider off here. A connection string pointing at a .db file is detected as SQLite on its own. I still name it, because sooner or later somebody else reads this.

Cell 5: aggregate, in SQL

-- --name heavyBirds
SELECT species,
       island,
       COUNT(*)                   AS n,
       ROUND(AVG(body_mass_g), 1) AS mean_mass_g
FROM penguins
WHERE body_mass_g > @minMass
GROUP BY species, island
ORDER BY mean_mass_g DESC

Two things in that cell are not SQL. The first line, the comment starting with --, is read as cell directives. --name heavyBirds puts the result in the store under that name instead of the default lastSqlResult. The other flags you can put on that line are --connection to target a named connection, --page-size, --no-display, and --timeout.

The second is @minMass, which you never declared as a SQL parameter anywhere. SQL cells resolve @name bindings from the same variable store, matching case-insensitively, and the kernel maps the .NET type to a DbType for you. So minMass was computed by pandas three cells ago and it's now the filter in a SQLite query. If you want the whole argument of this post in one line, that's it.

Use the bind syntax your database uses. @name is right for SQL Server, PostgreSQL, MySQL, and SQLite, and Oracle uses :name. A query copied out of another client runs unchanged.

Cell 6: the result comes back to Python

heavy = pd.DataFrame(heavyBirds)
above = int(heavy["n"].sum())
print(f"{above} of {len(df)} birds are above the median ({above / len(df):.1%})")

166 of 344 birds are above the median (48.3%)

On the .NET side a SQL result is a System.Data.DataTable. In Python it arrives as a list of row mappings, which is exactly the shape pd.DataFrame builds a frame from, so there is no conversion step to write. Date columns arrive as real datetime values, and a NULL arrives as None.

Notice df is still around. This is the same Python session as cell 2, so the frame built up there is still in memory and I can compare the two results without rebuilding either one.

Cell 7: render it, in PowerShell

The last cell goes back to csv, the raw string the C# cell downloaded, and loads it into a Microsoft.Data.Analysis.DataFrame with explicit column types. PowerShell sees shared variables as ordinary variables, so $csv is just there.

Import-Module DataFrame

$columnTypes = @(
    [string],   # species
    [string],   # island
    [double],   # bill_length_mm
    [double],   # bill_depth_mm
    [double],   # flipper_length_mm
    [double],   # body_mass_g
    [string],   # sex
    [int]       # year
)

$frame = Import-DataFrame -String ($csv -replace '(?m)(^|,)NA(?=,|$)', '$1') -ColumnType $columnTypes
$frame

Microsoft.Data.Analysis reads an empty numeric field as a null, so the NA markers get blanked out before the import. The regular expression only touches a field that is exactly NA, which is why it's anchored on a comma or a line boundary at both ends.

The last line returns the frame, and as of today it renders as a table.

The header carries the column name and the column's type, it stays put while the body scrolls, and a null renders as the word null rather than an empty cell. The table is a bounded preview on purpose: the first 100 rows and the first 50 columns, the frame's real totals in the footer, and any single value longer than 200 characters shortened. A wide or long frame can't swamp your output, and the footer always tells you what you're looking at.

The formatter reads the frame through reflection and takes no dependency on Microsoft.Data.Analysis, so the PowerShell module that created the frame keeps ownership of that assembly and there's no second copy to argue with. It's built into the VS Code extension, verso serve, and verso run, and it's published as Verso.DataFrame on NuGet if you're embedding the engine yourself. An explicit Display $frame renders the same way. And if you've already written your own DataFrame formatter, give it a priority above 50 and it stays in front of this one.

What crosses, and what doesn't

Python is a separate process, so values reach it as data. Most of that you never notice, and the parts you do are worth knowing before you lean on them.

That last row is a decision, not an accident. A value with no meaning outside the process that made it doesn't silently vanish. The name is still bound, and printing it tells you what happened:

<callback was not shared with Python: a function, which cannot be called from another process>

The value stays usable in the language that produced it. Two smaller rules go with that. A name beginning with a double underscore never reaches a Python cell, because the interpreter owns that prefix. And going the other way, datetime, Decimal, UUID, bytes, NumPy arrays, and pandas frames and series all convert back.

There's more on all of this in One variable store, eight languages, no hand-off, and on how the Python side is hosted in Python cells that run the Python you already have.

Two ways to finish

That's seven cells, and it stands on its own. Two things I'd do with it next.

The first is to turn it into a job. Declared parameters are coerced to CLR types and injected into the same variable store before any cell runs, so a parameter is just another name all four kernels can read. Add a species parameter, filter on it, and the notebook runs unattended:

verso run penguins.verso --param species=Gentoo --output json --output-file result.json

The exit codes are deterministic, so a pipeline can act on them: 0 for success, 1 for a cell failure, 2 for a timeout, and 5 for missing required parameters. I covered that end to end in Run the same notebook in CI that you run at your desk.

The second is to hand the result to somebody. Layout is a property of the file, so switching to the Presentation or Dashboard layout changes what a reader sees without touching a single cell.

Why it's worth the trouble

Here's how the neighbors do this. A Jupyter notebook binds one kernel, and the notebook's kernelspec metadata names one kernel specification for the document. In Polyglot Notebooks, several languages do live in one notebook, and each value that moves between them is named in a #!share or #!set command. The copy is made through the application/json MIME type by default, and for .NET-based kernels the serialization is done with System.Text.Json. Reference sharing is available under stated conditions: both kernels in the same process, both CLR-based, and --byref when using #!set.

Our answer is one VariableStore per session with no per-kernel isolation, which the kernels publish into and read from on their own. You saw the cost back in cell 2: a value that crosses a process boundary crosses as data, and Verso tells you plainly when something cannot make the trip. What you get for it is a notebook where the interesting line is WHERE body_mass_g > @minMass instead of the twenty lines it would otherwise take to get minMass from pandas into SQLite.

Get it

Verso 1.2.2 is on NuGet today, and the notebook editor is on the Visual Studio Marketplace.

dotnet tool install -g Verso.Cli
code --install-extension Datafication.verso-notebook

Build the seven cells yourself, or run your own table down the same path. If a value crosses a language boundary and comes out wrong, that's the report I most want to see.

The DataFrame formatter and the Palmer Penguins sample notebook it grew out of were contributed by @eosfor in #94 and #95. The dataset itself comes from the palmerpenguins project. The guides for everything used above are at versonotebooks.com/docs, and Verso itself is MIT-licensed at github.com/DataficationSDK/Verso.