How to multi column aggregates like Weighted mean

[aszenz]

Loving visidata so far, curious to know how to perform multi column aggregations, is it possible via existing cmds or does it need a plugin?

[midichef]

Can you give a specific example of what you want, including a sample data set?

In a simple case, you can often use = (addcol-expr) to combine columns. If you had data like:

weight,value
0.50,100
0.40,3 
0.10,5

you could calculate a weighted mean of value by doing: = weight*value Enter % + sum Enter

[aszenz]

Thanks, the dataset is like this

Order ID, Product Code, Price, Qty

Calculate weighted average price per product across orders.

The formula is

Avg(Price * Qty / Total Qty)

Which is similar to your weight solution but the question is how do I calculate this weight (qty/total qty) for each product.

[midichef]

Use the command addcol-aggregate. It groups rows by the key column, then calculates an aggregator for the group.

So, make product_code the key column using !. Then create 2 aggregates on the grouped rows: the sum of each product's quantity, and the sum of each order's quantity*price. The full list of keystrokes would be:

= quantity*price Enter %
^ order_revenue
Navigate to the product_code column, then press !.
Navigate to the order_revenue column, then Space addcol-aggregate Enter sum.
Navigate to quantity, then Space addcol-aggregate Enter sum.
= order_revenue_sum / quantity_sum Enter ^ avg_product_price

Note that when you edit the cells or add new rows,= will update its calculated results, but addcol-aggregate will not update its results.

[aszenz]

@midichef Thanks, I didn't know about this addcol-aggregate, seems useful. I only knew about regular aggregate-col