SDI

#SDI
# SUSENAS 2017


require(foreign)
require(dplyr)
require(reshape2)
require(stringi)

provinsi<-read.csv('https://raw.githubusercontent.com/pr4ka5a/Wilayah-Administratif-Indonesia/master/csv/provinces.csv', header=FALSE, stringsAsFactors=FALSE)
kabupaten<-read.csv('https://raw.githubusercontent.com/pr4ka5a/Wilayah-Administratif-Indonesia/master/csv/regencies.csv',header=FALSE, stringsAsFactors=FALSE)
names(provinsi) <- paste(c('R101','provinsi'))
provinsi$provinsi <- stri_trans_totitle(provinsi$provinsi)
names(kabupaten) <- paste(c('R102','R101','kabupaten'))
kabupaten$kabupaten <- stri_trans_totitle(kabupaten$kabupaten)
kabupaten[c(87,80,153,391,394),]$kabupaten <- paste(c('Kota Dumai', 'Kota Siak', 'Kota Batam', 'Kabupaten ToliToli', 'Kabupaten Tojo UnaUna' ))
kabupaten <- merge(provinsi, kabupaten, by='R101', all=TRUE)


x<-(read.dbf('kor17ind_a_diseminasi.dbf')[c(1:4,11,28,88,127,128)])
y <- read.dbf('kor17ind_b_diseminasi.dbf')[,c(1:4,21:25,135,136)]
merge(	, by.x=c('URUT','R101','R102','R105'), by.y=c('URUT','R101','R102','R105'),all=T)

# xx<-(cbind(R407 = x[order(x$URUT),]$R407,y[order(y$URUT),]))
xx<-(cbind(x[order(x$URUT),c(5,6,7)],y[order(y$URUT),]))

xxx <- xx[ xx$R407 %in% c(15:20),]


R301<-(data.frame(table(xxx$URUT)))
xxx<-merge(xxx, R301, by.x='URUT', by.y='Var1')

merge()

xxx$EXP_RT <- xxx$EXP_CAP * xxx$Freq

# Lower <=2jt
# Mid1 >2jt - 5 jt
# Mid2 >5jt - 10 jt
# Upper >10jt

xxx$NSEC<-ifelse( xxx$EXP_RT < 2000001, paste0('Lower'),
ifelse( xxx$EXP_RT < 5000001, paste0('Mid1'),
ifelse( xxx$EXP_RT < 10000001, paste0('Mid2'),paste0('Upper'))))

xxx$R102 <- paste0(xxx$R101,sprintf('%02d',xxx$R102))
xx$R102 <- paste0(xx$R101,sprintf('%02d',xx$R102))

# populasi usia 1520 berbanding jumlah kabupaten
yy<-merge(
	aggregate(FWT ~ R101+R102, xx, sum),
	aggregate(FWT ~ R101+R102,xxx,sum),
	by=c('R101','R102'),
	all.x=T
	)

yy$persen1520<-100*yy[,4]/yy[,3]

# 1520 SEC --> kelas ekonomi individu  usia 1520

dcast(aggregate(FWT ~ R101+R102+NSEC, xxx, sum),R101+R102 ~ NSEC)

z<-merge(
aggregate(FWT ~ R101+R102, xxx, sum),
dcast(aggregate(FWT ~ R101+R102+NSEC, xxx, sum),R101+R102 ~ NSEC),
by=c('R101','R102'),
all=T
)

# z$persen<-100*z[,5]/z[,3]
# zJml <- dcast(z, R101+R102 ~ NSEC , value.var='FWT.y')
# zPersen <- dcast(z, R101+R102 ~ NSEC , value.var='persen')


# usia 1520 merokok menurut NSEC
zz<-merge(
aggregate(FWT ~ R101+R102, xxx, sum),
dcast(aggregate(FWT ~ R101+R102+NSEC, xxx[ xxx$R1006 == 1,], sum),R101+R102 ~ NSEC),
by=c('R101','R102'),
all=T
)
# zz$persen<-100*zz[,5]/zz[,3]
# zzJml <- dcast(zz, R101+R102 ~ NSEC , value.var='FWT.y')
# zzPersen <- dcast(zz, R101+R102 ~ NSEC , value.var='persen')


#usia 1520 bekerja, sekolah
zzz<-merge(
aggregate(FWT ~ R101+R102, xxx, sum),
dcast(aggregate(FWT ~ R101+R102+R802, xxx , sum),R101+R102 ~ R802),
by=c('R101','R102'),
all=T)

#usia menurut jenjang pendidikan dari R802 yang bersekolah
xxx$jenjang <- ifelse( is.na(x$R515), NA,
	       ifelse(xxx$R515 %in% c(1:14), paste0('Pendidikan dasar/menengah (sd/smp/sma sederajat)'), paste0('Pendidikan Tinggi (diploma/s1/s2)')))
	       
yyy<-merge(
aggregate(FWT ~ R101+R102, xxx, sum),
dcast(aggregate(FWT ~ R101+R102+jenjang, xxx[ x$R802 == 2,] , sum),R101+R102 ~ jenjang),
by=c('R101','R102'),
all=T
)

write.csv(merge(
	kabupaten,
	yy,
	by=c('R101','R102'),
	all=T),
'01072019_SDI_populasi_1520.csv',row.names=F)
write.csv(merge(
	kabupaten,
	yyy,
	by=c('R101','R102'),
	all=T),
'01072019_SDI_1520_menurut_jenjang_pendidikan.csv',row.names=F)
write.csv(merge(
	kabupaten,
	z,
	by=c('R101','R102'),
	all=T),
'01072019_SDI_1520_kelas_ekonomi.csv',row.names=F)
write.csv(merge(
	kabupaten,
	zz,
	by=c('R101','R102'),
	all=T),
'01072019_SDI_1520_kelas_ekonomi_merokok.csv',row.names=F)
write.csv(merge(
	kabupaten,
	zzz,
	by=c('R101','R102'),
	all=T),
'01072019_SDI_1520_bekerja_sekolah_lainnya.csv',row.names=F)