參考引用來源:C#中利用DataTable.Compute()方法對資料欄位做數值計算
--
1.前言:
如果要對DataTable中的特定欄位做資料分析,在不使用其他第三方函式庫的情況下,可以用Compute方法來做基本的數值計算。
2.說明:
用法: DataTable.Compute( string expression, string filter)
支援以下聚合函數:
•Sum (Sum)
•Avg (Average)
•Min (Minimum)
•Max (Maximum)
•Count (Count)
•StDev (Statistical standard deviation)
•Var (Statistical variance)
使用上要注意的是欄位的型態需要是數值型態,如果是字串型態,可以簡單定義一個新欄位為數值型,再複製要計算的欄位資料做資料分析,否則會出現彙總函式和型別的無效用法: String錯誤訊息。
有關DataTable.Compute()的說明可參考:
http://msdn.microsoft.com/en-us/library/system.data.datatable.compute(v=vs.100).aspx
程式碼:
private void btCalc_Click(object sender, EventArgs e)
{
double mean, stdev, max, min, variance, count, sum;
DataTable dt = TxtConvertToDataTable(@"d:\tmp\dt.csv", "tmp", ",");//讀取資料
dt.Columns.Add("tmpColumn", typeof(double), "Convert(data, 'System.Double')");//加入暫存欄位,將資料中string的型態轉為double型態
mean = (double)dt.Compute("Avg(tmpColumn)", string.Empty);
stdev = (double)dt.Compute("Stdev(tmpColumn)", string.Empty);
max = (double)dt.Compute("Max(tmpColumn)", string.Empty);
min = (double)dt.Compute("Min(tmpColumn)", string.Empty);
variance = (double)dt.Compute("Var(tmpColumn)", string.Empty);
count = (int)dt.Compute("Count(tmpColumn)", string.Empty);
sum = (double)dt.Compute("Sum(tmpColumn)", string.Empty);
dt.Columns.Remove("tmpColumn");//移除暫存欄位
MessageBox.Show("Mean: "+mean+"\r\n"
+ "Stdev: " + stdev + "\r\n"
+ "Max: " + max + "\r\n"
+ "Min: " + min + "\r\n"
+ "Variance: " + variance + "\r\n"
+ "Count: " + count + "\r\n"
+ "Sum: " + sum + "\r\n"
);
}
public DataTable TxtConvertToDataTable(string File, string TableName, string delimiter)
{
DataTable dt = new DataTable();
DataSet ds = new DataSet();
StreamReader s = new StreamReader(File, System.Text.Encoding.Default);
string[] columns = s.ReadLine().Split(delimiter.ToCharArray());
ds.Tables.Add(TableName);
foreach (string col in columns)
{
bool added = false;
string next = "";
int i = 0;
while (!added)
{
string columnname = col + next;
columnname = columnname.Replace("#", "");
columnname = columnname.Replace("'", "");
columnname = columnname.Replace("&", "");
if (!ds.Tables[TableName].Columns.Contains(columnname))
{
ds.Tables[TableName].Columns.Add(columnname.ToUpper());
added = true;
}
else
{
i++;
next = "_" + i.ToString();
}
}
}
string AllData = s.ReadToEnd();
string[] rows = AllData.Split("\n".ToCharArray());
foreach (string r in rows)
{
string[] items = r.Split(delimiter.ToCharArray());
ds.Tables[TableName].Rows.Add(items);
}
s.Close();
dt = ds.Tables[0];
return dt;
}
沒有留言:
張貼留言