[gnumeric] update the rank & percentiles tool
- From: Andreas J. Guelzow <guelzow src gnome org>
- To: svn-commits-list gnome org
- Subject: [gnumeric] update the rank & percentiles tool
- Date: Wed, 3 Jun 2009 16:56:06 -0400 (EDT)
commit e0fff95b8b14e0e54c8adeca315ae0d51a6095e6
Author: Andreas J. Guelzow <aguelzow pyrshep ca>
Date: Wed Jun 3 14:54:26 2009 -0600
update the rank & percentiles tool
2009-06-03 Andreas J. Guelzow <aguelzow pyrshep ca>
* analysis-tools.c (analysis_tool_calc_length): also handle
GROUPED_BY_AREA
(rank_t): deleted
(cb_rank_compare): deleted
(analysis_tool_ranking_engine_run): rewrite to enter expressions
(analysis_tool_ranking_engine): increase required space
* dao.c (adjust_range): new
(dao_set_array_expr): use adjust_range
(dao_set_cell_expr): ditto
(dao_set_cell_value): ditto
(dao_set_cell_comment): ditto
(dao_set_style): ditto
(dao_set_bold): use dao_set_style
(dao_set_underlined): ditto
(dao_set_merge): new
* dao.h (dao_set_merge): new
---
src/dialogs/ChangeLog | 5 +
src/dialogs/dialog-analysis-tools.c | 2 +-
src/tools/ChangeLog | 19 ++++
src/tools/analysis-tools.c | 203 +++++++++++++++++++++--------------
src/tools/dao.c | 174 +++++++++++-------------------
src/tools/dao.h | 2 +
6 files changed, 210 insertions(+), 195 deletions(-)
diff --git a/src/dialogs/ChangeLog b/src/dialogs/ChangeLog
index 7e7c1ff..33d6059 100644
--- a/src/dialogs/ChangeLog
+++ b/src/dialogs/ChangeLog
@@ -1,3 +1,8 @@
+2009-06-03 Andreas J. Guelzow <aguelzow pyrshep ca>
+
+ * dialog-analysis-tools.c (dialog_ranking_tool): enable formula/variable
+ selector
+
2009-05-28 Andreas J. Guelzow <aguelzow pyrshep ca>
* dialog-so-styled.c (dialog_so_styled_text_widget): make sure
diff --git a/src/dialogs/dialog-analysis-tools.c b/src/dialogs/dialog-analysis-tools.c
index 3200ac6..8391133 100644
--- a/src/dialogs/dialog-analysis-tools.c
+++ b/src/dialogs/dialog-analysis-tools.c
@@ -896,7 +896,7 @@ dialog_ranking_tool (WBCGtk *wbcg, Sheet *sheet)
0))
return 0;
- gnm_dao_set_put (GNM_DAO (state->gdao), FALSE, FALSE);
+ gnm_dao_set_put (GNM_DAO (state->gdao), TRUE, TRUE);
tool_update_sensitivity_cb (NULL, state);
tool_load_selection ((GenericToolState *)state, TRUE);
diff --git a/src/tools/ChangeLog b/src/tools/ChangeLog
index 661d26d..06d887c 100644
--- a/src/tools/ChangeLog
+++ b/src/tools/ChangeLog
@@ -1,3 +1,22 @@
+2009-06-03 Andreas J. Guelzow <aguelzow pyrshep ca>
+
+ * analysis-tools.c (analysis_tool_calc_length): also handle
+ GROUPED_BY_AREA
+ (rank_t): deleted
+ (cb_rank_compare): deleted
+ (analysis_tool_ranking_engine_run): rewrite to enter expressions
+ (analysis_tool_ranking_engine): increase required space
+ * dao.c (adjust_range): new
+ (dao_set_array_expr): use adjust_range
+ (dao_set_cell_expr): ditto
+ (dao_set_cell_value): ditto
+ (dao_set_cell_comment): ditto
+ (dao_set_style): ditto
+ (dao_set_bold): use dao_set_style
+ (dao_set_underlined): ditto
+ (dao_set_merge): new
+ * dao.h (dao_set_merge): new
+
2009-05-23 Morten Welinder <terra gnome org>
* Release 1.9.8
diff --git a/src/tools/analysis-tools.c b/src/tools/analysis-tools.c
index f2f7ffd..4fe4b62 100644
--- a/src/tools/analysis-tools.c
+++ b/src/tools/analysis-tools.c
@@ -713,9 +713,13 @@ int analysis_tool_calc_length (analysis_tools_data_generic_t *info)
GnmValue *current = dataset->data;
int given_length;
- given_length = (info->group_by == GROUPED_BY_COL) ?
- (current->v_range.cell.b.row - current->v_range.cell.a.row + 1) :
- (current->v_range.cell.b.col - current->v_range.cell.a.col + 1);
+ if (info->group_by == GROUPED_BY_AREA) {
+ given_length = (current->v_range.cell.b.row - current->v_range.cell.a.row + 1) *
+ (current->v_range.cell.b.col - current->v_range.cell.a.col + 1);
+ } else
+ given_length = (info->group_by == GROUPED_BY_COL) ?
+ (current->v_range.cell.b.row - current->v_range.cell.a.row + 1) :
+ (current->v_range.cell.b.col - current->v_range.cell.a.col + 1);
if (given_length > result)
result = given_length;
}
@@ -3863,97 +3867,130 @@ analysis_tool_moving_average_engine (data_analysis_output_t *dao, gpointer specs
*
**/
-typedef struct {
- int rank;
- int same_rank_count;
- int point;
- gnm_float x;
-} rank_t;
-
-static gint
-cb_rank_compare (const void *va, const void *vb)
-{
- rank_t const *a = va;
- rank_t const *b = vb;
- if (a->x < b->x)
- return 1;
- else if (a->x == b->x)
- return 0;
- else
- return -1;
-}
-
-
static gboolean
analysis_tool_ranking_engine_run (data_analysis_output_t *dao,
analysis_tools_data_ranking_t *info)
{
- GPtrArray *data = NULL;
- guint n_data;
-
- data = new_data_set_list (info->base.input, info->base.group_by,
- TRUE, info->base.labels, dao->sheet);
-
- for (n_data = 0; n_data < data->len; n_data++) {
- rank_t *rank;
- guint i, j;
- data_set_t * this_data_set;
-
- this_data_set = g_ptr_array_index (data, n_data);
-
- dao_set_cell (dao, n_data * 4, 0, _("Point"));
- dao_set_cell (dao, n_data * 4+1, 0, this_data_set->label);
- dao_set_cell (dao, n_data * 4 + 2, 0, _("Rank"));
- dao_set_cell (dao, n_data * 4 + 3, 0, _("Percentile"));
-
- rank = g_new (rank_t, this_data_set->data->len);
-
- for (i = 0; i < this_data_set->data->len; i++) {
- gnm_float x = g_array_index (this_data_set->data, gnm_float, i);
-
- rank[i].point = i + 1;
- rank[i].x = x;
- rank[i].rank = 1;
- rank[i].same_rank_count = -1;
+ GSList *data = info->base.input;
+ int col = 0;
+
+ GnmFunc *fd_large;
+ GnmFunc *fd_row;
+ GnmFunc *fd_rank;
+ GnmFunc *fd_match;
+ GnmFunc *fd_percentrank;
+
+ fd_large = gnm_func_lookup ("LARGE", NULL);
+ gnm_func_ref (fd_large);
+ fd_row = gnm_func_lookup ("ROW", NULL);
+ gnm_func_ref (fd_row);
+ fd_rank = gnm_func_lookup ("RANK", NULL);
+ gnm_func_ref (fd_rank);
+ fd_match = gnm_func_lookup ("MATCH", NULL);
+ gnm_func_ref (fd_match);
+ fd_percentrank = gnm_func_lookup ("PERCENTRANK", NULL);
+ gnm_func_ref (fd_percentrank);
+
+ dao_set_merge (dao, 0, 0, 1, 0);
+ dao_set_italic (dao, 0, 0, 0, 0);
+ dao_set_cell (dao, 0, 0, _("Ranks & Percentiles"));
- for (j = 0; j < this_data_set->data->len; j++) {
- gnm_float y = g_array_index (this_data_set->data, gnm_float, j);
- if (y > x)
- rank[i].rank++;
- else if (y == x)
- rank[i].same_rank_count++;
- }
+ for (; data; data = data->next, col++) {
+ GnmValue *val_org = value_dup (data->data);
+ GnmExpr const *expr_large;
+ GnmExpr const *expr_rank;
+ GnmExpr const *expr_position;
+ GnmExpr const *expr_percentile;
+ int rows, i;
+
+ dao_set_cell (dao, 0, 1, _("Point"));
+ dao_set_cell (dao, 2, 1, _("Rank"));
+ dao_set_cell (dao, 3, 1, _("Percentile Rank"));
+ analysis_tools_write_label (val_org, dao, &info->base, 1, 1, col + 1);
+
+ rows = (val_org->v_range.cell.b.row - val_org->v_range.cell.a.row + 1) *
+ (val_org->v_range.cell.b.col - val_org->v_range.cell.a.col + 1);
+
+ expr_large = gnm_expr_new_funcall2
+ (fd_large, gnm_expr_new_constant (value_dup (val_org)),
+ gnm_expr_new_binary (gnm_expr_new_binary
+ (gnm_expr_new_funcall (fd_row, NULL),
+ GNM_EXPR_OP_SUB,
+ gnm_expr_new_funcall1
+ (fd_row, dao_get_cellref (dao, 1, 2))),
+ GNM_EXPR_OP_ADD,
+ gnm_expr_new_constant (value_new_int (1))));
+ dao_set_array_expr (dao, 1, 2, 1, rows, gnm_expr_copy (expr_large));
+
+ /* If there are ties the following will only give us the first occurrence... */
+ expr_position = gnm_expr_new_funcall3 (fd_match, expr_large,
+ gnm_expr_new_constant (value_dup (val_org)),
+ gnm_expr_new_constant (value_new_int (0)));
+
+ dao_set_array_expr (dao, 0, 2, 1, rows, expr_position);
+
+ expr_rank = gnm_expr_new_funcall2 (fd_rank,
+ make_cellref (-1,0),
+/* make_rangeref (-1, 0, -1, rows - 1), */
+ gnm_expr_new_constant (value_dup (val_org)));
+ if (info->av_ties) {
+ GnmExpr const *expr_rank_lower;
+ GnmExpr const *expr_rows_p_one;
+ GnmExpr const *expr_rows;
+ GnmFunc *fd_count;
+ fd_count = gnm_func_lookup ("COUNT", NULL);
+ gnm_func_ref (fd_count);
+
+ expr_rows = gnm_expr_new_funcall1
+ (fd_count, gnm_expr_new_constant (value_dup (val_org)));
+ expr_rows_p_one = gnm_expr_new_binary
+ (expr_rows,
+ GNM_EXPR_OP_ADD,
+ gnm_expr_new_constant (value_new_int (1)));
+ expr_rank_lower = gnm_expr_new_funcall3
+ (fd_rank,
+ make_cellref (-1,0),
+/* make_rangeref (-1, 0, -1, rows - 1), */
+ gnm_expr_new_constant (value_dup (val_org)),
+ gnm_expr_new_constant (value_new_int (1)));
+ expr_rank = gnm_expr_new_binary
+ (gnm_expr_new_binary
+ (gnm_expr_new_binary (expr_rank, GNM_EXPR_OP_SUB, expr_rank_lower),
+ GNM_EXPR_OP_ADD, expr_rows_p_one),
+ GNM_EXPR_OP_DIV,
+ gnm_expr_new_constant (value_new_int (2)));
+
+ gnm_func_unref (fd_count);
}
+/* dao_set_array_expr (dao, 2, 2, 1, rows, expr_rank); */
- qsort (rank, this_data_set->data->len,
- sizeof (rank_t), &cb_rank_compare);
-
- dao_set_percent (dao, n_data * 4 + 3, 1,
- n_data * 4 + 3, this_data_set->data->len);
- for (i = 0; i < this_data_set->data->len; i++) {
- /* Point number */
- dao_set_cell_int (dao, n_data * 4 + 0, i + 1, rank[i].point);
+ expr_percentile = gnm_expr_new_funcall3 (fd_percentrank,
+ gnm_expr_new_constant (value_dup (val_org)),
+ make_cellref (-2,0),
+ gnm_expr_new_constant (value_new_int (10)));
- /* GnmValue */
- dao_set_cell_float (dao, n_data * 4 + 1, i + 1, rank[i].x);
-
- /* Rank */
- dao_set_cell_float (dao, n_data * 4 + 2, i + 1,
- rank[i].rank +
- (info->av_ties ? rank[i].same_rank_count / 2. : 0));
-
- /* Percent */
- dao_set_cell_float_na (dao, n_data * 4 + 3, i + 1,
- 1. - (rank[i].rank - 1.) /
- (this_data_set->data->len - 1.),
- this_data_set->data->len != 0);
+ dao_set_percent (dao, 3, 2, 3, 1 + rows);
+ for (i = 2; i < rows + 2; i++) {
+ dao_set_cell_expr ( dao, 2, i, gnm_expr_copy (expr_rank));
+ dao_set_cell_expr ( dao, 3, i, gnm_expr_copy (expr_percentile));
}
- g_free (rank);
+
+
+ dao->offset_col += 4;
+ value_release (val_org);
+ gnm_expr_free (expr_rank);
+ gnm_expr_free (expr_percentile);
}
- destroy_data_set_list (data);
+ gnm_func_unref (fd_large);
+ gnm_func_unref (fd_row);
+ gnm_func_unref (fd_rank);
+ gnm_func_unref (fd_match);
+ gnm_func_unref (fd_percentrank);
- return 0;
+ dao_redraw_respan (dao);
+
+ return FALSE;
}
gboolean
@@ -3969,7 +4006,7 @@ analysis_tool_ranking_engine (data_analysis_output_t *dao, gpointer specs,
case TOOL_ENGINE_UPDATE_DAO:
prepare_input_range (&info->base.input, info->base.group_by);
dao_adjust (dao, 4 * g_slist_length (info->base.input),
- 1 + analysis_tool_calc_length (specs));
+ 2 + analysis_tool_calc_length (specs));
return FALSE;
case TOOL_ENGINE_CLEAN_UP:
return analysis_tool_generic_clean (specs);
diff --git a/src/tools/dao.c b/src/tools/dao.c
index d15df21..07f03bc 100644
--- a/src/tools/dao.c
+++ b/src/tools/dao.c
@@ -40,6 +40,7 @@
#include "workbook-control.h"
#include "command-context.h"
#include "gnm-format.h"
+#include "sheet-merge.h"
#include "sheet-object-cell-comment.h"
#include "style-color.h"
#include "graph.h"
@@ -308,6 +309,29 @@ dao_format_output (data_analysis_output_t *dao, char const *cmd)
}
+static gboolean
+adjust_range (data_analysis_output_t *dao, GnmRange *r)
+{
+ range_normalize (r);
+
+ r->start.col += dao->offset_col + dao->start_col;
+ r->end.col += dao->offset_col + dao->start_col;
+ r->start.row += dao->offset_row + dao->start_row;
+ r->end.row += dao->offset_row + dao->start_row;
+
+ if (dao->type == RangeOutput && (dao->cols > 1 || dao->rows > 1)) {
+ if (r->end.col >= dao->start_col + dao->cols)
+ r->end.col = dao->start_col + dao->cols - 1;
+ if (r->end.row >= dao->start_row + dao->rows)
+ r->end.row = dao->start_row + dao->rows - 1;
+ }
+
+ range_ensure_sanity (r, dao->sheet);
+
+ return ((r->start.col <= r->end.col) && (r->start.row <= r->end.row));
+
+}
+
gboolean
dao_cell_is_visible (data_analysis_output_t *dao, int col, int row)
{
@@ -336,46 +360,19 @@ dao_set_array_expr (data_analysis_output_t *dao,
GnmExpr const *expr)
{
GnmExprTop const *texpr;
- int col_end;
- int row_end;
+ GnmRange r;
- col += dao->offset_col;
- row += dao->offset_row;
- col_end = col + cols - 1;
- row_end = row + rows - 1;
+ range_init (&r, col, row, col + cols - 1, row + rows -1);
- /* Check that the output is in the given range, but allow singletons
- * to expand
- */
- if (dao->type == RangeOutput && (dao->cols > 1 || dao->rows > 1)) {
- if (col >= dao->cols || row >= dao->rows) {
- gnm_expr_free (expr);
- return;
- }
- if (col_end >= dao->cols)
- col_end = dao->cols - 1;
- if (row_end >= dao->rows)
- row_end = dao->rows - 1;
- }
-
- col += dao->start_col;
- row += dao->start_row;
- col_end += dao->start_col;
- row_end += dao->start_row;
- if (col >= gnm_sheet_get_max_cols (dao->sheet)
- || row >= gnm_sheet_get_max_rows (dao->sheet)) {
+ if (!adjust_range (dao, &r)) {
gnm_expr_free (expr);
return;
}
- if (col_end >= gnm_sheet_get_max_cols (dao->sheet))
- col_end = gnm_sheet_get_last_col (dao->sheet);
- if (row_end >= gnm_sheet_get_max_rows (dao->sheet))
- row_end = gnm_sheet_get_last_row (dao->sheet);
texpr = gnm_expr_top_new (expr);
gnm_cell_set_array_formula (dao->sheet,
- col, row,
- col_end, row_end,
+ r.start.col, r.start.row,
+ r.end.col, r.end.row,
texpr);
}
/*
@@ -400,28 +397,16 @@ dao_set_cell_expr (data_analysis_output_t *dao, int col, int row,
{
GnmCell *cell;
GnmExprTop const *texpr;
+ GnmRange r;
- col += dao->offset_col;
- row += dao->offset_row;
+ range_init (&r, col, row, col, row);
- /* Check that the output is in the given range, but allow singletons
- * to expand
- */
- if (dao->type == RangeOutput &&
- (dao->cols > 1 || dao->rows > 1) &&
- (col >= dao->cols || row >= dao->rows)) {
+ if (!adjust_range (dao, &r)) {
gnm_expr_free (expr);
return;
}
- col += dao->start_col;
- row += dao->start_row;
- if (col >= gnm_sheet_get_max_cols (dao->sheet) || row >= gnm_sheet_get_max_rows (dao->sheet)) {
- gnm_expr_free (expr);
- return;
- }
-
- cell = sheet_cell_fetch (dao->sheet, col, row);
+ cell = sheet_cell_fetch (dao->sheet, r.start.col, r.start.row);
texpr = gnm_expr_top_new (expr);
gnm_cell_set_expr (cell, texpr);
gnm_expr_top_unref (texpr);
@@ -447,28 +432,16 @@ void
dao_set_cell_value (data_analysis_output_t *dao, int col, int row, GnmValue *v)
{
GnmCell *cell;
+ GnmRange r;
- col += dao->offset_col;
- row += dao->offset_row;
+ range_init (&r, col, row, col, row);
- /* Check that the output is in the given range, but allow singletons
- * to expand
- */
- if (dao->type == RangeOutput &&
- (dao->cols > 1 || dao->rows > 1) &&
- (col >= dao->cols || row >= dao->rows)) {
+ if (!adjust_range (dao, &r)) {
value_release (v);
return;
}
- col += dao->start_col;
- row += dao->start_row;
- if (col >= gnm_sheet_get_max_cols (dao->sheet) || row >= gnm_sheet_get_max_rows (dao->sheet)) {
- value_release (v);
- return;
- }
-
- cell = sheet_cell_fetch (dao->sheet, col, row);
+ cell = sheet_cell_fetch (dao->sheet, r.start.col, r.start.row);
sheet_cell_set_value (cell, v);
}
@@ -612,25 +585,13 @@ void
dao_set_cell_comment (data_analysis_output_t *dao, int col, int row,
const char *comment)
{
- GnmCellPos pos;
char const *author = NULL;
+ GnmRange r;
- /* Check that the output is in the given range, but allow singletons
- * to expand
- */
- if (dao->type == RangeOutput &&
- (dao->cols > 1 || dao->rows > 1) &&
- (col >= dao->cols || row >= dao->rows))
- return;
-
- col += dao->start_col;
- row += dao->start_row;
- if (col >= gnm_sheet_get_max_cols (dao->sheet) || row >= gnm_sheet_get_max_rows (dao->sheet))
- return;
+ range_init (&r, col, row, col, row);
- pos.col = col;
- pos.row = row;
- cell_set_comment (dao->sheet, &pos, author, comment, NULL);
+ if (adjust_range (dao, &r))
+ cell_set_comment (dao->sheet, &r.start, author, comment, NULL);
}
@@ -714,28 +675,16 @@ static void
dao_set_style (data_analysis_output_t *dao, int col1, int row1,
int col2, int row2, GnmStyle *mstyle)
{
- GnmRange range;
-
- range.start.col = col1 + dao->start_col + dao->offset_col;
- range.start.row = row1 + dao->start_row + dao->offset_row;
- range.end.col = col2 + dao->start_col + dao->offset_col;
- range.end.row = row2 + dao->start_row + dao->offset_row;
+ GnmRange r;
- if (range.end.col > dao->start_col + dao->cols)
- range.end.col = dao->start_col + dao->cols;
- if (range.end.row > dao->start_row + dao->rows)
- range.end.row = dao->start_row + dao->rows;
+ range_init (&r, col1, row1, col2, row2);
- if (range.end.col < range.start.col) {
+ if (!adjust_range (dao, &r)) {
gnm_style_unref (mstyle);
- return;
- }
- if (range.end.row < range.start.row) {
- gnm_style_unref (mstyle);
- return;
+ return;
}
- sheet_style_apply_range (dao->sheet, &range, mstyle);
+ sheet_style_apply_range (dao->sheet, &r, mstyle);
}
/**
@@ -755,15 +704,10 @@ dao_set_bold (data_analysis_output_t *dao, int col1, int row1,
int col2, int row2)
{
GnmStyle *mstyle = gnm_style_new ();
- GnmRange range;
-
- range.start.col = col1 + dao->start_col;
- range.start.row = row1 + dao->start_row;
- range.end.col = col2 + dao->start_col;
- range.end.row = row2 + dao->start_row;
gnm_style_set_font_bold (mstyle, TRUE);
- sheet_style_apply_range (dao->sheet, &range, mstyle);
+
+ dao_set_style (dao, col1, row1, col2, row2, mstyle);
}
/**
@@ -783,15 +727,10 @@ dao_set_underlined (data_analysis_output_t *dao, int col1, int row1,
int col2, int row2)
{
GnmStyle *mstyle = gnm_style_new ();
- GnmRange range;
-
- range.start.col = col1 + dao->start_col;
- range.start.row = row1 + dao->start_row;
- range.end.col = col2 + dao->start_col;
- range.end.row = row2 + dao->start_row;
gnm_style_set_font_uline (mstyle, TRUE);
- sheet_style_apply_range (dao->sheet, &range, mstyle);
+
+ dao_set_style (dao, col1, row1, col2, row2, mstyle);
}
/**
@@ -1216,3 +1155,16 @@ dao_set_omit_so (data_analysis_output_t *dao, gboolean omit)
{
dao->omit_so = omit;
}
+
+
+
+void
+dao_set_merge (data_analysis_output_t *dao, int col1, int row1,
+ int col2, int row2)
+{
+ GnmRange r;
+
+ range_init (&r, col1, row1, col2, row2);
+ if (adjust_range (dao, &r))
+ gnm_sheet_merge_add (dao->sheet, &r, TRUE, NULL);
+}
diff --git a/src/tools/dao.h b/src/tools/dao.h
index 43be52b..fe3a497 100644
--- a/src/tools/dao.h
+++ b/src/tools/dao.h
@@ -77,6 +77,8 @@ void dao_set_date (data_analysis_output_t *dao, int col1, int row1,
int col2, int row2);
void dao_set_format (data_analysis_output_t *dao, int col1, int row1,
int col2, int row2, char const *format);
+void dao_set_merge (data_analysis_output_t *dao, int col1, int row1,
+ int col2, int row2);
void dao_set_colors (data_analysis_output_t *dao, int col1, int row1,
int col2, int row2,
[
Date Prev][
Date Next] [
Thread Prev][
Thread Next]
[
Thread Index]
[
Date Index]
[
Author Index]