Populate list with missing dates LINQ

Question

I have following list of objects

List < Percentages > MyList which contains values

            date    high    low      avg
2014-08-21 16:15:00  20     10       22.5
2014-08-21 16:12:00  21     11  02
2014-08-21 16:09:00  25     12  23
2014-08-21 16:08:00  23     16  22
2014-08-21 16:07:00  19     09  21
2014-08-21 16:04:00  35     20  21.5
2014-08-21 16:03:00  45     25  19.5
2014-08-21 16:00:00  64     20  33.5
2014-08-21 15:56:00  32     25  27.5

public class Percentages
{
    public DateTime Date { get; set; }     
    public decimal High { get; set; }        
    public decimal Low { get; set; }        
    public decimal Average { get; set; }          
}

As can be seen the list has some missing minutes . My goal is to add missing minutes to list with the values of previous date. Something like :

            date    high    low      avg
2014-08-21 16:15:00  20     10       22.5
2014-08-21 16:14:00  21     11       02
2014-08-21 16:13:00  21     11       02
2014-08-21 16:12:00  21     11       02
2014-08-21 16:11:00  25     12       23
2014-08-21 16:10:00  25     12       23
2014-08-21 16:09:00  25     12       23
2014-08-21 16:08:00  23     16       22
2014-08-21 16:07:00  19     09       21
2014-08-21 16:06:00  35     20       21.5
2014-08-21 16:05:00  35     20       21.5
2014-08-21 16:04:00  35     20       21.5
2014-08-21 16:03:00  45     25       19.5
2014-08-21 16:02:00  64     20       33.5
2014-08-21 16:01:00  64     20       33.5
2014-08-21 16:00:00  64     20       33.5
2014-08-21 15:59:00  32     25       27.5
2014-08-21 15:58:00  32     25       27.5
2014-08-21 15:57:00  32     25       27.5
2014-08-21 15:56:00  32     25       27.5

I did something like this (see below) but it seems a bit tricky probably with LINQ would be easier :

Mylist < Percentages > = ....
List< Percentages > tempList = new List <Percentages > 
for (int j = tempList.Count - 1; j> 0; j--) 
{
    if ( (tempList[j-1].Date - tempList[j].Date).TotalMinutes >1)
    {
        candles.Add(Mylist[j]);
    }
}

I would turn the problem around and start the from generating the output for every minute and only check if the result exists and if not use the more recent one. — matcheek, Aug 21 '14 at 14:19
I cannot im generating entries by grouping date in linq so i cannot check if there is a missing entry in the process i can do that only after — adi sba, Aug 21 '14 at 14:31
I'd probably not do it with linq but just use a while loop to loop through every minute of the range you want, and getting the value from your list if it exists and if not use the last one it found. I can't offhand even think of a nice (ie readable) way to generate the datetime list with linq. — Chris, Aug 21 '14 at 14:40

Tim Schmelter · Accepted Answer · 2014-08-21T20:55:28.780

2

This should work, i've used LINQ since you've asked for. In general your requirement depends heavily on consecutive elements which is often an indication that you should use a plain loop instead of LINQ.

// ensure that it's sorted by date
percentages.Sort((p1, p2) => p1.Date.CompareTo(p2.Date));
List<Percentages> newPercentages = new List<Percentages>();
foreach (Percentages percentage in percentages)
{
    Percentages lastPercentage = newPercentages.LastOrDefault();
    if (lastPercentage != null)
    {
        TimeSpan diff = percentage.Date - lastPercentage.Date;
        int missingMinutes = (int)diff.TotalMinutes - 1;
        if(missingMinutes > 0)
        {
          var missing = Enumerable.Range(1, missingMinutes)
            .Select(n => new Percentages
            {
                Date = lastPercentage.Date.AddMinutes(n),
                Average = lastPercentage.Average,
                High = lastPercentage.High,
                Low = lastPercentage.Low
            });
          newPercentages.AddRange(missing);
        }
    }
    newPercentages.Add(percentage);
}

edited Aug 21 '14 at 20:55

answered Aug 21 '14 at 14:44

Tim Schmelter

450,073
74
686
939

Problem is that not always is missing from and index till list.count-1 might be from from 0 to 4 and from 6 to 10 and from 11 to end. When you asign lastpercentage is not ok to choose last from list. it should be i-1 or something – adi sba Aug 21 '14 at 15:37
Sorry, you have lost me there. The list is ordered, so the last is the nearest. – Tim Schmelter Aug 21 '14 at 15:52
Yes but the most recent percentage to the selected one (percentage[i]) is not always the last one – adi sba Aug 21 '14 at 16:04
But im not using percentage[i] but LastOrDefault which is at this time the previous item since the current is not yet added. By the way, i cannot test it know, but the code should even work with a simple foreach loop. – Tim Schmelter Aug 21 '14 at 16:08
First you are sorting the original to be sure (from oldest index 0 to newest last index). After copy original list to a newList . Iterate trough original list from oldest to newest. Selected item . Asign previous item as being always Last from the newList => This is wrong because it will always have the value of the last item instead of the previous one – adi sba Aug 21 '14 at 16:20
Im not sorting by index but by the Date property and List.Sort does not need to create a new list. If you want you can use OrderBy(p=>p.Date).ToList() to create rwally a new list to avoid reordering the old one. Maybe i have time later to help you further, currently im writing on a phone. – Tim Schmelter Aug 21 '14 at 16:36
No , you are right your code is ok my bad i saw something else.Thank you PS: also im writing frome phone – adi sba Aug 21 '14 at 16:42

score 1 · Answer 2 · answered Aug 21 '14 at 14:42

Something in the lines of:

        var date = DateTime.Parse("21.08.2014");
        var nextdate = date.AddDays(1);
        var list = new List<DateTime>();
        for (var i = date; i < nextdate; i = i.AddMinutes(1))
        {
            list.Add(i);
        }
        list.RemoveRange(tempList.Select(o=>o.Date).ToList());

now list should contain missing values.

score 1 · Answer 3 · answered Aug 21 '14 at 19:14

Here's an implementation as an extension method, so you can chain it with other LINQ methods:

public static class Extensions
{
    public static IEnumerable<Percentage> SubstituteMissingMinutes(this IEnumerable<Percentage> source)
    {
        if(source == null) throw new ArgumentNullException("source");
        return SubstituteMissingMinutesImpl(source.OrderBy(p => p.Date)).Reverse();
    }

    private static IEnumerable<Percentage> SubstituteMissingMinutesImpl(IEnumerable<Percentage> source)
    {
        Percentage previous = null;
        foreach (var Percentage in source)
        {
            if(previous != null)
            {
                var counter = previous.Date;
                while((counter = counter.AddMinutes(1)) < Percentage.Date){
                    yield return new Percentage{ Date = counter, Low = previous.Low, High = previous.High, Avg = previous.Avg };
                }
            }
            previous = Percentage;
            yield return Percentage;
        }        
    }
}

Populate list with missing dates LINQ

3 Answers3

Linked