4

I have a variable called month that is a list of values separated by a | character (ex. "1 | 2 | 3"). The number of values in each cell is not the same, so some have "1 | 2" and others have "1 | 2 | 3 | 4". I need to separate month into month1, month2, etc. all the way to the maximum number of values separated by | in the column so that each value has its own column. Some rows should have NA for the columns that exceed their number of values in the original month column. I have no idea where to begin, but I've provided the a minimal reproducible example of what the variable/dataframe look like below.

id <- c("Lars", "Susan", "Juan")
month <- c("1 | 2", "1 | 2 | 3", "1 | 2 | 3 | 4")
df <- data.frame(id,month)
Improve this question
0

2 Answers 2

Reset to default
5

You can use read.delim() with fill = TRUE, constructing the column names afterward.

res <- read.delim(text= paste(id, month, sep = "|"), 
           sep = "|", fill = TRUE, header = FALSE)
names(res) <- c("id", paste0("month", 1:(ncol(res)-1)))
print(res)

     id month1 month2 month3 month4
1  Lars      1      2     NA     NA
2 Susan      1      2      3     NA
3  Juan      1      2      3      4

If the maximum number of columns isn't achieved in the first five input lines, this may go wrong:

The number of data columns is determined by looking at the first five lines of input (or the whole input if it has less than five lines), or from the length of ‘col.names’ if it is specified and is longer. This could conceivably be wrong if ‘fill’ or ‘blank.lines.skip’ are true, so specify ‘col.names’ if necessary (as in the ‘Examples’).

In that case you could specify col.names = c("id", paste0("month", 1:maxmonths)) as part of your read.delim() call. (Assuming you know maxmonths in advance - it would also be possible to run through month first to find the max value ... something like max(lengths(strsplit(months, "|"))))

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

5

You can use separate_wider_delim() from tidyr.

tidyr::separate_wider_delim(df, month, delim = ' | ', names_sep = '.', too_few = "align_start")

# # A tibble: 3 × 5
#   id    month.1 month.2 month.3 month.4
#   <chr> <chr>   <chr>   <chr>   <chr>  
# 1 Lars  1       2       NA      NA     
# 2 Susan 1       2       3       NA     
# 3 Juan  1       2       3       4

You can also pass a regular expression as its delimiter, but you need to specify it using stringr::regex().

tidyr::separate_wider_delim(df, month, delim = stringr::regex('\\s*\\|\\s*'),
                            names_sep = '.', too_few = "align_start")
Improve this answer

2 Comments

This is fantastic, thank you for your help!
@connormonie a good way to thank folks who have spent time answering your question and were helpful is to upvote their post. If a specific post answered your question, please consider marking it as answered.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.