Olah Data Excel dengan Python – Mengubah File

Pada tulisan sebelumnya kita telah mendiskusikan bagaimana membuat file Excel menggunakan Python, pada tulisan berikut kita akan mendiskusikan bagaimana mengubah dan mengolah data pada Excel. Kita akan menggunakan satu kasus yang pernah ditanyakan di Grup Excel Indonesia.

Anda dapat mendownload file Excelnya di sini lalu simpan dengan nama contoh_if.xlsx di folder kita sebelumnya d:\latihan_excel_python\. File di atas berisi daftar nilai siswa, tujuan yang akan dicapai adalah menentukan kelulusan tiap siswa dengan syarat.

  1. Nilai minimal 4,25 untuk setiap mata pelajaran dengan nilai rata-rata minimal 5,25 atau (LULUS)
  2. Boleh nilai minimal 4,00 pada salah satu mata pelajaran dengan nilai mata pelajaran lainnya minimal 6,00 (LULUS)
  3. Jika tidak memenuhi syarat tersebut (GAGAL)

Jika (if) disebut pula conditional statement, kita akan bertanya pada Google soal bagaimana membuat algoritma (langkah kerja) conditional statement pada Python. Cari python conditional statement pada Google, sekedar saran sebaiknya selalu gunakan bahasa inggris saat mencari sesuatu di Google, terutama yang berhubungan dengan pemrograman karena jumlah artikel yang jauh lebih banyak, topik yang beragam dan kedalaman materi yang lebih baik.

Kita buka saja jawaban pertama tampak menjanjikan. Oya, kita lupa belum mencari tahu bagaimana caranya membuka file Excel menggunakan library openpyxl. Sebenarnya tempat terbaik untuk mengetahui apa yang bisa dilakukan oleh sebuah teknologi (misalnya openpyxl) adalah dengan melihat dokumentasi resminya, di sini untuk openpyxl, lalu mencari bagian yang kita butuhkan. Misal jika ingin membuka file Excel maka dapat menggunakan bagian Loading from a file pada dokumentasi openpyxl. Jika Anda memiliki waktu, saya sarankan untuk membaca dan mempraktekkan kode yang ada pada dokumentasi tersebut, dengan begitu Anda akan lebih memahami fitur apa saja yang dimiliki suatu teknologi dan apa yang dapat dilakukan untuk menolong pekerjaan Anda sehari-hari.

Sekarang tinggal meramu algoritma saja, kurang lebih program yang akan kita buat melakukan hal seperti ini.

  1. Buka file Excel
  2. Baca tiap baris Excel dari awal hingga akhir
  3. Bandingkan tiap baris dengan kondisi tujuan (nilai minimal harus sekian maka lulus, atau boleh nilai minimal sekian namun mata pelajaran lain harus sekian)
  4. Tulis hasil langkah #3 di samping kanan baris
  5. Simpan file Excel

Kita akan melakukan dengan perlahan, tidak langsung mengikuti algoritma di atas. Yang pertama perlu dilakukan adalah melihat file Excel, mengamati karakteristik yang kira-kira akan berguna dalam mencapai tujuan. Misal kita lihat kalau data dimulai dari baris 2 sampai 13, kolom B berisi nama siswa, kolom E sampai J berisi nilai mata pelajaran dan kolom K berisi nilai rata-rata.

Selanjutnya berdasarkan informasi tersebut, kita akan latihan membuka file Excel dan menampilkan data. Buatlah sebuah file bernama d:\latihan_excel_python\buka_excel.py berisi kode berikut.

from openpyxl import load_workbook
from openpyxl import Workbook

wb = load_workbook('d:\\latihan_excel_python\\contoh_if.xlsx', data_only = True)
ws = wb.active
for row in range(2, 14):
  print(ws['B' + str(row)].value)

Kode di atas kurang lebih mengatakan begini.

Dari (library) openpyxl ambil fungsi load_workbook.

Dari (library) openpyxl ambil fungsi Workbook.

Variable wb adalah file contoh_if.xlsx, yang dibuka menggunakan fungsi load_workbook. Saat membuka hanya ambil data (nilai)nya saja, tidak perlu menampilkan rumus.

Variable ws adalah worksheet yang aktif dari wb.

Hitung angka dari 2 sampai 14, tiap menghitung, simpan angka baru dalam variabel bernama row.

Tampilkan (print) nilai sel pada kolom B baris row (2 – 14) dari worksheet ws.

Dua baris terakhir (for row dan print) disebut pula sebagai blok kode, ditandai dengan titik dua pada baris di atas dan indentasi (diketik menjorok, dalam contoh 2 spasi) pada baris di bawahnya.

For sendiri biasa disebut sebagai perulangan (looping), kurang lebih artinya lakukan sebanyak n kali, atau lakukan hingga (lakukan menghitung hingga angka 14). Melakukan apa? Tampilkan nilai sel kolom B dan baris row, variable row akan berubah dari 2 sampai 13.

Kali ini kita akan menjalankan kode melalui Command Prompt dengan mengetik python d:\latihan_excel_python\buka_excel.py lalu Enter.

Layar Command Prompt menampilkan isi kolom B (Nama) yaitu A, B, C, …, L. Anda dapat bereksperimen dengan mengganti nama kolom (‘B’) pada kode print(ws[‘B’ + str(row)].value) menjadi ‘C’ atau ‘D’, simpan file (Ctrl + S), lalu ulangi perintah di Command Prompt (dapat dengan menekan panah atas) dan Enter.

Anda dapat pula merubah kode for row in range(2, 14): menjadi, misalnya (2, 13) atau (1, 20), simpan lalu ulangi perintah di Command Prompt agar Anda makin memahami apa yang dikerjakan oleh kode di atas.

Setelah berhasil menampilkan data, kita akan menguji coba kondisi pertama, nilai minimal 4,25 untuk setiap mata pelajaran dengan nilai rata-rata minimal 5,25 maka LULUS. Buat file d:\latihan_excel_python\kondisi_pertama.py dan ketik (atau copas) kode di bawah ini.

from openpyxl import load_workbook
from openpyxl import Workbook
wb = load_workbook('d:\\latihan_excel_python\\contoh_if.xlsx', data_only = True)
ws = wb.active
for row in range(2, 14):

  # Nilai minimal 4.25 untuk setiap mata pelajaran
  # dengan nilai rata-rata minimal 5,25 maka LULUS
  if ws['E' + str(row)].value >= 4.25 and \
  ws['F' + str(row)].value >= 4.25 and \
  ws['G' + str(row)].value >= 4.25 and \
  ws['H' + str(row)].value >= 4.25 and \
  ws['I' + str(row)].value >= 4.25 and \
  ws['J' + str(row)].value >= 4.25 and \
  ws['K' + str(row)].value >= 5.25:
    print(ws['B' + str(row)].value, 'LULUS')

Kode di atas mengatakan begini pada komputer, kita langsung pada konsep yang baru.

Di dalam blok kode looping kita membuat blok kode conditional statement menggunakan if. Conditional statement tersebut akan benar (terpenuhi) jika.

  1. Sel pada kolom E baris ke-row (Nilai Bahasa Indonesia) bernilai lebih besar atau sama dengan 4,25
  2. Sel pada kolom F baris ke-row (Nilai Bahasa Ingris) bernilai lebih besar atau sama dengan 4,25
  3. Sel pada kolom G baris ke-row (Nilai Matematika) bernilai lebih besar atau sama dengan 4,25
  4. Sel pada kolom H baris ke-row (Nilai Fisika) bernilai lebih besar atau sama dengan 4,25
  5. Sel pada kolom I baris ke-row (Nilai Kimia) bernilai lebih besar atau sama dengan 4,25
  6. Sel pada kolom J baris ke-row (Nilai Biologi) bernilai lebih besar atau sama dengan 4,25
  7. Sel pada kolom K baris ke-row (Nilai Rata-Rata) bernilai lebih besar atau sama dengan 5,25

Jika semua kondisi tersebut terpenuhi maka tampilkan nilai kolom B baris ke-row dan kata LULUS.

Pada kode di atas, yang baru adalah and dan \ di akhir baris, and berarti kondisi ini harus terpenuhi dan kondisi ini harus terpenuhi dan seterusnya harus selalu terpenuhi.

Sedang tanda \ di bagian akhir digunakan jika kita ingin memotong kode yang seharusnya sebaris menjadi beberapa baris untuk memudahkan membaca. Salah satu konvensi penulisan kode yang jamak digunakan adalah 80 karakter per baris. Selain karena alasan sejarah seperti pada pranala ini, juga untuk memudahkan membaca, alasan yang sama kenapa surat kabar dibuat berkolom-kolom dengan hanya beberapa kata per baris.

Kita juga sekarang mengenal komentar pada kode, ditandai dengan tanda pagar (#) di awal baris. Kode sebaiknya dikomentari sebagai bentuk dokumentasi agar pembaca kode, seringnya adalah kita sendiri di masa depan, dapat memahami langkah kerja yang dilakukan kode.

Kita coba dengan mengetik python d:\latihan_excel_python\kondisi_pertama.py di Command Prompt.

 

Ternyata hanya siswa bernama K yang lulus. Anda dapat melihat file Excel tersebut untuk menguji apakah sudah benar hasil kode dan data yang dimiliki.

Sekarang saatnya mengantisipasi kondisi kedua, boleh nilai minimal 4,00 pada salah satu mata pelajaran dengan nilai mata pelajaran lainnya minimal 6,00. Kita akan sedikit mengubah kode, memanfaatkan fitur bahasa pemrograman bernama array, yang dalam Python diberi istilah list. Tulis kode berikut pada file d:\latihan_excel_python\kondisi_kedua.py.

from openpyxl import load_workbook
from openpyxl import Workbook
wb = load_workbook('d:\\latihan_excel_python\\contoh_if.xlsx', data_only = True)
ws = wb.active
for row in range(2, 14):
  ind = ws['E' + str(row)].value
  ing = ws['F' + str(row)].value
  mat = ws['G' + str(row)].value
  fi = ws['H' + str(row)].value
  ki = ws['I' + str(row)].value
  bio = ws['J' + str(row)].value
  rata2 = ws['K' + str(row)].value
  semua = [ind, ing, mat, fi, ki, bio]
  
  # Nilai minimal 4.25 untuk setiap mata pelajaran
  # dengan nilai rata-rata minimal 5,25 maka LULUS	
  if ind >= 4.25 and \
  ing >= 4.25 and \
  mat >= 4.25 and \
  fi >= 4.25 and \
  ki >= 4.25 and \
  bio >= 4.25 and \
  rata2 >= 5.25:
    print(ws['B' + str(row)].value, 'LULUS')
    
  # Nilai minimal 4.00 untuk satu mata pelajaran
  # dengan nilai lainnya minimal 6,00 maka LULUS
  elif 4 in semua:
    status = 'LULUS'
    for s in semua:
      if s is not 4 and s < 6:
        status = 'GAGAL'
    print(ws['B' + str(row)].value, status)
  else:
    print(ws['B' + str(row)].value, 'GAGAL')

Terdapat beberapa variable baru seperti ind, ing, mat, fi, ki, bio dan rata2 yang digunakan untuk menampung nilai dari masing-masing kolom (F – J) dalam suatu baris. Sedang variable semua adalah array/list atau kumpulan data yang diisi dengan nilai ind, ing, mat, fi, ki dan bio.

Blok kode di bawahnya menguji masing-masing variable nilai harus minimal 4,25 dan rata-rata nilai minimal 5,25. Sama dengan pada file d:\latihan_excel_python\kondisi_pertama.py hanya diganti menjadi variable ind, ing, mat, fi, ki, bio dan rata2.

Sejajar dengan if, kita membuat blok kode baru yang diawali dengan elif. Elif (Else If) adalah Conditional Statement seperti if yang akan dieksekusi oleh Python jika kondisi if tidak tercapai. Kode di atas berarti, jika nilai semua mata pelajaran >= 4,25 dan nilai rata-rata >=5,25 maka siswa LULUS, jika kondisi tersebut tidak terpenuhi (misal karena ada nilai mata pelajaran < 4,25 atau nilai rata-rata < 5,25) uji apakah terdapat nilai 4 pada array semua.

Blok kode elif akan melakukan:

  1. Membuat variable bernama status yang diberi nilai ‘LULUS’.
  2. Membuat blok kode di bawahnya dengan melakukan pembacaan pada semua nilai pada variable semua dengan membuat variable s sebagai penampung sementara tiap-tiap nilai pada semua.
    1. Jika s bukan 4 dan s < 6 maka ubah variable status menjadi ‘GAGAL’

    .

  3. Tampilkan nilai sel pada kolom B baris row dan nilai variable status.

Blok kode else akan dijalankan jika dua kondisi di atasnya tidak terpenuhi, dalam hal ini akan menampilkan nilai nama siswa dan tulisan ‘GAGAL’.

Seperti biasa, jalankan dengan mengetik python d:\latihan_excel_python\kondisi_kedua.py di Command Prompt.

Terlihat hanya siswa D dan K yang lulus, silahkan melihat file Excel untuk memastikan apakah algoritma yang kita buat telah sesuai.

Sekarang saatnya menggabungkan pengetahuan di atas menjadi sebuah kode Python yang akan menulis hasil kelulusan siswa. Buat file d:\latihan_excel_python\kondisi_final.py dengan kode berikut.

from openpyxl import load_workbook
from openpyxl import Workbook
wb = load_workbook('d:\\latihan_excel_python\\contoh_if.xlsx', data_only = True)
ws = wb.active
for row in range(2, 14):
  ind = ws['E' + str(row)].value
  ing = ws['F' + str(row)].value
  mat = ws['G' + str(row)].value
  fi = ws['H' + str(row)].value
  ki = ws['I' + str(row)].value
  bio = ws['J' + str(row)].value
  rata2 = ws['K' + str(row)].value
  semua = [ind, ing, mat, fi, ki, bio]
  
  # Nilai minimal 4.25 untuk setiap mata pelajaran
  # dengan nilai rata-rata minimal 5,25 maka LULUS	
  if ind >= 4.25 and \
  ing >= 4.25 and \
  mat >= 4.25 and \
  fi >= 4.25 and \
  ki >= 4.25 and \
  bio >= 4.25 and \
  rata2 >= 5.25:		
    ws['L' + str(row)] = 'LULUS'
    
  # Nilai minimal 4.00 untuk satu mata pelajaran
  # dengan nilai lainnya minimal 6,00 maka LULUS
  elif 4 in semua:
    status = 'LULUS'
    for s in semua:
      if s is not 4 and s < 6:
        status = 'GAGAL'		
    ws['L' + str(row)] = status
  else:		
    ws['L' + str(row)] = 'GAGAL'

wb.save('d:\\latihan_excel_python\\kondisi_hasil.xlsx')

Tidak ada yang istimewa di sini, kita hanya mengganti bagian kode yang menampilkan status kelulusan dengan perintah kepada komputer untuk kurang lebih begini: tulis pada kolom L baris ke row dengan ‘LULUS’/’GAGAL’. Di bagian akhir kita menambahkan kode untuk menyimpan file Excel dengan nama kondisi_hasil.xlsx. Jalankan kode dengan mengetik python d:\latihan_excel_python\kondisi_final.py dan Enter di Command Prompt.

Tidak ada pesan kesalahan, sepertinya berhasil. Buka file d:\latihan_excel_python\kondisi_hasil.xlsx.

Awesome! Kita berhasil mengolah data dan menuliskannya di Excel.

Tulisan ini hanya bertujuan untuk memberikan sedikit wacana tentang apa yang bisa dilakukan bahasa pemrograman, seperti Python, sebagai alat bantu mengolah data di Excel. Agar lebih berdaya dalam mengelola data, silahkan mempelajari dasar-dasar pemrograman Python melalui saluran lain seperti https://www.w3schools.com/python/default.asp, https://www.tutorialspoint.com/python/index.htm atau yang lainnya seperti buku atau ebook.

Salam.

2 Replies to “Olah Data Excel dengan Python – Mengubah File”

Leave a Reply

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