CODEWITHSUNDEEP
r
nik
nik

Reputation: 2584

How can I deform my data in a different arrangement

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)

Upvotes: 0

Views: 31

Answers (1)

Ronak Shah
Ronak Shah

Reputation: 388797

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.

Upvotes: 1

Related Questions