C＃加载csv文件并分组并汇总结果 [英] C# load csv file and group by and aggregate results

问题描述

如果我有一个文件：

if I have a file:

name    state      ClosetSize    childGender
Hank    OK         2               M
Hank    OK         1               F
Wilbur  TX         1               F
Wilbur  TX         2               F
Tom     CA         3               M
Tom     CA         4               M
Zed     VT         1               M
Zed     VT         2               M
Zed     VT         3               F

我想输出一个文件，如果仅有人的总壁橱大小至少有一个女性（F）和至少一个（M）。然后输出到另一个csv。文件。



我希望这只是Hank和Zed



我有什么尝试过：



我已经尝试将其加载到列表中并使用Linq

I want to output a file if the combined total closet size of only the people that has BOTH at least one female (F) and at least one (M). Then output that to another csv. file.

I would expect that to be only Hank and Zed

What I have tried:

I've tried loading this into a list and using Linq

推荐答案

尝试

try

DataTable dt = new DataTable();
           dt.Columns.Add("name");
           dt.Columns.Add("state");
           dt.Columns.Add("ClosetSize");
           dt.Columns.Add("childGender");
           dt.Rows.Add("Hank", "OK", 2, "M");
           dt.Rows.Add("Hank", "OK", 1, "F");
           dt.Rows.Add("Wilbur", "tx", 1, "F");
           dt.Rows.Add("Wilbur", "tx", 2, "F");
           dt.Rows.Add("Tom", "ca", 3, "M");
           dt.Rows.Add("Tom", "ca", 4, "M");
           dt.Rows.Add("Zed", "VT", 1, "M");
           dt.Rows.Add("Zed", "VT", 2, "M");
           dt.Rows.Add("Zed", "VT", 3, "F");

           DataTable dtOutput = dt.Clone();
           string[] names = dt.Rows.OfType<DataRow>().Select(k => k["name"] + "").Distinct().ToArray();
           foreach (string name in names)
           {
               var tempRows = dt.Select("name='" + name + "'");
               if (tempRows.Length > 1)
               {
                   string[] genders = tempRows.Select(k => k["childGender"] + "").Distinct().ToArray();
                   if (genders.Contains("M") && genders.Contains("F"))
                       foreach (DataRow row in tempRows)
                           dtOutput.Rows.Add(row.ItemArray);
               }
           }

将此转换为将csv转换为datatable，反之亦然

将CSV文件读入数据表 [ ^ ]

使用扩展方法将数据表导出为CSV [ ^ ]

另一种选择，使用 CsvHelper [ ^ ]：

Another option, using CsvHelper[^]:

struct SourceRecord
{
    public string name { get; set; }
    public string state { get; set; }
    public int ClosetSize { get; set; }
    public char childGender { get; set; }
    
    public static IReadOnlyCollection<SourceRecord> Load(TextReader reader)
    {
        var csv = new CsvReader(reader);
        return csv.GetRecords<SourceRecord>().ToList();
    }
    
    public static IReadOnlyCollection<SourceRecord> LoadFrom(string fileName)
    {
        using (var reader = File.OpenText(fileName))
        {
            return Load(reader);
        }
    }
}

struct OutputRecord
{
    public string name { get; set; }
    public string state { get; set; }
    public int ClosetSize { get; set; }
    
    public static void Save(TextWriter writer, IEnumerable<OutputRecord> records)
    {
        var csv = new CsvWriter(writer);
        csv.WriteRecords(records);
    }
    
    public static void SaveTo(string fileName, IEnumerable<OutputRecord> records)
    {
        using (var writer = new StreamWriter(fileName))
        {
            Save(writer, records);
        }
    }
}

static void Process(string inputFile, string outputFile)
{
    IReadOnlyCollection<SourceRecord> input = SourceRecord.LoadFrom(inputFile);
    
    IEnumerable<OutputRecord> output = input
        .GroupBy(r => new { r.name, r.state })
        .Where(g => g.Any(r => r.childGender == 'M') && g.Any(r => r.childGender == 'F'))
        .Select(g => new OutputRecord
        {
            name = g.Key.name,
            state = g.Key.state,
            ClosetSize = g.Sum(r => r.ClosetSize),
        });
    
    OutputRecord.SaveTo(outputFile, output);
}

输出：

Output:

name,state,ClosetSize
Hank,OK,3
Zed,VT,6

这篇关于C＃加载csv文件并分组并汇总结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！