# Normalizing the value of columns in Dataset [duplicate]

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",


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


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).

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

We can start by computing the totals:

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


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


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.