Combine tables with missing rows and apply formula

Jimmy84

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:

 

And I'm trying to produce this in JMP.

  

 

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.

JMP Help.xlsx

txnelson

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