how one can calculate a weight average price after a group by?

Question

I have the datatable below :

qty     price     date
5         2        1/25
6         1        1/25
8         3        4/25
1         2        4/25

and I want :

qty     price    date
11       1.45     1/25
9        2.88     4/25

I have already group the datatable by date with

 var groups = ToCalc.AsEnumerable().GroupBy(r => r.Field("trade_date"));

and the qty sum is like :

            foreach (var group in groups)
            {
                qty = Convert.ToDouble( group.Sum(r => r.Field("EXEC_QTY")));
            }

but I don't know how to calculate the weighted average price. Any idea ?

possible duplicate of [Weighted Average with LINQ](http://stackoverflow.com/questions/2714639/weighted-average-with-linq) — Dmitry Pavlov, Mar 12 '14 at 11:30

phoog · Accepted Answer · 2012-03-28T15:59:07.567

3

weightedAverage = group.Sum(r => r.Field("EXEC_QTY") * r.Field("price"))
     / group.Sum(r => r.Field("EXEC_QTY"));

Add Convert.ToDouble if you really need it.

Since you comment that qty is a double and price is a decimal:

weightedAverage = group.Sum(r => (decimal)r.Field("EXEC_QTY") * r.Field("price"))
     / group.Sum(r => (decimal)r.Field("EXEC_QTY"));

(Wouldn't it make more sense for price to be decimal and quantity to be a double? Or for both to be decimal?)

edited Mar 28 '12 at 15:59

answered Mar 28 '12 at 15:23

phoog

42,068
6
79
117

thanks, i tried : price =Convert.ToDouble( group.Sum(r => r.Field("EXEC_QTY") * r.Field("EXEC_PRICE")) /group.Sum(r => r.Field("EXEC_QTY"))); But EXEC_QTY is a decimal and EXEC_Price is a double , how to handle that ? – francops henri Mar 28 '12 at 15:54

score 0 · Answer 2 · answered Mar 28 '12 at 15:28

    class Data
    {
        public int Qty { get; set; }
        public double Price { get; set; }
        public string Date { get; set; }
    }
    static void Main(string[] args)
    {
        var data = new Data[] { 
            new Data { Qty = 5, Price = 2, Date = "1/25"  },
            new Data { Qty = 6, Price = 1, Date = "1/25"  },
            new Data { Qty = 8, Price = 3, Date = "4/25"  },
            new Data { Qty = 1, Price = 2, Date = "4/25"  },
        };

        var weighted = data.GroupBy(x => x.Date)
                           .Select(group => new Data { 
                               Date = group.Key, 
                               Qty = group.Sum(i => i.Qty), 
                               Price = group.Sum(i => i.Price * i.Qty) / group.Sum(i => i.Qty)
                       });            
    }

score 0 · Answer 3 · answered Mar 28 '12 at 15:33

I would do something like this:

var results = ToCalc.AsEnumerable()
    .GroupBy(r => r.Field("trade_date"))
    .Select(grp => 
            {
                var quantity = grp.Sum(r => r.Field("EXEC_QTY"));
                var avg = grp.Sum(r => r.Field("EXEC_QTY") * r.Field("price")) / quantity;
                return new { 
                               TradeDate = grp.Key, 
                               Quantity = quantity, 
                               WeightedAverage = avg
                           };
            });

results would then be an enumerable of anonymous objects containing the information you want, it much more accessible structure.

score 0 · Answer 4 · answered Mar 28 '12 at 15:52

You could perform all grouping inside one method by using this Enumerable.GroupByoverload:

var groups = ToCalc.AsEnumerable()
    .GroupBy(r => r.date, (k, g) => new
    {
        date = k,
        qty = g.Sum(i => i.qty),
        price = g.Sum(i => i.qty * i.price) / g.Sum(i => i.qty)
    });

This will give you something like:

{ date = "1/25", qty = 11, price = 1.4545454545454546 }
{ date = "4/25", qty = 9, price = 2.8888888888888888 }

how one can calculate a weight average price after a group by?

4 Answers4