Calculating Mode for String Data with Grouping

[#1]

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?

[#2]

Do you need a formula or just the result?

[#3]

Thank you for your replying, jthi.

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

[#4]

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

[#5]

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.

[#6 · Accepted solution]

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)]

[#7]

I will post a wish list there, thank you.

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

[#8]

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

[#9]

// データテーブルを取得
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]))]
    );
);

B列のユニークな値を取得し、これを用いてグループごとに処理を行っています。

しかし、これではエラーが出力されます。「You need to input a scriptable object for Send.」。

何か知見があれば教えていただきたいです。

[#10]

// データテーブルを取得
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]))]
    );
);

これでA列のキーを取得するところまでは解決することができました。

jthiさん、お騒がせしました。