/// <summary> /// Read output data from the .Rdata file generated by CroptimizR. /// </summary> /// <param name="path">Path to the .Rdata file on disk.</param> public DataTable ReadRData(string path) { StringBuilder script = new StringBuilder(); script.AppendLine($"load('{path.Replace(@"\", @"\\")}')"); IEnumerable <string> paramNames = Parameters.Select(p => $"'{p.Name}'"); script.AppendLine($"param_names <- c({string.Join(", ", paramNames)})"); script.AppendLine(ReflectionUtilities.GetResourceAsString("Models.Resources.RScripts.read_croptimizr_output.r")); R r = new R(); string scriptPath = GetTempFileName("read_croptimizr_output", ".r"); File.WriteAllText(scriptPath, script.ToString()); DataTable table = r.RunToTable(scriptPath); // The repetition column will be of type float. Need to change this to int. string repCol = "Repetition"; int[] reps = DataTableUtilities.GetColumnAsIntegers(table, repCol); table.Columns.Remove(repCol); table.Columns.Add(repCol, typeof(int)).SetOrdinal(0); for (int i = 0; i < table.Rows.Count; i++) { table.Rows[i][0] = reps[i]; } table.TableName = "CroptimizR"; return(table); }
/// <summary>Constructor.</summary> /// <param name="csvData">The data to read.</param> public TextStorageReader(string csvData) { using (var stream = new MemoryStream(Encoding.UTF8.GetBytes(csvData))) { var apsimReader = new ApsimTextFile(); apsimReader.Open(stream); data = apsimReader.ToTable(); apsimReader.Close(); foreach (var unit in apsimReader.Units) { units.Add(unit); } foreach (var heading in apsimReader.Headings) { headings.Add(heading); } if (data.Columns.Contains("SimulationID")) { foreach (var id in DataTableUtilities.GetColumnAsIntegers(data, "SimulationID").Distinct()) { nameIdMap.Add($"Sim{id}", id); } } } }
/// <summary>Gets a filter that includes rowid to implement data pagination (rolling cursor).</summary> /// <param name="from"></param> /// <param name="count"></param> /// <returns></returns> private string GetRollingCursorRowFilter(int from, int count) { string filter = GetFilter(); var data = dataStore.GetDataUsingSql($"SELECT rowid FROM keyset WHERE rowid >= {from+1} ORDER BY rowid LIMIT {count}"); var rowIds = DataTableUtilities.GetColumnAsIntegers(data, "rowid"); var rowIdsCSV = StringUtilities.Build(rowIds, ","); var returnFilter = $"RowID in ({rowIdsCSV})"; if (!string.IsNullOrEmpty(filter)) { returnFilter += $" AND ({filter})"; } return(returnFilter); }
/// <summary> /// Read output data from the .Rdata file generated by CroptimizR. /// </summary> /// <param name="path">Path to the .Rdata file on disk.</param> public DataTable ReadRData(string path) { DataTable table = ApsimTextFile.ToTable(path); // The repetition column will be of type float. Need to change this to int. string repCol = "Repetition"; int[] reps = DataTableUtilities.GetColumnAsIntegers(table, repCol); table.Columns.Remove(repCol); table.Columns.Add(repCol, typeof(int)).SetOrdinal(0); for (int i = 0; i < table.Rows.Count; i++) { table.Rows[i][0] = reps[i]; } table.TableName = "CroptimizR"; return(table); }
/// <summary>Gets a column of data from a view.</summary> /// <param name="data">The table</param> /// <param name="fieldName">Name of the field.</param> /// <returns>The column of data.</returns> private IEnumerable GetDataFromView(DataView data, string fieldName) { if (fieldName != null && data != null && data.Table.Columns.Contains(fieldName)) { if (data.Table.Columns[fieldName].DataType == typeof(DateTime)) { return(DataTableUtilities.GetColumnAsDates(data, fieldName)); } else if (data.Table.Columns[fieldName].DataType == typeof(string)) { return(DataTableUtilities.GetColumnAsStrings(data, fieldName)); } else if (data.Table.Columns[fieldName].DataType == typeof(int)) { return(DataTableUtilities.GetColumnAsIntegers(data, fieldName)); } else { return(DataTableUtilities.GetColumnAsDoubles(data, fieldName)); } } return(null); }
/// <summary>Called by the graph presenter to get a list of all annotations to put on the graph.</summary> public IEnumerable <IAnnotation> GetAnnotations() { List <IAnnotation> annotations = new List <IAnnotation>(); Graph parentGraph = Parent.Parent as Graph; if (data != null && ColumnName != null && xFieldName != null) { string phenologyColumnName = FindPhenologyStageColumn(data); if (phenologyColumnName != null && data.Columns.Contains(xFieldName)) { string[] names = DataTableUtilities.GetColumnAsStrings(data, phenologyColumnName); List <object> x; Type columnType = data.Columns[xFieldName].DataType; if (columnType == typeof(DateTime)) { x = DataTableUtilities.GetColumnAsDates(data, xFieldName).Cast <object>().ToList(); } else if (columnType == typeof(int)) { x = DataTableUtilities.GetColumnAsIntegers(data, xFieldName).Cast <object>().ToList(); } else if (columnType == typeof(double)) { x = DataTableUtilities.GetColumnAsDoubles(data, xFieldName).Cast <object>().ToList(); } else { throw new Exception($"Error in EventNamesOnGraph {Name}: unknown column type '{columnType.FullName}' in column '{xFieldName}'"); } if (names.Length == x.Count) { for (int i = 0; i < names.Length; i++) { if (names[i] != "?" && !string.IsNullOrEmpty(names[i])) { // Add a line annotation. LineAnnotation line = new LineAnnotation(); line.colour = Color.Black; line.type = LineType.Dot; line.x1 = x[i]; line.y1 = double.MinValue; line.x2 = x[i]; line.y2 = double.MaxValue; annotations.Add(line); // Add a text annotation. TextAnnotation text = new TextAnnotation(); text.text = names[i]; text.colour = Color.Black; text.leftAlign = true; text.x = x[i]; text.y = double.MinValue; text.textRotation = 270; annotations.Add(text); } } } } } return(annotations); }
/// <summary>Called by the graph presenter to get a list of all annotations to put on the graph.</summary> /// <param name="annotations">A list of annotations to add to.</param> public void GetAnnotationsToPutOnGraph(List <Annotation> annotations) { Graph parentGraph = Parent.Parent as Graph; if (data != null && ColumnName != null && xFieldName != null) { string columnName = FindColumn(data); if (columnName != null && data.Columns.Contains(xFieldName)) { string[] names = DataTableUtilities.GetColumnAsStrings(data, columnName); List <object> x; Type columnType = data.Columns[xFieldName].DataType; if (columnType == typeof(DateTime)) { x = DataTableUtilities.GetColumnAsDates(data, xFieldName).Cast <object>().ToList(); } else if (columnType == typeof(int)) { x = DataTableUtilities.GetColumnAsIntegers(data, xFieldName).Cast <object>().ToList(); } else if (columnType == typeof(double)) { x = DataTableUtilities.GetColumnAsDoubles(data, xFieldName).Cast <object>().ToList(); } else { throw new Exception($"Error in EventNamesOnGraph {Name}: unknown column type '{columnType.FullName}' in column '{xFieldName}'"); } if (names.Length == x.Count) { var baseColour = Color.LightBlue; var colourMap = new Dictionary <string, Color>(); int startIndex = -1; string startName = string.Empty; for (int i = 0; i < names.Length; i++) { if (startIndex == -1) { startIndex = i; startName = names[i]; } else if (names[i] != startName) { if (!string.IsNullOrEmpty(startName)) { // Add a line annotation. AddAnnotation(annotations, x, baseColour, colourMap, startIndex, startName, i); } startName = names[i]; startIndex = i; } } if (startIndex != -1) { AddAnnotation(annotations, x, baseColour, colourMap, startIndex, startName, names.Length); } } } } }
/// <summary>Main run method for performing our calculations and storing data.</summary> public void Run() { if (dataStore?.Writer != null && !dataStore.Writer.TablesModified.Contains("Report")) { return; } DataTable predictedData = dataStore.Reader.GetData("Report", filter: "SimulationName LIKE '" + Name + "%'", orderBy: "SimulationID"); if (predictedData != null) { IndexedDataTable variableValues = new IndexedDataTable(null); // Determine how many years we have per simulation DataView view = new DataView(predictedData); view.RowFilter = "SimulationName='" + Name + "Simulation1'"; var Years = DataTableUtilities.GetColumnAsIntegers(view, "Clock.Today.Year"); // Create a results table. IndexedDataTable results; if (Years.Count() > 1) { results = new IndexedDataTable(new string[] { "Year" }); } else { results = new IndexedDataTable(null); } // Loop through all years and perform analysis on each. List <string> errorsFromR = new List <string>(); foreach (double year in Years) { view.RowFilter = "Clock.Today.Year=" + year; foreach (DataColumn predictedColumn in predictedData.Columns) { if (predictedColumn.DataType == typeof(double)) { var values = DataTableUtilities.GetColumnAsDoubles(view, predictedColumn.ColumnName); if (values.Distinct().Count() > 1) { variableValues.SetValues(predictedColumn.ColumnName, values); } } } string paramNames = StringUtilities.Build(Parameters.Select(p => p.Name), ",", "\"", "\""); string sobolx1FileName = GetTempFileName("sobolx1", ".csv"); string sobolx2FileName = GetTempFileName("sobolx2", ".csv"); string sobolVariableValuesFileName = GetTempFileName("sobolvariableValues", ".csv"); // Write variables file using (var writer = new StreamWriter(sobolVariableValuesFileName)) DataTableUtilities.DataTableToText(variableValues.ToTable(), 0, ",", true, writer, excelFriendly: false, decimalFormatString: "F6"); // Write X1 using (var writer = new StreamWriter(sobolx1FileName)) DataTableUtilities.DataTableToText(X1, 0, ",", true, writer, excelFriendly: false, decimalFormatString: "F6"); // Write X2 using (var writer = new StreamWriter(sobolx2FileName)) DataTableUtilities.DataTableToText(X2, 0, ",", true, writer, excelFriendly: false, decimalFormatString: "F6"); string script = string.Format( $".libPaths(c('{R.PackagesDirectory}', .libPaths()))" + Environment.NewLine + $"library('boot', lib.loc = '{R.PackagesDirectory}')" + Environment.NewLine + $"library('sensitivity', lib.loc = '{R.PackagesDirectory}')" + Environment.NewLine + "params <- c({0})" + Environment.NewLine + "n <- {1}" + Environment.NewLine + "nparams <- {2}" + Environment.NewLine + "X1 <- read.csv(\"{3}\")" + Environment.NewLine + "X2 <- read.csv(\"{4}\")" + Environment.NewLine + "sa <- sobolSalt(model = NULL, X1, X2, scheme=\"A\", nboot = 100)" + Environment.NewLine + "variableValues = read.csv(\"{5}\")" + Environment.NewLine + "for (columnName in colnames(variableValues))" + Environment.NewLine + "{{" + Environment.NewLine + " sa$y <- variableValues[[columnName]]" + Environment.NewLine + " tell(sa)" + Environment.NewLine + " sa$S$Parameter <- params" + Environment.NewLine + " sa$T$Parameter <- params" + Environment.NewLine + " sa$S$ColumnName <- columnName" + Environment.NewLine + " sa$T$ColumnName <- columnName" + Environment.NewLine + " sa$S$Indices <- \"FirstOrder\"" + Environment.NewLine + " sa$T$Indices <- \"Total\"" + Environment.NewLine + " if (!exists(\"allData\"))" + Environment.NewLine + " allData <- rbind(sa$S, sa$T)" + Environment.NewLine + " else" + Environment.NewLine + " allData <- rbind(allData, sa$S, sa$T)" + Environment.NewLine + "}}" + Environment.NewLine + "write.table(allData, sep=\",\", row.names=FALSE)" + Environment.NewLine , paramNames, NumPaths, Parameters.Count, sobolx1FileName.Replace("\\", "/"), sobolx1FileName.Replace("\\", "/"), sobolVariableValuesFileName.Replace("\\", "/")); DataTable resultsForYear = null; try { resultsForYear = RunR(script); // Put output from R into results table. if (Years.Count() > 1) { results.SetIndex(new object[] { year.ToString() }); } foreach (DataColumn col in resultsForYear.Columns) { if (col.DataType == typeof(string)) { results.SetValues(col.ColumnName, DataTableUtilities.GetColumnAsStrings(resultsForYear, col.ColumnName)); } else { results.SetValues(col.ColumnName, DataTableUtilities.GetColumnAsDoubles(resultsForYear, col.ColumnName)); } } } catch (Exception err) { string msg = err.Message; if (Years.Count() > 1) { msg = "Year " + year + ": " + msg; } errorsFromR.Add(msg); } } var resultsRawTable = results.ToTable(); resultsRawTable.TableName = Name + "Statistics"; dataStore.Writer.WriteTable(resultsRawTable); if (errorsFromR.Count > 0) { string msg = StringUtilities.BuildString(errorsFromR.ToArray(), Environment.NewLine); throw new Exception(msg); } } }
/// <summary>Called by the graph presenter to get a list of all annotations to put on the graph.</summary> /// <param name="annotations">A list of annotations to add to.</param> public void GetAnnotationsToPutOnGraph(List <Annotation> annotations) { Graph parentGraph = Parent.Parent as Graph; if (data != null && ColumnName != null && xFieldName != null) { string columnName = FindColumn(data); if (columnName != null && data.Columns.Contains(xFieldName)) { string[] names = DataTableUtilities.GetColumnAsStrings(data, columnName); List <object> x; Type columnType = data.Columns[xFieldName].DataType; if (columnType == typeof(DateTime)) { x = DataTableUtilities.GetColumnAsDates(data, xFieldName).Cast <object>().ToList(); } else if (columnType == typeof(int)) { x = DataTableUtilities.GetColumnAsIntegers(data, xFieldName).Cast <object>().ToList(); } else if (columnType == typeof(double)) { x = DataTableUtilities.GetColumnAsDoubles(data, xFieldName).Cast <object>().ToList(); } else { throw new Exception($"Error in EventNamesOnGraph {Name}: unknown column type '{columnType.FullName}' in column '{xFieldName}'"); } if (names.Length == x.Count) { var baseColour = Color.LightBlue; var colourMap = new Dictionary <string, Color>(); int startIndex = -1; string startName = string.Empty; for (int i = 0; i < names.Length; i++) { if (names[i] != "?" && !string.IsNullOrEmpty(names[i])) { if (startIndex == -1) { startIndex = i; startName = names[i]; } else if (names[i] != startName) { // Add a line annotation. var bar = new LineAnnotation(); if (colourMap.ContainsKey(startName)) { bar.colour = colourMap[startName]; } else { bar.colour = ColourUtilities.ChangeColorBrightness(baseColour, colourMap.Count * 0.2); colourMap.Add(startName, bar.colour); } bar.type = LineType.Dot; bar.x1 = x[startIndex]; bar.y1 = double.MinValue; bar.x2 = x[i - 1]; bar.y2 = double.MaxValue; bar.InFrontOfSeries = false; bar.ToolTip = startName; annotations.Add(bar); startName = names[i]; startIndex = i; } } } } } } }
/// <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); } }