-
Notifications
You must be signed in to change notification settings - Fork 0
/
sparcs.R
117 lines (103 loc) · 5.98 KB
/
sparcs.R
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
library(tidyverse)
library(RSocrata)
library(bnlearn)
library(ggplot2)
## function to download from socrata
sparcs_function <- function(url, year){
# querying for conditions
sparcs_720 <- read.socrata(paste(url,"?APR DRG Code=720", sep = ""))
sparcs_194 <- read.socrata(paste(url,"?APR DRG Code=194", sep = ""))
# save to file
write.csv(sparcs_720, paste("./data/sparcs/septicemia/raw_", year, ".csv", sep = ""))
write.csv(sparcs_194, paste("./data/sparcs/heart_failure/raw_", year, ".csv", sep = ""))
#rbind
sparcs <- rbind(sparcs_720, sparcs_194)
sparcs <- sparcs %>%
filter(hospital_county %in% c("Manhattan", "Kings", "Bronx", "Queens", "Richmond")) %>%
select(hospital_county, facility_name:length_of_stay,apr_drg_code:payment_typology_1,total_charges) %>%
mutate(length_of_stay = as.numeric(length_of_stay),
total_charges_day = round(total_charges/length_of_stay, 2),
year = year) %>%
filter(payment_typology_1 %in% c("Medicare", "Private Health Insurance", " Medicaid", "Blue Cross/Blue Shield", "Self-Pay"))
}
# load additional datasets
catchment_area <- read_csv("./data/nyc_hospitals_thiessen_polygons/hospitals_catchment_area.csv") %>%
select(Hospital.N,System,area_sqmi)
hospital_beds <- read_csv("./data/nyc_hospitals/NYC_hospitals_data.csv") %>%
select(`Hospital Name`, `Number of Beds`)
hospital_info <- inner_join(catchment_area, hospital_beds, by = c("Hospital.N" = "Hospital Name"))
# extract
sparcs_2017_NYC <- sparcs_function("https://health.data.ny.gov/resource/22g3-z7e7.csv", 2017)
sparcs_2016_NYC <- sparcs_function("https://health.data.ny.gov/resource/gnzp-ekau.csv", 2016)
sparcs_2015_NYC <- sparcs_function("https://health.data.ny.gov/resource/82xm-y6g8.csv", 2015)
# summarizing
# sparcs_2017_NYC_summary <- sparcs_2017_NYC %>%
# drop_na() %>%
# group_by(apr_drg_code, facility_name, race, ethnicity, payment_typology_1, apr_severity_of_illness, age_group) %>%
# summarise(patients = n(),
# mean_length_of_stay = mean(length_of_stay),
# mean_total_charges_day = mean(total_charges_day))
# sparcs_2017_NYC_summary <- inner_join(sparcs_2017_NYC_summary, hospital_info, by = c("facility_name" = "Hospital.N"))
# sparcs_2016_NYC_summary <- sparcs_2016_NYC %>%
# drop_na() %>%
# group_by(apr_drg_code, facility_name, race, ethnicity, payment_typology_1, apr_severity_of_illness_description, age_group) %>%
# summarise(patients = n(),
# mean_length_of_stay = mean(length_of_stay),
# mean_total_charges_day = mean(total_charges_day))
# sparcs_2016_NYC_summary <- inner_join(sparcs_2016_NYC_summary, hospital_info, by = c("facility_name" = "Hospital.N"))
# sparcs_2015_NYC_summary <- sparcs_2015_NYC %>%
# drop_na() %>%
# group_by(apr_drg_code, facility_name, race, ethnicity, payment_typology_1, apr_severity_of_illness_description, age_group) %>%
# summarise(patients = n(),
# mean_length_of_stay = mean(length_of_stay),
# mean_total_charges_day = mean(total_charges_day))
# sparcs_2015_NYC_summary <- inner_join(sparcs_2015_NYC_summary, hospital_info, by = c("facility_name" = "Hospital.N"))
# summarizing
sparcs_2017_NYC_summary <- sparcs_2017_NYC %>%
drop_na() %>%
mutate(race = ifelse(ethnicity == "Spanish/Hispanic", "Spanish/Hispanic", race)) %>%
select(hospital_county:age_group, race,length_of_stay:apr_drg_code,apr_severity_of_illness,payment_typology_1,total_charges_day) %>%
group_by(apr_drg_code, facility_name, payment_typology_1, apr_severity_of_illness) %>%
summarize(patients = n(),
mean_length_of_stay = mean(length_of_stay),
mean_total_charges_day = mean(total_charges_day))%>%
inner_join(., hospital_info, by = c("facility_name" = "Hospital.N"))
sparcs_2016_NYC_summary <- sparcs_2016_NYC %>%
drop_na() %>%
mutate(race = ifelse(ethnicity == "Spanish/Hispanic", "Spanish/Hispanic", race)) %>%
select(hospital_county:age_group, race,length_of_stay:apr_drg_code,apr_severity_of_illness_description,payment_typology_1,total_charges_day) %>%
group_by(apr_drg_code, facility_name, payment_typology_1, apr_severity_of_illness_description) %>%
summarize(patients = n(),
mean_length_of_stay = mean(length_of_stay),
mean_total_charges_day = mean(total_charges_day))%>%
inner_join(., hospital_info, by = c("facility_name" = "Hospital.N"))
sparcs_2015_NYC_summary <- sparcs_2015_NYC %>%
drop_na() %>%
mutate(race = ifelse(ethnicity == "Spanish/Hispanic", "Spanish/Hispanic", race)) %>%
select(hospital_county:age_group, race,length_of_stay:apr_drg_code,apr_severity_of_illness_description,payment_typology_1,total_charges_day) %>%
group_by(apr_drg_code, facility_name, payment_typology_1, apr_severity_of_illness_description) %>%
summarize(patients = n(),
mean_length_of_stay = mean(length_of_stay),
mean_total_charges_day = mean(total_charges_day))%>%
inner_join(., hospital_info, by = c("facility_name" = "Hospital.N"))
# Septicemia
sparcs_2017_NYC_720_summary <- sparcs_2017_NYC_summary %>%
filter(apr_drg_code == 720)
sparcs_2016_NYC_720_summary <- sparcs_2016_NYC_summary %>%
filter(apr_drg_code == 720)
sparcs_2015_NYC_720_summary <- sparcs_2015_NYC_summary %>%
filter(apr_drg_code == 720)
# Heart Failure
sparcs_2017_NYC_194_summary <- sparcs_2017_NYC_summary %>%
filter(apr_drg_code == 194)
sparcs_2016_NYC_194_summary <- sparcs_2016_NYC_summary %>%
filter(apr_drg_code == 194)
sparcs_2015_NYC_194_summary <- sparcs_2015_NYC_summary %>%
filter(apr_drg_code == 194)
# writing to csv
write_csv(sparcs_2017_NYC_720_summary, "./data/sparcs/septicemia/summary_2017.csv")
write_csv(sparcs_2016_NYC_720_summary, "./data/sparcs/septicemia/summary_2016.csv")
write_csv(sparcs_2015_NYC_720_summary, "./data/sparcs/septicemia/summary_2015.csv")
write_csv(sparcs_2017_NYC_194_summary, "./data/sparcs/heart_failure/summary_2017.csv")
write_csv(sparcs_2016_NYC_194_summary, "./data/sparcs/heart_failure/summary_2016.csv")
write_csv(sparcs_2015_NYC_194_summary, "./data/sparcs/heart_failure/summary_2015.csv")