Please help, Data grouping, calculating total and maximum values

[pho7271]

Data format:

Code: Select all

Aaa 1 2.02
Aaa 6 32.1
Bbb 9 23.2
Bbb 4 332.0
CCC 5 232.0
Ddd 9 22.08
Ddd 1 12.78
Ddd 2 45.34
Eee 0 456

To achieve:
When the first column is grouped by the same value, calculate the maximum value in the second column and the total value in the third column for each group after grouping
Result:

Code: Select all

aaa 6 35.12
Bbb 9 355.2
CCC 5 232.0
Ddd 9 80.2
Eee 0 456

Is there any good solution? thanks

[Mod edit: Added [code][/code] tags for better overview.]

[mikeyww]

Code: Select all

#Requires AutoHotkey v2.0
str := "
(
Aaa 1 2.02
Aaa 6 32.1
Bbb 9 23.2
Bbb 4 332.0
CCC 5 232.0
Ddd 9 22.08
Ddd 1 12.78
Ddd 2 45.34
Eee 0 456
)"
maxi := Map(), sum  := Map(), out := ''
Loop Parse str, '`n', '`r'
 Loop Parse RegExReplace(A_LoopField, '\h+', ','), 'CSV'
  Switch A_Index {
   Case 1: group := A_LoopField
   Case 2: maxi[group] := maxi.Has(group) ? Max(maxi[group], A_LoopField) : A_LoopField
   Case 3: sum[group]  := sum.Has(group)  ? sum[group]     + A_LoopField  : A_LoopField
  }
For group in maxi
 out .= (out = '' ? '' : '`n') group ' ' maxi[group] ' ' Round(sum[group], 2)
MsgBox out, 'Results', 'Iconi'

Output

image240408-0817-001.png (23.43 KiB) Viewed 162 times

[pho7271]

@mikeyww
Thanks @mikeyww for getting back to me quickly

[flyingDman]

If you are a fan of more compact code:

Code: Select all

gmax := map(), gsum := map(), res := ""
for x,y in strsplit(str, "`n", "`r")
	{
	z := strsplit(y," "), grp := z[1]	
	gmax[grp] := gmax.has(grp) ? max(gmax[grp],z[2]) : z[2]
	gsum[grp] := gsum.has(grp) ? gsum[grp]+z[3] 	 : z[3]
	}
For grp in gmax
	res .= grp "`t" gmax[grp] "`t" Round(gsum[grp], 2) "`n"
MsgBox trim(res,"`n"), "Results"

Same technique. I typically prefer for-loops.

[pho7271]

Thanks @flyingDman for getting back to me