67534_en-US

liliconcom

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:

 

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.

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