Cara menggunakan sql python excel

Pandas adalah library python yang bersifat open source yang dibuat terutama untuk bekerja dengan data relasional atau berlabel secara mudah. Library yang satu ini menyediakan berbagai struktur data dan operasi untuk memanipulasi data numerik dan deret waktu.

Pandas dibangun di atas library NumPy. Salah satu kelebihan dari library pandas adalah lebih cepat dan memiliki kinerja serta produktivitas tinggi untuk berbagai pekerjaan pengolahan data.

Pandas awalnya dikembangkan oleh Wes McKinney pada tahun 2008 saat dia bekerja di AQR Capital Management. Dia memperbolehkan AQR untuk membuka sumber Pandas. Karyawan AQR lainnya, Chang She, bergabung sebagai kontributor utama untuk membangun library ini pada tahun 2012. Seiring berjalannya waktu, banyak versi pandas yang telah dirilis.

Versi terbaru pandas adalah 1.4.1. Pandas adalah salah satu library yang wajib dikuasai oleh seorang pemula karena library ini akan sangat berguna untuk berbagai tugas. Pada artikel kali ini kita akan belajar cara menggunakan library pandas khususnya untuk beginner. Jadi untuk kamu yang sedang belajar Data Science menggunakan Python, yuk simak artikel ini sampai akhir!

1. Install Library Pandas

Langkah pertama bekerja menggunakan pandas adalah memastikan library tersebut sudah terpasang di folder Python. Jika belum terinstall, maka kita perlu menginstalnya di sistem kita menggunakan perintah pip.

Caranya gampang, kita bisa menggunakan python-pip. Cukup ketikkan script di bawah ini kemudian run, maka library akan terinstall dan bisa digunakan.

Baca juga: Coding Python Simpel untuk Operasi Matematika

2. Mengimport Library Python

Setelah pandas diinstal ke dalam sistem, kita perlu mengimpor library ini agar bisa digunakan. Modul ini umumnya diimpor sebagai:

Istilah pd disebut sebagai alias untuk Pandas. Namun, sebenarnya kita tidak perlu mengimpor perpustakaan menggunakan alias, itu hanya sebagai pembantu untuk mempermudah dalam menulis code yang lebih singkat.

Alias ini juga bisa kita ubah sesuai keinginan kita. Pada umumnya, pandas bisa digunakan untuk manipulasi dua struktur data, yaitu series dan dataframe.

3. Series dengan Pandas

Di dunia nyata, Seri Pandas akan dibuat dengan memuat kumpulan data dari penyimpanan yang sudah ada. Penyimpanan ini dapat berupa database SQL, file CSV, file Excel, dan data storage lainnya.

Series pandas dapat dibuat dari list, dictionary, dari nilai skalar, dan lain sebagainya. Berikut ini adalah cara untuk membuat series menggunakan pandas.

4. Dataframe dengan Pandas

Pandas dataframe adalah struktur data tabular dua dimensi yang dapat berubah ukuran dan berpotensi heterogen dengan sumbu berlabel (baris dan kolom).

Dataframe terdiri dari tiga komponen utama, yaitu data, baris, dan kolom. Gambar di bawah ini adalah salah satu cara untuk membuat data frame menggunakan pandas.

Baca juga: Belajar Python: Mengenal Array pada Bahasa Pemrograman Python

Ingin belajar fungsi dan kegunaan lain yang lebih kompleks menggunakan pandas python? Yuk belajar dengan berbagai modul data science bersama DQLab!

Klik button di bawah ini atau sign up melalui DQLab.id untuk mengakses free modul dari DQLab ‘Introduction to Data Science’ dengan python dan R serta nikmati ebook gratis yang bisa diakses kapanpun dan dimanapun. Selamat belajar!

Ceritanya tadi ada task untuk mengolah data sebuah file excel berisi 22970 baris data transfer-transferan. Inti tugasnya adalah menampilkan seluruh pengirim (

# read excel, save to a dataframe
df = pd.read_excel('mtpg_report.xlsx',header=0, index_col=0)

# check shape
df.shape

0), penerima (

# read excel, save to a dataframe
df = pd.read_excel('mtpg_report.xlsx',header=0, index_col=0)

# check shape
df.shape

1), dan transaksi (

# read excel, save to a dataframe
df = pd.read_excel('mtpg_report.xlsx',header=0, index_col=0)

# check shape
df.shape

2) yang duplicated.

Sebenarnya gampang, ya, tinggal klik-klik langsung filter di excel. Tapi data sebanyak 22970 ini cukup untuk membuat komputer gw ngos-ngosan.

Terus kepikiran pengen buat macro di VBA Excel, nanti diquery pakai sql buat filternya. Tapi udah lupa cara bikin macro. Udah aja 1 jam berkutat belajar lagi macro terus menyerah pas gagal bikin koneksinya pakai ADODB (gak bisa create object kalau pakai macOS, ceunah).

Tiba-tiba. Oh, kenapa gak pakai Python aja kalau mau diperlakukan sebagai sql? Kayaknya lebih cepat. Tapi sebenarnya gak cepat juga, karena gw pengennya data itu jadi tabel dalam sebuah db yang nantinya akan gw query dengan sql (entah mengapa ngotot banget pengen pake sql query). Sementara gw belum tau caranya convert excel ke db. Jadilah lama lagi nyari-nyari caranya. Tambah lagi udah buang waktu buat mencoba bikin macro. Kayaknya lebih cepat kalau bikin filter langsung di excel dari tadi, wkwkwk.

Data

File excel-nya bisa didownload di sini. Catatan, data sudah dimodifikasi, baik isi maupun jumlah field, untuk kepentingan pembelajaran semata.

Dataset terdiri dari 22970 baris dan 5 kolom (

# read excel, save to a dataframe
df = pd.read_excel('mtpg_report.xlsx',header=0, index_col=0)

# check shape
df.shape

2,

# read excel, save to a dataframe
df = pd.read_excel('mtpg_report.xlsx',header=0, index_col=0)

# check shape
df.shape

4,

# read excel, save to a dataframe
df = pd.read_excel('mtpg_report.xlsx',header=0, index_col=0)

# check shape
df.shape

5,

# read excel, save to a dataframe
df = pd.read_excel('mtpg_report.xlsx',header=0, index_col=0)

# check shape
df.shape

0, dan

# read excel, save to a dataframe
df = pd.read_excel('mtpg_report.xlsx',header=0, index_col=0)

# check shape
df.shape

1).

Pertama, seperti biasa import dulu library yang akan digunakan.

# read excel, save to a dataframe
df = pd.read_excel('mtpg_report.xlsx',header=0, index_col=0)

# check shape
df.shape

8 untuk membuat DataFrame,

# read excel, save to a dataframe
df = pd.read_excel('mtpg_report.xlsx',header=0, index_col=0)

# check shape
df.shape

9 untuk query sql, dan

# preparing connection to db
engine = create_engine('sqlite:///mtpg_report.db', echo=True)
sqlite_connection = engine.connect()

0 untuk mengexport data dari excel ke db.

import pandas as pd
import sqlite3 as sq

# untuk export excel ke db
from sqlalchemy import create_engine

Kemudian, baca excel dan simpan di DataFame

# preparing connection to db
engine = create_engine('sqlite:///mtpg_report.db', echo=True)
sqlite_connection = engine.connect()

1. Terus check shape hasilnya

# preparing connection to db
engine = create_engine('sqlite:///mtpg_report.db', echo=True)
sqlite_connection = engine.connect()

2.

# read excel, save to a dataframe
df = pd.read_excel('mtpg_report.xlsx',header=0, index_col=0)

# check shape
df.shape

Export dataframe ke DB

Pertama kita siapkan koneksi dulu. Database yang akan dibuat bernama

# preparing connection to db
engine = create_engine('sqlite:///mtpg_report.db', echo=True)
sqlite_connection = engine.connect()

3.

# preparing connection to db
engine = create_engine('sqlite:///mtpg_report.db', echo=True)
sqlite_connection = engine.connect()

Setelah itu export Dataframe ke tabel bernama

# preparing connection to db
engine = create_engine('sqlite:///mtpg_report.db', echo=True)
sqlite_connection = engine.connect()

4. Di sini kita juga melakukan pengecekan, jika tabelnya sudah ada maka direplace. Bisa juga diganti dengan

# preparing connection to db
engine = create_engine('sqlite:///mtpg_report.db', echo=True)
sqlite_connection = engine.connect()

5 kalau tidak mau

# preparing connection to db
engine = create_engine('sqlite:///mtpg_report.db', echo=True)
sqlite_connection = engine.connect()

6 dengan db yang baru. Cek dokumentasinya di sini.

# export df to sqlite db, if exists then replace
sqlite_table = "mtpg"
df.to_sql(sqlite_table, sqlite_connection, if_exists='replace')

Proses di atas mengexport seluruh data dalam dataframe ke sebuah tabel. Jadi tidak perlu lagi mengcopy datanya satu-persatu. Setelah export selesai, tutup koneksinya.

sqlite_connection.close()

Query menggunakan SQL

Kita coba query sederhana dulu dengan memilih 5 record pertama. Inget, koneksinya ke db tapi pas select tetap ke tabel.

# check creted db by selecting 5 records
conn = sq.connect("mtpg_report.db")
df_mtpg = pd.read_sql_query ("select * from mtpg limit 5;", conn)
df_mtpg

Jika tidak ada masalah maka outputnya adalah 5 baris pertama dari data. Kalau sudah OK, kita bisa mulai query yang sesungguhnya.

Yang pertama adalah me-list semua

# read excel, save to a dataframe
df = pd.read_excel('mtpg_report.xlsx',header=0, index_col=0)

# check shape
df.shape

0 yang duplicated. Misal kita ingin mencari

# read excel, save to a dataframe
df = pd.read_excel('mtpg_report.xlsx',header=0, index_col=0)

# check shape
df.shape

0 yang duplicated dari

# preparing connection to db
engine = create_engine('sqlite:///mtpg_report.db', echo=True)
sqlite_connection = engine.connect()

9 dan

# export df to sqlite db, if exists then replace
sqlite_table = "mtpg"
df.to_sql(sqlite_table, sqlite_connection, if_exists='replace')

0. Untuk menampilkan semua

# read excel, save to a dataframe
df = pd.read_excel('mtpg_report.xlsx',header=0, index_col=0)

# check shape
df.shape

0 yang duplicated, bisa dilakukan dengan

# export df to sqlite db, if exists then replace
sqlite_table = "mtpg"
df.to_sql(sqlite_table, sqlite_connection, if_exists='replace')

2 diikuti dengan select

# export df to sqlite db, if exists then replace
sqlite_table = "mtpg"
df.to_sql(sqlite_table, sqlite_connection, if_exists='replace')

3 yang lebih dari 1.

#  sender id duplicated
str_query = "SELECT * \
            FROM mtpg \
                WHERE (\"Sender ID\") IN \
                (SELECT \"Sender ID\" \
                    FROM mtpg WHERE \"Merchant Name\" = \"WAL\" \
                    AND \"Transaction Type\" = \"Transfer OUT\" \
                    GROUP BY \"Sender ID\" \
                    HAVING COUNT (*)>1 \
                    )\
            ORDER BY \"Sender ID\" DESC"
df_mtpg = pd.read_sql_query (str_query, conn)
df_mtpg

Contoh ke dua masih seperti yang pertama, tapi kali ini kita tambahkan kriterianya. Selain

# read excel, save to a dataframe
df = pd.read_excel('mtpg_report.xlsx',header=0, index_col=0)

# check shape
df.shape

0 yang duplicated, kita juga ingin melist

# read excel, save to a dataframe
df = pd.read_excel('mtpg_report.xlsx',header=0, index_col=0)

# check shape
df.shape

1 yang duplicated.

# beneficiary id, dan sender id duplicated
str_query = "SELECT * \
            FROM mtpg \
                WHERE (\"Beneficiary ID\") IN \
                (SELECT \"Beneficiary ID\" \
                 FROM mtpg WHERE \"Sender ID\" IN  \
                    (SELECT \"Sender ID\" \
                        FROM mtpg WHERE \"Merchant Name\" = \"WAL\" \
                        AND \"Transaction Type\" = \"Transfer OUT\" \
                        GROUP BY \"Sender ID\" \
                        HAVING COUNT (*)>1 \
                    )\
                GROUP BY \"Beneficiary ID\" \
                HAVING COUNT (*)>1 \
                )\
            ORDER BY \"Sender ID\" DESC"
df_mtpg = pd.read_sql_query (str_query, conn)
df_mtpg

Contoh ke-tiga, tambah lagi dengan kriteria

# read excel, save to a dataframe
df = pd.read_excel('mtpg_report.xlsx',header=0, index_col=0)

# check shape
df.shape

2 yang duplicated.

# invoice no, beneficiary id, dan sender id duplicated

str_query = "SELECT * \
            FROM mtpg \
                WHERE (\"Invoice Number\") IN \
                (SELECT \"Invoice Number\" \
                    FROM mtpg WHERE \"Beneficiary ID\" IN \
                    (SELECT \"Beneficiary ID\" \
                     FROM mtpg WHERE \"Sender ID\" IN  \
                        (SELECT \"Sender ID\" \
                            FROM mtpg WHERE \"Merchant Name\" = \"WAL\" \
                            AND \"Transaction Type\" = \"Transfer OUT\" \
                            GROUP BY \"Sender ID\" \
                            HAVING COUNT (*)>1 \
                        )\
                    GROUP BY \"Beneficiary ID\" \
                    HAVING COUNT (*)>1 \
                    )\
                GROUP BY \"Invoice Number\" \
                HAVING COUNT (*)>1) \
                ORDER BY \"Sender ID\" DESC"
df_mtpg = pd.read_sql_query (str_query, conn)
df_mtpg

Terakhir, simpan hasilnya di file excel.

# save result to an excel file
df_mtpg.to_excel("mtpg_filter_duplicated.xlsx")

That’s all. Kalau ada saran untuk meringkas query-nya, boleh di komen, hehe. Jupyter notebook untuk latihan ini bisa didownload di sini. Thank you.

Apa itu SQL python?

Apa itu SQL Programming?

SQL dibaca apa?