Category: CODES

SELECT 
    substr(Date, 7, 4) || '-' || substr(Date, 1, 2) AS month,
    SUM(gross) AS total_gross,
    SUM(fee) AS total_fee
FROM "PayPal_2024_01_01-2024_12_31_"
WHERE Type = 'Subscription Payment'
GROUP BY month
ORDER BY month;

SELECT 
    strftime('%Y-%m', created_utc) AS month,
    SUM(CASE WHEN gross < 0 THEN gross ELSE 0 END) AS total_recurring_fee,
    SUM(CASE WHEN gross > 0 THEN gross ELSE 0 END) AS total_charged_amount,
    SUM(fee) AS total_merchant_fees
FROM "Stripe_2024_01_01-2024_12_31"
WHERE strftime('%Y', created_utc) = '2024'
GROUP BY month
ORDER BY month;

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 calculate the sum for each item in October 2024
query <- "
SELECT 
    Item, 
    SUM(Pay_Amount) AS Total_Spent
FROM 
    '지출-액수_2024_10_21'
WHERE 
    strftime('%Y-%m', '20' || substr(Date_Transaction, 7, 2) || '-' || substr(Date_Transaction, 1, 2) || '-' || substr(Date_Transaction, 4, 2)) = '2024-10'
GROUP BY 
    Item
ORDER BY 
    Total_Spent DESC;
"

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

# Print the result
print("Sum of Pay_Amount by Item for October 2024:")
print(October_2024_sum)

# Save the result as a CSV file
csv_file_path <- "/home/jbyungrokim/Downloads/October_2024_sum.csv"
write.csv(October_2024_sum, file = csv_file_path, row.names = FALSE)
print(paste("Data has been written to", csv_file_path))

# Create a bar graph using ggplot2
ggplot(October_2024_sum, aes(x = reorder(Item, -Total_Spent), y = Total_Spent)) +
  geom_bar(stat = "identity", fill = "steelblue") +
  theme_minimal() +
  labs(title = "Total Spending by Item for October 2024",
       x = "Item",
       y = "Total Spent ($)") +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

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

https://console.cloud.google.com/security/recaptcha?referrer=search&hl=en&project=csamigroup-com-recaptcha

https://developers.google.com/recaptcha/intro

import numpy as np
import matplotlib.pyplot as plt
from matplotlib.animation import FuncAnimation, FFMpegWriter

# Define the new function to integrate: x * log(x)
def func(x):
    # Avoid log(0) by returning 0 when x is 0
    return np.where(x == 0, 0, x * np.log(x))

# Set up the figure and axis
fig, ax = plt.subplots()
a, b = 0.01, 2  # Define the interval [a, b] avoiding 0 to prevent log(0) issues
x_vals = np.linspace(a, b, 1000)
ax.plot(x_vals, func(x_vals), 'r', label=r'$x \log(x)$')
ax.set_ylim(-1, 2)
ax.set_xlim(a, b)

# Title and labels
ax.set_title('Numerical Integration Process using Trapezoidal Rule for $x \log(x)$')
ax.set_xlabel('x')
ax.set_ylabel('f(x)')
ax.legend()

# Fill area under the curve (for animation purposes)
patches = []  # To store the artists

# Define the number of trapezoids to draw
n_trapezoids = 50
x_points = np.linspace(a, b, n_trapezoids + 1)
y_points = func(x_points)

# Function to update the animation at each step
def update(frame):
    global patches
    for patch in patches:
        patch.remove()
    patches = []
    
    # Plot the new trapezoid for the current frame
    if frame > 0:
        patch = ax.fill_between([x_points[frame-1], x_points[frame]], [y_points[frame-1], y_points[frame]], 
                                color='lightblue', alpha=0.5)
        patches.append(patch)  # Store the artist
    
    # Redraw the whole plot with current trapezoids filled
    for i in range(1, frame):
        patch = ax.fill_between([x_points[i-1], x_points[i]], [y_points[i-1], y_points[i]], color='lightblue', alpha=0.5)
        patches.append(patch)  # Store each trapezoid artist

# Create the animation
ani = FuncAnimation(fig, update, frames=range(1, n_trapezoids+1), interval=200, repeat=False)

# Save the animation as an MP4 file using FFmpeg
mp4_writer = FFMpegWriter(fps=10, metadata=dict(artist='Me'), bitrate=1800)
ani.save("integration_animation_xlogx.mp4", writer=mp4_writer)

# Optionally, show the animation (if you still want to display it)
plt.show()

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)

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 calculate the sum for each item in September 2024
query <- "
SELECT 
    Item, 
    SUM(Pay_Amount) AS Total_Spent
FROM 
    '지출-액수_2024_09_14'
WHERE 
    strftime('%Y-%m', '20' || substr(Date_Transaction, 7, 2) || '-' || substr(Date_Transaction, 1, 2) || '-' || substr(Date_Transaction, 4, 2)) = '2024-09'
GROUP BY 
    Item
ORDER BY 
    Total_Spent DESC;
"

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

# Print the result
print("Sum of Pay_Amount by Item for September 2024:")
print(September_2024_sum)

# Save the result as a CSV file
csv_file_path <- "/home/jbyungrokim/Downloads/September_2024_sum.csv"
write.csv(September_2024_sum, file = csv_file_path, row.names = FALSE)
print(paste("Data has been written to", csv_file_path))

# Create a bar graph using ggplot2
ggplot(September_2024_sum, aes(x = reorder(Item, -Total_Spent), y = Total_Spent)) +
  geom_bar(stat = "identity", fill = "steelblue") +
  theme_minimal() +
  labs(title = "Total Spending by Item for September 2024",
       x = "Item",
       y = "Total Spent ($)") +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

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

import sqlite3
import pandas as pd

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

# Connect to the SQLite database
conn = sqlite3.connect(db_path)

# Adjust the query to calculate the sum for each item by year and month
query = """
SELECT 
    strftime('%Y-%m', '20' || substr(Date_Transaction, 7, 2) || '-' || substr(Date_Transaction, 1, 2) || '-' || substr(Date_Transaction, 4, 2)) AS YearMonth,
    Item, 
    SUM(Pay_Amount) AS Total_Spent
FROM 
    '지출-액수_2024_08_20'
GROUP BY 
    YearMonth, Item
ORDER BY 
    YearMonth, Total_Spent DESC;
"""

# Execute the query and store the result in a pandas DataFrame
monthly_sum = pd.read_sql_query(query, conn)

# Reshape the data into a wide format for better readability in a spreadsheet
monthly_sum_wide = monthly_sum.pivot(index='Item', columns='YearMonth', values='Total_Spent').fillna(0)

# Export the data to a CSV file
output_path = "/home/jbyungrokim/CSV/monthly_sum_by_item_Jupyter.csv"
monthly_sum_wide.to_csv(output_path, index=True)

# Print the result
print("Sum of Pay_Amount by Item for each Year/Month:")
print(monthly_sum_wide)

# Close the connection to the SQLite database
conn.close()

library(RSQLite)
library(DBI)
library(tidyr)  # For reshaping the data
library(readr)  # For exporting data to CSV

# 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 calculate the sum for each item by year and month
query <- "
SELECT 
    strftime('%Y-%m', '20' || substr(Date_Transaction, 7, 2) || '-' || substr(Date_Transaction, 1, 2) || '-' || substr(Date_Transaction, 4, 2)) AS YearMonth,
    Item, 
    SUM(Pay_Amount) AS Total_Spent
FROM 
    '지출-액수_2024_08_20'
GROUP BY 
    YearMonth, Item
ORDER BY 
    YearMonth, Total_Spent DESC;
"

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

# Print the result
print("Sum of Pay_Amount by Item for each Year/Month:")
print(monthly_sum)

# Reshape the data into a wide format for better readability in a spreadsheet
monthly_sum_wide <- spread(monthly_sum, YearMonth, Total_Spent, fill = 0)

# Export the data to a CSV file
output_path <- "/home/jbyungrokim/CSV/monthly_sum_by_item.csv"
write_csv(monthly_sum_wide, output_path)

# Print the location of the saved file
print(paste("Data has been saved to:", output_path))

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