Spaces:
Running
Running
Download etl_process.R from phantomas34/perfect-margin: direct link, hf CLI and curl.
- Browser
- Download file 1.75 kB
-
https://huggingface.co/spaces/phantomas34/perfect-margin/resolve/main/etl_process.R
- Command line
-
hf download hf://spaces/phantomas34/perfect-margin/etl_process.R
-
curl -L -o etl_process.R https://huggingface.co/spaces/phantomas34/perfect-margin/resolve/main/etl_process.R
1.75 kB
| library(tidyverse) | |
| library(lubridate) | |
| library(DBI) | |
| library(RPostgres) | |
| # 1. Connect to DB | |
| con <- dbConnect(RPostgres::Postgres(), dbname = "sales_db", | |
| host = "localhost", port = 5433, | |
| user = Sys.info()[["user"]], password = "") | |
| # 2. Read and Clean CSV | |
| raw_data <- read_csv("Bid_SalesAnalysisByEstimatorByMonth_121125.csv", show_col_types = FALSE) | |
| clean_data <- raw_data %>% | |
| mutate(report_date = ym(MoYear)) %>% | |
| mutate(across(c(AmtBid, AmtBooked), ~ as.numeric(gsub("[\\$,]", "", .)))) %>% | |
| mutate(clean_name = case_when( | |
| Estimator == "Scott W. Hutchings" ~ "Scott Hutchings", | |
| Estimator == "SW" ~ "Scott Hutchings", | |
| Estimator == "SH" ~ "Scott Hutchings", | |
| TRUE ~ Estimator | |
| )) | |
| # 3. Update Estimators Table | |
| unique_estimators <- unique(clean_data$clean_name) | |
| for(name in unique_estimators){ | |
| # Check if exists, if not, insert | |
| exists <- dbGetQuery(con, "SELECT id FROM estimators WHERE primary_name = $1", params = list(name)) | |
| if(nrow(exists) == 0){ | |
| dbExecute(con, "INSERT INTO estimators (primary_name) VALUES ($1)", params = list(name)) | |
| } | |
| } | |
| # 4. Upload Sales Data | |
| est_map <- dbGetQuery(con, "SELECT id, primary_name FROM estimators") | |
| upload_ready <- clean_data %>% | |
| left_join(est_map, by = c("clean_name" = "primary_name")) %>% | |
| select(estimator_id = id, report_date, amt_bid = AmtBid, qty_bid = QtyBid, | |
| amt_booked = AmtBooked, qty_booked = QtyBooked) | |
| # Write to DB (Append mode) | |
| tryCatch({ | |
| dbWriteTable(con, "monthly_sales", upload_ready, append = TRUE, row.names = FALSE) | |
| print("Data Successfully Uploaded to Port 5433!") | |
| }, error = function(e) { | |
| print("Data likely already exists. (Duplicate key error expected if running twice).") | |
| }) | |
| dbDisconnect(con) |