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

[#1]

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

[#2]

This post might give some ideasOverlap / Count of periods at the same point of time .

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

[#3]

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

Im just looking at the highest count for 1 second. 

[#4]

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