Create a subset List from a List based on range of seconds

Question

I hope I explain this well. I have a List of "Records". Each record has a DateTime property. What I want to do is create another List that contains the records that are "X" seconds apart. I was trying to use a lambda to do this but can't figure it out. Could some one assist please. See Demo code.

public MainWindow()
{
    InitializeComponent();

    List<MyRecords> MyRecordsList = new List<MyRecords>();
    List<MyGroupedRecords> MyGroupedRecordsList = new List<MyGroupedRecords>();

    int groupRecordsThatAreSecondsApart = 3;

    MyRecords myRecord = new MyRecords() { Name = "A", RecordDate = new DateTime(2018, 2, 8, 0, 1, 0) };
    MyRecordsList.Add(myRecord);
    myRecord = new MyRecords() { Name = "B", RecordDate = new DateTime(2018, 2, 8, 0, 1, 1) };
    MyRecordsList.Add(myRecord);
    myRecord = new MyRecords() { Name = "C", RecordDate = new DateTime(2018, 2, 8, 0, 1, 19) };
    MyRecordsList.Add(myRecord);
    myRecord = new MyRecords() { Name = "C", RecordDate = new DateTime(2018, 2, 8, 0, 1, 4) };
    MyRecordsList.Add(myRecord);

    myRecord = new MyRecords() { Name = "W", RecordDate = new DateTime(2018, 2, 8, 2, 1, 10) };
    MyRecordsList.Add(myRecord);
    myRecord = new MyRecords() { Name = "X", RecordDate = new DateTime(2018, 2, 8, 3, 16, 31) };
    MyRecordsList.Add(myRecord);
    myRecord = new MyRecords() { Name = "Y", RecordDate = new DateTime(2018, 2, 8, 2, 1, 11) };
    MyRecordsList.Add(myRecord);
    myRecord = new MyRecords() { Name = "Z", RecordDate = new DateTime(2018, 2, 8, 2, 1, 14) };
    MyRecordsList.Add(myRecord);
}  

class MyRecords
{
    public string Name { get; set; }
    public DateTime RecordDate { get; set; }
}

class MyGroupedRecords
{
    public DateTime StartDate { get; set; }
    public List<MyRecords> GroupedRecordsList { get; set; }
}

I was trying to use something like this but can't figure it out:

List<GroupedSamples> groupedSamples = tag.SamplesCollection
    .GroupBy(r => r.StartTime)
    .Select(gg => new GroupedSamples 
    { 
        TimeStamp = gg.Key, 
        Samples = gg.OrderBy(r => r.StartTime).ToList(), 
        Count = gg.Count() 
    })
    .ToList();

I forgot to add that I want to be able to change the range between the search. Therefore I am assuming a variable of: TimeSpan secondsApart = new TimeSpan(0, 0, 3); should be put in. — 7VNT, Feb 08 '18 at 18:16
Could you add an example of the input and the desired output? — IvanGrasp, Feb 08 '18 at 18:20
If you had unique identifier for each record it would be much easier. You would only need to do `MyRecordsList.Select(currentRecord=> MyRecordsList.Where(subRecord=> [subRecord is around currentRecord by X] && currentRecord.Id != subRecord.Id).ToList()).toList()` this will return a `List>` and all you need to do is filter the duplicate lists. where they all have the same id as another list — Franck, Feb 08 '18 at 18:21
In the project I'm working in there is no unique identifier unfortunately. It is possible that a record can have the same RecordDate, but that is ok I want to include it in that list also, regardless if it is a duplicate. — 7VNT, Feb 08 '18 at 18:24
Input is MyRecordsList. The output based on groupRecordsThatAreSecondsApart = 3 seconds would be a new list that would contain 2 items. 1) StartDate (The smallest of the dates in the grouped row, 02/18/2018 00:01:00), then the GroupedListRecords property of the first item in the list would contain, { A, 02/18/2018/ 00:01:00 }, { B, 02/18/2018 00:01:01 }, { C, 02/18/201/ 00:01:04 } The Second Item in the new list would be StartDate = { 02/18/2018/ 00:01:10} GroupedRecordsList would contain: {W, 02/18/2018 00:01:10 }, { Y, 02/18/2018 00:01:11 }, { Z, 02/18/2018 00:01:14 }. — 7VNT, Feb 08 '18 at 18:58
your times are not strictly increasing, so what do you want to do when you have a record that goes in the past and breaks up the group. e.g.: your record at (2018, 2, 8, 3, 16, 31), should that break up the group or should the record before it and the record after it (which are less than 3 seconds apart) all be in the same group? — Wyck, Feb 08 '18 at 19:52
Yes, (2018, 2, 8, 3, 16, 31), would go into another group, seeing how it did not meet the criteria of being in the 3 second constraint. — 7VNT, Feb 09 '18 at 13:00

Ian Mercer · Answer 1 · 2018-02-09T00:23:44.673

2

You have two options:

Either you need to calculate the cartesian product of the list with itself (you can use SelectMany to do this) and then simply compare the two date time values presented (for any pairs that are low-high so you examine only half the product).

Or you need to sort the list and then use a sliding window (queue) over it that keeps items until the first is further away than the interval you are looking for. As you read each new value scan the queue to find values that work, then add it to the end and if items at the start are too far away in time, remove them from the front of the queue.

For an in-memory, small list I'd go with the first approach, for a database query I'd go with the later.

edited Feb 09 '18 at 00:23

answered Feb 08 '18 at 18:32

Ian Mercer

38,490
8
97
133

Not sure on how to do a Cartesian product. Is there an easier way or snippet? – 7VNT Feb 08 '18 at 19:00
Zip won't result to a Cartesian product. See the documentation. In order to get a true Cartesian product you need to nest foreach into foreach and create a pair (System.Tuple) of both current elements. – Zazaeil Feb 08 '18 at 19:14
@SerejaBogolubov right, sorry, typing too fast, I meant `SelectMany` applying the list to itself. – Ian Mercer Feb 09 '18 at 00:24

score 1 · Accepted Answer · answered Feb 08 '18 at 19:58

I'm inclined to implement it like this:

TimeSpan maxSpan = new TimeSpan(0, 0, groupRecordsThatAreSecondsApart);    
MyGroupedRecords group = null;
List<MyGroupedRecords> groups = new List<MyGroupedRecords>();

foreach (var record in MyRecordList.OrderBy(r => r.RecordDate))
{
    if (group == null || ((record.RecordDate - group.StartDate).CompareTo(maxSpan) > 0)) {
        // create a new group
        group = new MyGroupedRecords() { StartDate = record.RecordDate, Records = new List<MyRecords>() };
        groups.Add(group);
    }
    group.Records.Add(record);
}

Create a subset List from a List based on range of seconds

2 Answers2