Search code examples
rtranspose

How to automate transpose in R?

I have a data of sequential visits of multiple pollinators in long form (see image 1). In order to analyse the floral constancy data, I want to transpose them according to pollinator_ID (see image 2). Is there any way to automate this in R?

Image 1

I have tried converting the data into wide format, but from there I am not sure how to proceed. Here is the Image 2 which is what I expect the output to be.

Image 2

Solution

  • Seems an odd output format, but here is an example of how you can provide code and (hopefully) get an answer.

    library(dplyr)
library(tidyr)

# create example data 
df <- data.frame(pollinator = c(rep(4, 8), rep(5, 6), rep(6,2)),
                 flower_id = sample(c("H", "L"), 16, replace=TRUE))

# create a unique id by group
# transpose the dataframe
# join all the columns and rename it as sequence
# clean up the joined columns
df %>% 
  group_by(pollinator) %>% 
  mutate(n = 1:n(),
         id = paste0(pollinator, flower_id, n)) %>% 
  pivot_wider(-n, names_from = id, values_from = flower_id) %>% 
  unite(sequence, -pollinator, sep=" ") %>% 
  mutate(sequence = trimws(gsub("NA", "", sequence)))