Count value in multiple characters and mark position?

[#1]

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

[#2 · Accepted solution]

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

[#3]

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!

[#4]

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

[#5]

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.

[#6 · Accepted solution]

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

dt = Open("pathtomytable");

or

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"}
		)
	)
);

View more...

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

[#7]

Hi Jarmo, 

Thank you so much. It's work.

[#8]

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

[#9]

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

[#10]

There are a few different ways you can do this.

1. Use Recode to create a new column which will have the new group values.
2. Set the count column's Column Property called Value Labels, and use the option "Allow Ranges" to changes what the displayed values are
3. 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"
   		)
   	)
   );