Using Dcast in R to transform dataframe

Question

I have the following dataframe. And would like to get the desired output

data.frame(df)
num  Name1   Result1 Name2  Result2   Name3  Result3
1    75%      74      100%    101      50%   50
2    75%      73      100%    101      50%   49
3    50%      50      100%    105      125%  128

I tried Dcast using the following reshape2::dcast(df, num ~ Name1 + Name2 + Name3, value.var=c("Result1", "Result2", "Result3"))

The output from Dcast is close to my desired output but I would like only unique 'Name' values as my new columns. I can imagine that I can clean the table using aggregate before using Dcast but that seems excessive? I'm not sure if there's a faster way?

Desired output:

num  50%   75%      100%    125%    
1    50    74       101     NA    
2    49    73       101     NA      
3    50    NA       100     128

I would appreciate any help

jsv · Accepted Answer · 2021-03-10T01:53:22.503

You can find more information on the steps here and here.

dat %>% 
  rename_at(vars(matches("[0-9]")),
            ~str_replace(.,"(\\d)(\\w*)","\\2_\\1")) %>% 
  pivot_longer(cols=matches("_"),names_to=c(".value","group"),
               names_sep="_") %>% 
  dplyr::select(-group) %>% 
  pivot_wider(names_from = "Name",values_from="Result")

# A tibble: 3 x 5
    num `75%` `100%` `50%` `125%`
  <int> <int>  <int> <int>  <int>
1     1    74    101    50     NA
2     2    73    101    49     NA
3     3    NA    105    50    128

Alternately...

reshape(dat, idvar="num", direction="long", 
        varying=list(Name=c(2,4,6), Result=c(3,5,7)),
        v.names = c("Name", "Result") ) %>% 
  dplyr::select(-time) %>% 
  dcast(num ~ Name)
    
  num 50% 75% 100% 125%
1   1  50  74  101   NA
2   2  49  73  101   NA
3   3  50  NA  105  128

Cool thanks! you need to library(stringr) if anyone else looks at this — shah, Mar 10 '21 at 04:03

score 1 · Answer 2 · answered Mar 10 '21 at 03:27

Get the data in long format so we have data in two columns Name and Result. We can then get the data in wide format.

library(dplyr)
library(tidyr)

df %>%
  pivot_longer(cols = -num, 
               names_to = '.value', 
               names_pattern = '([A-Za-z]+)\\d+') %>%
  arrange(readr::parse_number(Name)) %>%
  pivot_wider(names_from = Name, values_from = Result)

#   num   `50%` `75%` `100%` `125%`
#  <int> <int> <int>  <int>  <int>
#1     1    50    74    101     NA
#2     2    49    73    101     NA
#3     3    50    NA    105    128

Using Dcast in R to transform dataframe

2 Answers2