สร้างรายงานจาก Excel ด้วย pandas

จัดการข้อมูล คำนวนผ่านคำสั่ง ให้งานน่าเบื่อกลายเป็นระบบที่ทำงานแทน เรียนกับ Ultimate Python

เอกสารประกอบ

การทำงานกับข้อมูลตารางกับ Python

ในการทำงานกับข้อมูลอย่าง Excel, csv เราจะใช้ library ที่ชื่อว่า pandas ซึ่งเป็นเครื่องมือที่นิยมใช้ในการวิเคราะห์ จัดการข้อมูลแบบตาราง ที่มาพร้อมกับเครื่องมือในการคำนวน การจัดการข้อมูลที่ใช้งานได้หลากหลายมากที่สุดอันหนึ่ง

ติดตั้ง pandas

จริงๆ แล้ว pandas เป็น library หรือชุดคำสั่งที่ต้องดาวน์โหลด และติดตั้งก่อนใช้งาน แต่ในบางกรณีเช่น การติดตั้งโปรแกรมอย่าง Anaconda จะมีการติดตั้ง pandas มาให้ด้วยแล้ว

สำหรับการติดตั้ง pandas เราจะใช้ pip ซึ่งเป็นโปรแกรมที่จัดการ package หรือชุดคำสั่งให้กับเรา ตามด้วยคำสั่ง install ตามด้วยชื่อ library นั่นคือ pandas นั่นเอง ซึ่งการติดตั้งเครื่องมือบนโปรแกรมบนเครื่องคอมพิวเตอร์ของเราต้องทำเพียงครั้งเดียวเท่านั้น แต่สำหรับการติดตั้งโปรแกรมออนไลน์จะต้องติดตั้งใหม่ทุกครั้งที่ใช้งาน

In [1]:
!pip install pandas

Requirement already satisfied: pandas in c:\users\acer\anaconda3\lib\site-packages (1.0.5) Requirement already satisfied: numpy>=1.13.3 in c:\users\acer\anaconda3\lib\site-packages (from pandas) (1.17.0) Requirement already satisfied: python-dateutil>=2.6.1 in c:\users\acer\anaconda3\lib\site-packages (from pandas) (2.8.1) Requirement already satisfied: pytz>=2017.2 in c:\users\acer\anaconda3\lib\site-packages (from pandas) (2020.1) Requirement already satisfied: six>=1.5 in c:\users\acer\anaconda3\lib\site-packages (from python-dateutil>=2.6.1->pandas) (1.15.0) 

เรียนรู้เกี่ยวกับ pandas บน official site

ติดตั้ง openpyxl

openpyxl เป็น library ที่ใช้เพื่อการทำความเข้าใจข้อมูลใน Excel และ csv ซึ่งในกรณีที่ใช้โปรแกรม Anaconda และ Jupyter Notebook ที่ติดตั้งอยู่บนคอมพิวเตอร์ จะไม่จำเป็นต้องใช้ openpyxl ในการใช้ร่วมกับ pandas ในการเปิดไฟล์ เนื่องจากในโปรแกรมดังกล่าวมีการติดตั้งชุดคำสั่งที่ทำหน้าที่เปิดไฟล์ Excel มาให้แล้วนั่นเอง

แต่สำหรับการเปิดไฟล์ Excel ด้วย library pandas บนโปรแกรมออนไลน์ จะต้องติดตั้ง openpyxl เพื่อใช้ในการเปิดไฟล์ Excel ซึ่งการติดตั้งเครื่องมือบนโปรแกรมบนเครื่องคอมพิวเตอร์ของเราต้องทำเพียงครั้งเดียวเท่านั้น แต่สำหรับการติดตั้งโปรแกรมออนไลน์จะต้องติดตั้งใหม่ทุกครั้งที่ใช้งาน

In [2]:
!pip install openpyxl

Requirement already satisfied: openpyxl in c:\users\acer\anaconda3\lib\site-packages (3.0.4) Requirement already satisfied: et-xmlfile in c:\users\acer\anaconda3\lib\site-packages (from openpyxl) (1.0.1) Requirement already satisfied: jdcal in c:\users\acer\anaconda3\lib\site-packages (from openpyxl) (1.4.1) 

เรียนรู้เกี่ยวกับ Openpyxl บน Real Python

นำ pandas มาใช้

เมื่อทำการติดตั้งเรียบร้อยจะต้องนำชุดคำสั่งมาใช้งานบนไฟล์ Notebook ปัจจุบันที่เรากำลังเปิดใช้งานเพื่อเขียนโค้ดอยู่ ผ่านคำสั่ง import ซึ่งจะต้องทำทุกครั้งในการเปิดใช้งานไฟล์ Notebook

ซึ่งการใช้งาน library จะต้องเรียกใช้ตัว library ซ้ำๆ ดังนั้นเราสามารถตั้งชื่อเล่นให้กับ library ด้วยคำสั่ง as เพื่อตั้งชื่อเล่นให้สามารถเรียกใช้งานได้ง่ายขึ้น

In [3]:
import pandas as pd

การระบุไฟล์ในโปรแกรมออนไลน์

แนะนำให้ใช้การระบุชื่อไฟล์ เนื่องจากในโปรแกรมออนไลน์มีการใช้การระบุตำแหน่งข้อมูลเป็นของตัวเอง แยกกับไฟล์ที่อยู่บนคอมพิวเตอร์ดังนั้นจะมีขั้นตอนที่ใช้งานต่างกัน

ดังนั้นการทำงานกับไฟล์ต่างๆ บนโปรแกรมออนไลน์จะต้องทำการอัพโหลดไฟล์ที่ต้องการไปยังโฟลเดอร์ชั่วคราวที่ถูกสร้างขึ้นตอนที่เข้าใช้งานตัวโปรแกรมออนไลน์

• ระบุชื่อไฟล์

In [7]:
name = "North.xlsx"
name

Out[7]:
'North.xlsx'

การเพิ่มไฟล์ไปยังโฟลเดอร์ปัจจุบัน

เพื่อเตรียมไฟล์สำหรับการใช้งานด้วยการเรียกชื่อไฟล์ สามารถทำได้ทั้งโปรแกรมที่ติดตั้งบนคอมพิวเตอร์ และโปรแกรมออนไลน์

• ไปยังโฟลเดอร์ปัจจุบันของ Notebook

เข้าถึงโฟลเดอร์ปัจจุบันของไฟล์ Notebook ได้ที่ File >> Open...

• นำไฟล์มายัง Folder ปัจจุบัน

ผ่านการ "Upload" ที่จะคัดลอกไฟล์ที่เลือกมาใส่ในโฟลเดอร์ปัจจุบัน

• เลือกไฟล์ที่ดาวน์โหลดมา และ "Upload"

เลือกไฟล์ Credentials และ "Upload" ซึ่งจริงๆ เป็นการคัดลอกข้อมูลมายังโฟลเดอร์ปัจจุบันนั่นเอง

• ไฟล์ดังกล่าวจะถูกสร้างขึ้นในโฟลเดอร์ปัจจุบัน

ดูที่อยู่โฟลเดอร์ปัจจุบันได้ที่ตำแหน่งของเส้นสีแดงได้เลย

การนำข้อมูล Excel มาทำงานด้วย pandas

จะนำข้อมูล Excel เข้ามาเป็นข้อมูลประเภท DataFrame ที่เป็นประเภท Object ที่ถูกสร้างขึ้นด้วย pandas สำหรับการจัดการข้อมูลแบบตาราง

ทำความเข้าใจข้อจำกัดของการเลือกใช้การระบุตำแหน่งไฟล์ และการเรียกชื่อไฟล์ และเลือกใช้อันใดอันหนึ่งได้เลย

• การเปิดไฟล์ ด้วยโปรแกรมออนไลน์

การใช้โปรแกรมออนไลน์หมายถึงในบางครั้งเราจะมีเครื่องมือที่ต้องการไม่ครบ ซึ่งหนึ่งในเครื่องมือที่อาจขาดหายไปได้ นั่นคือเครื่องมือที่ใช้เปิดไฟล์ Excel ซึ่งสำหรับตัวออนไลน์เราจะใช้เครื่องมือที่ชื่อว่า openpyxl ที่ทำการติดตั้งเมื่อตอนต้นบทเรียนมาใช้เปิดไฟล์ ซึ่งต่างจากการเปิดไฟล์ด้วยโปรแกรมที่ติดตั้งบนเครื่องที่ Anaconda จะทำการติดตั้งเครื่องมือการเปิดให้เราแล้ว

การใช้งาน openpyxl ที่ติดตั้งแล้วข้างบน เพื่อเปิดไฟล์ Excel ใช้ผ่านการระบุผ่าน parameter ที่ชื่อว่า engine= โดยระบุ 'openpyxl' เป็น string ตามตัวอย่างด้านล่าง

In [9]:
name = 'North.xlsx'
table = pd.read_excel(name,engine='openpyxl')
table

Out[9]:
ProductQtr1Qtr2Qtr3Qtr40Basil Leaf (Whole)67786760456878341Celery Seed (Whole)36346344642325462North Special875145225424513Catnip Leaf8543644747434Asafoetida Powder6546343267545Anise Seeds5344235216256Cardamom Seed (Ground)4226426426247Chamomile Flowers3566346327438Caraway Seed (Whole)3546334222559Cardamom Seed (Whole)25552525262410Bay Leaf (Whole)233532525652

ข้อมูล Excel ที่เปิดด้วย pandas

ข้อมูลตารางที่เปิดด้วย library pandas เป็นข้อมูลที่มีชื่อเรียกว่า DataFrame ซึ่งเป็นการจัดการข้อมูลที่มีโครงสร้างที่เก็บข้อมูลโดยการอ้างอิงจากชื่อ Row และ Column ของตารางข้อมูลนั้น ดังนั้นการใช้งานข้อมูลประเภทนี้ให้มีประสิทธิภาพ เราจะต้องมีการตั้งชื่อ Row, Column ที่เหมาะสม และเรียนรู้วิธีการทำงานกับมัน

การตั้ง Column, Row

การอ้างอิงถึงข้อมูลในตาราง DataFrame ใช้การกำหนดข้อมูลในตารางเป็น Column, Row เพื่อใช้อ้างอิงถึงข้อมูลในตาราง ก่อนที่จะทำงานกับข้อมูลนี้ต่อจะต้องตั้ง Column, Row ที่เหมาะสมเสียก่อน

การตั้ง Column

• การเลือกแถวมาตั้งเป็น Column

เราสามารถเลือกตำแหน่งของข้อมูลที่จะนำมาใช้เป็น Column เพื่อเรียกข้อมูลแต่และ Column ได้ โดยการระบุลงไปใน parameter header= ใน คำสั่ง .read_excel() โดยหากต้องการเลือกข้อมูลแถวแรกตั้งเป็น Column จะใช้การระบุ header=0 เนื่องจากตำแหน่งแรกเริ่มต้นที่ 0

In [4]:
table = pd.read_excel('North.xlsx',engine='openpyxl',header=0)
table

Out[4]:
ProductQtr1Qtr2Qtr3Qtr40Basil Leaf (Whole)67786760456878341Celery Seed (Whole)36346344642325462North Special875145225424513Catnip Leaf8543644747434Asafoetida Powder6546343267545Anise Seeds5344235216256Cardamom Seed (Ground)4226426426247Chamomile Flowers3566346327438Caraway Seed (Whole)3546334222559Cardamom Seed (Whole)25552525262410Bay Leaf (Whole)233532525652

การตั้ง Row

• การเลือก Column มาตั้งเป็น Row

เราจะใช้คำสั่ง .set_index() กับข้อมูล DataFrame และรับค่า ชื่อ Column ที่เราต้องการนำข้อมูลมาตั้งเป็นชื่อ Row ซึ่งชื่อ Row ที่เหมาะสมมีหลักการเลือกง่ายๆ คือ ให้เลือกข้อมูลที่เป็นตัวแทนของข้อมูลในแถวนั้นๆ เช่น ถ้าข้อมูลเป็นข้อมูลยอดขายของสินค้ารายไตรมาส ก็ให้เลือกชื่อสินค้า หรือรหัสสินค้าที่เป็นตัวแทนของแต่ละสินค้ามาเป็นชื่อ row

In [8]:
table = table.set_index('Product')
table

Out[8]:
Qtr1Qtr2Qtr3Qtr4ProductBasil Leaf (Whole)6778676045687834Celery Seed (Whole)3634634464232546North Special87514522542451Catnip Leaf854364474743Asafoetida Powder654634326754Anise Seeds534423521625Cardamom Seed (Ground)422642642624Chamomile Flowers356634632743Caraway Seed (Whole)354633422255Cardamom Seed (Whole)255525252624Bay Leaf (Whole)233532525652

การใช้ Column, Row ดึงข้อมูล

การทำงานของ pandas DataFrame จะใช้ Column, Row เป็นหลักในการดึงข้อมูลจาก DataFrame เมื่อตั้งค่าที่เหมาะสมแล้ว เราจะมาใช้ Column, Row เพื่อดึงข้อมูลจาก DataFrame กัน

การดึงข้อมูลจะใช้สัญลักษณ์ [ ] เป็นหลัก ซึ่งจะเห็นได้ในหลาย Object ที่ใช้สัญลักษณ์เดียวกันนี้ และรับค่าที่ใช้ดึงใส่ระหว่าง [ ] ซึ่งจะมีค่าต่างกันไปตามแต่ละวิธีที่จะได้เห็นต่อไปนี้

การดึงข้อมูล Column

• การดึงข้อมูล Column ด้วยชื่อ

การดึงข้อมูลด้วยชื่อ Column ที่จะระบุเป็น string ไว้ระหว่าง [ ] จะได้ผลลัพธ์เป็นข้อมูลประเภท Series เช่นการดึงข้อมูลจาก Column Qtr1 เขียนได้ในลักษณะดังนี้

In [9]:
table['Qtr1']

Out[9]:
Product Basil Leaf (Whole)        6778 Celery Seed (Whole)       3634 North Special              875 Catnip Leaf                854 Asafoetida Powder          654 Anise Seeds                534 Cardamom Seed (Ground)     422 Chamomile Flowers          356 Caraway Seed (Whole)       354 Cardamom Seed (Whole)      255 Bay Leaf (Whole)           233 Name: Qtr1, dtype: int64

การดึงข้อมูล Row

• การดึงข้อมูล row ด้วยชื่อ

การดึงข้อมูลด้วยชื่อ row จะใช้ .loc ประกอบกับ [ ] โดยใช้การระบุชื่อ row เป็น string ในลักษณะคล้ายกับการดึง Column โดยจะได้ข้อมูลมาเป็น Series เช่นการถึง row North Special เขียนได้ลักษณะดังต่อไปนี้

In [11]:
table.loc['North Special']

Out[11]:
Qtr1     875 Qtr2    1452 Qtr3     254 Qtr4    2451 Name: North Special, dtype: int64

การคำนวนข้อมูลระหว่าง Column, Row ใน DataFrame เดียวกัน

สำหรับการทำงานใน DataFrame สามารถนำข้อมูลในคนละ Column,Row มาคำนวนทางคณิตศาสตร์ด้วยกันได้เช่นกัน ซึ่งการทำงานของข้อมูล Column, Row จะมีการจับคู่ข้อมูลที่มี Column, Row เหมือนกัน มาคำนวนกันอัตโนมัติ ดังนั้น การตั้งชื่อ Column, Row ให้มีค่าตรงกันเพื่อระบุข้อมูลให้เหมาะสมจะทำให้การทำงานบนข้อมูล DataFrame มีประสิทธิภาพยิ่งขึ้น

• ข้อมูลใน Column

ข้อมูลใน Column จากการดึงจาก DataFrame ใดๆ สามารถนำมาคำนวนกันผ่าน +, -, *, /, //, % ได้เลย ซึ่ง pandas จะจับคู่ข้อมูลที่มีชื่อ Row ตรงกันแบบอัตโนมัติ (เพราะเมื่อดึงข้อมูล Column ออกมา จะได้ข้อมูลหลายๆ row มา)

In [8]:
q1 = table['Qtr1']
q2 = table['Qtr2']
first_half = q1 + q2
first_half

Out[8]:
Product Basil Leaf (Whole)        13538 Celery Seed (Whole)        9978 North Special              2327 Catnip Leaf                1218 Asafoetida Powder          1288 Anise Seeds                 957 Cardamom Seed (Ground)     1064 Chamomile Flowers           990 Caraway Seed (Whole)        987 Cardamom Seed (Whole)       780 Bay Leaf (Whole)            765 dtype: int64

• ข้อมูลใน Row

ข้อมูลใน row จากการดึงจาก DataFrame ใดๆ สามารถนำมาคำนวนกันผ่าน +, -, *, /, //, % ได้เลย ซึ่ง pandas จะจับคู่ข้อมูลที่มีชื่อ Column ตรงกันแบบอัตโนมัติ (เพราะเมื่อดึงข้อมูล Row ออกมา จะได้ข้อมูลหลายๆ Column มา)

In [9]:
whole = table.loc["Cardamom Seed (Whole)"]
ground = table.loc["Cardamom Seed (Ground)"]
cardamom = whole + ground
cardamom

Out[9]:
Qtr1     677 Qtr2    1167 Qtr3     894 Qtr4    1248 dtype: int64

• ข้อมูลในตาราง

เช่นเดียวกัน ข้อมูลที่อยู่ในตารางหรือข้อมูลประเภท DataFrame สามารถนำมาคำนวนกันผ่าน +, -, *, /, //, % ได้เลย ซึ่ง pandas จะจับคู่ข้อมูลที่มีชื่อ Column, Row ตรงกันแบบอัตโนมัติ

In [10]:
table + table

Out[10]:
Qtr1Qtr2Qtr3Qtr4ProductBasil Leaf (Whole)1355613520913615668Celery Seed (Whole)726812688128465092North Special175029045084902Catnip Leaf17087289481486Asafoetida Powder130812686521508Anise Seeds106884610421250Cardamom Seed (Ground)844128412841248Chamomile Flowers712126812641486Caraway Seed (Whole)7081266844510Cardamom Seed (Whole)51010505041248Bay Leaf (Whole)466106410501304

การแก้ไข / เพิ่มข้อมูลใน DataFrame

การแก้ไขข้อมูล หรือการเพิ่มข้อมูลใช้หลักการเดียวกันในการกำหนดตำแหน่งข้อมูลที่ต้องการและกำหนดข้อมูลที่ต้องการใส่เข้าไป เช่นในกรณีการระบุตำแหน่ง cell, การระบุ column, การระบุ row และใช้การกำหนดค่าด้วย =

ซึ่งหากในตำแหน่งดังกล่าวมีข้อมูลอยู่แล้วจะเกิดการบันทึกข้อมูลใหม่ทับเข้าไป แต่หากยังไม่มีข้อมูลอยู่ ข้อมูลจะถูกสร้างขึ้น

• แก้ไข / สร้างคอลัมภ์ใหม่

ใช้การระบุข้อมูลที่ต้องการใส่เข้าไปใน Column ที่ต้องการได้ทันที สำหรับข้อมูลที่ต้องการใส่เข้าไปใน column สามารถใช้ประเภทข้อมูลได้หลากหลายทั้ง DataFrame, Series ซึ่งข้อมูล 2 ประเภทนี้จะทำการจับคู่ Column, Row ที่ตรงกันให้ทันที หรือ ใช้ List ได้เช่นเดียวกัน แต่ List จะทำการใส่ข้อมูลตาม ลำดับ ใน List ดังนั้นจะต้องมีการตรวจสอบก่อนใช้งาน

In [12]:
table["first half"] = first_half
table

Out[12]:
Qtr1Qtr2Qtr3Qtr4first halfProductBasil Leaf (Whole)700067604568783413538Celery Seed (Whole)36346344642325469978North Special875145225424512327Catnip Leaf8543644747431218Asafoetida Powder6546343267541288Anise Seeds534423521625957Cardamom Seed (Ground)4226426426241064Chamomile Flowers356634632743990Caraway Seed (Whole)354633422255987Cardamom Seed (Whole)255525252624780Bay Leaf (Whole)233532525652765

• แก้ไข / สร้าง row ใหม่

เช่นเดียวกับการทำงานกับ column แต่มีข้อแตกต่างที่การทำงานกับ row จะใช้คำสั่ง .loc เพิ่มขึ้นมาสำหรับการระบุตำแหน่งของ row ที่ต้องการ

ข้อมูลใน Row

ข้อมูลใน row จากการดึงจาก DataFrame ใดๆ สามารถนำมาคำนวนกันผ่าน +, -, *, /, //, % ได้เลย ซึ่ง pandas จะจับคู่ข้อมูลที่มีชื่อ Column ตรงกันแบบอัตโนมัติ (เพราะเมื่อดึงข้อมูล Row ออกมา จะได้ข้อมูลหลายๆ Column มา)

In [9]:
whole = table.loc["Cardamom Seed (Whole)"]
ground = table.loc["Cardamom Seed (Ground)"]
cardamom = whole + ground
cardamom

Out[9]:
Qtr1     677 Qtr2    1167 Qtr3     894 Qtr4    1248 dtype: int64

สร้าง row ใหม่

In [13]:
table.loc["Cardamom Total"] = cardamom
table

Out[13]:
Qtr1Qtr2Qtr3Qtr4first halfProductBasil Leaf (Whole)7000.06760.04568.07834.013538.0Celery Seed (Whole)3634.06344.06423.02546.09978.0North Special875.01452.0254.02451.02327.0Catnip Leaf854.0364.0474.0743.01218.0Asafoetida Powder654.0634.0326.0754.01288.0Anise Seeds534.0423.0521.0625.0957.0Cardamom Seed (Ground)422.0642.0642.0624.01064.0Chamomile Flowers356.0634.0632.0743.0990.0Caraway Seed (Whole)354.0633.0422.0255.0987.0Cardamom Seed (Whole)255.0525.0252.0624.0780.0Bay Leaf (Whole)233.0532.0525.0652.0765.0Cardamom Total677.01167.0894.01248.0NaN

Save DataFrame เป็นไฟล์ Excel

ในการบันทึกข้อมูลออกเป็นไฟล์ Excel โดยที่ไม่มีการกำหนดค่า Format จะทำการบันทึกข้อมูลโดยใช้ Format default ของทาง pandas

• การบันทึกไฟล์ลงโฟลเดอร์ปัจจุบัน

ใช้คำสั่ง .to_excel() กับ DataFrame สำหรับการสร้างไฟล์ที่โฟลเดอร์ปัจจุบันที่เป็นที่อยู่ของ Notebook กำลังใช้งานอยู่ สามารถทำได้โดยการกำหนดชื่อของไฟล์ที่ต้องการได้เลย โดยจะต้องใส่ parameter เป็นชื่อไฟล์ที่ต้องการเป็น string พร้อมนามสกุลไฟล์ และอาจใช้ parameter sheet_name= เพื่อกำหนดชื่อชีทที่จะสร้างในไฟล์ Excel

In [5]:
north_south.to_excel("Total Sales.xlsx",sheet_name="Total Sales")

เสร็จสิ้นการเปิดไฟล์ Excel

เข้ากลุ่มแลกเปลียนความรู้

เรียนเรียน Python ใช้ทำงานจัดการ Excel อัตโนมัติ ใช้ทำ Data Analysis

เริ่มไว ใช้ได้ทันที พร้อมการดูแลจากผู้สอนโดยตรง และกลุ่มแลกเปลี่ยนความรู้

เรียนรู้เกี่ยวกับคอร์สเรียนเพิ่มเติม https://ultimatepython.teachable.com/p/python-excel-automation