82841_en-US

liliconcom

How do I merge two rows that don't have a key column

Hello JMP Community,

 

So I am doing a project for a friend where I am analzying stats for cricket players in the Indian League. I am using the internet open option in JMP to get the data from the internet. I get the table below. I am wondering if there is any way through JMP or SAS that I can merge the rows with the player's stats with the row right below it which has the player's team. Any help would be appreaciated

 

Thank you

IC

 

Re: How do I merge two rows that don't have a key column

Thank you for your solution. I edited the script so that the rows with all the teams(the even rows) would fill a matrix A. 

dt= current data table();
// Find all of the rows that have the teams
dt << select where( Substr( :Player, 1, 1 ) == "(" );

A= dt << Get Rows Where(Substr( :Player, 1, 1 ) == "(");

// Copy the team rows to a new table
dt2 = dt << Subset(
 Selected Rows( 0 ),
 rows(A),
 Selected columns only( 0 )
);

// Delete the team rows from the starting data table
dt << delete rows;

// Join the 2 data tables
dt3 = dt << Join( With( dt2 ), By Row Number );

Re: How do I merge two rows that don't have a key column

Here is one way to do this.  It separates the data into two tables and then merges them together based upon row number.  I am doing this with a script, but the script was built just going through the interactive steps to do this, and copying the scripts that JMP created.  I am using a shortened data table, because I am lazy and don't like to type.

Names Default To Here( 1 );
// Create a sample data table
dt = New Table( "Untitled 10",
 Add Rows( 6 ),
 New Column( "Player",
  Character,
  "Nominal",
  Set Values( {"AR Bawne", "(Delhi Daredevils)", "VR Aaron", "(Kings XI Punjab)", "AD Nath", "(Gujarat Lions)"} ),
  Set Display Width( 122 )
 ),
 New Column( "Mat",
  Character,
  "Nominal",
  Set Values( {"1", "(Delhi Daredevils)", "6", "(Kings XI Punjab)", "1", "(Gujarat Lions)"} ),
  Set Display Width( 137 )
 ),
 New Column( "Inns",
  Character,
  "Nominal",
  Set Values( {"1", "(Delhi Daredevils)", "2", "(Kings XI Punjab)", "1", "(Gujarat Lions)"} ),
  Set Display Width( 160 )
 )
);

wait(3);

// Find all of the rows that have the teams
dt << select where( Substr( :Player, 1, 1 ) == "(" );

// Copy the team rows to a new table
dt2 = dt << Subset(
 Selected Rows( 0 ),
 Rows( [2, 4, 6] ),
 Selected columns only( 0 )
);

// Delete the team rows from the starting data table
dt << delete rows;

// Change the columns to numeric in the starting data table(just because)
For( i = 2, i <= N Cols( dt ), i++,
 Column( dt, i ) << data type( numeric ) <<
 modeling type( continuous )
);

// Join the 2 data tables
dt3 = dt << Join( With( dt2 ), By Row Number );