Group By and Average Flat Data in DataWeave

Question

Input:

number,fruit,colour,join_date,avginqty,avgoutqty
282,apple,red,today,3 qty,2 qty
282,apple,red,today,3 qty, 
282,banana,yellow,yesterday,,
282,banana,yellow,yesterday,1 qty,

Output:

number,fruit,colour,join_date,avginqty,avgoutqty
282,apple,red,today,3 qty,2 qty
282,banana,yellow,yesterday,1 qty,

Attempted: but having this error: Cannot coerce Object to String

(payload groupBy (key,index) ->(key.number ++ key.join_date)) mapObject {
   a: {
       number: $[0].number,
       fruit: $[0].fruit,
       colour: $[0].colour,
       join_date:$[0].join_date,
       avginqty: sum($.avginqty)/sizeOf($.avginqty),
       avgoutqty: sum($.avgoutqty)/sizeOf($.avgoutqty)
   }
}

Answer 1

With the following input

number,fruit,colour,join_date,avginqty,avgoutqty
282,apple,red,today,3,2
282,apple,red,today,3,
282,banana,yellow,yesterday,,
282,banana,yellow,yesterday,1,

and using the following script

%dw 2.0
output application/csv
import * from dw::core::Arrays
---

(payload groupBy (key,index) ->(key.number ++ key.join_date)) mapObject {
   a: {
       number: $[0].number,
       fruit: $[0].fruit,
       colour: $[0].colour,
       join_date:$[0].join_date,
       avginqty: ($.avginqty sumBy (if($ == "") 0 else $))/sizeOf($.avginqty) ++ " qty",
       avgoutqty: ($.avgoutqty sumBy (if($ == "") 0 else $))/sizeOf($.avgoutqty) ++ " qty"    
   }
} pluck $

you would get the output as

number,fruit,colour,join_date,avginqty,avgoutqty
282,apple,red,today,3 qty,1 qty
282,banana,yellow,yesterday,0.5 qty,0 qty

In one of your previous questions (Which i had answered with 4 variations (one of those scenarios does address your last ask (if the input has qty String as a part of the quantity).. However you could use the following

Input

number,fruit,colour,join_date,avginqty,avgoutqty
282,apple,red,today,3 qty,2 qty
282,apple,red,today,3 qty,3 qty
282,banana,yellow,yesterday,2 qty,2 qty
282,banana,yellow,yesterday,1 qty,1 qty

Script

%dw 2.0
output application/csv
import * from dw::core::Arrays
---

(payload groupBy (key,index) ->(key.number ++ key.join_date)) mapObject {
   a: {
       number: $[0].number,
       fruit: $[0].fruit,
       colour: $[0].colour,
       join_date:$[0].join_date,
       avginqty: ($.avginqty sumBy (if(($ splitBy " ")[0] == "") 0 else ($ splitBy " ")[0] as Number ))/sizeOf($.avginqty) ++ " qty",
       avgoutqty: ($.avgoutqty sumBy (if(($ splitBy " ")[0] == "") 0 else ($ splitBy " ")[0] as Number))/sizeOf($.avgoutqty) ++ " qty"    
   }
} pluck $

Output

number,fruit,colour,join_date,avginqty,avgoutqty
282,apple,red,today,3 qty,2.5 qty
282,banana,yellow,yesterday,1.5 qty,1.5 qty

In case you would want to skip the rows which have qty as "" for avg calculation. Though this can lead to Division By Zero error in an edge case where all rows have one of those quantities as ""

avginqty: ($.avginqty sumBy (if($ == "") 0 else $))/(sizeOf($.avginqty filter $ != "")) ++ " qty",
avgoutqty: ($.avgoutqty sumBy (if($ == "") 0 else $))/(sizeOf($.avgoutqty filter $ != "")) ++ " qty"

To avoid DBZ error

avginqty: if((sizeOf($.avginqty filter $ != "")) != 0) ($.avginqty sumBy (if($ == "") 0 else $))/(sizeOf($.avginqty filter $ != "")) ++ " qty" else "", 
avgoutqty: if((sizeOf($.avgoutqty filter $ != "")) != 0) ($.avgoutqty sumBy (if($ == "") 0 else $))/(sizeOf($.avgoutqty filter $ != "")) ++ " qty" else ""

Screenshot with a different input configured. (all values)