Last active
November 4, 2019 04:55
-
-
Save kkprakasa/95997047915b1c665c59f95d61f52c6e to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # HNWI | |
| 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) | |
| ind <- read.dbf('./kor18ind_revisi_diseminasi.dbf')[,c(1:3,4,5,6,7,11,49,50,52,79,80,81,82,83,84,98,99,100,101,102,103,113,114,115,116,117,118,28,29,53:61,62,171,172,178)] | |
| ind$R102 <- paste0(ind$R101, sprintf('%02d',ind$R102)) | |
| ruta <- read.dbf('./kor18rt_diseminasi.dbf')[,c(1:3,16,153,157,165:167,169,175,181)] | |
| ruta$R102 <- paste0(ruta$R101, sprintf('%02d',ruta$R102)) | |
| rt2 <- read.dbf('./kor18rt_diseminasi.dbf')[,c('R101','R102','URUT','R1504','R1505','R1701A','R1701B','R1701C','R1701D','R1701E','R1701F','R1701G','R1701H','R1701I','R1701J','R1801M','R1803A','R1803B','R1803C','R1803D')] | |
| rt2$R102 <- paste0(rt2$R101, sprintf('%02d',rt2$R102)) | |
| ruta <- merge( | |
| ruta, rt2, by=c('R101','R102','URUT'), all.x=T | |
| ) | |
| spend42<-read.dbf('blok42.dbf')[,c(1:3,7,9,10,22,17,18)] | |
| spend42 <- spend42[ spend42$KODE %in% c(262,266,302,301,298), ] | |
| spend42$R102 <- paste0(spend42$R101,sprintf('%02d',spend42$R102)) | |
| spend42 <- dcast( spend42, URUT+R101+R102+R105+WEIND+WERT ~ KODE, value.var='B42K5') | |
| #internet susenas maret 2017 | |
| x <- read.dbf('/home/kaka/Documents/datalab/SUSENAS/SUSENAS 2017/Kor 2017/kor17ind_a_diseminasi.dbf')[,c(1:4,80,127,128)] | |
| yn <- c('1'='ya','5'='Tidak') | |
| r613 <- c('1'='Paket A', | |
| '2'='SDLB', | |
| '3'='SD', | |
| '4'='MI', | |
| '5'='Paket B', | |
| '6'='SMP LB', | |
| '7'='SMP', | |
| '8'='MTs', | |
| '9'='Paket C', | |
| '10'='SMLB', | |
| '11'='SMA', | |
| '12'='MA', | |
| '13'='SMK', | |
| '14'='MAK', | |
| '15'='D1/D2', | |
| '16'='D3', | |
| '17'='D4', | |
| '18'='S1', | |
| '19'='S2', | |
| '20'='S3' | |
| ) | |
| #r804 <- c() | |
| r805 <- c('1'='Berusaha sendiri', | |
| '2'='Berusaha dibantu buruh tidak tetap/Buruh tidak dibayar', | |
| '3'='Berusaha dibantu buruh tetap/ buruh dibayar', | |
| '4'='Buruh/karyawan/pegawai', | |
| '5'='Pekerja bebas', | |
| '6'='Pekerja keluarga atau tidak dibayar') | |
| r404 <- c('1'='Belum kawin','2'='Kawin','3'='Cerai hidup','4'='Cerai mati') | |
| r405 <- c('1'='Laki','2'='Perempuan') | |
| r612 <- c('1'='tidak/Belum pernah sekolah','2'='Masih bersekolah','3'='Tidak bersekolah lagi') | |
| r105 <- c('1'='Kota','2'='Desa') | |
| r1801K <- c('1'='Ya','5'='Tidak') | |
| r1801G <- c('1' = 'Ya','5' = 'Tidak') | |
| r1502 <- c('1'='Milik sendiri', | |
| '2'='Kontrak/Sewa', | |
| '3'='Bebas sewa', | |
| '4'='Dinas', | |
| '5'='Lainnya') | |
| r804 <- c('1'='Pertanian, kehutanan, perikanan', | |
| '2'='Pertambangan, Penggalian', | |
| '3'='Industri pengolahan', | |
| '4'='Pengadaan listrik, gas, uap/air panas, udara dingin', | |
| '5'='Pengelolaan air, air limbah, sampah, remediasi', | |
| '6'='Konstruksi', | |
| '7'='Perdagangan besar, eceran, reparasi/perawatan mobil/motor', | |
| '8'='Pengangkutan, pergudangan', | |
| '9'='Penyediaan akomodasi, makan, minum', | |
| '10'='Informasi, komunikasi', | |
| '11'='Aktivitas keuangan, asuransi', | |
| '12'='Real estat', | |
| '13'='Aktivitas profesional, ilmiah, teknis', | |
| '14'='Aktivitas penyewaan, sewa guna usaha tanpa hak opsi, ketenag', | |
| '15'='Administrasi pemerintahan, pertahanan, jaminan sosial wajib', | |
| '16'='Pendidikan', | |
| '17'='Aktivitas kesehatan manusia, sosial', | |
| '18'='Kesenian, hiburan, rekreasi', | |
| '19'='Aktivitas jasa lainnya', | |
| '20'='Aktivitas rumah tangga sebagai pemberi kerja', | |
| '21'='Aktivitas badan internasional, ekstra internasional lainnya') | |
| r802 <- c('1'='Bekerja','2'='Sekolah','3'='Megurusi rumah tangga','4'='Lainnya Selain Keg.Pribadi') | |
| r803 <- c('1'='Ya','5'='Tidak') | |
| r403<-c('1'='Kepala Rumah Tangga', | |
| '2'='Istri/suami', | |
| '3'='Anak kandung/tiri', | |
| '4'='Anak Angkat', | |
| '5'='Menantu', | |
| '6'='Cucu', | |
| '7'='Orang tua/mertua', | |
| '8'='Pembantu/sopir', | |
| '9'='Lainnya (orang yang tdk ada hubungan famili dengan KRT)') | |
| #r707 <- c() | |
| r717 <- c('1'='Ya','5'='Tidak') | |
| #buat kolom rataan perngeluaran per-kapita harian | |
| # ind$spendH <- ind$EXP_CAP/30 | |
| # ind$SECadb<- | |
| # ifelse(ind$spendH < 8480, paste0('Low'), | |
| # ifelse(ind$spendH < 16960, paste0('Middle 1'), | |
| # ifelse(ind$spendH < 42400, paste0('Middle 2'), | |
| # ifelse(ind$spendH <= 84800, paste0('Middle 3'), | |
| # paste0('High'))))) | |
| # ind$R804 <- r804[as.character(ind$R804)] | |
| y$R713 <- yn[ as.character(y$R713)] | |
| y$R714 <- yn[ as.character(y$R714)] | |
| y$R716 <- yn[ as.character(y$R716)] | |
| y$R1505 <- yn[ as.character(y$R1505)] | |
| y$R613 <- r613[as.character(y$R613)] | |
| y$R804 <- r804[as.character(y$R804)] | |
| y$R805 <- r805[as.character(y$R805)] | |
| y$R404 <- r404[as.character(y$R404)] | |
| y$R405 <- r405[as.character(y$R405)] | |
| y$R612 <- r612[as.character(y$R612)] | |
| y$R105 <- r105[as.character(y$R105)] | |
| y$R717 <- r717[as.character(y$R717)] | |
| y$R403 <- r403[ as.character(y$R403)] | |
| y$R802 <- r802[ as.character(y$R802)] | |
| y$R803 <- r803[ as.character(y$R803)] | |
| y$R1801K <- r1801K[as.character(y$R1801K)] | |
| y$R1801G <- r1801G[as.character(y$R1801G)] | |
| y$R1502 <- r1502[as.character(y$R1502)] | |
| y$r1701A<-yn[as.character(y$r1701A)] | |
| y$r1701B<-yn[as.character(y$r1701B)] | |
| y$r1701C<-yn[as.character(y$r1701C)] | |
| y$r1701D<-yn[as.character(y$r1701D)] | |
| y$r1701E<-yn[as.character(y$r1701E)] | |
| y$r1701F<-yn[as.character(y$r1701F)] | |
| y$r1701G<-yn[as.character(y$r1701G)] | |
| y$r1701H<-yn[as.character(y$r1701H)] | |
| y$r1701I<-yn[as.character(y$r1701I)] | |
| y$r1701J<-yn[as.character(y$r1701J)] | |
| y$r1801M<-yn[as.character(y$r1801M)] | |
| #x$R707 <- r707[as.character(x$R707)] | |
| y$R105 <- r105[as.character(y$R105)] | |
| ruta$R105 <- r105[as.character(ruta$R105)] | |
| # x$R105 <- r105[as.character(x$R105)] | |
| spend42$R105 <- r105[as.character(spend42$R105)] | |
| y<-(merge(ind,ruta,by=c('R101','R102','R105','URUT'),all=T)) | |
| gab<-merge( | |
| ruta,spend42, | |
| by=c('R101','R102','R105','URUT'), | |
| all.x=T | |
| ) | |
| gab2 <- merge( | |
| ind, gab, | |
| by=c('R101','R102','R105','URUT'), | |
| all.x=T | |
| ) | |
| gab2 <- merge( | |
| kabupaten,gab2, | |
| by=c('R101','R102'), | |
| All=T | |
| ) | |
| #2017 internet banking | |
| # rentang $PPP 2017 | |
| # < Rp8,380.978 | |
| # Rp8,380.978 - Rp83,809.78 | |
| # Rp8,380.978 - <Rp16,761.956 | |
| # Rp16,761.956 - <Rp41,904.89 | |
| # Rp41,904.89 - Rp83,809.78 | |
| # > Rp83,809.78 | |
| x$spendH<- x$EXP_CAP/30 | |
| x$SEC<- | |
| ifelse(x$spendH < 8381, paste0('Low'), | |
| ifelse(x$spendH < 16762, paste0('Middle 1'), | |
| ifelse(x$spendH < 41905, paste0('Middle 2'), | |
| ifelse(x$spendH <= 83810, paste0('Middle 3'), | |
| paste0('High'))))) | |
| x$R102 <- paste0(x$R101, sprintf('%02d',x$R102)) | |
| write.csv(gab2[ gab2$R102 %in% c('3507','7404'),], 'hnwi_kab.malang_kab.kolaka-rev2.csv', row.names=F) | |
| write.csv(gab2, 'hnwi_p1.csv', row.names=F) | |
| write.csv(x, 'pengguna e_banking_2017.csv', row.names=F) | |
| write.csv(x[ x$R102 %in% c('3507','7404'),], 'pengguna e_banking_2017_malangkab_kolakakab.csv', row.names=F) | |
| # data tambahan blok ketenagakerjaan dan status dalam ruta | |
| x<-read.dbf('./kor18ind_revisi_diseminasi.dbf')[,c(1:3,5,54:60,171,178)] | |
| x$R102 <- paste0(x$R101,sprintf('%02d',x$R102)) | |
| mlgk<-x[ x$R102 %in% c(3507,7404),] | |
| write.csv( | |
| merge( | |
| read.csv('./hnwi_kab.malang_kab.kolaka.csv', stringsAsFactors=F), | |
| mlgk, | |
| by=c("R101","R102","URUT"), | |
| all.x = T | |
| ), | |
| './hnwi_kab.malang_kab.kolaka-rev.csv', | |
| row.names=F) | |
| write.csv( | |
| merge(dcast(aggregate( FWT.x ~ R101+R102+R1801K, rutases[ rutases$SEC == 'High' & rutases$R1502=='Milik sendiri',], sum ),R101+R102 ~ R1801K),aggregate(FWT.x ~ R101+R102, rutases, sum)), | |
| 'data-row2.csv',row.names=F) | |
| uji<-gab2[,c(1:4,7,19,26,28,30,31)] | |
| write.csv( | |
| merge( | |
| kabupaten | |
| ,aggregate(FWT.y ~ R101+R102, uji[ uji$SEC == 'High'& uji$R1801 == 'Ya' & uji$R1502 == 'Milik sendiri' & uji$R403 == 'Kepala Rumah Tangga',], sum) | |
| , by=c('R101','R102') | |
| , all.x =T | |
| ) | |
| , 'data-row4.csv' | |
| , row.names=F | |
| ) | |
| # ambil data rumah tangga | |
| y <- gab2[ !duplicated(gab2$URUT),] | |
| # hitung pengeluaran rumah tangga sebulan dengan perkalian antara pengeluaran per kapita sebulan dengan jumlah anggota rumah | |
| # tangga | |
| y$EXP_RT <- y$EXP_CAP*y$R301 | |
| y10 <- y[ y$EXP_RT >10000000,] | |
| # ifelse( y$EXP_RT > 10000000 & y$R1801K == 'Ya' & y$R1502 == 'Milik Sendiri',paste0('Premium segment'), | |
| # ifelse( y$EXP_RT > 10000000 & y$R1801K != 'Ya' & y$R1502 != 'Milik Sendiri',paste0('Upper 1'), | |
| # ifelse( y$EXP_RT > 5000000 ))) | |
| #NSEC = NielsenSEC | |
| # y$NSEC<-ifelse( y$EXP_RT < 2000001, paste0('Lower'), | |
| # ifelse( y$EXP_RT < 5000001, paste0('Lower'), | |
| # ifelse( y$EXP_RT < 10000001, paste0('Upper3'), | |
| # ifelse( y$EXP_RT > 10000000 & y$R1801K != 'Ya' , paste0('Upper2'), | |
| # ifelse( y$EXP_RT > 10000000 & y$R1801K == 'Ya' & y$R1502 != 'Milik Sendiri',paste0('Upper1'),paste0('Premium segment')))))) | |
| # y | |
| y$NSEC<-ifelse( y$EXP_RT < 2000001, paste0('Lower'), | |
| ifelse( y$EXP_RT < 5000001, paste0('Lower1'), | |
| ifelse( y$EXP_RT < 10000001, paste0('Upper3'), | |
| ifelse( y$EXP_RT > 10000000 & y$R1801K != '1' , paste0('Upper2'), | |
| ifelse( y$EXP_RT > 10000000 & y$R1801K == '1' & y$R1502 != '1',paste0('Upper1'),paste0('Premium segment')))))) | |
| aggregate(FWT.y ~ R101+R102+R105+NSEC , y, sum) | |
| write.csv(merge(kabupaten,dcast(aggregate(FWT.y ~ R101+R102+R105+NSEC , y, sum), R101 +R102 ~ NSEC+R105),by=c('R101','R102')),'190619_hnwi_premium_segment.csv', row.names=F) | |
| ################################################ | |
| ################################################ | |
| ################################################ | |
| require(plyr) | |
| yfin2 <- | |
| merge( | |
| merge( | |
| kabupaten, | |
| dcast(aggregate(FWT.x ~ R101 + R102 + NSEC, y, sum), R101 + R102 ~ NSEC), | |
| by=c('R101','R102'), | |
| all.x=T | |
| ), | |
| merge( | |
| merge( | |
| ddply(y, .(R101,R102), function(x) data.frame( pengeluaran_max_rt = max(x$EXP_RT))), | |
| ddply(y, .(R101,R102), function(x) data.frame( pengeluaran_min_rt = min(x$EXP_RT))), | |
| by=c('R101','R102'), | |
| all =T | |
| ), | |
| merge( | |
| ddply(y, .(R101,R102), function(x) data.frame( pengeluaran_median_rt = weightedMedian(x$EXP_RT, x$FWT.x))), | |
| ddply(y, .(R101,R102), function(x) data.frame( pengeluaran_mean_rt = weightedMean(x$EXP_RT, x$FWT.x))), | |
| by=c('R101','R102'), | |
| all=T | |
| ), | |
| by=c('R101','R102'), | |
| all=T | |
| ), | |
| by=c('R101','R102'), | |
| all.x=T | |
| ) | |
| write.csv( | |
| merge( | |
| yfin2, | |
| aggregate(FWT.x ~ R101+R102, y,sum), | |
| by=c('R101','R102'), | |
| all.x=T), | |
| 'rutases_final_1.csv', | |
| row.names=F | |
| ) | |
| yfin <- merge( | |
| merge( | |
| kabupaten, | |
| merge( | |
| aggregate(FWT.x ~ R101+R102, y10, sum), | |
| aggregate(FWT.x ~ R101+R102, y10[ y10$R1502 == 'Milik sendiri'& y10$R1801K == 'Ya',], sum), | |
| by=c('R101','R102'), | |
| all.x=T | |
| ), | |
| by=c('R101','R102'), | |
| all.x=T | |
| ), | |
| merge( | |
| merge( | |
| ddply(y10, .(R101,R102), function(x) data.frame( pengeluaran_max_rt = max(x$EXP_RT))), | |
| ddply(y10, .(R101,R102), function(x) data.frame( pengeluaran_min_rt = min(x$EXP_RT))), | |
| by=c('R101','R102'), | |
| all =T | |
| ), | |
| merge( | |
| ddply(y10, .(R101,R102), function(x) data.frame( pengeluaran_median_rt = weightedMedian(x$EXP_RT, x$FWT.x))), | |
| ddply(y10, .(R101,R102), function(x) data.frame( pengeluaran_mean_rt = weightedMean(x$EXP_RT, x$FWT.x))), | |
| by=c('R101','R102'), | |
| all=T | |
| ), | |
| by=c('R101','R102'), | |
| all=T | |
| ), | |
| by=c('R101','R102'), | |
| all.x=T | |
| ) | |
| write.csv( | |
| merge( | |
| yfin, | |
| aggregate(FWT.x ~ R101+R102, y, sum), | |
| by=c('R101','R102'), | |
| all.x=T | |
| ), | |
| 'premium_segments_nielsen.csv', | |
| row.names=F) | |
| merge( | |
| # jumlah RT menurut Tipe wilayah | |
| dcast( | |
| aggregate(FWT.x ~ R101+R102+R105.y, y, sum), | |
| R101 + R102 ~ R105.y), | |
| merge( | |
| # Jumlah RT memiliki pengeluaran diatas 10Jt/ bln menurut wilayah | |
| dcast( | |
| aggregate(FWT.x ~ R101+R102+R105.y, y10, sum), | |
| R101 + R102 ~ R105.y), | |
| # Jumlah RT memiliki pengeluaran diatas 10Jt/bln , memiliki rumah sendiri, dan memiliki mobil | |
| dcast( | |
| aggregate(FWT.x ~ R101+R102+R105.y, y10[ y10$R1502 == 'Milik sendiri'& y10$R1801K == 'Ya',], sum), | |
| R101 + R102 ~ R105.y), | |
| by=c('R101','R102'), | |
| all.x=T | |
| ), | |
| by=c('R101','R102'), | |
| all.x=T | |
| ) | |
| write.csv(y[ y$R102 %in% c(3275,6271),c(1:5,44,24,29,31)],'validasi-bekasi-palangka.csv', row.names=F) | |
| gab2<-merge(gab2,y[,c(5,83)], by='URUT',all.x=T) | |
| indSES <- gab2[ gab2$NSEC == 'Premium segment',] | |
| #indSES[,c(1,2,3,4,5,6,8,9,10,11,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,49,50,51,52,57,75,76,77,78,79,80,81,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,53)] | |
| write.csv( | |
| + indSES[,c(1,2,3,4,5,6,8,9,10,11,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,49,50,51,52,57,75,76,77,78,79,80,81,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,53)], | |
| + 'indSES_final_1_min602_603.csv', | |
| + row.names=F) | |
| ######################################## | |
| # gab2 untuk perhitungan individu | |
| require(foreign) | |
| require(dplyr) | |
| require(reshape2) | |
| require(stringi) | |
| provinsi<-read.csv('https://raw.githubusercontent.com/edwardsamuel/Wilayah-Administratif-Indonesia/master/csv/provinces.csv', header=FALSE, stringsAsFactors=FALSE) | |
| kabupaten<-read.csv('https://raw.githubusercontent.com/edwardsamuel/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) | |
| ind <- read.dbf('./kor18ind_revisi_diseminasi.dbf')[,c(1:3,4,5,6,7,11,49,50,52,79,80,81,82,83,84,98,99,100,101,102,103,113,114,115,116,117,118,28,29,53:61,62,171,172,178)] | |
| ind$R102 <- paste0(ind$R101, sprintf('%02d',ind$R102)) | |
| ruta <- read.dbf('./kor18rt_diseminasi.dbf')[,c(1:3,16,153,157,165:167,169,175,181)] | |
| ruta$R102 <- paste0(ruta$R101, sprintf('%02d',ruta$R102)) | |
| rt2 <- read.dbf('./kor18rt_diseminasi.dbf')[,c('R101','R102','URUT','R1504','R1505','R1701A','R1701B','R1701C','R1701D','R1701E','R1701F','R1701G','R1701H','R1701I','R1701J','R1801M','R1803A','R1803B','R1803C','R1803D')] | |
| rt2$R102 <- paste0(rt2$R101, sprintf('%02d',rt2$R102)) | |
| ruta <- merge( | |
| ruta, rt2, by=c('R101','R102','URUT'), all.x=T | |
| ) | |
| spend42<-read.dbf('blok42.dbf')[,c(1:3,7,9,10,22,17,18)] | |
| spend42 <- spend42[ spend42$KODE %in% c(262,266,302,301,298), ] | |
| spend42$R102 <- paste0(spend42$R101,sprintf('%02d',spend42$R102)) | |
| spend42 <- dcast( spend42, URUT+R101+R102+R105+WEIND+WERT ~ KODE, value.var='B42K5') | |
| gab<-merge( | |
| ruta,spend42, | |
| by=c('R101','R102','R105','URUT'), | |
| all.x=T | |
| ) | |
| gab2 <- merge( | |
| ind, gab, | |
| by=c('R101','R102','R105','URUT'), | |
| all.x=T | |
| ) | |
| gab2 <- merge( | |
| kabupaten,gab2, | |
| by=c('R101','R102'), | |
| All=T | |
| ) | |
| gab2$EXP_RT <- gab2$EXP_CAP*gab2$R301 | |
| gab2$NSEC<-ifelse( gab2$EXP_RT < 2000001, paste0('Lower2'), | |
| ifelse( gab2$EXP_RT < 5000001, paste0('Lower1'), | |
| ifelse( gab2$EXP_RT < 10000001, paste0('Upper3'), | |
| ifelse( gab2$EXP_RT > 10000000 & gab2$R1801K != '1' , paste0('Upper2'), | |
| ifelse( gab2$EXP_RT > 10000000 & gab2$R1801K == '1' & gab2$R1502 != '1',paste0('Upper1'),paste0('Premium segment')))))) | |
| # SEC-S.A.C | |
| gab2$NSEC<-ifelse( gab2$EXP_RT < 2000001, paste0('Low'), | |
| ifelse( gab2$EXP_RT < 5000001, paste0('Middle 1'), | |
| ifelse( gab2$EXP_RT < 10000001, paste0('Middle 2'), | |
| ifelse( gab2$EXP_RT > 10000000 & gab2$R1801K != '1' , paste0('Upper'), | |
| ifelse( gab2$EXP_RT > 10000000 & gab2$R1801K == '1' & gab2$R1502 != '1',paste0('Upper'),paste0('Premium segment')))))) | |
| gab2$NSEC<-ifelse( gab2$EXP_RT < 2000001, paste0('Low'), | |
| ifelse( gab2$EXP_RT < 5000001, paste0('Middle 1'), | |
| paste0('Middle Up'))) | |
| gab2$R713 <- yn[ as.character(gab2$R713)] | |
| gab2$R714 <- yn[ as.character(gab2$R714)] | |
| gab2$R716 <- yn[ as.character(gab2$R716)] | |
| gab2$R1505 <- yn[ as.character(gab2$R1505)] | |
| gab2$R613 <- r613[as.character(gab2$R613)] | |
| gab2$R804 <- r804[as.character(gab2$R804)] | |
| gab2$R805 <- r805[as.character(gab2$R805)] | |
| gab2$R404 <- r404[as.character(gab2$R404)] | |
| gab2$R405 <- r405[as.character(gab2$R405)] | |
| gab2$R612 <- r612[as.character(gab2$R612)] | |
| gab2$R105 <- r105[as.character(gab2$R105)] | |
| gab2$R717 <- r717[as.character(gab2$R717)] | |
| gab2$R403 <- r403[ as.character(gab2$R403)] | |
| gab2$R802 <- r802[ as.character(gab2$R802)] | |
| gab2$R803 <- r803[ as.character(gab2$R803)] | |
| gab2$R1801K <- r1801K[as.character(gab2$R1801K)] | |
| gab2$R1801G <- r1801G[as.character(gab2$R1801G)] | |
| gab2$R1502 <- r1502[as.character(gab2$R1502)] | |
| gab2$R1701A<-yn[as.character(gab2$R1701A)] | |
| gab2$R1701B<-yn[as.character(gab2$R1701B)] | |
| gab2$R1701C<-yn[as.character(gab2$R1701C)] | |
| gab2$R1701D<-yn[as.character(gab2$R1701D)] | |
| gab2$R1701E<-yn[as.character(gab2$R1701E)] | |
| gab2$R1701F<-yn[as.character(gab2$R1701F)] | |
| gab2$R1701G<-yn[as.character(gab2$R1701G)] | |
| gab2$R1701H<-yn[as.character(gab2$R1701H)] | |
| gab2$R1701I<-yn[as.character(gab2$R1701I)] | |
| gab2$R1701J<-yn[as.character(gab2$R1701J)] | |
| gab2$R1801M<-yn[as.character(gab2$R1801M)] | |
| write.csv(gab2, '190619-indSES-final.csv', row.names=FALSE) | |
| y <- gab2[ !duplicated(gab2$URUT),] | |
| ps <- y[ y$NSEC == 'Premium segment'] | |
| ps$kat.pengeluaran<- | |
| ifelse(ps$EXP_RT < 15000000, paste0('>10jt-14,99jt'), | |
| ifelse(ps$EXP_RT < 20000000, paste0('15jt-19,99jt'),paste0('>20jt') | |
| )) | |
| ps0<-dcast( | |
| aggregate(FWT.x ~ R101+R102+kat.pengeluaran, ps, sum), | |
| R101+R102 ~ kat.pengeluaran, value.var='FWT.x' | |
| ) | |
| ps0 <-merge( | |
| kabupaten, ps0, | |
| by=c('R101','R102'), all=T) | |
| ####################################### | |
| write.csv( | |
| merge( | |
| kabupaten, | |
| merge( | |
| dcast(aggregate(FWT.y ~ R101+R102+R105+NSEC , x, sum), R101 +R102 ~ NSEC+R105), | |
| dcast(aggregate(FWT.y ~ R101+R102+NSEC , x, sum), R101+R102 ~ NSEC), | |
| by=c('R101','R102'), | |
| all=T | |
| ), | |
| by=c('R101','R102'), | |
| all=T | |
| ), | |
| '200619_hnwi_premium_segment.csv', | |
| row.names=F) | |
| ############ | |
| kab<-dcast( | |
| aggregate( | |
| FWT.x ~ R101+R102+NSEC, y,sum | |
| ), | |
| R101+R102 ~ NSEC, | |
| value.var = 'FWT.x' | |
| ) | |
| kab <- merge( | |
| kabupaten, kab, | |
| by=c('R101','R102') | |
| ,all=T) | |
| b41 <- read.dbf('blok41_gab_prop11-33_diseminasi.dbf') | |
| b41 <- rbind(b41,read.dbf('blok41_gab_prop34-94_diseminasi.dbf')) | |
| b42 <- read.dbf('blok42.dbf') | |
| # tt <- b42[b42$KODE %in% c(191,192,193,194,196,197,198,199,215,216,217,218,219,220,221,222,223,224,225,206,207,208,209,210,211,212,213,214,226,227,228,229,230,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,271,272,273,274,275,276,277,278,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,298,299,300,301,302,303,305,306,307,308,309,310,309,310),] | |
| # ss <- b41[b41$KODE %in% c(152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,170,171,172,173,174,175,176,177,180,179,181,182,185,184,186,187,188,2,3,4,5,6,7,10,9,11,12,13,14,15,17,18,21,22,25,23,19,20,24,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,53,55,56,57,54,58,59,60,61,63,64,65,66,69,67,68,70,71,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,99,100,101,102,103,104,105,107,108,109,110,111,112,113,114,115,116,117,118,119,122,121,123,124,126,127,128,129,130,131,132,134,135,136,137,138,139,140,141,142,143,144,145,147,148,149,150),] | |
| # | |
| tt <- b42[b42$KLP != 0,] | |
| ss <- b41[b41$KLP != 0,] | |
| tt$R102 <- paste0(tt$R101,sprintf('%02d',tt$R102)) | |
| ss$R102 <- paste0(ss$R101,sprintf('%02d',ss$R102)) | |
| # profil pengeluaran keseluruhan | |
| # tt<-merge(tt, tr, by='KODE', all.x=T) | |
| # ss<-merge(ss, tr, by='KODE', all.x=T) | |
| # | |
| tt$SETAHUNWERT <- tt$SEBULAN * tt$WERT * 12 | |
| # tt<-merge(y[,c(1,2,6,80)],tt,by=c('URUT','R101','R102'),all.x=T) | |
| # ttt <- dcast(tt, R101+R102+NSEC ~ KODE, value.var='SETAHUNWERT', sum) | |
| ss$SEBULAN <-(ss$B41K10 / 7)*30 # b41k10 <- pembelian+pemberian | |
| ss$SETAHUNWERT <- ss$SEBULAN * ss$WERT * 12 | |
| # ss<-merge(y[,c(1,2,6,80)],ss,by=c('URUT','R101','R102'),all.x=T) | |
| # sss <- dcast(ss, R101+R102+NSEC ~ KODE, value.var='SETAHUNWERT', sum) | |
| tr<-read.csv('trans.csv',header=T) | |
| tt<-merge(tt, tr, by='KODE', all.x=T) | |
| ss<-merge(ss, tr, by='KODE', all.x=T) | |
| # ttk <- dcast(tt, R101+R102+NSEC ~ Kategori, value.var='SETAHUNWERT', sum) | |
| # ssk <- dcast(ss, R101+R102+NSEC ~ Kategori, value.var='SETAHUNWERT', sum) | |
| # ttkb <- dcast(tt, R101+R102+URUT ~ KODE, value.var='SEBULAN', sum) | |
| # sskb <- dcast(ss, R101+R102+URUT ~ KODE, value.var='SEBULAN', sum) | |
| # ttkb<-transform(ttkb,'jum'=rowSums(ttkb[,-c(1:3)])) | |
| # sskb<-transform(sskb,'jum'=rowSums(sskb[,-c(1:3)])) | |
| ttk <- dcast(tt, R101+R102+URUT ~ Kategori, value.var='SETAHUNWERT', sum) | |
| ssk <- dcast(ss, R101+R102+URUT ~ Kategori, value.var='SETAHUNWERT', sum) | |
| ttk <- transform(ttk,'total.NonMakanan'=rowSums(ttk[,-c(1:3)])) | |
| ssk <- transform(ssk,'total.Makanan'=rowSums(ssk[,-c(1:3)])) | |
| # gabungan ttsk dan ssk | |
| tsk <- merge( | |
| ttk,ssk,by=c('R101','R102','URUT'),all=T | |
| ) | |
| final <- merge(y[,c(1,2,6,54,79,80)],tsk,by=c('URUT','R101','R102'),all.x=T) | |
| final.low <- aggregate(.~R101+R102,final[ final$NSEC=='Low',-c(1,5,6)],sum) | |
| final.mid1 <- aggregate(.~R101+R102,final[ final$NSEC=='Middle 1',-c(1,5,6)],sum) | |
| final.mid2 <- aggregate(.~R101+R102,final[ final$NSEC=='Middle 2',-c(1,5,6)],sum) | |
| final.upper <- aggregate(.~R101+R102,final[ final$NSEC=='Upper',-c(1,5,6)],sum) | |
| final.ps <- aggregate(.~R101+R102,final[ final$NSEC=='Premium segment',-c(1,5,6)],sum) | |
| final.low <- merge(kabupaten, final.low, by=c('R101','R102'), all.x=T ) | |
| final.mid1 <- merge(kabupaten, final.mid1, by=c('R101','R102'), all.x=T ) | |
| final.mid2 <- merge(kabupaten, final.mid2, by=c('R101','R102'), all.x=T ) | |
| final.upper <- merge(kabupaten, final.upper, by=c('R101','R102'), all.x=T ) | |
| final.ps <- merge(kabupaten, final.ps, by=c('R101','R102'), all.x=T ) | |
| write.csv(final.low,paste0(format(Sys.time(), "%Y%m%d"),'-profil-umum-pengeluaran-low-kabu.csv'), row.names=F) | |
| write.csv(final.mid1,paste0(format(Sys.time(), "%Y%m%d"),'-profil-umum-pengeluaran-mid1-kabu.csv'), row.names=F) | |
| write.csv(final.mid2,paste0(format(Sys.time(), "%Y%m%d"),'-profil-umum-pengeluaran-mid2-kabu.csv'), row.names=F) | |
| write.csv(final.upper,paste0(format(Sys.time(), "%Y%m%d"),'-profil-umum-pengeluaran-upper-kabu.csv'), row.names=F) | |
| write.csv(final.ps,paste0(format(Sys.time(), "%Y%m%d"),'-profil-umum-pengeluaran-ps-kabu.csv'), row.names=F) | |
| ######### | |
| ##### sampah | |
| rutaperkabt <- aggregate(WERT ~ URUT+R101+R102+NSEC, tt[!duplicated(tt$URUT),], sum) #jumlah ruta b42 | |
| rutaperkabs <- aggregate(WERT ~ URUT+R101+R102+NSEC, ss[!duplicated(tt$URUT),], sum) #jumlah ruta b41 | |
| # ttt.low <- ttt[ ttt$NSEC == 'Low',] | |
| # ttt.mid1 <- ttt[ ttt$NSEC == 'Middle 1',] | |
| # ttt.mid2 <- ttt[ ttt$NSEC == 'Middle 2',] | |
| # ttt.upper <- ttt[ ttt$NSEC == 'Upper',] | |
| # ttt.ps <- ttt[ ttt$NSEC == 'Premium segment',] | |
| # ttt.low[,-c(1:3)] <- ttt.low[,-c(1:3)]/rutaperkabt[rutaperkabt$NSEC == 'Low',]$WERT | |
| # ttt.mid1[,-c(1:3)] <- ttt.mid1[,-c(1:3)]/rutaperkabt[rutaperkabt$NSEC == 'Middle 1',]$WERT | |
| # ttt.mid2[,-c(1:3)] <- ttt.mid2[,-c(1:3)]/rutaperkabt[rutaperkabt$NSEC == 'Middle 2',]$WERT | |
| # ttt.upper[,-c(1:3)] <- ttt.upper[,-c(1:3)]/rutaperkabt[rutaperkabt$NSEC == 'Upper',]$WERT | |
| # ttt.ps[,-c(1:3)] <- ttt.ps[,-c(1:3)]/rutaperkabt[rutaperkabt$NSEC == 'Premium segment',]$WERT | |
| ttk.low <- ttk[ ttk$NSEC == 'Low',] | |
| ttk.mid1 <- ttk[ ttk$NSEC == 'Middle 1',] | |
| ttk.mid2 <- ttk[ ttk$NSEC == 'Middle 2',] | |
| ttk.upper <- ttk[ ttk$NSEC == 'Upper',] | |
| ttk.ps <- ttk[ ttk$NSEC == 'Premium segment',] | |
| ttk.low[,-c(1:3)] <- ttk.low[,-c(1:3)]/rutaperkabt[rutaperkabt$NSEC == 'Low',]$WERT | |
| ttk.mid1[,-c(1:3)] <- ttk.mid1[,-c(1:3)]/rutaperkabt[rutaperkabt$NSEC == 'Middle 1',]$WERT | |
| ttk.mid2[,-c(1:3)] <- ttk.mid2[,-c(1:3)]/rutaperkabt[rutaperkabt$NSEC == 'Middle 2',]$WERT | |
| ttk.upper[,-c(1:3)] <- ttk.upper[,-c(1:3)]/rutaperkabt[rutaperkabt$NSEC == 'Upper',]$WERT | |
| ttk.ps[,-c(1:3)] <- ttk.ps[,-c(1:3)]/rutaperkabt[rutaperkabt$NSEC == 'Premium segment',]$WERT | |
| # sss.low <- sss[ sss$NSEC == 'Low',] | |
| # sss.mid1 <- sss[ sss$NSEC == 'Middle 1',] | |
| # sss.mid2 <- sss[ sss$NSEC == 'Middle 2',] | |
| # sss.upper <- sss[ sss$NSEC == 'Upper',] | |
| # sss.ps <- sss[ sss$NSEC == 'Premium segment',] | |
| # sss.low[,-c(1:3)] <- sss.low[,-c(1:3)]/rutaperkabs[rutaperkabs$NSEC == 'Low',]$WERT | |
| # sss.mid1[,-c(1:3)] <- sss.mid1[,-c(1:3)]/rutaperkabs[rutaperkabs$NSEC == 'Middle 1',]$WERT | |
| # sss.mid2[,-c(1:3)] <- sss.mid2[,-c(1:3)]/rutaperkabs[rutaperkabs$NSEC == 'Middle 2',]$WERT | |
| # sss.upper[,-c(1:3)] <- sss.upper[,-c(1:3)]/rutaperkabs[rutaperkabs$NSEC == 'Upper',]$WERT | |
| # sss.ps[,-c(1:3)] <- sss.ps[,-c(1:3)]/rutaperkabs[rutaperkabs$NSEC == 'Premium segment',]$WERT | |
| ssk.low <- ssk[ ssk$NSEC == 'Low',] | |
| ssk.mid1 <- ssk[ ssk$NSEC == 'Middle 1',] | |
| ssk.mid2 <- ssk[ ssk$NSEC == 'Middle 2',] | |
| ssk.upper <- ssk[ ssk$NSEC == 'Upper',] | |
| ssk.ps <- ssk[ ssk$NSEC == 'Premium segment',] | |
| ssk.low[,-c(1:3)] <- ssk.low[,-c(1:3)]/rutaperkabs[rutaperkabs$NSEC == 'Low',]$WERT | |
| ssk.mid1[,-c(1:3)] <- ssk.mid1[,-c(1:3)]/rutaperkabs[rutaperkabs$NSEC == 'Middle 1',]$WERT | |
| ssk.mid2[,-c(1:3)] <- ssk.mid2[,-c(1:3)]/rutaperkabs[rutaperkabs$NSEC == 'Middle 2',]$WERT | |
| ssk.upper[,-c(1:3)] <- ssk.upper[,-c(1:3)]/rutaperkabs[rutaperkabs$NSEC == 'Upper',]$WERT | |
| ssk.ps[,-c(1:3)] <- ssk.ps[,-c(1:3)]/rutaperkabs[rutaperkabs$NSEC == 'Premium segment',]$WERT | |
| # final.low <- merge(ttt.low,sss.low,by=c('R101','R102','NSEC'),all=T) | |
| # final.mid1 <- merge(ttt.mid1,sss.mid1,by=c('R101','R102','NSEC'),all=T) | |
| # final.mid2 <- merge(ttt.mid2,sss.mid2,by=c('R101','R102','NSEC'),all=T) | |
| # final.upper <- merge(ttt.upper,sss.upper,by=c('R101','R102','NSEC'),all=T) | |
| # final.ps <- merge(ttt.ps,sss.ps,by=c('R101','R102','NSEC'),all=T) | |
| finalk.low <- merge(ttk.low,ssk.low,by=c('R101','R102','NSEC'),all=T) | |
| finalk.mid1 <- merge(ttk.mid1,ssk.mid1,by=c('R101','R102','NSEC'),all=T) | |
| finalk.mid2 <- merge(ttk.mid2,ssk.mid2,by=c('R101','R102','NSEC'),all=T) | |
| finalk.upper <- merge(ttk.upper,ssk.upper,by=c('R101','R102','NSEC'),all=T) | |
| finalk.ps <- merge(ttk.ps,ssk.ps,by=c('R101','R102','NSEC'),all=T) | |
| # write.csv(final.low,paste0(format(Sys.time(), "%Y%m%d"),'-profil-umum-pengeluaran-low-kabu.csv'), row.names=F) | |
| # write.csv(final.mid1,paste0(format(Sys.time(), "%Y%m%d"),'-profil-umum-pengeluaran-mid1-kabu.csv'), row.names=F) | |
| # write.csv(final.mid2,paste0(format(Sys.time(), "%Y%m%d"),'-profil-umum-pengeluaran-mid2-kabu.csv'), row.names=F) | |
| # write.csv(final.upper,paste0(format(Sys.time(), "%Y%m%d"),'-profil-umum-pengeluaran-upper-kabu.csv'), row.names=F) | |
| # write.csv(final.ps,paste0(format(Sys.time(), "%Y%m%d"),'-profil-umum-pengeluaran-ps-kabu.csv'), row.names=F) | |
| write.csv(finalk.low,paste0(format(Sys.time(), "%Y%m%d"),'-profil-umum-pengeluaran-low-kabu.csv'), row.names=F) | |
| write.csv(finalk.mid1,paste0(format(Sys.time(), "%Y%m%d"),'-profil-umum-pengeluaran-mid1-kabu.csv'), row.names=F) | |
| write.csv(finalk.mid2,paste0(format(Sys.time(), "%Y%m%d"),'-profil-umum-pengeluaran-mid2-kabu.csv'), row.names=F) | |
| write.csv(finalk.upper,paste0(format(Sys.time(), "%Y%m%d"),'-profil-umum-pengeluaran-upper-kabu.csv'), row.names=F) | |
| write.csv(finalk.ps,paste0(format(Sys.time(), "%Y%m%d"),'-profil-umum-pengeluaran-ps-kabu.csv'), row.names=F) | |
| write.csv(cbind(sss[,c(1,2)],sss[,-c(1,2)]/rutaperkabs$WERT),paste0(format(Sys.time(), "%Y%m%d"),'-profil-umum-pengeluaran-makanan-kabu.csv'), row.names=F) | |
| write.csv(cbind(ttt[,c(1,2)],ttt[,-c(1,2)]/rutaperkabt$WERT),paste0(format(Sys.time(), "%Y%m%d"),'-profil-umum-pengeluaran-non-makanan-kabu.csv'), row.names=F) | |
| # profil pengeluaran premium segmen | |
| ttps<-tt[ tt$URUT %in% ps$URUT, ] | |
| rutapsperkabt <- aggregate(WERT ~ R101+R102, ttps, sum) #jumlah ruta ps b42 | |
| rutapsperkabs <- aggregate(WERT ~ R101+R102, ssps, sum) #jumlah ruta ps b41 | |
| tttps<-dcast(ttps, R101+R102 ~ KODE, value.var='SETAHUNWERT', sum) | |
| ssps <- ss[ss$URUT %in% ps$URUT,] | |
| sssps <- dcast(ssps, R101+R102 ~ KODE, value.var='SETAHUNWERT', sum) | |
| write.csv(cbind(sssps[,c(1,2)],sssps[,-c(1,2)]/rutapsperkabs$WERT),paste0(format(Sys.time(), "%Y%m%d"),'-profil-ps-pengeluaran-makanan-kabu.csv'), row.names=F) | |
| write.csv(cbind(tttps[,c(1,2)],tttps[,-c(1,2)]/rutapsperkabt$WERT),paste0(format(Sys.time(), "%Y%m%d"),'-profil-ps-pengeluaran-non-makanan-kabu.csv'), row.names=F) | |
| tt<-merge(y[,c(1,2,6,80)],tt,by=c('URUT','R101','R102'),all.x=T) | |
| ss<-merge(y[,c(1,2,6,80)],tt,by=c('URUT','R101','R102'),all.x=T) | |
| # kategori b41 | |
| kat41 <- b41[ b41$KODE %in% c(1,8,16,52,62,72,98,106,120,125,133,146,151,183,151,183)] | |
| kat41$SEBULAN <-(kat41$B41K10 / 7)*30 | |
| kat41$SEBULANRT <-kat41$SEBULAN * kat41$WERT | |
| kat41$R102 <- paste0(kat41$R101,sprintf('%02d',kat41$R102)) | |
| kat41<-dcast(kat41, R101+R102+URUT ~ KODE, value.var='SEBULANRT', sum) | |
| final <- merge(y[,c(1,2,6,54,79,80)],x,by=c('URUT','R101','R102'),all.x=T) | |
| final.mu <- final[final$NSEC == 'Middle Up',] |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment