504584_en-US

liliconcom

Cleaning up a large data set with disorganized formats in JMP 14.2.0

Hi, I have a large data set with a combination of useful information in columns and in rows. The data in rows has a row with the type of information being collected which then needs to be linked to a result in a row further down in the column. The amount of rows with this information is variable and in some cases is a free form input rather than a specified input. I've been using recoding, subsetting, and transposing to get the data into a manageable format but it's time consuming and difficult to data verify as I have >50K rows to work with. I have tried to generate a simplified example of what I have and what I would like below. Can anybody help?

 

What I have 

Assay	Useful info 1	Useful info 2	Useful info 3	Useful info 4	Other info	Value for other info
1	Control Lot number	abcdefg	Control result	10	Useful info 5	Method ID
1	Control Lot number	abcdefg	Control result	10	Useful info 6	Method Version
1	Control Lot number	abcdefg	Control result	10	Result for useful info 5	Method x
1	Control Lot number	abcdefg	Control result	10	Result for useful info 6	1
2	Control Lot number	abcdefg	Control result	11	Useful info 5	Method ID
2	Control Lot number	abcdefg	Control result	11	Useful info 6	Method Version
2	Control Lot number	abcdefg	Control result	11	Useful info 7	Sample result
2	Control Lot number	abcdefg	Control result	11	Useful info 8	Equipment Number
2	Control Lot number	abcdefg	Control result	11	Result for useful info 5	Method x
2	Control Lot number	abcdefg	Control result	11	Result for useful info 6	1
2	Control Lot number	abcdefg	Control result	11	Result for useful info 7	12
2	Control Lot number	abcdefg	Control result	11	Result for useful info 8	123456
3	Control Lot number	abcdefg	Control result	10	Useful info 5	Method ID
3	Control Lot number	abcdefg	Control result	10	Useful info 6	Method Version
3	Control Lot number	abcdefg	Control result	10	Useful info 7	Sample result
3	Control Lot number	abcdefg	Control result	10	Useful info 8	Equipment Type
3	Control Lot number	abcdefg	Control result	10	Useful info 9	Equipment Number
3	Control Lot number	abcdefg	Control result	10	Result for useful info 5	Method x
3	Control Lot number	abcdefg	Control result	10	Result for useful info 6	1
3	Control Lot number	abcdefg	Control result	10	Result for useful info 7	12
3	Control Lot number	abcdefg	Control result	10	Result for useful info 8	Reader
3	Control Lot number	abcdefg	Control result	10	Result for useful info 9	123456

 

What I would like:

Assay	Control Lot number	Control result	Method ID	Method Version	Sample result	Equipment Number	Equipment Type
1	abcdefg	10	Method x	1
2	abcdefg	11	Method x	1	12	123456
3	abcdefg	10	Method x	1	12	123456	Reader

 

Or this could also work:

Assay	Parameter	Result
1	Control result	10
1	Method ID	Method x
1	Method Version	1
1	Sample result
1	Equipment Number
1	Equipment Type
1	Equipment Number
2	Method ID	Method x
2	Method Version	1
2	Sample result	12
2	Equipment Number	123456
2	Equipment Type
2	Equipment Number
3	Method ID	Method x
3	Method Version	1
3	Sample result	12
3	Equipment Number	123456
3	Equipment Type	Reader
3	Equipment Number	123456

 

Data Table