How to split one column into multiple columns by delimiter (with different numbers of delimiter)

I have a dataframe like this one:

continent <- c("Europe", "Asia")
country <- c("France;Germany;Italy", "Japan")
start_problem <- data.frame(continent, country)
start_problem

I would like to seperate the values in the column country to multiple columns, one for every country. The end product should look like:

continent <- c("Europe", "Asia")
country1 <- c("France", "Japan")
country2 <- c("Germany", NA)
country3 <- c("Italy", NA)
goal <- data.frame(continent, country1, country2, country3)
goal

Using separate_wider_delim() does not work since not every continent has the same amount of countries, thus not the same amount of delimiters in the original column.

Thanks in advance

We can first find out how many columns are needed by finding the max number of occurrence of the delimiter ;. Then paste that information in the into = parameter of separate with the "country" string.

library(tidyverse)

col_number <- max(str_count(start_problem$country, ";") + 1)

start_problem %>% separate(country, 
                           into = paste0("country", seq_len(col_number)), 
                           sep = ";")

  continent country1 country2 country3
1    Europe   France  Germany    Italy
2      Asia    Japan     <NA>     <NA>

Another option by first separating the rows with separate_rows. Create a column with the names to use for pivot_wider to make your data wider like this:

library(tidyverse)
start_problem %>%
  separate_rows(country, sep = ";") %>%
  mutate(col_name = paste0("country", row_number()), .by = continent) %>%
  pivot_wider(names_from = col_name, values_from = country)
#> # A tibble: 2 × 4
#>   continent country1 country2 country3
#>   <chr>     <chr>    <chr>    <chr>   
#> 1 Europe    France   Germany  Italy   
#> 2 Asia      Japan    <NA>     <NA>

^{Created on 2023-03-31 with reprex v2.0.2}

in Base R:

cbind(start_problem[1], read.csv2(text=start_problem[,2], header = FALSE))
  continent     V1      V2    V3
1    Europe France Germany Italy
2      Asia  Japan              

if you strictly want NA then use

cbind(start_problem[1], read.csv2(text=start_problem[,2], header = FALSE, na.strings = ''))
  continent     V1      V2    V3
1    Europe France Germany Italy
2      Asia  Japan    <NA>  <NA>