Double becoming Int when reading Csv

[maxigit]

This is a bit similar to the #157 issue with column being Maybe or not depending on luck.

Here the problem is between Double and Int. Yesterday I tried to rerun something I've done last week, fed the system with new file and suddenly, a column which was a Double is now Int. In fairness the column has always contains natural numbers but to be mixed other columns it is either to see it as a Double.

Changing 0 to 0.0 in the cvs file fixed the problem but it wasn't initially easy to find. The error was a runtime error and I don't remember if the column name was actually spelled out on the error.

I can see two way of solving the problem:
1 - specifying Double when reading the csv
2 - casting somehow the column to an Double.

For 1) I understand that there is a readCsvWithOptions which allow to pass a schema. However I just want to specify one column not all of them : AFAU, readCsv can Either infer from sample or use a schema but not both (.i.e use the schema for some columns and infer from the rest.

For 2) I could use F.lift round or apply round but I think both will crash the column is already a Double (This is similar problem to impute issue in #157).

Maybe we need a way to apply, lift a function only if matches the g - iven type but do nothing instead of crashing . This could be done by either

• introducing new functions,
• new functions returning Either as in applyEither :: Expr a -> ( a -> b) -> Either b b
• maybe a new wrapper as in apply (F.col @(DontCrashIfIAMNoAnA Int) round
• or a new type of column apply (F.colWhichIsPossiblyAn Int) round.

[mchav]

1. If you do happen to specify a single column you can run parseDefaults after. Function should probably have a better name. Lemme know if you have ideas. But maybe the API should change to [(name, Spec)] where everything that's not in spec is inferred.
2. realToFrac would work here, right? The F.toDouble function uses it under the hood so you could use that and it wouldn't crash. Maybe Int would still crash though.

[maxigit]

1. Sorry but actually don't understand what parseDefault does and therefore how it can help in here. Could you show an example ?

2. How can I call realToFrac without doing at some point F.col @Int which will crash if the column is already a Double. Could you an example too ?

[mchav]

dataframe> df <- D.readCsvWithOpts D.defaultReadOptions {D.typeSpec = D.SpecifyTypes [("households", D.schemaType @Float)], D.safeRead = False} "./data/housing.csv" -- everything else is text
dataframe> df
---------------------------------------------------------------------------------------------------------------------------------------------------------
longitude | latitude | housing_median_age | total_rooms | total_bedrooms | population | households | median_income | median_house_value | ocean_proximity
----------|----------|--------------------|-------------|----------------|------------|------------|---------------|--------------------|----------------
  Text    |   Text   |        Text        |    Text     |   Maybe Text   |    Text    |   Float    |     Text      |        Text        |      Text      
----------|----------|--------------------|-------------|----------------|------------|------------|---------------|--------------------|----------------

dataframe> D.parseDefaults D.defaultParseOptions df -- now run inference
--------------------------------------------------------------------------------------------------------------------------------------------------------------
longitude | latitude | housing_median_age | total_rooms | total_bedrooms | population | households |   median_income    | median_house_value | ocean_proximity
----------|----------|--------------------|-------------|----------------|------------|------------|--------------------|--------------------|----------------
 Double   |  Double  |       Double       |   Double    |  Maybe Double  |   Double   |   Float    |       Double       |       Double       |      Text      
----------|----------|--------------------|-------------|----------------|------------|------------|--------------------|--------------------|----------------

[mchav]

Then for the second one I'm assuming you're using the template haskell to derive the columns.

dataframe> df <- D.readCsv "./data/housing.csv" 
dataframe> :declareColumns df
longitude :: Expr Double
latitude :: Expr Double
housing_median_age :: Expr Double
total_rooms :: Expr Double
total_bedrooms :: Expr Maybe Double
population :: Expr Double
households :: Expr Double
median_income :: Expr Double
median_house_value :: Expr Double
ocean_proximity :: Expr Text
dataframe> D.derive "median_house_value" (F.toDouble median_house_value) df
-- works

[maxigit]

dataframe> D.parseDefaults D.defaultParseOptions df -- now run inference

It seems to work indeed. Does parseDefaults only parses Text columns then ?

Then for the second one I'm assuming you're using the template haskell to derive the columns.

I am not for different reasons one being that I am doing interactive exploration but reproductible script and I prefer to be able to have the column definitions visible in the .hs than have a blind declaration when I don't know the identifiers used and their types.

dataframe> D.derive "median_house_value" (F.toDouble median_house_value) df

This works indeed, but only because median_house_value as the correct (or wrong types depending on the point of view).
I explain, after :declareColumns youll end up (depending on luck with either households :: Expr Intorhouseholds :: Expr Double`.

In both case F.toDouble households will work because the type is known at compile time.
However if I don't use declareColums but explicitely states households = F.col @Double "households" (which is what I need ultimately) then F.toDouble householdswill NOT work if the column was parsed as anIntinstead of aDouble`.

You have a similar problem with total_bedrooms which could be (depending on luck again) either a Double an Int a Maybe Double or a Maybe Int.
Writtings codes which compiles regardless of the final type is complete. What if I need to do something along total_room / total_bedrooms , do I need to write just to be sure toDouble total_rooms / toDouble (impute total_bedrooms) ? (But will impute works if bedrooms is not a Maybe etc ... ?)

[mchav]

Are you working from ihaskell, from a cabal script, or from a cabal project?

[maxigit]

A stack project

[mchav]

You can control a lot of these options so I'm not sure it's luck.

• safeRead = whether or not something will be maybe
• typeSpec = whether to use inference
• inference sample size = how many rows to use to infer the types

So it also depends on the shape of your data. If you want a pipeline that doesn't make schema assumptions based on your data pipeline (or at least one that works deterministically regardless of the data changing in small ways) I'd say you should do something specify the schema manually kinda like you would in a database.

And to be robust to failures I'd parse everything as a Maybe or Either that way your pipeline can bake in how you fix data in failure cases.

So something like:

data CustomEnum = Opt1 | Opt2 

schema :: [(Text, SchemaType)]
schemaType = [("a", schemaType @(Either Text CustomEnum)]

colA :: Expr (Either Text CustomEnum)
colA = col "a"

main :: IO ()
main = do
  df <- readCsvWithOpts defaultReadOptions { typeSpec = SpecifyTypes schema} "./data/sample.csv"
  -- use FrameM to clean the data and export new symbols

  -- process the data here

Or a similar workflow with the typed API. Since a lot of these sample-based defaults are fine for EDA they are okay to keep in the happy path but for something that you'll run frequently with the same data you probably wanna clean it up more manually.

[maxigit]

I am just trying to use this package in a real world context and let you know the problem I am encountering in hope that it might be help in removing the rough edges. If my workflow doesn't belong the target workflow, feel to ignore my comments.

You can control a lot of these options so I'm not sure it's luck.

The problem is I can't without insight. For example, I've been generating payroll data last week, the format allows decimal or not (so I can have 2 or 2.0 and some columns are optional, In have a blank or 0 or 0.0.
Let's I am given one of those file and I want to play it. I expect it to be a Maybe Double and I don't have anything to do, the infered types is correct, so I move on and carry on doing things.

The next week, for some reasons,everybody (there are 3 employees) happen to a have a wages which is a full number and the system exporting those data didn't but the decimal. When I rerun the script, what was a Maybe Double is now a Maybe Int and things crashes either at runtime or compile time depending how to I coded it.

The next week, people which normally don't have contribution (i.e Nothing) are an holiday so they are not present in the csv. Suddently the Maybe Double becomes a Double because.

The problem is those two cases is there is no way to fix it so that it works for the current week but also for the previous week. If I change Double to Int it will work for this week, but not for last week and therefore probably not for next week. (Being able to make that easy change which works for both is what I call robust).

An easy fix would be for example to simply ignore type mismatch instead of raising an exception, but for some reason you seem to think that I should modify my workflow instead.

[mchav]

I see. I thought you needed immediate unblocking as well. A general discussion around rough edges is welcome.

In that case it makes sense to do something like your last suggestion - have a function at coerces a column to a particular type. I.e

F.coerce <default> <name>
F.unsafeCoerce <name>

So you could in turn go: F.coerce @Double 0 "households" + 5. That will at least solve the imputation and column resolution problem.

The for the apply path it could be applyLenient or something like that.

That roughly solves #160 as well without introducing the complication around types and redefining their operations.

[mchav]

I'm still thinking about this. @daikonradish @mcoady what do y'all think of this approach? It would entail making a new Leaf if the Expr GADT that flags casts?

[mcoady]

Hello, just had a read. In terms of the Double / Int issue, on my side been ok with that, as I've just been using a schema to make numeric fields Double / Maybe Double so I don't find that a pain point. For context I'm not doing a lot of exploratory analysis but think for that side, could see coerce being useful.

I think from looking over #160 again something like applyLenient, lift2Lenient would be great if I'm understanding correctly, basically ignoring missing values.

My 2c, but I think that could make sense as the default behaviour, with perhaps applyStrict for the existing behaviour. My rationale is just less friction there - I recall seeing a similar thing with impute - in the pandas world for example, calling fillNa on a fully populated column is fine, whereas here we have an exception.

I agree that accommodating both would make sense - would make it easier to work with missing values for sure.

[maxigit]

but I think that could make sense as the default behaviour, with perhaps applyStrict for the existing behaviour

I agree with this especially because it would also make explicit which function can fail at runtime (which normally is not really expected in haskell code).

Having said that, I think this bit is important and and should be thought carefully : there is no rush (even though :

I thought you needed immediate unblocking as well.

) I usually unblock myself first and then file a battle report :-)