How to combine two list according to date

Question

I have two string list , first list contains Ids for corresponding second list each element. Definition of the list,

IdsList ["Id1","Id2"]
ShippingsNoList ["n1,n2..","t1,t2"] 

means that n1,n2->Id1, t1,t2->Id2

IdsList format -> A-date-B ShippingNumbersList format-> number1,number2,etc.

My purpose combine two list and return result as string. If I find ShippingNumber which equals another ShippingNumber(s) and their Id's date should also be matched, then I should take Shipping Number and related Ids. One shipping Number may be already assigned more than one Id's which date is same. Example:

IdsList=["A-28.03.18-B", 
         "S-17.05.18-G", 
         "L-17.05.18-P",
         "M-28.03.18-T",
         "B-17.05.18-U"]

ShippingNumbersList=["100,200,300", 
                      "100,900", 
                      "200,300,100",
                      "100,900,300",
                      "100,300"]

Expected Result:

100-> A-28.03.18-B,M-28.03.18-T
300-> A-28.03.18-B,M-28.03.18-T
100-> S-17.05.18-G,L-17.05.18-P,B-17.05.18-U
300-> L-17.05.18-P, B-17.05.18-U

Answer 1

Try this LINQ "beauty".

var idsList = new string[]
{
    "A-28.03.18-B",
    "S-17.05.18-G",
    "L-17.05.18-P",
    "M-28.03.18-T",
    "B-17.05.18-U"
};

var shippingNumbersList = new string[]
{
    "100,200,300",
    "100,900",
    "200,300,100",
    "100,900,300",
    "100,300"
};

var data = idsList
.Zip(shippingNumbersList, (x, y) =>
{
    //parse the entry of the idsList ('x') in a dateTime
    var date = DateTime.Parse(x.Split("-")[1]); //<-- may need to use DateTime.ParseExact(x.Split('-')[1], "dd.MM.yy", CultureInfo.InvariantCulture) - depending on the culture you are using, this will now work on any machine

    //parse the entry of the shippingNumbersList ('y') in a IEnumerable<int>
    var numbers = y.Split(",").Select(int.Parse);

    //make a IEnumerable of the two different data, consisting of (Id, Date, ShippingNumber) <- a single ShippingNumber, thats why we call numbers.Select
    return numbers.Select(number => (Id: x, Date: date, ShippingNumber: number));
}) //<-- use ZIP to combine the two lists together
.SelectMany(x => x) //<-- use SELECTMANY to get a flat list of each "id" with the x number of "shippingNumberList"
.GroupBy(x => (Date: x.Date, ShippingNumber: x.ShippingNumber)) //<-- use GROUPBY for the Date and ShippingNumber
.Where(x => x.Count() > 1) //<-- use WHERE to filter those who only have 1 entry in a group consisting of Date+ShippingNumber
.Select(x => x.Key.ShippingNumber + "-> " + string.Join(",", x.Select(y => y.Id))) //<-- use SELECT to resolve the group to a string, there the Key is the combined Date + ShippingNumber and the Value is the flatList of that group
.ToList(); //<-- use TOLIST to make a List out of the IEnumerable

Had to fix some stuff for it to run on dotnetfiddle, but here you go: https://dotnetfiddle.net/bKpUDz

Answer 2

Data :

var IdsList =new string[] {"A-28.03.18-B",
    "S-17.05.18-G",
    "L-17.05.18-P",
    "M-28.03.18-T",
    "B-17.05.18-U" };

var ShippingNumbersList =new string[] {"100,200,300",
                        "100,900",
                        "200,300,100",
                        "100,900,300",
                        "100,300" };

Making resuts:

//normalizing data and make a list of joined columns
var normalizedlist = IdsList
.Select((Ids, index) => new { Ids = Ids, ShippingNumbers = ShippingNumbersList[index].Split(',') })
    .ToList();
//for each distinct ShippingNumber find and write respective Id
foreach (var ShippingNumber in normalizedlist.SelectMany(x=>x.ShippingNumbers).Distinct())
{
    //fitering and then grouping by date 
    var filtered = normalizedlist.Where(y => y.ShippingNumbers.Contains(ShippingNumber))
        .GroupBy(y => y.Ids.Split('-')[1])
        .Where(y => y.Count() > 1)
        .Select(y => y.Select(z=>z.Ids));
    foreach (var date in filtered)
    {
    Console.WriteLine($"{ShippingNumber}>>{string.Join(",",date.ToArray())}");
    }
}

Output:

100>>A-28.03.18-B,M-28.03.18-T
100>>S-17.05.18-G,L-17.05.18-P,B-17.05.18-U
300>>A-28.03.18-B,M-28.03.18-T
300>>L-17.05.18-P,B-17.05.18-U

Answer 3

Here is another solution which is tested :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;
using System.Text.RegularExpressions;

namespace ConsoleApplication100
{

    class Program
    {
        static void Main(string[] args)
        {
            List<string> IdsList = new List<string>() {
                                 "A-28.03.18-B",
                                 "S-17.05.18-G", 
                                 "L-17.05.18-P",
                                 "M-28.03.18-T",
                                 "B-17.05.18-U"
                              };
            List<string> ShippingNumbersList = new List<string>() {
                                              "100,200,300", 
                                              "100,900", 
                                              "200,300,100",
                                              "100,900,300",
                                              "100,300"
                                          };
            var results = Shipping.MergeList(IdsList, ShippingNumbersList);
        }

    }
    public class Shipping
    {

        public static object MergeList(List<string> ids, List<string> numbers)
        {
            string pattern = @"\w-(?'day'[^\.]+)\.(?'month'[^\.]+)\.(?'year'[^-]+)";

            List<KeyValuePair<DateTime, string>> idDates = new List<KeyValuePair<DateTime,string>>();
            foreach(string id in ids)
            {
                Match match = Regex.Match(id,pattern);
                idDates.Add(new KeyValuePair<DateTime, string>( new DateTime(2000 + int.Parse(match.Groups["year"].Value), int.Parse(match.Groups["month"].Value), int.Parse(match.Groups["day"].Value)), id));
            }

            var groups = idDates.SelectMany((x, i) => numbers[i].Split(new char[] {','}).Select(y => new { idDate = x, number = y })).ToList();

            var groupDates = groups.GroupBy(x => new { date = x.idDate.Key, number = x.number }).ToList();

            var results = groupDates.Select(x => new { number = x.Key.number, ids = x.Select(y => y.idDate.Value).ToList() }).ToList();

            return results;
        }
    }

}