61129_en-US

liliconcom

Merging Rows

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 RowsUse the Summary platform
Tables=>SummaryRe: Merging Rows

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

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

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

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

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

By using

     Tables==>Split

you will  be able to do what you want

or with

     Tables==>Transpose

Re: Merging Rows

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

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

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

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

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

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

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

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