Splitting numerical vector column in a dataframe into individual columns

I have a df with gps coordinates, but the xyz coordinates are contained in a vector in the first column. Here is an example of the df.

          xyz             ID                          Time

1	c(14.85, 1.32, 0)	John Doe                      13:00
2	c(14.89, 1.30, 0)	John Doe	                  13:01
3   c(14.93, 1.27, 0)   John Doe                      13:02

Ideally, I would like the data to look like this.

      x           y             z            ID       Time

1	14.85        1.32           0	       John Doe    13:00
2	14.89        1.30           0	       John Doe	   13:01
3   14.93        1.27           0          John Doe    13:02

Sorry if there is no great reproducible example, I'm not entirely sure how to create a vector within a df column.

I'm guessing you mean that xyz is a list column. You can use tidyr::unnest_wider() to extract each element of a list column into its own column.

data <- dplyr::tribble(~ xyz, ~ ID, ~ Time, 
                       c(14.85, 1.32, 0), "John Doe", "13:00",
                       c(14.89, 1.30, 0), "John Doe", "13:01",
                       c(14.93, 1.27, 0), "John Doe", "13:02")

print(data)
#> # A tibble: 3 x 3
#>   xyz       ID       Time 
#>   <list>    <chr>    <chr>
#> 1 <dbl [3]> John Doe 13:00
#> 2 <dbl [3]> John Doe 13:01
#> 3 <dbl [3]> John Doe 13:02

tidyr::unnest_wider(data, xyz, names_repair = ~ c("x", "y", "z", "ID", "Time"))
#> New names:
#> * `` -> ...1
#> * `` -> ...2
#> * `` -> ...3
#> New names:
#> * `` -> ...1
#> * `` -> ...2
#> * `` -> ...3
#> New names:
#> * `` -> ...1
#> * `` -> ...2
#> * `` -> ...3
#> # A tibble: 3 x 5
#>       x     y     z ID       Time 
#>   <dbl> <dbl> <dbl> <chr>    <chr>
#> 1  14.8  1.32     0 John Doe 13:00
#> 2  14.9  1.3      0 John Doe 13:01
#> 3  14.9  1.27     0 John Doe 13:02

Created on 2020-06-08 by the reprex package (v0.3.0)

Hi @mbk123,

Will something like this work?

# Load libraries ----------------------------------------------------------
library("tidyverse")


# Set example data --------------------------------------------------------
d <- tribble(~xyz, ~ID, ~Time,
            c(14.85, 1.32, 0),	"John Doe", "13:00",
            c(14.89, 1.30, 0),	"John Doe", "13:01",
            c(14.93, 1.27, 0),  "John Doe", "13:02")


# Separate coordinates ----------------------------------------------------
d %>%
 rowwise %>%
 mutate(xyz = str_c(xyz, collapse = ",")) %>% 
 separate(xyz, into = c("x", "y", "z"), sep = ",") %>% 
 mutate_at(vars(matches("x|y|z")), as.numeric)

Hope it helps :slightly_smiling_face:

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.