지출-액수.csv PIE Graph with exclusions of items in R

# Install and load the necessary packages. Below three lines can be dropped if installed already.
install.packages("RSQLite")
install.packages("DBI")
install.packages("ggplot2")  # This package is used for plotting
install.packages("ggrepel")  # This package is used to avoid label clustering
library(RSQLite)
library(DBI)
library(ggplot2)
library(ggrepel)

# Define the path to your SQLite database
db_path <- "/home/jbyungrokim/CSV/CSV.db"

# Connect to the SQLite database
conn <- dbConnect(RSQLite::SQLite(), dbname = db_path)

# Corrected query to get the total spending by each item for July 2024, excluding 'Rent - Monthly' and 'Grocery'
query <- "
SELECT 
    Item, 
    SUM(Pay_Amount) AS Total_Spent
FROM 
    '지출-액수_2024_08_17'
WHERE 
    substr(Date_Transaction, 1, 2) = '07' 
    AND substr(Date_Transaction, 7, 2) = '24'
    AND Item NOT IN ('Rent - Monthly', 'Grocery')
GROUP BY 
    Item
ORDER BY 
    Total_Spent DESC;
"

# Execute the query and store the result in a data frame
july_spending <- dbGetQuery(conn, query)

# Close the connection to the SQLite database
dbDisconnect(conn)

# Create a pie chart using ggplot2 and ggrepel
ggplot(july_spending, aes(x = "", y = Total_Spent, fill = Item)) +
  geom_bar(width = 1, stat = "identity") +
  coord_polar("y") +
  labs(title = "Spending Breakdown by Item - July 2024 (Excluding Rent and Grocery)") +
  theme_minimal() +
  theme(axis.title.x = element_blank(),
        axis.title.y = element_blank(),
        panel.grid = element_blank(),
        axis.text.x = element_blank(),
        axis.ticks = element_blank()) +
  geom_text_repel(aes(label = paste0(round(Total_Spent / sum(Total_Spent) * 100, 1), "%")),
                  position = position_stack(vjust = 0.5),
                  box.padding = 0.5,
                  direction = "y",
                  segment.color = "grey50")