sokwedb - Recent changes [en]

Conversion Progress

KarlPinc — Mon, 20 Jul 2026 23:46:37 GMT

Housekeeping: All tables are implemented

Conversion Data Issues

StevanEarl — Sun, 19 Jul 2026 23:36:06 GMT

Update Problem #83 row count

Show changes

Conversion Data Issues

KarlPinc — Sat, 18 Jul 2026 15:34:15 GMT

* (#45) The follow_arrival.fa_update column is not preserved: Resolve

Conversion Progress

KarlPinc — Thu, 16 Jul 2026 03:06:06 GMT

Data Tables: ARRIVALS_A is supposed to generate warnings instead of having hard errors

Conversion Data Issues

KarlPinc — Wed, 15 Jul 2026 17:21:34 GMT

Bad data: Data is fixed in clean. Query easy to find the problem rows.

Conversion Data Issues

IanGilby — Tue, 07 Jul 2026 21:11:28 GMT

* (#76) There are AGGRESSION_EVENT rows where the Actor/Actee are not in the biography table.

Conversion Progress

KarlPinc — Tue, 07 Jul 2026 16:17:58 GMT

Data Tables: Re-doing WATCHES.Type

Conversion Data Issues

IanGilby — Mon, 06 Jul 2026 19:33:44 GMT

* (#79) There are AGGRESSION_EVENT participants outside their valid study participation window in the biography data.

Conversion Data Issues

KarlPinc — Mon, 06 Jul 2026 16:26:41 GMT

Karl's comment

Conversion Data Issues

StevanEarl — Mon, 06 Jul 2026 15:47:41 GMT

fix malformed header

Show changes

Conversion Progress

KarlPinc — Sun, 05 Jul 2026 18:43:40 GMT

Data Tables: Mating events now allow same-sex matings

Conversion Data Issues

KarlPinc — Fri, 03 Jul 2026 22:33:17 GMT

SOLUTION: Uh, maybe this should not be fixed in MS Access.

@@ Line 74: / Line 74: @@
 ! Table !! State !! Remarks
 |-
-| ELO_RANKS_DAILY_KK_F || Not started, no table yet ||
+| ELO_RANKS_DAILY_KK_F || Coded ||
 |-
-| ELO_RANKS_DAILY_KK_M || Not started, no table yet ||
+| ELO_RANKS_DAILY_KK_M || Coded ||
 |-
-| ELO_RANKS_DAILY_MT_F || Not started, no table yet ||
+| ELO_RANKS_DAILY_MT_F || Coded ||
 |-
-| ELO_RANKS_DAILY_MT_M || Not started, no table yet ||
+| ELO_RANKS_DAILY_MT_M || Coded ||
 |-
-| FERTILITY || Not started, no table yet ||
+| FERTILITY || Coded ||
 |-
 | REPRO_STATES || Coded ||
@@ Line 97: / Line 97: @@
 ! Table !! State !! Remarks
 |-
-| CHIMPS_ON_TIKIS_LOOKUP || Not started, no table yet ||
+| CHIMPS_ON_TIKIS_LOOKUP || Coded ||
 |-
-| COMMUNITY_MEMBERSHIP_UPDATE_LOG || Not started, no table yet ||
+| COMMUNITY_MEMBERSHIP_UPDATE_LOG || Coded ||
 |-
-| FOOD_VARIATIONS || Not started, no table yet ||
+| FOOD_VARIATIONS || Coded ||
 |-
-| PANTGRUNT_EVENT_LOG || Not started, no table yet ||
+| PANTGRUNT_EVENT_LOG || Coded ||
 |-
-| SIV_STATUS_BOUT || Not started, no table yet ||
+| SIV_STATUS_BOUT || Coded ||
 |-
-| SUBADULT_ARRIVALS_LOG || Not started, no table yet ||
+| SUBADULT_ARRIVALS_LOG || Coded ||
 |-
-| TLK_BRECORD_NOTES_CODES || Not started, no table yet ||
+| TLK_BRECORD_NOTES_CODES || Coded ||
 |}

@@ Line 1,138: / Line 1,138: @@
 RESOLVED
-== * (#45) The follow_arrival.fa_update column is not preserved ==
+== (#45) The follow_arrival.fa_update column is not preserved ==
 === Problem ===
@@ Line 1,153: / Line 1,153: @@
 ASK KARL WHY THIS COLUMN CAN'T GO IN
+There is now a ARRIVALS.Updated column.  This provides a place to put the existing
+data.  Going forward, a PG extension (see below) can be installed for more functionality.
 Wouldn't you rather have a [https://en.wikipedia.org/wiki/Temporal_database temporal database]?  (Simple description in the top paragraph [https://wiki.postgresql.org/wiki/Temporal_Extensions here].)  This would allow you to "time travel" and look at the database content at any point in time.  And, it puts a "last changed" timestamp on every row, of every table you say to track temporally, automatically.

@@ Line 17: / Line 17: @@
 | AGGRESSION_EVENT_LOG || Coded ||
 |-
-| AGGRESSIONS || Coded -- changes pending ||
+| AGGRESSIONS || Coded ||
 |-
-| ARRIVALS || Coded -- changes pending ||
+| ARRIVALS || Coded ||
 |-
-| ARRIVALS_A || Coded -- changes pending ||
+| ARRIVALS_A || Coded ||
 |-
 | BIOGRAPHY_DATA || Done ||
@@ Line 27: / Line 27: @@
 | BIOGRAPHY_LOG || Done ||
 |-
-| BRECORD_NOTES || Coded -- changes pending ||
+| BRECORD_NOTES || Coded ||
 |-
-| COLOBUS (COLOBUS hunts) || Coded -- changes pending ||
+| COLOBUS (COLOBUS encounters) || Coded ||
 |-
 | COMM_MEMBS || Coded ||
@@ Line 35: / Line 35: @@
 | COMM_MEMB_LOG || Done ||
 |-
-| EVENTS || In progress, per WATCHES.Type ||
+| EVENTS || Coded ||
 |-
 | FOLLOW_OBSERVERS || Done ||
@@ Line 41: / Line 41: @@
 | FOLLOW_STUDIES || Done ||
 |-
-| FOOD_EVENTS || Coded -- changes pending ||
+| FOOD_EVENTS || Coded ||
 |-
-| GROOM_SCANS_B || Coded -- changes pending ||
+| GROOM_SCANS_B || Coded ||
 |-
-| GROOMINGS || Coded -- changes pending ||
+| GROOMINGS || Coded ||
 |-
 | HUMANS || Coded No conversion needed -- not in MS Access ||
 |-
-| LOCATIONS_PAPER (spatial LOCATIONS in map numbers) || Coded -- changes pending ||
+| LOCATIONS_PAPER (spatial LOCATIONS in map numbers) || Coded ||
 |-
-| LOCATIONS_UTM (spatial LOCATIONS in UTM coordinates) || Coded -- changes pending ||
+| LOCATIONS_UTM (spatial LOCATIONS in UTM coordinates) || Coded ||
 |-
-| MATINGS || Coded -- changes pending ||
+| MATINGS || Coded ||
 |-
-| PANTGRUNTS || Coded -- changes pending ||
+| PANTGRUNTS || Coded ||
 |-
-| ROLES || In progress, per WATCHES.Type ||
+| ROLES || Coded ||
 |-
 | NON_BREC_SIGHTING_SOURCES || Coded ||
@@ Line 65: / Line 65: @@
 | SWELLING_SOURCES || Coded ||
 |-
+| WATCHES || Coded ||
-| WATCHES || Follows and others are done, other WATCHES.Type values are in progress ||
 |}
@@ Line 84: / Line 84: @@
 | FERTILITY || Not started, no table yet ||
 |-
-| REPRO_STATES || Not started ||
+| REPRO_STATES || Coded ||
 |-
-| SWELLING_STATES || No conversion needed, no code written to (re)generate table content ||
+| SWELLING_STATES || Coded. No conversion needed, no code written to (re)generate table content ||
 |-
+| SIGHTINGS || Coded, and done because no conversion needed.  Needs function written to populate it. ||
-| SIGHTINGS || Not started ||
 |}
 == Housekeeping ==

@@ Line 1,887: / Line 1,887: @@
 SHOULD BE SOLVED BY WATCHES TABLE
+This means, aggressions can have a row on WATCHES, and go into the database, without there being a follow.
 I will mark resolved when I get the changes to aggression events done.  (Karl)
@@ Line 3,533: / Line 3,534: @@
 ==== Karl's comment ====
-Stevan, this is supposed to be fixed in the transition from the <code>raw</code> schema to the <code>tidy</schema>.  At least this is where the date and time intervals are normalized.
+Stevan, this is supposed to be fixed in the transition from the <code>raw</code> schema to the <code>tidy</code> schema.  At least this is where the date and time intervals are normalized.
 Maybe it got missed, or just needed bringing up as an issue.  No real point in fixing it in MS Access.  Doing so feels like losing information, and possibly being inconsistent in the way time intervals are normalized.
 == * (#97) There are GROOMINGS rows that have direction values that do not match to mappable values. ==
@@ Line 3,711: / Line 3,711: @@
 ORDER BY gb.grm_fol_date, gb.grm_fol_b_focal_animid, gb.grm_b_partner_animid;
 </pre>
+== * (#101) There are AGGRESSION_EVENT rows that have NULL focals, a NULL ae_fol_b_focal_id value ==
+=== Problem ===
+There are  144 aggression_event rows that have NULL for the focal.
+=== Bad data ===
+<pre>
+SELECT *
+  FROM easy.aggression_event
+  WHERE ae_fol_b_focal_id IS NULL;
+</pre>
+=== Solution ===
+Change these values to the unknown individual, <code>UNK</code>.
+This means that these rows are converted to WATCHES rows with a Type of <code>B</code>, and <code>UNK</code> as the focal.
+The data needs to be reviewed to see if this is appropriate.  See also problem #102.
+== * (#102) There are AGGRESSION_EVENT rows that have NULL times, a NULL ae_time value ==
+=== Problem ===
+There are  107 aggression_event rows that have NULL for the time.
+Note that all of these rows also have NULL for the focal, problem #101.
+=== Bad data ===
+<pre>
+SELECT *
+  FROM easy.aggression_event
+  WHERE ae_time IS NULL;
+</pre>
+=== Solution ===
+Change these values to the unknown time, midnight, <code>00:00</code>.
+These need to be reviewed to ensure the change is appropriate.
+Meeting discussions indicate the many, ideally all, of these rows are the result of perusal of a book's content, where aggressions were mentioned but these aggressions did not otherwise appear in the data.

@@ Line 2,209: / Line 2,209: @@
 you run the risk of future data entry using a bunch of odd or outdated people.
-== * (#76) There are AGGRESSION_EVENT rows where the Actor/Actee are not in the biography table. ==
+== (#76) There are AGGRESSION_EVENT rows where the Actor/Actee are not in the biography table. ==
 === Problem ===
@@ Line 2,286: / Line 2,286: @@
 === SOLUTION ===
+ICG fixed in Access 7/7/2026. replaced "group", "males", etc with UNK. Fixed bad IDs. Updated comments field to reflect original entry terms.
-ALLOW FOR NOW. NEED TO HAVE A LARGER DISCUSSION ABOUT HOW TO DEAL WITH 'MALES', 'GROUP', ETC
 ==== Karl comment ====

@@ Line 2,341: / Line 2,341: @@
 Change the NULL values to the empty string in the clean schema.
-== * (#79) There are AGGRESSION_EVENT participants outside their valid study participation window in the biography data. ==
+== (#79) There are AGGRESSION_EVENT participants outside their valid study participation window in the biography data. ==
 === Problem ===
@@ Line 2,423: / Line 2,423: @@
 === SOLUTION ===
+ICG FIXED IN ACCESS 7/6/2026
-ALLOW FOR NOW. IAN TO FIX
 ==== Karl comment ====
@@ Line 2,484: / Line 2,484: @@
 === SOLUTION ===
+MOST ARE NULL VALUES BECAUSE THEY WERE 'BOOK EXTRACTS'. ASSIGN A 'DUMMY' TIME?
-ALLOW FOR NOW. IAN OR VOLUNTEER TO FIX BY HAND
+ICG FIXED THE FEW THAT WERE NOT BETWEEN 4:00 AND 20:00 IN ACCESS 7/6/2026

@@ Line 2,015: / Line 2,015: @@
 The usual solution is to have the unknown individual, <code>UNK</code>, be the target.  If NULL has some other special meaning, other than we don't know at whom the aggression was directed, we could make up another "special" <code>BIOGRAPHY_DATA</code> row, similar to the <code>UNK</code>, <code>MGF</code>, <code>MGM</code>, etc. rows.
+Yes, use UNK.
 == * (#74) There are AGGRESSION_EVENT event flags (multiple columns) that have values other than X or NULL. ==
@@ Line 2,559: / Line 2,561: @@
 There seems to be only a problem because NULL values exist.  The other non-conformant data seems to be gone from MS Access.  So make a fight category code that is "no data" and clean up the data in the <code>clean</code> schema.
+NULL means unrated
 == * (#83) There are AGGRESSION_EVENT rows where the Actor and Actee have the same animal ID. ==
@@ Line 2,627: / Line 2,631: @@
 How about not converting this data and letting people who want it get it from the <code>clean</code> schema?
+do not convert these rows, fix them later
 == * (#84) Some follows have a community with trailing spaces ==

@@ Line 2,621: / Line 2,621: @@
 === SOLUTION ===
 ALLOW FOR NOW. EVENTUALLY A VOLUNTEER CAN CHECK AND FIX
+==== Karl's comment ====
+If we allow, we allow for all event types, not just aggressions.  Groomings, matings, etc would all allow "self-dealing".  I'd rather not.
+How about not converting this data and letting people who want it get it from the <code>clean</code> schema?
 == * (#84) Some follows have a community with trailing spaces ==
@@ Line 2,642: / Line 2,648: @@
 KARL TO FIX
+Fix by trimming spaces in the <code>clean</code> schema.
 == * (#85) There are numerous instances of local food names that translate to multiple scientific food names. ==
@@ Line 2,674: / Line 2,682: @@
 === SOLUTION ===
 ALLOW FOR NOW, BUT NEEDS TO BE DISCUSSED AS A PI GROUP
+==== Karl's comment ====
+To allow, do some hackery when constructing the support tables.  Make non-unique values unique by adding extra "duplicate #1 --" sort of text into the value of the column.  Either "by hand" in the conversion code or by way of some algorythm.
 == * (#86) There are numerous instances in the food_part_lookup that that conflate name, initial, and/or the english translation. ==
@@ Line 3,026: / Line 3,038: @@
 ALLOW FOR NOW. NEED TO CHECK WHETHER LEGIT NEW FOODS OR JUST POOR HANDWRITING, SPELLING, ETC
+==== Karl's comments ====
+Ok.  Allowing means adding a bunch of odd values, that could continue to be used in the future until the data is cleaned and the "extra" codes removed.
 == * (#89) There is a food_bout row that has both a compound fb_fpl_local_food_part value and where fb_fpl_local_food_part2 is not null. ==
@@ Line 3,208: / Line 3,224: @@
 ALLOW FOR NOW. NEED TO HAND CHECK AND DECIDE ON PROTOCOL
+==== Karl's comments ====
+Stevan can chime in here, but I think this means adding more illegitimate "legitimate" codes.
 == * (#91) There are food_bout rows that have fb_begin_feed_time > fb_end_feed_time. ==
@@ Line 3,266: / Line 3,287: @@
 === SOLUTION ===
 NEED TO LOOK AT THE SOURCE OF THE PROBLEM B/C NO-ONE ENTERS FEEDING FROM B-REC NOTES.
+==== Karl's comments ====
+I think it makes sense to normalize the data in the clean schema, upper casing and removing spaces.  And then deal with what's left.
 == * (#93) Need to address how we treat behaviours for the second value of food parts AND how they are derived. ==
@@ Line 3,419: / Line 3,444: @@
 </pre>
-=== SOLUTION ==
+=== SOLUTION ===
 GET STEVAN TO EXPLAIN TO ICG AND ELO
@@ Line 3,468: / Line 3,493: @@
 === SOLUTION ===
 ALLOW FOR NOW. IAN TO FIX
+==== Karl's comment ====
+See remarks on problem #81.
 == * (#95) There is at least one food_bout rows where the start or stop time has a seconds value. ==
@@ Line 3,492: / Line 3,521: @@
 IAN (HOPEFULLY) FIXED IN ACCESS 7/3
+==== Karl's comment ====
+Stevan, this is supposed to be fixed in the transition from the <code>raw</code> schema to the <code>tidy</schema>.  At least this is where the date and time intervals are normalized.
+Maybe it got missed, or just needed bringing up as an issue.  No real point in fixing it in MS Access.  Doing so feels like losing information, and possibly being inconsistent in the way time intervals are normalized.