topic Re: Find MaxN value (species count) per ID for overlapping time range. in Discussions

Find MaxN value (species count) per ID for overlapping time range.

MRM — Fri, 09 Jun 2023 00:49:20 GMT

Hi,

I am trying to find the maximum number of individuals observed in a single video frame.

I have roughly 3000 2min videos with 5000 observations recorded.

For a given ID, what is the greatest value of the sum of Count for a species (eg. DuCr & ShPe) when the same species has overlapping time_start and time_end

example data:

ID	Species	Count	time_start	time_end
video_1	DuCr	1	12:00	12:01
video_1	ShPe	1	12:00	12:01
video_1	Shpe	1	12:05	12:06
video_2	DuCr	1	12:00	12:01
video_2	ShPe	1	12:00	12:01
video_2	Shpe	1	12:00	12:01

The output should look like this:

ID	DuCr	ShPe
video_1	1	1
video_2	1	2

This is the most related discussion post I could find:

https://community.jmp.com/t5/Discussions/Finding-the-max-value-as-per-ID/m-p/52616#M29792

Re: Find MaxN value (species count) per ID for overlapping time range.

jthi — Fri, 13 May 2022 06:57:56 GMT

This post might give some ideas .

One question, if time period is 12:00 - 12:01 and other one is 12:01 - 12:02 is that an overlap or not?

Re: Find MaxN value (species count) per ID for overlapping time range.

MRM — Mon, 16 May 2022 17:12:37 GMT

Yes, the 12:01 would be the overlapping second.

Im just looking at the highest count for 1 second.

Re: Find MaxN value (species count) per ID for overlapping time range.

jthi — Mon, 16 May 2022 19:38:19 GMT

This solution might be over-complicated (hopefully it is at least correct). It is based on pairings created by ID and Species and "distances" between different times (distance matrix can get quite big which can slow down the calculations)

Names Default To Here(1);

dt = New Table("Untitled 37",
	Add Rows(8),
	New Column("ID",
		Character,
		"Nominal",
		Set Values({"video_1", "video_1", "video_1", "video_2", "video_2", "video_2", "video_2", "video_2"})
	),
	New Column("Species", Character(16), "Nominal", Set Values({"DuCr", "ShPe", "ShPe", "DuCr", "ShPe", "ShPe", "ShPe", "ShPe"})),
	New Column("Count", Numeric, "Continuous", Format("Best", 12), Set Values([1, 1, 1, 1, 1, 1, 1, 1])),
	New Column("time_start",
		Numeric,
		"Continuous",
		Format("hr:m", 12),
		Input Format("hr:m"),
		Set Values([43200, 43200, 43500, 43200, 43200, 43200, 43260, 43320])
	),
	New Column("time_end",
		Numeric,
		"Continuous",
		Format("hr:m", 12),
		Input Format("hr:m"),
		Set Values([43260, 43260, 43560, 43260, 43260, 43260, 43320, 43380])
	),
	New Column("Correct", Numeric, "Continuous", Format("Best", 12), Set Values([1, 1, 1, 1, 3, 3, 4, 2]))
);

// dt:time_start << Data Type(Numeric, Format("hr:m", 30), Input Format("hr:m")) << Set Modeling Type("Continuous") << Set Field Width(12);
// dt:time_end << Data Type(Numeric, Format("hr:m", 30), Input Format("hr:m")) << Set Modeling Type("Continuous") << Set Field Width(12);

// Create Pair column with ID and Species
dt << New Column("Pairs", Character, Nominal, <<Set Each Value(:ID || :Species));
// Create matrix of time_start and time_end columns
time_m = dt[0, {"time_start", "time_end"}];
// Calculate distances between all rows
D = Distance(time_m, time_m);
// Get unique pairs
Summarize(dt, uniq_pairs = by(:Pairs));
// Create result column
dt << New Column("Overlap_count", Numeric, Continuous);

For Each Row(
	dt, // loop over each row
	pair_rows = Loc(dt[0, "Pairs"], :Pairs); // get pairs for current row
	nearest_rows = pair_rows[Loc(D[Row(), pair_rows] <= 7200)]; // get rows which are close enough
	counts = Sum(dt[nearest_rows, "Count"]); // calculate sum of Count
	dt[Row(), "Overlap_count"] = counts; // set value to Overlap_count column
);

dt_summary = dt << Summary(
	Group(:ID),
	Max(:Overlap_count),
	Subgroup(:Species),
	Freq("None"),
	Weight("None"),
	statistics column name format("column"),
	Link to original data table(0)
);