Count value in multiple characters and mark position?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 | | | | | | |
Basic Data Analysis and ModelingRe: Count value in multiple characters and mark position?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?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?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?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?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;
);
Re: Count value in multiple characters and mark position?Hi Jarmo,
Thank you so much. It's work.
Re: Count value in multiple characters and mark position?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?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?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?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?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?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?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?
