在c#中使用LINQ / GroupBy创建一个简单的csv文件

Question

我有csv文件之前处理在这个格式:

支出tbody> <<tr>约翰迈克尔史蒂文史蒂文

名称	类别
约翰	100	杂货店
	50	餐厅
	55	拼车
	15	杂货店
	50	餐厅

比起使用csv进行实验，我创建了一个ValueTuple列表，如下所示:

var source = new List<(string Name, double Spending, string Category)>()
{
("John", 100, "Grocery"),
("John", 50, "Dining"),
("Michael", 55, "Carpooling"),
("Steven", 15, "Grocery"),
("Steven", 50, "Dining"),
};

使用linq，你可以用下面的查询来解决这个问题:

var target =
from @record in source
group @record by @record.Name into customerRecords
let totalSpending = customerRecords.Sum(r => r.Spending)
let ratio = totalSpending < 60 ? 1 : 60 / totalSpending
from customer in customerRecords
select (customer.Name, customer.Spending * ratio, customer.Category);

• 首先让我们按名称
对记录进行分组那么让我们计算一下总支出
• 由此我们可以计算出比值
  • 如果总支出小于60，则比率为1
• 最后，让我们遍历分组并生成一个新的元组，其值为调整后的

让我们打印出来:

foreach (var item in target) Console.WriteLine(item);

则输出如下:

(John, 40, Grocery)
(John, 20, Dining)
(Michael, 55, Carpooling)
(Steven, 13.846153846153847, Grocery)
(Steven, 46.15384615384615, Dining)

---

更新转换为csv

如果你不想使用第三方库，那么你可以这样转换:

var csvBuilder = new StringBuilder();
csvBuilder.AppendLine("Name,Spending,Category");
foreach (var item in target)
{
csvBuilder.AppendLine(string.Join(",", item.Name, item.Item2, item.Category));
}
File.WriteAllText("output.csv", csvBuilder.ToString());

如果你愿意学习第三方库，那么我会推荐CsvHelper

我的回答是:

namespace Query_csv
{
class Program
{
static void Main(string[] args)
{
//Reading csv file
string[] rawcsv = System.IO.File.ReadAllLines(@"Customer Spending.csv");

//Creating a list of Value Tuples
List<(string Name, double Spending , string Category)> customer_vtuples_list = new List<(string, double, string)>();
//Skip header and print all rows as you see in Notepad using for loop
//Console.WriteLine("Print each row on a new line:  ");
for (int i = 1; i < rawcsv.Length; i++)
{
string[] customerSplitData = rawcsv[i].Split(',');
//Console.WriteLine(rawcsv[i]);

double convertedcustomerSpending = Convert.ToDouble(customerSplitData[1]);
customer_vtuples_list.Add((customerSplitData[0], convertedcustomerSpending, customerSplitData[2]));
}

for( int v = 0; v < customer_vtuples_list.Count; v++)
{
//Console.WriteLine("HelloWorld!");
//Console.WriteLine(customer_vtuples_list[v]);
//Console.WriteLine(customer_vtuples_list[v].Name);
}
//Creating a list of new target values
var target = from @record in customer_vtuples_list group @record by @record.Name into customerRecords let totalSpending = customerRecords.Sum(r => r.Spending) let ratio = totalSpending < 60 ? 1 : 60 / totalSpending from customer in customerRecords select (customer.Name, customer.Spending * ratio, customer.Category);
foreach (var item in target) Console.WriteLine(item);
Console.ReadKey();
}
}
}