Independent Verification of Enterprise Based Training (EBT)

##  0. Standardizes Start Date / End Date / Assessment Date to DD/MM/YYYY,
##     regardless of the original mixed formats.
##  1. Reads "7 EBT Summary Information Upto June 26_work.xlsx" (sheet
##     "Summary informatio (2)"), single header row.
##  2. Reads "5 upazila_district_division_name.xlsx" (sheet "Sheet1") which
##     has columns Division / District / Upazila.
##  3. Reads "4 asset_sector_wise_trade_occupation_list.xlsx" (sheet
##     "Sheet1") which has columns "Trade / Occupation Names" and
##     "Priority Sector/ISC".
##  4. For every row in the Summary file:
##       a. Fuzzy-matches "Address of Industry Partner/Training Institute "
##          against Upazila names first, then District, then Division.
##          Most specific match (Upazila) wins if found; otherwise it falls
##          back to District, then Division. If nothing matches well enough,
##          it writes "Not found".
##       b. Matches "Name of Occupation" against "Trade / Occupation Names"
##          (allowing partial / substring matches in either direction) and
##          pulls back the corresponding "Priority Sector/ISC" value. If
##          nothing matches, it writes "Not matched".
##  5. Saves everything (original columns + 2 new columns) to a new xlsx file.


## ---- 0. Install/load required packages --------------------------------
required_pkgs <- c("readxl", "writexl", "dplyr", "stringr", "stringdist", "lubridate")
to_install <- required_pkgs[!required_pkgs %in% installed.packages()[, "Package"]]
if (length(to_install) > 0) install.packages(to_install)

library(readxl)
library(writexl)
library(dplyr)
library(stringr)
library(stringdist)
library(lubridate)


## ---- 1. FILE PATHS — edit these three lines ----------------------------
folder <- "D:/Munem/All Projects/2026/DTE/10. Sampling/7. Sampling Sixth round"

summary_path <- file.path(folder, "7 EBT Summary Information Upto June 26_work.xlsx")
geo_path     <- file.path(folder, "5 upazila_district_division_name.xlsx")
sector_path  <- file.path(folder, "4 asset_sector_wise_trade_occupation_list.xlsx")

output_path  <- file.path(folder, "7 EBT Summary Information Upto June 26_Matched.xlsx")


## ---- 2. Tuning parameter -------------------------------------------------

MAX_NORM_DIST <- 0.25


## ---- 3. Load the data ----------------------------------------------------
## After loading, check colnames(summary_df) — if the columns printed
## don't look right, adjust "skip" (try skip = 1 or skip = 2).
summary_df <- read_excel(summary_path, sheet = "Summary informatio (2)")
geo_df     <- read_excel(geo_path,     sheet = "Sheet1")
sector_df  <- read_excel(sector_path,  sheet = "Sheet1")

## Trim whitespace from column names — Excel headers sometimes have hidden
## leading/trailing spaces, and readxl doesn't always preserve them
## consistently, which caused a silent NULL-column bug last time.
colnames(summary_df) <- trimws(colnames(summary_df))
colnames(geo_df)     <- trimws(colnames(geo_df))
colnames(sector_df)  <- trimws(colnames(sector_df))

## Drop trailing blank columns some Excel exports leave behind (...46, ...47, etc.)
blank_cols <- str_detect(colnames(summary_df), "^\\.\\.\\.[0-9]+$")
if (any(blank_cols)) summary_df <- summary_df[, !blank_cols]

cat("Summary file columns:\n");  print(colnames(summary_df))
cat("\nGeo lookup columns:\n");  print(colnames(geo_df))
cat("\nSector lookup columns:\n"); print(colnames(sector_df))


## ---- 4. Text cleaning helper ----------------------------------------------
clean_text <- function(x) {
  x %>%
    str_to_lower() %>%
    str_replace_all("[^a-z0-9\\s]", " ") %>%   # strip punctuation/numbers' noise chars
    str_squish()                                # collapse extra whitespace
}


## =========================================================================
##  TASK 0 — Standardize Start Date / End Date / Assessment Date
## =========================================================================
##  dates arrive in mixed formats (e.g. 18/04/2026, 20 Apr 2026, 11-04-2026, 25.04.2026, 5 April 2026) — and some cells are already real
##  Excel dates while others are text, so after read_excel the column can contain a mix of ISO-style strings (from real dates) and the original messy text (from text cells). This handles both in one pass.
##  Dates are kept as REAL Date values (not text) so Excel treats them as actual dates — sortable, usable in date-difference formulas, and filterable by date — rather than as plain text that merely looks like a date. Excel will display them using your default date format.

clean_dates <- function(x) {
  x_chr <- trimws(as.character(x))
  as_date(parse_date_time(x_chr, orders = c("ymd", "dmy", "mdy"), quiet = TRUE))
}

date_cols <- c("Start Date", "End Date", "Assessment Date")

for (col in date_cols) {
  if (!col %in% colnames(summary_df)) {
    cat(sprintf("WARNING: column '%s' not found — skipping date cleaning for it.\n", col))
    next
  }
  parsed <- clean_dates(summary_df[[col]])
  n_failed <- sum(is.na(parsed) & !is.na(summary_df[[col]]) & trimws(as.character(summary_df[[col]])) != "")
  if (n_failed > 0) {
    cat(sprintf("NOTE: %d value(s) in '%s' could not be parsed and will be blank — check these rows manually:\n", n_failed, col))
    bad_rows <- which(is.na(parsed) & !is.na(summary_df[[col]]) & trimws(as.character(summary_df[[col]])) != "")
    print(summary_df[bad_rows, c("Sl.", col)])
  }
  summary_df[[col]] <- parsed
}


## =========================================================================
##  TASK 1 — Address -> Division (fuzzy, most-specific-level-wins)
## =========================================================================

geo_df <- geo_df %>%
  mutate(
    Division = str_squish(as.character(Division)),
    District = str_squish(as.character(District)),
    Upazila  = str_squish(as.character(Upazila))
  )

upazila_lookup <- geo_df %>%
  distinct(Upazila, District, Division) %>%
  filter(!is.na(Upazila), Upazila != "") %>%
  mutate(Upazila_clean = clean_text(Upazila))

district_lookup <- geo_df %>%
  distinct(District, Division) %>%
  filter(!is.na(District), District != "") %>%
  mutate(District_clean = clean_text(District))

division_lookup <- geo_df %>%
  distinct(Division) %>%
  filter(!is.na(Division), Division != "") %>%
  mutate(Division_clean = clean_text(Division))


## Build all contiguous word-windows of length n from a vector of words
ngram_windows <- function(words, n) {
  if (length(words) < n) return(character(0))
  sapply(1:(length(words) - n + 1), function(i) paste(words[i:(i + n - 1)], collapse = " "))
}

## For one address (already cleaned) and a vector of candidate place names
## (already cleaned), find the candidate with the smallest normalized edit
## distance against any equal-length word-window of the address.
best_fuzzy_match <- function(address_clean, candidates) {
  candidates <- unique(candidates[!is.na(candidates) & candidates != ""])
  addr_words <- str_split(address_clean, "\\s+")[[1]]
  addr_words <- addr_words[addr_words != ""]
  if (length(addr_words) == 0 || length(candidates) == 0) {
    return(list(match = NA_character_, score = Inf))
  }
  
  best_name  <- NA_character_
  best_score <- Inf
  
  for (cand in candidates) {
    cand_words <- str_split(cand, "\\s+")[[1]]
    n <- length(cand_words)
    windows <- ngram_windows(addr_words, n)
    if (length(windows) == 0) next
    dists <- stringdist(cand, windows, method = "lv")
    norm_dists <- dists / nchar(cand)
    min_norm <- min(norm_dists)
    if (min_norm < best_score) {
      best_score <- min_norm
      best_name  <- cand
    }
  }
  list(match = best_name, score = best_score)
}

## Main per-address matcher: tries Upazila, then District, then Division.
match_address_to_division <- function(raw_address) {
  if (is.na(raw_address) || str_trim(raw_address) == "") {
    return(c(Matched_Division = "Not found", Matched_Level = NA, Matched_Place_Name = NA))
  }
  addr_clean <- clean_text(raw_address)
  
  ## Level 1: Upazila (most specific)
  res <- best_fuzzy_match(addr_clean, upazila_lookup$Upazila_clean)
  if (!is.na(res$match) && res$score <= MAX_NORM_DIST) {
    row <- upazila_lookup %>% filter(Upazila_clean == res$match) %>% slice(1)
    return(c(Matched_Division = row$Division, Matched_Level = "Upazila", Matched_Place_Name = row$Upazila))
  }
  
  ## Level 2: District
  res <- best_fuzzy_match(addr_clean, district_lookup$District_clean)
  if (!is.na(res$match) && res$score <= MAX_NORM_DIST) {
    row <- district_lookup %>% filter(District_clean == res$match) %>% slice(1)
    return(c(Matched_Division = row$Division, Matched_Level = "District", Matched_Place_Name = row$District))
  }
  
  ## Level 3: Division
  res <- best_fuzzy_match(addr_clean, division_lookup$Division_clean)
  if (!is.na(res$match) && res$score <= MAX_NORM_DIST) {
    row <- division_lookup %>% filter(Division_clean == res$match) %>% slice(1)
    return(c(Matched_Division = row$Division, Matched_Level = "Division", Matched_Place_Name = row$Division))
  }
  
  return(c(Matched_Division = "Not found", Matched_Level = NA, Matched_Place_Name = NA))
}


## ---- Apply to every row of the summary file -------------------------------
address_col <- trimws("Address of Industry Partner/Training Institute")
cat(sprintf("\nMatching %d addresses to Division (this can take a little while)...\n", nrow(summary_df)))

address_results <- t(sapply(summary_df[[address_col]], match_address_to_division))
summary_df$Matched_Division     <- address_results[, "Matched_Division"]
summary_df$Matched_Level         <- address_results[, "Matched_Level"]
summary_df$Matched_Place_Name    <- address_results[, "Matched_Place_Name"]


## =========================================================================
##  TASK 2 — Occupation -> Sector (substring match, either direction)
## =========================================================================

trade_col  <- "Trade / Occupation Names"
sector_col <- "Priority Sector/ISC"

sector_df <- sector_df %>%
  mutate(Trade_clean = clean_text(.data[[trade_col]]))

match_occupation_to_sector <- function(raw_occupation) {
  if (is.na(raw_occupation) || str_trim(raw_occupation) == "") return("Not matched")
  occ_clean <- clean_text(raw_occupation)
  if (occ_clean == "") return("Not matched")
  
  hits <- sector_df %>%
    filter(
      str_detect(Trade_clean, fixed(occ_clean)) |
        str_detect(occ_clean, fixed(Trade_clean))
    )
  
  if (nrow(hits) > 0) {
    return(paste(unique(hits[[sector_col]]), collapse = "; "))
  } else {
    return("Not matched")
  }
}

occupation_col <- trimws("Name of Occupation")
cat(sprintf("Matching %d occupations to Sector...\n", nrow(summary_df)))

summary_df$Matched_Sector <- sapply(summary_df[[occupation_col]], match_occupation_to_sector)


## =========================================================================
##  5. Save the result
## =========================================================================
write_xlsx(summary_df, output_path)
cat(sprintf("\nDone. Output written to: %s\n", output_path))

##  1. Reads "7.3 EBT Summary Information Upto June 26_Matched.xlsx"
##     (sheet "Sheet1").
##  2. Reads "8 Trade or occupation wsie sector from fifth round
##     cleaning.xlsx" (sheet "Sheet1"), which has columns "Trade" and
##     "Sector".
##  3. For every row in the Summary file, matches "Name of Occupation"
##     against "Trade" using an EXACT match — but trimmed of extra
##     whitespace and case-insensitive (so "Tailoring " matches
##     "tailoring"). This is a separate, additional check from the
##     existing "Matched_Sector" column already in the file (which came
##     from a different, substring-based match against a different list)
##     — that column is left untouched.
##  4. Adds ONE new combined column, "Trade_Sector_Match":
##       - If matched:      "Matched: <Sector Name>"
##       - If not matched:  "Not matched"
##  5. Saves everything to a NEW file: "7.4 EBT Summary Information Upto
##     June 26_Matched2.xlsx" (the original "7.3 ..." file is left
##     untouched).


## ---- 0. Install/load required packages --------------------------------
required_pkgs <- c("readxl", "writexl", "dplyr", "stringr")
to_install <- required_pkgs[!required_pkgs %in% installed.packages()[, "Package"]]
if (length(to_install) > 0) install.packages(to_install)

library(readxl)
library(writexl)
library(dplyr)
library(stringr)


## ---- 1. FILE PATHS -------------------------------------
folder <- "D:/Munem/All Projects/2026/DTE/10. Sampling/7. Sampling Sixth round"

summary_path <- file.path(folder, "7.3 EBT Summary Information Upto June 26_Matched.xlsx")
trade_path   <- file.path(folder, "8 Trade or occupation wsie sector from fifth round cleaning.xlsx")

output_path  <- file.path(folder, "7.4 EBT Summary Information Upto June 26_Matched2.xlsx")


## ---- 2. Load the data ----------------------------------------------------
summary_df <- read_excel(summary_path, sheet = "Sheet1")
trade_df   <- read_excel(trade_path,   sheet = "Sheet1")

## Trim whitespace from column names, in case of hidden leading/trailing
## spaces (this bit us last time with the address column).
colnames(summary_df) <- trimws(colnames(summary_df))
colnames(trade_df)   <- trimws(colnames(trade_df))

cat("Summary file columns:\n"); print(colnames(summary_df))
cat("\nTrade/Sector lookup columns:\n"); print(colnames(trade_df))

occupation_col <- "Name of Occupation"
trade_col      <- "Trade"
sector_col     <- "Sector"

stopifnot(occupation_col %in% colnames(summary_df))
stopifnot(trade_col %in% colnames(trade_df))
stopifnot(sector_col %in% colnames(trade_df))


## ---- 3. Build a clean lookup key (trimmed, lowercased) --------------------
trade_df <- trade_df %>%
  mutate(Trade_key = str_squish(str_to_lower(as.character(.data[[trade_col]]))))

## Warn if the lookup file itself has duplicate Trade names mapping to
## different sectors — worth knowing about before trusting the match.
dupe_check <- trade_df %>%
  filter(Trade_key != "") %>%
  group_by(Trade_key) %>%
  summarise(n_distinct_sectors = n_distinct(.data[[sector_col]]), .groups = "drop") %>%
  filter(n_distinct_sectors > 1)

if (nrow(dupe_check) > 0) {
  cat(sprintf("\nWARNING: %d Trade name(s) in the lookup file map to more than one Sector — check these:\n", nrow(dupe_check)))
  print(dupe_check)
}

## Keep first sector for each unique Trade key (after the warning above)
trade_lookup <- trade_df %>%
  filter(Trade_key != "") %>%
  distinct(Trade_key, .keep_all = TRUE) %>%
  select(Trade_key, !!sector_col := all_of(sector_col))


## ---- 4. Match ----------------------------------------------------------
summary_df <- summary_df %>%
  mutate(Occupation_key = str_squish(str_to_lower(as.character(.data[[occupation_col]])))) %>%
  left_join(trade_lookup, by = c("Occupation_key" = "Trade_key")) %>%
  mutate(
    Trade_Sector_Match = ifelse(
      !is.na(.data[[sector_col]]),
      paste0("Matched: ", .data[[sector_col]]),
      "Not matched"
    )
  ) %>%
  select(-Occupation_key, -all_of(sector_col))


## ---- 5. Save the result ---------------------------------------------------
write_xlsx(summary_df, output_path)

n_matched <- sum(summary_df$Trade_Sector_Match != "Not matched")
cat(sprintf(
  "\nDone. %d of %d rows matched.\nOutput written to: %s\n",
  n_matched, nrow(summary_df), output_path
))