使用dplyr转置Redshift的tbl对象时spread函数报错求助

Hey there! Let's work through this pivot issue step by step. The problem you're hitting with spread is likely tied to untrimmed whitespace in your day column and the need to map full weekday names to the abbreviations you want in your final output. Here's how to fix it:

Step 1: Clean and standardize your weekday data #

First, we need to trim extra spaces from the day column and convert those full weekday names to the 3-4 letter abbreviations you specified. This ensures consistent column names when we pivot.

library(dplyr)
library(tidyr)
library(stringr) # For str_trim()

# Start with your existing tbl from Redshift
cleaned_data <- your_redshift_tbl %>%
  # Trim leading/trailing spaces from the day column
  mutate(day = str_trim(day)) %>%
  # Map full weekday names to your target abbreviations
  mutate(day_abbr = case_when(
    day == "MONDAY" ~ "MON",
    day == "TUESDAY" ~ "TUES",
    day == "WEDNESDAY" ~ "WED",
    day == "THURSDAY" ~ "THUR",
    day == "FRIDAY" ~ "FRI",
    day == "SATURDAY" ~ "SAT",
    day == "SUNDAY" ~ "SUN",
    TRUE ~ day # Catch any unexpected values to avoid errors
  )) %>%
  # Optional: Convert riid from double to integer for cleaner output
  mutate(riid = as.integer(riid))

Step 2: Pivot to wide format with pivot_wider #

Instead of using spread (which is now soft-deprecated in favor of more flexible functions), we'll use tidyr::pivot_wider. This handles missing columns automatically and gives you more control over the output:

final_output <- cleaned_data %>%
  pivot_wider(
    id_cols = riid, # Keep riid as the row identifier
    names_from = day_abbr, # Use our cleaned abbreviations as column names
    values_from = hour, # Populate columns with hour values
    values_fn = first, # Handle any duplicate riid/day pairs (take first hour)
    names_expand = TRUE, # Ensure all 7 weekday columns exist even if empty
    names_order = c("MON", "TUES", "WED", "THUR", "FRI", "SAT", "SUN") # Match your desired column order
  ) %>%
  # Optional: Replace NA values with empty strings if you prefer that over NA
  mutate(across(c(MON, TUES, WED, THUR, FRI, SAT, SUN), ~replace_na(., "")))

Why spread was throwing errors #

A few common issues that break spread here:

• Uncleaned whitespace: Your original day column had trailing spaces (like "THURSDAY "), which would create column names with spaces instead of clean abbreviations.
• Missing columns: spread won't automatically add columns for weekdays that don't appear in your data, while pivot_wider with names_expand = TRUE fixes this.
• Duplicate entries: If you had multiple rows for the same riid and day, spread would throw an error immediately—pivot_wider lets you specify how to handle duplicates with values_fn.

Testing this with your sample data will give you exactly the output format you want:

内容的提问来源于stack exchange，提问作者Pravellika