Introduction

This document demonstrates the analysis of Monash Connect student queries and reviews data. The analysis focuses on understanding the characteristics of student questions, sentiment distribution, and engagement patterns to inform AI-driven dialogue workflows.

1. Setup and Data Loading

1.1 Package Loading and Environment Setup

# Load required packages
# tidyverse includes ggplot2, dplyr, stringr, readr, etc.
suppressPackageStartupMessages({
  library(tidyverse)
})

# Helper function to create output directory if it doesn't exist
ensure_dir <- function(path) {
  if (!dir.exists(path)) {
    dir.create(path, recursive = TRUE)
    cat("Created directory:", path, "\n")
  }
}

# Create output directory for all analysis artifacts
OUT_DIR <- "outputs_a3_demo"
ensure_dir(OUT_DIR)

# Display working directory and output directory
cat("Working directory:", getwd(), "\n")
## Working directory: F:/2025 S2/Bioinformation/code
cat("Output directory:", OUT_DIR, "\n")
## Output directory: outputs_a3_demo

1.2 Data Loading and Column Normalization

# Define the path to your CSV file
# Replace this with your actual CSV file path
DATA_PATH <- "monash_connect_reviews_20251018_154724.csv"  # Updated to correct file

# Check if file exists
if (!file.exists(DATA_PATH)) {
  stop("CSV file not found. Please update DATA_PATH with the correct file path.")
}

message("Reading CSV from: ", DATA_PATH)
## Reading CSV from: monash_connect_reviews_20251018_154724.csv
# Read the CSV file with increased guess_max for better column type detection
raw <- suppressMessages(readr::read_csv(DATA_PATH, guess_max = 200000))

# Display basic information about the raw data
cat("Raw data dimensions:", nrow(raw), "rows x", ncol(raw), "columns\n")
## Raw data dimensions: 178 rows x 10 columns
cat("Column names:", paste(names(raw), collapse = ", "), "\n")
## Column names: source, keyword, title, author, content, url, num_comments, score, created_utc, sentiment
# Normalize column names to be R-safe (replace spaces and special characters)
colnames(raw) <- make.names(colnames(raw))

# Heuristic mapping for common column patterns
# This handles different CSV formats automatically
text_col <- if ("text" %in% names(raw)) "text" else 
            if ("content" %in% names(raw)) "content" else 
            if ("title" %in% names(raw)) "title" else names(raw)[min(2, ncol(raw))]

sent_col <- if ("sentiment" %in% names(raw)) "sentiment" else 
            if (ncol(raw) >= 10) names(raw)[10] else NA

up_col <- if ("upvotes" %in% names(raw)) "upvotes" else 
          if ("score" %in% names(raw)) "score" else 
          if (ncol(raw) >= 7) names(raw)[7] else NA

cm_col <- if ("comments" %in% names(raw)) "comments" else 
          if ("num.comments" %in% names(raw)) "num.comments" else 
          if (ncol(raw) >= 8) names(raw)[8] else NA

src_col <- if ("source" %in% names(raw)) "source" else names(raw)[1]
url_col <- if ("url" %in% names(raw)) "url" else 
           if (ncol(raw) >= 5) names(raw)[5] else NA
ts_col <- if ("timestamp" %in% names(raw)) "timestamp" else 
          if ("created.utc" %in% names(raw)) "created.utc" else NA
role_col <- if ("role" %in% names(raw)) "role" else 
            if ("category" %in% names(raw)) "category" else NA

cat("Detected columns:\n")
## Detected columns:
cat("  Text column:", text_col, "\n")
##   Text column: content
cat("  Sentiment column:", ifelse(is.na(sent_col), "Not found", sent_col), "\n")
##   Sentiment column: sentiment
cat("  Upvotes column:", ifelse(is.na(up_col), "Not found", up_col), "\n")
##   Upvotes column: score
cat("  Comments column:", ifelse(is.na(cm_col), "Not found", cm_col), "\n")
##   Comments column: score
cat("  Source column:", src_col, "\n")
##   Source column: source

1.3 Data Cleaning and Transformation

# Transform raw data into standardized format
df <- raw %>%
  transmute(
    source = .data[[src_col]],
    text = as.character(.data[[text_col]]),
    url = if (!is.na(url_col)) .data[[url_col]] else NA,
    upvotes = suppressWarnings(as.numeric(if (!is.na(up_col)) .data[[up_col]] else NA)),
    comments = suppressWarnings(as.numeric(if (!is.na(cm_col)) .data[[cm_col]] else NA)),
    sentiment = if (!is.na(sent_col)) as.character(.data[[sent_col]]) else NA_character_,
    timestamp = if (!is.na(ts_col)) as.character(.data[[ts_col]]) else NA_character_,
    role = if (!is.na(role_col)) as.character(.data[[role_col]]) else NA_character_
  ) %>%
  mutate(
    # Clean text: replace line breaks and normalize whitespace
    text = stringr::str_replace_all(text, "[\\r\\n]+", " "),
    text = stringr::str_squish(text)
  ) %>%
  # Remove rows with empty or missing text
  filter(!is.na(text) & text != "")

# Display cleaned data information
cat("Cleaned data dimensions:", nrow(df), "rows x", ncol(df), "columns\n")
## Cleaned data dimensions: 166 rows x 8 columns
cat("Text length range:", min(nchar(df$text)), "-", max(nchar(df$text)), "characters\n")
## Text length range: 3 - 38510 characters
# Save a sample for reproducibility
sample_data <- head(df, 20)
readr::write_csv(sample_data, file.path(OUT_DIR, "sample_preview.csv"))
cat("Saved sample data to:", file.path(OUT_DIR, "sample_preview.csv"), "\n")
## Saved sample data to: outputs_a3_demo/sample_preview.csv

2. Data Understanding and Feature Engineering

2.1 Feature Engineering

# Create language-agnostic features for analysis
df <- df %>%
  mutate(
    # Text length features
    char_len = nchar(text),
    word_len = stringr::str_count(text, boundary("word")),
    
    # Question detection
    has_qmark = stringr::str_detect(text, "\\?"),
    
    # Handle missing values in categorical variables
    sentiment = ifelse(is.na(sentiment) | sentiment == "", "unlabelled", sentiment),
    role = ifelse(is.na(role) | role == "", "unknown", role)
  )

# Display feature summary
cat("Feature engineering completed:\n")
## Feature engineering completed:
cat("  Character length range:", min(df$char_len), "-", max(df$char_len), "\n")
##   Character length range: 3 - 38510
cat("  Word length range:", min(df$word_len), "-", max(df$word_len), "\n")
##   Word length range: 1 - 6886
cat("  Questions detected:", sum(df$has_qmark), "out of", nrow(df), "posts\n")
##   Questions detected: 102 out of 166 posts
cat("  Sentiment categories:", paste(unique(df$sentiment), collapse = ", "), "\n")
##   Sentiment categories: 中性, 正面, 负面
cat("  Role categories:", paste(unique(df$role), collapse = ", "), "\n")
##   Role categories: unknown

2.2 Data Completeness Analysis

# Calculate completeness (non-missing rate) for each column
completeness <- df %>% 
  summarise(across(everything(), ~mean(!is.na(.))))

# Save completeness results
readr::write_csv(completeness, file.path(OUT_DIR, "completeness.csv"))

# Display completeness table
knitr::kable(completeness, 
             caption = "Data Completeness by Column (proportion of non-missing values)",
             digits = 3)
Data Completeness by Column (proportion of non-missing values)
source text url upvotes comments sentiment timestamp role char_len word_len has_qmark
1 1 1 1 1 1 0 1 1 1 1 
cat("Data completeness analysis completed.\n")
## Data completeness analysis completed.
cat("Overall data quality is", round(mean(as.numeric(completeness)), 3), "\n")
## Overall data quality is 0.909

3. Data Visualization and Analysis

3.1 Sentiment Distribution Analysis

# Create sentiment distribution plot if sentiment data is available
if (!all(df$sentiment == "unlabelled")) {
  # Calculate sentiment counts
  sentiment_counts <- df %>%
    count(sentiment) %>%
    arrange(desc(n))
  
  # Create visualization
  p_sent <- sentiment_counts %>%
    ggplot(aes(x = reorder(sentiment, n), y = n, fill = sentiment)) +
    geom_col(alpha = 0.8) +
    coord_flip() +
    labs(
      title = "Distribution of Sentiment Categories",
      subtitle = "Analysis of student query sentiment patterns",
      x = "Sentiment Category",
      y = "Number of Posts",
      fill = "Sentiment"
    ) +
    theme_minimal() +
    theme(
      plot.title = element_text(size = 14, face = "bold"),
      plot.subtitle = element_text(size = 12),
      axis.text = element_text(size = 10)
    )
  
  # Save plot
  ggsave(file.path(OUT_DIR, "fig1_sentiment_distribution.png"), 
         p_sent, width = 8, height = 5, dpi = 150)
  
  # Display plot
  print(p_sent)
  
  # Display summary statistics
  cat("Sentiment distribution summary:\n")
  print(sentiment_counts)
} else {
  cat("No sentiment data available for analysis.\n")
}

## Sentiment distribution summary:
## # A tibble: 3 × 2
##   sentiment     n
##   <chr>     <int>
## 1 中性         91
## 2 正面         56
## 3 负面         19

3.2 Text Length Distribution Analysis

# Create histogram of query length distribution
# Calculate 98th percentile to remove extreme outliers for better visualization
length_98th <- quantile(df$word_len, 0.98)

p_len <- ggplot(df, aes(x = word_len)) +
  geom_histogram(binwidth = 5, fill = "#4C97F7", color = "white", alpha = 0.8) +
  scale_x_continuous(limits = c(0, length_98th)) +  # Truncate extreme values
  labs(
    title = "Distribution of Query Length (in Words)",
    subtitle = paste("Showing queries up to", round(length_98th), "words (98th percentile)"),
    x = "Words per Query",
    y = "Count"
  ) +
  theme_minimal() +
  theme(
    plot.title = element_text(size = 14, face = "bold"),
    plot.subtitle = element_text(size = 12)
  )

# Save plot
ggsave(file.path(OUT_DIR, "fig2_length_histogram.png"), 
       p_len, width = 8, height = 5, dpi = 150)
## Warning: Removed 4 rows containing non-finite outside the scale range
## (`stat_bin()`).
## Warning: Removed 2 rows containing missing values or values outside the scale range
## (`geom_bar()`).
# Display plot
print(p_len)
## Warning: Removed 4 rows containing non-finite outside the scale range (`stat_bin()`).
## Removed 2 rows containing missing values or values outside the scale range
## (`geom_bar()`).

# Display length statistics
cat("Text length statistics:\n")
## Text length statistics:
cat("  Mean words per query:", round(mean(df$word_len), 2), "\n")
##   Mean words per query: 198.6
cat("  Median words per query:", round(median(df$word_len), 2), "\n")
##   Median words per query: 57.5
cat("  Standard deviation:", round(sd(df$word_len), 2), "\n")
##   Standard deviation: 737.95
cat("  98th percentile:", round(length_98th, 2), "\n")
##   98th percentile: 1887.5

3.3 Question Proportion Analysis

# Analyze proportion of queries that are direct questions
question_stats <- df %>%
  count(has_qmark) %>%
  mutate(
    prop = n / sum(n),
    label = ifelse(has_qmark, "Questions", "Non-Questions")
  )

# Create visualization
p_qmark <- question_stats %>%
  ggplot(aes(x = label, y = prop, fill = label)) +
  geom_col(alpha = 0.8, show.legend = FALSE) +
  geom_text(aes(label = paste0(round(prop * 100, 1), "%")), 
            vjust = -0.5, size = 4, fontface = "bold") +
  labs(
    title = "Proportion of Queries as Direct Questions",
    subtitle = "Analysis of question mark usage in student queries",
    x = "Query Type",
    y = "Proportion of Total Queries"
  ) +
  scale_y_continuous(labels = scales::percent_format()) +
  theme_minimal() +
  theme(
    plot.title = element_text(size = 14, face = "bold"),
    plot.subtitle = element_text(size = 12)
  )

# Save plot
ggsave(file.path(OUT_DIR, "fig3_question_proportion.png"), 
       p_qmark, width = 8, height = 5, dpi = 150)

# Display plot
print(p_qmark)

# Display question statistics
cat("Question analysis:\n")
## Question analysis:
cat("  Total queries:", nrow(df), "\n")
##   Total queries: 166
cat("  Questions (with ?):", sum(df$has_qmark), "\n")
##   Questions (with ?): 102
cat("  Question rate:", round(mean(df$has_qmark) * 100, 1), "%\n")
##   Question rate: 61.4 %

3.4 Engagement Analysis by Sentiment

# Analyze engagement metrics by sentiment if numeric engagement data exists
if (!all(is.na(df$upvotes)) | !all(is.na(df$comments))) {
  
  # Calculate average engagement by sentiment
  agg <- df %>%
    mutate(sentiment = ifelse(sentiment == "unlabelled", "unlabelled", sentiment)) %>%
    group_by(sentiment) %>%
    summarise(
      avg_upvotes = mean(upvotes, na.rm = TRUE),
      avg_comments = mean(comments, na.rm = TRUE),
      count = n(),
      .groups = "drop"
    ) %>%
    pivot_longer(cols = starts_with("avg_"), names_to = "metric", values_to = "value") %>%
    mutate(metric = case_when(
      metric == "avg_upvotes" ~ "Average Upvotes",
      metric == "avg_comments" ~ "Average Comments",
      TRUE ~ metric
    ))
  
  # Create visualization
  p_eng <- ggplot(agg, aes(x = sentiment, y = value, fill = metric)) +
    geom_col(position = "dodge", alpha = 0.8) +
    labs(
      title = "Engagement Metrics by Sentiment",
      subtitle = "Comparison of upvotes and comments across sentiment categories",
      x = "Sentiment Category",
      y = "Average Count",
      fill = "Engagement Metric"
    ) +
    theme_minimal() +
    theme(
      plot.title = element_text(size = 14, face = "bold"),
      plot.subtitle = element_text(size = 12),
      legend.position = "bottom"
    )
  
  # Save plot
  ggsave(file.path(OUT_DIR, "fig4_engagement_by_sentiment.png"), 
         p_eng, width = 8, height = 5, dpi = 150)
  
  # Display plot
  print(p_eng)
  
  # Display engagement summary
  cat("Engagement analysis by sentiment:\n")
  print(agg)
} else {
  cat("No engagement data (upvotes/comments) available for analysis.\n")
}

## Engagement analysis by sentiment:
## # A tibble: 6 × 4
##   sentiment count metric           value
##   <chr>     <int> <chr>            <dbl>
## 1 中性         91 Average Upvotes   16.4
## 2 中性         91 Average Comments  16.4
## 3 正面         56 Average Upvotes   23.6
## 4 正面         56 Average Comments  23.6
## 5 负面         19 Average Upvotes   72.2
## 6 负面         19 Average Comments  72.2

3.5 Role-wise Analysis (if role data exists)

# Analyze query characteristics by role/category if role data exists
if (!all(df$role == "unknown")) {
  
  # Calculate role statistics
  role_stats <- df %>%
    group_by(role) %>%
    summarise(
      count = n(),
      avg_words = mean(word_len, na.rm = TRUE),
      question_rate = mean(has_qmark, na.rm = TRUE),
      .groups = "drop"
    ) %>%
    arrange(desc(count))
  
  # Create visualization for role-wise length distribution
  p_role <- ggplot(df, aes(x = role, y = word_len, fill = role)) +
    geom_violin(trim = TRUE, alpha = 0.7) +
    geom_boxplot(width = 0.15, outlier.size = 0.7, alpha = 0.8) +
    scale_y_continuous(limits = c(0, quantile(df$word_len, 0.98))) +
    labs(
      title = "Query Length Distribution by Role/Category",
      subtitle = "Violin plot showing word count distribution across different user roles",
      x = "Role/Category",
      y = "Words per Query"
    ) +
    theme_minimal() +
    theme(
      plot.title = element_text(size = 14, face = "bold"),
      plot.subtitle = element_text(size = 12),
      legend.position = "none",
      axis.text.x = element_text(angle = 45, hjust = 1)
    )
  
  # Save plot
  ggsave(file.path(OUT_DIR, "fig5_role_length.png"), 
         p_role, width = 8, height = 5, dpi = 150)
  
  # Display plot
  print(p_role)
  
  # Display role statistics
  cat("Role-wise analysis:\n")
  print(role_stats)
} else {
  cat("No role/category data available for analysis.\n")
}
## No role/category data available for analysis.

4. Summary Statistics and Export

4.1 Summary Table Generation

# Create comprehensive summary statistics
summary_tbl <- tibble(
  Metric = c(
    "Total Queries",
    "Mean Words per Query",
    "Median Words per Query",
    "Question Rate (%)",
    "Labelled Sentiment Rate (%)",
    "Unique Sources",
    "Date Range"
  ),
  Value = c(
    nrow(df),
    round(mean(df$word_len, na.rm = TRUE), 2),
    round(median(df$word_len, na.rm = TRUE), 2),
    round(mean(df$has_qmark, na.rm = TRUE) * 100, 1),
    round(mean(df$sentiment != "unlabelled", na.rm = TRUE) * 100, 1),
    length(unique(df$source)),
    if (!all(is.na(df$timestamp))) {
      paste("From", min(df$timestamp, na.rm = TRUE), "to", max(df$timestamp, na.rm = TRUE))
    } else "Not available"
  )
)

# Save summary table
readr::write_csv(summary_tbl, file.path(OUT_DIR, "summary_table.csv"))

# Display summary table
knitr::kable(summary_tbl, 
             caption = "Dataset Summary Statistics",
             col.names = c("Metric", "Value"))
Dataset Summary Statistics
Metric Value
Total Queries 166
Mean Words per Query 198.6
Median Words per Query 57.5
Question Rate (%) 61.4
Labelled Sentiment Rate (%) 100
Unique Sources 1
Date Range Not available 
cat("Summary statistics generated and saved.\n")
## Summary statistics generated and saved.

4.2 Export Analysis Results

# Create a comprehensive analysis report
analysis_report <- list(
  data_summary = summary_tbl,
  completeness = completeness,
  sentiment_distribution = if (exists("sentiment_counts")) sentiment_counts else NULL,
  question_analysis = question_stats,
  role_analysis = if (exists("role_stats")) role_stats else NULL
)

# Save analysis report as RDS for future reference
saveRDS(analysis_report, file.path(OUT_DIR, "analysis_report.rds"))

# Display file locations
cat("Analysis artifacts saved to:", OUT_DIR, "\n")
## Analysis artifacts saved to: outputs_a3_demo
cat("Files created:\n")
## Files created:
files_created <- list.files(OUT_DIR, full.names = TRUE)
for (file in files_created) {
  cat("  -", basename(file), "\n")
}
##   - analysis_report.rds 
##   - completeness.csv 
##   - fig1_sentiment_distribution.png 
##   - fig2_length_histogram.png 
##   - fig3_question_proportion.png 
##   - fig4_engagement_by_sentiment.png 
##   - FIT5145_A3_Analysis_Report.pdf 
##   - sample_preview.csv 
##   - summary_table.csv

5. Narrative Summary

# Generate narrative text for the report
cat("\n\n--- ANALYSIS NARRATIVE ---\n")
## 
## 
## --- ANALYSIS NARRATIVE ---
cat("\n## 4.2 Understanding the Data\n")
## 
## ## 4.2 Understanding the Data
cat("The dataset contains", nrow(df), "short-form, context-rich queries about Monash Connect.\n")
## The dataset contains 166 short-form, context-rich queries about Monash Connect.
cat("We derived language-agnostic descriptors (character length, word length, presence of question marks)\n")
## We derived language-agnostic descriptors (character length, word length, presence of question marks)
cat("and verified column completeness. This establishes basic veracity and informs prompt design for AI systems.\n")
## and verified column completeness. This establishes basic veracity and informs prompt design for AI systems.
if (file.exists(file.path(OUT_DIR, "fig1_sentiment_distribution.png"))) {
  cat("\n## 4.3 Sentiment Distribution\n")
  cat("Most posts show a mix of sentiment categories, reflecting diverse user experiences.\n")
  cat("This diversity is valuable as it provides balanced exposure for AI models to handle both\n")
  cat("factual inquiries and emotional responses effectively.\n")
}
## 
## ## 4.3 Sentiment Distribution
## Most posts show a mix of sentiment categories, reflecting diverse user experiences.
## This diversity is valuable as it provides balanced exposure for AI models to handle both
## factual inquiries and emotional responses effectively.
cat("\n## 4.4 Text Length Distribution\n")
## 
## ## 4.4 Text Length Distribution
cat("Queries cluster between", round(quantile(df$word_len, 0.25)), "and", 
    round(quantile(df$word_len, 0.75)), "words, which is ideal for efficient processing.\n")
## Queries cluster between 35 and 118 words, which is ideal for efficient processing.
cat("This suggests that prompt windows can remain compact while retaining sufficient context\n")
## This suggests that prompt windows can remain compact while retaining sufficient context
cat("for meaningful AI responses.\n")
## for meaningful AI responses.
if (file.exists(file.path(OUT_DIR, "fig3_question_proportion.png"))) {
  cat("\n## 4.5 Question Proportion Analysis\n")
  cat("The analysis shows that", round(mean(df$has_qmark) * 100, 1), 
      "% of queries contain question marks.\n")
  cat("This high rate confirms that users are actively seeking answers, validating\n")
  cat("the primary goal of AI assistance systems.\n")
}
## 
## ## 4.5 Question Proportion Analysis
## The analysis shows that 61.4 % of queries contain question marks.
## This high rate confirms that users are actively seeking answers, validating
## the primary goal of AI assistance systems.
if (exists("agg")) {
  cat("\n## 4.6 Engagement vs Sentiment\n")
  cat("Engagement patterns reveal how different sentiment categories perform.\n")
  cat("This information can be used to prioritize responses and identify areas\n")
  cat("requiring specialized attention or escalation procedures.\n")
}
## 
## ## 4.6 Engagement vs Sentiment
## Engagement patterns reveal how different sentiment categories perform.
## This information can be used to prioritize responses and identify areas
## requiring specialized attention or escalation procedures.
if (file.exists(file.path(OUT_DIR, "fig5_role_length.png"))) {
  cat("\n## 4.7 Role-wise Analysis\n")
  cat("Different user roles show varying communication patterns.\n")
  cat("This insight informs persona-specific response strategies in AI systems\n")
  cat("(e.g., concise responses for staff, detailed explanations for students).\n")
}

cat("\n## 4.8 Summary\n")
## 
## ## 4.8 Summary
cat("Overall, the dataset is well-suited for AI-driven dialogue workflows.\n")
## Overall, the dataset is well-suited for AI-driven dialogue workflows.
cat("The analysis substantiates Volume, Variety, and Veracity aspects of the data.\n")
## The analysis substantiates Volume, Variety, and Veracity aspects of the data.
cat("The short-form nature of queries supports real-time Velocity requirements\n")
## The short-form nature of queries supports real-time Velocity requirements
cat("of conversational AI systems, making it ideal for implementation.\n")
## of conversational AI systems, making it ideal for implementation.
cat("\n--- END NARRATIVE ---\n")
## 
## --- END NARRATIVE ---

Conclusion

This analysis provides a comprehensive understanding of the Monash Connect query dataset, revealing patterns in user behavior, sentiment distribution, and engagement metrics. The findings support the development of effective AI-driven dialogue systems for student support services.

The visualizations and statistics generated in this analysis can inform prompt engineering, response strategies, and system design for AI-powered student assistance platforms.