The most general views are the SOCIAL_EVENTS_W_BABOONS and the ASOCIAL_EVENTS views. Taken together these 2 views return (1 or more) rows for all behavioral events. They are suitable for use in arbitrary queries and are probably the best choice in most cases. For the most part, the SOCIAL_EVENTS view should be used in place of the SOCIAL_EVENTS_W_BABOONS view since it does not contain rows for social interactions with baboons.
The ASOCIAL_EVENTS, SOCIAL_EVENTS, and SOCIAL_EVENTS_W_BABOONS views, have columns for each animal involved to encode the following properties: the animal's chimpid, the animal's role in the follow (mom, infant, sib, nonfocal), and the animal's role (part) in the behavioral event (self, actor, actee, participant). When using the ASOCIAL_EVENTS, SOCIAL_EVENTS, and SOCIAL_EVENTS_W_BABOONS views these 3 columns must often be tested in tandem to obtain the desired results. The other views in this section also often have these columns, or a subset of them, but are designed to reduce the need to test all 3 of these values together.
The ACTOR_ACTEES_W_BABOONS and ACTOR_ACTEES views can be simpler to use than the SOCIAL_EVENTS_W_BABOONS and SOCIAL_EVENTS views when interested in directed behaviors. The ACTOR_ACTEES_W_BABOONS and ACTOR_ACTEES views contain at most a single row per behavioral event and classify the individuals involved into separate columns for actor and actee.
The FOCAL_SOCIAL_EVENTS and FOCAL_EVENTS views classify the involved individuals based on whether or not they are focals in the follow; there is a set of “focal” columns to describe (one of) the focals involved in the interaction and a set of “other” columns to describe the 2nd individual involved in the interaction. This may be less useful than it first appears. Because many interactions are between focals a focal often shows up in the “other” columns and those interactions that are between focals have two rows in these views. There are 2 rows per event so that each focal can show up once in the “focal” category.
The FOCAL_EVENTS view is different in that it contains at least one row for every recorded behavioral interaction.
The FAMILY_EVENTS view contains exactly one row for each recorded behavioral interaction, with columns structured in a fashion somewhat like the way behavioral data is recorded. This is helpful when checking data in the system against the data as it comes from the field. The family_events view is also useful for changing database content, an operation usually based on data recorded in the field. But the correspondence between the columns of the FAMILY_EVENTS view and the paper records is likely to be less useful than it seems when it comes to analysis because queries must be written with foreknowledge of who plays what function (mom, infant, sib) in the follow rather than relying on the system to inform. The FAMILY_EVENTS view cannot distinguish between twins and singleton offspring whereas the other views can. [62] Further, the FAMILY_EVENTS view is by far the least efficient in its execution. Data analysis will probably be better served by spending the time to learn to use the other views.
All these views have slightly different implementations and some may perform better than others depending on the circumstances of use.
Note
While it is certainly possible to create more specialized behavioral views than those described here to encompass all the relevant combinatorial parings of mom, infant, sib, and nonfocal, as well as the orthogonal categories of directed and non-directed social interactions and asocial interactions it is hoped that enough skill writing queries in SQL will be developed that the existing views will suffice. One useful SQL technique can be found in the Queries section above.
ACTOR_ACTEES contains one row per EVENTS row where there are 2 chimpanzees involved in an actor/actee behavioral even. Events involving baboons are excluded. ACTOR_ACTEES provides a straightforward way to analyze social behaviors from the perspective of who is initiating and who receiving the behavior.
Figure 5.31. Query Defining the ACTOR_ACTEES View
SELECT events.eid AS eid
, events.intid AS intid
, actors.pid AS actorpid
, actees.pid AS acteepid
, events.behavior AS behavior
, events.position AS position
, events.uncertain AS uncertain
, followrole(intervals.followid, actors.participant) AS actorrole
, actors.participant AS actor
, followrole(intervals.followid, actees.participant) AS acteerole
, actees.participant AS actee
, events.comment AS comment
FROM events
JOIN intervals
ON (intervals.intid = events.intid)
JOIN parts AS actors
ON (actors.eid = events.eid)
JOIN parts AS actees
ON (actees.eid = events.eid)
WHERE actors.part = 'gmi_actor'
AND actees.part = 'gmi_actee';
| Column | From | Description |
|---|---|---|
| EID | EVENTS.EID | Identifier of the behavioral event row. |
| IntID | EVENTS.IntID | Identifier of the observation interval during which the event was recorded. |
| ActorPID | PARTS.PID | Identifier of the behavioral participant row which records behavioral data regarding the chimpanzee who is the actor in the behavior. |
| ActeePID | PARTS.PID | Identifier of the behavioral participant row which records behavioral data regarding the chimpanzee who is the actee in the behavior. |
| Behavior | EVENTS.Behavior | Code indicating the behavior observed. |
| Position | EVENTS.Position | Position within the canopy where the event occurred. |
| Uncertain | EVENTS.Uncertain | Whether or not the behavior was observed with certainty. |
| ActorRole (Role in Follow of Actor) | FOLLOWPARTS.Role | Code for the role, i.e. mom, sib, infant, played by the animal who is the actor in the behavior. |
| Actor | PARTS.Participant | The identifier or alternate identifier of the actor. |
| ActeeRole (Role in Follow of Actee) | FOLLOWPARTS.Role | Code for the role, i.e. mom, sib, infant, played by the chimpanzee who is the actee in the behavior. |
| Actee | PARTS.Participant | The identifier or alternate identifier of the actee. |
| Comment | EVENTS.Comment | Textual remarks regarding the behavioral event. |
ACTOR_ACTEES_W_BABOONS contains one row per EVENTS row where there are 2 animals involved in an actor/actee behavioral event, including events involving baboons. ACTOR_ACTEES_W_BABOONS provides a straightforward way to analyze social behaviors from the perspective of who is initiating and who receiving the behavior.
Figure 5.33. Query Defining the ACTOR_ACTEES_W_BABOONS View: Part I
SELECT events.eid AS eid
, events.intid AS intid
, actors.pid AS actorpid
, actees.pid AS acteepid
, events.behavior AS behavior
, events.position AS position
, events.uncertain AS uncertain
, followrole(intervals.followid, actors.participant) AS actorrole
, FALSE AS baboonactor
, actors.participant AS actor
, followrole(intervals.followid, actees.participant) AS acteerole
, FALSE AS baboonactee
, actees.participant AS actee
, events.comment AS comment
FROM events
JOIN intervals
ON (intervals.intid = events.intid)
JOIN parts AS actors
ON (actors.eid = events.eid)
JOIN parts AS actees
ON (actees.eid = events.eid)
WHERE actors.part = 'gmi_actor'
AND actees.part = 'gmi_actee'
UNION
SELECT events.eid AS eid
, events.intid AS intid
, actors.pid AS actorpid
, NULL AS acteepid
, events.behavior AS behavior
, events.position AS position
, events.uncertain AS uncertain
, followrole(intervals.followid, actors.participant) AS actorrole
, FALSE AS baboonactor
, actors.participant AS actor
, 'NF' AS acteerole
, TRUE AS baboonactee
, NULL::VARCHAR(gmi_animid_len) AS actee
, events.comment AS comment
FROM events
JOIN intervals
ON (intervals.intid = events.intid)
JOIN parts AS actors
ON (actors.eid = events.eid)
WHERE actors.part = 'gmi_actor'
AND events.baboon
Figure 5.34. Query Defining the ACTOR_ACTEES_W_BABOONS View: Part II
UNION
SELECT events.eid AS eid
, events.intid AS intid
, NULL AS actorpid
, actees.pid AS acteepid
, events.behavior AS behavior
, events.position AS position
, events.uncertain AS uncertain
, 'NF' AS actorrole
, TRUE AS baboonactor
, NULL::VARCHAR(gmi_animid_len) AS actor
, followrole(intervals.followid, actees.participant) AS acteerole
, FALSE AS baboonactee
, actees.participant AS actee
, events.comment AS comment
FROM events
JOIN intervals
ON (intervals.intid = events.intid)
JOIN parts AS actees
ON (actees.eid = events.eid)
WHERE actees.part = 'gmi_actee'
AND events.baboon;
| Column | From | Description |
|---|---|---|
| EID | EVENTS.EID | Identifier of the behavioral event row. |
| IntID | EVENTS.IntID | Identifier of the observation interval during which the event was recorded. |
| ActorPID | PARTS.PID | Identifier of the behavioral participant row which records behavioral data regarding the animal who is the actor in the behavior. |
| ActeePID | PARTS.PID | Identifier of the behavioral participant row which records behavioral data regarding the animal who is the actee in the behavior. |
| Behavior | EVENTS.Behavior | Code indicating the behavior observed. |
| Position | EVENTS.Position | Position within the canopy where the event occurred. |
| Uncertain | EVENTS.Uncertain | Whether or not the behavior was observed with certainty. |
| ActorRole (Role in Follow of Actor) | FOLLOWPARTS.Role or NF
in the case of baboons or nonfocal chimpanzees |
Code for the role, i.e. mom, sib, infant, played by the animal who is the actor in the behavior. |
| BaboonActor | Computed | A Boolean value, true when the actor is a baboon. |
| Actor | PARTS.Participant | The identifier or alternate identifier of the
actor, or NULL if the actor is a baboon. |
| ActeeRole (Role in Follow of Actee) | FOLLOWPARTS.Role or NF
in the case of baboons or nonfocal chimpanzees |
Code for the role, i.e. mom, sib, infant, played by the animal who is the actee in the behavior. |
| BaboonActee | Computed | A Boolean value, true when the actee is a baboon. |
| Actee | PARTS.Participant | The identifier or alternate identifier of the
actee, or NULL if the actee is a baboon. |
| Comment | EVENTS.Comment | Textual remarks regarding the behavioral event. |
ASOCIAL_EVENTS contains one row per EVENTS
row per when there is a related PARTS row having
a Part value
of S. ASOCIAL_EVENTS provides a
direct way to analyze non-social behaviors, those behaviors
involving a single chimp and no other animal.
Figure 5.36. Query Defining the ASOCIAL_EVENTS View
SELECT events.eid AS eid
, events.intid AS intid
, parts.pid AS pid
, events.behavior AS behavior
, events.position AS position
, events.uncertain AS uncertain
, followparts.role AS role
, parts.participant AS participant
, events.comment AS comment
FROM events
JOIN intervals
ON (intervals.intid = events.intid)
JOIN parts
ON (parts.eid = events.eid)
JOIN followparts
ON (followparts.followid = intervals.followid)
JOIN chimpids AS p_ids
ON (p_ids.chimpid = parts.participant)
JOIN chimpids AS fp_ids
ON (fp_ids.chimpid = followparts.chimpid)
WHERE parts.part = 'gmi_self'
AND fp_ids.animid = p_ids.animid;
| Column | From | Description |
|---|---|---|
| EID | EVENTS.EID | Identifier of the behavioral event row. |
| IntID | EVENTS.IntID | Identifier of the observation interval during which the event was recorded. |
| PID | PARTS.PID | Identifier of the behavioral participant row which records behavioral data regarding the individual exhibiting the behavior. |
| Behavior | EVENTS.Behavior | Code indicating the behavior observed. |
| Position | EVENTS.Position | Position within the canopy where the event occurred. |
| Uncertain | EVENTS.Uncertain | Whether or not the behavior was observed with certainty. |
| Role (Role in Follow of Chimp) | FOLLOWPARTS.Role | Code for the role, i.e. mom, sib, infant, played by the individual involved in the behavioral event. |
| Participant | PARTS.Participant | The identifier or alternate identifier of the individual exhibiting the behavior. |
| Comment | EVENTS.Comment | Textual remarks regarding the behavioral event. |
An event bout is defined as a contiguous series of minutes during which a focal is involved in a behavior. However, behavioral events involving baboons are excluded when considering contiguity. Behaviors are distinguished by the codes used to represent them. An individual can be involved in more than one event bout at a time, if involved in more than one behavior at a time.
EVENT_BOUTS contains one row per FOLLOWPARTS row per INTERVALS row, per EVENTS.Behavior value having a related PARTS row that shows the given focal involved in the given behavior -- one row per follow, per focal, per interval in which the focal is involved in a behavior (excluding behavioral events which involve a baboon), per code for the behavior involved in. The number of rows returned is not affected by the number of times the focal is involved in the behavior during any given interval, nor is it affected by the behavioral role the focal plays regards the behavior. There is a row per focal per behavior the focal is involved in per interval, excluding cases where a baboon is involved.
Event bouts are numbered sequentially, in a time-wise order, per follow, per focal, per behavior, starting with 1.
Bouts are said to be censored, either on the left (earlier) or on the right (later) if the “edge” of the bout exists because there is a lack of behavioral data on the given focal. Censoring can occur because of “badobs”, because observation has not started or because observation finishes, or any other time there is no behavioral data what so ever on the focal. Note that censoring or lack thereof of any one focal is not affected by data or lack thereof on any other focal. Note also that observations involving baboons are not excluded when it comes to considering whether a bout is censored. Baboon related observations are like any other behavioral events involving the focal when it comes to considering whether behavioral data exists.
Likewise, the columns that indicate censoring due to the start of observation at the beginning of the day and end of observation at the end of the day are per-focal and not affected by observations that may or may not be made on the other focals.
Note that uncertain behavioral events are not distinguished from certain behavioral events in event bout analysis, although the EVENT_BOUTS view does provide some information regarding uncertainty.
Tip
Since the EVENT_BOUTS view can take a number of minutes to return results it may be desirable to put the results of interest into a table for repeated analysis. If this is done it is very likely a good idea to add indexes to most of the new table's columns. This will greatly improve performance.
Figure 5.38. Query Defining the EVENT_BOUTS View: Part I
-- Note that we don't worry about leap seconds given
-- the time values in the db.
WITH detail AS
-- Compute mins_in and mins_out
(SELECT bouts.followid AS followid
, bouts.intid AS intid
, bouts.function AS function
, bouts.animid AS animid
, bouts.role AS role
, bouts.chimpid AS chimpid
, bouts.bout AS bout
, bouts.behavior AS behavior
, bouts.uncertain AS uncertain
, bouts.time AS time
, (EXTRACT(EPOCH FROM bouts.time)
- EXTRACT(EPOCH FROM FIRST_VALUE(bouts.time) OVER bout_w)
) / 60
+ 1
AS mins_in
, (EXTRACT(EPOCH FROM LAST_VALUE(bouts.time)
OVER (bout_w RANGE BETWEEN CURRENT ROW
AND UNBOUNDED FOLLOWING))
- EXTRACT(EPOCH FROM bouts.time)
) / 60
AS mins_out
FROM
-- Compute bout number by summing transition ticks
(SELECT ticks.followid AS followid
, ticks.intid AS intid
, ticks.function AS function
, ticks.animid AS animid
, ticks.role AS role
, ticks.chimpid AS chimpid
, ticks.behavior AS behavior
, ticks.uncertain AS uncertain
, ticks.time AS time
, SUM(ticks.bouttick)
OVER (PARTITION BY ticks.followid
, ticks.behavior
, ticks.animid
ORDER BY ticks.time
, ticks.bouttick DESC)
AS bout
Figure 5.39. Query Defining the EVENT_BOUTS View: Part II
FROM
-- Distinguish one bout from another
(SELECT intervals.followid AS followid
, intervals.intid AS intid
, followroles.function AS function
, part_cids.animid AS animid
, followparts.role AS role
, followparts.chimpid AS chimpid
, events.behavior AS behavior
, BOOL_AND(events.uncertain) AS uncertain
, intervals.time AS time
, CASE
WHEN LAG(MIN(intervals.time)) OVER tick_w
= MIN(intervals.time) - '1 minute'::INTERVAL
THEN 0
ELSE
1
END
AS bouttick
FROM intervals
JOIN events
ON (events.intid = intervals.intid)
JOIN parts
ON (parts.eid = events.eid)
JOIN chimpids AS part_cids
ON (part_cids.chimpid = parts.participant)
JOIN chimpids AS fp_cids
ON (fp_cids.animid = part_cids.animid)
JOIN followparts
ON (followparts.followid = intervals.followid
AND followparts.chimpid = fp_cids.chimpid)
JOIN followroles
ON (followroles.role = followparts.role)
WHERE NOT(events.baboon)
GROUP BY intervals.followid
, intervals.intid
, part_cids.animid
, events.behavior
-- The values that are unique per above anyway
, followroles.function
, followparts.role
, followparts.chimpid
, intervals.time
WINDOW tick_w AS (PARTITION BY intervals.followid
, events.behavior
, part_cids.animid
ORDER BY intervals.time)
) AS ticks
) AS bouts
WINDOW bout_w AS (PARTITION BY bouts.followid
, bouts.behavior
, bouts.animid
, bouts.bout
ORDER BY bouts.time)
)
Figure 5.40. Query Defining the EVENT_BOUTS View: Part III
-- Distribute aggregated bout info over the bout's minutes
SELECT detail.*
, summary.prior_gap AS prior_gap
, summary.gap_noobs AS gap_noobs
, summary.left_censored AS left_censored
, summary.right_censored AS right_censored
, summary.day_start AS day_start
, summary.day_end AS day_end
FROM
detail
JOIN
(-- Aggregate per-bout information
SELECT bout_ends.followid AS followid
, bout_ends.animid AS animid
, bout_ends.behavior AS behavior
, bout_ends.bout AS bout
, CASE
WHEN bout_ends.bout = 1 THEN
NULL
ELSE
SUM(bout_ends.prior_gap)
END
AS prior_gap
, CASE
WHEN bout_ends.bout = 1 THEN
NULL
ELSE
SUM(bout_ends.prior_gap)
-- Subtract number of minutes the focal had
-- any behaviors during the gap.
- (SELECT COUNT(DISTINCT intervals.intid)
FROM intervals
JOIN events
ON (events.intid = intervals.intid)
JOIN parts
ON (parts.eid = events.eid)
JOIN chimpids
ON (chimpids.chimpid
= parts.participant)
WHERE intervals.followid = bout_ends.followid
AND MIN(bout_ends.time) > intervals.time
AND intervals.time
>= MIN(bout_ends.time)
- (SUM(bout_ends.prior_gap)
|| ' minutes')::INTERVAL
AND chimpids.animid = bout_ends.animid)
END
AS gap_noobs
, BOOL_OR(bout_ends.left_censored) AS left_censored
, BOOL_OR(bout_ends.right_censored) AS right_censored
Figure 5.41. Query Defining the EVENT_BOUTS View: Part IV
, CASE
WHEN bout_ends.bout = 1 THEN
NOT EXISTS
-- Behavioral data on the focal before the bout
(SELECT 1
FROM intervals
JOIN events
ON (events.intid = intervals.intid)
JOIN parts
ON (parts.eid = events.eid)
JOIN chimpids
ON (chimpids.chimpid
= parts.participant)
WHERE intervals.followid = bout_ends.followid
AND intervals.time < MIN(bout_ends.time)
AND chimpids.animid = bout_ends.animid
LIMIT 1)
ELSE
FALSE
END
AS day_start
, CASE
WHEN bout_ends.bout = LAST_VALUE(bout_ends.bout)
OVER (PARTITION BY
bout_ends.followid
, bout_ends.animid
, bout_ends.behavior
ORDER BY bout_ends.bout
RANGE BETWEEN CURRENT ROW
AND UNBOUNDED FOLLOWING)
THEN
NOT EXISTS
-- Behavioral data on the focal after the bout
(SELECT 1
FROM intervals
JOIN events
ON (events.intid = intervals.intid)
JOIN parts
ON (parts.eid = events.eid)
JOIN chimpids
ON (chimpids.chimpid
= parts.participant)
WHERE intervals.followid = bout_ends.followid
AND intervals.time
> MAX(bout_ends.time)
AND chimpids.animid = bout_ends.animid
LIMIT 1)
ELSE
FALSE
END
AS day_end
Figure 5.42. Query Defining the EVENT_BOUTS View: Part V
FROM
-- Examine the bout's endpoints to compute prior_gap, and
-- left and right censoring.
(SELECT detail.followid
, detail.animid
, detail.behavior
, detail.bout
, detail.mins_in AS mins_in
, detail.mins_out AS mins_out
, CASE
WHEN detail.mins_in = 1 THEN
(EXTRACT(EPOCH FROM detail.time)
- EXTRACT(EPOCH FROM
LAG(detail.time)
OVER (PARTITION BY
detail.followid
, detail.animid
, detail.behavior
ORDER BY detail.time))
) / 60 - 1
ELSE
0
END
AS prior_gap
, CASE
WHEN detail.mins_in = 1 THEN
NOT EXISTS
(SELECT 1
FROM intervals AS i
JOIN events AS e
ON (e.intid = i.intid)
JOIN parts AS p
ON (p.eid = e.eid)
JOIN chimpids AS p_cids
ON (p_cids.chimpid = p.participant)
WHERE i.followid = detail.followid
AND i.time = detail.time
- '1 minute'::INTERVAL
AND p_cids.animid = detail.animid
LIMIT 1)
ELSE
FALSE
END
AS left_censored
Figure 5.43. Query Defining the EVENT_BOUTS View: Part VI
, CASE
WHEN detail.mins_out = 0 THEN
NOT EXISTS
(SELECT 1
FROM intervals AS i
JOIN events AS e
ON (e.intid = i.intid)
JOIN parts AS p
ON (p.eid = e.eid)
JOIN chimpids AS p_cids
ON (p_cids.chimpid = p.participant)
WHERE i.followid = detail.followid
AND i.time = detail.time
+ '1 minute'::INTERVAL
AND p_cids.animid = detail.animid
LIMIT 1)
ELSE
FALSE
END
AS right_censored
, detail.time AS time
FROM detail
WHERE detail.mins_in = 1
OR detail.mins_out = 0
) AS bout_ends
GROUP BY bout_ends.followid
, bout_ends.animid
, bout_ends.behavior
, bout_ends.bout)
AS summary
ON (summary.followid = detail.followid
AND summary.animid = detail.animid
AND summary.behavior = detail.behavior
AND summary.bout = detail.bout);
| Column | From | Description |
|---|---|---|
| FollowID | FOLLOWPARTS.FollowID | Identifier of the follow. |
| IntID | INTERVALS.IntID | Identifier of the observation interval comprising one of the bout's minutes. |
| Function (Function in Follow of Focal) | FOLLOWROLES.Function | Code indicating the biological role the focal plays in the follow: Mother, Infant, or Sibling. |
| AnimID | CHIMPIDS.AnimID | Canonical identifier of the focal, the BIOGRAPHY_DATA.AnimID value. This can differ from the ChimpID in the case of twins or other circumstances where individuals have multiple identifiers. |
| Role (Role in Follow of Focal) | FOLLOWPARTS.Role | Code indicating the family role the focal plays in the follow. This can differ from the Function in the case of twins. |
| ChimpID | FOLLOWPARTS.ChimpID | Identifier of the focal as recorded in the FOLLOWROLES row for the follow. This can differ from the focal's AnimID in the case of twins or other circumstances where individuals have multiple identifiers. In such cases this may also differ from the ChimpID stored in the PARTS.Participant rows which designate the focal as being involved in a behavioral event. |
| Bout | Computed | Integer indicating the bout number. Bout numbers start with 1 and are per follow, per focal, per behavior code. |
| Behavior | EVENTS.Behavior | Code indicating the behavior in which the focal is involved. |
| Uncertain | EVENTS.Uncertain | TRUE if all of the (non-baboon related)
behavioral events in which the focal is involved during
the given minute for the given behavior are uncertain,
FALSE otherwise. |
| Time | INTERVALS.Time | The time of the observation. |
| Mins_In (Minutes Into the bout) | Computed | Number of minutes into the current bout, including the current minute.[a] |
| Mins_Out (Minutes remaining in the bout) | Computed | Number of minutes remaining in the current bout, excluding the current minute.[b] |
| Prior_Gap (Minutes between the current and Prior bout) | Computed | The number of minutes between the current bout
and the previous bout (of the same follow, focal,
behavior). NULL if there is no prior bout. |
| Gap_NoObs (Minutes without Observation of the focal between the current and prior bout) | Computed | The number of minutes between the current bout
and the previous bout (of the same follow, focal,
behavior) during which there is no behavioral data what
so ever on the focal. NULL if there is no prior
bout. |
| Left_Censored | Computed. | TRUE when the bout is left censored, FALSE
otherwise. See above for more information regards
censoring. |
| Right_Censored | Computed. | TRUE when the bout is right censored, FALSE
otherwise. See above for more information regards
censoring. |
| Day_Start (Bout is censored due to Start of Day) | Computed | TRUE when the bout is censored because it
occurs at the beginning of the day's observation of the
follow, FALSE otherwise. |
| Day_End (Bout is censored due to End of Day) | Computed | TRUE when the bout is censored because it
occurs at the end of the day's observation of the
follow, FALSE otherwise. |
EVENT_BOUT_AGGS contains one row per FOLLOWPARTS row per EVENTS.Behavior value having a related PARTS row that shows the given focal involved in the given behavior -- one row per follow, per focal in which the focal is involved in a behavior (excluding behavioral events which involve a baboon), per code for the behavior involved in. Each row represents an entire event bout.
See EVENT_BOUTS for more information on event bout analysis.
Figure 5.45. Query Defining the EVENT_BOUT_AGGS View
SELECT event_bouts.followid AS followid
, event_bouts.function AS function
, event_bouts.animid AS animid
, event_bouts.role AS role
, event_bouts.chimpid AS chimpid
, event_bouts.bout AS bout
, event_bouts.behavior AS behavior
, BOOL_OR(event_bouts.uncertain) AS uncertain
, MIN(event_bouts.time) AS start_time
, MAX(event_bouts.time) AS stop_time
, event_bouts.prior_gap AS prior_gap
, event_bouts.gap_noobs AS gap_noobs
, event_bouts.left_censored AS left_censored
, event_bouts.right_censored AS right_censored
, event_bouts.day_start AS day_start
, event_bouts.day_end AS day_end
FROM event_bouts
GROUP BY event_bouts.followid
, event_bouts.animid
, event_bouts.bout
, event_bouts.behavior
-- The values that are unique per above anyway
, event_bouts.function
, event_bouts.role
, event_bouts.chimpid
, event_bouts.prior_gap
, event_bouts.gap_noobs
, event_bouts.left_censored
, event_bouts.right_censored
, event_bouts.day_start
, event_bouts.day_end;
Figure 5.46. Entity Relationship Diagram of the EVENT_BOUT_AGGS View
| Column | From | Description |
|---|---|---|
| FollowID | FOLLOWPARTS.FollowID | Identifier of the follow. |
| Function (Function in Follow of Focal) | FOLLOWROLES.Function | Code indicating the biological role the focal plays in the follow: Mother, Infant, or Sibling. |
| AnimID | CHIMPIDS.AnimID | Canonical identifier of the focal, the BIOGRAPHY_DATA.AnimID value. This can differ from the ChimpID in the case of twins or other circumstances where individuals have multiple identifiers. |
| Role (Role in Follow of Focal) | FOLLOWPARTS.Role | Code indicating the family role the focal plays in the follow. This can differ from the Function in the case of twins. |
| ChimpID | FOLLOWPARTS.ChimpID | Identifier of the focal as recorded in the FOLLOWROLES row for the follow. This can differ from the focal's AnimID in the case of twins or other circumstances where individuals have multiple identifiers. In such cases this may also differ from the ChimpID stored in the PARTS.Participant rows which designate the focal as being involved in a behavioral event. |
| Bout | Computed | Integer indicating the bout number. Bout numbers start with 1 and are per follow, per focal, per behavior code. |
| Behavior | EVENTS.Behavior | Code indicating the behavior in which the focal is involved. |
| Uncertain | EVENTS.Uncertain | TRUE if any of the underlying
EVENT_BOUTS rows which comprise the
bout are uncertain, FALSE otherwise. |
| Start_Time | INTERVALS.Time | The time the event bout started. The time of the first observation of the bout. |
| Stop_Time | INTERVALS.Time | The time the event bout stopped. The time of the last observation of the bout. |
| Prior_Gap (Minutes between the current and Prior bout) | Computed | The number of minutes between the current bout
and the previous bout (of the same follow, focal,
behavior). NULL if there is no prior bout. |
| Gap_NoObs (Minutes without Observation of the focal between the current and prior bout) | Computed | The number of minutes between the current bout
and the previous bout (of the same follow, focal,
behavior) during which there is no behavioral data what
so ever on the focal. NULL if there is no prior
bout. |
| Left_Censored | Computed. | TRUE when the bout is left censored, FALSE
otherwise. See above for more information regards
censoring. |
| Right_Censored | Computed. | TRUE when the bout is right censored, FALSE
otherwise. See above for more information regards
censoring. |
| Day_Start (Bout is censored due to Start of Day) | Computed | TRUE when the bout is censored because it
occurs at the beginning of the day's observation of the
follow, FALSE otherwise. |
| Day_End (Bout is censored due to End of Day) | Computed | TRUE when the bout is censored because it
occurs at the end of the day's observation of the
follow, FALSE otherwise. |
FAMILY_EVENTS contains one row per EVENTS row. FAMILY_EVENTS provides a way to review recorded behaviors in a way similar to the way data is recorded in the field. It is also useful for changing database content, since this is often done based on data recorded in the field.
Note
The FAMILY_EVENTS view is neither particularly efficient nor particularly well suited to data analysis. Other views will very likely be much faster and more appropriate when analyzing data.
Note
The FAMILY_EVENTS view is limited in that it will not change database content in the tables which extend the EVENTS table, tables such as VOCS, EATS, and the like. For this the DATASHEETS view must be used.
Figure 5.47. Query Defining the FAMILY_EVENTS View: Part I
SELECT focal_follows.followid AS followid
, intervals.intid AS intid
, events.eid AS eid
, moms.pid AS e_mompid
, infants.pid AS e_infantpid
, sibs.pid AS e_sibpid
, nfs.pid AS e_nonfocalpid
, focal_follows.date AS date
, intervals.time AS time
, focal_follows.mom AS mom
, focal_follows.infant AS infant
, focal_follows.infant2 AS infant2
, focal_follows.sib AS sib
, focal_follows.sib2 AS sib2
, events.behavior AS behavior
, events.position AS position
, events.uncertain AS uncertain
, moms.participant AS e_mom
, moms.part AS e_mompart
, infants.participant AS e_infant
, infants.part AS e_infantpart
, sibs.participant AS e_sib
, sibs.part AS e_sibpart
, nfs.participant AS e_nonfocal
, nfs.part AS e_nonfocalpart
, events.baboon AS e_baboon
, CASE
WHEN events.baboon THEN
CASE
WHEN moms.part = 'gmi_actor'
OR infants.part = 'gmi_actor'
OR sibs.part = 'gmi_actor' THEN
'gmi_actee'
WHEN moms.part = 'gmi_actee'
OR infants.part = 'gmi_actee'
OR sibs.part = 'gmi_actee' THEN
'gmi_actor'
ELSE
'gmi_participant'
END
ELSE
NULL
END::parts_part AS e_baboonpart
, events.comment AS e_comment
, intervals.comment AS i_comment
Figure 5.48. Query Defining the FAMILY_EVENTS View: Part II
FROM focal_follows
JOIN intervals
ON (intervals.followid = focal_follows.followid)
JOIN events
ON (events.intid = intervals.intid)
LEFT OUTER JOIN parts AS moms
ON (moms.eid = events.eid
AND followfunc(intervals.followid
, moms.participant)
= 'gmi_mom')
LEFT OUTER JOIN parts AS infants
ON (infants.eid = events.eid
AND followfunc(intervals.followid
, infants.participant)
= 'gmi_infant')
LEFT OUTER JOIN parts AS sibs
ON (sibs.eid = events.eid
AND followfunc(intervals.followid
, sibs.participant)
= 'gmi_sib')
LEFT OUTER JOIN parts AS nfs
ON (nfs.eid = events.eid
AND followfunc(intervals.followid
, nfs.participant)
= 'gmi_nonfocal');
| Column | From | Description |
|---|---|---|
| FollowID | FOLLOWS.FollowID | Identifier of the follow. |
| IntID | EVENTS.IntID | Identifier of the observation interval during which the event was recorded. |
| EID | EVENTS.EID | Identifier of the behavioral event row. |
| MomPID (Event Mom PID) | PARTS.PID | Identifier of the behavioral participant row
which records behavioral data regarding the mother in
the follow, or NULL when the mother is not a
participant in the behavior. |
| E_InfantPID (Event Infant PID) | PARTS.PID | Identifier of the behavioral participant row
which records behavioral data regarding the infant in
the follow, or NULL when the infant is not a
participant in the behavior. |
| E_SibPID (Event Sib PID) | PARTS.PID | Identifier of the behavioral participant row
which records behavioral data regarding the sibling in
the follow, or NULL when the sibling is not a
participant in the behavior. |
| E_NonfocalPID (Event Non-Focal PID) | PARTS.PID |
Identifier of the behavioral participant row
which records behavioral data regarding any nonfocal
individual involved in the behavior, or |
| Date | FOLLOWS.Date | The date of the follow. |
| Time | INTERVALS.Time | The time of the interval. |
| Mom | FOLLOWPARTS.ChimpID | The ChimpID of the mother in the follow. |
| Infant | FOLLOWPARTS.ChimpID | The ChimpID of an infant in the follow. |
| Infant2 | FOLLOWPARTS.ChimpID | The ChimpID of a
second infant in the follow, or NULL if the infant
is not a twin. |
| Sib | FOLLOWPARTS.ChimpID | The ChimpID of a
sibling in the follow, or NULL if there is no
sibling focal. |
| Sib2 | FOLLOWPARTS.ChimpID | The ChimpID of a
second sibling in the follow, or NULL if the sibling
is not a twin. |
| Behavior | EVENTS.Behavior | Code indicating the behavior observed. |
| Position | EVENTS.Position | Position within the canopy where the event occurred. |
| Uncertain | EVENTS.Uncertain | Whether or not the behavior was observed with certainty. |
| E_Mom (Event Mom) | PARTS.Participant | The identifier or alternate identifier of the mom,
or NULL if the mother is not a participant in the
behavior. |
| E_MomPart (Event Mom Participant code) | PARTS.Part | The role the mom plays in the behavior, or
NULL if the mother is not a participant in the
behavior. |
| E_Infant (Event Infant) | PARTS.Participant | The identifier or alternate identifier of the
infant, or NULL when the infant is not a participant
in the behavior. |
| E_InfantPart (Event Infant Participant code) | PARTS.Part | The role the infant plays in the behavior, or
NULL when the infant is not a participant in the
behavior. |
| E_Sib (Events Sibling) | PARTS.Participant | The identifier or alternate identifier of the
sibling, or NULL when the sibling is not a
participant in the behavior. |
| E_SibPart (Event Sibling Participant code) | PARTS.Part | The role the sibling plays in the behavior, or
NULL when the sibling is not a participant in the
behavior. |
| E_Nonfocal | PARTS.Participant |
The identifier or alternate identifier of any
nonfocal individual involved in the behavior, or
|
| E_NonfocalPart (Event Non-Focal Participant code) | PARTS.Part |
The role a nonfocal individual plays in the
behavior, or |
| E_Baboon (Event Baboon) | EVENTS.Baboon | Whether or not a baboon is the second participant in the behavioral interaction. |
| E_BaboonPart (Event Baboon Participant code) | A simulated PARTS.Part value |
The role the baboon plays in the behavior, or
|
| E_Comment (Event Comment) | EVENTS.Comment | Textual remarks regarding the behavioral event. |
| I_Comment (Interval Comment) | INTERVALS.Comment | Textual remarks regarding the interval. |
Altering the FAMILY_EVENTS view alters the content of the underlying EVENTS and PARTS tables in the fashion described below. The INTERVALS and related FOLLOWS and FOLLOWPARTS rows must exist; the content of these tables is never altered.
Inserting a row into the FAMILY_EVENTS view inserts a row into the EVENTS table, as expected.
When inserting into FAMILY_EVENTS the interval of the follow must be specified. The interval may be specified in either or both of the following 2 ways:
-
A IntID may be supplied.
-
A Time may be supplied and the follow specified. The follow may be specified in either or both of the following 2 ways:
-
A FollowID may be supplied.
-
Date, Mom and, Infant values all may be supplied.
Note
See the note regards twins when inserting into FOLLOW_INTERVALS.
-
Caution
FAMILY_EVENTS inserts rows into the database only when there is a INTERVALS row that matches the above selection criteria. If no row matches the database content is not altered. Note that no row will match if conflicting information is supplied.
Note
When inserting into FAMILY_EVENTS it is not
recommended to set any of the
EID,
E_MomPID,
E_InfantPID,
E_SibPID, or
E_NonfocalPID values.
Omitting these columns or supplying NULL values will
allow the system to automatically generate ids.
Note
When inserting into FAMILY_EVENTS the user-supplied value for E_BaboonPart is ignored.
Updating the FAMILY_EVENTS view updates EVENTS, as expected.
Updating FAMILY_EVENTS can update, insert into, or delete rows from PARTS.
Note
The examples below explicitly specify an
EVENTS.EID
value. This is solely for the purpose of brevity. Any valid
WHERE clause could be used instead to
designate the desired row in
EVENTS.
If the old values for a mom, infant, sib or nonfocal
PARTS row are not NULL and the new
values are all NULL then the old row is deleted. The
following example removes the mother as a participant in the
event with an
EVENTS.EID
of 1234:
Example 5.6. Deleting The Mother's PARTS Row
UPDATE family_events
SET e_mom = NULL
, e_mompid = NULL
, e_mompart = NULL
WHERE eid = 1234;
If the old values for a mom, infant, sib, or nonfocal
are NULL, i.e. if the mom, infant, sib, or nonfocal has no
PARTS row then a new
PARTS row can be inserted by supplying
values. The following example adds, to the event with an
EVENTS.EID
of 1234, assuming she is not already one
of the individuals involved in the event, the mother as an
actor (PARTS.Part
= 'R'):
Example 5.7. Inserting A PARTS Row For The Mother
UPDATE family_events
SET e_mom = 'GA'
, e_mompart = 'R'
WHERE eid = 1234;
If old values exist, they are replaced by new values.
Assuming the non-focal JOE is involved in
the event with an
EVENTS.EID
of 1234, the following example changes
the non-focal to JAK:
Example 5.8. Changing Non-Focal Participant In A PARTS Row
UPDATE family_events
SET e_nonfocal = 'JAK'
WHERE eid = 1234;
Note
The ChimpID values need
not correspond to the roles the individuals play in the
follow because data is associated with chimp id values,
not focal roles. The presence of focal roles in the
column names is, in some sense, artificial. For example,
there are two ways to change the infant
(INF) to a sibling
(SB) in the event with an
EVENTS.EID
of 1234. The first is to delete the
PARTS row for the infant and insert a
new PARTS row for the sibling:
Example 5.9. Deleting the Infant's PARTS row and Inserting a New PARTS Row for the Sibling
UPDATE family_events
SET e_infant = NULL
, e_infantpid = NULL
, e_infantpart = NULL
, e_sib = 'SB'
, e_sibpart = 'R'
WHERE eid = 1234;
Deleting and re-adding rows is somewhat inefficient and error prone. It is probably better to simply replace the infant's ChimpID value with the siblings. Simply changing the PARTS.Participant value is enough; being the sibling is a property of a ChimpID with respect to a follow and, again, when updating the database there is no correspondence between the column names and the roles played by the individual chimps. The following is sufficient to re-use the exiting PARTS row and change the infant's ChimpID value to the siblings:
Example 5.10. Changing an Infant's PARTS row Into A PARTS Row for the Sibling
UPDATE family_events
SET e_infant = 'SB' -- SB is the sibling
WHERE eid = 1234;
When the result is queried SB
will show up as a sibling, not an infant.
It is an error to attempt to update the values of any columns not derived from the EVENTS or PARTS tables. It is also an error to attempt to change the E_BaboonPart value.
When updated, FAMILY_EVENTS raises a notice containing the number of rows altered. A row is counted only once, when updating the row changes the database content in any way.
FOCAL_EVENTS contains one row per EVENTS row per focal individual involved in the behavioral event. FOCAL_EVENTS provides a direct way to analyze behaviors when interested in the role (mom, infant, sib, non-focal) of the individuals involved in the behavior.
Caution
FOCAL_EVENTS has 1 or 2 rows per behavioral event -- per EVENTS row -- depending on whether the the behavioral interaction is social and how many of the individuals involved are focals in the follow.
Caution
FOCAL_EVENTS includes rows for behavioral interactions with baboons.
Figure 5.50. Query Defining the FOCAL_EVENTS View
SELECT events.eid AS eid
, events.intid AS intid
, focals.pid AS focalpid
, otherpart.pid AS otherpid
, events.behavior AS behavior
, events.position AS position
, events.uncertain AS uncertain
, followparts.role AS focalrole
, focals.participant AS focal
, focals.part AS focalpart
, CASE
WHEN events.baboon THEN
'gmi_nonfocal'
ELSE
followrole(intervals.followid, otherpart.participant)
END AS otherrole
, otherpart.participant AS other
, CASE
WHEN events.baboon THEN
CASE
WHEN focals.part = 'gmi_actor' THEN
'gmi_actee'
WHEN focals.part = 'gmi_actee' THEN
'gmi_actor'
ELSE
'gmi_participant'
END::parts_part
ELSE
otherpart.part
END AS otherpart
, events.baboon AS baboon
, events.comment AS comment
FROM events
JOIN intervals
ON (intervals.intid = events.intid)
JOIN followparts
ON (followparts.followid = intervals.followid)
JOIN chimpids AS fp_ids
ON (fp_ids.chimpid = followparts.chimpid)
JOIN parts AS focals
ON (focals.eid = events.eid)
JOIN chimpids AS focal_ids
ON (focal_ids.chimpid = focals.participant)
LEFT OUTER JOIN parts AS otherpart
ON (otherpart.eid = events.eid
AND focals.pid IS DISTINCT FROM otherpart.pid)
WHERE focal_ids.animid = fp_ids.animid;
| Column | From | Description |
|---|---|---|
| EID | EVENTS.EID | Identifier of the behavioral event row. |
| IntID | EVENTS.IntID | Identifier of the observation interval during which the event was recorded. |
| FocalPID | PARTS.PID | Identifier of the behavioral participant row which records behavioral data regarding one of the focals in the follow. |
| OtherPID | PARTS.PID |
Identifier of the behavioral participant row which records behavioral data regarding the other individual (the individual involved in the behavior who is not designated as the focal in the row). This value is |
| Behavior | EVENTS.Behavior | Code indicating the behavior observed. |
| Position | EVENTS.Position | Position within the canopy where the event occurred. |
| Uncertain | EVENTS.Uncertain | Whether or not the behavior was observed with certainty. |
| FocalRole (Role in Follow of Focal Chimp) | FOLLOWPARTS.Role | Code for the role, i.e. mom, sib, infant, played by the individual designated as focal in the behavioral event. |
| Focal | PARTS.Participant | The identifier or alternate identifier of the focal. |
| FocalPart | PARTS.Part | The role the focal plays in the behavior. |
| OtherRole (Role in Follow of Other Chimp) | FOLLOWPARTS.Role or computed in the case of interaction with a baboon |
Code for the role played by the other
individual in the behavioral event,
or This value is |
| Other | PARTS.Participant |
The identifier or alternate identifier of the other individual (the the individual not designated as the focal in the row). This value is |
| OtherPart | PARTS.Part or computed in the case of interaction with a baboon |
The role the other individual (the individual involved in the behavior who is not designated as the focal in the row) plays in the behavior. This value is |
| Baboon | EVENTS.Baboon | Whether or not a baboon is the second participant in the behavioral interaction. |
| Comment | EVENTS.Comment | Textual remarks regarding the behavioral event. |
FOCAL_SOCIAL_EVENTS contains one row per EVENTS row per individual involved in the behavioral event when the individual is a focal in the follow where a focal in the follow is one of 2 chimpanzees involved in the exhibited behavior. FOCAL_SOCIAL_EVENTS provides a direct way to analyze social behaviors when interested in the role (mom, infant, sib, non-focal) of the chimpanzees involved in the behavior.
Caution
FOCAL_SOCIAL_EVENTS has 0, 1 or 2 rows per behavioral event -- per EVENTS row -- depending on whether there are 2 chimpanzees involved in the event and how many of the chimpanzees are focals in the follow.
Figure 5.52. Query Defining the FOCAL_SOCIAL_EVENTS View
SELECT events.eid AS eid
, events.intid AS intid
, focals.pid AS focalpid
, otherpart.pid AS otherpid
, events.behavior AS behavior
, events.position AS position
, events.uncertain AS uncertain
, followparts.role AS focalrole
, focals.participant AS focal
, focals.part AS focalpart
, followrole(intervals.followid, otherpart.participant) AS otherrole
, otherpart.participant AS other
, otherpart.part AS otherpart
, events.comment AS comment
FROM events
JOIN intervals
ON (intervals.intid = events.intid)
JOIN followparts
ON (followparts.followid = intervals.followid)
JOIN chimpids AS fp_ids
ON (fp_ids.chimpid = followparts.chimpid)
JOIN parts AS focals
ON (focals.eid = events.eid)
JOIN chimpids AS focal_ids
ON (focal_ids.chimpid = focals.participant)
JOIN parts AS otherpart
ON (otherpart.eid = events.eid)
WHERE focal_ids.animid = fp_ids.animid
AND focals.pid <> otherpart.pid;
| Column | From | Description |
|---|---|---|
| EID | EVENTS.EID | Identifier of the behavioral event row. |
| IntID | EVENTS.IntID | Identifier of the observation interval during which the event was recorded. |
| FocalPID | PARTS.PID | Identifier of the behavioral participant row which records behavioral data regarding one of the focals in the follow. |
| OtherPID | PARTS.PID | Identifier of the behavioral participant row which records behavioral data regarding the other individual (the individual involved in the behavior who is not designated as the focal in the row). |
| Behavior | EVENTS.Behavior | Code indicating the behavior observed. |
| Position | EVENTS.Position | Position within the canopy where the event occurred. |
| Uncertain | EVENTS.Uncertain | Whether or not the behavior was observed with certainty. |
| FocalRole (Role in Follow of Focal Chimp) | FOLLOWPARTS.Role | Code for the role, i.e. mom, sib, infant, played by the individual designated as focal in the behavioral event. |
| Focal | PARTS.Participant | The identifier or alternate identifier of the focal. |
| FocalPart | PARTS.Part | The role the focal plays in the behavior. |
| OtherRole (Role in Follow of Other Chimp) | FOLLOWPARTS.Role | Code for the role played by the other individual
in the behavioral event,
or NF if the other
individual is not a focal in the follow. |
| Other | PARTS.Participant | The identifier or alternate identifier of the other individual (the the individual not designated as the focal in the row). |
| OtherPart | PARTS.Part | The role the other individual (the individual involved in the behavior who is not designated as the focal in the row) plays in the behavior. |
| Comment | EVENTS.Comment | Textual remarks regarding the behavioral event. |
NOOBS contains one row per FOLLOWS row, per FOLLOWPARTS row, per minute from 06:00 through 20:00, inclusive, but only when there is no behavioral data on the focal. NOOBS provides a direct way to tell when there is no behavioral data on a focal, regardless of reason and, so long as the individual is a focal in a follow on given day, irrespective of whether an observer is present.
Note that the ChimpID should be the same as the AnimID, unless twins are involved.
Figure 5.54. Query Defining the NOOBS View
SELECT followparts.followid AS followid
, followparts.chimpid AS chimpid
, followparts.role AS role
, chimpids.animid AS animid
, times.time AS time
FROM followparts
JOIN chimpids ON (chimpids.chimpid = followparts.chimpid)
CROSS JOIN
(SELECT GENERATE_SERIES('2000-01-01 gmi_day_start'::TIMESTAMP
, '2000-01-01 gmi_day_end'::TIMESTAMP
, '1 minute')::TIME
AS time
) AS times
WHERE NOT EXISTS
(SELECT 1
FROM intervals
JOIN events ON (events.intid = intervals.intid)
JOIN parts ON (parts.eid = events.eid)
JOIN chimpids AS part_cids
ON (part_cids.chimpid = parts.participant)
WHERE intervals.followid = followparts.followid
AND intervals.time = times.time
AND part_cids.animid = chimpids.animid
LIMIT 1);
| Column | From | Description |
|---|---|---|
| FollowID | FOLLOWPARTS.FollowID | Identifier of the follow. |
| ChimpID | FOLLOWPARTS.ChimpID | The ChimpID of the focal. Useful when twins are involved. |
| Role | FOLLOWPARTS.Role | The role the focal plays in the follow. |
| AnimID | CHIMPIDS.AnimID | The canonical id, the BIOGRAPHY_DATA.AnimID of the focal. |
| Time | Computed | The time. |
A social bout is defined as a contiguous series of minutes during which a focal is involved in a behavior with a given social partner. However, behavioral events involving baboons are excluded when considering contiguity. Behaviors are distinguished by the codes used to represent them. An individual can be involved in more than one social bout at a time, if involved in more than one social behavior at a time.
SOCIAL_BOUTS contains one row per FOLLOWPARTS row per INTERVALS row, per EVENTS.Behavior value having a related PARTS row that shows the given focal involved in the given behavior with a social partner, per AnimID of the social partner -- one row per follow, per focal, per social partner, per interval in which the focal is involved in a social behavior (excluding behavioral events which involve a baboon), per code for the behavior involved in. The number of rows returned is not affected by the behavioral role the focal plays regards the social behavior or whether the focal is involved with the same behavior with the same social partner in a number of ways. There is a row per focal per social partner per social behavior the focal is involved in per interval, excluding cases where a baboon is involved.
Social bouts are numbered sequentially, in a time-wise order, per follow, per focal, per social partner, per social behavior, starting with 1.
Note that uncertain behavioral events are not distinguished from certain behavioral events in event bout analysis, although the SOCIAL_BOUTS view does provide some information regarding uncertainty.
See EVENT_BOUTS for more information on bout analysis.
Figure 5.56. Query Defining the SOCIAL_BOUTS View: Part I
-- Note that we don't worry about leap seconds given
-- the time values in the db.
WITH detail AS
-- Compute mins_in and mins_out
(SELECT bouts.followid AS followid
, bouts.intid AS intid
, bouts.function AS function
, bouts.animid AS animid
, bouts.role AS role
, bouts.chimpid AS chimpid
, bouts.other_animid AS other_animid
, bouts.bout AS bout
, bouts.behavior AS behavior
, bouts.uncertain AS uncertain
, bouts.time AS time
, (EXTRACT(EPOCH FROM bouts.time)
- EXTRACT(EPOCH FROM FIRST_VALUE(bouts.time) OVER bout_w)
) / 60
+ 1
AS mins_in
, (EXTRACT(EPOCH FROM LAST_VALUE(bouts.time)
OVER (bout_w RANGE BETWEEN CURRENT ROW
AND UNBOUNDED FOLLOWING))
- EXTRACT(EPOCH FROM bouts.time)
) / 60
AS mins_out
FROM
-- Compute bout number by summing transition ticks
(SELECT ticks.followid AS followid
, ticks.intid AS intid
, ticks.function AS function
, ticks.animid AS animid
, ticks.role AS role
, ticks.chimpid AS chimpid
, ticks.other_animid AS other_animid
, ticks.behavior AS behavior
, ticks.uncertain AS uncertain
, ticks.time AS time
, SUM(ticks.bouttick)
OVER (PARTITION BY ticks.followid
, ticks.behavior
, ticks.animid
, ticks.other_animid
ORDER BY ticks.time
, ticks.bouttick DESC)
AS bout
Figure 5.57. Query Defining the SOCIAL_BOUTS View: Part II
FROM
-- Distinguish one bout from another
(SELECT intervals.followid AS followid
, intervals.intid AS intid
, followroles.function AS function
, part_cids.animid AS animid
, followparts.role AS role
, followparts.chimpid AS chimpid
, other_cids.animid AS other_animid
, events.behavior AS behavior
, BOOL_AND(events.uncertain) AS uncertain
, intervals.time AS time
, CASE
WHEN LAG(MIN(intervals.time)) OVER tick_w
= MIN(intervals.time) - '1 minute'::INTERVAL
THEN 0
ELSE
1
END
AS bouttick
Figure 5.58. Query Defining the SOCIAL_BOUTS View: Part III
FROM intervals
JOIN events
ON (events.intid = intervals.intid)
JOIN parts
ON (parts.eid = events.eid)
JOIN chimpids AS part_cids
ON (part_cids.chimpid = parts.participant)
JOIN chimpids AS fp_cids
ON (fp_cids.animid = part_cids.animid)
JOIN followparts
ON (followparts.followid = intervals.followid
AND followparts.chimpid = fp_cids.chimpid)
JOIN followroles
ON (followroles.role = followparts.role)
JOIN parts AS other_parts
ON (other_parts.eid = events.eid
AND other_parts.pid <> parts.pid)
JOIN chimpids AS other_cids
ON (other_cids.chimpid = other_parts.participant)
WHERE NOT(events.baboon)
GROUP BY intervals.followid
, intervals.intid
, part_cids.animid
, other_cids.animid
, events.behavior
-- The values that are unique per above anyway
, followroles.function
, followparts.role
, followparts.chimpid
, intervals.time
WINDOW tick_w AS (PARTITION BY intervals.followid
, events.behavior
, part_cids.animid
, other_cids.animid
ORDER BY MIN(intervals.time))
) AS ticks
) AS bouts
WINDOW bout_w AS (PARTITION BY bouts.followid
, bouts.behavior
, bouts.animid
, bouts.other_animid
, bouts.bout
ORDER BY bouts.time)
)
Figure 5.59. Query Defining the SOCIAL_BOUTS View: Part IV
-- Distribute aggregated bout info over the bout's minutes
SELECT detail.followid AS followid
, detail.intid AS intid
, detail.function AS function
, detail.animid AS animid
, detail.role AS role
, detail.chimpid AS chimpid
, summary.other_function AS other_function
, detail.other_animid AS other_animid
, summary.other_role AS other_role
, summary.other_chimpid AS other_chimpid
, detail.bout AS bout
, detail.behavior AS behavior
, detail.uncertain AS uncertain
, detail.time AS time
, detail.mins_in AS mins_in
, detail.mins_out AS mins_out
, summary.prior_gap AS prior_gap
, summary.gap_noobs AS gap_noobs
, summary.left_censored AS left_censored
, summary.right_censored AS right_censored
, summary.day_start AS day_start
, summary.day_end AS day_end
FROM
detail
JOIN
(-- Determine the role the focal's social partner has
-- in the follow family group, if any.
SELECT bouts.followid AS followid
, bouts.animid AS animid
, bouts.other_animid AS other_animid
, other_followroles.function AS other_function
, other_followparts.role AS other_role
, other_followparts.chimpid AS other_chimpid
, bouts.behavior AS behavior
, bouts.bout AS bout
, bouts.prior_gap AS prior_gap
, bouts.gap_noobs AS gap_noobs
, bouts.left_censored AS left_censored
, bouts.right_censored AS right_censored
, bouts.day_start AS day_start
, bouts.day_end AS day_end
Figure 5.60. Query Defining the SOCIAL_BOUTS View: Part V
FROM
(-- Aggregate per-bout information
SELECT bout_ends.followid AS followid
, bout_ends.animid AS animid
, bout_ends.other_animid AS other_animid
, bout_ends.behavior AS behavior
, bout_ends.bout AS bout
, CASE
WHEN bout_ends.bout = 1 THEN
NULL
ELSE
SUM(bout_ends.prior_gap)
END
AS prior_gap
, CASE
WHEN bout_ends.bout = 1 THEN
NULL
ELSE
SUM(bout_ends.prior_gap)
-- Subtract number of minutes the focal had
-- any behaviors during the gap.
- (SELECT COUNT(DISTINCT intervals.intid)
FROM intervals
JOIN events
ON (events.intid = intervals.intid)
JOIN parts
ON (parts.eid = events.eid)
JOIN chimpids
ON (chimpids.chimpid
= parts.participant)
WHERE intervals.followid = bout_ends.followid
AND MIN(bout_ends.time)
> intervals.time
AND intervals.time
>= MIN(bout_ends.time)
- (SUM(bout_ends.prior_gap)
|| ' minutes')::INTERVAL
AND chimpids.animid = bout_ends.animid)
END
AS gap_noobs
, BOOL_OR(bout_ends.left_censored) AS left_censored
, BOOL_OR(bout_ends.right_censored) AS right_censored
Figure 5.61. Query Defining the SOCIAL_BOUTS View: Part VI
, CASE
WHEN bout_ends.bout = 1 THEN
NOT EXISTS
-- Behavioral data on the focal before the bout
(SELECT 1
FROM
intervals
JOIN events
ON (events.intid = intervals.intid)
JOIN parts
ON (parts.eid = events.eid)
JOIN chimpids
ON (chimpids.chimpid
= parts.participant)
WHERE intervals.followid = bout_ends.followid
AND intervals.time
< MIN(bout_ends.time)
AND chimpids.animid = bout_ends.animid
LIMIT 1)
ELSE
FALSE
END
AS day_start
, CASE
WHEN bout_ends.bout = LAST_VALUE(bout_ends.bout)
OVER (PARTITION BY
bout_ends.followid
, bout_ends.animid
, bout_ends.behavior
ORDER BY bout_ends.bout
RANGE
BETWEEN CURRENT ROW
AND UNBOUNDED FOLLOWING)
THEN
NOT EXISTS
-- Behavioral data on the focal after the bout
(SELECT 1
FROM intervals
JOIN events
ON (events.intid = intervals.intid)
JOIN parts
ON (parts.eid = events.eid)
JOIN chimpids
ON (chimpids.chimpid
= parts.participant)
WHERE intervals.followid = bout_ends.followid
AND intervals.time > MAX(bout_ends.time)
AND chimpids.animid = bout_ends.animid
LIMIT 1)
ELSE
FALSE
END
AS day_end
Figure 5.62. Query Defining the SOCIAL_BOUTS View: Part VII
FROM
-- Examine the bout's endpoints to compute prior_gap, and
-- left and right censoring.
(SELECT detail.followid
, detail.animid
, detail.other_animid
, detail.behavior
, detail.bout
, detail.mins_in AS mins_in
, detail.mins_out AS mins_out
, CASE
WHEN detail.mins_in = 1 THEN
(EXTRACT(EPOCH FROM detail.time)
- EXTRACT(EPOCH FROM
LAG(detail.time)
OVER (PARTITION BY
detail.followid
, detail.animid
, detail.other_animid
, detail.behavior
ORDER BY detail.time))
) / 60 - 1
ELSE
0
END
AS prior_gap
, CASE
WHEN detail.mins_in = 1 THEN
NOT EXISTS
(SELECT 1
FROM
intervals AS i
JOIN events AS e
ON (e.intid = i.intid)
JOIN parts AS p
ON (p.eid = e.eid)
JOIN chimpids AS p_cids
ON (p_cids.chimpid = p.participant)
WHERE i.followid = detail.followid
AND i.time = detail.time
- '1 minute'::INTERVAL
AND p_cids.animid = detail.animid
LIMIT 1)
ELSE
FALSE
END
AS left_censored
Figure 5.63. Query Defining the SOCIAL_BOUTS View: Part VIII
, CASE
WHEN detail.mins_out = 0 THEN
NOT EXISTS
(SELECT 1
FROM
intervals AS i
JOIN events AS e
ON (e.intid = i.intid)
JOIN parts AS p
ON (p.eid = e.eid)
JOIN chimpids AS p_cids
ON (p_cids.chimpid = p.participant)
WHERE i.followid = detail.followid
AND i.time = detail.time
+ '1 minute'::INTERVAL
AND p_cids.animid = detail.animid
LIMIT 1)
ELSE
FALSE
END
AS right_censored
, detail.time AS time
FROM detail
WHERE detail.mins_in = 1
OR detail.mins_out = 0
) AS bout_ends
GROUP BY bout_ends.followid
, bout_ends.animid
, bout_ends.other_animid
, bout_ends.behavior
, bout_ends.bout
) AS bouts
JOIN chimpids AS other_chimpids
ON (other_chimpids.animid = bouts.other_animid)
LEFT OUTER JOIN followparts AS other_followparts
ON (other_followparts.followid = bouts.followid
AND other_followparts.chimpid
= other_chimpids.chimpid)
LEFT OUTER JOIN followroles AS other_followroles
ON (other_followroles.role
= other_followparts.role)
) AS summary
ON (summary.followid = detail.followid
AND summary.animid = detail.animid
AND summary.other_animid = detail.other_animid
AND summary.behavior = detail.behavior
AND summary.bout = detail.bout);
| Column | From | Description |
|---|---|---|
| FollowID | FOLLOWPARTS.FollowID | Identifier of the follow. |
| IntID | INTERVALS.IntID | Identifier of the observation interval comprising one of the bout's minutes. |
| Function (Function in Follow of Focal) | FOLLOWROLES.Function | Code indicating the biological role the focal plays in the follow: Mother, Infant, or Sibling. |
| AnimID | CHIMPIDS.AnimID | Canonical identifier of the focal, the BIOGRAPHY_DATA.AnimID value. This can differ from the ChimpID in the case of twins or other circumstances where individuals have multiple identifiers. |
| Role (Role in Follow of Focal) | FOLLOWPARTS.Role | Code indicating the family role the focal plays in the follow. This can differ from the Function in the case of twins. |
| ChimpID | FOLLOWPARTS.ChimpID | Identifier of the focal as recorded in the FOLLOWROLES row for the follow. This can differ from the focal's AnimID in the case of twins or other circumstances where individuals have multiple identifiers. In such cases this may also differ from the ChimpID stored in the PARTS.Participant rows which designate the focal as being involved in a behavioral event. |
| Other_Function (Function in Follow of focal's social partner) | FOLLOWROLES.Function | Code indicating the biological role the focal's
social partner plays in the follow: Mother, Infant,
Sibling, or NULL when the social partner is not also
a focal. |
| Other_AnimID (AnimID of the focal's social partner) | CHIMPIDS.AnimID | Canonical identifier of the focal's social partner, the BIOGRAPHY_DATA.AnimID value. This can differ from the ChimpID in the case of twins or other circumstances where individuals have multiple identifiers. |
| Other_Role (Role in Follow of focal's social partner) | FOLLOWPARTS.Role | Code indicating the family role the focal's
social partner plays in the follow. As with
Other_Function the value
is NULL when the focal's social partner is not a
focal. The value in this column can differ from the
Other_Function in the
case of twins. |
| Other_ChimpID (ChimpID of the focal's social partner when the partner is a focal) | FOLLOWPARTS.ChimpID |
Identifier of the focal's social partner as recorded in the FOLLOWROLES row for the follow. This can differ from the focal's AnimID in the case of twins or other circumstances where individuals have multiple identifiers. In such cases this may also differ from the ChimpID stored in the PARTS.Participant rows which designate the social partner as being involved in a behavioral event.
|
| Bout | Computed | Integer indicating the bout number. Bout numbers start with 1 and are per follow, per focal, per social partner, per behavior code. |
| Behavior | EVENTS.Behavior | Code indicating the behavior in which the focal is involved. |
| Uncertain | EVENTS.Uncertain | TRUE if all of the (non-baboon related)
behavioral events in which the focal is involved with
the social partner during the given minute for the
given behavior are uncertain, FALSE
otherwise. |
| Time | INTERVALS.Time | The time of the observation. |
| Mins_In (Minutes Into the bout) | Computed | Number of minutes into the current bout, including the current minute.[a] |
| Mins_Out (Minutes remaining in the bout) | Computed | Number of minutes remaining in the current bout, excluding the current minute.[b] |
| Prior_Gap (Minutes between the current and Prior bout) | Computed | The number of minutes between the current bout
and the previous bout (of the same follow, focal,
social partner, and behavior). NULL if there is no
prior bout. |
| Gap_NoObs (Minutes without Observation of the focal between the current and prior bout) | Computed | The number of minutes between the current bout
and the previous bout (of the same follow, focal,
social partner, and behavior) during which there is no
behavioral data what so ever on the focal. NULL if
there is no prior bout. |
| Left_Censored | Computed. | TRUE when the bout is left censored, FALSE
otherwise. See EVENT_BOUTS above for
more information regards censoring. |
| Right_Censored | Computed. | TRUE when the bout is right censored, FALSE
otherwise. See EVENT_BOUTS above for
more information regards censoring. |
| Day_Start (Bout is censored due to Start of Day) | Computed | TRUE when the bout is censored because it
occurs at the beginning of the day's observation of the
follow, FALSE otherwise. |
| Day_End (Bout is censored due to End of Day) | Computed | TRUE when the bout is censored because it
occurs at the end of the day's observation of the
follow, FALSE otherwise. |
SOCIAL_BOUT_AGGS contains one row per FOLLOWPARTS row per EVENTS.Behavior value having a related PARTS row that shows the given focal involved in the given behavior with a social partner, per AnimID of the social partner -- one row per follow, per focal, per social partner (excluding behavioral events which involve a baboon), per code for the behavior involved in. Each row represents an entire event bout.
See SOCIAL_BOUTS for more information on event bout analysis.
Figure 5.65. Query Defining the SOCIAL_BOUT_AGGS View
SELECT social_bouts.followid AS followid
, social_bouts.function AS function
, social_bouts.animid AS animid
, social_bouts.role AS role
, social_bouts.chimpid AS chimpid
, social_bouts.other_function AS other_function
, social_bouts.other_animid AS other_animid
, social_bouts.other_role AS other_role
, social_bouts.other_chimpid AS other_chimpid
, social_bouts.bout AS bout
, social_bouts.behavior AS behavior
, BOOL_OR(social_bouts.uncertain) AS uncertain
, MIN(social_bouts.time) AS start_time
, MAX(social_bouts.time) AS stop_time
, social_bouts.prior_gap AS prior_gap
, social_bouts.gap_noobs AS gap_noobs
, social_bouts.left_censored AS left_censored
, social_bouts.right_censored AS right_censored
, social_bouts.day_start AS day_start
, social_bouts.day_end AS day_end
FROM social_bouts
GROUP BY social_bouts.followid
, social_bouts.animid
, social_bouts.other_animid
, social_bouts.bout
, social_bouts.behavior
-- The values that are unique per above anyway
, social_bouts.function
, social_bouts.role
, social_bouts.chimpid
, social_bouts.other_function
, social_bouts.other_role
, social_bouts.other_chimpid
, social_bouts.prior_gap
, social_bouts.gap_noobs
, social_bouts.left_censored
, social_bouts.right_censored
, social_bouts.day_start
, social_bouts.day_end;
Figure 5.66. Entity Relationship Diagram of the SOCIAL_BOUT_AGGS View
| Column | From | Description |
|---|---|---|
| FollowID | FOLLOWPARTS.FollowID | Identifier of the follow. |
| Function (Function in Follow of Focal) | FOLLOWROLES.Function | Code indicating the biological role the focal plays in the follow: Mother, Infant, or Sibling. |
| AnimID | CHIMPIDS.AnimID | Canonical identifier of the focal, the BIOGRAPHY_DATA.AnimID value. This can differ from the ChimpID in the case of twins or other circumstances where individuals have multiple identifiers. |
| Role (Role in Follow of Focal) | FOLLOWPARTS.Role | Code indicating the family role the focal plays in the follow. This can differ from the Function in the case of twins. |
| ChimpID | FOLLOWPARTS.ChimpID | Identifier of the focal as recorded in the FOLLOWROLES row for the follow. This can differ from the focal's AnimID in the case of twins or other circumstances where individuals have multiple identifiers. In such cases this may also differ from the ChimpID stored in the PARTS.Participant rows which designate the focal as being involved in a behavioral event. |
| Other_Function (Function in Follow of focal's social partner) | FOLLOWROLES.Function | Code indicating the biological role the focal's
social partner plays in the follow: Mother, Infant,
Sibling, or NULL when the social partner is not also
a focal. |
| Other_AnimID (AnimID of the focal's social partner) | CHIMPIDS.AnimID | Canonical identifier of the focal's social partner, the BIOGRAPHY_DATA.AnimID value. This can differ from the ChimpID in the case of twins or other circumstances where individuals have multiple identifiers. |
| Other_Role (Role in Follow of focal's social partner) | FOLLOWPARTS.Role | Code indicating the family role the focal's
social partner plays in the follow. As with
Other_Function the value
is NULL when the focal's social partner is not a
focal. The value in this column can differ from the
Other_Function in the
case of twins. |
| Other_ChimpID (ChimpID of the focal's social partner when the partner is a focal) | FOLLOWPARTS.ChimpID |
Identifier of the focal's social partner as recorded in the FOLLOWROLES row for the follow. This can differ from the focal's AnimID in the case of twins or other circumstances where individuals have multiple identifiers. In such cases this may also differ from the ChimpID stored in the PARTS.Participant rows which designate the social partner as being involved in a behavioral event.
|
| Bout | Computed | Integer indicating the bout number. Bout numbers start with 1 and are per follow, per focal, per behavior code. |
| Behavior | EVENTS.Behavior | Code indicating the behavior in which the focal is involved. |
| Uncertain | EVENTS.Uncertain | TRUE if any of the underlying
SOCIAL_BOUTS rows which comprise the
bout are uncertain, FALSE otherwise. |
| Start_Time | INTERVALS.Time | The time the event bout started. The time of the first observation of the bout. |
| Stop_Time | INTERVALS.Time | The time the event bout stopped. The time of the last observation of the bout. |
| Prior_Gap (Minutes between the current and Prior bout) | Computed | The number of minutes between the current bout
and the previous bout (of the same follow, focal,
behavior). NULL if there is no prior bout. |
| Gap_NoObs (Minutes without Observation of the focal between the current and prior bout) | Computed | The number of minutes between the current bout
and the previous bout (of the same follow, focal,
behavior) during which there is no behavioral data what
so ever on the focal. NULL if there is no prior
bout. |
| Left_Censored | Computed. | TRUE when the bout is left censored, FALSE
otherwise. See above for more information regards
censoring. |
| Right_Censored | Computed. | TRUE when the bout is right censored, FALSE
otherwise. See above for more information regards
censoring. |
| Day_Start (Bout is censored due to Start of Day) | Computed | TRUE when the bout is censored because it
occurs at the beginning of the day's observation of the
follow, FALSE otherwise. |
| Day_End (Bout is censored due to End of Day) | Computed | TRUE when the bout is censored because it
occurs at the end of the day's observation of the
follow, FALSE otherwise. |
SOCIAL_EVENTS contains one row per EVENTS row per individual involved in the behavioral event per chimpanzee when there are 2 chimpanzees (no baboons) involved in the exhibited behavior. SOCIAL_EVENTS provides a direct way to analyze chimp social behaviors regardless of whether a chimpanzee is a focal in the follow.
Caution
SOCIAL_EVENTS has 0 or 2 rows per behavioral event -- per EVENTS row -- depending on whether there are 2 chimpanzees involved in the event.
Tip
The two SOCIAL_EVENTS rows for each EVENTS row are reciprocal, the individuals arbitrarily designated “first” and “second” are reversed. So, either querying for the first individual as the mother and the 2nd as the infant or querying for the second individual as the mother and the 1st as the infant will eliminate double counting of events; each query returning “one side” of the “duplicated pair”.
The mutual placement of each interaction participant in both the “first” and “second” columns can be convenient when querying; all individuals involved in a behavior can be tested against, joined with, etc., regardless of whether the “first” or “second” column content is examined.
Tip
The Part1Role and Part2Role columns significantly slow the query. Performance is much improved if, when possible, these columns are not displayed or queried against.
Figure 5.67. Query Defining the SOCIAL_EVENTS View
SELECT events.eid AS eid
, events.intid AS intid
, part1.pid AS part1pid
, part2.pid AS part2pid
, events.behavior AS behavior
, events.position AS position
, events.uncertain AS uncertain
, followrole(intervals.followid, part1.participant) AS part1role
, part1.participant AS participant1
, part1.part AS part1
, followrole(intervals.followid, part2.participant) AS part2role
, part2.participant AS participant2
, part2.part AS part2
, events.comment AS comment
FROM events
JOIN intervals
ON (intervals.intid = events.intid)
JOIN parts AS part1
ON (part1.eid = events.eid)
JOIN parts AS part2
ON (part2.eid = events.eid)
WHERE part1.pid <> part2.pid;
| Column | From | Description |
|---|---|---|
| EID | EVENTS.EID | Identifier of the behavioral event row. |
| IntID | EVENTS.IntID | Identifier of the observation interval during which the event was recorded. |
| Part1PID | PARTS.PID | Identifier of the behavioral participant row which records behavioral data regarding “the first” of the individuals in the behavioral event. |
| Part2PID | PARTS.PID | Identifier of the behavioral participant row which records behavioral data regarding “the second” of the individuals in the behavioral event. |
| Behavior | EVENTS.Behavior | Code indicating the behavior observed. |
| Position | EVENTS.Position | Position within the canopy where the event occurred. |
| Uncertain | EVENTS.Uncertain | Whether or not the behavior was observed with certainty. |
| Part1Role (Role in Follow of First Chimp) | FOLLOWPARTS.Role | Code for the role, i.e. mom, sib, infant, played by the individual designated as “the first” in the behavioral event. |
| Participant1 | PARTS.Participant | The identifier or alternate identifier of the “first” individual. |
| Part1 | PARTS.Part | The role the “first” individual plays in the behavior. |
| Part2Role (Role in Follow of Second Chimp) | FOLLOWPARTS.Role | Code for the role, i.e. mom, sib, infant played by the individual designated as “the second” in the behavioral event. |
| Participant2 | PARTS.Participant | The identifier or alternate identifier of the “second” individual. |
| Part2 | PARTS.Part | The role the “second” individual plays in the behavior. |
| Comment | EVENTS.Comment | Textual remarks regarding the behavioral event. |
SOCIAL_EVENTS_W_BABOONS contains one row per EVENTS row per animal involved in the behavioral event per individual involved in the exhibited behavior, including baboons. SOCIAL_EVENTS_W_BABOONS provides a direct way to analyze social behaviors regardless of whether a chimpanzee is a focal in the follow.
Caution
SOCIAL_EVENTS_W_BABOONS has 0 or 2 rows per behavioral event -- per EVENTS row -- depending on whether there are 2 animals involved in the event.
The two SOCIAL_EVENTS rows for each EVENTS row are reciprocal, the individuals arbitrarily designated “first” and “second” are reversed. This can be convenient when querying; all individuals involved in a behavior can be tested against, joined with, etc., regardless of whether the “first” or “second” column content is examined.
Figure 5.69. Query Defining the SOCIAL_EVENTS_W_BABOONS View: Part I
SELECT events.eid AS eid
, events.intid AS intid
, part1.pid AS part1pid
, part2.pid AS part2pid
, events.behavior AS behavior
, events.position AS position
, events.uncertain AS uncertain
, followrole(intervals.followid, part1.participant) AS part1role
, FALSE AS part1baboon
, part1.participant AS participant1
, part1.part AS part1
, followrole(intervals.followid, part2.participant) AS part2role
, FALSE AS part2baboon
, part2.participant AS participant2
, part2.part AS part2
, events.comment AS comment
FROM events
JOIN intervals
ON (intervals.intid = events.intid)
JOIN parts AS part1
ON (part1.eid = events.eid)
JOIN parts AS part2
ON (part2.eid = events.eid)
WHERE part1.pid <> part2.pid
Figure 5.70. Query Defining the SOCIAL_EVENTS_W_BABOONS View: Part II
UNION
SELECT events.eid AS eid
, events.intid AS intid
, part1.pid AS part1pid
, NULL AS part2pid
, events.behavior AS behavior
, events.position AS position
, events.uncertain AS uncertain
, followrole(intervals.followid, part1.participant) AS part1role
, FALSE AS part1baboon
, part1.participant AS participant1
, part1.part AS part1
, 'NF' AS part2role
, TRUE AS part2baboon
, NULL::VARCHAR(gmi_animid_len) AS participant2
, CASE
WHEN part1.part = 'gmi_actor' THEN
'gmi_actee'
WHEN part1.part = 'gmi_actee' THEN
'gmi_actor'
ELSE
'gmi_participant'
END::parts_part
AS part2
, events.comment AS comment
FROM events
JOIN intervals
ON (intervals.intid = events.intid)
JOIN parts AS part1
ON (part1.eid = events.eid)
WHERE events.baboon
Figure 5.71. Query Defining the SOCIAL_EVENTS_W_BABOONS View: Part III
UNION
SELECT events.eid AS eid
, events.intid AS intid
, NULL AS part1pid
, part2.pid AS part2pid
, events.behavior AS behavior
, events.position AS position
, events.uncertain AS uncertain
, 'NF' AS part1role
, TRUE AS part1baboon
, NULL::VARCHAR(gmi_animid_len) AS participant1
, CASE
WHEN part2.part = 'gmi_actor' THEN
'gmi_actee'
WHEN part2.part = 'gmi_actee' THEN
'gmi_actor'
ELSE
'gmi_participant'
END::parts_part
AS part1
, followrole(intervals.followid, part2.participant) AS part2role
, FALSE AS part2baboon
, part2.participant AS participant2
, part2.part AS part2
, events.comment AS comment
FROM events
JOIN intervals
ON (intervals.intid = events.intid)
JOIN parts AS part2
ON (part2.eid = events.eid)
WHERE events.baboon;
| Column | From | Description |
|---|---|---|
| EID | EVENTS.EID | Identifier of the behavioral event row. |
| IntID | EVENTS.IntID | Identifier of the observation interval during which the event was recorded. |
| Part1PID | PARTS.PID | Identifier of the behavioral participant row which records behavioral data regarding “the first” of the animals in the behavioral event. |
| Part2PID | PARTS.PID | Identifier of the behavioral participant row which records behavioral data regarding “the second” of the animals in the behavioral event. |
| Behavior | EVENTS.Behavior | Code indicating the behavior observed. |
| Position | EVENTS.Position | Position within the canopy where the event occurred. |
| Uncertain | EVENTS.Uncertain | Whether or not the behavior was observed with certainty. |
| Part1Role (Role in Follow of First Animal) | FOLLOWPARTS.Role or NF
in the case of baboons or nonfocal chimpanzees |
Code for the role, i.e. mom, sib, infant, played by the animal designated as “the first” in the behavioral event. |
| Part1Baboon | Computed | A Boolean value, true when the “first” animal is a baboon. |
| Participant1 | PARTS.Participant | The identifier or alternate identifier of the
“first” animal, or NULL if the individual
is a baboon. |
| Part1 | PARTS.Part or computed based on the 2nd individual's role if the animal is a baboon | The role the “first” animal plays in the behavior. |
| Part2Role (Role in Follow of Second Animal) | FOLLOWPARTS.Role or NF
in the case of baboons or nonfocal chimpanzees |
Code for the role, i.e. mom, sib, infant, played by the animal designated as “the second” in the behavioral event. |
| Part2Baboon | Computed | A Boolean value, true when the “second” animal is a baboon. |
| Participant2 | PARTS.Participant | The identifier or alternate identifier of the
“second” animal, or NULL if the individual
is a baboon. |
| Part2 | PARTS.Part or computed based on the 2nd individual's role if the animal is a baboon | The role the “second” animal plays in the behavior. |
| Comment | EVENTS.Comment | Textual remarks regarding the behavioral event. |