R Data Exploration: Tidyverse vs data.table (and Why You Should Prefer data.table)¶

# Install both ecosystems
install.packages(c("tidyverse", "data.table", "microbenchmark", "ggplot2"))

# Load libraries
library(dplyr)
library(data.table)
library(microbenchmark)
library(ggplot2)

# Set data.table options for better performance
options(datatable.optimize = 2L)  # Level 2 optimization
options(datatable.verbose = FALSE)  # Suppress verbose output

# Create sample datasets for comparison
set.seed(42)

# Small dataset for initial examples
mtcars_dt <- as.data.table(mtcars)
mtcars_tbl <- as_tibble(mtcars)

# Large dataset for performance testing
n <- 1000000
large_data <- data.table(
  id = 1:n,
  group = sample(letters[1:10], n, replace = TRUE),
  value1 = rnorm(n),
  value2 = rnorm(n),
  category = sample(c("A", "B", "C"), n, replace = TRUE)
)

# Save for later use
fwrite(large_data, "large_data.csv")

# tidyverse approach
library(readr)
system.time({
  data_tidy <- read_csv("large_data.csv")
})

# data.table approach
system.time({
  data_dt <- fread("large_data.csv")
})

# Microbenchmark for data reading
library(microbenchmark)

# Create test file
test_data <- data.table(
  id = 1:100000,
  value = rnorm(100000),
  category = sample(letters, 100000, replace = TRUE)
)
fwrite(test_data, "test_data.csv")

# Benchmark reading
benchmark_results <- microbenchmark(
  tidyverse = read_csv("test_data.csv"),
  datatable = fread("test_data.csv"),
  times = 10
)

print(benchmark_results)
# data.table typically 2-5x faster for large files

# data.table advantages
advantages <- list(
  "speed" = "fread() is optimized C code, parallel parsing",
  "memory" = "Lower memory footprint, efficient data types",
  "robustness" = "Handles messy CSVs better, fewer parsing errors",
  "features" = "Built-in compression, column type detection"
)

# Example of messy CSV handling
messy_csv <- "id,name,value\n1,John,10.5\n2,Mary,\n3,Bob,20.3\n4,Alice,15.7"
fwrite(messy_csv, "messy.csv")

# data.table handles missing values gracefully
dt_messy <- fread("messy.csv", na.strings = "")
print(dt_messy)

# tidyverse approach
result_tidy <- mtcars_tbl %>%
  filter(mpg > 20) %>%
  select(mpg, cyl, gear)

# data.table approach
result_dt <- mtcars_dt[mpg > 20, .(mpg, cyl, gear)]

# Verify results are identical
all.equal(result_tidy, result_dt, check.attributes = FALSE)

# Complex filtering with multiple conditions
# tidyverse
complex_tidy <- mtcars_tbl %>%
  filter(mpg > 20, cyl %in% c(4, 6), gear >= 4) %>%
  select(mpg, cyl, gear, hp) %>%
  arrange(desc(mpg))

# data.table
complex_dt <- mtcars_dt[
  mpg > 20 & cyl %in% c(4, 6) & gear >= 4,
  .(mpg, cyl, gear, hp)
][order(-mpg)]

# Performance comparison
microbenchmark(
  tidyverse = mtcars_tbl %>% filter(mpg > 20) %>% select(mpg, cyl, gear),
  datatable = mtcars_dt[mpg > 20, .(mpg, cyl, gear)],
  times = 1000
)

# data.table reference semantics (no copying)
# This modifies the original data.table in-place
mtcars_dt[mpg > 25, mpg := mpg * 1.1]

# tidyverse creates copies (memory intensive)
mtcars_tbl_new <- mtcars_tbl %>%
  mutate(mpg = ifelse(mpg > 25, mpg * 1.1, mpg))

# Check memory usage
object.size(mtcars_dt)  # Original size
object.size(mtcars_tbl_new)  # Larger due to copying

# tidyverse approach
grouped_tidy <- mtcars_tbl %>%
  group_by(cyl) %>%
  summarise(
    mean_mpg = mean(mpg),
    median_mpg = median(mpg),
    count = n()
  )

# data.table approach
grouped_dt <- mtcars_dt[
  , .(mean_mpg = mean(mpg), median_mpg = median(mpg), count = .N),
  by = cyl
]

# Verify results
all.equal(grouped_tidy, grouped_dt, check.attributes = FALSE)

# Multiple grouping variables and functions
# tidyverse
advanced_tidy <- mtcars_tbl %>%
  group_by(cyl, gear) %>%
  summarise(
    mean_mpg = mean(mpg),
    sd_mpg = sd(mpg),
    min_hp = min(hp),
    max_hp = max(hp),
    count = n(),
    .groups = "drop"
  )

# data.table
advanced_dt <- mtcars_dt[
  , .(mean_mpg = mean(mpg), sd_mpg = sd(mpg), 
      min_hp = min(hp), max_hp = max(hp), count = .N),
  by = .(cyl, gear)
]

# Performance on large dataset
n <- 1000000
large_dt <- data.table(
  group1 = sample(letters[1:5], n, replace = TRUE),
  group2 = sample(1:10, n, replace = TRUE),
  value = rnorm(n)
)

# Benchmark grouping
microbenchmark(
  datatable = large_dt[, .(mean_val = mean(value), count = .N), by = .(group1, group2)],
  times = 10
)

# data.table secondary indexes for fast grouping
# Set key for automatic sorting and fast access
setkey(mtcars_dt, cyl, gear)

# Now grouping by key variables is extremely fast
fast_grouped <- mtcars_dt[
  , .(mean_mpg = mean(mpg), count = .N),
  by = .(cyl, gear)
]

# Check if key is set
key(mtcars_dt)

# Create sample datasets for joining
dt1 <- data.table(
  id = c(1, 2, 3, 4),
  name = c("Alice", "Bob", "Charlie", "David"),
  department = c("IT", "HR", "IT", "Finance")
)

dt2 <- data.table(
  id = c(1, 2, 3, 5),
  salary = c(50000, 60000, 55000, 70000),
  experience = c(3, 5, 2, 8)
)

# tidyverse approach
tbl1 <- as_tibble(dt1)
tbl2 <- as_tibble(dt2)

joined_tidy <- tbl1 %>%
  left_join(tbl2, by = "id")

# data.table approach
joined_dt <- dt1[dt2, on = .(id)]

# Inner join
inner_tidy <- tbl1 %>% inner_join(tbl2, by = "id")
inner_dt <- dt1[dt2, on = .(id), nomatch = 0]

# Create larger datasets for performance testing
n <- 100000
dt_large1 <- data.table(
  id = 1:n,
  value1 = rnorm(n),
  group = sample(letters[1:10], n, replace = TRUE)
)

dt_large2 <- data.table(
  id = sample(1:n, n/2),
  value2 = rnorm(n/2),
  category = sample(c("A", "B", "C"), n/2, replace = TRUE)
)

# Set keys for fast joins
setkey(dt_large1, id)
setkey(dt_large2, id)

# Benchmark joins
microbenchmark(
  datatable = dt_large1[dt_large2, on = .(id)],
  times = 10
)

# Multiple key joins
dt3 <- data.table(
  id1 = c(1, 2, 3),
  id2 = c("A", "B", "C"),
  value = c(10, 20, 30)
)

dt4 <- data.table(
  id1 = c(1, 2, 3),
  id2 = c("A", "B", "C"),
  score = c(100, 200, 300)
)

# Join on multiple keys
multi_join <- dt3[dt4, on = .(id1, id2)]

# Rolling joins for time series
time_dt1 <- data.table(
  time = as.POSIXct(c("2023-01-01", "2023-01-03", "2023-01-05")),
  value = c(10, 20, 30)
)

time_dt2 <- data.table(
  time = as.POSIXct(c("2023-01-02", "2023-01-04")),
  price = c(100, 200)
)

setkey(time_dt1, time)
setkey(time_dt2, time)

# Rolling join (forward fill)
rolling_join <- time_dt1[time_dt2, on = .(time), roll = TRUE]

# Create wide dataset
wide_data <- data.table(
  id = 1:4,
  name = c("Alice", "Bob", "Charlie", "David"),
  math = c(90, 85, 92, 88),
  science = c(85, 90, 87, 91),
  english = c(88, 92, 89, 85)
)

# tidyverse approach
wide_tbl <- as_tibble(wide_data)
long_tidy <- wide_tbl %>%
  pivot_longer(cols = c(math, science, english),
               names_to = "subject",
               values_to = "score")

# data.table approach
long_dt <- melt(wide_data,
                id.vars = c("id", "name"),
                variable.name = "subject",
                value.name = "score")

# Verify results
all.equal(long_tidy, long_dt, check.attributes = FALSE)

# Convert back to wide format
# tidyverse approach
wide_tidy <- long_tidy %>%
  pivot_wider(names_from = subject,
              values_from = score)

# data.table approach
wide_dt <- dcast(long_dt, id + name ~ subject, value.var = "score")

# Performance comparison
microbenchmark(
  tidyverse = wide_tbl %>% pivot_longer(cols = c(math, science, english)),
  datatable = melt(wide_data, id.vars = c("id", "name")),
  times = 100
)

# Complex reshaping with multiple variables
complex_wide <- data.table(
  id = rep(1:3, each = 2),
  time = rep(c("before", "after"), 3),
  var1 = rnorm(6),
  var2 = rnorm(6),
  var3 = rnorm(6)
)

# Reshape multiple variables
complex_long <- melt(complex_wide,
                     id.vars = c("id", "time"),
                     variable.name = "variable",
                     value.name = "value")

# Reshape back with aggregation
complex_wide_agg <- dcast(complex_long,
                          id ~ time + variable,
                          value.var = "value",
                          fun.aggregate = mean)

# Best practice: data.table for heavy lifting, ggplot2 for plotting
# Process data with data.table
plot_data <- mtcars_dt[
  , .(mean_mpg = mean(mpg), mean_hp = mean(hp), count = .N),
  by = cyl
]

# Plot with ggplot2
library(ggplot2)
p <- ggplot(plot_data, aes(x = cyl, y = mean_mpg, size = count)) +
  geom_point() +
  labs(title = "Mean MPG by Cylinders",
       x = "Number of Cylinders",
       y = "Mean MPG") +
  theme_minimal()

print(p)

# For large datasets, aggregate first with data.table
# This is much faster than letting ggplot2 handle raw data
large_plot_data <- large_data[
  , .(mean_value = mean(value1), count = .N),
  by = .(group, category)
]

# Then plot the aggregated data
p_large <- ggplot(large_plot_data, aes(x = group, y = mean_value, fill = category)) +
  geom_bar(stat = "identity", position = "dodge") +
  labs(title = "Mean Values by Group and Category") +
  theme_minimal()

print(p_large)

# Create benchmark function
benchmark_operations <- function(n = 100000) {
  # Create test data
  test_data <- data.table(
    id = 1:n,
    group = sample(letters[1:10], n, replace = TRUE),
    value1 = rnorm(n),
    value2 = rnorm(n)
  )

  # Convert to tibble for tidyverse
  test_tbl <- as_tibble(test_data)

  # Benchmark operations
  results <- list()

  # Filtering
  results$filter <- microbenchmark(
    tidyverse = test_tbl %>% filter(value1 > 0),
    datatable = test_data[value1 > 0],
    times = 100
  )

  # Grouping
  results$group <- microbenchmark(
    tidyverse = test_tbl %>% group_by(group) %>% summarise(mean_val = mean(value1)),
    datatable = test_data[, .(mean_val = mean(value1)), by = group],
    times = 100
  )

  # Joining
  test_data2 <- data.table(
    id = sample(1:n, n/2),
    extra_value = rnorm(n/2)
  )
  test_tbl2 <- as_tibble(test_data2)

  results$join <- microbenchmark(
    tidyverse = test_tbl %>% left_join(test_tbl2, by = "id"),
    datatable = test_data[test_data2, on = .(id)],
    times = 100
  )

  return(results)
}

# Run benchmarks
benchmark_results <- benchmark_operations(1000000)

# Print results
for (operation in names(benchmark_results)) {
  cat("\n", operation, ":\n")
  print(benchmark_results[[operation]])
}

# Memory usage comparison
memory_test <- function() {
  # Create large dataset
  n <- 1000000
  large_dt <- data.table(
    id = 1:n,
    value = rnorm(n)
  )

  # Memory before operation
  mem_before <- gc()

  # data.table operation (in-place)
  large_dt[value > 0, value := value * 2]

  # Memory after operation
  mem_after <- gc()

  cat("Memory usage - Before:", mem_before[2, 2], "MB\n")
  cat("Memory usage - After:", mem_after[2, 2], "MB\n")
  cat("Memory increase:", mem_after[2, 2] - mem_before[2, 2], "MB\n")
}

memory_test()

# data.table is preferred for:
preferences <- list(
  "large_datasets" = ">1M rows, memory efficiency",
  "heavy_grouping" = "Complex aggregations, multiple grouping variables",
  "memory_constraints" = "In-place operations, reference semantics",
  "production_systems" = "Speed, reliability, reproducibility",
  "data_pipelines" = "ETL, data processing, batch jobs"
)

# Example: Production data pipeline
production_pipeline <- function(input_file, output_file) {
  # Read with data.table for speed
  data <- fread(input_file)

  # Process with data.table for efficiency
  processed <- data[
    , .(mean_value = mean(value), count = .N),
    by = .(group, category)
  ][count > 10]  # Filter groups with sufficient data

  # Write with data.table for speed
  fwrite(processed, output_file)

  return(processed)
}

# tidyverse is preferred for:
tidyverse_preferences <- list(
  "teaching" = "Readable syntax, clear data flow",
  "prototyping" = "Quick exploration, iterative analysis",
  "visualization" = "ggplot2 integration, plot aesthetics",
  "team_collaboration" = "Familiar syntax, gentle learning curve",
  "small_datasets" = "<100K rows, interactive analysis"
)

# Example: Exploratory data analysis
eda_pipeline <- function(data) {
  # Use tidyverse for readability
  summary <- data %>%
    group_by(category) %>%
    summarise(
      mean_value = mean(value),
      median_value = median(value),
      count = n(),
      .groups = "drop"
    ) %>%
    arrange(desc(mean_value))

  # Plot with ggplot2
  p <- ggplot(summary, aes(x = category, y = mean_value)) +
    geom_col() +
    theme_minimal()

  return(list(summary = summary, plot = p))
}

# Best practice: Use both appropriately
hybrid_approach <- function(input_file) {
  # 1. Read with data.table (fast)
  raw_data <- fread(input_file)

  # 2. Process with data.table (efficient)
  processed_data <- raw_data[
    , .(mean_value = mean(value), count = .N),
    by = .(group, category)
  ][count > 10]

  # 3. Convert to tibble for analysis
  analysis_data <- as_tibble(processed_data)

  # 4. Use tidyverse for exploration
  exploration <- analysis_data %>%
    group_by(group) %>%
    summarise(total_count = sum(count)) %>%
    arrange(desc(total_count))

  # 5. Plot with ggplot2
  p <- ggplot(analysis_data, aes(x = group, y = mean_value, fill = category)) +
    geom_col(position = "dodge") +
    theme_minimal()

  return(list(data = analysis_data, exploration = exploration, plot = p))
}

# ❌ WRONG: Mixing data types silently
# This can cause silent failures
df <- data.frame(id = 1:5, value = rnorm(5))
dt <- as.data.table(df)

# Don't mix data.frame and data.table operations
result <- df %>% filter(value > 0)  # This works
result <- dt %>% filter(value > 0)  # This might not work as expected

# ✅ CORRECT: Use consistent data types
# Always convert early
dt <- as.data.table(df)
result <- dt[value > 0]  # Use data.table syntax

# ❌ WRONG: Memory-intensive operations
# This creates copies
large_data <- data.table(id = 1:1000000, value = rnorm(1000000))
large_data_new <- large_data[value > 0]  # Creates copy
large_data_new[, value := value * 2]     # Creates another copy

# ✅ CORRECT: In-place operations
large_data[value > 0, value := value * 2]  # Modifies in-place

# ❌ WRONG: Inefficient grouping
# This is slow for large datasets
slow_group <- large_data %>%
  group_by(round(value, 1)) %>%
  summarise(count = n())

# ✅ CORRECT: Use data.table grouping
fast_group <- large_data[, .(count = .N), by = round(value, 1)]

# ❌ WRONG: Long pipe chains obscure bottlenecks
# This makes debugging difficult
result <- data %>%
  filter(condition1) %>%
  group_by(group) %>%
  summarise(mean_val = mean(value)) %>%
  filter(mean_val > threshold) %>%
  arrange(desc(mean_val)) %>%
  head(10)

# ✅ CORRECT: Break into steps for clarity
filtered_data <- data[condition1]
grouped_data <- filtered_data[, .(mean_val = mean(value)), by = group]
filtered_groups <- grouped_data[mean_val > threshold]
final_result <- filtered_groups[order(-mean_val)][1:10]

# ❌ WRONG: Not setting keys for joins
# This is slow for large datasets
slow_join <- dt1[dt2, on = .(id)]

# ✅ CORRECT: Set keys for fast joins
setkey(dt1, id)
setkey(dt2, id)
fast_join <- dt1[dt2, on = .(id)]

# ❌ WRONG: Not cleaning up large objects
# This can cause memory issues
large_data <- fread("huge_file.csv")
processed_data <- large_data[condition]
# large_data still in memory

# ✅ CORRECT: Clean up when done
large_data <- fread("huge_file.csv")
processed_data <- large_data[condition]
rm(large_data)  # Remove from memory
gc()  # Force garbage collection

# Essential data.table operations
# 1. Fast data reading
DT <- fread("data.csv")

# 2. Filtering and selecting
DT[condition, .(col1, col2)]

# 3. Grouping and aggregation
DT[, .(mean_val = mean(value), count = .N), by = group]

# 4. Joining tables
DT1[DT2, on = .(key)]

# 5. Reshaping data
melt(DT, id.vars = "id")
dcast(DT, id ~ variable, value.var = "value")

# 6. In-place operations
DT[condition, col := new_value]

# 7. Setting keys for performance
setkey(DT, key_column)

# Essential tidyverse operations
# 1. Data reading
data <- read_csv("data.csv")

# 2. Filtering and selecting
data %>% filter(condition) %>% select(col1, col2)

# 3. Grouping and aggregation
data %>% group_by(group) %>% summarise(mean_val = mean(value))

# 4. Joining tables
data1 %>% left_join(data2, by = "key")

# 5. Reshaping data
data %>% pivot_longer(cols = c(col1, col2), names_to = "variable", values_to = "value")
data %>% pivot_wider(names_from = variable, values_from = value)

# 6. Mutating data
data %>% mutate(new_col = col1 * 2)

# Performance optimization checklist
performance_checklist <- list(
  "data_reading" = "Use fread() for large files",
  "memory_management" = "Use in-place operations, clean up objects",
  "grouping" = "Set keys for fast grouping and joins",
  "filtering" = "Use data.table syntax for large datasets",
  "joining" = "Set keys before joining, use appropriate join types",
  "reshaping" = "Use melt() and dcast() for large datasets",
  "plotting" = "Aggregate data first, then plot with ggplot2"
)