date - Misaligned data and duplicate keys using Deedle? F# -

i have data has reference date , publish date. similar economic reports published/released on different dates reference (i.e. q4 gdp 2014 references date 12/31/2014 published following week on 01/07/2015). multiple references date values can published on single publish date. want able add data has similar structure misaligned , duplicate reference , publish dates.

below sample of data item a:

publish_itema           reference_itema         value_itema 2002-01-10 00:00:00.000 2001-09-30 00:00:00.000 83 2002-02-14 00:00:00.000 2001-12-31 00:00:00.000 48 2002-05-23 00:00:00.000 2002-03-31 00:00:00.000 57 2002-08-15 00:00:00.000 2002-06-30 00:00:00.000 41 2002-12-31 00:00:00.000 2002-09-30 00:00:00.000 18 2003-02-13 00:00:00.000 2002-12-31 00:00:00.000 18 2003-05-22 00:00:00.000 2003-03-31 00:00:00.000 29 2003-08-21 00:00:00.000 2003-06-30 00:00:00.000 40 2003-12-31 00:00:00.000 2003-09-30 00:00:00.000 51 2004-12-16 00:00:00.000 2002-12-31 00:00:00.000 17 2004-12-16 00:00:00.000 2003-03-31 00:00:00.000 28 2004-12-16 00:00:00.000 2003-06-30 00:00:00.000 33 2004-12-16 00:00:00.000 2003-09-30 00:00:00.000 60 2004-12-16 00:00:00.000 2003-12-31 00:00:00.000 107 

below sample of data item b:

publish_itemb           reference_itemb         value_itemb         2001-01-25 00:00:00.000 2000-12-31 00:00:00.000 -207 2001-04-25 00:00:00.000 2000-12-31 00:00:00.000 -195 2001-04-25 00:00:00.000 2001-03-31 00:00:00.000 43 2001-07-19 00:00:00.000 2001-06-30 00:00:00.000 61 2001-10-18 00:00:00.000 2001-09-30 00:00:00.000 66 2002-01-17 00:00:00.000 2001-12-31 00:00:00.000 38 2002-04-24 00:00:00.000 2002-03-31 00:00:00.000 40 2002-07-18 00:00:00.000 2002-06-30 00:00:00.000 32 2002-10-17 00:00:00.000 2002-09-30 00:00:00.000 -45 2003-01-16 00:00:00.000 2002-12-31 00:00:00.000 -8 2003-04-24 00:00:00.000 2003-03-31 00:00:00.000 14 2003-07-17 00:00:00.000 2003-06-30 00:00:00.000 19 2003-10-23 00:00:00.000 2003-09-30 00:00:00.000 44 2004-01-22 00:00:00.000 2003-12-31 00:00:00.000 63 

i able alignments , arithmetic columns of values (i.e. itemaframe?value_itema + itembframe?value_itemb) , return series either reference date or publish date dependent on required.

aligning reference date easy because dates non-overlapping there no issue duplicate key, returning frame publish date problematic because not keys unique

any suggestion appreciated.

thanks!

the answer depends on want when there multiple values given (duplicate) publish day. there same number of keys in both of frames? have way of aggregating values (e.g. take average or sum them)?

for example, let's publish , reference integers:

let f =     frame [ "publish" => series.ofvalues [ 1; 1; 2; 2 ]             "reference" => series.ofvalues [ 1; 2; 3; 4 ]           "value" => series.ofvalues [ 10; 9; 11; 8] ] 

you can frame multi-level index (grouped publish day this):

f |> frame.grouprowsbyint "publish" 

now keys tuples - first element "publish" value , second original row index (here, ordinal index - use "reference" date secondary part of index). if have way of making keys match @ point (e.g. there same number of duplicates in both frames , ordinal indexing enough), can use frames now.

however, next thing can create series of frames, containing groups:

f |> frame.grouprowsbyint "publish" |> frame.nest 

so, example, if wanted average value each publish day, do:

f |> frame.grouprowsbyint "publish" |> frame.nest |> series.mapvalues (fun df -> df?value |> stats.mean) 

alternatively, can create series has list of values each "publish" date, make further calculations harder:

f |> frame.grouprowsbyint "publish" |> frame.nest |> series.mapvalues (fun df -> df?value.values |> list.ofseq) 

fundamentally, need indexing scheme uniquely identify rows in both of frames, can align them. key "publish" date or "publish" date else.