0

I imagine this is very easy but I can't figure it out. Please help me!

I have data from a .csv file with this structure. It has 157 specimens and 21 X and Y coordinates for each one, so in total 3297 rows with 3 columns (specimen, x, y).

Specimen X Y
specimen 1 1.1 1.2
specimen 1 1.3 1.4
specimen 1 1.5 1.6
specimen 2 0.7 0.8
specimen 2 0.9 1.0
specimen 2 1.1 1.2

But I need it to look like this:

Specimen X1 Y1 X2 Y2 X3 Y3 ... X21 Y21
specimen 1 1.1 1.3 1.3 1.4 1.5 1.6 ... ... ...
specimen 2 0.7 0.8 0.9 1.0 1.1 1.2 ... ... ...

I tried in R doing this:

mydata <- read.csv("measurements.csv") # read file
mydata_coord <- data.frame(mydata$Specimen, mydata$X, mydata$Y) # Create dataframe with specimen and coordinates
mydata_nested <- mydata %>% nest_by(mydata.Specimen)

But this creates a nested list that looks like this:

Nested table I get in R

How do I make that nested list become a dataframe with pairs of x,y in the same row for each specimen with names x1,y1,x2,y2,...x21,y21? The result would then be a dataframe of 42 columns and 157 rows.

Adriana Velasquez
  • 25
  • 9
  • 1
    A couple of things: 1) Try searching here on Stackoverflow for `[r] reshape long to wide` and you should get many results for this issue. 2) `mydata_coord <- data.frame(mydata$Specimen, mydata$X, mydata$Y)` will break your names, instead try `mydata_coord <- mydata[c("Specimen","X","Y")]` 3) Consider whether you can work with your data in the long/'tidy' format without reshaping it, as that is fairly standard practice and a pretty efficient way to store the data you have - see http://vita.had.co.nz/papers/tidy-data.html – thelatemail Aug 18 '22 at 00:25
  • 1
    `library(dplyr); library(tidyr); df %>% group_by(Specimen) %>% mutate(id = row_number()) %>% pivot_wider(names_from = "id", values_from = c("X", "Y"), names_sep = "")` – Gregor Thomas Aug 18 '22 at 00:28
  • @GregorThomas thank you so much! It worked great so I can finally move on with my analysis. I'm glad people like you exist : ) – Adriana Velasquez Aug 18 '22 at 01:53
  • @GregorThomas I just realized that it's great but it gives me the values x1,x2,x3..x21, y1,y2,y3,...,y21 instead of x1,y1,x2,y2,...x21,y21. Yikessss... – Adriana Velasquez Aug 18 '22 at 02:01
  • 1
    You can reorder the columns---call the previous result `df_wide`, then `nm = names(df_wide)[-1]; nm_ordered = nm[order(as.integer(substr(nm, 2, 20)))]; df_wide = df_wide[c("Specimen", nm_ordered)]` – Gregor Thomas Aug 18 '22 at 02:14
  • 1
    @GregorThomas Thank you thank you thank you! It's one of those moments when you have a deadline and can't take the time to study the whole thing to figure it out! – Adriana Velasquez Aug 18 '22 at 02:33

0 Answers0