Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
Jimmy84
Level I

Combine tables with missing rows and apply formula

I have two data sets. One is an average of insect counts that were done in a plot weekly or biweekly. The other is the yield that was measured every week. I'm trying to combine these two data tables so that I can analyze the impact of insects on yield.

 

I have this:

Capture.JPG

 

And I'm trying to produce this in JMP.


Capture2.JPG  

 

Note that for week 44 and 45 I simple move the Yield value over, but for week 47 I need to sum Yield from weeks 46 and 47. The same for week 49. For week 52 I sum the yield from weeks 50-52.

 

I have many plots and uneven weeks of insect and yield sampling.

 

Thank you for any help.

1 REPLY 1
Highlighted
txnelson
Super User

Re: Combine tables with missing rows and apply formula

Here is one method to get what you want

names default to here(1);
dtInsects = New Table( "Insects",
	Add Rows( 10 ),
	New Column( "Week",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [39, 40, 41, 42, 43, 44, 45, 47, 49, 52] )
	),
	New Column( "Plot#",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values(
			[59.01, 59.01, 59.01, 59.01, 59.01, 59.01, 59.01, 59.01, 59.01, 59.01]
		)
	),
	New Column( "Insects",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [0, 0.2, 0.2, 3.6, 15.4, 2.6, 0.3, 0.3, 0.3, 0.7] )
	)
);
dtYields = New Table( "Yields",
	Add Rows( 9 ),
	New Column( "Week",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [44, 45, 46, 47, 48, 49, 50, 51, 52] )
	),
	New Column( "Plot#",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values(
			[59.01, 59.01, 59.01, 59.01, 59.01, 59.01, 59.01, 59.01, 59.01]
		)
	),
	New Column( "Yield",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values(
			[130.96, 343.1, 466.2, 903.59, 2611.25, 1052.88, 1335.74, 1414.32,
			500.25]
		)
	)
);

// Join the data
dtTogether = dtInsects << Join(
	With( dtYields ),
	Merge Same Name Columns,
	By Matching Columns( :Week = :Week, :Plot# = :Plot# ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 1, 1 ),
	Preserve main table order( 0 ),
	Output Table( "All Together" )
);

dtTogether << New Column( "Calculated Yield",
	formula(
		rowNum = Row() - 1;
		cumSum = :Yield;
		If( Is Missing( :Yield ) == 0,
			While( :Match Flag[rowNum] == 2,
				cumSum = cumSum + :Yield[rowNum];
				rowNum--;
			)
		);
		cumSum;
	)
);

// Make formula columns static values
dtTogether:Calculated Yield << delete formula;

// Delete rows that only came from the With Data Table
dtTogether << select where( :Match Flag == 2 ) << delete rows;

// Delete the Match Flag column
dtTogether << delete columns("Match Flag");
Jim
Article Labels

    There are no labels assigned to this post.