I have a datatable that was imported from a CSV file. There are a multitude of different tables that can be imported.
The datatables have four columns (type=double): LookupColumn L M S
LookupColumn will usually have a unique name (e.g., Length, Height, Weight). The other column names remain the same. This is immaterial as you can just use dt.Column[0] mostly. The lookup column will always be the first column imported.
I need to search the datatable on LookupColumn for a LookupValue passed from the app (from a textbox).
If a LookupValue matches exactly a number in LookupColumn, then, return the values for L, M, S.
If there is no match, then I need to find the rows on either side of where the LookupValue would lie and return the min/max values for each variable in L,M,S.
Once I have those, I can interpolate the values for L, M, S.
For example:
| Col_0 | L | M | S |
|---|---|---|---|
| 45.0 | -0.3521 | 2.441 | 0.09182 |
| 45.5 | -0.3521 | 2.524 | 0.09153 |
| 46.0 | -0.3521 | 2.608 | 0.09124 |
| 46.5 | -0.3521 | 2.691 | 0.09094 |
| 47.0 | -0.3521 | 2.776 | 0.09065 |
| 47.5 | -0.3521 | 2.861 | 0.09036 |
| 48.0 | -0.3521 | 2.948 | 0.09007 |
If my LookupValue in Col[0] = 46.5, the program would return L=-0.3521 M=2.691 S=0.09094
These values will be put in textboxes on the form the viewer sees.
If there was no match (assuming LookupValue was within the range LookupColumn min/max) then I need to return the rows on both sides of where the value would lie if it were present--that is, Lmin Lmax, Mmin Mmax, Smin Smax and use those in the following formula to get the interpolated value (IntVal) for LookupColumn (Col_0).
For example, if the LookupValue in (Col_0) = 46.8, the returned results (array?, list?) would be the rows where Col_0 = 46.5 and 47.0:
| Col_0 | L Values | M Values | S Values |
|---|---|---|---|
| LookupMin = 46.5 | Lmin = -0.3521 | Mmin = 2.691 | Smin = 0.09094 |
| LookupMax = 47.0 | Lmax = -0.3521 | Mmax = 2.776 | Smax = 0.09065 |
Interpolated Value = LMSmin + (46.8 - LookupMin) * (LMSmax - LMSmin / LookupMax - LookupMin)
Interpolated L = -0.3521 because Lmin = Lmax
Interpolated M = 2.691 + (46.8 - 46.5) * (2.776 - 2.691 / 47.0 - 46.5)
Interpolated M = 2.7418
Interpolated S = 0.09094 + (46.8 - 46.5) * (0.09065 - 0.09094 / 47.0 - 46.5)
Interpolated S = 0.09088
So, given the Min/Max values for Col_0 and either L, M, or S min/max values, I can interpolate any value the user provides that isn't in the lookup even if the LookupValue has more decimals. The interpolated L,M,S values will be put in textboxes for the user.
I have a bit of code that works when there's a match, but, I think there's a better/more concise way either using Linq or Tuples. I realize this isn't the best code and I'm open to suggestions.
I have scoured StackOverflow and found several posts on interpolation and Lookup Tables. It seems that the best practice for the lookup is to use a tuple, but, I'm not very clear on on their use.
For the most part, this question is focused on returning the Min/Max values of the lookup when there's no match. Once I have those, I don't think the interpolation is a big feat as I know the formula. Also, I know that the user could enter values out of range--I will account for those issues later.
Any help is appreciated.
private void tbLookup_Leave(object sender, System.EventArgs e)
{
string colName = tmpDT.Columns[0].ColumnName;
string colSearch = colName + " = '" + tbLookup.Text + "'";
if (tbLookup.Text.Length > 0)
{
// Exact match
while (true)
{
DataRow[] foundRow = tmpDT.Select(colSearch);
if (foundRow.Length == 0)
{
break;
}
foreach (DataRow row in foundRow)
{
string L = row.Field<string>("L");
string M = row.Field<string>("M");
string S = row.Field<string>("S");
tbLkupL.Text = L;
tbLkupM.Text = M;
tbLkupS.Text = S;
}
// No match
// Call interpolation method
}
}
else
{
MessageBox.Show("Please enter a lookup value", "Missing Data");
}
You inquired about possibly using LINQ, so I checked my code chest and found something similar, that I adapted to your needs.
using System.Linq; // Add this at the top of the Program.cs file.
The extension method returns three output parameters, that contain found indices, or -1 if not found.
// An extension methods class must be the first class in a file.
// Add this class inside the namespace of a console app, before the Program class (in the Program.cs file).
public static class ExtensionMethods
{
public static bool GetNearestOrEqual<TSource, TValue>(this System.Collections.Generic.IEnumerable<TSource> source, System.Func<TSource, TValue> valueSelector, TValue referenceValue, out int indexOfLowerMax, out int indexOfEqual, out int indexOfHigherMin)
where TValue : struct, System.IComparable<TValue>
{
using var e = source.GetEnumerator();
var ltCurrent = new TValue?();
var gtCurrent = new TValue?();
indexOfLowerMax = -1;
indexOfEqual = -1;
indexOfHigherMin = -1;
var index = 0;
while (e.MoveNext())
{
var currentValue = valueSelector(e.Current);
switch (currentValue.CompareTo(referenceValue))
{
case int lo when lo < 0:
if (!ltCurrent.HasValue || currentValue.CompareTo(ltCurrent.Value) > 0)
{
indexOfLowerMax = index;
ltCurrent = currentValue;
}
break;
case int hi when hi > 0:
if (!gtCurrent.HasValue || currentValue.CompareTo(gtCurrent.Value) < 0)
{
indexOfHigherMin = index;
gtCurrent = currentValue;
}
break;
default:
indexOfEqual = index;
break;
}
index++;
}
return indexOfLowerMax != -1 || indexOfEqual != -1 || indexOfHigherMin != -1;
}
}
Sample of how you could use it (created a simple console app):
// Replace the Main() inside the Program class of a console app.
static void Main(string[] args)
{
var dt = new System.Data.DataTable();
dt.Columns.Add("Col_0", typeof(double));
dt.Columns.Add("L", typeof(double));
dt.Columns.Add("M", typeof(double));
dt.Columns.Add("S", typeof(double));
dt.Rows.Add(new object[] { 45.0, -0.3521, 2.441, 0.09182 });
dt.Rows.Add(new object[] { 45.5, -0.3521, 2.524, 0.09153 });
dt.Rows.Add(new object[] { 46.0, -0.3521, 2.608, 0.09124 });
dt.Rows.Add(new object[] { 46.5, -0.3521, 2.691, 0.09094 });
dt.Rows.Add(new object[] { 47.0, -0.3521, 2.776, 0.09065 });
dt.Rows.Add(new object[] { 47.5, -0.3521, 2.861, 0.09036 });
dt.Rows.Add(new object[] { 48.0, -0.3521, 2.948, 0.09007 });
var lookupValue = 46.8;
var foundAnything = dt.Rows.Cast<System.Data.DataRow>().GetNearestOrEqual(o => (double)o.ItemArray[0], lookupValue, out var indexOfLowerMax, out var indexOfEqual, out var indexOfHigherMin);
// Assuming example for when both low and high are found...
var dr = dt.NewRow();
var lookuploDiff = lookupValue - (double)dt.Rows[indexOfLowerMax][0];
var hiloDiff = (double)dt.Rows[indexOfHigherMin][0] - (double)dt.Rows[indexOfLowerMax][0];
dr.ItemArray = new object[] {
lookupValue,
(double)dt.Rows[indexOfLowerMax][1] + lookuploDiff * (((double)dt.Rows[indexOfHigherMin][1] - (double)dt.Rows[indexOfLowerMax][1]) / hiloDiff),
(double)dt.Rows[indexOfLowerMax][2] + lookuploDiff * (((double)dt.Rows[indexOfHigherMin][2] - (double)dt.Rows[indexOfLowerMax][2]) / hiloDiff),
(double)dt.Rows[indexOfLowerMax][3] + lookuploDiff * (((double)dt.Rows[indexOfHigherMin][3] - (double)dt.Rows[indexOfLowerMax][3]) / hiloDiff),
};
dt.Rows.InsertAt(dr, indexOfHigherMin);
}
As always, if there are any questions, this is the place. :)