Default behaviour of `apply`, `derive` and `lift2` when column(s) have missing values

[mcoady]

Description

I'm comparing a pipeline written in polars to the equivalent in dataframe. I'm seeing a couple of mild 'pain points' around apply, derive and lift2, specifically relating to the handling of OptionalColumn.

It may be I'm taking the wrong approach (do let me know if I'm missing something here) but I wanted to share some thoughts / suggestions.

Essentially, if I have a column with missing values, I'm having to handle the column type @a and @(Maybe a) explicitly, whereas in polars we just ignore any missing values.

So, in the case of apply, instead of being explicit here handling both cases -

D.apply @Text f c
D.apply @(Maybe Text) (fmap f) c

we could just use the first line.

Similarly for derive and lift2 -

D.derive @Text b (F.lift2 f (F.col a) (F.col b))
D.derive @(Maybe Text) b (F.lift2 (\x y -> f x <$> y) (F.col a) (F.col b))

we could just use the first line (would just have Nothing if one/both arguments are missing).

Obviously, this could be a design decision as well, given the usual role of Maybe. IMO it would be nicer to adopt the polars behaviour here and not have to worry about the above examples.

Implementation

In mapColumn it was straightforward to add runOptional to replace the run used on OptionalColumn to effect this. This would take care of apply and derive.

To handle the lift2 behaviour, on first glance it would be a case of adding some extra cases in zipWithColumns. Happy to take a look but wanted to understand what the desired behavior is first.

Current behaviour

dataframe> df <- D.readCsv "test_missing.csv"
dataframe> df
-----------------------
category |    value
---------|-------------
  Int    | Maybe Double
---------|-------------
1        | Just 10.5
1        | Nothing
2        | Just 50.5

dataframe> df |> D.derive "doubled_value" (F.col @Double "value" * 2)
*** Exception:

[Error]: Type Mismatch
        While running your code I tried to get a column of type: "Double" but the column in the dataframe was actually of type: "Maybe Double"
        This happened when calling function interpret on (mult (col @Double "value") (lit (2.0)))

To get the desired outcome, we use lift and fmap (although let me know if this is the wrong approach).

dataframe> df |> D.derive "doubled_value" (F.lift (fmap (*2)) $ F.col @(Maybe Double) "value")
---------------------------------------
category |    value     | doubled_value
---------|--------------|--------------
  Int    | Maybe Double | Maybe Double
---------|--------------|--------------
1        | Just 10.5    | Just 21.0
1        | Nothing      | Nothing
2        | Just 50.5    | Just 101.0

Proposed behaviour

dataframe> df |> D.derive "doubled_value" (F.col @Double "value" * 2)
---------------------------------------
category |    value     | doubled_value
---------|--------------|--------------
  Int    | Maybe Double | Maybe Double
---------|--------------|--------------
1        | Just 10.5    | Just 21.0
1        | Nothing      | Nothing
2        | Just 50.5    | Just 101.0

For reference, the polars behaviour

df = pl.DataFrame({
  "category": [1, 1, 2],
  "value": [10.5, None, 50.5],
})
df = df.with_columns((pl.col("value") * 2).alias("doubled_value"))
print(df)
shape: (3, 3)
┌──────────┬───────┬───────────────┐
│ category ┆ value ┆ doubled_value │
│ ---      ┆ ---   ┆ ---           │
│ i64      ┆ f64   ┆ f64           │
╞══════════╪═══════╪═══════════════╡
│ 1        ┆ 10.5  ┆ 21.0          │
│ 1        ┆ null  ┆ null          │
│ 2        ┆ 50.5  ┆ 101.0         │
└──────────┴───────┴───────────────┘

[mchav]

I was actually thinking about this problem for the last two days.

I was writing the benchmark for https://duckdblabs.github.io/db-benchmark. The benchmark is run for inputs where you can control size and missingness in the data generation script.

So as written we have: F.sum (F.col @Double "v1"). But as soon as the data includes missing values you either fail to compile (if you use template Haskell to generate column references), or you have a runtime failure like you described.

That kind of sucks.

Two strains of solutions: 1) everything defaults to a Maybe which also sucks or 2) we have a series of type promotion rules.

E.g Double + Int doesn't work right now but maybe promoting the Int to a double could make sense. Maybe a + a doesn't work but maybe we could promote a to Maybe a then lift operations.

But this whole model would break the "expressions are type safe views on top of the data" in favor of ergonomics. Which strategically moves this further from Haskell land. It necessarily a bad thing but then I'd need to think what that means for the whole package philosophy.

[mcoady]

Agree, it's def a trade-off.

Just giving my opinion, but I think the spirit of (1) could live comfortably within Haskell land, because one could argue that a Column is an explicit coupling of a type and an index. So function application is then limited to (type, index).

For point (2) I feel an explicit promotion function would be a value add in terms of signalling what the pipeline accepts. Certainly flagging somehow that Int inputs can be considered Double within a pipeline would be handy IMO. Doing that automatically though would seem to stray from the philosophy re: typing so shouldn't be the default perhaps, and maybe be made explicit?

[maxigit]

Two strains of solutions: 1) everything defaults to a Maybe which also sucks or 2) we have a series of type promotion rules.

Regarding 1) the Monoid package solves this problem with the Sum newtype.
It gives any Num a a Monoid instance and therefore a way to create a a out of thin air via mempty.
Something similar can be done, or alternatively having a Maybe0 which defaulft to 0 when needed.

Regarding 2) if the aim of dataframe is to be as close as possible to a pandas/polar dataframe, then some sort of promotion will be needed : everything you are not able to do (like multiply a df by 3) because of the type system will be seen as a hindrance and a reason not to have a strong type system.

An easy way to do promotion is via column , if I have a col @Double "x" nothing could stop me in theory to promote it (or see it) as a col @(Maybe double) "x" or even col @(Maye (Sum Double)) "x" just by using the correct column.

A bit like virtual column or the ala in lenses. (It might even be a good idea to allow nested column accessible through lens like column syntax (or even proper lenses) as in (F.col @double "point.x")