2

I have to import a table that look like as the following dataframe:

> df = data.frame(x = c("a", "a.b","a.b.c","a.b.d", "a.d"))
> df
      x
1  <NA>
2     a
3   a.b
4 a.b.c
5 a.b.d
6   a.d

I'd like to separate the first column in one or more columns based one how many separator I'll find.

The output should lool like this

> df_separated
  col1 col2 col3
1    a <NA> <NA>
2    a    b <NA>
3    a    b    c
4    a    b    d
5    a    d <NA>

I tried to use the separate function in tidyr but I need to specify a priori how many outoput columns I need.

Thank you very much for your help

Luca Monno
  • 778
  • 1
  • 8
  • 22

1 Answers1

9

You can first count the number of columns it can take and then use separate.

nmax <- max(stringr::str_count(df$x, "\\.")) + 1
tidyr::separate(df, x, paste0("col", seq_len(nmax)), sep = "\\.", fill = "right")

#  col1 col2 col3
#1    a <NA> <NA>
#2    a    b <NA>
#3    a    b    c
#4    a    b    d
#5    a    d <NA>
Ronak Shah
  • 355,584
  • 18
  • 123
  • 178