2 Dimensional Lookup Tables
Prerequisites
Uses the XmlPropertyObject outlined at Topic – Advanced – The XmlPropertyObject
Summary
Demonstrates how to create a 2-D lookup table and perform multi-dimensional interpolation.
Video
Reference Materials
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using SMADA2013.App_Code;
namespace SMADA2013.App_Code
{
public class LookupTable :XmlPropertyObject
{
/* This class is a base for a lookup table in 2 dimensions
The table allows values to be looked up in the table and
interpolated values obtained. Example would be
*
Curve Number
* 10 20 30 40 50
* 10 0.2 0.3 0.4 0.5 0.6
* DCIA 20 0.4 0.5 0.6 0.7 0.8
* % 30 0.5 0.6 0.7 0.8 0.9
*
* For Curve Number = 30
* DCIA = 20
*
* Lookup Value = 0.6
*
* RowData is new line delimited
* ColumnData is new line delimited
* TableData is tab (columns) and new line (rows) delimited
*
*/
public const string SessionId = "LookupTableID";
public string RowTitle { get; set; }
public string RowDescription { get; set; }
public string RowData { get; set; }
public int RowDataDecimalPlaces { get; set; }
public string ColumnTitle{ get; set; }
public string ColumnDescription{ get; set; }
public string ColumnData{ get; set; }
public int ColumnDataDecimalPlaces { get; set; }
public string TableData { get; set; }
public int TableDataDecimalPlaces { get; set; }
public override Dictionary<string, string> InputProperties()
{
throw new NotImplementedException();
}
public override Dictionary<string, string> PropertyLabels()
{
return new Dictionary<string, string>
{
{"Name", "Title"},
{"RowTitle", "Row Title"},
{"RowDescription", "Row Description"},
{"ColumnTitle", "Column Title"},
{"ColumnDescription", "Column Description"},
};
}
public override Dictionary<string, int> PropertyDecimalPlaces()
{
return new Dictionary<string, int>
{
{"RowData", RowDataDecimalPlaces},
{"ColumnData", ColumnDataDecimalPlaces},
{"TableData", ColumnDataDecimalPlaces}
};
}
public double Calculate(double rowValue, double columnValue)
{
var rowValues = AsDoubleArray(RowData);
double[] rowInterpolated = CreateInterpolationRow(rowValue, rowValues);
var colValues = AsDoubleArray(ColumnData);
return InterpolationValue(columnValue, colValues, rowInterpolated);
}
public string ImportData(string data, int decimalPlaces = 2)
{
data = data.TrimEnd();
data = data.TrimStart();
while (data.Contains(" "))
{
data = data.Replace(" ", " ");
}
string finalData = String.Empty;
string formatString = "{0:N" + decimalPlaces.ToString().Trim() + "}";
// makes a string array of all values
string[] rowVals = data.Split(new String[] { "\n" }, StringSplitOptions.RemoveEmptyEntries);
foreach (string row in rowVals)
{
var r = new ArrayList();
string[] colVals = row.Split(new String[] { " ", ";", ",", "\t" }, StringSplitOptions.RemoveEmptyEntries);
string rowString = String.Empty;
foreach (string colVal in colVals)
{
r.Add(colVal);
}
foreach (string colVal in r)
{
double tempVal = Convert.ToDouble(colVal);
string formattedVal = String.Format(formatString, (double) tempVal);
rowString += formattedVal + "\t";
}
finalData += rowString.Trim();
finalData += "\n";
}
finalData += finalData.Trim();
return finalData;
}
public int FindIndex(double value, double[] values)
{
if (value <= values[0]) return 0;
if (value >= values[values.Length - 1]) return values.Length - 1;
for (int i = values.Length-1; i >= 0 ; i--)
{
if (value >= values[i]) return i;
}
return 0;
}
public double InterpolationFactor(double value, double[] values)
{
// this finds the fraction difference a value lies between 2 points
int baseIndex = FindIndex(value, values);
if (values.Length == 1) return values[0];
if (baseIndex == 0) return values[0];
// First make sure not over limit on size, if so extrapolate
if (baseIndex == values.Length - 1)
{
if (values[baseIndex] == values[baseIndex - 1]) return 0;
return (value - values[baseIndex - 1]) / (values[baseIndex] - values[baseIndex - 1]);
}
if (values[baseIndex + 1] == values[baseIndex]) return 0;
return (value - values[baseIndex])/(values[baseIndex+1] - values[baseIndex]);
}
public double InterpolationValue(double value, double[] indexValues, double[] lookupValues)
{
// Performs and returns the actual interpolated value, index and lookupvalues must
// have same length
int index = FindIndex(value, indexValues);
double factor = InterpolationFactor(value, indexValues);
if (factor == 0) return lookupValues[index];
if (index == 0) return lookupValues[0];
if (indexValues.Length == 1) return lookupValues[0];
//if (index == indexValues.Length - 1) return lookupValues[indexValues.Length - 1];
// Extrapolate
if (index == indexValues.Length - 1)
return lookupValues[index] + factor*(lookupValues[index] - lookupValues[index - 1]);
// Interpolate
return lookupValues[index] + factor * (lookupValues[index+1] - lookupValues[index]);
}
public double[] CreateInterpolationRow(double rowValue, double[] rowValues)
{
// This creates a new row - by interpolating the row Value against the
// the row values - it creates an interpolation factor and a base index.
// It then must apply this for every column to create a row value
int rowIndex = FindIndex(rowValue, rowValues);
double rowFactor = InterpolationFactor(rowValue, rowValues);
int nRows = AsDoubleArray(RowData).Length;
int nCols = AsDoubleArray(ColumnData).Length;
double[,] data = AsDoubleArray2D(TableData);
var row = new double[nCols];
for (int i = 0; i <= nCols - 1; i++)
{
// lookupValues is the column as double[]
var lookupValues = GetColumnData(i, nRows, data);
row[i] = InterpolationValue(rowValue, rowValues, lookupValues);
}
return row;
}
public string CalculatedRowValues(double rv)
{
return AsString(CreateInterpolationRow(rv, AsDoubleArray(RowData)), TableDataDecimalPlaces);
}
public double[] GetColumnData(int index, int nRows, double[,] values)
{
var ret = new double[nRows];
for (int i = 0; i < nRows; i++)
{
ret[i] = values[i, index];
}
return ret;
}
public double[,] AsDoubleArray2D(string values)
{
string s = values;
s = s.TrimEnd();
s = s.TrimStart();
while (s.Contains(" "))
{
s = s.Replace(" ", " ");
}
// makes a string array of all values
string[] rowVals = s.Split(new String[] {"\n"}, StringSplitOptions.RemoveEmptyEntries);
string[] temp = rowVals[0].Split(new String[] {"\t"}, StringSplitOptions.RemoveEmptyEntries);
// dimensioned retVals[rowIndex, columnIndex]
double[,] returnValues = new double[rowVals.Length,temp.Length];
// Reinitialize the ArrayList
var arraylist = new ArrayList();
int rowIndex = 0;
foreach (string rowVal in rowVals)
{
int colIndex = 0;
string[] colVals = rowVal.Split(new String[] {"\t"}, StringSplitOptions.RemoveEmptyEntries);
foreach (string colVal in colVals)
{
returnValues[rowIndex, colIndex] = Convert.ToDouble(colVal);
colIndex++;
}
rowIndex++;
}
return returnValues;
}
public new string AsHtmlTable()
{
string s = "<table>";
foreach (KeyValuePair<string, string> pair in PropertyLabels())
{
s += "<tr>";
s += "<td>" + pair.Value + "</td>";
string v = GetValue(pair.Key);
s += "<td>" + v + "</td>";
s += "</tr>";
}
s += "<tr>";
s += "<td colspan='2'>";
s += LookupTableAsHtml();
s += "</td>";
s += "</tr>";
s += "</table>";
return s;
}
public string LookupTableAsHtml()
{
ArrayList rows = AsArrayList(RowData, "String");
ArrayList cols = AsArrayList(ColumnData, "String");
ArrayList rowValues = AsArrayList(TableData, "String", new string[] {"\n"});
string s ="<table>";
s +="<tr>";
s += "<td>" + RowTitle + "</td>";
s += "<td colspan='" + Convert.ToInt32(cols.Count) + "'>" + ColumnTitle + "</td>";
s += "</tr>";
s += "<tr>";
s += "<td></td>";
foreach (string col in cols)
{
s += "<td>" + col + "</td>";
}
s += "</tr>";
int i = 0;
foreach (string row in rows)
{
s += "<tr>";
s += "<td>" + row + "</td>";
ArrayList cellValues = AsArrayList(Convert.ToString(rowValues[i]), "String");
foreach (string cell in cellValues)
{
s += "<td>" + cell + "</td>";
}
s += "</tr>";
i++;
}
s += "</table>";
return s;
}
public void ImportFromExcel(string data)
{
/* Sample Import
*
* First Row is Name
* Second Row is Tab - then Row Title, Column Titles
* Third Row is Tab - then Column Values
* Remaining Rows are Row Value - then Table Values
* Example
Mean Annual Mass Removal Efficiencies for 0.25-inches of Retention for Zone 1
NDCIA CN Percent DCIA
5.0 10.0 15.0 20.0 25.0 30.0 35.0 40.0 45.0 50.0 55.0 60.0 65.0 70.0 75.0 80.0 85.0 90.0 95.0 100.0
30.0 86.20 81.30 73.30 65.50 58.70 53.00 48.30 44.20 40.80 37.90 35.30 33.10 31.10 29.40 27.80 26.40 25.10 24.00 22.90 21.90
35.0 81.60 78.70 71.70 64.50 58.00 52.50 47.90 44.00 40.60 37.70 35.20 33.00 31.00 29.30 27.80 26.40 25.10 23.90 22.90 21.90
40.0 76.40 75.50 69.60 63.10 57.10 51.90 47.40 43.60 40.30 37.50 35.00 32.90 30.90 29.20 27.70 26.30 25.10 23.90 22.90 21.90
45.0 70.70 71.70 67.20 61.40 55.90 51.00 46.80 43.10 40.00 37.20 34.80 32.70 30.80 29.10 27.60 26.30 25.00 23.90 22.90 21.90
50.0 64.70 67.50 64.20 59.40 54.50 50.00 46.00 42.60 39.50 36.90 34.60 32.50 30.70 29.00 27.50 26.20 25.00 23.90 22.90 21.90
55.0 58.60 62.80 60.90 57.00 52.70 48.70 45.10 41.80 39.00 36.50 34.20 32.30 30.50 28.90 27.40 26.10 24.90 23.90 22.90 21.90
60.0 52.80 57.80 57.10 54.20 50.70 47.10 43.90 40.90 38.30 35.90 33.80 31.90 30.20 28.70 27.30 26.00 24.90 23.80 22.80 21.90
65.0 47.30 52.60 53.00 51.10 48.30 45.30 42.50 39.80 37.40 35.30 33.30 31.50 29.90 28.40 27.10 25.90 24.80 23.80 22.80 21.90
70.0 42.20 47.30 48.60 47.60 45.60 43.20 40.80 38.50 36.40 34.40 32.60 31.00 29.50 28.10 26.90 25.70 24.70 23.70 22.80 21.90
75.0 37.80 42.20 43.90 43.70 42.40 40.70 38.80 36.90 35.10 33.40 31.80 30.40 29.00 27.80 26.60 25.50 24.50 23.60 22.70 21.90
80.0 34.00 37.50 39.10 39.40 38.80 37.70 36.40 34.90 33.50 32.10 30.80 29.50 28.30 27.20 26.20 25.20 24.30 23.50 22.70 21.90
85.0 30.80 33.10 34.30 34.80 34.70 34.20 33.40 32.50 31.40 30.40 29.40 28.40 27.40 26.50 25.70 24.80 24.10 23.30 22.60 21.90
90.0 27.90 29.20 29.90 30.30 30.30 30.20 29.80 29.30 28.80 28.20 27.50 26.80 26.20 25.50 24.90 24.20 23.60 23.00 22.50 21.90
95.0 25.30 25.60 25.80 25.90 26.00 25.90 25.80 25.60 25.40 25.20 24.90 24.60 24.30 24.00 23.60 23.30 23.00 22.60 22.30 21.90
98.0 23.80 23.80 23.80 23.70 23.70 23.60 23.50 23.40 23.30 23.20 23.10 23.00 22.90 22.80 22.60 22.50 22.40 22.20 22.10 21.90
* *
*/
string[] raw = data.Split(new string[] {"\n"}, StringSplitOptions.RemoveEmptyEntries);
// Row 1
Name = raw[0].Trim();
Description = Name;
// Row 2
string[] temp = raw[3].Trim().Split(new string[] { "\t" }, StringSplitOptions.RemoveEmptyEntries);
RowTitle = temp[0];
ColumnTitle = temp[1];
//Row 3
ColumnData = raw[4].Replace('\t','\n').Trim();
// Get decimal places
temp = ColumnData.Split(new string[] { "\t" }, StringSplitOptions.RemoveEmptyEntries);
for (int i=0; i<temp.Length;i++)
{
ColumnDataDecimalPlaces = Math.Max(RowDataDecimalPlaces, temp[0].Length - temp[0].IndexOf(".") - 1);
}
RowData = String.Empty;
TableData = String.Empty;
for (int i = 5; i < raw.Length; i++)
{
string[] colData = raw[i].Trim().Split(new string[] {"\t"}, StringSplitOptions.RemoveEmptyEntries);
// First Value is Row data
RowData += colData[0] + "\n";
RowDataDecimalPlaces = Math.Max(RowDataDecimalPlaces, colData[0].Length - colData[0].IndexOf(".") - 1);
for (int j = 1; j < colData.Length; j++)
{
TableDataDecimalPlaces = Math.Max(TableDataDecimalPlaces, colData[j].Length - colData[j].IndexOf(".") - 1);
TableData += colData[j] + "\t";
}
TableData = TableData.Trim();
TableData += "\n";
}
TableData = TableData.Trim();
RowData = RowData.Trim();
}
}
}