Cara Menggabungkan Banyak File Excel dengan Python Pandas: Tutorial Lengkap di Google Colab dan PC

Table of Contents

Cara Menggabungkan Banyak File Excel dengan Python Pandas: Tutorial Lengkap di Google Colab

Apakah Anda memiliki banyak file Excel yang berisi data siswa dan nilai, kemudian ingin menggabungkannya menjadi satu database secara otomatis? Jika dilakukan secara manual, pekerjaan tersebut bisa memakan waktu lama dan berisiko menimbulkan kesalahan pencocokan data.

Salah satu solusi yang dapat digunakan adalah Python dengan library Pandas. Dengan Python, kita dapat membaca beberapa file Excel sekaligus, mencocokkan nama siswa, menggabungkan nilai berdasarkan mata pelajaran, serta menghasilkan laporan data yang berhasil maupun gagal dicocokkan.

Dalam tutorial ini, kita akan membuat program untuk menggabungkan database siswa dengan file lembar jawaban siswa kelas X, XI, dan XII. Proses pengerjaan dapat dilakukan menggunakan Google Colab tanpa harus menginstal Python di komputer.

1. Tujuan dan Gambaran Proyek

Proyek ini menggunakan empat file Excel yang terdiri atas satu database utama dan tiga file nilai siswa.

Nama File	Fungsi
DB Siswa.xlsx	Database utama seluruh siswa
Lembar Jawab Siswa Kelas X.xlsx	Data nilai siswa kelas X
Lembar Jawab Siswa Kelas XI.xlsx	Data nilai siswa kelas XI
Lembar Jawab Siswa Kelas XII.xlsx	Data nilai siswa kelas XII

Program akan membaca seluruh sheet pada setiap file nilai. Nama sheet akan digunakan sebagai identitas mata pelajaran, sedangkan nama siswa digunakan untuk mencocokkan data dengan database utama.

Hasil akhirnya adalah sebuah file Excel baru yang berisi database siswa beserta nilai dari berbagai mata pelajaran. Program juga menghasilkan laporan untuk memeriksa nama ganda dan data yang tidak ditemukan.

2. Persiapan Struktur File Excel

Sebelum menjalankan program, pastikan struktur database utama memiliki kolom berikut:

• Nama Lengkap: nama siswa sesuai database utama.
• Kelas: kelas atau tingkat siswa (jika tersedia).
• Kolom lainnya: NIS, NISN, jurusan, dan informasi siswa lainnya.

Contoh database utama:

No	Nama Lengkap	Kelas	Jurusan
1	Andi Saputra	X	TKL
2	Budi Santoso	XI	DKV
3	Citra Amelia	XII	TPFL

Selanjutnya, setiap sheet pada file lembar jawaban harus memiliki minimal dua kolom:

• Nama Peserta Didik: nama siswa yang mengikuti ujian.
• Score: nilai hasil pengerjaan.

Contoh data nilai:

Nama Peserta Didik	Score
Andi Saputra	85
Budi Santoso	90
Citra Amelia	88

Catatan: Jika nama kolom pada file Anda berbeda, sesuaikan pengaturan nama kolom pada script Python.

3. Mengenal Library Python yang Digunakan

Program ini menggunakan beberapa library Python dengan fungsi sebagai berikut:

• Pandas: membaca, mengolah, membersihkan, dan menggabungkan data.
• OpenPyXL: membantu membaca dan memformat file Excel.
• Pathlib: memeriksa keberadaan file.
• Re: membersihkan format nama siswa menggunakan regular expression.

Pandas sangat membantu dalam pengolahan data tabular karena menyediakan fungsi seperti read_excel(), concat(), dan merge().

4. Menjalankan Python Menggunakan Google Colab

Google Colab merupakan layanan notebook berbasis cloud yang memungkinkan pengguna menjalankan Python melalui browser. Dengan demikian, pengguna tidak perlu melakukan instalasi Python secara manual.

1. Buka Google Colab.
2. Login menggunakan akun Google.
3. Pilih menu File kemudian New Notebook.
4. Siapkan file Excel yang akan diproses.
5. Salin script Python pada bagian berikutnya.
6. Jalankan program menggunakan tombol Run.

5. Mengunggah File Excel ke Google Colab

Sebelum menjalankan program utama, unggah empat file Excel ke lingkungan Google Colab.

Gunakan kode berikut pada cell pertama:

from google.colab import files
uploaded = files.upload()
print("File yang berhasil diunggah:")
for nama_file in uploaded.keys():
    print("-", nama_file)

Pilih empat file Excel yang telah disiapkan. Pastikan nama file sesuai dengan pengaturan program.

6. Script Python Lengkap untuk Menggabungkan Banyak File Excel

Berikut merupakan program utama untuk membaca database siswa, memproses seluruh sheet nilai, melakukan pencocokan nama, dan menghasilkan file Excel baru.

Program ini juga dilengkapi validasi untuk mencegah penggabungan data yang ambigu akibat nama siswa ganda.

# ==========================================================
# PROGRAM MENGGABUNGKAN DATA SISWA DAN NILAI EXCEL
# PYTHON + PANDAS + OPENPYXL
# GOOGLE COLAB
# ==========================================================
import pandas as pd
import re
from pathlib import Path
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils import get_column_letter
# ==========================================================
# 1. PENGATURAN PROGRAM
# ==========================================================
FILE_DATABASE = "DB Siswa.xlsx"
FILE_NILAI = {
    "X": "Lembar Jawab Siswa Kelas X.xlsx",
    "XI": "Lembar Jawab Siswa Kelas XI.xlsx",
    "XII": "Lembar Jawab Siswa Kelas XII.xlsx"
}
KOLOM_DATABASE = "Nama Lengkap"
KOLOM_KELAS = "Kelas"
KOLOM_NAMA_NILAI = "Nama Peserta Didik"
KOLOM_SCORE = "Score"
FILE_OUTPUT = "DB_Siswa_Final.xlsx"
# ==========================================================
# 2. FUNGSI MEMBERSIHKAN NAMA
# ==========================================================
def bersihkan_nama(nama):
    if pd.isna(nama):
        return ""
    nama = str(nama).strip().upper()
    # Menghapus spasi berlebih
    nama = re.sub(r"\s+", " ", nama)
    return nama
# ==========================================================
# 3. MEMERIKSA FILE
# ==========================================================
if not Path(FILE_DATABASE).exists():
    raise FileNotFoundError(
        f"Database tidak ditemukan: {FILE_DATABASE}"
    )
for kelas, file in FILE_NILAI.items():
    if not Path(file).exists():
        raise FileNotFoundError(
            f"File nilai kelas {kelas} tidak ditemukan: {file}"
        )
# ==========================================================
# 4. MEMBACA DATABASE UTAMA
# ==========================================================
db = pd.read_excel(FILE_DATABASE)
if KOLOM_DATABASE not in db.columns:
    raise ValueError(
        f"Kolom {KOLOM_DATABASE} tidak ditemukan."
    )
# Membuat kunci pencocokan
db["_KEY"] = db[KOLOM_DATABASE].apply(
    bersihkan_nama
)
if (db["_KEY"] == "").any():
    raise ValueError(
        "Database memiliki nama siswa kosong."
    )
# Memeriksa nama ganda pada database
duplikat_db = db[
    db.duplicated("_KEY", keep=False)
]
if not duplikat_db.empty:
    duplikat_db.drop(
        columns=["_KEY"]
    ).to_excel(
        "Laporan_Duplikat_Database.xlsx",
        index=False
    )
    raise ValueError(
        "Ditemukan nama siswa ganda dalam database. "
        "Periksa Laporan_Duplikat_Database.xlsx. "
        "Gunakan NISN sebagai kunci jika tersedia."
    )
# Database hasil awal
hasil = db.copy()
# Tempat laporan
laporan_tidak_cocok = []
laporan_duplikat = []
laporan_proses = []
# ==========================================================
# 5. MEMBACA SELURUH FILE DAN SHEET
# ==========================================================
for kelas, file in FILE_NILAI.items():
    print("=" * 55)
    print(f"MEMPROSES KELAS {kelas}")
    print(f"FILE: {file}")
    print("=" * 55)
    excel = pd.ExcelFile(file)
    for sheet in excel.sheet_names:
        print(f"Memproses sheet: {sheet}")
        df = pd.read_excel(
            excel,
            sheet_name=sheet
        )
        # Memeriksa kolom wajib
        kolom_wajib = [
            KOLOM_NAMA_NILAI,
            KOLOM_SCORE
        ]
        if not all(
            kolom in df.columns
            for kolom in kolom_wajib
        ):
            laporan_proses.append({
                "Kelas": kelas,
                "File": file,
                "Sheet": sheet,
                "Status": "Dilewati: kolom tidak lengkap"
            })
            continue
        # Mengambil kolom yang diperlukan
        df = df[
            [KOLOM_NAMA_NILAI, KOLOM_SCORE]
        ].copy()
        # Membersihkan nama
        df["_KEY"] = df[
            KOLOM_NAMA_NILAI
        ].apply(bersihkan_nama)
        # Menghapus nama kosong
        df = df[
            df["_KEY"] != ""
        ].copy()
        # ==================================================
        # 6. MEMERIKSA NAMA GANDA
        # ==================================================
        duplikat = df[
            df.duplicated("_KEY", keep=False)
        ].copy()
        if not duplikat.empty:
            duplikat["Kelas"] = kelas
            duplikat["File"] = file
            duplikat["Sheet"] = sheet
            laporan_duplikat.append(
                duplikat
            )
            # Nama ganda tidak dipakai otomatis
            df = df[
                ~df["_KEY"].isin(
                    duplikat["_KEY"]
                )
            ].copy()
        # ==================================================
        # 7. MEMBUAT NAMA KOLOM NILAI
        # ==================================================
        nama_mapel = str(sheet).strip()
        nama_kolom = (
            f"{kelas} - {nama_mapel}"
        )
        if nama_kolom in hasil.columns:
            raise ValueError(
                f"Kolom {nama_kolom} sudah tersedia."
            )
        # Mengambil data nilai
        data_nilai = df[
            ["_KEY", KOLOM_SCORE]
        ].rename(
            columns={
                KOLOM_SCORE: nama_kolom
            }
        )
        # ==================================================
        # 8. MEMERIKSA SISWA TIDAK DITEMUKAN
        # ==================================================
        tidak_cocok = data_nilai[
            ~data_nilai["_KEY"].isin(
                db["_KEY"]
            )
        ].copy()
        if not tidak_cocok.empty:
            tidak_cocok["Kelas"] = kelas
            tidak_cocok["File"] = file
            tidak_cocok["Sheet"] = sheet
            laporan_tidak_cocok.append(
                tidak_cocok
            )
        # ==================================================
        # 9. MENGGABUNGKAN DATA
        # ==================================================
        hasil = hasil.merge(
            data_nilai,
            on="_KEY",
            how="left",
            validate="one_to_one"
        )
        laporan_proses.append({
            "Kelas": kelas,
            "File": file,
            "Sheet": sheet,
            "Jumlah Data": len(df),
            "Jumlah Duplikat": len(duplikat),
            "Jumlah Tidak Cocok": len(tidak_cocok),
            "Status": "Berhasil diproses"
        })
# ==========================================================
# 10. MENGHAPUS KOLOM SEMENTARA
# ==========================================================
hasil.drop(
    columns=["_KEY"],
    inplace=True
)
# ==========================================================
# 11. MENYIAPKAN LAPORAN
# ==========================================================
if laporan_tidak_cocok:
    df_tidak_cocok = pd.concat(
        laporan_tidak_cocok,
        ignore_index=True
    )
else:
    df_tidak_cocok = pd.DataFrame(
        columns=[
            KOLOM_NAMA_NILAI,
            KOLOM_SCORE,
            "Kelas",
            "File",
            "Sheet"
        ]
    )
if laporan_duplikat:
    df_duplikat = pd.concat(
        laporan_duplikat,
        ignore_index=True
    )
else:
    df_duplikat = pd.DataFrame(
        columns=[
            KOLOM_NAMA_NILAI,
            KOLOM_SCORE,
            "Kelas",
            "File",
            "Sheet"
        ]
    )
df_log = pd.DataFrame(
    laporan_proses
)
# ==========================================================
# 12. MENYIMPAN HASIL KE EXCEL
# ==========================================================
with pd.ExcelWriter(
    FILE_OUTPUT,
    engine="openpyxl"
) as writer:
    # Sheet utama
    hasil.to_excel(
        writer,
        sheet_name="Data Lengkap",
        index=False
    )
    # Laporan nama tidak cocok
    df_tidak_cocok.to_excel(
        writer,
        sheet_name="Tidak Cocok",
        index=False
    )
    # Laporan duplikat
    df_duplikat.to_excel(
        writer,
        sheet_name="Duplikat",
        index=False
    )
    # Log proses
    df_log.to_excel(
        writer,
        sheet_name="Log Proses",
        index=False
    )
    # ======================================================
    # 13. FORMAT TAMPILAN EXCEL
    # ======================================================
    for ws in writer.sheets.values():
        ws.freeze_panes = "B2"
        ws.auto_filter.ref = ws.dimensions
        # Format header
        for cell in ws[1]:
            cell.font = Font(
                bold=True,
                color="FFFFFF"
            )
            cell.fill = PatternFill(
                fill_type="solid",
                fgColor="1565C0"
            )
            cell.alignment = Alignment(
                horizontal="center",
                vertical="center"
            )
        # Mengatur lebar kolom
        for kolom in ws.columns:
            panjang = max(
                len(str(cell.value or ""))
                for cell in kolom
            )
            huruf = get_column_letter(
                kolom[0].column
            )
            ws.column_dimensions[
                huruf
            ].width = min(
                panjang + 3,
                40
            )
# ==========================================================
# 14. MENAMPILKAN RINGKASAN
# ==========================================================
print("\n")
print("=" * 55)
print("PROSES PENGGABUNGAN SELESAI")
print("=" * 55)
print(
    f"Jumlah siswa database : {len(db)}"
)
print(
    f"Jumlah kolom hasil    : {len(hasil.columns)}"
)
print(
    f"Nama tidak ditemukan  : {len(df_tidak_cocok)}"
)
print(
    f"Data nama ganda       : {len(df_duplikat)}"
)
print(
    f"Jumlah sheet diproses : {len(df_log)}"
)
print(
    f"File hasil            : {FILE_OUTPUT}"
)
print("=" * 55)
print(
    "Periksa laporan validasi sebelum menggunakan hasil."
)

7. Mengunduh Hasil Penggabungan Excel

Setelah program selesai dijalankan, hasil penggabungan akan tersimpan dengan nama DB_Siswa_Final.xlsx.

Jika menggunakan Google Colab, jalankan kode berikut pada cell baru:

from google.colab import files
files.download("DB_Siswa_Final.xlsx")

File Excel hasil penggabungan akan diunduh ke komputer secara otomatis.

8. Memahami Struktur Hasil Penggabungan

Program menghasilkan satu file Excel dengan empat sheet utama.

Nama Sheet	Keterangan
Data Lengkap	Database siswa yang sudah dilengkapi nilai dari berbagai mata pelajaran dan tingkat kelas.
Tidak Cocok	Daftar nama yang ditemukan pada file nilai tetapi tidak terdapat pada database utama.
Duplikat	Daftar nama yang muncul lebih dari satu kali pada sheet nilai.
Log Proses	Informasi setiap file dan sheet yang berhasil atau dilewati.

Contoh gambaran hasil pada sheet Data Lengkap:

Nama Lengkap	Kelas	X - KKA	XI - Bahasa Inggris	XII - Matematika
Andi Saputra	X	85
Budi Santoso	XI		90
Citra Amelia	XII			88

Nilai kosong menunjukkan bahwa tidak ditemukan data nilai yang cocok untuk siswa dan mata pelajaran tersebut. Nilai kosong tidak otomatis berarti siswa mendapatkan nilai nol.

9. Mengapa Pencocokan Nama Harus Divalidasi?

Dalam pengolahan database siswa, pencocokan berdasarkan nama memiliki keterbatasan. Dua siswa bisa memiliki nama yang sama, sedangkan satu siswa bisa ditulis dengan format nama yang berbeda pada beberapa file.

Contohnya:

• ANDI SAPUTRA
• Andi Saputra
• Andi Saputra

Program melakukan normalisasi sederhana untuk menghilangkan perbedaan huruf besar-kecil dan spasi berlebih.

Namun, normalisasi tersebut tidak dapat memastikan bahwa dua nama yang sama benar-benar merujuk kepada orang yang sama. Karena itu, untuk database sekolah yang digunakan secara resmi, sebaiknya gunakan NISN atau nomor induk siswa sebagai kunci pencocokan apabila tersedia pada seluruh file.

Periksa juga laporan Duplikat dan Tidak Cocok sebelum menggunakan hasil penggabungan untuk keperluan penilaian.

10. Menjalankan Program di Komputer Windows

Selain Google Colab, program ini dapat dijalankan langsung pada komputer atau laptop Windows yang telah memiliki Python.

Langkah-langkahnya:

1. Instal Python dari website resmi Python.
2. Buka Command Prompt atau Terminal.
3. Instal library yang diperlukan.
4. Letakkan seluruh file Excel dalam satu folder dengan script Python.
5. Simpan program dengan nama gabung_excel.py.
6. Jalankan program menggunakan perintah Python.

Perintah instalasi library:

pip install pandas openpyxl

Perintah menjalankan program:

python gabung_excel.py

Pastikan nama file Excel dan nama kolom sudah sesuai dengan konfigurasi pada program.

11. Permasalahan yang Sering Terjadi

A. File Excel tidak ditemukan

Pastikan semua file telah diunggah ke Google Colab atau berada dalam folder yang sama dengan program Python. Periksa juga penulisan nama file.

B. Kolom nama tidak ditemukan

Periksa nama kolom pada Excel. Jika kolom yang digunakan bernama Nama Siswa, ubah variabel KOLOM_NAMA_NILAI menjadi Nama Siswa.

C. Nama siswa tidak cocok

Periksa kemungkinan kesalahan penulisan nama, perbedaan gelar, singkatan, atau penggunaan nama panggilan. Cocokkan dengan identitas siswa yang benar.

D. Ditemukan nama siswa ganda

Periksa laporan duplikat. Jangan langsung menghapus salah satu data karena keduanya mungkin merupakan siswa yang berbeda atau data nilai yang tercatat lebih dari satu kali.

E. Nilai tidak muncul pada hasil akhir

Periksa kembali nama sheet, kolom Score, nama siswa, dan laporan Tidak Cocok. Pastikan data nilai memang tersedia pada file sumber.

12. Kesimpulan

Menggabungkan banyak file Excel tidak harus dilakukan secara manual. Dengan Python dan Pandas, proses pengolahan database siswa dapat dilakukan secara lebih cepat, terstruktur, dan berulang secara otomatis.

Melalui tutorial ini, kita telah mempelajari cara membaca banyak file Excel, memproses seluruh sheet, membersihkan nama siswa, mencocokkan data, menggabungkan nilai, serta membuat laporan validasi.

Google Colab juga memberikan kemudahan karena program dapat dijalankan langsung melalui browser tanpa perlu instalasi Python di komputer.

Teknik ini dapat dikembangkan lebih lanjut untuk pengolahan nilai ujian, rekapitulasi hasil belajar, pengolahan data sekolah, dan berbagai kebutuhan administrasi pendidikan lainnya.

---

Referensi dan Dokumentasi

• Dokumentasi Resmi Pandas
• Dokumentasi OpenPyXL
• Google Colab
• Dokumentasi Python

Penulis: Tim KelasKodingID

KelasKodingID – Belajar, Berkarya, dan Berkembang Bersama Teknologi.