1
$\begingroup$

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

enter image description here

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}}
$\endgroup$
1
  • $\begingroup$ I am CONFLICTED!!! Both answers were helpful and I used both but I can only upvote one. @Adam's answer easily exported to Excel but (as advertised) was slow, especially with the real (huge) data file. $\endgroup$
    – Rogo
    Sep 11, 2022 at 16:28

2 Answers 2

0
$\begingroup$

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]]

enter image description here

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

grouped[Mean, Key[2009]]
(* -0.100066 *)
$\endgroup$
3
  • $\begingroup$ made verifying the contents of the Excel file I exported with Adam's answer easy and is lightening fast. I have not yet figured how to adapt the answer to make it easy to export to Excel, but I am sure with the judicious use of Normal I can do it. Thanks. $\endgroup$
    – Rogo
    Sep 11, 2022 at 16:29
  • $\begingroup$ This works: Export["path\\test.xlsx", Values[Normal[Normal[data][[#]] & /@ Range[Length[data]]]], "XLSX"]; $\endgroup$
    – Rogo
    Sep 11, 2022 at 19:26
  • $\begingroup$ @Rogo you can also do Export[..., Normal@data[All,Values], "XLSX"] $\endgroup$
    – Lukas Lang
    Sep 11, 2022 at 19:46
0
$\begingroup$

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]] &]}]

grid

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.