topic Re: How to subset data with some headers in rows? in Discussions

How to subset data with some headers in rows?

UserID16644 — Tue, 12 Nov 2024 06:43:01 GMT

Hi, I have a big data which has headers inside some rows. I wanted to subset the data per header with its value. My data looks something like this:

Header 1	Column 2	Column 3	Column 4
1	1	1	1
1	1	1	1
1	1	1	1
Header 2	Column 2	Column 3	Column 4
2	2	2	2
2	2	2	2
Header 3	Column 2	Column 3	Column 4
3	3	3	3
3	3	3	3
3	3	3	3

I need to subset the data per Header, so I should have Header 2 with

Header 2	Column 2	Column 3	Column 4
2	2	2	2
2	2	2	2

Header 3

Header 3	Column 2	Column 3	Column 4
3	3	3	3
3	3	3	3
3	3	3	3

I tried creating a Flag column, assign 1 to each header then subset. But the result is incorrect as it subset 1 and blank with Subset 1 with all the headers. This is what I created:

CSV << New Column ("Flag", Numeric);
r = CSV << getRowsWhere( Contains( :Header 1, "Header " ));
Column(CSV, "Flag")[r] = 1;

Please help

Re: How to subset data with some headers in rows?

jthi — Tue, 12 Nov 2024 07:07:21 GMT

Move Header1 "down" from the column name (if it is there),

create flag column with formula such as

If(Starts With(:Column 1, "Header"),
	flag = :Column 1
);
flag;

Then you can subset using that new column as By

Re: How to subset data with some headers in rows?

UserID16644 — Tue, 12 Nov 2024 08:41:00 GMT

Sorry, Im a little bit confused. How did you create Header1 Column?

Re: How to subset data with some headers in rows?

jthi — Tue, 12 Nov 2024 09:31:59 GMT

Using Column Names -> Move down

Re: How to subset data with some headers in rows?

hogi — Tue, 12 Nov 2024 10:37:54 GMT

If(Starts With(:Column 1, "Header"),
	flag = :Column 1
);
flag;

cool , temporary flag - available also for subsequent rows.

Re: How to subset data with some headers in rows?

jthi — Tue, 12 Nov 2024 13:54:13 GMT

You might want to make it a bit more robust by making sure it has been initialized for example by using As Constant() (or If(Row() == 1,....). In this case is wasn't necessary so I didn't bother with it

As Constant(flag = "");
If(Starts With(:Column 1, "Header"),
	flag = :Column 1
);
flag;

Also the readability might improve with something like

As Constant(flag = "");
If(Starts With(:Header 1, "Header"),
	flag = :Header 1
,
	flag
)

You just want to "return" the value you wish to see in rows.

Re: How to subset data with some headers in rows?

UserID16644 — Wed, 13 Nov 2024 06:05:57 GMT

I mean Column 5, sorry for the confusion. Is that from the formula? Im using JMP16 and it has an error

Name Unresolved: flag in access or evaluation of 'flag' , flag/*###*/

Re: How to subset data with some headers in rows?

jthi — Wed, 13 Nov 2024 06:06:24 GMT

After having correct column headers, create new column with a formula

Re: How to subset data with some headers in rows?

UserID16644 — Wed, 13 Nov 2024 06:57:35 GMT

Can this be done using JSL?

Re: How to subset data with some headers in rows?

jthi — Wed, 13 Nov 2024 07:06:16 GMT

Yes. I think JMP can record all these actions and you can get the script from enhanced log (or build workflow)

Re: How to subset data with some headers in rows?

hogi — Wed, 13 Nov 2024 07:48:31 GMT

To create a column with a formula, right click on the column headers to add a new column:

in the column menu, add a formula

and paste the formula from @jthi 's post.