지출-액수.csv R Graph

# Install and load the necessary packages. After installing once, below 3 lines can be dropped.
install.packages("RSQLite")
install.packages("DBI")
install.packages("ggplot2")  # This package is used for plotting
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)

# Adjust the query to handle the MM/DD/YY format
query <- "
SELECT 
    strftime('%Y-%m', '20' || substr(Date_Transaction, 7, 2) || '-' || substr(Date_Transaction, 1, 2) || '-' || substr(Date_Transaction, 4, 2)) AS YearMonth, 
    SUM(Pay_Amount) AS Total_Spent
FROM 
    '지출-액수_2024_08_17'
WHERE 
    Item = 'Grocery'
GROUP BY 
    YearMonth
ORDER BY 
    YearMonth;
"

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

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

# Create a plot using ggplot2
ggplot(monthly_sum, aes(x = YearMonth, y = Total_Spent)) +
  geom_line(group = 1, color = "blue") + 
  geom_point(color = "red") +
  labs(title = "Monthly Grocery Spending",
       x = "Year-Month",
       y = "Total Spent on Groceries") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))