3
$\begingroup$

This question already has an answer here:

Here's some data in a Dataset. It's about insurers.

   i= Dataset[{<|"State" -> "AR",    "Risk Corridors|Individual Risk Adjustment Transfer" ->    68171.,    "Premium Earned|Individual All" ->
2.20398*10^6|>, <|"State" ->     "AR", "Risk Corridors|Individual Risk Adjustment Transfer" -> 0.,    "Premium Earned|Individual All" ->
304375.|>, <|"State" -> "CA",    "Risk Corridors|Individual Risk Adjustment Transfer" -> 0.,    "Premium Earned|Individual All" ->
-2.55924*10^6|>, <|"State" ->     "CA", "Risk Corridors|Individual Risk Adjustment Transfer" -> \
-3.17428*10^7,    "Premium Earned|Individual All" -> 6.61111*10^7|>, <|"State" ->     "CO", "Risk Corridors|Individual Risk Adjustment Transfer" -> 
   9.28865*10^6,    "Premium Earned|Individual All" -> 7.13151*10^7|>, <|"State" ->     "CO", "Risk Corridors|Individual Risk Adjustment Transfer" -> 0.,    "Premium Earned|Individual All" -> 1.35173*10^7|>, <|"State" ->     "CO", "Risk Corridors|Individual Risk Adjustment Transfer" -> 0.,    "Premium Earned|Individual All" -> 846565.|>}]

I want to compute the fraction of Premium Earned|Individual All for each insurer within a state and add that value as a column to my Dataset. Here's how I am currently doing it. It's really cumbersome. First I create a function that Merges in different ways depending on whether the values are strings or not.

mergeDifferentTypes = 
 row \[Function] 
  Merge[row, 
   Switch[#, {__String}, First[#], _, Total[Select[#, NumericQ]]] &]

Then, I rename one of the columns (otherwise the JoinAcross that is coming up gets confused), group the data by state and apply my mergeDifferentTypes function.

totals = i[GroupBy[#State &] , 
  mergeDifferentTypes, <|"State" -> "State", 
   "Total Premium" -> "Premium Earned|Individual All"|>]

Now I perform the join, remembering to take Normal of i and Norma[Values[i]] because Mathematica does not like JoinAcross operations on Datasets or even on Normals of Datasets where the rows have names.

Dataset[JoinAcross[Normal[i], Normal[Values[totals]], 
   Key@"State"]][All, 
 Association[#, 
   "fraction" -> 
    Slot@"Premium Earned|Individual All"/Slot@"Total Premium"] &]

So, this is simply horrible code. Improvements requested on how to do this better within Mathematica 11.0 (or 11.01 if you've downloaded it).

$\endgroup$

marked as duplicate by WReach, m_goldberg, mikado, user31159, ilian Oct 1 '16 at 2:33

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

6
$\begingroup$

We can start by computing the totals:

totals = i[GroupBy["State"], Total, "Premium Earned|Individual All"]

dataset screenshot

We can then use them to generate the Total Premium and Fraction columns:

i[All
, With[{total = totals[#State]}
  , <| #, "Total Premium" -> total, "Fraction" -> #"Premium Earned|Individual All" / total |>
  ] &
]

dataset screenshot

This technique is described in more detail in Allocate amount A according to the distribution of amount B. I will be voting to close the present question as a duplicate of that.

$\endgroup$

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