How to concatenate all vlaues in a column which is a list of data frames R

Question

structure of data frame

> str(df)
    'data.frame':   459 obs. of  6 variables:    
     $ Source     : chr  "Mumbai" "Mumbai" "Bangalore" "Bangalore" ...    
     $ Destination: chr  "Bangalore" "Bangalore" "Chennai" "Cochin" ...    
     $ src_loc    :'data.frame':    459 obs. of  2 variables:    
      ..$ lon: num  72.9 72.9 77.6 77.6 73.9 ...    
      ..$ lat: num  19.1 19.1 13 13 18.5 ...    
     $ dest_loc   :'data.frame':    459 obs. of  2 variables:    
      ..$ lon: num  77.6 77.6 80.3 76.3 78.5 ...    
      ..$ lat: num  12.97 12.97 13.08 9.93 17.39 ...    
    $ route_line:List of 459    
      ..$ :'data.frame':    219 obs. of  2 variables:    
      .. ..$ lat: num  19.1 19.1 19.1 19.1 19.1 ...    
      .. ..$ lon: num  72.9 72.9 72.9 72.9 73 ...    
      ..$ :'data.frame':    219 obs. of  2 variables:    
      .. ..$ lat: num  19.1 19.1 19.1 19.1 19.1 ...    
      .. ..$ lon: num  72.9 72.9 72.9 72.9 73 ...    
      ..$ :'data.frame':    244 obs. of  2 variables:    
      .. ..$ lat: num  13 13 13 13 13 ...    
      .. ..$ lon: num  77.6 77.6 77.6 77.6 77.6 ...
      ..$ :'data.frame':    228 obs. of  2 variables:    
      .. ..$ lat: num  13 13 13 12.9 12.9 ...    
      .. ..$ lon: num  77.6 77.6 77.6 77.6 77.6 ...    
      ..$ :'data.frame':    232 obs. of  2 variables:    
      .. ..$ lat: num  18.5 18.5 18.5 18.5 18.5 ...    
      .. ..$ lon: num  73.9 73.9 73.9 73.9 73.9 ...    
      ..$ :'data.frame':    234 obs. of  2 variables:    
      .. ..$ lat: num  15.4 15.4 15.4 15.4 15.4 ...    
      .. ..$ lon: num  75.1 75.1 75.1 75.1 75.1 ...    
      ..$ :'data.frame':    218 obs. of  2 variables:    
      .. ..$ lat: num  17.4 17.4 17.4 17.5 17.5 ...    
      .. ..$ lon: num  78.5 78.5 78.5 78.5 78.5 ...

so on..

> df$route_line[[1]] #gives a data frame



            lat      lon
    1   19.07597 72.87765
    2   19.06575 72.89918
    3   19.06331 72.91443
    4   19.05159 72.93661
    5   19.06758 72.98437
    6   19.06653 73.02000
    7   19.04099 73.02868
    8   19.02309 73.04452
    9   19.03844 73.07676
    10  18.99688 73.13215
    11  18.98191 73.14718
    12  18.96049 73.15789
    13  18.94201 73.15694
    14  18.92484 73.16662
    15  18.89439 73.20433
    16  18.84075 73.24026
    17  18.81434 73.27669
    18  18.79409 73.29148
    19  18.77373 73.32182
    20  18.77023 73.33760
    21  18.76414 73.34698
    22  18.77114 73.36076
    23  18.76580 73.35765
    24  18.77090 73.36348
    25  18.75822 73.37283
    26  18.76368 73.38653
    27  18.76939 73.40145
    28  18.76301 73.41848
    29  18.75766 73.42920
    30  18.73973 73.42921

I want to create a new column (with name route_str) which contains the string obtained by concatenating all latitudes and longitudes in the above obtained data frame for every row in df

For example,

> df$route_str[1] #should give
[1] "19.07597 72.87765, 19.06575 72.89918, 19.06331 72.91443,19.05159 72.93661..." so  on till 30

I tried this

> fun <- function(ip)
+ {
+ a <- ip[[1]]
+ a[3] <- paste(a[1],a[2]," ")
+ op <- paste(a[3],collapse = ",")
+ return(op)
+ }
> df$route_str <- lapply(df$route_line,fun)

But the output I get is

> unique_routes$route_str[1]
[[1]]
[1] "19.0759696960449 19.0657501220703  "

I tried to create reproducible data using following code but the structure isn't the same

df <- data.frame(src=c("chennai","Mumbai","Bangalore"),dest=c("Mumbai","Bangalore","Mumbai"),route=list(list(lat=c(19,20,21),lon=c(72,73,74)),data.frame(lat=c(19,20,21),lon=c(72,73,74)),data.frame(lat=c(19,20,21),lon=c(72,73,74))))

But the structure of above created data is as follows

> str(df)
'data.frame':   3 obs. of  8 variables:
 $ src        : Factor w/ 3 levels "Bangalore","chennai",..: 2 3 1
 $ dest       : Factor w/ 2 levels "Bangalore","Mumbai": 2 1 2
 $ route.lat  : num  19 20 21
 $ route.lon  : num  72 73 74
 $ route.lat.1: num  19 20 21
 $ route.lon.1: num  72 73 74
 $ route.lat.2: num  19 20 21
 $ route.lon.2: num  72 73 74

I am using R version 3.3.1 on windows 10 pls help!

EDIT:

This is how I ended up with that complicated data frame

Initial Data frame was like this

> df <- data.frame(source=c("chennai","Mumbai","Bangalore"),destination=c("Mumbai","Bangalore","Mumbai"))

> df
     source destination
1   chennai      Mumbai
2    Mumbai   Bangalore
3 Bangalore      Mumbai

I want to have a column containing single string with all the way-points(lat lon) between source and destination separated by a comma I used googleway package to get waypoints

> library(googleway)
> res <- function(src,dest,key) #key is google maps API key
+ {
+ polylinex <- google_directions(origin = src,destination = dest,key = key)
+ return(polylinex$routes$overview_polyline$points)
+ } 

> df$source <- as.character(df$source)
> df$destination <- as.character(df$destination)
> df$x <- mapply(res,df$source,df$destination,key)
> df$route_line <- lapply(df$x,function(y) googleway::decode_pl(y))
> df <- df[,!(names(df)=="x")]
> str(df)
'data.frame':   3 obs. of  3 variables:
 $ source     : chr  "chennai" "Mumbai" "Bangalore"
 $ destination: chr  "Mumbai" "Bangalore" "Mumbai"
 $ route_line :List of 3
  ..$ :'data.frame':    219 obs. of  2 variables:
  .. ..$ lat: num  13.1 13.1 13.1 13.1 13.1 ...
  .. ..$ lon: num  80.3 80.2 80.2 80.2 80.2 ...
  ..$ :'data.frame':    219 obs. of  2 variables:
  .. ..$ lat: num  19.1 19.1 19.1 19.1 19.1 ...
  .. ..$ lon: num  72.9 72.9 72.9 72.9 73 ...
  ..$ :'data.frame':    218 obs. of  2 variables:
  .. ..$ lat: num  13 13 13 13 13 ...
  .. ..$ lon: num  77.6 77.6 77.6 77.6 77.5 ...

Let's keep it simple, using a simple data frame from cars I was able to create a new column like this `cars$test <- paste0(cars$speed, ", ", cars$dist)` . So I presume using something like that would work woudl'nt it ? `df$route_str <- paste0(df$route_line[[1]][1], ", ", df$route_line[[1]][2])` Or am I missing something from your explanation ? If so, could you provide some sample data so we can help you. — Pierre Chevallier, Mar 02 '17 at 15:43
I edited the post explaining how to get the sample data.. Pls check it. Thanks! :) — troglodyte07, Mar 02 '17 at 17:29

SymbolixAU · Accepted Answer · 2017-03-03T01:03:05.493

A slight modification to your lapply into an sapply, and altering the paste sequence slightly will get you want you want

df$route_str <- sapply(df$x, function(y){
    df_coords <- decode_pl(y)
    paste0(t(sapply(df_coords, paste0)), collapse = ",")
})


str(df)

'data.frame':   3 obs. of  4 variables:
    $ source     : chr  "chennai" "Mumbai" "Bangalore"
$ destination: chr  "Mumbai" "Bangalore" "Mumbai"
$ x          : chr  "weznA{z|hNjrAlkDue@vsDtVnhD|dAnkErSbdI~kGzmRtmLjrNldI|iWnjBbuDf^duJgPzqNsiCtaIyLpnOyXzrKe{AvaG|JxpF~VpkCga@tkG_sBp|Cev@fvDpI|gF"| __truncated__ "ywlsBi|x{Lz~@qeCfNi~AfhAsiC}bBoiHpEu}Er~Cgu@znB_bB}~AohEvbGeyIp|A}|AzdC}aAnrB|DhjBo{@h}DujFfnIq_F`dDubFp}Bm{Af~Bs|DzTsaB`e@uy@w"| __truncated__ "oodnA}drxMkcAhKggApm@s}A|uAey@|rAi~BdjF{fDpaLgxB||F}`DvxE{sDdmDgkGthKmlK|vJmgIbzJa`BrjCssC|aBw`Dvw@osBrkCutNpbIigD|sCk`Ft_C}iPv"| __truncated__
$ route_str  : chr  "13.0826797485352,80.2706985473633,13.0693397521973,80.2431106567383,13.0755300521851,80.2141876220703,13.0717391967773,80.18706"| __truncated__ "19.0759696960449,72.8776473999023,19.0657501220703,72.8991775512695,19.0633087158203,72.9144287109375,19.0515899658203,72.93660"| __truncated__ "12.9715995788574,77.5945510864258,12.9825401306152,77.5925750732422,12.9940996170044,77.5851287841797,13.0092391967773,77.57122"| __truncated__

Note: I'm the googleway author, thanks for using the package

Thanks a lot! That worked! I want that structure because I'm trying to get toll prices in a particular route using api call and it takes a list of waypoints as a string in its parameters. The other package I used is ggmap. But googleway seems to give a lot more waypoints. So thanks for the package too! :) — troglodyte07, Mar 03 '17 at 01:01
@sruthisripathi - ah, that makes sense now :) If this answers your question feel free to press the 'tick' under the voting arrows :) — SymbolixAU, Mar 03 '17 at 01:02
@sruthisripathi - not directly that I'm aware of. If you can call R scripts from python then it's probably possible — SymbolixAU, Mar 03 '17 at 21:27

How to concatenate all vlaues in a column which is a list of data frames R

1 Answers1