Skip to Content

Berhenti Membuang Waktu di Excel: Biarkan Python Melakukan Pekerjaannya

Dari membaca file Excel hingga menghasilkan laporan visual dengan Python

Salah satu hal yang paling mengejutkan saya ketika memulai karir di bidang Analytics adalah betapa dominannya Excel sampai saat ini. Ketika sebuah analisis dipresentasikan atau insight ditampilkan melalui dashboard, pertanyaan "Apakah Anda juga memilikinya dalam format Excel?" sering muncul. Mentalitas "Saya akan menghilangkan kebutuhan akan Excel dari organisasi ini" dengan cepat menghilang ketika saya menyadari betapa dicintainya alat berusia 40 tahun ini.

Diperkirakan setidaknya 1,1 miliar orang menggunakan Excel. Ini masuk akal, karena Excel menyediakan antarmuka yang mudah digunakan, memungkinkan pengguna melakukan hampir semua operasi manipulasi data secara intuitif.

Namun, terlepas dari fleksibilitasnya, Excel memiliki beberapa kelemahan - pada saat tulisan ini dibuat, Excel (hanya) dapat menangani 1.048.576 baris dengan 16.384 kolom. Ini mungkin tampak banyak, tetapi sangat kecil untuk dunia big data. Selain itu, jika Anda ingin menyediakan laporan dengan metrik yang disesuaikan untuk tim atau orang tertentu, Anda akhirnya akan mengelola banyak file Excel yang berbeda.

Python menawarkan cara yang ampuh untuk mengelola tugas Excel. Dengan memanfaatkan kemampuan Python untuk menangani volume data yang besar dan berinteraksi dengan sistem operasi, pengguna dapat menghasilkan beberapa laporan secara bersamaan.

Dalam artikel ini, saya akan menunjukkan cara menggunakan Python untuk menyelesaikan tugas-tugas berikut:

  • Membaca dan menulis file Excel
  • Menghapus baris berdasarkan kondisi
  • Memformat kolom (tipe data)
  • Membuat kolom baru
  • Menyorot baris secara kondisional
  • Menerapkan perhitungan
  • Membuat grafik
  • Menghasilkan beberapa laporan berdasarkan kondisi

Skenario: Mimpi Buruk Manual Seorang Analis Kesehatan

Bayangkan ini: Anda adalah seorang analis kesehatan, bosan dengan rutinitas yang berulang. Secara berkala, Anda mengekstrak data janji temu pasien dari sistem ke file Excel. Tugas Anda adalah membersihkan dan memformat data serta mengekstrak pasien yang termasuk dalam penyedia asuransi tertentu agar dapat dibagikan kepada perusahaan terkait.

Setiap entri dalam data mentah merujuk pada pasien dan berisi ID Pasien, Usia, Jenis Kelamin, Dokter, Biaya Konsultasi, dan Penyedia Asuransi. Ada lima nilai penyedia asuransi yang mungkin: Aetna, Cigna, United Health, Blue Cross, atau None (untuk pasien tanpa asuransi).

Raw patient appointment data — Source: Author

Laporan yang Disesuaikan Tanpa Kekacauan

Setiap penyedia asuransi menerima laporan yang disesuaikan untuk mereka, yang hanya berisi pasien mereka. Laporan tersebut memiliki:

  • Biaya konsultasi dengan pajak yang dihitung
  • Kolom yang diformat dan disorot untuk wawasan cepat
  • Grafik batang yang memvisualisasikan biaya berdasarkan jenis diagnosis

Tidak ada lagi spreadsheet yang berantakan — hanya laporan bersih dan profesional yang siap dibagikan.

Report for each insurance provider — Created by Author

Namun, Anda tidak ingin membuat ikhtisar terpisah untuk setiap penyedia layanan setiap minggu secara manual, Anda lebih suka menyelami proyek yang lebih menarik. Jadi buatlah diagram, merencanakan pelarian Anda dari tugas yang membosankan ini.

Automated Report Generation Pipeline- Source:Author

Tujuan

Tujuannya sederhana: Anda harus dapat memberi Python dataset janji temu mentah kapan saja. Ini akan secara otomatis memprosesnya, membaginya berdasarkan penyedia asuransi, dan menghasilkan laporan Excel yang diformat dengan visualisasi.

Mari kita memulai aspek teknis!

Pilihan Perpustakaan Python

Seperti yang diketahui setiap praktisi data, tidak pernah ada satu cara untuk solusi. Python menawarkan berbagai perpustakaan untuk memanipulasi file Excel, masing-masing dengan kekuatannya sendiri. Berikut ringkasan singkat dari beberapa di antaranya:

  • Pandas — Pilihan utama untuk membaca file Excel besar karena mendukung chunking. Ini memungkinkan Anda memuat dan memproses data dalam bagian yang lebih kecil dan dapat dikelola. Secara efektif menangani lebih banyak data daripada yang biasanya dapat dikelola Excel. Namun, tidak menyediakan fitur untuk memformat atau menata file Excel.
  • XlsxWriter — Ideal untuk membuat laporan Excel dari awal. Ini menawarkan opsi pemformatan yang luas. Sayangnya, ini adalah perpustakaan write-only, Anda tidak dapat menggunakannya untuk membaca file Excel.
  • OpenPyXL — Perpustakaan serbaguna yang dapat membaca, menulis, dan memodifikasi file Excel. Mendukung pemformatan kompleks dan pembuatan grafik.

OpenPyXL jelas merupakan pilihan terbaik untuk proyek kita.

Implementasi Menggunakan OpenPyXL

Mari kita lihat langkah-langkah mendetail tentang cara mengimplementasikan otomatisasi Excel dengan Python:

EXCEL-AUTOMATION/
├── data/
│   ├── input/ # -> Here we store are raw data
│   ├── output/ #-> Target location 
│   ├── raw/
│   └── transformed/ #-> Here we store transformed data 
├── pipeline/
│   └── pipeline.py #-> Python script that does the heavy lifting
├── requirements.txt

1. Menginstal Dependencies

Pertama, kita mulai dengan menginstal perpustakaan OpenPyXL menggunakan perintah berikut:

python
pip install openpyxl

2. Mengimpor Library

Kemudian kita mengimpor library yang akan kita gunakan:

python
import os
import openpyxl

3. Membaca File Excel

Kita dapat membaca workbook Excel dan memilih sheet yang diinginkan dalam file Excel menggunakan baris kode berikut:

python
# Membaca workbook
workbook = openpyxl.load_workbook(
    r"data/input/Healthcare_Appointments_Dataset.xlsx"
)

# Mengakses sheet yang diperlukan
sheet = workbook['Healthcare_Appointments_Dataset']

Penjelasan: 

  • Kode di atas membuka file Excel yang ditentukan dan mengakses sheet tertentu dalam file tersebut. 
  • Objek sheet mewakili satu sheet dalam workbook Excel yang memungkinkan kita berinteraksi dengan data dan sel.

4. Menampilkan Header Kolom

Untuk melihat header kolom dalam dataset kita secara cepat, kita dapat mengiterasi melalui baris pertama sebagai berikut:

python
# mencetak nilai dari baris pertama
for cell in sheet[1]:
    print(cell.value)

Appointment Date
Patient ID
Age
Gender
Diagnosis
Doctor
Visit Type
Appointment Duration
Consultation Fee
Insurance Provider
Is Follow-up Needed?

5. Menghapus Baris dengan ID Pasien yang Hilang

Dalam file Excel kita, beberapa baris tidak berisi ID pasien. Mari buat fungsi yang menghapus entri ini:

python
# menghapus baris dengan nilai yang hilang di kolom 'PatientId'
def delete_missing_patient_id_rows(sheet):
    for row in sheet.iter_rows():
        if row[1].value == None:
            sheet.delete_rows(row[0].row)

Penjelasan: 

  • Fungsi ini mengambil objek sheet OpenPyXL sebagai argumen. 
  • sheet.iter_rows() menghasilkan iterable dari semua baris dalam worksheet. 
  • Kemudian memeriksa apakah nilai kedua (ID pasien kita) kosong (row[1].value == None). 
  • Metode delete_rows() kemudian menghapus baris pada nomor indeks yang dikembalikan oleh row.row.

6. Memformat Tipe Kolom

Untuk fungsi kedua kita, kita ingin memformat nilai numerik "Biaya Konsultasi" dengan tanda Euro:

python
def apply_currency_format(sheet):
    for row in sheet.iter_rows():
        if row[8].value is not None:
            # Mengatur format angka ke mata uang Euro
            row[8].number_format = '€#,##0.00'

Penjelasan: 

  • Kita menggunakan loop for dengan metode iter_rows(). 
  • Kita memeriksa apakah nilai di kolom ke-9 (row[8]) tidak None untuk menghindari error. 
  • number_format diatur ke '€#,##0.00', yang menampilkan nilai sebagai jumlah Euro (misalnya, €1.234,56).

7. Menambahkan Kolom Baru: "Memiliki Asuransi"

python
# Fungsi menambahkan kolom baru
def add_column(sheet, column_name):
    sheet.insert_cols(11)
    sheet['K1'] = column_name
    for row in sheet.iter_rows(min_row=2):
        if row[9].value == 'None':
            row[10].value = 'No'
        else:
            row[10].value = 'Yes'

Penjelasan: 

  • Kita menggunakan insert_cols(11) untuk membuat kolom baru pada posisi 11 (atau kolom "K"). 
  • Header kolom baru diatur menggunakan sheet['K1'] = column_name. 
  • Kita melakukan loop pada setiap baris (dimulai dari baris kedua) dan memeriksa apakah nilai di kolom ke-10 (row[9], yang berisi informasi asuransi) adalah 'None'. Jika 'None', kita mengatur nilai kolom baru ke 'No'; jika tidak, diatur ke 'Yes'.

8. Pemformatan Kondisional: Menyorot Tindak Lanjut

python
# Baris tindak lanjut berwarna hijau
def highlight_follow_ups(sheet):
    for row in sheet.iter_rows(min_row=2):
        if row[11].value == True:
            for cell in row:
                cell.fill = openpyxl.styles.PatternFill(
                    start_color='FF77DD77', 
                    end_color='FF77DD77', 
                    fill_type='solid')

Penjelasan: 

  • Kita melakukan loop melalui baris, dimulai dari baris kedua (untuk melewati header). 
  • Jika nilai di kolom ke-12 (row) adalah True, itu berarti tindak lanjut sudah dijadwalkan. 
  • Kita menggunakan PatternFill untuk menerapkan sorotan hijau (#FF77DD77) ke setiap sel di baris tersebut.

9. Menerapkan Perhitungan Pajak

python
def apply_tax(sheet):
    for row in sheet.iter_rows(min_row=2):
        if row[8].value != None:
            row[8].value = float(row[8].value) * 1.15

Penjelasan:

  • Fungsi ini memperbarui kolom "Biaya Konsultasi" dengan menerapkan pajak 15% ke setiap nilai. 
  • Kita mengiterasi melalui setiap baris lembar, melewati header. 
  • Kita memeriksa apakah nilai di kolom ke-9 (row[8], yang merupakan "Biaya Konsultasi") tidak None untuk menghindari error. 
  • Kita mengalikan biaya dengan 1,15, secara efektif menambahkan pajak 15% ke biaya tersebut.

10. Menyimpan Workbook Terpisah untuk Setiap Penyedia Asuransi

python
# Menyimpan workbook yang berbeda untuk setiap perusahaan asuransi 
def save_workbook_per_insurance(input_path):

    # menemukan file yang berakhiran dengan .xlsx di path input
    xlsx_file = [file for file in os.listdir(input_path) if file.endswith('.xlsx')]
    workbook = openpyxl.load_workbook(input_path + "/"+ xlsx_file[0])
    sheet = workbook.active

    insurance_companies = sheet['J']
    insurance_companies = set([cell.value for cell in insurance_companies])
    for company in insurance_companies:
        new_workbook = openpyxl.Workbook()
        new_sheet = new_workbook.active
        new_sheet.title = company
        
        new_sheet.append([cell.value for cell in sheet[1]]) # Menyalin header
        for row in sheet.iter_rows(min_row=2):
            if row[9].value == company:
                new_sheet.append([cell.value for cell in row])
        new_workbook.save(f'data/transformed/{company}.xlsx')

Penjelasan: 

  • Fungsi ini membuat workbook Excel baru untuk setiap perusahaan asuransi dalam dataset. 
  • Ini memfilter data berdasarkan perusahaan asuransi dan menyimpan baris yang sesuai dalam file terpisah. 
  • Kita mengekstrak set perusahaan asuransi unik dari kolom J. Workbook baru dibuat untuk setiap perusahaan, dan baris yang relevan disalin. 
  • Baris header disalin terlebih dahulu, diikuti oleh baris yang difilter. 
  • Setiap file disimpan di direktori data/transformed/ dengan nama perusahaan.

11. Membuat Sheet Ringkasan dan Grafik Batang

python
def add_summary(workbook, sheet):
    diagnosis_fees = {}

    # Iterasi melalui baris data dan akumulasikan biaya berdasarkan diagnosis
    for row in sheet.iter_rows(min_row=2, values_only=True):
        diagnosis = str(row[4])
        consultation_fee = float(row[8])

        # Perbarui total biaya untuk setiap diagnosis
        if diagnosis in diagnosis_fees:
            diagnosis_fees[diagnosis] += consultation_fee
        else:
            diagnosis_fees[diagnosis] = consultation_fee

    # Buat sheet ringkasan baru
    summary_sheet = workbook.create_sheet('Summary')
    summary_sheet.append(['Diagnosis', 'Total Fee'])

    # Tambahkan diagnosis dan total biaya ke summary sheet
    for diagnosis, fee in diagnosis_fees.items():
        summary_sheet.append([diagnosis, fee])

Penjelasan: 

  • Fungsi ini menghasilkan sheet ringkasan di tab kedua file Excel, menampilkan total biaya konsultasi dikelompokkan berdasarkan diagnosis. 
  • Data yang diringkas ini akan menjadi dasar untuk membuat grafik batang pada langkah berikutnya. 
  • Kita membuat dictionary untuk menyimpan total biaya untuk setiap diagnosis. 
  • Fungsi ini mengiterasi melalui setiap baris, mengekstrak diagnosis dan biaya konsultasi. 
  • Jika diagnosis sudah ada dalam dictionary, kita menambahkan biaya; jika tidak, kita membuat entri baru. 
  • Akhirnya, sheet baru bernama "Summary" dibuat, dan hasilnya ditambahkan.

12. Membuat Grafik

python
def add_chart(workbook):
    summary_sheet = workbook['Summary']
    first_sheet = workbook[workbook.sheetnames[0]]

    # Membuat objek BarChart
    chart = openpyxl.chart.BarChart()
    chart.title = 'Diagnosis Fees'
    chart.x_axis.title = 'Diagnosis'
    chart.y_axis.title = 'Total Fee'

    # Mendefinisikan data untuk grafik
    labels = openpyxl.chart.Reference(summary_sheet, min_col=1, min_row=2, max_row=summary_sheet.max_row)
    data = openpyxl.chart.Reference(summary_sheet, min_col=2, min_row=1, max_row=summary_sheet.max_row)

    # Menambahkan data dan label ke grafik
    chart.add_data(data, titles_from_data=True)
    chart.set_categories(labels)

    # Menambahkan grafik ke sheet pertama
    first_sheet.add_chart(chart, 'M1')

Penjelasan: 

  • Fungsi ini membuat grafik batang dalam file Excel menggunakan kelas BarChart OpenPyXL dan data ringkasan yang telah kita definisikan sebelumnya dalam fungsi. 
  • Label diambil dari kolom pertama sheet "Summary" (nama diagnosis). Data diambil dari kolom kedua (total biaya konsultasi). 
  • Grafik menggunakan label dan data ini untuk menampilkan total biaya per diagnosis. 
  • Grafik ditambahkan ke sheet pertama pada posisi M1.

13. Menyatukan Semuanya

python
def run_all(input_path, output_path, new_column_name, sheet_name=None):

    # Simpan workbook berbeda untuk setiap perusahaan asuransi
    save_workbook_per_insurance(input_path)

    for file in os.listdir(input_path):
        if file.endswith('.xlsx'):
            file_path = os.path.join(input_path, file)
            workbook = openpyxl.load_workbook(file_path)
            sheet = workbook[sheet_name] if sheet_name else workbook.active

            # Terapkan semua fungsi pemrosesan
            delete_missing_patient_id_rows(sheet)
            apply_currency_format(sheet)
            add_column(sheet, new_column_name)
            highlight_follow_ups(sheet)
            apply_tax(sheet)
            add_summary(workbook, sheet)
            add_chart(workbook)

            # Simpan workbook yang dimodifikasi
            output_file_path = os.path.join(output_path, file)
            workbook.save(output_file_path)
            print(f"Processed and saved: {output_file_path}")

Penjelasan: Fungsi ini menggabungkan semuanya menjadi satu proses yang terstruktur, menerapkan semua fungsi yang telah kita definisikan sebelumnya. Parameter:

  • input_path: Path ke folder yang berisi file Excel.
  • output_path: Path tempat file yang diproses akan disimpan.
  • new_column_name: Nama kolom baru yang akan ditambahkan.
  • sheet_name (opsional): Nama sheet tertentu untuk diproses; jika dihilangkan, defaultnya adalah sheet aktif.

Kesimpulan

Dengan menggunakan Python untuk mengotomatisasi tugas Excel yang berulang, Anda dapat menghemat banyak waktu dan fokus pada analisis data yang lebih berharga. Pendekatan ini memungkinkan Anda:

  1. Memproses data dalam jumlah yang jauh lebih besar daripada yang dapat ditangani Excel secara native
  2. Menstandarisasi format dan perhitungan di seluruh beberapa laporan
  3. Menghasilkan laporan yang disesuaikan secara otomatis untuk berbagai pemangku kepentingan
  4. Menghilangkan kesalahan manual dalam pemrosesan data
  5. Menambahkan visualisasi yang konsisten ke semua laporan Anda

Yang terpenting, Anda tidak perlu lagi membuat laporan Excel secara manual setiap minggu atau bulan. Cukup jalankan script Python Anda, dan proses yang membosankan akan menjadi tugas otomatis yang membutuhkan waktu sepersekian dari apa yang biasanya Anda lakukan.

Glossary: Terminologi Penting untuk Data Analyst

  • OpenPyXL: Library Python yang memungkinkan manipulasi file Excel (.xlsx), termasuk membaca, menulis dan memodifikasi file.
  • Workbook: Istilah untuk file Excel utuh yang dapat berisi beberapa sheet.
  • Sheet: Lembar kerja tunggal dalam file Excel.
  • Iter_rows(): Metode dalam OpenPyXL yang menghasilkan iterator untuk mengakses baris dalam worksheet.
  • PatternFill: Kelas dalam OpenPyXL yang digunakan untuk menerapkan warna latar belakang atau pola ke sel.
  • BarChart: Kelas dalam OpenPyXL untuk membuat grafik batang dalam Excel.
  • Data Transformation: Proses mengubah data dari satu format atau struktur ke format atau struktur lain.
  • Conditional Formatting: Teknik untuk mengubah tampilan sel berdasarkan kondisi atau nilai tertentu.
  • Cell Reference: Cara mengidentifikasi sel individual dalam spreadsheet, biasanya dalam format [kolom][baris], seperti A1, B2, dll.

Citations:

  1. https://www.youtube.com/watch?v=esaaaTJu574
  2. https://www.datacamp.com/tutorial/python-excel-tutorial
  3. https://www.freecodecamp.org/news/automate-excel-tasks-with-python/
  4. https://www.udemy.com/course/ms-excel-automation-excel-data-analysis-with-python/
  5. https://plotly.com/blog/automate-excel-reports-with-python/
  6. https://www.dataquest.io/blog/excel-and-pandas/
  7. https://www.youtube.com/watch?v=WcDaZ67TVRo
  8. https://www.simplilearn.com/tutorials/python-tutorial/how-to-automate-excel-sheet-in-python
  9. https://www.youtube.com/watch?v=H4XbvL8Mglc


Panduan Lengkap: Menggunakan ChatGPT Advanced Data Analysis untuk Analisis Data Tanpa Coding