一、概述:
在python转c#时,python中pandas.merge可以按主键合并两个datatable,苦苦找了很久,希望c#也有同样的函数,未果,就自己写了一个,目前测试没问题,同样我也考虑了效率和简洁的问题。
二、原理:
(1)封装的函数如下:传入两个datatable和主键,返回一个datatable
private DataTable unite_on_datatable(DataTable dt1,DataTable dt2,string key)
(2)先将dt1和dt2按照主键排序,声明一个新的datatable叫dt3,dt3包含了所有的列名,然后dt1和dt2从上到下遍历一遍,将所有相同的主键的列全部加到dt3中
三、展示结果:
四、代码:
//对两个datatable进行排序
private DataTable unite_on_datatable(DataTable dt1, DataTable dt2, string key)
{
DataTable dt3 = dt1.Clone();
for (int i = 0; i < dt2.Columns.Count; i++)
{
if (dt2.Columns[i].ColumnName.ToLower() != key.ToLower())
{
dt3.Columns.Add(dt2.Columns[i].ColumnName);
}
}
//先排序
dt1 = sort_desc(dt1, key);
dt2 = sort_desc(dt2, key);
int count1 = 0, count2 = 0;
while (true)
{
string heatid1 = dt1.Rows[count1][key].ToString();
string heatid2= dt2.Rows[count2][key].ToString();
if (count1 >= (dt1.Rows.Count) || (count2 >= (dt2.Rows.Count)))
return dt3;
if (string.Compare(dt1.Rows[count1][key].ToString(), dt2.Rows[count2][key].ToString()) > 0)
{
//找到
while (string.Compare(dt1.Rows[count1][key].ToString(), dt2.Rows[count2][key].ToString()) > 0)
{
string heat_id1 = dt1.Rows[count1][key].ToString();
string heat_id2 = dt2.Rows[count2][key].ToString();
count1++;
if (count1 >= dt1.Rows.Count)
return dt3;
}
while (string.Compare(dt1.Rows[count1][key].ToString(), dt2.Rows[count2][key].ToString()) < 0)
{
count2++;
if (count2 >= dt2.Rows.Count)
return dt3;
}
}
else if(string.Compare(dt1.Rows[count1][key].ToString(), dt2.Rows[count2][key].ToString()) < 0)
{
while (string.Compare(dt1.Rows[count1][key].ToString(), dt2.Rows[count2][key].ToString()) < 0)
{
count2++;
if (count2 >= dt2.Rows.Count)
return dt3;
}
//找到
while (string.Compare(dt1.Rows[count1][key].ToString(), dt2.Rows[count2][key].ToString()) > 0)
{
count1++;
if (count1 >= dt1.Rows.Count)
return dt3;
}
}
if (string.Compare(dt1.Rows[count1][key].ToString(), dt2.Rows[count2][key].ToString()) == 0)
{
//赋值给新的一列
DataRow dr = dt3.NewRow();
for (int i = 0; i < dt1.Columns.Count; i++)
{
dr[dt1.Columns[i].ColumnName] = dt1.Rows[count1][dt1.Columns[i].ColumnName];
}
for (int i = 0; i < dt2.Columns.Count; i++)
{
dr[dt2.Columns[i].ColumnName] = dt2.Rows[count2][dt2.Columns[i].ColumnName];
}
dt3.Rows.Add(dr.ItemArray);
count1++;
count2++;
}
}
}
//对DataTable排序
private DataTable sort_desc(DataTable dt1, string key)
{
DataTable dt2 = dt1.Clone();
DataRow[] dr = dt1.Select("", key + " desc");
for (int i = 0; i < dr.Length; i++)
{
dt2.Rows.Add(dr[i].ItemArray);
}
return dt2;
}
五、总结
准备写好更多datatable合并类似于pandas.merge的例子,封装成一个dll文件。整体的文件见:https://github.com/cysisu/datatable-,后续会继续更新