/// <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>Reads all data from the specified reader.</summary>
/// <param name="reader">Storage reader.</param>
/// <param name="simulationDescriptions">Complete list of simulation descriptions.</param>
public void ReadData(IStorageReader reader, List <SimulationDescription> simulationDescriptions)
{
if (X != null && Y != null)
{
return;
}
if (series.TableName == null)
{
if (!String.IsNullOrEmpty(XFieldName))
{
X = GetDataFromModels(XFieldName);
}
if (!String.IsNullOrEmpty(YFieldName))
{
Y = GetDataFromModels(YFieldName);
}
if (!String.IsNullOrEmpty(X2FieldName))
{
X2 = GetDataFromModels(X2FieldName);
}
if (!String.IsNullOrEmpty(Y2FieldName))
{
Y2 = GetDataFromModels(Y2FieldName);
}
}
else
{
var fieldsThatExist = reader.ColumnNames(series.TableName);
// If we have descriptors, then use them to filter the data for this series.
string filter = null;
if (Descriptors != null)
{
foreach (var descriptor in Descriptors)
{
if (fieldsThatExist.Contains(descriptor.Name))
{
filter = AddToFilter(filter, descriptor.Name + " = '" + descriptor.Value + "'");
}
else
{
filter = AddSimulationNameClauseToFilter(filter, descriptor, simulationDescriptions);
}
}
// Incorporate our scope filter if we haven't limited filter to particular simulations.
if (!filter.Contains("SimulationName IN"))
{
filter = AddToFilter(filter, scopeFilter);
}
}
else
{
filter = AddToFilter(filter, scopeFilter);
}
if (!string.IsNullOrEmpty(userFilter))
{
filter = AddToFilter(filter, userFilter);
}
// Get a list of fields to read from data store.
var fieldsToRead = new List <string>();
fieldsToRead.Add(XFieldName);
fieldsToRead.Add(YFieldName);
if (X2FieldName != null)
{
fieldsToRead.Add(X2FieldName);
}
if (Y2FieldName != null)
{
fieldsToRead.Add(Y2FieldName);
}
// Add any error fields to the list of fields to read.
var fieldsToAdd = new List <string>();
foreach (var fieldName in fieldsToRead)
{
if (fieldsThatExist.Contains(fieldName + "Error"))
{
fieldsToAdd.Add(fieldName + "Error");
}
}
fieldsToRead.AddRange(fieldsToAdd);
// Add any field names from the filter.
fieldsToRead.AddRange(ExtractFieldNamesFromFilter(filter));
// Add any fields from child graphable models.
foreach (IGraphable series in Apsim.Children(series, typeof(IGraphable)))
{
fieldsToRead.AddRange(series.GetExtraFieldsToRead(this));
}
// Checkpoints don't exist in observed files so don't pass a checkpoint name to
// GetData in this situation.
string localCheckpointName = CheckpointName;
if (!reader.ColumnNames(series.TableName).Contains("CheckpointID"))
{
localCheckpointName = null;
}
// Go get the data.
Data = reader.GetData(series.TableName, localCheckpointName, fieldNames: fieldsToRead.Distinct(), filter: filter);
// Get the units for our x and y variables.
XFieldUnits = reader.Units(series.TableName, XFieldName);
YFieldUnits = reader.Units(series.TableName, YFieldName);
// If data was found, populate our data (e.g. X and Y) properties.
if (Data.Rows.Count > 0)
{
X = GetDataFromTable(Data, XFieldName);
Y = GetDataFromTable(Data, YFieldName);
X2 = GetDataFromTable(Data, X2FieldName);
Y2 = GetDataFromTable(Data, Y2FieldName);
Error = GetErrorDataFromTable(Data, YFieldName);
if (series.Cumulative)
{
Y = MathUtilities.Cumulative(Y as IEnumerable <double>);
}
if (series.CumulativeX)
{
X = MathUtilities.Cumulative(X as IEnumerable <double>);
}
}
}
}
/// <summary>Reads all data from the specified reader.</summary>
/// <param name="data">Data to read from.</param>
/// <param name="simulationDescriptions">Complete list of simulation descriptions.</param>
/// <param name="reader">Data store reader.</param>
public void ReadData(DataTable data, List <SimulationDescription> simulationDescriptions, IStorageReader reader)
{
if (X != null && Y != null)
{
return;
}
if (Series.TableName == null)
{
GetDataFromModels();
}
else
{
var fieldsThatExist = DataTableUtilities.GetColumnNames(data).ToList();
// If we have descriptors, then use them to filter the data for this series.
List <string> simulationNameFilter = new List <string>();;
IEnumerable <SimulationDescription> simulationNamesWithDescriptors = new List <SimulationDescription>();
if (Descriptors != null)
{
foreach (var descriptor in Descriptors)
{
if (!fieldsThatExist.Contains(descriptor.Name))
{
if (simulationNamesWithDescriptors.Any())
{
simulationNamesWithDescriptors = simulationNamesWithDescriptors.Where(s => s.HasDescriptor(descriptor));
}
else
{
simulationNamesWithDescriptors = simulationDescriptions.Where(sim => sim.HasDescriptor(descriptor));
}
}
}
if (simulationNamesWithDescriptors.Any())
{
simulationNameFilter = simulationNamesWithDescriptors.Select(s => s.Name).ToList();
}
// Incorporate our scope filter if we haven't limited filter to particular simulations.
if (!simulationNameFilter.Any() && InScopeSimulationNames != null)
{
simulationNameFilter = new List <string>(InScopeSimulationNames);
}
}
else if (InScopeSimulationNames != null)
{
simulationNameFilter = new List <string>(InScopeSimulationNames ?? Enumerable.Empty <string>());
}
string filter = GetFilter(fieldsThatExist);
if (simulationNameFilter.Any())
{
var simulationIds = reader.ToSimulationIDs(simulationNameFilter);
var simulationIdsCSV = StringUtilities.Build(simulationIds, ",");
if (fieldsThatExist.Contains("SimulationID"))
{
filter = AddToFilter(filter, $"SimulationID in ({simulationIdsCSV})");
}
}
filter = filter?.Replace('\"', '\'');
View = new DataView(data);
View.RowFilter = filter;
// Get the units for our x and y variables.
XFieldUnits = reader.Units(Series.TableName, XFieldName);
YFieldUnits = reader.Units(Series.TableName, YFieldName);
// If data was found, populate our data (e.g. X and Y) properties.
if (View?.Count > 0)
{
X = GetDataFromView(View, XFieldName);
Y = GetDataFromView(View, YFieldName);
X2 = GetDataFromView(View, X2FieldName);
Y2 = GetDataFromView(View, Y2FieldName);
XError = GetErrorDataFromView(View, XFieldName);
YError = GetErrorDataFromView(View, YFieldName);
if (Series.Cumulative)
{
Y = MathUtilities.Cumulative(Y as IEnumerable <double>);
}
if (Series.CumulativeX)
{
X = MathUtilities.Cumulative(X as IEnumerable <double>);
}
}
}
}
/// <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.");
}
}
}
}