/// <summary>
/// Calculates statistics of selected columns. Creates a new table where the statistical data will be written to.
/// </summary>
/// <param name="srctable">Source table.</param>
/// <param name="selectedColumns">Selected data columns in the source table. If the argument is null, all columns will be used.</param>
/// <param name="selectedRows">Selected rows in the source table. If the argument is null, all rows will be used.</param>
/// <param name="destinationTable">The table where the statistical results are written to.</param>
public static void DoStatisticsOnColumns(
this DataColumnCollection srctable,
IAscendingIntegerCollection selectedColumns,
IAscendingIntegerCollection selectedRows,
DataColumnCollection destinationTable
)
{
bool bUseSelectedColumns = (null != selectedColumns && 0 != selectedColumns.Count);
int numcols = bUseSelectedColumns ? selectedColumns.Count : srctable.ColumnCount;
bool bUseSelectedRows = (null != selectedRows && 0 != selectedRows.Count);
if (numcols == 0)
return; // nothing selected
// add a text column and some double columns
// note: statistics is only possible for numeric columns since
// otherwise in one column doubles and i.e. dates are mixed, which is not possible
// 1st column is the name of the column of which the statistics is made
Data.TextColumn colCol = new Data.TextColumn();
// 2nd column is the mean
Data.DoubleColumn colMean = new Data.DoubleColumn();
// 3rd column is the standard deviation
Data.DoubleColumn colSd = new Data.DoubleColumn();
// 4th column is the standard e (N)
Data.DoubleColumn colSe = new Data.DoubleColumn();
// 5th column is the sum
Data.DoubleColumn colSum = new Data.DoubleColumn();
var colSumSqr = new Data.DoubleColumn();
// 6th column is the number of items for statistics
Data.DoubleColumn colN = new Data.DoubleColumn();
var colFracOneSigma = new Data.DoubleColumn();
var colFracTwoSigma = new Data.DoubleColumn();
var colFracThreeSigma = new Data.DoubleColumn();
var colMinimum = new DoubleColumn(); // Minimum of the values
var colMaximum = new DoubleColumn(); // Maximum of the values
int currRow = 0;
for (int si = 0; si < numcols; si++)
{
Altaxo.Data.DataColumn col = bUseSelectedColumns ? srctable[selectedColumns[si]] : srctable[si];
if (!(col is Altaxo.Data.INumericColumn))
continue;
int rows = bUseSelectedRows ? selectedRows.Count : srctable.RowCount;
if (rows == 0)
continue;
// now do the statistics
Data.INumericColumn ncol = (Data.INumericColumn)col;
double sum = 0;
double sumsqr = 0;
int NN = 0;
double minimum = double.PositiveInfinity;
double maximum = double.NegativeInfinity;
for (int i = 0; i < rows; i++)
{
double val = bUseSelectedRows ? ncol[selectedRows[i]] : ncol[i];
if (Double.IsNaN(val))
continue;
NN++;
sum += val;
sumsqr += (val * val);
minimum = Math.Min(minimum, val);
maximum = Math.Max(maximum, val);
}
// now fill a new row in the worksheet
double mean = sum / NN;
double ymy0sqr = sumsqr - sum * sum / NN;
if (ymy0sqr < 0) ymy0sqr = 0; // if this is lesser zero, it is a rounding error, so set it to zero
double sd = NN > 1 ? Math.Sqrt(ymy0sqr / (NN - 1)) : 0;
double se = sd / Math.Sqrt(NN);
// calculate fractions
double oneSigmaLo = mean - 1 * sd, oneSigmaHi = mean + 1 * sd;
double twoSigmaLo = mean - 2 * sd, twoSigmaHi = mean + 2 * sd;
double threeSigmaLo = mean - 3 * sd, threeSigmaHi = mean + 3 * sd;
int cntOneSigma = 0, cntTwoSigma = 0, cntThreeSigma = 0;
for (int i = 0; i < rows; i++)
{
double val = bUseSelectedRows ? ncol[selectedRows[i]] : ncol[i];
if (Double.IsNaN(val))
continue;
if (Altaxo.Calc.RMath.IsInIntervalCC(val, oneSigmaLo, oneSigmaHi)) ++cntOneSigma;
if (Altaxo.Calc.RMath.IsInIntervalCC(val, twoSigmaLo, twoSigmaHi)) ++cntTwoSigma;
if (Altaxo.Calc.RMath.IsInIntervalCC(val, threeSigmaLo, threeSigmaHi)) ++cntThreeSigma;
}
if (0 == NN)
{
minimum = maximum = double.NaN;
}
colCol[currRow] = col.Name;
colMean[currRow] = mean; // mean
colSd[currRow] = sd;
colSe[currRow] = se;
colSum[currRow] = sum;
colSumSqr[currRow] = sumsqr;
colN[currRow] = NN;
colFracOneSigma[currRow] = cntOneSigma / (double)NN;
colFracTwoSigma[currRow] = cntTwoSigma / (double)NN;
colFracThreeSigma[currRow] = cntThreeSigma / (double)NN;
colMinimum[currRow] = minimum;
colMaximum[currRow] = maximum;
currRow++; // for the next column
} // for all selected columns
if (currRow != 0)
{
destinationTable.EnsureExistence(DefaultColumnNameColumnName, typeof(TextColumn), ColumnKind.X, 0).Append(colCol);
AppendStatisticalData(destinationTable, colMean, colSd, colSe, colSum, colSumSqr, colN, colFracOneSigma, colFracTwoSigma, colFracThreeSigma, colMinimum, colMaximum);
}
}
public static void StatisticsOnColumns(
Altaxo.AltaxoDocument mainDocument,
Altaxo.Data.DataTable srctable,
IAscendingIntegerCollection selectedColumns,
IAscendingIntegerCollection selectedRows
)
{
bool bUseSelectedColumns = (null != selectedColumns && 0 != selectedColumns.Count);
int numcols = bUseSelectedColumns ? selectedColumns.Count : srctable.DataColumns.ColumnCount;
bool bUseSelectedRows = (null != selectedRows && 0 != selectedRows.Count);
if (numcols == 0)
{
return; // nothing selected
}
Data.DataTable table = null; // the created table
// add a text column and some double columns
// note: statistics is only possible for numeric columns since
// otherwise in one column doubles and i.e. dates are mixed, which is not possible
// 1st column is the name of the column of which the statistics is made
Data.TextColumn colCol = new Data.TextColumn();
// 2nd column is the mean
Data.DoubleColumn colMean = new Data.DoubleColumn();
// 3rd column is the standard deviation
Data.DoubleColumn colSd = new Data.DoubleColumn();
// 4th column is the standard e (N)
Data.DoubleColumn colSe = new Data.DoubleColumn();
// 5th column is the sum
Data.DoubleColumn colSum = new Data.DoubleColumn();
// 6th column is the number of items for statistics
Data.DoubleColumn colN = new Data.DoubleColumn();
int currRow = 0;
for (int si = 0; si < numcols; si++)
{
Altaxo.Data.DataColumn col = bUseSelectedColumns ? srctable[selectedColumns[si]] : srctable[si];
if (!(col is Altaxo.Data.INumericColumn))
{
continue;
}
int rows = bUseSelectedRows ? selectedRows.Count : srctable.DataColumns.RowCount;
if (rows == 0)
{
continue;
}
// now do the statistics
Data.INumericColumn ncol = (Data.INumericColumn)col;
double sum = 0;
double sumsqr = 0;
int NN = 0;
for (int i = 0; i < rows; i++)
{
double val = bUseSelectedRows ? ncol[selectedRows[i]] : ncol[i];
if (Double.IsNaN(val))
{
continue;
}
NN++;
sum += val;
sumsqr += (val * val);
}
// now fill a new row in the worksheet
if (NN > 0)
{
double mean = sum / NN;
double ymy0sqr = sumsqr - sum * sum / NN;
if (ymy0sqr < 0)
{
ymy0sqr = 0; // if this is lesser zero, it is a rounding error, so set it to zero
}
double sd = NN > 1 ? Math.Sqrt(ymy0sqr / (NN - 1)) : 0;
double se = sd / Math.Sqrt(NN);
colCol[currRow] = col.Name;
colMean[currRow] = mean; // mean
colSd[currRow] = sd;
colSe[currRow] = se;
colSum[currRow] = sum;
colN[currRow] = NN;
currRow++; // for the next column
}
} // for all selected columns
if (currRow != 0)
{
table = new Altaxo.Data.DataTable("Statistics of " + srctable.Name);
table.DataColumns.Add(colCol, "Col", Altaxo.Data.ColumnKind.X);
// new : add a copy of all property columns; can be usefull
for (int i = 0; i < srctable.PropertyColumnCount; i++)
{
DataColumn originalColumn = srctable.PropertyColumns[i];
DataColumn clonedColumn = (DataColumn)originalColumn.Clone();
clonedColumn.Clear();
for (int si = 0; si < numcols; si++)
{
int idx = bUseSelectedColumns ? selectedColumns[si] : si;
clonedColumn[si] = originalColumn[idx];
}
table.DataColumns.Add(clonedColumn, srctable.PropertyColumns.GetColumnName(i), srctable.PropertyColumns.GetColumnKind(i), srctable.PropertyColumns.GetColumnGroup(i));
}
table.DataColumns.Add(colMean, "Mean");
table.DataColumns.Add(colSd, "Sd");
table.DataColumns.Add(colSe, "Se");
table.DataColumns.Add(colSum, "Sum");
table.DataColumns.Add(colN, "N");
mainDocument.DataTableCollection.Add(table);
// create a new worksheet without any columns
Current.ProjectService.CreateNewWorksheet(table);
}
}
public void Required(Data.TextColumn col, string message = "Required")
{
Validate(col, v => !Text.IsNullOrEmpty(v), message);
}
/// <summary>
/// Calculates statistics of selected columns. Creates a new table where the statistical data will be written to.
/// </summary>
/// <param name="srctable">Source table.</param>
/// <param name="selectedColumns">Selected data columns in the source table. If the argument is null, all columns will be used.</param>
/// <param name="selectedRows">Selected rows in the source table. If the argument is null, all rows will be used.</param>
/// <param name="destinationTable">The table where the statistical results are written to.</param>
public static void DoStatisticsOnColumns(
this DataColumnCollection srctable,
IAscendingIntegerCollection selectedColumns,
IAscendingIntegerCollection selectedRows,
DataColumnCollection destinationTable
)
{
bool bUseSelectedColumns = (null != selectedColumns && 0 != selectedColumns.Count);
int numcols = bUseSelectedColumns ? selectedColumns.Count : srctable.ColumnCount;
bool bUseSelectedRows = (null != selectedRows && 0 != selectedRows.Count);
if (numcols == 0)
{
return; // nothing selected
}
// add a text column and some double columns
// note: statistics is only possible for numeric columns since
// otherwise in one column doubles and i.e. dates are mixed, which is not possible
// 1st column is the name of the column of which the statistics is made
var colCol = new Data.TextColumn();
// 2nd column is the mean
var colMean = new Data.DoubleColumn();
// 3rd column is the standard deviation
var colSd = new Data.DoubleColumn();
// 4th column is the standard e (N)
var colSe = new Data.DoubleColumn();
// 5th column is the sum
var colSum = new Data.DoubleColumn();
var colSumSqr = new Data.DoubleColumn();
// 6th column is the number of items for statistics
var colN = new Data.DoubleColumn();
var colFracOneSigma = new Data.DoubleColumn();
var colFracTwoSigma = new Data.DoubleColumn();
var colFracThreeSigma = new Data.DoubleColumn();
var colMinimum = new DoubleColumn(); // Minimum of the values
var colMaximum = new DoubleColumn(); // Maximum of the values
int currRow = 0;
for (int si = 0; si < numcols; si++)
{
Altaxo.Data.DataColumn col = bUseSelectedColumns ? srctable[selectedColumns[si]] : srctable[si];
if (!(col is Altaxo.Data.INumericColumn))
{
continue;
}
int rows = bUseSelectedRows ? selectedRows.Count : srctable.RowCount;
if (rows == 0)
{
continue;
}
// now do the statistics
var ncol = (Data.INumericColumn)col;
double sum = 0;
double sumsqr = 0;
int NN = 0;
double minimum = double.PositiveInfinity;
double maximum = double.NegativeInfinity;
for (int i = 0; i < rows; i++)
{
double val = bUseSelectedRows ? ncol[selectedRows[i]] : ncol[i];
if (double.IsNaN(val))
{
continue;
}
NN++;
sum += val;
sumsqr += (val * val);
minimum = Math.Min(minimum, val);
maximum = Math.Max(maximum, val);
}
// now fill a new row in the worksheet
double mean = sum / NN;
double ymy0sqr = sumsqr - sum * sum / NN;
if (ymy0sqr < 0)
{
ymy0sqr = 0; // if this is lesser zero, it is a rounding error, so set it to zero
}
double sd = NN > 1 ? Math.Sqrt(ymy0sqr / (NN - 1)) : 0;
double se = sd / Math.Sqrt(NN);
// calculate fractions
double oneSigmaLo = mean - 1 * sd, oneSigmaHi = mean + 1 * sd;
double twoSigmaLo = mean - 2 * sd, twoSigmaHi = mean + 2 * sd;
double threeSigmaLo = mean - 3 * sd, threeSigmaHi = mean + 3 * sd;
int cntOneSigma = 0, cntTwoSigma = 0, cntThreeSigma = 0;
for (int i = 0; i < rows; i++)
{
double val = bUseSelectedRows ? ncol[selectedRows[i]] : ncol[i];
if (double.IsNaN(val))
{
continue;
}
if (Altaxo.Calc.RMath.IsInIntervalCC(val, oneSigmaLo, oneSigmaHi))
{
++cntOneSigma;
}
if (Altaxo.Calc.RMath.IsInIntervalCC(val, twoSigmaLo, twoSigmaHi))
{
++cntTwoSigma;
}
if (Altaxo.Calc.RMath.IsInIntervalCC(val, threeSigmaLo, threeSigmaHi))
{
++cntThreeSigma;
}
}
if (0 == NN)
{
minimum = maximum = double.NaN;
}
colCol[currRow] = col.Name;
colMean[currRow] = mean; // mean
colSd[currRow] = sd;
colSe[currRow] = se;
colSum[currRow] = sum;
colSumSqr[currRow] = sumsqr;
colN[currRow] = NN;
colFracOneSigma[currRow] = cntOneSigma / (double)NN;
colFracTwoSigma[currRow] = cntTwoSigma / (double)NN;
colFracThreeSigma[currRow] = cntThreeSigma / (double)NN;
colMinimum[currRow] = minimum;
colMaximum[currRow] = maximum;
currRow++; // for the next column
} // for all selected columns
if (currRow != 0)
{
destinationTable.EnsureExistence(DefaultColumnNameColumnName, typeof(TextColumn), ColumnKind.X, 0).Append(colCol);
AppendStatisticalData(destinationTable, colMean, colSd, colSe, colSum, colSumSqr, colN, colFracOneSigma, colFracTwoSigma, colFracThreeSigma, colMinimum, colMaximum);
}
}
public static void StatisticsOnColumns(
Altaxo.AltaxoDocument mainDocument,
Altaxo.Data.DataTable srctable,
IAscendingIntegerCollection selectedColumns,
IAscendingIntegerCollection selectedRows
)
{
bool bUseSelectedColumns = (null!=selectedColumns && 0!=selectedColumns.Count);
int numcols = bUseSelectedColumns ? selectedColumns.Count : srctable.DataColumns.ColumnCount;
bool bUseSelectedRows = (null!=selectedRows && 0!=selectedRows.Count);
if(numcols==0)
return; // nothing selected
Data.DataTable table = null; // the created table
// add a text column and some double columns
// note: statistics is only possible for numeric columns since
// otherwise in one column doubles and i.e. dates are mixed, which is not possible
// 1st column is the name of the column of which the statistics is made
Data.TextColumn colCol = new Data.TextColumn();
// 2nd column is the mean
Data.DoubleColumn colMean = new Data.DoubleColumn();
// 3rd column is the standard deviation
Data.DoubleColumn colSd = new Data.DoubleColumn();
// 4th column is the standard e (N)
Data.DoubleColumn colSe = new Data.DoubleColumn();
// 5th column is the sum
Data.DoubleColumn colSum = new Data.DoubleColumn();
// 6th column is the number of items for statistics
Data.DoubleColumn colN = new Data.DoubleColumn();
int currRow=0;
for(int si=0;si<numcols;si++)
{
Altaxo.Data.DataColumn col = bUseSelectedColumns ? srctable[selectedColumns[si]] : srctable[si];
if(!(col is Altaxo.Data.INumericColumn))
continue;
int rows = bUseSelectedRows ? selectedRows.Count : srctable.DataColumns.RowCount;
if(rows==0)
continue;
// now do the statistics
Data.INumericColumn ncol = (Data.INumericColumn)col;
double sum=0;
double sumsqr=0;
int NN=0;
for(int i=0;i<rows;i++)
{
double val = bUseSelectedRows ? ncol[selectedRows[i]] : ncol[i];
if(Double.IsNaN(val))
continue;
NN++;
sum+=val;
sumsqr+=(val*val);
}
// now fill a new row in the worksheet
if(NN>0)
{
double mean = sum/NN;
double ymy0sqr = sumsqr - sum*sum/NN;
if(ymy0sqr<0) ymy0sqr=0; // if this is lesser zero, it is a rounding error, so set it to zero
double sd = NN>1 ? Math.Sqrt(ymy0sqr/(NN-1)) : 0;
double se = sd/Math.Sqrt(NN);
colCol[currRow] = col.Name;
colMean[currRow] = mean; // mean
colSd[currRow] = sd;
colSe[currRow] = se;
colSum[currRow] = sum;
colN[currRow] = NN;
currRow++; // for the next column
}
} // for all selected columns
if(currRow!=0)
{
table = new Altaxo.Data.DataTable("Statistics of " + srctable.Name);
table.DataColumns.Add(colCol,"Col",Altaxo.Data.ColumnKind.X);
// new : add a copy of all property columns; can be usefull
for (int i = 0; i < srctable.PropertyColumnCount; i++)
{
DataColumn originalColumn = srctable.PropertyColumns[i];
DataColumn clonedColumn = (DataColumn)originalColumn.Clone();
clonedColumn.Clear();
for (int si = 0; si < numcols; si++)
{
int idx = bUseSelectedColumns ? selectedColumns[si] : si;
clonedColumn[si] = originalColumn[idx];
}
table.DataColumns.Add(clonedColumn, srctable.PropertyColumns.GetColumnName(i), srctable.PropertyColumns.GetColumnKind(i), srctable.PropertyColumns.GetColumnGroup(i));
}
table.DataColumns.Add(colMean,"Mean");
table.DataColumns.Add(colSd,"Sd");
table.DataColumns.Add(colSe,"Se");
table.DataColumns.Add(colSum,"Sum");
table.DataColumns.Add(colN,"N");
mainDocument.DataTableCollection.Add(table);
// create a new worksheet without any columns
Current.ProjectService.CreateNewWorksheet(table);
}
}
