In openrefine I have a number of rows consisting of (n) values (different number)of previously clustered rows by applying a "Cluster and Edit" and then a "Join multi-valued cells".
"11.11, 10.06, 10.2, 8.77, 9.4, 5.56"
I can then apply a transformation to sum it all like:
forEach(value.split(','),v,v.toNumber()).sum()
Which works fine, but can't figure out how to a similar process for:
do the same to obtain the average instead of the sum
do the average if the values where % percentage like:
0.17%, 1.49%, 0.68%, 0.29%, 0.72%, 22.22%
Take a look at the GREL String Functions.
To get the average, just divide the sum by the number of clustered rows (length).
forEach(value.split(','),v,v.toNumber()).sum() / value.split(',').length()
If the values contain a percentage sign, you can delete it by replacing it with an empty string and then do as above.
forEach(value.replace('%','').split(','),v,v.toNumber()).sum() / value.replace('%','').split(',').length() + '%'