R – monthly from Oct 2023 to Aug 2024

library(RSQLite)
library(DBI)
library(ggplot2)

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

# Define the range of months
months <- c('2023-10', '2023-11', '2023-12', '2024-01', '2024-02', '2024-03', '2024-04', '2024-05', '2024-06', '2024-07', '2024-08')

# Loop through each month and run the query
for (month in months) {
  query <- paste0("
  SELECT 
      Item, 
      SUM(Pay_Amount) AS Total_Spent
  FROM 
      '지출-액수_2024_09_18'
  WHERE 
      strftime('%Y-%m', '20' || substr(Date_Transaction, 7, 2) || '-' || substr(Date_Transaction, 1, 2) || '-' || substr(Date_Transaction, 4, 2)) = '", month, "'
  GROUP BY 
      Item
  ORDER BY 
      Total_Spent DESC;
  ")
  
  # Execute the query and store the result in a data frame
  monthly_sum <- dbGetQuery(conn, query)
  
  # Print the result
  print(paste("Sum of Pay_Amount by Item for", month, ":"))
  print(monthly_sum)
  
  # Save the result as a CSV file
  csv_file_path <- paste0("/home/jbyungrokim/Downloads/지출-", month, "_sum.csv")
  write.csv(monthly_sum, file = csv_file_path, row.names = FALSE)
  print(paste("Data has been written to", csv_file_path))
  
  # Create a bar graph using ggplot2
  p <- ggplot(monthly_sum, aes(x = reorder(Item, -Total_Spent), y = Total_Spent)) +
    geom_bar(stat = "identity", fill = "steelblue") +
    theme_minimal() +
    labs(title = paste("Total Spending by Item for", month),
         x = "Item",
         y = "Total Spent ($)") +
    theme(axis.text.x = element_text(angle = 45, hjust = 1))
  
  # Print the plot to ensure it displays
  print(p)
}

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