BookmarkSubscribeRSS Feed
IC

New Contributor

Joined:

Nov 7, 2018

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

cricket.PNG

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson

Super User

Joined:

Jun 22, 2012

Solution

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.

secondline.PNG

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 );

 

Jim
2 REPLIES
Highlighted
txnelson

Super User

Joined:

Jun 22, 2012

Solution

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.

secondline.PNG

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 );

 

Jim
IC

New Contributor

Joined:

Nov 7, 2018

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 );