Mengolah data menggunakan excel merupakan hal lazim ditemui di aplikasi perkantoran terutama untuk data berbentuk tabular, excel sudah menjadi aplikasi wajib dan banyak juga alternatif nya seperti WPS, open office, libre office.
Ratusan fungsi diexcel sangat membantu sekali dalam operasi yang melibatkan proses perhitungan seperti di akuntansi, statistik, visualisasi, bahkan banyak koq tersedia add in baik yang berbayar atau yang gratisan. Bahkan di website ini juga dibahas mengenai function excel untuk mempermudah administrasi bisnis.
Mengingat format excel begitu luas digunakan tidak semua operasi dapat didukung oleh excel, seperti join tabel. Yup, operasi join tabel biasanya hanya ada di bahasa SQL (Structure query languange). Operasi ini sangat berguna sekali untuk mencocokan baris berdasarkan primary dan foreign key. Ada 4 jenis operasi join yaitu left, right, inner, dan full join. Pada tutorial ini, saya tidak membahas mengenai excel, namun kebetulan saja ada kerjaan yang mengharuskan format data excel untuk mencari kecocokan data transaksi antara terbit do dan surat pengeluaran gudang (gd1k).
Pada prakteknya data tersebut sangat banyak, sehingga saya buat simple saja untuk mendapatkan gambaran mudah operasi join di R menggunakan package dplyr. Perhatikan 2 tabel berikut
Tabel sebelah kiri berisikan informasi mengenai dokumen DO yang diterbitkan oleh bagian penjualan dengan 2 informasi yang penting yaitu no_do dan pagu.
Bila ada pembeli yang melakukan order pembelian, maka akan diberikan dokumen no_do yang akan dibawa sebagai bukti sah untuk ambil barang digudang.
Pada bagian kanan merupakan dokumen yang dikeluarkan oleh bagian gudang berdasarkan informasi no_do yang dibawa oleh pembeli/angkutan.
Terkadang seorang pembeli tidak langsung membawa seluruhnya, misalkan pada no_do diterbitkan sebanyak 200 kg, yang kemudian dilakukan pengambilan sebanyak 2 kali yaitu no_k = 2 sebanyak 150 dan no_k = 3 sebanyak 50 sehingga totalnya 200 sesuai dengan do terbit.
Dari informasi diatas, maka tabel delivery order mempunyai primari key = no_do sebagai foreign key di tabel pengeluaran gudang.
Right Join
Operasi ini menggabungkan tabel kiri ke kanan, jika ada yang kosong maka diabaikan, kalian bisa melihat! Bahwa pada tabel pengeluaran barang yang belum mengeluarkan satupun no_do = 5 tidak akan ditampilkan
model_right = right_join(do,gd1k, by = c('no_do'))
model_right = model_right %>%select(no_k,no_do,nama_desa,salur)
model_right
hasil
no_k no_do nama_desa salur 1 1 1 sidas 100 2 2 2 banyuke 150 3 3 2 banyuke 50 4 4 3 paloan 300 5 5 4 kuala behe 200
Left Join
Operasi ini berkebalikan dengan right join, mari kita coba saja
model_left = left_join(do,gd1k, by = c('no_do'))
model_left = model_left %>%select(no_k,no_do,nama_desa,salur)
model_left
hasil
no_k no_do nama_desa salur 1 1 1 sidas 100 2 2 2 banyuke 150 3 3 2 banyuke 50 4 4 3 paloan 300 5 5 4 kuala behe 200 6 NA 5 lapas NA
Informasi diatas kurang informatif bagi penggunanya, mari kita lakukan teknik agregasi untuk menghitung jumlah pengeluaran dan frekuensinya agar lebih ringkas melalui group by pada tabel pengeluaran barang agar no_do menjadi lebih ringkas, kita kasih nama variabel gruping
gruping = gd1k %>%group_by(no_do)%>%summarize(keluar = sum(salur),frekuensi=n()) gruping
hasil
no_do keluar frekuensi <dbl> <dbl> <int> 1 1 100 1 2 2 200 2 3 3 300 1 4 4 200 1
Kalian jadi tahu bahwa no_do = 2 dikeluarkan sebanyak 2 kali dengan jumlah totalnya 200.
Full Join
Dengan operasi full join yaitu menggabungkan left dan right join secara bersama-sama, kalian bisa lihat hasilnya sebagai berikut untuk full join antar gruping dan do
model2 = full_join(do,gruping, by = c('no_do'))
model2
hasilnya
no_do nama_desa pagu ket keluar frekuensi 1 1 sidas 100 cb 100 1 2 2 banyuke 200 cb 200 2 3 3 paloan 300 cb 300 1 4 4 kuala behe 400 cb 200 1 5 5 lapas 1000 km5 NA NA
Kurang informatif, kita kasih kolom baru dengan mutate untuk menghitung sisa DO yang belum dikeluarkan
model3 = model2 %>% mutate(sisa=pagu-keluar) model3 = model3 %>% select(no_do,nama_desa,pagu,keluar,frekuensi,sisa) model3
hasil
no_do nama_desa pagu keluar frekuensi sisa 1 1 sidas 100 100 1 0 2 2 banyuke 200 200 2 0 3 3 paloan 300 300 1 0 4 4 kuala behe 400 200 1 200 5 5 lapas 1000 NA NA NA
Kalian bisa simpulkan bahwa
- Banyuke diambilnya tidak secara sekaligus tapi sebanyak 2 kali
- Kuala bahe masih ada sisa 200, sedangkan
- Lapas tidak pernah / belum pernah diambil sama sekali
Tapi kalau ingin membuat laporan sebagai berikut akan susah karena terbit dokumen pengeluaran gudang bisa saja lebih dari 1 kali
Kalau menggunakan full join maka hasilnya sudah betul tapi bikin bingung yang baca
model_full = full_join(do,gd1k, by = c('no_do'))
model_full = model_full %>% select(no_do,nama_desa,pagu,no_k,salur)
model_full
hasil
no_do nama_desa pagu no_k salur 1 1 sidas 100 1 100 2 2 banyuke 200 2 150 3 2 banyuke 200 3 50 4 3 paloan 300 4 300 5 4 kuala behe 400 5 200 6 5 lapas 1000 NA NA
Biar lebih jelas, berikut versi excel nya
Kita akan menghilangkan/diberikan string kosong pada grid warna kuning, Kalau ingin dibuat ideal, maka akan diolah sebagai berikut
Berikut kodenya
model4 = left_join(model3,gd1k,by = c('no_do'))
#untuk mengisi dengan string kosong
duplikat_do = model4 %>% select(no_do)
duplikat_do = rbind('0',duplikat_do)
no_do2 = duplikat_do[-nrow(duplikat_do),]
duplikat_do = data.frame(dat,no_do2)
hasil = duplikat_do %>% mutate(rekap_do = if_else(no_do == no_do2,'','x'))
for (i in 1:length(hasil$no_do))
{
if (hasil[i,3]=='x')
{
hasil[i,3]=hasil[i,1]
}else
{
model4[i,2]='' #kolom desa
model4[i,3]='' #kolom pagu
model4[i,6]='' #kolom sisa
}
}
model4 = model4 %>% mutate(rekap_do=hasil[,3])
model4 = model4 %>% select(rekap_do,nama_desa,pagu,sisa,no_k,salur)
model4
Hasilnya pun sempurna seperti ini
rekap_do nama_desa pagu sisa no_k salur 1 1 sidas 100 0 1 100 2 2 banyuke 200 0 2 150 3 3 50 4 3 paloan 300 0 4 300 5 4 kuala behe 400 200 5 200 6 5 lapas 1000 <NA> NA NA
Berikut hasil jika di generate di excel, sehingga laporan menjadi rapi
Bagaimana menurut kalian untuk menyajikan laporan diatas? Atau ada ide lain agar mengindari looping di R, setidaknya penulis masih menggunakan cara ini karena lebih mudah. Silahkan untuk download file excel berikut serta kodenya
Berikut kode lengkap nya yang bisa kalian pelajari
library(dplyr)
library(openxlsx)
do = read.xlsx('D:/do gula.xlsx')
gd1k = read.xlsx('D:/keluar gula.xlsx')
model_left = left_join(do,gd1k, by = c('no_do'))
model_left = model_left %>%select(no_k,no_do,nama_desa,salur)
model_left
model_right = right_join(do,gd1k, by = c('no_do'))
model_right = model_right %>%select(no_k,no_do,nama_desa,salur)
model_right
model_full = full_join(do,gd1k, by = c('no_do'))
model_full = model_full %>% select(no_do,nama_desa,pagu,no_k,salur)
model_full
gruping = gd1k %>%group_by(no_do)%>%summarize(keluar = sum(salur),frekuensi=n())
gruping
model2 = full_join(do,gruping, by = c('no_do'))
model2
model3 = model2 %>% mutate(sisa=pagu-keluar)
model3 = model3 %>% select(no_do,nama_desa,pagu,keluar,frekuensi,sisa)
model3
model4 = left_join(model3,gd1k,by = c('no_do'))
model4
no_do = model4 %>% select(no_do)
duplikat_do = rbind('0',no_do)
no_do2 = duplikat_do[-nrow(duplikat_do),]
duplikat_do = data.frame(no_do,no_do2)
duplikat_do
hasil = duplikat_do %>% mutate(rekap_do = if_else(no_do == no_do2,'','x'))
for (i in 1:length(hasil$no_do))
{
if (hasil[i,3]=='x')
{
hasil[i,3]=hasil[i,1]
}else
{
model4[i,2]='' #kolom desa
model4[i,3]='' #kolom pagu
model4[i,6]='' #kolom sisa
}
}
model4 = model4 %>% mutate(rekap_do=hasil[,3])
model4 = model4 %>% select(rekap_do,nama_desa,pagu,sisa,no_k,salur)
model4
write.xlsx(model4,'C:/Users/mulka/Desktop/Joint di R/kesimpulan.xlsx')