/// <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.DeleteDataInTable(this.Name);
DataTable statsData = new DataTable();
statsData.Columns.Add("SimulationName", typeof(string));
statsData.Columns.Add("VariableName", typeof(string));
statsData.Columns.Add("n", typeof(string));
statsData.Columns.Add("residual", typeof(double));
statsData.Columns.Add("R^2", typeof(double));
statsData.Columns.Add("RMSD", typeof(double));
statsData.Columns.Add("%", typeof(double));
statsData.Columns.Add("MSD", typeof(double));
statsData.Columns.Add("SB", typeof(double));
statsData.Columns.Add("SDSD", typeof(double));
statsData.Columns.Add("LCS", typeof(double));
DataTable simulationData = dataStore.GetData(this.TableName);
if (simulationData != null)
{
DataView view = new DataView(simulationData);
string[] columnNames = DataTableUtilities.GetColumnNames(simulationData);
foreach (string observedColumnName in columnNames)
{
if (observedColumnName.StartsWith("Observed."))
{
string predictedColumnName = observedColumnName.Replace("Observed.", "Predicted.");
if (simulationData.Columns.Contains(predictedColumnName))
{
DataColumn predictedColumn = simulationData.Columns[predictedColumnName];
DataColumn observedColumn = simulationData.Columns[observedColumnName];
if (predictedColumn.DataType == typeof(double) &&
observedColumn.DataType == typeof(double))
{
// Calculate stats for each simulation and store them in a rows in our stats table.
string[] simulationNames = dataStore.SimulationNames;
foreach (string simulationName in simulationNames)
{
string seriesName = simulationName;
view.RowFilter = "SimulationName = '" + simulationName + "'";
CalcStatsRow(view, observedColumnName, predictedColumnName, seriesName, statsData);
}
// Calculate stats for all simulations and store in a row of the stats table.
string overallSeriesName = "Combined " + observedColumnName.Replace("Observed.", "");
view.RowFilter = null;
CalcStatsRow(view, observedColumnName, predictedColumnName, overallSeriesName, statsData);
}
}
}
}
// Write the stats data to the DataStore
statsData.TableName = this.Name;
dataStore.WriteTable(statsData);
}
}
/// <summary>Find all report models and give them the factor values.</summary>
/// <param name="factorValues">The factor values to send to each report model.</param>
/// <param name="simulation">The simulation to search for report models.</param>
private void StoreFactorsInDataStore(Simulation simulation, List <FactorValue> factorValues)
{
List <string> names = new List <string>();
List <string> values = new List <string>();
GetFactorNamesAndValues(factorValues, names, values);
string parentFolderName = null;
IModel parentFolder = Apsim.Parent(this, typeof(Folder));
if (parentFolder != null)
{
parentFolderName = parentFolder.Name;
}
DataTable factorTable = new DataTable();
factorTable.TableName = "_Factors";
factorTable.Columns.Add("ExperimentName", typeof(string));
factorTable.Columns.Add("SimulationName", typeof(string));
factorTable.Columns.Add("FolderName", typeof(string));
factorTable.Columns.Add("FactorName", typeof(string));
factorTable.Columns.Add("FactorValue", typeof(string));
for (int i = 0; i < names.Count; i++)
{
DataRow row = factorTable.NewRow();
row[0] = Name;
row[1] = simulation.Name;
row[2] = parentFolderName;
row[3] = names[i];
row[4] = values[i];
factorTable.Rows.Add(row);
}
storage.WriteTable(factorTable);
}
/// <summary>Main run method for performing our post simulation calculations</summary>
/// <param name="dataStore">The data store.</param>
public void Run(IStorageReader dataStore)
{
DataTable predictedData = dataStore.GetData("Report");
if (predictedData != null)
{
DataTable elementalEffects = CreateElementalEffectsTable(predictedData);
dataStore.DeleteDataInTable(elementalEffects.TableName);
dataStore.WriteTable(elementalEffects);
DataTable muStarByPath = CreateMuStarByPath(elementalEffects);
dataStore.DeleteDataInTable(muStarByPath.TableName);
dataStore.WriteTable(muStarByPath);
DataTable muStar = CreateMuStar(muStarByPath);
dataStore.DeleteDataInTable(muStar.TableName);
dataStore.WriteTable(muStar);
}
}
/// <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.DeleteDataInTable(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.WriteTable(probabilityData);
}
}
/// <summary>
/// Main run method for performing our calculations and storing data.
/// </summary>
/// <param name="dataStore">The data store to store the data</param>
public void Run(IStorageReader dataStore)
{
string fullFileName = AbsoluteFileName;
if (fullFileName != null && File.Exists(fullFileName))
{
// Open the file
FileStream stream = File.Open(fullFileName, FileMode.Open, FileAccess.Read, FileShare.Read);
// Create a reader.
IExcelDataReader excelReader;
if (Path.GetExtension(fullFileName).Equals(".xls", StringComparison.CurrentCultureIgnoreCase))
{
throw new Exception("EXCEL file must be in .xlsx format. Filename: " + fullFileName);
}
else
{
// Reading from a OpenXml Excel file (2007 format; *.xlsx)
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
// Read all sheets from the EXCEL file as a data set
// excelReader.IsFirstRowAsColumnNames = true;
DataSet dataSet = excelReader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
// Write all sheets that are specified in 'SheetNames' to the data store
foreach (DataTable table in dataSet.Tables)
{
bool keep = StringUtilities.IndexOfCaseInsensitive(this.SheetNames, table.TableName) != -1;
if (keep)
{
TruncateDates(table);
dataStore.DeleteDataInTable(table.TableName);
dataStore.WriteTable(table);
}
}
// Close the reader and free resources.
excelReader.Close();
}
}
/// <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.DeleteDataInTable(this.Name);
DataTable simulationData = dataStore.GetData(TableName, fieldNames: dataStore.GetTableColumns(TableName));
if (simulationData != null)
{
IndexedDataTable simData = new IndexedDataTable(simulationData, new string[] { FieldToSplitOn });
IndexedDataTable probabilityData = new IndexedDataTable(new string[] { FieldToSplitOn });
foreach (var group in simData.Groups())
{
object keyValue = group.IndexValues[0];
// Add in our key column
probabilityData.SetIndex(new object[] { keyValue });
probabilityData.Set <object>(FieldToSplitOn, keyValue);
// Add in all other numeric columns.
bool haveWrittenProbabilityColumn = false;
foreach (DataColumn column in simulationData.Columns)
{
if (column.DataType == typeof(double))
{
var values = group.Get <double>(column.ColumnName).ToList();
values.Sort();
if (!haveWrittenProbabilityColumn)
{
// Add in the probability column
double[] probabilityValues = MathUtilities.ProbabilityDistribution(values.Count, this.Exceedence);
probabilityData.SetValues("Probability", probabilityValues);
haveWrittenProbabilityColumn = true;
}
probabilityData.SetValues(column.ColumnName, values);
}
}
}
// Write the stats data to the DataStore
DataTable t = probabilityData.ToTable();
t.TableName = this.Name;
dataStore.WriteTable(t);
}
}
/// <summary>
/// Main run method for performing our calculations and storing data.
/// </summary>
public void Run(IStorageReader dataStore)
{
string fullFileName = FullFileName;
if (fullFileName != null)
{
Simulations simulations = Apsim.Parent(this, typeof(Simulations)) as Simulations;
dataStore.DeleteDataInTable(Name);
DataTable data = GetTable();
if (data != null)
{
data.TableName = this.Name;
dataStore.WriteTable(data);
}
}
}
/// <summary>Main run method for performing our post simulation calculations</summary>
/// <param name="dataStore">The data store.</param>
public void Run(IStorageReader dataStore)
{
string sql = "SELECT * FROM REPORT WHERE SimulationName LIKE '" + Name + "%' ORDER BY SimulationID";
DataTable predictedData = dataStore.RunQuery(sql);
if (predictedData != null && hasRun)
{
// 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.DeleteDataInTable(eeTable.TableName);
dataStore.WriteTable(eeTable);
dataStore.DeleteDataInTable(muStarTable.TableName);
dataStore.WriteTable(muStarTable);
}
hasRun = false;
}
/// <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(IStorageReader dataStore)
{
if (PredictedTableName != null && ObservedTableName != null)
{
dataStore.DeleteDataInTable(this.Name);
List <string> predictedDataNames = dataStore.GetTableColumns(PredictedTableName);
List <string> observedDataNames = dataStore.GetTableColumns(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("I.'@field', ");
}
else
{
query.Append("I.'@field' AS 'Observed.@field', R.'@field' AS 'Predicted.@field', ");
}
query.Replace("@field", s);
}
query.Append("FROM " + ObservedTableName + " I INNER JOIN " + PredictedTableName + " R USING (SimulationID) WHERE I.'@match1' = R.'@match1'");
if (FieldName2UsedForMatch != null && FieldName2UsedForMatch != string.Empty)
{
query.Append(" AND I.'@match2' = R.'@match2'");
}
if (FieldName3UsedForMatch != null && FieldName3UsedForMatch != string.Empty)
{
query.Append(" AND I.'@match3' = R.'@match3'");
}
int checkpointID = dataStore.GetCheckpointID("Current");
query.Append(" AND R.CheckpointID = " + checkpointID);
query.Replace(", FROM", " FROM"); // get rid of the last comma
query.Replace("I.'SimulationID' AS 'Observed.SimulationID', R.'SimulationID' AS 'Predicted.SimulationID'", "I.'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)))
{
simulationNames.AddRange(experiment.GetSimulationNames());
}
foreach (Simulation simulation in Apsim.FindAll(this, typeof(Simulation)))
{
if (!(simulation.Parent is Experiment))
{
simulationNames.Add(simulation.Name);
}
}
query.Append(" AND I.SimulationID in (");
foreach (string simulationName in simulationNames)
{
if (simulationName != simulationNames[0])
{
query.Append(',');
}
query.Append(dataStore.GetSimulationID(simulationName));
}
query.Append(")");
}
DataTable predictedObservedData = dataStore.RunQuery(query.ToString());
if (predictedObservedData != null)
{
predictedObservedData.TableName = this.Name;
dataStore.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.GetUnits(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.AddUnitsForTable(Name, unitFieldNames, unitNames);
}
}
else
{
// Determine what went wrong.
DataTable predictedData = dataStore.RunQuery("SELECT * FROM " + PredictedTableName);
DataTable observedData = dataStore.RunQuery("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.");
}
}
}
}
