How to index a Deedle Frame with Excel's dates as keys?

Question

Say I'm given a column "Date" with values that look like: 03/10/86, 06/10/86, 07/10/86, etc...

It is not as simple as doing Frame.indexRowsDate("Date"). My current solution is to create on the Excel 3 extra columns:

Year
Month
Day

with values:

=Year(A2)
=Month(A2)
=Day(A2)

(for row 2, where A is the column with the dates)

and then use this function:

let toDateTime (os:ObjectSeries<_>) =
  let year = (os.Get "Year") :?> int)
  let month = (os.Get "Month" :?> int)
  let day = (os.Get "Day" :?> int)
  DateTime(year,month,day)

Frame.indexRowsUsing toDateTime frame

Solution Given the provided answer, the new toDateTime looks like this:

let toDateTime (os:ObjectSeries<_>) =
  DateTime.Parse((os.Get "Date") :?> string)

@robkuz Either Deedle is trying to construct a DateTime like this: DateTime("03/10/86"), but that is not a valid constructor for DateTime or Deedle is training to construct a DateTime with the Excel integer representation of that day, which doesn't follows the convention of DateTime — Lay González, Jan 26 '17 at 21:14

score 3 · Accepted Answer · answered Jan 26 '17 at 21:34

You cant use the constructor function for DateTime and feed it a string.

let d = new DateTime("03/10/86")

but you can use the static member TryParse which will return a tuple to you

let (success, date) = DateTime.TryParse("03/10/86")
if success then
   //use date here
else
   //do some error handling

How to index a Deedle Frame with Excel's dates as keys?

1 Answers1