Find time-overlap in data

[Arto Arman]

Hi everybody,

I'm looking at data from a prison, where we have cell ids, seat id, entry data and time and exit date and time. Some of the cells were single cells, while others were doubles, but not all double cells had both beds occupied at all times.

For my analysis, I think it is important to control for people that had a mate in their cell. Thus, I'd like to find a code on how disentangle the date and time and find the overlapping time frames in the same cell. Is there a way to do this without having to check every cell manually?

The code looks the following way:

Thanks a lot for your help!
Best,
Arto

[Hemanshu Kumar]

Arto Arman as advised in the Statalist FAQ, it is not particularly helpful to have screenshots of your data. It is vastly more useful to have a data extract using the -dataex- command. Could you please provide that?

Also please clarify how to identify different individuals in a cell. Is a person uniquely identified by the combination of cell_ID and seat? So the "mate" would have the same cell but different seat?

[Arto Arman]

Hi! I did not post a dataex example, since I found it not to be very useful, but I'll post it below. To uniquely identify an individual, we have a unique ID (guestooID).

* Example generated by -dataex-. For more info, type help dataex
clear
input str36 guestooID float cell_id str1 seat int trial_date_start double trial_time_start int trial_date_end double trial_time_end
"c2dd8543-b268-436d-8f98-cd22f7d798b0"  1 "A" 22728 -1.8933804e+12 22729      -1893413776000
"0df3424b-1e2c-40fc-a61d-859b7a0400bd"  2 ""  22730  -1.893402e+12 22731      -1893417651000
"4f0d10f7-2d5b-4a41-8eb0-6bc6a43ac253"  3 "F" 22728 -1.8933804e+12 22729      -1893417881000
"8a1b2674-630c-4f1b-aec8-f1486f30c364"  3 "F" 22729 -1.8933984e+12 22730      -1893378671000
"079d2aa7-6e19-481f-8c48-e4c08ac85293"  4 "F" 22730  -1.893402e+12 22731      -1893434305000
"a3ff7188-ffb2-4f7c-90ce-cd8f91413419"  4 "T" 22728 -1.8934092e+12 22729 -1893416449999.9998
"ab97b653-ff03-499d-a99d-4dace2ab6b65"  5 "F" 22729 -1.8933732e+12 22730      -1893389192000
"f671cb78-b75d-445c-bbd1-01cb7ac837f8"  5 "T" 22728 -1.8933804e+12 22729      -1893413817000
"aac02d56-75bd-4c70-ba39-93c9ad1e0f81"  5 "T" 22729 -1.8934092e+12 22730      -1893425010000
"c70fd87f-e508-4f9a-a7ed-6abe86fc38f8"  5 "T" 22730 -1.8934056e+12 22731      -1893431026000
"44c1d449-2341-4c2f-ae9d-235814804901"  6 "B" 22728 -1.8934056e+12 22729      -1893418382000
"c5187c59-9e1e-4354-aaf1-a2981b41d62d"  6 "F" 22728 -1.8933732e+12 22729      -1893387647000
"63be0eb9-0dac-4348-875c-2d9ab41f83ca"  7 "F" 22728 -1.8934092e+12 22729      -1893414906000
"c42168a9-e248-4907-9551-509cde628377"  7 "T" 22728 -1.8933804e+12 22729 -1893409919000.0002
"8277fc33-bb74-46a7-948b-c6679abb5b95"  7 "T" 22730 -1.8934056e+12 22731      -1893416854000
"88e35522-9e7c-4cb5-9e9f-635f184a3d01"  8 "F" 22729  -1.893402e+12 22730      -1893414946000
"a0e29416-970a-4bc1-af2e-0acb3ff66709"  8 "F" 22730 -1.8934056e+12 22731      -1893416674000
"f1245474-e8cf-4ac3-a314-8dd53e710ca5"  8 "T" 22729 -1.8934056e+12 22731 -1893433312999.9998
"bdf0ef61-4a92-4e09-ae39-1dbb3fe27cb9"  9 "F" 22729   -1.89342e+12 22730      -1893397286000
"41f1b571-bea7-4c8f-af12-e2977aae377a"  9 "T" 22728 -1.8933876e+12 22729      -1893422985000
"073a03e5-cfda-4b4d-aa5f-f135b79275cf"  9 "T" 22729  -1.893384e+12 22730 -1893421919999.9998
"9aabeab0-aab6-49a6-abde-960480f7d1a7" 10 "F" 22728 -1.8933876e+12 22729 -1893424670999.9998
"387a83a0-7f02-422e-ad20-a9119c67a57a" 10 "F" 22729   -1.89342e+12 22730      -1893377931000
"bb18208c-3e31-459d-904d-930afe92bd87" 10 "T" 22729 -1.8934236e+12 22730      -1893407193000
"576e8ee2-6d4f-4675-9320-767562a16fa5" 11 "F" 22728 -1.8933876e+12 22729      -1893421072000
"4eac6445-c15f-4ea6-8851-1561f7e28cdf" 11 "F" 22729 -1.8934164e+12 22731      -1893416328000
"240f89b8-2f09-4ebe-8891-7ffe76467193" 11 "T" 22729 -1.8933984e+12 22730      -1893399106000
"a3a55c85-de95-4235-b674-37d00ae72106" 12 "F" 22728 -1.8933876e+12 22729      -1893421796000
"1e267ef7-5755-41e1-852e-14d78a7d046d" 12 "F" 22729 -1.8933984e+12 22730      -1893394330000
"63accfbd-f6a6-4abd-94cf-29eb415a3e20" 13 "F" 22728 -1.8934056e+12 22729      -1893417231000
"0879a7be-623f-46dc-9aa9-e72e9713e68d" 13 "F" 22730  -1.893402e+12 22731 -1893431014999.9998
"b7d47543-efae-45a2-8837-576a9b01fa6b" 13 "T" 22728 -1.8933732e+12 22729      -1893425128000
"8592f2ad-fb6f-4251-81ab-2cdcff972b7b" 13 "T" 22730 -1.8934092e+12 22731      -1893423042000
"111ebd91-9765-4deb-b2a3-15a186f7796a" 14 "F" 22729 -1.8934236e+12 22730 -1893426041000.0002
"329501ff-a975-411f-b5c2-e94616cf8458" 14 "T" 22728 -1.8933804e+12 22729      -1893414834000
"15c8df7b-60c6-46c5-904c-cbcac7971c1e" 14 "T" 22730 -1.8934344e+12 22731      -1893417288000
"a0c96572-eb9a-4f15-a90d-09e1cdc111a2" 15 "F" 22728 -1.8933876e+12 22729 -1893423403000.0002
"8f170267-3323-4e65-ab8b-ecc5d8cdc787" 15 "F" 22729 -1.8934164e+12 22730      -1893416459000
"0486a28a-b26d-49d0-b0f7-2358f491bbee" 15 "T" 22728  -1.893402e+12 22729 -1893405194000.0002
"a61f8f26-1a30-4319-84c8-c2f57b0bdd46" 15 "T" 22730  -1.893438e+12 22730      -1893399531000
"3f992ec3-a0d8-49f3-ad6b-759e00eb2280" 16 "T" 22730 -1.8934236e+12 22731 -1893427249999.9998
"e22eeca5-7a30-4c46-bc5e-8b22d78aa5d2" 17 "F" 22728 -1.8934056e+12 22729 -1893407890000.0002
"4a029b29-1e4b-4866-9248-d4ba4bf4cf3c" 17 "F" 22729 -1.8933948e+12 22730 -1893415038000.0002
"f91b4053-7f56-4d4e-98c8-7990c9ce29ca" 17 "F" 22730 -1.8934056e+12 22730      -1893375687000
"5d22a107-39c4-4c0b-a29d-3f75ab4358d8" 17 "T" 22729  -1.893438e+12 22730      -1893377344000
"7ede517f-2752-4183-a78d-0a79a43ff74c" 18 "F" 22728 -1.8933804e+12 22729      -1893410147000
"bd8ffb0c-13a0-4008-9af6-39bfd0e91448" 18 "F" 22729 -1.8933768e+12 22731      -1893419660000
"d0ab8203-2a3e-40f9-8239-ac3c35713d2f" 18 "T" 22729 -1.8933876e+12 22729      -1893371180000
"c8434dd2-d12c-4d2d-b85f-c6f6f36d82d3" 18 "T" 22730 -1.8934308e+12 22731      -1893425618000
"295e9a7a-6a25-4b9d-bbfb-18428773e15c" 19 "F" 22729 -1.8934416e+12 22729 -1893378403999.9998
"4f532b80-6028-4406-9136-59c471b2cf57" 19 "F" 22730 -1.8933912e+12 22731      -1893423409000
"191d4ee6-8f5c-426c-9132-e7c0f51cd101" 19 "T" 22729 -1.8934272e+12 22731      -1893433008000
"794b0f8e-6f7e-428e-a202-1b73307c083e" 20 "F" 22729 -1.8934128e+12 22730      -1893425164000
"aa8c4a49-b0e1-41fa-bc85-ffb0c49f4a7b" 20 "T" 22728  -1.893384e+12 22729      -1893378046000
"e3af2968-b069-485c-8725-2a09f4b705f9" 20 "T" 22730  -1.893438e+12 22731      -1893427654000
"b7411c15-dbdd-43e3-8122-3737a2fae7f0" 21 "F" 22728 -1.8933804e+12 22729 -1893399829000.0002
"4277db97-38cc-41a6-beca-7304da03e01a" 21 "F" 22730 -1.8934236e+12 22731      -1893426791000
"1d76a66b-758c-4c5c-9319-1bd00398f20d" 21 "T" 22728 -1.8933768e+12 22729      -1893402827000
"8750b7e5-ff8a-4181-bd22-8eadb7187f17" 22 "F" 22728 -1.8933984e+12 22730 -1893419845000.0002
"b1b6233e-b58e-493f-b153-a873c493c0e1" 22 "T" 22728 -1.8933876e+12 22729      -1893421356000
"46159dea-61bc-4c69-b376-76a6116f264b" 22 "T" 22729 -1.8933912e+12 22730      -1893438176000
"8e2e84c6-84a4-437c-b467-c7185db098e0" 23 "F" 22729  -1.893402e+12 22730      -1893374595000
"8b2c2666-a848-4e16-8df5-90837bc7eef0" 23 "T" 22730 -1.8934308e+12 22731      -1893434054000
"601e22ad-e371-4058-812f-00501a4ebb17" 24 "F" 22728 -1.8933948e+12 22729      -1893413206000
"72f98cbd-2d43-468b-94f6-a090d5905ec7" 24 "F" 22729 -1.8934128e+12 22730      -1893420911000
"22b46d7e-4ba9-4311-bd93-3cc4d6bd276a" 24 "F" 22730 -1.8934056e+12 22731 -1893427873999.9998
"ab25a7db-dbcd-4513-a6dd-147f10a3b273" 24 "T" 22730  -1.893438e+12 22731      -1893420294000
"585787cc-afee-4e22-b831-858dd05c4481" 25 "T" 22729 -1.8934344e+12 22729      -1893411036000
"6caa8043-1e98-495f-9e21-5686e7f71093" 26 "F" 22729  -1.893384e+12 22730      -1893415275000
"f212cf00-969f-4d23-a3ef-d890cd2f85a8" 26 "T" 22729  -1.893384e+12 22730      -1893414346000
"f4cc680c-7f96-472d-b2fb-ccc0c85d62a0" 27 "F" 22728 -1.8933876e+12 22729      -1893420763000
"cc077e2c-b688-4fc2-998e-bd2344c29e44" 27 "F" 22729 -1.8934092e+12 22730      -1893412298000
"ae62c516-5289-4209-aa3b-c4a2643ad918" 27 "F" 22730 -1.8934056e+12 22731 -1893428343000.0002
"32e855f3-212b-442c-98fb-78234bbe6aee" 27 "T" 22728 -1.8933876e+12 22729      -1893428929000
"b7d1a92a-7eaf-4528-bca5-c9ee22cfaaf4" 27 "T" 22730 -1.8934236e+12 22731      -1893428392000
"1da5a184-675b-41b5-be9e-9f74d01dbb46" 28 "F" 22728 -1.8933876e+12 22729      -1893421510000
"1407b5cb-6e1f-4822-82ca-ccee9cb993c2" 28 "T" 22729 -1.8933948e+12 22730 -1893400440999.9998
"cbb0ec4d-cb66-4154-8c50-9ed1e7d8e802" 29 "F" 22728 -1.8933876e+12 22729      -1893420279000
"9bd9da26-7d00-4971-86ca-b5c663884822" 29 "T" 22728 -1.8934092e+12 22729      -1893391507000
"557ae543-4705-4937-90d4-801e14130b2a" 29 "T" 22729 -1.8933732e+12 22730      -1893393339000
"de5c5148-6a39-4c13-ac85-7871537ee8c8" 30 "F" 22728 -1.8933876e+12 22729      -1893423089000
"d19a141f-0821-48ac-985d-bf423d436551" 31 "T" 22729  -1.893402e+12 22730 -1893404006999.9998
"f65772d7-b30b-4def-994b-74c5d27e82a1" 32 "T" 22729 -1.8934236e+12 22729      -1893370428000
"650cf39d-c0bd-4c22-a9f4-f67cce36b3dd" 32 "T" 22730 -1.8934236e+12 22731      -1893417339000
"b4419366-d2a6-48e6-ae6b-a33b159954d2" 33 "F" 22729  -1.893438e+12 22730 -1893420768999.9998
"70be3444-85ad-4b90-809f-b6096fd5f9a0" 33 "T" 22728 -1.8933876e+12 22729      -1893421680000
"31e99718-8afd-4cb8-b3b5-1d97111483c9" 34 "F" 22729 -1.8933984e+12 22730      -1893394553000
"9db02b22-b2c4-40c8-811e-569657c2d691" 34 "T" 22729  -1.893402e+12 22729      -1893390704000
"355262f7-ae08-483d-9a23-fe1210e55b72" 34 "T" 22729 -1.8933804e+12 22730      -1893408780000
"a2b135dc-e2a0-4961-8589-a07c290a924c" 35 "F" 22728  -1.893384e+12 22729 -1893390674999.9998
"60fc1061-8f7d-4630-a2d0-83d329f1ab8a" 35 "T" 22729 -1.8933804e+12 22730 -1893401222000.0002
"7255192e-f408-4c6a-bcc9-b95c76ca5060" 36 "T" 22729  -1.893402e+12 22729      -1893384199000
"321e56c7-863c-4ff9-9105-ad947dbaf802" 36 "T" 22730 -1.8934344e+12 22730 -1893400717999.9998
"8daecfec-023e-4476-b3d4-cac08048683e" 37 "F" 22728 -1.8933804e+12 22729      -1893417323000
"0389d724-d4cb-45ab-817f-839c5dcbcafd" 37 "F" 22729 -1.8933768e+12 22730      -1893412052000
"589e5986-08e6-4af1-b791-ed306380d032" 37 "T" 22729 -1.8933732e+12 22730      -1893394096000
"b125c24d-4fd7-48d9-9ec3-5096309a390a" 38 "T" 22728 -1.8933768e+12 22729 -1893416285999.9998
"62a3cec8-c5fd-4159-b7cb-cb5d4d34a908" 39 "F" 22728 -1.8933876e+12 22729 -1893427194999.9998
"cd8f3e16-4baf-4b87-b5d1-e5b446dcbf01" 39 "F" 22730 -1.8934308e+12 22731      -1893416043000
"68986e39-be33-429a-8b0b-ebcf32501d12" 39 "T" 22728 -1.8933876e+12 22729 -1893432892000.0002
end
format %tdnn/dd/CCYY trial_date_start
format %tcHH:MM:SS trial_time_start
format %tdnn/dd/CCYY trial_date_end
format %tcHH:MM:SS trial_time_end

[Hemanshu Kumar]

I'm not sure sure what shape or form you want your data in, but here is some code that should help you get what you need:

egen int person = group(guestooID) // just to get a simpler identifier to work with
isid cell_id person

gen double trial_start = cofd(trial_date_start) + trial_time_start + (tc(01jan1960 00:00:00)-tc(31dec1899 00:00:00))
gen double trial_end = cofd(trial_date_end) + trial_time_end + (tc(01jan1960 00:00:00)-tc(31dec1899 00:00:00))
format %tc trial_start trial_end
drop guestooID seat trial_date_* trial_time_*

preserve
    rename person mate
    rename (trial_start trial_end) mate_=
    tempfile mates
    save `mates'
restore

joinby cell_id using `mates'

replace mate_trial_start = . if mate == person
replace mate_trial_end = . if mate == person

gen double overlap_duration = cond(mate!=person,max(min(trial_end,mate_trial_end) - max(trial_start,mate_trial_start),0),.)
replace overlap_duration = overlap_duration / (1000*60*60) // convert milliseconds into hours
format overlap_duration %5.2f

bys person cell_id: gen num_mates = _N-1
drop if mate == person & num_mates > 0
replace mate = . if mate == person
drop num_mates

gsort person -overlap_duration
order person mate

which produces, for the first ten persons:

. li if person < 11, noobs sepby(person) ab(20)
  +--------------------------------------------------------------------------------------------------------------------------------+
  | person   mate   cell_id          trial_start            trial_end     mate_trial_start       mate_trial_end   overlap_duration |
  |--------------------------------------------------------------------------------------------------------------------------------|
  |      1     35        37   25mar2022 22:00:00   26mar2022 12:12:28   25mar2022 23:00:00   26mar2022 17:11:44              13.21 |
  |      1     56        37   25mar2022 22:00:00   26mar2022 12:12:28   24mar2022 21:00:00   25mar2022 10:44:37               0.00 |
  |--------------------------------------------------------------------------------------------------------------------------------|
  |      2     62        15   24mar2022 15:00:00   25mar2022 14:06:45   24mar2022 19:00:00   25mar2022 09:03:16              14.05 |
  |      2     58        15   24mar2022 15:00:00   25mar2022 14:06:45   25mar2022 11:00:00   26mar2022 10:59:01               3.11 |
  |      2     67        15   24mar2022 15:00:00   25mar2022 14:06:45   26mar2022 05:00:00   26mar2022 15:41:09               0.00 |
  |--------------------------------------------------------------------------------------------------------------------------------|
  |      3     81         9   25mar2022 20:00:00   26mar2022 09:28:00   25mar2022 10:00:00   26mar2022 16:18:34              13.47 |
  |      3     24         9   25mar2022 20:00:00   26mar2022 09:28:00   24mar2022 19:00:00   25mar2022 09:10:15               0.00 |
  |--------------------------------------------------------------------------------------------------------------------------------|
  |      4     66         4   26mar2022 15:00:00   27mar2022 06:01:35   24mar2022 13:00:00   25mar2022 10:59:10               0.00 |
  |--------------------------------------------------------------------------------------------------------------------------------|
  |      5     51        13   26mar2022 15:00:00   27mar2022 06:56:25   26mar2022 13:00:00   27mar2022 09:09:18              15.94 |
  |      5     40        13   26mar2022 15:00:00   27mar2022 06:56:25   24mar2022 14:00:00   25mar2022 10:46:09               0.00 |
  |      5     78        13   26mar2022 15:00:00   27mar2022 06:56:25   24mar2022 23:00:00   25mar2022 08:34:32               0.00 |
  |--------------------------------------------------------------------------------------------------------------------------------|
  |      6      .         2   26mar2022 15:00:00   27mar2022 10:39:09                    .                    .                  . |
  |--------------------------------------------------------------------------------------------------------------------------------|
  |      7     19        14   25mar2022 09:00:00   26mar2022 08:19:18   24mar2022 21:00:00   25mar2022 11:26:06               2.44 |
  |      7      9        14   25mar2022 09:00:00   26mar2022 08:19:18   26mar2022 06:00:00   27mar2022 10:45:12               2.32 |
  |--------------------------------------------------------------------------------------------------------------------------------|
  |      8     12        28   25mar2022 17:00:00   26mar2022 15:25:59   24mar2022 19:00:00   25mar2022 09:34:50               0.00 |
  |--------------------------------------------------------------------------------------------------------------------------------|
  |      9      7        14   26mar2022 06:00:00   27mar2022 10:45:12   25mar2022 09:00:00   26mar2022 08:19:18               2.32 |
  |      9     19        14   26mar2022 06:00:00   27mar2022 10:45:12   24mar2022 21:00:00   25mar2022 11:26:06               0.00 |
  |--------------------------------------------------------------------------------------------------------------------------------|
  |     10     16        19   25mar2022 08:00:00   27mar2022 06:23:12   25mar2022 04:00:00   25mar2022 21:33:16              13.55 |
  |     10     31        19   25mar2022 08:00:00   27mar2022 06:23:12   26mar2022 18:00:00   27mar2022 09:03:11              12.39 |
  +--------------------------------------------------------------------------------------------------------------------------------+

[Arto Arman]

My word, that's fantastic! Thanks you so much, that is a very big help!
Best,
Arto