Category: CODES

Posted on

Jupyter Installation

sudo apt install python3-venv (Install the python3-venv package if you don't have it)
python3 -m venv myenv (Create a virtual environment)
source myenv/bin/activate (Activate the virtual environment)
pip install jupyter (Install Jupyter within this environment)
jupyter notebook (Run Jupyter Notebook)

after above procedure:

source myenv/bin/activate 
jupyter notebook

When you're done, you can deactivate the virtual environment by typing deactivate.
Posted on

지출-액수 R Bar Graph 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)

# 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'
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 bar graph using ggplot2
ggplot(july_spending, aes(x = reorder(Item, -Total_Spent), y = Total_Spent, fill = Item)) +
  geom_bar(stat = "identity") +
  labs(title = "Spending Breakdown by Item - July 2024 (Excluding Rent and Grocery)",
       x = "Item",
       y = "Total Spent") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1),
        legend.position = "none")
Posted on

지출-액수.csv each month Item Sum, R Code

library(RSQLite)
library(DBI)

# 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 July 2024
query <- "
SELECT 
    Item, 
    SUM(Pay_Amount) AS Total_Spent
FROM 
    '지출-액수_2024_08_17'
WHERE 
    strftime('%Y-%m', '20' || substr(Date_Transaction, 7, 2) || '-' || substr(Date_Transaction, 1, 2) || '-' || substr(Date_Transaction, 4, 2)) = '2024-03'
GROUP BY 
    Item
ORDER BY 
    Total_Spent DESC;
"

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

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

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

Stripe month by month revenue graph generating R code

# Install and load the necessary packages.  Below three lines can be dropped after installation once.
install.packages("RSQLite")
install.packages("DBI")
install.packages("ggplot2")
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)

# Query to calculate the monthly total of the 'gross' field, grouped by year and month
query <- "
SELECT 
    strftime('%Y-%m', created) AS YearMonth, 
    SUM(gross) AS Total_Gross
FROM 
    'Itemized_balance_change_from_activity_USD_2022-03-11_to_2024-08-05_America-Anchorage'
WHERE 
    strftime('%Y-%m', created) IS NOT NULL
GROUP BY 
    YearMonth
ORDER BY 
    YearMonth;
"

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

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

# Convert YearMonth to a Date type for better plotting
monthly_totals$YearMonth <- as.Date(paste0(monthly_totals$YearMonth, "-01"), format = "%Y-%m-%d")

# Create a line plot using ggplot2
ggplot(monthly_totals, aes(x = YearMonth, y = Total_Gross)) +
  geom_line(color = "blue", linewidth = 1) +  # Updated to use 'linewidth' instead of 'size'
  geom_point(color = "red", size = 2) +
  labs(title = "Monthly Total Gross Over Time",
       x = "Month-Year",
       y = "Total Gross") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))
Posted on

지출-액수.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")
Posted on

지출-액수.csv monthly sum by Item using R

# Install and load the necessary packages. Below two lines can be dropped after installing once.
install.packages("RSQLite")
install.packages("DBI")
library(RSQLite)
library(DBI)

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

# Print the result
print("Monthly Sum of Pay_Amount for Grocery Items by Year and Month:")
print(monthly_sum)

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

지출-액수.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))
Posted on

중앙일보 Yellowpage after combined_output_02

delete from combined_output_02 where field1 like 'field%'

MAKE field9

MAKE URL field

MAKE AreaCode field

MAKE TEL field

ALTER TABLE combined_output_02
ADD COLUMN field9 TEXT;

ALTER TABLE combined_output_02
ADD COLUMN URL TEXT;

ALTER TABLE combined_output_02
ADD COLUMN AreaCode TEXT;

ALTER TABLE combined_output_02
ADD COLUMN TEL TEXT;

ID auto number primary key add

MAKE ID field, Primary Key

한글 상호, update field7 to field8

UPDATE combined_output_02
SET field8 = field7
WHERE field8 IS NULL;

URL move, field3, field4, field5, field6

UPDATE combined_output_02
SET URL = 
    CASE 
        WHEN field7 LIKE 'URL%' THEN field7
        WHEN field6 LIKE 'URL%' THEN field6
        WHEN field5 LIKE 'URL%' THEN field5
        WHEN field4 LIKE 'URL%' THEN field4
        ELSE URL -- Keep the existing URL value if none of the conditions are met
    END;

do above for field4, field5, field6, etc.

update combined_output_02 set URL = substr(URL, -length(URL) + 5)

add field9, and copy field4, field5, field6 TEL 

copy phone TEL: xxx-xxx-xxx:

UPDATE combined_output_02
SET field9 = 
    CASE 
        WHEN field5 LIKE 'TEL:%' AND LENGTH(field5) = 17 THEN field5
        WHEN field4 LIKE 'TEL:%' AND LENGTH(field4) = 17 THEN field4
        WHEN field6 LIKE 'TEL:%' AND LENGTH(field6) = 17 THEN field6
        ELSE field9 -- Keep the existing value of field9 if none of the conditions are met
    END;

reformat phone TEL: (xxx) xxx-xxxx:

UPDATE combined_output_02
SET field9 = 
    CASE 
        WHEN field5 LIKE 'TEL%(%)%' AND LENGTH(field5) = 19 THEN
            SUBSTR(field5, 1, 5) || SUBSTR(field5, 7, 3) || '-' || SUBSTR(field5, 12, 3) || '-' || SUBSTR(field5, -4)
        WHEN field4 LIKE 'TEL%(%)%' AND LENGTH(field4) = 19 THEN
            SUBSTR(field4, 1, 5) || SUBSTR(field4, 7, 3) || '-' || SUBSTR(field4, 12, 3) || '-' || SUBSTR(field4, -4)
        WHEN field6 LIKE 'TEL%(%)%' AND LENGTH(field6) = 19 THEN
            SUBSTR(field6, 1, 5) || SUBSTR(field6, 7, 3) || '-' || SUBSTR(field6, 12, 3) || '-' || SUBSTR(field6, -4)
        ELSE
            field9 -- Keep the existing value of field9 if none of the conditions are met
    END;

reformat phone TEL: (xxx)xxx-xxxx:

UPDATE combined_output_02
SET field9 = 
    CASE 
        WHEN field5 LIKE 'TEL%(%)%' AND LENGTH(field5) = 18 THEN
            SUBSTR(field5, 1, 5) || SUBSTR(field5, 7, 3) || '-' || SUBSTR(field5, 11, 3) || '-' || SUBSTR(field5, -4)
        WHEN field4 LIKE 'TEL%(%)%' AND LENGTH(field4) = 18 THEN
            SUBSTR(field4, 1, 5) || SUBSTR(field4, 7, 3) || '-' || SUBSTR(field4, 11, 3) || '-' || SUBSTR(field4, -4)
        WHEN field6 LIKE 'TEL%(%)%' AND LENGTH(field6) = 18 THEN
            SUBSTR(field6, 1, 5) || SUBSTR(field6, 7, 3) || '-' || SUBSTR(field6, 11, 3) || '-' || SUBSTR(field6, -4)
        ELSE
            field9 -- Keep the existing value of field9 if none of the conditions are met
    END;

repeat above for TEL: in field4 and field6

area_code field add

UPDATE combined_output_02
SET AreaCode = substr(field9, 6, 3)

UPDATE field9 into TEL

update combined_output_02 set TEL = substr(field9, -12) where field9 is not null

AreaCode_TimeZone import

StandardTimeZone add

select combined_output_02.* , AreaCode_TimeZone.StandardTimeZone from combined_output_02 LEFT join AreaCode_TimeZone on combined_output_02.AreaCode = AreaCode_TimeZone.AreaCode

unique_phone sort out

SELECT MIN(ID) AS min_ID, TEL
FROM combined_output_02_timezone
GROUP BY TEL
select * from combined_output_02_timezone where ID in (select min_ID from unique_phone)

Posted on

KoreaDaily URL update

UPDATE combined_output_02_unique_phone
SET URL = field7
WHERE field7 like 'URL%'

do above for field4, field5, field6, etc.