Extract list data from a column

I have a dataframe that looks really similar to this (basically a table of cities and their coordinates on a map. Note that coordinates is a list of X,Y values.

foo <- data.frame(
city = c("chicago", "new york"),
coordinate = I(list(list(10, 15), list(20, 25))),
myabbr = c("chi", "ny")
)

bar <- subset(foo, select=c("city", "coordinate"))


Right now, I can create a new table of only the city and the coordinates. I really want the X and Y values to be a separate column. This is what I tried

bar <- subset(foo, select=c("city", "coordinate[1]", "coordinate[2]"))


I'm not sure exactly how to do this though.

cbind(as.character(bar$city), apply(bar, 1, function(i)unlist(i$coordinate)))

cbind(foo, do.call(rbind, lapply(foo$coordinate, unlist)))

7 Answers
7

One more option for you, use listCol_w from splitstackshape.

listCol_w

splitstackshape

library(splitstackshape)
listCol_w(foo, "coordinate")
# city myabbr coordinate_fl_1 coordinate_fl_2
#1: chicago chi 10 15
#2: new york ny 20 25


[[

listCol_w

ggplot2

You can access a list Element with list[[index]]. In your case you can extract it this way:

list[[index]]

foo <- data.frame(city=c("chicago", "new york"), coordinate=I(list(list(10, 15), list(20,25))), myabbr=c("chi", "ny"))
foo$coordinate_x = foo$coordinate[[1]]
foo$coordinate_y = foo$coordinate[[2]]
foo


What you need is to extract the X and Y elements from the list column "coordinate". List extraction is done like list[[index]] in R.

list[[index]]

i.e.

foo <- data.frame(city=c("chicago", "new york"), coordinate=I(list(list(10, 15), list(20,25))), myabbr=c("chi", "ny"))

bar <- subset(foo, select=c("city", "coordinate"))

bar$x <- bar$coordinate[[1]]
bar$y <- bar$coordinate[[2]]

bar$coordinate <- NULL


The idea here is to unlist each row of your coordinates and cbind that with the cities, i.e.

unlist

cbind

cbind(city = as.character(bar$city),
setNames(data.frame(apply(bar, 1, function(i)unlist(i$coordinate))),
c('coordinate1', 'coordinate2')))


which gives,

city coordinate1 coordinate2
1 chicago 10 20
2 new york 15 25


We can unlist the column and bind back to original dataframe, try:

cbind(foo, do.call(rbind, lapply(foo$coordinate, unlist)))
# city coordinate myabbr 1 2
# 1 chicago 10, 15 chi 10 15
# 2 new york 20, 25 ny 20 25


bar%>%
group_by(city)%>%
mutate(coordinate=list(unlist(coordinate)),
n=list(paste0("coordinate",1:lengths(coordinate))))%>%
unnest%>%
spread(n,coordinate)

# A tibble: 2 x 3
# Groups: city [2]
city coordinate1 coordinate2
<fct> <dbl> <dbl>
1 chicago 10. 15.
2 new york 20. 25.


You can try a tidyverse as well

library(tidyverse)

foo %>%
mutate(coordinate=map(coordinate,~unlist(.) %>%
paste(., collapse=","))) %>%
separate(coordinate, into = c("x", "y"), sep=",")
# A tibble: 2 x 4
city x y myabbr
<fct> <chr> <chr> <fct>
1 chicago 10 15 chi
2 new york 20 25 ny


This gives you the expected result

.Last.value %>%
select(-myabbr)


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.