How can I deform my data in a different arrangement

Question

I have a data like this

df_original<-structure(list(Mydata = c("AAA", "ABA", "CCC"), F1_100 = c(11448.6, 
7611.5, 12257.1), F2_100 = c(2848.2, 2366.4, 2505.6), F3_100 = c(1257.8, 
1967.7, 1681.7), F1_200M = c(13947.6, 8155.4, 13446.6), F2_200M = c(2743.3, 
2349.6, 2490.1), F3_200M = c(1612.2, 2395.3, 2129.3), F1_165T = c(13561.5, 
7628.7, 11687.4), F2_165T = c(2309.7, 2233.6, 2032), F3_165T = c(1754.1, 
2526, 2132.5), F1_320Y = c(13368.2, 7301.7, 11176.5), F2_320T = c(3065.2, 
2057.5, 1875.4), F1_320T = c(11319.4, 15751.6, 7778.5)), class = "data.frame", row.names = c(NA, 
-3L))

I just give one example for the sake of simplicity

Lets look at the first raw (first four columns)

Mydata    F1_100    F2_100  F3_100
AAA      11448.6    2848.2  1257.8

here I have AAA which has 3 values for 100 (3 columns). I want to rearrange it like this

   Mydata       F_100   
    AAA_F1      11448.6
    AAA_F2      2848.2  
    AAA_F3      1257.8

I am trying to rearrange it which will look like the following

    output_df <- structure(list(Mydata = c("AAA_F1", "AAA_F2", "AAA_F3", "ABA_F1", 
"ABA_F2", "ABA_F3", "CCC_F1", "CCC_F2", "CCC_F3"), F_100 = c(11448.6, 
2848.2, 1257.8, 7611.5, 2366.4, 1967.7, 12257.1, 2505.6, 1681.7
), F_200M = c(13947.6, 2743.3, 1612.2, 8155.4, 2349.6, 2395.3, 
13446.6, 2490.1, 2129.3), F_165T = c(13561.5, 2309.7, 1754.1, 
7628.7, 2233.6, 2526, 11687.4, 2032, 2132.5), F_320Y = c(13368.2, 
NA, NA, 7301.7, NA, NA, 11176.5, NA, NA), F_320T = c(11319.4, 
3065.2, NA, 15751.6, 2057.5, NA, 7778.5, 1875.4, NA)), class = "data.frame", row.names = c(NA, 
-9L))

As it was shown above, the previous post just put everything in one column, does not solve my issue , for instance, look at this

reshape(df_original, 
        direction = "long",
        varying = list(names(df_original)[3:13]),
        v.names = "Value")

Or what @Gregor Thomas said

library(tidyverse) 
 df_original %>% select(Mydata, contains("_100")) %>% pivot_longer(cols = -Mydata)

Something like `library(tidyverse)` and `df_original %>% select(Mydata, contains("_100")) %>% pivot_longer(cols = -Mydata)`. You can do a little string splitting and pasting to get it just right. — Gregor Thomas, Jan 19 '21 at 03:02
@Gregor Thomas it just put everything in one column, the above comment too, I was more thinking of a different structure. I just added more code example including yours — nik, Jan 19 '21 at 03:05
Ah, sorry. I assumed that `output_df` was the same as what you posted as the result for 3 values. Just a second... — Gregor Thomas, Jan 19 '21 at 03:15
@Ronak Shah your answer is much closer but with whatever reason, some data seems to be printed as NA!!! not sure if it is something to do with the input data, I am double checking — nik, Jan 19 '21 at 03:26

score 1 · Accepted Answer · answered Jan 19 '21 at 03:29

With tidyr you can use pivot_longer with unite :

library(tidyr)

df_original %>%
  pivot_longer(cols = -Mydata, 
               names_to = c('col1', '.value'), 
               names_sep = '_') %>%
  unite(Mydata, Mydata, col1)

#  Mydata  `100` `200M` `165T` `320Y` `320T`
#  <chr>   <dbl>  <dbl>  <dbl>  <dbl>  <dbl>
#1 AAA_F1 11449. 13948. 13562. 13368. 11319.
#2 AAA_F2  2848.  2743.  2310.    NA   3065.
#3 AAA_F3  1258.  1612.  1754.    NA     NA 
#4 ABA_F1  7612.  8155.  7629.  7302. 15752.
#5 ABA_F2  2366.  2350.  2234.    NA   2058.
#6 ABA_F3  1968.  2395.  2526     NA     NA 
#7 CCC_F1 12257. 13447. 11687. 11176.  7778.
#8 CCC_F2  2506.  2490.  2032     NA   1875.
#9 CCC_F3  1682.  2129.  2132.    NA     NA

The reason you have NA's is because in your output you have considered 320Y and 320T columns together but here they are kept as separate columns.

I corrected my output, great comment, I liked it so far – nik Jan 19 '21 at 03:39 — nik, Jan 19 '21 at 03:39

How can I deform my data in a different arrangement

1 Answers1