topic Re: Merging Rows in Discussions

Merging Rows

Jaz — Tue, 26 Jun 2018 12:57:32 GMT

Hi,

I was just wondering if there was away to merge two rows from the same data table using JSL? I was contemplating using the Join function but I'm not entirely sure how that will work.

Thanks.

Re: Merging Rows

txnelson — Tue, 26 Jun 2018 13:07:34 GMT

You need to just try it. Interactively join the table to it's self and see the results. If you then open the "Source" entry in the Tables Panel on the left of the data table, you will see the JSL used to join the data table with it'self

Re: Merging Rows

dale_lehman — Tue, 26 Jun 2018 14:31:26 GMT

I think you need to say more about your intent with merging rows. I've had data sets where there were multiple rows that needed to be "combined" into a single observation. How to do it depended on whether I wanted the average or sum (or....) of those rows. Other times, there are just multiple rows with identical data and I want to eliminate the multiple observations. So, I think you need to say why there are multiple rows to begin with and what you are attempting to reduce them to single rows for.

Re: Merging Rows

Jaz — Tue, 26 Jun 2018 14:42:22 GMT

Hi,

Basically I have two heading rows for each of my columns, the first is the main heading of the column and the second is the sub-heading of the column. I want to combine the heading and subheading rows so that they only take up one row. I'm aware I could do this by accessing values and replacing the contents of the first row and deleting the second but this seems like a very arduous task. I would much rather there be a concise function that could help me?

Re: Merging Rows

dale_lehman — Tue, 26 Jun 2018 14:46:58 GMT

I'll leave it for someone better at scripting to answer your specific question. But I've only had that particular issue occur when importing data from elsewhere (such as a spreadsheet or text file). If that is the case, it is best solved at the file opening stage - you can change the open setting so that data starts on line 3 and the heading comprises 2 lines rather than 1. It would seem odd to create a data set in JMP where the heading is put on two lines to start with.

Re: Merging Rows

Jaz — Tue, 26 Jun 2018 15:07:59 GMT

Ok, thanks anyway. When I try opening via "Data With Preview" and try to enter the first two line numbers in the "Files contain column names on line: ", the wizard's won't let me enter two. Any suggestions?

Re: Merging Rows

dale_lehman — Tue, 26 Jun 2018 15:19:45 GMT

What kind of file are you opening? If it is a text file, make sure the radio button says "data with preview" and if it is Excel then make the radio button for the "Always force row 1..." is set to Never.

Re: Merging Rows

Jeff_Perkinson — Tue, 26 Jun 2018 15:34:07 GMT

Try the Column Name Utilities add-in by @XanGregg and @MikeD_Anderson.

It has tools for promoting a row into the column names. I think that might be what you're looking for.

Re: Merging Rows

chessknt87 — Tue, 21 Aug 2018 14:45:18 GMT

My question is similar to this one I think.

My table has a column with repeating values but the rows have unique values and I am wondering if it is possible to consolidate the repeating rows in to a single row with new columns. Example below:

Subject Value Subject Value Value2

A 1 A 1 2

A 2 ----> B 3 4

B 3

B 4

Re: Merging Rows

txnelson — Tue, 21 Aug 2018 14:50:28 GMT

By using

Tables==>Split

you will be able to do what you want

or with

Tables==>Transpose

Re: Merging Rows

chessknt87 — Tue, 21 Aug 2018 15:07:02 GMT

Thank you for your reply. The problem is that I want it to create new column labels since the data in the table has nothing to split by. If I group by subject and ask it to split value and date what do I pick for split by? I want each observation to just be listed under a new column created for a duplicate observation

Subject Value Date Subject Value Date Value2 Date2 value3 date3

A 1 x A 1 x 2 y . .

A 2 y ----> B 3 z 4 g 5 f

B 3 z

B 4 g

B 5 f

Re: Merging Rows

txnelson — Tue, 21 Aug 2018 15:16:34 GMT

Create a new column that gives a sequence number for each of your subjects

if(row()==1,counter-1);
If(lag(:subject) != :subject, counter=1, counter=counter+1);
counter;

Then you can split by the new column

Re: Merging Rows

kevin_herlihy_l — Fri, 22 Mar 2019 14:53:34 GMT

Hi! I've got a similar problem. I'd like to merge rows with the same time value without creating new columns. So in the example below rows 75629 and 75630 would become one row with more data in it and 75631/2 would move up one number. Is there a way to do something like this?

Re: Merging Rows

Jeff_Perkinson — Sat, 23 Mar 2019 14:09:48 GMT

If it's a matter of dealing with the missing values for those columns you might try using Tables->Summary to group by your datetime column and use the Max statististic for all those columns.

Re: Merging Rows

plfazeli — Wed, 16 Sep 2020 18:11:05 GMT

You mentioned this in a post a few years ago "I've had data sets where there were multiple rows that needed to be "combined" into a single observation. How to do it depended on whether I wanted the average or sum (or....) of those rows."

Can you tell me how to do this? (I would want a sum of the value in a variable for unique subject IDs with multiple rows of data)

Re: Merging Rows

txnelson — Wed, 16 Sep 2020 18:53:59 GMT

Use the Summary platform
Tables=>Summary