Health and Hospitals Dataset

Introduction

Health and Hospitals are seeking to better understand the patients admitted to their hospitals within New York City in order to better serve their representative patient community. The purpose of this study is to bring insight into our patients by better understanding the proposed research areas.

Research Questions

  1. How many patients are in the dataset?
  2. Investigate demographics of the patient population.
  3. How many of the patients died?
  4. How many died taking into account data from vital statistics?
  5. For the patients who died, what was the date of their last encounter/visit? Create a table which lists number of days from last visit date to date of death. Interpret.
  6. Calculate total days spent at the hospital for each patient. Analyze /Interpret.
  7. Investigate inpatient visits (patients who stayed for more than a day at the hospital) for each patient. Output a list of the top 10 patient_ids who spent maximum days in the hospital in the past year (2015).

How many patients are in the dataset?

Within the our data, there are 80 unique patients. 79 of the patients visited the hospital multiple times, 1 patient visted once.

ID count
37026752 922
52263456 679
56819168 547
35764288 460
12137440 448
33221440 425
3435936 409
53420640 366
2205504 280
40905536 251
47221440 244
52898720 242
12134304 233
57565760 230
30041536 205
17997504 201
34603968 181
24810016 180
35001792 171
15095136 159
34207040 151
28653184 150
47483072 148
55738144 145
66704736 144
22300992 138
10709216 136
64927520 130
42485632 101
63040320 99
27421632 94
28438144 94
16293312 85
63941248 85
37790816 82
40160736 76
54848192 75
3847424 68
37209088 63
12613664 60
23665824 60
50986208 59
3950912 57
7193088 57
57631168 57
9774688 55
29779232 55
34861120 54
15150240 45
28990976 40
18739168 39
10087392 38
38659264 37
18837952 35
51996896 34
40674368 30
50413440 27
26523392 26
38058496 24
55047104 24
65186240 20
39541824 18
819392 17
30172352 16
44109184 16
15576288 14
57708896 13
60754176 13
13288576 12
64050112 11
62697600 8
48319264 7
63383264 7
3136000 4
9075808 3
47097792 3
62105792 3
46070080 2
66174752 2
12728352 1

Investigate demographics of the patient population.

Race

Within our data, the patient population is as follows:

The highest frequency of race is ‘Black or African American’, followed by ‘Hispanic’.

Sex

The number of males in our patient population outnumber the number of females - about 6:4.

How many of the patients died?

Out of the 80 patients in our dataset, 4 of them have passed away.
ID
819392
28438144
30041536
30172352

How many died taking into account data from vital statistics?

Looking at data from the vital stats table, vital stats shows that there are 98 unique patients associated with DATE_OF_DEATH. This seems to indicate a difference between the two datasets.
x freq
34720 1
297472 1
819392 1
2738848 1
2800672 1
4337984 1
4626720 1
6223168 1
6674528 1
7339360 1
7453600 1
11111968 1
12356512 1
13554016 1
13752928 1
14058688 1
15150240 1
15181376 1
19353600 1
19401984 1
20039712 1
20292384 1
20718880 1
21542752 1
22386336 1
22495648 1
24629024 1
24663968 1
24803520 1
25332832 1
26154688 1
28283808 1
28438144 1
28653184 1
29722784 1
29973888 1
30041536 1
30172352 1
30311680 1
30972256 1
31249568 1
31458112 1
32354112 1
32739168 1
33104736 1
36042944 1
37472512 1
37511488 1
41756288 1
42370720 1
42519456 1
44109184 1
44648352 1
45427200 1
45721536 1
46219936 1
51375520 1
52633504 1
53067840 1
53331488 1
53420640 1
55521760 1
55682368 1
56538944 1
60000864 1
60040960 1
61997600 1
63383264 1
64368864 1
64906912 1
65664032 1
66123008 1
66242400 1
67152960 1
67378976 1
69495776 1
71635200 1
71929984 1
71936032 1
72760128 1
75103616 1
76259232 1
76640704 1
76682144 1
77370048 1
77373408 1
80459680 1
82681088 1
83874784 1
83918912 1
84279776 1
84284032 1
84789600 1
85254400 1
85384320 1
85620416 1
86567936 1
86708832 1

For the patients who died, what was the date of their last encounter/visit? Create a table which lists number of days from last visit date to date of death. Interpret.

Since the Test_DataCore sheet has the full patient information, I’ve decided to join Test_DataCore_VitalStats to Test_DataCore on the patient ID. This will allow whatever IDs are in Test_DataCore_VitalStats that match Test_DataCore to add the corrosponding DATE_OF_DEATH.

The last Date of the patients discharge date is as shown below and how long since their last discharge date to date of death:
ID ADMISSION_DATE_TIME DISCHARGE_DATE_TIME Death_Date diff_in_days
63383264 2015-01-16 14:20:00 2015-01-16 2015-06-25 160 days
44109184 2015-09-08 12:59:00 2015-09-10 2015-10-29 49 days
53420640 2015-10-31 00:24:00 2015-10-31 2015-12-17 47 days
15150240 2015-10-20 15:50:00 2015-10-30 2015-11-15 16 days
28653184 2015-10-16 01:47:00 2015-10-26 2015-10-30 4 days
819392 2015-07-09 23:40:00 2015-07-10 2015-07-10 0 days
28438144 2015-06-21 02:49:00 2015-07-02 2015-07-02 0 days
30041536 2015-02-16 16:40:00 2015-02-25 2015-02-25 0 days
30172352 2015-09-15 12:45:00 2015-09-17 2015-09-17 0 days

From the data provided, 4 patients died during their last visit, the remaining patients died after discharge from the hospital.

Calculate total days spent at the hospital for each patient. Analyze /Interpret.

The total number of days each patient has stayed in the hospital as shown below. This takes into account each visit (freq) to the hospital, and adds up the total # of days across all visits. For vists that were missing a discharge date, the discharge date was replaced with the admission date - making the assumption that the patient left the same day. (We could have also made the assumption that the patient stayed one day, or the mean # of days.)

With Outliers

ID days freq mean median sd max
819392 50 days 17 2.9 days 1.0 days 5.3 days 22 days
2205504 552 days 280 2.0 days 1.0 days 3.2 days 27 days
3136000 5 days 4 1.2 days 1.0 days 1.5 days 3 days
3435936 650 days 409 1.6 days 0.0 days 4.6 days 62 days
3847424 154 days 68 2.3 days 1.0 days 5.1 days 31 days
3950912 324 days 57 5.7 days 0.0 days 15.7 days 107 days
7193088 120 days 57 2.1 days 1.0 days 4.9 days 33 days
9075808 184 days 3 61.3 days 13.0 days 95.2 days 171 days
9774688 113 days 55 2.1 days 1.0 days 3.3 days 17 days
10087392 88 days 38 2.3 days 1.5 days 2.7 days 11 days
10709216 184 days 136 1.4 days 1.0 days 1.7 days 8 days
12134304 290 days 233 1.2 days 0.0 days 2.4 days 19 days
12137440 1898 days 448 4.2 days 1.0 days 11.5 days 125 days
12613664 76 days 60 1.3 days 0.0 days 1.8 days 7 days
12728352 8 days 1 8.0 days 8.0 days NA 8 days
13288576 10 days 12 0.8 days 0.0 days 2.6 days 9 days
15095136 271 days 159 1.7 days 1.0 days 2.5 days 16 days
15150240 142 days 45 3.2 days 1.0 days 4.4 days 19 days
15576288 27 days 14 1.9 days 2.0 days 1.4 days 5 days
16293312 214 days 85 2.5 days 1.0 days 2.6 days 11 days
17997504 370 days 201 1.8 days 0.0 days 3.2 days 21 days
18739168 111 days 39 2.8 days 1.0 days 4.0 days 19 days
18837952 93 days 35 2.7 days 1.0 days 3.9 days 19 days
22300992 193 days 138 1.4 days 1.0 days 2.0 days 14 days
23665824 89 days 60 1.5 days 0.0 days 3.6 days 24 days
24810016 1 days 180 0.0 days 0.0 days 0.1 days 1 days
26523392 36 days 26 1.4 days 1.0 days 2.2 days 8 days
27421632 3568 days 94 38.0 days 3.0 days 58.5 days 218 days
28438144 296 days 94 3.1 days 1.0 days 6.5 days 50 days
28653184 201 days 150 1.3 days 1.0 days 2.1 days 15 days
28990976 145 days 40 3.6 days 3.0 days 3.3 days 13 days
29779232 258 days 55 4.7 days 2.0 days 6.9 days 30 days
30041536 365 days 205 1.8 days 0.0 days 7.8 days 87 days
30172352 46 days 16 2.9 days 1.0 days 4.3 days 14 days
33221440 564 days 425 1.3 days 0.0 days 2.4 days 21 days
34207040 231 days 151 1.5 days 1.0 days 2.6 days 19 days
34603968 374 days 181 2.1 days 0.0 days 11.6 days 153 days
34861120 136 days 54 2.5 days 0.0 days 5.6 days 27 days
35001792 837 days 171 4.9 days 1.0 days 9.7 days 72 days
35764288 656 days 460 1.4 days 0.0 days 2.8 days 27 days
37026752 19372 days 922 21.0 days 2.0 days 59.8 days 680 days
37209088 148 days 63 2.3 days 0.0 days 5.9 days 30 days
37790816 90 days 82 1.1 days 1.0 days 1.7 days 10 days
38058496 23 days 24 1.0 days 1.0 days 0.4 days 2 days
38659264 42 days 37 1.1 days 0.0 days 2.4 days 13 days
39541824 53 days 18 2.9 days 1.0 days 3.8 days 13 days
40160736 200 days 76 2.6 days 1.0 days 5.1 days 31 days
40674368 34 days 30 1.1 days 1.0 days 1.2 days 5 days
40905536 370 days 251 1.5 days 0.0 days 3.5 days 26 days
42485632 116 days 101 1.1 days 1.0 days 1.6 days 8 days
44109184 37 days 16 2.3 days 0.0 days 3.6 days 10 days
46070080 2 days 2 1.0 days 1.0 days 1.4 days 2 days
47097792 22 days 3 7.3 days 7.0 days 6.5 days 14 days
47221440 251 days 244 1.0 days 0.0 days 8.2 days 126 days
47483072 453 days 148 3.1 days 2.0 days 3.7 days 24 days
48319264 16 days 7 2.3 days 2.0 days 1.6 days 5 days
50413440 131 days 27 4.9 days 2.0 days 6.3 days 23 days
50986208 105 days 59 1.8 days 0.0 days 2.5 days 11 days
51996896 42 days 34 1.2 days 1.0 days 1.4 days 7 days
52263456 4889 days 679 7.2 days 1.0 days 27.9 days 232 days
52898720 200 days 242 0.8 days 0.0 days 1.9 days 16 days
53420640 886 days 366 2.4 days 1.0 days 11.7 days 168 days
54848192 178 days 75 2.4 days 1.0 days 5.3 days 28 days
55047104 280 days 24 11.7 days 3.5 days 17.2 days 68 days
55738144 570 days 145 3.9 days 1.0 days 15.1 days 110 days
56819168 989 days 547 1.8 days 0.0 days 5.1 days 80 days
57565760 197 days 230 0.9 days 1.0 days 1.2 days 13 days
57631168 237 days 57 4.2 days 1.0 days 6.7 days 35 days
57708896 3 days 13 0.2 days 0.0 days 0.4 days 1 days
60754176 31 days 13 2.4 days 1.0 days 2.8 days 10 days
62105792 6 days 3 2.0 days 1.0 days 1.7 days 4 days
62697600 26 days 8 3.2 days 2.5 days 3.2 days 10 days
63040320 109 days 99 1.1 days 0.0 days 1.9 days 8 days
63383264 5 days 7 0.7 days 0.0 days 1.5 days 4 days
63941248 41 days 85 0.5 days 0.0 days 1.3 days 8 days
64050112 10 days 11 0.9 days 0.0 days 2.1 days 7 days
64927520 191 days 130 1.5 days 1.0 days 4.8 days 53 days
65186240 26 days 20 1.3 days 1.0 days 2.0 days 7 days
66174752 2 days 2 1.0 days 1.0 days 1.4 days 2 days
66704736 1602 days 144 11.1 days 9.0 days 10.3 days 62 days

Some patients have large amounts of total days. When looking into the data, some patients are admitted under a single visit for around, or over, a year (example: 37026752, 494558576). This leads one to believe that there may be inaccuraces in the data.

Remove Outliers

One way to solve this is to remove outliers from our dataset. After removing potential outliers, we are left with 9849 rows of data.

Updated Statistics

After the removal of outliers, the below is the updated stats:
ID days freq mean median sd max
819392 50 days 17 2.9 days 1.0 days 5.3 days 22 days
2205504 552 days 280 2.0 days 1.0 days 3.2 days 27 days
3136000 5 days 4 1.2 days 1.0 days 1.5 days 3 days
3435936 650 days 409 1.6 days 0.0 days 4.6 days 62 days
3847424 154 days 68 2.3 days 1.0 days 5.1 days 31 days
3950912 217 days 56 3.9 days 0.0 days 7.8 days 44 days
7193088 120 days 57 2.1 days 1.0 days 4.9 days 33 days
9075808 13 days 2 6.5 days 6.5 days 9.2 days 13 days
9774688 113 days 55 2.1 days 1.0 days 3.3 days 17 days
10087392 88 days 38 2.3 days 1.5 days 2.7 days 11 days
10709216 184 days 136 1.4 days 1.0 days 1.7 days 8 days
12134304 290 days 233 1.2 days 0.0 days 2.4 days 19 days
12137440 1513 days 444 3.4 days 1.0 days 7.3 days 71 days
12613664 76 days 60 1.3 days 0.0 days 1.8 days 7 days
12728352 8 days 1 8.0 days 8.0 days NA 8 days
13288576 10 days 12 0.8 days 0.0 days 2.6 days 9 days
15095136 271 days 159 1.7 days 1.0 days 2.5 days 16 days
15150240 142 days 45 3.2 days 1.0 days 4.4 days 19 days
15576288 27 days 14 1.9 days 2.0 days 1.4 days 5 days
16293312 214 days 85 2.5 days 1.0 days 2.6 days 11 days
17997504 370 days 201 1.8 days 0.0 days 3.2 days 21 days
18739168 111 days 39 2.8 days 1.0 days 4.0 days 19 days
18837952 93 days 35 2.7 days 1.0 days 3.9 days 19 days
22300992 193 days 138 1.4 days 1.0 days 2.0 days 14 days
23665824 89 days 60 1.5 days 0.0 days 3.6 days 24 days
24810016 1 days 180 0.0 days 0.0 days 0.1 days 1 days
26523392 36 days 26 1.4 days 1.0 days 2.2 days 8 days
27421632 685 days 72 9.5 days 1.0 days 18.1 days 62 days
28438144 296 days 94 3.1 days 1.0 days 6.5 days 50 days
28653184 201 days 150 1.3 days 1.0 days 2.1 days 15 days
28990976 145 days 40 3.6 days 3.0 days 3.3 days 13 days
29779232 258 days 55 4.7 days 2.0 days 6.9 days 30 days
30041536 278 days 204 1.4 days 0.0 days 5.0 days 57 days
30172352 46 days 16 2.9 days 1.0 days 4.3 days 14 days
33221440 564 days 425 1.3 days 0.0 days 2.4 days 21 days
34207040 231 days 151 1.5 days 1.0 days 2.6 days 19 days
34603968 221 days 180 1.2 days 0.0 days 2.6 days 16 days
34861120 136 days 54 2.5 days 0.0 days 5.6 days 27 days
35001792 765 days 170 4.5 days 1.0 days 8.3 days 55 days
35764288 656 days 460 1.4 days 0.0 days 2.8 days 27 days
37026752 2997 days 833 3.6 days 1.0 days 6.6 days 71 days
37209088 148 days 63 2.3 days 0.0 days 5.9 days 30 days
37790816 90 days 82 1.1 days 1.0 days 1.7 days 10 days
38058496 23 days 24 1.0 days 1.0 days 0.4 days 2 days
38659264 42 days 37 1.1 days 0.0 days 2.4 days 13 days
39541824 53 days 18 2.9 days 1.0 days 3.8 days 13 days
40160736 200 days 76 2.6 days 1.0 days 5.1 days 31 days
40674368 34 days 30 1.1 days 1.0 days 1.2 days 5 days
40905536 370 days 251 1.5 days 0.0 days 3.5 days 26 days
42485632 116 days 101 1.1 days 1.0 days 1.6 days 8 days
44109184 37 days 16 2.3 days 0.0 days 3.6 days 10 days
46070080 2 days 2 1.0 days 1.0 days 1.4 days 2 days
47097792 22 days 3 7.3 days 7.0 days 6.5 days 14 days
47221440 125 days 243 0.5 days 0.0 days 1.5 days 10 days
47483072 453 days 148 3.1 days 2.0 days 3.7 days 24 days
48319264 16 days 7 2.3 days 2.0 days 1.6 days 5 days
50413440 131 days 27 4.9 days 2.0 days 6.3 days 23 days
50986208 105 days 59 1.8 days 0.0 days 2.5 days 11 days
51996896 42 days 34 1.2 days 1.0 days 1.4 days 7 days
52263456 1613 days 656 2.5 days 0.0 days 7.6 days 67 days
52898720 200 days 242 0.8 days 0.0 days 1.9 days 16 days
53420640 526 days 363 1.4 days 1.0 days 3.4 days 58 days
54848192 178 days 75 2.4 days 1.0 days 5.3 days 28 days
55047104 280 days 24 11.7 days 3.5 days 17.2 days 68 days
55738144 267 days 142 1.9 days 1.0 days 5.2 days 60 days
56819168 909 days 546 1.7 days 0.0 days 3.8 days 43 days
57565760 197 days 230 0.9 days 1.0 days 1.2 days 13 days
57631168 237 days 57 4.2 days 1.0 days 6.7 days 35 days
57708896 3 days 13 0.2 days 0.0 days 0.4 days 1 days
60754176 31 days 13 2.4 days 1.0 days 2.8 days 10 days
62105792 6 days 3 2.0 days 1.0 days 1.7 days 4 days
62697600 26 days 8 3.2 days 2.5 days 3.2 days 10 days
63040320 109 days 99 1.1 days 0.0 days 1.9 days 8 days
63383264 5 days 7 0.7 days 0.0 days 1.5 days 4 days
63941248 41 days 85 0.5 days 0.0 days 1.3 days 8 days
64050112 10 days 11 0.9 days 0.0 days 2.1 days 7 days
64927520 191 days 130 1.5 days 1.0 days 4.8 days 53 days
65186240 26 days 20 1.3 days 1.0 days 2.0 days 7 days
66174752 2 days 2 1.0 days 1.0 days 1.4 days 2 days
66704736 1602 days 144 11.1 days 9.0 days 10.3 days 62 days

Investigate inpatient visits (patients who stayed for more than a day at the hospital) for each patient.

Output a list of the top 10 patient_ids who spent maximum days in the hospital in the past year (2015).

ID VISIT_ID diff_in_days
55047104 1179203021 47 days
37026752 1191859457 42 days
55047104 1164925181 33 days
40160736 1183189890 31 days
54848192 1174526561 28 days
3435936 1164625149 26 days
54848192 1159284225 25 days
23665824 1190948730 24 days
3435936 1171595998 23 days
57631168 1192036489 23 days

Clinic Information

Certin clinics within the dataset appear more often than hours. The top 10 clinics that appeared are shown below, with clinic code ‘0’ having the highest frequency.

Further Investigations

  • Further investigations can include looking at diagnosis by:

  • Totals, deaths, clinics, race, age, sex, etc

  • Time of day admissions and discharges

  • What illnesses affect our patient population the most?

  • What are our busiest times of the day? Are we properly staffed to handle busy times?

Nicholas Schettini

1/26/2019