Transpose specific columns to organize a dataframe in R

Question

I have a file similar to this but with more columns, but this is the logic:

23-07 23-08 23-09 23-10 23-11    ID   Sire   trait1  trait2
        150                       1   John    10       5
 200                              2   James   15       2
                  300             3   Josh    60       1
             500                  4   Peter   80       0
                        600       5   Peter   90       8

And I would like this:

 Date     Weight   ID   Sire   trait1  trait2
 23-07     150      1   John    10       5
 23-09     200      2   James   15       2
 23-08     300      3   Josh    60       1
 23-10     500      4   Peter   80       0
 23-10     600      5   Peter   90       8

I tried several approaches involving spreadand tidyr, but I could not to do this.

You can split your data frame, transpose the columns you want to transpose and `cbind` what you have split. In your case, however, transposition would not give you what you want! — haci, Sep 25 '19 at 23:04
Use `gather` for the date columns, and then `select` to reorder the columns. You might need to use filter to remove rows with Weight==NA after the gather (or there might be an option in gather to do this). — Simon Woodward, Sep 25 '19 at 23:07

yusuzech · Accepted Answer · 2019-09-25T23:22:18.913

With tidyr 1.0.0, you can use pivot_longer(), you can read more in this link

df %>%
    pivot_longer(
        -c(ID,Sire,trait1,trait2),
        names_to = "Date",
        values_to = "Weight",
        values_drop_na = TRUE
    )

returns

# A tibble: 5 x 6
  ID    Sire  trait1 trait2 Date  Weight
  <chr> <chr> <chr>  <chr>  <chr> <chr> 
1 1     John  10     5      23-07 150   
2 2     James 15     2      23-09 200   
3 3     Josh  60     1      23-08 300   
4 4     Peter 80     0      23-10 500   
5 5     Peter 90     8      23-10 600

df <- structure(list(`23-07` = c("150", NA, NA, NA, NA), `23-09` = c(NA, 
"200", NA, NA, NA), `23-08` = c(NA, NA, "300", NA, NA), `23-10` = c(NA, 
NA, NA, "500", "600"), ID = c("1", "2", "3", "4", "5"), Sire = c("John", 
"James", "Josh", "Peter", "Peter"), trait1 = c("10", "15", "60", 
"80", "90"), trait2 = c("5", "2", "1", "0", "8")), row.names = c(NA, 
-5L), class = c("tbl_df", "tbl", "data.frame"))

`pivot_longer()` has an argument to drop `NA` values so you can add `values_drop_na = TRUE` and avoid using `filter()`. — Ritchie Sacramento, Sep 25 '19 at 23:10

Transpose specific columns to organize a dataframe in R

1 Answers1