How can I reformat the data and map it?

Question

Let's say I have data where the column name is the city and row name is the longitude and latitude.

       Columbus Nashville  Austin  Washington D.C.     London Manchester
 lon -82.99879  -86.7816 -97.74306       -77.03687 -0.1277583  -2.242631
 lat  39.96118  36.16266  30.26715        38.90719   51.50735   53.48076

Is there a way that I can reformat this so I have:

City     lon      lat
Columbus 82.99879 39.96118
Nashville -86.7816 36.16266

The code to generate the data:

df <- structure(
list(
Columbus = structure(
  list(lon = -82.9987942, lat = 39.9611755), .Names = c("lon", "lat")),
Nashville = structure(
  list(lon = -86.7816016, lat = 36.1626638), .Names = c("lon", "lat")), 
Austin = structure(
  list(lon = -97.7430608, lat = 30.267153), .Names = c("lon", "lat")), 
`Washington D.C.` = structure(
  list(lon = -77.0368707, lat = 38.9071923), .Names = c("lon", "lat")), 
London = structure(
  list(lon = -0.1277583, lat = 51.5073509), .Names = c("lon", "lat")), 
Manchester = structure(
  list(lon = -2.2426305, lat = 53.4807593), .Names = c("lon", "lat"))), 
.Names = c("Columbus", "Nashville", "Austin", "Washington D.C.", 
"London", "Manchester"), 
row.names = c("lon", "lat"), class = "data.frame")

My final goal is to map it but with the original format, it does not work.

I have tried:

tibble::rownames_to_column(as.data.frame(df)) 
data.table::setDT(as.data.frame(df))[]

And then to map:

leaflet(data = df) %>%
  addProviderTiles("Thunderforest.OpenCycleMap") %>%
  addMarkers(~lon, ~lat)

Rahul · Answer 1 · 2017-01-27T19:22:56.067

3

Just transpose the data:

library(dplyr)  #for mutate, if you so choose to use it
df.new <- t(df)
df.new <- as.data.frame(df.new) #if you want a dataframe instead
df.new <- df.new %>% mutate(City=rownames(.))

Output:

         lon      lat            City
1  -82.99879 39.96118        Columbus
2   -86.7816 36.16266       Nashville
3  -97.74306 30.26715          Austin
4  -77.03687 38.90719 Washington D.C.
5 -0.1277583 51.50735          London
6  -2.242631 53.48076      Manchester

I love pipes, so you could just write one line:

df %>% t() %>% as.data.frame() %>% mutate(City=rownames(.))

edited Jan 27 '17 at 19:22

answered Jan 27 '17 at 17:46

Rahul

2,579
1
13
22

But then what I get is this: # A tibble: 6 × 2 lon lat 1 2 3 4 5 6 – lizzie Jan 27 '17 at 18:01
@rahul `df` is not a straightforward data frame and is not amenable to being transposed like that. – Spacedman Jan 27 '17 at 18:12
1

Yep, sorry about tbl_df(). Just used as.data.frame() and you're done. – Rahul Jan 27 '17 at 19:16

score 1 · Answer 2 · answered Jan 27 '17 at 18:11

Your df is a structure with list columns inside a data frame. Unpick it with unlist and make a matrix, then add the names:

dd  = data.frame(
 name=colnames(df),
 matrix(unlist(df),ncol=2,byrow=TRUE),
 stringsAsFactors=FALSE)
names(dd)=c("name","lon","lat")

gives a nice clean data frame:

> str(dd)
'data.frame':   6 obs. of  3 variables:
 $ name: chr  "Columbus" "Nashville" "Austin" "Washington D.C." ...
 $ lon : num  -82.999 -86.782 -97.743 -77.037 -0.128 ...
 $ lat : num  40 36.2 30.3 38.9 51.5 ...
> dd
             name         lon      lat
1        Columbus -82.9987942 39.96118
2       Nashville -86.7816016 36.16266
3          Austin -97.7430608 30.26715
4 Washington D.C. -77.0368707 38.90719
5          London  -0.1277583 51.50735
6      Manchester  -2.2426305 53.48076

How can I reformat the data and map it?

2 Answers2

Linked