Data Wrangling for Avg. # of Physical Therapy Appointments Post-Surgery

[Drew Nelson]

Hi all,

I am looking to find the average number of physical therapy appointments post-surgery for several types of surgery. My end results would be an average number of physical therapy appointments (counting both physical therapy evals and physical therapy) by Appointment_Type but only for surgeries (total shoulder, total hip, total knee, etc.).

Here's an example of my data set:

ID Appointment_Type
1 Total Knee (Surgery)
1 Physical Therapy Evaluation (PTE)
1 Physical Therapy Follow-up (PTFU)
1 PTFU
1 PTFU
2 Total Hip (Surgery)
2 PTE
2 PTFU
2 PTFU
2 PTFU
2 PTE*
2 PTFU
3 Total Shoulder (Surgery)
3 PTE
3 PTFU
3 PTFU
3 PTFU
3 PTFU
3 Total Hip* (Surgery)
3 PTE
3 PTFU

I am running into two issues. First, a patient may have multiple physical therapy evaluations. Only one of the evaluations is related to the surgical episodes. The other evaluations and their subsequent follow-ups should not be counted. So, for example, ID # 2 had total hip surgery, then had a physical therapy evaluation, followed by three physical therapy follow-ups. ID #2 then had a physical therapy evaluation for a completly different issue that is unrelated to the total hip surgery. For ID#2 I only want to count the first physical therapy evaluation and the three physical therapy follow-ups (4 total physical therapy appointments), as the patients' second evaluation is unrelated to the total hip surgery.

Second, a patient may have two different surgical episodes. So, for example, ID #3 had total shoulder surgery, one physical therapy evaluation, and 4 physical therapy follow-ups (5 total physical therapy appointments). ID#3 then had total hip surgery, a physical therapy evaluation, and a physical therapy appointment (2 total physical therapy appointments). Although same patient, the physical therapy appointments need to be counted only for their respective surgical episode. If I collapse by ID, I run the risk of counting all of ID#3's physical therapy appointments for the original total shoulder surgery (7 total physical therapy appointments).

I've reshaped these data to wide; however, the two issues mentioned above remain problematic.

Any suggestions wuld be greatly appreciated.

[William Lisowski]

Perhaps this example will start you in a useful direction - reshaping to a wide layout certainly will not.

* Example generated by -dataex-. For more info, type help dataex
clear
input float ID str40 Appointment_Type
1 "Total Knee (Surgery)"             
1 "Physical Therapy Evaluation (PTE)"
1 "Physical Therapy Follow-up (PTFU)"
1 "PTFU"                             
1 "PTFU"                             
2 "Total Hip (Surgery)"              
2 "PTE"                              
2 "PTFU"                             
2 "PTFU"                             
2 "PTFU"                             
2 "PTE*"                             
2 "PTFU"                             
3 "Total Shoulder (Surgery)"         
3 "PTE"                              
3 "PTFU"                             
3 "PTFU"                             
3 "PTFU"                             
3 "PTFU"                             
3 "Total Hip* (Surgery)"             
3 "PTE"                              
3 "PTFU"                             
end

generate at = trim(upper(Appointment_Type))
generate s = strpos(at,"SURGERY")>0
generate e = strpos(at,"PTE")>0
generate t = strpos(at,"PTFU")>0
drop at

sort ID, stable
by ID: generate surg = sum(s)
sort ID surg, stable
by ID surg: generate pt = sum(e)

list, sepby(ID surg pt) noobs
collapse (firstnm) Appointment_Type (sum) e t if pt<2, by(ID surg)
list, noobs

. list, sepby(ID surg pt) noobs

  +----------------------------------------------------------------+
  | ID                    Appointment_Type   s   e   t   surg   pt |
  |----------------------------------------------------------------|
  |  1                Total Knee (Surgery)   1   0   0      1    0 |
  |----------------------------------------------------------------|
  |  1   Physical Therapy Evaluation (PTE)   0   1   0      1    1 |
  |  1   Physical Therapy Follow-up (PTFU)   0   0   1      1    1 |
  |  1                                PTFU   0   0   1      1    1 |
  |  1                                PTFU   0   0   1      1    1 |
  |----------------------------------------------------------------|
  |  2                 Total Hip (Surgery)   1   0   0      1    0 |
  |----------------------------------------------------------------|
  |  2                                 PTE   0   1   0      1    1 |
  |  2                                PTFU   0   0   1      1    1 |
  |  2                                PTFU   0   0   1      1    1 |
  |  2                                PTFU   0   0   1      1    1 |
  |----------------------------------------------------------------|
  |  2                                PTE*   0   1   0      1    2 |
  |  2                                PTFU   0   0   1      1    2 |
  |----------------------------------------------------------------|
  |  3            Total Shoulder (Surgery)   1   0   0      1    0 |
  |----------------------------------------------------------------|
  |  3                                 PTE   0   1   0      1    1 |
  |  3                                PTFU   0   0   1      1    1 |
  |  3                                PTFU   0   0   1      1    1 |
  |  3                                PTFU   0   0   1      1    1 |
  |  3                                PTFU   0   0   1      1    1 |
  |----------------------------------------------------------------|
  |  3                Total Hip* (Surgery)   1   0   0      2    0 |
  |----------------------------------------------------------------|
  |  3                                 PTE   0   1   0      2    1 |
  |  3                                PTFU   0   0   1      2    1 |
  +----------------------------------------------------------------+

. collapse (firstnm) Appointment_Type (sum) e t if pt<2, by(ID surg)

. list, noobs

  +----------------------------------------------+
  | ID   surg           Appointment_Type   e   t |
  |----------------------------------------------|
  |  1      1       Total Knee (Surgery)   1   3 |
  |  2      1        Total Hip (Surgery)   1   3 |
  |  3      1   Total Shoulder (Surgery)   1   4 |
  |  3      2       Total Hip* (Surgery)   1   1 |
  +----------------------------------------------+

.

[Drew Nelson]

Thank you, William!