F# - Write Deedle FrameData To CSV

Question

I need to write a Deedle FrameData (including "ID" column and additional "Delta" column with blank entries) to CSV. While I can generate a 2D array of the FrameData, I am unable to write it correctly to a CSV file.

module SOQN = 

    open System
    open Deedle
    open FSharp.Data

    //  TestInput.csv
    //  ID,Alpha,Beta,Gamma
    //  1,no,1,hi
    //  ...

    //  TestOutput.csv
    //  ID,Alpha,Beta,Gamma,Delta
    //  1,"no","1","hi",""
    //  ...

    let inputCsv = @"D:\TestInput.csv"
    let outputCsv = @"D:\TestOutput.csv"
    let (df:Frame<obj,string>) = Frame.ReadCsv(inputCsv, hasHeaders=true, inferTypes=false, separators=",", indexCol="ID")

    // See http://www.fssnip.net/sj/title/Insert-Deedle-frame-into-Excel
    let data4Frame (frame:Frame<_,_>) = frame.GetFrameData()

    // See http://www.fssnip.net/sj/title/Insert-Deedle-frame-into-Excel
    let boxOptional obj =
        match obj with
        | Deedle.OptionalValue.Present obj -> box (obj.ToString()) 
        | _ -> box ""

    // See http://www.fssnip.net/sj/title/Insert-Deedle-frame-into-Excel
    let frameToArray (data:FrameData) =
        let transpose (array:'T[,]) =
          Array2D.init (array.GetLength(1)) (array.GetLength(0)) (fun i j -> array.[j, i])
        data.Columns
        |> Seq.map (fun (typ, vctr) -> vctr.ObjectSequence |> Seq.map boxOptional |> Array.ofSeq)
        |> array2D
        |> transpose

    let main = 
        printfn ""
        printfn "Output Deedle FrameData To CSV"
        printfn ""
        let dff = data4Frame df
        let rzlt = frameToArray dff     
        printfn "rzlt: %A" rzlt     
        do 
            use writer = new StreamWriter(outputCsv)
            writer.WriteLine("ID,Alpha,Beta,Gamma,Delta")
            // writer.WriteLine rzlt
        0

    [<EntryPoint>]
    main
    |> ignore

What am I missing?

May I ask why do you need write such function if there is standard one (`SaveCsv`)? — FoggyFinder, May 23 '18 at 17:55
@FoggyFinder. I believe SaveCsv applies to _DataFrame_ but not to _FrameData_. Can you clarify? — matekus, May 23 '18 at 17:56
oh, yes, sorry for that. I have never used `FrameData` before. — FoggyFinder, May 23 '18 at 18:03
@FoggyFinder. No problem. I am trying to do some "data munging" of incoming CSV files. — matekus, May 23 '18 at 18:04
@FoggyFinder, I need to manipulate the rows separately from the header so that is why I am trying the _FrameData_ route. — matekus, May 23 '18 at 18:13
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/171640/discussion-between-foggy-finder-and-matekus). — FoggyFinder, May 23 '18 at 18:14

score 3 · Accepted Answer · answered May 23 '18 at 20:36

I would not use FrameData to do this - frame data is mostly internal and while there are some legitimate uses for it, I don't think it makes sense for this task.

If you simply want to add an empty Delta column to your input CSV, then you can do this:

let df : Frame<int, _> = Frame.ReadCsv("C:/temp/test-input.csv", indexCol="ID")
df.AddColumn("Delta", [])
df.SaveCsv("C:/temp/test-output.csv", ["ID"])

This does almost everything you need - it writes the ID column and the extra Delta column.

The only caveat is that it does not add the extra quotes around the data. This is not required by the CSV specification unless you need to escape a comma in a column and I don't think there is an easy way to get Deedle to do this.

So, I think then you'd have to write your own writing to a CSV file. The following shows how to do this, but it does not correctly escape quotes and commas (which is why you should use SaveCsv even if it does not put in the quotes when they're not needed):

use writer = new StreamWriter("C:/temp/test-output.csv")
writer.WriteLine("ID,Alpha,Beta,Gamma,Delta")
for key, row in Series.observations df.Rows do
  writer.Write(key)
  for value in Series.valuesAll row do
    writer.Write(",")
    writer.Write(sprintf "\"%O\"" (if value.IsSome then value.Value else box ""))
  writer.WriteLine()

Thanks for the detailed explanation and the benefit of your expertise. I am _munging_ incoming csv files to output to a standard format. Usually, this means effectively working with the header and rows separately and bringing them together in the output CSV file. — matekus, May 23 '18 at 20:59

score 1 · Answer 2 · answered May 23 '18 at 20:21

You can get the example of writing to csv from source of the library (it uses FrameData there)

After adding wrapper:

type FrameData with 
    member frameData.SaveCsv(path:string, ?includeRowKeys, ?keyNames, ?separator, ?culture) = 
      use writer = new StreamWriter(path)
      writeCsv writer (Some path) separator culture includeRowKeys keyNames frameData

you could write like this:

dff.SaveCsv outputCsv

F# - Write Deedle FrameData To CSV

2 Answers2