raw_data_cleaning.R

# ----------------------------------------------------------------------------
# PROJECT: Chequeo Digital 
# TASK: clean HTML from survey data
# AUTHOR: UNDP - Accelerator Lab
# START DATE: 10/03/2025
#
# ----------------------------------------------------------------------------
#
# ----------------------------------------------------------------------------
# Replicability
# ----------------------------------------------------------------------------
#
# To make the project computationally replicable it is necessary to
# register package versions and their dependencies. As well as the R version.
#
# ----------------------------------------------------------------------------
# Print version, platform, and running OS
# ----------------------------------------------------------------------------

# ----------------------------------------------------------------------------
sI <- sessionInfo()

cat(
  sI$R.version$version.string, "\n",
  "Platform:", sI$R.version$platform, "\n",
  "Running under:", sI$running, "\n"
)
# ----------------------------------------------------------------------------

# ----------------------------------------------------------------------------
# Start a new project with renv
# ----------------------------------------------------------------------------

# ----------------------------------------------------------------------------
# Save package versions and their dependencies
# renv::init() # Only once

# renv Is an R package designed for project dependency management. 
# It allows to capture the exact state of the packages used.
# ----------------------------------------------------------------------------

# ----------------------------------------------------------------------------
# Code for starting the code replication: 
# renv::restore() # Only the first time
# ----------------------------------------------------------------------------

# ----------------------------------------------------------------------------
# Import packages 
# ----------------------------------------------------------------------------

if (!requireNamespace("pacman", quietly = TRUE)) {
  install.packages("pacman")  # Installs pacman if not available
}

pacman::p_load(
  dplyr,
  readr,
  here,
  writexl
)
# ----------------------------------------------------------------------------

###############################################################################
###############################################################################

# ----------------------------------------------------------------------------
# LOAD DATABASE
# ----------------------------------------------------------------------------
# Empresas Questions Data
#
# Relative path to data
empresas_questions_path <- here("data",
                      "cd_preguntas.csv")

# Import empresas data
cd_q_empresas <- read_csv(empresas_questions_path)
# ----------------------------------------------------------------------------


# ----------------------------------------------------------------------------
# DATA CLEANING
# ----------------------------------------------------------------------------

# -------------------------------------------------
# Empresas Questions Data
# -------------------------------------------------

# -------------------------------------------------
# 1. Count the number of unique IDTestUsua
# -------------------------------------------------
unique_ids_count <- length(unique(cd_q_empresas$IdTestUsuario))

cat("Number of unique IdTestUsuario:", unique_ids_count, "\n")

# -------------------------------------------------
# 2. Check if there are fully duplicated rows
# -------------------------------------------------
# 'duplicated(df)' returns TRUE for every fully duplicated row
num_duplicates <- sum(duplicated(cd_q_empresas))

cat("Number of duplicated rows in the dataset:", num_duplicates, "\n")

# -------------------------------------------------
# 3. Check how many companies have HTML code in the TextResponse column
# -------------------------------------------------
# We know that some answers may have html code, 
# let's systematically evaluate this point.

rows_with_html <- cd_q_empresas[grepl("<.*?", cd_q_empresas$TextoRespuesta, 
                                      ignore.case = TRUE),
                                "IdTestUsuario"]

companies_with_html <- length(unique(rows_with_html))

cat("Number of companies with HTML code in at least one response:", 
    companies_with_html, "\n")
# -------------------------------------------------


# -------------------------------------------------
# Get information of the case(s) with HTML code  
# Step 1: Define the regex pattern to detect basic HTML tags

# 1) Identify rows that contain HTML in any of the three specified columns
html_pattern <- "<.*?"
has_html <- grepl(html_pattern, cd_q_empresas[["TextoRespuesta"]], ignore.case = TRUE) |
  grepl(html_pattern, cd_q_empresas[["IdRespuesta"]], ignore.case = TRUE) |
  grepl(html_pattern, cd_q_empresas[["NavegadorRespuesta"]], ignore.case = TRUE)

cat("Number of rows affected by HTML:", sum(has_html), "\n")

# Only proceed if at least one row is affected
if (any(has_html)) {
  # 2) Merge and clean HTML from the three columns into "TextoRespuesta"
  combined_text <- paste(
    cd_q_empresas[["TextoRespuesta"]][has_html],
    cd_q_empresas[["IdRespuesta"]][has_html],
    cd_q_empresas[["NavegadorRespuesta"]][has_html],
    sep = " "
  )
  
  # Remove HTML tags from the combined text
  combined_text_clean <- gsub(html_pattern, "", combined_text)
  
  # Overwrite "TextoRespuesta" with the cleaned, merged text
  cd_q_empresas[["TextoRespuesta"]][has_html] <- combined_text_clean
  
  # 3) Shift all columns to the left by two positions for those rows
  #    starting immediately after "TextoRespuesta"
  #    We'll do this in a vectorized way, only for the rows with HTML.
  start_col <- 9  # The next column after "TextoRespuesta" (8) is column 9
  end_col   <- ncol(cd_q_empresas) - 2  # We'll remove the last two columns later
  
  cd_q_empresas[has_html, start_col:end_col] <- 
    cd_q_empresas[has_html, (start_col + 2):ncol(cd_q_empresas)]
}

# 4) Finally, drop the last two columns (these are now empty or irrelevant)
cd_q_empresas <- cd_q_empresas[, 1:(ncol(cd_q_empresas) - 2)]

#cat("Final data frame dimensions:", dim(cd_q_empresas2), "\n")
# ----------------------------------------------------------------------------

# ----------------------------------------------------------------------------
# 1) Define a simple function to remove residual HTML artifacts
clean_html_text <- function(text_vector) {
  # Remove standard HTML tags
  text_vector <- gsub("<.*?>", "", text_vector, ignore.case = TRUE)
  
  # Remove partial tags or style artifacts like span style="..."
  text_vector <- gsub('span style="[^"]+"', "", text_vector, ignore.case = TRUE)
  text_vector <- gsub('/span>', "", text_vector, ignore.case = TRUE)
  text_vector <- gsub('u>', "", text_vector, ignore.case = TRUE)
  text_vector <- gsub('/u>', "", text_vector, ignore.case = TRUE)
  text_vector <- gsub('font-size:[^ ]+', "", text_vector, ignore.case = TRUE)
  text_vector <- gsub('font-weight:[^ ]+', "", text_vector, ignore.case = TRUE)
  text_vector <- gsub(">", "", text_vector)  
  
  # Remove repeated whitespace
  text_vector <- gsub("\\s+", " ", text_vector)
  
  # Trim leading or trailing spaces
  text_vector <- trimws(text_vector)
  
  return(text_vector)
}

# 2) Apply the function to the target column after merging text
cd_q_empresas[["TextoRespuesta"]] <- 
  clean_html_text(cd_q_empresas[["TextoRespuesta"]])
# ----------------------------------------------------------------------------

# ----------------------------------------------------------------------------
# 4. Simple data overview
# ----------------------------------------------------------------------------
# Quick look at the structure and a basic summary
cat("\nData structure:\n")
str(cd_q_empresas)

cat("\nStatistical summary of the dataset:\n")
print(summary(cd_q_empresas))

# ----------------------------------------------------------------------------
# We knew there was HTML in those corrected variables above. Now let's check if 
# any other variables have html and apply the function to them if required.
# ----------------------------------------------------------------------------

# ----------------------------------------------------------------------------
# Function to check which variables contain HTML code
detect_html_variables <- function(data) {
  # Define the regex pattern to identify HTML
  html_pattern <- "<.*?>"
  
  # Initialize an empty vector to store affected variable names
  affected_vars <- c()
  
  # Loop through each column in the dataset
  for (col in names(data)) {
    # Extract column values, ensuring NA values are handled properly
    column_data <- data[[col]]
    
    # Remove NA values before applying grepl to avoid errors
    column_data <- column_data[!is.na(column_data)]
    
    # Check if any row in the column contains HTML
    if (any(grepl(html_pattern, column_data, ignore.case = TRUE))) {
      affected_vars <- c(affected_vars, col)  # Store column name if HTML is found
    }
  }
  
  return(affected_vars)  # Return the names of the columns that contain HTML
}

# Usage:
html_columns <- detect_html_variables(cd_q_empresas)
cat("Variables containing HTML:\n", paste(html_columns, collapse = ", "), "\n")
# ----------------------------------------------------------------------------

# ----------------------------------------------------------------------------

#  Apply the function to the new target column 
cd_q_empresas[["TextoPregunta"]] <- 
  clean_html_text(cd_q_empresas[["TextoPregunta"]])

# Test again, no columns with HTML now
html_columns2 <- detect_html_variables(cd_q_empresas)
cat("Variables containing HTML:\n", paste(html_columns2, collapse = ", "), "\n")
# ----------------------------------------------------------------------------

# ----------------------------------------------------------------------------
# Save data without HTML and columns corrected

# Save the dataframe as an Excel file
write_xlsx(cd_q_empresas, "data/cd_preguntas_noHTML.xlsx")
# ----------------------------------------------------------------------------