Summing values ​​for nested lists for each index

Question

I have a List<List<string>> called _DataCollection, where each of the nested lists has an equal number of values. Although in all lines, the values ​​in the nested lists are lines consisting of alphanumeric characters, blank lines, or a currency value. for instance

 _DataCollection[0] = {"tom", "abc", "$525.34", "$123"} _DataCollection[1] = {"dick", "xyz", "$100", "$234"} _DataCollection[2] = {"harry", "", "$250.01", "$40"} _DataCollection[2] = {"bob", "", "$250.01", ""} 

I need to make a way to sum all the values ​​for each index in all nested lists and add this to the list:

 newSumList[0] = "N/A" since "tom" + "dick" + "harry" + "bob" can't be aggregated. newSumList[1] = "N/A" since "abc" + "xyz" + "" + "" can't be aggregated. newSumList[2] = "1125.36" newSumList[3] = "397" even though the last value of the last nested list is "". 

Basically, summarize all the numerical values ​​in the nested lists for each index.

The only way I can think of is to repeat the iteration and keep the current amount, but I was wondering if I could do this using LINQ or something else.

Answer 1

Here you go.

 var list = new List<List<string>> { new List<string> {"tom", "abc", "$525.34", "$123"}, new List<string> {"dick", "xyz", "$100", "$234"}, new List<string> {"harry", "", "$250.01", "$40"}, new List<string> {"bob", "", "$250.01", ""} }; decimal num; var itemsPerLine = list[0].Count; // 4 var res = list.SelectMany(line => line); .Select((s, i) => new { Text = s, Index = i }) .GroupBy(i => i.Index % itemsPerLine) // transformed matrix here .Select(g => g.Sum(i => decimal.TryParse(i.Text, NumberStyles.AllowCurrencySymbol | NumberStyles.AllowDecimalPoint, new CultureInfo("en-US"), out num) ? num : 0)); 

You can, of course, specify what should be recognized as a number by changing the NumberStyles flags and culture information.

Answer 2

Try the following: -

 decimal _temp =0; int ListLength = _DataCollection.First().Count(); var query = _DataCollection.SelectMany(x => x). Select((v, i) => new { Val = v, Index = i % ListLength }) .GroupBy(x => x.Index) .Select(z => z.Sum(y => decimal.TryParse(y.Val,out _temp) ? _temp : 0)); 

Fiddle work.

Answer 3

Or, for another approach that does not require sequence GroupBy with GroupBy :

  var culture = new CultureInfo("en-US"); List<string> sums = _DataCollection.Count == 0 ? new List<string>() : Enumerable.Range(0, _DataCollection.First().Count) .Select(i => _DataCollection.Select(list => list[i]) .Select(s => { decimal val; return string.IsNullOrEmpty(s) ? (decimal?)0 : decimal.TryParse(s, NumberStyles.Currency, culture, out val) ? (decimal?)val : (decimal?)null; }) .Aggregate((decimal?)0, (sum, val) => sum + val)) .Select(sum => sum.HasValue ? sum.Value.ToString(culture) : "N/A") .ToList(); 

Answer 4

 List<List<string>> _DataCollection=new List<List<string>>(); _DataCollection.Add( new List<string> {"tom", "abc", "$525.34", "$123"}); _DataCollection.Add( new List<string> {"dick", "xyz", "$100", "$234"}); _DataCollection.Add( new List<string> {"harry", "", "$250.01", "$40"}); _DataCollection.Add( new List<string> {"bob", "", "$250.01", ""}); List<string> newSumList = new List<string>(); for (int i = 0; i < _DataCollection.Count; i++) { decimal Sum = 0; string CurrentSumList; string Comment; decimal amount = 0; for (int j = 0; j < _DataCollection.Count; j++) { bool IsDecimalAmount=decimal.TryParse( _DataCollection[j][i].Replace('$','0'),out amount); if (IsDecimalAmount) { Sum += amount; } else { Comment = "String"; } } CurrentSumList = Sum.ToString(); newSumList.Add(CurrentSumList); } 

I implemented this and it gives me the result.

Answer 5

 // Replace $value with value and remove all non-value strings var dollars = _DataCollection .Select(l => l.Select(str => str.Contains('$') ? str.Split('$')[1] : string.Empty)); var newSumList = new List<double>(); // Add all values in a new list for (int i = 0; i < _DataCollection[0].Count; i++) { double toAdd = 0; foreach (var entry in dollars) { // If entry is a value, parse it, 0 otherwise var value = entry.ElementAt(i) != string.Empty ? double.Parse(entry.ElementAt(i)) : 0; toAdd = toAdd + value; } newSumList.Add(toAdd); } newSumList.ForEach(Console.WriteLine); 

Answer 6

without repeating several times and supporting empty source lists

 var list = new List<string[]> { new [] {"tom", "abc", "$525.34", "$123"}, new [] {"dick", "xyz", "$100", "$234"}, new [] {"harry", "", "$250.01", "$40"}, new [] {"bob", "", "$250.01", ""} }; var cutlure = new CultureInfo("en-US"); var result = list.Aggregate((decimal[])null, (sums, strings) => { if (sums == null) sums = Enumerable.Repeat(decimal.MinValue, strings.Length).ToArray(); for (int i = 0; i < strings.Length; i++) { decimal value; if (decimal.TryParse(strings[i], NumberStyles.Currency, cutlure, out value)) sums[i] = (sums[i] == decimal.MinValue) ? value : sums[i] + value; } return sums; }, sums => sums.Select(sum => (sum == decimal.MinValue) ? "N/A" : sum.ToString()).ToArray());