How to dataframe that holds a list inside of a column into multiple columns in R?

Question

I have a column called coordinates that a dataframe of latitude and longitude coordinates for each address. I want to split column into two columns called lat and long rather one column called coordinates

I have the following data:

vsn                             address         coordinates
53 079 Ashland Ave & Elston Ave Chicago IL -87.66826, 41.91873
76 097     Pulaski Rd & 71st St Chicago IL -87.72242, 41.76412
84 0A3  Long Ave & Lawrence Ave Chicago IL -87.76257, 41.96759

the coordinates column contains a list I need the data to transform into the following:

vsn                             address        Lat       Lon  
53 079 Ashland Ave & Elston Ave Chicago IL -87.66826 41.91873
76 097     Pulaski Rd & 71st St Chicago IL -87.72242 41.76412
84 0A3  Long Ave & Lawrence Ave Chicago IL -87.76257 41.96759

I dont know how to extract the data because its a data frame specifically what is shown below

The type of the column coordinates is shown below:

ouput of dput(data$coordinates)

structure(list(coordinates = list(c(-87.668257, 41.918733), c(-87.72242, 
41.764122), c(-87.76257, 41.96759))), row.names = c(53L, 76L, 
84L), class = "data.frame")

Please show the output of dput(DF) in the question where DF is the input data frame with the 3 columns. — G. Grothendieck, Apr 11 '19 at 19:26
I have reopened this. The purported duplicate is for splitting a string but this question is about spliting a list. — G. Grothendieck, Apr 11 '19 at 20:46

Rushabh Patel · Answer 1 · 2019-04-11T18:03:03.470

0

Try this-

   > library(splitstackshape)
   > cSplit(dt,"Coordinates")

Note- This function can also help you to trim white spaces while splitting.

Check ?cSplit for more help.

edited Apr 11 '19 at 18:03

answered Apr 11 '19 at 17:57

Rushabh Patel

2,672
13
34

Jilber Urbina · Answer 2 · 2019-04-11T20:38:30.257

We can use separate

> library(tidyverse)
    > dat %>% 
    separate(coordinates, c("Lat", "Lon"), sep=",") %>% 
    mutate(Lat = as.numeric(Lat),
           Lon = as.numeric(Lon))
# A tibble: 3 x 4
  vsn    address                               Lat   Lon
  <chr>  <chr>                               <dbl> <dbl>
1 53 079 Ashland Ave & Elston Ave Chicago IL -87.7  41.9
2 76 097 Pulaski Rd & 71st St Chicago IL     -87.7  41.8
3 84 0A3 Long Ave & Lawrence Ave Chicago IL  -87.8  42.0

Update

Given your the version of your question, here's a R base solution

> out <- as.data.frame(do.call(rbind, dat$coordinates))
> names(out) <- c("Lat", "Lon")
> out
        Lat      Lon
1 -87.66826 41.91873
2 -87.72242 41.76412
3 -87.76257 41.96759

I get this result: Warning: Expected 2 pieces. Additional pieces discarded in 1 rows [1]. Warning: NAs introduced by coercion Warning: NAs introduced by coercion vsn Lat Lon 1 079 NA NA 2 097 NA NA 3 0A3 NA NA — Casey Charlesworth, Apr 11 '19 at 18:16

G. Grothendieck · Accepted Answer · 2019-04-11T20:12:57.487

Because the input data was not shown reproducibly in the question, there is some question regarding whether the coordinates column is actually a list as stated in the question or what was really meant was that it is a column of comma-separated character strings. In the Note at the end we show both reproducibly and here we show how to handle both cases:

coordinates is a column of character strings

library(dplyr)
library(tidyr)

DFstring %>%
  separate(coordinates, c("Lat", "Lon"), sep = ", ", convert = TRUE)

giving:

  vsn                             address       Lat      Lon
1 079 Ashland Ave & Elston Ave Chicago IL -87.66826 41.91873
2 097     Pulaski Rd & 71st St Chicago IL -87.72242 41.76412
3 0A3  Long Ave & Lawrence Ave Chicago IL -87.76257 41.96759

coordinates column is a list

library(dplyr)

DFlist %>%
  rowwise %>%
  mutate(Lat = as.numeric(coordinates[1]), Lon = as.numeric(coordinates[2])) %>%
  ungroup %>%
  select(-coordinates)

giving:

# A tibble: 3 x 4
  vsn   address                               Lat   Lon
  <chr> <chr>                               <dbl> <dbl>
1 079   Ashland Ave & Elston Ave Chicago IL -87.7  41.9
2 097   Pulaski Rd & 71st St Chicago IL     -87.7  41.8
3 0A3   Long Ave & Lawrence Ave Chicago IL  -87.8  42.0

Note

Lines <- "vsn;address;coordinates
079;Ashland Ave & Elston Ave Chicago IL;-87.66826, 41.91873
097;Pulaski Rd & 71st St Chicago IL;-87.72242, 41.76412
0A3;Long Ave & Lawrence Ave Chicago IL;-87.76257, 41.96759"

DFstring <- read.table(text = Lines, header = TRUE, sep = ";", as.is = TRUE,
  strip.white = TRUE)

DFlist <- DFstring
DFlist$coordinates <- strsplit(DFstring$coordinates, ", ")

Update

Note that the code already posted above works with the dput output that was added to the question.

DF <-
structure(list(coordinates = list(c(-87.668257, 41.918733), c(-87.72242, 
41.764122), c(-87.76257, 41.96759))), row.names = c(53L, 76L, 
84L), class = "data.frame")

# same as code above except we use DF as the input
DF %>%
  rowwise %>%
  mutate(Lat = as.numeric(coordinates[1]), Lon = as.numeric(coordinates[2])) %>%
  ungroup %>%
  select(-coordinates)

giving:

# A tibble: 3 x 2
    Lat   Lon
  <dbl> <dbl>
1 -87.7  41.9
2 -87.7  41.8
3 -87.8  42.0

I got the following error for coordinates column is a list Error : (list) object cannot be coerced to type 'double' — Casey Charlesworth, Apr 11 '19 at 18:24
Start a new instance of R, copy and paste the code in the Note into it and then copy and paste the code in the body of the answer and you should get the same result I got. — G. Grothendieck, Apr 11 '19 at 18:45
I've updated the question now. The type is not what I initially specified. My bad. The type is actually a data frame with a list inside of it — Casey Charlesworth, Apr 11 '19 at 18:51
The code I had posted already works with the dput output in the question without making any changes to it. See Update. — G. Grothendieck, Apr 11 '19 at 20:13

score 0 · Answer 4 · answered Apr 11 '19 at 18:13

One possibility: map_df() to separate columns, then cbind() the result to the original data frame.

library(dplyr)
library(purrr)

# Example Data
X <- data_frame(
    vsn = c(53, 76, 84),
    coordinates = map(1:3, ~ as.list(rnorm(2)))
)

# Create a new data frame from the list column
purrr::map_df(X$coordinates, ~ data_frame(Lat = .x[[1]], Lon = .x[[2]]))

# A tibble: 3 x 2
    Lat   Lon
  <dbl> <dbl>
1 -1.03 1.45 
2 -1.17 0.794
3  2.06 0.646

Then cbind() to combine with the original data frame

cbind(X, purrr::map_df(X$coordinates, ~ data_frame(Lat = .x[[1]], Lon = .x[[2]])))

  vsn           coordinates       Lat       Lon
1  53   -1.034076, 1.451652 -1.034076 1.4516519
2  76 -1.1738099, 0.7943916 -1.173810 0.7943916
3  84  2.0586963, 0.6462277  2.058696 0.6462277

How to dataframe that holds a list inside of a column into multiple columns in R?

4 Answers4

Update

coordinates is a column of character strings

coordinates column is a list

Note

Update