1. Home
  2. Docs
  3. Kelas Privat Python – 01
  4. Materi [Manajemen Data]
  5. Excel dan Python

Excel dan Python

Xls merupakan salah satu format penyimpanan populer yang banyak digunakan oleh perusahaan ataupun amatir. Kemudahan yang dirasakan oleh user saat penggunaan menggunakan perangkat lunak yang berafiliasi menjadi alasan utama populernya format ini. Karena hal tersebut kemampuan untuk bisa menangani format xls menjadi nilai lebih bagi para developer python. Python sendiri memiliki banyak sekali library yang berhubungan dengan kegiatan manipulasi data xls, diantaranya:
  • Xlsxwriter
  • xlrd
  • xlwings
  • xlwt
  • pyexcel, dan
  • openpyxl
Pada kesempatan kali ini kita akan library openpyxl beserta penggunaannya.

Openpyxl

Openpyxl adalah library python yang digunakan untuk melakukan baca/tulis file Excel 2010 baik itu format xlsx atau lainnya. Openpyxl memiliki basis awal dari PHPExcel. Meinstall library openpyxl sangatlah mudah, kita bisa memanfaatkan pip disini,
pip install openpyxl

Manipulasi workbook

import library openpyxl lalu import class workbook dari library openpyxl
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
wb.active digunakan untuk memastikan bahwa kita bekerja pada workbook yang sedang aktif (default = 0). Untuk menambahkan sheet yang kita inginkan kita bisa menggunakan wb.create_sheet(). Jika kita memiliki nama sheet yang sama, maka sistem akan melakukan penambahan index secara otomatis. Secara default sheet yang kita tambahkan akan ditambahkan ke akhir list. Tambahkan parameter 0 untuk menambahkan sheet ke awal dan -1 untuk menambahkan sheet ke urutan kedua dari akhir.
ws1 = wb.create_sheet("kerja")
ws2 = wb.create_sheet("kerja", 0)
ws3 = wb.create_sheet("kerja", -1)

print(wb.sheetnames)
untuk melakukan melihat semua nama sheet yang ditambahkan kita bisa melakukan print(wb.sheetnames) dan akan mendapatkan hasil,
['kerja1', 'Sheet', 'kerja2', 'kerja']
kita dapat mengganti namanya secara manual dengan menggunakan .title . Misalkan kita ingin mengganti nama dari sheet 2, maka yang kita lakukan
ws.title = 'kerjabaru'
dan jika kita print ulang akan mendapatkan hasil,
['kerja1', 'kerjabaru', 'kerja2', 'kerja']
Setelah memberikan nama pada sheetmu, kamu bisa melakukan akses langsung dengan menggunakan nama tersebut.
workaktif = wb['kerjabaru']
print (workaktif)

#<Worksheet "kerjabaru">
Kita bisa melakukan penyimpanan dengan menggunakan
wb.save('namafile.xlsx')

Manipulasi Sel

Untuk mengakses sel terdapat dua cara, yang pertama adalah akses langsung dengan menggunakan nama selnya atau dengan menggunakan index.
ws['B4'] = 4
#atau
ws.cell(row=4, column=2).value = 4
untuk melakukan akses banyak sel kita bisa memanfaatkan for loop. misalkan kita punya sebuah list a dan ingin kita masukkan pertama pada baris pertama dan kedua pada kolom pertama.
a = ['aku', 'suka', 'makan', 'bakso']

for row in range(0,len(a)):
    ws.cell(row=row+1, column=1).value= a[row]

for column in range(0,len(a)):
    ws.cell(row=1, column=column+1).value= a[column]
row + 1 dan column +1 digunakan karena baris dan kolom tidak bisa dimulai dari 0. Jika kita memiliki data ke dalam banyak baris dan kolom kita bisa mengubahnya ke dalam bentuk matriks (list dalam list) lalu kita manfaatkan fungsi append. Misalkan kita mempunyai data list makanan terhadapat hari dan ingin memasukkan pada bari dan kolom excel maka yang kita lakukan adalah,
menu = [['hari'],['senin', 'nasi', 'ayam'],['selasa', 'susu'],['rabu','nasi goreng', 'ati ampela', 'jus apel'],['kamis','capcay','telur mata sapi']]
for item in menu:
    ws.append(item)

Membaca Excel

Pada kesempatan kali ini kita akan mencoba untuk melakukan penulisan dan pembacaan file excel yang berasal dari basis data sqlite. Untuk melakukan pembacaan data pada file excel, kita bisa memanfaatkan modul load_workbook pada library openpyxl.
from openpyxl import load_workbook

data = load_workbook(filename="namafile.xlsx")
Kode di atas akan membuka file namafile.xlsx. Beberapa fungsi yang kita jalankan saat akan menulis data pada excel bisa kita jalankan juga pada bagian ini untuk mengecek data kita.
print(data.sheetnames)
sheet = data.actve
print(sheet)
data.sheetnames akan memberikan kita nama-nama dari worksheet yang dimiliki oleh file tersebut. Untuk memasangkan variabel pada sheet active kita bisa menggunakan data.active atau gunakan sheet[‘namasheet’] untuk menginisiasi.
print(sheet["A1"])
#<Cell 'Sheet'.A1>

print(sheet["A1"].value)
#'First Name'
print(sheet[“A1”]) digunakan untuk mengecek sel kita, dan print(sheet[“A1”].value) digunakan untuk mengambil nilai dari sel yang kita pilih. selain menggunakan cara di atas kita juga bisa menggunakan .cell() untuk melakukan pengambilan data.
sheet.cell(row=1, column=1)
#<Cell 'Sheet1'.A1>

>>> sheet.cell(row=1, column=1).value
#'First Name'
Untuk menggunakan mengambil data dalam range maka kita bisa menggunakan query,
sheet["A3:D4"]
sheet["A"]
sheet[1]
sheet["A:B"]
sheet[1:4]
  • sheet[“A3:D4”] akan mengambil data pada kota di dalam A3 sampai D4
  • sheet[“A”] akan mengambil semua data pada kolom A
  • sheet[1] akan mengambil semua data pada baris pertama
  • sheet[“A:B”] akan mengambil semua data pada kolom A sampai kolom B
  • sheet[1:4] akan mengambil semua data pada baris pertama sampai bari keempat.
Selain menggunakan cara di atas untuk melakukan banyak pengambilan kita dapat memanfaatkan iter_rows() dan iter_cols()
#digunakan untuk mengambil semua kolom dengan range disebutkan
sheet.iter_cols(min_row=1, max_row=4, min_col=1, max_col=4)
#digunakan untuk mengambil semua baris dengan range disebutkan
sheet.iter_row(min_row=1, max_row=4, min_col=1, max_col=4)

Pandas & Excel

Karena dalam melakukan Data Mining dan Machine Learning penggunaan pandas cukup sering dilakukan oleh bebebrapa user, jadi kemampuan untuk mengkonversikan dataframe kedalam excel menjadi penting. Untuk melakukan konversi dari dataframe ke dalam excel yang disimpan kita dapat menggunakan.
df.to_excel("namafile.xlsx") #df bisa diganti nama dataframe
Jika kita perlu memasukkan ke dalam sheet tertentu, bisa kita gunakan
df.to_excel("namafile.xlsx",
             sheet_name='nama_sheet')
Memasukkan beberapa dataframe kedalam sheet yang berbeda dapat kita lakukan dengan memanfaatkan fungsi ExcelWriter
with pd.ExcelWriter('namafile.xlsx') as writer:  
    hasil1.to_excel(writer, sheet_name='nama_sheet_1')
    hasil2.to_excel(writer, sheet_name='nama_sheet_2')

Visualisasi

Excel tidak hanya digunakan untuk menyimpan data, pada umumnya seseorang akan menggunakan juga sebagai media untuk memvisualisasikan data yang telah diolah. Bisa untuk kebutuhan presentasi atau juga laporan. Menariknya adalah openpyxl sendiri sudah memiliki kemampuan untuk melakukan visualisasi dari IDE kita tanpa harus membuka file excel tersebut. Pertama kita import semua library yang dibutuhkan
from openpyxl import Workbook
from openpyxl.chart import BarChart, Series, Reference
import csv
Lalu kita akan buka data dari file csv yang kita miliki dan inisiasi file excel kita
#inisiasi excel
wb = Workbook()
ws = wb.active

#open file
data = open('pulau_indonesia.csv')
rows = csv.reader(data, delimiter=',')
Dengan memanfaatkan looping kita coba untuk membaca perbaris file csv kita dan memasukkannya ke dalam file excel
index = 0
for row in rows:
    data_clean = []
    for i in row:
        try:
            i = int(i)
        except:
            pass
        data_clean.append(i)
    ws.append(data_clean)
    index +=1
len_row = len(data_clean)
Agar data tersebut dapat diolah maka kita perlu mengubah semua data kita yang terlihat seperti angka tapi masih string (misal: “76”) menjadi integer (76). itulah guna dari kode ‘for i in row()’. variabel index digunakan untuk mengetahui jumlah row maksimum dan variabel len_row digunakan untuk mengetahui berapa banyak data kita. kedua variabel tersebut akan kita gunakan pada langkah selanjutnya. Sekarang saatnya kita menginisiasi chart kita, chart yang akan kita gunakan adalah jenis bar.
chart1 = BarChart()
chart1.type = "col"
chart1.style = 3
chart1.title = "Bar Chart"
chart1.y_axis.title = 'Jumlah Pulau'
chart1.x_axis.title = 'Nama Provinsi'
  • chart.type digunakan untuk menentukan jenis bar chart kita, apakah secara horizontal atau vertikal.
  • chart style digunakan untuk mengubah style atau warna dari chart kita
  • chart.title digunakan untuk mengganti Judul chart kita
  • axis.title digunakan untuk memberi judul axis kita
data = Reference(ws, min_col=3, min_row=1, max_row=index, max_col=len_row-1)
cats = Reference(ws, min_col=2, min_row=2, max_row=index)
chart1.height = 10 # default is 7.5
chart1.width = 30 # default is 15
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
ws.add_chart(chart1, "G2")
  • chart.heigt untuk mengatur tinggi chart
  • chart.width untuk mengatur lebar chart
  • chart.add_data untuk memasukkan data untuk dipakai chart kita, title_from_data jika ingin data kita langsung memasukkan legenda dari judul kolom.
  • chart.set_categories digunakan untuk memberi nilai axis x
Lalu setelah semua itu kita masukkan chart kita ke dalam workspaces dengan menggunakan add_chart(), dimana ‘G2’ adalah dimana saya ingin menempatkan chart saya. terakhir kita save file excel kita
wb.save("bar.xlsx")
Saat kita buka file excel tersebut akan terlihat seperti gambar di bawah.
Kode lengkap nya menjadi,
from openpyxl import Workbook
from openpyxl.chart import BarChart, Series, Reference
import csv

#inisiasi excel
wb = Workbook()
ws = wb.active

#open file
data = open('pulau_indonesia.csv')
rows = csv.reader(data, delimiter=',')

index = 0
for row in rows:
    data_clean = []
    for i in row:
        try:
            i = int(i)
        except:
            pass
        data_clean.append(i)
    ws.append(data_clean)
    index +=1
len_row = len(data_clean)

chart1 = BarChart()
chart1.type = "col"
chart1.style = 3
chart1.title = "Bar Chart"
chart1.y_axis.title = 'Jumlah Pulau'
chart1.x_axis.title = 'Nama Provinsi'

data = Reference(ws, min_col=3, min_row=1, max_row=index, max_col=len_row-1)
cats = Reference(ws, min_col=2, min_row=2, max_row=index)
chart1.height = 10 # default is 7.5
chart1.width = 30 # default is 15
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
ws.add_chart(chart1, "G2")

wb.save("bar.xlsx")
Kalian bisa mencoba mengubah-ubah chart kalian dengan meilhat dokumentasi, terdapat hampir semua jenis chart yang bisa kalian implementasikan sesuai kebutuhan.

Sumber pustaka

https://openpyxl.readthedocs.io/