Choose Language Hide Translation Bar
Highlighted
zjuv007
Level III

How to fit data into a standard list of columns?

I have a set of standard columns that I would like to use for multiple data tables, the problem is that some data tables may not contain ALL of those columns.

 

How can I simply:

1. Create a data table with those standard list of columns (ex: ES1, ES2, ES3, ES4, ES5)

ES1ES2ES3ES4ES5
     
     

2. Read a data table that may contain partial of the above columns (ex: ES1, ES3) 

 ES1ES3
Count Line 124
Count Line 2210

3. Create the data table of count of # 2 data table with the full list of standard columns:

 ES1ES2ES3ES4ES5
Count Line 120400
Count Line 2201000

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
txnelson
Super User

Re: How to fit data into a standard list of columns?

Given your new specification, the issue becomes a little more complex.  There is not a built in summation feature in JMP.  There are just so many different actions that one may want to do with the joining of data, the summation is not set as the default item to do.  The simple script below performs the summation you want.  Most of the code shown, is just there to create the sample data tables.  Only the code in lines 32-59 do the work of putting the data together.

Names Default To Here( 1 );

// Create the sample data tables
dt = New Table( "Primary",
	Add Rows( 3 ),
	New Column( "Column 1",
		Character,
		"Nominal",
		Set Selected,
		Set Values( {"NDP1007", "NDP2024", "NDP3030"} )
	),
	New Column( "ES1", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [2, 2, 1] ) ),
	New Column( "ES2", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [5, 0, 1] ) ),
	New Column( "ES3",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [5, 0, 2] ),
		Set Display Width( 46 )
	),
	New Column( "ES4", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [3, 1, 3] ) ),
	New Column( "ES5", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 2, 3] ) ),
	Set Row States( [0, 1, 0] )
);
dt2 = New Table( "Secondary",
	Add Rows( 2 ),
	New Column( "Column 1", Character, "Nominal", Set Values( {"NDP1007", "NDP2024"} ) ),
	New Column( "ES1", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [2, 0] ) ),
	New Column( "ES3", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [2, 3] ) )
);

// Change the names in the Secondary data table so after joining they can be summed together
// and create a list of the new names
newColNameList = {};
For( i = 2, i <= N Cols( dt2 ), i++,
	Column( dt2, i ) << set name( (Column( dt2, i ) << get name) || "_X" );
	Insert Into( NewColNameList, Column( dt2, i ) << get name );
);


// Update the primary data table with the secondary table
dt << Update( With( dt2 ), Match Columns( :Column 1 = :Column 1 ) );

// Create the code to add the data together that needs to be summed
For( col = 1, col <= N Items( newColNameList ), col++, 
    // Make sure the standard name is in the data table and if so, add
	// the values together
	If( Try( Column( dt, Word( -2, newColNameList[col], "_" ) ) << get name ) != "",
		For( theRow = 1, theRow <= N Rows( dt ), theRow++,
			Column( dt, Word( -2, newColNameList[col], "_" ) )[theRow] = Sum(
				Column( dt, Word( -2, newColNameList[col], "_" ) )[theRow],
				Column( dt, newColNameList[col] )[theRow]
			)
		)
	)
);

// Delete the Secondary column no longer needed
dt << delete columns( newColNameList );
Jim

View solution in original post

Highlighted
gzmorgan0
Super User

Re: How to fit data into a standard list of columns?

@zjuv007 ,

 

Jim, @txnelson, provided you with the keys to combine your tables. The last step to get the sum of common records is to execute a Table Sumary.

 

The script below builds on Jim's first solution and assumes you want the Sum() of all numeric columns. You might want to modify numclist to be a list of possible columns, as Jim's second script demonstrated. The script and the data tables, sheet1 and sheet2, are attached.

 

Names Default To Here( 1 );

dt1 = Data Table( "sheet1" );
dt2 = Data Table( "sheet2" );

dt3 = dt1 << concatenate( dt2 );

numclist = dt1 << Get Column Names(Numeric, String);

sumXP = Expr(Summary(
	Group( :Column 1 ),
	Weight( "None" ),
	statistics column name format( "column" ),
	Link to original data table( 0 ),
	Output Table Name ("Summed Table")
) );

for(i=1, i<=nitems(numclist), i++,
	Insert Into(sumXP, EvalExpr(Sum(Column(Expr(numclist[i])))))
);
show(NameExpr(sumXP) );


Eval (EvalExpr(dt3 << expr(sumXP) ) );
dt = Data Table( "Summed Table" );

//option 
dt << delete column( {"N Rows"});

 image.png

View solution in original post

8 REPLIES 8
Highlighted
txnelson
Super User

Re: How to fit data into a standard list of columns?

Here are a couple differentways to do this

Names Default To Here( 1 );

dt1 = Data Table( "Your first table" );
dt2 = Data Table( "Your second table" );

dt1 << concatenate( dt2 );
Names Default To Here( 1 );

StdNamesList = {"PQrz", "acgh","bddxy","abcd"};

ExistingColNames = dt << get column names;
dt2 = Data Table( "Your second table" );

// If a std name exits in the data table, eliminate it from this working copy of the
// translate data table
For(i=1,i<=ncols(dt),i++,
	If(contains(StdNamesList,ExistingColNames[i])
		stdNamesList = remove( stdNamesList,contains(StdNamesList,ExistingColNames[i], 1))
	)
);

// Add in the missing columns
For(i=1,i<=ncols(dt),i++,
	dt2 << New Column(StdNamesList[i],set each value(0));
);
Jim
Highlighted
zjuv007
Level III

Re: How to fit data into a standard list of columns?

Hi Jim,

 

The concatenate doesn't appear to summate the values of common rows, for instance, for NDP1007, I would anticipate the value of ES3 to now be 7 ( the sum total of Sheet 1, Sheet 1 2).

 

Is there a way upon which I can do this?

 

Kind Regards,

Zach

Capture.JPG

Highlighted
zjuv007
Level III

Re: How to fit data into a standard list of columns?

Any thoughts on how to summate two separate tables as I indicated above?

Kind regards,
Zach
Highlighted
txnelson
Super User

Re: How to fit data into a standard list of columns?

Given your new specification, the issue becomes a little more complex.  There is not a built in summation feature in JMP.  There are just so many different actions that one may want to do with the joining of data, the summation is not set as the default item to do.  The simple script below performs the summation you want.  Most of the code shown, is just there to create the sample data tables.  Only the code in lines 32-59 do the work of putting the data together.

Names Default To Here( 1 );

// Create the sample data tables
dt = New Table( "Primary",
	Add Rows( 3 ),
	New Column( "Column 1",
		Character,
		"Nominal",
		Set Selected,
		Set Values( {"NDP1007", "NDP2024", "NDP3030"} )
	),
	New Column( "ES1", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [2, 2, 1] ) ),
	New Column( "ES2", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [5, 0, 1] ) ),
	New Column( "ES3",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [5, 0, 2] ),
		Set Display Width( 46 )
	),
	New Column( "ES4", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [3, 1, 3] ) ),
	New Column( "ES5", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 2, 3] ) ),
	Set Row States( [0, 1, 0] )
);
dt2 = New Table( "Secondary",
	Add Rows( 2 ),
	New Column( "Column 1", Character, "Nominal", Set Values( {"NDP1007", "NDP2024"} ) ),
	New Column( "ES1", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [2, 0] ) ),
	New Column( "ES3", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [2, 3] ) )
);

// Change the names in the Secondary data table so after joining they can be summed together
// and create a list of the new names
newColNameList = {};
For( i = 2, i <= N Cols( dt2 ), i++,
	Column( dt2, i ) << set name( (Column( dt2, i ) << get name) || "_X" );
	Insert Into( NewColNameList, Column( dt2, i ) << get name );
);


// Update the primary data table with the secondary table
dt << Update( With( dt2 ), Match Columns( :Column 1 = :Column 1 ) );

// Create the code to add the data together that needs to be summed
For( col = 1, col <= N Items( newColNameList ), col++, 
    // Make sure the standard name is in the data table and if so, add
	// the values together
	If( Try( Column( dt, Word( -2, newColNameList[col], "_" ) ) << get name ) != "",
		For( theRow = 1, theRow <= N Rows( dt ), theRow++,
			Column( dt, Word( -2, newColNameList[col], "_" ) )[theRow] = Sum(
				Column( dt, Word( -2, newColNameList[col], "_" ) )[theRow],
				Column( dt, newColNameList[col] )[theRow]
			)
		)
	)
);

// Delete the Secondary column no longer needed
dt << delete columns( newColNameList );
Jim

View solution in original post

Highlighted
gzmorgan0
Super User

Re: How to fit data into a standard list of columns?

@zjuv007 ,

 

Jim, @txnelson, provided you with the keys to combine your tables. The last step to get the sum of common records is to execute a Table Sumary.

 

The script below builds on Jim's first solution and assumes you want the Sum() of all numeric columns. You might want to modify numclist to be a list of possible columns, as Jim's second script demonstrated. The script and the data tables, sheet1 and sheet2, are attached.

 

Names Default To Here( 1 );

dt1 = Data Table( "sheet1" );
dt2 = Data Table( "sheet2" );

dt3 = dt1 << concatenate( dt2 );

numclist = dt1 << Get Column Names(Numeric, String);

sumXP = Expr(Summary(
	Group( :Column 1 ),
	Weight( "None" ),
	statistics column name format( "column" ),
	Link to original data table( 0 ),
	Output Table Name ("Summed Table")
) );

for(i=1, i<=nitems(numclist), i++,
	Insert Into(sumXP, EvalExpr(Sum(Column(Expr(numclist[i])))))
);
show(NameExpr(sumXP) );


Eval (EvalExpr(dt3 << expr(sumXP) ) );
dt = Data Table( "Summed Table" );

//option 
dt << delete column( {"N Rows"});

 image.png

View solution in original post

Highlighted
zjuv007
Level III

Re: How to fit data into a standard list of columns?

If I want to expand this to summating across N tables, is there a simple way to fix Sum2Tables.jsl to perform this? (In most cases, I would be limiting it to N=5).

Kind Regards, this worked!
Highlighted
zjuv007
Level III

Re: How to fit data into a standard list of columns?

Also, IF I'd like to simply just generate a standard list of these columns, in instances in which the first table I grab doesn't have all of the appropriate columns... Can I do this first, then simply fill out and count as zeros where there are no counts in that specific sum column?
Highlighted
txnelson
Super User

Re: How to fit data into a standard list of columns?

Before you ask such follow on questions, you need to study the supplied answer, so you understand how it works. If you understand the statement

dt3 = dt1 << concatenate( dt2 );

and look it up in the Scripting Guide, or in the Scripting Index, you will see that you can Concatenate together as many data tables in one statement as you want.

 

Concerning your second question, changing the line 

numclist = dt1 << Get Column Names(Numeric, String);

 to

numclist = dt3 << Get Column Names(Numeric, String);

will give you a complete list of all columns found across all data tables.  Using that combined list might be the answer you need

 

 

 

Jim
Article Labels

    There are no labels assigned to this post.