list of individuals at same place, same time

**erynne** · 06-11-2011, 01:23 PM

Hi everyone, I'm new to the forum and far from an Excel expert (but am enthusiastically learning

.
I'm working on an analysis of the polar bear studbook and am trying to generate a list of “pairings”, which would include a male and a female, housed at the same institution (location) at the same time.
My columns are : (A) ID, (B) ***, (C) DOB, (D) Event, (E) Location, (F) Date, (G) Age at Event

Events include births, transfers, and deaths (total of 3545). To complicate the analysis, I only want to include individuals of reproductive age (4 yrs or greater) and an individual may be part of more than one pairing at a given timepoint (for example if 1 male is housed with 2 females).

I would like my output to list the pairing (M-F IDs), location, and duration.

As an example, I attached a simplified spreadsheet (data from the 1980s only). I hope I'm not in over my head and would greatly value all suggestions and advice!

**shg** · 06-11-2011, 03:46 PM

It seems like there are a lot of assumptions to be made.

The only record of bear 169 is death at Racine. Would one assume that she spent her whole life there?

What is Go LTF? Is it different in principal from Transfer?

If you have a wild capture, how do you know the DOB? Is that just a guess?

I think I'd approach it it in several steps (in VBA; I couldn't do this with formulas):

Sort by Stud and make a Bear dictionary with stud# as the Key and Gender/DOB as the Item.

Create a dictionary for each location. Sorting the list by date, and for each event (date), add (or edit) a dictionary entry in one or more dictionaries with the date as the key and the item as the list of bears present on that date.

Walk the dictionary keys, find the bears present, filter by DOB (from the bear records), and spit out the event duration.

Or maybe start with a list of bears and their locations over time ...

It would some head scrtaching and a while to code.

**erynne** · 06-11-2011, 04:07 PM

Thanks for your reply!
The complete dataset *should* contain all births/ transfers/ deaths for each individual (I just pasted events listed in the 80s into the example set).
Go LTF is Gone or Lost To Followup, usually indicating an international transfer.
Most of the "wild captures" are actually orphaned cubs, so although their DOB/age is an approximation, it's probably pretty close.
This might be the kick in the butt I need to learn some programming!

**shg** · 06-14-2011, 10:39 AM

If you post the whole dataset, I'll look at it when I have time.

**erynne** · 06-14-2011, 01:47 PM

Here're all the events pertaining to the bears in my dataset. To give you some background (if you're interested) I'm analyzing variables that may affect the reproductive parameters (age, litter size, latitude, survival, ....) of PBs born in captivity (n= 702). I found that the age difference of male-female pairs that successfully reproduce is very low (~1.4 yrs), considering that the age range of reproductively sound bears in captivity is 4- 30. I'd like to run a t-test to compare "successful pairings" (which I have listed based on the parents of the bears in my dataset) vs. pairings that do not result in cubs to determine what's worked in the past.

I sincerely appreciate your help and will acknowledge your assistance in the final manuscript, which I hope to have published by the end of the year.

**shg** · 06-14-2011, 07:24 PM

What's the last column mean?

I'm not promising anything, but will post back eventually.

EDIT: Also, what should the output look like?

**erynne** · 06-15-2011, 03:46 PM

The last column tells whether the date is estimated. None= not an estimate; month= the month is estimated, etc.
Ideally, I would like the output to list the pairings: (A) male's and (B) female's IDs, the (C) location, (D) the start date, and (E) the end dates of the pairing.
Thanks again. I'll let you know if I figure out a solution in the meantime.