932917_en-US

liliconcom

Calculating Mode for String Data with Grouping

I attempted to calculate the mode of column A, grouped by column B, using the Col Mode(:A,:B) function. However, since column A contains string data, no value was output. I then discovered that Mode(:A) can obtain the mode even for string data. The problem now is that I don't know how to perform the grouping. How should I construct the formula?

Automation and ScriptingRe: Calculating Mode for String Data with Grouping

Do you need a formula or just the result?

Re: Calculating Mode for String Data with Grouping

Thank you for your replying, jthi.

I need a formula. I would like to write it on a data table.

Re: Calculating Mode for String Data with Grouping

Easiest would be most likely to create new column in which you convert your values numeric (Recode -> Labels to Codes can do it) and use that for the calculation. You can then use Copy Column Properties to move over the Label Values column property.

Also, make a wish list item Col Mode() to support character columns

Re: Calculating Mode for String Data with Grouping

So, it means it takes an extra step. I understand. I would like to request that Col Mode() supports string columns. Thank you very much.

Re: Calculating Mode for String Data with Grouping

You can create wish list item here. You could for example create workflow or script to take care of that extra step.

 

It is most likely also possible with a formula, no idea how efficient this would be. This should get the mode of :sex over :age

As Constant(
	score_indices = Associative Array(:sex) << get keys
);
score_indices[Col Mode(Col Score(:sex), :age)]

Re: Calculating Mode for String Data with Grouping

I will post a wish list there, thank you.

How did you create the column "S"? I am interested in the idea!

Re: Calculating Mode for String Data with Grouping

I did provide the formula for S (requires JMP19).

Re: Calculating Mode for String Data with Grouping

// データテーブルを取得
dt = Current Data Table();

// B列のユニークな値を取得（グループ化のため）
unique_groups = As List(dt:B<< Get As Matrix);

// 新しい列 "mother" を作成
dt << New Column("mother", Character, "Nominal");

// 各グループごとに処理を実行
For(i = 1, i <= N Items(unique_groups), i++,
    // 現在のグループをフィルタリング
    current_group = unique_groups[i];
    Show(current_group);
    subset_rows = As List(dt << Get Rows Where(:B == current_group));
    Show(subset_rows);
    Show(Type(subset_rows));

    // A列のキーを取得
    score_indices = dt:A[subset_rows] << Get As List;
    Show(score_indices);

    // グループ内の "mother" 列を更新
    dt:mother[subset_rows] = If(
        Is Missing(score_indices[Col Mode(Col Score(dt:A[subset_rows]))]),
        score_indices[2],
        score_indices[Col Mode(Col Score(dt:A[subset_rows]))]
    );
);

The unique values ​​in column B are extracted and used to process each group.

However, this will output an error: " You need to input a scriptable object for Send. "

If you have any insight, please let me know.

Re: Calculating Mode for String Data with Grouping

// データテーブルを取得
dt = Current Data Table();

// B列のユニークな値を取得（グループ化のため）
unique_groups = As List(dt:B<< Get As Matrix);

// 新しい列 "mother" を作成
dt << New Column("mother", Character, "Nominal");

// 各グループごとに処理を実行
For(i = 1, i <= N Items(unique_groups), i++,
    // 現在のグループをフィルタリング
    current_group = unique_groups[i];
    Show(current_group);
    subset_rows = As List(dt << Get Rows Where(:B == current_group));
    Show(subset_rows);
    Show(Type(subset_rows));

    // A列のキーを取得
    score_indices = Associative Array(Set Unique(dt:A[subset_rows]));    Show(score_indices);

    // グループ内の "mother" 列を更新
    dt:mother[subset_rows] = If(
        Is Missing(score_indices[Col Mode(Col Score(dt:A[subset_rows]))]),
        score_indices[2],
        score_indices[Col Mode(Col Score(dt:A[subset_rows]))]
    );
);

This has solved the problem up to the point of getting the key for column A.

Sorry for the trouble, jthi.

Re: Calculating Mode for String Data with Grouping

// データテーブルを取得
dt = Current Data Table();
process_nm = "A";

// B列のユニークな値を取得（グループ化のため）
unique_groups = dt:B << Get values;

// 新しい列 "mother" を作成
dt << New Column("mother", Character, "Nominal");

// 各グループごとに処理を実行
For(i = 1, i <= N Items(unique_groups), i++,
    // 現在のグループをフィルタリング
    current_group = unique_groups[i];
    subset_rows = dt << Get Rows Where(:B == current_group);
    // A列のキーを取得
    score_indices = Associative Array(Set Unique(Column(process_nm)[subset_rows])) << get keys;
    // グループ内の "mother" 列を更新
    dt:mother[subset_rows] = Mode(Column(process_nm)[subset_rows]);
);

Calculating Mode for String Data with Grouping

- Col Mode() does not support strings

・Mode() supports strings

・Mode() does not support grouping

【countermeasure】

1. Treat strings like numbers and use Col Mode().

2. Filter the rows in advance and use Mode().

Re: Calculating Mode for String Data with Grouping

A wish along this line:
:folded_hands: Summary and Tabulate: add aggregation option for Character columns 

Once the feature has been implemented, you will be able to use the Tables/Summary function for this task.