topic Re: Count value in multiple characters and mark position? in Discussions

Count value in multiple characters and mark position?

doraemengs — Wed, 12 Jun 2024 14:10:47 GMT

Hi, how can I count the number 0,1,2 from the 1 characteristic column as below? Thank you so much! (x = missing)

Pattern	Count 0	Count 1	Count 2	Position 0	Position 1	Position 2
x,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0	26	1	0	2,3,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28	4	0
2,2,2,2,2,0,0,0,0,0,0,1,1,1,2,0,1,1,1,2,2,2,0,0,0,0,0,0
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,0,1,1,1,1,0
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,1,0,1
2,2,2,2,2,2,2,2,0,2,2,2,0,0,0,1,2,2,2,2,2,2,2,2,2,2,2,2

Re: Count value in multiple characters and mark position?

jthi — Wed, 12 Jun 2024 14:20:21 GMT

Loc() can get you the indices. Below is quick and dirty solution

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(5),
	Compress File When Saved(1),
	New Column("Pattern",
		Character,
		"Nominal",
		Set Values(
			{"x,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0",
			"2,2,2,2,2,0,0,0,0,0,0,1,1,1,2,0,1,1,1,2,2,2,0,0,0,0,0,0",
			"0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,0,1,1,1,1,0",
			"0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,1,0,1",
			"2,2,2,2,2,2,2,2,0,2,2,2,0,0,0,1,2,2,2,2,2,2,2,2,2,2,2,2"}
		)
	)
);

vals = {"0", "1", "2"};

For Each({val}, vals,
	new_col1 = dt << New Column("Count " || val, Numeric, Continuous, Formula(
		N Items(Loc(Words(:Pattern, ","), val));
	));
	new_col2 = dt << New Column("Position " || val, Character, Nominal, Formula(
		Char(Substitute(Char(Loc(Words(:Pattern, ","), val);), "[", "", "]", "", " ", ""));
	));
	dt << run formulas;
	new_col1 << delete formula;
	new_col2 << delete formula;
);

Re: Count value in multiple characters and mark position?

doraemengs — Wed, 12 Jun 2024 14:26:03 GMT

Hi jthi,

Thank you for your help. But if I have multiple rows, can I use the matrix or other command instead of the

Set Values(
			{"x,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0",
			"2,2,2,2,2,0,0,0,0,0,0,1,1,1,2,0,1,1,1,2,2,2,0,0,0,0,0,0",
			"0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,0,1,1,1,1,0",
			"0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,1,0,1",
			"2,2,2,2,2,2,2,2,0,2,2,2,0,0,0,1,2,2,2,2,2,2,2,2,2,2,2,2"}

Thank you!

Re: Count value in multiple characters and mark position?

jthi — Wed, 12 Jun 2024 14:32:41 GMT

Get the reference to your datatable to dt variable (for example Open() will return a reference to the table which was opened) and the script should work as long the column is named Pattern

Re: Count value in multiple characters and mark position?

doraemengs — Wed, 12 Jun 2024 14:42:37 GMT

Thank you, but I mean that I have 6453 rows (that I need to copy all of the data into "set value { }". Could you please provide me with a better solution to this? Thank you so much.

Re: Count value in multiple characters and mark position?

jthi — Wed, 12 Jun 2024 14:46:36 GMT

You don't need to copy anything. Just get a reference to your data table.

dt = Open("pathtomytable");

dt = Datatable("mytablename")

or (usually bad option but does what you need it to from time to time)

dt = Current Data Table();

You use the one which fits your needs to replace this part of my solution

dt = New Table("Untitled",
	Add Rows(5),
	Compress File When Saved(1),
	New Column("Pattern",
		Character,
		"Nominal",
		Set Values(
			{"x,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0",
			"2,2,2,2,2,0,0,0,0,0,0,1,1,1,2,0,1,1,1,2,2,2,0,0,0,0,0,0",
			"0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,0,1,1,1,1,0",
			"0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,1,0,1",
			"2,2,2,2,2,2,2,2,0,2,2,2,0,0,0,1,2,2,2,2,2,2,2,2,2,2,2,2"}
		)
	)
);

For example

Names Default To Here(1);

dt = Current Data Table();

vals = {"0", "1", "2"};

For Each({val}, vals,
	new_col1 = dt << New Column("Count " || val, Numeric, Continuous, Formula(
		N Items(Loc(Words(:Pattern, ","), val));
	));
	new_col2 = dt << New Column("Position " || val, Character, Nominal, Formula(
		Char(Substitute(Char(Loc(Words(:Pattern, ","), val);), "[", "", "]", "", " ", ""));
	));
	dt << run formulas;
	new_col1 << delete formula;
	new_col2 << delete formula;
);

Re: Count value in multiple characters and mark position?

doraemengs — Wed, 12 Jun 2024 14:52:33 GMT

Hi Jarmo,

Thank you so much. It's work.

Re: Count value in multiple characters and mark position?

txnelson — Wed, 12 Jun 2024 14:54:18 GMT

Are you saying that your actual data are currently in a JMP matrix, not in a JMP data table?

Re: Count value in multiple characters and mark position?

doraemengs — Wed, 12 Jun 2024 15:34:19 GMT

Hi Jim,

It's my fault understood. My data is on the JMP table. It's already worked on Jarmo advised. In addition, do you have an idea to make the data in "pattern" column to group it in the same groups?. such as positions 1-5 with the value of 1 is the same as positions 6-8 with the value of 1. (how to generate the same pattern of value in multiple positions?)

Pattern	Pattern name
0,0,0,0,0,2,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0	1
0,0,0,2,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0	1
1,1,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0	2
0,0,0,0,0,1,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0	2
0,0,0,0,0,0,0,0,0,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0	2
0,0,0,0,0,0,0,0,0,0,0,1,1,1,1,1,1,0,0,0,0,0,0,0,0,0,0,0	2
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,1,1,1,0,0,0,0,0,0	2
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,1,0,0,0	2
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,1	2
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,2,2,2	3
0,0,0,0,0,0,0,0,0,0,0,2,2,2,2,2,2,0,0,0,0,0,0,0,0,0,0,0	3
0,1,0,0,0,0,0,0,0,0,0,2,2,2,2,2,2,0,0,0,0,0,0,0,0,0,x,0	4
0,0,0,1,0,0,0,0,0,0,0,2,2,2,2,2,2,0,0,0,0,0,0,0,0,0,0,0	4
1,0,0,0,0,1,0,0,0,0,0,2,2,2,2,2,2,0,0,0,0,0,0,0,0,0,0,0	4

Re: Count value in multiple characters and mark position?

txnelson — Wed, 12 Jun 2024 15:50:30 GMT

There are a few different ways you can do this.

Use Recode to create a new column which will have the new group values.
Set the count column's Column Property called Value Labels, and use the option "Allow Ranges" to changes what the displayed values are

Write a little script that creates a new column and does the binning

Names Default To Here( 1 );
dt = Current Data Table();
dt << New Column( "Count 1 Bins",
	character,
	set each value(
		If(
			:Count 1 <= 5, "1-5",
			:Count 1 <= 10, "6-10",
			:Count 1 <= 15, "11-15"
		)
	)
);

Re: Count value in multiple characters and mark position?

doraemengs — Wed, 12 Jun 2024 16:01:51 GMT

Hi Jim,

Thank you so much, but when I try 1 and 2, it seems difficult due to a lot of data rows (6000+). The 3 dose not work because there are multiple patterns on it (2000 pattern of 28 positions in 6000 rows).

Best regards,

Theerachai

Re: Count value in multiple characters and mark position?

txnelson — Wed, 12 Jun 2024 16:36:02 GMT

My misunderstanding. Now my response back to you is that I am sure JMP can do what you want.......if you can define the rules as to how to determine what a group is?

Re: Count value in multiple characters and mark position?

doraemengs — Wed, 12 Jun 2024 18:45:32 GMT

Hi Jim, Thank you, I try to combine the pattern of data. In general, my data positions 1-5, 6-9,10-11,12-17, 18-22, 23-25, and 26-28 are the same categories if they have the same 0-2 points (without fixing position in each category) in addition, sometimes, the categories would be 11-17, 1 5 6 8 13 18 23 26, 2 7 19 24 25 27 28, 3 9, 4 20 22, and 21. And I want to find other patterns in all the data. Could you please to guide me?

Thank you!

Re: Count value in multiple characters and mark position?

txnelson — Thu, 13 Jun 2024 09:41:17 GMT

I remain confused. You attached an example data table. Would you please take a specific row(s) from the example data table and walk through the logic on how you would analyze the data from the row?