CODEWITHSUNDEEP
rdataframedata-manipulation
j.doe
j.doe

Reputation: 35

create data frame with columns being unique values in other data frame

I am trying to convert my data frame so that the columns are unique lat values and rows are unique lon values with the values being the distance in r.

My original data frame is similar to this:

df <- data.frame( lat =c(0,0,0,25,25,25,30,30,30), lon =c(1,5,10,1,5,10,1,5,10), distance = c(20, 22, 25, 10, 12, 15, 5, 7, 9))
df

but i want to convert it into a form like this (although column names are not necessary)

final_df <- data.frame(lat0 = c(20,22,25), lat25 = c(10,12,15), lat30= c(5,7,9))
final_df

Upvotes: 1

Views: 450

Answers (3)

Chris Ruehlemann
Chris Ruehlemann

Reputation: 21440

Use tapply:

t(tapply(df$distance, list(df$lat, df$lon), sum))
    0 25 30
1  20 10  5
5  22 12  7
10 25 15  9

If you want column names:

t <- as.data.frame(t(tapply(df$distance, list(df$lat, df$lon), sum)))
colnames(t) <- paste("lat", names(t), sep = "")
t
   lat0 lat25 lat30
1    20    10     5
5    22    12     7
10   25    15     9

Upvotes: 1

GKi
GKi

Reputation: 39737

You can use xtabs as long as there is only one combination of lat and lon.

xtabs(distance ~ lon + lat, df)
#as.data.frame.matrix(xtabs(distance ~ lon + lat, df)) #Alternative to get a data.frame
#    lat
#lon   0 25 30
#  1  20 10  5
#  5  22 12  7
#  10 25 15  9

Upvotes: 1

Roman
Roman

Reputation: 17678

you can try

library(tidyverse)
df %>% 
  pivot_wider(names_from = lat, 
              names_prefix= "lat",
              values_from = distance)
# A tibble: 3 x 4
    lon  lat0 lat25 lat30
  <dbl> <dbl> <dbl> <dbl>
1     1    20    10     5
2     5    22    12     7
3    10    25    15     9

add select(-lon) to remove this column after making the data "wide".

Upvotes: 1

Related Questions