Excel เป็นเครื่องมือทำงานยอดนิยมในการทำงาน ซึ่งทำให้ในหลาย ๆ ครั้ง ข้อมูลที่เราต้องการถูกเก็บอยู่ในไฟล์ Excel (เช่น .xls และ .xlsx)
ในบทความนี้ เราจะมาทำความรู้จักกับ readxl และ XLConnect ซึ่งเป็น packages สำหรับทำงานกับ Excel ในภาษา R กัน
เราจะดูการใช้งานผ่านตัวอย่างการทำงานกับ Daily Transactions Dataset จาก Kaggle ที่ถูกเก็บในไฟล์ XLSX (”Daily Household Transactions.xlsx”):
ถ้าพร้อมแล้ว ไปเริ่มกันเลย
- 1️⃣ Package 1. readxl
- 2️⃣ Package 2. XLConnect
- 💪 Summary
- 😺 GitHub
- 📃 References
- ✅ R Book for Psychologists: หนังสือภาษา R สำหรับนักจิตวิทยา
1️⃣ Package 1. readxl
readxl เป็น package สำหรับโหลดข้อมูลจาก Excel และมี function ที่เราจะเรียกใช้งานได้ คือ read_excel() ซึ่งต้องการ 2 arguments:
path: ชื่อไฟล์ หรือ file pathsheet: ชื่อหรือลำดับของ sheet ที่เก็บข้อมูลที่เราต้องการ
ในการเริ่มต้นใช้งาน readxl เราจะติดตั้งและโหลด package:
# Install readxl
install.packages("readxl")
# Load readxl
library(readxl)
จากนั้น เรียกใช้ read_excel() เพื่อโหลดข้อมูล:
# Import Excel data with read_excel()
all_transactions <- read_excel("Daily Household Transactions.xlsx",
sheet = 1)
# View the first few rows
head(all_transactions)
ผลลัพธ์:
# A tibble: 6 × 8
Date Mode Category Subcategory Note Amount `Income/Expense` Currency
<dttm> <chr> <chr> <chr> <chr> <dbl> <chr> <chr>
1 2018-09-20 12:04:08 Cash Transpo… Train 2 Pl… 30 Expense INR
2 2018-09-20 12:03:15 Cash Food snacks Idli… 60 Expense INR
3 2018-09-19 00:00:00 Saving Bank acco… subscri… Netflix 1 mo… 199 Expense INR
4 2018-09-17 23:41:17 Saving Bank acco… subscri… Mobile Ser… Data… 19 Expense INR
5 2018-09-16 17:15:08 Cash Festiva… Ganesh Puj… Gane… 251 Expense INR
6 2018-09-15 06:34:17 Credit Card subscri… Tata Sky Perm… 200 Expense INR
Note: read_excel() มี parametres อื่น ๆ ที่เราสามารถตั้งค่าได้ เช่น:
range: ช่วงข้อมูลที่เราต้องการโหลดcol_names: ชื่อ columnscol_types: ประเภทข้อมูลในแต่ละ columnskip: จำนวน rows ที่เราจะข้ามในการโหลดข้อมูล เช่น เรากำหนดskip = 5,read_excel()จะโหลดข้อมูลตั้งแต่ row ที่ 6 เป็นต้นไป
ดูคู่มือการใช้งาน read_excel() ทั้งหมดได้ที่ read_excel: Read xls and xlsx files.
2️⃣ Package 2. XLConnect
XLConnect เป็น package ที่ช่วยให้เราทำงานกับไฟล์ Excel จาก R ได้โดยตรง
การใช้งาน XLConnect แบ่งได้เป็น 5 กรณี ดังนี้:
- โหลดและสำรวจ workbook
- โหลดข้อมูลจาก sheet
- จัดการ sheet
- เพิ่มข้อมูลใน sheet
- บันทึก workbook
เราไปดูการใช้งานในแต่ละกรณีกัน
.
📔 กรณีที่ 1. โหลดและสำรวจ Workbook
ในการเริ่มใช้งาน XLConnect เราจะต้องติดตั้งและโหลด package ก่อน:
# Install
install.packages("XLConnect")
# Load
library(XLConnect)
จากนั้น เราสามารถโหลด Excel เราเข้ามาใน R ได้ด้วย loadWorkbook():
# Load the workbook
workbook <- loadWorkbook("Daily Household Transactions.xlsx")
และดู sheet ทั้งหมดใน workbook ด้วย getSheets():
# List sheets
getSheets(workbook)
ผลลัพธ์:
[1] "All Transactions"
ในตัวอย่าง จะเห็นว่า Excel ของเรามี 1 sheet ได้แก่ “All Transactions”
.
⬇️ กรณีที่ 2. โหลดข้อมูลจาก sheet
เมื่อเห็นโครงสร้างของไฟล์ Excel แล้ว เราสามารถโหลดข้อมูลจาก sheet ที่ต้องการได้ด้วย readWorksheet():
# Get sheet data
sheet1_data <- readWorksheet(workbook,
sheet = "All Transactions")
# Print data
head(sheet1_data)
ผลลัพธ์:
Date Mode Category Subcategory Note Amount Income.Expense Currency
1 2018-09-20 12:04:08 Cash Transportation Train 2 Place 5 to Place 0 30 Expense INR
2 2018-09-20 12:03:15 Cash Food snacks Idli medu Vada mix 2 plates 60 Expense INR
3 2018-09-19 00:00:00 Saving Bank account 1 subscription Netflix 1 month subscription 199 Expense INR
4 2018-09-17 23:41:17 Saving Bank account 1 subscription Mobile Service Provider Data booster pack 19 Expense INR
5 2018-09-16 17:15:08 Cash Festivals Ganesh Pujan Ganesh idol 251 Expense INR
6 2018-09-15 06:34:17 Credit Card subscription Tata Sky Permanent Residence - Tata Play recharge 200 Expense INR
.
🖐️ กรณีที่ 3. จัดการ Sheet
ในการจัดการ sheet เราสามารถทำได้ 3 อย่าง:
- สร้าง sheet ใหม่:
createSheet() - เปลี่ยนชื่อ sheet:
renameSheet() - ลบ sheet:
removeSheet()
ยกตัวอย่างการสร้าง sheet ใหม่:
# Create new sheets
createSheet(workbook,
name = "New")
# List sheets
getSheets(workbook)
ผลลัพธ์:
[1] "All Transactions" "New"
เปลี่ยนชื่อ sheet:
# Rename the new sheet
renameSheet(workbook,
sheet = "New",
newName = "Some Transactions")
# List sheets
getSheets(workbook)
ผลลัพธ์:
[1] "All Transactions" "Some Transactions"
และลบ sheet ทิ้ง:
# Delete the new sheet
removeSheet(workbook,
sheet = "Some Transactions")
# List sheets
getSheets(workbook)
ผลลัพธ์:
[1] "All Transactions"
.
➕ กรณีที่ 4. เพิ่มข้อมูลใน Sheet
เราสามารถใส่ข้อมูลลงใน sheet ได้ด้วย writeWorksheet()
ยกตัวอย่างเช่น เราต้องการเพิ่มข้อมูลสรุปค่าใช้จ่ายตามประเภทการใช้จ่าย
เราจะเริ่มจากสรุปค่าใช้จ่ายตามประเภทโดยใช้ dplyr package:
# Load dplyr
library(dplyr)
# Calculate expense by category
expense_by_cat <- sheet1_data |>
# Filter for expense
filter(Income.Expense == "Expense") |>
# Group by category
group_by(Category) |>
# Calculate sum amount
summarise(Sum = sum(Amount)) |>
# Ungroup
ungroup() |>
# Sort by category
arrange(desc(Sum))
# View the results
expense_by_cat
(Note: อ่านเกี่ยวกับการใช้ dplyr ได้ที่นี่)
ผลลัพธ์:
# A tibble: 27 × 2
Category Sum
<chr> <dbl>
1 Money transfer 606529.
2 Investment 271858
3 Transportation 169054.
4 Household 161646.
5 subscription 114588.
6 Food 96403.
7 Public Provident Fund 90000
8 Other 87025.
9 Family 78582.
10 Health 66253.
# ℹ 17 more rows
# ℹ Use `print(n = ...)` to see more rows
จากนั้น สร้าง sheet ใหม่เพื่อเก็บข้อมูลที่เราได้มา:
# Create a new sheet
createSheet(workbook,
name = "Expense by Category")
# Add data to "Expense by Catogory" sheet
writeWorksheet(workbook,
data = expense_by_cat,
sheet = "Expense by Category")
เมื่อเราเรียกดูข้อมูลจาก “Expense by Catogory” เราจะเห็นข้อมูลแบบนี้:
# Read the sheet
readWorksheet(workbook,
sheet = "Expense by Category")
ผลลัพธ์:
Category Sum
1 Money transfer 606528.90
2 Investment 271858.00
3 Transportation 169053.78
4 Household 161645.58
5 subscription 114587.91
6 Food 96403.10
7 Public Provident Fund 90000.00
8 Other 87025.28
9 Family 78582.20
10 Health 66252.75
11 Tourism 63608.85
12 Gift 40168.00
13 Apparel 25373.82
14 Recurring Deposit 22000.00
15 maid 21839.00
16 Cook 12443.00
17 Rent 10709.00
18 Festivals 6911.00
19 Culture 4304.36
20 Beauty 4189.00
21 Self-development 2357.00
22 Education 537.00
23 Grooming 400.00
24 Social Life 298.00
25 water (jar /tanker) 148.00
26 Documents 100.00
27 garbage disposal 67.00
.
💾 กรณีที่ 5. บันทึก Workbook
จนถึงจุดนี้ สิ่งที่เราแก้ไขไปจะยังไม่ถูกบันทึกลงในไฟล์ Excel ของเรา
เราต้องใช้ saveWorkbook() เพื่อบันทึกการเปลี่ยนแปลงทั้งหมดได้
อย่างในตัวอย่าง เราจะบันทึกการเปลี่ยนแปลงทั้งหมดไปที่ workbook ใหม่ชื่อ “Daily Household Transactions (Updated).xlsx”:
# Save the file
saveWorkbook(workbook,
file = "Daily Household Transactions (Updated).xlsx")
เมื่อเราเปิด working directory ดู เราจะเห็น Excel 2 ไฟล์:
- ไฟล์ต้นฉบับ
- ไฟล์ที่เราสร้างใหม่
💪 Summary
ในบทความนี้ เราได้เรียนรู้วิธีการทำงานกับ Excel ในภาษา R ผ่าน 2 packages ได้แก่:
Package 1. readxl:
| Function | For |
|---|---|
read_excel() | โหลดข้อมูลจาก Excel |
Package 2. XLConnect:
| Function | For |
|---|---|
loadWorkbook() | โหลด workbook |
getSheets() | ดู sheets ใน workbook |
readWorksheet() | โหลดข้อมูลจาก sheet |
createSheet() | สร้าง sheet |
renameSheet() | เปลี่ยนชื่อ sheet |
removeSheet() | ลบ sheet |
writeWorksheet() | เพิ่มข้อมูลใน sheet |
saveWorkbook() | บันทึก workbook |
😺 GitHub
ดู code และ Excel ตัวอย่างได้ที่ GitHub
📃 References
✅ R Book for Psychologists: หนังสือภาษา R สำหรับนักจิตวิทยา
📕 ขอฝากหนังสือเล่มแรกในชีวิตด้วยนะครับ 😆
🙋 ใครที่กำลังเรียนจิตวิทยาหรือทำงานสายจิตวิทยา และเบื่อที่ต้องใช้ software ราคาแพงอย่าง SPSS และ Excel เพื่อทำข้อมูล
💪 ผมขอแนะนำ R Book for Psychologists หนังสือสอนใช้ภาษา R เพื่อการวิเคราะห์ข้อมูลทางจิตวิทยา ที่เขียนมาเพื่อนักจิตวิทยาที่ไม่เคยมีประสบการณ์เขียน code มาก่อน
ในหนังสือ เราจะปูพื้นฐานภาษา R และพาไปดูวิธีวิเคราะห์สถิติที่ใช้บ่อยกัน เช่น:
- Correlation
- t-tests
- ANOVA
- Reliability
- Factor analysis
🚀 เมื่ออ่านและทำตามตัวอย่างใน R Book for Psychologists ทุกคนจะไม่ต้องพึง SPSS และ Excel ในการทำงานอีกต่อไป และสามารถวิเคราะห์ข้อมูลด้วยตัวเองได้ด้วยความมั่นใจ
แล้วทุกคนจะแปลกใจว่า ทำไมภาษา R ง่ายขนาดนี้ 🙂↕️
👉 สนใจดูรายละเอียดหนังสือได้ที่ meb:
Shi no Shigoto 