Loading Data

choose.files()

## [1] "D:\\dataextractdumai\\buban bojonegoro\\data_lama\\2016.csv"

setwd("D://dataextractdumai//buban bojonegoro//data_lama")
library(stats)
library(dplyr)
library(tidyr)
library(tidyverse)
library(ggplot2)
library(kableExtra)
library(lubridate)

Membuat dataframe pertahun pembebanan

Tahun pembebanan 2016 s/d 2017

df2016 <- read.csv("2016.csv", sep = ";") %>% 
  rename(SEP = No.SJP, Poli = Poli.Tuj.SJP, Nokapst = No.KA.Pst, 
         Jkpst = Jk.Pst, Nmtkp = Nm.TKP, DU = Kd.Diag.Primer,
         NmDU = Nm.Diag.Primer, DS = Diag.Sekunder, Kdinacbgs = Kd.Ina.CB.Gs, 
         Nmcmg = Nm.CMG, Nminacbgs = Nm.Ina.CB.Gs, Nmjnsplg = Nm.Jns.Pulang, 
         Tgldtgsjp = Tgl.Dtg.SJP, Tglplgsjp = Tgl.Plg.SJP,
         Tglpelayanan = Tgl.Pelayanan, Tglstjkeu = Tgl.Stj.Keu, 
         Biayaverifikasi = Biaya.Verifikasi) %>% 
  select(SEP, Poli, Nokapst, Jkpst, Nmtkp, Kdinacbgs, Nmcmg, Nminacbgs, DU, 
         NmDU, DS, Procedure, Nmjnsplg, Tgldtgsjp, Tglplgsjp,  Tglpelayanan, 
         Tglstjkeu, Umur, Biayaverifikasi)
df2017 <- read.csv("2017.csv", sep = ";") %>% 
  rename(SEP = No.SJP, Poli = Poli.Tuj.SJP, Nokapst = No.KA.Pst, 
         Jkpst = Jk.Pst, Nmtkp = Nm.TKP, DU = Kd.Diag.Primer,
         NmDU = Nm.Diag.Primer, DS = Diag.Sekunder, Kdinacbgs = Kd.Ina.CB.Gs, 
         Nmcmg = Nm.CMG, Nminacbgs = Nm.Ina.CB.Gs, Nmjnsplg = Nm.Jns.Pulang, 
         Tgldtgsjp = Tgl.Dtg.SJP, Tglplgsjp = Tgl.Plg.SJP,
         Tglpelayanan = Tgl.Pelayanan, Tglstjkeu = Tgl.Stj.Keu, 
         Biayaverifikasi = Biaya.Verifikasi) %>% 
  select(SEP, Poli, Nokapst, Jkpst, Nmtkp, Kdinacbgs, Nmcmg, Nminacbgs, DU, 
         NmDU, DS, Procedure, Nmjnsplg, Tgldtgsjp, Tglplgsjp,  Tglpelayanan, 
         Tglstjkeu, Umur, Biayaverifikasi)
df1617 <- rbind(df2016, df2017) %>%
  rename(Biaya = Biayaverifikasi)

df1617$Tglstjkeu <- as.Date(df1617$Tglstjkeu, format = "%d/%m/%Y")
df1617$Tglpelayanan <- as.Date(df1617$Tglpelayanan, format = "%d/%m/%Y")
df1617$Tgldtgsjp <- as.Date(df1617$Tgldtgsjp, format = "%d/%m/%Y")
df1617$Tglplgsjp <- as.Date(df1617$Tglplgsjp, format = "%d/%m/%Y")

head(df1617, n = 10)%>%
kbl(caption = "Data Klaim Tingkat Lanjut") %>%
  kable_classic_2(full_width = F)

Data Klaim Tingkat Lanjut
SEP	Nokapst	Jkpst	Nmtkp	Kdinacbgs	Nmcmg	Nminacbgs	DU	NmDU	DS	Procedure	Nmjnsplg	Tgldtgsjp	Tglplgsjp	Tglpelayanan	Tglstjkeu	Umur	Biaya
0202R00401160000001	79941702	Perempuan	RITL	O-6-13-I	Deleiveries Groups	PERSALINAN VAGINAL RINGAN	O631	Prolonged second stage (of labour)		7279 - Other vacuum extraction	Sehat	2016-01-01	2016-01-02	2016-01-01	2016-02-23	34	1830600
0202R00401160000002	73422994	Perempuan	RITL	K-4-18-I	Digestive system Groups	DIAGNOSIS SISTEM PENCERNAAN LAIN-LAIN (RINGAN)	K30	Dyspepsia			Sehat	2016-01-01	2016-01-03	2016-01-01	2016-02-23	53	3032300
0202R00401160000004	98536791	Perempuan	RITL	K-4-17-I	Digestive system Groups	NYERI ABDOMEN & GASTROENTERITIS LAIN-LAIN (RINGAN)	A09	Diarrhoea and gastroenteritis of presumed infectious origin			Sehat	2016-01-01	2016-01-02	2016-01-01	2016-02-23	37	2121700
0202R00401160000005	101587912	Perempuan	RITL	K-4-18-I	Digestive system Groups	DIAGNOSIS SISTEM PENCERNAAN LAIN-LAIN (RINGAN)	K30	Dyspepsia			Sehat	2016-01-01	2016-01-05	2016-01-01	2016-02-23	49	3537700
0202R00401160000006	31801194	Perempuan	RITL	A-4-14-I	Infectious & parasitic diseases Groups	PENYAKIT INFEKSI BAKTERI DAN PARASIT LAIN-LAIN RINGAN	A010	Typhoid fever			Sehat	2016-01-01	2016-01-05	2016-01-01	2016-02-23	23	2522200
0202R00501160000002	15495023	Laki-laki	RITL	K-4-17-I	Digestive system Groups	NYERI ABDOMEN & GASTROENTERITIS LAIN-LAIN (RINGAN)	A09	Diarrhoea and gastroenteritis of presumed infectious origin			Sehat	2016-01-01	2016-01-03	2016-01-01	2016-02-23	2	2423100
0202R00501160000003	32088570	Laki-laki	RITL	I-4-12-I	Cardiovascular system Groups	KEGAGALAN JANTUNG RINGAN	I110	Hypertensive heart disease with (congestive) heart failure	K30 - Dyspepsia		Sehat	2016-01-01	2016-01-03	2016-01-01	2016-02-23	57	5338800
0202R00501160000005	98536813	Perempuan	RITL	A-4-13-II	Infectious & parasitic diseases Groups	INFEKSI NON BAKTERI SEDANG	A91	Dengue haemorrhagic fever	R579 - Shock, unspecified		Sehat	2016-01-01	2016-01-05	2016-01-01	2016-02-23	8	2326600
0202R00501160000006	25173558	Perempuan	RITL	K-4-11-II	Digestive system Groups	GASTRITIS & ULKUS PEPTIKUM SEDANG	K291	Other acute gastritis	E785 - Hyperlipidaemia, unspecified; E148 - Unspecified diabetes mellitus with unspecified complications		Sehat	2016-01-01	2016-01-04	2016-01-01	2016-02-23	60	3259300
0202R00501160000007	75108672	Perempuan	RITL	O-6-13-I	Deleiveries Groups	PERSALINAN VAGINAL RINGAN	O809	Single spontaneous delivery, unspecified			Sehat	2016-01-01	2016-01-03	2016-01-01	2016-02-23	33	2416400

Tahun pembebanan 2018 s/d Okt 2020 (karena beda format data)

df1 <- read.csv("Sheet_1_Full_Data_data jan 18.csv", sep = ",")
df2 <- read.csv("Sheet_1_Full_Data_data feb 18.csv", sep = ",")
df3 <- read.csv("Sheet_1_Full_Data_data mar 18.csv", sep = ",")
df4 <- read.csv("Sheet_1_Full_Data_data Apr 18.csv", sep = ",")
df5 <- read.csv("Sheet_1_Full_Data_data mei 18.csv", sep = ",") %>%
  rename(Diagsekunder = ï..Diagsekunder, ï..Tglstjkeu = Tglstjkeu)
df6 <- read.csv("Sheet_1_Full_Data_data jun 18.csv", sep = ",")  %>%
  rename(Diagsekunder = ï..Diagsekunder, ï..Tglstjkeu = Tglstjkeu)
df7 <- read.csv("Sheet_1_Full_Data_data jul 18.csv", sep = ",")
df8 <- read.csv("Sheet_1_Full_Data_data ags 18.csv", sep = ",")
df9 <- read.csv("Sheet_1_Full_Data_data sep 18.csv", sep = ",")
df10 <- read.csv("Sheet_1_Full_Data_data oct 18.csv", sep = ",")
df11 <- read.csv("Sheet_1_Full_Data_data nov 18.csv", sep = ",")
df12 <- read.csv("Sheet_1_Full_Data_data des 18.csv", sep = ",")
df13 <- read.csv("Sheet_1_Full_Data_data jan 19.csv", sep = ",")
df14 <- read.csv("Sheet_1_Full_Data_data feb 19.csv", sep = ",") %>%
  rename(Diagsekunder = ï..Diagsekunder, ï..Tglstjkeu = Tglstjkeu)
df15 <- read.csv("Sheet_1_Full_Data_data mar 19.csv", sep = ",")
df16 <- read.csv("Sheet_1_Full_Data_data apr 19.csv", sep = ",")
df17 <- read.csv("Sheet_1_Full_Data_data mei 19.csv", sep = ",")
df18 <- read.csv("Sheet_1_Full_Data_data Juli 19.csv", sep = ",")
df19 <- read.csv("Sheet_1_Full_Data_data ags 19.csv", sep = ",")
df20 <- read.csv("Sheet_1_Full_Data_data sept 19.csv", sep = ",")
df21 <- read.csv("Sheet_1_Full_Data_data Oct 19.csv", sep = ",")
df22 <- read.csv("Sheet_1_Full_Data_data nov 19.csv", sep = ",")
df23 <- read.csv("Sheet_1_Full_Data_data des 19.csv", sep = ",")
df24 <- read.csv("Sheet_1_Full_Data_data jan 20.csv", sep = ",")
df25 <- read.csv("Sheet_1_Full_Data_data Februari.csv", sep = ",")
df26 <- read.csv("Sheet_1_Full_Data_data maret.csv", sep = ",")
df27 <- read.csv("Sheet_1_Full_Data_data april.csv", sep = ",")
df28 <- read.csv("Sheet_1_Full_Data_data mei.csv", sep = ",")
df29 <- read.csv("Sheet_1_Full_Data_data juni.csv", sep = ",")
df30 <- read.csv("Sheet_1_Full_Data_data Juli.csv", sep = ",")
df31 <- read.csv("Sheet_1_Full_Data_data ags.csv", sep = ",")
df32 <- read.csv("Sheet_1_Full_Data_data sep20.csv", sep = ",") %>%
  rename(Diagsekunder = ï..Diagsekunder, ï..Tglstjkeu = Tglstjkeu)

df1820 <- rbind(df1, df2, df3, df4, df5, df6, df7, df8, df9, df10, df11, df12, 
                df13, df14, df15, df16, df17, df18, df19, df20, df21, df22, df23,
                df24, df25, df26, df27, df28, df29, df30, df31, df32) %>%
  rename(Tglstjkeu = ï..Tglstjkeu, SEP = Nosjp, Poli = Politujsjp,
         DU = Kddiagprimer, NmDU = Nmdiagprimer, DS = Diagsekunder,
         Nmjnsplg = Nmjnspulang) %>% 
  select(SEP, Poli, Nokapst, Jkpst, Nmtkp, Kdinacbgs, Nmcmg, Nminacbgs, DU, 
         NmDU, DS, Procedure, Nmjnsplg, Tgldtgsjp, Tglplgsjp, Tglpelayanan,
         Tglstjkeu, Umur, Biayaverifikasi) %>%
  rename(Biaya = Biayaverifikasi)

df1820$Tglstjkeu <- as.Date(df1820$Tglstjkeu, format = "%m/%d/%Y")
df1820$Tgldtgsjp <- as.Date(df1820$Tgldtgsjp, format = "%m/%d/%Y")
df1820$Tglplgsjp <- as.Date(df1820$Tglplgsjp, format = "%m/%d/%Y")
df1820$Tglpelayanan <- as.Date(df1820$Tglpelayanan, format = "%m/%d/%Y")

head(df1820, n = 10)%>%
kbl(caption = "Data Klaim Tingkat Lanjut") %>%
  kable_classic_2(full_width = F)

Data Klaim Tingkat Lanjut
SEP	Poli	Nokapst	Jkpst	Nmtkp	Kdinacbgs	Nmcmg	Nminacbgs	DU	NmDU	DS	Procedure	Nmjnsplg	Tgldtgsjp	Tglplgsjp	Tglpelayanan	Tglstjkeu	Umur	Biaya
1304R00111170007851	URO	15483877	Perempuan	RJTL	Q-5-41-0	Ambulatory GroupsEpisodic	PENYAKIT AKUT BESAR LAINLAIN	N133	Other and unspecified hydronephrosis			Sehat	2017-11-20	2017-11-20	2017-11-01	2018-01-31	55	292500
0202R00511170000697		15483877	Perempuan	RITL	K-4-11-I	Digestive system Groups	GASTRITIS ULKUS PEPTIKUM RINGAN	K297	Gastritis unspecified	I10 - Essential (primary) hypertension		Sehat	2017-11-10	2017-11-16	2017-11-01	2018-01-31	55	3085200
1302R00311170001663	MAT	40721387	Perempuan	RJTL	Q-5-18-0	Ambulatory GroupsEpisodic	KONSULTASI ATAU PEMERIKSAAN LAINLAIN	Z010	Examination of eyes and vision			Sehat	2017-11-17	2017-11-17	2017-11-01	2018-01-22	71	127800
1305R00111170002203	OBG	25911342	Perempuan	RJTL	Q-5-44-0	Ambulatory GroupsEpisodic	PENYAKIT KRONIS KECIL LAINLAIN	N920	Excessive and frequent menstruation with regular cycle	N840 - Polyp of corpus uteri		Sehat	2017-11-07	2017-11-07	2017-11-01	2018-01-22	43	183300
1305R00111170009509	OBG	25911342	Perempuan	RJTL	Q-5-18-0	Ambulatory GroupsEpisodic	KONSULTASI ATAU PEMERIKSAAN LAINLAIN	Z719	Counselling unspecified	N920 - Excessive and frequent menstruation with regular cycle; N840 - Polyp of corpus uteri		Sehat	2017-11-28	2017-11-28	2017-11-01	2018-01-22	43	142200
1305R00111170002847		25911342	Perempuan	RITL	W-1-11-I	Female reproductive system Groups	PROSEDUR DILATASI KURET INTRAUTERIN SERVIK RINGAN	N840	Polyp of corpus uteri		6909 - Other dilation and curettage	Sehat	2017-11-08	2017-11-10	2017-11-01	2018-01-22	43	3127000
1304R00111170009130	MAT	102051163	Perempuan	RJTL	H-3-12-0	Eye and Adnexa Groups	PROSEDUR LAINLAIN PADA MATA	H524	Presbyopia		9503 - Extended ophthalmologic work-up; 8911 - Tonometry	Sehat	2017-11-25	2017-11-25	2017-11-01	2018-01-31	43	213400
1304R00111170009381	PAR	47364969	Lakilaki	RJTL	Q-5-44-0	Ambulatory GroupsEpisodic	PENYAKIT KRONIS KECIL LAINLAIN	A162	Tuberculosis of lung without mention of bacteriological or histological confirmation			Sehat	2017-11-27	2017-11-27	2017-11-01	2018-01-31	18	183300
1304R00111170008987	PAR	47364969	Lakilaki	RJTL	Q-5-44-0	Ambulatory GroupsEpisodic	PENYAKIT KRONIS KECIL LAINLAIN	Z039	Observation for suspected disease or condition unspecified		8749 - Other chest x-ray	Sehat	2017-11-25	2017-11-25	2017-11-01	2018-01-31	18	183300
1304R00111170009881	JWA	45461474	Lakilaki	RJTL	F-5-14-0	Mental Health and Behavioral Groups	PSIKOTHERAPI INDIVIDU DEWASA BUKAN AKUT	F202	Catatonic schizophrenia			Sehat	2017-11-28	2017-11-28	2017-11-01	2018-01-31	19	196500

Filtering dan membuat variabel baru

Data difilter mulai Bulan Layanan Per Januari 2016

df1620 <- rbind(df1617, df1820) %>%
  subset(Tglpelayanan >= "2016-01-01")
df1620$LOS <- as.integer(df1620$Tglplgsjp) - as.integer(df1620$Tgldtgsjp) +1

df1620$tp <- as.character(trimws(substr(df1620$Kdinacbgs,3,3)), "both")
df1620 <- data.frame(df1620)
df1620 <- df1620 %>% 
            mutate(
              tipe = case_when(
                tp == "1" ~ "Prosedur Rawat Inap",
                tp == "2" ~ "Prosedur Besar Rawat Jalan",
                tp == "3" ~ "Prosedur Signifikan Rawat Jalan",
                tp == "4" ~ "Rawat Inap Bukan Prosedur",
                tp == "5" ~ "Rawat Jalan Bukan Prosedur",
                tp == "6" ~ "Rawat Inap Kebidanan",
                tp == "7" ~ "Rawat Jalan Kebidanan",
                tp == "8" ~ "Rawat Inap Neonatal",
                tp == "9" ~ "Rawat Jalan Neonatal",
                tp == "0" ~ "Error",
                TRUE ~ tp)) %>%
  select(-tp)

df1620$Kdppk <- as.character(trimws(substr(df1620$SEP,1,8)), "both")
df1620 <- df1620 %>% 
            mutate(
              NmFKRTL = case_when(
                Kdppk == "0202R004" ~ "RS Bhayangkara Wahyu Tutuko",
                Kdppk == "0202R011" ~ "RSI Muhammadiyah Sumberrejo",
                Kdppk == "0202R012" ~ "RS Muhammadiyah Kalitidu",
                Kdppk == "0202S001" ~ "KU Muh Kedungadem",
                Kdppk == "0202R016" ~ "RSIA Fatma",
                Kdppk == "0203R005" ~ "RS Muhammadiyah Tuban",
                Kdppk == "0203R007" ~ "RS Medika Mulia",
                Kdppk == "1302R003" ~ "RS NU Tuban",
                Kdppk == "1304R001" ~ "RSUD dr R Sosodoro",
                Kdppk == "1304R002" ~ "RSUD Sumberrejo",
                Kdppk == "1304R003" ~ "RSUD Padangan",
                Kdppk == "1305R001" ~ "RSUD dr R Koesma",
                Kdppk == "0202R005" ~ "RS Aisyiyah",
                Kdppk == "0202R015" ~ "RS MNU Muna Anggita",
                Kdppk == "0203R008" ~ "RSUD Ali Manshur Jatirogo",
                TRUE ~ Kdppk)) %>%
  select(-Kdppk) %>%
  arrange(Tglpelayanan)

head(df1620, n = 20)%>%
kbl(caption = "Data Klaim Tingkat Lanjut") %>%
  kable_classic_2(full_width = F)

Data Klaim Tingkat Lanjut
SEP	Nokapst	Jkpst	Nmtkp	Kdinacbgs	Nmcmg	Nminacbgs	DU	NmDU	DS	Procedure	Nmjnsplg	Tgldtgsjp	Tglplgsjp	Tglpelayanan	Tglstjkeu	Umur	Biaya	LOS	tipe	NmFKRTL
0202R00401160000001	79941702	Perempuan	RITL	O-6-13-I	Deleiveries Groups	PERSALINAN VAGINAL RINGAN	O631	Prolonged second stage (of labour)		7279 - Other vacuum extraction	Sehat	2016-01-01	2016-01-02	2016-01-01	2016-02-23	34	1830600	2	Rawat Inap Kebidanan	RS Bhayangkara Wahyu Tutuko
0202R00401160000002	73422994	Perempuan	RITL	K-4-18-I	Digestive system Groups	DIAGNOSIS SISTEM PENCERNAAN LAIN-LAIN (RINGAN)	K30	Dyspepsia			Sehat	2016-01-01	2016-01-03	2016-01-01	2016-02-23	53	3032300	3	Rawat Inap Bukan Prosedur	RS Bhayangkara Wahyu Tutuko
0202R00401160000004	98536791	Perempuan	RITL	K-4-17-I	Digestive system Groups	NYERI ABDOMEN & GASTROENTERITIS LAIN-LAIN (RINGAN)	A09	Diarrhoea and gastroenteritis of presumed infectious origin			Sehat	2016-01-01	2016-01-02	2016-01-01	2016-02-23	37	2121700	2	Rawat Inap Bukan Prosedur	RS Bhayangkara Wahyu Tutuko
0202R00401160000005	101587912	Perempuan	RITL	K-4-18-I	Digestive system Groups	DIAGNOSIS SISTEM PENCERNAAN LAIN-LAIN (RINGAN)	K30	Dyspepsia			Sehat	2016-01-01	2016-01-05	2016-01-01	2016-02-23	49	3537700	5	Rawat Inap Bukan Prosedur	RS Bhayangkara Wahyu Tutuko
0202R00401160000006	31801194	Perempuan	RITL	A-4-14-I	Infectious & parasitic diseases Groups	PENYAKIT INFEKSI BAKTERI DAN PARASIT LAIN-LAIN RINGAN	A010	Typhoid fever			Sehat	2016-01-01	2016-01-05	2016-01-01	2016-02-23	23	2522200	5	Rawat Inap Bukan Prosedur	RS Bhayangkara Wahyu Tutuko
0202R00501160000002	15495023	Laki-laki	RITL	K-4-17-I	Digestive system Groups	NYERI ABDOMEN & GASTROENTERITIS LAIN-LAIN (RINGAN)	A09	Diarrhoea and gastroenteritis of presumed infectious origin			Sehat	2016-01-01	2016-01-03	2016-01-01	2016-02-23	2	2423100	3	Rawat Inap Bukan Prosedur	RS Aisyiyah
0202R00501160000003	32088570	Laki-laki	RITL	I-4-12-I	Cardiovascular system Groups	KEGAGALAN JANTUNG RINGAN	I110	Hypertensive heart disease with (congestive) heart failure	K30 - Dyspepsia		Sehat	2016-01-01	2016-01-03	2016-01-01	2016-02-23	57	5338800	3	Rawat Inap Bukan Prosedur	RS Aisyiyah
0202R00501160000005	98536813	Perempuan	RITL	A-4-13-II	Infectious & parasitic diseases Groups	INFEKSI NON BAKTERI SEDANG	A91	Dengue haemorrhagic fever	R579 - Shock, unspecified		Sehat	2016-01-01	2016-01-05	2016-01-01	2016-02-23	8	2326600	5	Rawat Inap Bukan Prosedur	RS Aisyiyah
0202R00501160000006	25173558	Perempuan	RITL	K-4-11-II	Digestive system Groups	GASTRITIS & ULKUS PEPTIKUM SEDANG	K291	Other acute gastritis	E785 - Hyperlipidaemia, unspecified; E148 - Unspecified diabetes mellitus with unspecified complications		Sehat	2016-01-01	2016-01-04	2016-01-01	2016-02-23	60	3259300	4	Rawat Inap Bukan Prosedur	RS Aisyiyah
0202R00501160000007	75108672	Perempuan	RITL	O-6-13-I	Deleiveries Groups	PERSALINAN VAGINAL RINGAN	O809	Single spontaneous delivery, unspecified			Sehat	2016-01-01	2016-01-03	2016-01-01	2016-02-23	33	2416400	3	Rawat Inap Kebidanan	RS Aisyiyah
0202R00501160000008	59248507	Perempuan	RITL	J-4-16-II	Respiratory system Groups	SIMPLE PNEUMONIA & WHOOPING COUGH SEDANG	J180	Bronchopneumonia, unspecified	C780 - Secondary malignant neoplasm of lung; C509 - Malignant neoplasm of breast, unspecified		Meninggal	2016-01-01	2016-01-04	2016-01-01	2016-02-23	47	5860100	4	Rawat Inap Bukan Prosedur	RS Aisyiyah
0202R00501160000009	96121488	Laki-laki	RITL	K-4-17-II	Digestive system Groups	NYERI ABDOMEN & GASTROENTERITIS LAIN-LAIN (SEDANG)	A09	Diarrhoea and gastroenteritis of presumed infectious origin	E148 - Unspecified diabetes mellitus with unspecified complications; A169 - Respiratory tuberculosis unspecified, without mention of bacteriological or histological confirmation		Sehat	2016-01-01	2016-01-05	2016-01-01	2016-02-23	52	3991400	5	Rawat Inap Bukan Prosedur	RS Aisyiyah
0202R01101160000001	80461672	Laki-laki	RITL	A-4-13-I	Infectious & parasitic diseases Groups	INFEKSI NON BAKTERI RINGAN	A90	Dengue fever [classical dengue]			Sehat	2016-01-01	2016-01-07	2016-01-01	2016-02-23	13	1751400	7	Rawat Inap Bukan Prosedur	RSI Muhammadiyah Sumberrejo
0202R01101160000002	104322524	Perempuan	RITL	A-4-13-I	Infectious & parasitic diseases Groups	INFEKSI NON BAKTERI RINGAN	A91	Dengue haemorrhagic fever			Sehat	2016-01-01	2016-01-04	2016-01-01	2016-02-23	9	1251000	4	Rawat Inap Bukan Prosedur	RSI Muhammadiyah Sumberrejo
0202R01101160000003	45884742	Perempuan	RITL	W-4-16-II	Female reproductive system Groups	GANGGUAN ANTEPARTUM SEDANG	O141	Severe pre-eclampsia	O809 - Single spontaneous delivery, unspecified; O329 - Maternal care for malpresentation of fetus, unspecified		Sehat	2016-01-01	2016-01-03	2016-01-01	2016-02-23	31	2535200	3	Rawat Inap Bukan Prosedur	RSI Muhammadiyah Sumberrejo
0202R01101160000004	91961503	Laki-laki	RITL	A-4-14-I	Infectious & parasitic diseases Groups	PENYAKIT INFEKSI BAKTERI DAN PARASIT LAIN-LAIN RINGAN	A010	Typhoid fever	J029 - Acute pharyngitis, unspecified		Sehat	2016-01-01	2016-01-05	2016-01-01	2016-02-23	10	3026700	5	Rawat Inap Bukan Prosedur	RSI Muhammadiyah Sumberrejo
0202R01101160000005	103485527	Perempuan	RITL	K-4-17-I	Digestive system Groups	NYERI ABDOMEN & GASTROENTERITIS LAIN-LAIN (RINGAN)	A09	Diarrhoea and gastroenteritis of presumed infectious origin			Sehat	2016-01-01	2016-01-03	2016-01-01	2016-02-23	21	1768100	3	Rawat Inap Bukan Prosedur	RSI Muhammadiyah Sumberrejo
0202R01101160000015	54961596	Perempuan	RITL	J-4-16-I	Respiratory system Groups	SIMPLE PNEUMONIA & WHOOPING COUGH RINGAN	J180	Bronchopneumonia, unspecified			Sehat	2016-01-01	2016-01-04	2016-01-01	2016-02-23	4	3791000	4	Rawat Inap Bukan Prosedur	RSI Muhammadiyah Sumberrejo
0202R01201160000001	9505760	Perempuan	RITL	I-4-17-I	Cardiovascular system Groups	HIPERTENSI RINGAN	I10	Essential (primary) hypertension			Sehat	2016-01-01	2016-01-02	2016-01-01	2016-02-23	73	2894200	2	Rawat Inap Bukan Prosedur	RS Muhammadiyah Kalitidu
0202R01201160000003	27635353	Laki-laki	RITL	I-4-17-II	Cardiovascular system Groups	HIPERTENSI SEDANG	I10	Essential (primary) hypertension	R02 - Gangrene, not elsewhere classified	8622 - Excisional debridement of wound, infection, or burn	Sehat	2016-01-01	2016-01-05	2016-01-01	2016-02-23	61	3923500	5	Rawat Inap Bukan Prosedur	RS Muhammadiyah Kalitidu

glimpse(df1620)

## Rows: 1,526,354
## Columns: 22
## $ SEP          <chr> "0202R00401160000001", "0202R00401160000002", "0202R00...
## $ Poli         <chr> "", "", "", "", "", "", "", "", "", "", "", "", "", ""...
## $ Nokapst      <int> 79941702, 73422994, 98536791, 101587912, 31801194, 154...
## $ Jkpst        <chr> "Perempuan", "Perempuan", "Perempuan", "Perempuan", "P...
## $ Nmtkp        <chr> "RITL", "RITL", "RITL", "RITL", "RITL", "RITL", "RITL"...
## $ Kdinacbgs    <chr> "O-6-13-I", "K-4-18-I", "K-4-17-I", "K-4-18-I", "A-4-1...
## $ Nmcmg        <chr> "Deleiveries Groups", "Digestive system Groups", "Dige...
## $ Nminacbgs    <chr> "PERSALINAN VAGINAL RINGAN", "DIAGNOSIS SISTEM PENCERN...
## $ DU           <chr> "O631", "K30", "A09", "K30", "A010", "A09", "I110", "A...
## $ NmDU         <chr> "Prolonged second stage (of labour)", "Dyspepsia", "Di...
## $ DS           <chr> "", "", "", "", "", "", "K30 - Dyspepsia", "R579 - Sho...
## $ Procedure    <chr> "7279 - Other vacuum extraction", "", "", "", "", "", ...
## $ Nmjnsplg     <chr> "Sehat", "Sehat", "Sehat", "Sehat", "Sehat", "Sehat", ...
## $ Tgldtgsjp    <date> 2016-01-01, 2016-01-01, 2016-01-01, 2016-01-01, 2016-...
## $ Tglplgsjp    <date> 2016-01-02, 2016-01-03, 2016-01-02, 2016-01-05, 2016-...
## $ Tglpelayanan <date> 2016-01-01, 2016-01-01, 2016-01-01, 2016-01-01, 2016-...
## $ Tglstjkeu    <date> 2016-02-23, 2016-02-23, 2016-02-23, 2016-02-23, 2016-...
## $ Umur         <int> 34, 53, 37, 49, 23, 2, 57, 8, 60, 33, 47, 52, 13, 9, 3...
## $ Biaya        <int> 1830600, 3032300, 2121700, 3537700, 2522200, 2423100, ...
## $ LOS          <dbl> 2, 3, 2, 5, 5, 3, 3, 5, 4, 3, 4, 5, 7, 4, 3, 5, 3, 4, ...
## $ tipe         <chr> "Rawat Inap Kebidanan", "Rawat Inap Bukan Prosedur", "...
## $ NmFKRTL      <chr> "RS Bhayangkara Wahyu Tutuko", "RS Bhayangkara Wahyu T...

Preprocessing Data

Data Utilisasi Per Bulan Per FKRTL:

UR_bln <- df1620 %>%
  dplyr::group_by(Tglpelayanan, Nmtkp, NmFKRTL, tipe) %>%
  summarise(Biaya = sum(Biaya), Kasus = n_distinct(SEP),
            Peserta = n_distinct(Nokapst), 
            Unitcost = sum(Biaya/Kasus),
            Frekuensi = Kasus/Peserta)

Contoh Q-5-44-0 di RSUD dr R Sosodoro

UR_bln_q5440 <- df1620 %>%
  subset(NmFKRTL == "RSUD dr R Sosodoro") %>%
  subset(Kdinacbgs == "Q-5-44-0") %>%
  dplyr::group_by(Tglpelayanan, Nmtkp, NmFKRTL) %>%
  summarise(Biaya = sum(Biaya), Kasus = n_distinct(SEP),
            Peserta = n_distinct(Nokapst), 
            Unitcost = sum(Biaya/Kasus),
            Frekuensi = Kasus/Peserta)

ggplot(UR_bln_q5440,
       aes(Tglpelayanan, Kasus, fill = Biaya)) +
  geom_point() +
  geom_smooth(method = "gam", formula = y ~ s(x))

Disease Management

Pattern analysis

Hipertensi rawat jalan di FKRTL Cabang Bojonegoro

UR_bln_HT <- df1620 %>%
  subset(Nmtkp == "RJTL") %>%
  subset(!Poli == "IGD | UGD | HDL" ) %>%
  subset(str_detect(DU, c("I10")) | str_detect(DS, c("I10"))) %>%
  dplyr::group_by(Tglpelayanan, Nmtkp, NmFKRTL) %>%
  summarise(Biaya = sum(Biaya), Kasus = n_distinct(SEP),
            Peserta = n_distinct(Nokapst), 
            Unitcost = sum(Biaya/Kasus),
            Frekuensi = Kasus/Peserta)

ggplot(UR_bln_HT,
       aes(Tglpelayanan, Kasus, fill = Biaya)) +
  geom_line(color = "yellow", size = 1) +
  geom_smooth(method = "gam", formula = y ~ s(x))

DM Tipe 2 rawat jalan di FKRTL Cabang Bojonegoro

UR_bln_DM <- df1620 %>%
  subset(Nmtkp == "RJTL") %>%
  subset(!Poli == "IGD | UGD | HDL" ) %>%
  subset(str_detect(DU, c("E119")) | str_detect(DS, c("E119"))) %>%
  dplyr::group_by(Tglpelayanan, Nmtkp, NmFKRTL) %>%
  summarise(Biaya = sum(Biaya), Kasus = n_distinct(SEP),
            Peserta = n_distinct(Nokapst), 
            Unitcost = sum(Biaya/Kasus),
            Frekuensi = Kasus/Peserta)

ggplot(UR_bln_DM,
       aes(Tglpelayanan, Kasus, fill = Biaya)) +
  geom_point() +
  geom_smooth(method = "gam", formula = y ~ s(x))

Q-5-44-0 pada Time Series

UR_bln_q5440_ts <- ts(UR_bln_q5440 %>%
                  select(Tglpelayanan, Kasus, Biaya,
                         Unitcost, Peserta, Frekuensi),
                start = c(2016,1), end = c(2020,12), frequency = 12)
plot(UR_bln_q5440_ts)

UR_bln_q5440_ts

##          Nmtkp Tglpelayanan Kasus     Biaya Unitcost Peserta Frekuensi
## Jan 2016     1        16801  3890 643406000 165400.0    3009  1.292788
## Feb 2016     1        16832  3866 639436400 165400.0    3023  1.278862
## Mar 2016     1        16861  4295 710393000 165400.0    3285  1.307458
## Apr 2016     1        16892  4365 721971000 165400.0    3279  1.331199
## May 2016     1        16922  4374 723459600 165400.0    3270  1.337615
## Jun 2016     1        16953  4394 726767600 165400.0    3231  1.359950
## Jul 2016     1        16983  3715 614461000 165400.0    2982  1.245808
## Aug 2016     1        17014  4731 782507400 165400.0    3469  1.363794
## Sep 2016     1        17045  4193 693522200 165400.0    3216  1.303794
## Oct 2016     1        17075  4550 767373300 168653.5    3386  1.343768
## Nov 2016     1        17106  3549 650531700 183300.0    2715  1.307182
## Dec 2016     1        17136  3368 617354400 183300.0    2595  1.297881
## Jan 2017     1        17167  3490 639717000 183300.0    2684  1.300298
## Feb 2017     1        17198  3299 604706700 183300.0    2530  1.303953
## Mar 2017     1        17226  3719 681692700 183300.0    2814  1.321606
## Apr 2017     1        17257  3595 658963500 183300.0    2771  1.297366
## May 2017     1        17287  3902 715236600 183300.0    2912  1.339973
## Jun 2017     1        17318  2707 496193100 183300.0    2189  1.236638
## Jul 2017     1        17348  3810 698373000 183300.0    3000  1.270000
## Aug 2017     1        17379  3766 690307800 183300.0    2852  1.320477
## Sep 2017     1        17410  3506 642649800 183300.0    2699  1.299000
## Oct 2017     1        17440  3814 699106200 183300.0    2894  1.317899
## Nov 2017     1        17471  3656 670144800 183300.0    2773  1.318428
## Dec 2017     1        17501  3346 613321800 183300.0    2630  1.272243
## Jan 2018     1        17532  4053 742914900 183300.0    3159  1.283001
## Feb 2018     1        17563  3327 609839100 183300.0    2561  1.299102
## Mar 2018     1        17591  3366 616987800 183300.0    2622  1.283753
## Apr 2018     1        17622  3579 656030700 183300.0    2738  1.307159
## May 2018     1        17652  3446 631651800 183300.0    2712  1.270649
## Jun 2018     1        17683  2469 452567700 183300.0    2132  1.158068
## Jul 2018     1        17713  3757 688658100 183300.0    2993  1.255262
## Aug 2018     1        17744  3433 629268900 183300.0    2710  1.266790
## Sep 2018     1        17775  3256 596824800 183300.0    2604  1.250384
## Oct 2018     1        17805  3249 595541700 183300.0    2570  1.264202
## Nov 2018     1        17836  2962 542934600 183300.0    2387  1.240888
## Dec 2018     1        17866  3127 573179100 183300.0    2519  1.241366
## Jan 2019     1        17897  3668 672344400 183300.0    2896  1.266575
## Feb 2019     1        17928  3157 578678100 183300.0    2594  1.217039
## Mar 2019     1        17956  3403 623769900 183300.0    2685  1.267412
## Apr 2019     1        17987  3578 655847400 183300.0    2794  1.280601
## May 2019     1        18017  3577 656214000 183453.7    2689  1.330234
## Jun 2019     1        18048  2768 507374400 183300.0    2341  1.182401
## Jul 2019     1        18078  4363 799737900 183300.0    3313  1.316933
## Aug 2019     1        18109  3839 703688700 183300.0    3024  1.269511
## Sep 2019     1        18140  4029 738515700 183300.0    3100  1.299677
## Oct 2019     1        18170  3999 733016700 183300.0    3112  1.285026
## Nov 2019     1        18201  3660 670878000 183300.0    2903  1.260765
## Dec 2019     1        18231  3945 723118500 183300.0    3105  1.270531
## Jan 2020     1        18262  4165 763444500 183300.0    3269  1.274090
## Feb 2020     1        18293  3820 700206000 183300.0    3032  1.259894
## Mar 2020     1        18322  3471 636234300 183300.0    2717  1.277512
## Apr 2020     1        18353  2755 504991500 183300.0    2300  1.197826
## May 2020     1        18383  2328 426722400 183300.0    1950  1.193846
## Jun 2020     1        18414  2946 540001800 183300.0    2350  1.253617
## Jul 2020     1        16801  3890 643406000 165400.0    3009  1.292788
## Aug 2020     1        16832  3866 639436400 165400.0    3023  1.278862
## Sep 2020     1        16861  4295 710393000 165400.0    3285  1.307458
## Oct 2020     1        16892  4365 721971000 165400.0    3279  1.331199
## Nov 2020     1        16922  4374 723459600 165400.0    3270  1.337615
## Dec 2020     1        16953  4394 726767600 165400.0    3231  1.359950

Scaling (Normalisasi data)

scale01 <- function(x){
  (x - min(x)) / (max(x) - min(x))
}

UR_bln_Rajal <- UR_bln %>%
  filter(Nmtkp == "RJTL") %>%
  mutate(Kasus = scale01(Kasus), 
         Biaya = scale01(Biaya),
         Unitcost = scale01(Unitcost),
         Peserta = scale01(Peserta),
         Frekuensi = scale01(Frekuensi))

UR_bln_Ranap <- UR_bln %>%
  filter(Nmtkp == "RITL") %>%
  mutate(Kasus = scale01(Kasus), 
         Biaya = scale01(Biaya),
         Unitcost = scale01(Unitcost),
         Peserta = scale01(Peserta),
         Frekuensi = scale01(Frekuensi))

ggplot(UR_bln_Rajal,
       aes(Tglpelayanan)) +
  geom_freqpoly(binwidth = 5)

hist(UR_bln_Rajal$Frekuensi, col = "skyblue3", breaks = 5)

hist(UR_bln_Rajal$Biaya, col = "skyblue3", breaks = 5)

hist(UR_bln_Ranap$Biaya, col = "skyblue3")

ggplot(UR_bln_Ranap,
       aes(Unitcost, fill = Nmtkp)) +
         geom_histogram(binwidth = 0.5)

Kasus_By_bln <- df1620 %>%
  subset(tipe == "Rawat Inap Bukan Prosedur") %>%
  dplyr::group_by(Tglpelayanan, Biaya, NmFKRTL) %>%
  summarise(Unitcost = sum(Biaya)/n_distinct(SEP),
            Kasus = n_distinct(SEP))

Tipe_Kasus_bln <- df1620 %>%
  dplyr::group_by(Tglpelayanan, NmFKRTL, tipe) %>%
  summarise(Biaya = sum(Biaya), Kasus = n_distinct(SEP),
            Unitcost = sum(Biaya)/n_distinct(SEP))

Contoh subset dan visualisasi (misal: RSUD dr R Sosodoro)

# Basic line plot (Jumlah Kasus Rawat Inap Bukan Prosedur)
ggplot(Kasus_By_bln,
       aes(x = Tglpelayanan, y = Biaya, color = NmFKRTL)) +
  geom_point() +
  geom_smooth(method = "gam", formula = y ~ s(x))

# Basic line plot (Trend Unit Cost berdasar Tipe Kasus)
ggplot(Tipe_Kasus_bln,
       aes(x = Tglpelayanan, y = Unitcost, color = tipe)) +
  geom_point() +
  geom_smooth(method = "gam", formula = y ~ s(x))

Prolanis

Dodyk Sukra Goutama

November 05, 2020

Loading Data

Membuat dataframe pertahun pembebanan

Tahun pembebanan 2016 s/d 2017

Tahun pembebanan 2018 s/d Okt 2020 (karena beda format data)

Filtering dan membuat variabel baru

Data difilter mulai Bulan Layanan Per Januari 2016

Preprocessing Data

Data Utilisasi Per Bulan Per FKRTL:

Contoh Q-5-44-0 di RSUD dr R Sosodoro

Disease Management

Pattern analysis

Hipertensi rawat jalan di FKRTL Cabang Bojonegoro

DM Tipe 2 rawat jalan di FKRTL Cabang Bojonegoro

Q-5-44-0 pada Time Series

Scaling (Normalisasi data)

Contoh subset dan visualisasi (misal: RSUD dr R Sosodoro)