如何重新格式化数据并对其进行映射？

Question

假设我有一个数据，其中列名是城市，行名是经度和纬度。

       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

有没有办法重新格式化它，这样我就可以：

City     lon      lat
Columbus 82.99879 39.96118
Nashville -86.7816 36.16266

生成数据的代码：

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")

我的最终目标是映射它，但使用原始格式，它不起作用。

我试过了：

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

然后映射：

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

Answer 1

只需转置数据即可：

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(.))

输出：

         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

我喜欢管道，所以你可以只写一行：

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

Answer 2

您的df是一个在数据框内具有列表列的结构。使用unlist将其取消选中，并创建一个矩阵，然后添加名称：

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

给出了一个很好的干净的数据框架：

> 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