Using pivot_longer to separate columns into long format

user_1994925 0
Viewed 210

I have a df that is of non-finite length that looks like the table below. The example here only has 2 traits: "lipids" and "density". Other rows may have 50 traits or more. But will always have the same pattern of trait, unit, method. When importing into R using read_excel it changes non unique names to xxx...[col.number]. I want to use pivot_longer to cast the data into a long format from wide. I'm having difficulty manipulating the function and would appreciate some help. The final column names I would like would be geno_name, observation_id, trait, value, unit, method

Sample Data Sample Data

Desired Output (without the drop_na statement to show example) enter image description here

x <- structure(list(geno_name = "MB mixed", observation_id = 10, lipids = NA, 
    unit...3 = NA, method...4 = NA, density = 1.125, unit...6 = "g cm^-3", 
    method...7 = "3D scanning"), class = "data.frame", row.names = c(NA,-1L))

So far I have:

x %>% pivot_longer(
    cols = 3:ncol(x),
    names_to = c("trait","unit","method"),
    #need help with these other arguments
    values_drop_na = T)
r
1 Answers

The data column names to be used in 'long' format doesn't all have the same pattern in column names. Therefore, the steps included are

  • rename columns that doesn't have the ... or _ in their column names by adding those with paste/str_c

  • reshape to long format with pivot_longer - taking into account the pattern in names with either names_sep or names_pattern, specify the names_to as a vector of c(".value", "trait") in the same order we want the column values and the suffix value to be stored as separate columns

  • Once we reshaped, create a grouping column based on the values in the 'trait' (some of them are numbers - create a logical vector and get the cumulative sum) along with the other grouping 'geno_name', 'observation_id' (which doesn't create a unique column though))

  • Now summarise the other columns by slicing the first row after ordering based on NA elements i.e. if there are no NA, the first value will be non-NA or else it will be NA

library(dplyr)
library(stringr)
library(tidyr)
x %>%
   rename_at(vars(names(.)[!str_detect(names(.), "[_.]+")]), 
       ~ str_c("value...", .)) %>%
   pivot_longer(cols = 3:ncol(.), 
      names_to = c(".value", "trait"), names_sep = "\\.+") %>% 
   group_by(geno_name, observation_id, 
       grp = cumsum(str_detect(trait, "\\D+"))) %>%
   summarise(across(everything(), ~ .[order(is.na(.))][1]),
         .groups = 'drop') %>%
   select(-grp)

-output

# A tibble: 2 x 6
#  geno_name observation_id trait   value unit    method     
#  <chr>              <dbl> <chr>   <dbl> <chr>   <chr>      
#1 MB mixed              10 lipids  NA    <NA>    <NA>       
#2 MB mixed              10 density  1.12 g cm^-3 3D scanning

data

x <- structure(list(geno_name = "MB mixed", observation_id = 10, lipids = NA, 
    unit...3 = NA, method...4 = NA, density = 1.125, unit...6 = "g cm^-3", 
    method...7 = "3D scanning"), class = "data.frame", row.names = c(NA, 
-1L))
user_37322710
Related