0
field Fund1 Fund2 Fund3 Fund4
Subdetail Test Test2 Test3 Test 4
Amount 500 520 100 400
Percentage Limit 30 20 20 100

So I have the above test table named data1. I wish to convert it to something like below:

Name field value
Fund1 Subdetail Test
Fund1 Amount 500
Fund1 Percentage Limit 30

How should I do this? I tried:

data1 <- reshape2::melt(data1, idvar = 'field')

But it does not work

user1464667
  • 133
  • 1
  • 2
  • 7

1 Answers1

1
library(tidyverse)     

df <- read.csv(text = "field    Fund1   Fund2   Fund3   Fund4
Subdetail   Test    Test2   Test3   Test 4
Amount  500 520 100 400
Percentage Limit    30  20  20  100", sep = '\t')

df %>%
  pivot_longer(cols = Fund1:Fund4, names_to = "Name", values_to = "Value")

Returns:

# A tibble: 12 x 3
   field            Name  Value 
   <chr>            <chr> <chr> 
 1 Subdetail        Fund1 Test  
 2 Subdetail        Fund2 Test2 
 3 Subdetail        Fund3 Test3 
 4 Subdetail        Fund4 Test 4
 5 Amount           Fund1 500   
 6 Amount           Fund2 520   
 7 Amount           Fund3 100   
 8 Amount           Fund4 400   
 9 Percentage Limit Fund1 30    
10 Percentage Limit Fund2 20    
11 Percentage Limit Fund3 20    
12 Percentage Limit Fund4 100
denisafonin
  • 1,116
  • 1
  • 7
  • 16
  • Is there any alternative to pivot_longer()? Can't seem to update tidyverse to the version that has it – user1464667 Sep 29 '21 at 13:14
  • Try `gather` it is doing the same thing – denisafonin Sep 29 '21 at 13:22
  • 1
    `pivot_longer()` isn't in `dplyr` its in `tidyr` –  Sep 29 '21 at 13:46
  • Right! I think it's best to install `tidyverse` – denisafonin Sep 29 '21 at 14:00
  • 1
    It might be best to install `tidyverse`, but I personally do not like loading the entire thing just for one function. Not saying it's wrong, but I would just be loading `tidyr` here. But as we can see, not always easy to remember what is where! –  Sep 29 '21 at 14:07