I have the below list that has an object with two properties, Date and energy value, and I have to calculate the average energy per month.

The steps I need to follow are to find the start date and end date among all these and find the number of years, and then, each month need to calculate the average energy. If I see multiple months, I need to average the energy value.

public class DateEnergyValue 
{
   public DateOnly CollectedDate{ get; set; }
   public double Value { get; set; }
}

List<DateEnergyValue> DateEnergyValues = new List<DateEnergyValue>() { .....} 

I can get the start date and end date like as below.

var startDate = DateEnergyValues.Min(a => a.CollectedDate);
var endDate = DateEnergyValues.Max(r=>r.CollectedDate);

I can get the number of years as well.

TimeSpan TS = startDate - endDate;
double Years = TS.TotalDays / 365.25; 

But not sure how to go about the remaining calculation, and below the sample data image

Could anyone please help with any ideas or suggestions with the same? Many thanks in advance!!

CodePudding user response：

I have an idea of ​​how I could do it I'm not sure about the performance of it but I could do it

        List<DateEnergyValue> teste = new List<DateEnergyValue>();

        var primeiro = teste.GroupBy(x => new { mounth = x.CollectedDate.Month })
                            .Select(x => 
                                         new { 
                                             media = x.Average(p => p.Value), 
                                             mounth = x.Key.mounth 
                                         })
                            .ToList();

the list would be grouped by month and then create a new list from the grouping with an average of the groupings by month that would result in the value that needs the monthly average...

Hope this helps

CodePudding user response：

After calculating the min and max date, you can iterate the months between those two dates and get an average for each one (leaving it zero if no matches were found for that month):

  static void Main(string[] args)
  {

    // the supplied data as a pipe and comma delimited string
    String data = 
      "5/1/2009|2400,6/1/2009|2400,7/1/2009|2400,8/1/2009|7315.8,9/1/2009|5140.3,"  
      "10/1/2009|1557.8,11/1/2009|1557.8,12/1/2009|369.7,1/1/2010|369.7,"   
      "2/1/2010|144.8,3/1/2010|437.2,4/1/2010|804.1,5/1/2010|965.1,6/1/2010|1131,"   
      "7/1/2010|1179.4,8/1/2010|1170.8,9/1/2010|993.6,10/1/2010|714.9,11/1/2010|244.3,"  
      "12/1/2010|126.8,1/1/2011|122.1,2/1/2011|114.6,3/1/2011|185.2,4/1/2011|441.9,"  
      "5/1/2011|1494.9,6/1/2011|1009.9,7/1/2011|3051.3,8/1/2011|3229.7,9/1/2011|996,"  
      "10/1/2011|3328.7,11/1/2011|870.1,12/1/2011|1051.8,4/1/2011|481000,5/1/2011|446000,"  
      "6/1/2011|608000,7/1/2011|323000,8/1/2011|478000,9/1/2011|452000,10/1/2011|505000,"   
      "11/1/2011|438000,12/1/2011|456654";

    List<DateEnergyValue> DateEnergyValues = new List<DateEnergyValue>();
    
    // this part just generates the list of instances:
    foreach(String pair in data.Split(",".ToCharArray())) {
      String[] values = pair.Split("|".ToCharArray());    
      DateEnergyValue dev = new DateEnergyValue();
      dev.CollectedDate = DateTime.ParseExact(values[0], "M/d/yyyy", null);
      dev.Value = double.Parse(values[1]);
      DateEnergyValues.Add(dev);      
    }

    // you provided this part:
    var minDate = DateEnergyValues.Min(a => a.CollectedDate);
    var maxDate = DateEnergyValues.Max(r => r.CollectedDate);

    List<DateEnergyValue> DateEnergyAverages = new List<DateEnergyValue>();
    
    DateTime startDate = new DateTime(minDate.Year, minDate.Month, 1);
    DateTime stopDate = new DateTime(maxDate.Year, maxDate.Month, 1);
    while (startDate <= stopDate) {
      DateEnergyValue devAverage = new DateEnergyValue();
      devAverage.CollectedDate = startDate;
      var monthValues = DateEnergyValues.Where(x => x.CollectedDate.Year == startDate.Year && x.CollectedDate.Month == startDate.Month);
      if (monthValues.Count() > 0) {
        devAverage.Value = monthValues.Average(x => x.Value);
      }
      DateEnergyAverages.Add(devAverage);
      startDate = startDate.AddMonths(1);
    }

    foreach(DateEnergyValue dev in DateEnergyAverages) {
      Console.WriteLine(dev);
    }
  }

I added a ToString() to the class so it'll ouput:

using System;
public class DateEnergyValue 
{
  
  public DateTime CollectedDate{ get; set; }
  public double Value { get; set; }

  public override String ToString() {
    return $"Date: {CollectedDate.ToString("M/d/yyyy")}, Value: {Value}";
  }
  
}

The generated output:

Date: 5/1/2009, Value: 2400
Date: 6/1/2009, Value: 2400
Date: 7/1/2009, Value: 2400
Date: 8/1/2009, Value: 7315.8
Date: 9/1/2009, Value: 5140.3
Date: 10/1/2009, Value: 1557.8
Date: 11/1/2009, Value: 1557.8
Date: 12/1/2009, Value: 369.7
Date: 1/1/2010, Value: 369.7
Date: 2/1/2010, Value: 144.8
Date: 3/1/2010, Value: 437.2
Date: 4/1/2010, Value: 804.1
Date: 5/1/2010, Value: 965.1
Date: 6/1/2010, Value: 1131
Date: 7/1/2010, Value: 1179.4
Date: 8/1/2010, Value: 1170.8
Date: 9/1/2010, Value: 993.6
Date: 10/1/2010, Value: 714.9
Date: 11/1/2010, Value: 244.3
Date: 12/1/2010, Value: 126.8
Date: 1/1/2011, Value: 122.1
Date: 2/1/2011, Value: 114.6
Date: 3/1/2011, Value: 185.2
Date: 4/1/2011, Value: 240720.95
Date: 5/1/2011, Value: 223747.45
Date: 6/1/2011, Value: 304504.95
Date: 7/1/2011, Value: 163025.65
Date: 8/1/2011, Value: 240614.85
Date: 9/1/2011, Value: 226498
Date: 10/1/2011, Value: 254164.35
Date: 11/1/2011, Value: 219435.05
Date: 12/1/2011, Value: 228852.9