/// <summary>
/// Export the initial conditions table to markdown format.
/// </summary>
/// <returns></returns>
public string ToMarkdown()
{
DataTable scalarsTable = new DataTable();
DataTable vectorsTable = new DataTable();
scalarsTable.Columns.Add("Name");
scalarsTable.Columns.Add("Value");
foreach (InitialCondition condition in Conditions)
{
if (condition.TypeName.Contains("[]"))
{
string columnName = condition.Name;
if (!string.IsNullOrEmpty(condition.Units))
{
columnName += $" ({condition.Units})";
}
string[] value = condition.Value.Split(',');
if (condition.TypeName == "Double[]")
{
if (string.IsNullOrEmpty(condition.DisplayFormat))
{
DataTableUtilities.AddColumn(vectorsTable, columnName, MathUtilities.StringsToDoubles(value));
}
else
{
value = value.Select(x => double.Parse(x).ToString(condition.DisplayFormat)).ToArray();
DataTableUtilities.AddColumn(vectorsTable, columnName, value);
}
}
else
{
DataTableUtilities.AddColumn(vectorsTable, columnName, value);
}
}
else
{
string value = condition.Value;
if (!string.IsNullOrEmpty(condition.Units))
{
value += $" ({condition.Units})";
}
scalarsTable.Rows.Add(condition.Description, value);
}
}
StringBuilder markdown = new StringBuilder();
markdown.AppendLine($"### {relativePath}");
markdown.AppendLine();
if (scalarsTable.Rows.Count > 0)
{
markdown.AppendLine(DataTableUtilities.ToMarkdown(scalarsTable, true));
}
if (vectorsTable.Rows.Count > 0)
{
markdown.AppendLine(DataTableUtilities.ToMarkdown(vectorsTable, true));
}
return(markdown.ToString());
}
/// <summary>
/// Document the event order. Cannot assume the order based on the order
/// of the event declarations.
/// </summary>
/// <param name="tags"></param>
/// <param name="headingLevel">The heading level.</param>
/// <param name="indent">The indent level.</param>
private void DocumentEventOrder(List <AutoDocumentation.ITag> tags, int headingLevel, int indent)
{
// The strategy here is to create an instance of clock and call it's
// timestep method (OnDoCommence) for two days. WE will subscribe to
// all the clock methods via a method (Handler) in an instance of an
// EventHanderClass. This method will then sort out what is a event
// from the daily time step, what is an event from before the timestep
// and post timestep events.
var clock = new Clock();
var methodInfo = typeof(EventHandlerClass).GetMethod("Handler", System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance);
var preTimestepEvents = new List <string>();
var timestepEvents = new List <string>();
var postTimestepEvents = new List <string>();
foreach (var eventMember in clock.GetType().GetEvents())
{
var handlerInstance = new EventHandlerClass()
{
EventName = eventMember.Name,
PreTimestepEvents = preTimestepEvents,
TimestepEvents = timestepEvents,
PostTimestepEvents = postTimestepEvents
};
var handler =
Delegate.CreateDelegate(eventMember.EventHandlerType,
handlerInstance,
methodInfo);
eventMember.AddEventHandler(
clock,
handler);
}
clock.Start = new DateTime(1900, 1, 1);
clock.End = new DateTime(1900, 1, 2);
var commenceMethod = clock.GetType().GetMethod("OnDoCommence", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance);
commenceMethod.Invoke(clock, new object[] { clock, new CommenceArgs() });
var table = new DataTable();
tags.Add(new AutoDocumentation.Heading("Pre-timestep events (in order)", headingLevel));
DataTableUtilities.AddColumn(table, "Events", preTimestepEvents.ToArray());
tags.Add(new AutoDocumentation.Table(table, indent));
var table2 = new DataTable();
tags.Add(new AutoDocumentation.Heading("Timestep events (in order)", headingLevel));
DataTableUtilities.AddColumn(table2, "Events", timestepEvents.ToArray());
tags.Add(new AutoDocumentation.Table(table2, indent));
var table3 = new DataTable();
tags.Add(new AutoDocumentation.Heading("Post-timestep events (in order)", headingLevel));
DataTableUtilities.AddColumn(table3, "Events", postTimestepEvents.ToArray());
tags.Add(new AutoDocumentation.Table(table3, indent));
}
/// <summary>Set a column of string values in the specified table.</summary>
private static void SetStringValues(DataTable table, string columnName, string[] values, int startRow)
{
if (MathUtilities.ValuesInArray(values))
{
DataTableUtilities.AddColumn(table, columnName, values, startRow, values.Length);
}
else if (!table.Columns.Contains(columnName))
{
table.Columns.Add(columnName, typeof(string));
}
}
/// <summary>
/// The main run method called to fill tables in the specified DataStore.
/// </summary>
/// <param name="dataStore">The DataStore to work with</param>
public void Run(IStorageReader dataStore)
{
dataStore.DeleteTable(this.Name);
DataTable simulationData = dataStore.GetData("*", this.TableName);
if (simulationData != null)
{
// Add all the necessary columns to our data table.
DataTable probabilityData = new DataTable();
probabilityData.Columns.Add("Probability", typeof(double));
foreach (DataColumn column in simulationData.Columns)
{
if (column.DataType == typeof(double))
{
probabilityData.Columns.Add(column.ColumnName, typeof(double));
}
}
string[] simulationNames = dataStore.SimulationNames;
DataView view = new DataView(simulationData);
foreach (string simulationName in simulationNames)
{
view.RowFilter = "SimulationName = '" + simulationName + "'";
int startRow = probabilityData.Rows.Count;
// Add in a simulation column.
string[] simulationNameColumnValues = StringUtilities.CreateStringArray(simulationName, view.Count);
DataTableUtilities.AddColumn(probabilityData, "SimulationName", simulationNameColumnValues, startRow, simulationNameColumnValues.Length);
// Add in the probability column
double[] probabilityValues = MathUtilities.ProbabilityDistribution(view.Count, this.Exceedence);
DataTableUtilities.AddColumn(probabilityData, "Probability", probabilityValues, startRow, view.Count);
// Add in all other numeric columns.
foreach (DataColumn column in simulationData.Columns)
{
if (column.DataType == typeof(double))
{
double[] values = DataTableUtilities.GetColumnAsDoubles(view, column.ColumnName);
Array.Sort <double>(values);
DataTableUtilities.AddColumn(probabilityData, column.ColumnName, values, startRow, values.Length);
}
}
}
// Write the stats data to the DataStore
probabilityData.TableName = this.Name;
dataStore.WriteTableRaw(probabilityData);
}
}
/// <summary>
/// Add a column to the specified table based on values in the 'value'
/// </summary>
/// <param name="heading">The new column heading</param>
/// <param name="dataTypeName">The data type of the value</param>
/// <param name="displayFormat">The display format to use when writing the column</param>
/// <param name="showTotal">A value indicating whether a total should be added.</param>
/// <param name="value">The values containing the array</param>
/// <param name="table">The table where a column should be added to</param>
private static void AddArrayToTable(string heading, string dataTypeName, string displayFormat, bool showTotal, object value, DataTable table)
{
if (displayFormat == null)
{
displayFormat = "N3";
}
string[] stringValues = value.ToString().Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
if (dataTypeName == "Double[]")
{
List <double> values = new List <double>();
values.AddRange(MathUtilities.StringsToDoubles(stringValues));
if (showTotal)
{
values.Add(MathUtilities.Sum(values));
}
stringValues = MathUtilities.DoublesToStrings(values, displayFormat);
}
else if (dataTypeName == "Int32[]")
{
List <double> values = new List <double>();
values.AddRange(MathUtilities.StringsToDoubles(stringValues));
if (showTotal)
{
values.Add(MathUtilities.Sum(values));
}
stringValues = MathUtilities.DoublesToStrings(values, "N0");
}
else if (dataTypeName != "String[]")
{
// throw new ApsimXException(null, "Invalid property type: " + dataTypeName);
}
DataTableUtilities.AddColumn(table, heading, stringValues);
}
public DataTable GetData(string tableName, string checkpointName = null, IEnumerable <string> simulationNames = null, IEnumerable <string> fieldNames = null, string filter = null, int from = 0, int count = 0, IEnumerable <string> orderBy = null, bool distinct = false)
{
string rowFilter = null;
if (checkpointName != null)
{
rowFilter += "CheckpointName = '" + checkpointName + "'";
}
if (simulationNames != null && simulationNames.Any())
{
if (rowFilter != null)
{
rowFilter += " AND ";
}
rowFilter += $"SimulationName in ({simulationNames.Enclose("'","'").Join(",")})";
}
if (filter != null)
{
if (rowFilter != null)
{
rowFilter += " AND ";
}
rowFilter += "(" + filter + ")";
}
if (rowFilter == null)
{
return(data);
}
else if (fieldNames == null)
{
var view = new DataView(data);
view.RowFilter = rowFilter;
return(view.ToTable());
}
else
{
var dataCopy = data.Copy();
foreach (DataColumn column in data.Columns)
{
if (!fieldNames.Contains(column.ColumnName) &&
column.ColumnName != "CheckpointName" &&
column.ColumnName != "SimulationName" &&
column.ColumnName != "SimulationID")
{
dataCopy.Columns.Remove(column.ColumnName);
}
}
// Add in a simulation name column if it doesn't exist.
if (dataCopy.Columns.Contains("SimulationID") && !dataCopy.Columns.Contains("SimulationName"))
{
dataCopy.Columns.Add("SimulationName", typeof(string));
foreach (DataRow row in dataCopy.Rows)
{
row["SimulationName"] = "Sim" + row["SimulationID"].ToString();
}
}
var view = new DataView(dataCopy);
view.RowFilter = rowFilter;
if (distinct)
{
var column = dataCopy.Columns[fieldNames.First()];
var columnName = column.ColumnName;
var columnType = column.DataType;
var values = DataTableUtilities.GetColumnAsStrings(view, columnName).Distinct().ToArray();
var data = new DataTable();
//data.Columns.Add(columnName, columnType);
DataTableUtilities.AddColumn(data, columnName, values);
return(data);
}
else
{
return(view.ToTable());
}
}
}
/// <summary>
/// Give values from the source data for all variable names in the expression.
/// </summary>
/// <param name="expression">The expression evaluator.</param>
/// <param name="sourceData">The source data.</param>
private void FillVariableNamesInExpression(ExpressionEvaluator expression, DataTable sourceData)
{
var simulationData = new List <Data>();
foreach (var variable in expression.Variables)
{
if (simulationData.Find(data => data.Name == variable.m_name) == null)
{
simulationData.Add(new Data(sourceData, variable.m_name));
}
}
// Need to get a list of common values for the key across the datasets.
IEnumerable <object> keyValues = null;
foreach (var dataset in simulationData)
{
if (keyValues == null)
{
keyValues = dataset.GetValues(FieldNameToMatchOn);
}
else
{
keyValues = keyValues.Intersect(dataset.GetValues(FieldNameToMatchOn));
}
}
// Create a new data set with the key field.
var newTable = new DataTable(Name);
DataTableUtilities.AddColumnOfObjects(newTable, FieldNameToMatchOn, keyValues.ToArray());
// Add all the double fields into the table.
foreach (DataColumn column in sourceData.Columns)
{
if (column.DataType == typeof(double) && column.ColumnName != FieldNameToMatchOn)
{
newTable.Columns.Add(column.ColumnName, typeof(double));
}
}
// Get each dataset to return
foreach (DataColumn column in sourceData.Columns)
{
if (column.DataType == typeof(double) && column.ColumnName != FieldNameToMatchOn)
{
var expressionVariables = new List <Symbol>();
foreach (var variable in expression.Variables)
{
var dataset = simulationData.Find(data => data.Name == variable.m_name);
var symbol = new Symbol();
symbol.m_name = variable.m_name;
symbol.m_values = dataset.GetDoubleValues(column.ColumnName);
if (symbol.m_values.Length != keyValues.Count())
{
throw new Exception($"Incorrect number of values for simulation {dataset.Name}");
}
expressionVariables.Add(symbol);
}
expression.Variables = expressionVariables;
expression.EvaluatePostfix();
if (expression.Error)
{
throw new Exception($"Error while evaluating expression for column {column.ColumnName}. {expression.ErrorDescription}");
}
// Add data to new data table.
DataTableUtilities.AddColumn(newTable, column.ColumnName, expression.Results);
}
}
// Give the new data table to the data store.
dataStore.Writer.WriteTable(newTable);
}
/// <summary>Main run method for performing our post simulation calculations</summary>
public void Run()
{
// If the predicted table has not been modified, don't do anything.
// This can happen if other simulations were run but the Morris model was not.
if (dataStore?.Writer != null && !dataStore.Writer.TablesModified.Contains(TableName))
{
return;
}
DataTable predictedData = dataStore.Reader.GetData(TableName);
if (predictedData != null)
{
// Determine how many aggregation values we have per simulation
DataView view = new DataView(predictedData);
view.RowFilter = "SimulationName='" + Name + "Simulation1'";
AggregationValues = DataTableUtilities.GetColumnAsStrings(view, AggregationVariableName);
// Create a table of all predicted values
DataTable predictedValues = new DataTable();
List <string> descriptiveColumnNames = new List <string>();
List <string> variableNames = new List <string>();
foreach (string aggregationValue in AggregationValues)
{
string value = aggregationValue;
if (DateTime.TryParse(value, out DateTime date))
{
value = date.ToString("yyyy-MM-dd");
}
view.RowFilter = $"{AggregationVariableName}='{value}'";
foreach (DataColumn predictedColumn in view.Table.Columns)
{
if (predictedColumn.DataType == typeof(double))
{
double[] values = DataTableUtilities.GetColumnAsDoubles(view, predictedColumn.ColumnName);
if (values.Distinct().Count() == 1)
{
if (!descriptiveColumnNames.Contains(predictedColumn.ColumnName))
{
descriptiveColumnNames.Add(predictedColumn.ColumnName);
}
}
else
{
DataTableUtilities.AddColumn(predictedValues, predictedColumn.ColumnName + "_" + value, values);
if (!variableNames.Contains(predictedColumn.ColumnName))
{
variableNames.Add(predictedColumn.ColumnName);
}
}
}
}
}
// Run R
DataTable eeDataRaw;
DataTable statsDataRaw;
RunRPostSimulation(predictedValues, out eeDataRaw, out statsDataRaw);
// Get ee data from R and store in ee table.
// EE data from R looks like:
// "ResidueWt", "FASW", "CN2", "Cona", "variable","path"
// - 22.971008269563,0.00950570342209862,-0.00379987333757356,56.7587080430652,"FallowEvaporation1996",1
// - 25.790599484188, 0.0170777988614538, -0.0265991133629069,58.0240658644712,"FallowEvaporation1996",2
// - 26.113599477728, 0.0113851992409871, 0.0113996200126667,57.9689677010766,"FallowEvaporation1996",3
// - 33.284199334316, 0.0323193916349732, -0.334388853704853,60.5376820772641,"FallowEvaporation1996",4
DataView eeView = new DataView(eeDataRaw);
IndexedDataTable eeTableKey = new IndexedDataTable(new string[] { "Parameter", AggregationVariableName });
// Create a path variable.
var pathValues = Enumerable.Range(1, NumPaths).ToArray();
foreach (var parameter in Parameters)
{
foreach (DataColumn column in predictedValues.Columns)
{
eeView.RowFilter = "variable = '" + column.ColumnName + "'";
if (eeView.Count != NumPaths)
{
throw new Exception("Found only " + eeView.Count + " paths for variable " + column.ColumnName + " in ee table");
}
string aggregationValue = StringUtilities.GetAfter(column.ColumnName, "_");
string variableName = StringUtilities.RemoveAfter(column.ColumnName, '_');
eeTableKey.SetIndex(new object[] { parameter.Name, aggregationValue });
List <double> values = DataTableUtilities.GetColumnAsDoubles(eeView, parameter.Name).ToList();
for (int i = 0; i < values.Count; i++)
{
values[i] = Math.Abs(values[i]);
}
var runningMean = MathUtilities.RunningAverage(values);
eeTableKey.SetValues("Path", pathValues);
eeTableKey.SetValues(variableName + ".MuStar", runningMean);
}
}
DataTable eeTable = eeTableKey.ToTable();
eeTable.TableName = Name + "PathAnalysis";
// Get stats data from R and store in MuStar table.
// Stats data coming back from R looks like:
// "mu", "mustar", "sigma", "param","variable"
// -30.7331368183818, 30.7331368183818, 5.42917964248002,"ResidueWt","FallowEvaporation1996"
// -0.0731299918470997,0.105740687296631,0.450848277601353, "FASW","FallowEvaporation1996"
// -0.83061431285624,0.839772007599748, 1.75541097254145, "CN2","FallowEvaporation1996"
// 62.6942591520838, 62.6942591520838, 5.22778043503867, "Cona","FallowEvaporation1996"
// -17.286285468283, 19.4018404625051, 24.1361388348929,"ResidueWt","FallowRunoff1996"
// 8.09850688306722, 8.09852589447407, 15.1988107373113, "FASW","FallowRunoff1996"
// 18.6196168461051, 18.6196168461051, 15.1496277765849, "CN2","FallowRunoff1996"
// -7.12794888887507, 7.12794888887507, 5.54014788597839, "Cona","FallowRunoff1996"
IndexedDataTable tableKey = new IndexedDataTable(new string[2] {
"Parameter", AggregationVariableName
});
foreach (DataRow row in statsDataRaw.Rows)
{
string variable = row["variable"].ToString();
string aggregationValue = StringUtilities.GetAfter(variable, "_");
variable = StringUtilities.RemoveAfter(variable, '_');
tableKey.SetIndex(new object[] { row["param"], aggregationValue });
tableKey.Set(variable + ".Mu", row["mu"]);
tableKey.Set(variable + ".MuStar", row["mustar"]);
tableKey.Set(variable + ".Sigma", row["sigma"]);
// Need to bring in the descriptive values.
view.RowFilter = $"{AggregationVariableName}='{aggregationValue}'";
foreach (var descriptiveColumnName in descriptiveColumnNames)
{
var values = DataTableUtilities.GetColumnAsStrings(view, descriptiveColumnName);
if (values.Distinct().Count() == 1)
{
tableKey.Set(descriptiveColumnName, view[0][descriptiveColumnName]);
}
}
}
DataTable muStarTable = tableKey.ToTable();
muStarTable.TableName = Name + "Statistics";
dataStore.Writer.WriteTable(eeTable);
dataStore.Writer.WriteTable(muStarTable);
}
}
/// <summary>Main run method for performing our calculations and storing data.</summary>
public void Run()
{
if (PredictedTableName == null || ObservedTableName == null)
{
return;
}
// If neither the predicted nor obseved tables have been modified during
// the most recent simulations run, don't do anything.
if (dataStore?.Writer != null &&
!(dataStore.Writer.TablesModified.Contains(PredictedTableName) || dataStore.Writer.TablesModified.Contains(ObservedTableName)))
{
return;
}
IEnumerable <string> predictedDataNames = dataStore.Reader.ColumnNames(PredictedTableName);
IEnumerable <string> observedDataNames = dataStore.Reader.ColumnNames(ObservedTableName);
if (predictedDataNames == null)
{
throw new ApsimXException(this, "Could not find model data table: " + PredictedTableName);
}
if (observedDataNames == null)
{
throw new ApsimXException(this, "Could not find observed data table: " + ObservedTableName);
}
// get the common columns between these lists of columns
List <string> commonCols = predictedDataNames.Intersect(observedDataNames).ToList();
// This should be all columns which exist in one table but not both.
IEnumerable <string> uncommonCols = predictedDataNames.Except(observedDataNames).Union(observedDataNames.Except(predictedDataNames));
IStorageReader reader = dataStore.Reader;
string match1ObsShort = reader.BriefColumnName(ObservedTableName, FieldNameUsedForMatch);
string match2ObsShort = reader.BriefColumnName(ObservedTableName, FieldName2UsedForMatch);
string match3ObsShort = reader.BriefColumnName(ObservedTableName, FieldName3UsedForMatch);
string match1PredShort = reader.BriefColumnName(PredictedTableName, FieldNameUsedForMatch);
string match2PredShort = reader.BriefColumnName(PredictedTableName, FieldName2UsedForMatch);
string match3PredShort = reader.BriefColumnName(PredictedTableName, FieldName3UsedForMatch);
StringBuilder query = new StringBuilder("SELECT ");
for (int i = 0; i < commonCols.Count; i++)
{
string s = commonCols[i];
string obsColShort = reader.BriefColumnName(ObservedTableName, s);
string predColShort = reader.BriefColumnName(PredictedTableName, s);
if (i != 0)
{
query.Append(", ");
}
if (s == FieldNameUsedForMatch || s == FieldName2UsedForMatch || s == FieldName3UsedForMatch)
{
query.Append($"O.\"{obsColShort}\"");
}
else
{
query.Append($"O.\"{obsColShort}\" AS \"Observed.{obsColShort}\", P.\"{predColShort}\" AS \"Predicted.{predColShort}\"");
}
}
// Add columns which exist in one table but not both.
foreach (string uncommonCol in uncommonCols)
{
// Basically this hack is here to allow error data to be added to the p/o graphs.
// This is kind of terrible, but I really don't want to duplicate every
// column from both predicted and observed tables if we don't have to.
// This does raise the question of whether we should be creating a "PredictedObserved"
// table at all, since we're actually duplicating data in the DB by doing so.
if (AllColumns || uncommonCol.EndsWith("Error"))
{
if (predictedDataNames.Contains(uncommonCol))
{
query.Append($", P.\"{uncommonCol}\" as \"Predicted.{uncommonCol}\"");
}
else if (observedDataNames.Contains(uncommonCol))
{
query.Append($", O.\"{uncommonCol}\" as \"Observed.{uncommonCol}\"");
}
}
}
query.AppendLine();
query.AppendLine($"FROM [{ObservedTableName}] O");
query.AppendLine($"INNER JOIN [{PredictedTableName}] P");
query.Append($"USING ([SimulationID], [CheckpointID], [{FieldNameUsedForMatch}]");
if (!string.IsNullOrEmpty(FieldName2UsedForMatch))
{
query.Append($", \"{FieldName2UsedForMatch}\"");
}
if (!string.IsNullOrEmpty(FieldName3UsedForMatch))
{
query.Append($", \"{FieldName3UsedForMatch}\"");
}
query.AppendLine(")");
int checkpointID = dataStore.Writer.GetCheckpointID("Current");
query.AppendLine("WHERE [CheckpointID] = " + checkpointID);
query.Replace("O.\"SimulationID\" AS \"Observed.SimulationID\", P.\"SimulationID\" AS \"Predicted.SimulationID\"", "O.\"SimulationID\" AS \"SimulationID\"");
query.Replace("O.\"CheckpointID\" AS \"Observed.CheckpointID\", P.\"CheckpointID\" AS \"Predicted.CheckpointID\"", "O.\"CheckpointID\" AS \"CheckpointID\"");
if (Parent is Folder)
{
// Limit it to particular simulations in scope.
List <string> simulationNames = new List <string>();
foreach (Experiment experiment in this.FindAllInScope <Experiment>())
{
var names = experiment.GenerateSimulationDescriptions().Select(s => s.Name);
simulationNames.AddRange(names);
}
foreach (Simulation simulation in this.FindAllInScope <Simulation>())
{
if (!(simulation.Parent is Experiment))
{
simulationNames.Add(simulation.Name);
}
}
query.Append(" AND O.[SimulationID] in (");
foreach (string simulationName in simulationNames)
{
if (simulationName != simulationNames[0])
{
query.Append(',');
}
query.Append(dataStore.Writer.GetSimulationID(simulationName, null));
}
query.Append(")");
}
DataTable predictedObservedData = reader.GetDataUsingSql(query.ToString());
if (predictedObservedData != null)
{
foreach (DataColumn column in predictedObservedData.Columns)
{
if (column.ColumnName.StartsWith("Predicted."))
{
string shortName = column.ColumnName.Substring("Predicted.".Length);
column.ColumnName = "Predicted." + reader.FullColumnName(PredictedTableName, shortName);
}
else if (column.ColumnName.StartsWith("Observed."))
{
string shortName = column.ColumnName.Substring("Observed.".Length);
column.ColumnName = "Observed." + reader.FullColumnName(ObservedTableName, shortName);
}
else if (column.ColumnName.Equals(match1ObsShort) || column.ColumnName.Equals(match2ObsShort) || column.ColumnName.Equals(match3ObsShort))
{
column.ColumnName = reader.FullColumnName(ObservedTableName, column.ColumnName);
}
}
// Add in error columns for each data column.
foreach (string columnName in commonCols)
{
if (predictedObservedData.Columns.Contains("Predicted." + columnName) &&
predictedObservedData.Columns["Predicted." + columnName].DataType == typeof(double))
{
var predicted = DataTableUtilities.GetColumnAsDoubles(predictedObservedData, "Predicted." + columnName);
var observed = DataTableUtilities.GetColumnAsDoubles(predictedObservedData, "Observed." + columnName);
if (predicted.Length > 0 && predicted.Length == observed.Length)
{
var errorData = MathUtilities.Subtract(predicted, observed);
var errorColumnName = "Pred-Obs." + columnName;
var errorColumn = predictedObservedData.Columns.Add(errorColumnName, typeof(double));
DataTableUtilities.AddColumn(predictedObservedData, errorColumnName, errorData);
predictedObservedData.Columns[errorColumnName].SetOrdinal(predictedObservedData.Columns["Predicted." + columnName].Ordinal + 1);
}
}
}
// Write table to datastore.
predictedObservedData.TableName = this.Name;
dataStore.Writer.WriteTable(predictedObservedData);
List <string> unitFieldNames = new List <string>();
List <string> unitNames = new List <string>();
// write units to table.
reader.Refresh();
foreach (string fieldName in commonCols)
{
string units = reader.Units(PredictedTableName, fieldName);
if (units != null && units != "()")
{
string unitsMinusBrackets = units.Replace("(", "").Replace(")", "");
unitFieldNames.Add("Predicted." + fieldName);
unitNames.Add(unitsMinusBrackets);
unitFieldNames.Add("Observed." + fieldName);
unitNames.Add(unitsMinusBrackets);
}
}
if (unitNames.Count > 0)
{
// The Writer replaces tables, rather than appends to them,
// so we actually need to re-write the existing units table values
// Is there a better way to do this?
DataView allUnits = new DataView(reader.GetData("_Units"));
allUnits.Sort = "TableName";
DataTable tableNames = allUnits.ToTable(true, "TableName");
foreach (DataRow row in tableNames.Rows)
{
string tableName = row["TableName"] as string;
List <string> colNames = new List <string>();
List <string> unitz = new List <string>();
foreach (DataRowView rowView in allUnits.FindRows(tableName))
{
colNames.Add(rowView["ColumnHeading"].ToString());
unitz.Add(rowView["Units"].ToString());
}
dataStore.Writer.AddUnits(tableName, colNames, unitz);
}
dataStore.Writer.AddUnits(Name, unitFieldNames, unitNames);
}
}
else
{
// Determine what went wrong.
DataTable predictedData = reader.GetDataUsingSql("SELECT * FROM [" + PredictedTableName + "]");
DataTable observedData = reader.GetDataUsingSql("SELECT * FROM [" + ObservedTableName + "]");
if (predictedData == null || predictedData.Rows.Count == 0)
{
throw new Exception(Name + ": Cannot find any predicted data.");
}
else if (observedData == null || observedData.Rows.Count == 0)
{
throw new Exception(Name + ": Cannot find any observed data in node: " + ObservedTableName + ". Check for missing observed file or move " + ObservedTableName + " to top of child list under DataStore (order is important!)");
}
else
{
throw new Exception(Name + ": Observed data was found but didn't match the predicted values. Make sure the values in the SimulationName column match the simulation names in the user interface. Also ensure column names in the observed file match the APSIM report column names.");
}
}
}
/// <summary>Main run method for performing our post simulation calculations</summary>
/// <param name="dataStore">The data store.</param>
public void Run(IDataStore dataStore)
{
DataTable predictedData = dataStore.Reader.GetData("Report", filter: "SimulationName LIKE '" + Name + "%'", orderBy: "SimulationID");
if (predictedData != null)
{
// Determine how many years we have per simulation
DataView view = new DataView(predictedData);
view.RowFilter = "SimulationName='" + Name + "Simulation1'";
Years = DataTableUtilities.GetColumnAsIntegers(view, "Clock.Today.Year");
// Create a table of all predicted values
DataTable predictedValues = new DataTable();
List <string> descriptiveColumnNames = new List <string>();
List <string> variableNames = new List <string>();
foreach (double year in Years)
{
view.RowFilter = "Clock.Today.Year=" + year;
foreach (DataColumn predictedColumn in view.Table.Columns)
{
if (predictedColumn.DataType == typeof(double))
{
double[] valuesForYear = DataTableUtilities.GetColumnAsDoubles(view, predictedColumn.ColumnName);
if (valuesForYear.Distinct().Count() == 1)
{
if (!descriptiveColumnNames.Contains(predictedColumn.ColumnName))
{
descriptiveColumnNames.Add(predictedColumn.ColumnName);
}
}
else
{
DataTableUtilities.AddColumn(predictedValues, predictedColumn.ColumnName + year, valuesForYear);
if (!variableNames.Contains(predictedColumn.ColumnName))
{
variableNames.Add(predictedColumn.ColumnName);
}
}
}
}
}
// Run R
DataTable eeDataRaw;
DataTable statsDataRaw;
RunRPostSimulation(predictedValues, out eeDataRaw, out statsDataRaw);
// Get ee data from R and store in ee table.
// EE data from R looks like:
// "ResidueWt", "FASW", "CN2", "Cona", "variable","path"
// - 22.971008269563,0.00950570342209862,-0.00379987333757356,56.7587080430652,"FallowEvaporation1996",1
// - 25.790599484188, 0.0170777988614538, -0.0265991133629069,58.0240658644712,"FallowEvaporation1996",2
// - 26.113599477728, 0.0113851992409871, 0.0113996200126667,57.9689677010766,"FallowEvaporation1996",3
// - 33.284199334316, 0.0323193916349732, -0.334388853704853,60.5376820772641,"FallowEvaporation1996",4
DataView eeView = new DataView(eeDataRaw);
IndexedDataTable eeTableKey = new IndexedDataTable(new string[] { "Parameter", "Year" });
// Create a path variable.
var pathValues = Enumerable.Range(1, NumPaths).ToArray();
foreach (var parameter in Parameters)
{
foreach (DataColumn column in predictedValues.Columns)
{
eeView.RowFilter = "variable = '" + column.ColumnName + "'";
if (eeView.Count != NumPaths)
{
throw new Exception("Found only " + eeView.Count + " paths for variable " + column.ColumnName + " in ee table");
}
int year = Convert.ToInt32(column.ColumnName.Substring(column.ColumnName.Length - 4));
string variableName = column.ColumnName.Substring(0, column.ColumnName.Length - 4);
eeTableKey.SetIndex(new object[] { parameter.Name, year });
List <double> values = DataTableUtilities.GetColumnAsDoubles(eeView, parameter.Name).ToList();
for (int i = 0; i < values.Count; i++)
{
values[i] = Math.Abs(values[i]);
}
var runningMean = MathUtilities.RunningAverage(values);
eeTableKey.SetValues("Path", pathValues);
eeTableKey.SetValues(variableName + ".MuStar", runningMean);
}
}
DataTable eeTable = eeTableKey.ToTable();
eeTable.TableName = Name + "PathAnalysis";
// Get stats data from R and store in MuStar table.
// Stats data coming back from R looks like:
// "mu", "mustar", "sigma", "param","variable"
// -30.7331368183818, 30.7331368183818, 5.42917964248002,"ResidueWt","FallowEvaporation1996"
// -0.0731299918470997,0.105740687296631,0.450848277601353, "FASW","FallowEvaporation1996"
// -0.83061431285624,0.839772007599748, 1.75541097254145, "CN2","FallowEvaporation1996"
// 62.6942591520838, 62.6942591520838, 5.22778043503867, "Cona","FallowEvaporation1996"
// -17.286285468283, 19.4018404625051, 24.1361388348929,"ResidueWt","FallowRunoff1996"
// 8.09850688306722, 8.09852589447407, 15.1988107373113, "FASW","FallowRunoff1996"
// 18.6196168461051, 18.6196168461051, 15.1496277765849, "CN2","FallowRunoff1996"
// -7.12794888887507, 7.12794888887507, 5.54014788597839, "Cona","FallowRunoff1996"
IndexedDataTable tableKey = new IndexedDataTable(new string[2] {
"Parameter", "Year"
});
foreach (DataRow row in statsDataRaw.Rows)
{
string variable = row["variable"].ToString();
int year = Convert.ToInt32(variable.Substring(variable.Length - 4));
variable = variable.Substring(0, variable.Length - 4);
tableKey.SetIndex(new object[] { row["param"], year });
tableKey.Set(variable + ".Mu", row["mu"]);
tableKey.Set(variable + ".MuStar", row["mustar"]);
tableKey.Set(variable + ".Sigma", row["sigma"]);
// Need to bring in the descriptive values.
view.RowFilter = "Clock.Today.Year=" + year;
foreach (var descriptiveColumnName in descriptiveColumnNames)
{
var values = DataTableUtilities.GetColumnAsStrings(view, descriptiveColumnName);
if (values.Distinct().Count() == 1)
{
tableKey.Set(descriptiveColumnName, view[0][descriptiveColumnName]);
}
}
}
DataTable muStarTable = tableKey.ToTable();
muStarTable.TableName = Name + "Statistics";
dataStore.Writer.WriteTable(eeTable);
dataStore.Writer.WriteTable(muStarTable);
}
}
/// <summary>Main run method for performing our calculations and storing data.</summary>
/// <param name="dataStore">The data store.</param>
/// <exception cref="ApsimXException">
/// Could not find model data table: + PredictedTableName
/// or
/// Could not find observed data table: + ObservedTableName
/// </exception>
public void Run(IDataStore dataStore)
{
if (PredictedTableName != null && ObservedTableName != null)
{
IEnumerable <string> predictedDataNames = dataStore.Reader.ColumnNames(PredictedTableName);
IEnumerable <string> observedDataNames = dataStore.Reader.ColumnNames(ObservedTableName);
if (predictedDataNames == null)
{
throw new ApsimXException(this, "Could not find model data table: " + PredictedTableName);
}
if (observedDataNames == null)
{
throw new ApsimXException(this, "Could not find observed data table: " + ObservedTableName);
}
// get the common columns between these lists of columns
IEnumerable <string> commonCols = predictedDataNames.Intersect(observedDataNames);
StringBuilder query = new StringBuilder("SELECT ");
foreach (string s in commonCols)
{
if (s == FieldNameUsedForMatch || s == FieldName2UsedForMatch || s == FieldName3UsedForMatch)
{
query.Append("O.'@field', ");
}
else
{
query.Append("O.'@field' AS 'Observed.@field', P.'@field' AS 'Predicted.@field', ");
}
query.Replace("@field", s);
}
query.Append("FROM " + ObservedTableName + " O INNER JOIN " + PredictedTableName + " P USING (SimulationID) WHERE O.'@match1' = P.'@match1'");
if (FieldName2UsedForMatch != null && FieldName2UsedForMatch != string.Empty)
{
query.Append(" AND O.'@match2' = P.'@match2'");
}
if (FieldName3UsedForMatch != null && FieldName3UsedForMatch != string.Empty)
{
query.Append(" AND O.'@match3' = P.'@match3'");
}
int checkpointID = dataStore.Writer.GetCheckpointID("Current");
query.Append(" AND P.CheckpointID = " + checkpointID);
query.Replace(", FROM", " FROM"); // get rid of the last comma
query.Replace("O.'SimulationID' AS 'Observed.SimulationID', P.'SimulationID' AS 'Predicted.SimulationID'", "O.'SimulationID' AS 'SimulationID'");
query = query.Replace("@match1", FieldNameUsedForMatch);
query = query.Replace("@match2", FieldName2UsedForMatch);
query = query.Replace("@match3", FieldName3UsedForMatch);
if (Parent is Folder)
{
// Limit it to particular simulations in scope.
List <string> simulationNames = new List <string>();
foreach (Experiment experiment in Apsim.FindAll(this, typeof(Experiment)))
{
var names = experiment.GenerateSimulationDescriptions().Select(s => s.Name);
simulationNames.AddRange(names);
}
foreach (Simulation simulation in Apsim.FindAll(this, typeof(Simulation)))
{
if (!(simulation.Parent is Experiment))
{
simulationNames.Add(simulation.Name);
}
}
query.Append(" AND O.SimulationID in (");
foreach (string simulationName in simulationNames)
{
if (simulationName != simulationNames[0])
{
query.Append(',');
}
query.Append(dataStore.Writer.GetSimulationID(simulationName));
}
query.Append(")");
}
DataTable predictedObservedData = dataStore.Reader.GetDataUsingSql(query.ToString());
if (predictedObservedData != null)
{
// Add in error columns for each data column.
foreach (string columnName in commonCols)
{
if (predictedObservedData.Columns.Contains("Predicted." + columnName) &&
predictedObservedData.Columns["Predicted." + columnName].DataType == typeof(double))
{
var predicted = DataTableUtilities.GetColumnAsDoubles(predictedObservedData, "Predicted." + columnName);
var observed = DataTableUtilities.GetColumnAsDoubles(predictedObservedData, "Observed." + columnName);
if (predicted.Length > 0 && predicted.Length == observed.Length)
{
var errorData = MathUtilities.Subtract(predicted, observed);
var errorColumnName = "Pred-Obs." + columnName;
var errorColumn = predictedObservedData.Columns.Add(errorColumnName, typeof(double));
DataTableUtilities.AddColumn(predictedObservedData, errorColumnName, errorData);
predictedObservedData.Columns[errorColumnName].SetOrdinal(predictedObservedData.Columns["Predicted." + columnName].Ordinal + 1);
}
}
}
// Write table to datastore.
predictedObservedData.TableName = this.Name;
dataStore.Writer.WriteTable(predictedObservedData);
List <string> unitFieldNames = new List <string>();
List <string> unitNames = new List <string>();
// write units to table.
foreach (string fieldName in commonCols)
{
string units = dataStore.Reader.Units(PredictedTableName, fieldName);
if (units != null && units != "()")
{
string unitsMinusBrackets = units.Replace("(", "").Replace(")", "");
unitFieldNames.Add("Predicted." + fieldName);
unitNames.Add(unitsMinusBrackets);
unitFieldNames.Add("Observed." + fieldName);
unitNames.Add(unitsMinusBrackets);
}
}
if (unitNames.Count > 0)
{
dataStore.Writer.AddUnits(Name, unitFieldNames, unitNames);
}
}
else
{
// Determine what went wrong.
DataTable predictedData = dataStore.Reader.GetDataUsingSql("SELECT * FROM " + PredictedTableName);
DataTable observedData = dataStore.Reader.GetDataUsingSql("SELECT * FROM " + ObservedTableName);
if (predictedData == null || predictedData.Rows.Count == 0)
{
throw new Exception(Name + ": Cannot find any predicted data.");
}
else if (observedData == null || observedData.Rows.Count == 0)
{
throw new Exception(Name + ": Cannot find any observed data in node: " + ObservedTableName + ". Check for missing observed file or move " + ObservedTableName + " to top of child list under DataStore (order is important!)");
}
else
{
throw new Exception(Name + ": Observed data was found but didn't match the predicted values. Make sure the values in the SimulationName column match the simulation names in the user interface. Also ensure column names in the observed file match the APSIM report column names.");
}
}
}
}
/// <summary>Main run method for performing our calculations and storing data.</summary>
public void Run()
{
if (PredictedTableName != null && ObservedTableName != null)
{
IEnumerable <string> predictedDataNames = dataStore.Reader.ColumnNames(PredictedTableName);
IEnumerable <string> observedDataNames = dataStore.Reader.ColumnNames(ObservedTableName);
if (predictedDataNames == null)
{
throw new ApsimXException(this, "Could not find model data table: " + PredictedTableName);
}
if (observedDataNames == null)
{
throw new ApsimXException(this, "Could not find observed data table: " + ObservedTableName);
}
// get the common columns between these lists of columns
IEnumerable <string> commonCols = predictedDataNames.Intersect(observedDataNames);
IStorageReader reader = dataStore.Reader;
string match1ObsShort = reader.BriefColumnName(ObservedTableName, FieldNameUsedForMatch);
string match2ObsShort = reader.BriefColumnName(ObservedTableName, FieldName2UsedForMatch);
string match3ObsShort = reader.BriefColumnName(ObservedTableName, FieldName3UsedForMatch);
string match1PredShort = reader.BriefColumnName(PredictedTableName, FieldNameUsedForMatch);
string match2PredShort = reader.BriefColumnName(PredictedTableName, FieldName2UsedForMatch);
string match3PredShort = reader.BriefColumnName(PredictedTableName, FieldName3UsedForMatch);
StringBuilder query = new StringBuilder("SELECT ");
foreach (string s in commonCols)
{
string obsColShort = reader.BriefColumnName(ObservedTableName, s);
string predColShort = reader.BriefColumnName(PredictedTableName, s);
if (s == FieldNameUsedForMatch || s == FieldName2UsedForMatch || s == FieldName3UsedForMatch)
{
query.Append("O.[" + obsColShort + "], ");
}
else
{
query.Append("O.[" + obsColShort + "] AS [Observed." + obsColShort + "], P.[" + predColShort + "] AS [Predicted." + predColShort + "], ");
}
}
query.Append("FROM [" + ObservedTableName + "] O INNER JOIN [" + PredictedTableName + "] P USING ([SimulationID]) WHERE O.[" + match1ObsShort + "] = P.[" + match1PredShort + "]");
if (FieldName2UsedForMatch != null && FieldName2UsedForMatch != string.Empty)
{
query.Append(" AND O.[" + match2ObsShort + "] = P.[" + match2PredShort + "]");
}
if (FieldName3UsedForMatch != null && FieldName3UsedForMatch != string.Empty)
{
query.Append(" AND O.[" + match3ObsShort + "] = P.[" + match3PredShort + "]");
}
int checkpointID = dataStore.Writer.GetCheckpointID("Current");
query.Append(" AND P.[CheckpointID] = " + checkpointID);
query.Replace(", FROM", " FROM"); // get rid of the last comma
query.Replace("O.[SimulationID] AS [Observed.SimulationID], P.[SimulationID] AS [Predicted.SimulationID]", "O.[SimulationID] AS [SimulationID]");
if (Parent is Folder)
{
// Limit it to particular simulations in scope.
List <string> simulationNames = new List <string>();
foreach (Experiment experiment in Apsim.FindAll(this, typeof(Experiment)))
{
var names = experiment.GenerateSimulationDescriptions().Select(s => s.Name);
simulationNames.AddRange(names);
}
foreach (Simulation simulation in Apsim.FindAll(this, typeof(Simulation)))
{
if (!(simulation.Parent is Experiment))
{
simulationNames.Add(simulation.Name);
}
}
query.Append(" AND O.[SimulationID] in (");
foreach (string simulationName in simulationNames)
{
if (simulationName != simulationNames[0])
{
query.Append(',');
}
query.Append(dataStore.Writer.GetSimulationID(simulationName, null));
}
query.Append(")");
}
DataTable predictedObservedData = reader.GetDataUsingSql(query.ToString());
if (predictedObservedData != null)
{
foreach (DataColumn column in predictedObservedData.Columns)
{
if (column.ColumnName.StartsWith("Predicted."))
{
string shortName = column.ColumnName.Substring("Predicted.".Length);
column.ColumnName = "Predicted." + reader.FullColumnName(PredictedTableName, shortName);
}
else if (column.ColumnName.StartsWith("Observed."))
{
string shortName = column.ColumnName.Substring("Observed.".Length);
column.ColumnName = "Observed." + reader.FullColumnName(ObservedTableName, shortName);
}
else if (column.ColumnName.Equals(match1ObsShort) || column.ColumnName.Equals(match2ObsShort) || column.ColumnName.Equals(match3ObsShort))
{
column.ColumnName = reader.FullColumnName(ObservedTableName, column.ColumnName);
}
}
// Add in error columns for each data column.
foreach (string columnName in commonCols)
{
if (predictedObservedData.Columns.Contains("Predicted." + columnName) &&
predictedObservedData.Columns["Predicted." + columnName].DataType == typeof(double))
{
var predicted = DataTableUtilities.GetColumnAsDoubles(predictedObservedData, "Predicted." + columnName);
var observed = DataTableUtilities.GetColumnAsDoubles(predictedObservedData, "Observed." + columnName);
if (predicted.Length > 0 && predicted.Length == observed.Length)
{
var errorData = MathUtilities.Subtract(predicted, observed);
var errorColumnName = "Pred-Obs." + columnName;
var errorColumn = predictedObservedData.Columns.Add(errorColumnName, typeof(double));
DataTableUtilities.AddColumn(predictedObservedData, errorColumnName, errorData);
predictedObservedData.Columns[errorColumnName].SetOrdinal(predictedObservedData.Columns["Predicted." + columnName].Ordinal + 1);
}
}
}
// Write table to datastore.
predictedObservedData.TableName = this.Name;
dataStore.Writer.WriteTable(predictedObservedData);
List <string> unitFieldNames = new List <string>();
List <string> unitNames = new List <string>();
// write units to table.
reader.Refresh();
foreach (string fieldName in commonCols)
{
string units = reader.Units(PredictedTableName, fieldName);
if (units != null && units != "()")
{
string unitsMinusBrackets = units.Replace("(", "").Replace(")", "");
unitFieldNames.Add("Predicted." + fieldName);
unitNames.Add(unitsMinusBrackets);
unitFieldNames.Add("Observed." + fieldName);
unitNames.Add(unitsMinusBrackets);
}
}
if (unitNames.Count > 0)
{
// The Writer replaces tables, rather than appends to them,
// so we actually need to re-write the existing units table values
// Is there a better way to do this?
DataView allUnits = new DataView(reader.GetData("_Units"));
allUnits.Sort = "TableName";
DataTable tableNames = allUnits.ToTable(true, "TableName");
foreach (DataRow row in tableNames.Rows)
{
string tableName = row["TableName"] as string;
List <string> colNames = new List <string>();
List <string> unitz = new List <string>();
foreach (DataRowView rowView in allUnits.FindRows(tableName))
{
colNames.Add(rowView["ColumnHeading"].ToString());
unitz.Add(rowView["Units"].ToString());
}
dataStore.Writer.AddUnits(tableName, colNames, unitz);
}
dataStore.Writer.AddUnits(Name, unitFieldNames, unitNames);
}
}
else
{
// Determine what went wrong.
DataTable predictedData = reader.GetDataUsingSql("SELECT * FROM [" + PredictedTableName + "]");
DataTable observedData = reader.GetDataUsingSql("SELECT * FROM [" + ObservedTableName + "]");
if (predictedData == null || predictedData.Rows.Count == 0)
{
throw new Exception(Name + ": Cannot find any predicted data.");
}
else if (observedData == null || observedData.Rows.Count == 0)
{
throw new Exception(Name + ": Cannot find any observed data in node: " + ObservedTableName + ". Check for missing observed file or move " + ObservedTableName + " to top of child list under DataStore (order is important!)");
}
else
{
throw new Exception(Name + ": Observed data was found but didn't match the predicted values. Make sure the values in the SimulationName column match the simulation names in the user interface. Also ensure column names in the observed file match the APSIM report column names.");
}
}
}
}