Grouping Associations with varying lengths

Question

I have a large dataset that comes to me in lists of four elements. Each list has a type, an identifying number, a year and a return. Here is a small sample

What I need is a dataset in which the elements for each list are...

{Type, Num, AnnReturn for 1990, AnnReturn for 1991, AnnReturn for 1992, ..., AnnReturn for 2019, AnnReturn for 2020, AnnReturn for 2021}

...and the Header would cover 34 columns:

Type, Num, 1990, 1991, 1992...2019, 2020 ,2021

Of course "GroupBy..." is the answer and if every Num had data in all years the data would be rectangular and it would be easy. However, not all Nums have an Annual return for all years. In fact, in more than 100,000 four-element lists only 5 have AnnReturns for all 32 years. The "rows" are ragged on each end on nearly all of them. To end up with a proper table form there must be a way to insert the "right" number of blanks (Nulls or zero values won't work because operations on the columns follow) before and after each AnnReturn series appearing on rows in unequal lengths with differing starting points. There are even some rows with gaps where the AnnReturns start and then stop and start again in a year or so.

Here is a representative portion where you can see the "Year" field is of differing lengths for each NUM

 {{"I", 121387, 2002, 0.0147619}, {"I", 121387, 2003, 0.0784896}, {"I",
   121387, 2004, 0.177987}, {"I", 121387, 2005, 0.419411}, {"I", 
  121387, 2006, 0.26646}, {"I", 121387, 2007, 0.118356}, {"I", 121387,
   2008, -0.120457}, {"I", 121387, 2009, -0.138521}, {"I", 121387, 
  2010, 0.025091}, {"I", 121387, 2011, 0.20055}, {"I", 121387, 2012, 
  0.152156}, {"I", 121387, 2013, 0.172852}, {"I", 121387, 2014, 
  0.100339}, {"I", 121387, 2015, 0.148478}, {"I", 121387, 2016, 
  0.269283}, {"I", 121387, 2017, 0.159436}, {"I", 121387, 2018, 
  0.180345}, {"I", 121387, 2019, 0.156307}, {"I", 121387, 2020, 
  0.213832}, {"I", 121387, 2021, 0.654568}, {"I", 121388, 1998, 
  0.211778}, {"O", 279600, 2010, 0.191132}, {"O", 279600, 2011, 
  0.0671077}, {"O", 279600, 2012, 0.0628037}, {"O", 279600, 2013, 
  0.0801541}, {"O", 279600, 2014, 0.0911147}, {"O", 279600, 2015, 
  0.117787}, {"O", 279600, 2016, 0.0738923}, {"O", 279600, 2017, 
  0.146092}, {"O", 279600, 2018, 0.0649184}, {"O", 279600, 2019, 
  0.039959}, {"O", 279601, 2005, 0.179231}, {"I", 279602, 2005, 
  0.204455}, {"O", 279603, 2005, 0.0741547}, {"O", 279603, 2006, 
  0.0880792}, {"O", 279603, 2007, 0.253865}, {"O", 279603, 2008, 
  0.0630856}, {"O", 279603, 2009, -0.0616112}, {"O", 279603, 2010, 
  0.0813291}, {"O", 279603, 2011, 0.0623848}, {"O", 279603, 2012, 
  0.126916}, {"O", 279603, 2013, 0.0505087}, {"R", 293206, 2012, 
  0.277507}, {"R", 293206, 2013, 0.189662}, {"R", 293206, 2014, 
  0.134599}, {"R", 293206, 2015, 0.164631}, {"R", 293206, 2016, 
  0.0627536}, {"R", 293206, 2017, 0.0760446}, {"R", 293206, 2018, 
  0.10318}, {"R", 293206, 2019, 0.0794525}, {"R", 293206, 2020, 
  0.0383547}, {"R", 293206, 2021, 0.169052}, {"R", 293207, 2012, 
  0.0787251}, {"R", 293207, 2013, 0.0238834}, {"R", 293207, 2014, 
  0.186054}, {"R", 293207, 2015, 0.264552}, {"R", 293207, 2016, 
  0.155441}, {"R", 293207, 2017, 0.052002}, {"R", 293207, 2018, 
  0.0893041}, {"R", 293207, 2019, -0.00651004}, {"R", 293207, 2020, 
  0.0894872}, {"R", 293207, 2021, 0.225394}, {"I", 293208, 2012, 
  0.0850256}, {"I", 437678, 2020, 0.161467}, {"I", 437678, 2021, 
  0.65435}, {"I", 437679, 2020, 0.155083}, {"I", 437679, 2021, 
  0.204795}, {"I", 437680, 2018, 0.0870769}, {"I", 437680, 2019, 
  0.118006}, {"I", 437680, 2020, 0.164029}, {"I", 437680, 2021, 
  0.117644}, {"I", 437691, 2020, 0.384934}, {"I", 437691, 2021, 
  0.227843}, {"I", 437694, 2018, 0.0978758}, {"I", 437694, 2019, 
  0.078642}, {"I", 437694, 2020, 0.0748457}, {"I", 437694, 2021, 
  0.546993}, {"I", 437695, 2018, 0.130336}, {"I", 437695, 2019, 
  0.137943}, {"I", 437695, 2020, 0.076376}, {"I", 437695, 2021, 
  0.37341}, {"O", 437697, 2019, 0.0671211}, {"O", 437697, 2020, 
  0.050637}, {"O", 437697, 2021, 0.0305274}}

Answer 1

Here's one way:

data = {{"I", 121387, 2002, 0.0147619}, ..., {"O", 437697, 2021, 0.0305274}};

grouped = 
 Dataset[GatherBy[data, #[[;; 2]] &]][
  All, <|"Type" -> #[[1, 1]], "Num" -> #[[1, 2]], Rule @@@ #[[All, 3 ;; 4]]|> &
 ];

This uses GatherBy to do the grouping. I then build an association from the groups, using the type and number from the first entry, and using all items to create the year->value entries. You can now leverage the automatic Missing-semantics of Dataset: For example, querying a given key will return a list with Missing entries for the entries that are, well, missing:

grouped[All, Key[2009]]

You can also easily compute the mean of all entries present:

grouped[Mean, Key[2009]]
(* -0.100066 *)

Answer 2

It's slow but is this what you're looking for?

Grid@Table[
Join[l[[1, ;; 2]], 
FirstCase[l, {t_, c_, #, v_} :> Round[v, .1], ""] & /@ 
Range @@ MinMax@dat[[;; , 3]]], {l, GatherBy[dat, #[[2]] &]}]