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
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