Membuat Laporan menggunakan operasi Join di R

Print Friendly, PDF & Email

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

  1. Banyuke diambilnya tidak secara sekaligus tapi sebanyak 2 kali
  2. Kuala bahe masih ada sisa 200, sedangkan
  3. 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

  1.  do gula
  2. keluar gula

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')

 

Leave a Reply

Your email address will not be published. Required fields are marked *