readxl และ XLConnect: วิธีใช้ 2 packages สำหรับทำงานกับ Excel ในภาษา R — ตัวอย่างการทำงานกับ Daily Household Transactions

# Install readxl
install.packages("readxl")

# Load readxl
library(readxl)

# 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     

# Install
install.packages("XLConnect")

# Load
library(XLConnect)

# Load the workbook
workbook <- loadWorkbook("Daily Household Transactions.xlsx")

# List sheets
getSheets(workbook)

[1] "All Transactions"

# 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

# Create new sheets
createSheet(workbook,
            name = "New")

# List sheets
getSheets(workbook)

[1] "All Transactions" "New" 

# Rename the new sheet
renameSheet(workbook,
            sheet = "New",
            newName = "Some Transactions")

# List sheets
getSheets(workbook)

[1] "All Transactions"  "Some Transactions"

# Delete the new sheet
removeSheet(workbook,
            sheet = "Some Transactions")

# List sheets
getSheets(workbook)

[1] "All Transactions" 

# 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

# 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

# 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")

# 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

# Save the file
saveWorkbook(workbook,
             file = "Daily Household Transactions (Updated).xlsx")