如何将数据表的行转换为列

Question

我有一个csv文件，它有多个列与数据

Name, Description, Date
"ABC", "Hello testing", "2022-09-14"
"ABC", "Hello testing123", "2022-09-15"
"ABC", "Hello testing245", "2022-09-16"
"ABC1", "Hello testing", "2022-09-14"
"ABC1", "Hello testing143", "2022-09-16"
"ABC2", "Hello testing2", "2022-09-14"
"ABC2", "Hello testing2", "2022-09-15"

我想将Name值更改为列，并相应地添加行数据

当前输出:

ABC                ,ABC1              ,ABC2
"Hello testing"    , "Hello testing"  , "Hello testing" 
"Hello testing123", "Hello testing123","Hello testing123"
"Hello testing245", "Hello testing245","Hello testing245"
"Hello testing"   ,  "Hello testing"  , "Hello testing"
"Hello testing143",  "Hello testing143",  "Hello testing143"
"Hello testing2"  ,  "Hello testing2"  ,"Hello testing2" 
"Hello testing2"  ,  "Hello testing2"  ,  "Hello testing2"

我需要添加列名称值作为标题，然后根据示例输出添加描述和日期

样本输出:

Date ,ABC               ,ABC1           ,ABC2
14,   Hello testing,    Hello testing   ,Hello testing2
15,   Hello testing123,                 ,Hello testing2
16,   Hello testing245, Hello testing143

我已经读了csv，然后我的输出表显示相同的数据在所有行的所有列。我怎么能分组它根据名称?

代码:

using (var reader = new StreamReader("data.csv"))
using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
{

// Extract the column headers from the first row
csv.Read();
csv.ReadHeader();
int columnNameIndex = 0; //name
int columnValueIndex = 2; //description
DataTable inputTable = new DataTable();
foreach (string header in csv.HeaderRecord)
{
inputTable.Columns.Add(header);
}
while (csv.Read())
{
DataRow row = inputTable.NewRow();
foreach (DataColumn column in inputTable.Columns)
{
row[column.ColumnName] = csv.GetField(column.DataType, column.ColumnName);
}
inputTable.Rows.Add(row);
}
var outputTable = new DataTable();
//Get the names of the columns for the output table
var columnNames = inputTable.AsEnumerable().Where(x => x[columnNameIndex] != DBNull.Value && x[columnValueIndex] != DBNull.Value)
.Select(x => x[columnNameIndex].ToString()).Distinct().ToList();
DataRow outputRow = outputTable.NewRow();
//create the columns in the output table
foreach (var columnName in columnNames)
{
outputTable.Columns.Add(new DataColumn(columnName));
}
//get all the rows in the input table
var totalRows = inputTable.Rows.Count;
//loop through the input table
for (int n = 0; n < totalRows; n++)
{
//loop through each columnname for each row
for (int i = 0; i < columnNames.Count; i++)
{
//if it's the first loop we need a new row
if (i == 0)
{
outputRow = outputTable.NewRow();
}
outputRow[columnNames[i]] = inputTable.Rows[n][columnValueIndex].ToString();
//^^get the corresponding value from the input table
//confusion at this stage
if (i == columnNames.Count - 1)
outputTable.Rows.Add(outputRow);
}
}

}
}

Answer 1

一个解决方案:

//a way to read csv file
IEnumerable<(string id, string desc, string date)> ds = File.ReadAllLines("csv.txt")
.Skip(1)
.Select(x => x.Replace(""", ""))
.Select(x => (x.Split(',')[0], x.Split(',')[1], x.Split(',')[2]));
//get title of each column from dynamic values (ABC, ABC1,....)
var titles = ds.Select(x => x.id).Distinct().OrderBy(x => x);
//sort values by date then by id (ABC,ABC1...) and group by date
var dd = ds.OrderBy(x => x.date).ThenBy(x => x.id).GroupBy(x => x.date);
DataTable workTable = new DataTable("CSV");
workTable.Columns.Add(new DataColumn("date"));
foreach(var x in titles)
workTable.Columns.Add(new DataColumn(x));
//load the datable
foreach(var d in dd)
{
DataRow row = workTable.NewRow();
row["date"] = d.Key.Trim();
foreach (var v in d)
{
row[v.id] = v.desc.Trim();                   
}
workTable.Rows.Add(row);
}
//show the content of datable
foreach (DataRow row in workTable.Rows)
{
string date = row["date"].ToString();
Console.Write($"{date}");
foreach (var t in titles)
{
Console.Write($" -> {t} -> [{row[t]}]");
}
Console.WriteLine();
}

结果:

2022-09-14 -> ABC -> [Hello testing] -> ABC1 -> [Hello testing] -> ABC2 -> [Hello testing2]
2022-09-15 -> ABC -> [Hello testing123] -> ABC1 -> [] -> ABC2 -> [Hello testing2]
2022-09-16 -> ABC -> [Hello testing245] -> ABC1 -> [Hello testing143] -> ABC2 -> []

根据你的喜好调整你的输出