如何高效处理DT的分隔列，转换为以类型为列的结构？

I have the following data.table:

id values valid_types
1 2|3 100|200
2 4 200
3 2|1 500|100

Here, valid_types specifies the valid types for each row (there are 4 total types: 100, 200, 500, 2000), with values in values corresponding to each type separated by |. I want to reshape this into a wide data.table with these types as columns, like this:

id 100 200 500
1 2 3 NA
2 NA 4 NA
3 1 NA 2

My original plan was to split both columns into lists, merge using the type list as keys, then convert back to a data.table—but this method is complicated and doesn't work properly. Is there a simpler way to do this?

Great question! For this kind of reshaping task, data.table's built-in functions for splitting and casting are perfect—they're concise, efficient, and avoid messy list manipulation. Here's a step-by-step solution:

Step 1: Load the required library and create your input data.table #

First, let's replicate your input data:

library(data.table)

dt <- data.table(
  id = c(1, 2, 3),
  values = c("2|3", "4", "2|1"),
  valid_types = c("100|200", "200", "500|100")
)

Step 2: Reshape to long format (split pipe-separated values) #

We'll split both values and valid_types into individual rows, pairing each type with its corresponding value, grouped by id:

dt_long <- dt[, .(
  type = unlist(tstrsplit(valid_types, "\\|")),  # Split types into individual entries
  value = as.numeric(unlist(tstrsplit(values, "\\|")))  # Split values and convert to numeric
), by = id]

This gives us a clean long-format table where each row is a single id-type-value combination:

id type value
1:  1  100     2
2:  1  200     3
3:  2  200     4
4:  3  500     2
5:  3  100     1

Step 3: Reshape back to wide format #

Now use dcast() to pivot the type column into separate columns, with id as the row identifier:

# If you only want the present types (100,200,500)
dt_wide <- dcast(dt_long, id ~ type, value.var = "value")

# If you need to include all 4 types (including 2000, even with NAs)
dt_wide_full <- dcast(
  dt_long, 
  id ~ factor(type, levels = c("100", "200", "500", "2000")), 
  value.var = "value"
)
# Clean up column names
setnames(dt_wide_full, c("id", "100", "200", "500", "2000"))

Final Output #

The first dt_wide will match your expected output exactly:

id 100 200 500
1:  1   2   3  NA
2:  2  NA   4  NA
3:  3   1  NA   2

Why this works better than your original approach #

Instead of manually handling list merges, this method leverages data.table's optimized grouping and reshaping functions. It's:

• Simpler: No need to manage list keys or complex merges
• Faster: data.table operations are vectorized and optimized for speed, even on large datasets
• Flexible: Easy to adjust to include all 4 types (like 2000) by setting factor levels

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