Reports

These are the steps that AI gave me (they work for my simplified dataset.


#Pivot longer to gather Q2 and Q3 columns
df_long <- df1 %>%
  pivot_longer(cols = starts_with("Q2"), names_to = "Q2", values_to = "Q2_value") %>%
  pivot_longer(cols = starts_with("Q3"), names_to = "Q3", values_to = "Q3_value")

# Separate Q1 into multiple rows
df_long <- df_long %>%
  separate_rows(Q1, sep = ",\\s*")


# Filter rows to match Q1 with Q2 and Q3 columns using mapply
df_long <- df_long %>%
  filter(mapply(grepl, Q1, Q2) & mapply(grepl, Q1, Q3))

# Select and rename columns to match the desired format
df_long <- df_long %>%
  select(ID, Q1, Q2_value, Q3_value) %>%
  rename(Q2 = Q2_value, Q3 = Q3_value)

79603167