topic data cleanup, merge data from 3 columns into one column in Discussions

data cleanup, merge data from 3 columns into one column

TonyS — Sat, 10 Jun 2023 23:38:09 GMT

HI Newbie here, I need some help to merge the data from 3 columns into one column, the data were collected in different time and by different person, and in 3 columns, so there are some missing data, some matched, like this:

A B C

1 1

0 0

1 1

How do I merge the data in A, B, C columns into one column in JMP?

Thank you so much for helping!

Re: data cleanup, merge data from 3 columns into one column

jthi — Fri, 08 Oct 2021 16:03:41 GMT

How would you like to combine them? Sum of them? Concatenate them together? For example to sum them you could create new formula column with following formula:

Sum(:A, :B, :C)

To concatenate (character data), select all columns, right click on one of them and:

Or select all of them, Cols / Utilities / Combine Columns:

Re: data cleanup, merge data from 3 columns into one column

txnelson — Fri, 08 Oct 2021 16:02:25 GMT

Are the columns numeric or character?

Given the above example, what is your expectation of what the final column will contain?

0 0

1 1

Re: data cleanup, merge data from 3 columns into one column

TonyS — Fri, 08 Oct 2021 16:10:31 GMT

Thanks txnelson and jthi, the columns are character, I need them to be either 1 or 0, the data in the 3 columns are either match or missing, so I just need to aggregate them together.

Much appreciated!!

Re: data cleanup, merge data from 3 columns into one column

txnelson — Fri, 08 Oct 2021 16:18:28 GMT

The formula for a new column that contains the single matched value would be

substring( trim(:A || :B || :C), 1, 1 )

Concatenate the 3 columns together, and then take the first character

Re: data cleanup, merge data from 3 columns into one column

jthi — Fri, 08 Oct 2021 16:29:12 GMT

If you are sure you won't have any empty rows (or if you have empty row it will be coded as 0):

If(
	Contains(:A || :B || :C, "1"), "1",
	"0"
)

Also with JMP Missing is not same as "0". With the characters the Missing value would be empty string ("") and with numeric values it would be period (.) and sometimes this has to be taken into account.

Show(Ismissing(""));
Show(IsMissing(.));

Re: data cleanup, merge data from 3 columns into one column

TonyS — Fri, 08 Oct 2021 16:32:13 GMT

Thanks!!!