The same five questions in pandas, SQL, LINQ and DataBlock

Every table gets asked the same handful of questions. Which rows do I want. What does each group come to. How do these two tables line up. What is the trend across neighboring rows. How does this one long column read as a grid.

pandas answers all five. SQL answers all five. LINQ answers three of them and leaves you to write the other two by hand. DataBlock answers all five inside the .NET process that asked.

I put the four side by side because it's the quickest way I know to tell the real differences from the ones that are only spelling.

Five questions, four answers

Question	pandas	SQL	LINQ to Objects	DataBlock
Rows where salary is above 80,000	`df[df["Salary"] > 80000]`	`WHERE Salary > 80000`	`rows.Where(r => r.Salary > 80000)`	`staff.Where("Salary", 80000, ComparisonOperator.GreaterThan)`
Mean salary per department	`df.groupby("Department")["Salary"].mean()`	`SELECT Department, AVG(Salary) ... GROUP BY Department`	`rows.GroupBy(r => r.Department)` then `Average` per group	`staff.GroupByAggregate("Department", "Salary", AggregationType.Mean)`
Orders with their customer, keeping orders that have none	`orders.merge(customers, on="CustomerId", how="left")`	`LEFT JOIN customers ON ...`	`GroupJoin`, `SelectMany`, `DefaultIfEmpty`	`orders.Merge(customers, "CustomerId", MergeMode.Left)`
Three-row moving average of price	`df["Price"].rolling(3).mean()`	`AVG(Price) OVER (ORDER BY TradeDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)`	no operator, write the index arithmetic	`prices.Window("Price", WindowFunctionType.MovingAverage, 3)`
Revenue by region across quarters	`pd.pivot_table(df, index="Region", columns="Quarter", values="Revenue", aggfunc="sum")`	one `CASE` column per quarter	no operator	`sales.Pivot("Region", "Quarter", "Revenue", AggregationType.Sum)`

Four of those columns say roughly the same thing four ways. The first real difference isn't in the table at all. It's where the answering happens.

Two of the four run where your code runs. The other two run somewhere else, and you have to reach them.

pandas needs a Python runtime, so a .NET service that wants it needs either a second process to talk to or an embedded interpreter. SQL runs in the database engine, usually another process and often another machine, reached over a connection. LINQ to Objects and DataBlock both run in the process that called them. That's the whole second half of this post.

The filter is data, not code

The three in-code filters look like the same idea written three ways. They're three different kinds of thing. A pandas mask is a Python expression evaluated into a boolean array. A LINQ predicate is a compiled delegate. A DataBlock filter is three values:

var senior = staff.Where("Salary", 80000, ComparisonOperator.GreaterThan);

A column name, a value, and one of nine operators (Equals, NotEquals, GreaterThan, GreaterThanOrEqual, LessThan, LessThanOrEqual, Contains, StartsWith, EndsWith). Nothing in that call is executable. It's a description of a filter, which means you can serialize it, put it in a JSON body, and post it to a server that holds the data. That's exactly what the REST layer does with it. A delegate can't make that trip.

The cost is that Where can't take an arbitrary boolean expression. When you need one, you compute a column first and filter on that:

var flagged = staff
    .Compute("Bonus", "Salary * 0.1")
    .Where("Bonus", 8000, ComparisonOperator.GreaterThan);

Compute takes a string expression with arithmetic, comparison, logic, CASE WHEN ... THEN ... ELSE ... END, and 41 built-in functions covering math, dates, and strings. It reads like SQL, but it isn't SQL. There's no IN, no BETWEEN, no LIKE, and no aggregate function inside an expression.

Naming the answer

Grouped aggregation is where the four disagree about whose job it is to name the result. SQL makes you name it, or leaves you with whatever the driver calls the unnamed column. pandas hands back an indexed Series. DataBlock names it for you, from the aggregation:

var byDept = staff.GroupByAggregate("Department", "Salary", AggregationType.Mean);
// columns: Department, avg_Salary

The prefix comes from the aggregation type: count, sum, avg, min, max, std, var. If you'd rather choose, the third argument does it, and a dictionary overload runs several aggregations over one grouping in a single pass:

var summary = staff.GroupByAggregate("Department", new Dictionary<string, AggregationType>
{
    ["Salary"] = AggregationType.Mean,
    ["Headcount"] = AggregationType.Sum
});

The three LINQ doesn't have

System.Linq.Enumerable has Where, Select, GroupBy, Join and GroupJoin. It has no full outer join, no moving average, and no pivot. Those three are where a table-shaped API stops being a convenience.

Joins are one call and an enum with four members:

var enriched = orders.Merge(customers, "CustomerId", MergeMode.Left);

MergeMode is Left, Right, Full and Inner, and a second overload takes a different key column on each side. Full outer in LINQ is two passes and a union you write yourself.

Moving averages are where the gap is widest. SQL has had OVER for years and pandas has rolling. LINQ has index arithmetic:

var withTrend = prices.Window(
    "Price",
    WindowFunctionType.MovingAverage,
    windowSize: 3,
    resultColumnName: "MA3",
    orderByColumn: "TradeDate",
    partitionByColumns: new[] { "Symbol" });

WindowFunctionType has 22 members, including CumulativeSum, Lag, Lead, RowNumber, Rank and ExponentialMovingAverage, so the shapes SQL gives you with OVER are the shapes you get here.

Pivots are the newest addition to the API. Standard SQL has no pivot operator, so the portable answer is one CASE column per value and a GROUP BY, rewritten every time the set of values changes. Here the values stay data:

var grid = sales.Pivot("Region", "Quarter", "Revenue", AggregationType.Sum);

A second overload takes several index columns and a columnNameFormat, for when one row key isn't enough.

What I'm not saying

Nothing above says DataBlock is faster than pandas, or better than SQL, or a replacement for either. It isn't competing with a query planner that has a decade of work behind it, and a database that already holds your data should keep answering questions about it.

What the table shows is smaller and more practical than that. If your program is a .NET program, four of these five questions used to mean either writing the loop yourself or moving the data to something that could answer it. .NET has had table-shaped libraries for a while now, Microsoft.Data.Analysis and Deedle among them, and the appetite for them is not new. What DataBlock adds is that the answer arrives as an object your next line of C# can use, and the question itself is small enough to put in a message.

Run the five against your own tables. If one of them doesn't fit the shape you have, I'd like to hear which one.